Dears,
We have a reporting server(IBM Cognos BI) using Oracle RAC as the data warehouse. Cognos uses OCI to communicate with Oracle database(Oracle client is installed with the cognos server machine). Cognos only issues SELECT statements to the database.
This is our tnsnames.ora file looks like
${DB_NAME}=
(DESCRIPTION=
(ENABLE=BROKEN)
(LOAD_BALANCE=off)
(FAILOVER=on)
(ADDRESS=(PROTOCOL=tcp)(HOST=${VIP1_IP})(PORT=11521))
(ADDRESS=(PROTOCOL=tcp)(HOST=${VIP2_IP})(PORT=11521))
(CONNECT_DATA=
(SERVICE_NAME=${DB_NAME})
(FAILOVER_MODE=(TYPE=select)(METHOD=basic))
(server = dedicated)
)
)
As to TYPE=SELECT, it is said that if the instance on which the select statement is being executed is down, one of the available oracle instances will take over as nothing has happened. The failover is transparent to the application(Cognos). http://www.dba-oracle.com/art_oramag_rac_taf.htm
But when we manually shutdown an instance , we did not see the result as expected while get the following:
ORA-25402: transaction must roll back
Cause: A failure occured while a transaction was active on this connection.
Action: The client must roll back.
So, it looks like if application wants to take the advantage of this functionality, the application must be modified to do something related to transaction termination, Right? If so, like a product as Cognos, not a project, it could not do things specific to a particular database.
Does anyone face this situation before? Any thought is welcome.
Hi,
You might try changing the transaction isolation level used in the data source definition in your Cognos instance. It is probably defaulting to Cursor Stability. Try setting it right down at Read Uncommitted - see if that makes any difference.
Just a stab in the dark - if it works it will be another muppet lucky guess :)
MF.
Thanks. Initially, I also thought that. But did not give a try. You are right. Let's try that...
Most of the isolation level are not applicable for Oracle including Read uncommitted according to http://publib.boulder.ibm.com/infocenter/c8bi/v8r4m0/index.jsp?topic=/com.ibm.swg.im.cognos.ug_cra.8.4.0.doc/ug_cra_id10118IsolationLevels.html