Recovery from a lost Rollback tablespace datafile
The situation is, I lost a disk where Rollback tablespace datafile was sitting. The database was running in no archive log mode, only export database backup was with me. This is not a production database. For you, if this is a production database please consult Oracle support.
Solution:-
Shutdown the database with abort option and stop listener for this database.
Add _offline_rollback_segments =(r01,r02,r03,r04) in initQMI3.ora database
Start the database
Collect no. of rollback segments and their information
SVRMGR> select segment_name,owner,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
------------ ----- ------------- ------
SYSTEM SYS SYSTEM ONLINE
R01 SYS ROLLBACK NEEDS RECOVERY
R02 SYS ROLLBACK NEEDS RECOVERY
R03 SYS ROLLBACK NEEDS RECOVERY
R04 SYS ROLLBACK NEEDS RECOVERY
5 rows selected.
Drop all rollback segments which says "NEEDS RECOVERY"
SVRMGR> drop rollback segment r02;
Statement processed.
SVRMGR> drop rollback segment r01;
Statement processed.
SVRMGR> drop rollback segment r03;
Statement processed.
SVRMGR> drop rollback segment r04;
Statement processed.
Check whether all corrupt rollback segments are dropped
SVRMGR> select * from dba_rollback_segs;
SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
------------ ----- --------------- ------
SYSTEM SYS SYSTEM ONLINE
1 row selected.
Drop the rollback tablespace itself
SVRMGR> drop tablespace rollback including contents;
Statement processed.
SVRMGR> host
Find space to place rollback tablespace datafile
(51) oracle@uranus-80% pwd
/export/home/oracle/admin/QMI3/pfile
(52) oracle@uranus-80% df -k
Filesystem kbytes used avail capacity Mounted on
/dev/vx/dsk/rootvol 526270 228778 244865 49% /
/dev/vx/dsk/usr 246463 213361 8456 97% /usr
/proc 0 0 0 0% /proc
fd 0 0 0 0% /dev/fd
swap 300032 32 300000 1% /tmp
/dev/vx/dsk/uranus2dg/vol13 2042766 1297883 744883 64% /export/home/oracle
/dev/vx/dsk/uranusdg/disk55 1952350 221541 1535574 13% /disk53
/dev/vx/dsk/uranus2dg/vol10 8171464 5572830 2598634 69% /vol10
/dev/vx/dsk/uranus2dg/vol11 8171464 6916493 1254971 85% /vol11
/dev/vx/dsk/uranus2dg/vol12 2042766 1282870 759896 63% /vol12
(53) oracle@uranus-80% exit
(54) oracle@uranus-80%
SVRMGR> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/disk53/oradata/QMI3/system01.dbf
/stripe4/oradata/QMI3/temp01.dbf
/disk54/oradata/QMI3/users01.dbf
/vol10/oradata/QMI3/cosmos_data01.dbf
/vol10/oradata/QMI3/cosmos_data02.dbf
/vol11/oradata/QMI3/cosmos_indx01.dbf
/vol11/oradata/QMI3/cosmos_indx02.dbf
/disk43/oradata/QMI3/dweb_data01.dbf
/disk54/oradata/QMI3/dweb_indx01.dbf
/disk43/oradata/QMI3/cosmos_vp_d01.dbf
/disk54/oradata/QMI3/cosmos_vp_i01.dbf
11 rows selected.
Create Rollback Tablespace and Create rollback segments
SVRMGR> create tablespace rollback datafile '/disk53/oradata/QMI3/rollback01.dbf' size 200m;
Statement processed.
SVRMGR> create rollback segment r01 storage (initial 2m next 2m optimal 20m);
Statement processed.
SVRMGR> create rollback segment r02 storage (initial 2m next 2m optimal 20m);
Statement processed.
SVRMGR> create rollback segment r03 storage (initial 2m next 2m optimal 20m);
Statement processed.
SVRMGR> create rollback segment r04 storage (initial 2m next 2m optimal 20m);
Statement processed.
Verify database to recover any datfiles?
SVRMGR> select * from v$recover_file;
FILE# ONLINE ERROR CHANGE# TIME
---------- ------- ------------------ ---------- --------------------
0 rows selected.
SVRMGR>
Important Post-recovery steps
1) Shutdown the database
2) Remove _offline_rollback_segments =(r01,r02,r03,r04)
3) From initQMI3.ora file.
4) Add the rollback_segments =(r01,r02,r03,r04) in initQMI3.ora file.
5) startup the database
********************************************************************************