LOG MINER : Log miner_current_scn

LOGMINER - Based on SCN 

Scenario Preview:

We have archive log of particular date, with the help of  logminer we can extract the value based on SCN Number.

In general we can extract object value based on archive log | Timestamp date |  SCN Number

Operating System: OEL 7.7 64 bit

Database Version : 12.2.0.1 

Database : SMRUPGR12QA

Schema : infodba

Log Miner table : PFND0GENERALAUDIT

• Check UTL_FILE_DIR location below.

SQL> sho parameter utl_file_dir

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

utl_file_dir                         string

• We need to take bounce of  database as parameter value is not set as dyanamics to set UTL_FILE_DIR  location.

SQL> alter system set utl_file_dir='/u04/db_backup/expdp/SMRUPGR12QA/logminer' scope=spfile;

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 2147483648 bytes

Fixed Size                  3712904 bytes

Variable Size             822085752 bytes

Database Buffers         1308622848 bytes

Redo Buffers               13062144 bytes

Database mounted.

Database opened.

SQL> @d

NAME                             INSTANCE_NAME    DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    CURRENT_SCN        FAILOVER_SCN

-------------------------------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------

SMRUPGR1                         SMRUPGR12QA      SMRUPGR12QA                    READ WRITE           PRIMARY          NO                 0

SQL> sho parameter utl_file_dir

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

utl_file_dir                         string      /u04/db_backup/expdp/SMRUPGR12

                                                 QA/logminer

• Create directory for storing logminer file location 

SQL> CREATE OR REPLACE DIRECTORY LOG_DIR AS '/u04/db_backup/expdp/SMRUPGR12QA/logminer';

Directory created.

• Optional if we wants to exract from particular users

SQL> GRANT READ, WRITE ON DIRECTORY LOG_DIR TO infodba;

Grant succeeded.

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME

--------

NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

• Start log miner specifying name and location 

 SQL>  BEGIN

sys.DBMS_LOGMNR_D.build (

dictionary_filename => 'date_lgmnrdict.ora',

dictionary_location => 'LOG_DIR');

END;

/  2    3    4    5    6

PL/SQL procedure successfully completed.

• Using mention and specify the  archive log values below. 

SQL> BEGIN

DBMS_LOGMNR.add_logfile (

  2    3  options => DBMS_LOGMNR.new,

logfilename => '/u04/db_backup/Flash_recovery_area/SMRUPGR12QA/SMRUPGR12QA/archivelog/2020_11_19/o1_mf_1_737_hvcwzj71_.arc');

  4    5

  6  DBMS_LOGMNR.add_logfile (

  7  options => DBMS_LOGMNR.addfile,

  8  logfilename => '/u04/db_backup/Flash_recovery_area/SMRUPGR12QA/SMRUPGR12QA/archivelog/2020_11_19/o1_mf_1_738_hvd82hth_.arc');

  9

 10  DBMS_LOGMNR.add_logfile (

 11  options => DBMS_LOGMNR.addfile,

 12  logfilename => '/u04/db_backup/Flash_recovery_area/SMRUPGR12QA/SMRUPGR12QA/archivelog/2020_11_19/o1_mf_1_739_hvfq7xpq_.arc');

 13

 14  END;

 15  /

PL/SQL procedure successfully completed. 

SQL>  BEGIN

sys.DBMS_LOGMNR_D.build (

dictionary_filename => 'date_lgmnrdict.ora',

dictionary_location => 'LOG_DIR');

END;

/  2    3    4    5    6

PL/SQL procedure successfully completed.

• Using mention and specify the archive log values below. 

SQL> BEGIN

DBMS_LOGMNR.add_logfile (

  2    3  options => DBMS_LOGMNR.new,

logfilename => '/u04/db_backup/Flash_recovery_area/SMRUPGR12QA/SMRUPGR12QA/archivelog/2020_11_19/o1_mf_1_737_hvcwzj71_.arc');

  4    5

  6  DBMS_LOGMNR.add_logfile (

  7  options => DBMS_LOGMNR.addfile,

  8  logfilename => '/u04/db_backup/Flash_recovery_area/SMRUPGR12QA/SMRUPGR12QA/archivelog/2020_11_19/o1_mf_1_738_hvd82hth_.arc');

  9

 10  DBMS_LOGMNR.add_logfile (

 11  options => DBMS_LOGMNR.addfile,

 12  logfilename => '/u04/db_backup/Flash_recovery_area/SMRUPGR12QA/SMRUPGR12QA/archivelog/2020_11_19/o1_mf_1_739_hvfq7xpq_.arc');

 13

 14  END;

 15  /

PL/SQL procedure successfully completed.

• Log miner Based on SCN Number

begin

DBMS_LOGMNR.start_logmnr (

dictfilename => '/oradb/logminer/lgmnrdict.ora',

startscn => 20734338,

endscn => 20834345);

END;

/ 

PL/SQL procedure successfully completed.

SQL>

SELECT seg_owner, seg_name, count(*) AS Hits

FROM   v$logmnr_contents

WHERE  seg_name NOT LIKE '%$'

GROUP BY seg_owner, seg_name;

SELECT OPERATION, STATUS, SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER = 'INFODBA' AND TABLE_NAME = 'PFND0GENERALAUDIT';

SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();

PL/SQL procedure successfully completed.

SQL>