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>

 

Log Miner : Date_Timestamp_logminer

LOGMINER - Based on Date and time generation

Scenario Preview:

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

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.

• Start log mining mentioning date and time

SQL> begin

dbms_logmnr.start_logmnr (

dictfilename => '/u04/db_backup/expdp/SMRUPGR12QA/logminer/date_lgmnrdict.ora',

  2    3    4  starttime => TO_DATE('19-NOV-2020 05:26:00', 'DD-MON-YYYY HH:MI:SS'),

  5  endtime => TO_DATE('19-NOV-2020 08:35:00', 'DD-MON-YYYY HH:MI:SS'));

end;

/  6    7

PL/SQL procedure successfully completed.

• Example to check the logs contents 

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';

• End log mining operations

SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();

PL/SQL procedure successfully completed.

SQL>

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>