Logiranje izmjene strukture baze – DDL LOGGER

Thursday, 11.04.2013 – mradovan

Modeliranje baze podataka često nije jednokratan posao, uvijek se nađe potreba da se naprave izmjene na dijelovima koda ili strukturi tablica, doda ili oduzme neka kolona u nekoj tablici, izmijeni tip podatka neke kolone, doda ili obriše neki ključ ili index…

Kada se takve izmjene rade nepredviđeno, a ne dokumentiraju se, ili u okolini gdje takve stvari radi više ljudi (namjerno ili slučajno), i kad se to radi ručno kroz komandnu liniju a bez modeliranja u nekom alatu za modeliranje (čiji bi se modeli spremali pod sistemom verzioniranja), mogu dovesti do vrlo nezgodnih situacija u kojima ne znamo tok događaja – tko je, kada i što napravio (i zašto ?).

Čak sam i sam bio u situacijama gdje neke promjene radim na razvoju, pa nakon nekog vremena ne znam šta sam napravio i kojim redoslijedom, ili zaključim da sam otišao pogrešnim putem i da se trebam vratiti nekoliko koraka unatrag, a ne znam točno kako…

Rješenje je vrlo jednostavno – database trigger koji logira sistemske događaje u tablicu DDLOG sa sljedećim kolonama:

  • OWNER (vlasnik objekta)
  • OPERATION (CREATE, ALTER ili DROP)
  • OBJECT_TYPE (procedure, table, trigger,package…)
  • OBJECT_NAME (ime objekta)
  • SQL_TEXT (prvih 1000 znakova sql izraza)
  • SQL_FULLTEXT (kompletni kod izraza)
  • CREATED (vrijeme upisa)
  • CREATED_BY (user koji je pokrenuo operaciju)
  • TERMINAL_NAME (ime računala s kojeg je operacija pokrenuta)
  • TERMINAL_IP (IP adresa računala s kojeg je operacija pokrenuta)
  • SESSIONID (ID sesije u kojoj je operacija izvršena).

Iz drugih korisničkih shema se može raditi select na ovu tablicu uz posjedovanje SELECT ANY TABLE privilegije.

Druga tablica ERRLOG služi za logiranje eventualnih grešaka u samom triggeru.

Priložena je skripta koju je potrebno pokrenuti pod SYS userom, čime se kreira user DDLOGGER, sa tablicom DDLOG:

/********************************************************************************************************************************************
Desc: User for logging changes on database objects: CREATE, ALTER and DROP operations.
Author: Mihael Radovan
Last change: 28.03.2013
********************************************************************************************************************************************/
/*  user with privileges */
CREATE user ddlogger IDENTIFIED BY DDL;
GRANT UNLIMITED TABLESPACE TO ddlogger;

/* error log table */
CREATE TABLE ddlogger.errlog
(tstamp TIMESTAMP DEFAULT systimestamp,
errcode number,
errtext varchar2(500))
NOLOGGING, COMPRESS FOR oltp, NOCACHE, NOMONITORING;

/* err log procedure */

CREATE OR REPLACE PROCEDURE ddlogger.plog(errcode number, errtext varchar2)
IS
  PRAGMA autonomous_transaction;
BEGIN
  INSERT /*+ append */ INTO ddlogger.errlog(errcode, errtext) 
  VALUES (errcode, errtext);
  
  COMMIT;

END plog;
/
/* ddl log table */

CREATE TABLE ddlogger.ddlog (
owner   VARCHAR2(30),
operation   VARCHAR2(30),
object_type VARCHAR2(30),
object_name VARCHAR2(30),
sql_text    VARCHAR2(1000),
sql_fulltext clob,
created TIMESTAMP,
created_by  VARCHAR2(30),
terminal_name VARCHAR2(255),
terminal_ip VARCHAR2(30),
sessionid number)
NOLOGGING, COMPRESS FOR oltp, NOCACHE, NOMONITORING;

/* database trigger */

CREATE OR REPLACE TRIGGER ddlogger.DDL_TRIGGER BEFORE DDL ON DATABASE
DECLARE
  oper varchar2(30);
  l_sql_text ora_name_list_t;
  l_count NUMBER;
  l_sql varchar2(1000);
  errcode number;
  errtext varchar2(500);
BEGIN
  SELECT ora_sysevent
    INTO oper
   FROM DUAL;

   l_count := ora_sql_txt(l_sql_text);

  FOR i IN 1..l_count

  LOOP
      l_sql := l_sql||l_sql_text(i);
  END LOOP;

  IF (oper IN ('CREATE', 'ALTER', 'DROP')) AND (ora_dict_obj_name != ' ddlog') THEN NULL;

   INSERT /*+ append */ INTO ddlogger.ddlog (owner, operation, object_type, object_name, sql_text, sql_fulltext, created, created_by, terminal_name, terminal_ip, sessionid)
   VALUES (ora_dict_obj_owner, oper, ora_dict_obj_type, ora_dict_obj_name, substr(l_sql, 1, 1000), l_sql, systimestamp, user, SYS_CONTEXT ('userenv', 'host'),  SYS_CONTEXT ('userenv', 'ip_address'), SYS_CONTEXT ('userenv', 'sessionid'));
  END IF;

EXCEPTION
  WHEN others THEN
     errcode:= SQLCODE;
     errtext:= SQLERRM;
     plog(errcode, errtext);
END;
/
  1. 3 Responses to “Logiranje izmjene strukture baze – DDL LOGGER”

  2. Veoma korisno! Ja bih samo da napomenem, da je praćenje/logovanje DDL izmjena veoma poželjno, čak staviše MUST!! jer se u praksi kad tad desi situacija, da nakon promjene nešto ne funkcioniše, pa se onda ne može otkriti “izvor” te greške, odnosno ko je izvršio taj DDL.

    Usput, poodavno sam napisao sličan članak na ovu temu: “Sačuvajte svoj kôd” -> http://www.baze-podataka.net/2007/04/02/sacuvajte-svoj-kod-backup-your-source-code/
    pa će možda nekom koristiti…

    By Dejan on Apr 11, 2013

  3. Upravo sam pročitao i tvoj uradak, da, bilo je to dosta davno, nisam to bio vidio… Ali korisno je u svakom slučaju…

    By mradovan on Apr 12, 2013

  4. Da dodam još par korisnih linkova:
    – ENABLE_DDL_LOGGING: http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams084.htm#REFRN10302
    – System-Defined Event Attributes: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#i1006199

    By Dejan on Apr 13, 2013

Post a Comment