Oracle | DBMS

Oracle DBMS_ADR Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai is now availble. If you haven't you downloaded already: Why?

Purpose This package is an administrative interface for the Automatic Diagnostic Repository.

AUTHID DEFINER

Constants

Name	Data Type	Value
ODL Message Types
log_msg_type_unknown	INTEGER	1
log_msg_type_incident	INTEGER	2
log_msg_type_error	INTEGER	3
log_msg_type_warning	INTEGER	4
log_msg_type_notification	INTEGER	5
log_msg_type_trace	INTEGER	6
General
NOERROR	INTEGER	0
Purge Default Threshold
PURGE_DEFAULT_THRESHOLD	NUMBER	90

Dependencies

ADR_HOME_T	ADR_LOG_MSG_ARGS_T	ADR_MSG_TEMPLATE_T
ADR_INCIDENT_CORR_KEYS_T	ADR_LOG_MSG_ECID_T	DBMS_ADR_INTERNAL
ADR_INCIDENT_ERR_ARGS_T	ADR_LOG_MSG_ERRID_T	DBMS_ADR_LIB
ADR_INCIDENT_FILES_T	ADR_LOG_MSG_SUPPL_ATTRS_T	DBMS_SYSTEM
ADR_INCIDENT_T

Documented No

First Available 11.2

Pragma PRAGMA SUPPLEMENTAL_LOG_DATA(default, READ_ONLY);

Security Model Owned by SYS with EXECUTE granted to the DBA role

Source {ORACLE_HOME}/rdbms/admin/dbmsadr.sql

Subprograms

CLEANOUT_SCHEMA	GET_LOG_LOCATION	SET_EXCEPTION_MODE
CREATE_INCIDENT	GET_TRACE_LOCATION	SET_LOG_MSG_TEMPLATE
DOWNGRADE_SCHEMA	MIGRATE_SCHEMA	SET_TRACEFILE_IDENTIFIER
GET_CALL_ERROR_MSG	RECOVER_SCHEMA	WRITE_LOG
GET_CALL_STATUS	RUN_PURGE	WRITE_TRACE

CLEANOUT_SCHEMA

Recreates the ADR home, without any diagnostic contents dbms_adr.cleanout_schema;

exec dbms_adr.cleanout_schema;

      

      PL/SQL procedure successfully completed.

CREATE_INCIDENT

creates an incident in the current RDBMS ADR Home dbms_adr.create_incident( problem_key IN VARCHAR2, error_facility IN VARCHAR2 DEFAULT NULL, error_number IN INTEGER DEFAULT NULL, error_message IN VARCHAR2 DEFAULT NULL, error_args IN adr_incident_err_args_t DEFAULT NULL, ecid IN VARCHAR2 DEFAULT NULL, signalling_component IN VARCHAR2 DEFAULT NULL, signalling_subcomponent IN VARCHAR2 DEFAULT NULL, suspect_component IN VARCHAR2 DEFAULT NULL, suspect_subcomponent IN VARCHAR2 DEFAULT NULL, correlation_keys IN adr_incident_corr_keys_t DEFAULT NULL, files IN adr_incident_files_t DEFAULT NULL) RETURN adr_incident_t;

TBD

DOWNGRADE_SCHEMA

Downgrades the ADR home by restoring files dbms_adr.downgrade_schema;

exec dbms_adr.downgrade_schema;

      

      PL/SQL procedure successfully completed.

GET_CALL_ERROR_MSG

Returns the error message if the last call to DBMS_ADR API that returned an error. Returns NULL if no error dbms_adr.get_call_error_msg RETURN VARCHAR2;

SELECT dbms_adr.get_call_error_msg;

      

      GET_CALL_ERROR_MSG

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

GET_CALL_STATUS

Returns the status of the last call to the DBMS_ADR API dbms_adr.get_call_status RETURN NUMBER;

SELECT dbms_adr.get_call_status;

      

      GET_CALL_STATUS

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

                    0

GET_LOG_LOCATION

Returns the complete path of the log directory in the RDBMS ADR home dbms_adr.get_log_location RETURN VARCHAR2;

SELECT dbms_adr.get_log_location;

      

      GET_LOG_LOCATION

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

      /u01/app/oracle/diag/rdbms/asra23ai_grd_iad/ASRA23ai/alert

GET_TRACE_LOCATION

Returns the complete path of the trace directory in the RDBMS ADR home dbms_adr.get_trace_location RETURN VARCHAR2;

SELECT dbms_adr.get_trace_location;

      

      GET_TRACE_LOCATION

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

      /u01/app/oracle/diag/rdbms/asra23ai_grd_iad/ASRA23ai/trace

MIGRATE_SCHEMA

Migrates the ADR home to the current version dbms_adr.migrate_schema;

exec dbms_adr.migrate_schema;

      

      PL/SQL procedure successfully completed.

RECOVER_SCHEMA

Attempts to bring the ADR home to a consistent state after a failed migrate or downgrade operation dbms_adr.recover_schema;

exec dbms_adr.recover_schema;

      

      PL/SQL procedure successfully completed.

RUN_PURGE

Purges diagnostics for the current container dbms_adr.run_purge(threshold IN NUMBER);

exec dbms_adr.run_purge(1);

      

      PL/SQL procedure successfully completed.

SET_EXCEPTION_MODE

sets the ADR exception mode. If TRUE all the exceptions will be raised to client: If FALSE suppressed. dbms_adr.set_exception_mode(exc_mode IN BOOLEAN DEFAULT FALSE);

exec dbms_adr.set_exception_mode(TRUE);

      

      PL/SQL procedure successfully completed.

SET_LOG_MSG_TEMPLATE

Creates a log message template object which can be used in the write_log API call. The purpose of the log message template object is to avoid have to specify common parameters in each call to write_log. When passing a template object, optional parameters that were not set explicitly will be copied from the template object instead. dbms_adr.set_log_msg_template( org_id IN VARCHAR2 DEFAULT NULL, component_id IN VARCHAR2 DEFAULT NULL, instance_id IN VARCHAR2 DEFAULT NULL, hosting_client_id IN VARCHAR2 DEFAULT NULL, msg_group IN VARCHAR2 DEFAULT NULL, host_id IN VARCHAR2 DEFAULT NULL, host_nwaddr IN VARCHAR2 DEFAULT NULL, module_id IN VARCHAR2 DEFAULT NULL, process_id IN VARCHAR2 DEFAULT NULL, thread_id IN VARCHAR2 DEFAULT NULL, user_id IN VARCHAR2 DEFAULT NULL, upstream_comp_id IN VARCHAR2 DEFAULT NULL, downstream_comp_id IN VARCHAR2 DEFAULT NULL, ecid IN adr_log_msg_ecid_t DEFAULT NULL, error_instance_id IN adr_log_msg_errid_t DEFAULT NULL, msg_args IN adr_log_msg_args_t DEFAULT NULL, detail_location IN VARCHAR2 DEFAULT NULL, suppl_detail IN VARCHAR2 DEFAULT NULL, con_uid IN NUMBER DEFAULT NULL, con_id IN NUMBER DEFAULT NULL, con_name IN VARCHAR2 DEFAULT NULL) RETURN adr_msg_template_t;

TBD

SET_TRACEFILE_IDENTIFIER

An API for performing the action ALTER SESSION SET TRACEFILE_IDENTIFIER = '<identifier_string>'; dbms_adr.set_tracefile_identifier(trc_identifier IN VARCHAR2);

exec dbms_adr.set_tracefile_identifier('test_plan1');

      

      PL/SQL procedure successfully completed.

WRITE_LOG

Writes log entries to the alert log in the RDBMS ADR home dbms_adr.write_log( msg_id IN VARCHAR2, msg_type IN INTEGER, msg_level IN INTEGER, msg_text IN VARCHAR2, timestamp_originating IN TIMESTAMP WITH TIME ZONE DEFAULT NULL, timestamp_normalized IN TIMESTAMP WITH TIME ZONE DEFAULT NULL, org_id IN VARCHAR2 DEFAULT NULL, component_id IN VARCHAR2 DEFAULT NULL, instance_id IN VARCHAR2 DEFAULT NULL, hosting_client_id IN VARCHAR2 DEFAULT NULL, msg_group IN VARCHAR2 DEFAULT NULL, host_id IN VARCHAR2 DEFAULT NULL, host_nwaddr IN VARCHAR2 DEFAULT NULL, module_id IN VARCHAR2 DEFAULT NULL, process_id IN VARCHAR2 DEFAULT NULL, thread_id IN VARCHAR2 DEFAULT NULL, user_id IN VARCHAR2 DEFAULT NULL, suppl_attrs IN adr_log_msg_suppl_attrs_t DEFAULT NULL, problem_key IN VARCHAR2 DEFAULT NULL, upstream_comp_id IN VARCHAR2 DEFAULT NULL, downstream_comp_id IN VARCHAR2 DEFAULT NULL, ecid IN adr_log_msg_ecid_t DEFAULT NULL, error_instance_id IN adr_log_msg_errid_t DEFAULT NULL, msg_args IN adr_log_msg_args_t DEFAULT NULL, detail_location IN VARCHAR2 DEFAULT NULL, suppl_detail IN VARCHAR2 DEFAULT NULL, msg_template_obj IN adr_msg_template_t DEFAULT NULL, con_uid IN NUMBER DEFAULT NULL, con_id IN NUMBER DEFAULT NULL, con_name IN VARCHAR2 DEFAULT NULL);

exec dbms_adr.write_log(42, dbms_adr.log_msg_type_notification, 1, 'Test Message');

      

      PL/SQL procedure successfully completed.

      
      

      -- go to the directory returned by GET_LOG_LOCATION function and open log.xml

      -- in an appropriate editor to view the message.

WRITE_TRACE

Write trace lines to the trace file in the RDBMS ADR home dbms_adr.write_trace(trace_data IN VARCHAR2);

SELECT dbms_adr.get_trace_location;

      

      GET_TRACE_LOCATION

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

      /u01/app/oracle/diag/rdbms/asra23ai_grd_iad/ASRA23ai/trace

      

      exec dbms_adr.write_trace('Testing the new database version 26ai');

      

      PL/SQL procedure successfully completed.

      

      -- go to the directory returned by the GET_TRACE_LOCATION function

      -- and open the most recent .trc file.