43 - [Oracle 19C] Enable DDL logging

 

 

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT


SQL> alter session set container=orclpdb;

Session altered.


SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 3 ORCLPDB			  READ WRITE NO


SQL> show con_name

CON_NAME
------------------------------
ORCLPDB

 

 

 

SQL> show parameter ddl;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout		     integer	 0
enable_ddl_logging		     boolean	 FALSE


SQL> alter session set enable_ddl_logging=true;

Session altered.


SQL> show parameter ddl;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout		     integer	 0
enable_ddl_logging		     boolean	 TRUE

 

 

 

[oracle@test ddl]$ pwd
/u01/app/oracle/diag/rdbms/orcl/orcl/log/ddl
[oracle@test ddl]$ ll
합계 0
[oracle@test ddl]$ ls -al
합계 0
drwxr-x--- 2 oracle oinstall   6 11월 11 00:59 .
drwxr-x--- 8 oracle oinstall 103 11월 11 00:59 ..
[oracle@test ddl]$

...

SQL> create table test (n number);
Table created.
SQL>

...

[oracle@test ddl]$ ls -al
합계 4
drwxr-x--- 2 oracle oinstall  21 11월 24 11:35 .
drwxr-x--- 8 oracle oinstall 123 11월 24 11:35 ..
-rw-r----- 1 oracle oinstall 329 11월 24 11:35 log.xml
[oracle@test ddl]$ cat log.xml
<msg time='2023-11-24T11:35:44.957+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='kpdbLogDDL:24048:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='test.com' host_addr='192.168.2.225'
 pid='46518' version='2' con_uid='2854247726'
 con_id='3' con_name='ORCLPDB'>
 <txt>create table test (n number)
 </txt>
</msg>
[oracle@test ddl]$

 

 

 

SQL> create table test (n number);
Table created.

SQL> create table test1 (n number);
Table created.

SQL> drop table test;
Table dropped.

SQL> drop table test1;
Table dropped.
SQL>

...

 

 

 

• DDL 변경 정보를 찾는 2가지 방법 - ddl_orcl.log & ./ddl/log.xml

[oracle@test log]$ pwd
/u01/app/oracle/diag/rdbms/orcl/orcl/log


[oracle@test log]$ ll
합계 12
drwxr-x--- 2 oracle oinstall    6 11월 11 00:59 attention
-rw-r----- 1 oracle oinstall 6342 11월 24 11:20 attention.log
drwxr-x--- 2 oracle oinstall   21 11월 24 11:35 ddl
-rw-r----- 1 oracle oinstall  260 11월 24 11:38 ddl_orcl.log
drwxr-x--- 2 oracle oinstall    6 11월 11 00:59 debug
drwxr-x--- 2 oracle oinstall    6 11월 11 00:59 hcs
drwxr-x--- 2 oracle oinstall    6 11월 11 00:59 imdb
drwxr-x--- 2 oracle oinstall    6 11월 11 00:59 test


[oracle@test log]$ cat ddl_orcl.log
2023-11-24T11:35:44.957676+00:00
diag_adl:create table test (n number)
2023-11-24T11:37:43.264037+00:00
diag_adl:create table test1 (n number)
2023-11-24T11:37:54.879449+00:00
diag_adl:drop table test
2023-11-24T11:38:00.515070+00:00
diag_adl:drop table test1
[oracle@test log]$

 

 

 

[oracle@test ddl]$ pwd
/u01/app/oracle/diag/rdbms/orcl/orcl/log/ddl


[oracle@test ddl]$ ll
합계 4
-rw-r----- 1 oracle oinstall 1256 11월 24 11:38 log.xml


[oracle@test ddl]$ cat log.xml

<msg time='2023-11-24T11:35:44.957+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='kpdbLogDDL:24048:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='test.com' host_addr='192.168.2.225'
 pid='46518' version='2' con_uid='2854247726'
 con_id='3' con_name='ORCLPDB'>
 <txt>create table test (n number)
 </txt>
</msg>
<msg time='2023-11-24T11:37:43.263+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='kpdbLogDDL:24048:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='test.com' host_addr='192.168.2.225'
 pid='46518' con_uid='2854247726' con_id='3'
 con_name='ORCLPDB'>
 <txt>create table test1 (n number)
 </txt>
</msg>
<msg time='2023-11-24T11:37:54.879+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='kpdbLogDDL:24048:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='test.com' host_addr='192.168.2.225'
 pid='46518' con_uid='2854247726' con_id='3'
 con_name='ORCLPDB'>
 <txt>drop table test
 </txt>
</msg>
<msg time='2023-11-24T11:38:00.514+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='kpdbLogDDL:24048:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='test.com' host_addr='192.168.2.225'
 pid='46518' con_uid='2854247726' con_id='3'
 con_name='ORCLPDB'>
 <txt>drop table test1
 </txt>
</msg>
[oracle@test ddl]$