Home
All Oracle Error Codes
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

RE: How to handle the exception when remote db is down

Mercadante, Thomas F

2005-02-16

Replies:
Sami,

The problem is that Oracle is still holding the update to the remote
database open. Try the following:

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);
   Commit;     <========== This makes sure your first insert works
5 begin
6 insert into db_lnk_test@(protected));
7 exception when others then
   Rollback;    <===== Rollback the remote transactions
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;


You could also look at using savepoints. Nick had a good idea also about
looking at Advanced Queueing.

Tom


-----Original Message-----
From: Sami Seerangan [mailto:dba.orcl@(protected)]
Sent: Tuesday, February 15, 2005 8:47 PM
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


SQL> create database link testa connect to MY_USER identified by
temp_123 using 'testa';

Database link created.


SQL> get p1
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));
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>

SQL> exec db_lnk_test_proc(1);

PL/SQL procedure successfully completed.


SQL> exec db_lnk_test_proc(5);
BEGIN db_lnk_test_proc(5); END;

*
ERROR at line 1:
ORA-02067: transaction or savepoint rollback required
ORA-06512: at "MY_USER.DB_LNK_TEST_PROC", line 8
ORA-02055: distributed update operation failed; rollback required
ORA-02068: following severe error from TESTA
ORA-03113: end-of-file on communication channel
ORA-06512: at line 1

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