I recently wrote a blog on how to separate database traffic on an ODA using KVM Database Systems on different VLAN : Creating KVM Database System on separate VLAN network on ODA
As we could see there is pros and cons. Another way would be to have the database created on the Bare Metal itself and using listeners running on individual network interfaces. Let’s try this! Of course this solution is more suitable for Standard Edition database as we won’t have to reduce the cores. For Enterprise Edition, the KVM Database Systems will help us mitigating the impact of the core licensing by doing hard partitioning.
Current configuration
On my ODA I have got 2 existing databases :
[root@dbi-oda-x8 ~]# odacli list-databases ID DB Name DB Type DB Version CDB Class Shape Storage Status DbHomeID ---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ---------------------------------------- 42656d90-e18e-4325-a69a-13d3a6b58710 DBITST SI 19.13.0.0.211019 true OLTP odb1 ASM CONFIGURED 5e7a146a-c18d-427b-a206-e532ec9907cf a2193c80-d004-4295-847e-ec8277952bd7 DBTAG38 SI 19.13.0.0.211019 false OLTP odb1 ASM CONFIGURED 5e7a146a-c18d-427b-a206-e532ec9907cf
I have the default listener and the listener for ASM :
grid@dbi-oda-x8:/home/grid/ [+ASM1] ps -ef | grep -i [t]nslsnr grid 11937 1 0 14:01 ? 00:00:08 /u01/app/19.13.0.0/grid/bin/tnslsnr ASMNET1LSNR_ASM -no_crs_notify -inherit grid 12096 1 0 14:01 ? 00:00:00 /u01/app/19.13.0.0/grid/bin/tnslsnr LISTENER -no_crs_notify -inherit
We are going to use 2 listeners, one listener for each databases. Let’s use the default LISTENER for the DBITST database and create a new one, LISTENER_VLAN38, that will listen on our VLAN38 network interface.
Check default LISTENER configuration
The default LISTENER configuration is the following :
grid@dbi-oda-x8:/home/grid/ [+ASM1] srvctl config listener -listener listener Name: LISTENER Type: Database Listener Network: 1, Owner: grid Home: <CRS home> End points: TCP:1521 Listener is enabled. Listener is individually enabled on nodes: Listener is individually disabled on nodes:
As we can see the default LISTENER is listening on the default network number 1 and port 1521.
The VIP is existing on the network 1 :
[root@dbi-oda-x8 bin]# pwd /u01/app/19.13.0.0/grid/bin [root@dbi-oda-x8 bin]# ./srvctl config vip -node dbi-oda-x8 VIP exists: network number 1, hosting node dbi-oda-x8 VIP Name: dbi-oda-x8-vip.dbi-lab.ch VIP IPv4 Address: 10.36.0.241 VIP IPv6 Address: VIP is enabled. VIP is individually enabled on nodes: VIP is individually disabled on nodes: [root@dbi-oda-x8 bin]#
Let’s check which network that one is :
grid@dbi-oda-x8:/home/grid/ [+ASM1] srvctl config network Network 1 exists Subnet IPv4: 10.36.0.0/255.255.255.0/pubnet, static Subnet IPv6: Ping Targets: Network is enabled Network is individually enabled on nodes: Network is individually disabled on nodes:
So our default LISTENER is listening on our pubnet network :
grid@dbi-oda-x8:/home/grid/ [+ASM1] ip addr sh pubnet 9: pubnet: mtu 1500 qdisc noqueue state UP group default qlen 1000 link/ether 3c:fd:fe:92:80:18 brd ff:ff:ff:ff:ff:ff inet 10.36.0.241/24 brd 10.36.0.255 scope global pubnet valid_lft forever preferred_lft forever
The same can be seen with lsnrctl command :
grid@dbi-oda-x8:/home/grid/ [+ASM1] lsnrctl status listener LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-FEB-2022 21:34:10 Copyright (c) 1991, 2021, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 17-FEB-2022 14:01:19 Uptime 0 days 7 hr. 32 min. 51 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/19.13.0.0/grid/network/admin/listener.ora Listener Log File /u01/app/grid/diag/tnslsnr/dbi-oda-x8/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.36.0.241)(PORT=1521))) Services Summary... Service "+APX" has 1 instance(s). Instance "+APX1", status READY, has 1 handler(s) for this service... Service "+ASM" has 1 instance(s). Instance "+ASM1", status READY, has 1 handler(s) for this service... Service "+ASM_DATA" has 1 instance(s). Instance "+ASM1", status READY, has 1 handler(s) for this service... Service "+ASM_RECO" has 1 instance(s). Instance "+ASM1", status READY, has 1 handler(s) for this service... Service "DBITST.dbi-lab.ch" has 1 instance(s). Instance "DBITST", status READY, has 1 handler(s) for this service... Service "DBITSTXDB.dbi-lab.ch" has 1 instance(s). Instance "DBITST", status READY, has 1 handler(s) for this service... Service "DBTAG38XDB.dbi-lab.ch" has 1 instance(s). Instance "DBTAG38", status READY, has 1 handler(s) for this service... Service "DBTAG38_SITE1.dbi-lab.ch" has 1 instance(s). Instance "DBTAG38", status READY, has 1 handler(s) for this service... Service "d6a0adf9be85a039e053f100240a4f65.dbi-lab.ch" has 1 instance(s). Instance "DBITST", status READY, has 1 handler(s) for this service... Service "dbitstpdb.dbi-lab.ch" has 1 instance(s). Instance "DBITST", status READY, has 1 handler(s) for this service... The command completed successfully grid@dbi-oda-x8:/home/grid/ [+ASM1]
Both our databases DBITST and DBTAG38 are so far registered on the default listener.
Configure an additional network interface in the clusterware
We will now add a new network interface for further use in a new listener. This network interface will be part of the VLAN38 network :
[root@dbi-oda-x8 ~]# ip addr sh brtagged38 12: brtagged38: mtu 1500 qdisc noqueue state UP group default qlen 1000 link/ether 52:54:00:3b:dc:76 brd ff:ff:ff:ff:ff:ff inet 10.38.0.10/24 brd 10.38.0.255 scope global brtagged38 valid_lft forever preferred_lft forever
To add a new network we need to be login as the root user :
[root@dbi-oda-x8 ~]# cd /u01/app/19.13.0.0/grid/bin/ [root@dbi-oda-x8 bin]# ./srvctl add network -netnum 2 -subnet 10.38.0.0/255.255.255.0
We can check and see that now we have 2 networks. Network 1 part of pubnet and Network 2 part of brtagged38 :
[root@dbi-oda-x8 bin]# ./srvctl config network Network 1 exists Subnet IPv4: 10.36.0.0/255.255.255.0/pubnet, static Subnet IPv6: Ping Targets: Network is enabled Network is individually enabled on nodes: Network is individually disabled on nodes: Network 2 exists Subnet IPv4: 10.38.0.0/255.255.255.0/, static Subnet IPv6: Ping Targets: Network is enabled Network is individually enabled on nodes: Network is individually disabled on nodes:
Create the VIP for the network 2
[root@dbi-oda-x8 bin]# pwd /u01/app/19.13.0.0/grid/bin [root@dbi-oda-x8 bin]# ./srvctl add vip -node dbi-oda-x8 -netnum 2 -address 10.38.0.10/255.255.255.0 [root@dbi-oda-x8 bin]#
Check both VIP (one for each network) :
[root@dbi-oda-x8 bin]# ./srvctl config vip -node dbi-oda-x8 VIP exists: network number 1, hosting node dbi-oda-x8 VIP Name: dbi-oda-x8-vip.dbi-lab.ch VIP IPv4 Address: 10.36.0.241 VIP IPv6 Address: VIP is enabled. VIP is individually enabled on nodes: VIP is individually disabled on nodes: VIP exists: network number 2, hosting node dbi-oda-x8 VIP IPv4 Address: 10.38.0.10 VIP IPv6 Address: VIP is enabled. VIP is individually enabled on nodes: VIP is individually disabled on nodes: [root@dbi-oda-x8 bin]#
Create now the second LISTENER_VLAN38 listener
Let’s create the second listener LISTENER_VLAN38 to listen on Network 2 on port 1521 :
grid@dbi-oda-x8:/home/grid/ [+ASM1] srvctl add listener -listener LISTENER_VLAN38 -netnum 2 -endpoints "TCP:1521" PRCN-2061 : Failed to add listener ora.LISTENER_VLAN38.lsnr PRCN-2065 : Ports 1521 are not available on the nodes given PRCN-2067 : Port 1521 is not available on nodes: dbi-oda-x8.dbi-lab.ch
We are getting this error because, for preventing port 1521 to be assigned to another listener by mistake, the clusterware won’t allow port 1521 to be reassigned. Neither it is the default network or not. We are then going to use the skip option.
grid@dbi-oda-x8:/home/grid/ [+ASM1] srvctl add listener -listener LISTENER_VLAN38 -netnum 2 -endpoints "TCP:1521" -skip grid@dbi-oda-x8:/home/grid/ [+ASM1]
Let’s check our listeners :
grid@dbi-oda-x8:/home/grid/ [+ASM1] srvctl config listener Name: LISTENER Type: Database Listener Network: 1, Owner: grid Home: <CRS home> End points: TCP:1521 Listener is enabled. Listener is individually enabled on nodes: Listener is individually disabled on nodes: Name: LISTENER_VLAN38 Type: Database Listener Network: 2, Owner: grid Home: <CRS home> End points: TCP:1521 Listener is enabled. Listener is individually enabled on nodes: Listener is individually disabled on nodes:
We can see that we are now having our both listeners LISTENER and LISTENER_VLAN38 running respectively on network 1 port 1521 and network 2 port 1521.
As we can see the new listener have not been already started :
grid@dbi-oda-x8:/home/grid/ [+ASM1] ps -ef | grep [t]nslsnr grid 11937 1 0 14:01 ? 00:00:09 /u01/app/19.13.0.0/grid/bin/tnslsnr ASMNET1LSNR_ASM -no_crs_notify -inherit grid 12096 1 0 14:01 ? 00:00:00 /u01/app/19.13.0.0/grid/bin/tnslsnr LISTENER -no_crs_notify -inherit grid@dbi-oda-x8:/home/grid/ [+ASM1] srvctl status listener Listener LISTENER is enabled Listener LISTENER is running on node(s): dbi-oda-x8 Listener LISTENER_VLAN38 is enabled Listener LISTENER_VLAN38 is not running
Let’s start it !
grid@dbi-oda-x8:/home/grid/ [+ASM1] srvctl start listener -listener LISTENER_VLAN38 grid@dbi-oda-x8:/home/grid/ [+ASM1] ps -ef | grep [t]nslsnr grid 1505 1 0 22:51 ? 00:00:00 /u01/app/19.13.0.0/grid/bin/tnslsnr LISTENER_VLAN38 -no_crs_notify -inherit grid 11937 1 0 14:01 ? 00:00:09 /u01/app/19.13.0.0/grid/bin/tnslsnr ASMNET1LSNR_ASM -no_crs_notify -inherit grid 12096 1 0 14:01 ? 00:00:00 /u01/app/19.13.0.0/grid/bin/tnslsnr LISTENER -no_crs_notify -inherit grid@dbi-oda-x8:/home/grid/ [+ASM1] srvctl status listener Listener LISTENER is enabled Listener LISTENER is running on node(s): dbi-oda-x8 Listener LISTENER_VLAN38 is enabled Listener LISTENER_VLAN38 is running on node(s): dbi-oda-x8 grid@dbi-oda-x8:/home/grid/ [+ASM1]
As we can see both our listeners are up and running. For now all databases are both registered on the default LISTENER :
grid@dbi-oda-x8:/home/grid/ [+ASM1] lsnrctl status LISTENER LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-FEB-2022 22:55:11 Copyright (c) 1991, 2021, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 17-FEB-2022 14:01:19 Uptime 0 days 8 hr. 53 min. 52 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/19.13.0.0/grid/network/admin/listener.ora Listener Log File /u01/app/grid/diag/tnslsnr/dbi-oda-x8/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.36.0.241)(PORT=1521))) Services Summary... Service "+APX" has 1 instance(s). Instance "+APX1", status READY, has 1 handler(s) for this service... Service "+ASM" has 1 instance(s). Instance "+ASM1", status READY, has 1 handler(s) for this service... Service "+ASM_DATA" has 1 instance(s). Instance "+ASM1", status READY, has 1 handler(s) for this service... Service "+ASM_RECO" has 1 instance(s). Instance "+ASM1", status READY, has 1 handler(s) for this service... Service "DBITST.dbi-lab.ch" has 1 instance(s). Instance "DBITST", status READY, has 1 handler(s) for this service... Service "DBITSTXDB.dbi-lab.ch" has 1 instance(s). Instance "DBITST", status READY, has 1 handler(s) for this service... Service "DBTAG38XDB.dbi-lab.ch" has 1 instance(s). Instance "DBTAG38", status READY, has 1 handler(s) for this service... Service "DBTAG38_SITE1.dbi-lab.ch" has 1 instance(s). Instance "DBTAG38", status READY, has 1 handler(s) for this service... Service "d6a0adf9be85a039e053f100240a4f65.dbi-lab.ch" has 1 instance(s). Instance "DBITST", status READY, has 1 handler(s) for this service... Service "dbitstpdb.dbi-lab.ch" has 1 instance(s). Instance "DBITST", status READY, has 1 handler(s) for this service... The command completed successfully grid@dbi-oda-x8:/home/grid/ [+ASM1] lsnrctl status LISTENER_VLAN38 LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-FEB-2022 22:55:17 Copyright (c) 1991, 2021, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_VLAN38))) STATUS of the LISTENER ------------------------ Alias LISTENER_VLAN38 Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 17-FEB-2022 22:51:41 Uptime 0 days 0 hr. 3 min. 36 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/19.13.0.0/grid/network/admin/listener.ora Listener Log File /u01/app/grid/diag/tnslsnr/dbi-oda-x8/listener_vlan38/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_VLAN38))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.38.0.10)(PORT=1521))) The listener supports no services The command completed successfully grid@dbi-oda-x8:/home/grid/ [+ASM1]
The listener.ora configuration file includes both listeners :
grid@dbi-oda-x8:/home/grid/ [+ASM1] cat /u01/app/19.13.0.0/grid/network/admin/listener.ora LISTENER_VLAN38=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_VLAN38)))) # line added by Agent LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM)))) # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_VLAN38=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_LISTENER_VLAN38=SUBNET # line added by Agent grid@dbi-oda-x8:/home/grid/ [+ASM1]
Configure both listeners’ entries in the tnsnames.ora file
These entries will be used to easily configure local listener parameter.
Both databases share the same ORACLE_HOME. So the following both entries will be added to the same tnsnames.ora file :
LISTENER_DBITST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.36.0.241)(PORT = 1521)) LISTENER_DBTAG38 = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.38.0.10)(PORT = 1521))
Update tnsnames.ora file accordingly :
oracle@dbi-oda-x8:/home/oracle/ [DBITST] cd $TNS_ADMIN oracle@dbi-oda-x8:/u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_1/network/admin/ [DBITST] vi tnsnames.ora oracle@dbi-oda-x8:/u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_1/network/admin/ [DBITST] cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. DBITST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.36.0.241)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DBITST.dbi-lab.ch) ) ) LISTENER_DBITST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.36.0.241)(PORT = 1521)) DBTAG38_SITE1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.38.0.10)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DBTAG38_SITE1.dbi-lab.ch) ) ) LISTENER_DBTAG38 = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.38.0.10)(PORT = 1521)) oracle@dbi-oda-x8:/u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_1/network/admin/ [DBITST]
Configure local_listener for DBITST database and register database in LISTENER
oracle@dbi-oda-x8:/home/oracle/ [DBITST] sqh SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 17 23:11:45 2022 Version 19.13.0.0.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.13.0.0.0 SQL> set tab off SQL> show parameter local_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string -oracle-none- SQL> alter system set local_listener=LISTENER_DBITST scope=both; System altered. SQL> show parameter local_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string LISTENER_DBITST SQL> SQL> alter system register; System altered.
Configure local_listener for DBTAG38 database and register database in LISTENER_DBTAG38
oracle@dbi-oda-x8:/home/oracle/ [DBTAG38] sqh SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 17 23:15:19 2022 Version 19.13.0.0.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.13.0.0.0 SQL> set tab off SQL> show parameter local_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string -oracle-none- SQL> alter system set local_listener=LISTENER_DBTAG38 scope=both; System altered. SQL> show parameter local_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string LISTENER_DBTAG38 SQL> alter system register; System altered.
Update listener_networks for DBTAG38 database to match second network
oracle@dbi-oda-x8:/home/oracle/ [DBTAG38] sqh SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 17 23:26:44 2022 Version 19.13.0.0.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.13.0.0.0 SQL> set tab off SQL> show parameter listener_networks NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ listener_networks string ((NAME=net1)(LOCAL_LISTENER=(D ESCRIPTION=(ADDRESS=(PROTOCOL= TCP)(HOST=10.36.0.241)(PORT=15 21))))) SQL> alter system set listener_networks="((NAME=net2)(LOCAL_LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.38.0.10)(PORT=1521)))))" scope=both; System altered. SQL> show parameter listener_networks NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ listener_networks string ((NAME=net2)(LOCAL_LISTENER=(D ESCRIPTION=(ADDRESS=(PROTOCOL= TCP)(HOST=10.38.0.10)(PORT=152 1))))) SQL>
Check LISTENER registration
grid@dbi-oda-x8:/home/grid/ [+ASM1] lsnrctl status LISTENER | grep -iE 'DBITST|DBTAG38' Service "DBITST.dbi-lab.ch" has 1 instance(s). Instance "DBITST", status READY, has 2 handler(s) for this service... Service "DBITSTXDB.dbi-lab.ch" has 1 instance(s). Instance "DBITST", status READY, has 1 handler(s) for this service... Instance "DBITST", status READY, has 2 handler(s) for this service... Service "dbitstpdb.dbi-lab.ch" has 1 instance(s). Instance "DBITST", status READY, has 2 handler(s) for this service... grid@dbi-oda-x8:/home/grid/ [+ASM1]
Only DBITST database is registered on the default LISTENER.
Check LISTENER_VLAN38 registration
grid@dbi-oda-x8:/home/grid/ [+ASM1] lsnrctl status LISTENER_VLAN38 | grep -iE 'DBITST|DBTAG38' Service "DBTAG38XDB.dbi-lab.ch" has 1 instance(s). Instance "DBTAG38", status READY, has 1 handler(s) for this service... Service "DBTAG38_SITE1.dbi-lab.ch" has 1 instance(s). Instance "DBTAG38", status READY, has 2 handler(s) for this service... grid@dbi-oda-x8:/home/grid/ [+ASM1]
Only DBTAG38 database is registered on the listener LISTENER_VLAN38
Remote connection on database DBITST
From my laptop I can reach directly the DBITST database using pubnet network.
Remote connection on database DBTAG38
From my laptop I can reach directly the DBTAG38 database using brtagged38 network.