15 Learn About Migrating a Multi-Node Database Cloud Service Deployment to Virtual Machine Database System

If you want to migrate a two-node Oracle Real Application Cluster (Oracle RAC) database created using Oracle Database Cloud Service to an Oracle Cloud Infrastructure Virtual Machine Database System, then you can perform the database migration by using Oracle Data Guard.

For more information about the source and target databases, see the following table:

Information Source Database Target Database
Platform Oracle Cloud Infrastructure Compute Classic Oracle Cloud Infrastructure Compute
Database Type Oracle Real Application Cluster (RAC) Database on Database Cloud Service Classic Oracle RAC Database on Virtual Machine Database System
Creation Mechanism Database Cloud Service UI, CLI, API Oracle Cloud Infrastructure UI, CLI, API
Size 2 Node 2 Node

Architecture

You can migrate Oracle Database releases 12.1.0.2 and 12.2.0.1. Before you migrate your database, you must have Oracle RAC database on Oracle Database Cloud Service and a two-node Oracle RAC Database running on Oracle Cloud Infrastructure.

When you use Oracle Data Guard to perform the migration, the source database is the primary database, and the target database is the standby database.

The following diagram shows the migration process:

To perform the migration, you must follow these general steps:

  1. Plan the migration.

    When you plan the database migration, you begin by inventorying the source environment (the primary database) and then you decide on the best migration strategy. To inventory the source environment, you must perform tasks such as determining the sizes of database files and checking which disaster recovery plans are in place. To decide on the best strategy, you should plan, for example, the best time of day to perform the migration.

  2. Prepare for the migration.

    To prepare to migrate the source database (the primary database) to the target environment (the standby database), you must perform tasks such as ensuring that the database that you want to migrate is running, installing the latest patches for both databases so that they are patched at the same level, and ensuring that the 1521 port is open between the primary database and the standby database. In this solution, the net service name for the source (primary) database is OCIC-ORCL and the net service name for the target (standby) database is OCI-ORCL .

    Note:

    Oracle recommends using the same database name for both databases so that applications can automatically fail over to the new database.
  3. Perform the migration.

    You can perform the database migration by configuring the primary database (the source database) and the standby database (the target database) for Oracle Data Guard, copying the TDE wallets from the primary database to the standby database, and then completing the standby database configuration.

Required Tools to Perform the Migration

Before you begin the database migration, in addition to knowledge of Oracle Cloud Infrastructure, you must have knowledge in several areas of Oracle Database tools.

The tools with which you must be familiar are as follows:

  • SQL*Plus
  • Oracle Data Guard
  • Familiarity with using the srvctl and dgmgrl utilities
  • Familiarity with editing the tnsnames.ora, listener.ora, sqlnet.ora, and oraenv files
  • Familiarity with performing Oracle Data Guard switchover operations
  • (Optionally) Familiarity with generating Oracle Automatic Workload Repository and Oracle Automatic Database Diagnostic Monitor reports
  • (Optionally) Familiarity with Oracle Automatic Storage Management Cluster File System

About Required Services and Roles

This solution requires Oracle Cloud Infrastructure.

Role Required to...
Administrator (SYSDBA and SYSOPER privileges) Perform SYS-related administration tasks.
Administrator (SYSDBG privilege) Perform Oracle Data Guard tasks, if you are using Oracle Database 12c release 2 (12.1.0.2) or later.
Administrator (SYSKM privilege) Perform Transparent Data Encryption tasks, if you are using Oracle Database 12c release 2 (12.1.0.2) or later.

Plan

To plan the Oracle RAC database migration, you must inventory the source environment and decide on the best migration strategy.

Inventory the Source Environment

Inventorying the environment includes tasks such as ensuring that you have the supported Oracle Database versions and configurations.

  • Ensure that you have the supported versions and configurations.

    At a minimum, you should have at least Oracle Database 12c release 1 (12.1.0.2) (standalone).

  • Determine the size of the database files of the source database.

    You can find the total size of the database files of the database that you plan to migrate, including redo log files sizes, by executing the du -s command at the command line. For example:

    du -s /u01/app/oracle/* 

    This value provides information about how much space to allocate for the target database system. Check the name and sizes of data files by querying the V$DATAFILE and V$TEMPFILE dynamic views. If you are using Oracle Automatic Storage Management, then check the data files used by ASM as well.

  • Determine the workload level.

    You can generate an Oracle Automatic Workload Repository report to find a sample of the workload for the source database. Alternatively, if you have an Oracle Diagnostics Pack and Oracle Tuning Pack license, you can generate an Automatic Database Diagnostic Monitor report to find the source database performance over a period of time between specified snapshots. The time model statistics, operating system statistics, and wait events provide a relatively clear measure of the workload, in terms of the operating system capacity.

  • Determine the environment variables that have been set in the source database.

    You may want to use these same settings in the target database.

  • Check the database character set.

    You can find the database character set by issuing the following query:

    SELECT * FROM NLS_DATABASE_PARAMETERS;

    You will need to ensure that the target database will also have this character set.

  • Determine the disaster recovery plan that is currently in place.

    For example, if Oracle Data Guard is already deployed, then you can create a standby database for the migration procedure. (This migration solution will use Oracle Data Guard for the migration.) If off-site backups are used, then you should plan on making a new backup to ship to Oracle Cloud, using Oracle Recovery Manager (Oracle RMAN).

Decide on the Best Migration Strategy

After you inventory your environment, you should decide on the best migration strategy.

Consider the following before you begin the migration process:

  • Take a backup of your current RAC database before starting the migration
  • The best time of day to perform the migration
  • Downtime requirements
  • Database size
  • The source database and the target database platform (endian)
  • Security considerations
  • A strategy for large workloads

Prepare Oracle RAC

To prepare for the migration of a Oracle Real Application Cluster (Oracle RAC) database to an Oracle Cloud Infrastructure server, you must perform multiple preparatory tasks before migration can start.

Add Entries for the Database Instances

Update the /etc/oratab file on the Oracle RAC nodes and add an entry for your database instance as follows:

  1. Use SSH to sign in to the first node of the source database (the primary database) to be migrated.
  2. Update /etc/oratab to add the database instance ID to the database entry by doing the following:
    1. Edit /etc/oratab:
      sudo vi /etc/oratab
    2. Add an entry for your database in the following format:
      $ORACLE_SID:$ORACLE_HOME:N
      Example for node 1:
      orcl1:/u01/app/oracle/product/12.1.0.2/dbhome_1:N
      Example for node 2:
      orcl2:/u01/app/oracle/product/12.1.0.2/dbhome_1:N
  3. Run the oraenv script to set environment variables of the database such as $ORACLE_HOME:
    . oraenv
  4. Repeat the previous steps for the second Oracle RAC node.

Ensure That the Database to be Migrated Is Running

Before you begin the migration process, you must check that the source database (the primary database) to be migrated is running.

  1. Use SSH to sign in to the server where the source database (the primary database) to be migrated is located.
  2. Sign in as the database software owner oracle.
    sudo su - oracle
  3. Execute the oraenv script, which sets the $ORACLE_HOME environment variable.
    . oraenv
  4. Go to the $ORACLE_HOME location.
    cd $ORACLE_HOME

    If the $ORACLE_HOME location has not been set, then use the oraenv script (located in the /usr/local/bin directory) to set the environment, including $PATH, so the lsnrctl and sqlplus commands can resolve without using full path names.

  5. Check the listener status.
    srvctl status listener
  6. If the listener is not running (for example, the output has error TNS-12541: TNS:no listener), then switch to OS user grid and start the listener.
    sudo su - grid  
    srvctl start listener
  7. Check that the database is running.
    sqlplus / as sysdba
    This command should connect you to the database instance and the SQL> prompt should appear.
  8. Check if the database is running in Read Write Mode
    SELECT NAME, OPEN_MODE FROM V$DATABASE;

    Output similar to the following appears:

    NAME              OPEN_MODE
    --------------    ---------
    source_db_name    READ WRITE
  9. Exit the SQL*Plus
    EXIT

Ensure That All Database Components on the Source Database Are Installed on the Target Database

You can find the components that are installed on the source database (the primary database) by querying the DBA_REGISTRY data dictionary view.

  1. Use SSH to sign in to the source database server.
  2. Sign in to SQL*Plus as an administrator user.
    For example:
    sqlplus sys / as sysdba
    Password: password
  3. Make a note of the version of the software that is displayed in the opening banner.
  4. Exit SQL*Plus.
    EXIT
  5. Use the opatch inventory command to find the latest patch set that has been applied.
    For example:
    $ORACLE_HOME/OPatch/opatch lsinventory
  6. Repeat these steps on the second node of the source database and both nodes of the target database (the standby database).
  7. The second node of the source database and both nodes of the target database should have the same or later versions installed.

Create a Standby Database for the Oracle Cloud Infrastructure System

You must create a standby database (the target database) on the Oracle Cloud Infrastructure, in addition to the database that is currently on this system. The creation process for creating this database creates a starter database during provisioning. Create the database system with the host name, shape, and CPU count that your site requires.

Generate SSH Key Pair
To gain local access to the tools, utilities and other resources on Oracle Cloud Infrastructure Virtual Machine Database system, you use Secure Shell (SSH) client software to establish a secure connection and log in as the user oracle or the user opc. To access the standby Virtual Machine Database system, using SSH, you have to use SSH key pair instead of a password to authenticate a remote user. A key pair consists of a private key and public key. You keep the private key on your computer and provide the public key every time you launch an instance. To create key pairs, you can use a third-party tool such as OpenSSH on UNIX-style systems (including Linux, Solaris, BSD, and OS X) or PuTTY Key Generator on Windows.
Create Virtual Cloud Network

When you work with Oracle Cloud Infrastructure, one of the first steps is to set up a virtual cloud network (VCN) for your cloud resources. Ensure that you have set up a VCN before creating a standby database. You can refer to Oracle Cloud Infrastructure documentation for more information on how to create a VCN.

Verify the Virtual Machine Database Shapes Supported by your Tenancy

When you sign up for Oracle Cloud Infrastructure, a set of service limits are configured for your tenancy. The service limit is the quota or allowance set on a resource. For example, your tenancy is allowed a maximum number of compute instances per availability domain. These limits are generally established with your Oracle account representative when you purchase Oracle Cloud Infrastructure.

When you create a standby Virtual Machine Database system, you have to ensure that the Virtual Machine database shape that you select, should closely map to the primary(source) instance. You also MUST ensure that the selected shape is supported by your tenancy.

Verify your tenancy limits and usage (by region):

Note:

If a given resource type has limits per availability domain, the limit and usage for each availability domain is displayed.
  1. Open the Oracle Cloud Services Dashboard. Open the User menu and click Tenancy: <your_tenancy_name>.
  2. Click Service Limits on the left side of the page.

    Your resource limits and usage for the specific region are displayed, broken out by service.

  3. Click on Database, and verify the Virtual Machine database shapes supported by your tenancy.

Your selection of the standby database shape should be a combination of shape that closely maps to primary(source) instance shape along with supported database shapes in your tenancy. Virtual Machine Database system is available in fixed data sizing shapes. Ensure that the shape chosen for creating database should be able to accommodate the source database plus any future sizing requirements. A thumb rule is to use a shape similar or higher in size than source database.

Create Standby Virtual Machine Database System

Note:

Ensure the database is created with the same parameters, such as character set, as the primary database (the source database).
  1. Login to your Oracle Cloud Services Dashboard
  2. Open the navigation menu. Under Services, click Database (NOT Database Classic).
  3. Under Database, click Bare Metal, VM, and Exadata.
  4. Select the compartment in which you want to work.
  5. Click Launch DB System.
  6. In the Launch DB System wizard, enter the following:
    1. DB System Information
      • Compartment: By default, the DB system launches in your current compartment and you can use the network resources in that compartment. Click the click here link in the dialog box if you want to enable compartment selection for the DB system, network, and subnet resources.
      • Display Name: A friendly, display name for the DB system. The name doesn't need to be unique. An Oracle Cloud Identifier (OCID) will uniquely identify the DB system.
      • Availability Domain: The availability domain in which the DB system resides.
      • Shape Type: Select Virtual Machine
      • Shape: The shape to use to launch the DB system. The shape determines the type of DB system and the resources allocated to the system. Choose the Virtual Machine Database Shape that you identified from the previous section.

      • Total Node Count: The number of nodes in the DB system. The number depends on the shape you select. You must specify 2.
      • Oracle Database Software Edition: The database edition supported by the DB system. Choose a database edition which is same or higher than the primary database.

      • Available Storage Size (GB): Enter a size with at least the same size as your primary (source) server.
      • License Type: The type of license you want to use for the DB system. Your choice affects metering for billing.

        • License included means the cost of the cloud service includes a license for the Database service.
        • Bring Your Own License (BYOL) means you are an Oracle Database customer with an Unlimited License Agreement or Non-Unlimited License Agreement and want to use your license with Oracle Cloud Infrastructure. This removes the need for separate on-premises licenses and cloud licenses.
      • SSH Public Key: The public key portion of the key pair you want to use for SSH access to the DB system. Use the public key that you generated in the previous section.
    2. Network Information
      • Virtual Cloud Network: The VCN in which to launch the DB system. Select the VCN that you created in the previous section.
      • Subnet Compartment: The compartment containing a subnet within the cloud network to attach the DB system to.
      • Client Subnet: The subnet to which the DB system should attach.

      • Hostname Prefix: Your choice of host name for the DB system. The host name must begin with an alphabetic character, and can contain only alphanumeric characters and hyphens (-).

        • The maximum number of characters allowed is 30. The host name must be unique within the subnet. If it is not unique, the DB system will fail to provision.

      • Host Domain Name: The domain name for the DB system. If the selected subnet uses the Oracle-provided Internet and VCN Resolver for DNS name resolution, this field displays the domain name for the subnet and it can't be changed. Otherwise, you can provide your choice of a domain name. Hyphens (-) are not permitted.

      • Host and Domain URL: Combines the host and domain names to display the fully qualified domain name (FQDN) for the database. The maximum length is 64 characters.
    3. Database Information
      • Database Name: The name for the database. The database name must begin with an alphabetic character and can contain a maximum of eight alphanumeric characters. Special characters are not permitted. Specify a name that is different from the primary database (the source database) name.
      • Database Version: The version of the initial database created on the DB system when it is launched. After the DB system is active, you can create additional databases on it. You can mix database versions on the DB system, but not editions.
      • PDB Name: Omit this setting because the pluggable database (PDB) will be created later on, when you perform the Oracle RMAN duplicate step.
      • Database Admin Password: Enter the same SYS password that is used for the primary database (the source database). It should be a strong password for SYS, SYSTEM, TDE wallet, and PDB Admin. The password must be 9 to 30 characters and contain at least 2 uppercase, 2 lowercase, 2 numeric, and 2 special characters. The special characters must be _, #, or -. The password must not contain the username (SYS, SYSTEM, and so on) or the word "oracle" either in forward or reversed order and regardless of casing. (If the primary database SYS password does not fit this requirement, then you can change it after you complete these settings.)

      • Confirm Database Admin Password: Re-enter the Database Admin Password you specified.
      • Automatic Backup: Check the check box to enable automatic incremental backups for this database.
      • Database Workload: Select the workload type that best suits your application.

        • Online Transactional Processing (OLTP) configures the database for a transactional workload, with a bias towards high volumes of random data access.
        • Decision Support System (DSS) configures the database for a decision support or data warehouse workload, with a bias towards large data scanning operations.
      • Character Set: The character set for the database. The default is AL32UTF8.
      • National Character Set: The national character set for the database. The default is AL16UTF16.
      • Tags: Optionally, you can apply tags. If you have permissions to create a resource, you also have permissions to apply free-form tags to that resource. To apply a defined tag, you must have permissions to use the tag namespace. For more information about tagging, see Resource Tags. If you are not sure if you should apply tags, skip this option (you can apply tags later) or ask your administrator.
  7. Click Launch DB System. The DB system appears in the list with a status of Provisioning. The DB system's icon changes from yellow to green (or red to indicate errors).
  8. Wait for the DB system's icon to turn green, with a status of Available, and then click the highlighted DB system name. Details about the DB system are displayed.
  9. Note the IP addresses; you'll need the private or public IP address, depending on network configuration, to connect to the DB system.
  10. Check the SYS password on the primary (source) database. If it does not meet the Oracle Cloud Infrastructure password requirements, then change it to match the password that you created for the standby database (the target database).

Ensure That Port 1522 and 1521 Is Open Between the Primary Database and the Standby Database

You must ensure that port 1522 is open on the primary database (the source database) and port 1521 on the standby database (the target database) to allow the databases to connect.

Enable Communication from the Oracle Cloud Infrastructure System to the Oracle Cloud Infrastructure Classic System

On the Oracle Cloud Infrastructure Classic system, you must open port 22 and 1522 for ingress traffic from the Oracle Cloud Infrastructure virtual machine system.

Configure the Standby Database to Primary Database Communication Settings in the Oracle Cloud My Services Console

You can use the Oracle Cloud My Services console to configure the first part of the communication settings.

  1. Sign in to My Services console.
  2. From the Dashboard, click Database Classic.
  3. On Service: Oracle Database Cloud Service, click Open Service Console, in the right-hand side of the page.
  4. From the menu icon menu for your database deployment, select Access Rules.
    The Access Rules page is displayed.
  5. Locate the ora_p2_dblistener rule to enable ingress traffic on port 1522 from the public internet.
  6. From the menu icon menu for the located rule, select Enable.
    The Enable Access Rule window is displayed.
  7. Click Enable.
    The Enable Access Rule window closes and the rule is displayed as enabled in the list of rules. The given port on the compute node is opened to the public internet.
  8. Create the source-db-ssh rule to open ingress traffic on port 22 from the public internet.
    if port 22 is not already open add this access rule.
    In the "Create Access Rule" wizard enter the following information:
    1. Rule Name: source-db-ssh
    2. Source: PUBLIC-INTERNET (select from drop down menu)
    3. Destination: The database name from the drop down menu
    4. Destination Port(s): 22
    5. Protocol: TCP (select from drop down menu)
  9. Click Create.
  10. Set Status to Enabled.
  11. Use SSH to sign in to the server where the standby database (the target database) is located.
  12. Test SSH on port 22 from the Oracle Cloud Infrastructure system to the Oracle Database Cloud Service system.
    Port 22 on the Oracle Database Cloud Service system is open by default from the public internet.
    1. Transfer the SSH private key to the /home/opc/.ssh directory by using a secure file transfer utility such as SCP.
    2. As the Oracle Cloud Infrastructure opc user, test SSH by running the following command. In this example, the private key is named privateKey:
      ssh -i /home/opc/.ssh/privateKey opc@source_ip
Complete the Oracle Cloud Infrastructure to Oracle Cloud Infrastructure Classic Communication Settings on the Command Line

You must set a TCP socket size, edit the etc/host file, and update the tnsnames.ora file on the primary database (the source database).

  1. SSH to the first node of the primary database (the source database)
  2. Connect as root.
    sudo su -
  3. (Optional) Set the TCP socket size. For example:
    sysctl -w net.core.rmem_max=10485760 
    sysctl -w net.core.wmem_max=10485760

    Note:

    The TCP socket size is set to increase performance during the migration. This setting may not be ideal for production databases.
  4. Connect as the oracle user.
    su - oracle
  5. Execute the oraenv script, which sets the $ORACLE_HOME environment variable.
    . oraenv
  6. On both Oracle RAC nodes, in the $ORACLE_HOME/network/admin/tnsnames.ora file on the primary database (the source database) on Oracle Cloud Infrastructure Classic, add a TNS entry similar to the following:

    Note:

    Replace source_node1_ip, source_node2_ip, source_server_name with the parameters of the primary database (the source database). Replace target_node1_ip,target_node2_ip, target_server_name with the parameters of the standby database (the target database).
    MIGRAC_OCIC_s1 =
      (DESCRIPTION =
        (ADDRESS_LIST=
          (ADDRESS = (PROTOCOL = TCP)(HOST = source_node1_ip)(PORT = 1522))
          (ADDRESS = (PROTOCOL = TCP)(HOST = source_node2_ip)(PORT = 1522))
        )
        (CONNECT_DATA =
          (UR=A)
          (SERVER = DEDICATED)
          (SERVICE_NAME = source_service_name)
        )
      )
      
    MIGRAC_OCI_s2 =
      (DESCRIPTION =
        (ADDRESS_LIST=
          (ADDRESS = (PROTOCOL = TCP)(HOST = target_node1_ip)(PORT = 1521))
          (ADDRESS = (PROTOCOL = TCP)(HOST = target_node2_ip)(PORT = 1521))
        )
        (CONNECT_DATA =
          (UR=A)
          (SERVER = DEDICATED)
          (SERVICE_NAME = target_service_name)
        )
      )
    

    Note:

    (UR=A) is optional for RAC.
  7. Test the SQL*Plus connect from the standby database (the target database) system to the primary database (target database) on port 1521.
    sqlplus sys@migrac_oci_s2 as sysdba
    Enter password: password
    
    SQL> SELECT NAME FROM V$DATABASE;
  8. Exit SQL*Plus.
    exit
  9. Repeat these steps on the second node of the primary database (source database).
Enable Communication from the Oracle Cloud Infrastructure Classic System to the Oracle Cloud Infrastructure System

On the Oracle Cloud Infrastructure Database system, you must open ports 22 and 1521 for ingress traffic from the Oracle Cloud Infrastructure Classic system.

Configure the Oracle Cloud Infrastructure Classic to Oracle Cloud Infrastructure Communication Settings in the Oracle Cloud Infrastructure Console

You can use the Oracle Cloud Infrastructure console to configure the first part of the communication settings.

  1. Make a note of the public IP address of the Oracle Cloud Infrastructure Classic server.
  2. Sign in to the Oracle Cloud Infrastructure console.
  3. On the left side of the page, select your compartment, and then select the Networking tab at the top of the page.
  4. On the Virtual Cloud Networks in the Compartment page, select the name of your network.
  5. On the page that is labeled with the network name, select Security Lists from the left navigation pane.
  6. On the Security Lists page, select the list that you want to view.
  7. On the Security_list_name Security List for network_name page, click Edit all rules.
  8. Scroll to find the rule that you want to change, or click Add Rule for either the Ingress or Engress rule.
  9. Update the database system security list to the Oracle Cloud Infrastructure Classic server public IP that you obtained in the first step.
    By default, port 22 for SSH is enabled on Oracle Cloud Infrastructure systems for traffic from the public internet.
    1. Set SOURCE CIDR to the IP address on the Oracle Cloud Infrastructure Compute Classic server.
    2. Set IP PROTOCOL to TCP.
    3. Set SOURCE PORT RANGE to ALL.
    4. Set DESTINATION PORT RANGE to 1521.
Complete the Oracle Cloud Infrastructure Classic to Oracle Cloud Infrastructure Communication Settings on the Command Line

You must edit the $ORACLE_HOME/network/admin/tnsnames.ora file, set the TCP socket size on the standby database (the target database).

  1. SSH to the first node of the standby database (the target database).
  2. On the standby database (the target database), connect as the database software owner oracle.
    sudo su - oracle
  3. Execute the oraenv script, which sets the $ORACLE_HOME environment variable.
    . oraenv
  4. On both nodes, in the $ORACLE_HOME/network/admin/tnsnames.ora file on the Oracle Cloud Infrastructurevirtual machine system, add a TNS entry for each of the two databases:
    In this example, the primary database TNS name is OCIC-ORCL and the standby database TNS name is OCI-ORCL.

    Note:

    Replace source_node1_ip, source_node2_ip, source_server_name with the parameters of the primary database (the source database).

    Replace target_node1_ip, target_node2_ip, target_server_name with the parameters of the standby database (the target database)

    MIGRAC_OCIC_s1 =
      (DESCRIPTION =
        (ADDRESS_LIST=
          (ADDRESS = (PROTOCOL = TCP)(HOST = source_node1_ip)(PORT = 1522))
          (ADDRESS = (PROTOCOL = TCP)(HOST = source_node2_ip)(PORT = 1522))
        )
        (CONNECT_DATA =
          (UR=A)
          (SERVER = DEDICATED)
          (SERVICE_NAME = source_service_name)
        )
      )
      
    MIGRAC_OCI_s2 =
      (DESCRIPTION =
        (ADDRESS_LIST=
          (ADDRESS = (PROTOCOL = TCP)(HOST = target_node1_ip)(PORT = 1521))
          (ADDRESS = (PROTOCOL = TCP)(HOST = target_node2_ip)(PORT = 1521))
        )
        (CONNECT_DATA =
          (UR=A)
          (SERVER = DEDICATED)
          (SERVICE_NAME = target_service_name)
        )
      )
  5. Use SQL*Plus to test connection from the Oracle Cloud Infrastructure system to the Oracle Cloud Infrastructure Classic database on port 1521.
    sqlplus sys@migrac_orcl_s2 as sysdba
    Enter password: password
    (At this stage, any SQL statements you execute will fail because the standby database has not been fully configured yet.)
  6. At the command line, connect as root.
    sudo su -
  7. (Optional) On both nodes, set the TCP socket size.For example:
    sysctl -w net.core.rmem_max=10485760 
    sysctl -w net.core.wmem_max=10485760

    Note:

    The TCP socket size is set to increase performance during the migration. This setting may not be ideal for production databases.
  8. Repeat these steps on the second node of the standby database (target database).
Ensure That Bundle Patches Have Been Applied and Are in Sync

Ensure that the patch level on the primary database (the source database) Oracle Cloud Infrastructure Classic system is earlier or the same as the patch level on the standby database (the target database) Oracle Cloud Infrastructure system.

  1. Use SSH to sign in to the first node of the primary database (the source database).
  2. Check the patch level on the primary database as follows:
    1. To find a brief listing of patches:
      $ORACLE_HOME/OPatch/opatch lspatches
    2. To find a detailed listing of patches:
      $ORACLE_HOME/OPatch/opatch lsinventory
  3. Make a note of the patch level.
  4. Repeat steps 1 to 3 for the second node of the primary database (the source database).
  5. Use SSH to sign in to the nodes of the standby database (the target database).
  6. Check the patch level on the nodes of the standby database by running the opatch lsinventory command.
  7. Compare the patch levels of the nodes of the databases. Ensure that the standby system has a bundle patch that is either equal to or later than the bundle patch that is on the primary database.
  8. If you must install a later patch on the standby database (the target database), then access My Oracle Support: https://support.oracle.com/
  9. Download the correct version of the patch to the standby database (the target database).
  10. Extract the bundle patch.
  11. List the available patches.
    $ORACLE_HOME/OPatch/opatch lspatches
  12. Apply the patch.
    $ORACLE_HOME/OPatch/opatch apply patch_number

Migrate Oracle RAC

To perform the migration of a Oracle RAC database from an Oracle Cloud Infrastructure Classic server to an Oracle Cloud Infrastructure Database system, you can use Oracle Data Guard. You must configure the database on Oracle Cloud Infrastructure Classic as the primary database (the source database), which you migrate to a standby database (the target database) on Oracle Cloud Infrastructure on virtual machine systems.

Configure the Primary (Source) Database

To configure the primary database (the source database), you configure Oracle Data Guard and modify the listener.ora and tnsnames.ora files for the standby database (the target database).

Configure the Primary Database for the Standby Database

In this configuration, you configure the primary (source) database to use Oracle Data Guard.

  1. Use SSH to sign in to the primary database (the source database) server.
  2. On the standby database (the target database), connect as the database software owner oracle.
    sudo su - oracle
  3. Execute the oraenv script, which sets the $ORACLE_HOME environment variable.
    . oraenv
  4. Sign in to the database instance as a user who has administrator privileges. For example:
    sqlplus / as sysoper
  5. Ensure that the database is in ARCHIVELOG mode.
    ARCHIVE LOG LIST
  6. If the output for Database log mode is No Archive Mode and the output for Automatic archival is Disabled, then do the following:
    1. Exit SQL*Plus
      SQL> exit
    2. Shut down the database.
      srvctl stop database -db db_unique_name
    3. Restart the database in mount mode
      srvctl start database -db db_unique_name -o mount
    4. Sign in to the database instance as a user who has administrator privileges. For example:
      sqlplus / as sysoper
    5. Enable archive log mode.
      ALTER DATABASE ARCHIVELOG;
    6. Ensure that the database is now in archive log mode.
      ARCHIVE LOG LIST
      The output for the output for Database log mode should be Archive Mode and the output for Automatic archival is Enabled.
    7. Open the database.
      ALTER DATABASE OPEN;
  7. Connect with the SYSDBA administrator privilege.
    CONNECT / AS SYSDBA
  8. For a multitenant environment, do the following:
    1. Check the status of the PDBS.
      SHOW PDBS
    2. If the PDBS are not open, then open them.
      ALTER PLUGGABLE DATABASE ALL OPEN;
  9. Ensure that the database is in force logging mode. For example:
    SELECT NAME, OPEN_MODE, FORCE_LOGGING FROM V$DATABASE;
  10. If necessary, enable force logging.
    ALTER DATABASE FORCE LOGGING;
  11. Check the configuration.
    SELECT NAME, CDB, OPEN_MODE, FORCE_LOGGING FROM V$DATABASE;
    The FORCE_LOGGING column should be YES.
  12. Use the SHOW PARAMETER command to check the following database parameters:
    1. DB_NAME and DB_UNIQUE_NAME: Ensure that these names are different from the names that are used on the target database.
    2. REMOTE_LOGIN_PASSWORDFILE: This parameter must be set to EXCLUSIVE.
Add Static Services to the Primary Database listener.ora File

In this section, you must add a new static listener to listener.ora and restart the listener.

  1. Use SSH to sign in to the first node of the primary (source) database.
  2. At the command line, connect as grid user.
    sudo su - grid
  3. Modify the /u01/app/12.2.0.1/grid/network/admin/listener.ora file to include the static listener. The following example shows the format to use for one static listener:
    SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
          (SDU=65535)
          (GLOBAL_DBNAME = source_db_unique_name.source_db_domain)
          (ORACLE_HOME = source_oracle_home)
          (ENVS="TNS_ADMIN= source_oracle_home/network/admin")
          (SID_NAME = source_db_name)
        )
      )
  4. Stop the listener.

    Note:

    Stopping and starting the static listener can affect new connections to the database for a few seconds.
    srvctl stop listener -l LISTENER
  5. Restart the listener.
    srvctl start listener -l LISTENER
  6. Check the listener status.
    lsnrctl status
  7. Validate that there are entries in the output with the status UNKNOWN.
  8. Repeat the previous steps for the second node of the primary database (the source database).
Configure the Primary Database Parameters

After you configure the primary (source) database and add static services to the primary database listener.ora file, you can configure the Oracle Data Guard parameters on the primary database.

  1. Use SSH to sign in to the first RAC node of the primary database (the source database).
  2. Sign in to the database instance as a user who has the SYSDBA administrator privilege.
    sqlplus / as sysdba
    Enter password: password
  3. Enable automatic standby file management.
    ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SID='*' SCOPE=BOTH;
  4. Set the archive lag target.
    ALTER SYSTEM SET ARCHIVE_LAG_TARGET=1800 SID='*' SCOPE=BOTH;
  5. Identify the Oracle Broker configuration file names and locations. The following statements depend on the type of database storage.
    ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='/u02/app/oracle/oradata/<db_unique_name>/dr1<db_unique_name>.dat' SID='*' SCOPE=BOTH; SCOPE=BOTH;
    ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='/u03/app/oracle/fast_recovery_area/<db_unique_name>/dr2<db_unique_name>.dat' SID='*' SCOPE=BOTH;
  6. Enable the Oracle Broker DMON process for the database.
    ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;
  7. (Optional) Set the DB_BLOCK_CHECKING and DB_BLOCK_CHECKSUM parameters.
    ALTER SYSTEM SET DB_BLOCK_CHECKING=FULL SID='*' SCOPE=BOTH;
    ALTER SYSTEM SET DB_BLOCK_CHECKSUM=FULL SID='*' SCOPE=BOTH;
  8. (Optional) Set the log buffer to 256 megabytes.
    ALTER SYSTEM SET LOG_BUFFER=268435456 SID='*' SCOPE=BOTH;
  9. Set the DB_LOST_WRITE_PROTECT parameter to TYPICAL.
    ALTER SYSTEM SET DB_LOST_WRITE_PROTECT=TYPICAL SID='*' SCOPE=BOTH;
  10. Enable the database flashback feature. The minimum recommended value for DB_FLASHBACK_RETENTION_TARGET is 120 minutes.
    ALTER DATABASE FLASHBACK ON;
    ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=120; 
    ALTER SYSTEM ARCHIVE LOG CURRENT;
  11. Add the standby redo logs, based on the online redo log. You can use the query below to determine the number and size (in bytes) of the ORLs. The size of the standby redo logs must be the same as the online redo logs, but you must add one or more additional standby redo logs than there are online redo logs. In the following example, four online redo logs exist, so you must add at least five standby redo logs. In other words, for each thread, you must specify the current redo logs plus at least one, and then use the same size for it as the original redo logs.
    1. Execute the following query to determine the number, and size in bytes, of the Oracle redo logs.
      SELECT GROUP#, BYTES FROM V$LOG;
      The output should be similar to the following.
      GROUP# BYTES
      ------ ----------
      1      1073741824
      2      1073741824
      3      1073741824
      4      1073741824
    2. For the first thread, specify the current redo logs plus one more, and use the same size as the current redo logs. For example:
      ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
      GROUP 5 SIZE 1073741824,
      GROUP 6 SIZE 1073741824, 
      GROUP 7 SIZE 1073741824,
      GROUP 8 SIZE 1073741824,
      GROUP 9 SIZE 1073741824;
    3. For the second thread, specify the current redo logs plus one more, and use the same size as the current redo logs. For example:
      ALTER DATABASE ADD STANDBY LOGFILE THREAD 2
      GROUP 10 SIZE 1073741824,
      GROUP 11 SIZE 1073741824, 
      GROUP 12 SIZE 1073741824,
      GROUP 13 SIZE 1073741824,
      GROUP 14 SIZE 1073741824;
    4. Verify that you created the correct number of standby redo logs.
      SELECT GROUP#, BYTES FROM V$STANDBY_LOG;
      Output similar to the following should appear:
          GROUP#      BYTES
      ---------- ----------
               5 1073741824
               6 1073741824
               7 1073741824
               8 1073741824
               9 1073741824
              10 1073741824
              11 1073741824
              12 1073741824
              13 1073741824
              14 1073741824
      10 rows selected.

Configure the Standby (Target) Database

To configure the standby (target) database, you must drop the standby database and then modify the oratab, listener.ora, and tnsnames.ora files.

Add Entries for the Database Instances

Update the /etc/oratab file on the Oracle RAC nodes and add an entry for your database instance as follows:

  1. Use SSH to sign in to the first node of the source database (the primary database) to be migrated.
  2. Update /etc/oratab to add the database instance ID to the database entry by doing the following:
    1. Edit /etc/oratab:
      sudo vi /etc/oratab
    2. Add an entry for your database in the following format:
      $ORACLE_SID:$ORACLE_HOME:N
      Example for node 1:
      orcl1:/u01/app/oracle/product/12.1.0.2/dbhome_1:N
      Example for node 2:
      orcl2:/u01/app/oracle/product/12.1.0.2/dbhome_1:N
  3. Run the oraenv script to set environment variables of the database such as $ORACLE_HOME:
    . oraenv
  4. Repeat the previous steps for the second Oracle RAC node.
Drop the Standby (Target) Database
  1. Use SSH to log in to the standby (target) server.
  2. Switch to the oracle user that is the database owner.
  3. Sign in to the database instance as a user who has the SYSDBA administrator privilege.
    sqlplus / as sysdba
    Enter password: password
  4. Uncluster the database as follows:
    alter system set cluster_database=false sid='*' scope=spfile;
    System altered.
  5. Exit SQL*Plus:
     exit
  6. Stop the database using srvctl:
    srvctl stop database -db database_unqiue_name
  7. Sign in to the database instance as a user who has the SYSDBA administrator privilege.
    sqlplus / as sysdba
    Enter password: password
  8. Start up and mount the database in restricted mode:
     startup mount restrict
    ORACLE instance started.
    Total System Global Area 7516192768 bytes
    Fixed Size                  2941872 bytes
    Variable Size            1409289296 bytes
    Database Buffers         6073352192 bytes
    Redo Buffers               30609408 bytes
    Database mounted.
  9. Drop the standby database:
     drop database;
    Database dropped.
Add Static Services to the Standby Database listener.ora File

After you add static services to the standby database (the source database) listener.ora file, you must restart the listener. .

  1. Use SSH to sign in to the first Oracle RAC node of the standby database (the source database) server.
  2. At the command line, connect as grid user.
    sudo su - grid
  3. Modify the $ORACLE_HOME/network/admin/listener.ora file to include the static listener. The following example shows the format to use for one static listener:
    SID_LIST_LISTENER=
     (SID_LIST=
      (SID_DESC=
      (SDU=65535)
      (GLOBAL_DBNAME = standby_db_unique_name.standby_db_domain)
      (SID_NAME = standby_db_sid)
      (ORACLE_HOME= standby_oracle_home)
      (ENVS="TNS_ADMIN= standby_oracle_home/network/admin")
      )
    )
  4. Use the srvctl utility to stop the listener.
    srvctl stop listener -l LISTENER
  5. Restart the listener.
    srvctl start listener -l LISTENER
  6. Check the listener status.
    lsnrctl status

    Note:

    output is the new listener in the status UNKNOWN
  7. Repeat these steps for the second Oracle RAC node.
Copy TDE Wallets from the Primary Database to the Standby Database

You can manually copy the TDE wallet files from the primary database (the source database) system to the standby database (the target database) system by using Secure Copy Protocol (SCP).

Compress the TDE Wallet

You must perform this operation in the primary database (the source database).

  1. Use SSH to sign in to the primary database (the source database) server.
  2. At the command line, connect as the database software owner oracle.
    sudo su - oracle
  3. Execute the oraenv script, which sets the $ORACLE_HOME environment variable.
    . oraenv
  4. To find the wallet location, sign in to the primary database (the source database) instance with the SYSDBA administrator privilege.
    sqlplus / as sysdba
  5. Query the WRL_PARAMETER column of the V$ENCRYPTION_WALLET dynamic view to find the directory where the wallet is located.
    SELECT * FROM V$ENCRYPTION_WALLET;
  6. Exit SQL*Plus.
    exit
  7. Go to the directory where the wallet files are located. For example:
    cd /u02/app/oracle/admin/source_db_unique_name
  8. Use the tar command to compress the TDE wallet. For example:
    tar cvf tde_wallet.tar ./tde_wallet
    Output similar to the following appears:
    ./tde_wallet/
    ./tde_wallet/ewallet.p12
    ./tde_wallet/cwallet.sso
    ./tde_wallet/ewallet_2018021607225910.p12
Copy the TDE Wallet and Set Permissions on the Wallet Directory

After you back up the TDE wallet file, you must create a directory for the wallet and set permissions on this directory.

  1. Copy the wallet tar file to a tmp directory. For example:
    cp tde_wallet.tar /tmp/
  2. Exit to become the OPC user.
    $ exit
  3. Copy the private key from your local host to the primary database.
    By default, the private keys aren't stored on the DBs
    scp -i /home/opc/.ssh/privateKey opc@<Primary DB IP>:/home/opc/.ssh/
  4. Use SCP to copy the wallet files from the primary database (the source database) to the standby database (the target database), in the /opt/oracle/dcs/commonstore/wallets/tde/$ORACLE_UNQNAME directory. For example:
    scp -i /home/opc/.ssh/privateKey /tmp/tde_wallet.tar opc@<Standby DB IP>:/tmp/
    Output similar to the following appears:
    tde_wallet.tar
    100% 20KB 20.0KB/s 00:00
  5. Use SSH to sign in to the target database server.
  6. Sign in as database software owner oracle.
    sudo su - oracle
  7. Execute the oraenv script, which sets the $ORACLE_HOME environment variable.
    . oraenv
  8. To find the wallet location, display the contents of sqlnet.ora:
    cat $ORACLE_HOME/network/admin/sqlnet.ora
  9. The ENCRYPTION_WALLET_LOCATION parameter displays the location of the wallet. For example:
    ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/opt/oracle/dcs/commonstore/wallets/tde/$ORACLE_UNQNAME)))
  10. Go to the target wallet directory. For example:
    cd /opt/oracle/dcs/commonstore/wallets/tde
  11. Check that the correct wallet is in this directory.
    ls target_db_unique_name
  12. Back up the wallet file. For example:
    mv target_db_unique_name target_db_unique_name.old
  13. Create a directory in which to store the wallet. For example:
    mkdir target_db_unique_name
  14. Check the permissions on the wallet directory.
    ls -ld target_db_unique_name
  15. If necessary, give the database software owner oracle read, write, and execute permissions.
    chmod 700 target_db_unique_name
  16. Check the permissions again.
    ls -ld target_db_unique_name
  17. Copy the wallet tar file to the current directory.
    cp /tmp/tde_wallet.tar .
  18. Check the permissions.
    ls -rlt
    Output similar to the following appears:
    total 124
    drwx------ 2 oracle oinstall 20480 Feb 16 09:25 target_db_unique_name.old
    drwx------ 2 oracle oinstall 20480 Feb 16 10:16 target_db_unique_name
    -rw-r--r-- 1 oracle oinstall 20480 Feb 16 10:17 tde_wallet.tar
Complete the TDE Wallet Process

You must extract the TDE wallet file tar and then move its contents to the wallet directory on the standby database (the target database).

  1. On the standby database (the target database), ensure that you are in the correct wallet directory. For example:
    pwd
     
    # Output similar to the following should appear:
    /opt/oracle/dcs/commonstore/wallets/tde
  2. Extract the tar file.
    tar xvf tde_wallet.tar
    Output similar to the following should appear:
    ./tde_wallet/ewallet.p12
    ./tde_wallet/ewallet_2018050819024979.p12
    ./tde_wallet/cwallet.sso
  3. Move the tde_wallet contents to the wallet directory on the standby database (the target database).
    mv ./tde_wallet/* ./target_db_unique_name
  4. Remove the tde_wallet contents from the standby database (the target database).
    rm -rf ./tde_wallet
Configure the Standby Initialization Parameter File and Start the Instance in NOMOUNT Mode

After you configure the standby initialization file, then you can restart the database in NOMOUNT mode.

  1. Use SSH to sign in to the standby database (the target database) server.
  2. Sign in as the database software owner oracle.
    sudo su - oracle
  3. Execute the oraenv script, which sets the $ORACLE_HOME environment variable.
    . oraenv
  4. Go to the dbs directory.
    cd $ORACLE_HOME/dbs
  5. Create a temporary initialization parameter file, named init_<sid>.orawhere <sid> is the target database SID as follows:
    echo "*.db_name='db_name'" > $ORACLE_HOME/dbs/init<sid>.ora
    echo "*.db_unique_name='target_db_unique_name'" >> $ORACLE_HOME/dbs/init<sid>.ora
  6. Back up the existing password file, if one exists. For example:
    mv $ORACLE_HOME/dbs/orapw<sid> $ORACLE_HOME/dbs/orapw<sid>.old
  7. Create a new password file. For example:
    orapwd file=$ORACLE_HOME/dbs/orapwtarget password=admin_password_for_primary entries=5
  8. Change the password file in cluster to non-ASM password file:
    $ srvctl modify database -d <oci_db_unique_name> -pwfile $ORACLE_HOME/dbs/orapw<oci_oracle_sid>
    $ srvctl config database -d <oci_db_unique_name>
  9. Connect to the standby database (the target database) instance as a user who has the sysdba administrator privilege. For example:
    sqlplus / as sysdba 
  10. Shut down the database. For example:
    shutdown immediate
  11. Restart the database in NOMOUNT mode using the init<sid>.ora initialization parameter file.
    startup force nomount PFILE=?/dbs/init<sid>.ora
Duplicate the Target Database for the Standby from the Active Database

You can execute a script to duplicate the standby database (the target database). If the primary database (the source database) is large, then you can allocate additional channels to improve its performance. For a newly installed database, one channel typically runs the database duplication in a couple of minutes. Ensure that no errors occur after you run the Oracle Recovery Manager (Oracle RMAN) duplication operation. If errors occur, then restart the database by using the initialization parameter file (not spfile), in case it is generated under the $ORACLE_HOME/dbs directory as part of the Oracle RMAN duplication process.

  1. Connect to the standby database (the target database) as the database software owner oracle.
    su - oracle
  2. Execute the oraenv script, which sets the $ORACLE_HOME environment variable.
    . oraenv
  3. Create dup.rcv
    vi dup.rcv
    Paste the contents of the script while updating the required variables.
  4. With the standby database (the target database) in NOMOUNT mode, connect to Oracle RMAN.
    rman
    RMAN> connect target sys@<primary_db_tnsnames_name>
    target database Password: password
    RMAN> connect auxiliary sys@<standby_db_tnsnames_name>
    auxiliary database Password: password
  5. Execute the following script to duplicate the target database for a standby database from an active database. The following example shows the dup.rcv script, which must be user-created, and is based on the My Oracle Support note 2369137. RMAN Active Duplicate Runs Into RMAN-06217 -- PUSH & PULL method Explanation (Doc ID 2369137.1). In this example, the dup.rcv script has been customized to push duplication (image copies) from the file system to Oracle Automatic Storage Management (Oracle ASM). Other options such as from the file system to Oracle Automatic Storage Management Cluster File System, or Oracle ASM to Oracle ASM, would require changes to the file destination parameters and the file name conversion parameters.
    @dup.rcv
    Output similar to the following appears:
    
    RMAN> run {
    2> allocate channel prmy1 type disk;
    3> allocate channel prmy2 type disk;
    4> allocate channel prmy3 type disk;
    5> allocate channel prmy4 type disk;
    6> allocate auxiliary channel stby1 type disk;
    7> allocate auxiliary channel stby2 type disk;
    8> allocate auxiliary channel stby type disk;
    9> duplicate target database for standby from active database dorecover
    10> spfile
    11> parameter_value_convert '/u02/app/oracle/oradata/source_db_name','+DATA'
    12> Set CLUSTER_DATABASE='FALSE'
    13> set db_unique_name='<target_db_unique_name>'
    14> set db_create_file_dest='+DATA'
    15> set db_create_online_log_dest_1='+RECO'
    16> set db_recovery_file_dest='+RECO'
    17> set audit_file_dest='/u01/app/oracle/admin/db_name/adump'
    18> set control_files='+DATA','+RECO'
    19> set dg_broker_config_file1='+DATA/<target_db_unique_name>/dr1<target_db_unique_name>.dat'
    20> set dg_broker_config_file2='+RECO/<target_db_unique_name>/dr2<target_db_unique_name>.dat'
    21> set dispatchers='(PROTOCOL=TCP) (SERVICE=<target_db_name>XDB)'
    22> set instance_name='<target_db_name>'
    23> set db_domain='<target_db_domain>'
    24> set db_recovery_file_dest='+RECO'
    25> ;
    26> }
Post Oracle Recovery Manager Duplication Steps

After you complete the Oracle Recovery Manager (Oracle RMAN) duplication operation, you should perform these clean-up tasks on the standby database (the target database).

Update the Password File

Update the password file as follows:

  1. Move the password file back to ASM:
    [oracle@oci_node1 ~]$ cp $ORACLE_HOME/dbs/orapw<oci_node1_oracle_sid> /tmp/orapw<oci_node1_oracle_sid>
  2. Exit to the opc user:
    [oracle@oci_node1 ~]$ exit
  3. Switch to the grid user:
    [opc@oci_node1 ~]$ sudo su - grid
  4. Switch to ASMCMD prompt:
    [grid@oci_node1 ~]$ asmcmd
  5. Copy the password in ASMCMD:
    ASMCMD> pwcopy --dbuniquename <oci_db_unique_name> '/tmp/orapw<oci_node1_oracle_sid>' '+DATA'

    Note:

    The command may display errors as ASM is a different version than the database. The errors can be ignored as the registration issue is fixed later in this procedure.

  6. Navigate to the +DATA/<OCI_DB_UNIQUE_NAME>/PASSWORD/ directory to find the system_generated_id to use in step 9.
    ASMCMD> cd +DATA/<OCI_DB_UNIQUE_NAME>/PASSWORD/ 
    ASMCMD> ls -lt
  7. Exit ASMCMD and the grid user:
    ASMCMD> exit
    [grid@oci_node1 ~]$ exit
  8. Switch to the oracle user:
    [opc@oci_node1 ~]$ sudo su - oracle
  9. Execute the oraenv script, which sets the $ORACLE_HOME environment variable.
    . oraenv
  10. Modify password file in cluster to ASM:
    [oracle@oci_node1 ~]$ srvctl modify database -d <oci_db_unique_name> -pwfile +DATA/<OCI_DB_UNIQUE_NAME>/PASSWORD/pwd<oci_db_unique_name><system_generated_id>
    [oracle@oci_node1 ~]$ rm $ORACLE_HOME/dbs/orapw<oci_node1_oracle_sid>
Enable Oracle Flashback

You should enable Oracle Flashback.

  1. Use SSH to sign in to the standby database (the target database) server.
  2. Sign in as the database software owner oracle.
    sudo su - oracle
  3. Sign in to the database instance as a user who has the SYSOPER administrator privilege. For example:
    sqlplus / as sysoper
    Enter password: password
  4. Enable Oracle Flashback.
    ALTER DATABASE FLASHBACK ON;
  5. Connect as a user with the SYSDBA administrator privilege.
    CONNECT / AS SYSDBA
  6. Set the flashback retention target.
    ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=120;
Move the spfile File to Oracle Automatic Storage Management

You should move the spfile file to Oracle Automatic Storage Management.

  1. Use SSH to connect to the standby database (the target database) server.
  2. Sign in to the database instance as a user who has the SYSOPER administrator privilege.
  3. Create and move the spfile file to Oracle Automatic Storage Management.
    1. Create the pfile.
      create pfile='/tmp/init<sid>.ora' from spfile;
    2. Exit SQL*Plus:
      SQL> exit
    3. Edit the pfile:
      vi /tmp/init<sid>.ora
    4. Delete the following line from the file:
      *.instance_name=<sid_for_node1>
    5. Add the following lines for each node:
      <sid_for_node1>.instance_name='<sid_for_node1>'
      <sid_for_node2>.instance_name='<sid_for_node2>'
    6. Sign in to SQL*Plus as an administrator user. For example:
      sqlplus sys / as sysdba
      Password: password
    7. Shut down the database
      shutdown immediate
    8. Restart the database in MOUNT mode by using the initdb_name.ora file that you just created.
      startup mount pfile='/tmp/init<sid>.ora';
    9. Create the spfile file.
      create spfile='+DATA' from pfile='/tmp/init<sid>.ora';
  4. Exit SQL*Plus.
  5. As the grid user, find the spfile file on Oracle Automatic Storage Management by using the asmcmd command.
    asmcmd
    ASMCMD> cd +DATA/target_db_unique_name/PARAMETERFILE/
    ASMCMD> ls -lt
    Output similar to the following appears:
    Type           Redund  Striped  Time             Sys  Name
    PARAMETERFILE  UNPROT  COARSE   APR 09 16:00:00  Y    spfile.262.973010033
    Make a note of the ASM name (spfile.262.973010033), which you will need in the next task.
Change the inittarget_db_name.ora File to Reference the spfile File

You can modify the init<sid>.ora file to reference the spfile file..

  1. Use SSH to connect to the standby database (the target database) server.
  2. Sign in as the database software owner oracle.
    su - oracle
  3. Execute the oraenv script to set the $ORACLE_HOME environment variable.
    . oraenv
  4. Go to the $ORACLE_HOME/dbs directory.
    cd $ORACLE_HOME/dbs
  5. Ensure that the init<sid>.ora file is in this directory.
    ls *.ora
  6. Change the inittarget_db_name.ora file to refer to the spfile file. For example:
    mv spfiletarget_db_name.ora spfiletarget_db_name.ora.stby
    mv init<sid>.ora init<sid>.ora.stby
    echo ''SPFILE='+DATA/target_db_unique_name/PARAMETERFILE/spfile.262.973010033'' > init<sid>.ora
    cat init<sid>inittarget_db_name.ora--To check the file
    In this output, spfile.262.973010033 is the name of the file that you generated when you moved the spfile file to Oracle Automatic Storage Management in the previous task.
    Output similar to the following appears:
    SPFILE='+DATA/target_db_unique_name/PARAMETERFILE/spfile.262.973010033'
Modify and Start the Standby Database in MOUNT Mode

You can use the srvctl to modify and start the standby database (the target database).

  1. Use SSH to sign in to the standby database (the target database) server.
  2. Sign in as the database software owner oracle.
    su - oracle
  3. Execute the oraenv script, which sets the $ORACLE_HOME environment variable.
    . oraenv
  4. Use srvctl to modify and start the standby database (the target database) in MOUNT mode. For example:
    srvctl modify database -db target_db_unique_name -role /
    PHYSICAL_STANDBY -s "READ ONLY"  -spfile /
    +DATA/target_db_unique_name/PARAMETERFILE/spfile.262.973010033
    
    srvctl config database -db target_db_unique_name
  5. Sign in to the database instance as a user who has the SYSOPER administrator privilege.
    sqlplus / as sysoper
  6. Shut down the database,
    SQL*Plus
    SHUTDOWN IMMEDIATE
  7. tart the database in mount
    STARTUP MOUNT
  8. alter the cluster_database parameter
    
    alter  system set cluster_database=True  sid='*' scope=spfile;
  9. Shut down the database
    SHUTDOWN IMMEDIATE
  10. Exit SQL*Plus.
    EXIT
  11. Start the database in MOUNT mode by using srvctl.
    srvctl start database -db target_db_unique_name -o mount
  12. Sign in to the database instance as a user who has the SYSDBA administrator privilege.
    sqlplus / as sysdba
  13. Query the V$DATABASE dynamic view to ensure that the database is in MOUNT mode.
    SELECT NAME, OPEN_MODE FROM V$DATABASE;
    #Output similar to the following appears:
    NAME              OPEN_MODE
    --------------    ---------
    source_db_name    MOUNTED
Set the Database and Log File Name Conversion Parameters on the Primary Database

You must set the conversion parameters for the database and the log file name on the primary database (the source database).

  1. Use SSH to sign in to the primary database (the source database) server.
  2. Switch to the oracle user:
    sudo su - oracle
  3. Sign in to the database instance as a user who has the SYSDBA administrator privilege. For example:
    sqlplus / as sysdba
    Enter password: password
  4. Check the CONVERT parameter.
    SHOW PARAMETER CONVERT
    Output similar to the following appears:
    NAME                         TYPE        VALUE
    ------------------------------------ ----------- ------
    db_file_name_convert                 string
    log_file_name_convert                string
    pdb_file_name_convert                string
    The VALUE column should be empty (null). If there is a value, then make a note of this value for after the migration is complete. After the migration is complete, these values are set to null.
  5. Use the information from this output to set the DB_FILE_NAME_CONVERT parameter.

    Note:

    Note that in this step, the SOURCE_DB_NAME should be in upper case.
    ALTER SYSTEM SET DB_FILE_NAME_CONVERT='+DATA','/u02/app/oracle/oradata/SOURCE_DB_NAME/'' SID='*' SCOPE=SPFILE;
  6. Set the LOG_FILE_NAME_CONVERT parameter. For example:
    ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='+RECO','/u04/app/oracle/redo/' SID='*' SCOPE=SPFILE;
  7. Restart the database.
    srvctl stop database -db source_db_name
    srvctl start database -db source_db_name
Set the Database and Log File Name Conversion Parameters on the Standby Database

You must set the conversion parameters for the database and the log file name on the standby database (the target database).

  1. Use SSH to sign in to the standby database (the target database) server.
  2. Switch to the oracle user:
    sudo su - oracle
  3. Sign in to the database instance as a user who has the SYSDBA administrator privilege. For example:
    sqlplus / as sysdba
    Enter password: password
  4. Check the CONVERT parameter.
    SHOW PARAMETER CONVERT
    Output similar to the following appears:
     NAME                        TYPE        VALUE
    ------------------------------------ ----------- ------
    db_file_name_convert                 string
    log_file_name_convert                string
    pdb_file_name_convert                string
    The VALUE column should be empty (null). If there is a value, then make a note of this value for after the migration is complete. After the migration is complete, these values are set to null.
  5. Use the information from this output to set the DB_FILE_NAME_CONVERT parameter. For example:

    Note:

    Note that in this step, the SOURCE_DB_NAME should be in upper case.
    ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u02/app/oracle/oradata/SOURCE_DB_NAME/','+DATA' SID='*' SCOPE=SPFILE;
  6. Set the LOG_FILE_NAME_CONVERT parameter. For example:
    ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u04/app/oracle/redo/','+RECO'
    SID='*' SCOPE=SPFILE;
  7. Restart the database.
    srvctl stop database -db target_db_unique_name
    srvctl start database -db target_db_unique_name -o mount
Configure the Database with Oracle Data Guard Broker

You can use the dbmgrl utility to configure either the primary database (the source database) or the standby database (the target database) with Oracle Data Guard Broker.

  1. Use SSH to sign in to the primary database (the source database) or the standby database (the target database) server.
  2. Start the dgmgrl command line utility:
    dgmgrl
  3. Connect as user SYS from either the primary or the standby database system. For example, to log in to a primary database whose TNS name is OCIC-ORCL:
    connect sys@ocic-orcl
    Enter password: password
  4. Using the dbmgrl utility, create the Oracle Data Guard configuration and identity for the primary and standby databases. For example:
    create configuration configuration_name as primary database is source_db_unique_name connect identifier is OCIC-ORCL; -- Uses the source TNS name
     
    add database target_db_unique_name as connect identifier is OCI-ORCL; --Uses the target TNS name
  5. Enable the configuration.
    enable configuration
  6. Show the Oracle Data Guard configuration on the standby database.
    show configuration
    Output similar to the following appears:
    Configuration - configuration_name
      Protection Mode: MaxPerformance
      Members:
      source_db_unique_name           - Primary database
        target_db_unique_name         - Physical standby database
    
    Fast-Start Failover: DISABLED
    
    Configuration Status:
    SUCCESS   (status updated 12 seconds ago)

Validate Oracle Data Guard Broker on the Primary Database and the Standby Database

You can use SQL*Plus to validate Oracle Data Guard Broker on the primary database (the source database) and the standby database (the target database).

Validate Oracle Data Guard Broker on the Primary Database

You can use SQL*Plus to validate Oracle Data Guard Broker on the primary database (the source database).

  1. Use SSH to sign in to the primary database (the source database) server.
  2. Connect as a user who has the SYSDBA administrator privilege. For example, for a primary database whose TNS name is OCIC-ORCL:
    connect sys@ocic-orcl as sysdba
    Enter password: password
  3. Query the V$DATABASE dynamic view.
    SELECT FORCE_LOGGING, FLASHBACK_ON, OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, DATAGUARD_BROKER, PROTECTION_MODE FROM V$DATABASE;
    
  4. Output similar to the following appears:
    
    FORCE_LOGGING                           FLASHBACK_ON       OPEN_MODE
    --------------------------------------- ------------------ -----------
    DATABASE_ROLE    SWITCHOVER_STATUS    DATAGUAR PROTECTION_MODE
    ---------------- -------------------- -------- --------------------
    YES                                     YES                READ WRITE
    PRIMARY          TO STANDBY           ENABLED  MAXIMUM PERFORMANCE
    In the output, the DATABASE_ROLE should be PRIMARY and OPEN_MODE should be READ WRITE.
Validate Oracle Data Guard Broker on the Standby Database

You can use SQL*Plus to validate Oracle Data Guard Broker on the standby database (the target database).

  1. Use SSH to sign in to the standby database (the target database) server.
  2. Connect as a user who has the SYSDBA administrator privilege.
  3. Query the V$DATABASE dynamic view.
    SELECT FORCE_LOGGING, FLASHBACK_ON,
    OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS,
    DATAGUARD_BROKER, PROTECTION_MODE
    FROM V$DATABASE;
    Output similar to the following appears:
    FORCE_LOGGING                           FLASHBACK_ON       OPEN_MODE
    --------------------------------------- ------------------ -----------
    DATABASE_ROLE    SWITCHOVER_STATUS    DATAGUAR PROTECTION_MODE
    ---------------- -------------------- -------- --------------------
    YES                                     YES                MOUNTED
    PHYSICAL STANDBY NOT ALLOWED          ENABLED  MAXIMUM PERFORMANCE
    The output should show DATABASE_ROLE as PHYSICAL STANDBYand OPEN_MODE as MOUNTED.
  4. Verify that the Oracle Data Guard processes are initiated in the standby database.
    SELECT PROCESS,PID,DELAY_MINS FROM V$MANAGED_STANDBY;
    Output similar to the following appears:
    PROCESS   PID                      DELAY_MINS
    --------- ------------------------ ----------
    ARCH      9207                              0
    ARCH      9212                              0
    ARCH      9216                              0
    ARCH      9220                              0
    RFS       1065                              0
    RFS       1148                              0
    RFS       1092                              0
    MRP0      972                               0
    RFS       1208                              0
    The output should indicate that the processes are running with little or no delay. If the DELAY_MINS for MRP0, the databases are synchronized.
  5. Check theLOG_ARCHIVE_DEST parameter.
    SHOW PARAMETER LOG_ARCHIVE_DEST_
    Output similar to the following appears:
    NAME                         TYPE      VALUE
    ---------------------------- --------- -----------------------------
    log_archive_dest_1           string       
                                           location=USE_DB_RECOVERY_FILE_
                                           DEST, valid_for=(ALL_LOGFILES,
                                           ALL_ROLES)
    log_archive_dest_10          string
    log_archive_dest_11          string
    log_archive_dest_12          string
    log_archive_dest_13          string
    log_archive_dest_14          string
    log_archive_dest_15          string
    ...
    log_archive_dest_2           string    service="oci-orcl", ASYNC
                                           NOAF FIRM delay=0 optional
                                           compression=disable
                                           max_failure=0 max_connections
                                           =1 reopen=300 db_unique_name=
                                           "source_db_unique_name"
                                           net_timeout=30, valid_for=
                                           (online_logfile,all_roles)
    ...
    
    The output should be similar to the output for log_archive_dest_2, with the service pointing to the standby database (the target database), which in this example is oci-orcl.
  6. Check the LOG_ARCHIVE_CONFIG parameter.
    SHOW PARAMETER LOG_ARCHIVE_CONFIG#
    Output similar to the following appears:
    
    NAME                         TYPE      VALUE
    ---------------------------- --------- -----------------------------------------------------------
    log_archive_config           string    dg_config=(source_db_unique_name,target_db_unique_name)
    
  7. Check the FAL_SERVER parameter.
    SHOW PARAMETER FAL_SERVER
    Output similar to the following appears:
    
    NAME                        TYPE      VALUE
    --------------------------- --------- ----------
    fal_server                  string    <tns_entry_of_primary>  
  8. Check the LOG_ARCHIVE_FORMAT parameter.
    SHOW PARAMETER LOG_ARCHIVE_FORMAT
    Output similar to the following appears:
    
    NAME                        TYPE      VALUE
    --------------------------- --------- --------------
    log_archive_format          string    %t_%s_%r.dbf
Complete the Validation on the Primary Database

You can use dgmrgl to complete the Oracle Data Guard Broker validation on the primary database (the source database).

  1. Use SSH to sign in to the primary database (the source database) server.
  2. Repeat steps 5 through 8 in the topic Validate Oracle Data Guard Broker on the Standby Database on the primary database (the source database).
  3. Start the dgmgrl command line utility:
    dgmgrl
  4. Connect as user SYS from either the primary or the standby database system. For example, to log in to a primary database whose TNS name is OCIC-ORCL:
    connect sys@primary_db_tnsnames_name
    Enter password: password
  5. Check the Oracle Data Guard configuration.
    show configuration verbose
    Output similar to the following appears:
    Configuration - configuration_name
     
      Protection Mode: MaxPerformance
      Members:
      source_db_unique_name           - Primary database
        target_db_unique_name         - 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'
     
    Fast-Start Failover: DISABLED
  6. Check the status on the standby database (the target database). For example:
    show database verbose target_db_unique_name
    After you complete these steps, you must test that the Oracle Data Guard configuration is functioning as expected by performing switchover operations in both directions.

Perform the Migration

To complete the migration, you must perform a switchover operation from the primary database (the source database) to the standby database (the target database).

  1. Use SSH to sign in to the primary database (the source database) server.
  2. Start the dgmgrl command line utility.
    dgmgrl 
  3. Connect as user SYS from either the primary or the standby database system. For example, to log in to a primary database whose TNS name is OCIC-ORCL:.
    connect sys@ocic-orcl
    Enter password: password
  4. Check the configuration.
    show configuration verbose
  5. In the configuration verbose output, check that the Database Status setting says SUCCESS.
  6. Check the configuration for the primary database (the source database).
    show database verbose source_db_unique_name
    The database verbose output should show that the role is primary and the setting for StaticConnectIdentifier is the same as DGConnectIdentifier.
  7. Perform a switchover operation to the standby database (the target database).
    switchover to target_db_unique_name
    The output should indicate that the switchover operation is occurring between the two databases.
  8. Run show configuration to verify that there are no errors or warnings:
    show configuration;

Post-Migration Steps

After you complete the migration of an Oracle database from an Oracle Cloud Infrastructure Compute Classic server to an Oracle Cloud Infrastructure server that uses a Virtual Machine Database system, you should validate the migration, and then remove the configuration from the primary database (the source database).

Test the Oracle Data Guard Configuration on the Standby Database

At this stage, the target database is now the primary database. The source database is now the standby database.

You can test the Oracle Data Guard connection on the target database, by performing a switchover operation with the source database. This switchover operation will make the target database take the standby role again. The purpose of this test is to prove that you can return to the original configuration in case the target database is not functional.

  1. Use SSH to sign in to the standby database (the target database) server.
  2. Start the dgmgrl utility. For example:
    dgmgrl sys@target_db
    Enter password: password
  3. Perform a switchover operation to the source database, which will make the target database take the standby role.
    switchover to source_db_unique_name;
    The output should indicate that the switchover operation is occurring between the two databases.
  4. (Optional) To prevent changes to the new standby database until the new primary database is determined to be fully functional, temporarily disable the Redo Apply feature.
    edit database source_db_unique_name set state = 'APPLY-OFF';
    If you perform another switchover operation so that the target database is now the standby database, you can perform an APPLY-OFF operation to prevent the source database from being updated. This enables the target database to be put in service, and keeps the source database as a point-in-time backup in case of a logical failure in the new configuration.
  5. (Optional) To restart the apply feature:
    edit database source_db_unique_name set state = 'APPLY-ON';
  6. Exit dgmgrl.
    exit
  7. Perform a switchover operation to the target database, which will make the source database the standby role.
    switchover to target_db_unique_name;
    The output should indicate that the switchover operation is occurring between the two databases.
  8. Test the connection to the new primary database. For example, after exporting the target unique name, connect as user SYS and select from an encrypted table space. In this example, the HR.EMPLOYEES table is encrypted.
    exit
  9. Test the connection to the new primary database. For example, after exporting the target unique name, connect as user SYS and select from an encrypted table space. In this example, the HR.EMPLOYEES table is encrypted.
    export ORACLE_UNQNAME=target_db_unique_name
     
    sqlplus sys@target_TNS_name
    Password: password
     
    SQL> ALTER SESSION SET CONTAINER = PDB1;
    SQL> SELECT * FROM HR.EMPLOYEES;
     
    SQL> EXIT
Clean Up the Standby Database

After you complete and test the migration, you can remove the Oracle Data Guard configuration from the standby database (the target database). You do not need to remove the original source database. At this stage, the standby database is the new source database.

  1. Use SSH to sign in to the standby database (the target database) server and sign in to the Oracle Data Guard dgmgrl utility.
  2. Check the configuration.
    show configuration
  3. If the configuration does not show Protection Mode: MaxPerformance, then set Oracle Data Guard to use the MaxPerformance protection mode.
    edit configuration set protection mode as maxperformance
  4. Disable and then remove the configuration.
    edit database source_db_unique_name set state = 'APPLY-OFF';
     
    disable configuration;
     
    remove configuration;
     
    exit
  5. Connect to the database instance as a user who has the SYSDBA administrator privilege.For example:
    sqlplus / as sysdba
  6. Check the DG_BROKER_CONFIG_FILE parameters.
    SHOW PARAMETER DB_BROKER_CONFIG_FILE
    The output should list the associated data and recovery files for this configuration, typically named dg_broker_config_file1 and dg_broker_config_file2.
  7. Start another terminal window, and sign in to asmcmd as the grid user.
  8. Remove the Oracle Data Guard configuration files that were listed when you checked the DG_BROKER_CONFIG_FILE parameters.
  9. Return to the window that is running SQL*Plus.
  10. Execute the following ALTER SYSTEM statements:
    ALTER SYSTEM SET DG_BROKER_START=FALSE SID='*' SCOPE=BOTH;
    ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='' SID='*' SCOPE=SPFILE;
    ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='' SID='*' SCOPE=SPFILE;
    ALTER SYSTEM RESET LOG_ARCHIVE_CONFIG SID='*' SCOPE=SPFILE;
  11. Check the following parameters:
    SHOW PARAMETER DB_FILE_NAME_CONVERT
    SHOW PARAMETER LOG_FILE_NAME_CONVERT
    SHOW PARAMETER LOG_ARCHIVE_DEST
    SHOW PARAMETER LOG_ARCHIVE_DEST_STATE
    SHOW PARAMETER STANDBY_ARCHIVE_DEST
    SHOW PARAMETER FAL
  12. If any of the preceding parameters is set, then reset the parameters to use blank values. For example, for STANDBY_ARCHIVE_DEST:
    ALTER SYSTEM SET STANDBY_ARCHIVE_DEST='' SID='*' SCOPE=SPFILE;
  13. Restart the database.
    SHUTDOWN IMMEDIATE
    STARTUP
  14. Drop the standby logs from the primary database (the source database).
    1. Find the group numbers for the standby database redo logs that are on the new primary database (which was formerly the target database).
      SELECT GROUP# FROM V$STANDBY_LOG;Output similar to the following appears:    GROUP#
      ----------
               5
               6
               7
               8
               9
    2. Remove the standby logs. For example:
      ALTER DATABASE DROP STANDBY LOGFILE GROUP 5;
      ALTER DATABASE DROP STANDBY LOGFILE GROUP 6;
      ALTER DATABASE DROP STANDBY LOGFILE GROUP 7;
      ALTER DATABASE DROP STANDBY LOGFILE GROUP 8;
      ALTER DATABASE DROP STANDBY LOGFILE GROUP 9;
  15. (Optional) Change the DB_BLOCK_CHECKSUM and DB_BLOCK_CHECKING parameters.
    The default values are DB_BLOCK_CHECKSUM=TYPICAL and DB_BLOCK_CHECKING=FALSE.
  16. Exit SQL*Plus.
    EXIT