◉ DDL Log
$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>
Posts
0 comments:
Post a Comment