28 Using the PL/SQL Unified Diagnostic Logging and Debugging Framework

The PL/SQL Unified Diagnostic Logging and Debugging framework captures diagnostic information from the PL/SQL layer for various operations, such as reconciliation and real-time data purge, while ensuring that performance, scalability, and availability are not affected.

28.1 Understanding the PL/SQL Unified Diagnostic Logging and Debugging Framework

Understanding the PL/SQL Unified Diagnostic Logging and Debugging framework involves understanding diagnostic logging and debugging, how to configure the system properties to control logging of diagnostic data, and the other important features of the framework.

28.1.1 About the PL/SQL Unified Diagnostic Logging and Debugging Framework

The PL/SQL Unified Diagnostic Logging and Debugging framework helps track progress and debug problems with operations in the PL/SQL layer based on the diagnostic level that you select for the operations.

Without some kind of debugging functionality in the PL/SQL operations code, it can be difficult to track down the source of a PL/SQL error for operations, such as reconciliation and data purge.

The framework provides logging and debugging information as operations run. You can control the amount of information to be logged by using the system properties.

Summary and detailed information is captured in two separate diagnostic logging tables, DIAG_LOG and DIAG_LOG_DTLS. By default, the DIAG_LOG_DTLS table does not store diagnostic data for OIM operations that completes successfully. It stores only relevant data for unsuccessful runs.

28.1.2 Features of the Framework

You can control the level of data collection in PL/SQL Unified Diagnostic Logging and Debugging framework for reconciliation and data purge operations by setting the values of system properties.

The framework is enabled in Oracle Identity Manager by default. Note the following information about the framework:

  • By default, coarse-grained level information is captured in PL/SQL diagnostic logging tables as a log for reconciliation runs, and no information is stored for OIM Data Purge scheduled task runs.

  • For troubleshooting in PL/SQL layer for reconciliation operations or data purge executions, you can set the following values for the system properties:

    • DB Diagnostic Level for Recon: FINE for collecting fine-grained informational events, or FINEST for collecting fine-grained informational events along with data for collection variables that are used as input to Stored Program Units. FINE is the recommended value.

    • DB Diagnostic Level for Online Data Purge: FINEST for collecting fine-grained information to debug the online data purge operation.

  • When the diagnostic data is captured, you can reset the values of the system properties to the default values for the operations.

  • Data growth and the subsequent footprint in the PL/SQL diagnostic logging tables are controlled on an on-going basis.

28.1.3 Configurable Diagnostic Levels Provided in the Framework

You can configure the amount and the type of information written to the PL/SQL diagnostic logging tables by specifying the diagnostic level.

You can configure the following diagnostic levels by using system properties for OIM operations:

  • INFO: This is the default level for reconciliation operation. This level designates informational messages that highlight the progress of the operation at coarse-grained level. This does not have any performance impact on the operations.

  • FINE: This level designates fine-grained informational events that are most useful for debugging an unsuccessful operation. If an operation fails and you need diagnostic information, you can set this diagnostic level by configuring a system property.

  • FINEST: This level designates fine-grained informational events that are most useful for debugging an operation. Collection type variables passed as input parameters to the particular operation subprograms are also logged in this level. If an operation fails and you need diagnostic information, you can set this diagnostic level by configuring a system property.

  • NONE: This is the default level for real-time data purge operation. This level disables the PL/SQL diagnostic logging. If you are facing performance issues during any of the operations because of the PL/SQL diagnostic logging, then you can configure a system property to set this level to switch off logging for any operation.

For the diagnostic levels, the data captured in the diagnostic logging tables are segregated into the message types listed in Table 28-1.

Table 28-1 Message Types for Diagnostic Levels

Message Type Message Description Available in Diagnostic Level
NOTIFICATION A major lifecycle event, such as the activation or deactivation of primary programs or subprograms of an operation. INFO, FINE, FINEST
DEBUG Detailed trace or debug information that can help Oracle Support diagnose problems for the particular operation. FINE, FINEST
WARNING A potential problem that the administrator should review. Examples are invalid parameter values or nonexistence of a specified file. INFO, FINE, FINEST
FATAL A serious problem that requires immediate attention from the administrator. INFO, FINE, FINEST

28.1.4 Configurable System Properties to Control Logging

To control the amount of logging for reconciliation and data purge, you can set the diagnostic levels as values for the OIM.DBDiagnosticLevelRecon and OIM.DBDiagnosticLevelDataPurge system properties.

The following system properties control the amount of logging for reconciliation and OIM Data Purge scheduled task runs:

  • DB Diagnostic Level for Recon with keyword OIM.DBDiagnosticLevelRecon

  • DB Diagnostic Level for Online Data Purge with keyword OIM.DBDiagnosticLevelDataPurge

See Default System Properties in Oracle Identity Governance for information about the system properties.

28.2 Configuring the Diagnostic Level

You can set the diagnostic level for reconciliation and online data purge operations by setting the values of the DB Diagnostic Level for Online Data Purge and DB Diagnostic Level for Recon system properties.

To configure the diagnostic level for troubleshooting reconciliation or data purge operations in the PL/SQL layer:
  1. Log in to Oracle Identity System Administration.
  2. Under System Management, click System Configuration.
  3. Search for and open the DB Diagnostic Level for Online Data Purge or DB Diagnostic Level for Recon system properties.
  4. Modify the value of the system properties to FINE or FINEST.
  5. Click Save.
After the diagnostic data is collected, reset the values of the system properties to the default values.

28.3 Understanding the Data Captured by PL/SQL Diagnostic Logging Tables

The PL/SQL Unified Diagnostic Logging and Debugging framework collects information in the background based on the diagnostic level set.

The framework can capture the following metrics:

  • Summary level info available for the operations run:

    • Batch-level summary for reconciliation operations

    • Run-level summary for real-time data purge operations

  • ECID for transactions

  • Success or failure status

  • Starting, ending, and execution time for the operation-level and subprogram-level run

  • Subprogram run-level diagnostic information

The following tables store the diagnostic information for the operations:

The DIAG_LOG Table

This table stores operation-level run data. Table 28-2 lists the columns of the DIAG_LOG table.

Table 28-2 The DIAG_LOG Table

Column Description
DIAG_LOG_KEY Stores the keys to uniquely identify the operation-level runs
ECID Stores the unique identifier to correlate events or requests associated with the same transaction across several components
MODULE_NAME Stores the following OIM operation names:
  • RECONCILIATION

  • DATAPURGE

DIAGNOSTIC_LEVEL
Stores the following values:
  • INFO

  • FINE

  • FINEST

  • NONE

START_TIME Stores the start time of the entire operation run
END_TIME Stores the end time of the entire operation run
STATUS
Stores the overall status of the operation run, which can be any one of the following during the run:
  • STARTED

  • COMPLETED

  • ERRORED_OUT, which means that the operation run could not proceed because of run-time errors. You can explore the root cause further by using the DESCRIPTION column.

DESCRIPTION Stores the operation run-level description, such as RB_KEY (reconciliation batch key) and RJ_KEY (reconciliation job key), and the type of reconciliation, such as trusted source, target resource, role, or role hierarchy

The DIAG_LOG_DTLS Table

This table stores the subprogram run-level diagnostic details. Table 28-3 lists the columns of the DIAG_LOG_DTLS table.

Table 28-3 The DIAG_LOG_DTLS Table

Column Description
DIAG_LOGDTLS_KEY Stores the keys to uniquely identify a subprogram in an operation
DIAG_LOG_KEY Stores the logical foreign key for the DIAG_LOG table
MESSAGE_TYPE

Stores the following message types based on the diagnostic level set for the operation:

  • NOTIFICATION

  • DEBUG

  • FATAL

  • WARNING

SUBPROGRAM_NAME Stores the name of the subprogram invoked during operation run
SUBPROGRAM_EXEC_TIME Stores the execution time of the subprogram invoked during the operation run
SUBPROGRAM_RUN_NOTE Stores the run-level log of the subprogram invoked during the operation run

28.4 Collecting Data Captured by PL/SQL Diagnostic Logging Tables

You can share the diagnostic data collected in the DIAG_LOG and DIAG_LOG_DTLS tables in Excel format or by using Oracle Data Pump Export/Import utility.

To help troubleshoot issues with reconciliation and data purge operations, you may want to share diagnostic data with Oracle. You can use one of the following methods to share the diagnostic data collected in the DIAG_LOG and DIAG_LOG_DTLS tables:

  • If the data volume in the tables is no more than a few thousands, then you can share the data from the two tables in a Microsoft Excel spreadsheet.

  • If the data volume is more than a few thousand, then you can share the diagnostic data in a dump file. To create the dump file, use the Oracle Data Pump Export/Import utility. Use the following command to create a DiagTblsExp.dmp file that includes the two tables:

    expdp system/<SYSTEM_PASSWD> tables=<OIM_SCHEMA_NAME>.DIAG_LOG, <OIM_SCHEMA_NAME>.DIAG_LOG_DTLS directory=DATA_PUMP_DIR dumpfile=DiagTblsExp.dmp logfile=DiagTblsExp.log

    Here, replace <SYSTEM_PASSWD> with the SYSTEM user password and <OIM_SCHEMA_NAME> with the name of the Oracle Identity Manager schema. For example:

    expdp system/PASSWORD tables=DEV_OIM.DIAG_LOG, TEST_OIM.DIAG_LOG_DTLS directory=DATA_PUMP_DIR dumpfile=DiagTblsExp.dmp logfile=DiagTblsExp.log

28.5 Controlling Data Growth of PL/SQL Diagnostic Logging Tables

By default, the DIAGNOSTIC_MAINT Oracle Database scheduler job is automatically scheduled to purge data from the diagnostic logging tables. You can change the retention period as required.

To control the data growth in the two PL/SQL diagnostic logging tables (DIAG_LOG and DIAG_LOG_DTLS), DIAGNOSTIC_MAINT is automatically scheduled to run once a week on Sunday 05:00 AM.

By default, the job performs the following operations:

  • All records with status COMPLETED are purged from the DIAG_LOG table.

  • All records in either table from the last 7 days that have the status STARTED or ERRORED_OUT are retained.

If you want to increase the retention period from the default value of 7 days, then log in as OIM User and run the following block:

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE (
   name         =>  'DIAGNOSTIC_MAINT',
   attribute    =>  'job_action',
   value        =>  'BEGIN oim_pkg_db_diagnostics.oim_sp_diag_purge(<RETENTION_PERIOD>); END;');
END;
/

Here, replace <RETENTION_PERIOD> with the appropriate value as required. For example, to change the retention period to 15 days, run:

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE (
   name         =>  'DIAGNOSTIC_MAINT',
   attribute    =>  'job_action',
   value        =>  'BEGIN oim_pkg_db_diagnostics.oim_sp_diag_purge(15); END;');
END;
/