I have a Oracle 11gR2 2-node RAC setup. One of the applications that runs on this RAC setup is a DML with large number of INSERT. I would like to restrict this application to only one node, so as to avoid the Cluster waits. With that in mind, I created a Service and assigned Node_1 in the preferred list and Node_2 in the available list -
srvctl add service -d orcl -s orcl_ins -r Node_1 -a Node_2
The jdbc application connect string is setup as -
public static String dburl = "jdbc oracle:thin (DESCRIPTION_LIST=(LOAD_BALANCE=off)(FAILOVER=on)(DESCRIPTION=(CONNECT_TIMEOUT=20)(TRANSPORT_CONNECT_TIMEOUT=10)(RETRY_COUNT=3)(ADDRESS_LIST=(LOAD_BALANCE=off)(ADDRESS=(PROTOCOL=TCP)(HOST=host01-scan)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl_ins))))";
But the application when it runs still ends up with connections (equal number) on both nodes. Can you please look at this setting and help me with why the instance affinity with the service_name defined is not working?
Check the services parameter on all instances you should only have the service parameter set on the preferred instance. If the service parameter is set on all instances it will register with the listener and the connection can connect to any instance. The service parameter will automatically be changed to the available instance during a failover.