1. Provision and deploy a maximum availability solution for PeopleSoft on Oracle Cloud
  2. Configure for Database Migration
  3. Set Up the Future Secondary Database

Set Up the Future Secondary Database

After you establish the first physical standby in Oracle Cloud Infrastructure (OCI), you'll create a second one in another region. This second database is the database in your cloud-based disaster recovery environment.

Oracle Data Guard cascade standby functionality, where the second standby receives its redo from the first standby, not directly from the on-premises primary, reduces network traffic from the on-premises host site. It will also establish what will ultimately be the main redo propagation route.

At this time, there are constraints preventing us from using OCI tooling to establish and fully manage our future disaster recovery database.Oracle Data Guard Association cloud service cannot currently register an existing standby database relationship and won't be able to manage the standby database configuration. Therefore, for example, Oracle Managed Disaster Recovery Cloud Service cannot be used.

Since both standby databases are established with an OCI-based placeholder database, the OCI control plane can manage patching and other lifecycle activity for each of them.

Create Placeholder Database

Use the OCI Console to create a new placeholder database in a different region (recommended) or in a different availability domain in the same region.

Follow the steps here, DO NOT delete the placeholder database using tooling such as OCI or dbaascli.
  1. Select Exadata On Oracle Public Cloud. Choose the Oracle Exadata Database Service on Dedicated Infrastructure service that you want to deploy the database on.

    Follow these constraints:

    • The database home must be at the same software version, release, and patch level as the source.
    • The DB_NAME must be the same as on the primary and the first standby database.
    • The DB_UNIQUE_NAME can be left blank or specified, but must be different from both the on-premises primary and the first physical standby database.
    • Do not configure automatic backups when provisioning this database.
    • Do not specify a PDB name when provisioning this database.
  2. Capture the cascade standby configuration data.
    1. Log in as the oracle OS user on one of the database nodes hosting the place holder database just created.
    2. Source the environment for this database.
    3. Run the following command using your DB_UNIQUE_NAME:
    $ srvctl config database -db DB_UNIQUE_NAME
    Save this configuration data, you'll use it in several steps below.
  3. Shut the placeholder database down.
    $ srvctl stop database -db cascade standby placeholder database -stopoption immediate
  4. Log in as the grid OS user. Using the command asmcmd, empty the files in the directories under +DATAC1/DB_UNIQUE_NAME:
    1. DATAFILE
    2. ONLINELOG
    3. All PDB GUID/DATAFILE
    4. All control files under +DATAC1/DB_UNIQUE_NAME/CONTROLFILE
    5. The password file as specified in the configuration data captured in Step 1
  5. Under +RECOC1/DB_UNIQUE_NAME, remove the files in the directories ARCHIVELOG, AUTOBACKUP, and FLASHBACKLOG.
  6. Do not remove the spfile.

Prepare for Database Restore

Configure the new Oracle home in preparation for the restore of the database.

  • Adjust the tnsnames.ora file on each environment to be aware of each of the other databases. Verify communications between environments.
  • Copy the password file from the first standby database.
  • Copy the Transparent Data Encryption (TDE) wallet from the first standby database.
  • Adjust the database parameters for the cascade standby database.

Configure TNS for Cascade Standby

Adjust the tnsnames.ora file on each environment to be aware of each of the other databases. Verify communications between environments.

Data Guard Broker must be able to communicate with each database in the configuration no matter which instance it is connected to. Oracle Zero Downtime Migration did this configuration for the initial standby relationship. You must add the cascade standby database into the configuration:
  • Add the TNS connect string for the cascade standby database to the tnsnames.ora files used by all Oracle Real Application Clusters (Oracle RAC) instances of the on-premises primary and the first standby databases
  • Add the TNS connect strings for the on-premises primary and the first OCI standby databases to the tnsnames.ora files used by all Oracle RAC instances of the cascade standby database.
These TNS entries must each use SCAN IP addresses, not the SCAN name. The following is an example of a compliant TNS entry that Oracle Zero Downtime Migration created for our first standby database: 
CDBHCM_iad1dx =
          (DESCRIPTION =
             (ADDRESS = (PROTOCOL = TCP) (HOST = <SCAN IPv4 address  1>) (PORT = 1521))
             (ADDRESS = (PROTOCOL = TCP) (HOST = <SCAN IPv4 address  2>) (PORT = 1521))
             (ADDRESS = (PROTOCOL = TCP) (HOST = <SCAN IPv4 address  3>)) (PORT = 1521))
            (CONNECT_DATA =
              (SERVER = DEDICATED)
              (SERVICE_NAME = CDBHCM_iad1dx)
              (FAILOVER_MODE =
                  (TYPE = select)
                  (METHOD = basic)
              )
              (UR=A)
             )
          )

You must log into each database server as the oracle OS user, source your environment, then change the directory to $TNS_ADMIN.

  1. For each Oracle RAC instance of both the on-premises primary and the first OCI standby, edit the tnsnames.ora file and add the cascade standby database TNS connect string.
  2. For each Oracle RAC instance of the OCI cascade standby, edit the tnsnames.ora file and add TNS connect strings for both the on-premises primary and the first OCI standby databases.
  3. Test that you can ping the first standby database from the cascade standby, using the tnsping utility with the added connect string alias.
    $ tnsping CDBHCM_iad1dx
    This should return OK with latency time in milliseconds. If OK is not returned, then check for errors and address accordingly.
  4. Test the connection from each of the database servers that will host the cascade standby database to the first standby database (CDBHCM_iad1dx) using SQL*Plus. You will need the SYS password for the primary.
    $ sqlplus sys/<password>@CDBHCM_iad1dx as sysdba
    Correct any errors and repeat until you can connect successfully.

Copy the Password File

Copy the password file from the first standby database.

  1. Log in to one of the servers hosting your first standby database (CDBHCM_iad1dx) as the oracle OS user.
  2. Use srvctl to determine where the password file for this database is located, then copy it to the /tmp directory.
    To determine the wallet root location, run the following as sysdba:
    $ srvctl config database -db first standby db name
  3. Look for the line that says “Password file:” and record its location (Oracle Automatic Storage Management (Oracle ASM) path).
  4. Become the grid OS user and use the asmcmd command to copy the password file to the /tmp directory:
    $ asmcmd -p
asmcmd> cd +DATAC1/path from step 3
asmcmd> cp <password file name> /tmp/password file name
  5. Transfer the password file to a temporary location on one of the cascade standby database servers using scp or by whatever means you use to transfer files within OCI.
  6. Log into the cascade standby database server on which the password file was placed, as the grid OS user. Copy the password file into Oracle ASM, using the location specified in the cascade standby configuration data above.
    $ asmcmd -p --privilege sysdba
asmcmd> pwcopy –dbuniquename cascade standby db unique name /tmp/password-file-name +ASM Diskgroup/path/password-file-name -f
    For example,
    asmcmd> pwcopy –dbuniquename CDBHCM_phx5s   /tmp/password file name +DATAC1/CDBHCM_phx5s/PASSWORD/orapwCDBHCM_phx5s -f
  7. Ensure all TNS connect strings are configured correctly by validating that each database can connect to all other databases. Fix any connection errors for the following connection attempts fails.
    1. From the on-premises (primary) database:
      $ sqlplus sys/password@first standby db as sysdba
$ sqlplus sys/password@cascade standby db as sysdba
    2. From the first physical standby:
      $ sqlplus sys/password@on-prem primary as sysdba
$ sqlplus sys/password@cascade standby db as sysdba
    3. From the cascade physical standby:
      $ sqlplus sys/password@on-prem primary as sysdba
$ sqlplus sys/password@first standby db as sysdba
    Do not proceed until all connection attempts succeed.

Copy the TDE Wallet

Copy the Transparent Data Encryption (TDE) wallet from the first standby database. On Oracle Exadata Database Service on Dedicated Infrastructure, the location that cloud tooling uses to store the TDE wallets is on Oracle Advanced Cluster File System (Oracle ACFS), which all database servers in the cluster share.
  1. Log in to one of the servers hosting your first standby database (CDBHCM_iad1dx) as the oracle OS user and change directory to the wallet root location.
    To determine the wallet root location, run the following as sysdba:
    $ sqlplus / as sysdba
SQL> show wallet_root
$ cd wallet root location from “show wallet_root” above
  2. Go to the wallet root location and zip up the tde directory.
    The tde directory is under the directory given in step 1, which is typically /var/opt/oracle/dbaas_acf/<DB_NAME>/wallet_root. 
    $ zip -r CDBHDM_tde_wallet.zip  tde
  3. Transfer this ZIP file to one of the database servers that will host the cascade database (CDBHCM_phx5s) to a temporary location (for example, /tmp).
    Use scp or by whatever means you use to transfer files within OCI.
  4. Log into the database servers that will host the cascade database (CDBHCM_phx5s) and on which the zip file was place, as the oracle OS user and change directory to the wallet root location.

    The location should be the same as earlier, since the DB_NAME is the same (CDBHCM). 

    $ cd /var/opt/oracle/dbaas_acf/<DB_NAME>/wallet_root
  5. Move the existing TDE directory to different name.
    $ mv tde tde_date
  6. Move the ZIP file containing the TDE wallet (CDBHDM_tde_wallet.zip) created in Step 2 to following path:/var/opt/oracle/dbaas_acf/<DB_NAME>/wallet_root.
    Replace DB_NAME with the name of your database.
  7. Unzip the CDBHDM_tde_wallet.zip file.
    $ unzip CDBHDM_tde_wallet.zip

This creates a new tde subdirectory with the wallet files from the first physical standby database.

Adjust the Database Parameters for Cascade Standby

Finalize the configuration of the cascade standby database.

  1. Log in to one of the servers hosting the first standby database (CDBHCM_iad1dx) as the oracle OS user and source the environment. 
    $ . ./CDBHCM.env
  2. Create a pfile from the first standby database to be used as a reference for adjusting the parameters on the cascade standby database.
    $ cd $ORACLE_HOME/dbs
$ sqlplus / as sysdba
SQL> create pfile=’tmp_CDBHCM_iad1dx_init.ora’ from spfile;
  3. Log into one of the database servers that will host the cascade standby database (CDBHCM_phx5s) and source the environment:
    $ . ./CDBHCM.env
  4. Startup NOMOUNT on one instance.
    $ sqlplus / as sysdba
SQL> startup nomount
  5. Make the following adjustments to the database parameters for the cascade database, referencing the list of database parameters from Step 2 above:
    SQL> alter system set control_files=’’ sid=’*’ scope=spfile;
SQL> alter system set undo_tablespace=’<Refer to the parameter list from step 2>’ sid=’<ORACLE_SID for instance 1>’ scope=spfile;
SQL> alter system set undo_tablespace=’<Refer to the parameter list from step 2>’ sid=’<ORACLE_SID for instance 2>’ scope=spfile;
SQL> alter system set undo_tablespace=’<Refer to the parameter list from step 2>’ sid=’<ORACLE_SID for instance N>’ scope=spfile;
SQL> alter system set sga_target=’<Refer to the parameter list from step 2>’ sid=’*’ scope=spfile;
SQL> alter system set log_buffer=’<Refer to the parameter list from step 2>’ sid=’*’ scope=spfile;
  6. Adjust the parameters specific to PeopleSoft.
    SQL> alter system set “_gby_hash_aggregation_enabled”=false sid=’*’ scope=spfile;
SQL> alter system set “_ignore_desc_in_index”=true sid=’*’ scope=spfile;
SQL> alter system set “_unnest_subquery”=true sid=’*’ scope=spfile;
SQL> alter system set nls_length_semantics='CHAR' sid=’*’ scope=spfile;

    Note:

    Do not change the following parameters:
    • DB_NAME
    • DB_UNIQUE_NAME
    • WALLET_ROOT
  7. Shut down and restart NOMOUNT on the instance to implement the changes.
    $ sqlplus / as sysdba
SQL> shutdown immediate
SQL> startup nomount

Restore the Database to the Cascade Standby

Restore the database onto the cascade standby footprint from the first physical standby database. Use the Oracle Recovery Manager (RMAN) command RESTORE FROM SERVICE to restore the control file and data files.

  1. If the instance for the cascade standby is not started, then start it in NOMOUNT:
    $ sqlplus / as sysdba 
$ SQL> startup nomount
  2. Use RMAN to restore the control file and data files from the first standby to the cascade standby.

    Note:

    It may be necessary to adjust the number of RMAN channels for “device type disk” so as not to saturate the network. If a change is required, then do so before executing the command “restore database from service”. You can do this with the following command, replacing N as appropriate: 
    RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM N; 
    $ rman target / nocatalog
RMAN> restore standby controlfile from service ‘first standby db name’;
RMAN> alter database mount;
RMAN> restore database from service ‘first standby db name’ section size 8G;
RMAN> shutdown immediate;
RMAN> exit
  3. Restart all instances and mount the cascade standby database using srvctl.
    $ srvctl start database -db cascade standby db unique name -startoption mount
  4. Create and clear all online and standby log files using the following script.
    $ sqlplus “/ as sysdba”
SQL> set pagesize 0 feedback off linesize 120 trimspool on
SQL> spool /tmp/clearlogs.sql
SQL> select distinct 'alter database clear logfile group '||group#||';' from v$logfile;
SQL> spool off
  5. Inspect the generated clearlogs.sql script before executing it. It will cause the database instance to create and clear both online and standby logs files for all threads. Then execute the script.
    SQL> @/tmp/clearlogs.sql

Configure Data Guard Broker for the Cascade Standby

You already configured Data Guard Broker between the on-premises primary and the first OCI standby database by Oracle Zero Downtime Migration, now you'll add the cascade standby to the configuration.

The cascade standby and the on-premises databases do not communicate directly with each other. When necessary, their redo is shipped through the first on-premises standby database:

  • When the on-premises database is primary, redo is sent from the on-premises primary to or through the first standby, then to the cascade standby:
    • On-premises primary to the OCI first standby
    • OCI first standby to the OCI cascade standby
  • When the first standby is in the primary role, redo is sent from that database directly to both the on-premises and the cascade standby databases:
    • OCI primary to the on-premises standby
    • OCI primary to the OCI cascade standby
  • If the cascade standby becomes primary in this configuration, redo will be sent from that database to or through the OCI first standby, then to the on-premises database:
    • OCI first standby to the on-premises standby
    • OCI cascade primary to the OCI first standby
  1. Configure Data Guard Broker on the database server hosting the cascade standby. Log into one of the database servers hosting the cascade standby database as the oracle OS user and source the environment. 
    $ sqlplus / as sysdba
SQL> alter system set dg_broker_config_file1=’+DTAC1/cascade standby db/DG/dr1 cascade standby db.dat’ sid=’*’ scope=both;
SQL> alter system set dg_broker_config_file2=’+RECOC1/cascade standby db/DG/dr2 cascade standby db.dat’ sid=’*’ scope=both;
SQL> alter system set dg_broker_start=TRUE sid=’*’ scope=both;
  2. Log into either the primary or the first physical standby database, and source the environment. Add the new cascade standby to the existing Data Guard Broker configuration.
    $ dgmgrl 
DGMGRL>  connect sys/password
DGMGRL> show configuration
DGMGRL> add database 'cascade standby db’
 as connect identifier is cascade standby db;
  3. Add redo routes.
    DGMGRL> edit database on-premises db set property redoroutes='(LOCAL : first standby db ASYNC)';
DGMGRL> edit database first standby db set property redoroutes='(LOCAL : on-premises db ASYNC, cascade standby db ASYNC)(on-premises db : cascade standby db ASYNC)(cascade standby db : on-premises db ASYNC)';
DGMGRL> edit database cascade standby db set property redoroutes='(LOCAL : first standby db ASYNC)';
  4. Enable the new cascade standby database.
    DGMGRL> enable database cascade standby db;
  5. Once the cascade database is enabled, it will start to receive redo generated by the on-premises primary database through the first standby database. From within Data Guard Broker, show the configuration:
    DGMGRL> show configuration lag
Configuration - zdm_psfthcm_dg
  Protection Mode: MaxPerformance
  Members:
  CDBHCM_sca6dp  - Primary database
    CDBHCM_iad1dx - Physical standby database 
                      Transport Lag:      0 seconds (computed 0 seconds ago)
                      Apply Lag:          0 seconds (computed 1 second ago)
      CDBHCM_phx5s - Physical standby database (receiving current redo)
                        Transport Lag:      1 second (computed 1 second ago)
                        Apply Lag:          2 seconds (computed 1 second ago)

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 47 seconds ago)

Define the Role-Based Database Services for the Future Standby

Add role-based database services that the PeopleSoft application will use when the OCI secondary database is filling the PRIMARY role.

  1. Add role-based database services for the process scheduler.
    srvctl add service -db CDBHCM_phx5s -pdb HR92U033 -service HR92U033_BATCH -preferred "CDBHCM1,CDBHCM2" -notification TRUE -role PRIMARY -failovermethod BASIC -failovertype AUTO -failoverretry 10 -failoverdelay 3
  2. Add role-based database services for the online users. 
    srvctl add service -db CDBHCM_phx5s -pdb HR92U033 -service HR92U033_ONLINE -preferred "CDBHCM1,CDBHCM2" -notification TRUE -role PRIMARY -failovermethod BASIC -failovertype AUTO -failoverretry 10 -failoverdelay 3