REDO log 日志挖掘数据恢复
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> select count(*) from test;
COUNT(*)
----------
100
SQL> set long 999
SQL> select dbms_metadata.get_ddl('TABLE','TEST') from dual;
DBMS_METADATA.GET_DDL('TABLE','TEST')
--------------------------------------------------------------------------------
CREATE TABLE "SYS"."TEST"
( "ID" NUMBER(*,0),
"AGE" NUMBER(*,0) NOT NULL ENABLE,
PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
DBMS_METADATA.GET_DDL('TABLE','TEST')
--------------------------------------------------------------------------------
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
SQL> delete from test;
100 rows deleted.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select recid,name,completion_time from v$archived_log where name not null order by 3; //查看当前ARCHIVE LOG
RECID NAME COMPLETION_TIME
---------- ---------------------------------------------------------------------- -------------------
50 +DATA/asm11gr2/archivelog/2013_03_21/thread_1_seq_55.319.810643001 2013-03-21 10:36:41
51 +DATA/asm11gr2/archivelog/2013_03_21/thread_1_seq_56.317.810644341 2013-03-21 10:59:01
52 +DATA/asm11gr2/archivelog/2013_03_21/thread_1_seq_57.318.810645283 2013-03-21 11:14:42
53 +DATA/asm11gr2/archivelog/2013_03_21/thread_1_seq_58.312.810656927 2013-03-21 14:28:47
54 +DATA/asm11gr2/archivelog/2013_03_21/thread_1_seq_59.311.810657029 2013-03-21 14:30:29
55 +DATA/asm11gr2/archivelog/2013_03_21/thread_1_seq_60.310.810657051 2013-03-21 14:30:50
56 +DATA/asm11gr2/archivelog/2013_03_21/thread_1_seq_61.293.810684065 2013-03-21 22:01:10
57 +DATA/asm11gr2/archivelog/2013_03_22/thread_1_seq_62.281.810698403 2013-03-22 02:00:06
58 +DATA/asm11gr2/archivelog/2013_03_22/thread_1_seq_63.325.810721773 2013-03-22 08:29:39
59 +DATA/asm11gr2/archivelog/2013_03_22/thread_1_seq_64.335.810770403 2013-03-22 22:00:08
60 +DATA/asm11gr2/archivelog/2013_03_22/thread_1_seq_65.336.810774041 2013-03-22 23:00:43
RECID NAME COMPLETION_TIME
---------- ---------------------------------------------------------------------- -------------------
61 +DATA/asm11gr2/archivelog/2013_03_23/thread_1_seq_66.340.810807425 2013-03-23 08:17:08
62 +DATA/asm11gr2/archivelog/2013_03_23/thread_1_seq_67.346.810814045 2013-03-23 10:07:29
63 +DATA/asm11gr2/archivelog/2013_03_23/thread_1_seq_68.350.810839437 2013-03-23 17:10:40
64 +DATA/asm11gr2/archivelog/2013_03_23/thread_1_seq_69.351.810839471 2013-03-23 17:11:10
65 +DATA/asm11gr2/archivelog/2013_03_24/thread_1_seq_70.355.810874845 2013-03-24 03:00:48
66 +DATA/asm11gr2/archivelog/2013_03_24/thread_1_seq_71.356.810900367 2013-03-24 10:06:11
67 +DATA/asm11gr2/archivelog/2013_03_24/thread_1_seq_72.357.810921251 2013-03-24 15:54:14
68 +DATA/asm11gr2/archivelog/2013_03_25/thread_1_seq_73.358.810950445 2013-03-25 00:00:47
69 +DATA/asm11gr2/archivelog/2013_03_25/thread_1_seq_74.359.810986657 2013-03-25 10:04:19
70 +DATA/asm11gr2/archivelog/2013_03_25/thread_1_seq_75.360.810991805 2013-03-25 11:30:09
71 +DATA/asm11gr2/archivelog/2013_03_25/thread_1_seq_76.367.810992781 2013-03-25 11:46:24
RECID NAME COMPLETION_TIME
---------- ---------------------------------------------------------------------- -------------------
72 +DATA/asm11gr2/archivelog/2013_03_25/thread_1_seq_77.381.811001473 2013-03-25 14:11:18
73 +DATA/asm11gr2/archivelog/2013_03_25/thread_1_seq_78.384.811029993 2013-03-25 22:06:45
74 +DATA/asm11gr2/archivelog/2013_03_27/thread_1_seq_79.386.811192075 2013-03-27 19:08:02
75 +DATA/asm11gr2/archivelog/2013_03_28/thread_1_seq_80.388.811248871 2013-03-28 10:54:37
76 +DATA/asm11gr2/archivelog/2013_03_28/thread_1_seq_81.389.811267325 2013-03-28 16:02:07
77 +DATA/asm11gr2/archivelog/2013_03_28/thread_1_seq_82.390.811288871 2013-03-28 22:01:16
78 +DATA/asm11gr2/archivelog/2013_03_28/thread_1_seq_83.371.811289287 2013-03-28 22:08:11
79 +DATA/asm11gr2/archivelog/2013_03_29/thread_1_seq_84.370.811346249 2013-03-29 13:57:33
80 +DATA/asm11gr2/archivelog/2013_03_29/thread_1_seq_85.369.811346253 2013-03-29 13:57:33
81 +DATA/asm11gr2/archivelog/2013_03_29/thread_1_seq_86.329.811347555 2013-03-29 14:19:14
82 +DATA/asm11gr2/archivelog/2013_03_29/thread_1_seq_87.342.811347557 2013-03-29 14:19:17
RECID NAME COMPLETION_TIME
---------- ---------------------------------------------------------------------- -------------------
83 +DATA/asm11gr2/archivelog/2013_03_29/thread_1_seq_88.330.811347563 2013-03-29 14:19:22
56 rows selected.
SQL> show parameter utl_file;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string /dbsoft/oracle/dictfile
创建数据字典
SQL> exec dbms_logmnr_d.build(dictionary_filename => 'logmnr_dict.ora',dictionary_location => '/dbsoft/oracle/dictfile',options => dbms_logmnr_d.store_in_flat_file);
PL/SQL procedure successfully completed.
加载archive log
SQL> exec dbms_logmnr.add_logfile('+DATA/asm11gr2/archivelog/2013_03_29/thread_1_seq_86.329.811347555',options => dbms_logmnr.new)
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.add_logfile('+DATA/asm11gr2/archivelog/2013_03_29/thread_1_seq_87.342.811347557',options => dbms_logmnr.addfile)
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.add_logfile('+DATA/asm11gr2/archivelog/2013_03_29/thread_1_seq_88.330.811347563',options => dbms_logmnr.addfile)
PL/SQL procedure successfully completed.
//加载dictfile,启动分析
SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/dbsoft/oracle/dictfile/logmnr_dict.ora');
PL/SQL procedure successfully completed.
查找segment_name
SQL> select operation,sql_redo,sql_undo from v$logmnr_contents where seg_name like '%TEST%';
OPERATION SQL_REDO SQL_UNDO
---------- ------------------------------------------------------------ ----------------------------------------------------------------------
DELETE delete from "SYS"."TEST" where "ID" = '1' and "AGE" = '1' an insert into "SYS"."TEST"("ID","AGE") values ('1','1');
d ROWID = 'AAASkRAABAAAU9JAAA';
DELETE delete from "SYS"."TEST" where "ID" = '2' and "AGE" = '2' an insert into "SYS"."TEST"("ID","AGE") values ('2','2');
d ROWID = 'AAASkRAABAAAU9JAAB';
DELETE delete from "SYS"."TEST" where "ID" = '3' and "AGE" = '3' an insert into "SYS"."TEST"("ID","AGE") values ('3','3');
d ROWID = 'AAASkRAABAAAU9JAAC';
DELETE delete from "SYS"."TEST" where "ID" = '4' and "AGE" = '4' an insert into "SYS"."TEST"("ID","AGE") values ('4','4');
d ROWID = 'AAASkRAABAAAU9JAAD';
......
OPERATION SQL_REDO SQL_UNDO
---------- ------------------------------------------------------------ ----------------------------------------------------------------------
DELETE delete from "SYS"."TEST" where "ID" = '97' and "AGE" = '97' insert into "SYS"."TEST"("ID","AGE") values ('97','97');
and ROWID = 'AAASkRAABAAAU9JABg';
DELETE delete from "SYS"."TEST" where "ID" = '98' and "AGE" = '98' insert into "SYS"."TEST"("ID","AGE") values ('98','98');
and ROWID = 'AAASkRAABAAAU9JABh';
DELETE delete from "SYS"."TEST" where "ID" = '99' and "AGE" = '99' insert into "SYS"."TEST"("ID","AGE") values ('99','99');
and ROWID = 'AAASkRAABAAAU9JABi';
OPERATION SQL_REDO SQL_UNDO
---------- ------------------------------------------------------------ ----------------------------------------------------------------------
DELETE delete from "SYS"."TEST" where "ID" = '100' and "AGE" = '100 insert into "SYS"."TEST"("ID","AGE") values ('100','100');
' and ROWID = 'AAASkRAABAAAU9JABj';
100 rows selected.
打完收工!
