基于日志挖掘的误操作不完全恢复思路

发布时间：2020-12-14 04:03:05 所属栏目：大数据来源：网络整理

导读：生成备份所有数据文件的脚本: select 'alter tablespace '||tablespace_name||' begin backup;'||chr(10)|| 'ho cp -v '||file_name||' /home/oracle/hotbk/'||chr(10)|| 'alter tablespace '||tablespace_name||' end backup;' from dba_data_files; 备份co

生成备份所有数据文件的脚本:
select 'alter tablespace '||tablespace_name||' begin backup;'||chr(10)||
'ho cp -v '||file_name||' /home/oracle/hotbk/'||chr(10)||
'alter tablespace '||tablespace_name||' end backup;'
from dba_data_files;

备份controlfile:
alter database backup controlfile to '/home/oracle/hotbk/ctl_after_reset.bk';
alter database backup controlfile to trace as '/home/oracle/hotbk/cre_ctl.sql';

备份成功后切换一次归档日志:
alter system switch logfile;

online redolog 很重要！！！

SQL> select? CHECKPOINT_CHANGE#? from v$database;

CHECKPOINT_CHANGE#
------------------
?????????? 1079325

alter tablespace USERS begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/users01.dbf? /home/oracle/hotbk/
alter tablespace USERS end backup;

alter tablespace UNDOTBS1 begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/undotbs01.dbf? /home/oracle/hotbk/
alter tablespace UNDOTBS1 end backup;

alter tablespace SYSAUX begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/sysaux01.dbf? /home/oracle/hotbk/
alter tablespace SYSAUX end backup;

alter tablespace SYSTEM begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/system01.dbf? /home/oracle/hotbk/
alter tablespace SYSTEM end backup;

alter tablespace JF_DATA begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/jf_data01.dbf? /home/oracle/hotbk/
alter tablespace JF_DATA end backup;

alter tablespace JF_DATA begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/jf_data02.dbf? /home/oracle/hotbk/
alter tablespace JF_DATA end backup;

alter tablespace T2 begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/t2a.dbf? /home/oracle/hotbk/
alter tablespace T2 end backup;

SQL> select? CHECKPOINT_CHANGE#? from v$database;

CHECKPOINT_CHANGE#
------------------
?????????? 1079325
SQL> SELECT COUNT(*) FROM DAODAO;

? COUNT(*)
----------
??? 500002

SQL> select? CHECKPOINT_CHANGE#? from v$database;

CHECKPOINT_CHANGE#
------------------
?????????? 1079325
SQL> SELECT COUNT(*) FROM DAODAO;

? COUNT(*)
----------
??? 500002

SQL> DELETE FROM DAODAO;

500002 rows deleted.
SQL> select? CHECKPOINT_CHANGE#? from v$database;

CHECKPOINT_CHANGE#
------------------
?????????? 1079325

SQL> conn / as sysdba
Connected.
SQL> archive log list;
Database log mode????????????? Archive Mode
Automatic archival???????????? Enabled
Archive destination??????????? /home/oracle/archivelog
Oldest online log sequence???? 7
Next log sequence to archive?? 10
Current log sequence?????????? 10
SQL> select? CHECKPOINT_CHANGE#? from v$database;

CHECKPOINT_CHANGE#
------------------
?????????? 1089560
SQL> drop table daodao purge;

Table dropped.

SQL> purge recyclebin;

Recyclebin purged.

挖掘dml语句需要将数据库置为追加日志数据模式:
默认只是记录ddl语句
alter database add SUPPLEMENTAL LOG data;

SQL> select group#,status from v$log where status='CURRENT';

??? GROUP# STATUS
---------- ----------------
???????? 2 CURRENT
SQL> SELECT MEMBER FROM? V$LOGFILE WHERE GROUP#=2;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/mdnss/redo02.log

挖掘dml语句需要将数据库置为追加日志数据模式:
默认只是记录ddl语句
alter database add SUPPLEMENTAL LOG data;
SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/mdnss/redo02.log',dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL>? exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/mdnss/redo01.log',dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/mdnss/redo03.log',dbms_logmnr.addfile);

PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

select scn,sql_redo from v$logmnr_contents where lower(sql_redo) like '%drop%' and seg_name='DAODAO'? and seg_owner='SCOTT'

SQL> select scn,sql_redo from v$logmnr_contents where lower(sql_redo) like '%drop%' and seg_name='DAODAO'? and seg_owner='SCOTT';

?????? SCN
----------
SQL_REDO
--------------------------------------------------------------------------------
?? 1089685
drop table daodao purge;
说明这个SCN号上面删除了这个对应的表所以得进行还原

SQL> select * from v$log;

SQL> select? CHECKPOINT_CHANGE#? from v$database;

CHECKPOINT_CHANGE#
------------------
?????????? 1079325

alter tablespace USERS begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/users01.dbf? /home/oracle/hotbk/
alter tablespace USERS end backup;

alter tablespace UNDOTBS1 begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/undotbs01.dbf? /home/oracle/hotbk/
alter tablespace UNDOTBS1 end backup;

alter tablespace SYSAUX begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/sysaux01.dbf? /home/oracle/hotbk/
alter tablespace SYSAUX end backup;

alter tablespace SYSTEM begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/system01.dbf? /home/oracle/hotbk/
alter tablespace SYSTEM end backup;

alter tablespace JF_DATA begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/jf_data01.dbf? /home/oracle/hotbk/
alter tablespace JF_DATA end backup;

alter tablespace JF_DATA begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/jf_data02.dbf? /home/oracle/hotbk/
alter tablespace JF_DATA end backup;

alter tablespace T2 begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/t2a.dbf? /home/oracle/hotbk/
alter tablespace T2 end backup;

alter database backup controlfile to '/home/oracle/hotbk/ctl_after_reset.bk';
alter database backup controlfile to trace as '/home/oracle/hotbk/cre_ctl.sql';
SQL> select? CHECKPOINT_CHANGE#? from v$database;

CHECKPOINT_CHANGE#
------------------
?????????? 1079325
SQL> SELECT COUNT(*) FROM DAODAO;

? COUNT(*)
----------
??? 500002

SQL> select * from v$log;

??? GROUP#??? THREAD#? SEQUENCE#????? BYTES? BLOCKSIZE??? MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS?????????? FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
???????? 1????????? 1????????? 9?? 52428800??????? 512????????? 1 YES
INACTIVE?????????????? 1089570 31-JUL-13????? 1089575 31-JUL-13

???????? 2????????? 1???????? 10?? 52428800??????? 512????????? 1 NO
CURRENT??????????????? 1089575 31-JUL-13?? 2.8147E+14

???????? 3????????? 1????????? 7?? 52428800??????? 512????????? 1 YES
INACTIVE?????????????? 1089560 31-JUL-13????? 1089565 31-JUL-13

???????? 4????????? 1????????? 8?? 52428800??????? 512????????? 1 YES
INACTIVE?????????????? 1089565 31-JUL-13????? 1089570 31-JUL-13

select * from v$logfile

??? GROUP# STATUS? TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
???????? 3???????? ONLINE
/u01/app/oracle/oradata/mdnss/redo03.log
NO

???????? 2???????? ONLINE
/u01/app/oracle/oradata/mdnss/redo02.log
NO

???????? 1???????? ONLINE
/u01/app/oracle/oradata/mdnss/redo01.log
NO

???????? 4???????? ONLINE
/u01/app/oracle/oradata/mdnss/redo09.log
NO

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

[oracle@node1 hotbk]$ cp -v? ./*.dbf /u01/app/oracle/oradata/mdnss/
`./jf_data01.dbf' -> `/u01/app/oracle/oradata/mdnss/jf_data01.dbf'
`./jf_data02.dbf' -> `/u01/app/oracle/oradata/mdnss/jf_data02.dbf'
`./sysaux01.dbf' -> `/u01/app/oracle/oradata/mdnss/sysaux01.dbf'
`./system01.dbf' -> `/u01/app/oracle/oradata/mdnss/system01.dbf'
`./t2a.dbf' -> `/u01/app/oracle/oradata/mdnss/t2a.dbf'
`./undotbs01.dbf' -> `/u01/app/oracle/oradata/mdnss/undotbs01.dbf'
`./users01.dbf' -> `/u01/app/oracle/oradata/mdnss/users01.dbf'

[oracle@node1 ~]$ ls /home/oracle/hotbk
jf_data01.dbf? jf_data02.dbf? sysaux01.dbf? system01.dbf? t2a.dbf? undotbs01.dbf? users01.dbf
[oracle@node1 ~]$ ls /u01/app/oracle/oradata/mdnss
control01.ctl? jf_data02.dbf? redo02.log? redo09.log?? redo10.log??? system01.dbf? temp01.dbf???? users01.dbf
jf_data01.dbf? redo01.log???? redo03.log? redo10b.log? sysaux01.dbf? t2a.dbf?????? undotbs01.dbf
[oracle@node1 ~]$ cp -v /home/oracle/hotbk/ctl_after_reset.bk /u01/app/oracle/oradata/mdnss/control01.ctl
`/home/oracle/hotbk/ctl_after_reset.bk' -> `/u01/app/oracle/oradata/mdnss/control01.ctl'
[oracle@node1 ~]$ cp -v /home/oracle/hotbk/ctl_after_reset.bk /u01/app/oracle/flash_recovery_area/mdnss/control02.ctl
`/home/oracle/hotbk/ctl_after_reset.bk' -> `/u01/app/oracle/flash_recovery_area/mdnss/control02.ctl'

startup mount

SQL> recover database using backup controlfile until change 1089684;?
ORA-00279: change 1086571 generated at 07/31/2013 16:01:31 needed for thread 1
ORA-00289: suggestion : /home/oracle/archivelog/1_3_822209838.dbf
ORA-00280: change 1086571 for thread 1 is in sequence #3

SQL> select * from v$recover_file;?

???? FILE# ONLINE? ONLINE_
---------- ------- -------
ERROR??????????????????????????????????????????????????????????????? CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
???????? 1 ONLINE? ONLINE
???????????????????????????????????????????????????????????????????? 1086670
31-JUL-13

???????? 2 ONLINE? ONLINE
???????????????????????????????????????????????????????????????????? 1086631
31-JUL-13

???????? 3 ONLINE? ONLINE
???????????????????????????????????????????????????????????????????? 1086599
31-JUL-13

???????? 4 ONLINE? ONLINE
???????????????????????????????????????????????????????????????????? 1086571

???????? 5 ONLINE? ONLINE
???????????????????????????????????????????????????????????????????? 1086706
31-JUL-13

???????? 6 ONLINE? ONLINE

???? FILE# ONLINE? ONLINE_
---------- ------- -------
ERROR??????????????????????????????????????????????????????????????? CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
???????????????????????????????????????????????????????????????????? 1086725
31-JUL-13

???????? 7 ONLINE? ONLINE
???????????????????????????????????????????????????????????????????? 1086744
31-JUL-13

7 rows selected.
注意这个SCN是通过日志查出的SCN -1的状态1089684=1089685-1的SCN状态处去
SQL> recover database using backup controlfile until change 1089684;?
ORA-00279: change 1086571 generated at 07/31/2013 16:01:31 needed for thread 1
ORA-00289: suggestion : /home/oracle/archivelog/1_3_822209838.dbf
ORA-00280: change 1086571 for thread 1 is in sequence #3

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/home/oracle/archivelog/1_3_822209838.dbf
ORA-00279: change 1087756 generated at 07/31/2013 16:10:54 needed for thread 1
ORA-00289: suggestion : /home/oracle/archivelog/1_4_822209838.dbf
ORA-00280: change 1087756 for thread 1 is in sequence #4
ORA-00278: log file '/home/oracle/archivelog/1_3_822209838.dbf' no longer
needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
?/home/oracle/archivelog/1_4_822209838.dbf
ORA-00279: change 1088643 generated at 07/31/2013 16:11:10 needed for thread 1
ORA-00289: suggestion : /home/oracle/archivelog/1_5_822209838.dbf
ORA-00280: change 1088643 for thread 1 is in sequence #5
ORA-00278: log file '/home/oracle/archivelog/1_4_822209838.dbf' no longer
needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/home/oracle/archivelog/1_5_822209838.dbf
ORA-00279: change 1089508 generated at 07/31/2013 16:11:27 needed for thread 1
ORA-00289: suggestion : /home/oracle/archivelog/1_6_822209838.dbf
ORA-00280: change 1089508 for thread 1 is in sequence #6
ORA-00278: log file '/home/oracle/archivelog/1_5_822209838.dbf' no longer
needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
?/home/oracle/archivelog/1_6_822209838.dbf
ORA-00279: change 1089560 generated at 07/31/2013 16:13:29 needed for thread 1
ORA-00289: suggestion : /home/oracle/archivelog/1_7_822209838.dbf
ORA-00280: change 1089560 for thread 1 is in sequence #7
ORA-00278: log file '/home/oracle/archivelog/1_6_822209838.dbf' no longer
needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
?/home/oracle/archivelog/1_7_822209838.dbf
ORA-00279: change 1089565 generated at 07/31/2013 16:13:33 needed for thread 1
ORA-00289: suggestion : /home/oracle/archivelog/1_8_822209838.dbf
ORA-00280: change 1089565 for thread 1 is in sequence #8
ORA-00278: log file '/home/oracle/archivelog/1_7_822209838.dbf' no longer
needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
?/home/oracle/archivelog/1_8_822209838.dbf
ORA-00279: change 1089570 generated at 07/31/2013 16:13:37 needed for thread 1
ORA-00289: suggestion : /home/oracle/archivelog/1_9_822209838.dbf
ORA-00280: change 1089570 for thread 1 is in sequence #9
ORA-00278: log file '/home/oracle/archivelog/1_8_822209838.dbf' no longer
needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/home/oracle/archivelog/1_9_822209838.dbf
ORA-00279: change 1089575 generated at 07/31/2013 16:13:40 needed for thread 1
ORA-00289: suggestion : /home/oracle/archivelog/1_10_822209838.dbf
ORA-00280: change 1089575 for thread 1 is in sequence #10
ORA-00278: log file '/home/oracle/archivelog/1_9_822209838.dbf' no longer
needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/home/oracle/archivelog/1_10_822209838.dbf
ORA-00308: cannot open archived log
'/home/oracle/archivelog/1_10_822209838.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

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

从上面查询的CURRENT日志是group#=2号文件其他组的状态组都是inactive状态

SQL> select * from v$log;

???????? 2????????? 1???????? 10?? 52428800??????? 512????????? 1 NO
CURRENT??????????????? 1089575 31-JUL-13?? 2.8147E+14

???????? 3????????? 1????????? 7?? 52428800??????? 512????????? 1 YES
INACTIVE?????????????? 1089560 31-JUL-13????? 1089565 31-JUL-13

???????? 4????????? 1????????? 8?? 52428800??????? 512????????? 1 YES
INACTIVE?????????????? 1089565 31-JUL-13????? 1089570 31-JUL-13

select * from v$logfile

???????? 2???????? ONLINE
/u01/app/oracle/oradata/mdnss/redo02.log
NO

所以在应用完所有归档日志文件之前来试用输入在线日志就可以恢复到对应的SCN状态点
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/mdnss/redo02.log
Log applied.
Media recovery complete.
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/mdnss/redo02.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.
误删除的表已经回来了
SQL> select count(*) from scott.daodao;

? COUNT(*) ---------- ???????? 0

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!