Add New Interface for Oracle DB

listeneroracleoracle-11g-r2

I believe there are similar question about this. But I need more explanation about my plan.

I have an 11g Oracle database running with one listener on port 1522. And currently using one interface (one IP for the server). Some client using another segment of IP, which always use router to communicate with server. Every morning, my router get heavy load because some different segment user create connection to database's IP.

From this situation, I want to add new interface for the server and assigned same IP segment for those client. From netstat I assume that no need extra configuration from oracle side. Here is the netstat result.

enter image description here

I will only add the IP address from OS side. And change the tnsnames.ora of client to connect to new IP address of server.

Is my scenario will work well? Or will I need to reconfigure the listener and database to work with my situation?

Best Answer

You need to add that new IP in your listener.ora file and modify tnsnames.ora file to load balance the connection requests to two different IPs.

Let's say I have database server and one listener is serving at 192.168.30.133/Port 1522. Next, I want to add new interface with IP 192.168.30.144.

Then I need to add the second IP address to my listener.ora file as shown below.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.30.133)(PORT = 1522))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.30.144)(PORT = 1524))
    )
  )

In order to balance the load of client connections between these two IP address you need to modify tnsnames.ora file at client site as shown below.

 ORAPDB1 =
  (DESCRIPTION =
   (ADDRESS_LIST =
    (LOAD_BALANCE = on)
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.30.133)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.30.144)(PORT = 1524))
   )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orapdb1)
    )
  )

Let's try to connect to the database and see whether the connection requests is distributed to these IPs or not.

[oracle@ora12c trace]$ lsnrctl status listener1

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 22-FEB-2017 10:46:07

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.30.133)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     listener1
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                22-FEB-2017 10:44:36
Uptime                    0 days 0 hr. 1 min. 31 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ora12c/listener1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.30.133)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.30.144)(PORT=1524)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ora12c.dba.com)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.1.0/db_1/admin/oracdb/xdb_wallet))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ora12c.dba.com)(PORT=5501))(Security=(my_wallet_directory=/u01/app/oracle/admin/oracdb/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "oracdb" has 1 instance(s).
  Instance "oracdb", status READY, has 1 handler(s) for this service...
Service "oracdbXDB" has 1 instance(s).
  Instance "oracdb", status READY, has 1 handler(s) for this service...
Service "orapdb1" has 1 instance(s).
  Instance "oracdb", status READY, has 1 handler(s) for this service...
The command completed successfully

Lets try to connect to the database.

[oracle@ora12c trace]$ sqlplus jay/jay@orapdb1 

SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 22 10:47:14 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Wed Feb 22 2017 10:25:31 +05:45

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL>

Now lets check which IP address was chosen to server the request.

Go to $ORACLE_BASE/diag/tnslsnr/DB_NAME/listener1/trace.

[oracle@ora12c]$ tail -f listener.log
22-FEB-2017 10:47:14 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orapdb1)(CID=(PROGRAM=sqlplus)(HOST=ora12c.dba.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.30.144)(PORT=21140)) * establish * orapdb1 * 0

We can see that 192.168.30.144 was used to serve the request.

Let's log out and try again to connect to the database.

[oracle@ora12c trace]$ sqlplus jay/jay@orapdb1 

SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 22 10:47:57 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Wed Feb 22 2017 10:47:14 +05:45

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

Listener.log

[oracle@ora12c]$ tail -f listener.log
22-FEB-2017 10:47:57 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orapdb1)(CID=(PROGRAM=sqlplus)(HOST=ora12c.dba.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.30.133)(PORT=37185)) * establish * orapdb1 * 0

Now we can see 192.168.30.133 is used.

Note: As @Colin 'T Hart said. You can use same port number for both IPs.

Related Question