Subject: | TAF with DBD:Oracle - no way to stop retrying reconnects? |
Date: | Tue, 03 Apr 2012 12:50:08 +0200 |
To: | "DBD:" [...] relay01.alfahosting-server.de:Oracle Bug <bug-DBD-Oracle [...] rt.cpan.org> |
From: | Norbert Debes <norbert.debes [...] oradbpro.com> |
Hi DBD:Oracle team,
I have run some tests with TAF in DBD::Oracle with a TAF callback
handler. What I'm missing is some feature to limit the number of
reconnection retries. The program retries forever, no matter what the
return code of the TAF callback is.
It looks like there should be an additional parameter*taf_retries* that
limits the number of reconnect attempts. According to Oracle® Database
PL/SQL Packages and Types Reference
11g Release 2 (11.2)
E10577-05
December 2009:
If a TAF callback has been registered, then the failover retries and
failover delay
are ignored. If an error occurs, TAF will continue to re-attempt the
connect and
authentication as long as the callback returns a value of OCI_FO_RETRY. Any
delay should be coded into the callback logic.
So the RETRIES set for the service on the server-side using DBMS_SERVICE
are ignored.
Another problem: the value 15 that I passed for taf_sleep is ignored.
Instead a 5 second sleep is always used.
By the way the example TAF handler provided in the DBD::Oracle doc did
not work for me. I had to change
ora_taf_function=>'handle_taf'
to
ora_taf_function=>'main::handle_taf'
Does this reproduce in other environments?
Could it be that the return value from the TAF handler is ignored? It should be possible to return a value that stops any further
reconnect attempts. Unfortunately even the Oracle® Call Interface
Programmer's Guide
11g Release 2 (11.2)
E10646-04
does not say what value to return in order to stop reconnect attempts (or I could not find the information).
I'll attach the source file that I used.
I tested with this service that has server-side TAF settings (such that EZConnect can be used on the client-side):
begin
dbms_service.create_service(
SERVICE_NAME => 'PLMON',
NETWORK_NAME => 'PLMON',
FAILOVER_METHOD => 'BASIC',
FAILOVER_TYPE => 'SESSION',
FAILOVER_RETRIES => 1440,
FAILOVER_DELAY => 60
);
end;
/
exec dbms_service.start_service('PLMON', dbms_service.all_instances);
begin
dbms_service.modify_service(
SERVICE_NAME => 'PLMON',
FAILOVER_METHOD => 'BASIC',
FAILOVER_TYPE => 'SESSION',
FAILOVER_RETRIES => 4,
FAILOVER_DELAY => 15
);
end;
/
-- this triggers failover:
exec dbms_service.stop_service('PLMON', dbms_service.all_instances);
exec dbms_service.disconnect_session('PLMON', dbms_service.immediate);
PS:
E:\home\ndebes\it\perl>perl taf.pl
audsid=1162169
update and commit complete
audsid=1162169
update and commit complete
Instance Unavailable Please stand by!!
Your TAF type is SESSION
3.4.2012 12:27:20
Failover error - sleeping ...
Failover aborted. Failover will not take place.
DBD::Oracle::db prepare failed: ORA-03113: end-of-file on communication channel
Process ID: 22192
Session ID: 72 Serial number: 63 (DBD ERROR: OCIStmtExecute/Describe) [for Statement "SELECT userenv('sessionid') FROM dual"] at taf.pl line 108.
ORA-03113: end-of-file on communication channel
Process ID: 22192
Session ID: 72 Serial number: 63 (DBD ERROR: OCIStmtExecute/Describe)
audsid=1162170
update and commit complete
audsid=1162170
update and commit complete
Message body is not shown because sender requested not to inline it.
Message body is not shown because sender requested not to inline it.