PT - Extracting Session level trace based on sid and serial#

Session level trace example  based on sid and serial#

• Checking the database name 

SQL> @d

NAME      INSTANCE_NAME    DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    CURRENT_SCN        FAILOVER_SCN

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

TC12DEV   TC12DEV          TC12DEV                        READ WRITE           PRIMARY          NO                 0

• Command to check sessions details  (describing only particular session only for trace generation)

SQL> @sessions

     INST   SID SERIAL# USERNAME     OSUSER           PROGRAM    LOCKED S hh:mm:ss SQL_ID            SEQ# Current/LastEvent         State (sec)

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

MODULE                                                                                               ACTION

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

        1   298   33658 INFODBA      varunyadav       SQL Develo        I 00:29:48 cr6axzpwa6byz       62 SQL*Net message from clie WAITING   1788

SQL Developer

 9 rows selected.

• Extracting trace based on sis and session#

SQL> DEFINE v_sid =298

SQL> DEFINE v_serial =33658

SQL> BEGIN

DBMS_MONITOR.SESSION_TRACE_ENABLE( SESSION_ID => &v_sid, SERIAL_NUM =>

&v_serial, WAITS => TRUE, BINDS => FALSE);

END;

/  2    3    4    5

old   2: DBMS_MONITOR.SESSION_TRACE_ENABLE( SESSION_ID => &v_sid, SERIAL_NUM =>

new   2: DBMS_MONITOR.SESSION_TRACE_ENABLE( SESSION_ID => 298, SERIAL_NUM =>

old   3: &v_serial, WAITS => TRUE, BINDS => FALSE);

new   3: 33658, WAITS => TRUE, BINDS => FALSE);

PL/SQL procedure successfully completed.

SQL> SELECT P.TRACEFILE FROM V$SESSION S JOIN V$PROCESS P ON S.PADDR = P.ADDR WHERE S.SID = &V_SID;

old   1: SELECT P.TRACEFILE FROM V$SESSION S JOIN V$PROCESS P ON S.PADDR = P.ADDR WHERE S.SID = &V_SID

new   1: SELECT P.TRACEFILE FROM V$SESSION S JOIN V$PROCESS P ON S.PADDR = P.ADDR WHERE S.SID = 298

TRACEFILE

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

/u01/app/oracle/diag/rdbms/tc12dev/TC12DEV/trace/TC12DEV_ora_20976.trc

SQL> BEGIN

DBMS_MONITOR.SESSION_TRACE_DISABLE( SESSION_ID => &v_sid, SERIAL_NUM =>

&v_serial);

END;

/  2    3    4    5

old   2: DBMS_MONITOR.SESSION_TRACE_DISABLE( SESSION_ID => &v_sid, SERIAL_NUM =>

new   2: DBMS_MONITOR.SESSION_TRACE_DISABLE( SESSION_ID => 298, SERIAL_NUM =>

old   3: &v_serial);

new   3: 33658);

PL/SQL procedure successfully completed.

SQL>

Trace output:

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>