Oracle log mining Solution

Oracle log mining solution preparation cd c: \ oracle \ product \ 10.2.0 \ db_1 \ BIN

Sqlplus sys/slims as sysdba; -- check whether archive log list is in archive mode; -- If archive is not found -------------------------- shutdown immediate; startup mount; alter database archivelog; alter database open; create table slims_bak_time (bak_time timestamp); insert into slims_bak_time values (sysdate); commit; select * from slims_bak_time; -- check audit show parameter audit; alter system set audit_sys_operations = TRUE scope = spfile; alter system set audit_trail = db, extended scope = spfile; -- execute the statement and process @ C: \ oracle \ product \ 10.2.0 \ db_1 \ RDBMS \ ADMIN \ dbmslm. SQL @ C: \ oracle \ product \ 10.2.0 \ db_1 \ RDBMS \ ADMIN \ dbmslms. SQL @ C: \ oracle \ product \ 10.2.0 \ db_1 \ RDBMS \ ADMIN \ dbmslmd. sqlshow parameter utl_file_dir; -- specify the dictionary folder alter system set utl_file_dir = 'C: \ oracle \ product \ 10.2.0 \ logminer 'scope = spfile; alter database add supplemental log data; shutdown immediate; startup open; -------------- PLSQLalter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss ';

 

Log interval analysis Stored Procedure

-- Encapsulate a log analysis and mining stored procedure create or replace procedure EXPORT_DATA AS NOW_TIME TIMESTAMP: = SYSDATE; TB_COUNT INT; TB_NAME VARCHAR (50 ); CURSOR CURRENT_REDO_CUR is select member from v $ logfile where group # IN (select group # from v $ log where status = 'current '); CURSOR NO_ARCH_REDO_CUR is select member from v $ logfile where group # IN (select group # from v $ log where archived = 'no' and status <> 'current '); CURSOR ARCH_CUR is select name from v $ ARCHIVED_LOG WHERE FIRST_TIME> (select max (BAK_TIME) FROM nation) AND FIRST_TIME <NOW_TIME order by stamp desc; begin insert into nation VALUES (NOW_TIME ); COMMIT; execute immediate 'alter session set nls_date_format = '| ''' yyyy-mm-dd hh24: mi: s '''; execute immediate 'alter session set nls_timestamp_format = '| ''' yyyy-mm-dd hh24: mi: s'''; DBMS_LOGMNR_D.BUILD ('dictionary. ora ', 'c: \ oracle \ product \ 10.2.0 \ logminer'); FOR CURRENT_REDO_REC IN CURRENT_REDO_CUR LOOP handler (LOGFILENAME => CURRENT_REDO_REC.MEMBER, OPTIONS => DBMS_LOGMNR.NEW); end loop; FOR NO_ARCH_REC IN your LOOP values (LOGFILENAME => NO_ARCH_REC.MEMBER, OPTIONS => records); end loop; FOR ARCH_REC IN ARCH_CUR LOOP values (LOGFILENAME => ARCH_REC.NAME, OPTIONS => records ); end loop; DBMS_LOGMNR.START_LOGMNR (DICTFILENAME => 'C: \ oracle \ product \ 10.2.0 \ logminer \ dictionary. ora ', OPTIONS => DBMS_LOGMNR.DDL_DICT_TRACKING); TB_NAME: = 'export _ DATA_LOG'; select count (*) INTO TB_COUNT FROM USER_TABLES WHERE TABLE_NAME = TB_NAME; IF (TB_COUNT> 0) then execute immediate 'drop table' | TB_NAME; end if; execute immediate 'create table' | TB_NAME | 'nologging as select * from v $ logmnr_contents where 1 = 2 '; execute immediate 'insert/* + append */into '| TB_NAME | 'select * from v $ logmnr_contents'; execute immediate 'commit '; DBMS_LOGMNR.END_LOGMNR; END;

 

You can execute the statement in the query result of SYS. EXPORT_DATA_LOG once.

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss';exec export_data;

 

This process only analyzes the statements from the last execution time to the current execution time.