Links
• Home
• Oracle DBA Forum
Frequent Oracle Errors
•TNS:could not resolve the connect identifier specified
•Backtrace message unwound by exceptions
•invalid identifier
•PL/SQL compilation error
•internal error
•missing expression
•table or view does not exist
•end-of-file on communication channel
•TNS:listener unknown in connect descriptor
•insufficient privileges
•PL/SQL: numeric or value error string
•TNS:protocol adapter error
•ORACLE not available
•target host or object does not exist
•invalid number
•unable to allocate string bytes of shared memory
•resource busy and acquire with NOWAIT specified
•error occurred at recursive SQL level string
•ORACLE initialization or shutdown in progress
•archiver error. Connect internal only, until freed
•snapshot too old
•unable to extend temp segment by string in tablespace
•Credential retrieval failed
•missing or invalid option
•invalid username/password; logon denied
•unable to create INITIAL extent for segment
•out of process memory when trying to allocate string bytes
•shared memory realm does not exist
•cannot insert NULL
•TNS:unable to connect to destination
•remote database not found'>ora-02019
•exception encountered: core dump
•inconsistent datatypes
•no data found
•TNS:operation timed out
•PL/SQL: could not find program
•existing state of packages has been discarded
•maximum number of processes exceeded
•error signaled in parallel query server
•ORACLE instance terminated. Disconnection forced
•TNS:packet writer failure
•see ORA-12699
•missing right parenthesis
•name is already used by an existing object
•cannot identify/lock data file
•invalid file operation
•quoted string not properly terminated
How to handle the exception when remote db is down

How to handle the exception when remote db is down

2005-02-16       - By Nick Tilbury @ Northampton

Reply:     1     2     3     4     5     6  

May I suggest you consider using Advanced Queuing.=20

Nick

-- --Original Message-- --
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)]On Behalf Of Sami Seerangan
Sent: 16 February 2005 01:47
To: oracle-l@(protected)
Subject: How to handle the exception when remote db is down


Hi All,

I am trying to do DML activity on both local and remore DB. If the
remote db is down, I need to insert the values into temporary table on
the local db so that later when the remore db becomes available I can
push the records.

This is what I did but don't know how to handle the exception when
remote db is down.

SQL> desc db_lnk_test
Name                                      Null?    Type
-- ---- ---- ---- ---- ---- ---- ---- --- -- ----- -- ---- ---- ---- -----=
-- --
C1                                                 NUMBER
C2                                                 VARCHAR2(100)
C3                                                 DATE

SQL> desc db_lnk_test_Q
Name                                      Null?    Type
-- ---- ---- ---- ---- ---- ---- ---- --- -- ----- -- ---- ---- ---- -----=
-- --
C1                                                 NUMBER
C2                                                 VARCHAR2(100)
C3                                                 DATE=20
=20
=20
SQL> create database link testa connect to MY_USER identified by
temp_123  using 'testa';
=20
Database link created.


SQL> get p1 =20
 1  create or replace procedure db_lnk_test_proc(i_p1 number)
 2  as
 3  begin
 4  insert into db_lnk_test values(i_p1,'From a1 db',sysdate);
 5  begin
 6  insert into db_lnk_test@(protected) values(i_p1,'From a1 db',sysdate);
 7  exception when others then
 8  insert into db_lnk_test_Q values(i_p1,'From a1 db',sysdate); --
If the remote db is down insert into QUEUE table
 9  end;
10  commit;
11* end;
SQL>=20

SQL> exec db_lnk_test_proc(1);
=20
PL/SQL procedure successfully completed.


SQL> exec db_lnk_test_proc(5);
BEGIN db_lnk_test_proc(5); END;
=20
*
ERROR at line 1:
ORA-02067 (See ORA-02067.ora-code.com): transaction or savepoint rollback required
ORA-06512 (See ORA-06512.ora-code.com): at "MY_USER.DB_LNK_TEST_PROC", line 8
ORA-02055 (See ORA-02055.ora-code.com): distributed update operation failed; rollback required
ORA-02068 (See ORA-02068.ora-code.com): following severe error from TESTA
ORA-03113 (See ORA-03113.ora-code.com): end-of-file on communication channel
ORA-06512 (See ORA-06512.ora-code.com): at line 1
=20
Thanks
Sami
--
http://www.freelists.org/webpage/oracle-l


=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D
This message is intended solely for the use of the individual or organisati=
on to whom it is addressed.  It may contain privileged or confidential info=
rmation.  If you have received this message in error, please notify the ori=
ginator immediately.  If you are not the intended recipient, you should not=
use, copy, alter, or disclose the contents of this message.  All informati=
on or opinions expressed in this message and/or any attachments are those o=
f the author and are not necessarily those of VarTecTelecom Europe Ltd or i=
ts affiliates. VarTec Telecom Europe Ltd accepts no responsibility for loss=
or damage arising from its use, including damage from virus.=20
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D

--
http://www.freelists.org/webpage/oracle-l