Oracle Logminer クイックスタート詳細

#1. Prepare the test data (test user)
SQL> conn testSQL> CREATE TABLE test(id varchar2(100));   
INSERT INTO test (id) values ('000001');  
INSERT INTO test (id) values ('000011');
commit;

Note: The following steps are all executed under logminer_test user
#2、View the current online log file of database
SQL>SELECT group#, sequence#, status, first_change#, first_time FROM V$log ORDER BY first_change#;
#Find that only redo001 is the current status.

#3. Add the online log file that needs to be parsed
SQL>exec dbms_logmnr.add_logfile('/home/oracle/app/oracle/oradata/orcl11g/redo01.log', dbms_logmnr.new);

#4、Start Logminer
SQL>
begin
SYS.DBMS_LOGMNR.START_LOGMNR (
options => SYS.DBMS_LOGMNR.skip_corruption + 
SYS.DBMS_LOGMNR.no_sql_delimiter + 
SYS.DBMS_LOGMNR.no_rowid_in_stmt + 
SYS.DBMS_LOGMNR.dict_from_online_catalog + 
SYS.DBMS_LOGMNR.string_literals_in_stmt 
);
end;
 
#5. Query redo log
SQL>select * from V$LOGMNR_CONTENTS;

#6、Stop Logminer
SQL>exec dbms_logmnr.end_logmnr;

INSERT INTO HR.JOBS(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES('IT_WT','Technical Writer', 4000, 11000);

insert into "UNKNOWN". "OBJ# 45522"("COL 1","COL 2","COL 3","COL 4") values
(HEXTORAW('45465f4748'),HEXTORAW('546563686e6963616c20577269746572'),
HEXTORAW('c229'),HEXTORAW('c3020b'));

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( 
   LOGFILENAME => '/oracle/logs/log1.dbf',
   OPTIONS => DBMS_LOGMNR.NEW);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( 
   LOGFILENAME => '/oracle/logs/log2.dbf', 
   OPTIONS => DBMS_LOGMNR.ADDFILE);

   OPTIONS parameter description:
     * DBMS_LOGMNR.SKIP_CORRUPTION - skip the redlog with errors
     * NO_SQL_DELIMITER - do not use ';' to split redo sql
     * DBMS_LOGMNR.NO_ROWID_IN_STMT - By default, SQL_REDO and SQL_UNDO statements used for UPDATE and DELETE operations contain " ROWID =" in the where clause.
     * However, this is inconvenient for applications that want to re-execute SQL statements. When this option is set, "ROWID" is not placed at the end of the refactoring statement
     * DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG - Use online dictionary
     * DBMS_LOGMNR.CONTINUOUS_MINE - requires the use of logs in the same instance where the redo log is generated
     * DBMS_LOGMNR.COMMITTED_DATA_ONLY - When this option is specified, LogMiner groups all DML operations belonging to the same transaction together. Transactions are returned in commit order.
     * DBMS_LOGMNR.STRING_LITERALS_IN_STMT - By default, when formatting formatted SQL statements, SQL_REDO and SQL_UNDO statements use the database session's NLS setting
     * (e.g. NLS_DATE_FORMAT, NLS_NUMERIC_CHARACTERS, etc.). With this option, the ANSI / ISO string literal format will be used

EXECUTE DBMS_LOGMNR.START_LOGMNR( 
   STARTTIME => '01-Jan-2003 08:30:00', 
   ENDTIME => '01-Jan-2003 08:45:00', 
   OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + 
   DBMS_LOGMNR.CONTINUOUS_MINE);

SELECT
    scn,
    timestamp,
    operation,
    seg_owner,
    table_name,
    sql_redo,
    row_id,
    csf
FROM
    v$logmnr_contents
WHERE
    scn > ?