Explain ADR Enhancements

Summary

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.

References

DDL Log

Description

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.

Licence Required

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

Not All DDL is Logged

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

Demonstration Script

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.

Debug Log

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.

New File types

    1. DDL Log

    2. Debug Log

New File locations

    • log

      • ddl

        • log.xml

      • ddl_jar.log

      • debug

        • ?

      • imdb

        • ?

      • test

        • ?

New command for ADRCI

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:

*************************************************************************