An example of restoring table space data using TSPITR in Oracle

In fact, the TSPITR method is to restore the entire tablespace, no matter how many tables or objects exist in the tablespace, as long as they are self-contained tablespaces, this method can be used for recovery.

In fact, the TSPITR method is to restore the entire tablespace, no matter how many tables or objects exist in the tablespace, as long as they are self-contained tablespaces, this method can be used for recovery.

There are two prerequisites:

1. The corresponding backup set must exist.

2. tablespace objects are sub-contained, that is, other tablespaces do not include object data related to the tablespace objects (independent of each other)

The recovery steps and principles are as follows:

First, complete the data check to determine the backup set and tablespace integrity;

Time Point;

;

. Database;

.

The following is an example of an experiment:

-- Create a test user zlm and grant permissions

SQL> create user zlm identified by zlm;

User created.

SQL> grant dba to zlm;

Grant succeeded.

-- Create a test table space tspitr

SQL> create tablespace tspitr datafile '/data/oradata/ora10g/tspitr01.dbf' size 100 m autoextend off extent management local uniform size 1 m segment space management auto;

Tablespace created.

-- Change zlm's default tablespace to tspitr.

SQL> alter user zlm default tablespace tspitr;

User altered.

SQL> show user

USER is ""

SQL> conn zlm/zlm @ ora10g213

Connected.

SQL>!

-- Create an RMAN backup set

[Oracle @ bak ~] $ Rman target/

Recovery Manager: Release 10.2.0.1.0-Production on Fri Dec 26 16:44:00 2014

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Connected to target database: ORA10G (DBID = 4175411955)

RMAN> backup as compressed backupset database format'/u01/orabackup/backupsets/full_ora10g _ % U' plus archive log format'/u01/orabackup/backupsets/arc_ora10g _ % U 'delete all input;

Starting backup at 26-DEC-14

Current log archived

Using target database control file instead of recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: sid = 138 devtype = DISK

Channel ORA_DISK_1: starting compressed archive log backupset

Channel ORA_DISK_1: specifying archive log (s) in backup set

Input archive log thread = 1 sequence = 30 recid = 30 stamp = 867343597

Channel ORA_DISK_1: starting piece 1 at 26-DEC-14

Channel ORA_DISK_1: finished piece 1 at 26-DEC-14

Piece handle =/u01/orabackup/backupsets/arc_ora10g_13pr577g_1_1 tag = TAG20141226T164639 comment = NONE

Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04

Channel ORA_DISK_1: deleting archive log (s)

Archive log filename =/oracle/flash_recovery_area/ORA10G/archivelog/2014_12_26/o1_mf_1_30_b9t83f1s _. arc recid = 30 stamp = 867343597

Finished backup at 26-DEC-14

Starting backup at 26-DEC-14

Using channel ORA_DISK_1

Channel ORA_DISK_1: starting compressed full datafile backupset

Channel ORA_DISK_1: specifying datafile (s) in backupset

Input datafile fno = 00001 name =/data/oradata/ora10g/system01.dbf

Input datafile fno = 00003 name =/data/oradata/ora10g/sysaux01.dbf

Input datafile fno = 00002 name =/data/oradata/ora10g/undotbs01.dbf

Input datafile fno = 00005 name =/data/oradata/ora10g/example01.dbf

Input datafile fno = 00006 name =/data/oradata/ora10g/tspitr01.dbf

Input datafile fno = 00004 name =/data/oradata/ora10g/users01.dbf

Channel ORA_DISK_1: starting piece 1 at 26-DEC-14

Channel ORA_DISK_1: finished piece 1 at 26-DEC-14

Piece handle =/u01/orabackup/backupsets/full_ora10g_14pr577l_1_1 tag = TAG20141226T164644 comment = NONE

Channel ORA_DISK_1: backup set complete, elapsed time: 00:02:47

Finished backup at 26-DEC-14

Starting backup at 26-DEC-14

Current log archived

Using channel ORA_DISK_1

Channel ORA_DISK_1: starting compressed archive log backupset

Channel ORA_DISK_1: specifying archive log (s) in backup set

Input archive log thread = 1 sequence = 31 recid = 31 stamp = 867343772

Channel ORA_DISK_1: starting piece 1 at 26-DEC-14

Channel ORA_DISK_1: finished piece 1 at 26-DEC-14

Piece handle =/u01/orabackup/backupsets/arc_ora10g_15pr57ct_1_1 tag = TAG20141226T164933 comment = NONE

Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Channel ORA_DISK_1: deleting archive log (s)

Archive log filename =/oracle/flash_recovery_area/ORA10G/archivelog/2014_12_26/o1_mf_1_31_b9t88wnv _. arc recid = 31 stamp = 867343772

Finished backup at 26-DEC-14

Starting Control File and SPFILE Autobackup at 26-DEC-14

Piece handle =/u01/orabackup/backupsets/ora10g-c-4175411955-20141226-05.ctl comment = NONE

Finished Control File and SPFILE Autobackup at 26-DEC-14

RMAN> exit

Recovery Manager complete.

-- Connect to the test user zlm to view the current log

[Oracle @ bak ~] $ Sqlplus/nolog

SQL * Plus: Release 10.2.0.1.0-Production on Fri Dec 26 16:50:46 2014

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Connected:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production

With the Partitioning, OLAP and Data Mining options

SQL> conn zlm/zlm @ ora10g213

Connected.

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

SEQUENCE # STATUS

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

32 CURRENT

30 INACTIVE

31 ACTIVE

-- Create test table t1 and switch logs several times

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> alter system switch logfile;

System altered.

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

SEQUENCE # STATUS

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

32 ACTIVE

33 CURRENT

31 ACTIVE

SQL> alter system switch logfile;

System altered.

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

SEQUENCE # STATUS

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

32 ACTIVE

33 ACTIVE

34 CURRENT -- data is still

SQL> select count (*) from t1;

COUNT (*)

----------

50382

-- Perform the truancate operation on the table to simulate incorrect operations

SQL> truncate table t1;

Table truncated.

SQL> select count (*) from t1;

COUNT (*)

----------

0

SQL> alter system switch logfile;

System altered.

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

SEQUENCE # STATUS

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

35 CURRENT -- truncate and then switch the log again. The CURRENT log is 35

33 ACTIVE

34 ACTIVE

SQL>!