Veritabanındaki tüm DDL işlemlerinin izlenmesi

Veritabanındaki nesneler üzerinde yapılan yapısal değişiklikleri , veritabanı yada şema genelinde izlemek mümkündür. Bu sayede nesnelerdeki tüm yapısal değişiklikleri izleyip, bir sorun anında eski halini yeniden oluşturabiliriz.

İzleme , trigger’lar ile yapılmaktadır. İlgili trigger , bir ddl işlemi sonrasında , talep edilen bilgileri bir tabloya yazabilir. 

Aşağıdaki şekilde bir tablo ve trigger oluşturup, veritabanındaki tüm ddl işlemlerini takip edebiliriz.

Tablo oluşturulması : 

CREATE TABLE AUDIT_DDL
(
   D              DATE,
   OSUSER         VARCHAR2 (255),
   SESSION_USER   VARCHAR2 (255),
   HOST           VARCHAR2 (255),
   TERMINAL       VARCHAR2 (255),
   MODULE         VARCHAR2 (255),
   OWNER          VARCHAR2 (30),
   TYPE           VARCHAR2 (30),
   NAME           VARCHAR2 (30),
   SYSEVENT       VARCHAR2 (30),
   SQL_TEXT        VARCHAR2 (4000)
);

Trigger oluşturulması :

Aşağıdaki trigger tüm veritabanı seviyesindeki ddl işlemlerini takip edecektir. Eğer trigger’daki “on database” yerine “on schema” yazılırsa , sadece trigger’ı oluşturan kullanıcının ddl işlemlerini kayıt edecektir.

create or replace trigger audit_ddl_trg after ddl on database
declare
  sql_text ora_name_list_t;
  stmt VARCHAR2(4000) := '';
  n number;
begin
    n:=ora_sql_txt(sql_text);
    for i in 1..n
    loop
      stmt:=substr(stmt||sql_text(i),1,4000);
    end loop; 
    insert into audit_ddl(d, osuser,session_user,host,terminal,module,owner,type,name,sysevent,sql_txt)
    values(
      sysdate,
      sys_context('USERENV','OS_USER') ,
      sys_context('USERENV','SESSION_USER') ,
      sys_context('USERENV','HOST') , 
      sys_context('USERENV','TERMINAL') ,
      sys_context('USERENV','MODULE') ,
      ora_dict_obj_owner,
      ora_dict_obj_type,
      ora_dict_obj_name,
      ora_sysevent,
      stmt
    );
end;
/

Yukarıdaki işlemler ile 11g ve öncesindeki veritabanlarında ddl işlemlerinin izlenmesi mümkündür. 12c ile gelen yeni bir özellik ile bu işlemlere gerek olmadan da ddl işlemlerini izlemek mümkündür.

 12c ile birlikte ENABLE_DDL_LOGGING parametresi enable edilerek log ve xml dosyası olarak $ORACLE_BASE/diag/rdbms/DBNAME/SID/log/ddl dizini altında loglanabiliyor. Bu parametre 11g veritabanlarında da bulunmaktadır. 11g veritabanlarında true yapıldığında log.xml şeklindeki dosya yerine alert<SID>.log dosyasına sadece işlemin komutu yazılmaktadır. Detaylı bilgi bulunmamaktadır.

Aşağıdaki şekilde ENABLE_DDL_LOGGING  parametresinin değişimi yapılabilir.

SQL> show parameter ENABLE_DDL_LOGGING

NAME				     TYPE		VALUE
---------------------------------------------
enable_ddl_logging		 boolean	FALSE


SQL> alter system set enable_ddl_logging=TRUE scope=both sid='*';

System altered.

NAME				     TYPE		VALUE
---------------------------------------------
enable_ddl_logging		 boolean	TRUE

Parametre değişimi sonrasında ilk ddl işleminde $ORACLE_BASE/diag/rdbms/DBNAME/SID/log/ddl dizini altında log.xml şeklinde bir dosya oluşturulup bu dosyay tüm ddl işlemleri aşağıdaki gibi yazılmaktadır.

<msg time='2018-05-11T13:40:37.219+03:00' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:4695:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='db01.local' host_addr='10.10.1.120'
 pid='326761' version='1'>
 <txt>create table a(ad varchar(100))
 </txt>
</msg>
<msg time='2018-05-11T13:55:40.162+03:00' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:4695:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='db.local' host_addr='10.10.1.120'
 pid='393307'>
 <txt>alter table a add soyad varchar(100)
 </txt>
</msg>