Load balancing on oracle (12r2) 2 node RAC is not working

load balancingoracle-12crac

Any help/pointers would be greatly appreciated.

I have a 2 node RAC (Oracle 12r2) on linux,

  • CRS and all its services are up.

  • Both the RAC instances are up.

  • scan listener resolves to 3 ip's.

But all the sessions from application go to only one node (RAC node 2).

and I see on my RAC node1 in the alert log file below error,

On WAS Console we have jdbc:oracle:thin:@myscan.mydomain.com:1521/mypdbservicename.mydomain.com

Not able connect to my pdb database using the service name via slqplus and via sql developer…

1)

Sqlplus schemaname/pwd@mypdb  --- fails

2)

sqlplus / as sysdba

alter session set container=mypdb;

Conn schemaname/pwd@mypdb failing --- fails 

NOTE: tnsping works

My Listener log consists the following:

<msg time='2019-08-23T07:09:36.992-04:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='RAC-01'
 host_addr='192.168.119.12' pid='17312'>
 <txt>23-AUG-2019 07:09:36 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=RAC-01)(USER=grid))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=203424000)) * status * 0
 </txt>
</msg>
<msg time='2019-08-23T07:09:37.994-04:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='RAC-01'
 host_addr='192.168.119.12' pid='17312'>
 <txt>23-AUG-2019 07:09:37 * service_update * +ASM1 * 0
 </txt>
</msg>
<msg time='2019-08-23T07:10:36.991-04:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='RAC-01'
 host_addr='192.168.119.12' pid='17312'>
 <txt>23-AUG-2019 07:10:36 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=RAC-01)(USER=grid))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=203424000)) * status * 0
 </txt>
</msg>
<msg time='2019-08-23T07:11:36.990-04:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='RAC-01'
 host_addr='192.168.119.12' pid='17312'>
 <txt>23-AUG-2019 07:11:36 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=RAC-01)(USER=grid))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=203424000)) * status * 0
 </txt>
</msg>
<msg time='2019-08-23T07:12:36.985-04:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='RAC-01'
 host_addr='192.168.119.12' pid='17312'>
 <txt>23-AUG-2019 07:12:36 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=RAC-01)(USER=grid))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=203424000)) * status * 0
 </txt>
</msg>
<msg time='2019-08-23T07:13:36.992-04:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='RAC-01'
 host_addr='192.168.119.12' pid='17312'>
 <txt>23-AUG-2019 07:13:36 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=RAC-01)(USER=grid))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=203424000)) * status * 0
 </txt>
</msg>
<msg time='2019-08-23T07:14:36.989-04:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='RAC-01'
 host_addr='192.168.119.12' pid='17312'>
 <txt>23-AUG-2019 07:14:36 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=RAC-01)(USER=grid))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=203424000)) * status * 0
 </txt>
</msg>
<msg time='2019-08-23T07:15:36.987-04:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='RAC-01'
 host_addr='192.168.119.12' pid='17312'>
 <txt>23-AUG-2019 07:15:36 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=RAC-01)(USER=grid))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=203424000)) * status * 0
 </txt>
</msg>
<msg time='2019-08-23T07:16:05.417-04:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='RAC-01'
 host_addr='192.168.119.12' pid='17312'>
 <txt>23-AUG-2019 07:16:05 * service_update * Rac1* 0
 </txt>
</msg>
<msg time='2019-08-23T07:16:36.991-04:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='RAC-01'
 host_addr='192.168.119.12' pid='17312'>
 <txt>23-AUG-2019 07:16:36 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=RAC-01)(USER=grid))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=203424000)) * status * 0
 </txt>
</msg>

Best Answer

  • With Modified JDBC parameter Both WAS & Jetty are working as expected and DB connection count are balanced on the both nodes.

On RAC nodes, i have changed the tnsnames.ora -> to include the fully qualified global service name (including domain name) and it fixed sql connectivity issues.