Setting Up the Oracle RDBMS and the Oracle GoldenGate Environment for Oracle GoldenGate Studio 12c


Options



Before You Begin

Purpose

This tutorial shows you how to prepare the Oracle RDBMS and the Oracle GoldenGate core components to work with Oracle GoldenGate Studio 12.2.1.

Time to Complete

Approximately twenty minutes.

What Do You Need?

You need a running Oracle RDBMS instance. You can refer to another OBE which covers creating an Oracle RDBMS instance suitable for Oracle GoldenGate Creating the Oracle Database Release 12c Instance for Data Synchronization Using Oracle GoldenGate 12c on Linux.

The RDBMS instance can reside on a different computer available on your network. This OBE uses the oracle instance ogg12 where the Fusion Middleware Oracle GoldenGate Studio repository is stored. In your environment, you can use a different Oracle RDBMS instance, as long as it is accessible over the network from the computer where you are installing Oracle GoldenGate Studio.

You also need Java installed on your computer. This OBE uses the JDK 1.8.0_77 - Java 8 is generally supported, you can use different versions as long as they are supported by the Oracle Fusion Middleware Supported Configurations Guide.

Several environment variables must be defined so that your bash shell is able to interact with the Oracle RDBMS, Oracle GoldenGate, and the Fusion Middleware Infrastructure. At a minimum, the variables shown below must be defined:

  • ORACLE_SID - in this OBE it is assumed to be ogg12, replace that SID with yours if your emvironment runs a different SID.
  • ORACLE_HOME - in this OBE it is assumed to be /u01/app/oracle/product/12.1.0.2/db_1 - change it if necessary to accommodate your environment if the Oracle software location is different in your environment.
  • JAVA_HOME - in this OBE it is assumed to be pointing to /usr/java/latest.
  • LD_LIBRARY_PATH - it must include the lib directory under $ORACLE_HOME. For example: LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:${ORACLE_HOME}/lib
  • PATH - it must include the bin directory under $ORACLE_HOME. For example: PATH=${PATH}:${ORACLE_HOME}/bin

Setting Up the Oracle RDBMS and the Oracle GoldenGate Environment

Setting up Database Parameters and Needed Objects

  1. Before you can use the OGG Studio GUI to create and deploy your replication processes, you must be sure that the Oracle RDBMS which you are using for this OBE is equipped to support a replication environment.

    The database instance must:

    • Be configured in archivelog mode
    • The enable_goldengate_replication instance parameter should be set to TRUE
    • Supplemental log data should be enabled
    • The instance should be set in FORCE LOGGING mode
    • An Oracle GoldenGate admininstrator user should be defined
    • The DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE() procedure should be run to enable the necessary privileges for the OGG administrator user.

    In addition, you should create the replication source schema (WEST) and the replication target schema (EAST) and create some objects in those schemas (tables and indexes) and populate those tables with some initial data.

  2. Download the SQL companion scripts to this OBE: the OBE_DDL_FILES.zip zip file.

    Open a termanl shell window and change directory to your default download directory. If you are running this OBE as the "oracle" user, the directory is /home/oracle/Downloads. Run the unzip command to extract all files. leave the terminal shell window open.

    $ cd ~
    $ cd Downloads
    $ unzip OBE_DDL_FILES.zip
    Archive:  OBE_DDL_FILES.zip
      inflating: drop_tables.sql         
      inflating: economic_entity.sql     
      inflating: gdp_by_year.sql         
      inflating: gdp_by_year_2008.sql    
      inflating: gdp_by_year_2009.sql    
      inflating: gdp_by_year_2010.sql    
      inflating: gdp_growth_by_year.sql  
      inflating: gdp_growth_by_year_2008.sql  
      inflating: gdp_growth_by_year_2009.sql  
      inflating: gdp_growth_by_year_2010.sql  
      inflating: oracle_table_creation.sql  
    $
    
  3. Define the ORACLE_SID environment variable to point to the Oracle instance running on your computer (in this OBE it is assumed to be ogg12.) If in your environment the instance is called differently, simply replace ogg12 with your instance name.
    Make sure that the environment variable ORACLE_HOME is defined and points to the directory where the Oracle software is installed. Also, make sure that the environment variable LD_LIBRARY_PATH is defined and includes the $ORACLE_HOME/lib directory. An example of your .bashrc file in case you have an instance called ogg12 and the Oracle software is located in /u01/app/oracle/product/12.1.0.2/db_1 is shown below:

    $ cd ~
    $ cat .bashrc
    ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1
    export ORACLE_HOME
    JAVA_HOME=/usr/java/latest
    export JAVA_HOME
    ORACLE_SID=ogg12
    export ORACLE_SID
    ORACLE_BASE=/u01/app/oracle
    export ORACLE_BASE
    LD_LIBRARY_PATH=$${LD_LIBRARY_PATH}:${ORACLE_HOME}/lib
    export LD_LIBRARY_PATH
    PATH=${PATH}:${ORACLE_HOME}/bin
    export PATH
    $
    

    Note that you also must make sure that the location where the oracle software executables are stored ($ORACLE_HOME/bin) is added to the the PATH, so that sqlplus is found by the shell. Source the .bashrc file so that your shell is updated with the newly defined environment variables. Connect to your database using sqlplus as sysdba and verify that the instance is running in archivelog mode.

    $ source .bashrc 
    $ sqlplus / as sysdba 
    SQL*Plus: Release 12.1.0.2.0 Production on Sun Jul 3 10:00:35 2016
    Copyright (c) 1982, 2014, Oracle.  All rights reserved.
    
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    
    SQL> SELECT log_mode FROM v$database; 
    LOG_MODE
    ------------
    ARCHIVELOG
    
    SQL> 
    

    The expected setting for log_mode is ARCHIVELOG. If in your environment the instance is running in NOARCHIVELOG you must execute the commands listed below to set your instance to run in ARCHIVELOG mode.

    Only execute these commands to change the mode from NOARCHIVELOG to ARCHIVELOG:

    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup mount
    ORACLE instance started.
    
    Total System Global Area 2147483648 bytes
    Fixed Size		    2926472 bytes
    Variable Size		 1644169336 bytes
    Database Buffers	  486539264 bytes
    Redo Buffers		   13848576 bytes
    Database mounted.
    SQL>  ALTER DATABASE ARCHIVELOG; 
    Database altered.
    
    SQL>  ALTER DATABASE OPEN; 
    Database altered.
    
    SQL>  SELECT log_mode FROM v$database; 
    
    LOG_MODE
    ------------
    ARCHIVELOG
    
    SQL>  
    
  4. Verify that the instance parameter enable_goldengate_replication has been set to TRUE. If it is not set to TRUE, use the ALTER SYSTEM command to change it.

    SQL>  show parameter enable_goldengate_replication
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    enable_goldengate_replication        boolean     FALSE
    SQL>  alter system set enable_goldengate_replication=true scope=both;
    
    System altered.
    
    SQL> show parameter enable_goldengate_replication
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    enable_goldengate_replication        boolean     TRUE
    
    
  5. Varify that the database has supplemental logging and force logging enabled. Use the command ALTER DATABASE to set those values if necessary.

    SQL>  SELECT supplemental_log_data_min, force_logging FROM v$database;
    SUPPLEME FORCE_LOGGING
    -------- ---------------------------------------
    NO	 NO
    SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; 
    
    Database altered.
    
    SQL> ALTER DATABASE FORCE LOGGING; 
    
    Database altered.
    
  6. If you had to modify the supplemental or force logging parameters, to ensure that the redo and archive logs contain supplemental log data, switch the logs by executing the following ALTER SYSTEM command:

    SQL> ALTER SYSTEM SWITCH LOGFILE; 
    
    System altered.
    
  7. Create the WEST and EAST users/schemas with minimal pivileges. Create the OGGADMIN user giving it the required privileges.

    SQL> create user west identified by Welcome1 default tablespace users temporary tablespace temp;  
    
    User created.
    
    SQL> grant CONNECT,RESOURCE, select any dictionary to WEST;
    
    Grant succeeded.
    
    SQL>  alter user WEST quota unlimited on USERS;
    
    User altered.
    
    SQL> create user east identified by Welcome1 default tablespace users temporary tablespace temp;  
    
    User created.
    
    SQL> grant CONNECT,RESOURCE, select any dictionary to EAST;
    
    Grant succeeded.
    
    SQL>  alter user EAST quota unlimited on USERS;
    
    User altered.
    
    SQL> create user oggadmin identified by Welcome1 default tablespace users temporary tablespace temp;  
    
    User created.
    
    SQL> grant connect, resource, ALTER SESSION,SELECT ANY DICTIONARY,FLASHBACK ANY TABLE to oggadmin;
    
    Grant succeeded.
    
    SQL> grant SELECT ANY TABLE, CREATE ANY TABLE , UPDATE ANY TABLE, DELETE ANY TABLE to oggadmin;
    
    Grant succeeded.
    
    SQL>  alter user oggadmin quota unlimited on USERS;
    
    User altered.
    
    SQL>  grant execute on DBMS_FLASHBACK to OGGADMIN;
    
    Grant succeeded.
    
  8. Run the DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE() to enable the Oracle GoldenGate privileges for the OGGADMIN user. Disconnect from the ogg12 instance.

    SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('OGGADMIN');    
    
    PL/SQL procedure successfully completed.
    
    SQL>  exit
    Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    $
    
  9. Change directory to the directory where you downloaded to SQL scripts for this OBE. This OBE assumes that directory to be /home/oracle/Downloads. If in your environment you are not connected to the Linux computer as the "oracle" user, or if your default download directory is different, adjust the commands shown below to your environment.

    $ cd ~  
    $ cd Downloads  
    [Downloads]$ pwd
    /home/oracle/Downloads
    
  10. Using sqlplus, connect to your Oracle instance as the user west. Run the script oracle_table_creation.sql to create the necessary objects for the west user. Make sure the three tables:

    • ECONOMIC_ENTITY
    • GDP_BY_YEAR
    • GDP_GROWTH_BY_YEAR
    have been successfully created. Run the economic_entity.sql script to populate the ECONOMIC_ENTITY table. Grant the INSERT,UPDATE,DELETE privileges on the three tables ECONOMIC_ENTITY,GDP_BY_YEAR,GDP_GROWTH_BY_YEAR to the OGGADMIN user. Exit sqlplus.
    $ sqlplus west/Welcome1@ogg12  
    SQL*Plus: Release 12.1.0.2.0 Production on Sun Jul 3 12:38:59 2016
    
    Copyright (c) 1982, 2014, Oracle.  All rights reserved.
    
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    
    SQL> @oracle_table_creation.sql
    
    Table created.
    
    
    Table created.
    
    
    Table created.
    
    SQL> select table_name from user_tables;
    
    TABLE_NAME
    --------------------------------------------------------------------------------              
    ECONOMIC_ENTITY
    GDP_BY_YEAR
    GDP_GROWTH_BY_YEAR
    
    SQL> @economic_entity.sql
    1 row created.
    
    1 row created.
    
     ... many lines omitted for clarity ... 
    
    1 row created.
    
    Commit complete.
    
    
    SQL> select count(*) from economic_entity;                                                   
    
      COUNT(*)
    ----------
           235
    
    SQL> grant update,insert,delete on economic_entity to oggadmin;   
    
    Grant succeeded.
    
    SQL> grant update,insert,delete on gdp_by_year to oggadmin;   
    
    Grant succeeded.
    
    SQL> grant update,insert,delete on gdp_growth_by_year to oggadmin;   
    
    Grant succeeded.
    
    SQL> exit
    Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    [Downloads]$ 
    
  11. Using sqlplus, connect to your Oracle instance as the user east. Run the script oracle_table_creation.sql to create the necessary objects for the east user. Make sure the objects have been successfully created. Run the economic_entity.sql script to populate the ECONOMIC_ENTITY table. Grant the INSERT,UPDATE,DELETE privileges on the the three tables ECONOMIC_ENTITY,GDP_BY_YEAR,GDP_GROWTH_BY_YEAR to the OGGADMIN user. Exit sqlplus.

    $ sqlplus east/Welcome1@ogg12  
    SQL*Plus: Release 12.1.0.2.0 Production on Sun Jul 3 14:52:17 2016
    
    Copyright (c) 1982, 2014, Oracle.  All rights reserved.
    
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    
    SQL> @oracle_table_creation.sql
    
    Table created.
    
    
    Table created.
    
    
    Table created.
    
    SQL> select table_name from user_tables;
    
    TABLE_NAME
    --------------------------------------------------------------------------------              
    ECONOMIC_ENTITY
    GDP_BY_YEAR
    GDP_GROWTH_BY_YEAR
    
    SQL> @economic_entity.sql
    1 row created.
    
    1 row created.
    
     ... many lines omitted for clarity ... 
    
    1 row created.
    
    Commit complete.
    
    
    SQL> select count(*) from economic_entity;                                                   
    
      COUNT(*)
    ----------
           235
    
    SQL> grant update,insert,delete on economic_entity to oggadmin;   
    
    Grant succeeded.
    
    SQL> grant update,insert,delete on gdp_by_year to oggadmin;   
    
    Grant succeeded.
    
    SQL> grant update,insert,delete on gdp_growth_by_year to oggadmin;   
    
    Grant succeeded.
    
    SQL> exit
    Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    [Downloads]$ 
    

Using Oracle Goldengate Wallet to Securely Store Credentials

  1. To avoid storing passwords in clear text in the various Extract and Replicat parameter files, OracleGoldenGate 12c offers the wallet facility. You can store encrypted credentials in the wallet credential store and refer them through an alias, rather than the combination username/password. Change directory to the Oracle GoldenGate source instance (/u01/app/oracle/product/ogg_src and launch ggsci

    $ cd /u01/app/oracle/product/ogg_src
    [ogg_src]$ ./ggsci
    Oracle GoldenGate Command Interpreter for Oracle
    Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
    Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48
    Operating system character set identified as UTF-8.
    Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
    
    GGSCI 1> 
    

    Enter the command create wallet and add a credential store to it.

    GGSCI 1> create wallet
    
    Created wallet at location 'dirwlt'.
    
    Opened wallet at location 'dirwlt'.
    
    GGSCI 2> add credentialstore
    
    Credential store created in ./dircrd/.
    
  2. Add the oggadmin, west and east user credentials to the credential store.

    GGSCI 3> Alter credentialstore add user oggadmin password Welcome1 alias oggadmin      
    Credential store in ./dircrd/ altered.
    GGSCI 4> Alter credentialstore add user west password Welcome1 alias west 
    
    Credential store in ./dircrd/ altered.
    
    GGSCI 5> Alter credentialstore add user east password Welcome1 alias east 
    
    Credential store in ./dircrd/ altered.
    
    
  3. Connect to the ogg12 database using the newly created aliases.

    GGSCI 6> dblogin useridalias oggadmin       
    Successfully logged into database.
    
    GGSCI 7> dblogin useridalias west 
    Successfully logged into database.
    
    GGSCI 8> dblogin useridalias east 
    Successfully logged into database.
    
  4. Repeat the same steps (1-3) for the Oracle Goldengate replication target. Exit ggsci, change directory to /u01/app/oracle/product/ogg_trg and launch ggsci in that environment. Create the wallet and the credential store, and add the same user credentials as you did for your replication source (oggadmin, west and east). Use dblogin to test that you can connect to the ogg12 database using the aliases you defined for your replication target.

    IMPORTANT: these steps are necessary to allow OGG Studio to connect to the database using the Oracle GoldenGate Monitor agents without sending passwords in clear over the network.

Want to Learn More?