【TSPITR】RMAN表空間基於時間點的自動恢復

lhrbest發表於2015-05-26

【RMAN】TSPITR--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<=2;

 

表已建立。

 

已用時間:  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_TIME，dba_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_INDEX在users表空間中，那麼我們刪除這個索引，等待恢復完成後再重建該索引。

 

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_TEST2、TSPITR_TEST3、TSPITR_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)，註明新增緣由

● 於 2015-05-24 09:00 ~ 2015-05-24 22:00 在唐鎮金唐公寓完成

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

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

拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的資料庫技術。

【TSPITR】RMAN表空間基於時間點的自動恢復
 DBA筆試面試講解
 歡迎與我聯絡

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-1671741/，如需轉載，請註明出處，否則將追究法律責任。

相關文章