表空间时间点恢复--TSPITR实验

--测试中使用了 set newname的方式来实现tspitr

实验思路：创建一个测试表空间test_tbs，在其上用非sys用户创建一个表，并插入数据；记录下该时间点或者SCN号。

使用 drop table  xxxxx purge 的方式 彻底的删除数据库表。为对比效果，在另外一个表空间users 创建一个对比表，来证明tspitr的作用范围仅仅是recover set 指定的表空间，数据库其他表空间的数据不会受到影响；

SQL> create table test_12(id_no  number,name varchar2(10)) tablespace test_tbs;

Table created.

SQL> insert into test_12 values (1,'A');

1 row created.

SQL> insert into test_12 values (2,'B');

1 row created.

SQL> insert into test_12 values (3,'C');

1 row created.

SQL> commit;

Commit complete.
SQL> create table test_13(id_no number,name varchar2(10)) tablespace users;

Table created.


SQL> insert into  test_13 values (1,'A');

1 row created.

SQL> insert into  test_13 values (2,'B');

1 row created.

SQL> insert into  test_13 values (3,'C');

1 row created.


SQL> COMMIT;

Commit complete.

SQL> select sysdate from dual;

SYSDATE
---------
19-OCT-13

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
2013-10-19 14:37:17 


SQL> conn scott/tiger
Connected.
SQL> select * from test_12;

     ID_NO NAME
---------- ----------
         1 A
         2 B
         3 C



SQL> drop table test_12 purge;

Table dropped.

SQL> select sysdate from dual;

SYSDATE
-------------------
2013-10-19 14:39:26 

#在彻底删除了test_12后，在user表空间创建一个对比表 test_13
SQL> create table test_13(id_no number,name varchar2(10)) tablespace users;

Table created.

SQL> insert into  test_13 values (1,'A');

1 row created.

SQL> insert into  test_13 values (2,'B');

1 row created.

SQL> insert into  test_13 values (3,'C');

1 row created.

SQL> COMMIT;


SQL> select sysdate from dual;

SYSDATE
-------------------
2013-10-19 14:43:01

#也就是说，在2013-10-19 14:37:17 的时候 test_12还存在，在2013-10-19 14:39:26  被彻底删除 而在2013-10-19 14:43:01左右，在users表空间上创建了一个表test_13
#目的：恢复被彻底删除的test_12，但是并不影响在users表空间建立的 test_13，可以选择对test_tbs上使用PITR，恢复到test_12被删除前的时间。
#我们可以选择时间2013-10-19 14:37:17 来作为恢复时间点。
SQL> conn / as sysdba
Connected.
SQL> execute dbms_tts.transport_set_check('TEST_TBS',TRUE);

PL/SQL procedure successfully completed.

SQL> select  * from transport_set_violations;

no rows selected

#要恢复的表空间是自包含的，且创建对象的为scott用户所有，满足tspitr的要求

SQL> select timestamp_to_scn(to_timestamp('2013-10-19 14:37:17','yyyy-mm-dd hh24:mi:ss')) from dual;

TIMESTAMP_TO_SCN(TO_TIMESTAMP('2013-10-1914:37:17','YYYY-MM-DDHH24:MI:SS'))
---------------------------------------------------------------------------
                                                                    1470112


#查看一下，对表test_12的操作的日志存在于哪一个归档。
SQL> select  sequence# ,first_change#,next_change# from v$archived_log where 1470112 between first_change# and next_change#;

 SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ------------- ------------
        37       1468131      1475824

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     37
Next log sequence to archive   39
Current log sequence           39
#可以选择；logseq 38（实际上会恢复到37号归档的最后一条记录），也可以使用until scn来精确的指定。

RMAN> @tspitr.sql

RMAN> run {
2> set newname for datafile '/u01/app/oracle/oradata/orcl/test_tbs.dbf' to '/u01/app/oracle/tspitr_new/test_tbs01.dbf';
3> recover tablespace test_tbs until scn 1470112 auxiliary destination '/u01/app/oracle/tspitr_new';
4> }
executing command: SET NEWNAME

Starting recover at 19-OCT-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=15 device type=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1

Creating automatic instance, with SID='Bmjp'

#输出太多，省略部分

Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/app/oracle/tspitr_new/ORCL/datafile/o1_mf_temp_964f5xr6_.tmp deleted
auxiliary instance file /u01/app/oracle/tspitr_new/ORCL/onlinelog/o1_mf_3_964f5oc9_.log deleted
auxiliary instance file /u01/app/oracle/tspitr_new/ORCL/onlinelog/o1_mf_2_964f5km6_.log deleted
auxiliary instance file /u01/app/oracle/tspitr_new/ORCL/onlinelog/o1_mf_1_964f5f69_.log deleted
auxiliary instance file /u01/app/oracle/tspitr_new/ORCL/datafile/o1_mf_sysaux_964dzro4_.dbf deleted
auxiliary instance file /u01/app/oracle/tspitr_new/ORCL/datafile/o1_mf_undotbs1_964dzrtx_.dbf deleted
auxiliary instance file /u01/app/oracle/tspitr_new/ORCL/datafile/o1_mf_system_964dzsrw_.dbf deleted
auxiliary instance file /u01/app/oracle/tspitr_new/ORCL/controlfile/o1_mf_964dzj0k_.ctl deleted
Finished recover at 19-OCT-13

#完成后，此时，test_tbs的数据文件还处于offline，在online之前，应该先对test_tbs备份
RMAN>backup as compressed backupset tablespace test_tbs

#检查结果
[oracle@linusfay-up oradata]$ cd ./orcl/
[oracle@linusfay-up orcl]$ ls -k
control01.ctl  redo01.log  redo03.log    system01.dbf  test_tbs.dbf   users01.dbf
example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf    undotbs01.dbf
[oracle@linusfay-up orcl]$ ls -l
total 1996420
-rw-r----- 1 oracle oinstall   9814016 Oct 19 15:41 control01.ctl
-rw-r----- 1 oracle oinstall 362422272 Oct 19 15:31 example01.dbf
-rw-r----- 1 oracle oinstall  52429312 Oct 19 15:25 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Oct 19 15:08 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Oct 19 15:40 redo03.log
-rw-r----- 1 oracle oinstall 629153792 Oct 19 15:38 sysaux01.dbf
-rw-r----- 1 oracle oinstall 754982912 Oct 19 15:38 system01.dbf
-rw-r----- 1 oracle oinstall  30416896 Oct 19 15:15 temp01.dbf
-rw-r----- 1 oracle oinstall  20979712 Oct 19 15:12 test_tbs.dbf
-rw-r----- 1 oracle oinstall  99622912 Oct 19 15:40 undotbs01.dbf
-rw-r----- 1 oracle oinstall   5251072 Oct 19 15:31 users01.dbf


SQL> select tablespace_name ,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE
TEST_TBS                       OFFLINE

7 rows selected.


SQL> select name from v$datafiles;
select name from v$datafiles
                 *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/tspitr_new/test_tbs01.dbf

6 rows selected.


SQL> conn scott/tiger
Connected.
SQL> 

SQL> select * from test_12;

     ID_NO NAME
---------- ----------
         1 A
         2 B
         3 C