Performing RMAN TSPITR Using Your Own Auxiliary Instance

Preparing Your Own Auxiliary Instance for RMAN TSPITR

Creating an Oracle instance suitable for use as an auxiliary instance requires that you carry out all of the following steps:

• Step 1: Create an Oracle Password File for the Auxiliary Instance

• Step 2: Create an Initialization Parameter File for the Auxiliary Instance

• Step 3: Check Oracle Net Connectivity to the Auxiliary Instance

DB_NAME=trgt
DB_UNIQUE_NAME=_trgt
CONTROL_FILES=/tmp/control01.ctl
DB_FILE_NAME_CONVERT=('/oracle/oradata/trgt/','/tmp/')
LOG_FILE_NAME_CONVERT=('/oracle/oradata/trgt/redo','/tmp/redo')
REMOTE_LOGIN_PASSWORDFILE=exclusive
COMPATIBLE =10.1.0
DB_BLOCK_SIZE=8192

Preparing RMAN Commands for TSPITR with Your Own Auxiliary Instance

If you are running your own auxiliary instance, then you may find that the sequence of commands required for TSPITR is quite long, if you allocate a complex channel configuration for restoring from backup, or if you are not using DB_FILE_NAME_CONVERT to control file naming.

You may wish to store the sequence of commands for TSPITR in a command file, a text file under the host operating system. This command file can be read into RMAN using the @ command (or the CMDFILE command line argument when starting RMAN) to execute the series of commands in the command file.

See "Using RMAN with Command Files" for more details.

Executing TSPITR with Your Own Auxiliary Instance

With the preparations complete and your TSPITR commands completely planned, you are now ready to carry out your TSPITR. The following steps are required:

• Step 1: Start the Auxiliary Instance in NOMOUNT Mode

• Step 2: Connect the RMAN Client to Target and Auxiliary Instances

• Step 3: Execute the RECOVER TABLESPACE Command

SQL> CONNECT SYS/oracle@aux AS SYSDBA
SQL> STARTUP NOMOUNT PFILE='/tmp/initAux.ora'

% rman target / auxiliary sysuser/syspwd@auxiliary_service_name

RMAN> RECOVER TABLESPACE ts1, ts2... UNTIL TIME 'time'

RUN {
   ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
   ALLOCATE CHANNEL c2 DEVICE TYPE SBT;
  # and so on...
   RECOVER TABLESPACE ts1, ts2 UNTIL TIME 'time';
}

RMAN> @/tmp/tspitr.rman ;

Executing TSPITR With Your Own Auxiliary Instance: Scenario

The following example shows the execution of a RECOVER TABLESPACE... UNTIL operation using the following features of RMAN TSPITR:

• Managing your own auxiliary instance

• Configuring channels for restore of backups from disk and sbt

• Using recoverable image copies for some auxiliary set datafiles using SET NEWNAME

• Specifying new names for recovery set datafiles using SET NEWNAME

The process used is as follows:

1. Prepare the auxiliary instance as described in "Preparing Your Own Auxiliary Instance for RMAN TSPITR". Specify "tspitr" as the password for the auxiliary instance in the password file, and set up the auxiliary instance parameter file /bigtmp/init_tspitr_prod.ora with the following settings:

   db_name=PROD
   db_unique_name=tspitr_PROD
   control_files=/bigtmp/tspitr_cntrl.f'
   db_file_name_convert=('?/oradata/prod', '/bigtmp')
   log_file_name_convert=('?/oradata/prod', '/bigtmp')
   compatible=10.1.0
   block_size=8192
   remote_login_password=exclusive
   
   

2. Create service name pitprod for the auxiliary instance, and check for connectivity.

3. Start the auxiliary instance in NOMOUNT state, as shown:

   $ sqlplus
   SQL> connect sys/tspitr@pit_prod as sysdba
           SQL> startup nomount pfile=/bigtmp/init_tspitr_prod.ora
   
   

4. Start up RMAN, connecting to the auxiliary instance:

   % rman target / auxiliary sys/tspitr@pit_prod 
   
   

5. Enter the following commands, in a RUN block, to set up and execute the TSPITR:

   run {
   # Specify NEWNAMES for recovery set datafiles
       SET NEWNAME FOR DATAFILE '?/oradata/prod/clients01.f' 
                                     TO '?/oradata/prod/clients01_rec.f';
               SET NEWNAME FOR DATAFILE '?/oradata/prod/clients02.f'
                                     TO '?/oradata/prod/clients02_rec.f';
               SET NEWNAME FOR DATAFILE '?/oradata/prod/clients03.f'
                                     TO '?/oradata/prod/clients03_rec.f';
               SET NEWNAME FOR DATAFILE '?/oradata/prod/clients04.f'
                                     TO '?/oradata/prod/clients04_rec.f';
   
   # Specified newnames for some of the auxiliary set 
   # datafiles that have a valid image copy to avoid restores:
               SET NEWNAME FOR DATAFILE '?/oradata/prod/system01.f'
                                     TO '/backups/prod/system01_monday_noon.f';
               SET NEWNAME FOR DATAFILE '?/oradata/prod/system02.f'
                                     TO '/backups/prod/system02_monday_noon.f';
               SET NEWNAME FOR DATAFILE '?/oradata/prod/undo01.f'
                                     TO '/backups/prod/undo01_monday_noon.f';
   
   # Specified the disk and SBT channels to use
               allocate auxiliary channel c1 device type disk;
               allocate auxiliary channel c2 device type disk;
               allocate auxiliary channel t1 device type sbt;
               allocate auxiliary channel t2 device type sbt;
   
   # Recovered the clients tablespace to 24 hours ago:
       RECOVER TABLESPACE clients UNTIL TIME 'sysdate-1';
      }
   
   

Consider storing this command sequence in a command file and executing the commnand file, to avoid errors.

If the TSPITR operation is successful, then the results are:

• The recovery set datafiles are registered in the target database control file under the names specified with SET NEWNAME, with their contents as of the time specified time for the TSPITR.

• The auxiliary files are removed by RMAN, including the control files, online logs and auxiliary set datafiles of the auxiliary instance

• The auxiliary instance is shut down

If the TSPITR operation fails, the auxiliary files are left on disk for troubleshooting purposes. If RMAN created the auxiliary instance, it is shut down; otherwise it is left in whatever state it was in when the TSPITR operation failed.