I am fascinated about the new Zero Downtime Migration tool that has been available since November 28th. Whilst I am still in the process of testing it, there is one big requirement that might cause some headache to some customers. It is about network connectivity:
Configuring Connectivity Between the Source and Target Database Servers
The source database server […] can connect to target database instance over target SCAN through the respecitve scan port and vice versa.
The SCAN of the target should be resolvable from the source database server, and the SCAN of the source should resolve from the target server.
Having connectivity from both sides, you can synchronize between the source database and target database from either side. […]
If you are taking cloud migrations seriously, you should have either a VPN site-to-site to the cloud, or a Fast Connect link. At CERN we are quite lucky to have a high bandwidth Fast Connect to OCI Frankfurt.
This requirement might be missing for many customers, so what is a possible solution to setup connectivity for database duplicates and Data Guard setups?
In the picture above you can see a classic situation, that usually has two problems that must be solved:
- the SCAN addresses are private: not accessible from internet
- there are multiple SCAN addresses, so tunneling through all of them might be complex
Is it possible to configure CMAN in front of the SCAN listener as a single IP entry and tunnel through SSH to this single IP?
I will show now how to achieve this configuration.
For sake of simplicity, I have put two single instances without SCAN and a CMAN installation on the database servers, but it will work with little modification using SCAN and RAC setups as well. Note that in a Cloud Infrastructure setup, this will require a correct setup of the TDE wallet on both the source and the destination.
Because I put everything on s single host, I have to setup CMAN to listen to another port, but having a separate host for CMAN is a better practice when it has to proxy to SCAN listeners.
Installing and configuring CMAN
The most important part of the whole setup is that the CMAN on the standby site must have a public IP address and open SSH port so that we can tunnel through it.
The on-premises CMAN must have open access to the standby CMAN port 22.
For both primary and standby site you can follow the instructions of my blog post: Install and configure CMAN 19c in the Oracle Cloud, step by step.
In my example, because I install CMAN on the same host of the DB, I configure CMAN to run on port 1522.
CMAN primary config:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
cman-onprem = (configuration= (address=(protocol=tcp)(host=ludodb01.cern.ch)(port=1522)) (parameter_list = (log_level=ADMIN) (max_connections=1024) (idle_timeout=0) (registration_invited_nodes = *) (inbound_connect_timeout=0) (session_timeout=0) (outbound_connect_timeout=0) (max_gateway_processes=16) (min_gateway_processes=2) (remote_admin=on) (trace_level=off) (max_cmctl_sessions=4) (event_group=init_and_term,memory_ops) ) (rule_list= (rule= (src=*)(dst=*)(srv=*)(act=accept) (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on)) ) ) ) |
CMAN standby config:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
cman-cloud = (configuration= (address=(protocol=tcp)(host=ludodb02.cern.ch)(port=1522)) (parameter_list = (log_level=ADMIN) (max_connections=1024) (idle_timeout=0) (registration_invited_nodes = *) (inbound_connect_timeout=0) (session_timeout=0) (outbound_connect_timeout=0) (max_gateway_processes=16) (min_gateway_processes=2) (remote_admin=on) (trace_level=off) (max_cmctl_sessions=4) (event_group=init_and_term,memory_ops) ) (rule_list= (rule= (src=*)(dst=*)(srv=*)(act=accept) (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on)) ) ) ) |
This configuration is not secure at all, you might want to secure it further in order to allow only the services needed for setting up Data Guard.
The registration of database services to CMAN through the the remote_listener parameter is optional, as I will register the entries statically in the listener and use a routed connection through CMAN.
Listener configuration
The listener must have a static entry for the database, so that duplicate and switchover work properly.
On primary add to listener.ora:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = db_onprem_DGMGRL.cern.ch) (ORACLE_HOME = /u01/app/oracle/product/rdbms1930 ) (SID_NAME = db) ) (SID_DESC = (GLOBAL_DBNAME = db_onprem.cern.ch) (ORACLE_HOME = /u01/app/oracle/product/rdbms1930 ) (SID_NAME = db) ) ) |
On standby:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = db_cloud_DGMGRL.cern.ch) (ORACLE_HOME = /u01/app/oracle/product/rdbms1930 ) (SID_NAME = db) ) (SID_DESC = (GLOBAL_DBNAME = db_cloud.cern.ch) (ORACLE_HOME = /u01/app/oracle/product/rdbms1930 ) (SID_NAME = db) ) ) |
In a RAC config, all the local listeners must be configured with the correct SID_NAME running on the host. Make sure to reload the listeners 😉
Creating the SSH tunnels
There must be two tunnels open: one that tunnels from on-premises to the cloud and the other that tunnels from the cloud to on-premises.
However, such tunnels can both be created from the on-premises CMAN host that has access to the cloud CMAN host:
|
1 2 3 4 5 |
# bind local port 1523 to remote port 1522 ssh -NnTf cman-cloud -L 1523:cman-cloud:1522 # bind remote port 1523 to local port 1522 ssh -NnTf cman-cloud -R 1523:cman-onprem:1522 |
in my case, the hostnames are:
|
1 2 |
ssh -NnTf ludodb02 -L 1523:ludodb02:1522 ssh -NnTf ludodb02 -R 1523:ludodb01:1522 |
Important: with CMAN on a host other than the DB server, the CMAN sshd must be configured to have GatewayPorts set to yes:
|
1 |
GatewayPorts yes |
After the tunnels are open, any connections to the local CMAN server port 1523 will be forwarded to the remote CMAN port 1522.
Configuring the TNSNAMES to hop through CMAN and SSH tunnel
Both servers must have now one entry for the local database pointing to the actual SCAN (or listener for single instances) and one entry for the remote database pointing to local port 1523 and routing to the remote scan.
On-premises tnsnames.ora:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
DB_ONPREM = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ludodb01.cern.ch)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db_onprem.cern.ch) ) ) DB_CLOUD = (DESCRIPTION = (SOURCE_ROUTE=yes) (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1523)) (ADDRESS=(PROTOCOL=tcp)(HOST=ludodb02)(PORT=1521))) (CONNECT_DATA = (SERVICE_NAME = db_cloud.cern.ch) ) ) |
Cloud tnsnames.ora:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DB_CLOUD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ludodb02.cern.ch)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db_cloud.cern.ch) ) ) DB_ONPREM= (DESCRIPTION= (SOURCE_ROUTE=yes) (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1523)) (ADDRESS=(PROTOCOL=tcp)(HOST=ludodb01)(PORT=1521))) (CONNECT_DATA=(SERVICE_NAME=db_onprem.cern.ch))) |
After copying the passwordfile and starting nomount the cloud database, it should be possible from both sides to connect as SYSDBA to both DB_CLOUD and DB_ONPREM.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
# [ oracle@ludodb02:/u01/app/oracle/network/admin [13:33:41] [19.3.0.0.0 [DBMS EE] SID=db] 0 ] # # s_ sys/change_on_install@db_onprem as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 17 13:37:42 2019 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 # [ oracle@ludodb02:/u01/app/oracle/network/admin [13:37:44] [19.3.0.0.0 [DBMS EE] SID=db] 0 ] # # s_ sys/change_on_install@db_cloud as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 17 13:37:53 2019 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 # [ oracle@ludodb02:/u01/app/oracle/network/admin [13:37:54] [19.3.0.0.0 [DBMS EE] SID=db] 0 ] # # tnsping db_onprem TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 17-DEC-2019 13:38:05 Copyright (c) 1997, 2019, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION= (SOURCE_ROUTE=yes) (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1523)) (ADDRESS=(PROTOCOL=tcp)(HOST=ludodb01)(PORT=1521))) (CONNECT_DATA=(SERVICE_NAME=db_onprem.cern.ch))) OK (10 msec) |
This configuration is ready for both duplicate from active database and for Data Guard.
I still have to figure out if it works with ZDM, but I think it is a big step towards establishing connection between on-premises and the Oracle Cloud when no VPN or Fast Connect are available.
Duplicate from active database
If the setup is correct, this should work:
|
1 2 |
rman_ target sys/change_on_install@db_onprem auxiliary sys/change_on_install@db_cloud RMAN> duplicate target database for standby from active database ; |
Setting up Data Guard
- Configure broker config files
- Add and clear the standby logs
- Start the broker
- Create the configuration:
123456create configuration db as primary database is db_onprem connect identifier is 'db_onprem';add database db_cloud as connect identifier is 'db_cloud';edit database db_onprem set property StaticConnectIdentifier='db_onprem';edit database db_cloud set property StaticConnectIdentifier='db_cloud';enable configuration;show configuration;
The static connect identifier here is better if it uses the TNSNAMES resolution because each database sees each other differently.
Checking the DG config
A validate first:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
DGMGRL> show configuration; Configuration - db Protection Mode: MaxPerformance Members: db_onprem - Primary database db_cloud - Physical standby database Fast-Start Failover: Disabled Configuration Status: SUCCESS (status updated 56 seconds ago) DGMGRL> validate database db_cloud; Database Role: Physical standby database Primary Database: db_onprem Ready for Switchover: Yes Ready for Failover: Yes (Primary Running) Flashback Database Status: db_onprem: Off db_cloud : Off Managed by Clusterware: db_onprem: NO db_cloud : NO Validating static connect identifier for the primary database db_onprem... The static connect identifier allows for a connection to database "db_onprem". |
Than a switchover, back and forth:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
DGMGRL> switchover to db_cloud; Performing switchover NOW, please wait... Operation requires a connection to database "db_cloud" Connecting ... Connected to "db_cloud" Connected as SYSDBA. New primary database "db_cloud" is opening... Operation requires start up of instance "db" on database "db_onprem" Starting instance "db"... Connected to an idle instance. ORACLE instance started. Connected to "db_onprem" Database mounted. Connected to "db_onprem" Switchover succeeded, new primary is "db_cloud" DGMGRL> show configuration; Configuration - db Protection Mode: MaxPerformance Members: db_cloud - Primary database db_onprem - Physical standby database Fast-Start Failover: Disabled Configuration Status: SUCCESS (status updated 57 seconds ago) DGMGRL> switchover to db_onprem; Performing switchover NOW, please wait... Operation requires a connection to database "db_onprem" Connecting ... Connected to "db_onprem" Connected as SYSDBA. New primary database "db_onprem" is opening... Operation requires start up of instance "db" on database "db_cloud" Starting instance "db"... Connected to an idle instance. ORACLE instance started. Connected to "db_cloud" Database mounted. Connected to "db_cloud" Switchover succeeded, new primary is "db_onprem" |
Conclusion
Yes, it is possible to setup a Data Guard between two sites that have no connections except mono-directional SSH. The SSH tunnels allow SQL*Net communication to a remote endpoint. CMAN allows to proxy through a single endpoint to multiple SCAN addresses.
However, do not forget about the ultimate goal that is to migrate your BUSINESS to the cloud, not just the database. Therefore, having a proper communication to the cloud with proper performance, architecture and security is crucial. Depending on your target Cloud database, Zero Downtime Migration or MV2ADB should be the correct and supported solutions.
Latest posts by Ludovico (see all)
- New views in Oracle Data Guard 23c - January 3, 2024
- New in Data Guard 21c and 23c: Automatic preparation of the primary - December 22, 2023
- Does FLASHBACK QUERY work across incarnations or after a Data Guard failover? - December 13, 2023