Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Sample Pro*C code for TAF in 8iOPS/9iRAC

Re: Sample Pro*C code for TAF in 8iOPS/9iRAC

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Thu, 03 Oct 2002 07:53:29 -0800
Message-ID: <F001.004DF596.20021003075329@fatcity.com>



Actually, we are aware about TAF setup in the TNSNAMES.ORA
SQLPlus sessions fail-over perfectly well with the TNSNAMES.ORA file.
It is our custom Pro*C programs [originally written against 8.1.5] that
don't seem to be handling failover when it is doing DML.

Quoting from the 9iRAC Concepts Manual, Chapter 10, the
paragraphs titled "Database Manipulation Language Clients" under the
section "Uses of Transparent Application Failover" :
"
Database Manipulation Language (DML) database clients perform INSERT, UPDATE, and DELETE operations. Oracle handles certain errors and performs a reconnect when those errors occur.

Without this application code, INSERT, UPDATE, and DELETE operations on the failed instance return an un-handled Oracle error code. Upon re-submission, Oracle routes the client connections to a surviving instance. The client transaction then stops only momentarily until server-side recovery completes
."

we doubt if the custom-built Pro*c programs are able to
handle the Oracle error code and re-submit the last DML which
was "in flight"

and the section "Transparent Application Failover Restrictions" :
"
When a connection fails, you might experience the following:
       All PL/SQL package states on the server are lost at failover
       ALTER SESSION statements are lost
       If failover occurs when a transaction is in progress, then each subsequent call causes an error message until the user issues an OCITransRollback call. Then Oracle issues an Oracle Call Interface (OCI) success message. Be sure to check this message to see if you must perform additional operations.
       Oracle fails over the database connection and if TYPE=SELECT in the FAILOVER_MODE section of the service name description, Oracle also attempts to fail over the query
       Continuing work on failed-over cursors can result in an error message

If the first command after failover is not a SQL SELECT or OCIStmtFetch statement, then an error message results. Failover only takes effect if the application is programmed with OCI release 8.0 or greater.
"
again we doubt if the programs are handling the error message,
issuing an OCITransRollback or a SQL SELECT.

My developers are now trying to include code to look for an error
message and/or reissue an SQL SELECT.

What I was looking for was some sample Pro*C programs or templates.
I have provided the "cdemofo.c" program from $O_H/rdbms/demo to my
developer but that isn't very helpful.

Hemant

At 08:08 PM 02-10-02 -0800, you wrote:
Only in the sense that you must use tools that use OCI, such as OCI itself,
Pro*Precompilers, SQLJ, and JDBC OCI drivers.  APIs that do not use OCI (and
thus can't use TAF) include JDBC Thin drivers...

You can use the TNS entry I sent in my email from SQL*Plus to failover
between two instances in 8i OPS, 9i RAC, and certain flavors of 9i Data
Guard.  You don't need to program in OCI, just use an OCI-based network
driver...

The distinction I was making was that in Oracle8 v8.0, you had to actually
program the failover code in "C" code in OCI only...

----- Original Message -----
To: <ORACLE-L@fatcity.com>
Cc: <Tim@SageLogix.com>
Sent: Wednesday, October 02, 2002 8:29 PM


> Tim, TAF still requires the application to be
> programmed with Oracle8 OCI and greater. TAF is a
> combination of OCI and net services to allow the
> failover to occur. This requirement is still
> documented in the Oracle9i R2 manuals.
>
>
http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/rac.920/a9
6597/pshavdtl.htm#20180
>
> Scott
>
> --- Tim Gorman &lt;Tim@SageLogix.com&gt; wrote:
> &gt; Hemant,
> &gt;
> &gt; TAF was specific to OCI only in v8.0 of the
> &gt; database.  In v8.1 and upwards, it is specified
> for
> &gt; any application or API in the TNSNAMES entry...
> &gt;
> &gt; This example comes straight out of the
> &#34;Oracle Net8
> &gt; Administration&#34; manual, page 8-14.  I'd
> suggest
> &gt; reading up on the meaning of the FAILOVER_MODE
> &gt; settings, specifically TYPE= and METHOD=.  Also,
> if
> &gt; you have your OPS/RAC instances in a pure
> &gt; &#34;active/passive&#34; arrangement, then the
> TNS entry
> &gt; below should work (i.e. LOAD_BALANCE=OFF).  If
> you
> &gt; have both instances equally available for user
> &gt; connections (i.e. &#34;active/active&#34;
> failover
> &gt; arrangement), then you might consider changing
> &gt; LOAD_BALANCE=ON to distribute the connections
> &gt; randomly.  However, you'll want to think
> carefully
> &gt; about using that mechanism...  :-)
> &gt;
> &gt;   sales.us.acme.com=
> &gt;       (description=
> &gt;           (load_balance=off)
> &gt;           (failover=on)
> &gt;           (address_list=
> &gt;               (address=
> &gt;                   (protocol=tcp)
> &gt;                   (host=sales1-server)
> &gt;                   (port=1521)
> &gt;               )
> &gt;               (address=
> &gt;                   (protocol=tcp)
> &gt;                   (host=sales2-server)
> &gt;                   (port=1521)
> &gt;               )
> &gt;           )
> &gt;           (connect_data=
> &gt;               (service_name = sales.us.acme.com)
> &gt;               (failover_mode =
> &gt; (type=select)(method=basic))
> &gt;           )
> &gt;       )
> &gt; Hope this helps...
> &gt;
> &gt; -Tim
> &gt;
> &gt; ----- Original Message -----
> &gt; To: &#34;Multiple recipients of list
> ORACLE-L&#34;
> &gt; &lt;ORACLE-L@fatcity.com&gt;
> &gt; Sent: Wednesday, October 02, 2002 10:13 AM
> &gt;
> &gt;
> &gt; &gt;
> &gt; &gt;
> &gt; &gt; Can anyone send me sample Pro*C code
> depicting how
> &gt; &gt; they've handled TAF in 8iOPS or 9iRAC ?
> &gt; &gt;
> &gt; &gt; We've got some application servers running
> Pro*C
> &gt; programs
> &gt; &gt; which don't seem to able to handle failover.
> &gt; SQLPlus sessions
> &gt; &gt; are failing over to the remaining instance
> but the
> &gt; Pro*C programs
> &gt; &gt; seem to be &#34;hanging&#34;, without
> returning any error,
> &gt; when
> &gt; &gt; a transaction is &#34;in-flight&#34;  We do
> know that
> &gt; Insert/Update/Deletes
> &gt; &gt; do not really failover but are wondering how
> the
> &gt; Pro*C programs
> &gt; &gt; could be written to handle the oracle error
> on
> &gt; session failover
> &gt; &gt; and resubmit the transaction.
> &gt; &gt;
> &gt; &gt;
> &gt; &gt; Hemant K Chitale
> &gt; &gt; My web site page is :
> http://hkchital.tripod.com
> &gt; &gt;
> &gt; &gt;
> &gt; &gt; --
> &gt; &gt; Please see the official ORACLE-L FAQ:
> &gt; http://www.orafaq.com
> &gt; &gt; --
> &gt; &gt; Author: Hemant K Chitale
> &gt; &gt;   INET: hkchital@singnet.com.sg
> &gt; &gt;
> &gt; &gt; Fat City Network Services    -- 858-538-5051
> &gt; http://www.fatcity.com
> &gt; &gt; San Diego, California        -- Mailing list
> and
> &gt; web hosting services
> &gt; &gt;
> &gt;
> ---------------------------------------------------------------------
> &gt; &gt; To REMOVE yourself from this mailing list,
> send an
> &gt; E-Mail message
> &gt; &gt; to: ListGuru@fatcity.com (note EXACT
> spelling of
> &gt; 'ListGuru') and in
> &gt; &gt; the message BODY, include a line containing:
> UNSUB
> &gt; ORACLE-L
> &gt; &gt; (or the name of mailing list you want to be
> &gt; removed from).  You may
> &gt; &gt; also send the HELP command for other
> information
> &gt; (like subscribing).
> &gt;
>
>
> __________________________________________________
> Do you Yahoo!?
> New DSL Internet Access from SBC & Yahoo!
> http://sbc.yahoo.com

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tim Gorman
  INET: Tim@SageLogix.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Hemant K Chitale
My web site page is :  http://hkchital.tripod.com

-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hemant K Chitale INET: hkchital@singnet.com.sg Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Oct 03 2002 - 10:53:29 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US