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?