Archive log mining steps(logminer to html)

1.1        Archive log mining steps(logminer to html):

1.1.1        STEP 1: Execute the below query on the target db

alter session set nls_date_format=’dd-mm-yyyy hh24:mi’;

select name,thread#,sequence#,first_time,next_time from v$archived_log where

first_time between to_date(’05-05-2019 16:00′) and to_date(’05-05-2019 20:00′); <<<<<<<<<<<<<<<<Put the time when the record was inserted on source around example around 2019/05/05 17:43:56.030.651,

we need to find corresponding archivelog on target

1.1.2        STEP 2: copy archivelog path

Please copy the archivelog path you get from the step 1, then make sure they are physically available, if not please restore them from backup

1.1.3        STEP 3 : Execute logminer in DB

EXECUTE below query in the TARGET database

SET MARKUP HTML ON SPOOL ON HEAD “<TITLE>LOGMNR – INFO </title> –

<STYLE TYPE=’TEXT/CSS’><!–BODY {background: ffffc6} –></STYLE>”

SET ECHO ON

REM Spooling to html file

spool LOGMNR_Info#1.html

ALTER SESSION SET NLS_DATE_FORMAT=’YYYY-MM-DD HH24:MI:SS’;

set numformat 9999999999999999999999g

exec dbms_logmnr.add_logfile(‘ complete path of arc seq you get from STEP 2’);

exec dbms_logmnr.add_logfile(‘ complete path of arc seq you get from STEP 2′); <– if you get more than one archivelog from step 2, then add those archive logs using this command otherwise keep one

exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

show parameter comp;

select global_name, sysdate from global_name;

select * from v$version;

select * from v$database;

select * from v$instance;

select * from v$nls_parameters;

select * from DATABASE_PROPERTIES;

select xid, username, os_username, DATA_OBJ#, rbasqn, RBABLK, rbabyte, RS_ID, row_id, rollback, seg_name, seg_owner, operation, timestamp, sql_redo, scn, cscn from v$logmnr_contents where seg_name=’CI_ACCT_CHAR’;

EXECUTE DBMS_LOGMNR.END_LOGMNR;

spool off

SET MARKUP HTML OFF

Leave a Reply

Your email address will not be published. Required fields are marked *