How to recover from a loss of the system critical UNDO tablespace on the original location

Tweet

Here it is another scenario dealing with the loss of a system critical tablespace: today it's UNDO tablespace's turn.

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

That's all.