This post is about the steps which are used to recover a never backed up tablespace after losing its datafile and even the current controlfile but before the autobackup feature completes its job.

All the required information is still available and written in your archived or online redo log. So the solution of the first question is as simple as write the following lines code:

A more difficult question could be instead:
what does it happen when you lose your new tablespace with all your new transactions already committed five minutes before your full RMAN backup script is executed (same situation described above) AND, at the same time, your control file where information of your datafile was recorded?
And no, the answer is not: “Today is a bad day”.
Would be RMAN able to restore your tablespace using a controlfile coming from a backup older than your “CREATE TABLESPACE” command?
Does your archived or online redo log have instead all the necessary information to restore and recover your tablespace?
The restore and recovery process depends on the information contained in the available controlfile: there could be two or three different scenarios and I’m going to show them in this post and in the next one.

This post will take into consideration a Recovery Manager setting using the CONTROLFILE AUTOBACKUP feature, a tablespace created after the only available full backup (so this backup doesn’t have information on this tablespace), some rows committed on the new tablespace, a crash happened few seconds before the autobackup feature completes its job to create a backup of the controlfile because of a structural change of the database.
Starting with Oracle 11gR2, RMAN creates the autobackup controlfile encompassing all of the structural changes that have occurred in the database within a few minutes.
“Within few minutes…” in my virtualized environment and several production installations means from 5 to even 10 minutes must pass before the autobackup controlfile is created.

So let’s start with an example remembering that in this crash scenario you are going to lose the “never backed up” tablespace and the current controlfile at the same time.

Our instance is up and running.

Connect with RMAN client to see my persistent settings.

Connect with sqlplus client and (try to) drop the tablespace XXX including its contents and datafiles.

Now delete all backups and copies. I want to be sure I don’t have a valid backup of XXX tablespace.

Now it’s time to take a full database backup including the archived redo log. This backup doesn’t contain a tablespace named XXX.

My only current backup is formed by the following backup sets:

I’m going to create the new XXX tablespace and a new table (IMPORTANT_XXX_TRANSACTION) containing some commited rows.

As anticipated at the beginning of this post in Oracle Database 11gR2 the autobackup feature starts within a delay trying to encompass all of the structural changes made to the database rather than creating a new backup of the controlfile on each structural change. This delay could be critical for you and for your transactions and simply implies different ways to restore and recover them.
Let’s simulate a loss of our current controlfile and of our “never backed up” new tablespace (XXX), before our instance remembers to create the “delayed” autobackup controlfile.

The instance is still up and running

The rows are still available

The alert log discovered a current controlfile is missing.

The instance is not working anymore.

I force the instance to abort.

Inside the “lost” controlfile some references to the new tablespace are present.

Let’s try to recover our database. Connecting the RMAN client, the output log shows a not started instance.

We have lost our current controlfile and want to restore it using the autobackup feature. RMAN is able to search from the available backup set and find the right one.

The restored controlfile doesn’t have any references about the XXX tablespace: that autobackup of the control file was created indeed before the creation of the lost tablespace.

Connect again with RMAN client to the “not mounted” instance.

A controlfile is now available to mount the instance.

The report schema command in “List of Permanent Datafiles” section does not have any information about XXX tablespace and its datafile.

The “RMAN-20202: Tablespace not found in the recovery catalog” and “RMAN-06019: could not translate tablespace name “XXX”” errors are thrown, because the controlfile used has no information about that tablespace as you can see in the following output:

To recover the missing and “never backed up” tablespace you must restore and recover the entire database.

In the above log of the recover database command you can see the creation of datafile number 8 …

creating datafile file number=8 name=/home/oracle/app/oracle/oradata/orcl/XXX01.dbf

… and in the alert log you can find also the following lines:

Since I restored a controlfile from a backup, it is required to open the database using the resetlogs option:

The new current controlfile has records about the XXX tablespace and its datafile.

All previous data inserted and committed into IMPORTANT_XXX_TRANSACTION table are available again.

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


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.