生成备份所有数据文件的脚本: 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
FILE# ONLINE ONLINE_ ---------- ------- ------- ERROR CHANGE# ----------------------------------------------------------------- ---------- TIME ---------
3 ONLINE ONLINE 1086599 31-JUL-13
4 ONLINE ONLINE 1086571
FILE# ONLINE ONLINE_ ---------- ------- ------- ERROR CHANGE# ----------------------------------------------------------------- ---------- TIME --------- 31-JUL-13
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;
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
所以在应用完所有归档日志文件之前 来试用输入在线日志 就 可以恢复到对应的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