Recovery Manager (RMAN) TSPITR enables quick recovery of one or more tablespaces in a database to an earlier time without affecting the rest of the tablespaces and objects in the database.

RMAN TSPITR is most useful for the following situations:

• To recover a logical database to a point different from the rest of the physical database, when multiple logical databases exist in separate tablespaces of one physical database. For example, you maintain logical databases in the orders and personnel tablespaces. An incorrect batch job or data manipulation language (DML) statement corrupts the data in only one tablespace.

• To recover data lost after data definition language (DDL) operations that change the structure of tables. You cannot use Flashback Table to rewind a table to before the point of a structural change such as a truncate table operation.

• To recover a table after it has been dropped with the PURGE option.

• To recover from the logical corruption of a table.

• To recover dropped tablespaces. In fact, RMAN can perform TSPITR on dropped tablespaces even when a recovery catalog is not used.

You can also use Flashback Database to rewind data, but you must rewind the entire database rather than just a subset. Also, unlike TSPITR, the Flashback Database feature necessitates the overhead of maintaining flashback logs. The point in time to which you can flash back the database is more limited than the TSPITR window, which extends back to your earliest recoverable backup.

Understand the concepts of RMAN TSPITR such as the terminology and modes used.

This section contains the following topics:

• Common Terms for RMAN TSPITR

• Modes of RMAN TSPITR

• How RMAN TSPITR Works With an RMAN-Managed Auxiliary Database

There are some limitations to consider when performing TSPITR.

After TSPITR completes, RMAN recovers the data files in the recovery set to the target time. Note the following special cases:

• TSPITR does not recover query optimizer statistics for recovered objects.You must gather new statistics after TSPITR completes.

• If you run TSPITR on a tablespace and bring the tablespace online at time t, then backups of the tablespace created before time t are no longer usable for recovery with a current control file. You cannot use the current control file to recover the database to any time less than or equal to t.

• If one or more data files in the recovery set have Oracle Managed File (OMF) names and the compatibility in the target database is set to version 10.1 or earlier, RMAN cannot reuse the data file. This restriction is true even if no SET NEWNAME command is provided for the data file. A new OMF name is created for the recovery set data file. This action temporarily doubles the space requirements for the data file. This is because DB_CREATE_FILE_DEST has two copies of the data file (the original data file and the one used by TSPITR) until the tablespace is dropped in the target and the original data file is deleted.

RMAN uses the transportable tablespaces functionality to perform TSPITR. Therefore, any limitations on transportable tablespaces are also applicable to TSPITR.

Be aware of certain precautions when not using a recovery catalog during TSPITR.

The precautions include the following:

• Because RMAN has no historical record of the undo in the control file, RMAN assumes that the current set of tablespaces with rollback or undo segments were the same set present at the time when recovery was performed. If the tablespace set has changed since that time, then the current rollback or undo segments were the same segments present at the time to which recovery is performed. If the undo segments have changed since that time, then you can use UNDO TABLESPACE to indicate the correct set of tablespaces with undo at the point in time where the tablespaces are being recovered.

• TSPITR to a time that is too old may not succeed if Oracle Database has reused the control file records for needed backups. (In planning your database, set the CONTROL_FILE_RECORD_KEEP_TIME initialization parameter to a value large enough to ensure that control file records needed for TSPITR are kept.)

• To rerun TSPITR when you are not using a recovery catalog, you must first drop the tablespace to be used by TSPITR from the target database.

It is extremely important that you choose the right target time or SCN for your TSPITR. Note that after you bring a tablespace online after TSPITR, you cannot use any backup from a time earlier than the moment you brought the tablespace online.

Initially, your recovery set includes the data files for the tablespaces that you intend to recover. However, if objects in the tablespaces that you need have relationships (such as constraints) to objects in other tablespaces, then you must account for these relationships before you can perform TSPITR

When you perform RMAN TSPITR on a tablespace, objects created after the target recovery time are lost. You can preserve such objects after they are identified by exporting them before TSPITR with the Data Pump Export utility and reimporting them afterward with Data Pump Import.

SELECT OWNER, NAME, TABLESPACE_NAME, 
       TO_CHAR(CREATION_TIME, 'YYYY-MM-DD:HH24:MI:SS') 
       FROM TS_PITR_OBJECTS_TO_BE_DROPPED 
WHERE TABLESPACE_NAME IN ('USERS','TOOLS') 
AND CREATION_TIME > TO_DATE('02-NOV-17:07:03:11','YY-MON-DD:HH24:MI:SS')
ORDER BY TABLESPACE_NAME, CREATION_TIME;

SELECT OWNER, NAME, TABLESPACE_NAME,
       TO_CHAR(CREATION_TIME,'YYYY-MM-DD:HH24:MI:SS')
       FROM TS_PITR_OBJECTS_TO_BE_DROPPED
WHERE TABLESPACE_NAME IN ('USERS','TOOLS')
AND CREATION_TIME > TO_DATE(TO_CHAR(SCN_TO_TIMESTAMP(1645870),
'MM/DD/YYYY HH24:MI:SS'),
'MM/DD/YYYY HH24:MI:SS')
ORDER BY TABLESPACE_NAME, CREATION_TIME;

You may not want the recovery set data files restored and recovered in their original locations. The SET NEWNAME command enables you to specify a new destination. When you specify a new destination for the recovery set, RMAN does not remove the original data files of the tablespaces.

RUN
{
   .
   .
   .
   SET NEWNAME FOR DATAFILE 'ORACLE_HOME/oradata/trgt/users01.dbf'
     TO '/newfs/users01.dbf';
   ...other SET NEWNAME commands...
   RECOVER TABLESPACE users, tools UNTIL SEQUENCE 1300 THREAD 1;
}

Unlike recovery set data files, which are usually stored in their original locations, auxiliary set data files must not overwrite the corresponding original files in the target database. If you do not specify an auxiliary set file location that is different from its original location, then TSPITR fails. The failure occurs when RMAN attempts to overwrite the corresponding file in the original database and discovers the file in use.

TSPITR performance can be enhanced by redirecting RMAN to use existing image copies of the recovery set and auxiliary set data files. In this case, RMAN does not need to restore the data files from backup.

In general, if a suitable image copy is available in the specified location, then RMAN uses the image copy to perform TSPITR, and the data file copy is uncataloged from the target control file.

You can use the following techniques to tell RMAN about the possible existence of an image copy of a data file:

• Use the CONFIGURE AUXNAME command with image copies of auxiliary set data files

• Use the SET NEWNAME command with image copies of recovery set data files or auxiliary set data files

The automatic auxiliary database uses a set of default initialization parameters. You can add other parameters, if required.

The automatic auxiliary database looks for additional initialization parameters to complement the default parameters in a location that is operating system-dependent. For example, in UNIX this location is: ?/rdbms/admin/params_auxinst.ora. RMAN always looks for this additional parameter file for an RMAN-automatic auxiliary database when performing TSPITR. If the file is not found, then RMAN does not generate an error. Instead, RMAN uses the default parameters listed in the following table for the RMAN-managed automatic auxiliary database.

Usually it is not necessary to alter or add to the values of these initialization parameters, especially if you provide an AUXILIARY DESTINATION clause to the RECOVER TABLESPACE command when using a RMAN-managed auxiliary database. If you override an initialization parameter in Table 21-4 with an inappropriate value, then TSPITR may fail due to problems with the auxiliary database. Nevertheless, you can add other parameters besides these basic parameters if needed. For example, you can use DB_FILE_NAME_CONVERT to specify the names of the data files in the auxiliary and recovery sets.

To override or specify parameters for the automatic auxiliary database, you can do either of the following:

• Place the initialization parameters in the operating system specific default auxiliary parameter file name. For example, in UNIX, the file name is: ?/rdbms/admin/params_auxinst.ora.

• Perform these steps:

  1. Place the initialization parameters in a file.

  2. Specify the location of this file with the SET AUXILIARY INSTANCE PARAMETER FILE command before executing TSPITR.

Regardless of the method that you choose, the parameters that you specify take precedence over defaults and can override the value of an AUXILIARY DESTINATION clause.

This section contains the following topics:

• Specifying the Auxiliary Database Archived Logs in TSPITR

• Specifying the Auxiliary Database Control File Location in TSPITR

• Specifying the Auxiliary Database Online Log Location in TSPITR

Creating an Oracle instance suitable for use as an auxiliary database requires you to perform a set of steps.

Keep in mind certain guidelines when performing TSPITR with your own auxiliary instance.

If you run your own auxiliary database, then it is possible for the sequence of commands required for TSPITR to be long. This situation can occur when you allocate a complex channel configuration for restoring from backup and you are not using DB_CREATE_FILE_DEST to determine file naming of auxiliary set files.

You may want to store the series of commands for TSPITR in an RMAN command file. Review the command file carefully to catch any errors. To read the command file into RMAN, use the @ command (or the CMDFILE command-line argument when starting RMAN).

The following example runs the command file named /tmp/tspitr.rman:

@/tmp/tspitr.rman;

Complete the prerequisites and then follow the steps in this section to perform TSPITR with your own auxiliary database.

This procedure uses the RECOVER TABLESPACE... UNTIL command to perform TSPITR.

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

   DB_NAME=PROD
   DB_UNIQUE_NAME=tspitr_PROD
   CONTROL_FILES=/bigtmp/tspitr_cntrl.dbf
   DB_CREATE_FILE_DEST=/bigtmp
   COMPATIBLE=11.0.0
   BLOCK_SIZE=8192
   REMOTE_LOGIN_PASSWORD=exclusive
   

2. Create service name pitprod for the auxiliary database, and check for connectivity.
3. Using SQL*Plus, connect to the auxiliary database with SYSOPER privileges. Start the instance in NOMOUNT mode:

   SQL> STARTUP NOMOUNT PFILE=/bigtmp/init_tspitr_prod.ora
   

4. Start RMAN and connect to the target and auxiliary database instances, as described in "Making Database Connections with RMAN".

   rman target / auxiliary '"sbu@pitprod AS SYSBACKUP"'
   

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

   RUN 
   {
   # Specify NEWNAME for recovery set data files
     SET NEWNAME FOR TABLESPACE clients
                           TO '?/oradata/prod/rec/%b';
   
   # Specify NEWNAMES for some auxiliary set 
   # data files that have a valid image copy to avoid restores:
     SET NEWNAME FOR DATAFILE '?/oradata/prod/system01.dbf'
                           TO '/backups/prod/system01_monday_noon.dbf';
     SET NEWNAME FOR DATAFILE '?/oradata/prod/system02.dbf'
                           TO '/backups/prod/system02_monday_noon.dbf';
     SET NEWNAME FOR DATAFILE '?/oradata/prod/sysaux01.dbf'
                           TO '/backups/prod/sysaux01_monday_noon.dbf';
     SET NEWNAME FOR DATAFILE '?/oradata/prod/undo01.dbf'
                           TO '/backups/prod/undo01_monday_noon.dbf';
   
   # Specify the types of channels to use
     ALLOCATE AUXILIARY CHANNEL c1 DEVICE TYPE DISK;
     ALLOCATE AUXILIARY CHANNEL t1 DEVICE TYPE sbt;
   
   # Recover the clients tablespace to 24 hours ago:
     RECOVER TABLESPACE clients UNTIL TIME 'sysdate-1';
   }
   

Name conflicts can occur between files in the target database, file names assigned by the SET NEWNAME or CONFIGURE AUXNAME commands, and file names generated by the effect of the DB_FILE_NAME_CONVERT parameter.

During TSPITR, RMAN needs information about which tablespaces had undo segments at the TSPITR target time. This information is usually available in the recovery catalog, if one is used.

If you are managing your own auxiliary database and TSPITR fails, do not attempt to rerun TSPITR without resolving the errors.

1. Identify and fix the problems that prevented TSPITR from a successful run.
2. Start the auxiliary database in NOMOUNT.
3. Run TSPITR again.