Integrated DDL and DML with Encrypt using Oracle GoldenGate 12c

Introduction: Integrated Replicat is a feature unique to Oracle GoldenGate 12c, for Oracle Database versions starting from Oracle 11g (11.2.0.4) and Oracle 12c (12.1.0). Classic Capture Mode: The Oracle GoldenGate Extract process captures data changes from the Oracle redo log files or archive log files on the source database system. Integrated Capture Mode: Oracle GoldenGate Extract process works directly with

Introduction:

Integrated Replicat is a feature unique to Oracle GoldenGate 12c, for Oracle Database versions starting from Oracle 11g (11.2.0.4) and Oracle 12c (12.1.0).

Classic Capture Mode:

The Oracle GoldenGate Extract process captures data changes from the Oracle redo log files or archive log files on the source database system.

Integrated Capture Mode:

Oracle GoldenGate Extract process works directly with the database log mining server which reads the database redo log files and captures the changes in the form of Logical Change Records (LCRs). This is then to the GoldenGate trail files.

Differences Between Classic Capture and Integrated Capture Mode:

In Integrated Capture mode, the extract process does not directly read the Oracle redo log files – that part of the job is performed by a logmining server residing in the Oracle database server.

This article explains how to configure Oracle GoldenGate (OGG) software to perform Integrated DDL and DML between Oracle 12c Database (orcl) and Oracle 12c database (orcldb).

For this exercise, use the details given below:

Activities completed in this Hands-On Lab:

  • Implementing Integrated Capture in Source Database with Uni-Directional replication
  • Implementing encrypted password in source database (orcl)
Source Database (orcl) Target Database (orcldb)
  1. Oracle Enterprise Linux 5.8 (x86-64)
  2. Oracle 12c R1 Database (12.1.0.1.0) ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1
  3. Oracle GoldenGate 12c (12.1.2.0.0) GOLDENGATE_HOME=/u01/app/ogg/12g
  4. Database Name: orcl
  5. Schema Name: scott
  6. Hostname: ggnode1
  7. IPAddress: 192.168.56.105
  1. Oracle Enterprise Linux 5.8 (x86-64)
  2. Oracle 12c R1 Database (12.1.0.1.0) ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1
  3. Oracle GoldenGate 12c (12.1.2.0.0) GOLDENGATE_HOME=/u01/app/ogg/12g
  4. Database Name: orcldb
  5. Schema Name: scott
  6. Hostname: ggnode2
  7. IPAddress: 192.168.56.106
>
OGG Processes at Source Database OGG Processes at Target Database
  • Manager: Listening port 15500
  • Extract Process-1: ESCOTT
  • Extract Process-2: PSCOTT
  • Manager: Listening port 15000
  • Replicat Process: RSCOTT

OGG Processes @Source database (orcl):

  • Extract Process (ESCOTT): This process will capture transactions from the Oracle transactions logs.
  • Extract Process (PSCOTT): This process reads from a trail file and not from database’s transaction log.

OGG Processes @Target database (orcldb):

  • Replicat Process (RSCOTT): This process is also known as the Integrated Replicat Process, and tells this process from which trail file to read.

Series of Steps @Source Database (orcl):

  • Install the Oracle 12c R1 database software as an ‘oracle’ user in the following directory:

ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1

  • Installing Oracle GoldenGate 12c software as a ‘oracle’ user in the following directory: GOLDENGATE_HOME=/u01/app/ogg/12g
  • Configure and create subdirectories for Oracle GoldenGate 12c in the source database (orcl).
  • Create and configure Manager, Extract processes, and Replicat process for bi-directional between Oracle 12c database (orcl) and Oracle 12c database (orcldb).

Installing Oracle GoldenGate 12c as ‘oracle’ user in the source database (orcl):

  • Select the option “Oracle GoldenGate for Oracle Database 12c”

  • Define the path for Oracle GoldenGate: /u01/app/ogg/12g:

db3

Configuring Oracle GoldenGate 12c in source database (orcl)

  • Run the ggsci command to log into the GoldenGate command line interface (GGSCI):

  • The following command will logs into the database as an ‘ogguser’

  • The following command will create subdirectories required for Oracle GoldenGate environment:

Log into the source database as a ‘sysdba’

  • Log into the source database (orcl) as a ‘sysdba’
  • Check the database archive log mode.
  • Create the GoldenGate tablespace
  • Create the GoldenGate user ‘ogguser’
  • Assign the privileges for ‘ogguser’ user
  • Add database level supplemental logging

Installing Oracle GoldenGate 12c as ‘oracle’ user in target database

Configuring the Target Database (orcldb):

  • Install Oracle 12c R1 database software as an ‘oracle’ user in the following directory:

  • Install Oracle GoldenGate 12c software as an ‘oracle’ user in the following directory: GOLDENGATE_HOME=/u01/app/ogg/12g
  • Configure and create subdirs for Oracle GoldenGate 12c in the source database (orcldb).
  • Create and configure Manager, Extract Replicat process for uni-directional between Oracle 12c database (orcl) and Oracle 12c database (orcldb)

Select the option “Oracle GoldenGate for Oracle Database 12c”.

Select Software Location: /u01/app/ogg/12g

db3

Run the ggsci command to log into the GoldenGate command line interface (GGSCI):

Oracle GoldenGate Command Interpreter for Oracle:

The following command will create subdirectories required for Oracle GoldenGate environment:

Creating subdirectories under current directory /u01/app/ogg/12g:

Log into the target database, check archive log mode and create user ‘ogguser’ with required privileges as ‘sysdba’:

  • Log into the target database (orcldb) as a ‘sysdba’
  • Check the database archive log mode
  • Create the GoldenGate tablespace
  • Create the GoldenGate user ‘ogguser’
  • Assign privileges for ‘ogguser’ user
  • Add database level supplemental logging

Log into Oracle GoldenGate 12c in Source Database Server:

Run the ggsci command to log into the GoldenGate command line interface (GGSCI):

The following command will log into the database as a ‘ogguser’:

Log into Oracle GoldenGate 12c in target database:

Run the ggsci command to log into the GoldenGate command line interface (GGSCI):

Oracle GoldenGate Command Interpreter for Oracle:

Checking transactions between Source Database (orcl) and Target Database (orcldb)

Implementing Encrypting password @Source Database (orcl):

Oracle GoldenGate provides the following options for encryption:

  • Login passwords used in the extract process and replicat process parameter files
  • Transaction data sent over TCP/IP networks

To encrypt the database password or data sent across via TCP/IP, Oracle GoldenGate uses Blowfish encryption.

Let’s examine some of the steps involved in setting up the encryption with GoldenGate.

Note: Copy ENCKEYS file generated in source database to target database to implement encrypted password functionality.

Oracle GoldenGate Command Interpreter for Oracle:

Stop the Manager Processes and Extract Processes and start both:

Checking the transactions after adding Keygen parameter file @source database:

Log into the source database (orcl):

Log into the target database (orcldb):

Summary:

Integrated capture mode is compatible with all the data types as well as compressed data. Integrated Capture was first introduced in Oracle GoldenGate 11g and it is compatible with the most of the Oracle Database versions. When we are configuring Oracle GoldenGate in the ASM and RAC environments, we don’t need to follow any extra steps. Oracle GoldenGate has built-in security features which implement security and protects the data transported over the network between source database and target databases.