Table space-based Incomplete recovery of RMAN (TSPITR)

Table space-based Incomplete recovery of RMAN (TSPITR)

Table space-based Incomplete recovery of RMAN (TSPITR)

• Table Space Time Point recovery (TSPITR): Concept

You can execute TSPITR to quickly restore one or more tablespaces to a previous time.

Executing TSPITR will not affect the status of other tablespaces or objects in the database.

Using RMAN automatic tablespace time point recovery (TSPITR) can quickly restore one or more tablespaces in Oracle DB to a previous time without affecting the statuses of other tablespaces and objects in the database.

• Table Space Time Point recovery (TSPITR): Terminology

Target time: the time point at which the tablespace is restored or SCN

Recovery set: data files that constitute the tablespace to be restored

Secondary Set: It is required to execute TSPITR on the recovery set, but is not a data file of the recovery set. The auxiliary set usually includes:

-SYSTEM tablespace

-Restored tablespace

-Temporary tablespace

Secondary goal: storage file disk location

The following terms are used to discuss TSPITR:

Target time: the time point at which the tablespace will be restored or the system change number (SCN) during the execution of TSPITR ).

Recovery set: the data file that makes up the tablespace to be restored.

Secondary Set: It is necessary to execute TSPITR on the Restoration set, but it is not a data file of the Restoration set. The auxiliary set usually includes:

-SYSTEM tablespace copy

-Data files that contain restoration segments from the target instance

-In some cases, temporary tablespace used during database object export from the secondary instance

Secondary objective: to store any auxiliary set data files, control files, and online logs of the secondary instance on the disk during TSPITR execution. After TSPITR is executed, you can delete the files stored in the secondary target.

• Tablespace time point recovery: Architecture

The following TSPITR entities are displayed in the chart:

Target Database: contains the tablespace to be restored

Control File: provide backup information to RMAN

Backup set: from the target database, used as the source to reconstruct the tablespace

Archive redo logs: from the target database, used as the source to reconstruct the tablespace

Secondary instance: The Oracle DB instance used for restoration during restoration

RMAN performs the following steps during recovery at the tablespace time point:

1. Restore the backup control file at a certain time point before the target time to the secondary instance. Restore the data files of the recovery set to the target database, and restore the data files of the secondary set to the secondary instance.

2. Restore the restored data file to the specified time point.

3. Export the dictionary metadata of objects in the restored tablespace to the target database.

4. Run the SWITCH command on the target database to direct the control file of the target database to the recovered data files in the secondary instance.

5. Import the dictionary metadata in the secondary instance to the target instance to access the recovered object.

• Execute basic RMAN TSPITR

Note: The tablespace to be restored must be self-contained.

Fully automatic TSPITR

-Specify the secondary target.

-RMAN manages all aspects of TSPITR.

-This is the recommended method.

Custom TSPITR using auto-help instances

-This is based on Fully Automatic TSPITR execution.

-Customize the file location.

-Specify the initialization parameters.

-Specify the channel configuration.

Use the TSPITR of your auxiliary instance

-Configure and manage secondary instances.

The following options are available when TSPITR is executed:

Fully automatic TSPITR: Specifies the secondary target. RMAN manages all aspects of the TSPITR operation. This is the simplest way to execute TSPITR. We recommend that you use this method unless it is explicitly stated that

After TSPITR, control the location of the recovery set, or control the location of the secondary set file during the execution of TSPITR, or control the channel configuration of the secondary instance or other aspects.

Custom TSPITR using auto-help instances: TSPITR is based on Fully Automatic TSPITR, and may still use the secondary target. You can customize one or more aspects of its behavior, such as auxiliary set files or recovery sets.

File Location. You can specify initialization parameters or channel configurations for auxiliary instances created and managed by RMAN.

Use the TSPITR of your secondary instance: Set, start, stop, and clear the secondary instance used in TSPITR. In addition, you can manage the TSPITR process by using methods in the Custom TSPITR of the auto-help instance.

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

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

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

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

Automatic Execution of TSPITR using RMAN:

1. Work Environment

At 12:21:32 SCOTT @ prod> create table emp1 as select * from emp;

Table created.

Elapsed: 00:00:00. 65

At 14:07:41 SCOTT @ prod> insert into emp1 select * from emp where rownum <5;

4 rows created.

Elapsed: 00:00:00. 02

At 14:08:18 SCOTT @ prod> commit;

Commit complete.

Elapsed: 00:00:00. 04

At 14:08:19 SCOTT @ prod> select count (*) from emp1;

COUNT (*)

----------

18

Elapsed: 00:00:00. 02

Emp1 table data is deleted by mistake:

14:08:20 SCOTT @ prod> truncate table emp1;

Table truncated.

Elapsed: 00:00:00. 32

14:08:30 SCOTT @ prod> insert into emp1 select * from emp where empno = 7788;

1 row created.

Elapsed: 00:00:00. 03

At 14:08:46 SCOTT @ prod> commit;

Commit complete.

Elapsed: 00:00:00. 06

At 14:08:48 SCOTT @ prod> select * from emp1;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7788 scott analyst 7566 19-APR-87 3000 20

Elapsed: 00:00:00. 08

14:08:55 SCOTT @ prod> update emp1 set empno = 8888;

1 row updated.

Elapsed: 00:00:00. 02

At 14:09:06 SCOTT @ prod> commit;

Commit complete.

Elapsed: 00:00:00. 01

At 14:09:08 SCOTT @ prod> select * from emp1;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

8888 scott analyst 7566 19-APR-87 3000 20

Elapsed: 00:00:00. 01

14:09:12 SCOTT @ prod & gt;

2. perform pre-recovery detection

Check whether tablespace is self-contained:

At 10:39:16 SCOTT @ prod> CONN/as sysdba

Connected.

10:39:25 SYS @ prod> exec DBMS_TTS.TRANSPORT_SET_CHECK ('tbs1', TRUE );

PL/SQL procedure successfully completed.

Elapsed: 00:01:58. 45

10:41:43 SYS @ prod> select * from TRANSPORT_SET_VIOLATIONS t;

No rows selected

Confirm the data file to be restored:

14:13:34 SYS @ prod> select * from dba_data_files t where T. TABLESPACE_NAME = 'tbs1 ';

FILE_NAME

Bytes ------------------------------------------------------------------------------------------------------------------------

FILE_ID TABLESPACE_NAME bytes blocks status RELATIVE_FNO AUT MAXBYTES MAXBLOCKS

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

INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE _

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

/U01/app/oracle/oradata/prod/tbs1.dbf

6 TBS1 10485760 1280 AVAILABLE 6 NO 0 0

0 9437184 1152 ONLINE

Elapsed: 00:00:00. 02

14:14:42 SYS @ prod>

Check whether the auxiliary set system undo and CONTROLFILE are included:

14:14:38 SYS @ prod> select file_name name from dba_data_files t where T. TABLESPACE_NAME IN ('system', 'undotbs1 ')

14:14:40 2 union

14:14:41 3 select max (name) from v $ controlfile t;

NAME

Bytes -------------------------------------------------------------------------------------------

/U01/app/oracle/oradata/prod/control01.ctl

/U01/app/oracle/oradata/prod/system01.dbf

/U01/app/oracle/oradata/prod/undotbs01.dbf

Elapsed: 00:00:00. 03

Objects that may be lost after the TSP check:

14:14:42 SYS @ prod> select * from TS_PITR_OBJECTS_TO_BE_DROPPED T where T. OWNER = 'tbs1 ';

No rows selected

Elapsed: 00:00:00. 17

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

1 2 Next Page