E Troubleshooting Oracle Database Vault

Enabling Trace Events for the Current Database Session

You can use the ALTER SESSION SET EVENTS SQL statement to enable trace events for the current database session.

1. Log into the database instance as a user who has been granted the DV_ADMIN role and the ALTER SESSION system privilege.

   For example:

   sqlplus leo_dvowner
   Enter password: password
   Connected.
   

2. Enter the ALTER SESSION SET EVENTS SQL statement to set the tracing to low, high, or highest, as described in "Levels of Oracle Database Vault Trace Events".

   • To turn on tracing for failed operations that have a low impact, enter one of the following statements:

     ALTER SESSION SET EVENTS 'TRACE[DV] DISK=LOW';
     
     ALTER SESSION SET EVENTS '47998 TRACE NAME CONTEXT FOREVER, LEVEL 1';
     

   • To turn on tracing for both failed and successful operations that have a high impact, enter one of the following statements:

     ALTER SESSION SET EVENTS 'TRACE[DV] DISK=HIGH';
     
     ALTER SESSION SET EVENTS '47998 TRACE NAME CONTEXT FOREVER, LEVEL 3';
     

   • To turn on tracing for both failed and successful operations with a function and PL/SQL call stack that has the highest impact, enter one of the following statements:

     ALTER SESSION SET EVENTS 'TRACE[DV] DISK=HIGHEST';
     
     ALTER SESSION SET EVENTS '47998 TRACE NAME CONTEXT FOREVER, LEVEL 4';
     

Enabling Trace Events for All Database Sessions

You can use the ALTER SYSTEM SET EVENTS SQL statement to enable Database Vault trace events for all database sessions.

1. Log into the database instance as a user who has been granted the DV_ADMIN role and the ALTER SYSTEM system privilege.

   For example:

   sqlplus leo_dvowner
   Enter password: password
   Connected.
   

2. Enter the ALTER SYSTEM SET EVENTS SQL statement, using the syntax that is shown in Step 2 in "Enabling Trace Events for the Current Database Session".

   For example:

   ALTER SYSTEM SET EVENTS 'TRACE[DV] DISK=LOW';
   

3. Restart the database.

   For example:

   SHUTDOWN IMMEDIATE
   STARTUP
   

Another way that you can enable trace events for all database sessions is to add the following line to the init.ora file, and then restart the database:

event="47998 trace name context forever, level [trace_level]"

Replace trace_level with one of the following values:

• 1 for the lowest level of tracing

• 3 for the high level

• 4 for the highest level

For example:

event="47998 trace name context forever, level [1]"

Management of Trace Events in a Multitenant Environment

You should be aware of how enabling trace events is affected in a multitenant environment.

• Trace events for the current session: In a multitenant environment, running the ALTER SESSION SET EVENTS SQL statement from either the root or a pluggable database (PDB) enables tracing for the current user session. If you switch from one PDB to another PDB (by using the ALTER SESSION SET CONTAINER statement), then tracing is still enabled for the new PDB. You cannot enable tracing for a single PDB in a multitenant container database (CDB); it applies to all PDBs and the root. Remember that must have the ALTER SESSION SET CONTAINER system privilege to move from one PDB to another.

• Trace events for all database sessions: In a multitenant environment, running the ALTER SYSTEM SET EVENTS statement from either the root or a specific PDB enables tracing for all PDBs in the container database.

Finding the Database Vault Trace File Directory Location

You can find the full directory location of trace files by querying the V$DIAG_INFO dynamic view.

• Query the V$DIAG_INFO dynamic view as follows:

  SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Default Trace File';
  
  VALUE
  --------------------------------------------------------------------------------
  /u01/app/oracle/product/12.1.0/log/diag/rdbms/orcl/orcl/trace/orcl_ora_7174.trc
  

Using the Linux grep Command to Search Trace Files for Strings

To query or process the trace files, you can use the Linux grep command to search for strings.

• For example, to find the trace files that show realm authorization failures, enter the following command:

  grep 'Result=Realm Authorization Failed'  *.trc
  

Using the ADR Command Interpreter (ADRCI) Utility to QueryTrace Files

You can query trace files by using the ADR Command Interpreter (ADRCI) command-line utility.

• To use the ADRCI utility to find trace file information, use the SHOW command.

For example, to use ADRCI to find the trace files, enter the SHOW TRACEFILE command:

adrci --To start ACRCI from the command line
adrci> show tracefile

diag/rdbms/orcl/orcl/trace/orcl_m002_14551.trc
diag/rdbms/orcl/orcl/trace/orcl_tmon_13450.trc
diag/rdbms/orcl/orcl/trace/orcl_vktm_963.trc
diag/rdbms/orcl/orcl/trace/alert_orcl.log
...

To find the number of all trace incidents:

adrci> show incident

ADR Home = /u01/app/oracle/product/12.1.0/log/diag/rdbms/orcl/orcl:
*************************************************************************
234 rows fetched

The following ADRCI command returns a list of all trace files whose name contains the word ora:

adrci> show tracefile %ora%

/u01/app/oracle/product/12.1.0/log/diag/rdbms/orcl/orcl/trace/orcl_ora_18841.trc
/u01/app/oracle/product/12.1.0/log/diag/rdbms/orcl/orcl/trace/orcl_ora_12017.trc
/u01/app/oracle/product/12.1.0/log/diag/rdbms/orcl/orcl/trace/orcl_ora_19372.trc
/u01/app/oracle/product/12.1.0/log/diag/rdbms/orcl/orcl/trace/orcl_ora_12221.trc
/u01/app/oracle/product/12.1.0/log/diag/rdbms/orcl/orcl/trace/orcl_ora_1600.trc
...

The following ADRCI command searches for trace files that contain the phrase Realm Authorization Failed:

adrci> show trace %trc -xp "[payload like '%Realm Authorization Failed%']"

Disabling Trace Events for the Current Database Session

You can use the ALTER SESSION SET EVENTS SQL statement to disable Database Vault tracing for the current database session.

1. Log into the database instance as a user who has been granted the DV_ADMIN role and the ALTER SESSION system privilege.

   For example:

   sqlplus leo_dvowner
   Enter password: password
   Connected.
   

2. Enter both of the following SQL statements to disable tracing:

   ALTER SESSION SET EVENTS 'TRACE[DV] OFF'; 
   ALTER SESSION SET EVENTS '47998 trace name context off';
   

   Alternatively, you can use the ALTER SYSTEM statement as well:

   ALTER SYSTEM SET EVENTS 'TRACE[DV] OFF'; 
   ALTER SYSTEM SET EVENTS '47998 trace name context off';
   

Disabling Trace Events for All Database Sessions

You can use the ALTER SYSTEM SET EVENTS SQL statement to disable Database Vault tracing for all database sessions.

1. Log into the database instance as a user who has been granted the DV_ADMIN role and the ALTER SYSTEM system privilege.

   For example:

   sqlplus leo_dvowner
   Enter password: password
   Connected.
   

2. Enter the ALTER SYSTEM SET EVENTS SQL statement, using the syntax that is shown in Step 2 in "Disabling Trace Events for the Current Database Session".

   For example:

   ALTER SYSTEM SET EVENTS 'TRACE[DV] OFF'; 
   

3. Restart the database.

   For example:

   SHUTDOWN IMMEDIATE
   STARTUP
   

Another way that you can disable trace events for all database sessions is to add the following line to the init.ora file, and then restart the database:

event="47998 trace name context off"

Ensure that the init.ora file does not have any conflicting 47998 lines, such as event="47998 trace name context forever, level [1]".

Disabling Trace Events in a Multitenant Environment

You should be aware of how enabling trace events is affected in a multitenant environment.

• Trace events for the current session: In a multitenant environment, running the ALTER SESSION SET EVENTS SQL statement from either the root or a PDB disables tracing for the current user session. If you switch from one PDB to another PDB (by using the ALTER SESSION SET CONTAINER statement), then tracing is still disabled for the new PDB. You cannot disable tracing for a single PDB in a CDB; it applies to all PDBs and the root. Remember that must have the ALTER SESSION SET CONTAINER system privilege to move from one PDB to another.

• Trace events for all database sessions: In a multitenant environment, running the ALTER SYSTEM SET EVENTS statement from either the root or a specific PDB disables tracing for all PDBs in the CDB.