Performing RMAN Tablespace Point-in-Time Recovery (TSPITR)

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:

1. 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.
2. 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.
3. To recover a table after it has been dropped with the PURGE option.
4. To recover from the logical corruption of a table.
5. To recover dropped tablespaces. In fact, RMAN can perform TSPITR on dropped tablespaces even when a recovery catalog is not used.

Note: To perform TSPITR for CDBs and PDBs, you must connect to the root as a user with the SYSDBA or SYSBACKUP privilege. To perform TSPITR of one more more PDBs, you must have a backup of the root and the CDB seed of the CDB that contains the PDBs.

（一）Basic Concepts of RMAN TSPITR

1. Target instance

Contains the tablespace to be recovered to the target time

1. Target instance

Contains the tablespace to be recovered to the target time

1. Target time

Point in time or SCN of the tablespace after TSPITR completes

1. Auxiliary database

A database used in the recovery process to perform the work of recovery. The auxiliary database has other files associated with it. See auxiliary set for a complete list.

1. Auxiliary destination

An optional disk location that RMAN uses to temporarily store the auxiliary set files. The auxiliary destination is used only with an RMAN-managed auxiliary database. Specifying an auxiliary destination with a user-managed auxiliary database results in an error.

All references to auxiliary destination in this chapter assume use of an RMAN-managed auxiliary database.

1. Recovery set

Data files in the tablespaces that you intend to recover

1. Auxiliary set

Data files required for TSPITR that are not part of the recovery set.

The auxiliary set typically includes:

1. The SYSTEM and SYSAUX tablespaces.
2. Data files containing rollback or undo segments from the target database instance.
3. Temporary tablespaces.
4. Control file from source database.
5. Archived redo logs that must be restored to recover the auxiliary database to specified point in time.
6. Online redo logs of the auxiliary database. These logs are different from the online redo logs of the source database. They are created when the auxiliary database is opened with the RESETLOGS option.

The auxiliary set does not include the parameter file, password file, or associated network files.

（二）How RMAN TSPITR Works With an RMAN-Managed Auxiliary Database

RMAN TSPITR automatically performs the following actions:

1. 检查recovery set表空间是否自包

If the tablespaces in the recovery set have not been dropped, checks to see if they are self-contained by executing the DBMS_TTS.TRANSPORT_SET_CHECK for the recovery set tablespaces and then checking that the view TRANSPORT_SET_VIOLATIONS is empty.

If the query returns rows, RMAN stops TSPITR processing. You must resolve any tablespace containment violations before TSPITR can proceed. 

1. 检查是否需要创建auxiliary database

Checks to see if a connection to a user-managed auxiliary database was provided. If it is, then RMAN TSPITR uses it. If not, RMAN TSPITR creates the auxiliary database, starts it, and connects to it.

1. 把源库中需要恢复的表空间OFFLINE

Takes the tablespaces to be recovered offline in the target database, if the tablespaces in the recovery set have not been dropped.

1. 在auxiliary database还原target time之前备份的控制文件

Restores a backup control file from a point in time before the target time to the auxiliary database.

1. 在auxiliary database还原recovery set 与auxiliary set数据文件

Restores the data files from the recovery set and the auxiliary set to the auxiliary database.

1. Recovers the restored data files in the auxiliary database to the specified time.
2. Opens the auxiliary database with the RESETLOGS option.
3. Makes the recovery set tablespaces read-only in the auxiliary database.
4. Exports the recovery set tablespaces from the auxiliary database using the Data Pump utility to produce a transportable tablespace dump file.
5. Shuts down the auxiliary database.
6. Drops the recovery set tablespaces from the target.
7. Data Pump utility reads the transportable tablespace dump file and plugs the recovery set tablespaces into the target.
8. Makes the tablespaces that were put in the target database read/write and immediately takes them offline.
9. Deletes all auxiliary set files.

At this point, RMAN TSPITR has finished. The recovery set data files are returned to their contents at the specified point in time, and belong to the target database.

The recovery set tablespaces are left offline for you to back up and then bring back online.

These last steps follow Oracle's recommendation and best practice of backing up recovered tablespaces as soon as TSPITR completes.

（三）TSPITR Restrictions, Special Cases, and Limitations

Some database problems cannot be resolved with TSPITR. The following list explains when you cannot perform TSPITR:

1. If there are no archived redo logs or if the database runs in NOARCHIVELOG mode.
2. If TSPITR is used to recover a renamed tablespace to a point in time before it was renamed, you must use the previous name of the tablespace to perform the recovery operation.

In this case when TSPITR completes, the target database contains two copies of the same tablespace, the original tablespace with the new name and the TSPITR tablespace with the old name. If this is not your goal, then you can drop the new tablespace with the new name.

1. If constraints for the tables in tablespace tbs1 are contained in tablespace tbs2, then you cannot recover tbs1 without also recovering tbs2.
2. If a table and its indexes are stored in different tablespaces, then the indexes must be dropped before performing TSPITR.
3. You cannot use TSPITR to recover the current default tablespace.
4. You cannot use TSPITR to recover tablespaces containing any of the following objects:

1. Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) unless all of the underlying or contained objects are in the recovery set. Additionally, if the partitions of a partitioned table are stored in different tablespaces, then you must either drop the table before performing TSPITR or move all the partitions to the same tablespace before performing TSPITR.
2. Undo or rollback segments
3. Oracle8-compatible advanced queues with multiple recipients
4. Objects owned by the user SYS. Examples of these types of objects are: PL/SQL, Java classes, callout programs, views, synonyms, users, privileges, dimensions, directories, and sequences.

1. Limitations of TSPITR

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

1. TSPITR does not recover query optimizer statistics for recovered objects.You must gather new statistics after TSPITR completes.
2. 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.
3. 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.

1. Special Considerations When Not Using a Recovery Catalog

You must be aware of following precautions:

1. 如果恢复点undo表空间与当前设置不同需要设置UNDO_TABLESAPCE调整后恢复

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(即undo tablespace) were the same set present at the time when recovery was 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.

1. TSPITR to a time that is too old may not succeed if Oracle Database has reused the control file records for needed backups.
2. 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. 重新执行需要先删除使用TSPITR恢复的表空间

（四）Planning and Preparing for TSPITR

1. Selecting the Right Target Time for TSPITR

To identify a target time for TSPITR, investigate past states of your data and find the point in time when unwanted changes occurred by using one of the following techniques:

Flashback Query

Oracle Transaction Query

Flashback Version Query

1. Determine the recovery set and resolve dependencies

RMAN TSPITR requires that the tablespace be self-contained and that no SYS -owned objects reside in the tablespace.

You can use the DBMS_TTS.TRANSPORT_SET_CHECK procedure to locate objects outside the tablespace and identify relationships between objects that span the recovery set boundaries. If the TRANSPORT_SET_VIOLATIONS view returns rows, you must investigate and correct the problem according to the choices mentioned earlier in this step.

Note: If one or more of the tablespaces in the recovery set have been dropped, RMAN TSPITR cannot run the procedure DBMS_TTS.TRANSPORT_SET_CHECK. In this case, DBMS_TTS.TRANSPORT_SET_CHECK is run when the Data Pump export of the auxiliary database occurs. Just like RMAN TSPITR, if the export operation encounters any tablespaces that are not self-contained, it fails.

Example 21-1 Querying DBMS_TTS.TRANSPORT_SET_CHECK for a Subset of Tablespaces

BEGIN

   DBMS_TTS.TRANSPORT_SET_CHECK('USERS,TOOLS', TRUE,TRUE);

END;

/

SELECT * FROM  TRANSPORT_SET_VIOLATIONS;

1. Identifying and Preserving Objects That Are Lost After TSPITR

可以在使用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.

To determine which objects are lost in TSPITR, query the TS_PITR_OBJECTS_TO_BE_DROPPED view on the primary database.

Filter the view for objects whose CREATION_TIME is after the target time for TSPITR.

For example, with a recovery set consisting of users and tools, and a recovery point in time of November 2, 2013, 7:03:11 am, issue the statement shown in Example 21-2.

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-13:07:03:11','YY-MON-DD:HH24:MI:SS')

ORDER BY TABLESPACE_NAME, CREATION_TIME;

Example 21-3 Using SCN and TS_PITR_OBJECTS_TO_BE_DROPPED

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;

（五）Performing Fully Automated RMAN TSPITR

执行TSPITR时有两种可用模式：使用RMAN-Managed Auxiliary Database或是使用Your Own Auxiliary Database。默认模式即前者，它不需要使用RMAN连接auxiliary database

使用默认模式会将recovery set data files还原到target database相应数据文件的源位置；在auxiliary database中使用target databases配置相同的channel进行restore；Auxiliary set files放在AUXILIARY DESTINATION指定位置

During TSPITR, the recovery set data files are written in their current locations on the target database. The same channel configurations for the target database are used on the auxiliary database when restoring files from backup. Auxiliary set data files and other auxiliary database files, however, are stored in the auxiliary destination. Use the AUXILIARY DESTINATION parameter to set a location for RMAN to use for the auxiliary set data files.

To perform fully automated RMAN TSPITR, the user performing TSPITR must be able to connect with the SYSBACKUP or SYSDBA privilege using operating system authentication.

To perform fully automated RMAN TSPITR:

1. Review the information in "TSPITR Restrictions, Special Cases, and Limitations".
2. Perform the tasks in "Planning and Preparing for TSPITR".
3. Start an RMAN session on the target database and, if applicable, connect to a recovery catalog.

Note: Do not connect to an auxiliary database when starting the RMAN client for automated TSPITR. If RMAN is connected to an auxiliary database when you run RECOVER TABLESPACE, then RMAN assumes that you are managing your own auxiliary database, as described in "Performing RMAN TSPITR Using Your Own Auxiliary Database".

1. Configure any channels required for TSPITR on the target instance.

Auxiliary database会使用target database相同配置的channel

The auxiliary database uses the same channel configuration as the target instance when performing TSPITR.

1. Run the RECOVER TABLESPACE command, specifying both the UNTIL clause and the AUXILIARY DESTINATION parameter. 不需要执行RESTORE命令

RECOVER TABLESPACE users, tools UNTIL LOGSEQ 1300 THREAD 1

  AUXILIARY DESTINATION '/disk1/auxdest';

1. If TSPITR completes successfully, then back up the recovered tablespaces before bringing them online. After you perform TSPITR on a tablespace, you can no longer use previous backups of that tablespace after TSPITR successfully completes.

BACKUP TABLESPACE users, tools;

1. Bring the tablespaces back online.

完成TSPITR相应表空间为OFFLINE状态，所以auxiliary database files会自动被删除

The tablespaces are left offline. All auxiliary set data files and other auxiliary database files are cleaned up from the auxiliary destination.

RMAN> ALTER TABLESPACE users, tools ONLINE;

（六）Overriding Defaults for RMAN TSPITR with an RMAN-Managed Auxiliary Database

可以对使用RMAN-Managed Auxiliary Database的TSPITR进行以下自定义选项

1. Rename or relocate your recovery set data files so that the data files making up the recovered tablespaces are not stored in the original locations after TSPITR.
2. Specify a location other than the auxiliary destination for some or all auxiliary set data files.
3. Rename files in an Oracle Managed Files format.
4. Set up image copy backups of your auxiliary set data files in advance to avoid having to restore data files during TSPITR.
5. Customize initialization parameters for your RMAN-managed auxiliary database.

1. Renaming TSPITR Recovery Set Data Files with SET NEWNAME

设置recovery set还原位置(通常它们还原到target database需要恢复文件位置，这样在使用传输表空间恢复target database不用复制数据文件)，不会删除源库相应数据文件

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.

Example 21-4 Renaming Recovery Set Files

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;}

RMAN does not detect conflicts between names set with SET NEWNAME and current data file names on the target database until the actual recovery. If RMAN detects a conflict, then TSPITR fails and RMAN reports an error. The valid data file is not overwritten.

1. Naming TSPITR Auxiliary Set Data Files

设置auxiliary set data files还原位置，默认它们还原到AUXILIARY DESTINATION 指定位置

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.

RMAN会按以下顺序控制auxiliary set data files还原位置

RMAN supports the following alternatives for controlling the location of auxiliary set data files, which are listed in order of precedence shown：

1. SET NEWNAME
2. CONFIGURE AUXNAME
3. DB_FILE_NAME_CONVERT

Note: If the target database uses OMF names for auxiliary set, then you cannot use DB_FILE_NAME_CONVERT

1. AUXILIARY DESTINATION argument to RECOVER TABLESPACE when using an RMAN-managed auxiliary database

Settings higher on the list override settings lower on the list in situations where both have been applied. For example, you might run RECOVER TABLESPACE... AUXILIARY DESTINATION on a target database when some auxiliary set data files have auxiliary names configured with CONFIGURE AUXNAME.

无论是否使用其它参数都建议同时设置AUXILIARY DESTINATION

Even if you intend to use either of the preceding techniques to provide locations for specific files, Oracle recommends that you provide an AUXILIARY DESTINATION argument to RECOVER TABLESPACE when using an RMAN-managed auxiliary database. If you overlook renaming some auxiliary set data files, then TSPITR still succeeds. Any files not otherwise renamed are placed in the auxiliary destination.

Note: You can view any current CONFIGURE AUXNAME settings by running the SHOW AUXNAME command

2.1 Considerations When Renaming OMF Auxiliary Set Files in TSPITR

2.1.1 Using ASM Storage

For Oracle Managed Files (OMF) that use ASM storage, the database converts only disk group names as in: +DISK1 to +DISK2.

You can use DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT initialization parameters for the auxiliary database to specify the conversion of the disk group. RMAN uses the pattern to convert the ASM disk group name and generates a valid OMF file name in the converted disk group. The following command demonstrates this point:

LOG_FILE_NAME_CONVERT='+onlinelogs','+tmpasm'

If the DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT parameters change a substring other than the disk group name, the conversion is ignored and the resulting disk group name is used, for example:

DB_FILE_NAME_CONVERT='+DATAFILE/prod','+DATAFILE/tspitr'

The preceding command results in an invalid ASM OMF file name and the change is ignored. Instead, the files are created in disk group name +DATAFILE and the following message is issued:

WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only

If auxiliary set data files are stored in ASM disk groups, then you can use the SET NEWNAME command to redirect individual files to a specific disk group accessible from the auxiliary database

Example 21-5 Redirecting ASM files

RUN

{

  SET NEWNAME FOR DATAFILE 1 TO "+DISK2";

  SET NEWNAME FOR DATAFILE 2 TO "+DISK3";

  RECOVER TABLESPACE users, tools

    UNTIL LOGSEQ 1300 THREAD 1

    AUXILIARY DESTINATION '/disk1/auxdest';

}

2.1.2 Using Non-ASM Storage

The initialization parameters DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT cannot be used to rename OMF (non-ASM) file names for the auxiliary database because this method generates invalid OMF file names. If you must control the generation of new OMF file names that do not use ASM storage, you must rename them using one of the following alternate techniques. The various naming options are listed in order from most recommended to least recommended.

1. Use an auxiliary destination, as described in "Performing Fully Automated RMAN TSPITR".
2. Specify locations for new OMF files with one or more of the OMF initialization parameters for the auxiliary database so that all of the necessary OMF files are handled:

1. DB_CREATE_FILE_DEST for the auxiliary set data files
2. DB_CREATE_ONLINE_LOG_DEST_n with DB_CREATE_FILE_DEST for the online redo logs of the auxiliary database if the online logs are not created in the DB_CREATE_FILE_DEST

2.2 Using SET NEWNAME to Name Auxiliary Set Data Files During TSPITR

To specify a new name for an auxiliary set data file, you can enclose RECOVER TABLESPACE in a RUN command and use a SET NEWNAME command within the RUN block to rename the file. 

Example 21-6 Renaming Auxiliary Set Oracle Managed Files (OMF) in TSPITR

RUN

{

  SET NEWNAME FOR DATAFILE '?/oradata/prod/system01.dbf'

    TO '/disk1/auxdest/system01.dbf';

  SET NEWNAME FOR DATAFILE '?/oradata/prod/sysaux01.dbf'

    TO '/disk1/auxdest/sysaux01.dbf';

  SET NEWNAME FOR DATAFILE '?/oradata/prod/undotbs01.dbf'

    TO '/disk1/auxdest/undotbs01.dbf';

  RECOVER TABLESPACE users, tools

    UNTIL LOGSEQ 1300 THREAD 1

    AUXILIARY DESTINATION '/disk1/auxdest';

}

The result depends on whether /disk1/auxdest/system01.dbf exists when RECOVER TABLESPACE is executed. If ?/oradata/system01.dbf exists at the specified location and was created at an SCN before the UNTIL time for TSPITR, then the DATAFILECOPY is used and the restore operation is not necessary. For more information, see "Using SET NEWNAME and CONFIGURE AUXNAME with Auxiliary Set Image Copies".

Otherwise, RMAN restores the auxiliary set data file to the NEWNAME instead of the default location. If your intention is to control where the auxiliary set data files are stored, then ensure that no file is stored at the location specified by SET NEWNAME before performing TSPITR.

2.3 Using DB_FILE_NAME_CONVERT to Name Auxiliary Set Data Files During TSPITR

Assume that you do not want to use an auxiliary destination for all of your auxiliary set data files, but you also do not want to name every file individually. In this case, you can include a DB_FILE_NAME_CONVERT initialization parameter in the initialization parameter file used by the auxiliary database. You can use this technique only in the following circumstances:

One of the following situations exists:

1. You create your own initialization parameter file for an automatically managed auxiliary database, as described in "Customizing Initialization Parameters for the Automatic Auxiliary Database in TSPITR"
2. You create your own auxiliary database, as described in "Performing RMAN TSPITR Using Your Own Auxiliary Database"

The DB_FILE_NAME_CONVERT initialization parameter in the auxiliary database specifies how to derive names for files in the auxiliary database from the original names of the corresponding files in the target database instance.

DB_FILE_NAME_CONVERT=('?/oradata/trgt', '/bigtmp')

You can still rename individual auxiliary set data files with the SET NEWNAME or CONFIGURE AUXNAME command. Also, files that do not match the patterns provided in DB_FILE_NAME_CONVERT are not renamed.

When using RMAN-managed auxiliary database, you can use the AUXILIARY DESTINATION parameter of RECOVER TABLESPACE command to ensure that all auxiliary set data files are sent to some destination. If the renaming methods do not provide a new name for a file at the auxiliary database, then TSPITR fails.

2.3.1 Renaming Temp Files During TSPITR

Temp files are considered part of the auxiliary set for your database. When the auxiliary database is instantiated, RMAN re-creates the temporary tablespaces of the target database and generates their names with the regular rules for the auxiliary data file names.

To rename temp files, you can use one of the following:

1. SET NEWNAME FOR TEMPFILE command
2. DB_FILE_NAME_CONVERT initialization parameter of the auxiliary database. See the previous example. If the temporary files have non-ASM Oracle Managed File names, you cannot use this parameter option. See "Considerations When Renaming OMF Auxiliary Set Files in TSPITR".
3. AUXILIARY DESTINATION clause of the RECOVER command when using an RMAN-managed auxiliary database

1. Using Image Copies for Faster RMAN TSPITR Performance

You can enhance TSPITR performance 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:

1. Use the CONFIGURE AUXNAME command with image copies of auxiliary set data files
2. Use the SET NEWNAME command with image copies of recovery set data files or auxiliary set data files

3.1 Using SET NEWNAME with Recovery Set Image Copies

During TSPITR, RMAN looks in the specified NEWNAME location for the data file. RMAN checks whether an image copy backup of the data file exists with a data file checkpoint SCN early enough that it can be recovered to the target time. If RMAN finds a usable image copy, then RMAN uses it in TSPITR. Otherwise, RMAN restores the data file to the NEWNAME location. Any file in the location specified by the NEWNAME is overwritten. The specified NEWNAME becomes the name of the data file in the target database after TSPITR completes. 

Example 21-7 Using SET NEWNAME

RUN

{

SET NEWNAME FOR DATAFILE 'ORACLE_HOME/oradata/trgt/users01.dbf'

  TO '/newfs/users1.dbf';

...other RMAN commands, if any...

RECOVER TABLESPACE users, tools UNTIL SEQUENCE 1300 THREAD 1;

}

3.2 Using SET NEWNAME and CONFIGURE AUXNAME with Auxiliary Set Image Copies

The CONFIGURE AUXNAME command sets a persistent alternative location for an auxiliary set data file image copy, whereas the SET NEWNAME command sets an alternative location for the duration of a RUN command.

As with all auxiliary set files, the file is deleted after TSPITR. This behavior occurs regardless of whether it was an image copy created before TSPITR or restored by RMAN during TSPITR.

The primary use of CONFIGURE AUXNAME is to make TSPITR faster by eliminating restore times. If you anticipate performing TSPITR, then you can include in your backup routine the maintenance of a set of image copies of the auxiliary set data files, and update these periodically to the earliest point to which you expect to perform TSPITR. The recommended usage model is:

1. Configure the AUXNAME for the files once when setting up this strategy.
2. Perform BACKUP AS COPY DATAFILE n FORMAT auxname regularly to maintain the updated image copy. For better performance, use an incrementally updated backup strategy to keep the image copies up-to-date without performing full backups of the data files.
3. When TSPITR is needed, specify a target time after the last update of the image copy.

Example:

1. Configure an AUXNAME for each data file in the auxiliary set by using a command of the following form:

CONFIGURE AUXNAME FOR DATAFILE n TO auxname_n;

1. Take an image copy of the auxiliary set every Sunday :

BACKUP AS COPY DATAFILE n FORMAT auxname_n

If the image copies are all in the same location on disk, and if they are named similarly to the original data files, then you can avoid performing backups of every data file. Instead, you can use the FORMAT or DB_FILE_NAME_CONVERT options of the BACKUP command and use BACKUP AS COPY DATABASE. For example, if the configured auxiliary names are a translation of the location maindisk to auxdisk, then you use the following command:

BACKUP AS COPY DATABASE DB_FILE_NAME_CONVERT (maindisk, auxdisk);

Note: Because Oracle managed file names cannot generally be translated using a simple substitution, you cannot typically use DB_FILE_NAME_CONVERT to generate names for image copies stored in OMF.

After these steps, you are prepared for TSPITR without restoring the auxiliary set from backup.

1. Customizing Initialization Parameters

RMAN-Managed Auxiliary Database会使用一些默认初始参数，另外还会去OS中找参数文件

The automatic auxiliary database uses a set of default initialization parameters as shown in Table 21-4. It also 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 in Table 21-4 for the RMAN-managed automatic auxiliary database.

Table 21-4 Default Initialization Parameters for the RMAN-Managed Auxiliary Database

Initialization Parameter	Value
DB_NAME	Same as DB_NAME of the source database
COMPATIBLE	Same as the COMPATIBLE setting of the target database
DB_UNIQUE_NAME	RMAN auto-generated unique value based on DB_NAME
DB_BLOCK_SIZE	Same as the DB_BLOCK_SIZE of the target database
DB_CREATE_FILE_DEST	Auxiliary destination (only if the AUXILIARY DESTINATION argument is specified when using an RMAN-managed auxiliary database). RMAN creates Oracle Managed Files for the auxiliary set files in this location.
LOG_ARCHIVE_DEST_1	Auxiliary destination (only if the AUXILIARY DESTINATION clause is specified when using an RMAN-managed auxiliary database). Archived logs needed for recovery are restored to this location.
SGA_TARGET	280M
DB_FILES	Same as DB_FILES of the target database
PROCESSES	50

如果用了错误值来覆盖以上默认参数可能会出错，但你也加一些其它参数设置

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 auxiliary database, you can do either of the following:

1. 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.
2. 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.

4.1 Specifying the Auxiliary Database Control File Location in TSPITR

If you use an initialization parameter file, then you can specify your own location for the control file of your auxiliary database. Set the CONTROL_FILES initialization parameter to specify a location for the control files.

If you do not explicitly specify a control file location, and if you use the AUXILIARY DESTINATION clause, then RMAN locates the control file in the auxiliary destination. If you do not use the AUXILIARY DESTINATION clause, then the auxiliary database control files are stored in an operating system-specific location.

No matter where you store your auxiliary database control file, it is removed at the end of the TSPITR operation. Because control files are relatively small, it is rare that RMAN encounters a problem creating an auxiliary control file. If there is not enough space to create the control file, however, then TSPITR fails.

4.2 Specifying the Auxiliary Database Archived Logs in TSPITR

To perform recovery on the auxiliary and recovery sets after restoring them at the auxiliary database, RMAN may need to restore archived logs. When an auxiliary destination is being used, the archived logs are restored to that location. In the absence of an auxiliary destination and any other initialization parameters, the archived logs are restored to an operating system specific location. For details, consult your operating system specific documentation. You can use the LOG_ARCHIVE_DEST_1 initialization parameter to specify an alternative location where the archived logs are restored.

4.3 Specifying the Auxiliary Database Online Log Location in TSPITR

If you specify the LOG_FILE_NAME_CONVERT initialization parameter in your auxiliary database parameter file and the parameter successfully converts the names of the online redo logs of the target, then this parameter determines the online redo log location. The same restrictions that apply to OMF data files, apply to OMF online redo logs. For more information, see "Considerations When Renaming OMF Auxiliary Set Files in TSPITR". If RMAN is managing the auxiliary database and an auxiliary destination is specified, RMAN creates the online redo log in the auxiliary destination.

Alternatively, you can use DB_CREATE_FILE_DEST or DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_1 to specify the location where the auxiliary database redo logs are created. If you choose the latter option, you must use DB_CREATE_ONLINE_LOG_1 with DB_CREATE_FILE_DEST.

TSPITR fails to create the online redo logs if you do not specify a location for them by using one of the following:

LOG_FILE_NAME_CONVERT

DB_CREATE_FILE_DEST

DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_1

AUXILIARY DESTINATION

（七）Performing RMAN TSPITR Using Your Own Auxiliary Database

Although Oracle recommends that you let RMAN manage all aspects of the auxiliary database, there may be times when you must create and manage your own auxiliary database.

使用此模式需要自己设置、启动、停用及清理auxiliary database

If you select this mode, you are responsible for setting up, starting, stopping and cleaning up the auxiliary database used in TSPITR.

One reason that you might want to create your own instance is to exercise control of channels used in TSPITR. The automatic auxiliary database uses the configured channels of the target database as the basis for the channels to configure on the auxiliary database and to use during the restore operation. You may need different channel settings and may not want to use the CONFIGURE command to change the settings on the target database.

In this case, you can operate your own auxiliary database. By connecting to the auxiliary database before invoking RECOVER, a run block can provide specific channel allocations using the ALLOCATE AUXILIARY CHANNEL command.

1. Preparing Your Own Auxiliary Database for RMAN TSPITR

Step 1: Create an Oracle Password File for the Auxiliary Database

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

Use a text editor to create an initialization parameter file for the auxiliary database on the target database host. For this example, assume that your parameter file is placed at /tmp/initAux.ora. Set the parameters described in Table 21-5.

For TSPITR, the target and auxiliary database instances must be on the same host.

Table 21-5 Initialization Parameters in a User-Managed Auxiliary Database

Initialization 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.
REMOTE_LOGIN_PASSWORDFILE	YES	Set to EXCLUSIVE when connecting to the auxiliary database with 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 database.
LOG_FILE_NAME_CONVERT	NO	Patterns to generate file names for the online redo logs of the auxiliary database based on the online redo log names of the target database. Query V$LOGFILE.MEMBERto obtain target instance online redo log file names, and ensure that the conversion pattern matches the format of the file name shown in the view. Note: Some platforms do not support ending patterns in a forward or backward slash (\ or /). See Also: "Specifying the Auxiliary Database Online Log Location in TSPITR" for restrictions on possible values for LOG_FILE_NAME_CONVERT with OMF file names and "Considerations When Renaming OMF Auxiliary Set Files in TSPITR"
DB_FILE_NAME_CONVERT	NO	Patterns to convert file names for the data files of the auxiliary database. You can use this parameter to generate file names for those files that you did not name with SET NEWNAME or CONFIGURE AUXNAME. Obtain the data file names by querying V$DATAFILE.NAME, and ensure that the conversion pattern matches the format of the file name displayed in the view. 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 Data Files During TSPITR" and "Considerations When Renaming OMF Auxiliary Set Files in TSPITR".
DB_CREATE_FILE_DEST	NO	Identifies a location for all auxiliary set files.
LOG_ARCHIVE_DEST_n	NO	Identifies where archived logs required for recover are created.
DB_CREATE_ONLINE_LOG_n	NO	With DB_CREATE_FILE_DEST identifies a different location where online redo logs are created.
CONTROL_FILES	NO	File names that do not conflict with the control file names of the target instance (or any other existing file).
SGA_TARGET	NO (Recommended)	280M
STREAMS_POOL_SIZE	NO YES	If SGA_TARGET is set If SGA_TARGET is not set

Set other parameters as needed, including the parameters to specify how much memory the auxiliary database uses.

The following example shows possible initialization parameter settings for an auxiliary database for TSPITR:

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 =11.0.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.

Step 3: Check Oracle Net Connectivity to the Auxiliary Database

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

1. Preparing RMAN Commands for TSPITR with Your Own Auxiliary Database

2.1 Planning Channels for TSPITR with Your Own Auxiliary Database

When you run your own auxiliary database, the default behavior is to use the automatic channel configuration of the target database. If you decide to allocate your own channels with a different configuration (changing the number of channels or channel parameters), you can include ALLOCATE AUXILIARY CHANNEL commands in a RUN block along with the RECOVER TABLESPACE command for TSPITR. Plan these commands, if necessary, and add them to the sequence of commands you run for TSPITR.

2.2 Planning Data File Names with Your Own Auxiliary Database: SET NEWNAME

You may want to use SET NEWNAME commands 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 these commands, if necessary, and add them to the sequence of commands that you run for TSPITR.

1. Executing TSPITR with Your Own Auxiliary Database

Step 1: Start the Auxiliary Database in NOMOUNT Mode

Before beginning RMAN TSPITR, start SQL*Plus and connect to the auxiliary database with SYSOPER privileges. 

Start the auxiliary database in NOMOUNT mode, specifying a parameter file if necessary.

SQL> STARTUP NOMOUNT PFILE='/tmp/initAux.ora'

Remember that if you specify PFILE, then the path for the PFILE is a client-side path on the host from which you run SQL*Plus.

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

Start RMAN and connect to the target database and the manually created auxiliary database.

rman target dba AUXILIARY auxusr@aux

Step 3: Execute the RECOVER TABLESPACE Command

In the simplest case, execute the RECOVER TABLESPACE... UNTIL command at the RMAN prompt:

RECOVER TABLESPACE ts1, ts2... UNTIL TIME 'time';

If you want to use the ALLOCATE AUXILIARY CHANNEL or SET NEWNAME commands, then include these commands before the RECOVER TABLESPACE command within a RUN command. The following example illustrates this technique:

RUN {

   ALLOCATE AUXILIARY CHANNEL c1 DEVICE TYPE DISK;

   ALLOCATE AUXILIARY CHANNEL c2 DEVICE TYPE sbt;

  # and so on...

   RECOVER TABLESPACE ts1, ts2 UNTIL TIME 'time'; }

1. Performing TSPITR with Your Own Auxiliary Database: Scenario

This scenario shows the execution of a RECOVER TABLESPACE... UNTIL operation. This scenario illustrates the following features of RMAN TSPITR:

Managing your own auxiliary database

Configuring channels for restore of backups from disk and SBT devices

Using recoverable image copies for some auxiliary set data files using SET NEWNAME

Specifying new names for recovery set data files using SET NEWNAME

To use TSPITR with your own auxiliary database:

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

1. Create service name pitprod for the auxiliary database, and check for connectivity.
2. 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

1. Start RMAN and connect to the target and auxiliary database instances.

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

1. 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';

}

Consider storing this command sequence in a command file and executing the command file.

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

1. The recovery set data files 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 TSPITR.
2. The auxiliary files are removed by RMAN, including the control files, online logs, and auxiliary set data files of the auxiliary database.
3. The auxiliary database is shut down.

If the TSPITR operation fails, the auxiliary set files are removed and the auxiliary database is shut down. The recovery set files are left in the specified location and in an unresolved state from the failed TSPITR run.

（八）Troubleshooting RMAN TSPITR

1. Troubleshooting File Name Conflicts During TSPITR

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.

Suppose that SET NEWNAME, CONFIGURE AUXNAME, and DB_FILE_NAME_CONVERT cause multiple files in the auxiliary or recovery sets to have the same name. In this case, RMAN reports an error during TSPITR. To correct the problem, use different values for these parameters.

2. Troubleshooting the Identification of Tablespaces with Undo Segments During TSPITR

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 there is no recovery catalog or if the information is not found in the recovery catalog, RMAN assumes that the set of tablespaces with undo segments at the target time equals the set of tablespaces with undo segments at the present time. If this assumption is not correct, then TSPITR fails with an error. In this case, use the UNDO TABLESPACE clause to provide a list of tablespaces with undo segments at the target time.

3. Troubleshooting the Restart of a Manual Auxiliary Database After TSPITR Failure

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

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