analyze redo/archive using logminer

First of all, query on V$DATABASE to check out if SUPPLEMENTAL_LOG_DATA_MIN is YES OR NO.


SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;


You can use this following query to alter SUPPLEMENTAL_LOG_DATA_MIN flag.


ALTER DATABASE ADD SUPPLEMENTAL LOG DATA ;


Those SQL Commands are to add Log file and start Log Miner.


--Add logfile to Log Miner

exec DBMS_LOGMNR.ADD_LOGFILE('/archivefs/flash_recovery_area/ABABIL/archivelog/2012_07_02/o1_mf_1_17205_7z2m4bfp_.arc');

--start log miner

exec DBMS_LOGMNR.START_LOGMNR(options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

Using SQLPLUS as sysdba, execute this following Code Snippet to retrieve Log/Archive Details



col USERNAME format a20
col OS_USERNAME format a20
col MACHINE_NAME format a20
col TABLE_NAME format a20
col SEG_OWNER format a20
col SQL_REDO format a1000
col SQL_UNDO format a1000
set lines 2200
set pages 200

Spool /home/oracle/miner_log.log

select * FROM  V$LOGMNR_CONTENTS   

Spool off;

Finally, use this SQL Command to end logmining after you are done with log/archive.


DBMS_LOGMNR.END_LOGMNR;

Thanks
A Rahim Khan

Advertisements
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: