关于Oracle12C rac DDL日志的测试验证

关于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

 

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

 

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中生效。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28612416/viewspace-1368129/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28612416/viewspace-1368129/