Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Thursday, 8 March 2012

How to Use LogMinor in Oracle 9i

What is LogMiner?
logMiner is a recovery utility. You can use it to recover the data from oracle redo log and archive log file. The Oracle LogMiner utility enables you to query redo logs through a SQL interface. Redo logs contain information about the history of activity on a database.
Purpose of LogMiner?
·         LogMiner is a utility which help us to extract the data from the redo logs (Online/Offline) When you run LogMiner, you need to specify the names of redo logs that you want to analyze
·         LogMiner retrieves information from those redo logs and returns it through the V$LOGMNR_CONTENTS
·         LogMiner only runs in Oracle version 8.1 or higher but you can use it to analyze redo logs from release 8.0 or later database.
·         The block size (DB_BLOCK_SIZE) of the analyzing instance must be identical to the block size of the log producing instance.
·         If this is not the case, you will receive an error indicating the archive log is corrupted (when it is probably not).
·         By default LogMiner returns all the rows in SCN order unless you have used the option COMMITTED_DATA_ONLY.
·         The option COMMITTED_DATA_ONLY to specify that only committed transactions should be retrieved.
·         SCN order is the order normally applied in media recovery
Benefit of LogMiner?
1.    Pinpointing when a logical corruption to a database; suppose when a row is accidentally deleted then logMiner helps to recover the database exact time based and changed based recovery.
2.    Perform table specific undo operation to return the table to its original state. LogMiner reconstruct the SQL statement in reverse order from which they are executed.
3.    It helps in performance tuning and capacity planning. You can determine which table gets the most update and insert. That information provides a historical perspective on disk access statistics, which can be used for tuning purpose.
4.    Performing post auditing; LogMiner is used to track any DML and DDL performed on database in the order they were executed.

Accessing Information Stored in Redo Logs

Oracle Corporation provides SQL access to the redo logs through LogMiner, which is part of the Oracle database server. LogMiner presents the information in the redo logs through the V$LOGMNR_CONTENTS fixed view. This view contains historical information about changes made to the database including, but not limited to, the following:
·         The type of change made to the database (INSERT, UPDATE, DELETE, or DDL).
·         The SCN at which a change was made (SCN column).
·         The SCN at which a change was committed (COMMIT_SCN column).
·         The transaction to which a change belongs (XIDUSN, XIDSLT, and XIDSQN columns).
·         The table and schema name of the modified object (SEG_NAME and SEG_OWNER columns).
·         The name of the user who issued the DDL or DML statement to make the change (USERNAME column).
·         Reconstructed SQL statements showing SQL that is equivalent (but not necessarily identical) to the SQL used to generate the redo records (SQL_REDO column). If a password is part of the statement in a SQL_REDO column, the password is encrypted.
·         Reconstructed SQL statements showing the SQL statements needed to undo the change (SQL_UNDO column). SQL_UNDO columns that correspond to DDL statements are always NULL. Similarly, the SQL_UNDO column may be NULL for some data types and for rolled back operations.
·         The redo logs contain internally generated numerical identifiers to identify tables and their associated columns. To reconstruct SQL statements, LogMiner needs to know how the internal identifiers map to user-defined names. This mapping information is stored in the data dictionary for the database. LogMiner provides a procedure (DBMS_LOGMNR_D.BUILD) that lets you extract the data dictionary.
How to Setup LogMinor? 
Minimum Supplemental (Required) for log Data:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Adding new archive and redologs files:
exec dbms_logmnr.add_logfile( 'C:\oracle1\oradata\SHAAN\Archive\74.ARC', dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile( 'C:\oracle1\oradata\SHAAN\Archive\75.ARC', dbms_logmnr.addfile);
Removing archive file for LOGMINOR list (If Required):
exec dbms_logmnr.add_logfile( 'C:\oracle1\oradata\SHAAN\Archive\74.ARC', dbms_logmnr.removefile);
Start the LOGMNR dictionary with flat file option:
EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME => 'C:\Oracle1\database\dictionary.ora');
---------------------------------------------------------------------------------------------------------------------
Example QUERY ON V$LOGMNR_CONTENTS:
SELECT USERNAME, SCN, CSCN, TIMESTAMP, SQL_UNDO, SQL_REDO
FROM V$LOGMNR_CONTENTS
WHERE SEG_OWNER = 'HR' AND OPERATION  in ('INSERT', 'UPDATE', 'DELETE');
Filtering Commited Data Only:
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => -
DBMS_LOGMNR.COMMITTED_DATA_ONLY);
SELECT USERNAME, SCN, CSCN, TIMESTAMP, SQL_UNDO, SQL_REDO
FROM V$LOGMNR_CONTENTS
WHERE SEG_OWNER = 'HR' AND OPERATION  in ('INSERT', 'UPDATE', 'DELETE');
Filtring data by Timestamp:
SELECT NAME, FIRST_TIME FROM V$ARCHIVED_LOG WHERE SEQUENCE# = (SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG
WHERE DICTIONARY_BEGIN = 'YES');
EXECUTE DBMS_LOGMNR.START_LOGMNR( -
DICTFILENAME => 'C:\oracle1\database\dictionary.ora', -
STARTTIME => TO_DATE('17-DEC-2011 08:30:00', 'DD-MON-YYYY HH:MI:SS'), -
ENDTIME => TO_DATE('18-DEC-2011 08:45:00', 'DD-MON-YYYY HH:MI:SS'));
SELECT (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,
USERNAME AS USERNAME,SQL_REDO AS SQL_REDO,SQL_UNDO AS SQL_UNDO
FROM V$LOGMNR_CONTENTS
WHERE SEG_OWNER='HR' AND OPERATION IN ('DELETE', INSERT', 'UPDATE');
Filtering data by SCN:
SELECT SCN, TIMESTAMP, LOG_ID, SEG_OWNER, SQL_UNDO, SQL_REDO FROM V$LOGMNR_CONTENTS WHERE OPERATION IN ('INSERT', 'UPDATE', 'DELETE') AND USERNAME='HR' ORDER BY SCN;
EXECUTE DBMS_LOGMNR.START_LOGMNR( -DICTFILENAME => 'C:\oracle1\database\dictionary.ora', -STARTSCN => 24384179, -ENDSCN => 24400920);
SELECT (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,USERNAME AS USERNAME,SQL_REDO AS SQL_REDO,SQL_UNDO AS SQL_UNDO
FROM V$LOGMNR_CONTENTS
WHERE SEG_OWNER='HR' AND OPERATION IN ('DELETE', INSERT', 'UPDATE');
Extracting Actual Data Values from Redo Logs (use of MINE_VALUE Function)
SELECT SQL_REDO FROM V$LOGMNR_CONTENTS
WHERE SEG_NAME= 'SHAAN' AND SEG_OWNER = 'HR' AND OPERATION= 'UPDATE'
AND DBMS_LOGMNR.MINE_VALUE(REDO_VALUE, 'HR.SHAAN.SALARY') >
2*DBMS_LOGMNR.MINE_VALUE(UNDO_VALUE, 'HR.SHAAN.SALARY')
Ending LOGMINER Session:
EXECUTE DBMS_LOGMNR.END_LOGMNR;
Important Dictionary for LOGMINOR:
SELECT * FROM V$LOGMNR_DICTIONARY;
SELECT * FROM V$LOGMNR_CONTENTS;
SELECT * FROM V$LOGMNR_LOGS;
SELECT * FROM V$LOGMNR_PARAMETERS;

2 comments:

  1. Hi Shahid,

    Can i use redologs to perform live redefinition of a table? My table is having 7Million records and i need to perform ALTER on one of the column. As always, downtime is not allowed, in hours definitely not.

    ReplyDelete
  2. Order a Sparkling White Smiles Custom Teeth Whitening System online and SAVE BIG!
    * 10 shades whiter in days!
    * Professional Results Are Guaranteed.
    * As good as your dentist, for a fraction of the cost.
    * Same as dentists use.

    ReplyDelete