一、本文说明：
本文转载于飚哥发表的一篇文章：http://f.dataguru.cn/thread-84252-1-2.html，此处只做转载+模拟。
二、转载内容：
2.1.1、版本和用户：
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> show user; USER is "JACK"
2.1.2、新建测试表：
SQL> create table t_logminer(id int) tablespace jack; Table created. SQL> insert into t_logminer select rownum from dual connect by rownum < 11; 10 rows created. SQL> select * from t_logminer; ID ---------- 1 2 3 4 5 6 7 8 9 10 10 rows selected. SQL> commit; Commit complete.
2.1.3、查询当前的log文件：
SQL> select member from v$logfile where group# in (select group# from v$log where status = 'CURRENT'); MEMBER -------------------------------------------------------------------------------- /u01/app/oracle/oradata/yft/redo03.log
2.1.4、查询当前的SCN：
SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ 2781038
2.1.5、假设此时用户一条误操作并成功commit：
SQL> delete t_logminer where id = 1; 1 row deleted. SQL> commit; Commit complete.
2.1.6、误操作之后的SCN：
SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ 2781164
2.1.7、用户意识到误操作，请求DBA协助恢复。
准备使用logminer尝试找到误操作的数据，并恢复。
添加日志文件：
SQL> exec dbms_logmnr.add_logfile(logfilename => '/u01/app/oracle/oradata/yft/redo03.log',options => dbms_logmnr.new); PL/SQL procedure successfully completed.
2.1.8、开始logminer：
SQL> exec dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog,startscn => 2781038,endscn => 2781164); PL/SQL procedure successfully completed.
2.1.9、logminer提取的日志信息会格式化后存放在视图v$logmnr中，此视图只对当前会话有效：
SQL> desc v$logmnr_contents; Name Null? Type ----------------------------------------- -------- ---------------------------- SCN NUMBER START_SCN NUMBER COMMIT_SCN NUMBER TIMESTAMP DATE START_TIMESTAMP DATE COMMIT_TIMESTAMP DATE XIDUSN NUMBER XIDSLT NUMBER XIDSQN NUMBER XID RAW(8) PXIDUSN NUMBER PXIDSLT NUMBER PXIDSQN NUMBER PXID RAW(8) TX_NAME VARCHAR2(256) OPERATION VARCHAR2(32) OPERATION_CODE NUMBER ROLLBACK NUMBER SEG_OWNER VARCHAR2(32) SEG_NAME VARCHAR2(256) TABLE_NAME VARCHAR2(32) SEG_TYPE NUMBER SEG_TYPE_NAME VARCHAR2(32) TABLE_SPACE VARCHAR2(32) ROW_ID VARCHAR2(18) USERNAME VARCHAR2(30) OS_USERNAME VARCHAR2(4000) MACHINE_NAME VARCHAR2(4000) AUDIT_SESSIONID NUMBER SESSION# NUMBER SERIAL# NUMBER SESSION_INFO VARCHAR2(4000) THREAD# NUMBER SEQUENCE# NUMBER RBASQN NUMBER RBABLK NUMBER RBABYTE NUMBER UBAFIL NUMBER UBABLK NUMBER UBAREC NUMBER UBASQN NUMBER ABS_FILE# NUMBER REL_FILE# NUMBER DATA_BLK# NUMBER DATA_OBJ# NUMBER DATA_OBJV# NUMBER DATA_OBJD# NUMBER SQL_REDO VARCHAR2(4000) SQL_UNDO VARCHAR2(4000) RS_ID VARCHAR2(32) SSN NUMBER CSF NUMBER INFO VARCHAR2(32) STATUS NUMBER REDO_VALUE NUMBER UNDO_VALUE NUMBER SAFE_RESUME_SCN NUMBER CSCN NUMBER OBJECT_ID RAW(16) EDITION_NAME VARCHAR2(30) CLIENT_ID VARCHAR2(64)
2.1.10、查询视图：
1 SQL> select count(*) from v$logmnr_contents; 2 3 COUNT(*) 4 ---------- 5 3366 6 SQL> select operation from v$logmnr_contents; 7 OPERATION 8 -------------------------------- 9 INSERT 10 COMMIT 11 START 12 UPDATE 13 COMMIT 14 START 15 UNSUPPORTED 16 COMMIT 17 UNSUPPORTED 18 UNSUPPORTED 19 UNSUPPORTED 20 21 3366 rows selected. 22 23 SQL> select sql_undo from v$logmnr_contents; 24 SQL_UNDO 25 -------------------------------------------------------------------------------- 26 delete from "SYS"."WRH$_SQL_SUMMARY" where "SNAP_ID" = '11' and "DBID" = '296361 27 1937' and "INSTANCE_NUMBER" = '1' and "TOTAL_SQL" = '618' and "TOTAL_SQL_MEM" = 28 '12578332' and "SINGLE_USE_SQL" = '340' and "SINGLE_USE_SQL_MEM" = '7121376' and 29 ROWID = 'AAABhDAACAAABCcAAC'; 30 31 32 33 update "SYS"."WRM$_SNAPSHOT" set "FLUSH_ELAPSED" = NULL, "STATUS" = '1' where "F 34 LUSH_ELAPSED" = TO_DSINTERVAL('+00000 00:00:06.8') and "STATUS" = '0' and ROWID 35 = 'AAABlvAACAAABeUAAC'; 36 37 38 SQL_UNDO 39 -------------------------------------------------------------------------------- 40 41 42 Unsupported 43 44 SQL> select sql_redo from v$logmnr_contents; 45 SQL_REDO 46 -------------------------------------------------------------------------------- 47 '0','0','1','1','0','0','1','1','0','0','0','0','0','0','10997','10997','16194', 48 '16194','0','0','0','0','0','0','0','0','0','0','0','0','0','0',NULL,NULL,'0','0 49 ','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0'); 50 51 insert into "SYS"."WRH$_SQL_SUMMARY"("SNAP_ID","DBID","INSTANCE_NUMBER","TOTAL_S 52 QL","TOTAL_SQL_MEM","SINGLE_USE_SQL","SINGLE_USE_SQL_MEM") values ('11','2963611 53 937','1','618','12578332','340','7121376'); 54 55 commit; 56 set transaction read write; 57 update "SYS"."WRM$_SNAPSHOT" set "FLUSH_ELAPSED" = TO_DSINTERVAL('+00000 00:00:0 58 59 SQL_REDO 60 -------------------------------------------------------------------------------- 61 6.8'), "STATUS" = '0' where "FLUSH_ELAPSED" IS NULL and "STATUS" = '1' and ROWID 62 = 'AAABlvAACAAABeUAAC'; 63 64 commit; 65 set transaction read write; 66 Unsupported 67 commit; 68 69 70 71 72 3366 rows selected.
在上面的查询中发现啥也没有！
2.1.11、检查logging：
SQL> select force_logging from v$database; FOR --- YES
2.1.12、经过在网上搜索了好久，发现必须要启动supplemental log data!
SQL> shutdown immediate; ORA-01031: insufficient privileges SQL> show user; USER is "JACK" SQL> conn /as sysdba Connected. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 246902784 bytes Fixed Size 1335780 bytes Variable Size 125832732 bytes Database Buffers 117440512 bytes Redo Buffers 2293760 bytes Database mounted. SQL> alter database add supplemental log data; Database altered. SQL> alter database open; Database altered.
2.2、上面的步骤重新走一遍：
SQL> conn jack/jack Connected. SQL> select * from t_logminer; ID ---------- 2 3 4 5 6 7 8 9 10 9 rows selected. SQL> select member from v$logfile where group# in (select group# from v$log where status = 'CURRENT'); MEMBER -------------------------------------------------------------------------------- /u01/app/oracle/oradata/yft/redo03.log SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ 2782607 SQL> delete t_logminer where id=3; 1 row deleted. SQL> commit; Commit complete. SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ 2782616 SQL> exec dbms_logmnr.add_logfile(logfilename => '/u01/app/oracle/oradata/yft/redo03.log',options => dbms_logmnr.new); PL/SQL procedure successfully completed. SQL> exec dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog,startscn => 2782607,endscn => 2782616); PL/SQL procedure successfully completed. SQL> set linesize 150; SQL> col sql_redo for a50; SQL> col sql_undo for a50; SQL> select operation,sql_redo,sql_undo from v$logmnr_contents where table_name = 'T_LOGMINER'; OPERATION SQL_REDO SQL_UNDO -------------------------------- -------------------------------------------------- -------------------------------------------------- DELETE delete from "JACK"."T_LOGMINER" where "ID" = '3' a insert into "JACK"."T_LOGMINER"("ID") values ('3') nd ROWID = 'AAAR7aAAFAAAAU3AAC'; ;
找到了执行的误操作，及相应的恢复数据的undo操作，执行即可恢复。
1777
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
