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

This article mainly explains "what are the knowledge points of Log Miner". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what are the knowledge points of Log Miner".

| | introduction to Log Miner |

Log Miner is a tool introduced by Oracle since Oracle 8i to analyze the contents of database redo log and archivelog. It can analyze all DDL and DML operations to the database through logs, and it can also analyze the time of operation, the SCN at the time of operation and the machine that operates. For DML operations, you can also query the sql of restore operations.

| | Log Miner composition |

The source database produces all redo log files for LogMiner analysis

The mining database is the database used to perform LogMiner analysis.

The LogMiner data dictionary is where LogMiner uses dictionaries to convert internal object identifiers and data types to readable data. If there is no dictionary, the results of the Log Miner analysis are displayed as binary data.

| | Log Miner data dictionary option |

When LogMiner analyzes redo data, you need a data dictionary to convert the log object ID into readable data. LogMiner provides three ways to use data dictionaries.

1. Use the online directory (Online Catalog)

Using catalog's data dictionary must be executed in the source database. The startup command is:

SQL > execute dbms_logmnr.start_logmnr (options= > dbms_logmnr.dict_from_online_catalog)

2. Extract the LogMiner dictionary to archive log. The startup command is:

SQL > execute dbms_logmnr_d.build (options= > dbms_logmnr_d.store_in_redo_logs)

Using this operation

3. Extract the LogMiner dictionary to the operating system file. The startup command is:

SQL > execute dbms_logmnr_d.build ('directory_name','/xxx/xxx/',dbms_logmnr_d.store_in_flat_file)

In this way, you need to set the utl_file_dir parameter, which needs to be restarted to take effect.

This tool is not complicated to use. Because extracting the Log Miner data dictionary to the operating system file requires restarting the database without setting parameters, the usage scenario is relatively narrow, so the following test scenario is to use the Online catalog data dictionary mode and extract the dictionary to redo log.

| | Test scenario |

1. Confirm that the supplementary log is enabled in the database.

Sys@RAC11G > select SUPPLEMENTAL_LOG_DATA_MIN from v$database

SUPPLEME

-

YES

-- if the returned result is no, open it with the following command

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA

Note: supplementary logging must be enabled before using log files for Log Miner analysis.

When supplementary logging is enabled, additional information is recorded in the redo log stream. If it is not enabled, some information about LogMiner mining cannot be displayed properly.

2. Create a test table and do some DML and DDL operations

Sys@RAC11G > create table test1 (NAME varchar2 (20), ID number)

Table created.

Sys@RAC11G > insert into test1 values ('Xerogramme 1')

1 row created.

Sys@RAC11G > insert into test1 values ('xx','2')

1 row created.

Sys@RAC11G > insert into test1 values ('xxx','3')

1 row created.

Sys@RAC11G > commit

Commit complete.

Sys@RAC11G > update test1 set name = 'xxxx' where id = 3

1 row updated.

Sys@RAC11G > commit

Commit complete.

Sys@RAC11G > truncate table test1

Table truncated.

3. Switch archive logs

Sys@RAC11G > alter system switch logfile

System altered.

Sys@RAC11G > alter system switch logfile

System altered.

-- then view the last generated archive log

Sys@RAC11G > select * from (select name from v$archived_log where name like'% archive%' order by SEQUENCE# desc) where rownum execute dbms_logmnr.add_logfile (logfilename= >'+ DATADG/rac11g/archivelog/2018_10_14/thread_1_seq_293.847.989533723',options= > dbms_logmnr.new)

PL/SQL procedure successfully completed.

-- add multiple logs, using the DBMS_LOGMNR.ADDFILE option

Sys@RAC11G > execute dbms_logmnr.add_logfile (logfilename= >'+ DATADG/rac11g/archivelog/2018_10_14/thread_1_seq_292.846.989533631',options= > DBMS_LOGMNR.ADDFILE)

PL/SQL procedure successfully completed.

5. Start Log Miner

Sys@RAC11G > execute dbms_logmnr.start_logmnr (options= > dbms_logmnr.dict_from_online_catalog)

PL/SQL procedure successfully completed.

6. Query the analysis results through the view v$logmnr_contents

V$logmnr_contents can query only when Log Miner is enabled.

Select to_char (timestamp,'yyyy-mm-dd hh34:mm:ss')

Operation

Username

SESSION_INFO

Sql_redo

From v$logmnr_contents

Where table_name = 'TEST1'

TO_CHAR (TIMESTAMP,' OPERATION USERNAME SESSION_INFO SQL_REDO

2018-10-14 22:10:50 DDL SYS login_username=SYS client_info= OS_username=oracle Machine_name=11gnode1 OS create table test1 (NAME varchar2 (20), ID number)

_ terminal=pts/5 OS_process_id=17273 OS_program_name=sqlplus@11gnode1 (TNS V

1-V3)

2018-10-14 22:10:29 INSERT SYS login_username=SYS client_info= OS_username=oracle Machine_name=11gnode1 OS insert into "SYS". "TEST1" ("NAME", "ID") values

_ terminal=pts/5 OS_process_id=17273 OS_program_name=sqlplus@11gnode1 (TNS V

1-V3)

2018-10-14 22:10:33 INSERT SYS login_username=SYS client_info= OS_username=oracle Machine_name=11gnode1 OS insert into "SYS". "TEST1" ("NAME", "ID") values ('xx','2')

_ terminal=pts/5 OS_process_id=17273 OS_program_name=sqlplus@11gnode1 (TNS V

1-V3)

2018-10-14 22:10:37 INSERT SYS login_username=SYS client_info= OS_username=oracle Machine_name=11gnode1 OS insert into "SYS". "TEST1" ("NAME", "ID") values ('xxx','3')

_ terminal=pts/5 OS_process_id=17273 OS_program_name=sqlplus@11gnode1 (TNS V

1-V3)

2018-10-14 22:10:51 UPDATE SYS login_username=SYS client_info= OS_username=oracle Machine_name=11gnode1 OS update "SYS". "TEST1" set "NAME" = 'xxxx' where "NAME" =' xxx' and ROWID ='

_ terminal=pts/5 OS_process_id=17273 OS_program_name=sqlplus@11gnode1 (TNS V AAAE6eAABAAAKHBAAC'

1-V3)

2018-10-14 22:10:12 DDL SYS login_username=SYS client_info= OS_username=oracle Machine_name=11gnode1 OS truncate table test1

_ terminal=pts/5 OS_process_id=17273 OS_program_name=sqlplus@11gnode1 (TNS V

1-V3)

.

7. Close Log Miner

EXECUTE DBMS_LOGMNR.END_LOGMNR ()

Note: the data of Log Miner only exists in PGA, and if the session of the query is disconnected, Log Miner will be closed with it.

8. Extract the data dictionary to redo log

Sys@RAC11G > EXECUTE DBMS_LOGMNR_D.BUILD (OPTIONS = > DBMS_LOGMNR_D.STORE_IN_REDO_LOGS)

PL/SQL procedure successfully completed.

Depending on the size of the dictionary, it may be included in multiple archives. If you have archived related redo log files, you can find the archive log that contains the beginning and end of the extracted dictionary. You can query the V$ARCHIVED_LOG view

Sys@RAC11G > SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES'

NAME

- -

+ DATADG/rac11g/archivelog/2018_10_15/thread_1_seq_308.869.989570647

Sys@RAC11G > SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES'

NAME

- -

+ DATADG/rac11g/archivelog/2018_10_15/thread_1_seq_309.871.989570649

9. Add archive logs containing data dictionaries and archive logs that need to be analyzed

Sys@RAC11G > execute dbms_logmnr.add_logfile (logfilename= >'+ DATADG/rac11g/archivelog/2018_10_15/thread_1_seq_308.869.989570647',options= > dbms_logmnr.new)

PL/SQL procedure successfully completed.

Sys@RAC11G > execute dbms_logmnr.add_logfile (logfilename= >'+ DATADG/rac11g/archivelog/2018_10_15/thread_1_seq_309.871.989570649',options= > dbms_logmnr.addfile)

PL/SQL procedure successfully completed.

Sys@RAC11G > execute dbms_logmnr.add_logfile (logfilename= >'+ DATADG/rac11g/archivelog/2018_10_14/thread_1_seq_293.847.989533723',options= > dbms_logmnr.addfile)

PL/SQL procedure successfully completed.

Sys@RAC11G > execute dbms_logmnr.add_logfile (logfilename= >'+ DATADG/rac11g/archivelog/2018_10_14/thread_1_seq_292.846.989533631',options= > dbms_logmnr.addfile)

PL/SQL procedure successfully completed.

10. Start Log Miner

Sys@RAC11G > execute dbms_logmnr.start_logmnr (options= > dbms_logmnr.DICT_FROM_REDO_LOGS)

PL/SQL procedure successfully completed.

11. Query the analysis results through the view v$logmnr_contents

Select to_char (timestamp,'yyyy-mm-dd hh34:mm:ss')

Operation

Username

SESSION_INFO

Sql_redo

From v$logmnr_contents

7 where table_name = 'TEST1'

TO_CHAR (TIMESTAMP,' OPERATION USERNAME

-

SESSION_INFO

- -

SQL_REDO

- -

2018-10-14 22:10:50 DDL SYS

Login_username=SYS client_info= OS_username=oracle Machine_name=11gnode1 OS_terminal=pts/5 OS_process_id=17273 OS_program_name=sqlplus@

11gnode1 (TNS V1-V3)

Create table test1 (NAME varchar2 (20), ID number)

2018-10-14 22:10:29 INSERT SYS

Login_username=SYS client_info= OS_username=oracle Machine_name=11gnode1 OS_terminal=pts/5 OS_process_id=17273 OS_program_name=sqlplus@

11gnode1 (TNS V1-V3)

Insert into "SYS". "TEST1" ("COL 1", "COL 2") values (HEXTORAW ('78'), HEXTORAW (' c102'))

2018-10-14 22:10:33 INSERT SYS

Login_username=SYS client_info= OS_username=oracle Machine_name=11gnode1 OS_terminal=pts/5 OS_process_id=17273 OS_program_name=sqlplus@

11gnode1 (TNS V1-V3)

Insert into "SYS". "TEST1" ("COL 1", "COL 2") values (HEXTORAW ('7878'), HEXTORAW ('c103'))

2018-10-14 22:10:37 INSERT SYS

Login_username=SYS client_info= OS_username=oracle Machine_name=11gnode1 OS_terminal=pts/5 OS_process_id=17273 OS_program_name=sqlplus@

11gnode1 (TNS V1-V3)

Insert into "SYS". "TEST1" ("COL 1", "COL 2") values (HEXTORAW ('787878'), HEXTORAW ('c104'))

2018-10-14 22:10:51 UPDATE SYS

Login_username=SYS client_info= OS_username=oracle Machine_name=11gnode1 OS_terminal=pts/5 OS_process_id=17273 OS_program_name=sqlplus@

11gnode1 (TNS V1-V3)

Update "SYS". "TEST1" set "COL 1" = HEXTORAW ('78787878') where "COL 1" = HEXTORAW ('787878') and ROWID =' AAAE6eAABAAAKHBAAC'

2018-10-14 22:10:12 DDL SYS

Login_username=SYS client_info= OS_username=oracle Machine_name=11gnode1 OS_terminal=pts/5 OS_process_id=17273 OS_program_name=sqlplus@

11gnode1 (TNS V1-V3)

Truncate table test1

| | some restrictions on Log Miner |

Of course, there are some limitations to this tool.

Source database and mining database

Both the source database and the mining database must run on the same operating system platform.

The mining database can be the same as the source database or completely independent.

The mining database must run the same version or higher of the Oracle database software as the source database.

The mining database must use the same character set (or superset of the character set) used by the source database.

Log Miner data dictionary

The data dictionary must be generated by the source database.

Archive log

All archive logs for each analysis must be generated from the same source data.

Must be associated with the same database RESETLOGS SCN.

Must be from an Oracle database of version 8i or later.

These restrictions are not harsh, and if you use this tool flexibly, you can maximize the utilization of archive logs and play a very important role at a critical time.

Thank you for your reading, these are the contents of "what are the knowledge points of Log Miner". After the study of this article, I believe you have a deeper understanding of what are the knowledge points of Log Miner, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.