關於Oracle DDL日誌的記錄：

[oracle@DBA12C02 ddl]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 15 10:19:03 2014

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

1、在CDB層進行測試：

SQL> show parameter ddl

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

ddl_lock_timeout integer 0

enable_ddl_logging boolean FALSE

SQL> host pwd

/oracle/app/db/diag/rdbms/cdb/CDB2/log/ddl

SQL> host ls

SQL> alter session set enable_ddl_logging=true;

Session altered.

SQL> create table guijian (id number,name varchar2(20));

Table created.

SQL> host ls

log.xml

SQL> host cat log.xml

msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'

level='16' host_id='DBA12C02' host_addr='10.0.57.15'

version='1'>

create table guijian (id number,name varchar2(20))

SQL> insert into guijian values (1,'guijian');

1 row created.

SQL> commit;

Commit complete.

SQL> host cat log.xml

msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'

level='16' host_id='DBA12C02' host_addr='10.0.57.15'

version='1'>

create table guijian (id number,name varchar2(20))

可以看到，在CDB層我們開啟DDL控制日之後，在系統中有個log.xml的日誌，該日誌中僅僅日錄了DDL操作的資訊。對DML操作日誌沒有任何的記錄。

2、在PDB層進行測試：

SQL> host rm -rf log.xml

SQL> host ls

SQL> alter system set enable_ddl_logging=false;

System altered.

SQL> alter session set container=pdba;

Session altered.

SQL> show parameter ddl

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

ddl_lock_timeout integer 0

enable_ddl_logging boolean FALSE

SQL> alter system set enable_ddl_logging=true;

System altered.

SQL> show parameter ddl

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

ddl_lock_timeout integer 0

enable_ddl_logging boolean TRUE

SQL> show con_name

CON_NAME

------------------------------

PDBA

SQL> host ls

----------在預設下開啟引數設定，並不會立即出現日誌。

SQL> create table guijian_ddl (id number,name varchar2(20));

Table created.

SQL> host ls

log.xml

SQL> host cat log.xml

msg_id='kpdbLogDDL:18370:2946163730' type='UNKNOWN' group='diag_adl'

level='16' host_id='DBA12C02' host_addr='10.0.57.15'

version='1'>

create table guijian_ddl (id number,name varchar2(20))

SQL> insert into guijian_ddl values (1,'guijian_ddl');

1 row created.

SQL> commit;

Commit complete.

SQL> host cat log.xml

msg_id='kpdbLogDDL:18370:2946163730' type='UNKNOWN' group='diag_adl'

level='16' host_id='DBA12C02' host_addr='10.0.57.15'

version='1'>

create table guijian_ddl (id number,name varchar2(20))

同樣在PDB下開啟引數設定後，DDL的log日誌同樣出現在了相應的位置。

3、在PDB下設定引數，檢驗CDB的DDL操作影響：

SQL> show parameter ddl

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

ddl_lock_timeout integer 0

enable_ddl_logging boolean TRUE

SQL> show con_name

CON_NAME

------------------------------

PDBA

SQL> conn /as sysdba

Connected.

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> show parameter ddl

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

ddl_lock_timeout integer 0

enable_ddl_logging boolean FALSE

SQL> create table ddl_guijian as select * from guijian where 1=2;

Table created.

SQL> host ls

log.xml

SQL> host cat log.xml

msg_id='kpdbLogDDL:18370:2946163730' type='UNKNOWN' group='diag_adl'

level='16' host_id='DBA12C02' host_addr='10.0.57.15'

version='1'>

create table guijian_ddl (id number,name varchar2(20))

從上面的測試可以看出，在PDB下設定DDL日誌引數並不會影響CDB下的引數設定，這也很好理解。PDB屬於CDB嘛。那麼單個PDB的設定會不會影響所有的PDB呢，我們根據相關理論判斷是不會影響其他PDB的，我們可做相關測試如下：

SQL> select name,open_mode from v$pdbs;

NAME OPEN_MODE

------------------------------ ----------

PDB$SEED READ ONLY

PDBA READ WRITE

PDBB READ WRITE

SQL> show parameter ddl

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

ddl_lock_timeout integer 0

enable_ddl_logging boolean FALSE

SQL> alter session set container=pdba;

Session altered.

SQL> show parameter ddl

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

ddl_lock_timeout integer 0

enable_ddl_logging boolean TRUE

SQL> alter session set container=pdbb;

Session altered.

SQL> show parameter ddl

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

ddl_lock_timeout integer 0

enable_ddl_logging boolean FALSE

從引數上判斷是ok的，不會有影響。

SQL> create table guijian_ddl_pdbb (id number,name varchar2(20));

Table created.

SQL> host cat log.xml

msg_id='kpdbLogDDL:18370:2946163730' type='UNKNOWN' group='diag_adl'

level='16' host_id='DBA12C02' host_addr='10.0.57.15'

version='1'>

create table guijian_ddl (id number,name varchar2(20))

從上面日誌結果可以看到，沒有任何的影響。

4、CDB下的引數設定是否影響全部PDB:

SQL> alter session set container=pdba;

Session altered.

SQL> show parameter ddl

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

ddl_lock_timeout integer 0

enable_ddl_logging boolean TRUE

SQL> alter session set enable_ddl_logging=false;

Session altered.

SQL> alter session set container=pdbb;

Session altered.

SQL> show parameter ddl

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

ddl_lock_timeout integer 0

enable_ddl_logging boolean FALSE

SQL> conn / as sysdba

Connected.

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> show parameter ddl

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

ddl_lock_timeout integer 0

enable_ddl_logging boolean FALSE

SQL> alter system set enable_ddl_logging=true;

System altered.

SQL> alter session set container=pdba;

Session altered.

SQL> show parameter ddl

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

ddl_lock_timeout integer 0

enable_ddl_logging boolean TRUE

SQL> alter session set container=pdbb;

Session altered.

SQL> show parameter ddl

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

ddl_lock_timeout integer 0

enable_ddl_logging boolean TRUE

從上面的測試結果看，的確CDB的引數設定屬於全域性性的，當然也可分別制定在哪個PDB中生效，若未指定，則表示在全部的PDB中生效。

關於Oracle12C rac DDL日誌的測試驗證

相關文章