5

In an Oracle database there's a big PL/SQL procedure being executed periodically that copies data from one DB to another one through a database link and it is failing after some hours with the following error:

ORA-03150: end-of-file on communication channel for database link 
ORA-02063: preceding line from DBPREMOTE 
ORA-06512: at "DBLOCAL.JOB_NAME", line 710 
...
ORA-06512: at line 1

Line 710 is the first line of a procedure:

 execute immediate 'set constraints all deferred';

Then the procedure does some inserts and updates, which I guess are failing at some point due to PK, data not valid or whatever other reason. I guess that the exception is being pointing at that line because it is the first one, not because it is actually failing there, but I don't know for sure the real exception.

Is there any chance I can get the real exception so I can handle it?

detoro84
  • 303
  • 1
  • 4
  • 14
  • It's more likely that you've got a slightly flaky network than an INSERT failing. If the INSERT was failing you'd still get an error related to that (you can test this - don't assume!) – Ben Dec 27 '17 at 10:06
  • It's a distributed transaction, so the first step is to look for more details in the `alert.log` on **the remote server**. That should give you enough information to understand why the transaction failed. – APC Dec 27 '17 at 12:49
  • We just received the alert.log details and we are getting: `TNS-12535: TNS:operation timed out ns secondary err code: 12560 nt main err code: 505 TNS-00505: Operation timed out nt secondary err code: 110 nt OS err code: 0` in the local DB, nothing in the alert.log of the remote server. So we are inclined to think that it is something in the local procedure. I guess the timeout after 2h is normal. – detoro84 Dec 27 '17 at 13:38
  • Can it be that `execute immediate 'set constraints all deferred';` is taking longer than the timeout? – detoro84 Dec 27 '17 at 14:39

1 Answers1

0

A potential workaround could be to close the DB-Link after each usage with a PL/SQL procedure like this:

FOR aLink IN (SELECT * FROM V$DBLINK) LOOP
    DBMS_SESSION.CLOSE_DATABASE_LINK(aLink.DB_LINK);
END LOOP;

or 

DECLARE
    DATABASE_LINK_IS_NOT_OPEN EXCEPTION;
    PRAGMA EXCEPTION_INIT(DATABASE_LINK_IS_NOT_OPEN, -2081);
BEGIN
    DBMS_SESSION.CLOSE_DATABASE_LINK('DBPREMOTE ');
EXCEPTION 
    WHEN DATABASE_LINK_IS_NOT_OPEN THEN 
        NULL;
END;

If the connections are dropped anyway, you should talk to your network guys. Perhaps they drop the connection by firewall settings. However, there could be many others reasons.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • Yes, the problem is a connection timeout, which would be fixed with this workaround but something is making the procedure to be so slow to let the link timeout. – detoro84 Dec 27 '17 at 13:43