We are going to have a look at recovery in Oracle database.  it’s time to cause a fault in the database so we need to recover it. The scenario is the following: what does it happen and how can I recover a database in NOARCHIVELOG mode when a disk failure occurs and I lost my current control file, all my datafiles and only multiplexed control files are available?

Let’s start up the instance and create a table.

My datafiles are located here.

Now let’s see what happens when I move all datafiles, redo log and control files to another directory simulating a disks failure. I assume for this scenario to be able to get back with a consistent copy of at least one multiplexed control files. My control files are all in the same path on my test machine.

The instance is still running

From the alert log the instance begins to trace some errors

Let’s try to shutdown our instance

Mmmmm… it doesn’t work… Of course the instance is not able to find and write any SCN on the current control file. So issue the following command

Let’s see what happens when an instance is not able to find a control file. The instance is not able to be open in MOUNT mode

From the alert log we can see the instance is not able to read the control file

Imagine you have lost all your database files except one control file that was wisely multiplexed on another disk (in my case it’s in the flash recovery area path). We can use it to mount the database, copying it to the original location.

Now the instance is able to open in mount mode using the CURRENT control file (compared with a next scenario where I will use the control file from backup).

Now it’s time to restore the datafiles from the backup using RMAN

On the alert log you can see all the steps

And indeed on the file system I can see all my datafiles

Now it’s time to open the database using the resetlogs option, but…

The following error happens because on the restored datafiles is written an SCN that was valid at the time the backup was taken and equal to the control file of that moment. The control file we are using is ahead compared with the SCN written on the datafiles… You have to perform (“simulate”) an incomplete recovery, typing CANCEL when requested

On alert log you an read

Still any redo log files are available

untill the following command completes

On the alert log your database is waiting for the redo log files… Wait some minutes and then you have successfully recovered your database.


About The Author

Leave a Reply

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