Migration with Data Guard: Oracle on Windows AWS to Oracle Cloud Infrastructure (OCI)

Oracle offers a number of methods to migrate Oracle databases to the Oracle Cloud Infrastructure (OCI). From Data Pump through RMAN, Data Guard and even Golden Gate there should be a solution for just about everyone. However, most of these managed migration services require that the source system is running Linux. So what do you do when you’re running Windows?
Well this blog details one way using Oracle Data Guard to perform a cross platform migration from Windows to a Linux Server in OCI. As I didn’t have a Windows Server available, I decided to use AWS to host the Windows server. This made the test a little more complicated and I had to resort to using a SSH Tunnel for migration.

Environment

Setup Oracle Cloud Infrastructure

Create an Oracle DB System in Oracle Cloud Infrastructure using a Public Subnet so that the database can be accessed from the internet.

The Security List for this Public Subnet should be restricted to only allow in-comming connections from the Public IP addresses of the AWS Server and the Oracle OCI Database System.

The Public IP addresses of the Servers can be found using the following commands:

Windows Powershell:

PS C:\Users\Administrator> (curl -uri "http://ifconfig.me/ip").Content
3.xxx.xxx.xxx
PS C:\Users\Administrator>

Linux Bash:

[opc@oci-node2-public ~]$ curl -w '\n' 'ifconfig.me'
140.xxx.xxx.xxx
[opc@oci-node2-public ~]$

Here is an example of the Ingress Rules:

Setup the Primary database for Data Guard

The Oracle Listener has to listen on the port 1522, as this port has been forward from the standby server to the primary server. Using Port 1522 allows the StaticConnectIdentifier entry to be resolveable on both servers.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = AWSTEST_AWS_DGMGRL )
      (ORACLE_HOME = C:\app\oracle\product\19.0.0\dbhome_1)
      (SID_NAME = AWSTEST)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = cda-srv-01)(PORT = 1522))
    )
  )

Setup the entries in tnsnames.ora

AWSTEST_AWS = (DESCRIPTION=
              (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)))
              (CONNECT_DATA=(SERVICE_NAME=AWSTEST_AWS_DGMGRL))
            )

AWSTEST_OCI = (DESCRIPTION=
              (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=140.xxx.xxx.xxx) (PORT=1521)))
              (CONNECT_DATA= (SERVICE_NAME=AWSTEST_OCI_DGMGRL))
            )

Startup SSH Tunnel

From the Primary Server cda-srv-01 start the ssh tunnel to forward the remote port 1522 to the local port 1522.

ssh  -R 1522:localhost:1522 -N -f [email protected]

Setup the Standby Server

Delete the database created by OCI. Delete the controlfiles, datafiles, tempfiles and logfiles Keep the spfile and password file.

The listener.ora requires a static entry for the Standby database:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oci-node2-public)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = AWSTEST_OCI_DGMGRL )
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1 )
      (SID_NAME = AWSTEST)
    )
  )

Setup the entries in tnsnames.ora

AWSTEST_AWS = (DESCRIPTION=
              (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)))
              (CONNECT_DATA=(SERVICE_NAME=AWSTEST_AWS_DGMGRL))
            )

AWSTEST_OCI = (DESCRIPTION=
              (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=140.xxx.xxx.xxx) (PORT=1521)))
              (CONNECT_DATA= (SERVICE_NAME=AWSTEST_OCI_DGMGRL))
            )

Reset the db_file_name_convert & log_file_name_convert parameters as we are using Oracle Managed Files.

alter system reset db_file_name_convert;
alter system reset log_file_name_convert;

Copy the password from the primary database to the standby:

cd $env:ORACLE_HOME\database
scp PWDAWSTEST.ora [email protected]:/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwAWSTEST

Startup the standby database in nomount status.

sqlplus / as sysdba
startup nomount

Duplicate the primary database to the standby.

rman target sys@AWSTEST_AWS auxiliary sys@AWSTEST_OCI

duplicate target database for standby from active database;

Enable Data Guard & Switchover

On the Primary and Standby databases:

alter system set dg_broker_start=true;

Setup Data Guard configuration:

create configuration 'AWS' as
  primary database is AWSTEST_AWS
  connect identifier is 'AWSTEST_AWS';

edit database AWSTEST_AWS
  set property StaticConnectIdentifier =
  '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=AWSTEST_AWS_DGMGRL)(INSTANCE_NAME=awstest)(SERVER=DEDICATED)))';

add database AWSTEST_OCI as connect identifier is 'AWSTEST_OCI' maintained as physical;

edit database AWSTEST_OCI
  set property StaticConnectIdentifier =
  '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=140.xxx.xxx.xxx)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=AWSTEST_OCI_DGMGRL)(INSTANCE_NAME=AWSTEST)(SERVER=DEDICATED)))';

edit database AWSTEST_OCI set property LogXptMode = ARCH;
edit database AWSTEST_AWS set property LogXptMode = ARCH;

enable configuration

Perform Switchover

show configuration verbose
validate database awstest_aws
validate database awstest_oci
switchover to awstest_oci
switchover to awstest_aws

[oracle@oci-node2-public ~]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sat Feb 11 15:34:29 2023
Version 19.17.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/password@AWSTEST_AWS
Connected to "AWSTEST_AWS"
Connected as SYSDBA.
DGMGRL> show configuration verbose

Configuration - AWS

  Protection Mode: MaxPerformance
  Members:
  awstest_aws - Primary database
    awstest_oci - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'
    ConfigurationWideServiceName    = 'AWSTEST_CFG'

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS

DGMGRL> validate database awstest_aws

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Flashback Database Status:
    awstest_aws:  Off

  Managed by Clusterware:
    awstest_aws:  NO
    Validating static connect identifier for the primary database awstest_aws...
    The static connect identifier allows for a connection to database "awstest_aws".

DGMGRL> validate database awstest_oci

  Database Role:     Physical standby database
  Primary Database:  awstest_aws

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    awstest_aws:  Off
    awstest_oci:  Off

  Managed by Clusterware:
    awstest_aws:  NO
    awstest_oci:  NO
    Validating static connect identifier for the primary database awstest_aws...
    The static connect identifier allows for a connection to database "awstest_aws".

DGMGRL> switchover to awstest_oci
Performing switchover NOW, please wait...
Operation requires a connection to database "awstest_oci"
Connecting ...
Connected to "AWSTEST_OCI"
Connected as SYSDBA.
New primary database "awstest_oci" is opening...
Operation requires start up of instance "awstest" on database "awstest_aws"
Starting instance "awstest"...
Connected to an idle instance.
ORACLE instance started.
Connected to "AWSTEST_AWS"
Database mounted.
Switchover succeeded, new primary is "awstest_oci"
DGMGRL> show configuration verbose

Configuration - AWS

  Protection Mode: MaxPerformance
  Members:
  awstest_oci - Primary database
    awstest_aws - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'
    ConfigurationWideServiceName    = 'AWSTEST_CFG'

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS

DGMGRL> switchover to awstest_aws
Performing switchover NOW, please wait...
Operation requires a connection to database "awstest_aws"
Connecting ...
Connected to "AWSTEST_AWS"
Connected as SYSDBA.
New primary database "awstest_aws" is opening...
Operation requires start up of instance "AWSTEST" on database "awstest_oci"
Starting instance "AWSTEST"...
Connected to an idle instance.
ORACLE instance started.
Connected to "AWSTEST_OCI"
Database mounted.
Switchover succeeded, new primary is "awstest_aws"
DGMGRL> show configuration verbose

Configuration - AWS

  Protection Mode: MaxPerformance
  Members:
  awstest_aws - Primary database
    awstest_oci - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'
    ConfigurationWideServiceName    = 'AWSTEST_CFG'

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS

DGMGRL>

Conclusion

With the above method I was able to migrate an Oracle database from AWS into the Oracle Cloud Infrastructure, with Data Guard via a ssh tunnel. The ssh tunnel was necessary as I don’t have direct routing between the AWS & OCI clouds, and this setup allows me to test various migration methods anyway.

I wouldn’t necessarily recommend this in a production environment, as there are number of security issues, like connecting a database server directly to the internet and opening a tunnel between the 2 cloud providers that could violate some organisational security policies.

In this test, the migration was performed between a AWS Sandbox and an Oracle OCI Sandbox where only the Oracle Demo accounts are installed in the database and there is no danger to any other infrastructure. The test environment will be deleted when testing is complete.