> Logminer Introduced in Oracle 8.1 version.
> When to use:-
1. If any user dropped the table and you want to catch him.
2. Some records are deleted from the table and you don't have backup, let us say nearly 1000 records.
in this situation you can retrieve these 1000 records using Logminer.
> Logminer is a utility provided with the Oracle database server which mines the redologs or Archivelogs and data dictionary information to build the SQL statements and keeps the contents of the redolog file in the fixed view called " V$logmnr_contents ".
> How the V$LOGMNR_CONTENTS View is Populated ?
The LogMiner populates this view only in response to a query against it. You must successfully start LogMiner before you can query V$LOGMNR_CONTENTS.
When a SQL select operation is executed against the V$LOGMNR_CONTENTS view, the redo log files are read sequentially.Translated information from the redo log files is returned as rows in the V$LOGMNR_CONTENTS view.
In some cases, certain columns in V$LOGMNR_CONTENTS may not be populated. For example:
The TABLE_SPACE column is not populated for rows where the value of the OPERATION column is DDL.
This is because a DDL may operate on more than one tablespace. For example, a table can be created with multiple partitions spanning multiple table spaces; hence it would not be accurate to populate the column.
> LogMiner does not generate SQL redo or SQL undo for temporary tables. The SQL_REDO column will contain the string "/* No SQL_REDO for temporary tables */" and the
SQL_UNDO column will contain the string "/* No SQL_UNDO for temporary tables */".
> To generate SQL statements Log-miner engine uses data dictionary information. This data dictionary information can be extracted to an external operating system file or redologs using the dbms_logmnr_d.build procedure.
> You must specify a directory for use by the DBMS_LOGMNR_D.BUILD procedure or the procedure will fail. To specify a directory, set the initialization parameter,UTL_FILE_DIR, in the initialization parameter file.
=====================================================================
YOU CAN FILTER AND FORMAT DATA RETURNED TO V$LOGMNR_CONTENTS
=====================================================================
> Showing Only Committed Transactions:-
When you use the COMMITTED_DATA_ONLY option to DBMS_LOGMNR.START_LOGMNR, only rows belonging to committed transactions are shown in the V$LOGMNR_CONTENTS view. This
enables you to filter out rolled back transactions, transactions that are in progress, and internal operations.
To enable this option, specify it when you start LogMiner, as follows:
> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.COMMITTED_DATA_ONLY);
When you specify the COMMITTED_DATA_ONLY option, LogMiner groups together all DML operations that belong to the same transaction.
Transactions are returned in the order in which they were committed.
Filtering Data by Time:-
To filter data by time, set the STARTTIME and ENDTIME parameters in the DBMS_LOGMNR.START_LOGMNR procedure.
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
DICTFILENAME => '/oracle/database/dictionary.ora', -
STARTTIME => '01-Jan-2008 08:30:00', -
ENDTIME => '01-Jan-2008 08:45:00'-
OPTIONS => DBMS_LOGMNR.CONTINUOUS_MINE);
Filtering Data by SCN:-
To filter data by SCN (system change number), use the STARTSCN and ENDSCN parameters to the PL/SQL DBMS_LOGMNR.START_LOGMNR procedure, as shown in this example:
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(-
STARTSCN => 531047, -
ENDSCN => 535695, -
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
DBMS_LOGMNR.CONTINUOUS_MINE);
Supplemental Logging:-
Redo log files are generally used for all type of recoveries. for recovery purpose what ever the data needed is recorded in the redo log files.
However, a redo-based application may require that additional columns be logged in the redo log files. The process of logging these additional columns is called
supplemental logging.
> By default, Oracle Database does not provide any supplemental logging, which means that by default LogMiner is not usable.
Therefore, you must enable at least minimal supplemental logging before generating log files which will be analyzed by LogMiner.
> There are two types of Logging Minimal Supplemental Logging and Identification Key Logging.
Database-Level Supplemental Logging.
There are two types of database-level supplemental logging: minimal supplemental logging and identification key logging, as described in the following sections.
Minimal supplemental logging does not impose significant overhead on the database generating the redo log files.
However, enabling database-wide identification key logging can impose overhead on the database generating the redo log files.
Oracle recommends that you at least enable minimal supplemental logging for LogMiner.
Minimal Supplemental Logging:-
Minimal supplemental logging logs the minimal amount of information needed for LogMiner to identify, group,and merge the redo operations associated with DML changes. It ensures that LogMiner (and any product building on LogMiner technology) has sufficient information to support chained rows and various storage arrangements, such as cluster tables and index-organized tables. To enable minimal supplemental logging, execute the following SQL statement:
====== X X X =====
Steps for Configuring Logminer :-
> In this Scenario we are Mining that Username who Dropped the Table using Logminer utility.
Steps:-
>> Create any directory at os level, I created as 'DEMO' which will use by the DBMS_LOGMNR_D.BUILD procedure which generate SQL statements.
C:\Users\oracle> mkdir demo
C:\Users\oracle> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 29 09:44:46 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
>> Map the above 'DEMO' directory at database level by setting initialization parameter 'utl_file_dir' and bounce the database.
SQL> alter system set utl_file_dir='C:\Users\oracle\demo' scope=spfile;
System altered.
SQL> startup force;
ORACLE instance started.
Total System Global Area 8351150080 bytes
Fixed Size 2271832 bytes
Variable Size 1577059752 bytes
Database Buffers 6761218048 bytes
Redo Buffers 10600448 bytes
Database mounted.
Database opened.
>> Oracle stores information requires at the time of recovery. you need additional information to analyze redo log files.
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
>> When you run this procedure Logminer engine uses data dictionary which will extracted in this file 'DEMO' which you created.
SQL> exec dbms_logmnr_d.build('dfile','C:\Users\oracle\demo');
PL/SQL procedure successfully completed.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
E:\PRODDB\REDO03.LOG
E:\PRODDB\REDO02.LOG
E:\PRODDB\REDO01.LOG
>> Apply the redologs files to start the mining processases. (All these redologs should be from same database and same resetlogs SCN).
SQL> exec dbms_logmnr.add_logfile('E:\PRODDB\REDO01.LOG');
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.add_logfile('E:\PRODDB\REDO02.LOG');
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.add_logfile('E:\PRODDB\REDO03.LOG');
PL/SQL procedure successfully completed.
>> Start logminer now, oracle will pool the logfiles provided through add_logfile in v$logmnr_contents view.
SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'C:\Users\oracle\demo\dfile');
PL/SQL procedure successfully completed.
>> Now we can catch that user who dropped the table, the user is 'TOM' and also we can check who created the table.
SQL> select username,table_name,sql_redo from v$logmnr_contents where seg_name='EMP';
USERNAME TABLE_NAME SQL_REDO
------------ ----------- ------------------------------
TOM EMP create table emp
(
empno number(5),
ename varchar2(5)
);
TOM EMP ALTER TABLE "TOM"."EMP" RENAME TO "BIN$COtUKAWSQAiUfgeFiX7JQg==$0" ;
TOM EMP drop table emp AS "BIN$COtUKAWSQAiUfgeFiX7JQg==$0" ;
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();
PL/SQL procedure successfully completed.
Happy Learning...
No comments:
Post a Comment