Skip Headers
Oracle® Database Backup and Recovery Advanced User's Guide
10g Release 2 (10.2)

Part Number B14191-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

Performing RMAN TSPITR Using Your Own Auxiliary Instance

Oracle recommends that you allow RMAN to manage the creation and destruction of the auxiliary instance used during RMAN TSPITR. However, creating and using your own auxiliary instance is also supported. One reason you might want to do this is to exercise control of channels used in TSPITR. RMAN's automatic auxiliary instance uses the configured channels of the target database as the basis for the channels to configure on the auxiliary instance and use during restore. If you need different channel settings, and you do not want to use CONFIGURE to change the settings on the target database, you can operate 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

For instructions on how to create and maintain Oracle password files, refer to the Oracle Database Administrator's Guide.

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

Create a client-side initialization parameter file for the auxiliary instance on the machine where you will be running SQL*Plus to control the auxiliary instance. For this example, we will assume your parameter file is placed at /tmp/initAux.ora. Set the parameters described in the following table, making sure that paths in parameters like DB_FILE_NAME_CONVERT, LOG_FILE_NAME_CONVERT and CONTROL_FILES are all server-side paths, not client-side.

Table 8-2 Initialization Parameters in the Auxiliary Instance

Parameter Mandatory? Value
DB_NAME YES The same name as the target database.
DB_UNIQUE_NAME YES A value different from any database in the same Oracle home. For simplicity, specify _dbname. For example, if the target database name is trgt, then specify _trgt.
LOG_FILE_NAME_CONVERT YES Patterns to generate filenames for the online redo logs of the auxiliary database based on the online redo log names of the target database. Query V$LOGFILE.MEMBER, to obtain target instance online log names, and ensure that the conversion pattern matches the format of the filename displayed in the view.

This parameter is the only way to name the online redo logs for the auxiliary instance. Without it, TSPITR will fail when trying to open the auxiliary instance because the online logs cannot be created.

Note: Some platforms do not support ending patterns in a forward or backward slash (\ or /).

REMOTE_LOGIN_PASSWORDFILE YES Set to EXCLUSIVE when connecting to the auxiliary instance by means of a password file. Otherwise, set to NONE.
COMPATIBLE YES The same value as the parameter in the target database.
DB_BLOCK_SIZE YES If this initialization parameter is set in the target database, then it must be set to the same value in the auxiliary instance.
DB_FILE_NAME_CONVERT NO Patterns to convert filenames for the datafiles of the auxiliary database. You can use this parameter to generate filenames for those files that you did not name with SET NEWNAME or CONFIGURE AUXNAME. Obtain the datafile filenames by querying V$DATAFILE.NAME, and ensure that the conversion pattern matches the format of the filename displayed in the view. You can also specify this parameter on the RECOVER command itself.

Note: Some platforms do not support ending patterns in a forward or backward slash (\ or /).

See Also: "Using DB_FILE_NAME_CONVERT to Name Auxiliary Set Datafiles"

CONTROL_FILES NO Filenames that do not conflict with the control file names of the target instance (or any other existing file).

Set other parameters as needed, including the parameters that allow you to connect as SYSDBA through Oracle Net.

Following are examples of the initialization parameter settings for 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

Note:

After setting these initialization parameters, ensure that you do not overwrite the initialization settings for the production files at the target database.


See Also:

Oracle Database Net Services Administrator's Guide for more information about Oracle Net

Step 3: Check Oracle Net Connectivity to the Auxiliary Instance

The auxiliary instance must have a valid net service name. Before proceeding, use SQL*Plus to ensure that you can establish a connection to the auxiliary instance.

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.

Planning Channels for TSPITR with Your Own Auxiliary Instance

When you run your own auxiliary instance, the default behavior is to use the automatic channel configuration of the target instance. However, if you decide to allocate your own channel configuration, you can do so by including the ALLOCATE AUXILIARY CHANNEL commands in a RUN block along with the RECOVER TABLESPACE command for TSPITR. Plan out these commands, if necessary, and add them to the sequence of commands you will run to perform your TSPITR.

See the example in "Executing TSPITR With Your Own Auxiliary Instance: Scenario" for details of how to include channel allocation in your TSPITR script.

Planning Datafile Names with Your Own Auxiliary Instance: SET NEWNAME

You may wish to use SET NEWNAME commands, either to refer to existing image copies of auxiliary set files to improve TSPITR performance, or to assign new names to the recovery set files for after TSPITR. Plan out these commands, if necessary, and add them to the sequence of commands you will run to perform your TSPITR.

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

Before beginning RMAN TSPITR, use SQL*Plus to connect to the auxiliary instance and start it in NOMOUNT mode, specifying a parameter file if necessary. For example:

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

Remember that the path for the PFILE will be a client-side path, on the machine from which you run SQL*Plus, not a server-side path.

Because the auxiliary instance does not yet have a control file, you can only start the instance in NOMOUNT mode. Do not create a control file or try to mount or open the auxiliary instance for TSPITR.

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

Start RMAN connecting to the target and the manually created auxiliary instance:

% rman target / auxiliary sysuser/syspwd@auxiliary_service_name

Step 3: Execute the RECOVER TABLESPACE Command

Now you are ready to run your TSPITR commands. In the simplest case, just execute the RECOVER TABLESPACE... UNTIL command at the RMAN prompt:

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

If you want to use ALLOCATE CHANNEL or SET NEWNAME then create a RUN block which includes those commands before the RECOVER TABLESPACE command.

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

Using a Command File for TSPITR

Entering a lengthy series of commands in a RUN block can be error-prone. To avoid making mistakes entering the sequence of commands, create a command file (called, for example, /tmp/tspitr.rman) to store the whole sequence of commands for your TSPITR. Review it carefully to catch any errors. Then run the command file at the RMAN prompt, using this command:

RMAN> @/tmp/tspitr.rman ;

The results will be the same as in the previous example.

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.