Case 1: If undo data file lost after cleanly shutdown
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.
Responses