This post is about the steps used for recover as from a loss of the SYSTEM tablespace on a different location. As DBA we all are aware of system tablespace that it always contains the data dictionary tables for the entire database. In particular, this example will restore the lost tablespace to another location, just as we 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 until the recovery 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 we shut it down

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, we 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

Source

For More Detail , You can join us follow:

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.