13 Learn About Migrating a Database Cloud Service Deployment to a Virtual Machine Database System

If you want to migrate a single-instance database, created with Oracle Database Cloud Service on an Oracle Cloud Infrastructure Compute Classic server, to an Oracle Cloud Infrastructure Virtual Machine Database System, then you can perform the database migration by using Oracle Data Guard.

This procedure can be used as part of an overall migration of your Oracle Cloud environment to Oracle Cloud Infrastructure.

Architecture

You can migrate Oracle Database releases 12.1.0.2 and 12.2.0.1. Before you migrate your database, you must have an Oracle Database Cloud Service instance that you want to migrate, and an Oracle Cloud Infrastructure Server with Oracle Database installed.

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:

Description of architecture.png follows
Description of the illustration architecture.png

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.

    Note:

    Oracle recommends using the same database name for both databases so that applications can automatically fall 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.

The roles that are described in this topic will be in the target database only if they were in the source database. Oracle Database creates these roles (as well as other roles) during the installation process to better enforce separation of duty.

The following table lists the roles that you will need to complete this Oracle Cloud Infrastructure solution.

Service Name: 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 the Database Migration

To plan the single instance of DBCS migration, you must inventory the source environment and decide on the best migration strategy.

Inventory the Source Environment

Inventorying the environment ensures that you have the supported Oracle Database versions and configurations that are required for migration.

  • Ensure that you have the supported versions and configurations. Here is the list of combinations that are supported by Oracle Data Guard for migration.

    Database versions and configurations Source (Primary) Target (Standby)
    Database Service Oracle Database Cloud Service (Standalone) Oracle Cloud Infrastructure Virtual Machine 1 Node Database System
    Database Version
    • 12.1.0.2
    • 12.2.0.1
    • 12.1.0.2
    • 12.2.0.1
    Database Storage Filesystem for :
    • 12.1.0.2
    • 12.2.0.1

    ASM for version 12.2.0.1 and version 12.1.0.2 databases

  • Determine the size of the source database .

    You can view the source database size from the Oracle Database Cloud Service service console. Identify the size of the OCPUs, Memory and Storage for the Oracle Database Cloud Service instance. This information will enable you to identify the appropriate Oracle Cloud Infrastructure Virtual Machine Database shape which maps to the source database size. Virtual Machine Database system is available in fixed 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 your target database.

  • 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.

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 primary database before starting the migration
  • The best time of day to perform the migration
  • Downtime requirements
  • Database size
  • Security considerations
  • A strategy for large workloads

Prepare For Migration

To prepare for the migration of a single instance Oracle Database Cloud Service to an Oracle Cloud Infrastructure server, you must perform multiple preparatory tasks before migration can start.

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 
    su - oracle
  3. 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.

  4. Check the listener status.
    lsnrctl status
  5. If the listener is not running (for example, the output has error TNS-12541: TNS:no listener), then start the listener.
    lsnrctl start
  6. Check that the database is running.
    sqlplus / as sysdba
    This command should connect you to the database instance and the SQL> prompt should appear.
  7. 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
  8. 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 and edition 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 target database (the standby database).
    The target database should have the same database version, and same or later bundle patch/PSU version installed than primary database.

Create a Standby Database for the Oracle Cloud Infrastructure System

You must create a standby database (the target database) on the Oracle Cloud Infrastructure Virtual Machine Database system, in addition to the database that is currently on this system. An Oracle database on Virtual Machine Database system can accommodate multiple databases on a single host. The 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. The steps below provide the detailed process for creating the Oracle Cloud Infrastructure Virtual Machine Database system.

If you want to learn more about the Virtual Machine Database systems, you can refer to Oracle Cloud Infrastructure documentation.

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
  1. Login to your Oracle Cloud Services Dashboard
  2. Open the navigation menu. Under Services, click Database (NOT Database Classic).
  3. Choose your Compartment.
  4. Click Launch DB System.
  5. In the Launch DB System dialog, 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 VM DB 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 can specify 1 or 2 nodes for virtual machine DB systems, except for VM.Standard2.1 and VM.Standard1.1, which are single-node DB systems.
      • Oracle Database Software Edition: The database edition supported by the DB system. You can mix supported database versions on the DB system, but not editions. (The database edition cannot be changed and applies to all the databases in this 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 Compartment: The compartment containing the network in which to launch the DB system.
      • 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.
      • 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: The name of the pluggable database. The PDB name must begin with an alphabetic character, and can contain a maximum of 8 alphanumeric characters. The only special character permitted is the underscore ( _).
      • Database Admin Password: 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.

      • 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.
  6. 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).
  7. 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.
  8. Note the IP addresses; you'll need the private or public IP address, depending on network configuration, to connect to the DB system.
  9. 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 1521 Is Open Between the Primary Database and the Standby Database

You must ensure that the primary database (the source database) and the standby database (the target database) can connect to each other through port 1521.

Enable Communication from Standby Database to Primary Database

On primary database (the source database), open port 22 and 1521 for ingress traffic from standby database (the target database).

  1. On Oracle Database Service Console, enable ora_p2_dblistener rule to open ingress traffic on port 1521 from public internet.
    1. Login to your Oracle Cloud Services Dashboard
    2. Open the navigation menu. Under Services, click Database Classic.
    3. Click action menu next to your DBCS deployment, select Access Rules from the menu displayed.
    4. Enable rule ora_p2_dblistener to open ingress traffic on port 1521
      Enable Rule
  2. From the standby database SSH to primary database (the source database) on port 22. Port 22 on primary database is open by default from public internet.
    [opc@<standby_db_name> ~]$ ssh -i <private key> opc@<Primary DB IP>
    [opc@<primary_db_name> ~]$

    Exit from the primary database base.

    [opc@<primary_db_name> ~]$ exit
    [opc@<standby_db_name> ~]$ 
  3. On the standby database change the user to root:
    sudo su 
  4. Set the TCP Socket size.
    sysctl -w net.core.rmem_max=10485760
    sysctl -w net.core.wmem_max=10485760
  5. On the standby database, switch back to user oracle. Add the TNS entry for primary database to $ORACLE_HOME/network/admin/tnsnames.ora.
    <Primary DB Name>=
      (DESCRIPTION =    
    (ADDRESS = (PROTOCOL = TCP)(HOST = <Primary DB Public IP>)(PORT = 1521))    
    (CONNECT_DATA =      
    (SERVER = DEDICATED)      
    (SERVICE_NAME = <Primary DB Service Name>)   
     )  
    )
  6. Test sqlplus connection from standby to primary database.
    sqlplus sys/<password>@<Primary DB Name> as sysdba 
    
    SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 20 02:18:35 2018
    Copyright (c) 1982, 2014, Oracle.  All rights reserved.  
    
    Connected to:
    Oracle Database 12c EE Extreme Perf Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
    and Real Application Testing options
    SQL>
Enable Communication from Primary Database to Standby Database System

On the standby database, open port 22 and 1521 for ingress traffic from primary database.

  1. Note down the public IP of primary database instance from the console.
  2. Login to Oracle Cloud Infrastructure Service console.
  3. Open the Oracle Cloud Infrastructure Navigation menu. Under Core Infrastructure, go to Networking and click Virtual Cloud Networks.
  4. Click the cloud network that you have used to create yourOracle Cloud Infrastructure Virtual Machine Database System.
  5. Click Security Lists.
  6. Click the security list you're interested in. It is generally the Default Security List.
  7. Click Edit All Rules.
  8. Click + Another Ingress Rule. Add primary databse IP to Source CIDR . Update the Destination Port Range to 22,1521.
    Add rule
  9. Click Save Security List Rules
  10. From the primary database, test SSH on port 22 to standby database System.
    ssh -i <private key> opc@<standby DB IP>
    [opc@<standby DB IP> ~]$

    Exit from the primary database base.

    [opc@<primary_db_name> ~]$ exit
    [opc@<standby_db_name> ~]$ 
  11. On the standby database change the user to root:
    sudo su
  12. Set TCP Socket size
    sysctl -w net.core.rmem_max=10485760
    sysctl -w net.core.wmem_max=10485760
  13. Add TNS entry for standby database to $ORACLE_HOME/network/admin/tnsnames.ora. Replace standby database server name with the public IP in TNS entry.
    <Standby DB Name>=  
    (DESCRIPTION =    
    (ADDRESS = (PROTOCOL = TCP)(HOST = <Standby DB Public IP>)(PORT = 1521))    
    (CONNECT_DATA =      
    (SERVER = DEDICATED)      
    (SERVICE_NAME = <Standby DB Service Name>)    
    )  
    )
  14. Use the utility tnsping to check the primary-standby DB connection.
    [oracle@<primary_db_name>]$ tnsping <Standby tns entry>
    TNS Ping Utility forLinux: Version 12.1.0.2.0- Production on 20-FEB-201802:23:01
    Copyright (c) 1997, 2014, Oracle.  All rights reserved.
    Used parameter files:
    /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora 
    
    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <Standby IP>)(PORT 
    = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = <Primary DB name>)))
    OK (20msec)
  15. Test sqlplus connection from primary to standby database on port 1521.
    sqlplus sys/<password>@<Standby DB Name> as sysdba
    SQL*Plus: Release 12.1.0.2.0Production on Tue Feb 2002:23:112018
    Copyright (c) 1982, 2014, Oracle.  All rights reserved. 
    
    Connected to:
    Oracle Database 12c EE Extreme Perf Release 12.1.0.2.0- 64bit Production
    With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
    OLAP, Advanced Analytics and Real Application Testing options
    SQL>

Ensure That Bundle Patches Have Been Applied and Are in Sync

You must ensure that the patch level on the primary database is earlier or the same as the patch level on the standby database.

The patches on the Oracle Database Cloud Service instance (on Oracle Cloud Infrastructure Compute Classic) must be the same as the Oracle Cloud Infrastructure Virtual Machine Database System, and must be manually applied. Look out for one-off patches that have been applied to the primary database, and if there is a need to apply them to the standby database as well.

  1. Use SSH to sign in to the primary database server.
    sudo su
    su - oracle
  2. List 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. Use SSH to sign in to the server where the standby database is located.
  5. Check the patch level on the standby database by running the opatch lsinventory command.
  6. Compare the patch level on the primary database with the patch level on the standby database. 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. If you find that you must install a patch on the Oracle Database Cloud Service primary database (the source database) instance, then ensure that the patch is not later than the patch on the Oracle Cloud Infrastructure standby database (the target database) instance.
  7. Ensure that the bundle patches that have been applied include the fix for Bug 18633374 (My Oracle Support Note - Doc ID 1918906.1). This patch must be applied to both primary and standby database.
    1. Download the patch from My Oracle Support Note - Doc ID 1918906.1 on your local system
    2. Transfer the patch to the database server on which the patch needs to be applied.
      ls -rlt p18633374_12102171017_Linux-x86-64.zip
      unzip p18633374_12102171017_Linux-x86-64.zip
    3. Check for conflicts
      cd 18633374/
      $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph
          ./

      Given below is the sample output of the above command, which shows that patches 28210208 and 27351628

      Conflict with 28210208
      Conflict details:
       /u01/app/oracle/product/12.1.0/dbhome_1/lib/libserver12.a:krb.o
       /u01/app/oracle/product/12.1.0/dbhome_1/lib/libserver12.a:krbi.o
       
      Conflict with 27351628
      Conflict details:
        /u01/app/oracle/product/12.1.0/dbhome_1/lib/libserver12.a:krbb.o
    4. Shutdown the database
      sqlplus / as sysdba
      SQL> shut immediate
       Database closed.
       Database dismounted.
       ORACLE instance shut down.
       SQL> exit
       Disconnected from Oracle Database 12c EE Extreme Perf Release 12.1.0.2.0 - 64bit Production
       With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
       and Real Application Testing options
    5. Stop the Listener
      $ lsnrctl stop
      LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 19-FEB-2018 13:02:25
      Copyright (c) 1991, 2014, Oracle. All rights reserved.
      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<Database host name>)(PORT=1521)))
       The command completed successfully
    6. Rollback the conflicting patches

      Execute this command for all the conflicting patches:

      $ORACLE_HOME/OPatch/opatch rollback -id <patch_id>
      Sample Output
      Oracle Interim Patch Installer version 12.2.0.1.9
       Copyright (c) 2018, Oracle Corporation. All rights reserved.
       
       Oracle Home : /u01/app/oracle/product/12.1.0/dbhome_1
       Central Inventory : /u01/app/oraInventory
       from : /u01/app/oracle/product/12.1.0/dbhome_1/oraInst.loc
       OPatch version : 12.2.0.1.9
       OUI version : 12.1.0.2.0
       Log file location : /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/opatch2018-02-19_13-02-42PM_1.log
       
       Patches will be rolled back in the following order:
       24401351
       The following patch(es) will be rolled back: 24401351
      Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
       (Oracle Home = '/u01/app/oracle/product/12.1.0/dbhome_1')
       
       Is the local system ready for patching? [y|n]
       y
       User Responded with: Y
      Rolling back patch 24401351...
      RollbackSession rolling back interim patch '24401351' from OH '/u01/app/oracle/product/12.1.0/dbhome_1'
      Patching component oracle.rdbms, 12.1.0.2.0...
       RollbackSession removing interim patch '24401351' from inventory
       Log file location: /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/opatch2018-02-19_13-02-42PM_1.log
      OPatch succeeded.
    7. Apply patch
      pwd
      /home/oracle/18633374
      [oracle@<database host> 18633374]$ $ORACLE_HOME/OPatch/opatch apply
    8. Restart the database
      sqlplus / as sysdba
      SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 19 13:04:48 2018
      Copyright (c) 1982, 2014, Oracle. All rights reserved.
      startupConnected to an idle instance.
      SQL> startup
    9. Start the listener
      $ lsnrctl start

      Note:

      Ensure that once the migration is complete, you have to rollback patch 18633374, and re-apply the conflicting patches.
  8. If you must install a later patch on the standby database (the target database), then access My Oracle Support.
  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

Compare Timezone Levels

The timezone of both the primary and standby database systems should be same. In case there is a difference, corresponding timezone patch to be applied. You can check for the timezone on both the systems, using the following command:

sudo su
su - oracle
sqlplus / as sysdba
 
SQL> select * from v$timezone_file;
FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_28.dat              28          0

The output of the above command must be same on both primary and standby databases.

Migrate the Database

To perform the migration of a single instance Oracle Database Cloud Service server to an Oracle Cloud Infrastructure Virtual Machine database system, you can use Oracle Data Guard. You must configure the database on Oracle Cloud Infrastructure Compute 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 Database 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 (source) database server.
    sudo su
    su - oracle
  2. Sign in to the database instance as a user who has administrator privileges. For example:
    sqlplus / as sysoper
  3. Ensure that the database is in ARCHIVELOG mode
    SQL> archive log list
    Database log mode Archive Mode
    Automatic archival Enabled
    Archive destination USE_DB_RECOVERY_FILE_DEST
    Oldest online log sequence 9
    Next log sequence to archive 11
    Current log sequence 11
    SQL>
    In the output above, notice that the value of Database log mode is set to Archive Mode, and the value of Automatic archival is Enabled.
  4. If the output for Database log mode is No Archive Mode and the output for Automatic archival is Disabled, then do the following:
    1. Shut down the database.
      SHUTDOWN IMMEDIATE
    2. Restart the database in mount mode
      STARTUP MOUNT
    3. Enable archive log mode.
      ALTER DATABASE ARCHIVELOG;
    4. Ensure that the database is now in archive log mode.
      ARCHIVE LOG LIST
    5. The output for the Database log mode should be Archive Mode and the output for Automatic archival is Enabled.
    6. Open the database.
      ALTER DATABASE OPEN;
  5. Connect with the SYSDBA administrator privilege.
    CONNECT / AS SYSDBA
  6. 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;
  7. Ensure that the database is in force logging mode. For example:
    SELECT NAME, OPEN_MODE, FORCE_LOGGING FROM V$DATABASE;
  8. If necessary, enable force logging.
    ALTER DATABASE FORCE LOGGING;
  9. Check the configuration.
    SELECT NAME, CDB, OPEN_MODE, FORCE_LOGGING FROM V$DATABASE;
    The FORCE_LOGGING column should be YES.
  10. Use the SHOW PARAMETER command to check the following database parameters:
    1. DB_NAME : It is recommended to use the same name as the target database.
    2. DB_UNIQUE_NAME: Ensure that this name is different from the name used on the target database.
    3. REMOTE_LOGIN_PASSWORD_FILE: This parameter must be set to EXCLUSIVE.
  11. Ensure that the Flashback in ON. If its not ON, use sql command ALTER DATABASE FLASHBACK ON;
    select flashback_on from v$database;
    FLASHBACK_ON
    ------------------
    YES
    show parameter flashback_retention_target
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    db_flashback_retention_target        integer     1440
    SQL>
Add Static Services to the Primary Database listener.ora File

You must add a new static listener to the primary (source) database listener.ora file and restart the listener.

  1. Use SSH to sign in to the primary (source) database server.
  2. At the command line, connect as root.
    sudo su -
  3. Sign in as the database software owner oracle.
    su - oracle
  4. 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 = <primary_db_unique_name>.<primary_db_domain>)
        (SID_NAME = <source_db_name>)
        (ORACLE_HOME=<oracle_home_directory>)
        (ENVS="TNS_ADMIN=<oracle_home_directory>/network/admin")
        )
        (SID_DESC=
        (SDU=65535)
        (GLOBAL_DBNAME = <primary_db_unique_name>_DGMGRL.<primary_db_domain>)
        (SID_NAME = <source_db_name>)
        (ORACLE_HOME=<oracle_home_directory>)
        (ENVS="TNS_ADMIN=<oracle_home_directory>/network/admin")
        )
      )
  5. Stop the listener.
    lsnrctl stop listener
  6. Restart the listener.
    lsnrctl start listener
  7. Check the listener status.
    lsnrctl status
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.

Note:

Ensure that the source database is in ARCHIVELOG MODE with FLASHBACK enabled by default. It is recommended to have DB_BLOCK_CHECKSUM=FULL. If they are any performance issues then switch to DB_BLOCK_CHECKING=MEDIUM
  1. Use SSH to sign in to the primary database (the source database) server.
  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=SPFILE;
  4. Set the archive lag target.
    ALTER SYSTEM SET ARCHIVE_LAG_TARGET=1800 SID='*' SCOPE=SPFILE;
  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/<source_db_name>/dr1<source_db_name>.dat' SCOPE=BOTH;
    ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='/u03/app/oracle/fast_recovery_area/<source_db_name>/dr2<source_db_name>.dat' SCOPE=BOTH;
  6. Enable the Oracle Broker DMON process for the database.
    ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;
  7. Set the DB_BLOCK_CHECKING and DB_BLOCK_CHECKSUM parameters.
    ALTER SYSTEM SET DB_BLOCK_CHECKING=FULL SCOPE=BOTH;
    ALTER SYSTEM SET DB_BLOCK_CHECKSUM=FULL SCOPE=BOTH;
  8. Set the log buffer to 256 megabytes.
    ALTER SYSTEM SET LOG_BUFFER=268435456 SCOPE=SPFILE;
  9. Set the DB_LOST_WRITE_PROTECT parameter to TYPICAL.
    ALTER SYSTEM SET DB_LOST_WRITE_PROTECT=TYPICAL 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, 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
    2. 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 4('/u04/app/oracle/redo/stby_redo01.log') size 1073741824,
      group 5('/u04/app/oracle/redo/stby_redo02.log') size 1073741824,
      group 6('/u04/app/oracle/redo/stby_redo03.log') size 1073741824,
      group 7('/u04/app/oracle/redo/stby_redo04.log') size 1073741824;
    3. 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
      ---------- ----------
               4   1073741824
               5   1073741824
               6   1073741824
               7   1073741824

Configure the Standby (Target) Database

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

Drop Standby database

This step cleans up the initial database for creating a physical standby on Virtual Machine Database system.

Note:

Capture db_unique_name on standby database. It is mandatory to use same db_unique_name for standby database creation. The db_unique_name is case sensitive.
  1. Use SSH to sign in to the standby database (the target database) server.
  2. At the command line, connect as root.
    sudo su -
  3. Sign in as the database software owner oracle.
    sudo su - oracle
  4. Stop the database
    srvctl stop database -d <standby_db_unique_name>
  5. Start the database in mount mode
    
    srvctl start database -d <standby_db_unique_name> -o mount
  6. Login to the database as user sysdba
    
    sqlplus / as sysdba
    SQL*Plus: Release 12.1.0.2.0 Production on Sat Feb 17 18:21:20 2018
    Copyright (c) 1982, 2014, Oracle.  All rights reserved.
    Connected to:
    Oracle Database 12c EE Extreme Perf Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
    Advanced Analytics and Real Application Testing options
    
  7. Drop the database
    alter system enable restricted session;
    System altered.
    drop database;
Add Static Services to the Standby Database listener.ora File

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

  1. Use SSH to sign in to the standby (target) database server.
  2. At the command line, connect as root.
    sudo su 
  3. Sign in as the database software owner oracle.
    su - oracle
  4. Execute the oraenv script, which sets the $ORACLE_HOME environment variable.
    . oraenv
    Output similar to the following should appear:
    ORACLE_SID = [oracle] ? db_name
    The Oracle base has been set to /u01/app/oracle
  5. SSH to the standby database system, log in as the opc or root user, and sudo to the grid user.
    sudo su - grid
  6. Modify the /u01/app/12.2.0.1/grid/network/admin/listener.ora file to include the static listener. The first static listener shown below is required for Oracle Recovery Manager (Oracle RMAN) duplicate.
    SID_LIST_LISTENER=
      (SID_LIST=
        (SID_DESC=
        (SDU=65535)
        (GLOBAL_DBNAME = <standby db_unique_name>.<standby db_domain>)
        (SID_NAME = <standby oracle_sid>)
        (ORACLE_HOME=<oracle home directory>)
        (ENVS="TNS_ADMIN=<oracle home directory>/network/admin")
        )
       
      )
  7. Use the srvctl utility to stop the listener.
    srvctl stop listener -l LISTENER
  8. Restart the listener.
    srvctl start listener -l LISTENER
  9. Check the listener status.
    lsnrctl status
    Sample Output
    LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 20-FEB-2018 02:45:31
    Copyright (c) 1991, 2016, Oracle.  All rights reserved.
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
    STATUS of the LISTENER
    ------------------------
    Alias                     LISTENER
    Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
    Start Date                19-FEB-2018 12:14:06
    Uptime                    0 days 14 hr. 31 min. 24 sec
    Trace Level               off
    Security                  ON: Local OS Authentication
    SNMP                      OFF
    Listener Parameter File   /u01/app/12.2.0.1/grid/network/admin/listener.ora
    Listener Log File         /u01/app/grid/diag/tnslsnr/migtest/listener/alert/log.xml
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.1.2)(PORT=1521)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=<host name>)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.1.0.2/dbhome_1/admin/<Standby_db_name>/xdb_wallet))(Presentation=HTTP)(Session=RAW))
    Services Summary...
    Service "+APX" has 1 instance(s).
      Instance "+APX1", status READY, has 1 handler(s) for this service...
    Service "+ASM" has 1 instance(s).
      Instance "+ASM1", status READY, has 1 handler(s) for this service...
    Service "+ASM_DATA" has 1 instance(s).
      Instance "+ASM1", status READY, has 1 handler(s) for this service...
    Service "+ASM_RECO" has 1 instance(s).
      Instance "+ASM1", status READY, has 1 handler(s) for this service...
    Service "<StandbyDB>" has 1 instance(s).
      Instance "<standby_db_name>.<standby_db_domain>", status READY, has 1 handler(s) for this service...
    Service "<standby_db_name>" has 1 instance(s).
      Instance "<standby_db_name>.<standby_db_domain>", status READY, has 1 handler(s) for this service...
    Service "<standby_db_name>.<standby_db_domain>" has 2 instance(s).
      Instance "MIGTEST", status UNKNOWN, has 1 handler(s) for this service...
      Instance "<StandbyDB>", status READY, has 1 handler(s) for this service...
    Service "<StandbyDB>" has 1 instance(s).
      Instance "<StandbyDB>", status READY, has 1 handler(s) for this service...
    Service "<StandbyDB>" has 1 instance(s).
        Instance "<StandbyDB>", status READY, has 1 handler(s) for this service...
    The command completed successfully

    Note:

    In the above output, you may see the new listener with status UNKNOWN. This is an expected output.
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 root.
    sudo su -
  3. Sign in as the database software owner oracle.
    su - oracle
  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 /u01/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 temp directory. For example:
    cp tde_wallet.tar /tmp/
  2. Exit twice to become the OCP user.
    $ exit
    # exit
  3. 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
  4. Use SSH to sign in to the target database server and then sign in as database software owner oracle.
  5. Go to the target wallet directory.For example:
    cd /opt/oracle/dcs/commonstore/wallets/tde
  6. Check that the correct wallet is in this directory.
    ls <Standby DB Unique Name>
  7. Back up the wallet file.For example:
    mv standby_db_unique_name standby_db_unique_name.old
  8. Create a directory in which to store the wallet.For example:
    mkdir standby_db_unique_name
  9. Check the permissions on the wallet directory.
    ls -ld standby_db_unique_name
  10. If necessary, give the database software owner oracle read, write, and execute permissions.
    chmod 700 standby_db_unique_name
  11. Check the permissions again.
    ls -ld standby_db_unique_name
  12. Copy the wallet tar file to the current directory.
    cp /tmp/tde_wallet.tar .
  13. Check the permissions.
    ls -rlt
    Output similar to the following appears:
    total 124
    drwx------ 2 oracle oinstall 20480 Feb 16 09:25 standby_db_unique_name.old
    drwx------ 2 oracle oinstall 20480 Feb 16 10:16 standby_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.
    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<Standby>.ora. For example:
    echo "*.db_name='standby_db_name'" > $ORACLE_HOME/dbs/initstandby_db_name.ora
    echo "*.db_unique_name='standby_db_unique_name'" >> $ORACLE_HOME/dbs/initStandby_db_name.ora
    echo "*.db_domain='standby_db_domain'" >> $ORACLE_HOME/dbs/initStandby_db_name.ora
  6. Back up the existing password file, if one exists. For example:
    mv $ORACLE_HOME/dbs/orapwtarget $ORACLE_HOME/dbs/orapwtarget.old
  7. Create a new password file. For example:
    orapwd file=$ORACLE_HOME/dbs/orapwtarget password=admin_password_for_primary entries=5
  8. Connect to the standby database (the target database) instance as a user who has the SYSOPER administrator privilege. For example:
    sqlplus / as sysoper
  9. Shut down the database. For example:
    shutdown immediate [ If the Database is already stopped, this may throw error]
  10. Restart the database in NOMOUNT mode using the init_target.ora initialization parameter file.
    startup force nomount PFILE=?/dbs/initStandby_db_name.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. Create a script file dup.rcv, where you will copy the commands and fill in the environment specific variables specified in the later step.
    vi dup.rcv
    run {
    allocate channel prmy1 type disk;
    allocate channel prmy2 type disk;
    allocate channel prmy3 type disk;
    allocate channel prmy4 type disk;
    allocate auxiliary channel stby1 type disk;
    allocate auxiliary channel stby2 type disk;
    allocate auxiliary channel stby type disk;
    duplicate target database for standby from active database dorecover
    spfile
    parameter_value_convert '/u02/app/oracle/oradata/<source_db_name>','+DATA'
    set db_unique_name='<target_db_unique_name>'
    set db_create_file_dest='+DATA'
    set db_create_online_log_dest_1='+RECO'
    set db_recovery_file_dest='+RECO'
    set audit_file_dest='/u01/app/oracle/admin/db_name/adump'
    set control_files='+DATA','+RECO'
    set dg_broker_config_file1='+DATA/<target_db_unique_name>/dr1<target_db_unique_name>.dat'
    set dg_broker_config_file2='+RECO/<target_db_unique_name>/dr2<target_db_unique_name>.dat'
    set dispatchers='(PROTOCOL=TCP) (SERVICE=<target_db_name>XDB)'
    set instance_name='<target_db_name>'
    set db_domain='<target_db_domain>'
    set db_recovery_file_dest='+RECO'
    ;
    }
  3. 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
  4. 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. 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.

    Modify the dup.rcv file with environment specific parameters.

    @dup.rcv
    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 db_unique_name='<target_db_unique_name>'
    13> set db_create_file_dest='+DATA'
    14> set db_create_online_log_dest_1='+RECO'
    15> set db_recovery_file_dest='+RECO'
    16> set audit_file_dest='/u01/app/oracle/admin/db_name/adump'
    17> set control_files='+DATA','+RECO'
    18> set dg_broker_config_file1='+DATA/<target_db_unique_name>/dr1<target_db_unique_name>.dat'
    19> set dg_broker_config_file2='+RECO/<target_db_unique_name>/dr2<target_db_unique_name>.dat'
    20> set dispatchers='(PROTOCOL=TCP) (SERVICE=<target_db_name>XDB)'
    21> set instance_name='<target_db_name>'
    22> set db_domain='<target_db_domain>'
    23> set db_recovery_file_dest='+RECO'
    24> ;
    25> }
Post Oracle Recovery Manager Duplication Steps

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

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<target_db_name>.ora' from spfile;
    2. Shut down the database
      shutdown immediate
    3. Restart the database in MOUNT mode by using the initdb_name.ora file that you just created.
      startup mount pfile='/tmp/init<target_db_name>.ora';
    4. Create the spfile file.
      create spfile='+DATA' from pfile='/tmp/init<target_db_name>.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 init<target_db_name>.ora File to Reference the spfile File

You can modify the init<target_db_name>.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<target_db_name>.ora file is in this directory.
    ls *.ora
  6. Change the init<target_db_name>.ora file to refer to the spfile file.For example:
    mv spfile<target_db_name>.ora spfile<target_db_name>.ora.stby
    mv init<target_db_name>.ora init<target_db_name>.ora.stby
    echo "SPFILE='+DATA/target_db_unique_name/PARAMETERFILE/spfile.262.973010033'" > init<target_db_name>.ora
    cat init<target_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. 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>
  4. Sign in to the database instance as a user who has the SYSOPER administrator privilege.
    sqlplus / as sysoper
  5. Shut down the database, and then exit SQL*Plus.
    SHUTDOWN IMMEDIATE
    EXIT
  6. Start the database in MOUNT mode by using srvctl.
    srvctl start database -db <target_db_unique_name> -o mount
  7. Sign in to the database instance as a user who has the SYSDBA administrator privilege.
    sqlplus / as sysdba
  8. 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. Sign in as the database software owner oracle.
    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. Check the names of the data files.
    SELECT NAME FROM V$DATAFILE;
    Output similar to the following appears:
    NAME
    -----------------------------------------------------------------------
    /u02/app/oracle/oradata/SOURCE_DB_NAME/system01.dbf
    /u02/app/oracle/oradata/SOURCE_DB_NAME/sysaux01.dbf
    /u02/app/oracle/oradata/SOURCE_DB_NAME/undotbs01.dbf
    /u02/app/oracle/oradata/SOURCE_DB_NAME/pdbseed/system01.dbf
    /u02/app/oracle/oradata/SOURCE_DB_NAME/users01.dbf
    /u02/app/oracle/oradata/SOURCE_DB_NAME/pdbseed/sysaux01.dbf
    /u02/app/oracle/oradata/SOURCE_DB_NAME/pdb1/system01.dbf
    /u02/app/oracle/oradata/SOURCE_DB_NAME/pdb1/sysaux01.dbf
    /u02/app/oracle/oradata/SOURCE_DB_NAME/pdb1/SAMPLE_SCHEMA_users01.dbf
    /u02/app/oracle/oradata/SOURCE_DB_NAME/pdb1/example01.dbf

    Note down the path in the output above.

  6. Check the V$LOGFILE dynamic view.
    SELECT MEMBER FROM V$LOGFILE;Output similar to the following appears:MEMBER
    -------------------------------------------------------------------
    /u04/app/oracle/oradata/redo/redo03.log
    /u04/app/oracle/oradata/redo/redo02.log
    /u04/app/oracle/oradata/redo/redo01.log
    /u03/app/oracle/fast_recovery_area/SOURCE_DB_NAME/onlinelog/o1_mf_4_fddlmffq_.log
    /u03/app/oracle/fast_recovery_area/SOURCE_DB_NAME/onlinelog/o1_mf_5_fddlvjo1_.log
    /u03/app/oracle/fast_recovery_area/SOURCE_DB_NAME/onlinelog/o1_mf_6_fddlvjs4_.log
    /u03/app/oracle/fast_recovery_area/SOURCE_DB_NAME/onlinelog/o1_mf_7_fddlvjys_.log
    /u03/app/oracle/fast_recovery_area/SOURCE_DB_NAME/onlinelog/o1_mf_8_fddlvk7x_.log
    /u03/app/oracle/fast_recovery_area/SOURCE_DB_NAME/onlinelog/o1_mf_9_fddlvkfj_.log

    Note down the path in the output above.

  7. Use the information from this output to set the DB_FILE_NAME_CONVERT parameter.

    Note:

    Note that the name of the source database in this output is case sensitive.
    ALTER SYSTEM SET DB_FILE_NAME_CONVERT='+DATA',
    '/u02/app/oracle/oradata/SOURCE_DB_NAME/' SID='*' SCOPE=SPFILE;
  8. Set the LOG_FILE_NAME_CONVERT parameter. For example:
    ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='+RECO','/u04/app/oracle/redo/','+RECO',
    '/u03/app/oracle/fast_recovery_area/SOURCE_DB_NAME/onlinelog/' SID='*' SCOPE=SPFILE;
  9. Restart the database.
    SHUTDOWN IMMEDIATE,
    STARTUP
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. Sign in as the database software owner oracle.
    sudo su - oracle
  3. Sign in to the database instance as a user who has the SYSDBA administrator privilege. For example:
    sqlplus / as sysdba
  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. Check the names of the data files.
    SELECT NAME FROM V$DATAFILE;
    Output similar to the following appears:
    NAME
    ------------------------------------------------
    +DATA/target_db_unique_name/DATAFILE/system.273.972998889
    +DATA/target_db_unique_name/DATAFILE/sysaux.272.972998889
    +DATA/target__unique_name/DATAFILE/undotbs1.270.972998945
    +DATA/target_db_unique_name/690A484F7D3F1B6EE05332C6120A3C84/DATAFILE/system.266.972998961
    +DATA/target_db_unique_name/DATAFILE/users.263.972998969
    +DATA/target_db_unique_name/690A484F7D3F1B6EE05332C6120A3C84/DATAFILE/sysaux.269.972998945
    +DATA/target_db_unique_name/690A76A8ED011E29E05332C6120AD40F/DATAFILE/system.265.972998945
    +DATA/target_db_unique_name/690A76A8ED011E29E05332C6120AD40F/DATAFILE/sysaux.264.972998889
    +DATA/target_db_unique_name/690A76A8ED011E29E05332C6120AD40F/DATAFILE/users.261.972998971
    +DATA/target_db_unique_name/690A76A8ED011E29E05332C6120AD40F/DATAFILE/example.267.972998887
  6. Check the V$LOGFILE dynamic view.
    SELECT MEMBER FROM V$LOGFILE;
    Output similar to the following appears:
    MEMBER
    -------------------------------------------------------------------
    +RECO/target_db_unique_name/ONLINELOG/group_3.264.972998987
    +RECO/target_db_unique_name/ONLINELOG/group_2.263.972998987
    +RECO/target_db_unique_name/ONLINELOG/group_1.257.972998985
    +RECO/target_db_unique_name/ONLINELOG/group_4.265.972998987
    +RECO/target_db_unique_name/ONLINELOG/group_5.266.972998987
    +RECO/target_db_unique_name/ONLINELOG/group_6.267.972998989
    +RECO/target_db_unique_name/ONLINELOG/group_7.268.972998989
    +RECO/target_db_unique_name/ONLINELOG/group_8.269.972998989
    +RECO/target_db_unique_name/ONLINELOG/group_9.270.972998989
  7. Use the information from this output to set the DB_FILE_NAME_CONVERT parameter.For example:
    ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u02/app/oracle/oradata/SOURCE_DB_NAME/','+DATA' SID='*' SCOPE=SPFILE;
  8. Set the LOG_FILE_NAME_CONVERT parameter. For example:
    ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u04/app/oracle/oradata/redo/','+RECO','/u03/app/oracle/fast_recovery_area/SOURCE_DB_NAME/onlinelog/','+RECO' SID='*' SCOPE=SPFILE;
  9. Restart the database.
    srvctl stop database -db target_db_unique_name 
    srvctl startup database -db target_db_unique_name -o mount
Configure the Database with Oracle Data Guard Broker

You can use the dgmgrl 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 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:
    dgmgrl sys@ocic-orcl
    Enter password: password
  3. Using the dgmgrl 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 <Source_tns_name>; -- Uses the source TNS name
    
    add database <target_db_unique_name> as connect identifier is <target_tns_name>; --Uses the target TNS name
  4. Enable the configuration.
    enable configuration
  5. 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 utility. For example, for a source database whose TNS name is OCIC-ORCL:
    dgmgrl sys@ocic-orcl
    Enter password: password
  3. Check the configuration.
    show configuration verbose
  4. In the configuration verbose output, check the StaticConnectIdentifier setting.
    This setting should point to the standby database (the target database) connection ID. The Database Status setting should say SUCCESS.
  5. If necessary, use dgmgrl to change the StaticConnectIdentifier setting to point to the correct TNS net services name. For example:
    edit database source_db_unique_name set property staticConnectidentifier='source_TNS_name';
    edit database target_db_unique_name set property staticConnectidentifier='target_TNS_name';
  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.

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
Reapply Rolled Back Patches (if any) on Primary

After you complete the migration, you should reapply the patches(if any) that you had rolled back as part of the Prepare section. This step should be performed on primary database.

Note:

This step is applicable ONLY if you had applied patch for bug 18633374 in the Prepare step, and have rolled back any patches as part of that procedure.
  1. Login to the primary database as oracle
  2. Roll back patch 18633374.
    $ORACLE_HOME/OPatch/opatch rollback -id 18633374
    Wait for the roll back to complete.
  3. Go to My Oracle Support (MOS) page. Use the Search box to find the MOS note for the patch number that was rolled back as part of Prepare step .
  4. Follow the instructions in the ReadMe file to reapply the patch.
  5. Repeat the steps 2 and 3 for all the patches that were rolled back.