Great Tips on Table Recovery with RMAN Backup

 Author: Yadu

Page: 1 2

Most database administrators face the scenario when they have to recover some specific table that dropped/truncated during a RMAN backup. Most of the times the goal is to recover a single table and not all objects in the tablespace.

Suppose our target database is on host A and we want to restore the database on host B. Both of them have different directory structures. The table to be recovered is named MYTABLE and is located on MYTABLESPACE tablespace. Also we do not want ORACLE_SID to be changed for restored database. The steps required to recover the MYTABLE table are discussed as follows.

Backup controlfile and archived logs:

First of all we need to backup the controlfile and move all archived logs to host B. The archive logs will be from the time you performed the backup.

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

Datafile Locations:

The file number and location of datafile will be changed on host B. Therefore you need to keep track of the file numbers and names on host A. You can retrieve this information by running below command.

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/mytable01.dbf
  6       /oracle/orcl/oradata/mytable02.dbf
  7       /oracle/orcl/oradata/undo02.dbf
  8       /oracle/orcl/oradata/rcvcat.dbf

Locating backups:

The backup sets must be located in the same directory where it was written during backup. For disk backups you either set up an NFS directory mounted on both host A and B or you can use symbolic links on host B.

Availability of init.ora on Host B:

Make sure that init.ora is available on host B including any location specific parameters such as ifile, log_archive_dest*, *_dump_dest or control file backup taken on Host A.

Allowing RMAN Remote connections:

You must set up a password file for duplicated database in order to allow RMAN remote connections.

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

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Monitoring Oracle Parallel Queries!!
   Database Tuning with Automatic Segment Space Management!!
   Go beyond running exp and imp - Follow Database Migration Tips!!
   Amazing Tips to Fix Broken Oracle ASM Instance!!