In this phase of recovery, we are going to have a look on recover from a loss of the system critical UNDO tablespace on the original location. I hope this phase will be useful for you.
As we all know that UNDO tablespace stores undo segments, generally used to explicitly (ROLLBACK command) or implicitly (a failed transaction) rollback a transaction, to recreate a read-consistent image and for all kind of recovery purposes.
As well as the SYSTEM tablespace the database must be in the MOUNT state to recover UNDO tablespace.
Let’s begin simulating the loss of the UNDO tablespace: in my case the instance is running and…
[oracle@localhost orcl]$ ps -ef|grep smon oracle 9247 1 0 07:48 ? 00:00:01 ora_smon_orcl
… I simply remove its UNDO datafile.
[oracle@localhost orcl]$ rm /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
As you can see when I try to insert a row in one table Oracle throws an error stating it’s not able to open undotbs01.dbf (data)file.
SQL> insert into hr.employees (employee_id, last_name, email, hire_date, job_id) 2 values (99999, 'pippo', 'pippo@waltdisney.com', sysdate, 'AC_ACCOUNT'); insert into hr.employees (employee_id, last_name, email, hire_date, job_id) * ERROR at line 1: ORA-01116: error in opening database file 3 ORA-01110: data file 3: '/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3
The same information is written in the alert log as well as in a trace file
... Fri Jul 27 08:09:04 2012 Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_9533.trc: ORA-01116: error in opening database file 3 ORA-01110: data file 3: '/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3 Fri Jul 27 08:09:04 2012 Checker run found 1 new persistent data failures ...
If you try to recover your UNDO tablespace taking it offline you receive some errors: you cannot take offline that tablespace.
RMAN> sql 'alter tablespace UNDOTBS1 offline immediate'; sql statement: alter tablespace UNDOTBS1 offline immediate RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of sql command on default channel at 07/27/2012 08:36:19 RMAN-11003: failure during parse/execution of SQL statement: alter tablespace UNDOTBS1 offline immediate ORA-30042: Cannot offline the undo tablespace
Then it’s time to shutdown the instance, but it doesn’t work.
RMAN> shutdown immediate; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of shutdown command at 07/27/2012 08:37:01 ORA-01116: error in opening database file 3 ORA-01110: data file 3: '/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3
You can at this time kill your instance or issue a SHUTDOWN ABORT command.
RMAN> shutdown abort; Oracle instance shut down
Your recovery process needs to be started while the database is in MOUNT mode.
RMAN> startup mount; connected to target database (not started) Oracle instance started database mounted Total System Global Area 456146944 bytes Fixed Size 1344840 bytes Variable Size 390073016 bytes Database Buffers 58720256 bytes Redo Buffers 6008832 bytes
Once the database is mounted you can restore your UNDO tablespace
RMAN> restore tablespace UNDOTBS1; Starting restore at 27-07-2012 08:38:54 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=18 device type=DISK channel ORA_DISK_1: restoring datafile 00003 input datafile copy RECID=20 STAMP=789551613 file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_undotbs1_81023bm5_.dbf destination for restore of datafile 00003: /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf channel ORA_DISK_1: copied datafile copy of datafile 00003 output file name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf RECID=0 STAMP=0 Finished restore at 27-07-2012 08:38:58
… recover it …
RMAN> recover tablespace UNDOTBS1; Starting recover at 27-07-2012 08:39:14 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 27-07-2012 08:39:16
.. and after the recovery process finishes you can finally open your database.
RMAN> alter database open; database opened
For More Detail , You can join us follow:
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp