NEW LOGMINER - Based on archivelog generation

Scenario Preview:

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

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

SQL> @d

NAME                             INSTANCE_NAME    DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    CURRENT_SCN        FAILOVER_SCN

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

SMRUPGR1                         SMRUPGR12QA      SMRUPGR12QA                    READ WRITE           PRIMARY          NO                 0

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 => 'lgmnrdict.ora',

dictionary_location => 'LOG_DIR');

END;

/  2    3    4    5    6

PL/SQL procedure successfully completed.

SQL> SQL>

SQL> SQL> select member from v$logfile;

MEMBER

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

/u01/app/oracle/oradata/SMRUPGR12QA/redo01.log

/u01/app/oracle/oradata/SMRUPGR12QA/redo02.log

/u01/app/oracle/oradata/SMRUPGR12QA/redo03.log

• Using mention archive log  values to log. 

SQL> BEGIN

DBMS_LOGMNR.add_logfile (

options => DBMS_LOGMNR.new,

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

DBMS_LOGMNR.add_logfile (

  2    3    4    5    6    7  options => DBMS_LOGMNR.addfile,

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

DBMS_LOGMNR.add_logfile (

  8    9   10   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> sho user

USER is "SYS"

SQL>

• Start log mining location 

SQL> sho user

USER is "SYS"

SQL> BEGIN

-- Start using all logs

DBMS_LOGMNR.start_logmnr (

dictfilename => '/u04/db_backup/expdp/SMRUPGR12QA/logminer/lgmnrdict.ora');

END;

/   2    3    4    5    6

PL/SQL procedure successfully completed.

SQL>

• Check the log mining contents.

SELECT seg_owner, seg_name, count(*) AS Hits

FROM   v$logmnr_contents

WHERE  seg_name NOT LIKE '%$'

GROUP BY seg_owner, seg_name;

• Check Tablevalue operation information details

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

• End log miner execution

SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();

PL/SQL procedure successfully completed.

SQL>