DBA Hub

📋Steps in this guide1/3

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

This article describes the enhancements to ADR in Oracle Database 12c.

oracle 12cconfigurationintermediate
by OracleDba
13 views
1

DDL Log

Setting the parameter to TRUE activates DDL logging. All DDL statements are logged into a plain text file and an XML file in the ADR location. 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. Navigating to the ADR home for the database and into the "log" directory, we can see the plain text DDL log. Moving down into the "ddl" directory, we can see the XML log. Interestingly, there does not appear to be any information in the logs to indicate which container the DDL came from.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/log/ddl_{SID}.log
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/log/ddl/log.xml

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;

$ 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
$

$ 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>
$
2

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. 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.

Code/Command (click line numbers to comment):

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

New ADRCI Command (SHOW LOG)

The command has been added to ADRCI to give visibility of the new DDL and debug logs. The available options are visible using the command. As the examples suggest, you can display the contents of the DDL log with the following command. If some were present, the contents of the debug log can be displayed with the following command. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
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>

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

adrci> show log -l debug

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

No diagnostic log in selected home
adrci>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!