DDL log 별도 저장

컨셉 매뉴얼을 잠깐 참조하다가 DDL Log가 있어 확인해본 내용 정리

 

예전에 audit 말고 ddl을 남기는 방법을 찾다가

 

ALERT LOG에 DDL 기록을 남기기 위해 enable_ddl_logging 파라미터를 확인해서 적용했던 적이 있는데

결국엔 다시 껐다.

 

이유는 당시 적용한 db는 주기적인 datapump 작업이 자주 일어나고 있었는데,

datapump 작업시의 임시 테이블이나 뷰 생성과 DROP에 기록도 남아 DB Alert log 를 더럽혔기 때문이다.

 

 

12c부터(R1인지 R2인지 정확히 모름) DDL 로그가 전용으로 생겼다

 

위치1 text file

    $ORACLE_BASE/diag/rdbms/<dbname>/<sid>/log/ddl_<sid>.log

위치2 xml file

    $ORACLE_BASE/diag/rdbms/<dbname>/<sid>/log/ddl/log.xml

 

설정:

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.

 

expdp를 수행해 본다

[oracle@test ~]$ expdp system schemas=scott
 
Export: Release 12.2.0.1.0 - Production on Wed Dec 19 21:09:45 2018
 
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
Password:
 
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** schemas=scott
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."T1"                                104.0 KB   10100 rows
. . exported "SCOTT"."EMP"                               8.781 KB      14 rows
. . exported "SCOTT"."DEPT"                              6.031 KB       4 rows
. . exported "SCOTT"."SALGRADE"                          5.960 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/admin/orcl12c/dpdump/expdat.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Dec 19 21:11:10 2018 elapsed 0 00:01:21

 

ddl 로그 파일을 확인해보니

[oracle@test log]$ cat ddl_orcl12c.log
2018-12-19T21:09:21.251992+09:00
diag_adl:CREATE TABLE "SYSTEM"."SYS_EXPORT_SCHEMA_01" --생략-- 
2018-12-19T21:09:22.504407+09:00
diag_adl:CREATE OR REPLACE FORCE VIEW "SYS"."AQ$_KUPC$DATAPUMP_QUETAB_1_F" sharing=none  AS SELECT --생략-- 
diag_adl:CREATE OR REPLACE FORCE VIEW "SYS"."AQ$_KUPC$DATAPUMP_QUETAB_1_F" sharing=none  AS SELECT --생략--
2018-12-19T21:09:26.354288+09:00
diag_adl:DROP TABLE "SYSTEM"."SYS_EXPORT_SCHEMA_01" PURGE
2018-12-19T21:09:48.012644+09:00
diag_adl:CREATE TABLE "SYSTEM"."SYS_EXPORT_SCHEMA_01"  --생략--
diag_adl:CREATE OR REPLACE FORCE VIEW "SYS"."AQ$_KUPC$DATAPUMP_QUETAB_1_F" sharing=none  AS SELECT --생략--  
diag_adl:CREATE OR REPLACE FORCE VIEW "SYS"."AQ$_KUPC$DATAPUMP_QUETAB_1_F" sharing=none  AS SELECT --생략--
2018-12-19T21:09:51.515314+09:00
diag_adl:ALTER TABLE "SYSTEM"."SYS_EXPORT_SCHEMA_01" ADD (UNIQUE (process_order, duplicate))
diag_adl:CREATE INDEX SYS_MTABLE_000005D25_IND_1 ON "SYSTEM"."SYS_EXPORT_SCHEMA_01" (object_schema, original_object_name, object_type)
diag_adl:CREATE INDEX SYS_MTABLE_000005D25_IND_2 ON "SYSTEM"."SYS_EXPORT_SCHEMA_01" (object_schema, object_name, object_type, partition_name, subpartition_name)
diag_adl:CREATE INDEX SYS_MTABLE_000005D25_IND_3 ON "SYSTEM"."SYS_EXPORT_SCHEMA_01" (base_process_order)
diag_adl:CREATE INDEX SYS_MTABLE_000005D25_IND_4 ON "SYSTEM"."SYS_EXPORT_SCHEMA_01" (original_object_schema, original_object_name, partition_name)
diag_adl:CREATE INDEX SYS_MTABLE_000005D25_IND_5 ON "SYSTEM"."SYS_EXPORT_SCHEMA_01" (seed)
2018-12-19T21:09:59.614785+09:00
diag_adl:truncate table "SYS"."IMPDP_STATS"
2018-12-19T21:10:03.507561+09:00
diag_adl:truncate table sys.spd_scratch_tab
2018-12-19T21:11:13.637296+09:00
diag_adl:DROP TABLE "SYSTEM"."SYS_EXPORT_SCHEMA_01" PURGE

 

이 내용들이 db alertlog에 남는다면 db alert log가 매우 지저분해질 수 있다.

 

일반 유저의 DDL은 당연히 남을 것이고

audit는 sys 유저는 별도 설정 안하면 안남는 이슈가 있는데, 과연 이 기능은 남을까?

 

SQL> show parameter audit_sys_op
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations                 boolean     FALSE  
 
SQL> show user
USER is "SYS"
SQL> create table t1 (c1 number);
 
Table created.

 

ddl 로그를 확인해본다.

[oracle@test log]$ cat ddl_orcl12c.log
2018-12-19T21:52:56.253252+09:00
diag_adl:create table t1 (c1 number)

sys에서 수행한 ddl도 남는다

 

ddl 전용 로그가 생겨서인지 db alert log에는 ddl 로그가 안남는걸 확인했다.

 

간편히 사용하고 싶다면 12c이상에선 이 방법도 고려해볼만 하겠다.

 

단, AUDIT과는 다르게 위 로그에서 알 수 있듯이 단순히 "언제","어떤 SQL" 정보만 남고 어떤 machine에서 어떤 유저가 수행했는지 나오지 않는다.

19-03 추가

11g에선 Oracle Change Management Pack 옵션 라이센스가 필요하고

12c부턴 Oracle Lifecycle Management Pack 옵션 라이센스가 필요한 파라미터이다

이 옵션은 사는 고객사를 본 적이 드물다. 파라미터 변경하지 말도록 하자.