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):
[oracle@ggnode1 12g]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
  • The following command will logs into the database as an ‘ogguser’
GGSCI (ggnode1.oracle.com) 1> dblogin userid ogguser, password oracle
Successfully logged into database.
  • The following command will create subdirectories required for Oracle GoldenGate environment:
GGSCI (ggnode1.oracle.com) 2> CREATE SUBDIRS
Creating subdirectories under current directory /u01/app/ogg/12g

Parameter files /u01/app/ogg/12g/dirprm: created
Report files /u01/app/ogg/12g/dirrpt: created
Checkpoint files /u01/app/ogg/12g/dirchk: created
Process status files /u01/app/ogg/12g/dirpcs: created
SQL script files /u01/app/ogg/12g/dirsql: created
Database definitions files /u01/app/ogg/12g/dirdef: created
Extract data files /u01/app/ogg/12g/dirdat: created
Temporary files /u01/app/ogg/12g/dirtmp: created
Credential store files /u01/app/ogg/12g/dircrd: created
Masterkey wallet files /u01/app/ogg/12g/dirwlt: created
Dump files /u01/app/ogg/12g/dirdmp: created
GGSCI (ggnode1.oracle.com) 2> exit

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
[oracle@ggnode1 admin]$ sqlplus / as sysdba
SQL> archive log list;
SQL> CREATE TABLESPACE GOLDENGATE
			DATAFILE '/u01/app/oracle/oradata/orcl/goldengate01.dbf' SIZE 100M AUTOEXTEND ON;
SQL> CREATE USER ogguser IDENTIFIED BY oracle
			DEFAULT TABLESPACE GOLDENGATE TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;
SQL> GRANT CONNECT TO ogguser;
SQL> GRANT DBA TO ogguser;
SQL> GRANT UNLIMITED TABLESPACE TO ogguser;
SQL> GRANT SELECT ANY DICTIONARY TO ogguser;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN,LOG_MODE,FORCE_LOGGING FROM V$DATABASE;
SQL> exit

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:
ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1
  • 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@ggnode2 12g]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle:

Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO

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

GGSCI (ggnode2.oracle.com) 1> create subdirs

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

Parameter files              /u01/app/ogg/12g/dirprm: already exists
Report files                 /u01/app/ogg/12g/dirrpt: created
Checkpoint files             /u01/app/ogg/12g/dirchk: created
Process status files         /u01/app/ogg/12g/dirpcs: created
SQL script files             /u01/app/ogg/12g/dirsql: created
Database definitions files   /u01/app/ogg/12g/dirdef: created
Extract data files           /u01/app/ogg/12g/dirdat: created
Temporary files              /u01/app/ogg/12g/dirtmp: created
Credential store files       /u01/app/ogg/12g/dircrd: created
Masterkey wallet files       /u01/app/ogg/12g/dirwlt: created
Dump files                   /u01/app/ogg/12g/dirdmp: created
GGSCI (ggnode2.oracle.com) 2> exit

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
SQL> connect sys/oracle@orcldb as sysdba
SQL> archive log list
SQL> CREATE TABLESPACE GOLDENGATE DATAFILE '/u01/app/oracle/oradata/orcldb/goldengate01.dbf' SIZE 100M AUTOEXTEND ON;
SQL> CREATE USER ogguser IDENTIFIED BY oracle DEFAULT TABLESPACE GOLDENGATE TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;
SQL> GRANT CONNECT TO ogguser;
SQL> GRANT DBA TO ogguser;
SQL> GRANT UNLIMITED TABLESPACE TO ogguser;
SQL> GRANT SELECT ANY DICTIONARY TO ogguser;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN,LOG_MODE,FORCE_LOGGING FROM V$DATABASE;
SQL> exit

Log into Oracle GoldenGate 12c in Source Database Server:

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

[oracle@ggnode1 12g]$ ./ggsci

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

GGSCI (ggnode1.oracle.com) 1> dblogin userid ogguser, password oracle
Successfully logged into database.

GGSCI (ggnode1.oracle.com) 3> register extract escott database
Extract ESCOTT successfully registered with database at SCN 1737120.

GGSCI (ggnode1.oracle.com) 4> add extract escott, integrated tranlog, begin now
EXTRACT added.

GGSCI (ggnode1.oracle.com) 5> add exttrail ./dirdat/ea, extract escott, megabytes 10
EXTTRAIL added.

GGSCI (ggnode1.oracle.com) 6> add extract pscott, exttrailsource ./dirdat/ea
EXTRACT added.

GGSCI (ggnode1.oracle.com) 7> add rmttrail ./dirdat/pa, extract pscott, megabytes 10
RMTTRAIL added.

GGSCI (ggnode1.oracle.com) 8> add schematrandata scott
2015-05-25 15:17:38 INFO OGG-01788 SCHEMATRANDATA has been added on schema scott.
2015-05-25 15:17:38 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema scott.

GGSCI (ggnode1.oracle.com) 9> edit param mgr
GGSCI (ggnode1.oracle.com) 10> edit param escott
GGSCI (ggnode1.oracle.com) 11> edit param pscott

GGSCI (ggnode1.oracle.com) 3> view param escott
extract escott
exttrail ./dirdat/ea
userid ogguser, password oracle
ddl include all
ddloptions report
statoptions resetreportstats
tranlogoptions excludeuser ogguser
table scott.*;

GGSCI (ggnode1.oracle.com) 11> view param pscott
extract pscott
rmthost ggnode2, mgrport 15000, compress
rmttrail ./dirdat/pa
passthru
table scott.*;

GGSCI (ggnode1.oracle.com) 12> view param mgr
PORT 15500
DYNAMICPORTLIST 15510-15520
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS

GGSCI (ggnode1.oracle.com) 12> edit param ./GLOBALS
GGSCI (ggnode1.oracle.com) 13> view param ./GLOBALS

GGSCHEMA OGGUSER
ENABLEMONITORING
CHECKPOINTTABLE GGS_CHECKPOINT

GGSCI (ggnode1.oracle.com) 13> start mgr
Manager started.

GGSCI (ggnode1.oracle.com) 14> start er *

Sending START request to MANAGER ...
EXTRACT ESCOTT starting

Sending START request to MANAGER ...
EXTRACT PSCOTT starting

GGSCI (ggnode1.oracle.com) 15> info all

Program Status   Group  Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STARTING ESCOTT 00:00:00     00:08:19
EXTRACT RUNNING  PSCOTT 00:00:00     00:08:03

GGSCI (ggnode1.oracle.com) 19>

Log into Oracle GoldenGate 12c in target database:

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

[oracle@ggnode2 12c]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle:

Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Sep 25 2013 02:33:54

GGSCI (ggnode2.oracle.com) 1> dblogin userid ogguser, password oracle
Successfully logged into database.

GGSCI (ggnode2.oracle.com) 3> edit param ./GLOBALS
GGSCI (ggnode2.oracle.com) 4> edit param mgr

[oracle@ggnode2 12g]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Sep 25 2013 02:33:54

GGSCI (ggnode2.oracle.com) 1> dblogin userid ogguser, password oracle
Successfully logged into database.

GGSCI (ggnode2.oracle.com) 2> add checkpointtable
No checkpoint table specified. Using GLOBALS specification (GGS_CHECKPOINT)...
Successfully created checkpoint table GGS_CHECKPOINT.

GGSCI (ggnode2.oracle.com) 3> add replicat rscott, exttrail ./dirdat/pa
REPLICAT added.

GGSCI (ggnode2.oracle.com) 4> edit param rscott
GGSCI (ggnode2.oracle.com) 3> view param mgr

PORT 15000
DYNAMICPORTLIST 15510-15520
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS

GGSCI (ggnode2.oracle.com) 4> view param rscott

replicat rscott
userid ogguser, password oracle
assumetargetdefs
ddloptions report
discardfile ./dirout/rscott.dsc, purge
map scott.*, target scott.*;

GGSCI (ggnode2.oracle.com) 5> start mgr
Manager started.

GGSCI (ggnode2.oracle.com) 6> start er *
Sending START request to MANAGER ...
REPLICAT RSCOTT starting

GGSCI (ggnode2.oracle.com) 10> info all

Program  Status  Group  Lag at Chkpt Time Since Chkpt
MANAGER  RUNNING
REPLICAT RUNNING RSCOTT 00:00:00     00:00:08

GGSCI (ggnode2.oracle.com) 11> exit

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

[oracle@ggnode1 ~]$ sqlplus /nolog
SQL*Plus: Release 12.1.0.1.0 Production on Mon May 25 15:33:02 2015

SQL> connect scott/oracle@orcl
Connected.

SQL> CREATE TABLE customers (
customers_id          number(10) NOT NULL,
customers_gender      char(1),
customers_firstname   varchar2(255) NOT NULL,
customers_lastname    varchar2(255) NOT NULL,
PRIMARY KEY (customers_id)
using index
);
Table created.

SQL> select tname from tab;
TNAME
--------------------------------------------------------------------------------
CUSTOMERS

[oracle@ggnode2 ~]$ source 12c.env
[oracle@ggnode2 ~]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.1.0 Production on Mon May 25 15:32:44 2015
SQL> connect scott/oracle@orcldb
Connected.

SQL> select tname from tab;
TNAME
--------------------------------------------------------------------------------
CUSTOMERS

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.

[oracle@ggnode1 12g]$ pwd
/u01/app/ogg/12g

[oracle@ggnode1 12g]$ ./keygen 128 4
0xE38B47407385780AA8B9071260F3C41F
0x21EC1C4D4F3F650FE0114C0334C2705B
0xB5A1F759B915394D6DF803298D2D7C79
0x4957D26624EC0C0BFADEBB4EE7988717

[oracle@ggnode1 12g]$ touch ENCKEYS
[oracle@ggnode1 12g]$ vi ENCKEYS

[oracle@ggnode1 12g]$ cat ENCKEYS
key128_1        0xE38B47407385780AA8B9071260F3C41F
key128_2        0x21EC1C4D4F3F650FE0114C0334C2705B
key128_3        0xB5A1F759B915394D6DF803298D2D7C79
key128_4        0x4957D26624EC0C0BFADEBB4EE7988717

[oracle@ggnode1 12g]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Sep 25 2013 02:33:54

GGSCI (ggnode1.oracle.com) 1> encrypt password oracle aes128 encryptkey key128_1
Encrypted password: AADAAAAAAAAAAAGAZEXHNGFAXBSGHJJGVAIIQFPBCJLJTGZHGDNFXHNJXGQDPHJEWBEEPJFHSDPIEDCC
Algorithm used: AES128

GGSCI (ggnode1.oracle.com) 2> dblogin userid ogguser, password AADAAAAAAAAAAAGAZEXHNGFAXBSGHJJGVAIIQFPBCJLJTGZHGDNFXHNJXGQDPHJEWBEEPJFHSDPIEDCC, encryptkey key128_1
Successfully logged into database.

GGSCI (ggnode1.oracle.com) 3> view param escott

extract escott
exttrail ./dirdat/ea
userid ogguser, password oracle
ddl include all
ddloptions report
statoptions resetreportstats
tranlogoptions excludeuser ogguser
table scott.*;

GGSCI (ggnode1.oracle.com) 4> edit param escott
GGSCI (ggnode1.oracle.com) 5> view param escott

extract escott
exttrail ./dirdat/ea
userid ogguser, password AADAAAAAAAAAAAGAZEXHNGFAXBSGHJJGVAIIQFPBCJLJTGZHGDNFXHNJXGQDPHJEWBEEPJFHSDPIEDCC, encryptkey key128_1
ddl include all
ddloptions report
statoptions resetreportstats
tranlogoptions excludeuser ogguser
table scott.*;

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

[oracle@ggnode1 12g]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle:

Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Sep 25 2013 02:33:54

GGSCI (ggnode1.oracle.com) 1> dblogin userid ogguser, password AADAAAAAAAAAAAGAZEXHNGFAXBSGHJJGVAIIQFPBCJLJTGZHGDNFXHNJXGQDPHJEWBEEPJFHSDPIEDCC, encryptkey key128_1
Successfully logged into database.

GGSCI (ggnode1.oracle.com) 2> info all

Program Status  Group  Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ESCOTT 00:00:05     00:00:01
EXTRACT RUNNING PSCOTT 00:00:00     00:00:02

Stop the Manager Processes and Extract Processes and start both:

GGSCI (ggnode1.oracle.com) 3> stop mgr
GGSCI (ggnode1.oracle.com) 4> stop er *
GGSCI (ggnode1.oracle.com) 5> start mgr
GGSCI (ggnode1.oracle.com) 6> start er *

GGSCI (ggnode1.oracle.com) 7> info all

Program Status   Group  Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STARTING ESCOTT 00:00:05     00:00:09
EXTRACT RUNNING  PSCOTT 00:00:00     00:00:08

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

Log into the source database (orcl):

SQL> connect scott/oracle@orcl
Connected.

SQL> select * from dept;
DEPTNO     DNAME          LOC
---------- -------------- -------------
    10     ACCOUNTING     NEW YORK
    20     RESEARCH       DALLAS
    30     SALES          CHICAGO
    40     OPERATIONS     BOSTON

SQL> insert into dept values (50, 'MARKETING','CALIFORNIA');
1 row created.

SQL> commit;
Commit complete.

SQL> select * from dept;
DEPTNO     DNAME          LOC
---------- -------------- -------------
    10     ACCOUNTING     NEW YORK
    20     RESEARCH       DALLAS
    30     SALES          CHICAGO
    40     OPERATIONS     BOSTON
    50     MARKETING      CALIFORNIA

Log into the target database (orcldb):

SQL> connect scott/oracle@orcldb
Connected.

SQL> select * from dept;
DEPTNO     DNAME          LOC
---------- -------------- -------------
    10     ACCOUNTING     NEW YORK
    20     RESEARCH       DALLAS
    30     SALES          CHICAGO
    40     OPERATIONS     BOSTON
    50     MARKETING      CALIFORNIA

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.

Tags: , , ,