Solved

Oracle Log Miner

Posted on 2001-06-18
3
443 Views
Last Modified: 2008-02-07
To all the Gurus out there
  I'm quite new with Oracle....I've read some articles on how we can dig into the redo log files to seek all updates
that have been made to tables in the database.
  Log Miner come into the pictures. Can anyone tell me

1) how to manipulate this features (e.g installation, requirements and steps needs to be consider).

2) If I'm not mistaken there are certain scripts needs to be installed, where can I get the scripts

* I'm using oracle 8.1.5, running on AIX 4.3.3

Thanks in advanced


Yours Truly
Ezree
0
Comment
Question by:ezree
  • 2
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 100 total points
Comment Utility
normally, your question should rather have gone to this topic area:
http://www.experts-exchange.com/jsp/qList.jsp?ta=oracle

Now you might post a 0-point question with a link to your question to attract oracle experts to your question!

Now, i'm not (yet) a guru in Oracle.
I know however that you can query the v$logmnr_contents dynamic view, which can show the undo and redo sql.

Before that, you must however create the dictionary file (by using a parameter in the init file:_
utl_file_dir=d:\Oracle\OraData\Prod (for example)
and then create the following package:
@d:\oracle\Ora81\rdbms\admin\dbmslogmnrd.sql

Execute the build procedure
EXEC DBMS_LOGMNR_D.build ('logmnrd.ora', 'c:\oracle\oradata\PROD')

create the following package:
@d:\oracle\Ora81\rdbms\admin\dbmslogmnr.sql

Now you can add the log files to analyse:
EXEC DBMS_LOGMNR.ADD_LOGFILE 'c:\...\redo01.log', dmbs_logmnr.NEW);
EXEC DBMS_LOGMNR.ADD_LOGFILE 'c:\...\redo02.log', dmbs_logmnr.ADDFILE);
EXEC DBMS_LOGMNR.ADD_LOGFILE 'c:\...\redo03.log', dmbs_logmnr.ADDFILE);
...

Finally (filename same as above (build), the starttime and endtime parameters are optional:
EXEC DBMS_LOGMNR.START_LOGMNR ('c:\oracle\oradata\PROD\logmnrd.ora'<,starttime, endtime>)  


CHeers


0
 
LVL 2

Expert Comment

by:stmontgo
Comment Utility
here is an excellent note on logminer set up

PURPOSE
-------

To provide the steps for setting up LogMiner on the database.
 
SCOPE & APPLICATION
-------------------

This is intended to help user set up LogMiner

RELATED DOCUMENTS
-----------------
Oracle8i Administrator's Guide, Release 8.1.5
Oracle8i Administrator's Guide, Release 8.1.6


Introduction:
===============
LogMiner runs in an Oracle instance with the database either mounted or
unmounted. LogMiner uses a dictionary file, which is a special file that
indicates the database that created it as well as the time the file was
created. The dictionary file is not required, but is recommended.
Without a dictionary file, the equivalent SQL statements will use Oracle
internal object IDs for the object name and present column values as hex data.

For example, instead of the SQL statement:

INSERT INTO emp(name, salary) VALUES ('John Doe', 50000);

LogMiner will display:
insert into Object#2581(col#1, col#2) values (hextoraw('4a6f686e20446f65'),
hextoraw('c306'));"

Create a dictionary file by mounting a database and then extracting dictionary
information into an external file.
 
You must create the dictionary file from the same database that generated the
log files you want to analyze. Once created, you can use the dictionary file
to analyze redo logs.

When creating the dictionary, specify the following:

* DICTIONARY_FILENAME to name the dictionary file.
* DICTIONARY_LOCATION to specify the location of the file.

LogMiner analyzes redo log files from any version 8.0.x and later Oracle database
that uses the same database characterset and is running on the same hardware
as the analyzing instance.

To Create a Dictionary File on an Oracle8i Database:
======================================================

1. Make sure to specify an existing directory that Oracle has permissions
   to write to by the PL/SQL procedure by setting the initialization
   parameter UTL_FILE_DIR in the init.ora.
   For example, set the following to use /oracle/logs:

UTL_FILE_DIR = /oracle/logs

   Be sure to shutdown and restart the instance after adding UTL_FILE_DIR
   to the init.ora.  
 
   If you do not reference this parameter, the procedure will fail.

2. Use SQL*Plus to mount and then open the database whose files you want to
   analyze. For example, enter:

STARTUP
 
3. Execute the PL/SQL procedure DBMS_LOGMNR_D.BUILD. Specify both a file name
   for the dictionary and a directory pathname for the file. This procedure
   creates the dictionary file, which you should use to analyze log files.
   For example, enter the following to create file dictionary.ora in
   /oracle/logs:

(REMEMBER TO INCULDE THE DASH '-' CONTINUATION CHARACTER AT THE END OF
 EACH LINE WHEN ENTERING A MULTI-LINE PL/SQL COMMAND IN SQL*PLUS)

EXECUTE DBMS_LOGMNR_D.BUILD( -
DICTIONARY_FILENAME =>'dictionary.ora', -
DICTIONARY_LOCATION => '/oracle/logs');


To Create a Dictionary File on an Oracle8 Database:
=====================================================

Although LogMiner only runs on databases of release 8.1 or higher, you can
use it to analyze redo logs from release 8.0 databases.

1. Use an O/S command to copy the dbmslmd.sql script, which is contained in the
   $ORACLE_HOME/rdbms/admin directory on the Oracle8i database, to the same
   directory in the Oracle8 database.

For example, enter:

% cp /8.1/oracle/rdbms/admin/dbmslmd.sql /8.0/oracle/rdbms/admin/dbmslmd.sql

NOTE:  In 8.1.5 the script is dbmslogmnrd.sql.  In 8.1.6 the script
            is dbmslmd.sql.

2. Use SQL*Plus to mount and then open the database whose files you want to
   analyze. For example, enter:

STARTUP
 
3. Execute the copied dbmslmd.sql script on the 8.0 database to create the
   DBMS_LOGMNR_D package.

For example, enter:

 
4. Make sure to specify an existing directory that Oracle has permissions
   to write to by the PL/SQL procedure by setting the initialization
   parameter UTL_FILE_DIR in the init.ora.  
   For example, set the following to use /8.0/oracle/logs:

UTL_FILE_DIR = /8.0/oracle/logs

   Be sure to shutdown and restart the instance after adding UTL_FILE_DIR
   to the init.ora.  
 
   If you do not reference this parameter, the procedure will fail.  

5. Execute the PL/SQL procedure DBMS_LOGMNR_D.BUILD. Specify both a file name
   for the dictionary and a directory pathname for the file. This procedure
   creates the dictionary file, which you should use to analyze log files.
   For example, enter the following to create file dictionary.ora in
   /8.0/oracle/logs:

(REMEMBER TO INCULDE THE DASH '-' CONTINUATION CHARACTER AT THE END OF
 EACH LINE WHEN ENTERING A MULTI-LINE PL/SQL COMMAND IN SQL*PLUS)

EXECUTE DBMS_LOGMNR_D.BUILD(-
DICTIONARY_FILENAME =>'dictionary.ora',-
DICTIONARY_LOCATION => '/8.0/oracle/logs');

After creating the dictionary file on the Oracle 8.0.x instance, the
dictionary file and any archived logs to be mined must be moved to the
server running the 8.1.x database on which LogMiner will be run if it is
different from the server which generated the archived logs.


Specifying Redo Logs for Analysis
=====================================

Once you have created a dictionary file, you can begin analyzing redo logs.
Your first step is to specify the log files that you want to analyze using
the ADD_LOGFILE procedure. Use the following constants:

* NEW to create a new list.
* ADDFILE to add redo logs to a list.
* REMOVEFILE to remove redo logs from the list.
To Use LogMiner:
1. Use SQL*Plus to start an Oracle instance, with the database either mounted
   or unmounted.

For example, enter:

STARTUP
 
2. Create a list of logs by specifying the NEW option when executing the
   DBMS_LOGMNR.ADD_LOGFILE procedure. For example, enter the following to
   specify /oracle/logs/log1.f:

(INCLUDE THE '-' WHEN ENTERING THE FOLLOWING)

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME => '/oracle/logs/log1.f', -
OPTIONS => dbms_logmnr.NEW);
 
3. If desired, add more logs by specifying the ADDFILE option.

For example, enter the following to add /oracle/logs/log2.f:

(INCLUDE THE '-' WHEN ENTERING THE FOLLOWING)

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME => '/oracle/logs/log2.f', -
OPTIONS => dbms_logmnr.ADDFILE);
 
4. If desired, remove logs by specifying the REMOVEFILE option.

For example, enter the following to remove /oracle/logs/log2.f:

(INCLUDE THE '-' WHEN ENTERING THE FOLLOWING)

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME => '/oracle/logs/log2.f', -
OPTIONS => dbms_logmnr.REMOVEFILE);


Using LogMiner:
=================

Once you have created a dictionary file and specified which logs to analyze,
you can start LogMiner and begin your analysis. Use the following options to
narrow the range of your search at start time:


This option       Specifies
===========     ==========

STARTSCN       The beginning of an SCN range.
ENDSCN               The termination of an SCN range.
STARTTIME       The beginning of a time interval.
ENDTIME       The end of a time interval.
DICTFILENAME       The name of the dictionary file.


Once you have started LogMiner, you can make use of the following data
dictionary views for analysis:

This view               Displays information about
==================      ============================

V$LOGMNR_DICTIONARY       The dictionary file in use.
V$LOGMNR_PARAMETERS       Current parameter settings for LogMiner.
V$LOGMNR_LOGS               Which redo log files are being analyzed.
V$LOGMNR_CONTENTS       The contents of the redo log files being analyzed.


To Use LogMiner:
=================
1. Issue the DBMS_LOGMNR.START_LOGMNR procedure to start LogMiner utility.

For example, to start LogMiner using /oracle/dictionary.ora, issue:

(INCLUDE THE '-' WHEN ENTERING THE FOLLOWING)

EXECUTE DBMS_LOGMNR.START_LOGMNR( -
DICTFILENAME =>'/oracle/dictionary.ora');
 
Optionally, set the STARTTIME and ENDTIME parameters to filter data by time.
Note that the procedure expects date values: use the TO_DATE function to
specify date and time, as in this example:

(INCLUDE THE '-' WHEN ENTERING THE FOLLOWING)

EXECUTE DBMS_LOGMNR.START_LOGMNR( -
DICTFILENAME => '/oracle/dictionary.ora', -
STARTTIME => to_date('01-Jan-1998 08:30:00', 'DD-MON-YYYY HH:MI:SS'), -
ENDTIME => to_date('01-Jan-1998 08:45:00', 'DD-MON-YYYY HH:MI:SS'));
 
Use the STARTSCN and ENDSCN parameters to filter data by SCN, as in this
example:

(INCLUDE THE '-' WHEN ENTERING THE FOLLOWING)

EXECUTE DBMS_LOGMNR.START_LOGMNR( -
DICTFILENAME => '/oracle/dictionary.ora', -
STARTSCN => 100, -
ENDSCN => 150);
 
2. View the output via the V$LOGMNR_CONTENTS table. LogMiner returns all rows
   in SCN order, which is the same order applied in media recovery.

For example,the following query lists information about operations:

SELECT operation, sql_redo FROM v$logmnr_contents;

OPERATION SQL_REDO              
--------- ----------------------------------------------------------
INTERNAL      
INTERNAL      
START     set transaction read write;  
UPDATE    update SYS.UNDO$ set NAME = 'RS0', USER# = 1, FILE# = 1, BLOCK# = 2450, SCNBAS =
COMMIT    commit;                                                                        
START     set transaction read write;                    
UPDATE    update SYS.UNDO$ set NAME = 'RS0', USER# = 1, FILE# = 1, BLOCK# = 2450, SCNBAS =
COMMIT    commit;                          
START     set transaction read write;    
UPDATE    update SYS.UNDO$ set NAME = 'RS0', USER# = 1, FILE# = 1, BLOCK# = 2450, SCNBAS =
COMMIT    commit;                                                                        
11 rows selected.


Analyzing Archived Redo Log Files from Other Databases:
========================================================

You can run LogMiner on an instance of a database while analyzing redo log
files from a different database. To analyze archived redo log files from other
databases,

LogMiner must:

* Access a dictionary file that is both created from the same database as the
  redo log files and created with the same database character set.
* Run on the same hardware platform that generated the log files, although it
  does not need to be on the same system.
* Use redo log files that can be applied for recovery from Oracle version 8.0
  and later.
.

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
just a friendly reminder...
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In today’s complex data management environments, it is not unusual for UNIX servers to be dedicated to a particular department, purpose, or database.  As a result, a SAS® data analyst often works with multiple servers, each with its own data storage…
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now