Recover from a loss of the system critical UNDO tablespace on the original location

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

Source

For More Detail , You can join us follow:

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp