Being an Oracle DBA we are aware that there are many types of backups are available for Oracle database maintenance in the same way we have multiple ways to restore our database.

One question raises here that why do we need have a different kind of ways to backup and recovery in Oracle database?

Answer of this question is that we can find a different kind of business with different requirements of business operation. On daily basis, if we have a full backup which takes hours which is not useful for an organization .

In normal life we do not have the same kind of problems and solutions, in the same manner, we can not apply only one way of backup and recovery for a different kind of conditions occurs in Oracle database.

We have multiple conditions with business requirements.Today we are going to have a look on recovery where we well know about  How to recover from a loss of a read-only tablespace.

We want to test how to restore and recover database from a loss of a read-only tablespace. We can anticipate there is a new feature introduced since Oracle Database 11gR1 that inverted the logic used until 10gR2. Let’s start with the examples. I’ve created a read-only tablespace named READ_ONLY using the following command:

The “report schema” command shows READ_ONLY tablespace information

The following are my current backup pieces:

I want to list all the datafiles included in one of my backup, so you can verify I haven’t any backup of my read-only tablespace.

It’s confirmed even when you issue the “report need backup” command, showing which datafiles, according to your rman backup policy configuration, need to be backed up. The command says datafile read_only01.dbf belonging to READ_ONLY tablespace must still be backed up.

Now let’s simulate a loss of that datafile

If i try to start the database some errors are shown:

Connect with RMAN and start the instance in MOUNT mode

Issue a simply “restore tablespace” command. RMAN will create again the tablespace looking in the redo log.

If we try to open the database it says, of course, one datafile needs to be recovered.

Issue the recover command so you can be able to open your database.

Now have a look at the second recovery scenario. What it really makes the difference is when we have to recover a read-only tablespace after a restore of the entire database. Until a certain release Oracle didn’t restore the read-only tablespace: you had to expressly issue the restore command.

Let’s simulate a lost of all datafiles.

Start again RMAN and open your instance in MOUNT mode.

Now it’s time to restore and recover the entire database

RMAN is able to gather information about READ_ONLY tablespace

Connecting to the instance I can verify my tablespace is there and available.

As we can see there’s no more any difference.

Until 11gR1 by default, the restore command skipped datafiles associated with read-only tablespaces. If you needed read-only tablespaces restored, then you had to use the “check readonly” command or restore each read-only tablespace individually.


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.