Shulou(Shulou.com)05/31 Report--

This article introduces how to use LogMiner in Oracle. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

LogMiner introduction

LogMiner is a practical and useful analysis tool provided by Oracle since product 8i. Using this tool, you can easily get the specific contents of Oracle redo log files (archive log files). LogMiner analysis tool is actually composed of a set of PL/SQL packages and some dynamic views. It is released as part of the Oracle database and is a completely free tool provided by oracle.

Environment: Oracle 11.2.0.4 RAC

1. Query the current log group

Use sys users to query the current log group of the Oracle database:

-- 1.current logSQL > select * from v$log GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME-- -1 1 29 52428800 512 2 YES INACTIVE 1547838 25-JUN-17 1547840 25-JUN-17 2 1 30 52428800 512 2 NO CURRENT 1567897 27-JUN-17 2.8147E+14 27-JUN-17 3 2 25 52428800 512 2 NO CURRENT 1567902 27-JUN-17 2. 8147E+14 42 24 52428800 512 2 YES INACTIVE 1567900 27-JUN-17 1567902 27-JUN-17

The current current here is:

Thread 1 sequence 30

Thread 2 sequence 25

two。 Business user insert operation

Simulated business user jingyu inserts T2 table data:

-- 2. Business user insert operation sqlplus jingyu/jingyu@jyzhaoSQL > select count (1) from T2; COUNT (1)-0SQL > insert into T2 select rownum, dbms_random.string ('baud page50) from dual connect by level Commit complete.SQL > select count (1) from T2; COUNT (1)-100000

3. Archive log switch

In order to distinguish the different operations of each log, the database is manually archived and switched to simulate the actual archiving switching in reality.

-3. Switch SQL > alter system archive log current;System altered.SQL > select * from v$log to simulate archive log GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME-- -1 1 31 52428800 512 2 NO CURRENT 1572517 27-JUN-17 2.8147E+14 21 30 52428800 512 2 YES ACTIVE 1567897 27-JUN-17 1572517 27-JUN-17 3 2 25 52428800 512 2 YES ACTIVE 1567902 27-JUN-17 1572521 27-JUN -17 42 26 52428800 512 2 NO CURRENT 1572521 27-JUN-17 2.8147E+14

4. Business user insert operation

Simulated business user jingyu deletes some of the data in T2 table:

-- 4. Business user delete operation SQL > delete from T2 where id

Commit;Commit complete.SQL > select count (1) from T2; COUNT (1)-90001

5. Archive log switch

In order to distinguish the different operations of each log, the database is manually archived and switched to simulate the actual archiving switching in reality.

-5. Switch SQL > alter system archive log current;System altered.SQL > select * from v$log to simulate archive log GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME-- -1 1 31 52428800 512 2 YES ACTIVE 1572517 27-JUN-17 1574293 27-JUN-17 2 1 32 52428800 512 2 NO CURRENT 1574293 27-JUN-17 2.8147E+14 32 27 52428800 512 2 NO CURRENT 1574296 27-JUN-17 2.8147E+14 4 2 26 52428800 512 2 YES ACTIVE 1572521 27-JUN-17 1574296 27-JUN-17

6. Business user update operation

Simulated business user jingyu updates some of the data in table T2:

-6. Business user update operation SQL > update T2 SET contents = 'xxx' where id > 99998; 2 rows updated.SQL > commit;Commit complete.

7. Archive log switch

In order to distinguish the different operations of each log, the database is manually archived and switched to simulate the actual archiving switching in reality.

-7. Switch SQL > alter system archive log current;System altered.SQL > select * from v$log to simulate archive log GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME-- -1 1 33 52428800 512 2 NO CURRENT 1575480 27-JUN-17 2.8147E+14 2 1 32 52428800 512 2 YES ACTIVE 1574293 27-JUN-17 1575480 27-JUN-17 32 27 52428800 512 2 YES ACTIVE 1574296 27-JUN-17 1575458 27-JUN -17 42 28 52428800 512 2 NO CURRENT 1575458 27-JUN-17 2.8147E+14

8. Confirm logs that need to be analyzed

Logs that need to be analyzed using LogMiner after confirmation:

-8. Confirm the log to be analyzed thread# 1 sequence# 30thread# 2 sequence# 25 this part of the log must be a record insertion operation thread# 1 sequence# 31thread# 2 sequence# 26 this part of the log must be a record deletion operation thread# 1 sequence# 32thread# 2 sequence# 27 this part of the log must be a record update operation

9. Backup archive log

Back up all the relevant archives in copy:

-- 9. Back up the relevant archived copy RUN {allocate channel dev1 device type disk format'/ tmp/backup/arc_%h_%e_%t';backup as copy archivelog sequence 30 thread 1, backup as copy archivelog sequence 31 thread 1, backup as copy archivelog sequence 32 thread 1, backup as copy archivelog sequence 25 thread 2, backup as copy archivelog sequence 26 thread 2, backup as copy archivelog sequence 27 thread 2, channel dev1;}

The archive log files backed up are as follows:

[oracle@jyrac1 backup] $ls-lrthtotal 17M Jun r-1 oracle asmadmin 2.3M Jun 27 21:50 arc_1_30_947800247-rw-r- 1 oracle asmadmin 591K Jun 27 21:50 arc_1_31_947800249-rw-r- 1 oracle asmadmin 143K Jun 27 21:50 arc_1_32_947800250-rw-r- 1 oracle asmadmin 9.5m Jun 27 21:50 arc_2_25_947800251-rw-r- -1 oracle asmadmin 3.6m Jun 27 21:50 arc_2_26_947800253-rw-r- 1 oracle asmadmin 77K Jun 27 21:50 arc_2_27_947800254

10. Use LogMiner to analyze

Use LogMiner to analyze archive logs:

-- use LogMiner to analyze archived logs-- there should be begin dbms_logmnr.add_logfile ('/ tmp/backup/arc_1_30_947800247'); dbms_logmnr.add_logfile ('/ tmp/backup/arc_2_25_947800251'); dbms_logmnr.start_logmnr (Options= > dbms_logmnr.dict_from_online_catalog); end for insert operation /-- there should be begin dbms_logmnr.add_logfile ('/ tmp/backup/arc_1_31_947800249') for delete operation; dbms_logmnr.add_logfile ('/ tmp/backup/arc_2_26_947800253'); dbms_logmnr.start_logmnr (Options= > dbms_logmnr.dict_from_online_catalog); end;/-- should have log begin dbms_logmnr.add_logfile ('/ tmp/backup/arc_1_32_947800250') for update operation Dbms_logmnr.add_logfile ('/ tmp/backup/arc_2_27_947800254'); dbms_logmnr.start_logmnr (Options= > dbms_logmnr.dict_from_online_catalog); end;/

Query v$logmnr_contents

Set lines 180 pages 500col username format a8col sql_redo format a50 select username,scn,timestamp,sql_redo from v$logmnr_contents where table_name='T2'; select username,scn,timestamp,sql_redo from v$logmnr_contents where username='JINGYU';select username,scn,timestamp,sql_redo from v$logmnr_contents where sql_redo like'% JINGYU%';select username,scn,timestamp,sql_redo from v$logmnr_contents where sql_redo like 'insert%JINGYU%';select username,scn,timestamp,sql_redo from v$logmnr_contents where sql_redo like' delete%JINGYU%' Select username,scn,timestamp,sql_redo from v$logmnr_contents where sql_redo like 'update%JINGYU%'

The experiment found that the relevant records could not be queried under the condition of username, and it was finally confirmed that the username was unknown rather than the jingyu of the business user who actually executed the statement.

The log SQL _ redo field mined is a complete SQL, which can be queried in the way of like. For example, when I analyze the log of the update operation, I can get the following results:

SQL >-there should be a log SQL > begin 2 dbms_logmnr.add_logfile ('/ tmp/backup/arc_1_32_947800250'); 3 dbms_logmnr.add_logfile ('/ tmp/backup/arc_2_27_947800254'); 4 dbms_logmnr.start_logmnr (Options= > dbms_logmnr.dict_from_online_catalog); 5 end; 6 / PL/SQL procedure successfully completed.SQL > select count (1) from v$logmnr_contents COUNT (1)-388SQL > select username,scn,timestamp,sql_redo from v$logmnr_contents where username='JINGYU';no rows selectedSQL > select username,scn,timestamp,sql_redo from v$logmnr_contents where sql_redo like'% JINGYU%' USERNAME SCN TIMESTAMP---SQL_REDO -UNKNOWN 1575420 27-JUN-17update "JINGYU". "T2" set "CONTENTS" = 'xxx' where "CONTENTS" =' YSWGNNLCLMYWPSLQETVLGQJRKQIEAMOEYUFNRUQULVFRVPEDRV' and ROWID = 'AAAVWVAAGAAAAHnABj' UNKNOWN 1575420 27-JUN-17update "JINGYU". "T2" set "CONTENTS" = 'xxx' where "CONTENTS" =' WHCWFOZVLJWHFWLJDNVSMQTORGJFFXYADIOJZWJCDDOYXAOQJG' and ROWID = 'AAAVWVAAGAAAAOYAAE';SQL >

At this point, the basic operation experiment of LogMiner has been completed.

Attached: some operation commands related to LogMiner refer to:

Conn / as sysdba-- install LOGMINER@$ORACLE_HOME/rdbms/admin/dbmslmd.sql;@$ORACLE_HOME/rdbms/admin/dbmslm.sql;@$ORACLE_HOME/rdbms/admin/dbmslms.sql;@$ORACLE_HOME/rdbms/admin/prvtlm.plb;-- stop logmnrexec dbms_logmnr.end_logmnr-query whether additional logs are enabled: select supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui from vault database;-- enable additional log alter database add supplemental log data Cancel supplementary log alter database drop supplemental log data (primary key) columns;alter database drop supplemental log data (unique) columns;alter database drop supplemental log data;-- the last one is the new archive select name,dest_id,thread#,sequence# from v$archived_log

Finally, confirm that if additional logs are enabled, username can capture the correct value:

SQL > set lines 180SQL > / GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME-- -1 1 35 52428800 512 2 YES INACTIVE 1590589 27-JUN-17 1591935 27-JUN-17 2 1 36 52428800 512 2 NO CURRENT 1591935 27-JUN-17 2.8147E+14 3 2 29 52428800 512 2 YES INACTIVE 1590594 27-JUN-17 1591938 27-JUN-17 42 30 52428800 512 2 NO CURRENT 1591938 27-JUN-17 2.8147 Elaine 141 362 update 30SQL > update T2 SQL = 2 'aaa' where id = 44449 1 row updated.SQL > commit;Commit complete.RUN {allocate channel dev1 device type disk format'/ tmp/backup/arc_%h_%e_%t';backup as copy archivelog sequence 36 thread 1; backup as copy archivelog sequence 30 thread 2; begin dbms_logmnr.add_logfile ('/ tmp/backup/arc_1_36_947808116'); dbms_logmnr.add_logfile ('/ tmp/backup/arc_2_30_947808118'); dbms_logmnr.start_logmnr (Options= > dbms_logmnr.dict_from_online_catalog); end / SQL > select username,scn,timestamp,sql_redo from v$logmnr_contents where username='JINGYU' USERNAME SCN TIMESTAMP---SQL_REDO - -JINGYU 1593448 27-JUN-17set transaction read write JINGYU 1593448 27-JUN-17update "JINGYU". "T2" set "CONTENTS" = 'aaa' where "CONTENTS" =' WZTSQZWYOCNDFKSMNJQLOLFUBRDOHCBMKXBHAPJSHCMWBYZJVH' and ROWID = 'AAAVWVAAGAAAACLAAL';JINGYU 1593450 27 Oracle. I hope the above content can be of some help and learn more knowledge. If you think the article is good, you can share it for more people to see.

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.