Monday 7 February 2011

Single Oracle listener on multiple networks

I had the requirement today to move the application connection to our production database over to the SAN network, rather than over our internal network in the hope of seeing increased performance. I also wanted to keep the listener on the internal network, as our SAN network is segregated from the rest of the network, therefore by keeping the internal network connection it can be used to connect by the DBAs for administrative purposes.

I started by googling for the solution but found this surprisingly difficult to find the solution! Therefore I am now blogging about it!

To start with we ensure that both ip addresses for our Oracle server are in the /etc/hosts file, eg:

1.2.3.4 host_net1
2.3.4.5 host_net2

We then take our existing listener.ora file:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS =
(PROTOCOL = TCP)(HOST = host_net1)(PORT = 1521))
      )
      (ADDRESS_LIST =
        (ADDRESS =
(PROTOCOL = IPC)(KEY = EXTPROC))
      )
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME =
/home/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME =TESTSID)
      (ORACLE_HOME =/home/oracle/product/10.2.0/db_1)
      (SID_NAME =TESTSID)
    )
  )

Here we have a listener listening on host host_net1 on port 1521, for database sid TESTSID.

We now want to add the listener listening on the other ip address, host_net2. As this is another connection, we will need to change the port as well, so we will therefore choose port 1526, as this is generally recognised as the alternative listener port.

To edit, the listener.ora we need to locate the port line in the existing file, ie:

(PROTOCOL = TCP)(HOST = host_net1)(PORT = 1521))

And the copy and paste this line, replacing the HOST and PORT, so our completed file will now look like this:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS =
(PROTOCOL = TCP)(HOST = host_net1)(PORT = 1521))
(PROTOCOL = TCP)(HOST = host_net2)(PORT = 1526))
      )
      (ADDRESS_LIST =
        (ADDRESS =
(PROTOCOL = IPC)(KEY = EXTPROC))
      )
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME =
/home/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME =TESTSID)
      (ORACLE_HOME =/home/oracle/product/10.2.0/db_1)
      (SID_NAME =TESTSID)
    )
  )

Finally save the file and then stop and start the listener in the usual manner.

You should then be able to confirm you are able to connect to the database via sqlnet from both networks.

No comments:

Post a Comment