Oracle 11g 主动选择的不完全恢复，基于SCN的，DML操作

用户的错误操作：delete, update, drop, commit

如果采用完全恢复，这些误操作还是会被完成，所以如果想撤销误操作，就需要把数据库恢复到误操作之前。

定位错误操作的时间，logmnr

--打开SQL*Plus时间显示 scott@TESTDB11>set time on 15:34:04 scott@TESTDB11>select * from emp;        EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ----------       7369 SMITH      CLERK           7902 17-DEC-80        800                    20       7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30       ......       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.   --删除所有数据，被删除的大概时间是15:34:26 15:34:26 scott@TESTDB11>delete from emp;   14 rows deleted. --提交 15:35:13 scott@TESTDB11>commit;   Commit complete.   15:35:16 scott@TESTDB11>select * from emp;   no rows selected   --做3次日志切换，目的就是让记录刚才改变的日志被归档出来 sys@TESTDB11>alter system switch logfile;   System altered.   --查看归档出来的日志，确定需要挖掘的日志序号为8 sys@TESTDB11>col name for a40 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;  SEQUENCE# NAME                                     FIRST_TIME          NEXT_TIME ---------- ---------------------------------------- ------------------- -------------------            1 /archive2/1_1_823247647.dbf              2013-08-12 07:54:07 2013-08-12 08:24:28          1 /archive1/1_1_823247647.dbf              2013-08-12 07:54:07 2013-08-12 08:24:28       ...          7 /archive2/1_7_823247647.dbf              2013-08-12 08:27:00 2013-08-12 08:27:01          8 /archive1/1_8_823247647.dbf              2013-08-12 08:27:01 2013-08-12 15:40:42          8 /archive2/1_8_823247647.dbf              2013-08-12 08:27:01 2013-08-12 15:40:42   130 rows selected.   -- 进行挖掘，根据前面的时间确定操作的SCN和时间为: 2703654 2013-08-12 15:35:13 sys@TESTDB11>exec dbms_logmnr.add_logfile(logfilename => '/archive1/1_8_823247647.dbf', options => dbms_logmnr.new);   PL/SQL procedure successfully completed.   sys@TESTDB11>exec dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog);   PL/SQL procedure successfully completed.   sys@TESTDB11>col sql_redo for a50 sys@TESTDB11>select scn, timestamp, sql_redo from v$logmnr_contents where seg_owner='SCOTT' and seg_name = 'EMP';          SCN TIMESTAMP           SQL_REDO ---------- ------------------- --------------------------------------------------    2683065 2013-08-12 08:46:52 insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","M                                GR","HIREDATE","SAL","COMM","DEPTNO") values ('793                                4','MILLER','CLERK','7782',TO_DATE('1982-01-23 00:                                00:00', 'yyyy-mm-dd hh24:mi:ss'),'1300',NULL,'10')                                ;      2703654 2013-08-12 15:35:13 delete from "SCOTT"."EMP" where "EMPNO" = '7369' a          SCN TIMESTAMP           SQL_REDO ---------- ------------------- --------------------------------------------------                                nd "ENAME" = 'SMITH' and "JOB" = 'CLERK' and "MGR"                                 = '7902' and "HIREDATE" = TO_DATE('1980-12-17 00:                                00:00', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = '800'                                 and "COMM" IS NULL and "DEPTNO" = '20' and ROWID                                = 'AAAU4PAAEAAAACTAAA';      .......省略      2703654 2013-08-12 15:35:13 delete from "SCOTT"."EMP" where "EMPNO" = '7934' a                                nd "ENAME" = 'MILLER' and "JOB" = 'CLERK' and "MGR                                " = '7782' and "HIREDATE" = TO_DATE('1982-01-23 00                                :00:00', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = '130                                0' and "COMM" IS NULL and "DEPTNO" = '10' and ROWI                                D = 'AAAU4PAAEAAAACTAAN';     30 rows selected.   -- 查看当前数据库检查点号, 上面确定的那个检查点2703654，肯定要比这个小。 sys@TESTDB11>select name, checkpoint_change# from v$datafile_header;   NAME                                     CHECKPOINT_CHANGE# ---------------------------------------- ------------------ /oradata/TestDB11/system01.dbf                      2703910 /oradata/TestDB11/sysaux01.dbf                      2703910 /oradata/TestDB11/undotbs01.dbf                     2703910 /oradata/TestDB11/users01.dbf                       2703910 /oradata/TestDB11/example01.dbf                     2703910 /oradata/TestDB11/newundotbs01.dbf                  2703910   6 rows selected.   --下面要进行不完全恢复 sys@TESTDB11>shutdown mount force; SP2-0717: illegal SHUTDOWN option 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/TestDB11   --人工计算一下恢复到检查点，确定恢复会在哪个日志停下来（其实不需要人工计算,Oralce会自己计算) sys@TESTDB11>col name for a40 sys@TESTDB11>select sequence#, name, first_change#, next_change# from v$archived_log;    SEQUENCE# NAME                                     FIRST_CHANGE# NEXT_CHANGE# ---------- ---------------------------------------- ------------- ------------          1 /archive1/1_1_823247647.dbf                    2679129      2681425          ....省略          7 /archive2/1_7_823247647.dbf                    2681524      2681527          8 /archive1/1_8_823247647.dbf                    2681527      2703910          8 /archive2/1_8_823247647.dbf                    2681527      2703910     --恢复 sys@TESTDB11>recover database until change 2703654; Media recovery complete. sys@TESTDB11>select sequence#, group#, status from v$log;    SEQUENCE#     GROUP# STATUS ---------- ---------- ----------------          7          1 INACTIVE          9          3 CURRENT          8          2 INACTIVE     --此时查看数据文件中头中的检查点号，为我们指定恢到的SCN号而且一致，可以开库 sys@TESTDB11>col name for a50 sys@TESTDB11>select name, checkpoint_change# from v$datafile_header;   NAME                                               CHECKPOINT_CHANGE# -------------------------------------------------- ------------------ /oradata/TestDB11/system01.dbf                                2703654 /oradata/TestDB11/sysaux01.dbf                                2703654 /oradata/TestDB11/undotbs01.dbf                               2703654 /oradata/TestDB11/users01.dbf                                 2703654 /oradata/TestDB11/example01.dbf                               2703654 /oradata/TestDB11/newundotbs01.dbf                            2703654   6 rows selected.      sys@TESTDB11>alter database open resetlogs;   Database altered.   --日志已经重置了 sys@TESTDB11>select sequence#, group#, status from v$log;    SEQUENCE#     GROUP# STATUS ---------- ---------- ----------------          1          1 CURRENT          0          2 UNUSED          0          3 UNUSED   --检查数据,都被恢复回来了（即解决了误操作的问题） 16:20:30 scott@TESTDB11>select * from emp;        EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ----------       7369 SMITH      CLERK           7902 17-DEC-80        800                    20       7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30       7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30 .......       7902 FORD       ANALYST         7566 03-DEC-81       3000                    20       7934 MILLER     CLERK           7782 23-JAN-82       1300                    10   14 rows selected.   --删除无用的归档 [oracle@S1011:/archive1]$ rm * [oracle@S1011:/archive2]$ rm *   --resetlogs之后，要做一个整个数据库备份（调用我们之前做好的脚本backup02.sql) sys@TESTDB11>@backup_script/backup02.sql

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17013648/viewspace-1153852/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17013648/viewspace-1153852/