Oracle RAC service/instance mapping question

From: Harvinder Singh <Harvinder.Singh_at_MetraTech.com>
Date: Thu, 17 Apr 2008 11:21:07 -0400
Message-ID: <D6424CD4C8A3C044BBC49877ED51C51804E02D0F@ex2003.metratech.com>


Hi,

We have a 2 node cluster Oracle 10g on Linux AS4 in a test environment. Database Name : Test
Service: Both RAC1 and RAC2 as primary
Service1 : RAC1 is primary and RAC2 is failover Service2 : RAC2 is primary and RAC1 is failover Since we are using distributed transactions we are using load_balancing and service_name (service1 and service2) in tnsnames.ora on application machines.
Application server1 tnsnames entry:
test =
  (DESCRIPTION =
    (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1521))
    )
    (CONNECT_DATA =
(SERVICE_NAME = service1)
(fail_over = on)

    )
  )

Application server2 tnsnames entry:
test =
  (DESCRIPTION =
    (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
    )
    (CONNECT_DATA =
(SERVICE_NAME = service2)
(fail_over = on)

    )
  )

We have setup both the remote and local listener parameters and everything looks fine and lsnrctl status shows the following information:
Service "service1" has 1 instance(s).
  Instance "rac1", status READY, has 1 handler(s) for this service... Service "service2" has 1 instance(s).
  Instance "rac2", status READY, has 2 handler(s) for this service...

When we shutdown the rac2, service2 is serviced by rac1 and we can see following in the listener status:
Service "service1" has 1 instance(s).
  Instance "rac1", status READY, has 1 handler(s) for this service... Service "service2" has 1 instance(s).
  Instance "rac1", status READY, has 1 handler(s) for this service...

Now all the connections from both application machines goes to instance "RAC1", Now when the RAC2 is started again all the connections are still going to RAC1 and we are not using resources of RAC2. What command or steps we need to do to make sure that when RAC2 server is up all the request for service2 connections starts going to RAC2 and not RAC1?

Thanks
--Harvinder  

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Apr 17 2008 - 10:21:07 CDT

Original text of this message