Log mining a Redo log file / Archive log file on a remote database

At the source site  

Enable the database to write to a directory you choose, in this example I use /tmp
alter system set utl_file_dir='/tmp' scope=spfile; 
Restart of the database is required in order for the parameter utl_file_dir be active
Extract the LogMiner dictionary to a flat file called dictionary.ora
EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', '/tmp', DBMS_LOGMNR_D.STORE_IN_FLAT_FILE); 

Copy redo log file from source to target:

  • Copy redo log file from ASM to the operating system
asmcmd cp '+DATA/PDB/ONLINELOG/group_1.259.919359545' /tmp 
  • Copy the the redo log file to the target host 
scp /tmp/dictionary.ora /tmp/group_1.259.919359545 target_host:/tmp 
  • Copy the dictionary file from source to target 
scp /tmp/dictionary.ora /tmp/group_1.259.919359545 target_host:/tmp 

On target db 

alter system set utl_file_dir='/tmp' scope=spfile; 
Restart of the database is required in order for the parameter utl_file_dir be active
Add the first redo log file for LogMiner to process
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/tmp/group_1.259.919359545', OPTIONS => DBMS_LOGMNR.NEW); 
Add additional  redo log file/s  to the existing list for LogMiner to process
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/tmp/group_2.261.919359549',OPTIONS => DBMS_LOGMNR.ADDFILE); 
Start LogMiner using the dictionary file
BEGIN
    DBMS_LOGMNR.START_LOGMNR (
        DictFileName => '/tmp/dictionary.ora'
    );
END;
If dictionary does not exist , you can omit it like this
BEGIN
    DBMS_LOGMNR.START_LOGMNR;
end;
Within the current LogMiner session run the following query

select * from V$LOGMNR_CONTENTS where RBASQN=33 and RBABLK > 57190; 
RBASQN - redo log sequence
RBABLK - block number

Comments

Popular posts from this blog

Fixing & Registering ORACLE_HOMES in Central Inventory

Oracle 18c - New Features for Active Data Guard

List RMAN backups size