Missing Undo datafile on hpux

Hello..

One of the undo datafile is missing from our filesystem and i needed some help to figure out how to fix it.. I know file 52 is an undo datafile because from where it was copied from..the dba did a refresh from prod to uat but he must have used the old create controlfile script and left out undo03.dbf

alert.log is showing this error message.
SMON: following errors trapped and ignored:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 52 cannot be read at this time
ORA-01111: name for data file 52 is unknown - rename to correct file
ORA-01110: data file 52: '/apps/oracle/product/9.2.0_64bit/dbs/MISSING00052'
*** 2006-01-27 18:37:39.943



SQL> select file#, name, status from v$datafile where file#=52;

FILE#
----------
NAME
--------------------------------------------------------------------------------
STATUS
-------
52
/apps/oracle/product/9.2.0_64bit/dbs/MISSING00052
RECOVER

Quote:

---

Originally Posted by Oracle9iNewbie

Hello..

One of the undo datafile is missing from our filesystem and i needed some help to figure out how to fix it.. I know file 52 is an undo datafile because from where it was copied from..the dba did a refresh from prod to uat but he must have used the old create controlfile script and left out undo03.dbf

alert.log is showing this error message.
SMON: following errors trapped and ignored:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 52 cannot be read at this time
ORA-01111: name for data file 52 is unknown - rename to correct file
ORA-01110: data file 52: '/apps/oracle/product/9.2.0_64bit/dbs/MISSING00052'
*** 2006-01-27 18:37:39.943



SQL> select file#, name, status from v$datafile where file#=52;

FILE#
----------
NAME
--------------------------------------------------------------------------------
STATUS
-------
52
/apps/oracle/product/9.2.0_64bit/dbs/MISSING00052
RECOVER

---

I believe that you can create a new undo tablespace. alter the database to use the new one.
Drop the old one.

The db is at mount state..
shall i do the following?

Create UNDO1 Tablespace undo_tbs1
datafile '/vol1/data/uday/undotbs1.dbf' size 25m autoextend on;
shutdown immediate;;
change undo_tablespace parameter in init.ora to undo1 from undo
startup mount
alter tablespace undo offline drop including contents;

will this work?

What I stated in my previous post worked for me.....

thanks for your help!