Oracle 11g 主動選擇的不完全恢復，基於時間，DDL操作

--庫為剛做完alter database open resetlogs，所以做3-4次日誌切換

sys@TESTDB11>alter system switch logfile;

--將資料字典匯出到重做日誌檔案中

sys@TESTDB11>exec dbms_logmnr_d.build(options => dbms_logmnr_d.store_in_redo_logs);

PL/SQL procedure successfully completed.

--做4次日誌切換

--檢視匯出到哪個重做日誌中了(這裡確定為6號)

SEQUENCE# NAME                                               DIC DIC

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

       128 /archive1/1_128_813665348.dbf                      NO  NO

       129 /archive1/1_129_813665348.dbf                      NO  NO

       129 /archive2/1_129_813665348.dbf                      NO  NO

       130 /archive1/1_130_813665348.dbf                      NO  NO

       130 /archive2/1_130_813665348.dbf                      NO  NO

       131 /archive1/1_131_813665348.dbf                      NO  NO

       131 /archive2/1_131_813665348.dbf                      NO  NO

         2 /archive2/1_2_823300846.dbf                        NO  NO

         2 /archive1/1_2_823300846.dbf                        NO  NO

         3 /archive1/1_3_823300846.dbf                        NO  NO

         3 /archive2/1_3_823300846.dbf                        NO  NO

         4 /archive1/1_4_823300846.dbf                        NO  NO

         4 /archive2/1_4_823300846.dbf                        NO  NO

         5 /archive1/1_5_823300846.dbf                        NO  NO

         5 /archive2/1_5_823300846.dbf                        NO  NO

         6 /archive1/1_6_823300846.dbf                        YES YES

         6 /archive2/1_6_823300846.dbf                        YES YES

         7 /archive1/1_7_823300846.dbf                        NO  NO

         7 /archive2/1_7_823300846.dbf                        NO  NO

         8 /archive1/1_8_823300846.dbf                        NO  NO

         8 /archive2/1_8_823300846.dbf                        NO  NO

         9 /archive1/1_9_823300846.dbf                        NO  NO

         9 /archive2/1_9_823300846.dbf                        NO  NO

        10 /archive1/1_10_823300846.dbf                       NO  NO

        10 /archive2/1_10_823300846.dbf                       NO  NO

122 rows selected.

--刪除表，記錄完成刪除的大致時間

23:10:41 scott@TESTDB11>drop table emp;

Table dropped.

23:10:45 scott@TESTDB11>

--在system表空間中建立一個新的表test2

sys@TESTDB11>create table tests2 tablespace system as select * from user_users;

Table created.

--3次日誌切換

--確定需要挖掘的日誌檔案為11

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

Session altered.

sys@TESTDB11>select sequence#, name, first_time, next_time from v$archived_log order by first_change#;

 SEQUENCE# NAME                                               FIRST_TIME          NEXT_TIME

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

         2 /archive1/1_2_823300846.dbf                        2013-08-12 22:55:38 2013-08-12 22:59:20

         2 /archive2/1_2_823300846.dbf                        2013-08-12 22:55:38 2013-08-12 22:59:20

         3 /archive1/1_3_823300846.dbf                        2013-08-12 22:59:20 2013-08-12 22:59:21

         3 /archive2/1_3_823300846.dbf                        2013-08-12 22:59:20 2013-08-12 22:59:21

         4 /archive1/1_4_823300846.dbf                        2013-08-12 22:59:21 2013-08-12 22:59:26

         4 /archive2/1_4_823300846.dbf                        2013-08-12 22:59:21 2013-08-12 22:59:26

         5 /archive1/1_5_823300846.dbf                        2013-08-12 22:59:26 2013-08-12 23:05:59

         5 /archive2/1_5_823300846.dbf                        2013-08-12 22:59:26 2013-08-12 23:05:59

         6 /archive1/1_6_823300846.dbf                        2013-08-12 23:05:59 2013-08-12 23:06:04

         6 /archive2/1_6_823300846.dbf                        2013-08-12 23:05:59 2013-08-12 23:06:04

         7 /archive1/1_7_823300846.dbf                        2013-08-12 23:06:04 2013-08-12 23:06:53

         7 /archive2/1_7_823300846.dbf                        2013-08-12 23:06:04 2013-08-12 23:06:53

         8 /archive1/1_8_823300846.dbf                        2013-08-12 23:06:53 2013-08-12 23:06:56

         8 /archive2/1_8_823300846.dbf                        2013-08-12 23:06:53 2013-08-12 23:06:56

         9 /archive1/1_9_823300846.dbf                        2013-08-12 23:06:56 2013-08-12 23:07:00

         9 /archive2/1_9_823300846.dbf                        2013-08-12 23:06:56 2013-08-12 23:07:00

        10 /archive1/1_10_823300846.dbf                       2013-08-12 23:07:00 2013-08-12 23:07:02

        10 /archive2/1_10_823300846.dbf                       2013-08-12 23:07:00 2013-08-12 23:07:02

        11 /archive1/1_11_823300846.dbf                       2013-08-12 23:07:02 2013-08-12 23:13:29

        11 /archive2/1_11_823300846.dbf                       2013-08-12 23:07:02 2013-08-12 23:13:29

        12 /archive1/1_12_823300846.dbf                       2013-08-12 23:13:29 2013-08-12 23:13:30

        12 /archive2/1_12_823300846.dbf                       2013-08-12 23:13:29 2013-08-12 23:13:30

--進行挖掘，確定恢復的時間為2013-08-12 23:10:45

sys@TESTDB11>exec dbms_logmnr.add_logfile(logfilename => '/archive2/1_6_823300846.dbf', options =>dbms_logmnr.new);

PL/SQL procedure successfully completed.

sys@TESTDB11>exec dbms_logmnr.add_logfile(logfilename => '/archive2/1_11_823300846.dbf', options =>dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

sys@TESTDB11>exec dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_redo_logs);

PL/SQL procedure successfully completed.

sys@TESTDB11>col sql_redo for a60

sys@TESTDB11>select scn, timestamp, sql_redo from v$logmnr_contents where seg_owner = 'SCOTT' and seg_name = 'EMP';

       SCN TIMESTAMP           SQL_REDO

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

   2641900 2013-08-12 23:10:45 ALTER TABLE "SCOTT"."EMP" RENAME CONSTRAINT "PK_EMP" TO "BIN

                               $481s1qCPA+vgRAgAJzxnug==$0" ;

   2641918 2013-08-12 23:10:45 drop table emp AS "BIN$481s1qCRA+vgRAgAJzxnug==$0" ;

--基於時間的不完全恢復

sys@TESTDB11>startup mount force;

ORACLE instance started.

Total System Global Area  855982080 bytes

Fixed Size                  2230792 bytes

Variable Size             641730040 bytes

Database Buffers          209715200 bytes

Redo Buffers                2306048 bytes

Database mounted.  

sys@TESTDB11>!cp /pooldisk02/backup01/inconsistent/*.dbf /oradata

sys@TESTDB11>recover database until time to_date('2013-08-12 23:10:45', 'yyyy-mm-dd hh24:mi:ss');

ORA-00279: change 2619570 generated at 08/12/2013 22:42:37 needed for thread 1

ORA-00289: suggestion : /archive2/1_1_823300846.dbf

ORA-00280: change 2619570 for thread 1 is in sequence #1

.....

ORA-00279: change 2641714 generated at 08/12/2013 23:07:00 needed for thread 1

ORA-00289: suggestion : /archive2/1_10_823300846.dbf

ORA-00280: change 2641714 for thread 1 is in sequence #10

ORA-00279: change 2641718 generated at 08/12/2013 23:07:02 needed for thread 1

ORA-00289: suggestion : /archive2/1_11_823300846.dbf

ORA-00280: change 2641718 for thread 1 is in sequence #11

Log applied.

Media recovery complete.

--確定資料檔案頭中的檢查點SCN(注意：與挖掘出來的SCN是有出入的,因為是按時間做的恢復)

sys@TESTDB11>select name, checkpoint_change# from  v$datafile_header;

NAME                                               CHECKPOINT_CHANGE#

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

/oradata/system01.dbf                                         2641892

/oradata/sysaux01.dbf                                         2641892

/oradata/undotbs01.dbf                                        2641892

/oradata/users01.dbf                                          2641892

/oradata/example01.dbf                                        2641892

sys@TESTDB11>alter database open resetlogs;

Database altered.

--驗證資料，還原到emp表刪除之前

23:31:24 scott@TESTDB11>select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

      ....

      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

--檢驗test2表，它是在drop操作之後建立的,所以沒有了。

sys@TESTDB11>select * from test2;

select * from test2

              *

ERROR at line 1:

ORA-00942: table or view does not exist

--進行整個庫非一致性備份

sys@TESTDB11>@backup_script/backup02.sql

結論：要麼其它的表空間回到歷史狀態，資料損失，要麼這張表不要了。