Using the PL/SQL Unified Diagnostic Logging and Debugging Framework

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.

This section contains the following topics:

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, data purge and reconciliation exceptions 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 Governance 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. No information is stored for OIM Data Purge scheduled task and Fine Grained Exception BIP report runs.
For troubleshooting in PL/SQL layer for reconciliation operations, Fine Grained Exception BIP report, 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.
- DB Diagnostic Level for OIM GDPR support: FINEST for collecting fine-grained information to debug the Deleted User Account Clean Up scheduled task.
- DB Diagnostic Level for Offline Data Purges: FINEST for collecting fine-grained information to debug the Offline Data Purge operation.
- DB Diagnostic Level for Online Recon Exceptions Purge: 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 OIM Mview Legacy Data Migration: FINEST for collecting fine-grained information to debug the legacy data migration operation for Fine Grained Exception BIP report.
- DB Diagnostic Level for MView creation for BIP report: FINEST for collecting fine-grained information to debug the data generation operation for Fine Grained Exception BIP report.
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, OIM.DBDiagnosticLevelDataPurge, OIM.DBDiagnosticLevelOffPurge, OIM.DBDiagnosticLevelGdprSupp, OIM.DBDiagnosticLevelMviewMig, and OIM.DBDiagnosticLevelMviewBIP system properties.

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

DB Diagnostic Level for Recon with keyword OIM.DBDiagnosticLevelRecon
DB Diagnostic Level for Online Data Purge with keyword OIM.DBDiagnosticLevelDataPurge
DB Diagnostic Level for OIM GDPR support with keyword OIM.DBDiagnosticLevelGdprSupp
DB Diagnostic Level for Offline Data Purges with keyword OIM.DBDiagnosticLevelOffPurge
DB Diagnostic Level for OIM Mview Legacy Data Migration with keyword OIM.DBDiagnosticLevelMviewMig
DB Diagnostic Level for MView creation for BIP report with keyword OIM.DBDiagnosticLevelMviewBIP

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, DB Diagnostic Level for Recon, DB Diagnostic Level for Offline Data Purges, DB Diagnostic Level for OIM GDPR support, DB Diagnostic Level for OIM Mview Legacy Data Migration, and DB Diagnostic Level for MView creation for BIP report system properties.

To configure the diagnostic level for troubleshooting reconciliation or data purge operations in the PL/SQL layer:

Log in to Oracle Identity System Administration.
Under System Management, click System Configuration.
Search for and open the DB Diagnostic Level for Online Data Purge, DB Diagnostic Level for Recon, DB Diagnostic Level for OIM GDPR support, DB Diagnostic Level for Offline Data Purges, DB Diagnostic Level for OIM Mview Legacy Data Migration, or DB Diagnostic Level for MView creation for BIP report system properties.
Modify the value of the system properties to FINE or FINEST.
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 OIM_GDPR_SUPPORT OFFLINE_DATAPURGE RECONEXCEPTIONS DATATRUNCATE OIM_MVIEW_LEGACY_DATA_MIG OIM_MVIEW_BIP_REP_GEN
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;
/