Determine initial load processes in Oracle GoldenGate!

Hi everyone!

One of the interesting features of Oracle GoldenGate is obtaining the information of any Extract or Replicat process used during the initial load. An initial load process involves extracting the records of a table from a source database to a table in the target database. After the data is synchronized, one can configure processes to capture changes, pump the data to target and finally replicate the data.

In scenarios where GoldenGate is already configured for data replication, we need to obtain the information about the extract and the replicat process used for initial data load. Through this post, we will learn how to obtain the same using a few GGSCI commands.

Pre-requisites: Oracle GoldenGate 11g for Windows (64 bit)

Step 1: Open the GGSCI interface


Step 2: Create an extract named ext1 as follows!

USERID ggs_owner, PASSWORD ggs_owner
TABLE ggs_owner.TEST1;

As seen above, we have used the parameter SOURCEISTABLE that extracts all column data specified within TABLE statement. This parameter is applied to initial load methods such as loading data from file to Replicat and loading data from file to database utility. When used, SOURCEISTABLE must be the first parameter in the Extract file.

Alternatively, one can specify the same as an argument to the ADD EXTRACT command.

Step 3: Create a replicat named rep6 as follows!

USERID ggs_owner, PASSWORD ggs_owner
MAP ggs_owner.*, TARGET ggs_target.*;

As seen above, we have added the SPECIALRUN parameter in the replicat file. It is used for certain initial loads and ensures that the replicat is started automatically instead of manually using the START REPLICAT command.


Step 4: Start the manager process


Step 5: View information about processes

Now, one can view the information about the processes used for initial load using the following command!

info all tasks


Credits: This post wouldn’t have been possible without the support and help of Mr. Pankaj Singh (Oracle DBA)

Tutorial #51: Install Oracle GoldenGate Director Server and Client

Hey everyone!

Apart from the standard GoldenGate Software Command Line Interface (GGSCI) tool, Oracle provides a Director client and a Director server under the management pack for GoldenGate.

In this tutorial, we will learn how to install and configure Oracle GoldenGate director client and server!

Pre-requisites: Windows XP Service Pack 2 (32 bit), JDK 1.6.0_20 or later, Microsoft Visual C++ 2005 SP1 Redistributable, Oracle weblogic server 10.3.3 or later

Step 1: Download & install Director server and client for GoldenGate

Download the latest version of the GoldenGate Director server and client depending upon your system requirements from over here. Make sure you download and install the GoldenGate Monitor tool as well.


Install all three components namely client, server and monitor successfully on your system. While installing the director server if you get an error like unable to run-cdc-tool script file, then examine the contents of the log file. Check the JDBC connection details and once again try installing.

Step 2: Start the Director server

Go to Start –> Programs –> Oracle GoldenGate Director Server and start the director server. A new command prompt instance should automatically open and the server should be in running mode!


Step 3: Using Director client to configure data source

Open the Director client Administrator tool. By default the username and password are both admin. While installing the director server I had specified the port as 7004. My login screen looks like this:


Once logged in, you can change the admin account password or keep it as it is. You can also add/configure a new data source and test it’s connection as follows!


Switch to the Monitor Agent tab and click on the Start Selected Monitor button.


Finally, launch the Oracle GoldenGate Director Client tool. Run the ggsci command info manager. You should see the manager running on the specified port! 🙂


This completes the tutorial. We have successfully installed and configured GoldenGate Director Client and Server. The Director Web console can also be seen by entering http://localhost:7004/acon in your browser. It should show you the following page!


Source: Oracle GoldenGate Director (Server,Client,Web) installation

Tutorial #48: Execute stored procedure using SQLEXEC in Oracle GoldenGate

Hi everyone!

It’s time for another interesting GoldenGate tutorial. In GoldenGate, the SQLEXEC parameter can be used with Extract or Replicat processes to connect to the database via stored procedures, SQL queries and DML operations.

In this tutorial, I will explain how to use SQLEXEC to execute a stored procedure in Oracle GoldenGate!

Pre-requisites: Oracle GoldenGate 11g (for 64 bit Windows), Oracle database 11gR2 Enterprise Edition

Step 1: Create tables in Oracle

Create the following tables in your Oracle database. I have created these tables under SYSTEM schema!

create table creditcard_accounts(account_code int primary key, account_id int, account_name varchar(10));

create table new_account(new_account_id int primary key, new_account_name varchar(10));

At present, do not insert rows in any of these tables. We will perform insertion during the replication process!

Step 2: Create stored procedure

Now, create a simple stored procedure as follows. Make sure the schema in which you have created this procedure (in my case SYSTEM) has execute and select privileges on the stored procedure. In some cases, you might even consider granting DBA rights to the schema.

create or replace procedure lookup_account(acc_code in int,acc_name out varchar)
select account_name into acc_name from creditcard_accounts where account_code=acc_code;

Step 3: Create Extract and Replicat processes

Open the GGSCI interface. Create an Extract and a Replicat process containing the SQLEXEC parameter as follows!

Create Extract

extract extl
userid ggs_owner, password ggs_owner
rmthost , mgrport 7809
rmttrail ./dirdat/kk
table system.creditcard_accounts;

Create Replicat

USERID ggs_owner, PASSWORD ggs_owner
SOURCEDEFS ./dirsql/tables_credit.sql 
MAP system.creditcard_accounts, TARGET system.new_account,& 
PARAMS (acc_code = ACCOUNT_CODE)), & 

Step 4: Create definitions file

Since both the table structures are different, I am going to create a definitions file that can be used by the Replicat when specified using the SOURCEDEFS parameter.

Create definitions file

# On the SOURCE
ggsci> edit params defgen
defsfile ./dirsql/tables_credit.sql
userid ggs_owner password ggs_owner

Using the DEFGEN utility generate the definition file.

C:\gg_src\defgen paramfile ./dirprm/defgen.prm

Step 5: Start Extract, Replicat and Manager process

Start all the processes. Check whether all the processes are running using the info command. Now, try inserting a new record in the creditcard_accounts table. If all processes are running successfully, the stored procedure gets executed and populates the the new_account table. You can verify the same by performing a select operation!





GG nightout #1: Send SMS automatically using Oracle GoldenGate

Hi, folks!

My name is Daniel Reymour. Since the past three years, I have been working on Oracle technologies/products including Oracle Fusion Middleware, Oracle Secure Enterprise Search and OEM. I am one of those developers trapped inside a sysadmin’s body, a FOSS evangelist, an avid reader and a professional guitarist! 🙂

Sending an SMS automatically is pretty similar to Sending An E-mail. We only need to modify the script contained within the batch file!

However, in order to generate the batch file we would be using Selenium, a popular application used for automating web applications for testing purposes. With Selenium configured, all we need is an online SMS sending portal to send our messages.

Our replicat file, just as mentioned in the email tutorial would be as follows; the only change includes adding the sendsms.bat file.

Create Replicat

GGSCI (jatin-PC) 30> add replicat replocal exttrail ./dirdat/aa
GGSCI (jatin-PC) 31> edit params replocal
--add following lines in the notepad file
REPLICAT replocal
USERID ggs_owner, PASSWORD ggs_owner
MAP system.same_host_src, TARGET ggs_owner.same_host_src, FILTER (@STREQ (EMPCITY, "pune" )), EVENTACTIONS ( IGNORE TRANS , SHELL "C:\programs\sendsms.bat");

To test this, let’s create a Java Project named GoldenGateSMS with package name com.example. Create a new class called

//all necessary imports go over here

public class TestSMS
    private Selenium selenium;

    public void setUp() throws Exception
	WebDriver driver = new FirefoxDriver();
	String baseUrl = "";
	selenium = new WebDriverBackedSelenium(driver, baseUrl);

    public void testSMS() throws Exception
	selenium.type("id=password", yourpassword);
	selenium.type("id=username", yourusername);"name=Login");
	selenium.type("css=#textArea", "Test Message from Oracle GoldenGate!");
	selenium.type("css=#textfield2 + input", yournumber);

   public void tearDown() throws Exception 


Export the java project as a .jar and call it within the batch file. For testing purposes, you can run the .jar file via command prompt as shown below. When the file runs, Selenium takes over and at the end you should receive an SMS on the number that you had mentioned.



Voila! We are done over here. BTW, this isn’t my personal space and I would like to thank Karan, friend and fellow GAD for giving me this opportunity! 🙂

Determine ports on which Extract and Replicat process run in GoldenGate


Today a friend of mine asked me how to determine on which port the Extract and the Replicat process run in Oracle GoldenGate. I was quick to respond with the nmap (network mapper) tool since I have been using it on my Linux machine for a long time now!

However, since most aren’t quite familiar with the tool, I decided to go with a Windows command called netstat. This command displays active TCP connections, ports on which the computer is listening, Ethernet statistics, IP routing table, IPv4 statistics (for the IP, ICMP, TCP, and UDP protocols), and IPv6 statistics (for the IPv6, ICMPv6, TCP over IPv6, and UDP over IPv6 protocols)

Let’s first start our GoldenGate Software Command Line Interface (GGSCI) by writing the following command!

Navigate to GoldenGate home (C:\gg_src) and type:


Now start the Manager process and obtain it’s information using the info command!

start manager
info manager


So this confirms that the Manager process is running on port number 7809.

Now, start any of your Extracts or Replicats as follows!


While all these processes are running, open an instance of the Windows Command Prompt (in Administrator mode) and type the following netstat command.

netstat -abno


-a Displays all connections and listening ports.

-b Displays the executable involved in creating each connection or listening port. In some cases well-known executables host multiple independent components, and in these cases the sequence of components involved in creating the connection or listening port is displayed. In this case the executable name is in [] at the bottom, on top is the component it called, and so forth until TCP/IP was reached. Note that this option can be time-consuming and will fail unless you have sufficient permissions.

-n Displays addresses and port numbers in numerical form.

-o Displays the owning process ID associated with each connection.


Once you run the netstat command search for the extract.exe and the replicat.exe files. Note down the ports on which they are running. The Extract will correspond to the port mentioned for extract.exe and the Replicat will correspond to the port mentioned for replicat.exe.

As seen above the Extract is running on port 7840 and Replicat is running on port 7841.

Tutorial #47: Send E-mail automatically using GoldenGate

Hello everyone!

Today’s post is a bit complex but at the same time interesting for all those familiar with Oracle GoldenGate. In this tutorial, you will learn how to send an e-mail once a particular transaction (insert/delete/update) takes place at the source side.

Pre-requisites: Oracle GoldenGate for Windows (64 bit), Oracle database 11gR2 Enterprise Edition with *SQLPlus, Eclipse IDE

1. Java Part

Let’s first write our email code using Java. Create a new Java console application project named SendMail in Eclipse. I will be using the JavaMail API by adding the latest version of the mail.jar file to my project. Let the package name be com.example and class name SendMailTLS.

package com.example;

import java.util.Properties;
import javax.activation.DataHandler;
import javax.activation.DataSource;
import javax.activation.FileDataSource;
import javax.mail.BodyPart;
import javax.mail.Message;
import javax.mail.MessagingException;
import javax.mail.Multipart;
import javax.mail.PasswordAuthentication;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;
public class SendMailTLS 
   public static void main(String[] args) 
     final String username = "";
     final String password = "yourgmailpassword";
     Properties props = new Properties();
     props.put("mail.smtp.auth", "true");
     props.put("mail.smtp.starttls.enable", "true");
     props.put("", "");
     props.put("mail.smtp.port", "587");
     Session session = Session.getInstance(props,
	  new javax.mail.Authenticator() {
	  protected PasswordAuthentication getPasswordAuthentication() {
	  return new PasswordAuthentication(username, password);
	    MimeMessage message = new MimeMessage(session);
	    message.setFrom(new InternetAddress(username));
        message.addRecipient(Message.RecipientType.TO,new InternetAddress(""));
	    message.setSubject("Mail from GoldenGate");
	    //set your message content    
	    BodyPart messageBodyPart1 = new MimeBodyPart();
	    messageBodyPart1.setText("A transaction was successfully completed!!");
	    //set DataHandler object to this object    
	    MimeBodyPart messageBodyPart2 = new MimeBodyPart();

        //change path to filename as per your needs
        //adding attachments to mail
	    String filename = "C:\\programs\\result.txt";
	    DataSource source = new FileDataSource(filename);
	    messageBodyPart2.setDataHandler(new DataHandler(source));
	    //add MimeBodyPart objects to this object    
	    Multipart multipart = new MimeMultipart();

	    //set the multiplart object to the message object
	    message.setContent(multipart );


      catch (MessagingException e) 
	     throw new RuntimeException(e);

Note: Make sure you add the latest version of mail.jar by right clicking your Project –> Properties –> Java Build Path –> Add External Jars.

Export the Project as a Runnable Jar file to a particular directory and click Finish.


2. GoldenGate part

In GoldenGate, I will be performing replication with source and target on the same physical host. Open *SQLPlus and create the following table in both the source and the target schema.

Source schema: SYSTEM
Table name: same_host_src

create table same_host_src(empid int primary key, empname varchar(10), empcity varchar(10));

Do not insert any rows in the source table now since we would be doing the same during the replication process!

Target schema: GGS_OWNER
Table name: same_host_src

create table same_host_src(empid int primary key, empname varchar(10), empcity varchar(10));

Now as per the replication process, we need to create a Extract and the corresponding Replicat process. Open the GGSCI command line interface and write the following commands!

Creating Extract

GGSCI (jatin-PC) 25> dblogin userid ggs_owner, password ggs_owner

GGSCI (jatin-PC) 26> add trandata system.same_host_src

GGSCI (jatin-PC) 27> add extract extlocal tranlog begin now

GGSCI (jatin-PC) 28> add rmttrail ./dirdat/aa extract extlocal

GGSCI (jatin-PC) 29> edit params extlocal

--add the following lines in the notepad file
extract extlocal
userid ggs_owner, password ggs_owner
rmthost, mgrport 7809
rmttrail ./dirdat/aa
table system.same_host_src;

Create Replicat

GGSCI (jatin-PC) 30> add replicat replocal exttrail ./dirdat/aa

GGSCI (jatin-PC) 31> edit params replocal

--add following lines in the notepad file
REPLICAT replocal
USERID ggs_owner, PASSWORD ggs_owner
MAP system.same_host_src, TARGET ggs_owner.same_host_src, FILTER (@STREQ (EMPCITY, "pune" )), EVENTACTIONS ( IGNORE TRANS , SHELL "C:\programs\mailsend.bat");


As you can see above, we have added a FILTER to the replicat file. It is used to specify the event which is to look for the string “pune” in the column name EMPCITY of the table SAME_HOST_SRC.

When this event occurs, the EVENTACTIONS clause specifies now what to do which is:

1. Ignore the transaction and do not replicate that insert into the table SAME_HOST_SRC on the target side.

2. Run the Windows batch file namely mailsend.bat on the target server since replicat process runs on target.

Run Manager, Extract and Replicat process

GGSCI (jatin-PC) 32> start manager

GGSCI (jatin-PC) 32> start extlocal

GGSCI (jatin-PC) 33> start replocal


Now, insert a new record in your source table. For our mail event to occur, I will purposely insert a record with EMPCITY as “pune” so that the batch file is called and the mail is sent to our recipient!


insert into same_host_src values(109,'nitesh','pune');


Check Extract statistics:

GGSCI (jatin-PC) 35> stats extlocal


Wait for some time. Log in to the Gmail account you have specified in the above Java code. You should see a new mail in your Inbox! 🙂 The mail should also contain the attachment file that includes the report of the transaction generated by GoldenGate.


The batch file plays a very important role here. It contains the code for calling the SendMail.jar file which in turn sends the email. More information can be obtained from over here.

This completes the tutorial. Hope it helps. Have a nice day! 🙂

Source: GoldenGate replication on same physical host

Tutorial #46: Configuring Oracle database for using GoldenGate


In my previous tutorial on Oracle GoldenGate, we saw how to install the GoldenGate software on a Windows 7 machine. However, in order to start using GoldenGate we need to first configure or prepare the Oracle database.

Over here I will demonstrate how to set up your Oracle database for getting started with GoldenGate. This would involve configuring both the source and target databases. In this case, we assume that both source and target are Oracle databases.

Pre-requisites: Oracle database 11g Enterprise Edition with *SQLPlus, Windows 7 (64 bit) Operating System

Step 1: Create a new schema for GoldenGate

Open *SQLPlus and Connect to your database instance as sysdba (system database administrator). In case you are not able to log in as sysdba, please refer this link. Once connected, execute the following commands in the given order!

1. create tablespace ggs_data
   datafile 'D:\app\your-path-to-ORADATA\orcl\ggs_data.dbf' size 200m
   autoextend on next 10m maxsize 4g;

2. create user ggs_owner identified by g
   default tablespace ggs_data
   temporary tablespace temp;

3. grant connect, resource to ggs_owner;

4. grant select any dictionary, select any table to ggs_owner;

5. grant create table to ggs_owner;

6. grant flashback any table to ggs_owner;

7. grant execute on dbms_flashback to ggs_owner;

8. grant execute on utl_file to ggs_owner;

9. grant create any table to ggs_owner;

10. grant insert any table to ggs_owner;

11. grant update any table to ggs_owner;

12. grant delete any table to ggs_owner;

13. grant drop any table to ggs_owner;

Step 2: Enable ARCHIVELOG mode for Oracle database

GoldenGate requires the Oracle database to be in ARCHIVELOG mode. When you enable this mode redo logs will be archived instead of overwritten. The archive logs are stored in a separate place usually can backed up regularly by your standard filesystem backup system (NetBackup or whatever).

-- to check it the database is running in archivelog mode:
select log_mode from v$database;

-- to make it running in archivelog mode:
shutdown immediate startup mount
alter database archivelog;

Step 3: Verify database-level supplemental logging

When the supplemental logging is active on a database, the redo logs contain other columns from tables to uniquely identify a row. We need to verify supplemental logging at the source database by writing the following SQL command!

-- it must be YES or IMPLICIT

-- to enable it:

Step 4: Add DDL replication support

Goldengate supports the replication of DDL commands, operating at a schema level, from one database to another. By default the DDL replication is disabled on the source database (extract side) but is enabled on the target Database (replicat side). The commands below are used to activate the DDL replication.

However, it is best suited to carry out DDL replication on both source and target databases as follows!

Create a file named as GLOBALS in the GoldenGate directory, if one does not exist!

C:\gg_src>notepad GLOBALS

Add the following into the GLOBALS file:

GGSCHEMA ggs_owner

Log in to *SQLPlus as sysdba by navigating to the gg_src folder!

C:\ggs_src>sqlplus /nolog

SQL*Plus: Release Production on Mon Jul 29 22:34:15 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> conn / as sysdba

Run the below SQL scripts

Note: When asked for schema name enter ggs_owner

SQL> @marker_setup.sql

SQL> @ddl_setup.sql

SQL> @role_setup.sql

Grant the created role (default name is GGS_GGSUSER_ROLE) to all Oracle GoldenGate Extract users.


Run the ddl_enable.sql script to enable the DDL trigger.

SQL> @ddl_enable.sql

Finally, make sure you commit all the changes made so far. You have now successfully configured the Oracle database for using GoldenGate. In the upcoming tutorials we will see how to perform initial load, unidirectional replication, bidirectional replication etc using Extract, Replicat processes and data pumps. Till then enjoy! 🙂

Source: Oracle GoldenGate Hands On Tutorial

References: Supplemental Logging, DDL replication

Tutorial #45: Installing GoldenGate on Windows

Hello everyone!

Oracle GoldenGate (GG) is a well known replication software that provides real time, log based change data capture and delivery between heterogeneous systems. It acts as an ETL tool that provides high availability and disaster tolerance due to which business critical applications can operate without any disruption.

The GoldenGate product suite includes the following!

1. Oracle GoldenGate

2. GoldenGate Director is a multi-tiered client-server application that enables the configuration and management of GoldenGate instances from a remote client (web based interface).

3. Oracle GoldenGate Veridata is an online high-speed data comparison solution that identifies and reports data discrepancies between databases and sets of data.

In this tutorial, I will explain how to download and configure GoldenGate on a Windows machine!

Pre-requisites: Windows 7 (64 bit) Operation System

Step 1: Download Microsoft Visual C++ 2005 SP1 and GoldenGate

First, you need to download the Microsoft Visual C++ 2005 SP1 Redistributable for Windows 7 (x64). Download appropriate version of GoldenGate from the Oracle website. If you working on an Oracle 11g database then you need to download the GoldenGate Oracle 11g 64 bit package for Windows. Extract the downloaded rar file to get the GG folder.

Step 2: Create new directory for GG software

Create a new directory called gg_src

C:\mkdir gg_src

Note: Do not install GoldenGate into a folder that contains spaces in its name.

Copy all the files inside the extracted GG folder to gg_src. Now, open a Command Prompt window and navigate to the folder gg_src and type the ggsci command.


GoldenGate should now open up. Write the command CREATE SUBDIRS to create the necessary sub-directories to be used by GoldenGate.


Step 3: Add and install Manager service

Inside the ggsci window, type edit params mgr. It will create a new Notepad file. Add the following lines to it!

port 7809
autostart ER *

Note: You can use port number as 7840 also. The autostart ER parameter indicates all Extract and Replicat processes to start automatically as soon as the Manager is started. PURGEOLDEXTRACTS is used to specify the minimum number of days for storing trail files. You can read about Extract, Replicat processes and trail files by downloading the latest GoldenGate documentation from over here.

Now, open another instance of Command Prompt, navigate to the gg_src folder and type the following command!


The Manager service is now successfully installed. To verify the same try starting the manager from the ggsci window as follows!


So that’s it for this tutorial. In the next tutorial, we will learn how to configure or prepare the Oracle database for using GoldenGate. Have a nice day! 🙂