DDL误操作基于scn的不完全恢复

不完全恢复（必须归档）的步骤：

1、先做全备份（最好是关库备份）。

2、通过logmnr找到误操作的时间点

3、转储所有数据文件。其他不用。

4、startup mount；

5、把数据文件恢复到错误发生的时间点。

6.开库resetlogs；

7.再全备份一次。

utl

1、首先创建一个ult目录。

[oracle@rtest oracle]$ mkdir utl

[oracle@rtest oracle]$ cd utl/

[oracle@rtest utl]$ pwd

/u01/app/oracle/utl

然后修改：

sys@TEST0910> show parameter utl

NAME                                 TYPE        VALUE

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

create_stored_outlines               string

utl_file_dir                         string

sys@TEST0910> alter system set utl_file_dir='/u01/app/oracle/utl';

alter system set utl_file_dir='/u01/app/oracle/utl'

                 *

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

2、使路径生效，shutdown immediate后startup

sys@TEST0910> alter system set utl_file_dir='/u01/app/oracle/utl' scope=spfile;

System altered.

sys@TEST0910> show parameter utl

NAME                                 TYPE        VALUE

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

create_stored_outlines               string

utl_file_dir                         string

sys@TEST0910> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@TEST0910> startup

ORACLE instance started.

Total System Global Area 2505338880 bytes

Fixed Size                  2230952 bytes

Variable Size             587203928 bytes

Database Buffers         1895825408 bytes

Redo Buffers               20078592 bytes

Database mounted.

Database opened.

sys@TEST0910> show parameter utl

NAME                                             TYPE        VALUE

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

create_stored_outlines                   string

utl_file_dir                                       string      /u01/app/oracle/utl

3、破坏试验之前，备份

冷备份：sys@TEST0910> @/u01/app/oracle/bak/cold_bak

热备份：sys@TEST0910>  @/u01/app/oracle/bak/hot_bak

破坏性实验：ddl语句的不完全恢复

过程：删数据--查日志--logmnr--关库--转储--mount--不完全恢复--开库resetlogs

truncate误操作

sys@TEST0910> select count(*) from scott.test9;

  COUNT(*)

----------

        14

4、使logmnr激活，并且误操作 truncate

sys@TEST0910> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

sys@TEST0910> truncate table scott.test9;

Table truncated.

没有undo日志啥的。

可以用闪回日志。

sys@TEST0910> select count(*) from scott.test9;

  COUNT(*)

----------

         0

5、查询当前日志组和归档日志。

sys@TEST0910> select * from v$log;

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

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

         1          1          1   52428800        512          1 NO  CURRENT                1885629 2013-09-22 12:57:15   2.8147E+14

         2          1          0   52428800        512          1 YES UNUSED                       0                                0

         3          1          0   52428800        512          1 YES UNUSED                       0                                0

sys@TEST0910> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_

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

         2         ONLINE  /u01/app/oracle/oradata/test0910/redo02.log        NO

         1         ONLINE  /u01/app/oracle/oradata/test0910/redo01.log        NO

         3         ONLINE  /u01/app/oracle/oradata/test0910/redo03.log        NO

sys@TEST0910> select name from v$archived_log;

NAME

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

/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_22/o1_mf_1_4_93y4xkgl_.arc

/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_22/o1_mf_1_5_93y8cd3d_.arc

 

 

logminer官方参考：http://docs.oracle.com/cd/E11882_01/server.112/e22490/logminer.htm#i1009063

 

6、使用logminer抓取日志，获取误操作的时间点。

sys@TEST0910> EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', -

>    '/u01/app/oracle/utl', -

>     DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

/

PL/SQL procedure successfully completed.

sys@TEST0910> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -

>    LOGFILENAME => '/u01/app/oracle/oradata/test0910/redo01.log', -

>    OPTIONS => DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

sys@TEST0910> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -

>    LOGFILENAME => '/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_22/o1_mf_1_5_93y8cd3d_.arc', -

>    OPTIONS => DBMS_LOGMNR.ADDFILE);

BEGIN DBMS_LOGMNR.ADD_LOGFILE(     LOGFILENAME => '/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_22/o1_mf_1_5_93y8cd3d_.arc',     OPTIONS => DBMS_LOGMNR.ADDFILE); END;

*

ERROR at line 1:

ORA-01287: file /u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_22/o1_mf_1_5_93y8cd3d_.arc is from a different database incarnation

ORA-06512: at "SYS.DBMS_LOGMNR", line 68

ORA-06512: at line 1

报错，是因为还没有归档。

sys@TEST0910> ALTER SESSION SET NLS_DATE_FORMAT = 'dd-mon-yyyy hh24:mi:ss';

Session altered.

sys@TEST0910> EXECUTE DBMS_LOGMNR.START_LOGMNR( -

>    DICTFILENAME =>'/u01/app/oracle/utl/dictionary.ora',OPTIONS => -

>    DBMS_LOGMNR.DDL_DICT_TRACKING);

PL/SQL procedure successfully completed.

 

7、查询logminer内容，并结束logmnr

sys@TEST0910> select username,scn,timestamp,sql_redo from v$logmnr_contents

  2  where seg_name='TEST9' order by 1;

USERNAME          SCN TIMESTAMP            SQL_REDO

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

SYS           1886381 22-sep-2013 13:09:55 truncate table scott.test9;

sys@TEST0910> EXECUTE DBMS_LOGMNR.END_LOGMNR;

PL/SQL procedure successfully completed.

 

8、模拟断电，然后转储所有的数据文件

sys@TEST0910> shutdown abort;

ORACLE instance shut down.

然后转储冷备份的数据文件。

转储所有的数据文件

[oracle@rtest bak]$ cp /u01/app/oracle/bak/cold_bak/users01.dbf /u01/app/oracle/oradata/test0910/disk1/users01.dbf

[oracle@rtest bak]$ cp /u01/app/oracle/bak/cold_bak/example01.dbf /u01/app/oracle/oradata/test0910/example01.dbf

[oracle@rtest bak]$ cp /u01/app/oracle/bak/cold_bak/lxtb01.dbf /u01/app/oracle/oradata/test0910/lxtb01.dbf

[oracle@rtest bak]$ cp /u01/app/oracle/bak/cold_bak/sysaux01.dbf /u01/app/oracle/oradata/test0910/sysaux01.dbf

[oracle@rtest bak]$ cp /u01/app/oracle/bak/cold_bak/system01.dbf /u01/app/oracle/oradata/test0910/system01.dbf

[oracle@rtest bak]$ cp /u01/app/oracle/bak/cold_bak/testtb.dbf /u01/app/oracle/oradata/test0910/testtb.dbf

[oracle@rtest bak]$ cp /u01/app/oracle/bak/cold_bak/undotbs01.dbf /u01/app/oracle/oradata/test0910/undotbs01.dbf

 

9、起库到mount状态，然后基于scn的不完全恢复，最后resetlogs打开数据库

sys@TEST0910> startup mount;

ORACLE instance started.

Total System Global Area 2505338880 bytes

Fixed Size                  2230952 bytes

Variable Size             587203928 bytes

Database Buffers         1895825408 bytes

Redo Buffers               20078592 bytes

Database mounted.

sys@TEST0910> recover database until change 1886381;

ORA-00279: change 1883694 generated at 09/22/2013 11:51:46 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_22/o1_mf_1_4_93y4xkgl_.arc

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

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 1884037 generated at 09/22/2013 11:58:41 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_22/o1_mf_1_5_93y8cd3d_.arc

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

Log applied.

Media recovery complete.

sys@TEST0910> alter database open resetlogs;

Database altered.

sys@TEST0910> select count(*) from scott.test9;

  COUNT(*)

----------

        14