{"id":17125,"date":"2022-02-17T23:03:23","date_gmt":"2022-02-17T22:03:23","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/separating-database-traffic-on-an-oda\/"},"modified":"2022-05-31T16:35:20","modified_gmt":"2022-05-31T14:35:20","slug":"separating-database-traffic-on-an-oda","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/separating-database-traffic-on-an-oda\/","title":{"rendered":"Separating database traffic on an ODA"},"content":{"rendered":"

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<\/a>
\nAs 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.<\/p>\n

<\/p>\n

Current configuration<\/h3>\n

On my ODA I have got 2 existing databases :<\/p>\n

\n[root@dbi-oda-x8 ~]# odacli list-databases\n\nID                                       DB Name    DB Type  DB Version           CDB        Class    Shape    Storage    Status        DbHomeID\n---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ----------------------------------------\n42656d90-e18e-4325-a69a-13d3a6b58710     DBITST     SI       19.13.0.0.211019     true       OLTP     odb1     ASM        CONFIGURED   5e7a146a-c18d-427b-a206-e532ec9907cf\na2193c80-d004-4295-847e-ec8277952bd7     DBTAG38    SI       19.13.0.0.211019     false      OLTP     odb1     ASM        CONFIGURED   5e7a146a-c18d-427b-a206-e532ec9907cf\n<\/pre>\n
<\/br><\/p>\n
I have the default listener and the listener for ASM :<\/p>\n
\ngrid@dbi-oda-x8:\/home\/grid\/ [+ASM1] ps -ef | grep -i [t]nslsnr\ngrid     11937     1  0 14:01 ?        00:00:08 \/u01\/app\/19.13.0.0\/grid\/bin\/tnslsnr ASMNET1LSNR_ASM -no_crs_notify -inherit\ngrid     12096     1  0 14:01 ?        00:00:00 \/u01\/app\/19.13.0.0\/grid\/bin\/tnslsnr LISTENER -no_crs_notify -inherit\n<\/pre>\n
<\/br><\/p>\n
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.<\/p>\n
Check default LISTENER configuration<\/h3>\n
The default LISTENER configuration is the following :<\/p>\n
\ngrid@dbi-oda-x8:\/home\/grid\/ [+ASM1] srvctl config listener -listener listener\nName: LISTENER\nType: Database Listener\nNetwork: 1, Owner: grid\nHome: <CRS home>\nEnd points: TCP:1521\nListener is enabled.\nListener is individually enabled on nodes:\nListener is individually disabled on nodes:\n<\/pre>\n
<\/br><\/p>\n
As we can see the default LISTENER is listening on the default network number 1 and port 1521.<\/p>\n
The VIP is existing on the network 1 :<\/p>\n
\n[root@dbi-oda-x8 bin]# pwd\n\/u01\/app\/19.13.0.0\/grid\/bin\n[root@dbi-oda-x8 bin]# .\/srvctl config vip -node dbi-oda-x8\nVIP exists: network number 1, hosting node dbi-oda-x8\nVIP Name: dbi-oda-x8-vip.dbi-lab.ch\nVIP IPv4 Address: 10.36.0.241\nVIP IPv6 Address:\nVIP is enabled.\nVIP is individually enabled on nodes:\nVIP is individually disabled on nodes:\n[root@dbi-oda-x8 bin]#\n<\/pre>\n
<\/br><\/p>\n
Let’s check which network that one is :<\/p>\n
\ngrid@dbi-oda-x8:\/home\/grid\/ [+ASM1] srvctl config network\nNetwork 1 exists\nSubnet IPv4: 10.36.0.0\/255.255.255.0\/pubnet, static\nSubnet IPv6:\nPing Targets:\nNetwork is enabled\nNetwork is individually enabled on nodes:\nNetwork is individually disabled on nodes:\n<\/pre>\n
<\/br><\/p>\n
So our default LISTENER is listening on our pubnet network :<\/p>\n
\ngrid@dbi-oda-x8:\/home\/grid\/ [+ASM1] ip addr sh pubnet\n9: pubnet:  mtu 1500 qdisc noqueue state UP group default qlen 1000\n    link\/ether 3c:fd:fe:92:80:18 brd ff:ff:ff:ff:ff:ff\n    inet 10.36.0.241\/24 brd 10.36.0.255 scope global pubnet\n       valid_lft forever preferred_lft forever\n<\/pre>\n
<\/br><\/p>\n
The same can be seen with lsnrctl command :<\/p>\n
\ngrid@dbi-oda-x8:\/home\/grid\/ [+ASM1] lsnrctl status listener\n\nLSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-FEB-2022 21:34:10\n\nCopyright (c) 1991, 2021, Oracle.  All rights reserved.\n\nConnecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))\nSTATUS of the LISTENER\n------------------------\nAlias                     LISTENER\nVersion                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production\nStart Date                17-FEB-2022 14:01:19\nUptime                    0 days 7 hr. 32 min. 51 sec\nTrace Level               off\nSecurity                  ON: Local OS Authentication\nSNMP                      OFF\nListener Parameter File   \/u01\/app\/19.13.0.0\/grid\/network\/admin\/listener.ora\nListener Log File         \/u01\/app\/grid\/diag\/tnslsnr\/dbi-oda-x8\/listener\/alert\/log.xml\nListening Endpoints Summary...\n  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))\n  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.36.0.241)(PORT=1521)))\nServices Summary...\nService \"+APX\" has 1 instance(s).\n  Instance \"+APX1\", status READY, has 1 handler(s) for this service...\nService \"+ASM\" has 1 instance(s).\n  Instance \"+ASM1\", status READY, has 1 handler(s) for this service...\nService \"+ASM_DATA\" has 1 instance(s).\n  Instance \"+ASM1\", status READY, has 1 handler(s) for this service...\nService \"+ASM_RECO\" has 1 instance(s).\n  Instance \"+ASM1\", status READY, has 1 handler(s) for this service...\nService \"DBITST.dbi-lab.ch\" has 1 instance(s).\n  Instance \"DBITST\", status READY, has 1 handler(s) for this service...\nService \"DBITSTXDB.dbi-lab.ch\" has 1 instance(s).\n  Instance \"DBITST\", status READY, has 1 handler(s) for this service...\nService \"DBTAG38XDB.dbi-lab.ch\" has 1 instance(s).\n  Instance \"DBTAG38\", status READY, has 1 handler(s) for this service...\nService \"DBTAG38_SITE1.dbi-lab.ch\" has 1 instance(s).\n  Instance \"DBTAG38\", status READY, has 1 handler(s) for this service...\nService \"d6a0adf9be85a039e053f100240a4f65.dbi-lab.ch\" has 1 instance(s).\n  Instance \"DBITST\", status READY, has 1 handler(s) for this service...\nService \"dbitstpdb.dbi-lab.ch\" has 1 instance(s).\n  Instance \"DBITST\", status READY, has 1 handler(s) for this service...\nThe command completed successfully\ngrid@dbi-oda-x8:\/home\/grid\/ [+ASM1]\n<\/pre>\n
<\/br><\/p>\n
Both our databases DBITST and DBTAG38 are so far registered on the default listener.<\/p>\n
Configure an additional network interface in the clusterware<\/h3>\n
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 :<\/p>\n
\n[root@dbi-oda-x8 ~]# ip addr sh brtagged38\n12: brtagged38:  mtu 1500 qdisc noqueue state UP group default qlen 1000\n    link\/ether 52:54:00:3b:dc:76 brd ff:ff:ff:ff:ff:ff\n    inet 10.38.0.10\/24 brd 10.38.0.255 scope global brtagged38\n       valid_lft forever preferred_lft forever\n<\/pre>\n
<\/br><\/p>\n
To add a new network we need to be login as the root user :<\/p>\n
\n[root@dbi-oda-x8 ~]# cd \/u01\/app\/19.13.0.0\/grid\/bin\/\n[root@dbi-oda-x8 bin]# .\/srvctl add network -netnum 2 -subnet 10.38.0.0\/255.255.255.0\n<\/pre>\n
<\/br><\/p>\n
We can check and see that now we have 2 networks. Network 1 part of pubnet and Network 2 part of brtagged38 :<\/p>\n
\n[root@dbi-oda-x8 bin]# .\/srvctl config network\nNetwork 1 exists\nSubnet IPv4: 10.36.0.0\/255.255.255.0\/pubnet, static\nSubnet IPv6:\nPing Targets:\nNetwork is enabled\nNetwork is individually enabled on nodes:\nNetwork is individually disabled on nodes:\nNetwork 2 exists\nSubnet IPv4: 10.38.0.0\/255.255.255.0\/, static\nSubnet IPv6:\nPing Targets:\nNetwork is enabled\nNetwork is individually enabled on nodes:\nNetwork is individually disabled on nodes:\n<\/pre>\n
<\/br><\/p>\n
Create the VIP for the network 2<\/h3>\n
\n[root@dbi-oda-x8 bin]# pwd\n\/u01\/app\/19.13.0.0\/grid\/bin\n[root@dbi-oda-x8 bin]# .\/srvctl add vip -node dbi-oda-x8 -netnum 2 -address 10.38.0.10\/255.255.255.0\n[root@dbi-oda-x8 bin]#\n<\/pre>\n
<\/br><\/p>\n
Check both VIP (one for each network) :<\/p>\n
\n[root@dbi-oda-x8 bin]# .\/srvctl config vip -node dbi-oda-x8\nVIP exists: network number 1, hosting node dbi-oda-x8\nVIP Name: dbi-oda-x8-vip.dbi-lab.ch\nVIP IPv4 Address: 10.36.0.241\nVIP IPv6 Address:\nVIP is enabled.\nVIP is individually enabled on nodes:\nVIP is individually disabled on nodes:\nVIP exists: network number 2, hosting node dbi-oda-x8\nVIP IPv4 Address: 10.38.0.10\nVIP IPv6 Address:\nVIP is enabled.\nVIP is individually enabled on nodes:\nVIP is individually disabled on nodes:\n[root@dbi-oda-x8 bin]#\n<\/pre>\n
<\/br><\/p>\n
Create now the second LISTENER_VLAN38 listener<\/h3>\n
Let’s create the second listener LISTENER_VLAN38 to listen on Network 2 on port 1521 :<\/p>\n
\ngrid@dbi-oda-x8:\/home\/grid\/ [+ASM1] srvctl add listener -listener LISTENER_VLAN38 -netnum 2 -endpoints \"TCP:1521\"\nPRCN-2061 : Failed to add listener ora.LISTENER_VLAN38.lsnr\nPRCN-2065 : Ports 1521 are not available on the nodes given\nPRCN-2067 : Port 1521 is not available on nodes: dbi-oda-x8.dbi-lab.ch\n<\/pre>\n
<\/br><\/p>\n
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.<\/p>\n
\ngrid@dbi-oda-x8:\/home\/grid\/ [+ASM1] srvctl add listener -listener LISTENER_VLAN38 -netnum 2 -endpoints \"TCP:1521\" -skip\ngrid@dbi-oda-x8:\/home\/grid\/ [+ASM1]\n<\/pre>\n
<\/br><\/p>\n
Let’s check our listeners :<\/p>\n
\ngrid@dbi-oda-x8:\/home\/grid\/ [+ASM1] srvctl config listener\nName: LISTENER\nType: Database Listener\nNetwork: 1, Owner: grid\nHome: <CRS home>\nEnd points: TCP:1521\nListener is enabled.\nListener is individually enabled on nodes:\nListener is individually disabled on nodes:\nName: LISTENER_VLAN38\nType: Database Listener\nNetwork: 2, Owner: grid\nHome: <CRS home>\nEnd points: TCP:1521\nListener is enabled.\nListener is individually enabled on nodes:\nListener is individually disabled on nodes:\n<\/pre>\n
<\/br><\/p>\n
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.<\/p>\n
As we can see the new listener have not been already started :<\/p>\n
\ngrid@dbi-oda-x8:\/home\/grid\/ [+ASM1] ps -ef | grep [t]nslsnr\ngrid     11937     1  0 14:01 ?        00:00:09 \/u01\/app\/19.13.0.0\/grid\/bin\/tnslsnr ASMNET1LSNR_ASM -no_crs_notify -inherit\ngrid     12096     1  0 14:01 ?        00:00:00 \/u01\/app\/19.13.0.0\/grid\/bin\/tnslsnr LISTENER -no_crs_notify -inherit\n\ngrid@dbi-oda-x8:\/home\/grid\/ [+ASM1] srvctl status listener\nListener LISTENER is enabled\nListener LISTENER is running on node(s): dbi-oda-x8\nListener LISTENER_VLAN38 is enabled\nListener LISTENER_VLAN38 is not running\n<\/pre>\n
<\/br><\/p>\n
Let’s start it !<\/p>\n
\ngrid@dbi-oda-x8:\/home\/grid\/ [+ASM1] srvctl start listener -listener LISTENER_VLAN38\n\ngrid@dbi-oda-x8:\/home\/grid\/ [+ASM1] ps -ef | grep [t]nslsnr\ngrid      1505     1  0 22:51 ?        00:00:00 \/u01\/app\/19.13.0.0\/grid\/bin\/tnslsnr LISTENER_VLAN38 -no_crs_notify -inherit\ngrid     11937     1  0 14:01 ?        00:00:09 \/u01\/app\/19.13.0.0\/grid\/bin\/tnslsnr ASMNET1LSNR_ASM -no_crs_notify -inherit\ngrid     12096     1  0 14:01 ?        00:00:00 \/u01\/app\/19.13.0.0\/grid\/bin\/tnslsnr LISTENER -no_crs_notify -inherit\n\ngrid@dbi-oda-x8:\/home\/grid\/ [+ASM1] srvctl status listener\nListener LISTENER is enabled\nListener LISTENER is running on node(s): dbi-oda-x8\nListener LISTENER_VLAN38 is enabled\nListener LISTENER_VLAN38 is running on node(s): dbi-oda-x8\ngrid@dbi-oda-x8:\/home\/grid\/ [+ASM1]\n<\/pre>\n
<\/br><\/p>\n
As we can see both our listeners are up and running. For now all databases are both registered on the default LISTENER :<\/p>\n
\ngrid@dbi-oda-x8:\/home\/grid\/ [+ASM1] lsnrctl status LISTENER\n\nLSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-FEB-2022 22:55:11\n\nCopyright (c) 1991, 2021, Oracle.  All rights reserved.\n\nConnecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))\nSTATUS of the LISTENER\n------------------------\nAlias                     LISTENER\nVersion                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production\nStart Date                17-FEB-2022 14:01:19\nUptime                    0 days 8 hr. 53 min. 52 sec\nTrace Level               off\nSecurity                  ON: Local OS Authentication\nSNMP                      OFF\nListener Parameter File   \/u01\/app\/19.13.0.0\/grid\/network\/admin\/listener.ora\nListener Log File         \/u01\/app\/grid\/diag\/tnslsnr\/dbi-oda-x8\/listener\/alert\/log.xml\nListening Endpoints Summary...\n  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))\n  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.36.0.241)(PORT=1521)))\nServices Summary...\nService \"+APX\" has 1 instance(s).\n  Instance \"+APX1\", status READY, has 1 handler(s) for this service...\nService \"+ASM\" has 1 instance(s).\n  Instance \"+ASM1\", status READY, has 1 handler(s) for this service...\nService \"+ASM_DATA\" has 1 instance(s).\n  Instance \"+ASM1\", status READY, has 1 handler(s) for this service...\nService \"+ASM_RECO\" has 1 instance(s).\n  Instance \"+ASM1\", status READY, has 1 handler(s) for this service...\nService \"DBITST.dbi-lab.ch\" has 1 instance(s).\n  Instance \"DBITST\", status READY, has 1 handler(s) for this service...\nService \"DBITSTXDB.dbi-lab.ch\" has 1 instance(s).\n  Instance \"DBITST\", status READY, has 1 handler(s) for this service...\nService \"DBTAG38XDB.dbi-lab.ch\" has 1 instance(s).\n  Instance \"DBTAG38\", status READY, has 1 handler(s) for this service...\nService \"DBTAG38_SITE1.dbi-lab.ch\" has 1 instance(s).\n  Instance \"DBTAG38\", status READY, has 1 handler(s) for this service...\nService \"d6a0adf9be85a039e053f100240a4f65.dbi-lab.ch\" has 1 instance(s).\n  Instance \"DBITST\", status READY, has 1 handler(s) for this service...\nService \"dbitstpdb.dbi-lab.ch\" has 1 instance(s).\n  Instance \"DBITST\", status READY, has 1 handler(s) for this service...\nThe command completed successfully\n\ngrid@dbi-oda-x8:\/home\/grid\/ [+ASM1] lsnrctl status LISTENER_VLAN38\n\nLSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-FEB-2022 22:55:17\n\nCopyright (c) 1991, 2021, Oracle.  All rights reserved.\n\nConnecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_VLAN38)))\nSTATUS of the LISTENER\n------------------------\nAlias                     LISTENER_VLAN38\nVersion                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production\nStart Date                17-FEB-2022 22:51:41\nUptime                    0 days 0 hr. 3 min. 36 sec\nTrace Level               off\nSecurity                  ON: Local OS Authentication\nSNMP                      OFF\nListener Parameter File   \/u01\/app\/19.13.0.0\/grid\/network\/admin\/listener.ora\nListener Log File         \/u01\/app\/grid\/diag\/tnslsnr\/dbi-oda-x8\/listener_vlan38\/alert\/log.xml\nListening Endpoints Summary...\n  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_VLAN38)))\n  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.38.0.10)(PORT=1521)))\nThe listener supports no services\nThe command completed successfully\ngrid@dbi-oda-x8:\/home\/grid\/ [+ASM1]\n<\/pre>\n
<\/br><\/p>\n
The listener.ora configuration file includes both listeners :<\/p>\n
\ngrid@dbi-oda-x8:\/home\/grid\/ [+ASM1] cat \/u01\/app\/19.13.0.0\/grid\/network\/admin\/listener.ora\nLISTENER_VLAN38=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_VLAN38))))\t\t# line added by Agent\nLISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))\t\t# line added by Agent\nASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM))))\t\t# line added by Agent\nENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON\t\t# line added by Agent\nVALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET\t\t# line added by Agent\nENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON\t\t# line added by Agent\nVALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET\t\t# line added by Agent\nENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_VLAN38=ON\t\t# line added by Agent\nVALID_NODE_CHECKING_REGISTRATION_LISTENER_VLAN38=SUBNET\t\t# line added by Agent\ngrid@dbi-oda-x8:\/home\/grid\/ [+ASM1]\n<\/pre>\n
<\/br><\/p>\n
Configure both listeners’ entries in the tnsnames.ora file<\/h3>\n
These entries will be used to easily configure local listener parameter.<\/p>\n
Both databases share the same ORACLE_HOME. So the following both entries will be added to the same tnsnames.ora file :<\/p>\n
\nLISTENER_DBITST =\n  (ADDRESS = (PROTOCOL = TCP)(HOST = 10.36.0.241)(PORT = 1521))\n\nLISTENER_DBTAG38 =\n  (ADDRESS = (PROTOCOL = TCP)(HOST = 10.38.0.10)(PORT = 1521))\n<\/pre>\n
<\/br><\/p>\n
Update tnsnames.ora file accordingly :<\/p>\n
\noracle@dbi-oda-x8:\/home\/oracle\/ [DBITST] cd $TNS_ADMIN\n\noracle@dbi-oda-x8:\/u01\/app\/odaorahome\/oracle\/product\/19.0.0.0\/dbhome_1\/network\/admin\/ [DBITST] vi tnsnames.ora\n\noracle@dbi-oda-x8:\/u01\/app\/odaorahome\/oracle\/product\/19.0.0.0\/dbhome_1\/network\/admin\/ [DBITST] cat tnsnames.ora\n# tnsnames.ora Network Configuration File: \/u01\/app\/odaorahome\/oracle\/product\/19.0.0.0\/dbhome_1\/network\/admin\/tnsnames.ora\n# Generated by Oracle configuration tools.\n\nDBITST =\n  (DESCRIPTION =\n    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.36.0.241)(PORT = 1521))\n    (CONNECT_DATA =\n      (SERVER = DEDICATED)\n      (SERVICE_NAME = DBITST.dbi-lab.ch)\n    )\n  )\n\nLISTENER_DBITST =\n  (ADDRESS = (PROTOCOL = TCP)(HOST = 10.36.0.241)(PORT = 1521))\n\n\nDBTAG38_SITE1 =\n  (DESCRIPTION =\n    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.38.0.10)(PORT = 1521))\n    (CONNECT_DATA =\n      (SERVER = DEDICATED)\n      (SERVICE_NAME = DBTAG38_SITE1.dbi-lab.ch)\n    )\n  )\n\nLISTENER_DBTAG38 =\n  (ADDRESS = (PROTOCOL = TCP)(HOST = 10.38.0.10)(PORT = 1521))\n\n\noracle@dbi-oda-x8:\/u01\/app\/odaorahome\/oracle\/product\/19.0.0.0\/dbhome_1\/network\/admin\/ [DBITST]\n<\/pre>\n
<\/br><\/p>\n
Configure local_listener for DBITST database and register database in LISTENER<\/h3>\n
\noracle@dbi-oda-x8:\/home\/oracle\/ [DBITST] sqh\n\nSQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 17 23:11:45 2022\nVersion 19.13.0.0.0\n\nCopyright (c) 1982, 2021, Oracle.  All rights reserved.\n\n\nConnected to:\nOracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production\nVersion 19.13.0.0.0\n\nSQL> set tab off\nSQL> show parameter local_listener\n\nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\nlocal_listener                       string      -oracle-none-\n\nSQL> alter system set local_listener=LISTENER_DBITST scope=both;\n\nSystem altered.\n\nSQL> show parameter local_listener\n\nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\nlocal_listener                       string      LISTENER_DBITST\nSQL>\n\nSQL> alter system register;\n\nSystem altered.\n<\/pre>\n
<\/br><\/p>\n
Configure local_listener for DBTAG38 database and register database in LISTENER_DBTAG38<\/h3>\n
\noracle@dbi-oda-x8:\/home\/oracle\/ [DBTAG38] sqh\n\nSQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 17 23:15:19 2022\nVersion 19.13.0.0.0\n\nCopyright (c) 1982, 2021, Oracle.  All rights reserved.\n\n\nConnected to:\nOracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production\nVersion 19.13.0.0.0\n\nSQL> set tab off\nSQL> show parameter local_listener\n\nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\nlocal_listener                       string      -oracle-none-\n\nSQL> alter system set local_listener=LISTENER_DBTAG38 scope=both;\n\nSystem altered.\n\nSQL> show parameter local_listener\n\nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\nlocal_listener                       string      LISTENER_DBTAG38\n\nSQL> alter system register;\n\nSystem altered.\n<\/pre>\n
<\/br><\/p>\n
Update listener_networks for DBTAG38 database to match second network<\/h3>\n
\noracle@dbi-oda-x8:\/home\/oracle\/ [DBTAG38] sqh\n\nSQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 17 23:26:44 2022\nVersion 19.13.0.0.0\n\nCopyright (c) 1982, 2021, Oracle.  All rights reserved.\n\n\nConnected to:\nOracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production\nVersion 19.13.0.0.0\n\nSQL> set tab off\nSQL> show parameter listener_networks\n\nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\nlistener_networks                    string      ((NAME=net1)(LOCAL_LISTENER=(D\n                                                 ESCRIPTION=(ADDRESS=(PROTOCOL=\n                                                 TCP)(HOST=10.36.0.241)(PORT=15\n                                                 21)))))\n\nSQL> alter system set listener_networks=\"((NAME=net2)(LOCAL_LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.38.0.10)(PORT=1521)))))\" scope=both;\n\nSystem altered.\n\nSQL> show parameter listener_networks\n\nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\nlistener_networks                    string      ((NAME=net2)(LOCAL_LISTENER=(D\n                                                 ESCRIPTION=(ADDRESS=(PROTOCOL=\n                                                 TCP)(HOST=10.38.0.10)(PORT=152\n                                                 1)))))\nSQL>\n<\/pre>\n
<\/br><\/p>\n
Check LISTENER registration<\/h3>\n
\ngrid@dbi-oda-x8:\/home\/grid\/ [+ASM1] lsnrctl status LISTENER | grep -iE 'DBITST|DBTAG38'\nService \"DBITST.dbi-lab.ch\" has 1 instance(s).\n  Instance \"DBITST\", status READY, has 2 handler(s) for this service...\nService \"DBITSTXDB.dbi-lab.ch\" has 1 instance(s).\n  Instance \"DBITST\", status READY, has 1 handler(s) for this service...\n  Instance \"DBITST\", status READY, has 2 handler(s) for this service...\nService \"dbitstpdb.dbi-lab.ch\" has 1 instance(s).\n  Instance \"DBITST\", status READY, has 2 handler(s) for this service...\ngrid@dbi-oda-x8:\/home\/grid\/ [+ASM1]\n<\/pre>\n
<\/br><\/p>\n
Only DBITST database is registered on the default LISTENER.<\/p>\n
Check LISTENER_VLAN38 registration<\/h3>\n
\ngrid@dbi-oda-x8:\/home\/grid\/ [+ASM1] lsnrctl status LISTENER_VLAN38 | grep -iE 'DBITST|DBTAG38'\nService \"DBTAG38XDB.dbi-lab.ch\" has 1 instance(s).\n  Instance \"DBTAG38\", status READY, has 1 handler(s) for this service...\nService \"DBTAG38_SITE1.dbi-lab.ch\" has 1 instance(s).\n  Instance \"DBTAG38\", status READY, has 2 handler(s) for this service...\ngrid@dbi-oda-x8:\/home\/grid\/ [+ASM1]\n<\/pre>\n
<\/br><\/p>\n
Only DBTAG38 database is registered on the listener LISTENER_VLAN38<\/p>\n
Remote connection on database DBITST<\/h3>\n
From my laptop I can reach directly the DBITST database using pubnet network.<\/p>\n
\"\"<\/a>
\n<\/br>
\n<\/br>
\n<\/br>
\n<\/br>
\n\"\"<\/a>
\n<\/br>
\n<\/br>
\n<\/br>
\n<\/br><\/p>\n
Remote connection on database DBTAG38<\/h3>\n
From my laptop I can reach directly the DBTAG38 database using brtagged38 network.<\/p>\n
\"\"<\/a>
\n<\/br>
\n<\/br>
\n<\/br>
\n<\/br>
\n\"\"<\/a>
\n<\/br>
\n<\/br>
\n<\/br>
\n<\/br><\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":48,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[59],"tags":[79],"type_dbi":[],"class_list":["post-17125","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-oda"],"acf":[],"yoast_head":"\nSeparating database traffic on an ODA - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/separating-database-traffic-on-an-oda\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Separating database traffic on an ODA\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/separating-database-traffic-on-an-oda\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2022-02-17T22:03:23+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-05-31T14:35:20+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/Screenshot-2022-02-17-at-23.38.18.png\" \/>\n<meta name=\"author\" content=\"Marc Wagner\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Marc Wagner\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"14 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/separating-database-traffic-on-an-oda\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/separating-database-traffic-on-an-oda\/\"},\"author\":{\"name\":\"Marc Wagner\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/225d9884b8467ead9a872823acb14628\"},\"headline\":\"Separating database traffic on an ODA\",\"datePublished\":\"2022-02-17T22:03:23+00:00\",\"dateModified\":\"2022-05-31T14:35:20+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/separating-database-traffic-on-an-oda\/\"},\"wordCount\":601,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/separating-database-traffic-on-an-oda\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/Screenshot-2022-02-17-at-23.38.18.png\",\"keywords\":[\"ODA\"],\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/separating-database-traffic-on-an-oda\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/separating-database-traffic-on-an-oda\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/separating-database-traffic-on-an-oda\/\",\"name\":\"Separating database traffic on an ODA - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/separating-database-traffic-on-an-oda\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/separating-database-traffic-on-an-oda\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/Screenshot-2022-02-17-at-23.38.18.png\",\"datePublished\":\"2022-02-17T22:03:23+00:00\",\"dateModified\":\"2022-05-31T14:35:20+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/225d9884b8467ead9a872823acb14628\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/separating-database-traffic-on-an-oda\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/separating-database-traffic-on-an-oda\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/separating-database-traffic-on-an-oda\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/Screenshot-2022-02-17-at-23.38.18.png\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/Screenshot-2022-02-17-at-23.38.18.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/separating-database-traffic-on-an-oda\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Separating database traffic on an ODA\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/225d9884b8467ead9a872823acb14628\",\"name\":\"Marc Wagner\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/a873cc6e7fbdbbcbdbcaf5dbded14ad9a77b2ec2c3e03b4d724ed33d35d5f328?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/a873cc6e7fbdbbcbdbcaf5dbded14ad9a77b2ec2c3e03b4d724ed33d35d5f328?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/a873cc6e7fbdbbcbdbcaf5dbded14ad9a77b2ec2c3e03b4d724ed33d35d5f328?s=96&d=mm&r=g\",\"caption\":\"Marc Wagner\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/marc-wagner\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Separating database traffic on an ODA - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/separating-database-traffic-on-an-oda\/","og_locale":"en_US","og_type":"article","og_title":"Separating database traffic on an ODA","og_description":"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 […]","og_url":"https:\/\/www.dbi-services.com\/blog\/separating-database-traffic-on-an-oda\/","og_site_name":"dbi Blog","article_published_time":"2022-02-17T22:03:23+00:00","article_modified_time":"2022-05-31T14:35:20+00:00","og_image":[{"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/Screenshot-2022-02-17-at-23.38.18.png","type":"","width":"","height":""}],"author":"Marc Wagner","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Marc Wagner","Est. reading time":"14 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/separating-database-traffic-on-an-oda\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/separating-database-traffic-on-an-oda\/"},"author":{"name":"Marc Wagner","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/225d9884b8467ead9a872823acb14628"},"headline":"Separating database traffic on an ODA","datePublished":"2022-02-17T22:03:23+00:00","dateModified":"2022-05-31T14:35:20+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/separating-database-traffic-on-an-oda\/"},"wordCount":601,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/separating-database-traffic-on-an-oda\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/Screenshot-2022-02-17-at-23.38.18.png","keywords":["ODA"],"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/separating-database-traffic-on-an-oda\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/separating-database-traffic-on-an-oda\/","url":"https:\/\/www.dbi-services.com\/blog\/separating-database-traffic-on-an-oda\/","name":"Separating database traffic on an ODA - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/separating-database-traffic-on-an-oda\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/separating-database-traffic-on-an-oda\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/Screenshot-2022-02-17-at-23.38.18.png","datePublished":"2022-02-17T22:03:23+00:00","dateModified":"2022-05-31T14:35:20+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/225d9884b8467ead9a872823acb14628"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/separating-database-traffic-on-an-oda\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/separating-database-traffic-on-an-oda\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/separating-database-traffic-on-an-oda\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/Screenshot-2022-02-17-at-23.38.18.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/Screenshot-2022-02-17-at-23.38.18.png"},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/separating-database-traffic-on-an-oda\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Separating database traffic on an ODA"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/225d9884b8467ead9a872823acb14628","name":"Marc Wagner","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/a873cc6e7fbdbbcbdbcaf5dbded14ad9a77b2ec2c3e03b4d724ed33d35d5f328?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/a873cc6e7fbdbbcbdbcaf5dbded14ad9a77b2ec2c3e03b4d724ed33d35d5f328?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a873cc6e7fbdbbcbdbcaf5dbded14ad9a77b2ec2c3e03b4d724ed33d35d5f328?s=96&d=mm&r=g","caption":"Marc Wagner"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/marc-wagner\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17125","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/48"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=17125"}],"version-history":[{"count":1,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17125\/revisions"}],"predecessor-version":[{"id":17126,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17125\/revisions\/17126"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=17125"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=17125"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=17125"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=17125"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}