How-To - ArcFM - Execute an Oracle Database Trace

Version 17

    Solution

     

    This document describes the steps required to execute an Oracle trace when a trace is requested by a Schneider Electric Technical Support Engineer. These steps are applicable to Oracle versions 10g, 11g, and 12c. Every attempt has been made to ensure that these steps are generic and applicable to all system configurations. If you experience any issues while following these steps, please contact the Technical Support Engineer assigned to your case.

     

    1. Identify the task to be traced. The task should minimize and simplify the steps as much as possible.
    2. Decide which database user is to be used to log in and complete the task.
    3. Using the following query, ensure that this user has no other sessions connected to the database:
      SELECT a.sid,
        a.serial#,
        a.username,
        a.osuser,
        a.program,
        a.machine,
        b.spid,
        TO_CHAR(a.logon_time,'YYYY-MM-DD hh24:mi:ss') LOGON_TIME,
        a.status
      FROM v$session a,
        v$process b
      WHERE a.type <> 'BACKGROUND'
      AND a.paddr   = b.addr;
    4. As the SYS user, create a logon trigger to start the trace when the user connects to the database and stops the trace when the user disconnects from the database by exiting the application. (Replace the 3 occurrences of <user_name_in_all_caps> with the user identified in Step 2):
      CREATE OR REPLACE TRIGGER ON_LOGON_<user_name_in_all_caps>
      AFTER LOGON ON DATABASE
      WHEN ( USER = '<user_name_in_all_caps>' )
      BEGIN
      execute immediate 'alter session set tracefile_identifier = ''on_logon_<user_name_in_all_caps>''';
      execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
      EXCEPTION
      WHEN OTHERS THEN
      NULL;
      END;

    5. Log in to the application as the user specified in Step 2 and complete the task specified in Step 1, then exit the application.
    6. Retrieve the trace file from the database server. It resides in the location for the Oracle version listed below. Note that <USER_DUMP_DEST>, <DIAGNOSTIC_DEST>, and <INSTANCE_NAME> are all initialization parameters that can be found by connecting to the database in SQL Plus as the SYS or SYSTEM user and executing “show parameter <parameter_name>”.
      - Oracle 10g:  <USER_DUMP_DEST>
      - Oracle 11g:  <DIAGNOSTIC_DEST>\diag\rdbms\<INSTANCE_NAME>\<INSTANCE_NAME>\trace
      - Oracle 12c:  <DIAGNOSTIC_DEST>\diag\rdbms\<INSTANCE_NAME>\<INSTANCE_NAME>\trace
    7. The trace file has a 'on_logon_<user_name_in_all_caps>' appended to the end of the trace file name and has a .trc extension. The files with the .trm extension do not need to be retrieved.
    8. Compress the trace file and provide it to the Technical Support Engineer along with a description of the task that generated it. If there is more than one trace file, provide all of them.
    9. As the SYS user, disable or delete the login trigger that was created in Step 4.