DDL operations present real danger for any database. Catastrophic commands like "drop trigger" or "drop table" are noticed immediately if they succeed to complete. But some other operations like altering/adding/dropping indexes or altering tables, are not easy to determine immediately after they happened. The worse of all is that it's circumstances are not obvious until they comes into play like downgrade database performance so much that the whole system suffer.
Because DDL are usually performed through scripts where many commands are executed sequentially, if you do not have straight error level checking through whenever sqlerror sqlplus directive, some errors that happened might not be catch and stays hidden with poor chance to notice them.
If beside DBA, there are many other users that has rights to perform any DDL operation, it is more then welcome to have history of such an activity. This is the best way to understand quickly what went wrong.
For such a cases DDL monitoring is right approach.
The solution
The solution is to implement DDL monitoring tool that will record all successful DDL operations in the database. This done through several parts:- AUDIT_DDL_LOG table which will hold DDL information that were processed
- AUDIT_DDL_LOG_ARCH table that will hold archive DDL information
- Database trigger which will fill AUDIT_DDL_LOG table
- Procedure ARCHIVE_AUDIT_DDL_LOG which will move records from AUDIT_DDL_LOG to AUDIT_DDL_LOG_ARCH table
- Database job which will fire procedure ARCHIVE_AUDIT_DDL_LOG
AUDIT_DDL_LOG and AUDIT_DDL_LOG_ARCH tables
Table structure is:CREATE SEQUENCE AUDIT_DDL_LOG_SEQ START WITH 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 CYCLE CACHE 100 ; CREATE TABLE AUDIT_DDL_LOG ( DDL_DATE DATE, USER_NAME VARCHAR2(30 BYTE), SESSION_ID NUMBER(8), SID NUMBER(8), DDL_TYPE VARCHAR2(30 BYTE), OBJECT_TYPE VARCHAR2(18 BYTE), OWNER VARCHAR2(30 BYTE), OBJECT_NAME VARCHAR2(128 BYTE), ID INTEGER ) TABLESPACE TOOLS PCTUSED 0 PCTFREE 0 ; CREATE UNIQUE INDEX AUDIT_DDL_LOG_UQX ON AUDIT_DDL_LOG (ID) LOGGING TABLESPACE TOOLS PCTFREE 0 INITRANS 2 MAXTRANS 255 ; ALTER TABLE AUDIT_DDL_LOG ADD ( CONSTRAINT AUDIT_DDL_LOG_PK PRIMARY KEY (ID) USING INDEX AUDIT_DDL_LOG_UQX) ; -- GRANT SELECT ON AUDIT_DDL_LOG TO POWER_USER;With granting select to POWER_USER role (now is commented), DBA allow that any user that own that role might look in the table for content that interest him/her. Because POWER_ROLE has user that belong to developers, DBA or any non "end user" group, he/she can alone check the result of scripts that was run under his session.
Because AUDIT_DDL_LOG is supposed to be table that holds current data, in a case of to many DDL's it might suffer of size problem.
This is why on regular time base I move old records to AUDIT_DDL_LOG_ARCH table.
CREATE TABLE AUDIT_DDL_LOG_ARCH( DDL_DATE DATE, USER_NAME VARCHAR2(30 BYTE), SESSION_ID NUMBER(8), SID NUMBER(8), DDL_TYPE VARCHAR2(30 BYTE), OBJECT_TYPE VARCHAR2(18 BYTE), OWNER VARCHAR2(30 BYTE), OBJECT_NAME VARCHAR2(128 BYTE), ID INTEGER ) TABLESPACE TOOLS PCTUSED 0 PCTFREE 0 COMPRESS ;Frequency of deleting records from AUDIT_DDL_LOG_ARCH (too old DDL are really not important) is totally customer based policy and have to be defined as it needs.
Database trigger
Because this trigger is fired when DDL statement completes (AFTER DDL), it's primary task is to fill AUDIT_DDL_LOG table with information about execution. If DDL statement fail, trigger will not be fired.This trigger is intentionally placed in tools schema (not in sys!) because when you export/import database, sys objects are gone. However this always withdrawn additional security settings for owner.
CREATE OR REPLACE TRIGGER TOOLS.audit_ddl_trigger
/* $Header: audit_ddl_trigger.tgb 1.00 10/01/2007 14:54 damirv $ */
/*--------------------------------------------------------------------------------------------------------------------
NAME : audit_ddl_trigger
PURPOSE : Log any DDL statement in "audit_ddl_log" table
Date : 01.10.2007.
Author : Damir Vadas, damir.vadas@gmail.com
Remarks :
Changes (DD.MM.YYYY, Name, CR/TR#):
-------------------------------------------------------------------------------------------------------------------- */
AFTER DDL
ON DATABASE
DECLARE
s_sess audit_ddl_log.session_id%TYPE;
s_sid audit_ddl_log.sid%TYPE;
s_seq INTEGER;
BEGIN
IF (ora_dict_obj_name != 'ALRT_PATH1' AND
ora_dict_obj_name NOT LIKE 'EUL4%' AND
ora_dict_obj_name NOT LIKE 'GL_INTERFACE%' AND
ora_dict_obj_name NOT LIKE 'GL_POSTING%' AND
ora_dict_obj_name NOT LIKE 'QUEST%' AND
ora_dict_obj_name NOT LIKE 'SYS_TEMP%' AND
ora_dict_obj_name NOT LIKE 'WF_LOCAL%' AND
ora_dict_obj_name NOT LIKE 'WF_UR_%' AND
ora_dict_obj_name NOT LIKE 'ORA_TEMP%'
) THEN
s_sess := sys_context('USERENV','SESSIONID');
select sid into s_sid from v$session where audsid = s_sess;
select AUDIT_DDL_LOG_SEQ.nextval into s_seq from dual;
insert into audit_ddl_log (
ddl_date,
user_name,
session_id,
sid,
ddl_type,
object_type,
owner,
object_name,
id
)
VALUES (
sysdate,
ora_login_user,
s_sess,
s_sid,
ora_sysevent,
ora_dict_obj_type,
ora_dict_obj_owner,
ora_dict_obj_name,
s_seq
);
END IF;
EXCEPTION
WHEN others THEN
null;
END;
/
In this database trigger I have excluded several objects that are used in Oracle eBS 11.5.10.x environment (temp objects) which logging was not important to me. Procedure ARCHIVE_AUDIT_DDL_LOG
In mine case I allays wanted to have at least 14 days in main AUDIT_DDL_LOG table. With p_retention parameter you can adjust this. Keep in mind that for p_retention=0 all data will be moved to AUDIT_DDL_LOG_ARCH table.CREATE OR REPLACE PROCEDURE archive_audit_ddl_log (p_retention in number default 14) IS
/*----------------------------------------------------------------------------------
NAME : archive_audit_ddl_log.prc
PURPOSE : Move records from audit_ddl_log to audit_ddl_log_arch table
Date : 01.10.2007.
Author : Damir Vadas, damir.vadas@gmail.com
Remarks : p_retention define how much data stays in audit_ddl_log table
Changes (DD.MM.YYYY, Name, CR/TR#):
-----------------------------------------------------------------------------------*/
DECLARE
l_date date;
BEGIN
l_date := sysdate-p_retention;
insert into audit_ddl_log_arch (select * from AUDIT_DDL_LOG where DDL_DATE < l_date);
delete from audit_ddl_log where DDL_DATE < l_date;
commit;
EXCEPTION
WHEN OTHERS THEN
rollback;
END archive_audit_ddl_log;
/Database job
The best way is to place archive_audit_ddl_log procedure in job which fires once a day. In this case job is fired every day at 1:01 AM.DECLARE X NUMBER; BEGIN SYS.DBMS_JOB.SUBMIT ( job => X ,what => 'archive_audit_ddl_log;'
,next_date => to_date('02.10.2007 00:00:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'TRUNC(SYSDATE+1+1/1440)'
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/
Owner of this job should not be in any circumstance sys. There is no reason for that. In mine case I use for many tasks like that TOOLS schema. The result
Here is part of AUDIT_DDL_LOG table content what is result of our trigger:
The end
As you see, this monitoring tool is pretty straight forward for implementation as well for use. From mine experience, something like that is worth to be implemented on any production database.If ypu want to monitor unsuccessful DDL statements (those which were not completed because of any kind of error), then one way to achieve is to do this:
- Add another table whose records will be filled with similar trigger which fire "BEFORE DDL"
- Difference (SQL "MINUS") between those two tables would show unsuccessful DDL operations
