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 :

[[email protected] ~]# 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 :

[email protected]:/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 :

[email protected]:/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 :

[[email protected] bin]# pwd
/u01/app/19.13.0.0/grid/bin
[[email protected] 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:
[[email protected] bin]#


Let’s check which network that one is :

[email protected]:/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 :

[email protected]:/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 :

[email protected]:/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
[email protected]:/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 :

[[email protected] ~]# 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 :

[[email protected] ~]# cd /u01/app/19.13.0.0/grid/bin/
[[email protected] 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 :

[[email protected] 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

[[email protected] bin]# pwd
/u01/app/19.13.0.0/grid/bin
[[email protected] bin]# ./srvctl add vip -node dbi-oda-x8 -netnum 2 -address 10.38.0.10/255.255.255.0
[[email protected] bin]#


Check both VIP (one for each network) :

[[email protected] 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:
[[email protected] bin]#


Create now the second LISTENER_VLAN38 listener

Let’s create the second listener LISTENER_VLAN38 to listen on Network 2 on port 1521 :

[email protected]:/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.

[email protected]:/home/grid/ [+ASM1] srvctl add listener -listener LISTENER_VLAN38 -netnum 2 -endpoints "TCP:1521" -skip
[email protected]:/home/grid/ [+ASM1]


Let’s check our listeners :

[email protected]:/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 :

[email protected]:/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

[email protected]:/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 !

[email protected]:/home/grid/ [+ASM1] srvctl start listener -listener LISTENER_VLAN38

[email protected]:/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

[email protected]:/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
[email protected]:/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 :

[email protected]:/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

[email protected]:/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
[email protected]:/home/grid/ [+ASM1]


The listener.ora configuration file includes both listeners :

[email protected]:/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
[email protected]:/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 :

[email protected]:/home/oracle/ [DBITST] cd $TNS_ADMIN

[email protected]:/u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_1/network/admin/ [DBITST] vi tnsnames.ora

[email protected]:/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))


[email protected]:/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

[email protected]:/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

[email protected]:/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

[email protected]:/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

[email protected]:/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...
[email protected]:/home/grid/ [+ASM1]


Only DBITST database is registered on the default LISTENER.

Check LISTENER_VLAN38 registration

[email protected]:/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...
[email protected]:/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.



















Thumbnail [60x60]
by
Marc Wagner