1. Migrate a multi-node Database Cloud Service Deployment to Virtual Machine Database System
  2. Migrate Oracle RAC

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