利用Logmnr找回误删除数据－自己的测试过程
create table t_1 (id number(10),name varchar2(20),age number(10) default 20,primary key (id));
select * from t_1;
insert into t_1 values(1,'t1',13);
commit;
insert into t_1 values(2,'t2',14);
insert into t_1 values(3,'t3',15);
insert into t_1 values(4,'t4',16);
commit;
delete from t_1;
commit;
alter system archive log current;
select * from v$archived_log;
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 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-436084/ ，如需转载，请注明出处，否则将追究法律责任。
