3 Installing and Configuring Oracle GoldenGate

You should perform the Oracle GoldenGate installation tasks, as described in this chapter, only if you have an on-premise version of Oracle Clinical (OC) and a cloud version of OHSCDA installed.

• Section 3.1, "Prerequisite"

• Section 3.2, "Installing Oracle GoldenGate"

• Section 3.3, "Deploying and Configuring OHSCDA-specific Oracle GoldenGate Code"

• Section 3.4, "Encrypting Oracle GoldenGate Data"

• Section 3.5, "Executing Scripts"

• Section 3.6, "Executing Full Load"

• Section 3.7, "Executing an Incremental Load"

3.1 Prerequisite

The Oracle Clinical database must be on version 12.1.0.2.

3.2 Installing Oracle GoldenGate

1. Set the environment variables on the source and target database systems.

   Variable Name	Value
   export ORACLE_HOME	<Oracle home directory>
   export PATH	$ORACLE_HOME/bin:$PATH
   export TNS_ADMIN	$ORACLE_HOME/network/admin
   export ORACLE_SID	If the source database is a pluggable database, set this value the same as the SID value set for the container database. If the source database is a standalone database, set the value (as required) for a regular database.
   export gg_home	<GoldenGate install directory>
   export LD_LIBRARY_PATH	${ORACLE_HOME}/lib

   
   

2. On the OC source database SQLPlus prompt, execute the following:

   SELECT log_mode FROM v$database;
   

3. If log_mode is NOARCHIVELOG, execute the following commands sequentially in SQLPlus:

   1. shutdown immediate

   2. startup mount

   3. ALTER DATABASE ARCHIVELOG;

   4. ALTER DATABASE OPEN;

   5. SELECT log_mode FROM v$database;

      This command ensures that ARCHIVELOG is enabled.

4. Install Oracle GoldenGate on the source and target database systems. For information, see Oracle Fusion Middleware Installing and Configuring Oracle GoldenGate for Oracle Database guide.

   Note:

   While installing Oracle GoldenGate, set the source manager port to 7809 and the target manager port to 7909.

3.3 Deploying and Configuring OHSCDA-specific Oracle GoldenGate Code

On the source database system:

1. Back up the mgr.prm file located in the $gg_home\dirprm directory.

2. Navigate to OCDA_ODI_Home\OCDA_Common\cda_goldengate.

3. Open mgr.prm and make sure the manager port number is 7809 (provided during GoldenGate installation).

4. Copy contents of the source_dirprm folder to the source GoldenGate system directory $gg_home/dirprm.

5. Open the fulpinta.prm and pinta.prm files and enter values for the following:

   • rmthost <target host IP address>

   • If the OC source database is a pluggable database, uncomment SOURCECATALOG <PDB> and enter the PDB name.

6. Open the einta.prm file, and if the OC source database is a pluggable database, uncomment SOURCECATALOG <PDB> and enter the PDB name.

On the target database system:

1. Back up the mgr.prm file located in the $gg_home\dirprm directory.

2. Navigate to OCDA_ODI_Home\OCDA_Common\cda_goldengate.

3. Open mgr.prm and make sure the manager port number is 7909 (provided during GoldenGate installation).

4. Copy contents of the target_dirprm folder to the target GoldenGate system directory $gg_home/dirprm.

5. Open the rinta.prm and fulrinta.prm files, and perform the following for all table entries:

   • If the OC source database is a pluggable database, enter value for <SRC_PDB> (source PDB from where tables were extracted). If the OC source database is a standalone database, delete <SRC_PDB>.

   • Change the Target parameter <PDB> to the PDB name.

   • Change the Target parameter <gg_oc_tgt_user> to the target OC schema name.

     For example, Map OPA.OPA_LEVEL_PRIVS, Target <PDB>.<gg_oc_tgt_user>.OPA_LEVEL_PRIVS;

3.4 Encrypting Oracle GoldenGate Data

1. Navigate to the Oracle GoldenGate home directory on the source database system.

2. From the shell command prompt, execute the following:

   ./keygen 256 1
   

   This generates a key. For example: 0x08E6F4291BD769739204C50E4A50C93B.

3. From the shell command prompt, execute the followings commands to create an ENCKEYS file and add the key generated in previous step:

   vi ENCKEYS
   cdasecurekey 0x08E6F4291BD769739204C50E4A50C93B
   

   The key alias (cdasecurekey) in the ENCKEYS file will be used in the extract, pump, and replicate files.

4. Copy the ENCKEYS file to the Oracle GoldenGate software location on the target database system.

5. From the shell command prompt on the source and target database systems, execute the following command to set permission on the ENCKEYS file:

   chmod 640 ENCKEYS
   

3.5 Executing Scripts

This section describes information on executing scripts on the OC source and target systems.

3.5.1 Executing Scripts on Oracle Clinical Source System

1. From the SQLPlus prompt, perform one of the following:

   • If OC source database is a standalone database, execute the following:

     cda_gg_oc_source_config.sql
     

   • If OC source database is a pluggable database, execute the following:

     cda_gg_oc_source_config_pdb.sql
     

2. From the Oracle GoldenGate Software Command Interface (GGSCI) terminal, execute the following commands sequentially:

   1. Create Wallet

   2. Add CredentialStore

   3. Alter CredentialStore Add User <ggadmin_user>@<cdb/dbname> Alias <ogg_user>

      At the password prompt, enter a password (<ggadmin_passwd>) for the <ggadmin_user>.

      Enter the same values in the prompt as provided in step 1.

   4. Info CredentialStore

      This displays the user added in the wallet.

3. From the GGSCI terminal, execute the following:

   dblogin useridalias <ogg_user>
   

4. Perform one of the following:

   • If the OC source database is a pluggable database:

     a. Navigate to the source Oracle GoldenGate home directory/dirprm, and open the ocda_add_trana_data_pdb.oby file.

     b. Add the PDB name (<PDB>) for all table entries.

     c.From the GGSCI terminal, execute the following:

     Obey  ./dirprm/ocda_add_trana_data_pdb.oby
     

   • If the OC source database is a standalone database:

     a. Navigate to the source Oracle GoldenGate home directory/dirprm, and open the ocda_add_trana_data.oby file.

     b. Add the PDB name (<PDB>) for all table entries.

     c. From the GGSCI terminal, execute the following:

     Obey  ./dirprm/ocda_add_trana_data.oby
     

5. From the SQLPlus prompt, execute the following:

   SELECT first_change#
   FROM v$archived_log 
     WHERE dictionary_begin = 'YES' AND 
       standby_dest = 'NO' AND
       name IS NOT NULL AND 
       status = 'A';
   

   Pick the maximum SCN <SCN_Number_1> (for example, 13910388958710), which will be used for the extract and pump operations.

   If the above query does not return any value:

   1. Connect as sys to the OC source database.

   2. From the SQLPlus prompt, execute the following:

      execute DBMS_LOGMNR_D.BUILD( options => DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
      

   3. Re-execute the following command to return <SCN_Number_1>:

      SELECT first_change#
      FROM v$archived_log 
        WHERE dictionary_begin = 'YES' AND 
          standby_dest = 'NO' AND
          name IS NOT NULL AND 
          status = 'A';
      

6. From the SQLPlus prompt, execute the following:

   select to_char(current_scn) from v$database;
   

   The database returns <SCN_Number_2>, which will be used in replicat.

7. Register the extract.

   • If the OC source database is a pluggable database, execute the following command from the GGSCI terminal:

     register extract einta database container(<pdb>)  SCN   <SCN_Number_1>
     

   • If the OC source database is a standalone database, execute the following command from the GGSCI terminal:

     register extract einta database SCN <SCN_Number_1>
     

8. From the GGSCI terminal, execute the following commands sequentially:

   1. add extract einta, integrated tranlog,SCN <SCN_Number_1>

   2. add exttrail ./dirdat/in, extract einta, megabytes 10

   3. add extract pinta, exttrailsource ./dirdat/in, SCN <SCN_Number_1>

   4. add rmttrail ./dirdat/pn, extract pinta, megabytes 10

3.5.2 Executing Scripts on Oracle Clinical Target System

1. From the SQLPlus prompt, execute the following:

   @cda_gg_target_config.sql
   

   Enter a value for each prompt.

   This creates an admin user on the Oracle GoldenGate target and grants the user the Oracle GoldenGate db config role. This script executes sub-scripts to create user, OC tables, function, view, and grants for the OC tables.

2. From the GGSCI terminal, execute the following commands sequentially to add <ggadmin_user> to the wallet and the alias <ogg_user> will be used in the extract files.

   1. Create Wallet

   2. Add CredentialStore

   3. Alter CredentialStore Add User <ggadmin_user>@<pdb> Alias <ogg_user>

      At the password prompt, enter a password (<ggadmin_passwd>) for the <ggadmin_user>.

      Enter the same values in the prompt as provided in step 1.

   4. Info CredentialStore

3. From the GGSCI terminal, execute the following commands sequentially:

   1. dblogin useridalias ogg_user

   2. add replicat fulrinta,exttrail ./dirdat/la,nodbCheckPoint

   3. add replicat rinta Integrated exttrail ./dirdat/pn

3.6 Executing Full Load

1. From the source Oracle GoldenGate home, execute the following command in a Bash/shell command prompt:

   ./extract paramfile ./dirprm/fulpinta.prm reportfile   ./dirrpt/FULPINTA.rpt
   

2. Monitor ggserr.log from the Oracle GoldenGate home in a separate Bash/shell command prompt.

   • On the source system, execute the following:

     tail -f ggserr.log
     

   • On the target system, execute the following:

     tail -f ggserr.log
     

   Monitor the following:

   • The collector starting on the target system

   • The trail files in the dirdat directory on the target system

   • The report file in the dirrpt directory on the source system

3. From the GGSCI terminal on the source system, execute the following:

   tail -f ggserr.log
   

   When extract is complete, the log file shows "EXTRACT FULPINTA stopped normally".

   The FULPINTA.rpt file is generated in the $gg_home/dirrpt directory.

4. From the GGSCI terminal on the target system, execute the following:

   start replicat fulrinta
   

   When the execution is complete, both the ggserr.log file and the dirrpt folder under the Oracle GoldenGate home directory will have the fulrinta file with a message that the execution has completed.

5. From the GGSCI terminal, execute the following:

   • On the source system:

     stop fulpinta
     

   • On the target system:

     stop fulrinta
     

6. Log on to the target database as an OC target user and execute the following command from the SQLPlus prompt:

   @ cda_oc_tables_index_constraints_creation.sql
   

3.7 Executing an Incremental Load

1. On the source system, execute the following commands sequentially from the GGSCI terminal:

   1. START EXTRACT einta ATCSN <SCN_Number_1>

   2. START EXTRACT pinta ATCSN <SCN_Number_1>

2. On the target system, execute the following command from the GGSCI terminal:

   START REPLICAT rinta, ATCSN <SCN_Number_2>
   

3. Monitor ggserr.log from the Oracle GoldenGate home in a separate Bash/shell command prompt.

   • On the source system, execute the following:

     tail -f ggserr.log
     

   • On the target system, execute the following:

     tail -f ggserr.log
     

   For specific log files related to the extract or replicat program, the $gg_home/dirrpt directory will have the corresponding log files on the source and target systems.

4. From the GGSCI terminal, execute the following commands to get the list of dml operations and the number of rows affected in the source database for each dml operation:

   • On the source system:

     stats einta
     stats pinta
     

   • On the target system:

     stats rinta
     

5. On the source system, execute the following command from the GGSCI terminal to get the list of program and their status:

   Info all <blank or program name>