Logminer operation steps
Premise:
1 data can be archived or non-archived.
(2) the supplemental_log_data_min needs to be adjusted to carry out log mining.
Sys@oratest1 (test-for-lihb) > select supplemental_log_data_min from v$database
SUPPLEME
-
NO
Sys@oratest1 (test-for-lihb) > alter database add supplemental log data
Database altered.
Sys@oratest1 (test-for-lihb) > select supplemental_log_data_min from v$database
SUPPLEME
-
YES
(3) Log mining is based on session. If session exits, it needs to be re-mined.
Steps:
1 View the current usage of online log files:
Sys@oratest1 (test-for-lihb) > select * from v$log
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
- --
11 565 52428800 5122 NO CURRENT 8490175 2017-11-13 10:37:37 2.8147E+14
21 563 52428800 5122 NO INACTIVE 8481985 2017-11-13 07 NO INACTIVE 00 purl 208489600 2017-11-13 10:21:05
3 1 564 52428800 5122 NO INACTIVE 8489600 2017-11-13 10 21 purl 058490175 2017-11-13 10:37:37
2 create path
Sys@oratest1 (test-for-lihb) > CREATE DIRECTORY utlfile AS'/ home/oracle/logmnr'
Directory created.
3 modify parameters
Sys@oratest1 (test-for-lihb) > alter system set utl_file_dir='/home/oracle/logmnr' scope=spfile
System altered.
4 create / home/oracle/logmnr directory, restart the database to make the utl_file_dir parameter effective
[oracle@test-for-lihb] $mkdir-p / home/oracle/logmnr
Sys@oratest1 (test-for-lihb) > shutdown immediate
Sys@oratest1 (test-for-lihb) > startup
5 create a data dictionary file
Sys@oratest1 (test-for-lihb) > EXECUTE dbms_logmnr_d.build (dictionary_filename = > 'dictionary.ora', dictionary_location = >' / home/oracle/logmnr')
PL/SQL procedure successfully completed.
6 View the physical location of the log file (you can also view the archive file)
Sys@oratest1 (test-for-lihb) > select * from v$logfile
GROUP# STATUS TYPE MEMBER IS_
1 ONLINE / opt/app/oracle/oradata/ORATEST1/onlinelog/o1_mf_1_dhl9h7tz_.log NO
1 ONLINE / opt/app/oracle/fast_recovery_area/ORATEST1/onlinelog/o1_mf_1_dhl9h8bn_.log YES
2 ONLINE / opt/app/oracle/oradata/ORATEST1/onlinelog/o1_mf_2_dhl9h8mc_.log NO
2 ONLINE / opt/app/oracle/fast_recovery_area/ORATEST1/onlinelog/o1_mf_2_dhl9h91y_.log YES
3 ONLINE / opt/app/oracle/oradata/ORATEST1/onlinelog/o1_mf_3_dhl9h98x_.log NO
3 ONLINE / opt/app/oracle/fast_recovery_area/ORATEST1/onlinelog/o1_mf_3_dhl9h9y1_.log YES
6 rows selected.
7 Analog scott user insert data
Scott@oratest1 (70) > truncate table T
Table truncated.
Scott@oratest1 (70) > insert into t values (10086)
1 row created.
Scott@oratest1 (70) > insert into t values (10087)
1 row created.
Scott@oratest1 (70) > insert into t values (10088)
1 row created.
Scott@oratest1 (70) > insert into t values (10089)
1 row created.
Scott@oratest1 (70) > commit
Commit complete.
8 add log file (if a log group has more than one member, you only need to add one, because all members of the same log group are the same. )
The first log file parameter is dbms_logmnr.NEW
BEGIN
Dbms_logmnr.add_logfile (logfilename= >'/ opt/app/oracle/oradata/ORATEST1/onlinelog/o1_mf_3_dhl9h98x_.log',options= > dbms_logmnr.NEW)
END
/
The subsequent log file parameter is dbms_logmnr.ADDFILE
BEGIN
Dbms_logmnr.add_logfile (logfilename= >'/ opt/app/oracle/oradata/ORATEST1/onlinelog/o1_mf_1_dhl9h7tz_.log',options= > dbms_logmnr.ADDFILE)
Dbms_logmnr.add_logfile (logfilename= >'/ opt/app/oracle/oradata/ORATEST1/onlinelog/o1_mf_2_dhl9h8mc_.log',options= > dbms_logmnr.ADDFILE)
END
/
9 start log mining
Unlimited mining:
EXECUTE dbms_logmnr.start_logmnr (dictfilename= >'/ home/oracle/logmnr/dictionary.ora')
Mining for a specific time period (untested):
EXECUTE dbms_logmnr.start_logmnr (dictfilename= >'/ home/oracle/logmnr/dictionary.ora',StartTime = > to_date ('2013-6-8 0000 Velcro 001L YYYYMuMMMAE DD HH24:MI:SS') EndTime = > to_date (' 2013-6-8 23 VLV 59mm MMMui DD HH24:MI:SS'))
10 View mining results
Sys@oratest1 (test-for-lihb) > select sql_redo from v$logmnr_contents where username='SCOTT'
. . .
Insert into "SCOTT". "T" ("ID") values ('10086')
Insert into "SCOTT". "T" ("ID") values ('10087')
Insert into "SCOTT". "T" ("ID") values ('10088')
Insert into "SCOTT". "T" ("ID") values ('10089')
.
11 turn off log mining.
EXECUTE DBMS_LOGMNR.END_LOGMNR
