Oracle Technology: Table space Recovery based on Point-in-time

Tspitr (tablespace point-in-time recovery) is used to restore one or more tablespaces to the state of a point in the past, while other tablespaces remain in place.

Tspitr Related concepts and terminology:

(1) tspitr (tablespace point-in-time Recover). TSPITR is the abbreviation for tablespace point-in-time Recovery, which represents the return of one or more tablespaces to the state of a previous point in time, while others

The table space remains in the existing state.

(2) Tspitr implementation method. When implementing Tablespace point-in-time recovery, you can use either the user-managed Tablespace point-in-time recovery method or the table space Point-in-time recovery managed by Rman.

(3) Dbpitr (Database point-in-time Recovery). DBPITR is the abbreviation for Database Point-in-time recovery, which represents the return of all tablespaces of a database to the state of a previous point in time. Attention

Dbpitr only applies to Archivelog mode.

(4) Primary database (Primary). A primary database is an Oracle database that is used to store application data, also known as a product or target database. When executing TSPITR, the primary database refers to the

The database that contains the tablespace being recovered.

(5) Recovery set (Recovery set). A recovery set is a collection of tablespaces that need to perform TSPITR on the primary database. Note that when the TSPITR is executed on the tablespace of the recovery set, the table space must be a self package

Included.

(6) Secondary databases (auxiliary database). A secondary database is a replica database of the primary database. When the TSPITR is executed, the secondary database is used to restore the recovery set table space to a past point in time. Attention

All physical files for the secondary database are obtained from the primary database backup, and the secondary database must contain the system table space, the UNDO table space, and the backup files for the recovery set table space.

(7) Auxiliary set (auxiliary set). A secondary set is a collection of files required by the secondary database, in addition to the Recovery set table space file. When the TSPITR is executed, the secondary database needs to recover the set table empty

Backup files, backup files in the SYSTEM table space, and spare files for the UNDO table space are also required.

First, TSPITR related basic concepts

1, what is TSPITR?

Tspitr is actually a point-in-time recovery, except that the recovery here is for a single tablespace, using this method to restore one or more of the non-system tablespaces in the database to a certain time in the past and protect

Hold the other table space unchanged.

Oracle can normally open a database only if the SCN is consistent between the control file, the data file header, and the online log file, so it is not possible to recover directly from a database that needs to be tspitr, which leads

Involved in another concept-assisted instance (auxiliary instance)/secondary (auxiliary database)

2. Main database, auxiliary database, auxiliary instance, recovery set, auxiliary set

A primary database is a database that needs to be tspitr.

A secondary database is a copy of the primary database or a subset of it for TSPITR because the TSPITR cannot be performed directly on the primary database.

A secondary instance corresponds to an instance of the secondary database.

A recovery set is a backup of all data files that comprise a tablespace that needs to be tspitr.

The auxiliary set refers to all the other files required for the recovery of the tablespace, including the backup file of the System\undo\temp tablespace, the backup control file, the archive log file, the password file, the parameter file, and so on.

Second, the advantages and disadvantages of tspitr and common situation

1, the advantages of TSPITR

The TSPITR process is performed on the secondary database, so the primary database can be kept running to improve the system ha.

The granularity of the TSPITR is tablespace, reducing the time for recovery.

Tspitr can only perform incomplete restores of a table space, while other tablespaces remain unchanged, reducing data loss.

2, Tspitr's shortcomings

The essence of Tspitr is a point-in-time recovery in which objects created in the tablespace are lost after the restore point in time.

Tspitr need to manually maintain the association of objects between tablespaces is cumbersome

3, the common situation of TSPITR

Some DML was executed incorrectly and a commit was made;

There is a logical inconsistency in the data;

It takes a long time to fully recover a large database, but only a subset of it is needed.

General process of TSPITR (take tspitr of the users table space for example)

1, consider tspitr need to restore to the point of time or SCN, and its gain and loss

You can query by ts_pitr_objects_to_be_dropped that all objects that are created later than recovery time are lost.

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= ' USERS ' and Creation_time > To_date (' 02-nov-02:07:03:11 ', ' yy-mon-dd:hh24:mi:ss ')

Order by Tablespace_name, Creation_time;

2. Create auxiliary instance (NT platform needs to be created by Oradim), prepare the related directory of secondary database.

3. Copy related backup sets and auxiliary sets

4. Modify the pfile of auxiliary concentration

Note: Db_name cannot be modified, control_files must point to a control file that is backed up at the same time as the backup set. Lock_name_space=auxiliary_sid, Lock_name_space allows the secondary database and the primary database to be

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

Db_name is the same (must be set when TSPITR on the same machine as the primary database). There are also two optional parameters Db_file_name_convert,log_file_name_convert specifying automatic conversion at mount time

The name of the file in the Controlfile can also be modified manually after the mount with "alter DATABASE RENAME file ' <...> ' to ' <...> ';"

5, secondary database boot to mount state

6. Boot the secondary database to the Mount state to mount the clone database with ALTER DATABASE. The secondary database is then detached from the archive mode, and all the data files will be offline.

7, the system, undo and the Users table space data file online

8, based on the time point or SCN recovery

Recover database using Backup Controlfile until Time/change

Alter database open resetlogs;

9, check whether the purpose of recovery has been implemented, to check the relationship and deal with related issues.

10. Set the Users table space in the secondary database to read-only and export their metadata (metadata)

SQL >alter tablespace users Read only;

11, the use of Transport_tablespace method to export the users table space of the original data.

Several parameters to be set

User_id= "Sys/pwd as SYSDBA"--must be performed as Sysdba

Transport_tablespace=y

Tablespaces=users

File= "D:\users.dmp"

12, processing the main database on the Users Table Space Association, and then delete it or offline for recovery

13. Copy the data files and metadata files from the users table space in the secondary database to the main database

14. Use the binary format (Bin command) when transferring with FTP.

Performing imp on the primary database

User_id= "Sys/pwd as SYSDBA"--must be performed as Sysdba

Transport_tablespace=y

Tablespaces=users

File= "/oracle/users.dmp"

Datafile= "/oracle/oradata/users01.dbf", "/oracle/oradata/users02.dbf"

15. Set the users table space to read-write mode

If the original users table space is offline for recovery words to first online.

16. Restore the association relationship in the main database

17. Backup The Users table space

18, the original backup after the TSPITR is invalid, it is strongly recommended to immediately back up the Tspitr table space.

As you can see from the above process, TSPITR is essentially a data file that puts all tablespaces except the system table space, the Undo table space, and the table space you want to restore to offline and then restores, opens the database, and then enters

Line transport.

Iv. How to check and process the association relationship

When some objects in a tspitr tablespace are associated with objects in other tablespaces (such as indexes, constraints, and so on), these relationships need to be processed before they can be tspitr.

Oracle primarily examines the effectiveness of the following:

1, tables, clusters, and their indexes (partitions or not partitions) must all be included in the recovery set.

2, all partitions of the partition object must be included in the recovery set

3. Tables containing parent-child relationships (foreign KEY constraints) must all be included in the recovery set

4. LOB objects and referencing tables must all be included in the recovery set.

By querying Sys.ts_pitr_check, you can find the associated objects and then handle them accordingly.

Take the Users table space as an example:

SELECT *from SYS. Ts_pitr_checkwhere (ts1_name= ' users ' and Ts2_name <> ' users ') or (ts1_name<> ' users ' and ts2_name = ' users '));

In general, there are three ways to deal with associated objects.

(1) Include the table space containing the associated object into the TSPITR scope

(2) Removing the relationship

(3) Temporarily suspend the relationship.

There is also a part of the TSPITR will be lost after the object, has been mentioned before, this part should also be handled accordingly, lest lost.

V. TSPITR matters needing attention

After Tspitr, the table space is not required to be all returned to the primary database, and only the specific data tables are returned.