Scripts

Script 1. DDL Auditing – TRIGGER FOR DDL STATEMENT FIRED IN A DATABASE

–create audit table in SYS’s schema

CREATE TABLE AUDIT_DDL (d date,OSUSER varchar2(255),CURRENT_USER varchar2(255),HOST varchar2(255),TERMINAL varchar2(255),
owner varchar2(255),type varchar2(255),name varchar2(255),sysevent varchar2(255));

–create trigger through sys which will insert all details into AUDIT_DDL table if  DDL is fired in database

create or replace trigger audit_ddl_trg after ddl on database
begin
insert into audit_ddl(d, osuser,current_user,host,terminal,owner,type,name,sysevent)
values(sysdate,sys_context('USERENV','OS_USER'),sys_context('USERENV','CURRENT_USER'),sys_context('USERENV','HOST'),
sys_context('USERENV','TERMINAL'),ora_dict_obj_owner,ora_dict_obj_type,ora_dict_obj_name,ora_sysevent);
end;
/

Script 2. Mail on any ORA- errors with help of procedure. Whenever any ORA- errors occurs, mail will be sent to specified email id.

–create trigger where in ORA errors will be defined.

CREATE OR REPLACE TRIGGER TRIG_ALERTS
AFTER SERVERERROR ON DATABASE
declare x varchar2(50);
msgtext2 varchar2(50);

BEGIN
select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS') into x from dual;
msgtext2:='ORA Errors';

/* Add on error as you encounter in the alert log file */
IF (ORA_IS_SERVERERROR(1555)) THEN
 MAILFROMDBS ('ORA-1555 : SNAPSHOT TOO OLD at '||x||' ',msgtext2);
ELSIF (ORA_IS_SERVERERROR(600)) THEN
 MAILFROMDBS ('ORA-600 Error ORACLE INTERNAL ERROR:FATAL at '||x||' ' ,msgtext2);
ELSIF (ORA_IS_SERVERERROR(60)) THEN
 MAILFROMDBS ('ORA-60 DEADLOCK ENCOUNTERED at '||x||' ',msgtext2);
ELSIF (ORA_IS_SERVERERROR(1541)) THEN
 MAILFROMDBS ('ORA-01541: system tablespace cannot be brought offline at '||x||' ',msgtext2);
ELSIF (ORA_IS_SERVERERROR(1652)) THEN
 MAILFROMDBS ('ORA-1652: unable to extend temp segment at '||x||' ',msgtext2);
ELSIF (ORA_IS_SERVERERROR(1653)) THEN
 MAILFROMDBS ('ORA-1653: unable to extend table segment at '||x||' ',msgtext2);
ELSIF (ORA_IS_SERVERERROR(1654)) THEN
 MAILFROMDBS ('ORA-1654: unable to extend index segment at '||x||' ',msgtext2);
ELSIF (ORA_IS_SERVERERROR(07445)) THEN
 MAILFROMDBS ('ORA-07445: exception encountered: core dump at '||x||' ',msgtext2);
ELSIF (ORA_IS_SERVERERROR(30036)) THEN
 MAILFROMDBS ('ORA-30036: Unable to extend segment in undo tablespace  at '||x||' ',msgtext2);
ELSIF (ORA_IS_SERVERERROR(01578)) THEN
 MAILFROMDBS ('01578: ORACLE data block corrupted  at '||x||' ',msgtext2);
END IF;
END;
/

— create a procedure to send a mail whenever an error occurs.

CREATE OR REPLACE PROCEDURE SYS.MAILFROMDBS ( MESSAGE  IN   VARCHAR,MESSAGE2  IN   VARCHAR) as
   y varchar2(50);
   z varchar2(50);
  iphost varchar2(50);
 CRLF        CHAR(2) := CHR(10) || CHR(13);
     begin
   select upper(INSTANCE_NAME) into y from v$instance;
   select HOST_NAME  into z from v$instance;
   select utl_inaddr.get_host_address(z) into iphost from dual;
 utl_mail.send
              ( sender => '<@gmail.com>'
                ,recipients => '<@gmail.com>'
                ,subject => 'Mail from '||y||' Oracle Database on '||z||' ('||iphost||') Server for ORA Error'
                ,message => 'Dear DB Admin: ' || CRLF || CRLF||
                            'Hi, I am '||y||'  database on  '||z||'('||iphost||')' || CRLF || CRLF||
                             message || CRLF || CRLF||
                            'Request you to do the needfull'||CRLF || CRLF||
                            'Kind regards,' || CRLF ||
                            ''||y||''
                ,mime_type => 'text/plain; charset=us-ascii'
        ,priority => 1
    );
 end;
/

%%%%%%%%%%%%%%%%%%% Some References %%%%%%%%%%%%%%%%%%%%

1. Schema difference for Oracle Database by André Araujo

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s