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.