Use RMAN for Automatic Recovery of table space TSPITR

With the development of Oracle, a complete solution system has been built in the Backup recovery field. In most cases, our backup and restoration requirements can be achieved using the mature Oracle solution. Table space Point In Time Recover (TSPITR) is a recovery scenario that we occasionally use In practice. This article will focus on this issue.
 

-------------------------------------- Split line --------------------------------------

Recommended reading:

RMAN: Configure an archive log deletion policy

Basic Oracle tutorial-copying a database through RMAN

Reference for RMAN backup policy formulation

RMAN backup learning notes

Oracle Database Backup encryption RMAN Encryption

-------------------------------------- Split line --------------------------------------

1. Talk about TSPITR
 
 

Strictly speaking, TSPITR is a relatively fine-grained Incomplete recovery technology. The Restoration Operation we usually see is to restore all tablespaces and data to the same time point, whether it is a fault recovery point or not. TSPITR uses the tablespace as the unit of granularity to separately restore the content of a tablespace to a specific recoverable time point.
 
For example, an Oracle database runs in archive mode and retains a full backup at midnight. At a.m., a misoperation occurred on the data table in a specific tablespace (a single table exclusive tablespace) and the data was damaged. It is required that the tablespace be restored to six o'clock in the morning without harming other data tables.
 
This is a typical application of TSPITR. In actual scenarios, we often want to restore part of the data to the past time point, rather than record the existing data.
 
Currently, the most convenient method for TSPITR is to use RMAN for automatic recovery. There are two prerequisites: one is that the corresponding backup set must exist, and the other is that the tablespace object is "self-contain ", that is, other tablespaces do not include object data related to the tablespace object.
 
The recovery steps and principles are as follows:
 
 

Ü RMAN three object sets: catalog, target, and auxiliary. Aupoliciary is used to assist the target database for various backup and restoration requirements. This database plays a temporary data restoration role in TSPITR;
 
Ü first, complete the data check to determine the backup set and tablespace integrity;
 
Ü the current time is T1. If you need to restore the data to T0. Use RMAN to extract the backup data set and restore it to auxiliary to form a new instance database. Apply the archived log sequence selectively TO restore the auxiliary TO the TO time point;
 
Ü use the expdp tool to export the destination tablespace from auxiliary as a dump object. Note: Restoring auxiliary does not mean rebuilding the target by 100%. Instead, it selects the system running tablespace and the target tablespace;
 
Ü Delete the original tablespace from the target database. Use impdp to import the dump file to the target database;
 
Ü clean up the environment and delete the created aupoliciary database;
 
 

This series of operations can be completed step by step if we use the RMAN command series. The TSPITR operation of automated RMAN is also fully automated for this process. Next we will demonstrate the operation through the experiment.
 
 

2. Prepare the environment
 
 

We chose Oracle 11gR2 for the test.
 
 

SQL> select * from v $ version;
 
BANNER
 
-----------------------------------------
 
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
 
PL/SQL Release 11.2.0.1.0-Production
 
CORE 11.2.0.1.0 Production
 
TNS for Linux: Version 11.2.0.1.0-Production
 
NLSRTL Version 11.2.0.1.0-Production
 
 

SQL> select log_mode from v $ database;
 
LOG_MODE
 
------------
 
ARCHIVELOG
 
 

Create the experiment tablespace and the experiment data table T. Note: Since we are using 11.2.0.1, if you are using sys users for testing, it will cause Bug 12411104: rman duplicate RMAN-05548 when connected to target. To avoid the problem, we switch to the scott user for execution.
 
 

SQL> create tablespace tsptiptbl datafile size 100 m autoextend off
 
2 extent management local uniform size 1 m
 
3 segment space management auto;
 
Tablespace created
 
 

-- Switch to the scott user for testing. This is very important !! (In 11.2.0.1)
 
SQL> conn scott/tiger @ wilson;
 
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as scott
 
 

SQL> create table t tablespace tsptiptbl as select * from dba_objects;
 
Table created
 
 

Retain a backup set.
 
 

BS Key Type LV Size Device Type Elapsed Time Completion Time
 
-------------------------------------------------------------
 
142 Full 1.33g disk 00:02:52 16-FEB-14

BP Key: 142 Status: AVAILABLE Compressed: NO Tag: TAG20140216T191156
 
Piece Name:/u01/flash_recovery_area/WILSON/backupset/2014_02_16/o1_mf_nnndf_TAG20140216T191156_9j177yk1 _. bkp
 
List of Datafiles in backup set 142
 
(Space reasons, omitted ......)
 
 

BS Key Size Device Type Elapsed Time Completion Time
 
-------------------------------------------------------
 
143 21.50 k disk 00:00:00 16-FEB-14

BP Key: 143 Status: AVAILABLE Compressed: NO Tag: TAG20140216T191454
 
Piece Name:/u01/flash_recovery_area/WILSON/backupset/2014_02_16/o1_mf_annnn_TAG20140216T191454_9j17fgd3 _. bkp
 
 

List of Archived Logs in backups set 143
 
Thrd Seq Low SCN Low Time Next SCN Next Time
 
-------------------------------------------------
 
1 497 5398705 16-FEB-14 5398798 16-FEB-14
 
 

BS Key Type LV Size Device Type Elapsed Time Completion Time
 
-------------------------------------------------------------
 
144 Full 9.67 m disk 00:00:04 16-FEB-14

BP Key: 144 Status: AVAILABLE Compressed: NO Tag: TAG20140216T191455
 
Piece Name:/u01/flash_recovery_area/WILSON/autobackup/2014_02_16/o1_mf_s_81_4495_9j17flq9 _. bkp
 
SPFILE encoded ded: Modification time: 16-FEB-14
 
SPFILE db_unique_name: WILSON
 
Control File Included: Ckp SCN: 5398809 Ckp time: 16-FEB-14
 
 

3. misoperations
 
 

The current time, which is represented by the logseq number.
 
 

SQL> select sequence #, status from v $ log;
 
SEQUENCE # STATUS
 
--------------------------
 
502 INACTIVE
 
503 CURRENT
 
501 INACTIVE
 
 

SQL> create table t tablespace TSPTIPTBL as select * from dba_objects;
 
Table created
 
 

SQL> alter system switch logfile;
 
System altered
 
 

SQL> select sequence #, status from v $ log;
 
SEQUENCE # STATUS
 
--------------------------
 
502 INACTIVE
 
503 ACTIVE
 
504 CURRENT
 
 

 

Switch the log to the next log object.
 
 

SQL> alter system switch logfile;
 
System altered
 
 

SQL> select sequence #, status from v $ log;
 
 

SEQUENCE # STATUS
 
--------------------------
 
505 CURRENT
 
503 ACTIVE
 
504 ACTIVE
 
 

SQL> truncate table t;
 
Table truncated
 
 

SQL> alter system switch logfile;
 
System altered
 
 

SQL> select sequence #, status from v $ log;
 
 

SEQUENCE # STATUS
 
--------------------------
 
505 ACTIVE
 
506 CURRENT
 
504 ACTIVE
 
 

The recovery objective is to restore the tablespace TSPTIPTBL to the location where logseq = 504.

For more details, please continue to read the highlights on the next page:

1 2 Next Page