GoldenGate Tools and Utilities

let’s take a quick look at some of the GoldenGate tools and utilities. The following sections provide a brief overview of the tools and utilities.

GGSCI

GGSCI is the GoldenGate Software Command Interface. GGSCI is delivered as part of the GoldenGate software and can be started from the software installation directory using the ggsci command. From the GGSCI command prompt, you can enter commands to manage all parts of the GoldenGate environment including Extracts, Replicats, trails, the Manager, and so on. The Help command is built into GGSCI to give information about each command and its syntax.

DEFGEN

You can use the GoldenGate DEFGEN utility to generate GoldenGate table-definition files when your source and target tables are different (if the source and target tables are identical, it isn’t necessary to run DEFGEN). You can also use DEFGEN to generate a data-definitions file for a data-pump Extract that is doing data transformations on a middle-tier server. DEFGEN is included as part of the regular GoldenGate installation and is available in the GoldenGate software-installation directory.

Logdump

You can use the GoldenGate Logdump utility to view records in the GoldenGate trail files. The Logdump utility is part of the regular GoldenGate software installation. Logdump lets you view the unstructured trail data in hex and ASCII format and count the number of records in the trail for debugging purposes. You can also filter the trail for desired records and save them to a new trail for reprocessing if needed.

Reverse

If you ever need to undo database changes, you can use the GoldenGate Reverse utility. Reverse uses a special Extract to capture the before images of changes and then reverses the order of the transactions and writes a new trail file for doing the reversal. A Replicat then processes the special reversed trail to undo database changes. Reverse can be useful for backing out unwanted changes to the database or quickly restoring a test database back to its original state. Reverse is another utility that is included with the GoldenGate software installation.

Veridata

Veridata is an additional product from GoldenGate that you can use to verify that your source and target databases match. For critical business applications, you should always verify that your source and target databases match when doing replication. If the databases being replicated are used for failover, this is especially important. Data verification is usually time consuming and difficult to accomplish for large amounts of data. Veridata reduces the amount of time verification takes and does high-speed data verification while the databases are online. Results are presented on a web interface. 

Director

Another add-on product available from GoldenGate is Director. Director is a graphical tool that you can use to centrally design, configure, manage, and monitor your entire GoldenGate environment. Director is made up of several components, including a centralized server and database repository, a client application, and a web browser interface. Director can also be integrated with third-party monitoring solutions. Director can manage all the databases and platforms that GoldenGate replication supports.

Installing Oracle GoldenGate 12c (12.2.0.1) for Oracle 12c on Unix Platform

Introduction

Oracle Goldengate provides very fast replication of heterogeneous databases by reading transaction logs and writing the changes to one or more target databases.

Prior to Oracle Golden Gate 12c, the installation was simple. Just copy the zip/tar file to the directory you want to install it in and unzip/untar the file. Then from the GGSCI prompt run CREATE SUBDIRS.

With the release of Oracle Golden Gate 12c, Oracle has finally created a Oracle Universal Installer (OUI) for Oracle Golden Gate. The OUI finally brings the installation of Oracle Golden Gate into the same java interface as all the other Oracle products. In this post, lets take a look at how to properly install the Oracle GoldenGate 12.2.0.1 software on linux.

GoldenGate software is available on OTN. Click on OTN GoldenGate Download 

Click on accept license agreement and then download the below Goldengate version for Linux.

Oracle GoldenGate V12.2.0.1.1 for Oracle on Linux x86-64 (454 MB)

The Oracle Goldengate software bundle is delivered as zip file (fbo_ggs_Linux_x64_shiphome.zip). 

You must expand the zip file using the unzip command before you can install the software.

Unzip the Oracle GoldenGate software bundle:

$ unzip fbo_ggs_Linux_x64_shiphome.zip

After unzip you will get given below files.

Go to fbo_ggs_Linux_x64_shiphome/Disk1 directory 

Run the run oui by writing the below command

$ ./runInstaller

Select your database version. In my case, it is 12c. And press Next.

Specify Oracle Goldengate software location and database location with manager port number.And press Next.

Press Next.

Press Next.

Press Next.

Before start GoldenGate Configuration please consider the following points.

1. Database should be running on archive log mode.
2. We should have Primary key or unique key on table.
3. Recycle bin feature should be turned off.
4. Supplemental log should be enabled.

Check your supplement log data and force logging is enabled if not then follow below steps:

SQL> alter database add supplemental log data;

Database altered.

SQL> alter database force logging;

Database altered.

SQL> select supplemental_log_data_min, force_logging from v$database;

SUPPLEME FORCE_LOGGING

-------- ---------------------------------------

YES      YES

SQL> alter system set recyclebin=off deferred;

SQL> purge dba_recyclebin;

We then need to create a database user which will be used by the Goldengate Manager, Extract and Replicat processes. We can create individual users for each process or configure just a common user – in our case we will create the one user GGUSER and grant it the required privileges.

SQL> CREATE TABLESPACE TS_GG DATAFILE '/app/oracle/oradata/orcl/orcl_gg_01.dbf' SIZE 1G LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;

SQL> CREATE USER gguser IDENTIFIED BY gguser DEFAULT TABLESPACE TS_GG TEMPORARY TABLESPACE TEMP; 

SQL> GRANT CONNECT, RESOURCE, DBA TO gguser;

SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=true SCOPE=BOTH; 

ADD SCHEMATRANDATA to enable schema-level supplemental logging for a table. ADD SCHEMATRANDATA acts on all of the current and future tables in a given schema to automatically log a superset of available keys that Oracle GoldenGate needs for row identification.

ggsci> ADD SCHEMATRANDATA <schema_name>

 Create the wallet for password security.

ggsci> Create Wallet

ggsci> Add CredentialStore

ggsci> Alter CredentialStore Add User gguser Password gguser Alias gguser

To grant the privileges needed by a user to be an administrator for OGG Integration with XStreamOut

exec dbms_goldengate_auth.grant_admin_privilege('GGUSER');

grant insert on system.logmnr_restart_ckpt$ to GGUSER;

grant update on sys.streams$_capture_process to GGUSER;

grant become user to GGUSER;

ORACLE GOLDENGATE ARCHITECTURE

Oracle Golden Gate is a tool which supports the replication of data across various heterogeneous platforms like Oracle, DB2, SQL Server, MySQL etc.

It supports different topologies shown in the Figure 1 which can be used to fulfill various data transfer requirements using data replication.

 Figure 1 Oracle Golden Gate Supported Topologies.

• Uni-directional: Data is replicated in one direction from source to target.
• Bi-Directional: The data flows in both direction and stays synced up between Source and Target sites. 
• Peer to Peer: Similar to Bi-directional but involves more than 2 databases which stay synced up.
• Broadcast: Data from source is sent to multiple destinations.
• Consolidation: Data from multiple sources is delivered to one destination DB.
• Cascading: Data from one source is sent to multiple destinations.

Oracle Golden Gate Architecture

 Figure 2 Oracle Golden Gate Architecture

Oracle Golden Gate Architecture is composed of the following Components:

1. Manager.
2. Extract.
3. Data pump.
4. Replicat.
5. Trails or extract.
6. Checkpoints.
7. Collector.

Manager

•   The Manager process must be running on both the sides (source as well as target systems).

•      It performs a number of functions including 
• Monitoring and starting other Golden Gate processes. 
•  Manages the disk space by purging the old trail files and also reporting.

•     Only one Manager Process is required for every Golden gate installation.

 Extract

•  The Extract process is the data capturing mechanism of Golden Gate.
•  It is responsible for capturing the committed DML transactions and the DDL from Oracle Redo logs.
•  Then Extract writes these data changes into Trail or Extract Files. 

Data Pumps

• Data Pumps are secondary extract mechanisms which exist in the source configuration.
• When Data Pump is configured,
• The Primary Extract process will write to the Local Trail and then this trail is read by the Data Pump and data is sent over the network to Remote Trails on the target system.

• Data Pump can also be useful in the following cases,
• When we are doing complex filtering and transformation of data.
• When we are consolidating data from many sources to a central target.

Replicat

• The Replicat process is the apply process in the Golden gate configuration.
• The Replicat process runs on the target system and reads transactional data changes as well as DDL changes and replicates then to the target database.
• Like the Extract process, the Replicat process can also be configured for Initial Load as well as Change Synchronization.

Trails

• Trails are series of files that Golden Gate temporarily stores on disks and these files are written to and read from by the Extract and Replicat processes.
• Depending on the configuration chosen, these trail files can exist on the source as well as on the target systems. If it exists on the local system, it will be known as an Extract Trail or if it exists on the target system it will be known as a Remote Trail.

Checkpoint

• The Extract Pump & Replicat processes use checkpoints for tracking the progress of these processes.
• This mechanism marks the location up to point where the data changes have been retrieved or applied from the trail files.
• This is useful when processes need to recover (without any data loss) or need to know the starting point after a failure.

Collector

• The Collector is a background process which runs on the target system and is started automatically by the Manager (Dynamic Collector) or it can be configured to start manually (Static Collector).
• It receives extracted data changes that are sent via TCP/IP and writes then to the trail files from where they are processed by the Replicat process.