Oracle Rman Advanced Recovery Overview (iii) How to recover table space points in time

Use tablespace point-in-time Restore (TSPITR) to restore one or more non-system tablespaces to a point in time different from the rest of the database. This is a bit like the flashback. For example, the user mistakenly deleted 3 sheets, we can use TSPITR recovery.

Look at the Tspitr workflow first, as shown in the following illustration:

(1) Using Target's backup set restore data file on the secondary instance

(2) Recover data files using Target's archive on the secondary library

(3) Export the relevant data on the auxiliary library

(4) Modify the control file of the main library

(5) To import the auxiliary library on the export file.

A few related definitions:

Auxiliary instance (auxiliary instance): The temporary instance that we created, Rman can use this instance to perform Tspitr, after completing TSPITR operation, can delete auxiliary instance.

Secondary (Auxiliary database): a copy or subset of the primary database for temporary recovery of tablespace.

Primary (Primary database): A database that requires TSPITR.

Recovery Set (Recovery set): A tablespace or data file that is restored to a point-in-time table space, and the system Tablespace data file cannot be part of a recovery set.

Auxiliary set (auxiliary set): A set of other target database files that need to perform tspitr. The auxiliary set includes backup control files, rollback and undo segment tablespace Data files, system table space data files, online redo logs for the secondary database, and an optional temporary tablespace in the secondary database.

Destination instance (target instance): Contains the tablespace to be recovered

First, the implementation of automatic TSPITR

1. Prepare for Tspitr

You need to complete some steps before you start executing tspitr.

(1) Determine the time point of the restore

This is the most critical factor. We need to take this action seriously because recovery of tablespace is a one-time process if the recovery directory is not used. If the point-in-time of the recovery is incorrectly identified, you cannot start over. If the recovery directory is used, there is no such restriction.

(2) Determine if the object in the delivery set is self-contained

You should use the Ts_pitr_check view to ensure that the recovery set is complete and identify all other tablespaces that might be used. First you need to check the Ts_pitr_check view to make sure there are no other related tablespaces. For example, we check the Dave table space, the sample code is as follows:

/* Formatted on 2010/7/7 17:10:00 (QP5 v5.115.810.9015) * *

SELECT Obj1_owner,

Obj1_name,

Obj1_type,

Reason

From Sys.ts_pitr_check

WHERE (Ts1_name in (' BL ') and Ts2_name not in (' BL '))

OR (Ts1_name not in (' BL ') and Ts2_name in (' BL '))

If there is no conflict, no rows are returned. If there is a conflict, you will see the rows of each conflict that is described. If there is a conflict, we also need to restore the associated table space.

(3) Save objects or data that may be lost

If we bring the Dave Tablespace back to some time before, any changes after that time, such as new objects, updates, inserts or deletions, will be lost. Losing these objects may not be a problem, but assuming that we need to save the data, we need to export the data that will be saved, or copy the data to another location in the database. Oracle provides a view ts_pitr_objects_to_bedropped that lists all objects that will be lost during the recovery operation. Use this view to determine the state of an object in a tablespace after it has been restored.

Sql> Col owner Format A10

Sql> Col name format A10

Sql> alter session set nls_date_format= ' Yyyy-mm-dd hh24:mi:ss ';

Sql> SELECT * from ts_pitr_objects_to_be_dropped WHERE tablespace_name = ' BL ';

OWNER NAME Creation_time Tablespace_name

---------- ---------- ------------------- ------------------------------

BL bl 2010-07-07 19:24:18 bl

2, the implementation of the actual TSPITR

Oracle Database 10g will perform an automatic tspitr for us, which means it will create a secondary instance. In this case, we only need to connect to the target database and the optional Recovery directory (if any) and execute the Recover tablespace command. RMAN will complete the rest of the work for us.

The following shows an example of using the Recover tablespace command to recover the BL table space. We use the optional auxiliary destination to indicate where Rman and Oracle should create files associated with the secondary database. Use this parameter to make this recovery a custom Tspitr with an Automation instance. If this parameter is not used, TSPITR is called a fully automatic tspitr recovery.

It is important to note that if you use the auxiliary destination parameter, you should have created the target directory, and Oracle must be able to write to the target directory. A slash (/or/) without a suffix in the target pathname will cause TSPITR to fail if it contains a slash, and an error message cannot accurately describe the problem. The order is as follows:

Recover tablespace BL until Time "to_date (' 2010-7-7 20:38:18 ', ' yyyy-mm-dd hh24:mi:ss ')" Auxiliary destination ' F:/BL '

There are a few caveats before executing this command, because TSPITR will use the existing backup set and archive file to create the secondary database, so before executing the command you need to verify that the target database is backed up and archived, and that the control files are backed up.

Rman> Recover tablespace BL until Time "to_date (' 2010-7-7 20:40:18 ', ' yyyy-mm-dd hh24:mi:ss ')" Auxiliary destination ' F :/bl ';

Started recover in July-July-10

Replacing the recovery directory with the target database control file

Allocated channel: Ora_disk_1

Channel ora_disk_1:sid=145 Device Type =disk

RMAN-05026: Warning: Assume that the following table space set applies to a specified point in time

Table Space List requires an UNDO segment

Table Space SYSTEM

Table Space UNDOTBS1

Use sid= ' iefs ' to create an automatic instance--This is the secondary database name that the system automatically creates

Initialization parameters for use by automatic instances:

Db_name=bl

Db_unique_name=iefs_tspitr_bl

compatible=11.2.0.0.0

db_block_size=8192

db_files=200

sga_target=280m

Processes=50

Db_create_file_dest=f:/bl

log_archive_dest_1= ' LOCATION=F:/BL '

#No Auxiliary parameter file used

Start Automatic instance BL

Oracle instance started

System global Zone total 292933632 bytes

Fixed Size 1374164 bytes

Variable Size 100665388 bytes

Database buffers 184549376 bytes

Redo buffers 6344704 bytes

An automatic instance has been created

Run Transport_set_check on recovery set table space

Transport_set_check has completed successfully

Contents of Memory Script:

{

# Set requested point at time

Set until Time "to_date (' 2010-7-7 20:40:18 ', ' yyyy-mm-dd hh24:mi:ss ')";

# Restore the Controlfile

Restore Clone controlfile;

# mount the Controlfile

SQL clone ' ALTER DATABASE mount clone database ';

# Archive Current Online log

SQL ' alter system archive log current ';

# Avoid unnecessary autobackups for structural changes during TSPITR

SQL ' begin Dbms_backup_restore. Autobackupflag (FALSE); end; ';