HOME > > Case 1: If undo data file lost after cleanly shutdown

Case 1: If undo data file lost after cleanly shutdown

Anup - Friday, May 20, 2011

When you are trying to start database:

SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1279120 bytes
Variable Size              71306096 bytes
Database Buffers           92274688 bytes
Redo Buffers                2912256 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/DB/ANUP/undotbs01.dbf'

Steps 1 Shutdown immediate

Step 2 Set UNDO_MANAGEMENT=manual in init file.

Step 3 Mount the database in restricted mode.

SQL> STARTUP RESTRICT MOUNT

Step 4 Offline drops the lost undo data file.

SQL> alter database datafile '/DB/ANUP/undotbs01.dbf' offline drop;

Step 5 Open the database.

SQL> ALTER DATABASE OPEN

Step 6 Drop the undo tablespace which contains rollback segments to which the datafile belonged.

SQL> drop tablespace undotbs1 including contents;

Step 7 Recreate the undo tablespace.

SQL> create undo tablespace undotbs2 datafile '/DB/ANUP/undotbs02.dbf' size 100m;

Step 8 Edit the parameter file setting:

UNDO_MANAGEMENT=AUTO and UNDO_TABLESPACE=

Step 9 Shutdown and startup the the database.

Contact me

Get in Touch

Need to get touch with me? Please fill out the form with your enquiry.

Name
Anup Srivastav
Address
Lucknow - Utter Pradesh
Email
myindiandba@gmail.com
Message me