Oracle Database Discussions

Hello,


I am facing some issues with a database:

SELECT   * FROM v$version;
 
=================
 
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE	9.2.0.1.0	Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
 
5 rows selected.

Running on Microsoft(R) Windows(R) Server 2003, Enterprise Edition

It keeps showing

Oracle.net.config.ServiceAliasException: 10 

very frequently. This problem is preventing me to access the EM.
I am resolving this by reconfiguring the tnsnames.ora every time the message is being displayed, but
have not find out any permanent solution to the above.
Nevertheless, I am capable to use SQL*Plus and access the DB via it.

Moreover, the database goes down frequently, and I need to find a solution to this too.
I am thinking of making use of a trigger, but I have never made use of level triggers so I am a little
bit concerned if it is the right thing to do.

Here it is what I am thinking of:
The trigger is to be fired everytime the db goes down.
Now, can the trigger call a batch (or sth else that will startup the db)?

create table tp_keep_track
(  error_source_tp VARCHAR2(30),
   db_or_user_name       VARCHAR2(30),
   event_name          VARCHAR2(20),
   event_time          DATE,
   triggered_by_user   VARCHAR2(30)
);

The above table, just to keep some statistics wherever the db goes down
After that:

create or replace trigger shut_trig_tp before shutdown on database
declare
pragma autonomous_transaction;
BEGIN
  INSERT INTO tp_keep_track
  (error_source_tp,
   db_or_user_name
   event_name,
   event_time,
   triggered_by_user)
   VALUES ( 'db_shutdown_tp',
   sys.database_name,
    sys.sysevent,
         sysdate,
       sys.login_user);
 COMMIT;
end;
/

Just to be sure that servererror are not the reason of such problems, I thought of another trigger:

   create or replace trigger log_serv_errors after servererror on database
  declare
     sql_text ora_name_list_t;
     msg varchar2(2000) := null;
     stmt varchar2(2000):= null;
  begin
     for i in 1 .. ora_server_error_depth loop
        msg := msg||ora_server_error_msg(i);
     end loop;
      for i in 1..ora_sql_txt(sql_text) loop
         stmt := stmt||sql_text(i);
      end loop;
   insert into tp_keep_track(error_source_tp, db_or_user_name,event_name,event_time,triggered_by_user)values ('serverError_tp',ora_login_user, msg, sysdate, stmt);
   end;

Now, in both case, I need your help to determine if the trigger should call a batch in order to startup the db after it goes down

Any advice?

Thank you all and regards,
Pupli