JDBC Datasource configuration in cluster with multiple nodes

[p_k3]

Hi experts,

I need to configure a JDBC datasource in Visual administration which will be used by the applications running in the portal. In our DEV & QA landscapes we have only one node and every thing is running fine. Now I need to configure the datasource in my PROD environment but I am little confused as to how to proceed with this because we have multiple nodes in the cluster in our production environment.

I sure that i dont have to configure the datasource separately in each of those node. we have 6 nodes in our prod portal and I think SAP would have designed it such a way that the datasource has to be configured in all of those nodes. but then I dont see any alternative way of configuring a single datasource which can be available across all the nodes of the cluster.

Can any one please guide me on this.

thanks in advance

--

PK 🙂

[Former Member]

Needs to know whether this is active/active cluster nodes, or active/passive cluster nodes. Normally there are 2 common approaches

1. virtual hostname (FQDN) or virtual IP address handling by load balancer

2. virtual hostname (FQDN) or virtual IP address handling by cluster manager

Each cluster node has their own hostname and IP which is permanent to it, but there should be another dynamic hostname or IP which commonly referring as virtual hostname and virtual IP.

You need to provide more info in order to tell me whether NetWeaver is in a cluster node, or database server is in cluster node.

[p_k3]

Hi Sing-Cheong,

Thanks for you reply.

To be frank with you, I never handled any cluster environment until now. Can you please guide me as to how I can check if its a active-active or active-passive cluster?

regards,

Pavan

[Former Member]

Hi P K,

Refers to architecture diagram for this SAP, if you have one. Look for network architecture diagram and Oracle RAC diagram.

You didn't tell me the Oracle version, so in this post, I would like to bring up that Oracle 11g RAC has a new feature called SCAN listener, which is Oracle's software level virtual hostname emulation as well.

I assume your don't have the architecture diagram, and your Oracle RAC (regardless 10g or 11g) is configured properly to use the virtual hostname. This way, you don't need to reverse engineer the design.

Following steps will contains the information to setup JDBC to use Oracle TNS setup in pre-configured RAC environment. Oracle Java OCI driver is used instead of JDBC thin Oracle driver to support RAC setup (I recommend)

Step 1:

Look at the tnsnames.ora file for the Oracle database (under admin directory), and it should contains either the virtual hostname of the database, or a set of lines pointing to multiple database servers' virtual or physical hostname.

You may need to verify all database nodes' tnsnames.ora to confirm they are consistent (except those debug or dummy entry). Normally the common in all of them is the correct value to use

Step 2:

Once you identified that, verify the entry is valid by typing "tnsping [TNS_NAME]." For example, if the TNS_NAME is iccs, then type "tnsping iccs"

Make second test to ensure it can login to database. Type "sqlplus system@[TNS_NAME]" then enter the system account password. If you has a username used in your JDBC connection, then test with that login. This will ensure the authentication is valid in database layer

Step 3:

Verify Oracle OCI JDBC driver is installed. It is called ojdbc14.jar

http://searchsap.techtarget.com/tip/Configuring-the-JDBC-Connector-Service-to-perform-database-looku...

This is the native Oracle JDBC driver which can be found in following as well

http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-10201-088211.html

Step 5:

Fill in the TNS name in your JDBC to use ojdbc14.jar. JDBC will automatically inherits the virtual hostname configured in Oracle RAC (to be exact, it is called Cluster Ready Services or CRS). The URL of JDBC syntax should look similar to below:

jdbc:oracle:oci:@[TNS name]

jdbc:oracle:oci:[username]@[TNS name]

jdbc:oracle:oci:[username]/[password]@[TNS name]

------------------

JDBC can be configured to use virtual IP of Oracle database servers like below, but Java JDBC support several other formats, such as using TNS name in steps indicated above

jdbc:oracle:thin:[username]/[password]@[ip address]:1521:[SID] (prior of Oracle 10g)

or

jdbc:oracle:thin:[username]/[password@//[ip address]:1521/[service name] (after Oracle 10g)

If you prefer to use JDBC Oracle thin driver, then following is the syntax for JDBC URL (I do not put username and password into it, but it is possible

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=

(LOAD_BALANCE=on)

(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))

(ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521))

(ADDRESS=(PROTOCOL=TCP)(HOST=host3)(PORT=1521))

)

(CONNECT_DATA=(SERVICE_NAME=service_name)))

Replace the hostname, port, and service name according to your setup. If you have more database node, then it will be longer. Everything should be in 1 line as the screen does not allow you to enter multiple line. This is for ease of reading purpose.

Personally, I do not recommend to use JDBC thin driver, because it makes troubleshooting in Oracle database server difficult. There is no way to tell what application is connecting from a particular IP address.

For example, SAP BusinessObject, Tomcat, jsp, any JDBC thin driver base RPC can all use JDBC thin within the same machine, and from database level, the application name will be "JDBC thin" beside username, password. If all are using identical username, then it is even harder to guess which one crash the database, or causing performance impact.

So I normally recommend to use Oracle JDBC OCI driver which provides many Oracle features, support native RAC, ease of troubleshooting.

Please remember to crosscheck the JDBC during future SAP NetWeaver upgrade as OCI driver is version specific (well, it supports a range of Oracle version and will contain new features as database evolve), while JDBC thin driver is not (because it lack many Oracle new features since year 2000)

[p_k3]

Hi Sing-Cheong,

Thanks a lot for your details response. That was very informative. Learnt quite new things from your response.

But what I am looking for is the way to configure a JDBC Datasource on Visual Administrator in clustered portal nodes. I checked the way other non-portal applications are accessing the same database from thei respective non-portal servers and they are using only one connection to connect to the database wich is similar to the string that I received from data database team.

Now I need to configure a datasource from Portal (in Visual Administrator) to this database but our production portal cluster has 6 nodes. I am just not sure if i should configure the datasource for each of those 6 nodes? I was just thinking that there may be just one central configuration which will be reflected across all the 6 nodes.

thanks in advance

--

PK

[Former Member]

The JDBC configuration is stores in database, so it only needs to configure once. All 6 Portal nodes will able to see the configuration as all of them read from the same Oracle RAC database.

[p_k3]

Hi Sing-Cheong,

Thanks for your reply.

Please accept my apologies for not understanding what you mean in your previous reply.

I need to configure the JDBC DataSource in the NetWeaver cluster using the JDBC Connector service. Is it possible to create the datasource at only just one place in NetWeaver Visual Administrator and the same can automatically be read by all te nodes in the cluster?

thanks in advance

--

PK

[Former Member]

Yes. Create JDBC only once