用户的错误操作: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/