TSPITR--表空间时间点恢复技术

使用TSPITR的目的

使用tspitr，是为了让快速恢复一个或者多个表空间到一个不同于其他表空间的，一个以前的时间点，却不影响其他表空间以及其objects。

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.
  

• 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 thePURGE 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.

TSPITR的一些术语：

RMAN TSPITR Entities

Name	Explanation
Target instance	Contains the tablespace to be recovered to the target time
Target time	Point in time or SCN of the tablespace after TSPITR completes
Auxiliary instance	A database instance used in the recovery process to perform the work of recovery. The auxiliary instance has other files associated with it. See auxiliary set for a complete list.
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 instance. Specifying an auxiliary destination with a user-managed auxiliary instance results in an error. All references to auxiliary destination in this chapter assume use of an RMAN-managed auxiliary instance.
Recovery set	Data files in the tablespaces that you intend to recover
Auxiliary set	Data files required for TSPITR that are not part of the recovery set. The auxiliary set typically includes: #system、sysaux、undo、temporary tablespace file、controlfile、archived logs The SYSTEM and SYSAUX tablespaces. Data files containing rollback or undo segments from the target database instance. Temporary tablespaces. Control file from source database. Archived redo logs that must be restored to recover the auxiliary instance to specified point in time. Online redo logs of the auxiliary instance. These are not the same logs as the online redo logs from the source database. They are created when the auxiliary instance is opened with theRESETLOGS option. The auxiliary set does not include the parameter file, password file, or associated network files.

TSPIRT 三种恢复模式

You start RMAN TSPITR with the RMAN RECOVER TABLESPACE command. You have several options for running RMAN TSPITR. The difference between the various modes of operation corresponds to how much automation versus customization you require in your environment. There are three ways to run the utility:

• Fully Automated (the default)

  In this mode, RMAN manages the entire TSPITR process including the auxiliary instance. You specify the tablespaces of the recovery set, an auxiliary destination, the target time, and you allow RMAN to manage all other aspects of TSPITR.    #指定recovery set auxiliary destination 和 target time
  

  The default mode is recommended unless you specifically need more control over the location of recovery set files after TSPITR, auxiliary set files during TSPITR, channel settings and parameters or some other aspect of your auxiliary instance. For more information, see "Performing Fully Automated RMAN TSPITR".

• Automated: RMAN-Managed Auxiliary Instance with User Settings

  You can override some defaults of RMAN TSPITR while still using an RMAN-managed auxiliary instance and destination. This variation of the default mode enables you to benefit from some built-in management that RMAN TSITR provides while being able to specify:

  • Location of auxiliary set or recovery set files

  • Initialization parameters

  For more information, see "Overriding Defaults for RMAN TSPITR with an RMAN-Managed Auxiliary Instance".

• Non-Automated: TSPITR and User-Managed Auxiliary Instance

  This mode of RMAN TSPITR requires you to set up and manage all aspects of the auxiliary instance and some aspects of the TSPITR process. This mode may be appropriate if, for example, you must allocate a different number of channels or change the channel parameters for your user-managed auxiliary instance.

  For more information, see "Performing RMAN TSPITR Using Your Own Auxiliary Instance".

使用TSPITR的一些限制

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

• If there are no archived redo logs or if the database runs in NOARCHIVELOG mode.   #要使用trpitr，数据库必须在archive模式并且target时间点之后的archive log都可用
  

• 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.                  #恢复renamed的表空间到rename操作之前的状态，必须使用rename操作之前的表空间名称

  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.

• If constraints for the tables in tablespace tbs1 are contained in tablespacetbs2, then you cannot recover tbs1 without also recoveringtbs2.#tspitr的表空间必须自包含，或者 将关联的表空间一起恢复
  

• You cannot use TSPITR to recover the current default tablespace.    #不能恢复默认表空间

• You cannot use TSPITR to recover tablespaces containing any of the following objects:#不能包含底层对象，sys用户的对象，undo表空间，或者包含回滚段的TS

  • 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

  • Undo or rollback segments

  • Oracle8-compatible advanced queues with multiple recipients

  • 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.

Limitations of 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 tot.#完成TSPITR后，应立即备份数据库

• If one or more data files in the recovery set have Oracle Managed File (OMF) names and thecompatibility 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 becauseDB_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.                                                  #database compatibility  大于等于10.1
  

TSPITR的一些准备工作

1、选择合适的 Target Time 

可以通过选择一个合适的SCN号，或者timestamp作为TSPITR的 Target Time Target Time是你想要将指定表空间rewind到的那个时间点或者SCN号。
可以通过Flashback Query, Oracle Transaction Query, and Flashback Version Query等查询，来确定你想要效果的一个合适的Target Time

2、确定Recovery set

如果你想要恢复的表空间内，有和其他表空间对象有关联。例如 constraint 和索引在另外一个表空间这些情况，必须解除这些依存关系才能进行TSPITR，保证tipstr的表空间时自包含的。

检验表空间是否自包含：

BEGIN
   DBMS_TTS.TRANSPORT_SET_CHECK('USERS,TOOLS', TRUE,TRUE);
END;
/
SELECT * FROM  TRANSPORT_SET_VIOLATIONS;

如果查询TRANSPORT_SET_VIOLATIONS没有返回结果，则表空间自包含。如果表空间不是自包含的，可以使用以下方法达到让表空间自包含。

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. You have the following choices when faced with such a relationship:

• Add the tablespace including the related objects to your recovery set

• Remove the relationship

• Suspend the relationship for the duration of TSPITR

3、确定因TSPITR会被drop掉的对象

将指定表空间rewind到一个之前的指定时间时，我们将丢失在point 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('02-NOV-07:07:03:11','YY-MON-DD:HH24:MI:SS')
ORDER BY TABLESPACE_NAME, CREATION_TIME;

如果有部分对象需要保留，则可以先expdp这部分对象，待TSPITR完成后，再将此部分数据impdp.

Fully Automated RMAN TSPITR

1、检验数据库是否满足、需要进行TSPITR，以及TSPITR能否解决问题

2、选择合适的recovery target time 、 确定recovery set、检验以及处理因tspitr会删除的objects

3、为TSIPTR分配RMAN 通道，或者使用默认通道

4、Run the RECOVER TABLESPACE command, specifying both the UNTIL clause and theAUXILIARY DESTINATION parameter.

Example

RECOVER TABLESPACE users, tools 
  UNTIL LOGSEQ 1300 THREAD 1
  AUXILIARY DESTINATION '/disk1/auxdest';

5、如果TSPITR执行成功，在online表空间之前，备份表空间

For example, enter the following command:

BACKUP TABLESPACE users, tools;

6、online表空间