Logminer

Logminer:

LogMiner is an Oracle utility which uses a dictionary meta data (online , from a flat file, or from redologs ) to scan the redo/archive logs and generates a set of SQL statements which would have the same effect on the database as applying the corresponding redo record.

Logminer can be used to mine the logs of the current database or a remote database.

The following steps are needed to analyze logs of a remote database.

1) Build the Dictionary file of the production database and scp to the mining/test server.
2) Get the list of archive logs which you want to mine from production server and scp to the mining/test server.
3) Add Redo Log Files for mining
4) Start LogMiner
5) Query V$LOGMNR_CONTENTS
6) End the LogMiner Session

Follow the below steps:
Production:
1: Log in as sys user
2: execute sys.dbms_logmnr_d.build ('PROD_dict.ora', '/oracle/admin/PROD/utldir');
3: Determine the archive log sequences that are needed by querying v$archived_log on the remote database. (Look at first_time, next_time values).
Ex: /u006/archive/PROD/PROD_1_4829_651194874.arc
4: send the above 2 files mentioned in step 2 and 3 to Mining/Test Server using scp/ftp/sftp etc.

Mining/Test Server:
1: Log in as sys user
2: exec sys.dbms_logmnr.add_logfile('/export/home/oracle/logminer/archive/PROD_1_4829_651194874.arc');
3: exec sys.Dbms_Logmnr.start_logmnr(dictfilename => '/export/home/oracle/logminer/dict/PROD_dict.ora');
4: Query the V$LOGMNR_CONTENTS to see the mined rows
5: exec sys.DBMS_LOGMNR.end_logmnr;

Note: Do not execute step 5 until you have finished quering V$LOGMNR_CONTENTS.