-
I have pl/sql procedure that is written on the basis of dbms_jobs. It sends alerts when there is a problem with database. I need to rewrite this procedure so alert is not send right away but after a couple of tries.
Can anyone please help me?
Here is text of procedure:
create or replace procedure MOOSE_DB is
IO_ERROR EXCEPTION;
PRAGMA EXCEPTION_INIT (IO_ERROR, -204);
DEDICATED_PROCESS_FAILED EXCEPTION;
PRAGMA EXCEPTION_INIT (DEDICATED_PROCESS_FAILED, -12500);
HOST_NOT_FOUND EXCEPTION;
PRAGMA EXCEPTION_INIT (HOST_NOT_FOUND, -12545);
TNS_ERROR EXCEPTION;
PRAGMA EXCEPTION_INIT (TNS_ERROR, -12154);
LISTENER_ERROR EXCEPTION;
PRAGMA EXCEPTION_INIT (LISTENER_ERROR, -12541);
UNABLE_TO_CONNECT EXCEPTION;
PRAGMA EXCEPTION_INIT (UNABLE_TO_CONNECT, -2068);
NO_DB_LINK EXCEPTION;
PRAGMA EXCEPTION_INIT (NO_DB_LINK, -2019);
MAX_PROCESSES_EXCEDED EXCEPTION;
PRAGMA EXCEPTION_INIT (MAX_PROCESSES_EXCEDED, -20);
ARCHIVER_ERROR EXCEPTION;
PRAGMA EXCEPTION_INIT (ARCHIVER_ERROR, -257);
cursor db is
select rtrim(sid) as sid , rtrim(host) as host, rtrim(department) as department, rtrim(server_group) as server_group, primary_dba from db_instances where alert_status = 'A' and status = 'ONLINE' ;
db_rec db%rowtype;
cursor db_alert_mail_server is
select smtp_server_name, pop3_imap4_server_name, alert_email_address from db_alert_mail_server;
db_alert_mail_server_rec db_alert_mail_server%rowtype;
cursor db_alert_moose is
select phone_email_address from db_alert_list where JOB_TITLE = 'MOOSE';
db_alert_moose_rec db_alert_moose%rowtype;
cursor db_alert_dba is
select email_address, phone_email_address from db_alert_list where NAME = db_rec.primary_dba;
db_alert_dba_rec db_alert_dba%rowtype;
cursor db_alert_dba_manager is
select email_address from db_alert_list where JOB_TITLE = 'DBA MANAGER';
db_alert_dba_manager_rec db_alert_dba_manager%rowtype;
stmt varchar2(2000);
rows number;
name_var varchar2(12);
version_var varchar2(66);
sqlcursor number;
sqlcursor2 number;
dblink varchar2(60);
begin
open db;
fetch db into db_rec;
open db_alert_mail_server;
fetch db_alert_mail_server into db_alert_mail_server_rec;
open db_alert_dba_manager;
fetch db_alert_dba_manager into db_alert_dba_manager_rec;
open db_alert_moose;
fetch db_alert_moose into db_alert_moose_rec;
while not db%NOTFOUND loop
open db_alert_dba;
fetch db_alert_dba into db_alert_dba_rec;
dblink := db_rec.sid||'.'||db_rec.host;
sqlcursor := dbms_sql.open_cursor;
stmt:='select dbid as dbid, log_mode as log_mode from v$database@'||dblink;
dbms_sql.parse(sqlcursor, stmt, 1);
dbms_sql.define_column(sqlcursor, 1, name_var, 12);
rows := dbms_sql.execute_and_fetch(sqlcursor);
dbms_sql.column_value(sqlcursor, 1, name_var);
dbms_sql.close_cursor(sqlcursor);
fetch db into db_rec;
close db_alert_dba;
end loop;
close db;
close db_alert_mail_server;
close db_alert_dba_manager;
close db_alert_moose;
EXCEPTION
WHEN IO_ERROR
THEN
update db_instances
set status_details = 'IO ERROR',
status = 'OFFLINE',
alert_time = sysdate(),
alert_number = 1
where sid = db_rec.sid and host = db_rec.host;
commit;
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_moose_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - IO ERROR');
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - IO ERROR');
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - IO ERROR');
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_manager_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - IO ERROR');
close db;
close db_alert_mail_server;
close db_alert_dba_manager;
close db_alert_moose;
MOOSE_DB;
WHEN DEDICATED_PROCESS_FAILED
THEN
update db_instances
set status_details = 'DEDICATED PROCESS UNABLE TO START',
status = 'OFFLINE',
alert_time = sysdate(),
alert_number = 1
where sid = db_rec.sid and host = db_rec.host;
commit;
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_moose_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - DEDICATED PROCESS UNABLE TO START');
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - DDEDICATED PROCESS UNABLE TO START');
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - DEDICATED PROCESS UNABLE TO START');
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_manager_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - DEDICATED PROCESS UNABLE TO START');
close db;
close db_alert_mail_server;
close db_alert_dba_manager;
close db_alert_moose;
MOOSE_DB;
WHEN HOST_NOT_FOUND
THEN
update db_instances
set status_details = 'HOST NOT FOUND',
status = 'OFFLINE',
alert_time = sysdate(),
alert_number = 1
where sid = db_rec.sid and host = db_rec.host;
commit;
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_moose_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - HOST NOT FOUND');
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - HOST NOT FOUND');
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - HOST NOT FOUND');
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_manager_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - HOST NOT FOUND');
close db;
close db_alert_mail_server;
close db_alert_dba_manager;
close db_alert_moose;
MOOSE_DB;
WHEN UNABLE_TO_CONNECT
THEN
update db_instances
set status_details = 'Unable to Connect',
status = 'OFFLINE',
alert_time = sysdate(),
alert_number = 1
where sid = db_rec.sid and host = db_rec.host;
commit;
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_moose_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - UNABLE TO CONNECT');
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - UNABLE TO CONNECT');
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - UNABLE TO CONNECT');
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_manager_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - UNABLE TO CONNECT');
close db;
close db_alert_mail_server;
close db_alert_dba_manager;
close db_alert_moose;
MOOSE_DB;
WHEN TNS_ERROR
THEN
update db_instances
set status_details = 'TNS ERROR',
status = 'OFFLINE',
alert_time = sysdate(),
alert_number = 1
where sid = db_rec.sid and host = db_rec.host;
commit;
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_moose_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - TNS ERROR');
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - TNS ERROR');
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - TNS ERROR');
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_manager_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - TNS ERROR');
close db;
close db_alert_mail_server;
close db_alert_dba_manager;
close db_alert_moose;
MOOSE_DB;
WHEN LISTENER_ERROR
THEN
update db_instances
set status_details = 'LISTENER ERROR',
status = 'OFFLINE',
alert_time = sysdate(),
alert_number = 1
where sid = db_rec.sid and host = db_rec.host;
commit;
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_moose_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - LISTENER ERROR');
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - LISTENER ERROR');
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - LISTENER ERROR');
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_manager_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - LISTENER ERROR');
close db;
close db_alert_mail_server;
close db_alert_dba_manager;
close db_alert_moose;
MOOSE_DB;
WHEN NO_DB_LINK
THEN
update db_instances
set status_details = 'NO DB LINK',
status = 'OFFLINE',
alert_time = sysdate(),
alert_number = 1
where sid = db_rec.sid and host = db_rec.host;
commit;
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_moose_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - NO DB LINK');
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - NO DB LINK');
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - NO DB LINK');
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_manager_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - NO DB LINK');
close db;
close db_alert_mail_server;
close db_alert_dba_manager;
close db_alert_moose;
MOOSE_DB;
WHEN MAX_PROCESSES_EXCEDED
THEN
update db_instances
set status_details = 'MAX PROCESSES EXCEDED',
status = 'OFFLINE',
alert_time = sysdate(),
alert_number = 1
where sid = db_rec.sid and host = db_rec.host;
commit;
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_moose_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid ,
db_rec.host||'.'||db_rec.sid||' - MAX PROCESSES EXCEDED');
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid ,
db_rec.host||'.'||db_rec.sid||' - MAX PROCESSES EXCEDED');
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid ,
db_rec.host||'.'||db_rec.sid||' - MAX PROCESSES EXCEDED');
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_manager_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid ,
db_rec.host||'.'||db_rec.sid||' - MAX PROCESSES EXCEDED');
close db;
close db_alert_mail_server;
close db_alert_dba_manager;
close db_alert_moose;
MOOSE_DB;
WHEN ARCHIVER_ERROR
THEN
update db_instances
set status_details = 'ARCHIVER ERROR',
status = 'OFFLINE',
alert_time = sysdate(),
alert_number = 1
where sid = db_rec.sid and host = db_rec.host;
commit;
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_moose_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - ARCHIVER ERROR');
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - ARCHIVER ERROR');
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - ARCHIVER ERROR');
dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_manager_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - ARCHIVER ERROR');
close db;
close db_alert_mail_server;
close db_alert_dba_manager;
close db_alert_moose;
MOOSE_DB;
end MOOSE_DB;