利用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博客 ” ，链接：http://blog.itpub.net/8102208/viewspace-436084/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8102208/viewspace-436084/