Recover Datafiles which are not backed Up

You recently added a data file to a
tablespace and had a failure before the data file was backed up. For example, a
backup is taken:
RMAN> backup database;
Sometime after the backup has
finished, a new tablespace is added:
CREATE TABLESPACE test_tbs DATAFILE ‘/u01/datafile/spd/test_tbs01.dbf’
SIZE 100m;
Now we have the situation where there
currently is no backup of the newly created data file. Suppose there is a media
failure that damages the data file before a fresh backup is taken.

Conceptual Details
For this solution to work, you need
to have a good baseline backup of your database and any subsequently generated redo
up to the point where the data file was created. If you have your current
control file, you can restore and recover at the data file, tablespace, or
database level. If you’re using a backup control file that has no information
about the data file, you must restore and recover at the database level.
Starting with Oracle Database 10g,
there is enough information in the redo stream for RMAN to automatically re-create
a data file that was never backed up. It doesn’t matter whether the control
file has a record of the data file.
Prior to Oracle Database10g,
manual intervention from the DBA was required to recover a data file that had
not been backed up yet. If Oracle identified that a data file was missing that
had not been backed up, the recovery process would halt, and you would have to
identify the missing data file and re-create it:
SQL> alter database create
datafile ‘/u01/datafile/spd/test_tbs01.dbf’
as ‘/u01/datafile/spd/test_tbs01.dbf’size
10485760 reuse;
After re-creating the missing data
file, you had to manually restart the recovery session. If you are using an old
version of the Oracle database, see MOS note 1060605.6 for details on how to
re-create a data file in this scenario.
In Oracle Database 10g and
newer, this is no longer the case. RMAN automatically detects that there isn’t
a backup of a data file being restored and re-creates the data file from
information retrieved from the control file and/or redo information as part of
the restore and recovery operations.
a Current Control File

In this example, we use the current
control file and are recovering the test_tbs01.dbf datafile in the newly added
tools tablespace.
$ rman target /
RMAN> startup mount;
RMAN> restore tablespace test_tbs;
You should see a message like the following in the output as
RMAN re-creates the data file:
creating datafile file number=10
Now issue the recover command and open the database:
RMAN> recover tablespace test_tbs;
RMAN> alter database open;
a Backup Control File

This scenario is applicable anytime
you use a backup control file to restore and recover a data file that has not
yet been backed up. First, we restore a control file from a backup taken prior
to when the data file was created:
$ rman target /
RMAN> startup nomount;
RMAN> restore controlfile from ‘/backup/bkp_ctl’;
RMAN> alter database mount;
Now you can verify the control file has no record of the
tablespace that was added after the backup was taken:
RMAN> report schema;
When the control file has no record of the data file, RMAN
will throw an error if you attempt to recover at the tablespace or data file
level. In this situation, you must use the restore database and recover
database commands as follows:
RMAN> restore database;
RMAN> recover database;
Next, you should see quite a bit of RMAN output. Near the
end of the output you should see a line similar to this indicating that the
data file has been re-created:
creating datafile file number=10
Since you restored using a backup control file, you are
required to open the database with the resetlogs command:

RMAN> alter database open resetlogs;

  • October 21, 2017 | 15 views
  • Comments