Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.