利用Logmnr找回误删除数据－自己的测试过程
SQL> desc dbms_logmnr
Element Type
------------------------ ---------
NEW CONSTANT
REMOVEFILE CONSTANT
ADDFILE CONSTANT
NO_DICT_RESET_ONSELECT CONSTANT
COMMITTED_DATA_ONLY CONSTANT
SKIP_CORRUPTION CONSTANT
DDL_DICT_TRACKING CONSTANT
DICT_FROM_ONLINE_CATALOG CONSTANT
DICT_FROM_REDO_LOGS CONSTANT
NO_SQL_DELIMITER CONSTANT
PRINT_PRETTY_SQL CONSTANT
CONTINUOUS_MINE CONSTANT
NO_ROWID_IN_STMT CONSTANT
LENGTH SUBTYPE
THREADID SUBTYPE
VALID_SQL CONSTANT
INVALID_SQL CONSTANT
UNGUARANTEED_SQL CONSTANT
HOLE_IN_LOGSTREAM CONSTANT
CORRUPTED_BLK_IN_REDO CONSTANT
LOGFILENAMETEMPLATE VARIABLE
LOGFILENAME SUBTYPE
LOGFILEDESCTEMPLATE VARIABLE
LOGFILEDESCRIPTION SUBTYPE
START_LOGMNR PROCEDURE
ADD_LOGFILE PROCEDURE
END_LOGMNR PROCEDURE
COLUMN_PRESENT FUNCTION
MINE_VALUE FUNCTION
REMOVE_LOGFILE PROCEDURE
SQL> exec sys.dbms_logmnr.add_logfile(LogFileName => '/opt/ora10g/arch/1_12_663085813.dbf',Options => sys.dbms_logmnr.NEW);
PL/SQL procedure successfully completed
SQL> exec sys.dbms_logmnr.start_logmnr(Options => sys.dbms_logmnr.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed
SQL> desc v$logmnr_contents
Name Type Nullable Default Comments
---------------- -------------- -------- ------- --------
SCN NUMBER Y
CSCN NUMBER Y
TIMESTAMP DATE Y
COMMIT_TIMESTAMP DATE Y
THREAD# NUMBER Y
LOG_ID NUMBER Y
XIDUSN NUMBER Y
XIDSLT NUMBER Y
XIDSQN NUMBER Y
PXIDUSN NUMBER Y
PXIDSLT NUMBER Y
PXIDSQN NUMBER Y
RBASQN NUMBER Y
RBABLK NUMBER Y
RBABYTE NUMBER Y
UBAFIL NUMBER Y
UBABLK NUMBER Y
UBAREC NUMBER Y
UBASQN NUMBER Y
ABS_FILE# NUMBER Y
REL_FILE# NUMBER Y
DATA_BLK# NUMBER Y
DATA_OBJ# NUMBER Y
DATA_OBJD# NUMBER Y
SEG_OWNER VARCHAR2(32) Y
SEG_NAME VARCHAR2(256) Y
TABLE_NAME VARCHAR2(32) Y
SEG_TYPE NUMBER Y
SEG_TYPE_NAME VARCHAR2(32) Y
TABLE_SPACE VARCHAR2(32) Y
ROW_ID VARCHAR2(18) Y
SESSION# NUMBER Y
SERIAL# NUMBER Y
USERNAME VARCHAR2(30) Y
SESSION_INFO VARCHAR2(4000) Y
TX_NAME VARCHAR2(256) Y
ROLLBACK NUMBER Y
OPERATION VARCHAR2(32) Y
OPERATION_CODE NUMBER Y
SQL_REDO VARCHAR2(4000) Y
SQL_UNDO VARCHAR2(4000) Y
RS_ID VARCHAR2(32) Y
SEQUENCE# NUMBER Y
SSN NUMBER Y
CSF NUMBER Y
INFO VARCHAR2(32) Y
STATUS NUMBER Y
REDO_VALUE NUMBER Y
UNDO_VALUE NUMBER Y
SQL_COLUMN_TYPE VARCHAR2(30) Y
SQL_COLUMN_NAME VARCHAR2(30) Y
REDO_LENGTH NUMBER Y
REDO_OFFSET NUMBER Y
UNDO_LENGTH NUMBER Y
UNDO_OFFSET NUMBER Y
DATA_OBJV# NUMBER Y
SAFE_RESUME_SCN NUMBER Y
XID RAW(8) Y
PXID RAW(8) Y
AUDIT_SESSIONID NUMBER Y
SQL> select * from v$logmnr_contents where rownum<5;
SCN CSCN TIMESTAMP COMMIT_TIMESTAMP THREAD# LOG_ID XIDUSN XIDSLT XIDSQN PXIDUSN PXIDSLT PXIDSQN RBASQN RBABLK RBABYTE UBAFIL UBABLK UBAREC UBASQN ABS_FILE# REL_FILE# DATA_BLK# DATA_OBJ# DATA_OBJD# SEG_OWNER SEG_NAME TABLE_NAME SEG_TYPE SEG_TYPE_NAME TABLE_SPACE ROW_ID SESSION# SERIAL# USERNAME SESSION_INFO TX_NAME ROLLBACK OPERATION OPERATION_CODE SQL_REDO SQL_UNDO RS_ID SEQUENCE# SSN CSF INFO STATUS REDO_VALUE UNDO_VALUE SQL_COLUMN_TYPE SQL_COLUMN_NAME REDO_LENGTH REDO_OFFSET UNDO_LENGTH UNDO_OFFSET DATA_OBJV# SAFE_RESUME_SCN XID PXID AUDIT_SESSIONID
---------- ---------- ----------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------------------- -------------------------------------------------------------------------------- -------------------------------- ---------- -------------------------------- -------------------------------- ------------------ ---------- ---------- ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------- -------------------------------- -------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------- ---------- ---------- ---------- -------------------------------- ---------- ---------- ---------- ------------------------------ ------------------------------ ----------- ----------- ----------- ----------- ---------- --------------- ---------------- ---------------- ---------------
764468 2008-8-26 2 1 12 0 61 48 0 61 48 12 4 16 1 0 0 0 0 0 0 0 0 0 AAAAAAAAAAAAAAAAAA 0 0 0 START 6 set transaction read write; 0x00000c.00000004.0010 1 0 0 0 0 1 0 0 0 0 0 00003D0030000000 00003D0030000000 0
764468 2008-8-26 2 1 12 0 61 48 0 61 48 12 4 16 1 0 0 0 1 1 106 15 15 SYS UNDO$ UNDO$ 2 TABLE SYSTEM AAAAAPAABAAAABqAAB 0 0 0 UPDATE 3 update "SYS"."UNDO$" set "NAME" = '_SYSSMU1$', "USER#" = '1', "FILE#" = '2', "BL update "SYS"."UNDO$" set "NAME" = '_SYSSMU1$', "USER#" = '1', "FILE#" = '2', "BL 0x00000c.00000004.0010 1 0 0 no supplemental log data found 3 2 3 0 0 0 0 0 00003D0030000000 00003D0030000000 0
764469 764469 2008-8-26 2 2008-8-26 22:17: 1 12 0 61 48 0 61 48 12 5 136 1 0 0 0 1 0 0 0 0 0 AAAAAAAAAAAAAAAAAA 0 0 0 COMMIT 7 commit; 0x00000c.00000005.0088 1 0 0 0 4 5 0 0 0 0 0 764469 00003D0030000000 00003D0030000000 0
764470 2008-8-26 2 1 12 0 7 49 0 7 49 12 6 16 1 0 0 0 0 0 0 0 0 0 AAAAAAAAAAAAAAAAAA 0 0 0 START 6 set transaction read write; 0x00000c.00000006.0010 1 0 0 0 6 7 0 0 0 0 0 0000070031000000 0000070031000000 0
SQL> select t.SCN,t.TIMESTAMP,t.SEG_OWNER,t.OPERATION,t.SQL_REDO,t.SQL_UNDO from v$logmnr_contents t where t.SEG_NAME='T_1';
SCN TIMESTAMP SEG_OWNER OPERATION SQL_REDO SQL_UNDO
---------- ----------- -------------------------------- -------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
778208 2008-8-27 0 TSTUSER DDL create table t_1 (id number(10),name varchar2(20),age number(10) default 20,prim
;
778240 2008-8-27 0 TSTUSER INSERT insert into "TSTUSER"."T_1"("ID","NAME","AGE") values ('1','t1','13'); delete from "TSTUSER"."T_1" where "ID" = '1' and "NAME" = 't1' and "AGE" = '13'
778248 2008-8-27 0 TSTUSER INSERT insert into "TSTUSER"."T_1"("ID","NAME","AGE") values ('2','t2','14'); delete from "TSTUSER"."T_1" where "ID" = '2' and "NAME" = 't2' and "AGE" = '14'
778248 2008-8-27 0 TSTUSER INSERT insert into "TSTUSER"."T_1"("ID","NAME","AGE") values ('3','t3','15'); delete from "TSTUSER"."T_1" where "ID" = '3' and "NAME" = 't3' and "AGE" = '15'
778248 2008-8-27 0 TSTUSER INSERT insert into "TSTUSER"."T_1"("ID","NAME","AGE") values ('4','t4','16'); delete from "TSTUSER"."T_1" where "ID" = '4' and "NAME" = 't4' and "AGE" = '16'
778738 2008-8-27 0 TSTUSER DELETE delete from "TSTUSER"."T_1" where "ID" = '1' and "NAME" = 't1' and "AGE" = '13' insert into "TSTUSER"."T_1"("ID","NAME","AGE") values ('1','t1','13');
778738 2008-8-27 0 TSTUSER DELETE delete from "TSTUSER"."T_1" where "ID" = '2' and "NAME" = 't2' and "AGE" = '14' insert into "TSTUSER"."T_1"("ID","NAME","AGE") values ('2','t2','14');
778738 2008-8-27 0 TSTUSER DELETE delete from "TSTUSER"."T_1" where "ID" = '3' and "NAME" = 't3' and "AGE" = '15' insert into "TSTUSER"."T_1"("ID","NAME","AGE") values ('3','t3','15');
778738 2008-8-27 0 TSTUSER DELETE delete from "TSTUSER"."T_1" where "ID" = '4' and "NAME" = 't4' and "AGE" = '16' insert into "TSTUSER"."T_1"("ID","NAME","AGE") values ('4','t4','16');
9 rows selected
SQL> create table logmnr_contents as select * from v$logmnr_contents;
Table created
SQL> exec sys.dbms_logmnr.end_logmnr
PL/SQL procedure successfully completed
SQL> select t.SCN,t.TIMESTAMP,t.SEG_OWNER,t.OPERATION,t.SQL_REDO,t.SQL_UNDO from v$logmnr_contents t where t.SEG_NAME='T_1';
select t.SCN,t.TIMESTAMP,t.SEG_OWNER,t.OPERATION,t.SQL_REDO,t.SQL_UNDO from v$logmnr_contents t where t.SEG_NAME='T_1'
ORA-01306: 在从 v$logmnr_contents 中选择之前必须调用 dbms_logmnr.start_logmnr()
SQL>
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/8102208/viewspace-436085/ ，如需转载，请注明出处，否则将追究法律责任。
