[Oracle] Oracle logminer (로그마이너)

// Oracle 로그마이너

 

SQL > @redo

 

    GROUP# MEMBER                                                MB        SEQ STATUS
---------- --------------------------------------------- ---------- ---------- ----------------
         1 +DATA/RMANDB/ONLINELOG/redo1_a.log                    50          4 CURRENT
         1 +DATA/RMANDB/ONLINELOG/redo1_b.log                    50          4 CURRENT
         2 +DATA/RMANDB/ONLINELOG/redo2_a.log                    50          2 INACTIVE
         2 +DATA/RMANDB/ONLINELOG/redo2_b.log                    50          2 INACTIVE
         3 +DATA/RMANDB/ONLINELOG/redo3_a.log                    50          3 INACTIVE
         3 +DATA/RMANDB/ONLINELOG/redo3_b.log                    50          3 INACTIVE

 

SQL > select object_name || '.' || procedure_name as proc_name from dba_procedures where object_name like '%LOGMNR' and procedure_name like '%LOG%';

 

PROC_NAME
---------------------------------------------------
DBMS_LOGMNR.REMOVE_LOGFILE
DBMS_LOGMNR.END_LOGMNR
DBMS_LOGMNR.ADD_LOGFILE
DBMS_LOGMNR.START_LOGMNR

SQL > EXEC DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME=>'+DATA/RMANDB/ONLINELOG/redo2_a.log',OPTIONS=>DBMS_LOGMNR.NEW);

 

SQL > EXEC DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME=>'+DATA/RMANDB/ONLINELOG/redo3_a.log',OPTIONS=>DBMS_LOGMNR.ADDFILE);

 

SQL > EXEC DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME=>'+DATA/RMANDB/ONLINELOG/redo1_a.log',OPTIONS=>DBMS_LOGMNR.ADDFILE);

 

SQL > EXEC DBMS_LOGMNR.START_LOGMNR(OPTIONS=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

 

/* container database 사용시

SQL> ALTER SESSION SET CONATINER='PDB1';

SQL> CREATE DIRECTORY SCRIPTS AS '/tmp/scripts';

SQL> EXEC DBMS_LOGMNR_D.BUILD('PDB1.dic','SCRIPTS', DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

SQL> ALTER SESSION SET CONTAINER='CDB$ROOT';

SQL> EXEC DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME=>'/u01/app/oracle/product/19.3.0/db_1/dbs/arch/1_33_1103592843.dbf',OPTIONS=>DBMS_LOGMNR.NEW);

SQL> EXEC DBMS_LOGMNR.START_LOGMNR (DICTFILENAME => '/tmp/scripts/PDB1.dic',OPTIONS => DBMS_LOGMNR.COMMITTED_DATA_ONLY);

*/

 

SQL > select scn,start_scn,to_char(timestamp,'YYYY-MM-DD HH24:MI:SS') as d_time,
to_char(start_timestamp,'YYYY-MM-DD HH24:MI:SS') as s_time,
to_char(commit_timestamp,'YYYY-MM-DD HH24:MI:SS') as e_time,
OPERATION,
SQL_REDO from v$logmnr_contents
where SQL_REDO LIKE '%&query%'

Enter value for query: <찾고자 하는 쿼리 입력>

 

SQL > EXECUTE DBMS_LOGMNR.END_LOGMNR();

 

// 자세한 내용 참조

bestugi.tistory.com/20