Again, it makes sense.
How do one be proactive and be alerted when user, suddenly, can't
connect to prod d/b?
Chuck wrote:
> epipko_at_gmail.com wrote:
> > It makes sense.
> > I am trying to log an event when d/b is down or user can't connect to
> > it.
> >
> > fitzjarrell_at_cox.net wrote:
> >> epipko_at_gmail.com wrote:
> >>> Hi all,
> >>> I have a trigger to catch ddl errors. I am trying (unsuccessful so far)
> >>> to catch ORA-03113.
> >>> I don't see this error in the list of errors Oracle can't catch and
> >>> don't know why it doesnt' work.
> >>> ----------------------------------------------------------------------------------------------------------
> >>> sql> DROP TABLE SERVERERROR_LOG;
> >>>
> >>> Table dropped.
> >>>
> >>> sql> CREATE TABLE SERVERERROR_LOG (
> >>> 2 err_user VARCHAR2(30),
> >>> 3 err_host VARCHAR2(30),
> >>> 4 err_program varchar2(30),
> >>> 5 err_datetime TIMESTAMP,
> >>> 6 err_stack VARCHAR2(2000),
> >>> 7 err_sql VARCHAR2(2000));
> >>>
> >>> Table created.
> >>>
> >>> CREATE OR REPLACE TRIGGER UB_OBJECTS.log_server_errors
> >>> AFTER SERVERERROR ON DATABASE
> >>> DECLARE
> >>> sql_text ora_name_list_t;
> >>> sql_stmt VARCHAR2(2000);
> >>> vProgram VARCHAR2(30);
> >>>
> >>> BEGIN
> >>> SELECT
> >>> program
> >>> INTO
> >>> vProgram
> >>> FROM v$session
> >>> WHERE audsid=USERENV('sessionid');
> >>>
> >>> FOR i IN 1..ora_sql_txt(sql_text)
> >>> LOOP
> >>> sql_stmt := sql_stmt || sql_text(i);
> >>> END LOOP;
> >>>
> >>> INSERT INTO SERVERERROR_LOG
> >>> (err_user,
> >>> err_host,
> >>> err_program,
> >>> err_datetime,
> >>> err_stack,
> >>> err_sql)
> >>> VALUES
> >>> ((SELECT SYS_CONTEXT ('USERENV', 'OS_USER') FROM DUAL),
> >>> (SELECT SYS_CONTEXT ('USERENV', 'HOST') FROM DUAL),
> >>> vProgram,
> >>> SYSTIMESTAMP,
> >>> DBMS_UTILITY.FORMAT_ERROR_STACK,
> >>> sql_stmt);
> >>>
> >>> END log_server_errors;
> >>>
> >>> sql> select * from dualllll
> >>> *
> >>> ERROR at line 1:
> >>> ORA-00942: table or view does not exist
> >>>
> >>> sql> select err_stack from servererror_log;
> >>>
> >>> ERR_STACK
> >>> ----------------------------------------
> >>> ORA-00942: table or view does not exist
> >>>
> >>> sql> select dbms_metadata.get_ddl('TABLE','PD2_WORK_ORDER')from dual;
> >>> ERROR:
> >>> ORA-03113: end-of-file on communication channel
> >>>
> >>> sql> select err_stack from servererror_log;
> >>> ERR_STACK
> >>> ----------------------------------------
> >>> ORA-00942: table or view does not exist
> >>> ----------------------------------------------------------------------------------------------------------
> >>>
> >>> Why can't I catch 3113???
> >>>
> >>> Thanks,
> >>> Eugene
> >> Does the fact that you're no longer CONNECTED to the database provide
> >> any clue? ORA-03113 is an informational message from SQL*Plus reported
> >> after the connection is lost. You can't capture that which isn't
> >> there.
> >>
> >>
> >> David Fitzjarrell
> >
>
> Please don't top post.
>
> Think about what you just said. If the database is down how is it going
> to fire a trigger?
>
> If the user can't connect to the database for some other reason (any
> other reason), how is the database going to fire a trigger regarding a
> session that never got started?
>
> Even for an ora-3113 (dropped connection), the database may not even
> know that the connection dropped unless DCD is enabled.
>
> Some errors simply cannot be trapped.
Received on Wed Jul 26 2006 - 00:34:55 CDT
