Recovery is an important process for any kind of organization.   if the database can not recover properly   That is the worst condition of any DBA‘s professional because it causes unlivable loss of an organization.  We have many kinds of solutions to restore the database according to the loss of database. We are going to have look on the recovery scenario where we lose our system tablespace.

In particular this example will restore the lost tablespace to its original location.
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 as soon as the database open.

Let’s begin simulating the loss of SYSTEM tablespace.
In my case the instance was not running as you can see when I deleted the file.

Here are my sys* datafiles

I remove just the datafile of the SYSTEM tablespace.

Let’s try to start up the instance.

I’m not able to login and a clear error message is showed on screen. Let’s use then RMAN

Of course even RMAN shows you the same error.
The instance tried to start, shared memory were successfully attached by the Oracle processes, but the instance doesn’t work as expected: the SYSTEM tablespace is missing for RMAN too… I need to kill the instance.

Let’s use RMAN again…

so we can start up the database in MOUNT mode

Now we can issue the restore command for the SYSTEM tablespace

The previous restore command try to restore the datafile of SYSTEM tablespace to its original location. Then it’s time to issue the recover command …

… and finally open the database.

Now the database is available again to all the users and the SYSTEM tablespace is fully recovered.

Thank you for giving your valuable time to read the above information.

Source

For More Detail , You can join us follow:

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

Leave a Reply