Recover from loss of UNDO datafile + ORA-01092: ORACLE instance terminated.
GKJoeJul 16 2009 — edited Jul 17 2009Hi All,
I lost the datafile for the undo tablespace and database was down.
Tried the normal method of making undo_management=MANAUL and drop and create undo tablespace with no luck, looks like there were uncommited transaction..
Please let me know how can i bring back the database..
Oracle 10.1.0.3.0
AIX 5.3
Archivelog Mode and have a valid backup too.
------------------------------------------------------------------------------------------------------------
SQL> startup mount pfile='/usr/app/sys/oracle/OraHome_1/dbs/initnmnt1.ora'
ORACLE instance started.
Total System Global Area 356515840 bytes
Fixed Size 1329816 bytes
Variable Size 224900456 bytes
Database Buffers 130023424 bytes
Redo Buffers 262144 bytes
Database mounted.
SQL>
SQL>
SQL> alter database datafile '/data/oracle/oradata/nmnt1/undotab01.dbf' offline drop;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
------------------------------------------------------------------------------------------------------------
~Thanks