DDL log logs certain DDL statements when enabled through a system parameter. A management pack licence is required to use this feature.
Debug log has entries for certain warnings and state information that used to be in the alert log.
9.1.3.4 DDL Log says:
The data definition language (DDL) log is a file that has the same format and basic behavior as the alert log, but it only contains the DDL statements issued by the database.
The DDL log is created only for the RDBMS component and only if the ENABLE_DDL_LOGGING initialization parameter is set to TRUE. When this parameter is set to FALSE, DDL statements are not included in any log.
The DDL log contains one log record for each DDL statement issued by the database. The DDL log is included in IPS incident packages.
There are two DDL logs that contain the same information. One is an XML file, and the other is a text file. The DDL log is stored in the log/ddl subdirectory of the ADR home.
This is not strictly true—only certain DDL statements are logged. See below for details.
Oracle Database Lifecycle Management Pack for Oracle Database says that ENABLE_DDL_LOGGING is a licensed parameter:
The init.ora parameter ENABLE_DDL_LOGGING is licensed as part of the Database Lifecycle Management Pack when set to TRUE. When set to TRUE, the database reports schema changes in real time into the database alert log under the message group schema_ddl. The default setting is FALSE.
Emphasis Mine
Based on 1.90 ENABLE_DDL_LOGGING, the following table summarises the DDL statements that are logged:
I presume that ALTER USER and CREATE USER are excluded because these commands can contain passwords.
Objects that are not included are:
CONTEXT
DATABASE LINK — commands involving this object contains plain-text passwords.
DIMENSION
DIRECTORY
EDITION
INDEXTYPE
JAVA
LIBRARY
MATERIALIZED VIEW
MATERIALIZED VIEW LOG
MATERIALIZED VIEW ZONEMAP
OPERATOR
ROLE
SCHEMA
A simple demonstration script is:
sqlplus / as sysdba <<DONE
REM ============================================================================
REM Connect to PLUM PDB
REM ============================================================================
ALTER SESSION SET CONTAINER=plum;
REM ============================================================================
REM Enable DDL Logging
REM ============================================================================
ALTER SYSTEM SET enable_ddl_logging=true SCOPE=MEMORY;
REM ============================================================================
REM Create OE Demo User
REM ============================================================================
CREATE USER oe_demo IDENTIFIED BY oe_demo;
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO oe_demo;
ALTER USER oe_demo QUOTA UNLIMITED ON users;
REM ============================================================================
REM Connect as OE Demo User
REM ============================================================================
CONNECT oe_demo/oe_demo@plum
CREATE SCHEMA AUTHORIZATION oe_demo
CREATE TABLE new_product
(color VARCHAR2(10) PRIMARY KEY, quantity NUMBER)
CREATE VIEW new_product_view
AS SELECT color, quantity FROM new_product WHERE color = 'RED';
CREATE TABLE new_product2
(color VARCHAR2(10) PRIMARY KEY, quantity NUMBER);
CREATE VIEW new_product_view2
AS SELECT color, quantity FROM new_product2 WHERE color = 'RED';
REM ============================================================================
REM Remove OE Demo User
REM ============================================================================
CONNECT / as sysdba
ALTER SESSION SET CONTAINER=plum;
DROP USER oe_demo CASCADE;
EXIT
DONE
adrci <<DONE
set home jar
show log -term
exit
DONE
The output from DDL log is:
ADR Home = /opt/app/oracle/diag/rdbms/jar/jar:
*************************************************************************
2018-04-13 20:11:46.483000 +10:00
CREATE TABLE new_product2
(color VARCHAR2(10) PRIMARY KEY, quantity NUMBER)
CREATE VIEW new_product_view2
AS SELECT color, quantity FROM new_product2 WHERE color = 'RED'
DROP USER oe_demo CASCADE
As expected, only the CREATE TABLE and CREATE VIEW statements outside of the CREATE SCHEMA were logged.
The CREATE USER and ALTER USER statements were not logged, but the DROP USER statement was logged.
The SHOW LOG command displays the contents of the DDL log through ADRCI.
9.1.3.5 Debug Log says:
An Oracle Database component can detect conditions, states, or events that are unusual, but which do not inhibit correct operation of the detecting component. The component can issue a warning about these conditions, states, or events. The debug log is a file that records these warnings.
These warnings recorded in the debug log are not serious enough to warrant an incident or a write to the alert log. They do warrant a record in a log file because they might be needed to diagnose a future problem.
The debug log has the same format and basic behavior as the alert log, but it only contains information about possible problems that might need to be corrected.
The debug log reduces the amount of information in the alert log and trace files. It also improves the visibility of debug information.
The debug log is included in IPS incident packages. The debug log's contents are intended for Oracle Support. Database administrators should not use the debug log directly.
DDL Log
Debug Log
log
ddl
log.xml
ddl_jar.log
debug
?
imdb
?
test
?
The new command is SHOW LOG. Sample session is:
adrci> set home jar
adrci> show log -l ddl -term
ADR Home = /opt/app/oracle/diag/rdbms/jar/jar:
*************************************************************************
2018-04-13 20:11:46.483000 +10:00
CREATE TABLE new_product2
(color VARCHAR2(10) PRIMARY KEY, quantity NUMBER)
CREATE VIEW new_product_view2
AS SELECT color, quantity FROM new_product2 WHERE color = 'RED'
DROP USER oe_demo CASCADE
2018-04-13 21:00:14.523000 +10:00
truncate table wri$_adv_addm_pdbs
adrci> show log -l debug -term
ADR Home = /opt/app/oracle/diag/rdbms/jar/jar:
*************************************************************************