How to use logminer 01/20 Update SLTechnology News&Howtos

How to use logminer

2025-01-20 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

This article mainly explains "how to use logminer". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn how to use logminer.

There are two types of LogMiner, one is to analyze the DML operation using the data dictionary of the source database, and the other is to extract the LogMiner data dictionary to the dictionary file to analyze the DDL operation.

Note: 1. It is best to configure the supplementary log when using the logmnr tool, otherwise the ddl operation will not be achieved eventually.

two。 The utl_file_dir parameter, which is the directory of the logmnr dictionary file, must be configured for the ddl operation, but not for the query dml operation.

LogMiner tools can be used to analyze both online and offline log files, that is, you can analyze your own database redo log files or other database redo log files.

In general, the main uses of LogMiner tools are:

1. Track the changes of the database: you can track the changes of the database offline without affecting the performance of the online system.

2. Back up the changes in the database: roll back specific change data and reduce the execution of point-in-time recovery.

3. optimization and expansion plan: the data growth pattern can be analyzed by analyzing the data in the log file.

First, determine the logical damage time of the database. Suppose a user mistakenly deletes the important table sales by performing drop table, the execution time and SCN value of the misoperation can be accurately located through LogMiner, and then the table data can be fully recovered by time-based recovery or SCN-based recovery.

Second, determine the fine logic recovery operations to be performed at the transaction level. Suppose that some users perform a series of DML operations and commit transactions on a table, and that one of the users has an error in the DML operation. Any user's DML operation and corresponding UNDO operation can be obtained through LogMiner, and the user's erroneous operation can be cancelled by executing UNDO operation.

Third, carry out follow-up audit. Through LogMiner, you can track all DML, DDL, and DCL operations of the Oracle database, thus obtaining information such as the time sequence in which these operations are performed, the users who performed them, and so on.

1. LogMiner basic object

Source database (source database): this database is a product database that contains redo logs and archive logs to be analyzed.

Analysis database (mining database): this database is the database to be used to perform LogMiner operations.

LogMiner dictionary: the LogMiner dictionary is used to convert internal object ID numbers and data types to object names and external data formats. When using LogMiner to analyze redo logs and archive logs, you should generate an LogMiner dictionary, otherwise you will not be able to read the analysis results.

2. LogMiner configuration requirements

(1) Source database and analysis database (source database and analysis database can be the same database)

The source database and the analysis database must run on the same hardware platform

The analysis database can be a standalone database or a source database

The version of the analysis database cannot be lower than the version of the source database

The analysis database and the source database must have the same character set.

(2) LogMiner dictionary: the LogMiner dictionary must be generated in the source database.

(3) redo log files

When analyzing multiple redo and archive logs, they must be redo and archive logs of the same source database

When analyzing multiple redo logs and archive logs, they must have the same resetlogs scn

When analyzing redo logs and archive logs must be above the Oracle8.0 version.

3. Supplementary log (suppplemental logging)

The redo log is used for routine recovery and media recovery, and the data required for these operations is automatically recorded in the redo log. However, the redo application may also need to record other column information to the redo log, and the logging process of recording other columns is called a supplementary log. By default, the Oracle database does not provide any supplementary logs, so that LogMiner cannot support the following characteristics by default:

Index clusters, chain rows, and migration rows

Direct path insertion

Extract the LogMiner dictionary to the redo log

Tracking DDL

Generate SQL_REDO and SQL_UNDO information for key columns

LONG and LOB data types.

Therefore, in order to take full advantage of the features provided by LogMiner, supplementary logs must be activated.

Syntax:

Ideally, the LogMiner dictionary file will be created after all database dictionary changes have been made, and before any redo log files to be analyzed. Starting with Oracle9i release 1 (9.0.1), you can use LogMiner to dump LogMiner dictionaries to redo log files or flat files, perform DDL operations, and dynamically apply DDL changes to LogMiner dictionaries.

In addition, supplementary logging (at least at the lowest level) should be enabled to ensure that you can take advantage of all the features provided by LogMiner. For information about using supplementary logging in LogMiner, see Oracle Database Utility.

Experiment 1: open archiving, do not open supplementary logs, and do not add logminer data dictionary, (using DBMS_LOGMNR_D.BUILD)

-- View the logs under the archive path and path:

SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 14

Next log sequence to archive 16

Current log sequence 16

SQL >

SQL > show parameter recover

NAME TYPE VALUE

Db_recovery_file_dest string / u01/app/oracle/fast_recovery_ area/DBDB/newback

Db_recovery_file_dest_size big integer 9G

Db_unrecoverable_scn_tracking boolean TRUE

Recovery_parallelism integer 0

SQL >

SQL > alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss'

Session altered.

SQL > select *

From (SELECT NAME

THREAD#

SEQUENCE#

APPLIED

ARCHIVED

COMPLETION_TIME

FROM V$ARCHIVED_LOG order by 6 desc) a

Where rownum select group#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS from v$log

GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS

1 1 16 1 NO CURRENT

2 1 14 1 YES INACTIVE

3 1 15 1 YES INACTIVE

-- query log group:

SQL > select t1.GROUPPERIMETRY select t2.MEMBER from v$log t1.TYPE from v$log T1 inner join v$logfile T2 on t1.GROUP#=t2.GROUP# and t1.STATUSrequests current

GROUP# STATUS MEMBER TYPE

1 CURRENT / u01/app/oracle/oradata/DBdb/redo01.log ONLINE

The current log group is group#1,sequence# 16.

-- Simulation operation:

SQL > conn hr/hr

Connected.

SQL >

SQL > create table log_a (x int)

Table created.

SQL > insert into log_a values (1)

1 row created.

SQL > insert into log_a values (2)

1 row created.

SQL > insert into log_a values (3)

1 row created.

SQL > commit

Commit complete.

SQL > update log_a set Xbox 4 where Xuan 1

1 row updated.

SQL > commit

Commit complete.

SQL > select * from log_a

four

two

three

-- query the working log group

SQL > select group#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS from v$log

GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS

1 1 16 1 NO CURRENT

2 1 14 1 YES INACTIVE

3 1 15 1 YES INACTIVE

SQL > select t1.GROUPPERIMETRY select t2.MEMBER from v$log t1.TYPE from v$log T1 inner join v$logfile T2 on t1.GROUP#=t2.GROUP# and t1.STATUSrequests current

GROUP# STATUS MEMBER TYPE

1 CURRENT / u01/app/oracle/oradata/DBdb/redo01.log ONLINE

-- then analyze the online redo log

SQL > exec dbms_logmnr.add_logfile ('/ u01ActionActionoradataPlacement'/ u01OnOnActionoradataUnitedDBdbPlancedredo01.logcodes and dbmswatches logmnr.new)

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

SQL > create table l1_Z1 as select * from v$logmnr_contents

Table created.

SQL > exec dbms_logmnr.end_logmnr ()

PL/SQL procedure successfully completed.

-- then check the online redo log of the analysis, and you can see the previous operations in the sql_redo.

SQL > select start_timestamp,sql_redo,sql_undo from l1_Z1 where sql_redo like'% LOG_A%'

START_TIMESTAMP SQL_REDO SQL_UNDO

- -

Delete from "SYS". "OBJ$" where "OBJ#" = '90306' and "DATAOBJ insert into" SYS "." OBJ$ "(" OBJ# "," DATAOBJ# "," OWNER# "," NAME ","

# "= '90306' and" OWNER# "= '84' and" NAME "=' LOG_A' and" N NAMESPACE "," SUBNAME "," TYPE# "," CTIME "," MTIME "," STIME "," STATUS "

AMESPACE "='1' and" SUBNAME "IS NULL and" TYPE# "='2' and", "REMOTEOWNER", "LINKNAME", "FLAGS", "OID$", "SPARE1", "SPARE2"

"CTIME" = TO_DATE ('2018-01-25 16 yyyy-mm-dd hh34:mi 1315 31mm,' yyyy-mm-dd hh34:mi "SPARE3", "SPARE4", "SPARE5", "SPARE6") values ('90306.

: ss') and "MTIME" = TO_DATE ('2018-01-25 1615 1314 31L,' yyyy-mm-, '84mm).

Dd hh34:mi:ss') and "STIME" = TO_DATE ('2018-01-25 16 purl 13 TO_DATE, yyy-mm-dd hh34:mi:ss'), TO_DATE (' 2018-01-25 16 purge 13 purge 31 min, 'yyyy-)

'yyyy-mm-dd hh34:mi:ss') and "STATUS" =' 1' and "REMOTEOWNE mm-dd hh34:mi:ss'), TO_DATE ('2018-01-25 16-13-13-31-31,' yyyy-mm-d

R "IS NULL and" LINKNAME "IS NULL and" FLAGS "='0' and" OID d hh34:mi:ss'), '1null, null, null, n

$"IS NULL and" SPARE1 "='6' and" SPARE2 "='1' and" SPARE3, NULL)

"='84 'and" SPARE4 "IS NULL and" SPARE5 "IS NULL and" SPAR

E6 "IS NULL and ROWID = 'AAAAASAABAAAVC0AAV'

START_TIMESTAMP SQL_REDO SQL_UNDO

- -

Insert into "SYS". "OBJ$" ("OBJ#", "DATAOBJ#", "OWNER#", "NAME", "delete from" SYS "." OBJ$ "where" OBJ# "= '90317' and" DATAOBJ

NAMESPACE "," SUBNAME "," TYPE# "," CTIME "," MTIME "," STIME "," STATUS # "= '90317' and" OWNER# "=' 84' and" NAME "= 'LOG_A' and" N

"," REMOTEOWNER "," LINKNAME "," FLAGS "," OID$ "," SPARE1 "," SPARE2 ", AMESPACE" ='1' and "SUBNAME" IS NULL and "TYPE#" ='2' and

"SPARE3", "SPARE4", "SPARE5", "SPARE6") values ('90317,90317' "CTIME" = TO_DATE (' 2018-01-25 17-17-17-14-14-48-48-48-14-48), 'yyyy-mm-dd hh34:mi

And "MTIME" = TO_DATE ('2018-01-25 1748 MTIME 46,' yyyy-mm-)

Yyy-mm-dd hh34:mi:ss'), TO_DATE ('2018-01-25 17 yyyy- dd hh34:mi:ss' 48 yyyy- dd hh34:mi:ss') and "STIME" = TO_DATE (' 2018-01-25 17 yyyy- dd hh34:mi:ss' 48 48 14) 46'

Mm-dd hh34:mi:ss'), TO_DATE ('2018-01-25 17 yyyy-mm-d 48 mm-dd hh34:mi:ss' 46,' yyyy-mm-d 'yyyy-mm-dd hh34:mi:ss') and "STATUS" =' 1' and "REMOTEOWNE

D hh34:mi:ss'), "IS NULL and" LINKNAME "IS NULL and" FLAGS "='0' and" OID.

, NULL); $"IS NULL and" SPARE1 "='6' and" SPARE2 "='1' and" SPARE3

"='84 'and" SPARE4 "IS NULL and" SPARE5 "IS NULL and" SPAR

START_TIMESTAMP SQL_REDO SQL_UNDO

- -

E6 "IS NULL and ROWID = 'AAAAASAABAAAVC0AAZ'

Insert into "HR". "LOG_A" ("X") values ('1'); delete from "HR". "LOG_A" where "X" ='1' and ROWID = 'AAAWDN

AAEAAAAzrAAA'

Insert into "HR". "LOG_A" ("X") values ('2'); delete from "HR". "LOG_A" where "X" ='2' and ROWID = 'AAAWDN

AAEAAAAzrAAB'

Insert into "HR". "LOG_A" ("X") values ('3'); delete from "HR". "LOG_A" where "X" ='3' and ROWID = 'AAAWDN

AAEAAAAzrAAC'

The experiment continues:

-- query:

SQL > select group#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS from v$log

GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS

1 1 16 1 NO CURRENT

2 1 14 1 YES INACTIVE

3 1 15 1 YES INACTIVE

SQL > select t1.GROUPPERIMETRY select t2.MEMBER from v$log t1.TYPE from v$log T1 inner join v$logfile T2 on t1.GROUP#=t2.GROUP# and t1.STATUSrequests current

GROUP# STATUS MEMBER TYPE

1 CURRENT / u01/app/oracle/oradata/DBdb/redo01.log ONLINE

SQL > l

1 select *

2 from (SELECT NAME

3 THREAD#

4 SEQUENCE#

5 APPLIED

6 ARCHIVED

7 COMPLETION_TIME

8 FROM V$ARCHIVED_LOG order by 6 desc) a

9 * where rownum /

NAME THREAD# SEQUENCE# APPLIED ARC COMPLETION_T

- -

/ u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_15_f6m8flqg_.arc 1 15 NO YES 25-JAN-18

/ u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_14_f6m5v9ts_.arc 1 14 NO YES 25-JAN-18

/ u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_13_f6m56dgw_.arc 1 13 NO YES 25-JAN-18

/ u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_12_f6m4nz1x_.arc 1 12 NO YES 25-JAN-18

/ u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_11_f6l9phsx_.arc 1 11 NO YES 25-JAN-18

/ u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_10_f6l9pgvh_.arc 1 10 NO YES 25-JAN-18

/ u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_9_f6l9pb8o_.arc 1 9 NO YES 25-JAN-18

/ u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_8_f6l9p9jl_.arc 18 NO YES 25-JAN-18

/ u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_7_f6l9p6xp_.arc 1 7 NO YES 25-JAN-18

/ u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_6_f6l9p5k2_.arc 1 6 NO YES 25-JAN-18

10 rows selected.

-- switch between log groups:

SQL > alter system switch logfile

System altered.

-- query again

SQL > select group#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS from v$log

GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS

1 1 16 1 YES ACTIVE

2 1 17 1 NO CURRENT

3 1 15 1 YES INACTIVE

SQL > select t1.GROUPPERIMETRY select t2.MEMBER from v$log t1.TYPE from v$log T1 inner join v$logfile T2 on t1.GROUP#=t2.GROUP# and t1.STATUSrequests current

GROUP# STATUS MEMBER TYPE

2 CURRENT / u01/app/oracle/oradata/DBdb/redo02.log ONLINE

SQL >

SQL > l

1 select *

2 from (SELECT NAME

3 THREAD#

4 SEQUENCE#

5 APPLIED

6 ARCHIVED

7 COMPLETION_TIME

8 FROM V$ARCHIVED_LOG order by 6 desc) a

9 * where rownum /

NAME THREAD# SEQUENCE# APPLIED ARC COMPLETION_T

- -

/ u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_16_f6mbk78z_.arc 1 16 NO YES 25-JAN-18