Friday, January 10, 2020

Recover loss of the SYSTEM tablespace on the original location

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 datafiles.
and removed system tablespace
Let's try to shut down the instance
I’m not able to login and a clear error message is showed on screen. Let’s use then RMAN
RMAN shows the same error message.
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.
Lets mount database using RMAN




You can see system tablespace size is '0'
Now we can issue restore command to for 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
Now OPEN the database


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

No comments: