Re: Oracle 10g RAC load-balancing configuration

From: <sudhanshu.goswami_at_gmail.com>
Date: 27 Apr 2006 00:14:18 -0700
Message-ID: <1146122058.721679.197950@e56g2000cwe.googlegroups.com>

Thanks for your time guys. I tried quite a few things in the pfile - single quotes, without quotes, double quotes - finally I just took the entry from the tnsnames.ora and pasted it there in the pfile itself. So the entries in the new pfile look like this:

tpcc1.local_listener = '(ADDRESS =
(PROTOCOL=TCP)(HOST=192.168.2.9)(PORT=1521))' tpcc2.local_listener = '(ADDRESS =
(PROTOCOL=TCP)(HOST=192.168.2.10)(PORT=1521))' remote_listener = '(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.9) (PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.10)(PORT = 1521)))' After this the database came up without problems. Still connection load balancing was not working. So I figured it had to do something with tnsnames.ora. I rewrote the tnsnames.ora file as:

TPCC.WORLD =
(DESCRIPTION =

   (load_balance = on)
   (failover = on)
   (ADDRESS_LIST =


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

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

)
(CONNECT_DATA = (SERVICE_NAME = tpcc)) )

TPCC1.WORLD =
  (DESCRIPTION =
   (load_balance = on)
   (failover = on)

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.9)(PORT = 1521))
    (CONNECT_DATA =
(SERVICE_NAME = tpcc)
(INSTANCENAME = tpcc1)
(SERVER = DEDICATED)

    )
  )

TPCC2.WORLD =
  (DESCRIPTION =
   (load_balance = on)
   (failover = on)

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.10)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = tpcc)
(INSTANCENAME = tpcc2)
(SERVER = DEDICATED)

)

Restarting the listeners the new status was like this:

> lsnrctl services LISTENER_TPCC1

LSNRCTL for HPUX: Version 10.1.0.2.0 - Production on 27-APR-2006 11:54:26

Connecting to (DESCRIPTION=(address=(protocol=ipc)(key=extproc))) Services Summary...
Service "plsextproc" has 1 instance(s).
Instance "plsextproc", status UNKNOWN, has 1 handler(s) for this service...

Handler(s):

      "DEDICATED" established:0 refused:0
         LOCAL SERVER

Service "tpcc" has 2 instance(s).
Instance "tpcc1", status READY, has 2 handler(s) for this service... Handler(s):

      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.9)(PORT=1521))
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER

Instance "tpcc2", status READY, has 1 handler(s) for this service... Handler(s):

      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.10)(PORT=1521))

The command completed successfully

After checking several connections using: sqlplus "tpcc_at_tpcc/tpcc"

I could see the connections were being load balanced properly between the two nodes.
Thanks a lot for your time guys! Seems the problem had to do with entries in tnsnames.ora and pfile.

Sudhanshu

Received on Thu Apr 27 2006 - 02:14:18 CDT