In this post, we are going to learn steps which are use recover a never backed up tablespace after losing its datafile and even the current controlfile after the autobackup feature completes its job. This crash involves the lost of the “never backed up” tablespace and of the current controlfile.

Let’s start with an example. Our instance is up and running.

Connect with RMAN client to see persistent settings.

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

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

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

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

I’m going to create the new YYY tablespace and a new table (IMPORTANT_YYY_TRANSACTION) containing some committed rows.

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. Because I don’t want to wait 5 minutes before autobackup controlfile feature starts and completes its job, I force the database to get a controlfile copy using RMAN. The important concept in this scenario is that I have a valid controlfile copy taken after the creation of a new and never backed up tablespace.

Let’s simulate a loss of our current controlfile and of our “never backed up” new tablespace (YYY).

The instance is still up and running

I force the instance to abort

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

Let’s start the instance in nomount mode.

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, in my case it is able to use “o1_mf_s_800434207_8c9ns04h_.bkp” backup set

The restored controlfile has references about YYY tablespace: that autobackup of the control file was created indeed after 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 has information about YYY tablespace and its datafile, even if it is not able to get the right size.

It’s now possible to restore YYY tablespace

On my local directory “tbs01.dbf” datafile is created.

At this step you cannot directly recover your tablespace as the “RMAN-06067: RECOVER DATABASE required with a backup or created control file” error suggests.

You have to recover the entire database

…and finally open it with the resetlogs option

All previous data inserted and committed into IMPORTANT_YYY_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.