Recovery scenario during loss of Undo tablespace datafile

LOSS OF UNDO TABLESPACE DATAFILE

(Complete Recovery and Only will loose the un commited transactions. Loss of undo datafile need the database outage)
1. I have created one temporary table for testing.
SQL> create table dba_segments_new as select * from dba_segments;
Table created.
1A. I have inserted the data into table and not commited.
SQL>insert into dba_segments_new select * from dba_segments_new;
4488 rows created.
SQL> /
8976 rows created.
SQL> /
17952 rows created.
SQL> /
35904 rows created.
SQL> select count(1) from dba_segments_new;
  COUNT(1)———-     71808
1B. Here we can see the  undo segments active and expired info below.
SQL> select tablespace_name,status,sum(bytes) from dba_undo_extents group by tablespace_name,status;
TABLESPACE_NAME                STATUS    SUM(BYTES)—————————— ——— ———-UNDOTBS1                       ACTIVE       1179648UNDOTBS1                       EXPIRED      5505024
SQL> select file_name from dba_data_files where tablespace_name=’UNDOTBS1′;
FILE_NAME——————————————————————————–/restore1/oracle/SID/data/SID_undotbs01.dbf

2. I have deleted the undo tablespace datafile at OS level. Below are the details.
Ls -ltrtotal 4315312-rw-r–r–   1 oracle   dba            5017 Oct 26 13:25 abcd-rw-r—–   1 oracle   dba        52436992 Oct 29 06:05 SID_users01.dbf-rw-r—–   1 oracle   dba       104865792 Oct 29 06:05 SID_autoindexes01.dbf-rw-r—–   1 oracle   dba       524296192 Oct 29 06:05 SID_autodata01.dbf-rw-r—–   1 oracle   dba       524296192 Oct 29 07:39 SID_sysaux01.dbf-rw-r—–   1 oracle   dba       209723392 Oct 29 07:44 SID_undotbs01.dbf-rw-r—–   1 oracle   dba       734011392 Oct 29 07:50 SID_system01.dbf-rw-r—–   1 oracle   dba        10486272 Oct 29 07:50 SID_redo02b.log-rw-r—–   1 oracle   dba        10486272 Oct 29 07:50 SID_redo02a.log-rw-r—–   1 oracle   dba        10486272 Oct 29 07:50 SID_redo01b.log-rw-r—–   1 oracle   dba        10486272 Oct 29 07:50 SID_redo01a.log-rw-r—–   1 oracle   dba         5881856 Oct 29 07:53 SID_control03.ctl-rw-r—–   1 oracle   dba         5881856 Oct 29 07:53 SID_control02.ctl-rw-r—–   1 oracle   dba         5881856 Oct 29 07:53 SID_control01.ctl
rm SID_undotbs01.dbf
3. After deleting the undo tablespace datafile, again I have insrted the data into table and finally got the below error.
SQL> select count(1) from dba_segments_new;
  COUNT(1)———-     71808
SQL> insert into dba_segments_new select * from dba_segments_new;
71808 rows created.
SQL> /insert into dba_segments_new select * from dba_segments_new*ERROR at line 1:ORA-01116: error in opening database file 2ORA-01110: data file 2: ‘/restore1/oracle/SID/data/SID_undotbs01.dbf’ORA-27041: unable to open fileIBM AIX RISC System/6000 Error: 2: No such file or directoryAdditional information: 3
4. Shut down the database using shut abort.
SQL> shut abortORACLE instance shut down.

5. Connect to RMAN and mount the database (If we have rman backup).
RMAN> startup mount;
Oracle instance starteddatabase mounted
Total System Global Area     838860800 bytes
Fixed Size                     2074928 bytesVariable Size                503318224 bytesDatabase Buffers             327155712 bytesRedo Buffers                   6311936 bytes
6. Offline the datafile which is lost
RMAN> alter database datafile 2 offline;
RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-00558: error encountered while parsing input commandsRMAN-01009: syntax error: found “datafile”: expecting one of: “mount, open”RMAN-01007: at line 1 column 16 file: standard input
RMAN>
Note: Got the above error because of syntax error.
RMAN> sql ‘alter database datafile 2 offline’;
sql statement: alter database datafile 2 offline
7. Restore the datafile:
RMAN> restore datafile 2;
Starting restore at 29-OCT-09using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00002 to /restore1/oracle/SID/data/SID_undotbs01.dbfchannel ORA_DISK_1: reading from backup piece /restore1/oracle/SID/backup/SID_20091013_700141178_4889_1_L0.dbfchannel ORA_DISK_1: restored backup piece 1piece handle=/restore1/oracle/SID/backup/SID_20091013_700141178_4889_1_L0.dbf tag=TAG20091013T113938channel ORA_DISK_1: restore complete, elapsed time: 00:00:07Finished restore at 29-OCT-09

8. Recover the datafile:
RMAN> recover datafile 2;
Starting recover at 29-OCT-09using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 125 is already on disk as file /restore1/oracle/SID/admin/a01/1_125_700066042.dbfarchive log thread 1 sequence 126 is already on disk as file /restore1/oracle/SID/admin/a01/1_126_700066042.dbfarchive log thread 1 sequence 127 is already on disk as file /restore1/oracle/SID/admin/a01/1_127_700066042.dbfarchive log thread 1 sequence 128 is already on disk as file /restore1/oracle/SID/admin/a01/1_128_700066042.dbfarchive log thread 1 sequence 129 is already on disk as file /restore1/oracle/SID/admin/a01/1_129_700066042.dbfarchive log thread 1 sequence 130 is already on disk as file /restore1/oracle/SID/admin/a01/1_130_700066042.dbfarchive log thread 1 sequence 131 is already on disk as file /restore1/oracle/SID/admin/a01/1_131_700066042.dbfarchive log thread 1 sequence 132 is already on disk as file /restore1/oracle/SID/admin/a01/1_132_700066042.dbfarchive log thread 1 sequence 133 is already on disk as file /restore1/oracle/SID/admin/a01/1_133_700066042.dbfarchive log thread 1 sequence 134 is already on disk as file /restore1/oracle/SID/admin/a01/1_134_700066042.dbfarchive log thread 1 sequence 135 is already on disk as file /restore1/oracle/SID/admin/a01/1_135_700066042.dbfarchive log thread 1 sequence 136 is already on disk as file /restore1/oracle/SID/admin/a01/1_136_700066042.dbfarchive log thread 1 sequence 137 is already on disk as file /restore1/oracle/SID/admin/a01/1_137_700066042.dbfarchive log thread 1 sequence 138 is already on disk as file /restore1/oracle/SID/admin/a01/1_138_700066042.dbfarchive log thread 1 sequence 139 is already on disk as file /restore1/oracle/SID/admin/a01/1_139_700066042.dbfarchive log thread 1 sequence 140 is already on disk as file /restore1/oracle/SID/admin/a01/1_140_700066042.dbfarchive log thread 1 sequence 141 is already on disk as file /restore1/oracle/SID/admin/a01/1_141_700066042.dbfarchive log thread 1 sequence 142 is already on disk as file /restore1/oracle/SID/admin/a01/1_142_700066042.dbfarchive log thread 1 sequence 143 is already on disk as file /restore1/oracle/SID/admin/a01/1_143_700066042.dbfarchive log thread 1 sequence 144 is already on disk as file /restore1/oracle/SID/admin/a01/1_144_700066042.dbfarchive log thread 1 sequence 145 is already on disk as file /restore1/oracle/SID/admin/a01/1_145_700066042.dbfarchive log thread 1 sequence 146 is already on disk as file /restore1/oracle/SID/admin/a01/1_146_700066042.dbfarchive log thread 1 sequence 147 is already on disk as file /restore1/oracle/SID/admin/a01/1_147_700066042.dbfarchive log thread 1 sequence 148 is already on disk as file /restore1/oracle/SID/admin/a01/1_148_700066042.dbfarchive log thread 1 sequence 149 is already on disk as file /restore1/oracle/SID/admin/a01/1_149_700066042.dbfarchive log thread 1 sequence 150 is already on disk as file /restore1/oracle/SID/admin/a01/1_150_700066042.dbfarchive log thread 1 sequence 151 is already on disk as file /restore1/oracle/SID/admin/a01/1_151_700066042.dbfarchive log thread 1 sequence 152 is already on disk as file /restore1/oracle/SID/admin/a01/1_152_700066042.dbfchannel ORA_DISK_1: starting archive log restore to default destinationchannel ORA_DISK_1: restoring archive logarchive log thread=1 sequence=23channel ORA_DISK_1: restoring archive logarchive log thread=1 sequence=24channel ORA_DISK_1: restoring archive logarchive log thread=1 sequence=25channel ORA_DISK_1: reading from backup piece /restore1/oracle/SID/backup/SID_20091013_700141227_4894_1.arcchannel ORA_DISK_1: restored backup piece 1piece handle=/restore1/oracle/SID/backup/SID_20091013_700141227_4894_1.arc tag=TAG20091013T114018channel ORA_DISK_1: restore complete, elapsed time: 00:00:02archive log filename=/restore1/oracle/SID/admin/a01/1_23_700066042.dbf thread=1 sequence=23archive log filename=/restore1/oracle/SID/admin/a01/1_24_700066042.dbf thread=1 sequence=24archive log filename=/restore1/oracle/SID/admin/a01/1_25_700066042.dbf thread=1 sequence=25archive log filename=/restore1/oracle/SID/admin/a01/1_26_700066042.dbf thread=1 sequence=26archive log filename=/restore1/oracle/SID/admin/a01/1_27_700066042.dbf thread=1 sequence=27archive log filename=/restore1/oracle/SID/admin/a01/1_28_700066042.dbf thread=1 sequence=28archive log filename=/restore1/oracle/SID/admin/a01/1_29_700066042.dbf thread=1 sequence=29archive log filename=/restore1/oracle/SID/admin/a01/1_30_700066042.dbf thread=1 sequence=30archive log filename=/restore1/oracle/SID/admin/a01/1_31_700066042.dbf thread=1 sequence=31archive log filename=/restore1/oracle/SID/admin/a01/1_32_700066042.dbf thread=1 sequence=32archive log filename=/restore1/oracle/SID/admin/a01/1_33_700066042.dbf thread=1 sequence=33archive log filename=/restore1/oracle/SID/admin/a01/1_34_700066042.dbf thread=1 sequence=34archive log filename=/restore1/oracle/SID/admin/a01/1_35_700066042.dbf thread=1 sequence=35archive log filename=/restore1/oracle/SID/admin/a01/1_36_700066042.dbf thread=1 sequence=36archive log filename=/restore1/oracle/SID/admin/a01/1_37_700066042.dbf thread=1 sequence=37archive log filename=/restore1/oracle/SID/admin/a01/1_38_700066042.dbf thread=1 sequence=38archive log filename=/restore1/oracle/SID/admin/a01/1_39_700066042.dbf thread=1 sequence=39archive log filename=/restore1/oracle/SID/admin/a01/1_40_700066042.dbf thread=1 sequence=40archive log filename=/restore1/oracle/SID/admin/a01/1_41_700066042.dbf thread=1 sequence=41archive log filename=/restore1/oracle/SID/admin/a01/1_42_700066042.dbf thread=1 sequence=42archive log filename=/restore1/oracle/SID/admin/a01/1_43_700066042.dbf thread=1 sequence=43archive log filename=/restore1/oracle/SID/admin/a01/1_44_700066042.dbf thread=1 sequence=44archive log filename=/restore1/oracle/SID/admin/a01/1_45_700066042.dbf thread=1 sequence=45archive log filename=/restore1/oracle/SID/admin/a01/1_46_700066042.dbf thread=1 sequence=46archive log filename=/restore1/oracle/SID/admin/a01/1_47_700066042.dbf thread=1 sequence=47archive log filename=/restore1/oracle/SID/admin/a01/1_48_700066042.dbf thread=1 sequence=48archive log filename=/restore1/oracle/SID/admin/a01/1_49_700066042.dbf thread=1 sequence=49archive log filename=/restore1/oracle/SID/admin/a01/1_50_700066042.dbf thread=1 sequence=50archive log filename=/restore1/oracle/SID/admin/a01/1_51_700066042.dbf thread=1 sequence=51archive log filename=/restore1/oracle/SID/admin/a01/1_52_700066042.dbf thread=1 sequence=52archive log filename=/restore1/oracle/SID/admin/a01/1_53_700066042.dbf thread=1 sequence=53archive log filename=/restore1/oracle/SID/admin/a01/1_54_700066042.dbf thread=1 sequence=54archive log filename=/restore1/oracle/SID/admin/a01/1_55_700066042.dbf thread=1 sequence=55archive log filename=/restore1/oracle/SID/admin/a01/1_56_700066042.dbf thread=1 sequence=56archive log filename=/restore1/oracle/SID/admin/a01/1_57_700066042.dbf thread=1 sequence=57archive log filename=/restore1/oracle/SID/admin/a01/1_58_700066042.dbf thread=1 sequence=58archive log filename=/restore1/oracle/SID/admin/a01/1_59_700066042.dbf thread=1 sequence=59archive log filename=/restore1/oracle/SID/admin/a01/1_60_700066042.dbf thread=1 sequence=60archive log filename=/restore1/oracle/SID/admin/a01/1_61_700066042.dbf thread=1 sequence=61archive log filename=/restore1/oracle/SID/admin/a01/1_62_700066042.dbf thread=1 sequence=62archive log filename=/restore1/oracle/SID/admin/a01/1_63_700066042.dbf thread=1 sequence=63archive log filename=/restore1/oracle/SID/admin/a01/1_64_700066042.dbf thread=1 sequence=64archive log filename=/restore1/oracle/SID/admin/a01/1_65_700066042.dbf thread=1 sequence=65archive log filename=/restore1/oracle/SID/admin/a01/1_66_700066042.dbf thread=1 sequence=66archive log filename=/restore1/oracle/SID/admin/a01/1_67_700066042.dbf thread=1 sequence=67archive log filename=/restore1/oracle/SID/admin/a01/1_68_700066042.dbf thread=1 sequence=68archive log filename=/restore1/oracle/SID/admin/a01/1_69_700066042.dbf thread=1 sequence=69archive log filename=/restore1/oracle/SID/admin/a01/1_70_700066042.dbf thread=1 sequence=70archive log filename=/restore1/oracle/SID/admin/a01/1_71_700066042.dbf thread=1 sequence=71archive log filename=/restore1/oracle/SID/admin/a01/1_72_700066042.dbf thread=1 sequence=72archive log filename=/restore1/oracle/SID/admin/a01/1_73_700066042.dbf thread=1 sequence=73archive log filename=/restore1/oracle/SID/admin/a01/1_74_700066042.dbf thread=1 sequence=74archive log filename=/restore1/oracle/SID/admin/a01/1_75_700066042.dbf thread=1 sequence=75archive log filename=/restore1/oracle/SID/admin/a01/1_76_700066042.dbf thread=1 sequence=76archive log filename=/restore1/oracle/SID/admin/a01/1_77_700066042.dbf thread=1 sequence=77archive log filename=/restore1/oracle/SID/admin/a01/1_78_700066042.dbf thread=1 sequence=78archive log filename=/restore1/oracle/SID/admin/a01/1_79_700066042.dbf thread=1 sequence=79archive log filename=/restore1/oracle/SID/admin/a01/1_80_700066042.dbf thread=1 sequence=80archive log filename=/restore1/oracle/SID/admin/a01/1_81_700066042.dbf thread=1 sequence=81archive log filename=/restore1/oracle/SID/admin/a01/1_82_700066042.dbf thread=1 sequence=82archive log filename=/restore1/oracle/SID/admin/a01/1_83_700066042.dbf thread=1 sequence=83archive log filename=/restore1/oracle/SID/admin/a01/1_84_700066042.dbf thread=1 sequence=84archive log filename=/restore1/oracle/SID/admin/a01/1_85_700066042.dbf thread=1 sequence=85archive log filename=/restore1/oracle/SID/admin/a01/1_86_700066042.dbf thread=1 sequence=86archive log filename=/restore1/oracle/SID/admin/a01/1_87_700066042.dbf thread=1 sequence=87archive log filename=/restore1/oracle/SID/admin/a01/1_88_700066042.dbf thread=1 sequence=88archive log filename=/restore1/oracle/SID/admin/a01/1_89_700066042.dbf thread=1 sequence=89archive log filename=/restore1/oracle/SID/admin/a01/1_90_700066042.dbf thread=1 sequence=90archive log filename=/restore1/oracle/SID/admin/a01/1_91_700066042.dbf thread=1 sequence=91archive log filename=/restore1/oracle/SID/admin/a01/1_92_700066042.dbf thread=1 sequence=92archive log filename=/restore1/oracle/SID/admin/a01/1_93_700066042.dbf thread=1 sequence=93archive log filename=/restore1/oracle/SID/admin/a01/1_94_700066042.dbf thread=1 sequence=94archive log filename=/restore1/oracle/SID/admin/a01/1_95_700066042.dbf thread=1 sequence=95archive log filename=/restore1/oracle/SID/admin/a01/1_96_700066042.dbf thread=1 sequence=96archive log filename=/restore1/oracle/SID/admin/a01/1_97_700066042.dbf thread=1 sequence=97archive log filename=/restore1/oracle/SID/admin/a01/1_98_700066042.dbf thread=1 sequence=98archive log filename=/restore1/oracle/SID/admin/a01/1_99_700066042.dbf thread=1 sequence=99archive log filename=/restore1/oracle/SID/admin/a01/1_100_700066042.dbf thread=1 sequence=100archive log filename=/restore1/oracle/SID/admin/a01/1_101_700066042.dbf thread=1 sequence=101archive log filename=/restore1/oracle/SID/admin/a01/1_102_700066042.dbf thread=1 sequence=102archive log filename=/restore1/oracle/SID/admin/a01/1_103_700066042.dbf thread=1 sequence=103archive log filename=/restore1/oracle/SID/admin/a01/1_104_700066042.dbf thread=1 sequence=104archive log filename=/restore1/oracle/SID/admin/a01/1_105_700066042.dbf thread=1 sequence=105archive log filename=/restore1/oracle/SID/admin/a01/1_106_700066042.dbf thread=1 sequence=106archive log filename=/restore1/oracle/SID/admin/a01/1_107_700066042.dbf thread=1 sequence=107archive log filename=/restore1/oracle/SID/admin/a01/1_108_700066042.dbf thread=1 sequence=108archive log filename=/restore1/oracle/SID/admin/a01/1_109_700066042.dbf thread=1 sequence=109archive log filename=/restore1/oracle/SID/admin/a01/1_110_700066042.dbf thread=1 sequence=110archive log filename=/restore1/oracle/SID/admin/a01/1_111_700066042.dbf thread=1 sequence=111archive log filename=/restore1/oracle/SID/admin/a01/1_112_700066042.dbf thread=1 sequence=112archive log filename=/restore1/oracle/SID/admin/a01/1_113_700066042.dbf thread=1 sequence=113archive log filename=/restore1/oracle/SID/admin/a01/1_114_700066042.dbf thread=1 sequence=114archive log filename=/restore1/oracle/SID/admin/a01/1_115_700066042.dbf thread=1 sequence=115archive log filename=/restore1/oracle/SID/admin/a01/1_116_700066042.dbf thread=1 sequence=116archive log filename=/restore1/oracle/SID/admin/a01/1_117_700066042.dbf thread=1 sequence=117archive log filename=/restore1/oracle/SID/admin/a01/1_118_700066042.dbf thread=1 sequence=118archive log filename=/restore1/oracle/SID/admin/a01/1_119_700066042.dbf thread=1 sequence=119archive log filename=/restore1/oracle/SID/admin/a01/1_120_700066042.dbf thread=1 sequence=120archive log filename=/restore1/oracle/SID/admin/a01/1_121_700066042.dbf thread=1 sequence=121archive log filename=/restore1/oracle/SID/admin/a01/1_122_700066042.dbf thread=1 sequence=122archive log filename=/restore1/oracle/SID/admin/a01/1_123_700066042.dbf thread=1 sequence=123archive log filename=/restore1/oracle/SID/admin/a01/1_124_700066042.dbf thread=1 sequence=124archive log filename=/restore1/oracle/SID/admin/a01/1_125_700066042.dbf thread=1 sequence=125archive log filename=/restore1/oracle/SID/admin/a01/1_126_700066042.dbf thread=1 sequence=126archive log filename=/restore1/oracle/SID/admin/a01/1_127_700066042.dbf thread=1 sequence=127archive log filename=/restore1/oracle/SID/admin/a01/1_128_700066042.dbf thread=1 sequence=128archive log filename=/restore1/oracle/SID/admin/a01/1_129_700066042.dbf thread=1 sequence=129archive log filename=/restore1/oracle/SID/admin/a01/1_130_700066042.dbf thread=1 sequence=130archive log filename=/restore1/oracle/SID/admin/a01/1_131_700066042.dbf thread=1 sequence=131archive log filename=/restore1/oracle/SID/admin/a01/1_132_700066042.dbf thread=1 sequence=132archive log filename=/restore1/oracle/SID/admin/a01/1_133_700066042.dbf thread=1 sequence=133archive log filename=/restore1/oracle/SID/admin/a01/1_134_700066042.dbf thread=1 sequence=134archive log filename=/restore1/oracle/SID/admin/a01/1_135_700066042.dbf thread=1 sequence=135archive log filename=/restore1/oracle/SID/admin/a01/1_136_700066042.dbf thread=1 sequence=136archive log filename=/restore1/oracle/SID/admin/a01/1_137_700066042.dbf thread=1 sequence=137archive log filename=/restore1/oracle/SID/admin/a01/1_138_700066042.dbf thread=1 sequence=138archive log filename=/restore1/oracle/SID/admin/a01/1_139_700066042.dbf thread=1 sequence=139archive log filename=/restore1/oracle/SID/admin/a01/1_140_700066042.dbf thread=1 sequence=140archive log filename=/restore1/oracle/SID/admin/a01/1_141_700066042.dbf thread=1 sequence=141archive log filename=/restore1/oracle/SID/admin/a01/1_142_700066042.dbf thread=1 sequence=142archive log filename=/restore1/oracle/SID/admin/a01/1_143_700066042.dbf thread=1 sequence=143archive log filename=/restore1/oracle/SID/admin/a01/1_144_700066042.dbf thread=1 sequence=144archive log filename=/restore1/oracle/SID/admin/a01/1_145_700066042.dbf thread=1 sequence=145archive log filename=/restore1/oracle/SID/admin/a01/1_146_700066042.dbf thread=1 sequence=146archive log filename=/restore1/oracle/SID/admin/a01/1_147_700066042.dbf thread=1 sequence=147archive log filename=/restore1/oracle/SID/admin/a01/1_148_700066042.dbf thread=1 sequence=148archive log filename=/restore1/oracle/SID/admin/a01/1_149_700066042.dbf thread=1 sequence=149archive log filename=/restore1/oracle/SID/admin/a01/1_150_700066042.dbf thread=1 sequence=150archive log filename=/restore1/oracle/SID/admin/a01/1_151_700066042.dbf thread=1 sequence=151media recovery complete, elapsed time: 00:00:39Finished recover at 29-OCT-09
9. Once recovery is completed successfully, put datafile back to online.
RMAN> sql’alter database datafile 2 online’;
sql statement: alter database datafile 2 online
10. Open the database.
RMAN> sql ‘alter database open’;
sql statement: alter database open
SQL> select count(1) from dba_segments_new;
  COUNT(1)———-      4488
SQL> select tablespace_name,status,sum(bytes) from dba_undo_extents group by tablespace_name,status;
TABLESPACE_NAME                STATUS    SUM(BYTES)—————————— ——— ———-UNDOTBS1                       UNEXPIRED    1179648UNDOTBS1                       EXPIRED      5505024
SQL> commit;
Commit complete.


11. We can see the unexpired undo segments above but will lose all the un commited transactions. Below we can see only commited transactions.
SQL> select count(1) from dba_segments_new;
  COUNT(1)———-      4488