Physical Undo datafile deleted

[smdakram]

!hello sir,

Iam using oracle9i databse on win-2000.

Database runing in Noarachive log mode.
Database is in shutdown state.
undomanagent AUTO.
Physicaly i delete undo datafile using OS COMMAND.
Can i get database back .I have no backup . No need of that undo datafile . I want to bring database back to normal stat. can i.
I try lot but i fail to get back to normal state.


akram shaik
OCA
akram_tri_a@yahoo.com

[Thomasps]

SQL> startup
ORACLE instance started.

Total System Global Area  105978600 bytes
Fixed Size                   453352 bytes
Variable Size              83886080 bytes
Database Buffers           20971520 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: 'D:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF'

SQL> alter database datafile 'D:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF' offline;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter tablespace UNDOTBS1 
add datafile 'D:\ORACLE\ORADATA\TEST\UNDOTBS02.DBF' size 200M;

Tablespace altered.

SQL> alter database datafile 'D:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF' 
offline drop;

Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  105978600 bytes
Fixed Size                   453352 bytes
Variable Size              83886080 bytes
Database Buffers           20971520 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.

[smdakram]

Thanks . I forgot these steps to perfrom.

Thank Q very much.Thomasps

[Thomasps]

You might get one more problem while doing any transaction- this is because ( I assume) the undo segments defined are refering to the lost file. I created another undo tablespace and changed the parameter undo_tablespace to the new one and problem resolved.