Logmnr in Oracle 10 Gb uses instance Parsing

Oracle 10gInLogmnrIn Oracle9i, The Logmnr tool is a good measure for DBAs to retrieve excluded data. Logmnr can be used to complete the following tasks:

1. Identify the database change registration, which may be a change analysis. If some users suspect that they have any problems with their use, many errors may occur, and Logmnr can be used to analyze these transactions, let's see what happened.

2. Identify and modify users' false monopoly. If some users accidentally remove non-tables, they are not sure, at this time, we can use Logmnr to analyze who is enforcing DML, which may be a DDL monopoly.

3. retrieve lost data. If you cannot use flashback or use flashback, you can use the Logmnr tool to retrieve data. In this case, you only need to archive logs.

Content and data dictionary included in the Logmnr Tool

Dbms_logmnr_d package: used to extract dictionary messages to the table, including three processes:

1) add_logfile: Used to Add/Remove log files for analysis.

2) start_logmnr: Used to enable log analysis and enable many different analysis options in 9i/10g.

3) end_logmnr: Used to terminate the analysis session. It recycles the memory borrowed by LogMiner.

In case of idle users wanting to use the above package, the current authorization capability in sys is sufficient, such:

Sys user authorization:

SQL> grant execute on dbms_logmnr to scott;

The data dictionary related to LogMiner is as follows:

U v $ logmnr_dictionary: Data Dictionary messages that LogMiner may exploit.

U v $ logmnr_parameters: The parameter message set by LogMiner.

U v $ logmnr_logs: List of logs currently used for analysis.

U v $ logmnr_contents: Log Analysis gains.

Logmnr analysis process demonstration

Create a table, insert some data, and then remove the data.

Connect:

 
 

  
  
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
  
  
With the Partitioning, OLAP and Data Mining options
  
  
SQL> alter system archive log current;
  
  
The system has been changed.
  
  
SQL> create table mytesttab as select * from scott. dept;
  
  
The table has been created.
  
  
SQL> insert into mytesttab values (80, 'it', 'wa ');
  
  
1 line has been created.
  
  
SQL> insert into mytesttab values (90, 'mgr ', 'ymr ');
  
  
1 line has been created.
  
  
SQL> commit;
  
  
Submission ends.
  
  
SQL> delete from mytesttab;
  
  
Six rows have been excluded.
  
  
SQL> commit;
  
  
Submission ends.
  
  
SQL> alter system archive log current;
  
  
The system has been changed.
 
 

Then, find the log files registered during the monopoly process.

 
 

  
  
D:/oracle/flash_recovery_area/MYORACLE/ARCHIVELOG/o1_mf_00004_3cvhlkjm_.arc file under the 2007_08_11 directory. We must analyze and process this file.
  
  
SQL> exec sys. dbms_logmnr.add_logfile (LogFileName => 'd:/oracle/flash_recovery_area/MYORACLE/ARCHIVELO
  
  
G/2007_08_11/o1_mf_00004_3cvhlkjm_.arc ', options => dbms_logmnr.new );
  
  
The PL/SQL process has won.
  
  
SQL> exec sys. dbms_logmnr.start_logmnr (options => sys. dbms_logmnr.dict_from_online_catalog );
  
  
The PL/SQL process has won.
  
  
OK. The analysis ends.
 
 

View log registration content

 
 

  
  
SQL> select t. scn, t. timestamp, t. seg_owner, t. operation
  
  
2 from v $ logmnr_contents t where t. seg_name = 'mytesttab ';
  
  
Scn timestamp SEG_OWNE OPERATION
  
  
------------------------------------------
  
  
5483840-07 SYS DDL
  
  
5483863-07 SYS INSERT
  
  
5483874-07 SYS INSERT
  
  
5483885-07 SYS DELETE
  
  
5483885-07 SYS DELETE
  
  
5483885-07 SYS DELETE
  
  
5483885-07 SYS DELETE
  
  
5483886-07 SYS DELETE
  
  
5483886-07 SYS DELETE
  
  
Nine rows have been selected.
  
  
SQL> select t. SQL _REDO from v $ logmnr_contents t where t. seg_name = 'mytesttab ';
  
  
SQL _REDO
  
  
Bytes ----------------------------------------------------------------------------------------------------
  
  
Create table mytesttab as select * from scott. dept;
  
  
Insert into "SYS". "MYTESTTAB" ("DEPTNO", "DNAME", "LOC") values ('80', 'it', 'wa ');
  
  
Insert into "SYS". "MYTESTTAB" ("DEPTNO", "DNAME", "LOC") values ('90', 'mgr ', 'ymr ');
  
  
Delete from "SYS". "MYTESTTAB" where "DEPTNO" = '10' and "DNAME" = 'account' and "LOC" = 'new
  
  
YORK
  
  
Delete from "SYS". "MYTESTTAB" where "DEPTNO" = '20' and "DNAME" = 'Research 'and "LOC" = 'Dallas'
  
  
Delete from "SYS". "MYTESTTAB" where "DEPTNO" = '30' and "DNAME" = 'sales' and "LOC" = 'Chicago 'and
  
  
Delete from "SYS". "MYTESTTAB" where "DEPTNO" = '40' and "DNAME" = 'operations' and "LOC" = 'boston'
  
  
Delete from "SYS". "MYTESTTAB" where "DEPTNO" = '80' and "DNAME" = 'it' and "LOC" = 'wa 'and ROWID =
  
  
Delete from "SYS". "MYTESTTAB" where "DEPTNO" = '90' and "DNAME" = 'mgr 'and "LOC" = 'lil' and ROWID =
  
  
Nine rows have been selected.
  
  
SQL> select t. SQL _UNDO from v $ logmnr_contents t where t. seg_name = 'mytesttab ';
  
  
SQL _UNDO
  
  
Bytes ----------------------------------------------------------------------------------------------------
  
  
Delete from "SYS". "MYTESTTAB" where "DEPTNO" = '80' and "DNAME" = 'it' and "LOC" = 'wa 'and ROWID =
  
  
Delete from "SYS". "MYTESTTAB" where "DEPTNO" = '90' and "DNAME" = 'mgr 'and "LOC" = 'lil' and ROWID =
  
  
Insert into "SYS". "MYTESTTAB" ("DEPTNO", "DNAME", "LOC") values ('10', 'accounting', 'New YORK ');
  
  
Insert into "SYS". "MYTESTTAB" ("DEPTNO", "DNAME", "LOC") values ('20', 'Research ', 'Dallas ');
  
  
Insert into "SYS". "MYTESTTAB" ("DEPTNO", "DNAME", "LOC") values ('30', 'sales', 'Chicago ');
  
  
Insert into "SYS". "MYTESTTAB" ("DEPTNO", "DNAME", "LOC") values ('40', 'operations', 'boston ');
  
  
Insert into "SYS". "MYTESTTAB" ("DEPTNO", "DNAME", "LOC") values ('80', 'it', 'wa ');
  
  
Insert into "SYS". "MYTESTTAB" ("DEPTNO", "DNAME", "LOC") values ('90', 'mgr ', 'ymr ');
  
  
Nine rows have been selected.
 
 

Here is an introduction to the use of Logmnr instances in Oracle 10g. I hope this introduction will be helpful to you!