How to recover from a loss of the SYSTEM tablespace on different location

Tweet

This is another post on a scenario describing how to proceed when you lose the SYSTEM tablespace: as already said this tablespace always contains the data dictionary tables for the entire database.
In particular this example will restore the lost tablespace to another location, just as you have to restore it because a disk controller is no more working and you have to recreate it (recovering) to a different location.

Next it's a short summary about the loss of SYSTEM tablespace, copied from a previous post.
When you lose the SYSTEM tablespace the instance could crash or hang: anyway the instance will alert you as soon as possible.
It's not rare the case when you cannot even shutdown the instance and proceed with a kill or a shutdown abort command.
If you have a good backup you can of course restore it, but the database could not be open untill the recover process finishes.
No problem for your committed transactions because your production database is always running in ARCHIVELOG mode and they will be available again as soon as the database opens.
Unlike recovery of non–system tablespaces that can be recovered with the database in the OPEN state, the database must be in the MOUNT state to recover either the SYSTEM or UNDO tablespace.

Let's begin simulating the loss of the SYSTEM tablespace. In my case the instance was running, so I shutted it down and...

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

... then removed the system datafile.

[oracle@localhost ~]$ rm /home/oracle/app/oracle/oradata/orcl/system01.dbf

Let's connect using RMAN

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Fri Jul 27 07:36:55 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

The instance was not started and I started it in MOUNT mode to begin the restore/recover process.

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area     456146944 bytes

Fixed Size                     1344840 bytes
Variable Size                385878712 bytes
Database Buffers              62914560 bytes
Redo Buffers                   6008832 bytes

Using the report schema command, you can see RMAN is not able to correctly know the size of SYSTEM datafile.

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    0        SYSTEM               *** /home/oracle/app/oracle/oradata/orcl/system01.dbf
2    1105     SYSAUX               *** /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3    40       UNDOTBS1             *** /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4    225      USERS                *** /home/oracle/app/oracle/oradata/orcl/users01.dbf
5    82       EXAMPLE              *** /home/oracle/app/oracle/oradata/orcl/example01.dbf
6    7        APEX_1930613455248703 *** /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767 /home/oracle/app/oracle/oradata/orcl/temp01.dbf

Now it's time to restore the datafile to a different location (/home/oracle/app/oracle/oradata/orcl/non_default_location/) compared with the original one (looking at the above report schema output it was: /home/oracle/app/oracle/oradata/orcl/).
To switch a datafile to another location you have to use SET NEWNAME command and include it in a run {...} block.
Moreover it's important to include also before issuing the recover command the SWITCH DATAFILE ALL command.
What does it mean and why we have to execute that command ? From Oracle documentation it "specifies that all data files for which a SET NEWNAME FOR DATAFILE command has been issued in this job are switched to their new name": control file will be so updated with the new location of SYSTEM datafile.

RMAN> run {                
2> set newname for datafile 1 to '/home/oracle/app/oracle/oradata/orcl/non_default_location/system01.dbf';
3> restore tablespace system;
4> switch datafile all;
5> recover tablespace system;
6> alter database open;
7> }

executing command: SET NEWNAME

Starting restore at 27-07-2012 07:40:21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK

channel ORA_DISK_1: restoring datafile 00001
input datafile copy RECID=17 STAMP=789551585 file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_system_81020mvz_.dbf
destination for restore of datafile 00001: /home/oracle/app/oracle/oradata/orcl/non_default_location/system01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00001
output file name=/home/oracle/app/oracle/oradata/orcl/non_default_location/system01.dbf RECID=23 STAMP=789723694
Finished restore at 27-07-2012 07:41:37

datafile 1 switched to datafile copy
input datafile copy RECID=24 STAMP=789723698 file name=/home/oracle/app/oracle/oradata/orcl/non_default_location/system01.dbf

Starting recover at 27-07-2012 07:41:39
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:04

Finished recover at 27-07-2012 07:41:48

database opened

After the end of recovery process, you can report the schema info again. The size of SYSTEM tablespace is again well known and the report schema command shows also a new location of the datafile number 1.

RMAN> report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    831      SYSTEM               *** /home/oracle/app/oracle/oradata/orcl/non_default_location/system01.dbf
2    1105     SYSAUX               *** /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3    40       UNDOTBS1             *** /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4    225      USERS                *** /home/oracle/app/oracle/oradata/orcl/users01.dbf
5    82       EXAMPLE              *** /home/oracle/app/oracle/oradata/orcl/example01.dbf
6    7        APEX_1930613455248703 *** /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767 /home/oracle/app/oracle/oradata/orcl/temp01.dbf

Looking into the /home/oracle/app/oracle/oradata/orcl/non_default_location directory we can find a new file. It's the datafile of the SYSTEM tablespace restored, recovered and finally moved to this different location.

[oracle@localhost non_default_location]$ ll -h
total 832M
-rw-rw---- 1 oracle oracle 832M Jul 27 07:44 system01.dbf

That's all.