Actual combat: Oracle 11g R2 logminer__oracle

Approximate process.

Oracle Log Analyzer Tool Logminer use

1. Set Date format

Alter system set nls_date_format= ' Yyyy-mm-dd hh24:mi:ss ' scope=spfile;

Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual;

2. Add Supplemental Log

If the database needs to use Logminer, it should be added, only after this log is added to capture DML

ALTER DATABASE ADD Supplemental LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;

3. Open Archive

Alter system set log_archive_dest_1= ' Location=e:\arch ' Scope=both;

Shutdown immediate

Startup Mount

ALTER DATABASE Archivelog;

ALTER DATABASE open;

4. Install Logminer Tools

To install the Logminer tool, you must first run the following two scripts, both of which must be run as SYS users. The first of these scripts is used to create a DBMS_LOGMNR package that analyzes the log files. The second script is used to create the Dbms_logmnr_d package, which is used to create the data dictionary file.

1. $ORACLE _home\rdbms\admin\dbmslm.sql
2. $ORACLE _home\rdbms\admin\dbmslmd.sql
3. $ORACLE _home\rdbms\admin\dbmslms.sql

@e:\app\administrator\product\11.2.0\dbhome_1\rdbms\admin\dbmslm.sql

The package has been created.

The authorization was successful.

The synonym has been created.

@e:\app\administrator\product\11.2.0\dbhome_1\rdbms\admin\dbmslmd.sql
The package has been created.

The synonym has been created.

@e:\app\administrator\product\11.2.0\dbhome_1\rdbms\admin\dbmslms.sql
Package created

No mistakes.

The authorization was successful.

5. Using the Logminer tool

5.1. Set parameter Utl_file_dir

A data dictionary file is a text file that is created using package dbms_logmnr_d. If the table in the database that we are analyzing changes, the data dictionary that affects the library changes, and the dictionary file needs to be recreated. Another scenario is that when you analyze a redo log for another database file, you also have to regenerate the data dictionary file of the parsed database.

In Oracle8i, first in the Init.ora initialization parameter file, specify the location of the data dictionary file, which is to add a parameter utl_file_dir, which is the directory where the data dictionary files are placed in the server. Such as:

Utl_file_dir = (e:\test)

After oracle9i, it is recommended to use SPFile to dynamically adjust parameters;

Sql> Show parameter SPFile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
SPFile string E:\APP\ADMINISTRATOR\PRODUCT\1
1.2.0\dbhome_1\database\spfile
ORCL. ORA

Sql> alter system set utl_file_dir= ' E:\test ' scope=spfile;

The system has changed.

Shutdown immediate

sql> Startup force

The ORACLE routine has started.

Total System Global area 289406976 bytes

Fixed Size 1248600 bytes

Variable Size 96469672 bytes

Database buffers 188743680 bytes

Redo buffers 2945024 bytes

Database loading complete.

The database is already open.

Sql> Show parameter Utl_file_dir;

NAME TYPE VALUE
------------------------------------ ----------- -----------
Utl_file_dir string E:\test

5.2 Creating a data dictionary file

Sql>@e:\dbms_logmnr_d.build.txt

The PL/SQL process has completed successfully.

Script Dbms_logmnr_d.build.txt

BEGIN
Dbms_logmnr_d.build (
Dictionary_filename => ' Logminer_dict.dat ',
Dictionary_location => ' E:\test ');
End;
/

5.3 Create a list of log files to parse

Oracle's redo logs are divided into two types, online and offline (offline) archive log files, where I mainly analyze archived logs, the same as the online log principle.

---online

A. Creating A List

Execute dbms_logmnr.add_logfile (Options =>dbms_logmnr.new,logfilename => ' E:\app\Administrator\oradata\orcl\ REDO01. LOG ");

B. Add additional log files to the list
Sql>execute dbms_logmnr.add_logfile (Options =>dbms_logmnr.addfile,logfilename=> ' E:\app\Administrator\ Oradata\orcl\redo02. LOG ");

Sql> Execute dbms_logmnr.add_logfile (options =>dbms_logmnr.addfile,logfilename=> ' E:\app\Administrator\ Oradata\orcl\redo03. LOG ");

can also be completed at once:

BEGIN
Dbms_logmnr.add_logfile (
' E:\app\Administrator\oradata\orcl\REDO01. LOG ',
Dbms_logmnr.new);
Dbms_logmnr.add_logfile (
' E:\app\Administrator\oradata\orcl\REDO02. LOG ',
Dbms_logmnr.addfile);
Dbms_logmnr.add_logfile (
' E:\app\Administrator\oradata\orcl\REDO03. LOG ',
Dbms_logmnr.addfile);
End;
/

# # #说明:
Dbms_logmnr.new--for building a log Analysis table
Dbms_logmnr.addfile--for adding, entering log files for analysis
Dbms_logmnr.removefile-log file for emigration, for analysis

Delete
Execute dbms_logmnr.add_logfile (Options =>dbms_logmnr.removefile,logfilename => ' E:\app\Administrator\ Oradata\orcl\redo03. LOG ");
Execute dbms_logmnr.add_logfile (Options =>dbms_logmnr.removefile,logfilename => ' E:\app\Administrator\ Oradata\orcl\redo02. LOG ");
Execute dbms_logmnr.add_logfile (Options =>dbms_logmnr.removefile,logfilename => ' E:\app\Administrator\ Oradata\orcl\redo01. LOG ");

### #说明:
Sql> Execute dbms_logmnr.add_logfile (logfilename => ' E:\app\Administrator\oradata\orcl\REDO03. LOG ', option => DBMS_LOGMNR. RemoveFile);

To view a list of log files:

Select Db_name, thread_sqn,filename from V$logmnr_logs;

----Offline (offline) archive log file
BEGIN
Dbms_logmnr.add_logfile (
' E:\arch\ARC0000000008_0742739616.0001 ',
Dbms_logmnr.new);
Dbms_logmnr.add_logfile (
' E:\arch\ARC0000000009_0742739616.0001 ',
Dbms_logmnr.addfile);
Dbms_logmnr.add_logfile (
' E:\arch\ARC0000000010_0742739616.0001 ',
Dbms_logmnr.addfile);
End;
/

5.4 Start Logminer for analysis

5.4.1 Unlimited Conditions

BEGIN
DBMS_LOGMNR.START_LOGMNR (
Dictfilename => ' E:\test\logminer_dict.dat '
);
End;
/

5.4.2 Restriction conditions

BEGIN
DBMS_LOGMNR.START_LOGMNR (
Dictfilename => ' E:\test\logminer_dict.dat ',
StartTime => to_date (' 2011-02-18 16:40:26 ', ' yyyy-mm-dd HH24:MI:SS '),
Endtime => to_date (' 2011-02-18 16:44:41 ', ' yyyy-mm-dd HH24:MI:SS ')
);
End;
/

5.5 Observation Analysis Results (v$logmnr_contents)

So far, we have analyzed the contents of the Redo log file. Dynamic performance View V$logmnr_contents contains all the information that Logminer analysis obtains.

SELECT Sql_redo from V$logmnr_contents;

SELECT Sql_redo from v$logmnr_contents where seg_name= ' T1 ';

SELECT Sql_redo from v$logmnr_contents where Username= ' Scott '
and Seg_name= ' Scott.t1 ' and upper (operation) = ' delete ';

SELECT Sql_redo from v$logmnr_contents where seg_name= ' T1 ' and upper (operation) = ' delete ';

SELECT Sql_redo from v$logmnr_contents where username= ' SYS ' and table_name= ' T1 ';

6. Close Logminer

It would be helpful to create a permanent database table with the contents of the V$logmnr_contents view
Sql> CREATE TABLE Logmnr_contents as SELECT * from V$logmnr_contents;
When you have completed the Redo log check, run the END_LOGMNR in the DBMS_LOGMNR
Execute DBMS_LOGMNR.END_LOGMNR ();