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

… then removed the system datafile.

Let’s connect using RMAN

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

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

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.

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.

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.


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.