Thursday, April 23, 2020

Automatic Diagnostics Repository (ADR) Enhancements in Oracle Database 12c (ADRCI)

Oracle Database 12c, Oracle Database Tutorial and Material, Oracle Database Prep, Oracle DB Exam

The basic concepts of the Automatic Diagnostics Repository (ADR) remain unchanged in Oracle Database 12c, but there have been several notable changes to the way the diagnostics information is handled.

◉ DDL Log


Oracle Database 12c, Oracle Database Tutorial and Material, Oracle Database Prep, Oracle DB Exam
Setting the ENABLE_DDL_LOGGING parameter to TRUE activates DDL logging. All DDL statements are logged into a plain text file and an XML file in the ADR location.

$ADR_BASE/diag/rdbms/{DB-name}/{SID}/log/ddl_{SID}.log
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/log/ddl/log.xml

The parameter is container-specific, so you will need to enable/disable it independently in the root container and any PDBs.

The following example shows how the parameter works and what output you can expect.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;
ALTER SYSTEM SET ENABLE_DDL_LOGGING=TRUE;

CONN test/test@pdb1

CREATE TABLE test1 (id NUMBER);
ALTER TABLE test1 ADD CONSTRAINT test1_pk PRIMARY KEY (id);
DROP TABLE test1 PURGE;

Navigating to the ADR home for the database and into the "log" directory, we can see the plain text DDL log.

$ cd /u01/app/oracle/diag/rdbms/cdb1/cdb1/log
$ ls
ddl  ddl_cdb1.log  debug  test
$ cat ddl_cdb1.log
Wed Jun 25 10:11:11 2014
diag_adl:CREATE TABLE test1 (id NUMBER)
diag_adl:ALTER TABLE test1 ADD CONSTRAINT test1_pk PRIMARY KEY (id)
diag_adl:DROP TABLE test1 PURGE
$

Moving down into the "ddl" directory, we can see the XML log.

$ cd ddl
$ ls
log.xml
$ cat log.xml
<msg time='2014-06-25T10:11:11.578+01:00' org_id='oracle' comp_id='rdbms'
 msg_id='kpdbLogDDL:15115:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='ol6-121.localdomain' host_addr='192.168.56.2'
 version='1'>
 <txt>CREATE TABLE test1 (id NUMBER)
 </txt>
</msg>
<msg time='2014-06-25T10:11:11.596+01:00' org_id='oracle' comp_id='rdbms'
 msg_id='kpdbLogDDL:15115:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='ol6-121.localdomain' host_addr='192.168.56.2'>
 <txt>ALTER TABLE test1 ADD CONSTRAINT test1_pk PRIMARY KEY (id)
 </txt>
</msg>
<msg time='2014-06-25T10:11:11.628+01:00' org_id='oracle' comp_id='rdbms'
 msg_id='kpdbLogDDL:15115:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='ol6-121.localdomain' host_addr='192.168.56.2'>
 <txt>DROP TABLE test1 PURGE
 </txt>
</msg>
$

Interestingly, there does not appear to be any information in the logs to indicate which container the DDL came from.

◉ Debug Log


Oracle Database 12c has thinned out some of the contents of the alert log and trace files. Some activities that may be considered unusual, but do not directly cause problems with the database are no longer written to the alert log and/or trace files. Instead, they are written to the debug log, typically located as follows.

$ADR_BASE/diag/rdbms/{DB-name}/{SID}/log/debug

As a DBA, you need to remember to keep an eye on the contents of the debug log in addition to the alert log. The debug log is included in any packages created to send to Oracle Support.

◉ New ADRCI Command (SHOW LOG)


The SHOW LOG command has been added to ADRCI to give visibility of the new DDL and debug logs. The available options are visible using the HELP SHOW LOG command.

adrci> help show log

  Usage: SHOW LOG [-l <log name>] [-p <predicate_string>]  [-term]
                    [ [-tail [num] [-f]] ]
  Purpose: Show diagnostic log messages.

  Options:
    [-p <predicate_string>]: The predicate string must be double-quoted.
    The fields in the predicate are the fields:
        ORIGINATING_TIMESTAMP         timestamp
        NORMALIZED_TIMESTAMP          timestamp
        ORGANIZATION_ID               text(65)
        COMPONENT_ID                  text(65)
        HOST_ID                       text(65)
        HOST_ADDRESS                  text(17)
        MESSAGE_TYPE                  number
        MESSAGE_LEVEL                 number
        MESSAGE_ID                    text(65)
        MESSAGE_GROUP                 text(65)
        CLIENT_ID                     text(65)
        MODULE_ID                     text(65)
        PROCESS_ID                    text(33)
        THREAD_ID                     text(65)
        USER_ID                       text(65)
        INSTANCE_ID                   text(65)
        DETAILED_LOCATION             text(161)
        UPSTREAM_COMP_ID              text(101)
        DOWNSTREAM_COMP_ID            text(101)
        EXECUTION_CONTEXT_ID          text(101)
        EXECUTION_CONTEXT_SEQUENCE    number
        ERROR_INSTANCE_ID             number
        ERROR_INSTANCE_SEQUENCE       number
        MESSAGE_TEXT                  text(2049)
        MESSAGE_ARGUMENTS             text(129)
        SUPPLEMENTAL_ATTRIBUTES       text(129)
        SUPPLEMENTAL_DETAILS          text(4000)
        PROBLEM_KEY                   text(65)

    [-l <log_name>]: Output specified log, otherwise all messages from
    all diagnostic logs under the current ADR Home are displayed.

    [-tail [num] [-f]]: Output last part of the log messages and
    output latest messages as the log grows. If num is not specified,
    the last 10 messages are displayed. If "-f" is specified, new data
    will append at the end as new log messages are generated.
    This option requires that a log name be supplied via -l option.

    [-term]: Direct results to terminal. If this option is not specified,
    the results will be open in an editor.
    By default, it will open in emacs, but "set editor" can be used
    to set other editors.

  Examples:
    show log -l debug
    show log -p "message_text like '%tablespace%'"
    show log -l ddl -tail 20

adrci>

As the examples suggest, you can display the contents of the DDL log with the following command.

adrci> show log -l ddl

ADR Home = /u01/app/oracle/diag/rdbms/cdb1/cdb1:
*************************************************************************
Output the results to file: /tmp/utsout_8165_140616_1.ado
adrci>

2014-06-25 10:11:11.578000 +01:00
CREATE TABLE test1 (id NUMBER)
ALTER TABLE test1 ADD CONSTRAINT test1_pk PRIMARY KEY (id)
DROP TABLE test1 PURGE

If some were present, the contents of the debug log can be displayed with the following command.

adrci> show log -l debug

ADR Home = /u01/app/oracle/diag/rdbms/cdb1/cdb1:
*************************************************************************

No diagnostic log in selected home
adrci>

Related Posts

0 comments:

Post a Comment