1. Runbook: Move On-Premise Databases and Oracle Cloud Infrastructure Classic Instances to Oracle Cloud Infrastructure
  2. Preparing for Database Migration

3 Preparing for Database Migration

The following topics describe how to prepare for database migration.

3.1 Setting Up Zero Downtime Migration Software

Requirement Description Comments
Set up the Zero Downtime Migration software
  1. You may use an existing user, or, on the Zero Downtime Migration service host, as root user, create a zdm group and add zdmuser user to the group.

    For example

    root> groupadd zdm

    root> useradd –g zdm zdmuser

    Note that you can use any non root user to set up Zero Downtime Migration. For the examples in this document all of the commands are shown run as zdmuser.

  2. Download the Zero Downtime Migration software kit from https://www.oracle.com/database/technologies/rac/zdm-downloads.html to the Zero Downtime Migration service host.
  3. Refer to the README in the download for the mandatory packages and install them.
  4. Change to the directory to where Zero Downtime Migration software is downloaded.

    zdmuser> cd zdm_download_directory

    Run the Zero Downtime Migration installation script.

    zdmuser>./zdminstall.sh setup oraclehome=zdm_oracle_home oraclebase=zdm_base_directory ziploc=zdm_software_location –zdm

    Where zmdinstall.sh is the installation script.

    oraclehome is the Oracle Home where the Zero Downtime Migration kit is installed.

    oraclebase is the base directory where all of the Zero Downtime Migration configuration files, logs, and other artifacts are stored.

    ziploc is the location of the compressed software file (ZIP) included in the Zero Downtime Migration kit.

    For example,

    zdmuser>./zdminstall.sh setup oraclehome=/u01/app/zdmhome

    oraclebase=/u01/app/zdmbase ziploc=/u01/app/oracle/zdm/shiphome/zdm_home.zip -zdm

    Hereafter, the oraclehome value is referred to as ZDM_HOME, and the oraclebase value is referred to as ZDM_BASE.

  5. Start the Zero Downtime Migration service as user zdmuser.

    zdmuser> /u01/app/zdmhome/bin/zdmservice start

    You must start zdmservice before you can migrate your databases using Zero Downtime Migration.

  6. To stop the Zero Downtime Migration service, run the following command.

    zdmuser> /u01/app/zdmhome/bin/zdmservice stop

  7. Verify that the Zero Downtime Migration service installation is successful.

    When you run the following command, the output should be similar to that shown here.

    zdmuser> /u01/app/zdmhome/bin/zdmservice status

    ---------------------------------------

    Service Status

    ---------------------------------------

    Running: true

    Tranferport: 5000-7000

    Conn String: jdbc:derby:/u01/app/zdmbase/derbyRepo;create=true

    Repo Path: /u01/app/zdmbase/derbyRepo

    RMI port: 8895

    HTTP port: 8896

    Wallet path: /u01/app/zdmbase/crsdata/fopds/security

 Ignore the following messages which are displayed on the terminal at the end of installation. There is no need to run these scripts.

"As a root user, execute the following script(s):

  1. /u01/app/zdmhome/inventory/orainstRoot.sh
  2. /u01/app/zdmhome/root.sh"

Parent topic: Preparing for Database Migration

3.2 Configuring Connectivity From the Zero Downtime Migration Service Host to the Source and Target Database Servers

Requirement Description Comments
Configure connectivity from the Zero Downtime Migration service host to the source and target database servers
  1. On the Zero Downtime Migration service host, verify that the authentication key pairs are available without a passphrase for the Zero Downtime Migration software installed user.

    If a new key pair must be generated without the passphrase, then, as a Zero Downtime Migration software installed user, generate new key pairs as described in Generating a Private SSH Key Without a Passphrase.

  2. Rename the private key file.

    Rename the ZDM_installed_user_home/.ssh/id_rsa file name to ZDM_installed_user_home/.ssh/ZDM_service_host_name.ppk

  3. Add the contents of the ZDM_installed_user_home/.ssh/id_rsa.pub file to the opc_user_home/.ssh/authorized_keys file on all of the source and target database servers.

    Note that the opc user is a standard Oracle Cloud user that is used to access the Oracle Cloud Infrastructure Classic and Exadata Cloud Service database servers.

  4. Add the source and target database server names and IP address details to the Zero Downtime Migration service host /etc/hosts file.

    For example,

    #OCI-C public IP of two node RAC server details

    192.0.2.1 zdm122011

    192.0.2.2 zdm122012

    #OCI public IP of two node RAC server details

    192.0.2.3 ocitarget1

    192.0.2.4 ocitarget2

  5. Make certain that port 22 in the source and target database servers accept incoming connections from the Zero Downtime Migration service host.
  6. Test the connectivity from the Zero Downtime Migration service host to all source and target database servers.

    zdmuser> ssh -i ZDM_service_node_private_key_file_location user@source/target_database_server_name

    For example,

    zdmuser> ssh -i /home/zdmuser/.ssh/zdm_service_node.ppk opc@zdm122011

    zdmuser> ssh -i /home/zdmuser/.ssh/zdm_service_node.ppk opc@zdm122012

    zdmuser> ssh -i /home/zdmuser/.ssh/zdm_service_node.ppk opc@ocitarget1

    zdmuser> ssh -i /home/zdmuser/.ssh/zdm_service_node.ppk opc@ocitarget2

Note 1: If you have root access to source database server, then configuring connectivity through SSH keys for the source database server is not required.

Note 2: If you configure connectivity through SSH keys, then the Zero Downtime Migration service host should be able to connect to the source and target database servers using a private key file, without prompting for any password.

Note 3: If your Zero Downtime Migration service host needs proxy details to connect to the target server, then along with SSH keys you also need to configure the ZDM_installed_user_home/.ssh/config with the following details, otherwise configuring the ZDM_installed_user_home/.ssh/config file is not required.

cat ZDM_installed_user_home/.ssh/config

Host *

ServerAliveInterval 10

ServerAliveCountMax 2

Host Target_server_name

HostName Target_server_IP_address

IdentityFile Private_key_file_location

User Target_user_login

ProxyCommand /usr/bin/nc --proxy proxy_url:port %h %p

For example

Host *

ServerAliveInterval 10

ServerAliveCountMax 2

Host ocitarget1

HostName 192.0.2.3

IdentityFile /home/zdmuser/.ssh/zdm_service_host.ppk

User opc

ProxyCommand /usr/bin/nc --proxy www-proxy-example.com:80 %h %p

Note 4: The authorized_keys file permissions should be similar to the following.

/home/opc/.ssh>ls -l authorized_keys

-rw------- 1 opc opc 1679 Oct 16 10:05 authorized_keys

Parent topic: Preparing for Database Migration

3.3 Configuring Connectivity Between the Source and Target Database Servers

Requirement Description Comments
Configure connectivity between the source and target database servers

You can configure connectivity between the source and target database servers using one of two options.

Option 1

The source database server specified in the ZDMCLI command -sourcenode parameter can connect to the target database instance over target SCAN through the respective 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 the source database server SCAN cannot be resolved from the target database server, then the SKIP_FALLBACK parameter in the response file must be set to TRUE, and you cannot synchronize between the target database and source database.

To test the connectivity for Option 1:

  1. Test connectivity from the source to target environments.

    Add the TNS entry of the target database to the source database server tnsnames.ora file.

    [oracle@zdm122011 ~]tnsping target-tns-string

  2. Test connectivity from the target to the source environment.

    Add the TNS entry of the source database to the target database server tnsnames.ora file.

    [oracle@exacstarget1~] tnsping source-tns-string

Option 2

If connectivity through SCAN and the SCAN port is not possible between the source and target database servers, set up an SSH tunnel from the source database server to the target database server using the procedure below. Using this option, you will not be able to synchronize between the target database and source database.

To set up an SSH tunnel on the source database servers for the root user:

  1. Generate a private SSH key file without a passphrase for the opc user on the target server.

    See Generating a Private SSH Key Without a Passphrase.

    If the target is an Oracle RAC database, then generate a private SSH key file without a passphrase from the first Oracle RAC server.

  2. Add the contents of the target server opc_user_home/.ssh/id_rsa.pub file into the source server opc_user_home/.ssh/authorized_keys file.

    If the source is an Oracle RAC database, add the contents of the target server opc_user_home/.ssh/id_rsa.pub file into the opc_user_home/.ssh/authorized_keys file on all Oracle RAC source servers.

  3. Copy the target server private SSH key file onto the source server in the /root/.ssh/ directory.

    If the source is an Oracle RAC database, copy the file into all of the source servers. For better manageability, keep the private SSH key file name the same as the target server name, and keep the .ppk extension. For example, exacstarget1.ppk (where exacstarget1 is the target server name).

    The file permissions should be similar to the following.

    /root/.ssh>ls -l exacstarget1t.ppk

    -rw------- 1 root root 1679 Oct 16 10:05 exacstarget1.ppk

  4. Put the following entries in the source server /root/.ssh/config file.

    Host *

    ServerAliveInterval 10

    ServerAliveCountMax 2

    Host Target_server_name

    HostName Target_server_IP_address

    IdentityFile Private_key_file_location

    User Target_user_login

    Where Target_server_name is the target database server name without the domain name. For an Oracle RAC database, use the first Oracle RAC server name without the domain name.

    Target_server_IP_address is the target database server IP address. For an Oracle RAC database use the first Oracle RAC server IP address.

    Private_key_file_location is the location of the private key file.

    Target_user_login is the OS user used to access the target database servers.

    For example, after specifying the relevant values, the /root/.ssh/config file contents similar to the following.

    Host *

    ServerAliveInterval 10

    ServerAliveCountMax 2

    Host exacstarget1

    HostName 192.0.2.3

    IdentityFile ~/.ssh/exacstarget1.ppk

    User opc

    The file permissions should be similar to the following.

    /root/.ssh>ls -l config

    -rw------- 1 root root 1679 Oct 16 10:05 config

    In the above example, the target server name is exacstarget1, and the target server public IP address is 192.0.2.3.

    If the source is an Oracle RAC database, then copy the same /root/.ssh/config file onto all of the source Oracle RAC database servers.

    Make sure that you can SSH to first target server from the source server before you enable the SSH tunnel.

    For an Oracle RAC database, test the connection from all of the source servers to the first target server.

    Test the connectivity using the private key file and ensure source database server is able to connect to target database server without prompting for any password.

    [root@zdm122011 ~] ssh -i /root/.ssh/exacstarget1.ppk opc@exacstarget1

    [root@zdm122012 ~] ssh -i /root/.ssh/exacstarget1.ppk opc@exacstarget1

  5. Run the following command on the source server to enable the SSH tunnel.

    ssh -f Target_hostname_without_domain_name -L \ ssh_tunnel_port_number:Target_server_IP_address: Target_server_listener_port -N

    Where Target_hostname_without_domain_name is the target database server name without a domain name. For an Oracle RAC database use the first Oracle RAC server name without domain name.

    The ssh_tunnel_port_number is any available ephemeral port in the range (1024-65545). Make sure that the SSH tunnel port is not used by any other process in the server before using it.

    Target_server_listener_port is the target database listener port number. The listener port must be open between the source database servers and target servers.

    Target_server_IP_address is configured based on database architecture. For a single instance database, specify the Target server IP address. For an Oracle RAC database, specify the Target scan name with the domain name.

    If the scan name with domain name is not resolvable or not working, then specify the IP address obtained using the LSNRCTL STATUS command output from the target server. For example

    Listening Endpoints Summary...

    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.0.2.9)(PORT=1521)))

    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.0.2.10)(PORT=1521)))

    The following is an example of the command run to enable the SSH tunel.

    [root@zdm122011 ~]ssh -f exacstarget1 -L 9001:192.0.2.9:1521 -N

    For an Oracle RAC database, this step must be repeated on all of the source servers.

  6. Test the connectivity with SSH tunnel for Option 2.

    For Oracle RAC, this step must be repeated on all of the source servers.

    Log in to source server, switch to the oracle user and source the database environment and run the following command.

    tnsping localhost:ssh_tunnel_port

    For example

    [oracle@zdm122011 ~] tnsping localhost:9001

    The command output is similar to the following.

    TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 22-JAN-2019 05:41:57

    Copyright (c) 1997, 2014, Oracle. All rights reserved.

    Used parameter files:

    Used HOSTNAME adapter to resolve the alias

    Attempting to contact(DESCRIPTION=(CONNECT_DATA= (SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP) (HOST=127.0.0.1)(PORT=9001)))

    OK (50 msec)

    If tnsping does not work, then the SSH tunnel is not enabled.

Ensure that the source database server is able to connect to target database server without prompting for any password, otherwise Data Guard Sync cannot occur from the source to the target side.

Parent topic: Preparing for Database Migration

3.4 Prepare the Response File Template

Prepare the response file template according to whether you will perform the migration online or offline.

Parent topic: Preparing for Database Migration

3.4.1 Preparing a Response File for Online Migration

Requirement Description Comments
Prepare the response file for online migration

Get the response file template from $ZDM_HOME/rhp/zdm/template/zdm_template.rsp, and edit the folowing settings.

  • Set TGT_DB_UNIQUE_NAME to the target database db_unique_name value.
  • Set MIGRATION_METHOD to DG_OSS, where DG stands for Data Guard and OSS stands for for Object Storage service.
  • Set PLATFORM_TYPE to VMDB.
  • If an SSH proxy is required to access the source database server from the Zero Downtime Migration service host, set SRC_HTTP_PROXY_URL and SRC_HTTP_PROXY_PORT.
  • If an SSH proxy is required to access the target database server from the Zero Downtime Migration service host, set TGT_HTTP_PROXY_URL and TGT_HTTP_PROXY_PORT.
  • If SSH tunneling is set up, set the TGT_SSH_TUNNEL_PORT parameter.
  • Specify the target database data files storage (ASM or ACFS) properties as appropriate for (TGT_DATADG, TGT_REDODG, and TGT_RECODG) or (TGT_DATAACFS, TGT_REDOACFS, and TGT_RECOACFS).
  • Set HOST and OPC_CONTAINER with the Object Store URL and bucket name.
  • Set SKIP_FALLBACK=TRUE if you do not want to ship redo logs from the target to the source standby, either voluntarily or because there is no connectivity between the target and the source.
  • Set SHUTDOWN_SRC=TRUE if, after the database migration, you want to shut down the source database.
  • Set SRC_RMAN_CHANNELS with the number of RMAN channels to be allocated at the source and used to perform RMAN backups. The default is 10.
  • Set TGT_RMAN_CHANNELS with the number of RMAN channels to be allocated at the target and used to perform RMAN restore. The default is 10.
For example, copy the response file to $ZDM_HOME/rhp/zdm/template/zdm_template_ZDM12201.rsp and add values to the settings based on source and target as shown below.

TGT_DB_UNIQUE_NAME=ZDM12201_phx1xx

MIGRATION_METHOD=DG_OSS

PLATFORM_TYPE=VMDB

TGT_HTTP_PROXY_URL=www-proxy-example.com

TGT_HTTP_PROXY_PORT=80

TGT_SSH_TUNNEL_PORT=9001

TGT_DATADG=+DATA

TGT_REDODG=+RECO

TGT_RECODG=+RECO

HOST=https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/tenancyname

OPC_CONTAINER=DEMOZDM

SKIP_FALLBACK=TRUE

SHUTDOWN_SRC=TRUE

SRC_RMAN_CHANNELS=6

You can find a sample response file in /$ZDM_HOME/rhp/zdm/template/zdm_template.rsp.

Parent topic: Prepare the Response File Template

3.4.2 Preparing a Response File for Offline Migration (Backup and Recovery)

Requirement Description Comments
Prepare the response file for offline migration (using Backup and Recovery)

Get the response file template from $ZDM_HOME/rhp/zdm/template/zdm_template.rsp, and edit the following settings.

  • Set TGT_DB_UNIQUE_NAME to the target database db_unique_name value.
  • Set PLATFORM_TYPE to the appropriate value, depending on your target environment.
  • For Oracle Cloud Infrastructure, set PLATFORM_TYPE=VMDB.
  • Set MIGRATION_METHOD to BACKUP_RESTORE_OSS, where OSS stands for Object Storage Service.
  • Specify the target database data files storage (ASM or ACFS) properties as appropriate. For ASM, set TGT_DATADG, TGT_REDODG, and TGT_RECODG. For ACFS set TGT_DATAACFS, TGT_REDOACFS, and TGT_RECOACFS.
  • If an SSH proxy is required to access the source database server from the Zero Downtime Migration service host, set SRC_HTTP_PROXY_URL and SRC_HTTP_PROXY_PORT.
  • If an SSH proxy is required to access the target database server from the Zero Downtime Migration service host, set TGT_HTTP_PROXY_URL and TGT_HTTP_PROXY_PORT.
  • Set HOST and OPC_CONTAINER with the Object Store URL and bucket name.
  • Set SHUTDOWN_SRC=TRUE if, after the database migration, you want to shut down the source database.
  • Set SRC_RMAN_CHANNELS to the number of RMAN channels to be allocated at the source and used to perform RMAN backups. The default is 10.
  • Set TGT_RMAN_CHANNELS to the number of RMAN channels to be allocated at the target and used to perform RMAN restore. The default is 10.

For example, copy the response file template to $ZDM_HOME/rhp/zdm/template/zdm_template_ZDM12201.rsp and enter values for the settings based on the source and target as shown here.

TGT_DB_UNIQUE_NAME=ZDM12201_phx1xx

MIGRATION_METHOD=BACKUP_RESTORE_OSS

PLATFORM_TYPE=VMDB

TGT_HTTP_PROXY_URL=www-proxy-example.com

TGT_HTTP_PROXY_PORT=80

TGT_DATADG=+DATA

TGT_REDODG=+RECO

TGT_RECODG=+RECO

HOST=https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/tenancyname

OPC_CONTAINER=DEMOZDM

SHUTDOWN_SRC=TRUE

SRC_RMAN_CHANNELS=6

You can find a sample response file in $ZDM_HOME/rhp/zdm/template/zdm_template.rsp.

Parent topic: Prepare the Response File Template

3.5 Preparing for Automatic Application Switchover

Requirement Description Comments
Prepare for automatic application switchover To minimize or eliminate service interruptions on the application after you complete the database migration and switchover, prepare your application to automatically switch over connections from the source database to the target database.

In the following example connect string, the application connects to the source database, and when it is not available the connection is switched over to the target database.

(DESCRIPTION=(FAILOVER=on)(LOAD_BALANCE=on) (CONNECT_TIMEOUT=3)(RETRY_COUNT=3)(ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=source_database_scan) (PORT=1521))(ADDRESS=(PROTOCOL=TCP) (HOST=target_database_scan)(PORT=1521)))(CONNECT_DATA= (SERVICE_NAME=zdm_prod_svc)))

On the source database, create the service, named zdm_prod_svc in the examples.

srvctl add service -db clever -service zdm_prod_svc -role PRIMARY -notification TRUE -session_state dynamic -failovertype transaction -failovermethod basic -commit_outcome TRUE -failoverretry 30 -failoverdelay 10 -replay_init_time 900 -clbgoal SHORT -rlbgoal SERVICE_TIME -preferred clever1,clever2 -retention 3600 -verbose

Parent topic: Preparing for Database Migration

3.6 Customizing a Migration Job

Requirement Description Comments
Customize the migration job

You can customize the Zero Downtime Migration workflow by registering action scripts or plug-ins as pre-actions or post-actions to be performed as part of the operational phases involved in your migration job.

The following topics describe how to customize a migration job.

Registering Action Plug-ins

Custom plug-ins must be registered to the Zero Downtime Migration service host to be plugged in as customizations for a particular operational phase.

Determine the operational phase the given plug-in has to be associated with, and run the ZDMCLI command add useraction, specifying -optype MIGRATE_DATABASE and the respective phase of the operation, whether the plug-in is run -pre or -post relative to that phase, and any on-error requirements. You can register custom plug-ins for operational phases after ZDM_SETUP_TGT in the migration job workflow.

What happens at runtime if the user action encounters an error can be specified with the -onerror option, which you can set to either ABORT, to end the process, or CONTINUE, to continue the migration job even if the custom plug-in exits with an error. See the example command usage below.

Use the Zero Downtime Migration software installed user (for example, zmduser) to add useractions to a database migration job. Adding user actions zdmvaltgt and zdmvalsrc with the add useraction command would look like the following.

zdmuser>$ZDM_HOME/bin/zdmcli add useraction -useraction zdmvaltgt -optype MIGRATE_DATABASE -phase ZDM_VALIDATE_TGT -pre -onerror ABORT -actionscript /home/zdmuser/useract.sh

zdmuser>$ZDM_HOME/bin/zdmcli add useraction -useraction zdmvalsrc -optype MIGRATE_DATABASE -phase ZDM_VALIDATE_SRC -pre -onerror CONTINUE -actionscript /home/zdmuser/useract1.sh

In the above command, the scripts /home/zdmuser/useract.sh and /home/zdmuser/useract1.sh are copied to the Zero Downtime Migration service host repository, and they are run if they are associated with any migration job run using an action template.

Creating an Action Template

After the user action plug-ins are registered, you create an action template that combines a set of action plug-ins which can be associated with a migration job.

An action template is created using the ZDMCLI command add imagetype, where the image type, imagetype, is a bundle of all of the user actions required for a specific type of database migration.

Create an image type that associates all of the user action plug-ins needed for the migration of the database. Once created, the image type can be reused for all migration operations for which the same set of plug-ins are needed.

The base type for the image type created here must be CUSTOM_PLUGIN, as shown in the example below.

For example, you can create an image type ACTION_ZDM that bundles both of the user actions created in the previous example, zdmvalsrc and zdmvaltgt.

zdmuser>$ZDM_HOME/bin/zdmcli add imagetype -imagetype ACTION_ZDM -basetype CUSTOM_PLUGIN -useractions zdmvalsrc,zdmvaltgt

Updating Action Plug-ins

You can update action plug-ins registered with the Zero Downtime Migration service host.

The following example shows you how to modify the user action zdmvalsrc to be a -post action, instead of a -pre action.

zdmuser>$ZDM_HOME/bin/zdmcli modify useraction -useraction zdmvalsrc -phase ZDM_VALIDATE_SRC -optype MIGRATE_DATABASE -post

This change is propagated to all of the associated action templates, so you do not need to update the action templates.

Associating an Action Template with a Migration Job

When you run a migration job you can specify the image type that specifies the plug-ins to be run as part of your migration job.

As an example, run the migration command specifying the action template ACTION_ZDM created in previous examples, -imagetype ACTION_ZDM, including the image type results in running the useract.sh and useract1.sh scripts as part of the migration job workflow.

By default, the action plug-ins are run for the specified operational phase on all nodes of the cluster. If the access credential specified in the migration command option -tgtarg2 is unique for a specified target node, then an additional auth argument should be included to specify the auth credentials required to access the other cluster nodes. For example, specify -tgtarg2 nataddrfile:auth_file_with_node_and_identity_file_mapping.

A typical nataddrfile for a 2 node cluster with node1 and node2 is shown here.

node1:node1:identity_file_path_available_on_zdmservice_node

node2:node2:identity_file_path_available_on_zdmservice_node

 none

Parent topic: Preparing for Database Migration