【TSPITR】RMAN表空间基于时间点的自动恢复

  1. 云栖社区>
  2. 博客>
  3. 正文

【TSPITR】RMAN表空间基于时间点的自动恢复

小麦苗 2015-05-26 09:28:37 浏览643
展开阅读全文

RMANTSPITR--RMAN表空间基于时间点的自动恢复

1.1  BLOG文档结构图

wps6C5C.tmp 

 

 

1.2  前言部分

 

1.2.1  导读

各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~:

TSPITR表空间基于时间点的自动恢复

logminer的简单应用

 

本文如有错误或不完善的地方请大家多多指正,ITPUB留言或QQ皆可,您的批评指正是我写作的最大动力。

1.2.2  实验环境介绍

 

目标库:11.2.0.3  RHEL6.5

ORACLE_SID: ora11g

 

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on 星期日 5月 24 15:19:35 2015

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

15:19:35 SQL> select * from v$version;

 

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE    11.2.0.3.0      Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

 

已用时间:  00: 00: 00.01

15:19:37 SQL> archive log list;

数据库日志模式            存档模式

自动存档             启用

存档终点            USE_DB_RECOVERY_FILE_DEST

最早的联机日志序列     36

下一个存档日志序列   38

当前日志序列           38

15:19:53 SQL>

15:20:37 SQL> show parameter name

 

NAME                                 TYPE        VALUE

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

db_file_name_convert                 string

db_name                              string      ora11g

db_unique_name                       string      ora11g

global_names                         boolean     FALSE

instance_name                        string      ora11g

lock_name_space                      string

log_file_name_convert                string

processor_group_name                 string

service_names                        string      ora11g

15:20:41 SQL>

 

 

1.2.3  本文简介

 

OCP考完已经接近快1年了,本来TSPITR是属于OCP的内容,应该早把这个实验做了,可是由于各种原因一直没有做这个实验,上周CUUG的公开课上讲的就是TSPITR的相关内容,好吧,借着周末就把这个实验做了吧,blog后也附上了一些与OCP有关的试题,用于网友自测。

关于TSPITR的视频参考:http://yunpan.cn/QCwUAI9bn7g7w  提取码:af2d

 

1.3  TSPITR的相关知识点归纳

1.3.1  TSPITR简介

 TSPITR(表空间时间点恢复)用于将一个或多个表空间恢复到过去某个时间点的状态,而其他表空间仍然保持现有状态。

TSPITR 相关的概念和术语:

(1) TSPITR (Tablespace Point-In-Time Recover)。TSPITR 是表空间时间点恢复的英文缩写格式,它表示将一个或多个表空间恢复到过去时间点的状态,而其他表空间仍然保持现有状态

(2) TSPITR 实现方法。当实现表空间时间点恢复时,既可以使用用户管理的表空间时间点恢复方法,也可以使用RMAN 管理的表空间时间点恢复。

(3) DBPITR (Database Point-In-Time Recovery)。DBPITR 是数据库时间点恢复的英文缩写格式,它表示将数据库的所有表空间恢复到过去时间点的状态。注意,DBPITR 只适用于ARCHIVELOG 模式。

(4) 主数据库(Primary Database)。主数据库是指用于存放应用系统数据的Oracle 数据库,也被称为产品数据库或目标数据库。当执行TSPITR 时,主数据库是指包含被恢复表空间的数据库。

(5) 恢复集(Recovery Set)。恢复集是指在主数据库上需要执行 TSPITR 的表空间集合。注意,当在恢复集的表空间上执行TSPITR 时,要求这些表空间必须是自包含的。

(6) 辅助数据库(Auxiliary Database)。辅助数据库是主数据库的一个副本数据库。当执行TSPITR 时,辅助数据库用于将恢复集表空间恢复到过去时间点。注意,辅助数据库的所有物理文件都是从主数据库备份中取得,并且辅助数据库必须包含SYSTEM 表空间、UNDO 表空间以及恢复集表空间的备份文件。

(7) 辅助集(Auxiliary Set)。辅助集是指辅助数据库所需要的、除了恢复集表空间文件之外的其他文件集合。当执行 TSPITR 时,辅助数据库除了需要恢复集表空间的备份文件之外,还需要备份控制文件、SYSTEM 表空间的备份文件、UNDO 表空间的备分文件。

1.3.2  何时使用TSPITR

? TSPITR 可在下列情况下使用:

恢复因错误的TRUNCATE TABLE语句而丢失的数据

从表逻辑损坏中恢复

撤消只影响部分数据库的批作业或DML 语句的结果

将逻辑方案恢复到与物理数据库其余部分不同的时间点

 

? TSPITR 使用可移动表空间和数据泵,提供了以下新功能和特性:

– TSPITR 可用于恢复已删除的表空间

可反复执行TSPITR 恢复到表空间联机之前的多个时间点,而无需使用恢复目录

 

在Oracle Database 11g发行版2 之前,TSPITR 使用导出和导入进行处理。TSPITR 现在使用可移动表空间和数据泵。由于底层技术的这一变化,现在可使用TSPITR 来恢复已删除的表空间。此外,可反复执行TSPITR 恢复到不同的时间点,而无需使用恢复目录。

 

 

 

1.4  实验部分

 

1.4.1  源库做备份操作

 

 

 

[oracle@rhel6_lhr ~]$ ORACLE_SID=ora11g 

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on 星期日 5月 24 19:27:25 2015

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

已连接到空闲例程。

 

19:27:25 SQL> startup

ORACLE 例程已经启动。

 

Total System Global Area  409194496 bytes

Fixed Size                  2228864 bytes

Variable Size             327159168 bytes

Database Buffers           71303168 bytes

Redo Buffers                8503296 bytes

数据库装载完毕。

数据库已经打开。

19:27:42 SQL> exit

从 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开

 

 

[oracle@rhel6_lhr ~]$ ORACLE_SID=ora11g

 

[oracle@rhel6_lhr ~]$ rman target /

 

恢复管理器: Release 11.2.0.3.0 - Production on 星期日 5月 24 19:36:47 2015

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

已连接到目标数据库: ORA11G (DBID=4270446895)

 

RMAN> backup database;

 

启动 backup 于 2015-05-24 19:36:53

使用目标数据库控制文件替代恢复目录

分配的通道: ORA_DISK_1

通道 ORA_DISK_1: SID=142 设备类型=DISK

通道 ORA_DISK_1: 正在启动全部数据文件备份集

通道 ORA_DISK_1: 正在指定备份集内的数据文件

输入数据文件: 文件号=00001 名称=/u01/app/oracle/oradata/ora11g/system01.dbf

输入数据文件: 文件号=00002 名称=/u01/app/oracle/oradata/ora11g/sysaux01.dbf

输入数据文件: 文件号=00005 名称=/u01/app/oracle/oradata/ora11g/example01.dbf

输入数据文件: 文件号=00003 名称=/u01/app/oracle/oradata/ora11g/undotbs01.dbf

输入数据文件: 文件号=00004 名称=/u01/app/oracle/oradata/ora11g/users01.dbf

输入数据文件: 文件号=00007 名称=/u01/app/oracle/oradata/ora11g/DWII_CNY_BK_F_01.dbf

输入数据文件: 文件号=00008 名称=/u01/app/oracle/oradata/ora11g/DWII_DPA_F_01.dbf

输入数据文件: 文件号=00009 名称=/u01/app/oracle/oradata/ora11g/DWII_DPA_I_01.dbf

输入数据文件: 文件号=00010 名称=/u01/app/oracle/oradata/ora11g/DWII_DPA_S_01.dbf

输入数据文件: 文件号=00011 名称=/u01/app/oracle/oradata/ora11g/DWII_SOR_F_01.dbf

输入数据文件: 文件号=00012 名称=/u01/app/oracle/oradata/ora11g/DWII_SOR_I_01.dbf

输入数据文件: 文件号=00013 名称=/u01/app/oracle/oradata/ora11g/DW_USER.dbf

输入数据文件: 文件号=00014 名称=/u01/app/oracle/oradata/ora11g/SQCHECK.dbf

输入数据文件: 文件号=00015 名称=/u01/app/oracle/oradata/ora11g/SD_CNY_D_01.dbf

输入数据文件: 文件号=00016 名称=/u01/app/oracle/oradata/ora11g/SD_CNY_F_01.dbf

输入数据文件: 文件号=00017 名称=/u01/app/oracle/oradata/ora11g/SD_DPA_D_01.dbf

输入数据文件: 文件号=00018 名称=/u01/app/oracle/oradata/ora11g/SD_DPA_F_01.dbf

输入数据文件: 文件号=00019 名称=/u01/app/oracle/oradata/ora11g/SD_SORT_T_01.dbf

输入数据文件: 文件号=00020 名称=/u01/app/oracle/oradata/ora11g/DWII_FXDM_F_01.dbf

输入数据文件: 文件号=00021 名称=/u01/app/oracle/oradata/ora11g/SD_SOR_T_01.dbf

输入数据文件: 文件号=00006 名称=/u01/app/oracle/oradata/ora11g/aa.dbf

通道 ORA_DISK_1: 正在启动段 1 于 2015-05-24 19:36:54

通道 ORA_DISK_1: 已完成段 1 于 2015-05-24 19:37:19

段句柄=/u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_05_24/o1_mf_nnndf_TAG20150524T193654_bp3fypc1_.bkp 标记=TAG20150524T193654 注释=NONE

通道 ORA_DISK_1: 备份集已完成, 经过时间:00:00:25

完成 backup 于 2015-05-24 19:37:19

 

启动 Control File and SPFILE Autobackup 于 2015-05-24 19:37:19

段 handle=/u01/app/oracle/flash_recovery_area/ORA11G/autobackup/2015_05_24/o1_mf_s_880573039_bp3fzhgy_.bkp comment=NONE

完成 Control File and SPFILE Autobackup 于 2015-05-24 19:37:22

 

RMAN> list backupset;

 

 

备份集列表

===================

 

 

BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间          

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

49      Full    1.18G      DISK        00:00:20     2015-05-24 19:37:14

        BP 关键字: 49   状态: AVAILABLE  已压缩: NO  标记: TAG20150524T193654

段名:/u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_05_24/o1_mf_nnndf_TAG20150524T193654_bp3fypc1_.bkp

  备份集 49 中的数据文件列表

  文件 LV 类型 Ckp SCN    Ckp 时间            名称

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

  1       Full 1596722    2015-05-24 19:36:54 /u01/app/oracle/oradata/ora11g/system01.dbf

  2       Full 1596722    2015-05-24 19:36:54 /u01/app/oracle/oradata/ora11g/sysaux01.dbf

  3       Full 1596722    2015-05-24 19:36:54 /u01/app/oracle/oradata/ora11g/undotbs01.dbf

  4       Full 1596722    2015-05-24 19:36:54 /u01/app/oracle/oradata/ora11g/users01.dbf

  5       Full 1596722    2015-05-24 19:36:54 /u01/app/oracle/oradata/ora11g/example01.dbf

  6       Full 1596722    2015-05-24 19:36:54 /u01/app/oracle/oradata/ora11g/aa.dbf

  7       Full 1596722    2015-05-24 19:36:54 /u01/app/oracle/oradata/ora11g/DWII_CNY_BK_F_01.dbf

  8       Full 1596722    2015-05-24 19:36:54 /u01/app/oracle/oradata/ora11g/DWII_DPA_F_01.dbf

  9       Full 1596722    2015-05-24 19:36:54 /u01/app/oracle/oradata/ora11g/DWII_DPA_I_01.dbf

  10      Full 1596722    2015-05-24 19:36:54 /u01/app/oracle/oradata/ora11g/DWII_DPA_S_01.dbf

  11      Full 1596722    2015-05-24 19:36:54 /u01/app/oracle/oradata/ora11g/DWII_SOR_F_01.dbf

  12      Full 1596722    2015-05-24 19:36:54 /u01/app/oracle/oradata/ora11g/DWII_SOR_I_01.dbf

  13      Full 1596722    2015-05-24 19:36:54 /u01/app/oracle/oradata/ora11g/DW_USER.dbf

  14      Full 1596722    2015-05-24 19:36:54 /u01/app/oracle/oradata/ora11g/SQCHECK.dbf

  15      Full 1596722    2015-05-24 19:36:54 /u01/app/oracle/oradata/ora11g/SD_CNY_D_01.dbf

  16      Full 1596722    2015-05-24 19:36:54 /u01/app/oracle/oradata/ora11g/SD_CNY_F_01.dbf

  17      Full 1596722    2015-05-24 19:36:54 /u01/app/oracle/oradata/ora11g/SD_DPA_D_01.dbf

  18      Full 1596722    2015-05-24 19:36:54 /u01/app/oracle/oradata/ora11g/SD_DPA_F_01.dbf

  19      Full 1596722    2015-05-24 19:36:54 /u01/app/oracle/oradata/ora11g/SD_SORT_T_01.dbf

  20      Full 1596722    2015-05-24 19:36:54 /u01/app/oracle/oradata/ora11g/DWII_FXDM_F_01.dbf

  21      Full 1596722    2015-05-24 19:36:54 /u01/app/oracle/oradata/ora11g/SD_SOR_T_01.dbf

 

BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间          

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

50      Full    9.67M      DISK        00:00:01     2015-05-24 19:37:20

        BP 关键字: 50   状态: AVAILABLE  已压缩: NO  标记: TAG20150524T193719

段名:/u01/app/oracle/flash_recovery_area/ORA11G/autobackup/2015_05_24/o1_mf_s_880573039_bp3fzhgy_.bkp

  包含的 SPFILE: 修改时间: 2015-05-24 19:27:36

  SPFILE db_unique_name: ORA11G

  包括的控制文件: Ckp SCN: 1596737      Ckp 时间: 2015-05-24 19:37:19

 

RMAN> report schema;

 

db_unique_name 为 ORA11G 的数据库的数据库方案报表

 

永久数据文件列表

===========================

文件大小 (MB) 表空间           回退段数据文件名称

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

1    770      SYSTEM               ***     /u01/app/oracle/oradata/ora11g/system01.dbf

2    580      SYSAUX               ***     /u01/app/oracle/oradata/ora11g/sysaux01.dbf

3    285      UNDOTBS1             ***     /u01/app/oracle/oradata/ora11g/undotbs01.dbf

4    96       USERS                ***     /u01/app/oracle/oradata/ora11g/users01.dbf

5    345      EXAMPLE              ***     /u01/app/oracle/oradata/ora11g/example01.dbf

6    5        AA                   ***     /u01/app/oracle/oradata/ora11g/aa.dbf

7    10       DWII_CNY_BK_F_01     ***     /u01/app/oracle/oradata/ora11g/DWII_CNY_BK_F_01.dbf

8    10       DWII_DPA_F_01        ***     /u01/app/oracle/oradata/ora11g/DWII_DPA_F_01.dbf

9    10       DWII_DPA_I_01        ***     /u01/app/oracle/oradata/ora11g/DWII_DPA_I_01.dbf

10   10       DWII_DPA_S_01        ***     /u01/app/oracle/oradata/ora11g/DWII_DPA_S_01.dbf

11   10       DWII_SOR_F_01        ***     /u01/app/oracle/oradata/ora11g/DWII_SOR_F_01.dbf

12   10       DWII_SOR_I_01        ***     /u01/app/oracle/oradata/ora11g/DWII_SOR_I_01.dbf

13   10       DW_USER              ***     /u01/app/oracle/oradata/ora11g/DW_USER.dbf

14   10       SQCHECK              ***     /u01/app/oracle/oradata/ora11g/SQCHECK.dbf

15   10       SD_CNY_D_01          ***     /u01/app/oracle/oradata/ora11g/SD_CNY_D_01.dbf

16   10       SD_CNY_F_01          ***     /u01/app/oracle/oradata/ora11g/SD_CNY_F_01.dbf

17   10       SD_DPA_D_01          ***     /u01/app/oracle/oradata/ora11g/SD_DPA_D_01.dbf

18   10       SD_DPA_F_01          ***     /u01/app/oracle/oradata/ora11g/SD_DPA_F_01.dbf

19   10       SD_SORT_T_01         ***     /u01/app/oracle/oradata/ora11g/SD_SORT_T_01.dbf

20   10       DWII_FXDM_F_01       ***     /u01/app/oracle/oradata/ora11g/DWII_FXDM_F_01.dbf

21   10       SD_SOR_T_01          ***     /u01/app/oracle/oradata/ora11g/SD_SOR_T_01.dbf

 

临时文件列表

=======================

文件大小 (MB) 表空间           最大大小 (MB) 临时文件名称

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

1    83       TEMP                 32767       /u01/app/oracle/oradata/ora11g/temp01.dbf

 

RMAN>

 

RMAN> list backupset summary;

 

 

备份列表

===============

关键字     TY LV S 设备类型 完成时间            段数 副本数 压缩标记

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

49      B  F  A DISK        2015-05-24 19:37:14 1       1       NO         TAG20150524T193654

50      B  F  A DISK        2015-05-24 19:37:20 1       1       NO         TAG20150524T193719

 

RMAN>

 

RMAN>

 

 

1.4.2  建立测试表并做truncate误操作

AA表空间下建立2个表,并建立一个索引在users表空间中,注意做误操作之前先切换一下日志

 

 

19:38:44 SQL> alter system switch logfile;

 

系统已更改。

 

已用时间:  00: 00: 00.01

19:38:50 SQL> alter system switch logfile;

 

系统已更改。

 

已用时间:  00: 00: 00.01

19:38:54 SQL> CREATE TABLE LHR.TSPITR_TEST  TABLESPACE AA   AS SELECT * FROM SCOTT.EMP;

 

表已创建。

 

已用时间:  00: 00: 00.11

19:39:45 SQL> CREATE TABLE LHR.TSPITR_TEST1 TABLESPACE AA   AS SELECT * FROM SCOTT.EMP  where rownum

 

表已创建。

 

已用时间:  00: 00: 00.01

19:39:52 SQL> create index lhr.TSPITR_test_index  on LHR.TSPITR_TEST(empno) tablespace users;

 

索引已创建。

 

已用时间:  00: 00: 00.00

19:39:58 SQL> select * from  LHR.TSPITR_TEST ;

 

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO

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

      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20

      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30

      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30

      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20

      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30

      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30

      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10

      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20

      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10

      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30

      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20

      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30

      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20

      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10

 

已选择14行。

 

已用时间:  00: 00: 00.05

19:40:16 SQL> alter system switch logfile;

 

系统已更改。

 

已用时间:  00: 00: 00.71

19:40:50 SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME

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

         1          1         64   52428800        512          1 YES INACTIVE               1596806 2015-05-24 19:38:50      1596809 2015-05-24 19:38:54

         2          1         65   52428800        512          1 YES ACTIVE                 1596809 2015-05-24 19:38:54      1596912 2015-05-24 19:40:50

         3          1         66   52428800        512          1 NO  CURRENT                1596912 2015-05-24 19:40:50   2.8147E+14

 

已用时间:  00: 00: 00.01

19:40:58 SQL> truncate table LHR.TSPITR_TEST ;

 

表被截断。

 

已用时间:  00: 00: 00.09

19:41:15 SQL>

19:41:15 SQL>  select * from  LHR.TEST_TSPITR;

 

未选定行

 

已用时间:  00: 00: 00.00

19:41:38 SQL>

 

19:41:38 SQL>  select * from  v$logfile;

 

    GROUP# STATUS  TYPE    MEMBER                                                                                                                                                                               

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

         3         ONLINE  /u01/app/oracle/oradata/ora11g/redo03.log                                                                                                                                            

         2         ONLINE  /u01/app/oracle/oradata/ora11g/redo02.log                                                                                                                                            

         1         ONLINE  /u01/app/oracle/oradata/ora11g/redo01.log                                                                                                                                            

 

已用时间:  00: 00: 00.00

 

19:42:24 SQL>

19:42:24 SQL> CREATE TABLE LHR.TSPITR_TEST2 TABLESPACE AA   AS SELECT * FROM SCOTT.EMP;

 

表已创建。

 

已用时间:  00: 00: 00.01

19:43:56 SQL> CREATE TABLE LHR.TSPITR_TEST3 TABLESPACE AA   AS SELECT * FROM SCOTT.EMP;

 

表已创建。

 

已用时间:  00: 00: 00.02

19:44:04 SQL> CREATE TABLE LHR.TSPITR_TEST4 TABLESPACE AA   AS SELECT * FROM SCOTT.EMP;

 

表已创建。

 

已用时间:  00: 00: 00.01

19:44:10 SQL>

 

 

 

这里可以看出执行误操作的时候sql的时间是19:41:15 左右,而且由于我们是测试库,没有什么用户操作,所以redo很少,从v$log中可以看出,误操作肯定在66号日志中

 

 

1.4.3  采用logminer找回误删除的时间点

生产环境下为了尽量减少对数据的影响,我们需要准确定位到误操作的时间点,所以可以采用logminer来找回误操作的时间点,当然可以查询其他的视图来获取,但是都不是很准确,比如查询dba_objects中的LAST_DDL_TIMEdba_tab_modifications中的timestamp,也可以查询v$sql,可以根据情况来决定,但是logminer是通用的,也是很准确的,关于logminer本来也很早就想写了,一直没写,后边有机会再写吧,今天我们就暂时看看怎么使用它吧。

 

 

 

采用logminer来找回误操作的时间点:

 

 

19:44:10 SQL> execute dbms_logmnr.add_logfile('/u01/app/oracle/oradata/ora11g/redo03.log',dbms_logmnr.new);

 

PL/SQL 过程已成功完成。

 

已用时间:  00: 00: 00.08

19:45:02 SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

 

PL/SQL 过程已成功完成。

 

已用时间:  00: 00: 00.10

19:45:09 SQL> select a.SCN,a.TIMESTAMP,a.SQL_REDO from v$logmnr_contents A where table_name='TSPITR_TEST' and OPERATION='DDL' order by a.SCN;

 

       SCN TIMESTAMP           SQL_REDO

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

  1596943 2015-05-24 19:41:15 truncate table LHR.TSPITR_TEST ;

 

已用时间:  00: 00: 00.26

19:45:23 SQL>

 

 

可以看出执行TRUNCATE的时间为2015-05-24 19:41:15, 我们恢复的时候恢复到19:41:00

 

 

1.4.4  执行TSPITR之前的检查

1.4.4.1  检查是否自包含

 

 

19:45:23 SQL> BEGIN

19:47:07   2      SYS.DBMS_TTS.TRANSPORT_SET_CHECK('AA', TRUE, TRUE);

19:47:07   3  END;

19:47:07   4  /

 

PL/SQL 过程已成功完成。

 

已用时间:  00: 00: 02.86

19:47:10 SQL> SELECT * FROM SYS.TRANSPORT_SET_VIOLATIONS;

 

VIOLATIONS

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

ORA-39907: 索引 LHR.TSPITR_TEST_INDEX (在表空间 USERS 中) 指向表 LHR.TSPITR_TEST (在表空间 AA 中)。

 

已用时间:  00: 00: 00.00

 

 

 

我们还可以这样去检查:

select * from SYS.TS_PITR_CHECK a WHERE a.ts1_name='AA';

wps6C6C.tmp 

 

说明,有一个索引 LHR..TSPITR_TEST_INDEXusers表空间中,那么我们删除这个索引,等待恢复完成后再重建该索引。

 

19:47:29 SQL> drop index  LHR.TSPITR_TEST_INDEX;

 

索引已删除。

 

已用时间:  00: 00: 00.02

19:48:46 SQL> BEGIN

19:49:11   2      SYS.DBMS_TTS.TRANSPORT_SET_CHECK('AA', TRUE, TRUE);

19:49:11   3  END;

19:49:11   4  /

 

PL/SQL 过程已成功完成。

 

已用时间:  00: 00: 02.64

19:49:14 SQL> SELECT * FROM SYS.TRANSPORT_SET_VIOLATIONS;

 

未选定行

 

已用时间:  00: 00: 00.00

19:49:20 SQL>

 

 

至此,自包含问题已解决。

1.4.4.2  检查哪些对象执行TSPITR后将被删除

 

select * from SYS.TS_PITR_OBJECTS_TO_BE_DROPPED A WHERE a.tablespace_name='AA' and a.creation_time>='2015-05-24 19:41:00';

wps6C6D.tmp 

则说明若执行TSPITR后,AA表空间中的表TSPITR_TEST2TSPITR_TEST3TSPITR_TEST4将被删除,那么为了保存这些对象我们将其利用expdp导出备份,等待tspitr执行完毕后再导入回来即可。

 

 

[oracle@rhel6_lhr ~]$ ORACLE_SID=ora11g

[oracle@rhel6_lhr ~]$ expdp lhr/lhr  directory=DATA_PUMP_DIR dumpfile=exptable_tspitrlhr.dmp TABLES=TSPITR_TEST2,TSPITR_TEST3,TSPITR_TEST4 LOGFILE=exptable_tspitrlhr.log

 

Export: Release 11.2.0.3.0 - Production on 星期日 5月 24 19:53:34 2015

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

启动 "LHR"."SYS_EXPORT_TABLE_01":  lhr/******** directory=DATA_PUMP_DIR dumpfile=exptable_tspitrlhr.dmp TABLES=TSPITR_TEST2,TSPITR_TEST3,TSPITR_TEST4 LOGFILE=exptable_tspitrlhr.log

正在使用 BLOCKS 方法进行估计...

处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA

使用 BLOCKS 方法的总估计: 192 KB

处理对象类型 TABLE_EXPORT/TABLE/TABLE

. . 导出了 "LHR"."TSPITR_TEST2"                        8.570 KB      14 行

. . 导出了 "LHR"."TSPITR_TEST3"                        8.570 KB      14 行

. . 导出了 "LHR"."TSPITR_TEST4"                        8.570 KB      14 行

已成功加载/卸载了主表 "LHR"."SYS_EXPORT_TABLE_01"

******************************************************************************

LHR.SYS_EXPORT_TABLE_01 的转储文件集为:

  /u01/app/oracle/admin/ora11g/dpdump/exptable_tspitrlhr.dmp

作业 "LHR"."SYS_EXPORT_TABLE_01" 已于 19:53:39 成功完成

 

[oracle@rhel6_lhr ~]$

 

[oracle@rhel6_lhr ~]$  cd  /u01/app/oracle/admin/ora11g/dpdump

[oracle@rhel6_lhr dpdump]$ ll -h

total 69M

-rw-r----- 1 oracle asmadmin  116 Mar 13 14:24 dp.log

-rw-r----- 1 oracle asmadmin 144K May 24 19:53 exptable_tspitrlhr.dmp

-rw-r--r-- 1 oracle asmadmin 1.2K May 24 19:53 exptable_tspitrlhr.log

-rw-r--r-- 1 oracle asmadmin 4.1K May 19 15:59 imp_exptest.log

-rwxr-xr-x 1 oracle oinstall  69M May 19 15:50 lhrsql20150515.dmp

[oracle@rhel6_lhr dpdump]$

 

 

1.4.5  执行TSPITR

 

 

 

RMAN> RECOVER TABLESPACE AA UNTIL time "to_date('2015-05-24 19:41:00','YYYY-MM-DD HH24:mi:ss')" auxiliary destination '/u01/auxdest';

 

启动 recover 于 2015-05-24 19:57:30

使用通道 ORA_DISK_1

RMAN-05026: 警告: 假定以下表空间集适用于指定的时间点

 

表空间列表要求具有 UNDO 段

表空间 SYSTEM

表空间 UNDOTBS1

 

使用 SID='lsll' 创建自动实例

 

供自动实例使用的初始化参数:

db_name=ORA11G

db_unique_name=lsll_tspitr_ORA11G

compatible=11.2.0.0.0

db_block_size=8192

db_files=200

sga_target=280M

processes=50

db_create_file_dest=/u01/auxdest

log_archive_dest_1='location=/u01/auxdest'

#No auxiliary parameter file used

 

 

启动自动实例 ORA11G

 

Oracle 实例已启动

 

系统全局区域总计     292278272 字节

 

Fixed Size                     2227744 字节

Variable Size                100663776 字节

Database Buffers             184549376 字节

Redo Buffers                   4837376 字节

自动实例已创建

对恢复集表空间运行 TRANSPORT_SET_CHECK

TRANSPORT_SET_CHECK 已成功完成

 

内存脚本的内容:

{

# set requested point in time

set until  time "to_date('2015-05-24 19:41:00','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;';

}

正在执行内存脚本

 

正在执行命令: SET until clause

 

启动 restore 于 2015-05-24 19:57:37

分配的通道: ORA_AUX_DISK_1

通道 ORA_AUX_DISK_1: SID=58 设备类型=DISK

 

通道 ORA_AUX_DISK_1: 正在开始还原数据文件备份集

通道 ORA_AUX_DISK_1: 正在还原控制文件

通道 ORA_AUX_DISK_1: 正在读取备份片段 /u01/app/oracle/flash_recovery_area/ORA11G/autobackup/2015_05_24/o1_mf_s_880573039_bp3fzhgy_.bkp

通道 ORA_AUX_DISK_1: 段句柄 = /u01/app/oracle/flash_recovery_area/ORA11G/autobackup/2015_05_24/o1_mf_s_880573039_bp3fzhgy_.bkp 标记 = TAG20150524T193719

通道 ORA_AUX_DISK_1: 已还原备份片段 1

通道 ORA_AUX_DISK_1: 还原完成, 用时: 00:00:01

输出文件名=/u01/auxdest/ORA11G/controlfile/o1_mf_bp3h5ks3_.ctl

完成 restore 于 2015-05-24 19:57:38

 

sql 语句: alter database mount clone database

 

sql 语句: alter system archive log current

 

sql 语句: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

 

内存脚本的内容:

{

# set requested point in time

set until  time "to_date('2015-05-24 19:41:00','YYYY-MM-DD HH24:mi:ss')";

plsql

declare

  sqlstatement       varchar2(512);

  offline_not_needed exception;

  pragma exception_init(offline_not_needed, -01539);

begin

  sqlstatement := 'alter tablespace '||  'AA' ||' offline immediate';

  krmicd.writeMsg(6162, sqlstatement);

  krmicd.execSql(sqlstatement);

exception

  when offline_not_needed then

    null;

end; >>>;

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile  1 to new;

set newname for clone datafile  3 to new;

set newname for clone datafile  2 to new;

set newname for clone tempfile  1 to new;

set newname for datafile  6 to

"/u01/app/oracle/oradata/ora11g/aa.dbf";

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile  1, 3, 2, 6;

switch clone datafile all;

}

正在执行内存脚本

 

正在执行命令: SET until clause

 

sql 语句: alter tablespace AA offline immediate

 

正在执行命令: SET NEWNAME

 

正在执行命令: SET NEWNAME

 

正在执行命令: SET NEWNAME

 

正在执行命令: SET NEWNAME

 

正在执行命令: SET NEWNAME

 

临时文件 1 在控制文件中已重命名为 /u01/auxdest/ORA11G/datafile/o1_mf_temp_%u_.tmp

 

启动 restore 于 2015-05-24 19:57:43

使用通道 ORA_AUX_DISK_1

 

通道 ORA_AUX_DISK_1: 正在开始还原数据文件备份集

通道 ORA_AUX_DISK_1: 正在指定从备份集还原的数据文件

通道 ORA_AUX_DISK_1: 将数据文件 00001 还原到 /u01/auxdest/ORA11G/datafile/o1_mf_system_%u_.dbf

通道 ORA_AUX_DISK_1: 将数据文件 00003 还原到 /u01/auxdest/ORA11G/datafile/o1_mf_undotbs1_%u_.dbf

通道 ORA_AUX_DISK_1: 将数据文件 00002 还原到 /u01/auxdest/ORA11G/datafile/o1_mf_sysaux_%u_.dbf

通道 ORA_AUX_DISK_1: 将数据文件 00006 还原到 /u01/app/oracle/oradata/ora11g/aa.dbf

通道 ORA_AUX_DISK_1: 正在读取备份片段 /u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_05_24/o1_mf_nnndf_TAG20150524T193654_bp3fypc1_.bkp

通道 ORA_AUX_DISK_1: 段句柄 = /u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_05_24/o1_mf_nnndf_TAG20150524T193654_bp3fypc1_.bkp 标记 = TAG20150524T193654

通道 ORA_AUX_DISK_1: 已还原备份片段 1

通道 ORA_AUX_DISK_1: 还原完成, 用时: 00:01:25

完成 restore 于 2015-05-24 19:59:08

 

数据文件 1 已转换成数据文件副本

输入数据文件副本 RECID=5 STAMP=880574348 文件名=/u01/auxdest/ORA11G/datafile/o1_mf_system_bp3h5qps_.dbf

数据文件 3 已转换成数据文件副本

输入数据文件副本 RECID=6 STAMP=880574348 文件名=/u01/auxdest/ORA11G/datafile/o1_mf_undotbs1_bp3h5qpy_.dbf

数据文件 2 已转换成数据文件副本

输入数据文件副本 RECID=7 STAMP=880574348 文件名=/u01/auxdest/ORA11G/datafile/o1_mf_sysaux_bp3h5qpw_.dbf

 

内存脚本的内容:

{

# set requested point in time

set until  time "to_date('2015-05-24 19:41:00','YYYY-MM-DD HH24:mi:ss')";

# online the datafiles restored or switched

sql clone "alter database datafile  1 online";

sql clone "alter database datafile  3 online";

sql clone "alter database datafile  2 online";

sql clone "alter database datafile  6 online";

# recover and open resetlogs

recover clone database tablespace  "AA", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

正在执行内存脚本

 

正在执行命令: SET until clause

 

sql 语句: alter database datafile  1 online

 

sql 语句: alter database datafile  3 online

 

sql 语句: alter database datafile  2 online

 

sql 语句: alter database datafile  6 online

 

启动 recover 于 2015-05-24 19:59:09

使用通道 ORA_AUX_DISK_1

 

正在开始介质的恢复

 

线程 1 序列 63 的归档日志已作为文件 /u01/app/oracle/flash_recovery_area/ORA11G/archivelog/2015_05_24/o1_mf_1_63_bp3g2b6k_.arc 存在于磁盘上

线程 1 序列 64 的归档日志已作为文件 /u01/app/oracle/flash_recovery_area/ORA11G/archivelog/2015_05_24/o1_mf_1_64_bp3g2g9r_.arc 存在于磁盘上

线程 1 序列 65 的归档日志已作为文件 /u01/app/oracle/flash_recovery_area/ORA11G/archivelog/2015_05_24/o1_mf_1_65_bp3g6280_.arc 存在于磁盘上

线程 1 序列 66 的归档日志已作为文件 /u01/app/oracle/flash_recovery_area/ORA11G/archivelog/2015_05_24/o1_mf_1_66_bp3h5qcs_.arc 存在于磁盘上

归档日志文件名=/u01/app/oracle/flash_recovery_area/ORA11G/archivelog/2015_05_24/o1_mf_1_63_bp3g2b6k_.arc 线程=1 序列=63

归档日志文件名=/u01/app/oracle/flash_recovery_area/ORA11G/archivelog/2015_05_24/o1_mf_1_64_bp3g2g9r_.arc 线程=1 序列=64

归档日志文件名=/u01/app/oracle/flash_recovery_area/ORA11G/archivelog/2015_05_24/o1_mf_1_65_bp3g6280_.arc 线程=1 序列=65

归档日志文件名=/u01/app/oracle/flash_recovery_area/ORA11G/archivelog/2015_05_24/o1_mf_1_66_bp3h5qcs_.arc 线程=1 序列=66

介质恢复完成, 用时: 00:00:01

完成 recover 于 2015-05-24 19:59:10

 

数据库已打开

 

内存脚本的内容:

{

# make read only the tablespace that will be exported

sql clone 'alter tablespace  AA read only';

# create directory for datapump import

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/u01/auxdest''";

# create directory for datapump export

sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/u01/auxdest''";

}

正在执行内存脚本

 

sql 语句: alter tablespace  AA read only

 

sql 语句: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/auxdest''

 

sql 语句: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/auxdest''

 

正在执行元数据导出...

   EXPDP> 启动 "SYS"."TSPITR_EXP_lsll": 

   EXPDP> 处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK

   EXPDP> 处理对象类型 TRANSPORTABLE_EXPORT/TABLE

   EXPDP> 处理对象类型 TRANSPORTABLE_EXPORT/TABLE_STATISTICS

   EXPDP> 处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

   EXPDP> 已成功加载/卸载了主表 "SYS"."TSPITR_EXP_lsll"

   EXPDP> ******************************************************************************

   EXPDP> SYS.TSPITR_EXP_lsll 的转储文件集为:

   EXPDP>   /u01/auxdest/tspitr_lsll_94503.dmp

   EXPDP> ******************************************************************************

   EXPDP> 可传输表空间 AA 所需的数据文件:

   EXPDP>   /u01/app/oracle/oradata/ora11g/aa.dbf

   EXPDP> 作业 "SYS"."TSPITR_EXP_lsll" 已于 19:59:58 成功完成

导出完毕

 

 

内存脚本的内容:

{

# shutdown clone before import

shutdown clone immediate

# drop target tablespaces before importing them back

sql 'drop tablespace  AA including contents keep datafiles';

}

正在执行内存脚本

 

数据库已关闭

数据库已卸装

Oracle 实例已关闭

 

sql 语句: drop tablespace  AA including contents keep datafiles

 

正在执行元数据导入...

   IMPDP> 已成功加载/卸载了主表 "SYS"."TSPITR_IMP_lsll"

   IMPDP> 启动 "SYS"."TSPITR_IMP_lsll": 

   IMPDP> 处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK

   IMPDP> 处理对象类型 TRANSPORTABLE_EXPORT/TABLE

   IMPDP> 处理对象类型 TRANSPORTABLE_EXPORT/TABLE_STATISTICS

   IMPDP> 处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

   IMPDP> 作业 "SYS"."TSPITR_IMP_lsll" 已于 20:00:15 成功完成

导入完毕

 

 

内存脚本的内容:

{

# make read write and offline the imported tablespaces

sql 'alter tablespace  AA read write';

sql 'alter tablespace  AA offline';

# enable autobackups after TSPITR is finished

sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';

}

正在执行内存脚本

 

sql 语句: alter tablespace  AA read write

 

sql 语句: alter tablespace  AA offline

 

sql 语句: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

 

删除自动实例

自动实例已删除

已删除辅助实例文件 /u01/auxdest/ORA11G/datafile/o1_mf_temp_bp3h8mj7_.tmp

已删除辅助实例文件 /u01/auxdest/ORA11G/onlinelog/o1_mf_3_bp3h8k01_.log

已删除辅助实例文件 /u01/auxdest/ORA11G/onlinelog/o1_mf_2_bp3h8hk7_.log

已删除辅助实例文件 /u01/auxdest/ORA11G/onlinelog/o1_mf_1_bp3h8goh_.log

已删除辅助实例文件 /u01/auxdest/ORA11G/datafile/o1_mf_sysaux_bp3h5qpw_.dbf

已删除辅助实例文件 /u01/auxdest/ORA11G/datafile/o1_mf_undotbs1_bp3h5qpy_.dbf

已删除辅助实例文件 /u01/auxdest/ORA11G/datafile/o1_mf_system_bp3h5qps_.dbf

已删除辅助实例文件 /u01/auxdest/ORA11G/controlfile/o1_mf_bp3h5ks3_.ctl

完成 recover 于 2015-05-24 20:00:17

 

RMAN>

 

 

 

 

 

1.4.6  online表空间并且导入丢失的对象

执行完恢复之后,表空间处于offline状态,需要将表空间online

 

19:49:20 SQL> select * from  LHR.TSPITR_TEST;

select * from  LHR.TSPITR_TEST

                   *

第 1 行出现错误:

ORA-00376: 此时无法读取文件 6

ORA-01110: 数据文件 6: '/u01/app/oracle/oradata/ora11g/aa.dbf'

 

 

已用时间:  00: 00: 00.00

20:01:49 SQL> alter tablespace aa online;

 

表空间已更改。

 

已用时间:  00: 00: 00.05

20:02:14 SQL> select * from  LHR.TSPITR_TEST ;

 

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO

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

      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20

      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30

      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30

      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20

      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30

      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30

      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10

      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20

      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10

      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30

      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20

      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30

      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20

      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10

 

已选择14行。

 

已用时间:  00: 00: 00.04

20:02:53 SQL> select * from  LHR.TSPITR_TEST2 ;

select * from  LHR.TSPITR_TEST2

                   *

第 1 行出现错误:

ORA-00942: 表或视图不存在

 

 

已用时间:  00: 00: 00.00

20:03:59 SQL>

 

数据已经恢复,可是LHR.TSPITR_TEST2丢失,下边我们来导入备份的数据:

[oracle@rhel6_lhr dpdump]$ impdp lhr/lhr  directory=DATA_PUMP_DIR dumpfile=exptable_tspitrlhr.dmp TABLES=TSPITR_TEST2,TSPITR_TEST3,TSPITR_TEST4 LOGFILE=imptable_tspitrlhr.log  

 

Import: Release 11.2.0.3.0 - Production on 星期日 5月 24 20:05:31 2015

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

已成功加载/卸载了主表 "LHR"."SYS_IMPORT_TABLE_01"

启动 "LHR"."SYS_IMPORT_TABLE_01":  lhr/******** directory=DATA_PUMP_DIR dumpfile=exptable_tspitrlhr.dmp TABLES=TSPITR_TEST2,TSPITR_TEST3,TSPITR_TEST4 LOGFILE=imptable_tspitrlhr.log

处理对象类型 TABLE_EXPORT/TABLE/TABLE

处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA

. . 导入了 "LHR"."TSPITR_TEST2"                        8.570 KB      14 行

. . 导入了 "LHR"."TSPITR_TEST3"                        8.570 KB      14 行

. . 导入了 "LHR"."TSPITR_TEST4"                        8.570 KB      14 行

作业 "LHR"."SYS_IMPORT_TABLE_01" 已于 20:05:34 成功完成

 

[oracle@rhel6_lhr dpdump]$

 

 

20:03:59 SQL> select * from  LHR.TSPITR_TEST2 ;

 

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO

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

      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20

      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30

      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30

      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20

      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30

      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30

      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10

      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20

      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10

      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30

      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20

      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30

      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20

      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10

 

已选择14行。

 

已用时间:  00: 00: 00.01

20:05:55 SQL>

 

 

可以看到数据已经恢复。

 

 

 

 

1.5  TSPITR有关的OCP试题部分

 

Which options must you configure while performing an automated Tablespace Point-in-Time Recovery (TSPITR) by using Recovery Manager (RMAN)?  使用恢复管理器(RMAN)执行自动表空间时间点恢复(TSPITR)的同时,你必须配置哪些选项?

A. New channels for restore and recovery tasks

B. New name for the data files of the tablespace

C. Auxiliary name for the data files of the tablespace

D. Auxiliary destinations for an auxiliary set of data files用于辅助数据文件集的辅助目的地

http://docs.oracle.com/cd/B19306_01/backup.102/b14191/rcmtspit.htm

Answer: D

 

 

 

353.True or false: tablespace point-in-time recovery is possible only with RMAN.

真的还是假的:表空间时间点只能用RMAN恢复

A. True

B. False

Answer: B

Tablespace point-in-time recovery has been done in Oracle for some time, using various means and methods. RMAN simply automates and simplifies the process for you. Oracle表空间的时间点恢复使用各种手段和方法已经有一段时间了。 RMAN仅为你自动化和简化过程。

 

 

354.Which command is used to begin a tablespace point-in-time recovery?

A. Restore tablespace

B. Recover tablespace

C. Tablespace recover

D. Recover to time

E. recover datafile

Answer: B

 

Why should you back up a duplicated tablespace after a TSPITR is complete?

A. The tablespace cannot be duplicated or restored to any point in time after the duplication.

B. The tablespace cannot be duplicated or restored to the point in time before the duplication.

C. The entire database cannot be restored after a TSPITR, so a backup is required.

D. You cannot bring the tablespace online until its been backed up.

E. There is no requirement to do so, as RMAN will back up the tablespace after the TSPITR.

Answer: B

 

In what state are the datafiles of a tablespace after a TSPITR has been successfully completed?

A. The datafiles have an ONLINE status.

B. The datafiles have an OFFLINE status.

C. The datafiles have an ONLINE status and are in hot backup mode prepared for an online backup.

D. The datafiles have an OFFLINE status and are in hot backup mode for an online backup.

E. The datafiles are in STANDBY mode.

Answer: A

该题答案有误,应该选择B

The recovery set tablespaces are left offline for you to back up and then bring back online. These last steps follow Oracle's recommendation and best practice of backing up recovered tablespaces as soon as TSPITR completes.

 

Because of a logical corruption in the EMPLOYES tables, you want to perform Tablespace Point-in-Time Recovery (TSPITR) to recover the table. Before you started the TSPITR process, you queried the TS_PITR_CHECK view and you realized that the table has a referential constraint with DEPARTMENTS that exists in another tablespace, MASTERTBS.

Which two actions will permit the TSPITR to work? (Choose two.)

A.Taking the MASTERTBS tablespace offline

B.Dropping the relationship between the tables

C.Adding the MASTERTBS tablespace to the recovery set

D.Putting the MASTERTBS tablespace in read-only mode

Answer:BC

 

What  view  would  you  use  to  determine if  a  given  tablespace  is  fully  self-contained for  the  execution  of  a tablespace point-in-time recovery?你会用什么视图以确定是否一个给定的表空间是完全独立的用于执行一个表空间时间点恢复?

A. TS_CHECK

B. TPITR_CHECK

C. TS_PITR_CHECK tablespace point-in-time recovery

D. CHECK_TSPITR

E. PITR_TS_CHECK

Answer: C

The TS_PITR_CHECK view is used to determine if a given tablespace (or tablespaces) can be independently transported or if there are other dependencies that will require the transport of additional tablespaces. TS_PITR_CHECK视图是用来确定是否一个给定的表空间(或表)可以独立地传输或是否有其他的依赖关系,这种关系将需要额外的表空间的传输。

 

Which of the following restrictions are not true with respect to tablespace point-in-time recovery? (Choose all that apply .)关于表空间时间点恢复,下列哪些限制不是真的?

A. The target database must be in NOARCHIVELOG mode. 目标数据库必须在非归档模式。

B. No backup is required of the database before you perform a TSPITR.当执行一个表空间时间点恢复之前,不需要数据库备份

C. Y ou must have all archived redo logs generated since the last backup up to the point to which you want to restore the transport set. 您必须拥有所有归档重做日志,该日志生成于自上次备份到你想还原传输集的点。

D.  If  you  rename  a  tablespace,  you  can  not  perform  a  TSPITR  to  any  point  in  time  before  that  rename operation occurred. 如果你重命名一个表空间,你不能执行TSPITR到在重命名操作发生之前任何一点。

E.  If  you  have  tables  in  tablespace_1  that  have  associated  constraints  in  tablespace_2,  then  you  must transport both tablespaces. 如果你有表在表空间1,而表空间1与表空间2有相关约束,那么你必须传输两个表空间。

Answer:  A,B

 

If there is a constraint between two objects in two different tablespaces, you must perform a TSPITR between the two tablespaces. As an alternative, you could disable or drop the constraint. You may not be able to reenable the constraint with validation after the TSPITR, however. 如果有两个不同的表空间中的两个对象之间的约束,你必须执行两个表之间的TSPITR作为替代,你可以禁用或删除该约束。然而您可能无法TSPITR后重新启用验证约束。

After you perform a TSPITR, you should back up the tablespace/datafile. If you do not, you will not be able to do a TSPITR to any point in time before the original TSPITR您执行表空间时间点恢复后,你应该备份的表空间/数据文件。如果你不这样做,在原表空间时间点恢复之前,你就不能进行一个表空间时间点恢复到任何时间点。

After a TSPITR each datafile associated with the TSPITR will be offline. Oracle recommends you back up the datafile before bringing it online.

 

 

Because of a logical corruption in your production database, you wanted to perform Tablespace Point in Time Recovery  (TSPITR).  But  before  you  start  the  recovery,  you  queried  the TS_PITR_OBJECTS_TO_BE_DROPPED view and realized that there are a large number of objects that would be dropped when you start the recovery by using this method.  由于生产数据库逻辑损坏,你想要执行表空间时间点恢复(TSPITR)。但在开始恢复之前,您查询了表空间时间点恢复删除对象视图,并意识到当你开始使用此方法恢复时有大量的对象会被丢弃。

You want to preserve these objects. Which option must you use to perform TSPITR and preserve the object?

要保留这些对象。你必须使用哪一个选项来执行表空间时间点恢复及保留对象?

A. Perform Export before TSPITR and Import after TSPITR

表空间时间点恢复前执行导出和表空间时间点恢复后执行导入

B. Move objects to another schema that has the same tablespace assigned

C. Perform Incomplete Recovery before TSPITR with the Log Sequence Number (LSN)

D. Perform Incomplete Recovery before TSPITR with the System Change Number (SCN)

Answer: A

(参考:http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmtspit.htm#BRADV89795

When you perform RMAN TSPITR on a tablespace,objects created after the target recovery time are lost.You can preserve such objects after they are identified by exporting them before TSPITR with the Data Pump Export utility and reimporting them afterward with Data Pump Import.

To determine which objects are lost in TSPITR,query the TS_PITR_OBJECTS_TO_BE_DROPPED view on the primary database.)

 

 

 

In Recovery Manager (RMAN), you are taking image copies of the data files of your production database and rolling them forward at regular intervals. You attempt to restart your database. After a regular maintenance task, you realize that one of the data files that belongs to the USERS tablespace is damaged and you need to recover the data file by using the image copy. Because a media failure caused the data file to be damaged, you want to place the data file in a different location while restoring the file. Which option must you consider for this task?  在恢复管理器(RMAN),你正在为你的生产数据库的数据文件制作映像副本,并定期向前滚动。您尝试重新启动数据库。定期维护任务后,你会发现一个属于用户表空间的数据文件被损坏,你需要通过使用映像副本去恢复数据文件。由于介质故障造成数据文件损坏,你想要把数据文件放置在不同的位置,同时恢复文件。你必须为这个任务考虑哪个选项?

A. using only the RMAN SWITCH command to set the new location for the data file

B. placing the database in the MOUNT state for the restore and recovery operations.

C. using an RMAN RUN block with the SET NEWNAME and then the SWITCH command.  使用带有设置新名字然后切换命令的RMAN运行块。Copy了一份,取个新名字,然后切换过去。

D. configuring two channels: one for the restore operation and the other for the recovery operation

Answer: C

 

In your production database, users report that they are unable to generate reports on an important table because it does not contain any data. While investigating the reason, you realize that another user executed the TRUNCATE TABLE command, which accidentally caused the data to be lost. Now you want to recover the lost data of the table without affecting objects in other schemas.  在生产数据库中,用户报告他们无法在一个重要的表上生成报告,因为它不包含任何数据。虽然调查的原因,你会发现另一个用户执行删减表命令,造成数据意外丢失。现在你要恢复表中丢失的数据,而不会影响其他方案中的对象。

Which method must you use to recover the lost data?

你必须使用哪种方法来恢复丢失的数据?

A. Complete Recovery with online redo log

B. Complete Recovery with archived redo log

C.  Tablespace  Point-in-Time  Recovery  (TSPITR) 表空间时间点恢复(TSPITR

D. Incomplete Recovery with system change number (SCN)

Answer: C

wps6C7E.tmp 

 

 

1.6  总结

 

TSPITR虽然很简单,但是有些小的细节还是应该需要注意一下的,比如我实验的时候就犯了一个错误,导致这个实验花了很久才完成。

1、备份的时候需要注意的地方:备份完成后需要做切换日志的操作,否则做truncate操作和备份有可能处于同一个scn,会导致最后报错,找不见备份文件。

 

http://www.itpub.net/thread-1922807-1-1.html

 

[oracle@rhel6_lhr dpdump]$ echo $ORACLE_SID
ora11g
[oracle@rhel6_lhr dpdump]$ rman target /

恢复管理器: Release 11.2.0.3.0 - Production on 星期日 5月 24 17:10:51 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

已连接到目标数据库: ORA11G (DBID=4270446895)

RMAN> RECOVER TABLESPACE AA
2> UNTIL time "to_date('2015-05-24 15:35:00','YYYY-MM-DD HH24:mi:ss')"
3> AUXILIARY DESTINATION '/u01/auxdest';

启动 recover 于 2015-05-24 17:10:59
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=31 设备类型=DISK
RMAN-05026: 警告: 假定以下表空间集适用于指定的时间点

表空间列表要求具有 UNDO 段
表空间 SYSTEM
表空间 UNDOTBS1

使用 SID='pExF' 创建自动实例

供自动实例使用的初始化参数:
db_name=ORA11G
db_unique_name=pExF_tspitr_ORA11G
compatible=11.2.0.0.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/u01/auxdest
log_archive_dest_1='location=/u01/auxdest'
#No auxiliary parameter file used


启动自动实例 ORA11G

Oracle 实例已启动

系统全局区域总计     292278272 字节

Fixed Size                     2227744 字节
Variable Size                100663776 字节
Database Buffers             184549376 字节
Redo Buffers                   4837376 字节
自动实例已创建
对恢复集表空间运行 TRANSPORT_SET_CHECK
TRANSPORT_SET_CHECK 已成功完成

内存脚本的内容:
{
# set requested point in time
set until  time "to_date('2015-05-24 15:35:00','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;';
}
正在执行内存脚本

正在执行命令: SET until clause

启动 restore 于 2015-05-24 17:11:03
分配的通道: ORA_AUX_DISK_1
通道 ORA_AUX_DISK_1: SID=58 设备类型=DISK

通道 ORA_AUX_DISK_1: 正在开始还原数据文件备份集
通道 ORA_AUX_DISK_1: 正在还原控制文件
通道 ORA_AUX_DISK_1: 正在读取备份片段 /u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_05_24/o1_mf_ncsnf_TAG20150524T151723_bp2yx059_.bkp
通道 ORA_AUX_DISK_1: 段句柄 = /u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_05_24/o1_mf_ncsnf_TAG20150524T151723_bp2yx059_.bkp 标记 = TAG20150524T151723
通道 ORA_AUX_DISK_1: 已还原备份片段 1
通道 ORA_AUX_DISK_1: 还原完成, 用时: 00:00:01
输出文件名=/u01/auxdest/ORA11G/controlfile/o1_mf_bp35f83l_.ctl
完成 restore 于 2015-05-24 17:11:05

sql 语句: alter database mount clone database

sql 语句: alter system archive log current

sql 语句: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

内存脚本的内容:
{
# set requested point in time
set until  time "to_date('2015-05-24 15:35:00','YYYY-MM-DD HH24:mi:ss')";
plsql
declare
  sqlstatement       varchar2(512);
  offline_not_needed exception;
  pragma exception_init(offline_not_needed, -01539);
begin
  sqlstatement := 'alter tablespace '||  'AA' ||' offline immediate';
  krmicd.writeMsg(6162, sqlstatement);
  krmicd.execSql(sqlstatement);
exception
  when offline_not_needed then
    null;
end; >>>;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  2 to new;
set newname for clone tempfile  1 to new;
set newname for datafile  6 to
"/u01/app/oracle/oradata/ora11g/aa.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 3, 2, 6;
switch clone datafile all;
}
正在执行内存脚本

正在执行命令: SET until clause

sql 语句: alter tablespace AA offline immediate

正在执行命令: SET NEWNAME

正在执行命令: SET NEWNAME

正在执行命令: SET NEWNAME

正在执行命令: SET NEWNAME

正在执行命令: SET NEWNAME

临时文件 1 在控制文件中已重命名为 /u01/auxdest/ORA11G/datafile/o1_mf_temp_%u_.tmp

启动 restore 于 2015-05-24 17:11:09
使用通道 ORA_AUX_DISK_1

无法删除一个或多个辅助集数据文件

删除自动实例
关闭自动实例
数据库已卸装
Oracle 实例已关闭
自动实例已删除
已删除辅助实例文件 /u01/auxdest/ORA11G/controlfile/o1_mf_bp35f83l_.ctl
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: recover 命令 (在 05/24/2015 17:11:14 上) 失败
RMAN-03015: 在存储的脚本Memory Script中出现错误
RMAN-06026: 有些目标没有找到 - 终止还原
RMAN-06023: 没有找到数据文件3的副本来还原
RMAN-06023: 没有找到数据文件1的副本来还原

RMAN> list backupset of datafile 1,3,2,6;


备份集列表
===================


BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间          
------- ---- -- ---------- ----------- ------------ -------------------
14      Full    1.17G      DISK        00:02:30     2015-05-24 15:19:53
        BP 关键字: 14   状态: AVAILABLE  已压缩: NO  标记: TAG20150524T151723
段名:/u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_05_24/o1_mf_nnndf_TAG20150524T151723_bp2yrbww_.bkp
  备份集 14 中的数据文件列表
  文件 LV 类型 Ckp SCN    Ckp 时间            名称
  ---- -- ---- ---------- ------------------- ----
  1       Full 1515679    2015-05-24 15:17:23 /u01/app/oracle/oradata/ora11g/system01.dbf
  2       Full 1515679    2015-05-24 15:17:23 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
  3       Full 1515679    2015-05-24 15:17:23 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
  6       Full 1515679    2015-05-24 15:17:23 /u01/app/oracle/oradata/ora11g/aa.dbf

RMAN>

 

 

 

 

1.7  RMAN系列参考文章

 

RMAN 备份恢复系列 

 

【推荐】 【RMAN】rm -rf 误操作的恢复过程 

http://blog.itpub.net/26736162/viewspace-1623938/ 

【推荐】 【RMAN】利用备份片还原数据库(中)-附加 

http://blog.itpub.net/26736162/viewspace-1621938/ 

【推荐】 【RMAN】利用备份片还原数据库(下) 

http://blog.itpub.net/26736162/viewspace-1621672/ 

【推荐】 【RMAN】利用备份片还原数据库(中) 

http://blog.itpub.net/26736162/viewspace-1621661/ 

【推荐】 【RMAN】利用备份片还原数据库(上) 

http://blog.itpub.net/26736162/viewspace-1621581/ 

【推荐】 【RMAN】RMAN跨版本恢复(下) 

http://blog.itpub.net/26736162/viewspace-1562583/ 

【推荐】  Oracle 组件 系列 小结 

http://blog.itpub.net/26736162/viewspace-1562441/ 

【推荐】 【RMAN】RMAN跨版本恢复(中) 

http://blog.itpub.net/26736162/viewspace-1561352/ 

【推荐】 【RMAN】RMAN跨版本恢复(上) 

http://blog.itpub.net/26736162/viewspace-1561185/ 

【推荐】  关于在不同版本和平台之间进行还原或复制的常见问题 

http://blog.itpub.net/26736162/viewspace-1549041/ 

 

 

【推荐】 undo表空间文件丢失恢复(4)--无备份无recover的情况下恢复 

http://blog.itpub.net/26736162/viewspace-1458787/ 

【推荐】 undo表空间文件丢失恢复(3)--无备份无redo的情况下恢复 

http://blog.itpub.net/26736162/viewspace-1458750/ 

【推荐】 undo表空间文件丢失恢复(2)--无备份有redo的情况下恢复 

http://blog.itpub.net/26736162/viewspace-1458663/ 

【推荐】 undo表空间文件丢失恢复(1)--有备份 

http://blog.itpub.net/26736162/viewspace-1458654/ 

 

 

【推荐】 ORACLE 数据泵之NETWORK_LINK 

http://blog.itpub.net/26736162/viewspace-1432591/ 

 

 

【推荐】 oracle控制文件在缺失归档日志的情况下的恢复 

http://blog.itpub.net/26736162/viewspace-1426552/ 

【推荐】 ORACLE 只读数据文件备份与恢复 

http://blog.itpub.net/26736162/viewspace-1425283/ 

 

 

【推荐】 热备下的测试库搭建 

http://blog.itpub.net/26736162/viewspace-1405324/ 

 

 

【推荐】 oracle 异构平台迁移之传输表空间一例 

http://blog.itpub.net/26736162/viewspace-1391913/ 

【推荐】 oracle 传输表空间一例 

http://blog.itpub.net/26736162/viewspace-1375260/ 

 

 

【推荐】 利用rman来实现linux平台数据库复制到windows平台数据库 

http://blog.itpub.net/26736162/viewspace-1352436/ 

【推荐】 直接复制数据文件实现linux平台数据库复制到windows平台数据库 

http://blog.itpub.net/26736162/viewspace-1352243/ 

 

 

【推荐】 使用OEM复制数据库 

http://blog.itpub.net/26736162/viewspace-1224865/ 

【推荐】 采用DUPLICATE 把asm数据库复制到文件系统 

http://blog.itpub.net/26736162/viewspace-1224861/ 

Duplicating a Database Without Recovery Catalog or Target Connection 

http://blog.itpub.net/26736162/viewspace-1223253/ 

【推荐】 Duplicating an Active Database 

http://blog.itpub.net/26736162/viewspace-1223247/ 

 



 ORACLE 11g TSPITR恢复被删除的表空间 

在Oracle11g中可以使用TSPITR来对被删除的表空间执行表空间按时间点恢复,下面通过一个实例来演示这个功能。
1.创建测试表空间test

SQL> create tablespace test datafile '/u03/app/oracle/oradata/db/test01.dbf' size 50M autoextend off extent management local segment space management auto;

Tablespace created.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u03/app/oracle/oradata/db/system01.dbf
/u03/app/oracle/oradata/db/sysaux01.dbf
/u03/app/oracle/oradata/db/undotbs01.dbf
/u03/app/oracle/oradata/db/users01.dbf
/u03/app/oracle/oradata/db/example01.dbf
/u03/app/oracle/oradata/db/test01.dbf

6 rows selected.

2.创建测试用户test与测试表t1

SQL> create user test identified by "test" default tablespace test temporary tablespace temp;

User created.

SQL> create table test.t1 as select * from dba_objects;

Table created.

SQL> select count(*) from test.t1;

  COUNT(*)
----------
     86031

3.对整个数据库的执行完全备份

RMAN> backup as backupset database plus archivelog;


Starting backup at 2015-06-01 22:05:12
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=1 STAMP=880996327
input archived log thread=1 sequence=6 RECID=2 STAMP=880996438
input archived log thread=1 sequence=7 RECID=3 STAMP=881014383
input archived log thread=1 sequence=8 RECID=4 STAMP=881014612
input archived log thread=1 sequence=9 RECID=5 STAMP=881015165
input archived log thread=1 sequence=10 RECID=13 STAMP=881233508
input archived log thread=1 sequence=11 RECID=14 STAMP=881233508
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:14
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:05:21
piece handle=/u02/backup/0kq8ea8q_1_1 tag=TAG20150601T220514 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=12 STAMP=881233507
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:21
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:05:22
piece handle=/u02/backup/0lq8ea91_1_1 tag=TAG20150601T220514 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=12 RECID=15 STAMP=881233508
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:23
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:05:26
piece handle=/u02/backup/0mq8ea92_1_1 tag=TAG20150601T220514 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=21 STAMP=881233663
input archived log thread=1 sequence=3 RECID=22 STAMP=881233941
input archived log thread=1 sequence=4 RECID=23 STAMP=881234587
input archived log thread=1 sequence=5 RECID=24 STAMP=881235045
input archived log thread=1 sequence=6 RECID=25 STAMP=881235180
input archived log thread=1 sequence=7 RECID=26 STAMP=881272559
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:26
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:05:27
piece handle=/u02/backup/0nq8ea96_1_1 tag=TAG20150601T220514 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=13 RECID=16 STAMP=881233508
input archived log thread=1 sequence=14 RECID=17 STAMP=881233508
input archived log thread=1 sequence=15 RECID=18 STAMP=881233508
input archived log thread=1 sequence=16 RECID=19 STAMP=881233508
input archived log thread=1 sequence=17 RECID=20 STAMP=881233508
input archived log thread=1 sequence=18 RECID=11 STAMP=881232587
input archived log thread=1 sequence=19 RECID=9 STAMP=881232587
input archived log thread=1 sequence=20 RECID=10 STAMP=881232587
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:27
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:05:28
piece handle=/u02/backup/0oq8ea97_1_1 tag=TAG20150601T220514 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=27 STAMP=881273112
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:28
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:05:29
piece handle=/u02/backup/0pq8ea98_1_1 tag=TAG20150601T220514 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2015-06-01 22:05:29

Starting backup at 2015-06-01 22:05:29
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u03/app/oracle/oradata/db/system01.dbf
input datafile file number=00002 name=/u03/app/oracle/oradata/db/sysaux01.dbf
input datafile file number=00003 name=/u03/app/oracle/oradata/db/undotbs01.dbf
input datafile file number=00005 name=/u03/app/oracle/oradata/db/test01.dbf
input datafile file number=00004 name=/u03/app/oracle/oradata/db/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:31
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:09:16
piece handle=/u02/backup/0qq8ea9b_1_1 tag=TAG20150601T220529 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:45
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:09:20
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:09:21
piece handle=/u02/backup/0rq8eagd_1_1 tag=TAG20150601T220529 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2015-06-01 22:09:21

Starting backup at 2015-06-01 22:09:21
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=9 RECID=28 STAMP=881273363
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:09:27
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:09:30
piece handle=/u02/backup/0sq8eagn_1_1 tag=TAG20150601T220926 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 2015-06-01 22:09:30

4.删除表空间test,并记录删除操作执行之前的系统SCN与时间

SQL> select to_char(scn_to_timestamp(current_scn),'yyyy-mm-dd hh24:mi:ss'),current_scn from v$database;

TO_CHAR(SCN_TO_TIME CURRENT_SCN
------------------- -----------
2015-06-01 22:11:45      751203


SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE

SQL> host ls -lrt /u03/app/oracle/oradata/db/
total 1548000
-rw-r----- 1 oracle11 oinstall  30416896 Jun  1 22:01 temp01.dbf
-rw-r----- 1 oracle11 oinstall  52429312 Jun  1 22:05 redo02.log
-rw-r----- 1 oracle11 oinstall  52429312 Jun  1 22:09 redo03.log
-rw-r----- 1 oracle11 oinstall   5251072 Jun  1 22:14 users01.dbf
-rw-r----- 1 oracle11 oinstall 775954432 Jun  1 22:14 system01.dbf
-rw-r----- 1 oracle11 oinstall  73408512 Jun  1 22:14 undotbs01.dbf
-rw-r----- 1 oracle11 oinstall 545267712 Jun  1 22:14 sysaux01.dbf
-rw-r----- 1 oracle11 oinstall  52429312 Jun  1 22:14 redo01.log
-rw-r----- 1 oracle11 oinstall   9748480 Jun  1 22:14 control01.ctl

从上面的查询可以看到表空间test已经被删除了。

5.现在执行TSPITR将表空间test恢复到被删除之前的时间点

RMAN> recover tablespace "TEST" until time '2015-06-01 22:11:45' auxiliary destination '/u02/auxiliary';

Starting recover at 2015-06-01 22:22:25
starting full resync of recovery catalog
full resync complete
using channel ORA_DISK_1

Creating automatic instance, with SID='jAvb'

initialization parameters used for automatic instance:
db_name=DB
db_unique_name=jAvb_tspitr_DB
compatible=11.2.0.4.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
db_create_file_dest=/u02/auxiliary
log_archive_dest_1='location=/u02/auxiliary'
#No auxiliary parameter file used


starting up automatic instance DB

Oracle instance started

Total System Global Area    1071333376 bytes

Fixed Size                     1369420 bytes
Variable Size                281021108 bytes
Database Buffers             784334848 bytes
Redo Buffers                   4608000 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until  time "2015-06-01 22:11:45";
# 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;';
# resync catalog
resync catalog;
}
executing Memory Script

executing command: SET until clause

Starting restore at 2015-06-01 22:22:40
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/0rq8eagd_1_1
channel ORA_AUX_DISK_1: piece handle=/u02/backup/0rq8eagd_1_1 tag=TAG20150601T220529
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u02/auxiliary/DB/controlfile/o1_mf_bprtokmc_.ctl
Finished restore at 2015-06-01 22:22:42

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

starting full resync of recovery catalog
full resync complete

contents of Memory Script:
{
# set requested point in time
set until  time "2015-06-01 22:11:45";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  2 to new;
set newname for clone tempfile  1 to new;
set newname for datafile  5 to
 "/u03/app/oracle/oradata/db/test01.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 3, 2, 5;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u02/auxiliary/DB/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 2015-06-01 22:22:51
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u02/auxiliary/DB/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u02/auxiliary/DB/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u02/auxiliary/DB/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u03/app/oracle/oradata/db/test01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/0qq8ea9b_1_1
channel ORA_AUX_DISK_1: piece handle=/u02/backup/0qq8ea9b_1_1 tag=TAG20150601T220529
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:15
Finished restore at 2015-06-01 22:25:07

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=881274307 file name=/u02/auxiliary/DB/datafile/o1_mf_system_bprtowv6_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=881274307 file name=/u02/auxiliary/DB/datafile/o1_mf_undotbs1_bprtowxf_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=881274307 file name=/u02/auxiliary/DB/datafile/o1_mf_sysaux_bprtowxc_.dbf

contents of Memory Script:
{
# set requested point in time
set until  time "2015-06-01 22:11:45";
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  2 online";
sql clone "alter database datafile  5 online";
# recover and open resetlogs
recover clone database tablespace  "TEST", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  2 online

sql statement: alter database datafile  5 online

Starting recover at 2015-06-01 22:25:08
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 9 is already on disk as file /u03/archivelog/1_9_881232587.dbf
archived log for thread 1 with sequence 10 is already on disk as file /u03/archivelog/1_10_881232587.dbf
archived log file name=/u03/archivelog/1_9_881232587.dbf thread=1 sequence=9
archived log file name=/u03/archivelog/1_10_881232587.dbf thread=1 sequence=10
media recovery complete, elapsed time: 00:00:16
Finished recover at 2015-06-01 22:25:25

database opened

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace  "TEST" read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u02/auxiliary''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u02/auxiliary''";
}
executing Memory Script

sql statement: alter tablespace  "TEST" read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u02/auxiliary''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u02/auxiliary''

Performing export of metadata...
   EXPDP> Starting "SYS"."TSPITR_EXP_jAvb":
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Master table "SYS"."TSPITR_EXP_jAvb" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_jAvb is:
   EXPDP>   /u02/auxiliary/tspitr_jAvb_29236.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace TEST:
   EXPDP>   /u03/app/oracle/oradata/db/test01.dbf
   EXPDP> Job "SYS"."TSPITR_EXP_jAvb" successfully completed at Mon Jun 1 22:27:28 2015 elapsed 0 00:01:12
Export completed


contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
}
executing Memory Script

database closed
database dismounted
Oracle instance shut down

Performing import of metadata...
   IMPDP> Master table "SYS"."TSPITR_IMP_jAvb" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_jAvb":
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   IMPDP> Job "SYS"."TSPITR_IMP_jAvb" successfully completed at Mon Jun 1 22:28:23 2015 elapsed 0 00:00:14
Import completed


contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace  "TEST" read write';
sql 'alter tablespace  "TEST" offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
# resync catalog
resync catalog;
}
executing Memory Script

sql statement: alter tablespace  "TEST" read write

sql statement: alter tablespace  "TEST" offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

starting full resync of recovery catalog
full resync complete

Removing automatic instance
Automatic instance removed
auxiliary instance file /u02/auxiliary/DB/datafile/o1_mf_temp_bprtv5bo_.tmp deleted
auxiliary instance file /u02/auxiliary/DB/onlinelog/o1_mf_3_bprttwvr_.log deleted
auxiliary instance file /u02/auxiliary/DB/onlinelog/o1_mf_2_bprttt0k_.log deleted
auxiliary instance file /u02/auxiliary/DB/onlinelog/o1_mf_1_bprttp4g_.log deleted
auxiliary instance file /u02/auxiliary/DB/datafile/o1_mf_sysaux_bprtowxc_.dbf deleted
auxiliary instance file /u02/auxiliary/DB/datafile/o1_mf_undotbs1_bprtowxf_.dbf deleted
auxiliary instance file /u02/auxiliary/DB/datafile/o1_mf_system_bprtowv6_.dbf deleted
auxiliary instance file /u02/auxiliary/DB/controlfile/o1_mf_bprtokmc_.ctl deleted
Finished recover at 2015-06-01 22:28:45
SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TEST                           OFFLINE

6 rows selected.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u03/app/oracle/oradata/db/system01.dbf
/u03/app/oracle/oradata/db/sysaux01.dbf
/u03/app/oracle/oradata/db/undotbs01.dbf
/u03/app/oracle/oradata/db/users01.dbf
/u03/app/oracle/oradata/db/test01.dbf

SQL> host ls -lrt /u03/app/oracle/oradata/db/
total 1599328
-rw-r----- 1 oracle11 oinstall  52429312 Jun  1 22:09 redo03.log
-rw-r----- 1 oracle11 oinstall   5251072 Jun  1 22:22 users01.dbf
-rw-r----- 1 oracle11 oinstall  52429312 Jun  1 22:22 redo01.log
-rw-r----- 1 oracle11 oinstall  30416896 Jun  1 22:28 temp01.dbf
-rw-r----- 1 oracle11 oinstall  52436992 Jun  1 22:28 test01.dbf
-rw-r----- 1 oracle11 oinstall 775954432 Jun  1 22:28 system01.dbf
-rw-r----- 1 oracle11 oinstall  73408512 Jun  1 22:28 undotbs01.dbf
-rw-r----- 1 oracle11 oinstall 545267712 Jun  1 22:28 sysaux01.dbf
-rw-r----- 1 oracle11 oinstall  52429312 Jun  1 22:29 redo02.log
-rw-r----- 1 oracle11 oinstall   9814016 Jun  1 22:30 control01.ctl

从上面的查询结果可以看到表空间test已经恢复,但现在test表空间是脱机状态。

6.将表空间test联机,并查询表test.t1来验证恢复是否真正成功.

RMAN> sql 'alter tablespace test online';

sql statement: alter tablespace test online
starting full resync of recovery catalog
full resync complete

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TEST                           ONLINE

6 rows selected.

SQL> select count(*) from test.t1;

  COUNT(*)
----------
     86031

从上面的查询结果可以看到被删除的表空间test已经恢复到被删除之前的时间点,表test.t1的记录已经恢复回来。




About Me

...............................................................................................................................

● 本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用

● 本文在itpub(http://blog.itpub.net/26736162)、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号(xiaomaimiaolhr)上有同步更新

● 本文itpub地址:http://blog.itpub.net/26736162/viewspace-1671741/

● 本文博客园地址:http://www.cnblogs.com/lhrbest

● 本文pdf版及小麦苗云盘地址:http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答:http://blog.itpub.net/26736162/viewspace-2134706/

● QQ群:230161599     微信群:私聊

● 联系我请加QQ好友(646634621),注明添加缘由

● 于

img_e3029f287d989cd04bd75432ecc1c172.png
DBA笔试面试讲解
欢迎与我联系

 

网友评论

登录后评论
0/500
评论