How to recover a table using RMAN backup

Posted on August 13, 2007. Filed under: Database Recovery |

This article is about recovering a particular table (which was dropped or truncated) using RMAN backup. I assume that logical backup is not planned as the size of the database is in Terabytes (TB) and RMAN is used for backing up the database. Here tablespace point in time recovery (TSPITR) does not come into picture because we want to recover a single table and not all the objects in the tablespace.

It is assumed that

 – The target database is on host A and the RMAN full backup was taken before the table TEST which is to be recovered was dropped.

 – The database is to be restored onto host B

 – The directory structure of host B is different to host A

 – The ORACLE_SID will not change for the restored database

 – The backups were carried out to disk

 – TEST table to be recovered is in the tablespace TEST_DATA

The following steps are required:

 – make the backup available to host B

 – make a copy of the init.ora available to host B

 – edit the init.ora to reflect directory structure changes

 – set up a password file for the duplicated database

 – mount the database

 – restore and rename the datafiles

 – recover and open the database

 -export and import the table to the target database

These steps are explained further below.

1.Backup the latest controlfile

sqlplus>alter database backup controlfile to ‘/oracle/control.ctl’;


Note: Backup the archived logs

Move all the archive logs to the Host B from the time backup was taken.


2. List Datafile Locations on Host A

The datafile numbers and location on host A are required. These datafile locations will change on host B

sqlplus> select file#, name from v$datafile;

  file#   name

  —–   ——————————

  1       /oracle/orcl/oradata/system01.dbf

  2       /oracle/ orcl/oradata/users..dbf

  3       /oracle/orcl/oradata/undo01.dbf

  4       /oracle/orcl/oradata/tools01.dbf

  5       /oracle/orcl/oradata/test01.dbf

  6       /oracle/orcl/oradata/test02.dbf

  7       /oracle/orcl/oradata/undo02.dbf

  8       /oracle/orcl/oradata/rcvcat.dbf

3. Make the Backups Available to Host B

During restore, RMAN will expect the backup sets to be located in the same directory as written to during the backup. For disk backups, this can be accomplished in many ways:

   – set up an NFS directory, mounted on both host A and host B

   – use of symbolic links on host B

4. init.ora on Host B

The “init.ora” needs to be made available on host B. Any location specific

parameters must be ammended. For example,

  – ifile

  – *_dump_dest

  – log_archive_dest*

  – control_files=(control file backup taken on Host A)

5. Setup PASSWORD File

In order to allow RMAN remote connections, a password file must be setup for the duplicated database. For example,

$orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle

6. Recover the Database

On Host B perform the following steps.

6.1 startup nomount the database

    sqlplus> startup nomount pfile=<location of init.ora>

6.2 mount the database

    sqlplus> alter database mount;

6.3 Rename and restore the datafiles, and perform database recovery


    RMAN can be used to change the location of the datafiles from the location  on host A to the new location on host B. Here rename the datafiles of SYSTEM,UNDOTBS1 and TEST_DATA tablespaces only.

Note: If you have two undo tablespaces in your database and you keep switching between these undo tablespaces it is necessary to restore both the undo tablespaces.

      RMAN> run {

     allocate channel c1 type disk;

     allocate channel c2 type disk;

     allocate channel c3 type disk;

     set newname for datafile 1 to ‘/oracle/datafiles/system01.dbf’;

     set newname for datafile 3 to ‘/oracle/datafiles/undo01.dbf’;

     set newname for datafile 5 to ‘/oracle/datafiles/test01.dbf’;

     set newname for datafile 6 to ‘/oracle/datafiles/test02.dbf’;

     set newname for datafile 7 to ‘/oracle/datafiles/undo02.dbf’;

     restore tablespace SYSTEM;

     restore tablespace UNDOTBS1;

     restore tablespace TEST_DATA;

     switch datafile all;


6.4 Recover and open the database

    Perform incomplete recovery and take the datafiles of the tablespaces other than SYSTEM,UNDOTBS1,TEST_DATA to offline. This makes our restore work easier. i.e. you don’t have to restore the whole database backup. When you issue offline drop command, controlfile assumes that it does not need these files for recovery(so need to restore!!).This is helpful when you have a database of say 1 TB and the tablespace in which the table to be recovered is present is of say 10 GB. By skipping the restoration of other tablespaces you save lot of time and space also.

sqlplus>alter database datafile 2 offline drop;

         alter database datafile 4 offline drop;

         alter database datafile 8 offline drop;


    sqlplus> recover database using backup controlfile until cancel; (or until time)


    Forward the database applying archived redo log files to the point just before the table was dropped and stop the recovery process by typing cancel at the prompt (assuming that you have required archived redo log files in the log_archive_dest directory)



6.5 Rename the logfiles prior to opening the database

    sqlplus> alter database rename file ‘<host A location>’ to ‘<host B location>’;

    sqlplus> alter database open resetlogs;


   Now you can query the table TEST to find out the data.

Once you are confirmed that the table TEST is recovered, export the table and import to the target database.


OraclePitStop (Contributed by Yadu)


Make a Comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

11 Responses to “How to recover a table using RMAN backup”

RSS Feed for OraclePitStop – Lets share some knowledge Comments RSS Feed

not clear must be more provided more clarity

It would be great if you let me know where I went wrong in making the points clear.


we can enable flashback Tech If its enabled



Good explaination but i have 1 tablespace of 1 TB and if i drop table from this tablespace than your action plan wont be feasible.


Its Good explanation,Infact we are also following the same procedure, But i think this is big procedure, and may take more than 10hr if database size is in TB, and is also difficult to find free space at OS level on another host.

Is there some another procedure, that we can restore only specific table ?


The trick here is to restore only the SYSTEM,UNDO and THE TABLESPACE containing the table and recover.You dont have to restore whole database on the other server.The other server would require space to accomadate datafiles of the SYSTEM,UNDO and THE TABLESPACE(conatining the table).No question of restoring of TB of data.


If you are tablespace is of 1 TB and the tables are small then it is better to backup only the important tables via export.

you skiped the step to restore the control file after starting the database in nomout . If you dont restore the control file you cannot mount the database.

Thanks Yadu to explain in detail, I appreciate
your effort.

Tariq H.

excellant. Very useful.

Where's The Comment Form?

Liked it here?
Why not try sites on the blogroll...

%d bloggers like this: