Datastage Connectivity to Oracle RAC

sjordery · Post by **sjordery** » Fri May 22, 2009 1:09 am

We have a RAC with two failover dbs, PREQDB1 & PREQDB2. as separate entries on the tnsnames.ora, .odbc.ini and .uvodbc.config files. This works, but obviously requires that we connect to a specific DSN (either 1 or 2) whereas I think ideally we want a single DSN that can failover to the second service if the first fails.

Do you think that this is possible? I am sure there must be numerous Oracle RAC installations working with Datastage, and it cannot be that if you use Datastage you can connect only to a specific server of the RAC. There must be a standard way of configuring to allow for automatic failover.

chulett · Post by **chulett** » Fri May 22, 2009 6:44 am

Really got nothing to do with DataStage, it is just another client like any other app or tool. That failover is all in the hands of your DBAs, how they have configured RAC and what's in your tnsnames file. The fact that it is 'RAC' under the covers is completely transparent to the outside world.

sjordery · Post by **sjordery** » Mon May 25, 2009 1:26 am

chulett wrote:Really got nothing to do with DataStage, it is just another client like any other app or tool. That failover is all in the hands of your DBAs, how they have configured RAC and what's in your tnsnames file. The fact that it is 'RAC' under the covers is completely transparent to the outside world.

yes but for the purposes of defining a TNS entry, when we define the TNS entry as defined by the DBA to take advantage of the RAC (cluster), from Datastage we are unable to connect - basically the SID value needs a definite node name, and not the symbolic node name which the DBA has defined so that applications are transparent to which db node is currently supporting the application.

keshav0307 · Post by **keshav0307** » Mon May 25, 2009 6:47 am

we have ORACLE RAC with 4 failover servers and successfully running with only one tns name in tnsnames.ora file, for all the four failover database instances

chulett · Post by **chulett** » Mon May 25, 2009 7:47 am

Same here, with three nodes. Sounds like your tnsnames entry isn't correct... not wrong, just not correct for DataStage it seems. Ask your DBA to 'fix' it for you.

sjordery · Post by **sjordery** » Wed May 27, 2009 6:18 am

keshav0307 wrote:we have ORACLE RAC with 4 failover servers and successfully running with only one tns name in tnsnames.ora file, for all the four failover database instances

Hi,
Good to know that it works for you. Is it possible to have a copy of the tnsnames.ora file so that we can see where we are going wrong?

sjordery · Post by **sjordery** » Wed May 27, 2009 6:34 am

chulett wrote:Same here, with three nodes. Sounds like your tnsnames entry isn't correct... not wrong, just not correct for DataStage it seems. Ask your DBA to 'fix' it for you.

I am pasting an extract of the TNS entries created by our DBA, along with his notes:
"
We can use any one of the following TNS entries to access preqdb database:

PREQDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sdcdbc01p-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = preqdb)
(INSTANCE_NAME = preqdb1)
)
)

This will connect to preqdb database only through the server sdcdbc01

PREQDB2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sdcdbc02p-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = preqdb)
(INSTANCE_NAME = preqdb2)
)
)

This will connect to preqdb database only through the server sdcdbc02

If we use any one of the above TNS entries then we can connect to the database through any one of the nodes/servers only. If the node we connected through fails then we lose connection.

To take advantage RAC (cluster) we use the following TNS entry. Ths will ensure connection failover to existing node in case there is any issue.

PREQDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sdcdbc01p-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = sdcdbc02p-vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = preqdb)
)
)

Here service preqdb is dynamically registered with listener and all the above TNS entries contain this entry. Hence when we use any of the above TNS names we connect to preqdb database.
"

Hi,
The funny thing is, when we log in from the Datastage server using sqlplus, we are able to connect to the db server using the TNS entry defined to take advantage of the RAC, i.e. the TNS entry PREQDB. But when we use Datastage for connecting, this fails. So we are thinking that something needs to be done at the Datastage end.

Any help will be much appreciated.

chulett · Post by **chulett** » Wed May 27, 2009 6:57 am

Ummm... 'fails' how, exactly?

sjordery · Post by **sjordery** » Mon Jun 01, 2009 7:59 am

Hi,

The error message is received when attempting to import a table definition from the DB:

Code: Select all

DSR.MetaGeta(GET.TABLES)(SQLConnect('PREQDB','pesdi')): BCI Error:

SQLSTATE=08001,CODE=-1,[DataStage][SQL Client][ODBC][DataDirect][ODBC Oracle Wire Protocol driver][Oracle]TNS-12505: TNS:listener could not resolve SID given in connect descriptor

Regards,
S

chulett · Post by **chulett** » Mon Jun 01, 2009 9:03 am

So... you're importing the metadata using ODBC? Why not OCI or 'orchdbutil' from the Import Orchestrate Schema option? Otherwise, are you certain your ODBC entry is setup/configured correctly?

crouse · Post by **crouse** » Fri Jun 12, 2009 3:25 pm

Do you have this set in your dsenv?

APT_ORACLE_NO_OPS=1;
export APT_ORACLE_NO_OPS

Needed for accessing a RAC that is not resident on the same server as DataStage.

crouse · Post by **crouse** » Fri Jun 12, 2009 3:27 pm

Also, PREQDB isn't a SID, it's a SERVICE_NAME.