Oracle GoldenGate on Linux: Bidirectional Replication from Oracle 11gR2 to Oracle 11gR2

Overview

Purpose

This Oracle-By-Example (OBE) tutorial covers installing, configuring, and managing Oracle GoldenGate version 11.2.1.0.2 on a pair of Linux Oracle 11gR2 databases.


Time to Complete

Approximately 3 hours


Introduction

Oracle GoldenGate provides very fast replication of heterogeneous databases by reading transaction logs and writing the changes to one or more target databases. There are five processes involved in a typical environment:


Manager:

Starts and stops the other processes on both the source and target hosts. Not required once Extract or Replicat is running.

Initial Load:

Optional. Used to populate the target tables one time. It can read either from the source tables directly or from ASCII files. This OBE will not use an Initial Load.

Extract:

Runs on the source to capture transaction data to trail files. There are two versions of Extract: Classic (this OBE) and Integrated.

Data Pump:

Optional, but highly recommended. Sends trail files from the source to the target over an IP network. Technically it is a secondary Extract.

Replicat:

Delivers data to the target database. Normally the Replicat runs on the target.

Scenario

There are two Linux hosts running Oracle 11gR2: host01 (red) and host02 (green). Host01 has a user/schema ogguser1 with a password of pswd1u, and an administrator with a user/schema oggadm1 with a password of pswd1a.
Host02 has a user/schema ogguser2 with a password of pswd2u, and an administrator with a user/schema oggadm2 with a password of pswd2a. There are tables on host01 (TCUSTMER and TCUSTORD) that need to be replicated to host02. Pay attention to the color of the screen banners to know which commands are going to which host. Also note whether you are entering GGSCI commands, SQL commands, or OS commands (the prompt will guide you).
Once the unidirectional replication is working, there is an optional desire to go bidirectional. That requires an additional instance of the Oracle GoldenGate software (a second install directory).

  In summary, the environment is:


Host Name	Color	OS	SID	User	Password	Port	Install Directory
host01	Red	Linux 64-bit	orcl01	ogguser1 oggadm1	pswd1u  pswd1a	15001	/u01/app/oracle/ogg01
host02	Green	Linux 64-bit	orcl02	ogguser2 oggadm2	pswd2u  pswd2a	15002	/u01/app/oracle/ogg02

Hardware and Software Requirements

The following is a list of hardware and software requirements:


Hardware

• Two Linux hosts: one source and one target. The example uses Oracle Enterprise Linux OEL 5.7, 64-bit. A different version of the Oracle GoldenGate software will run on Windows, or 32-bit Linux as well. It is possible to have the source and target be on the same host, but that is conceptually harder to visualize what is happening. If you do have only one host, it may be helpful to change the /etc/hosts file to make aliases for host01.example.com and host02.example.com.

Software

• Oracle GoldenGate on Oracle, Linux-64 ( Oracle Software Delivery Cloud ), version 11.2.1.0.2, part number V34339-01. This is the part number for 64-bit Linux.
• Oracle 11gR2 Database installed on both hosts.

Prerequisites

Before starting this tutorial, you should:

• Have root access to the Linux software (write access to /etc)
• Have web access to download the software and documentation.
• Create a database instance with SID=orcl01 on host01, and a database instance with SID=orcl02 on host02.
• Modify tnsnames.ora on both hosts so that they can talk to each other by SID.
Bear in mind that there are two hosts: host01 and host02; and that each host has three environment prompts: OS, GGSCI, and SQL. That makes six different places in which you could be typing! Try to be extra careful about which command you enter in which location. The wrong command in the wrong context is the most common error.


Typographic Conventions

Text color and font in the directions and in the screens should be interpreted as follows:


Some_Command

You type this as a command or a value. Example:
 Enter ./ggsci to start the command line interpreter. 

Some_Prompt

The system responds with this as a prompt or reply. Example:
 After the welcome splash banner, you can enter commands at the GGSCI (host01) 1> prompt. 

Some_Button

Click this on-screen button. Example:
 After selecting the version you want, click Continue to start the download. 

Some_Variable

A variable that you substitute with a real value. Example:
 Enter your userid/password at the prompt. 

Some_Filename

A filename, path, or folder/directory. Example:
 Edit the hosts file in the /etc directory. 

Some_Code

A keyword or code element. Example:
 Change the parameter HandleCollisions to NoHandleCollisions after the initial load. 

Also be careful about letter "oh" versus number "zero." The SID is orcl01 (leading oh and trailing zero-one).


File and Process Naming Conventions

File and process naming conventions can be whatever works for you or your company. Here are the sample conventions used in this OBE:
  pxxxxhh.ext
where:


p=process=

e(xtract), p(ump), r(eplicat), i(nitial), d(efgen), s(tartup).

xxxx=project=

All files related to a common project xxxx, for example hr, sales, engr.

hh=host-to-host=

aa, ab, ba, bb, as indicated by source and target host names where a=host01, and b=host02. Later on you will see that you cannot use numbers as part of some file names, so better not to go there at all.

ext=extension=

prm=parameter (stored in dirprm/), dsc=discard, rpt=report (stored in dirrpt/), def=definition (stored in dirdef/), oby=obey (stored in installation directory), sql=SQL (stored in dirsql/).

 

1. Installing the Software

The installation of the software is simply fetching the zip files from the web and unzipping them. To install the Oracle GoldenGate software, perform the following steps:

 
1.1 Accessing Oracle GoldenGate Documentation
Access the documentation library.
Using a web browser, go to http://www.oracle.com/technetwork/middleware/goldengate/documentation/index.html. You have the option to either read the documents online, or to download the library to your local workstation as either PDF or HTML format.



Click View Library.



Click Oracle Installation and Setup Guide. Read the Installation Guide for Oracle.

You have successfully accessed Oracle GoldenGate documentation as a prerequisite for installing the software.
 
1.2 Installing Oracle GoldenGate on Linux
Copy the software from Oracle Software Delivery Cloud.
Using a web browser, go to https://edelivery.oracle.com and click Sign In. On the Terms and Conditions page, select Yes for both agreements, and click Continue.
On the Media Pack Search page, select Product Pack = Oracle Fusion Middleware, and Platform = Linux x86-64.

Click Go.
Select Oracle GoldenGate on Oracle v11.2.1 Media Pack for Linux x86-64.

Click Continue.
Make sure you are looking at part number V34339-01 for "Oracle GoldenGate V11.2.1.0.3 for Oracle 11g on Linux x86-64."



Click Download.

Unpack the software in its directory on host01 (red).
Create the installation directory to receive the Oracle GoldenGate software.


Host01 - Linux

[oggadm1@host01 /]$ cd /u01/app/oracle [oggadm1@host01 oracle]$ mkdir ogg01 [oggadm1@host01 ogg01]$ cd ogg01/ [oggadm1@host01 ogg01]$

Copy the downloaded zip from wherever you downloaded it to ogg01/. Unzip it and untar (extract) it.


Host01 - Linux

[oggadm1@host01 ogg01]$ cp /stage/V34339-01.zip . [oggadm1@host01 ogg01]$ unzip V34339-01.zip Archive: V34339-01.zip inflating: fbo_ggs_Linux_x64_ora11g_64bit.tar inflating: Oracle_GoldenGate_11.2.1.0.3_README.doc inflating: Oracle GoldenGate_11.2.1.0.3_README.txt inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.3.pdf [oggadm1@host01 ogg01]$ tar -xvpf fbo_ggs_Linux_x64_ora11g_64bit.tar UserExitExamples/ UserExitExamples/ExitDemo_more_recs/ UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.HPUX ... many lines omitted for clarity ... ulg.sql usrdecs.h zlib.txt [oggadm1@host01 ogg01]$

Be mindful of the trailing dot in the copy command.

Start the GoldenGate Software Command Interface (GGSCI). Create the default empty subdirectories.


Host01 - Linux

[oggadm1@host01 ogg01]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (host01) 1> Create Subdirs Creating subdirectories under current directory /u01/app/oracle/ogg01 Parameter files /u01/app/oracle/ogg01/dirprm: already exists Report files /u01/app/oracle/ogg01/dirrpt: created Checkpoint files /u01/app/oracle/ogg01/dirchk: created Process status files /u01/app/oracle/ogg01/dirpcs: created SQL script files /u01/app/oracle/ogg01/dirsql: created Database definitions files /u01/app/oracle/ogg01/dirdef: created Extract data files /u01/app/oracle/ogg01/dirdat: created Temporary files /u01/app/oracle/ogg01/dirtmp: created Stdout files /u01/app/oracle/ogg01/dirout: created GGSCI (host01) 2> Exit [oggadm1@host01 ogg01]$

If a directory already exists, the installation leaves the contents of that directory alone.

Do the same steps for the other host. Unpack the software in its directory on host02 (green).
Create the installation directory to receive the Oracle GoldenGate software.


Host02 - Linux

[oggadm2@host02 /]$ cd /u01/app/oracle [oggadm2@host02 oracle]$ mkdir ogg02 [oggadm2@host02 ogg02]$ cd ogg02/ [oggadm2@host02 ogg02]$

Copy the downloaded zip from wherever you downloaded it to ogg02/. Unzip it and untar (extract) it.


Host02 - Linux

[oggadm2@host02 ogg02]$ cp /stage/V34339-01.zip . [oggadm2@host02 ogg02]$ unzip V34339-01.zip Archive: V34339-01.zip inflating: fbo_ggs_Linux_x64_ora11g_64bit.tar inflating: Oracle_GoldenGate_11.2.1.0.3_README.doc inflating: Oracle GoldenGate_11.2.1.0.3_README.txt inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.3.pdf [oggadm2@host02 ogg02]$ tar -xvpf fbo_ggs_Linux_x64_ora11g_64bit.tar UserExitExamples/ UserExitExamples/ExitDemo_more_recs/ UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.HPUX ... many lines omitted for clarity ... ulg.sql usrdecs.h zlib.txt [oggadm2@host02 ogg02]$

Start the GoldenGate Software Command Interface (GGSCI). Create the default empty subdirectories.


Host02 - Linux

[oggadm2@host02 ogg02]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (host02) 1> Create Subdirs Creating subdirectories under current directory /u01/app/oracle/ogg02 Parameter files /u01/app/oracle/ogg02/dirprm: already exists Report files /u01/app/oracle/ogg02/dirrpt: created Checkpoint files /u01/app/oracle/ogg02/dirchk: created Process status files /u01/app/oracle/ogg02/dirpcs: created SQL script files /u01/app/oracle/ogg02/dirsql: created Database definitions files /u01/app/oracle/ogg02/dirdef: created Extract data files /u01/app/oracle/ogg02/dirdat: created Temporary files /u01/app/oracle/ogg02/dirtmp: created Stdout files /u01/app/oracle/ogg02/dirout: created GGSCI (host02) 2> Exit [oggadm2@host02 ogg02]$

If a directory already exists, the installation leaves the contents of that directory alone.

You have successfully installed Oracle GoldenGate on Linux hosts host01 and host02.

 

2. Configuring the Environment

The configuration of the environment is done by editing ASCII files and running OS utilities. To configure the environment, perform the following steps:

 
2.1 Configuring the Oracle 11gR2 Databases
This section must be done in SQL*Plus as sysdba.
On host01, verify that LOG_MODE is set to ARCHIVELOG.


Host01 - Linux

[oggadm1@host01 ogg01] sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Mon Sep 17 17:19:01 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SELECT log_mode FROM v$database; LOG_MODE ------------ ARCHIVELOG SQL>

The result should be ARCHIVELOG. If it is, then skip to the next step. If the result is NOARCHIVELOG, then do the following:


Host01 - Linux

LOG_MODE ------------ NOARCHIVELOG SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 3340451840 bytes Fixed Size 2232960 bytes Variable Size 1811942784 bytes Database Buffers 1509949440 bytes Redo Buffers 16326656 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>

Verify that supplemental logging and forced logging are set properly.


Host01 - Linux

SQL> SELECT force_logging, supplemental_log_data_min FROM v$database; FOR SUPPLEME --- -------- YES YES SQL>

The results should both be YES. If it is, then skip to the next step. If either result is NO, then do the following:


Host01 - Linux

FOR SUPPLEME --- -------- NO NO SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; Database altered. SQL> ALTER DATABASE FORCE LOGGING; Database altered. SQL> ALTER SYSTEM SWITCH LOGFILE; System altered. SQL> SELECT force_logging, supplemental_log_data_min FROM v$database; FOR SUPPLEME --- -------- YES YES SQL>

There is no harm in doing the commands twice.

Create the administrator and user/schema owners on host01.
In real life, the user/schema owner would probably have more privileges, and the administrator would have less privileges. The DBMS_GOLDENGATE_AUTH package is not needed for this OBE, but is commonly used in more advanced configurations, so it is a good idea to see it here. Oddly, the DBA role is not sufficient for advanced scenarios; you must also run the DBMS_GOLDENGATE_AUTH package. When keying in the DBMS_GOLDENGATE_AUTHcommand, the entire string after EXEC is without spaces or line breaks.


Host01 - Linux

SQL> CREATE USER ogguser1 IDENTIFIED BY pswd1u; User created. SQL> CREATE USER oggadm1 IDENTIFIED BY pswd1a; User created. SQL> GRANT CONNECT, RESOURCE, UNLIMITED TABLESPACE TO ogguser1; Grant succeeded. SQL> GRANT dba TO oggadm1; Grant succeeded. SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE (grantee=>'OGGADM1',privile ge_type=>'capture',grant_select_privileges=>true, do_grants=>TRUE); PL/SQL procedure successfully completed. SQL> exit [oggadm1@host01 ogg01]$

Verify that both databases can talk to each other.
Test inter-database communication with tnsping.


Host01 - Linux

[oggadm1@host01 ogg01]$ tnsping orcl01 TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 17-SEP-2012 18:52 Copyright (c) 1997, 2011, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)( HOST = host01.example.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl01. example.com))) OK (0 msec) [oggadm1@host01 ogg01]$ tnsping orcl02; TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 17-SEP-2012 18:52 Copyright (c) 1997, 2011, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)( HOST = host02.example.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl02. example.com))) OK (10 msec) [oggadm1@host01 ogg01]$

The results should be OK. If it is, then skip to the next step. If it is not OK, then do the following to examine tnsnames.ora.


Host01 - Linux

[oggadm1@host01 ogg01]$ more $ORACLE_HOME/network/admin/tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_ 1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. ORCL02 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = host02.example.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl02.example.com) ) ) ORCL01 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = host01.example.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl01.example.com) ) ) [oggadm1@host01 ogg01]$

You should have orcl01 and orcl02 defined in tnsnames.ora.

Everything you did for the database orcl01 on host01 needs to be done for orcl02 on host02.
On host02, verify that LOG_MODE is set to ARCHIVELOG.


Host02 - Linux

[oggadm2@host02 ogg02] sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Mon Sep 17 17:19:01 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SELECT log_mode FROM v$database; LOG_MODE ------------ ARCHIVELOG SQL>

The result should be ARCHIVELOG. If it is, then skip to the next step. If the result is NOARCHIVELOG, then do the following:


Host02 - Linux

LOG_MODE ------------ NOARCHIVELOG SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 3340451840 bytes Fixed Size 2232960 bytes Variable Size 1811942784 bytes Database Buffers 1509949440 bytes Redo Buffers 16326656 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>

Verify that supplemental logging and forced logging are set properly.


Host02 - Linux

SQL> SELECT force_logging, supplemental_log_data_min FROM v$database; FOR SUPPLEME --- -------- YES YES SQL>

The results should both be YES. If it is, then skip to the next step. If either result is NO, then do the following:


Host02 - Linux

FOR SUPPLEME --- -------- NO NO SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; Database altered. SQL> ALTER DATABASE FORCE LOGGING; Database altered. SQL> ALTER SYSTEM SWITCH LOGFILE; System altered. SQL> SELECT force_logging, supplemental_log_data_min FROM v$database; FOR SUPPLEME --- -------- YES YES SQL>

There is no harm in doing the commands twice.

Create the administrator and user/schema owners on host02.


Host02 - Linux

SQL> CREATE USER ogguser2 IDENTIFIED BY pswd2u; User created. SQL> CREATE USER oggadm2 IDENTIFIED BY pswd2a; User created. SQL> GRANT CONNECT, RESOURCE, UNLIMITED TABLESPACE TO ogguser2; Grant succeeded. SQL> GRANT dba TO oggadm2; Grant succeeded. SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE (grantee=>'OGGADM2',privile ge_type=>'capture',grant_select_privileges=>true, do_grants=>TRUE); PL/SQL procedure successfully completed. SQL> exit [oggadm2@host02 ogg02]$

Verify that both databases can talk to each other.
Test inter-database communication with tnsping.


Host02 - Linux

[oggadm2@host02 ogg02]$ tnsping orcl01 TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 17-SEP-2012 18:52 Copyright (c) 1997, 2011, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)( HOST = host01.example.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl01. example.com))) OK (0 msec) [oggadm2@host02 ogg02]$ tnsping orcl02; TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 17-SEP-2012 18:52 Copyright (c) 1997, 2011, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)( HOST = host02.example.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl02. example.com))) OK (10 msec) [oggadm2@host02 ogg02]$

The results should be OK. If it is, then skip to the next step. If it is not OK, then do the following to examine tnsnames.ora.


Host02 - Linux

[oggadm2@host02 ogg02]$ more $ORACLE_HOME/network/admin/tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_ 1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. ORCL02 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = host02.example.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl02.example.com) ) ) ORCL01 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = host01.example.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl01.example.com) ) ) [oggadm2@host02 ogg02]$

You should have orcl01 and orcl02 defined in tnsnames.ora.

The databases on host01 and host02 are configured to support Oracle Goldengate. Users on each database are provisioned: one (or more) user and an administrator for each host.
 
2.2 Creating Startup Files and Managers
Create the GLOBALS file on host01 (red) in the Oracle GoldenGate installation directory using your text editor of choice.
It is possible to create and/or edit the GLOBALS file from inside GGSCI by prepending the name with "./". For example:
GGSCI> Edit Param ./GLOBALS 
except that for it to take effect, you have to leave and reenter GGSCI, so you might as well do the editing outside GGSCI before you start GGSCI the first time.
The GLOBALS file name must be all UPPERCASE, and it contains only one line (plus --comments) defining the checkpoint table schema and name. You can use any schema and any name. You can use vi or gedit or any text editor.


Host01 - Linux

[oggadm1@host01 ogg01] vi GLOBALS -- Created by Joe Admin 10/11/2012 on host01 CheckpointTable oggadm1.oggchkpt [oggadm1@host01 ogg01]

Checkpoint tables are only used by the Replicat, so this will not be used until the bidirectional section. Having it present and unused does not hurt anything.

Create the GLOBALS file on host02 (green) in the Oracle GoldenGate installation directory.


Host02 - Linux

[oggadm2@host02 ogg02]$ vi GLOBALS -- Created by Joe Admin 10/11/2012 on host02 CheckpointTable oggadm2.oggchkpt [oggadm2@host02 ogg02]$

Any change to the GLOBALS file requires a restart of GGSCI.

Create the optional startup.oby (Obey) file on host01 (red) in the Oracle GoldenGate installation directory.
These are GGSCI commands that are done almost every time you start GGSCI, and they do not persist between sessions, so you will find yourself entering them many, many times, and therefore it is convenient to create a startup obey file.
If you have several different databases or schemas that you login to on a regular basis, you may want to make startup01.oby, startup02.oby, startup03.oby, and so on. This will be revisited later in the naming conventions of process files.


Host01 - Linux

[oggadm1@host01 ogg01] vi startup.oby -- Created by Joe Admin 10/11/2012 on host01 DBLogin UserID oggadm1@orcl01, Password pswd1a Start Mgr Info Mgr Info CheckpointTable Set Editor gedit [oggadm1@host01 ogg01]

DBLogin:

Connects to the DB using the userid@SID/password specified. The @SID is optional if there is no ambiguity.

Start Mgr:

If the Manager is already started, there is no harm in trying to start it again. This is persistent between sessions.

Info Mgr:

Reports if the Manager started successfully, and if so, the port number being used.

Info CheckpointTable:

Reports if a checkpoint table (used by Replicat) was found.

Set Editor:

The default is vi. You can temporarily change that to a GUI editor such as gedit.

Create the optional startup.oby (Obey) file on host02 (green) in the Oracle GoldenGate installation directory.


Host02 - Linux

[oggadm2@host02 ogg02]$ vi startup.oby -- Created by Joe Admin 10/11/2012 on host02 DBLogin UserID oggadm2@orcl02, Password pswd2a Start Mgr Info Mgr Info CheckpointTable Set Editor gedit [oggadm2@host02 ogg02]$

Create the Manager parameter (mgr.prm) file on host01 in dirprm/.
Start GGSCI. Edit the file with no extension. Add the two lines (plus comments).


Host01 - Linux

[oggadm1@host01 ogg01] ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (host01) 1> Edit Param mgr -- Created by Joe Admin 10/11/2012 on host01 Port 15001 PurgeOldExtracts ./dirdat/*, UseCheckpoints GGSCI (host01) 2> Info mgr Manager is DOWN! GGSCI (host01) 3>

Note: If you do it correctly, GGSCI automatically adds the .prm extension and stores the file in the dirprm/ directory. If you wrongly add the extension yourself, GGSCI converts the filename to UPPERCASE and stores it in the installation directory which renders the file practically unusable.

Create the Manager parameter (mgr.prm) file on host02 in dirprm/.


Host02 - Linux

[oggadm2@host02 ogg02]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (host02) 1> Edit Param mgr -- Created by Joe Admin 10/11/2012 on host02 Port 15002 PurgeOldExtracts ./dirdat/*, UseCheckpoints GGSCI (host02) 2> Info mgr Manager is DOWN! GGSCI (host02) 3>

Notice that the port number is different from host01 to host02. If the Oracle GoldenGate instances are on different hosts, you don't have to make the ports different, as long as the combination of IP address plus port is unique. Nevertheless, it is a good practice to make the ports unique.

Start the Manager on host01 using the Obey files. Alternatively, you could type each of the lines every time you start GGSCI.


Host01 - Linux

GGSCI (host01) 3> Obey startup.oby GGSCI (host01) 4> -- Created by Joe Admin 10/11/2012 on host01 GGSCI (host01) 5> DBLogin UserID oggadm1@orcl01, Password pswd1a Successfully logged into database. GGSCI (host01) 6> Start Mgr Manager started. GGSCI (host01) 7> Info Mgr Manager is running (IP port host01.example.com.15001). GGSCI (host01) 8> Info CheckpointTable No checkpoint table specified, using GLOBALS specification (oggadm1.oggchkpt)... Checkpoint table oggadm1.oggchkpt does not exist. GGSCI (host01) 9> Set Editor gedit GGSCI (host01) 10>

You will create the checkpoint table in the next step.

Start the Manager on host02 using the Obey files.


Host02 - Linux

GGSCI (host02) 3> Obey startup.oby GGSCI (host02) 4> -- Created by Joe Admin 10/11/2012 on host02 GGSCI (host02) 5> DBLogin UserID oggadm2@orcl02, Password pswd2a Successfully logged into database. GGSCI (host02) 6> Start Mgr Manager started. GGSCI (host02) 7> Info Mgr Manager is running (IP port host02.example.com.15002). GGSCI (host02) 8> Info CheckpointTable No checkpoint table specified, using GLOBALS specification (oggadm2.oggchkpt)... Checkpoint table oggadm2.oggchkpt does not exist. GGSCI (host02) 9> Set Editor gedit GGSCI (host02) 10>

You will create the checkpoint table in the next step.

The global and startup files are all created, and the background Manager processes are started. You can verify which processes are running at any time by entering the command:
GGSCI (host) > Info All
 

2.3 Creating Tables

Create the checkpoint table on host01. (This is not needed on the source, so this is will not be used for unidirectional replication, but will be used for bidirectional replication when host01 becomes the target.)


Host01 - Linux

GGSCI (host01) 10> Add CheckpointTable No checkpoint table specified, using GLOBALS specification (oggadm1.oggchkpt). Successfully created checkpoint table oggadm1.oggchkpt. GGSCI (host01) 11> Exit [oggadm1@host01 ogg01]

One checkpoint table will suffice for the whole schema.

Create the checkpoint table on host02.


Host02 - Linux

GGSCI (host02) 10> Add CheckpointTable No checkpoint table specified, using GLOBALS specification (oggadm2.oggchkpt). Successfully created checkpoint table oggadm2.oggchkpt. GGSCI (host02) 11> Exit [oggadm2@host02 ogg02]$

Create empty source sample tables on host01 in schema ogguser1.
If you already have sample tables, you can use those. If not, Oracle GoldenGate software comes with a script demo_ora_create.sql to create two small sample tables tcustmer and tcustord. You can use any SQL utility you like to run the script. If you have no preference, use sqlplus.


Host01 - Linux

[oggadm1@host01 ogg01] sqlplus ogguser1@orcl01/pswd1u @demo_ora_create.sql SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 18 14:38:50 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options DROP TABLE tcustmer * ERROR at line 1: ORA-00942: table or view does not exist Table created. DROP TABLE tcustord * ERROR at line 1: ORA-00942: table or view does not exist Table created. SQL> exit [oggadm1@host01 ogg01]

The ORA-00942 "errors" are okay to ignore. If you run the script a second time you won't see those errors.
Warning! If you run the script a second time, it will drop the tables!

Create empty target sample tables on host02 in schema ogguser2.


Host02 - Linux

[oggadm2@host02 ogg02] sqlplus ogguser2@orcl02/pswd2u @demo_ora_create.sql SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 18 14:44:06 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options DROP TABLE tcustmer * ERROR at line 1: ORA-00942: table or view does not exist Table created. DROP TABLE tcustord * ERROR at line 1: ORA-00942: table or view does not exist Table created. SQL> exit [oggadm2@host02 ogg02]$

Oracle 11gR2 does support replicating DDL such as CREATE TABLE, and therefore the empty tables could be created on the target in other ways before any DML replication begins.

You can check on the tables available any time GGSCI is running (after you have run Obey startup.oby to login to the database) by entering the commands:


GGSCI (host) >  List Tables * 
GGSCI (host) >  List Tables ogguser1.* 

 
2.4 Adding Transaction Data
Restart GGSCI and run Add TranData for the whole user schema.


Host01 - Linux

[oggadm1@host01 ogg01] ./ggsci Oracle GoldenGate Command Interpreter for Oracle ... many lines omitted for clarity ... GGSCI (host01) 1> Obey startup.oby ... many lines omitted for clarity ... but make sure everything started. GGSCI (host01) > List Tables * OGGADM1.OGGCHKPT OGGADM1.OGGCHKPT_LOX Found 2 tables matching list criteria. GGSCI (host01) > List Tables ogguser1.* OGGUSER1.TCUSTMER OGGUSER1.TCUSTORD Found 2 tables matching list criteria. GGSCI (host01) > Add TranData ogguser1.tcustmer Logging of supplemental redo data enabled for table OGGUSER1.TCUSTMER. GGSCI (host01) > Add TranData ogguser1.tcust* Logging of supplemental redo log data is already enabled for table OGGUSER1.TCUST MER. Logging of supplemental redo data enabled for table OGGUSER1.TCUSTORD. GGSCI (host01) > Info TranData ogguser1.* Logging of supplemental redo log data is enabled for table OGGUSER1.TCUSTMER. Columns supplementally logged for table OGGUSER1.TCUSTMER: CUST_CODE. Logging of supplemental redo log data is enabled for table OGGUSER1.TCUSTORD. Columns supplementally logged for table OGGUSER1.TCUSTORD: CUST_CODE, ORDER_DATE, PRODUCT_CODE, ORDER_ID. GGSCI (host01) xx>

Note that you can add transaction data for an individual table or for wildcards. The wildcards can be for a whole schema, but there is a better way to do whole schemas. There is no harm in adding transaction data twice.

Do almost the same thing on host02. (This is not needed on the target, so this is will not be used for unidirectional replication, but will be used for bidirectional replication when host02 becomes the source.)
Use SchemaTranData instead of just TranData.


Host02 - Linux

[oggadm2@host02 ogg02] ./ggsci Oracle GoldenGate Command Interpreter for Oracle ... many lines omitted for clarity ... GGSCI (host02) 1> Obey startup.oby ... many lines omitted for clarity ... but make sure everything started. GGSCI (host02) > List Tables ogguser2.* OGGUSER2.TCUSTMER OGGUSER2.TCUSTORD Found 2 tables matching list criteria. GGSCI (host02) > Add SchemaTranData ogguser2 2012-09-18 15:18:34 INFO OGG-01788 SCHEMATRANDATA has been added on schema o gguser2. GGSCI (host02) > Info SchemaTranData ogguser2 2012-09-18 15:21:06 INFO OGG-01785 Schema level supplemental logging is enab led on schema OGGUSER2. GGSCI (host02) xx>

The end result for replication is the same, but there are several administrative differences when using SchemaTranData:


• No tables show up when you query Info TranData ogguser2.*, but they are in fact enabled.
• Any new table added to the schema becomes automatically enabled for transaction data logging (with plain old TranData you would have to manually add new tables each time for supplemental logging.)
• Not all other OEM databases support SchemaTranData.

The transaction data has been enabled for all user tables involved with Oracle GoldenGate.
 
2.5 Creating Column Definitions (defgen)
Create the column mapping from source to target tables using the OS utility.
This utility is required if the tables have a different (heterogeneous) structure, but is still highly recommended even if the tables have the same (homogeneous) structure. If they do have the same structure, you may be able to use the AssumeTargetDefs parameter instead of the SourceDefs parameter. This is covered later.
Please review the File and Process Naming Conventions in the Overview Topic.


Host01 - Linux

GGSCI (host01) > Edit Param dsalesab -- defgen column definitions for Sales: tcust* from host01 (a) to host02 (b) -- Created by Joe Admin on 10/11/2012 DefsFile dirdef/dsalesab.def, Purge UserID oggadm1@orcl01, Password pswd1a Table ogguser1.tcust*; GGSCI (host01) > Exit [oggadm1@host01 ogg01] ./defgen paramfile dirprm/dsalesab.prm *********************************************************************** Oracle GoldenGate Table Definition Generator for Oracle ... many lines omitted for clarity ... Expanding wildcard ogguser1.tcust*: Retrieving definition for OGGUSER1.TCUSTMER Retrieving definition for OGGUSER1.TCUSTORD Definitions generated for 2 tables in dirdef/dsalesab.def [oggadm1@host01 ogg01]

You can create definitions for a single table, multiple tables (using wildcards), an entire schema, or multiple schemas at a time. Having "excess" (that is, unused by Oracle GoldenGate) definitions in the file is not a problem. You may want to examine the contents of dirdef/dsalesab.def before sending it over to host02.

Copy the files from source host01 to target host02.
Use whatever method you wish to copy the .def files to the target dirdef directory: cut-n-paste, scp, ftp, and so on. The example will use secure shell copy (scp). Do not break the command line after /u01/app/oracle/og, the scp command is entered all on one line.


Host01 - Linux

[oggadm1@host01 ogg01] scp dirdef/dsalesab.def oggadm2@host02:/u01/app/oracle/og g02/dirdef The authenticity of host 'host02 (192.0.2.29)' can't be established. RSA key fingerprint is 8e:60:d0:a7:fc:55:6e:d9:81:bb:c9:90:19:f4:a8:11. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added 'host02,192.0.2.29' (RSA) to the list of known hosts. oggadm2@host02's password: ******** dsalesab.def 100% 1877 1.8KB/s 00:00 [oggadm1@host01 ogg01]

Of course you replace the asterisks with your password, for example pswd2a.

Make sure the files arrived intact on host02 in the proper directory.


Host02 - Linux

[oggadm2@host02 ogg02]$ ls -l dirdef total 4 -rw-r--r-- 1 oggadm2 oinstall 1877 Sep 18 13:43 dsalesab.def [oggadm2@host02 ogg02]$

The column definitions are created and copied. If the table structure changes, you need to do this again.

 

3. Configuring Data Capture Using Extract

Data capture, also known as Extract, is done on the source side. A primary extract is required; a secondary extract, known as a Data Pump, is optional but highly recommended. To configure data capture, perform the following steps:

 
3.1 Configuring the Primary Extract
On source host01, create the primary Extract parameter file.
Using the naming conventions discussed in the Overview, the path/filename.ext will be dirprm/esalesaa.prm. The path and extension will be added automatically. (The primary extract can write a trail file directly to the target host, in which case the suffix would be "ab", but since it is going to a local data pump before leaving the source host, the suffix is "aa". All of this is just convention, you can name the processes anything you want.)


Host01 - Linux

[oggadm1@host01 ogg01] ./ggsci Oracle GoldenGate Command Interpreter for Oracle ... many lines omitted for clarity ... GGSCI (host01) 1> Obey startup.oby ... many lines omitted for clarity ... but make sure everything started. GGSCI (host01) > Edit Param esalesaa -- Primary Extract from host01 to host02 -- Created by Joe Admin on 10/11/2012 -- SETENV(ORACLE_SID = "orcl01") Extract esalesaa ExtTrail ./dirdat/aa UserID oggadm1@orcl01, Password pswd1a -- TranLogOptions ExcludeUser oggadm1 Table ogguser1.tcust*; GGSCI (host01) >

Some of the parameters:


-- :

A comment. Useful for self-documenting files, as well as storing parameters inline that may be used later.

SETENV:

You have to define SID (the Database System Identifier) somewhere: either in the .profile or in the UserID or here in the SETENV. Only one place is necessary, though if you define it here it overrrides any previous definitions in the .profile.

ExtTrail:

Not really "external" if you are using a data pump.

aa:

A sequence number is appendeded to this alphabetic prefix. For example, the trail files are named aa000000, aa000001, aa000002, and so on, rolling over as the file grows beyond the specified size (default 100 MB).

TranLogOptions :

Used later by bidirectional replication to prevent loops.

Table:

You can have multiple Table statements, each with one table name (with optional wildcards) resolving to zero, one, or more tables. You cannot use a wildcard in the schema name.

It is possible to encrypt the passwords in the file, but that is beyond the scope of this exercise.
You can check your work by entering View Param esalesaa any time.

Create the Extract group and the local Extract trail file.


Host01 - Linux

GGSCI (host01) > Add Extract esalesaa, TranLog, Begin Now EXTRACT added. GGSCI (host01) > Add ExtTrail ./dirdat/aa, Extract esalesaa, Megabytes 5 EXTTRAIL added. GGSCI (host01) >

The Megabytes 5 is optional. The default is 100 Megabytes.

The primary Extract has been created and configured, but not started. Leave GGSCI running for the next step.
 
3.2 Configuring the Data Pump
Create the secondary Extract (data pump) parameter file.
Using the naming conventions discussed in the Overview, the path/filename.ext will be dirprm/psalesab.prm. The path and extension will be added automatically.


Host01 - Linux

GGSCI (host01) > Edit Param psalesab -- Data pump (secondary Extract) from host01 to host02 -- Created by Joe Admin on 10/11/2012 Extract psalesab RmtHost host02, MgrPort 15002, Compress RmtTrail ./dirdat/ab Passthru Table ogguser1.tcust*; GGSCI (host01) >

This Table schema is the source.
You can check your work by entering View Param psalesab any time.

Create the data pump group and the remote Extract trail file.


Host01 - Linux

GGSCI (host01) > Add Extract psalesab, ExtTrailSource ./dirdat/aa EXTRACT added. GGSCI (host01) > Add RmtTrail ./dirdat/ab, Extract psalesab, Megabytes 5 RMTTRAIL added. GGSCI (host01) >

The Megabytes 5 is optional. The default is 100 Megabytes.
The data pump reads from the local trail file aa and writes to the remote trail file ab. The remote trail file that will be created will be named dirdat/ab000000, then when that one fills up the next will be dirdat/ab000001, then dirdat/ab000002, and so on. Since the two sets of trail files are on different hosts (even though they are in directories with the same names), the files could be named the same thing (for example ab). The different file name is chosen just to illustrate that the parameter RmtTrail is creating a different trail file.

The secondary Extract has been created and configured, but not started. Leave GGSCI running for the next step.
 
3.3 Verify the Extract Processes (Optional)
Make sure the Extract processes were created and registered correctly.


Host01 - Linux

GGSCI (host01) > Info All Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT STOPPED ESALESAA 00:00:00 00:03:24 EXTRACT STOPPED PSALESAB 00:00:00 00:01:22 GGSCI (host01) >

Since nothing other than the Manager is started yet, the Extract Status should say STOPPED.

Make sure the trail files were created and registered correctly.


Host01 - Linux

GGSCI (host01) > Info ExtTrail * Extract Trail: ./dirdat/aa Extract: ESALESAA Seqno: 0 RBA: 0 File Size: 5M Extract Trail: ./dirdat/ab Extract: PSALESAB Seqno: 0 RBA: 0 File Size: 5M GGSCI (host01) >

Since nothing has started yet, the sequence numbers (Seqno) and relative byte addresses (RBA) should say 0.

Leave GGSCI running for the next step.

 

4. Configuring Data Delivery Using Replicat

Data delivery, also known as Replicat (the "e" is left off intentionally), is done at the target side. To configure data delivery, perform the following steps:

 

4.1 Configuring the Replicat

On target host02, create the Replicat parameter file.


Host02 - Linux

[oggadm2@host02 ogg02]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle ... many lines omitted for clarity ... GGSCI (host02) 1> Obey startup.oby ... many lines omitted for clarity ... but make sure everything started. GGSCI (host02) > Edit Param rsalesab -- Delivery from host01 to host02 -- Created by Joe Admin on 10/11/2012 Replicat rsalesab UserID oggadm2@orcl02, password pswd2a -- AssumeTargetDefs SourceDefs dirdef/dsalesab.def DiscardFile dirrpt/rsalesab.dsc, Append Map ogguser1.tcustmer, Target ogguser2.tcustmer; Map ogguser1.*, Target ogguser2.*; GGSCI (host02) >

The Map statement can be simple (as shown here), or very complex, transforming columns and fields. For example, you could combine three source columns first_name, middle_init, last_name into a single target column full_name.

Create the Replicat process.


Host02 - Linux

GGSCI (host02) > Add Replicat rsalesab, ExtTrail ./dirdat/ab REPLICAT added. GGSCI (host02) >

You can check your work by entering View Param rsalesab any time.

Make sure the Replicat process was created and registered correctly.


Host02 - Linux

GGSCI (host02) > Info All Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT STOPPED RSALESAB 00:00:00 00:00:33 GGSCI (host02) >

Since nothing other than the Manager is started yet, the Replicat Status should say STOPPED.

 
4.2 Starting All Processes
Start all Extract processes on the source.


Host01 - Linux

GGSCI (host01) > Start Extract * Sending START request to MANAGER ... EXTRACT ESALESAA starting Sending START request to MANAGER ... EXTRACT PSALESAB starting GGSCI (host01) >

Alternatively, you could have entered, Start *, or Start e* and Start p*.

Start all Replicat processes on the target.


Host02 - Linux

GGSCI (host02) > Start Replicat * Sending START request to MANAGER ... REPLICAT RSALESAB starting GGSCI (host02) >

Alternatively, you could have entered, Start *, or Start rsales*.

Display information about all processes on the target.
Display summary information.


Host02 - Linux

GGSCI (host02) > Info All Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING RSALESAB 00:00:00 00:00:03 GGSCI (host02) >

Display more information.


Host02 - Linux

GGSCI (host02) > Info rsalesab REPLICAT RSALESAB Last Started 2012-09-19 19:13 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:04 ago) Log Read Checkpoint File ./dirdat/bc000001 First Record RBA 0 GGSCI (host02) >

Display the most detailed information.


Host02 - Linux

GGSCI (host02) > Info rsalesab, Detail REPLICAT RSALESAB Last Started 2012-09-19 19:13 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:09 ago) Log Read Checkpoint File ./dirdat/ab000000 First Record RBA 0 Extract Source Begin End ./dirdat/ab000000 * Initialized * First Record ./dirdat/ab000000 * Initialized * First Record Current directory /u01/app/oracle/ogg02 Report file /u01/app/oracle/ogg02/dirrpt/RSALESAB.rpt Parameter file /u01/app/oracle/ogg02/dirprm/rsalesab.prm Checkpoint file /u01/app/oracle/ogg02/dirchk/RSALESAB.cpr Checkpoint table oggadm2.oggchkpt Process file /u01/app/oracle/ogg02/dirpcs/RSALESAB.pcr Stdout file /u01/app/oracle/ogg02/dirout/RSALESAB.out Error log /u01/app/oracle/ogg02/ggserr.log GGSCI (host02) >

In all cases, the Status should be RUNNING, and the time since the last update or checkpoint should be under 10 seconds.

Display information about all processes on the source.
Display summary information.


Host01 - Linux

GGSCI (host01) > Info All Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING ESALESAA 00:00:00 00:00:04 EXTRACT RUNNING PSALESAB 00:00:00 00:00:05 GGSCI (host01) >

Display detailed information.


Host01 - Linux

GGSCI (host01) > Info Extract * EXTRACT ESALESAA Last Started 2012-09-19 19:12 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:08 ago) Log Read Checkpoint Oracle Redo Logs 2012-09-19 19:21:49 Seqno 18, RBA 29845504 SCN 0.1372459 (1372459) EXTRACT PSALESAB Last Started 2012-09-19 19:12 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:01 ago) Log Read Checkpoint File ./dirdat/ab000000 First Record RBA 0 GGSCI (host01) > Exit [oggadm1@host01 ogg01]

Similar to the Replicat, the Status should be RUNNING, and the time since the last update or checkpoint should be under 10 seconds.

Everything should show a status of RUNNING. The source tables are still empty. No data has flowed yet, nothing has replicated yet.

 

5. Generating Data

The Oracle GoldenGate software comes with SQL scripts to generate sample data traffic. There is a difference in how you replicate an existing table (more complicated) versus starting with a new empty table (simpler). To generate sample data against an empty set of tables, perform the following steps:

 
5.1 Generating INSERTs (Initial Load)
Run the SQL script to INSERT rows.
On source host01, at the OS prompt, run the demo_ora_insert.sql script in sqlplus.


Host01 - Linux

[oggadm1@host01 ogg01] sqlplus ogguser1@orcl01/pswd1u @demo_ora_insert.sql SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 19 19:27:03 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 1 row created. 1 row created. 1 row created. 1 row created. Commit complete. SQL>

There should not be any errors. Leave SQL*Plus running for the next step.

Verify that the rows were inserted into the source tables.


Host01 - Linux

SQL> SELECT * FROM tcustmer; CUST_CODE NAME CITY STATE ---------- ------------------------------ -------------------- ------ WILL BG SOFTWARE CO. SEATTLE WA JANE ROCKY FLYER INC. DENVER CO SQL> SELECT * FROM tcustord; CUST ORDER_DAT PRODUCT_ ORDER_ID PRODUCT_PRICE PRODUCT_AMOUNT TRANSACTION_ID ---- --------- -------- ---------- ------------- -------------- -------------- WILL 30-SEP-94 CAR 144 17520 3 100 JANE 11-NOV-95 PLANE 256 133300 1 100 SQL> exit [oggadm1@host01 ogg01]

Verify that the source GGSCI Extract processes are still running.
On source host01, enter the following command:


Host01 - Linux

[oggadm1@host01 ogg01] ./ggsci GGSCI (host01) 1> Info All Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING ESALESAA 00:00:00 00:00:00 EXTRACT RUNNING PSALESAB 00:00:00 00:00:06 GGSCI (host01) 2> Exit [oggadm1@host01 ogg01]

If the Status says ABENDED, then check the process reports (covered as a later topic) to see what the error was.

Verify that the target GGSCI Replicat processes are still running.
On target host02, GGSCI should still be running. Enter the following command:


Host02 - Linux

GGSCI (host02) > Info All Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING RSALESAB 00:00:00 00:00:05 GGSCI (host02) > Exit [oggadm2@host02 ogg02]

If the Status says ABENDED, then check the process reports (covered as a later topic) to see what the error was.

Verify that rows have been inserted into the target tables.
On target host02, at the OS prompt, run the following SQL queries:


Host02 - Linux

[oggadm2@host02 ogg02] sqlplus ogguser2@orcl02/pswd2u SQL> SELECT * FROM tcustmer; CUST_CODE NAME CITY STATE ---------- ------------------------------ -------------------- ------ WILL BG SOFTWARE CO. SEATTLE WA JANE ROCKY FLYER INC. DENVER CO SQL> SELECT * FROM tcustord; CUST ORDER_DAT PRODUCT_ ORDER_ID PRODUCT_PRICE PRODUCT_AMOUNT TRANSACTION_ID ---- --------- -------- ---------- ------------- -------------- -------------- WILL 30-SEP-94 CAR 144 17520 3 100 JANE 11-NOV-95 PLANE 256 133300 1 100 SQL> exit [oggadm2@host02 ogg02]

The source and target database tcust* tables should match.

There are alternative ways of doing the initial load on the target if the source already has rows in it, but this is the simplest way when both tables are initially empty.
 
5.2 Generating UPDATEs/DELETEs
Run the SQL script to UPDATE and DELETE rows.
On source host01, at the OS prompt, run the demo_ora_misc.sql script in sqlplus.


Host01 - Linux

[oggadm1@host01 ogg01] sqlplus ogguser1@orcl01/pswd1u @demo_ora_misc.sql ... many lines omitted for clarity ... Commit complete. 3 rows deleted. Rollback complete. SQL>

There should not be any errors.

Verify that the rows were updated and deleted in the source tables.


Host01 - Linux

SQL> SELECT * FROM tcustmer; CUST_CODE NAME CITY STATE ---------- ------------------------------ -------------------- ------ WILL BG SOFTWARE CO. SEATTLE WA JANE ROCKY FLYER INC. DENVER CO DAVE DAVE'S PLANES INC. TALLAHASSEE FL BILL BILL'S USED CARS DENVER CO ANN ANN'S BOATS NEW YORK NY SQL> SELECT * FROM tcustord; CUST ORDER_DAT PRODUCT_ ORDER_ID PRODUCT_PRICE PRODUCT_AMOUNT TRANSACTION_ID ---- --------- -------- ---------- ------------- -------------- -------------- BILL 31-DEC-95 CAR 765 14000 3 100 BILL 01-JAN-96 TRUCK 333 25000 15 100 WILL 30-SEP-94 CAR 144 16520 3 100 SQL> exit [oggadm1@host01 ogg01]

Three rows inserted into tcustmer, three rows inserted into tcustord, four rows updated in tcustord, two rows deleted from tcustord.

Verify that the source GGSCI Extract processes are still running.
On source host01, enter the following command:


Host01 - Linux

[oggadm1@host01 ogg01] ./ggsci GGSCI (host01) 1> Info All Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING ESALESAB 00:00:00 00:00:09 EXTRACT RUNNING PSALESAB 00:00:00 00:00:01 GGSCI (host01) 2>

(For something as trivial as this, you do not need to run Obey startup.oby, though it couldn't hurt.) If the initial INSERTs worked, the odds are that the UPDATEs and DELETEs will work as well.

Verify that the target GGSCI Replicat processes are still running.
On target host02, GGSCI should still be running. Enter the following command:


Host02 - Linux

GGSCI (host02) > Info All Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING RSALESAB 00:00:00 00:00:09 GGSCI (host02) > Exit [oggadm2@host02 ogg02]

The Status should still be RUNNING.

Verify that rows have been updated and deleted in the target tables.
On target host02, run the following SQL queries:


Host02 - Linux

[oggadm2@host02 ogg02] sqlplus ogguser2@orcl02/pswd2u SQL> SELECT * FROM tcustmer; CUST_CODE NAME CITY STATE ---------- ------------------------------ -------------------- ------ WILL BG SOFTWARE CO. SEATTLE WA JANE ROCKY FLYER INC. DENVER CO DAVE DAVE'S PLANES INC. TALLAHASSEE FL BILL BILL'S USED CARS DENVER CO ANN ANN'S BOATS NEW YORK NY SQL> SELECT * FROM tcustord; CUST ORDER_DAT PRODUCT_ ORDER_ID PRODUCT_PRICE PRODUCT_AMOUNT TRANSACTION_ID ---- --------- -------- ---------- ------------- -------------- -------------- WILL 30-SEP-94 CAR 144 16520 3 100 BILL 31-DEC-95 CAR 765 14000 3 100 BILL 01-JAN-96 TRUCK 333 25000 15 100 SQL> exit [oggadm2@host02 ogg02]

This completes the configuration and operation of the basic unidirectional functionality of Oracle GoldenGate: Extract, Data Pump, and Replicat.

 

6. Managing the Oracle GoldenGate Environment

There are reports generated during and after the processes run. These reports contain information, warnings, and errors (if any) related to that run of the process. To view the reports, perform the following steps:

 
6.1 Viewing Reports
View the process reports on the source.
View the process report for the Extract.


Host01 - Linux

GGSCI (host01) 2> Send Extract esalesaa, Report Sending REPORT request to EXTRACT ESALESAA ... Request processed. GGSCI (host01) 3> View Report esalesaa *********************************************************************** Oracle GoldenGate Capture for Oracle Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:32:12 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. Starting at 2012-09-19 19:12:33 *********************************************************************** Operating System Version: Linux Version #1 SMP Wed Jul 27 21:02:33 EDT 2011, Release 2.6.32-200.13.1.el5uek Node: host01.example.com Machine: x86_64 soft limit hard limit Address Space Size : unlimited unlimited Heap Size : unlimited unlimited File Size : unlimited unlimited CPU Time : unlimited unlimited Process id: 18569 Description: *********************************************************************** ** Running with the following parameters ** *********************************************************************** ESALESAA.rpt (25%)

You can press [Enter] to move one line at a time, or press [Space] to move a page at a time. Go to the bottom of the report.


Host01 - Linux

*********************************************************************** * ** Run Time Statistics ** * *********************************************************************** Report at 2012-09-19 19:42:10 (activity since 2012-09-19 19:27:05) Output to ./dirdat/aa: From Table OGGUSER1.TCUSTMER: # inserts: 5 # updates: 1 # deletes: 0 # discards: 0 From Table OGGUSER1.TCUSTORD: # inserts: 5 # updates: 3 # deletes: 2 # discards: 0 *********************************************************************** ** Run Time Warnings ** *********************************************************************** GGSCI (host01) 4>

You can do the same thing to view the Data Pump psalesab report.

View the process reports for the target.
On the target host02, view the process reports for the Replicat. (For something as trivial as this, you do not need to run Obey startup.oby, though it couldn't hurt.)


Host02 - Linux

[oggadm2@host02 ogg02]$ ./ggsci GGSCI (host02) > Send Replicat rsalesab, Report Sending REPORT request to REPLICAT RSALESAB ... Request processed. GGSCI (host02)> View Report rsalesab *********************************************************************** Oracle GoldenGate Delivery for Oracle Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:37:31 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. Starting at 2012-09-19 19:49:13 *********************************************************************** Operating System Version: Linux Version #1 SMP Wed Jul 27 21:02:33 EDT 2011, Release 2.6.32-200.13.1.el5uek Node: host02.example.com Machine: x86_64 soft limit hard limit Address Space Size : unlimited unlimited Heap Size : unlimited unlimited File Size : unlimited unlimited CPU Time : unlimited unlimited Process id: 23557 Description: *********************************************************************** ** Running with the following parameters ** *********************************************************************** --More--(25%)

You can press [Enter] to move one line at a time, or press [Space] to move a page at a time. Go to the bottom of the report.


Host02 - Linux

*********************************************************************** * ** Run Time Statistics ** * *********************************************************************** Last record for the last committed transaction is the following: ___________________________________________________________________ Trail name : ./dirdat/ab000000 Hdr-Ind : E (x45) Partition : . (x04) UndoFlag : . (x00) BeforeAfter: B (x42) RecLength : 61 (x003d) IO Time : 2012-09-20 02:04:41.000792 IOType : 3 (x03) OrigNode : 255 (xff) TransInd : . (x02) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 20 AuditPos : 6237080 Continued : N (x00) RecCount : 1 (x01) 2012-09-20 02:04:41.000792 Delete Len 61 RBA 3775 Name: OGGUSER1.TCUSTORD ___________________________________________________________________ Reading ./dirdat/ab000000, current RBA 3932, 16 records Report at 2012-09-20 14:04:52 (activity since 2012-09-19 19:49:15) From Table OGGUSER1.TCUSTMER to OGGUSER2.TCUSTMER: # inserts: 5 # updates: 1 # deletes: 0 # discards: 0 From Table OGGUSER1.TCUSTORD to OGGUSER2.TCUSTORD: # inserts: 5 # updates: 3 # deletes: 2 # discards: 0 *********************************************************************** ** Run Time Warnings ** *********************************************************************** GGSCI (host02) >

The count of inserts/updates/deletes for the Replicat should match the number for the Extract. If there were any discards, they should be examined for the reason why they were discarded.

Reports are generated when you ask for a report to be sent (as you just did), or when a process stops. Reports are named/numbered processname.rpt for the most current one, and then processname0.rpt, processname1.rpt, processname2.rpt for the most recent, up to processname9.rpt for the oldest. As each new report is created, all the old reports "move down one" getting renamed/renumbered until the oldest is discarded. No more than 11 (the most recent with no number and the previous 0-9) are kept at a time.
 
6.2 Viewing Statistics
View statistics for the source Extract.


Host01 - Linux

GGSCI (host01) > Send Extract esalesaa, Stats Sending STATS request to EXTRACT ESALESAA ... Start of Statistics at 2012-09-20 14:08:06. Output to ./dirdat/aa: Extracting from OGGUSER1.TCUSTMER to OGGUSER1.TCUSTMER: *** Total statistics since 2012-09-19 19:27:05 *** Total inserts 5.00 Total updates 1.00 Total deletes 0.00 Total discards 0.00 Total operations 6.00 *** Daily statistics since 2012-09-20 00:00:00 *** Total inserts 3.00 Total updates 1.00 Total deletes 0.00 Total discards 0.00 Total operations 4.00 *** Hourly statistics since 2012-09-20 14:00:00 *** No database operations have been performed. *** Latest statistics since 2012-09-19 19:27:05 *** Total inserts 5.00 Total updates 1.00 Total deletes 0.00 Total discards 0.00 Total operations 6.00 Extracting from OGGUSER1.TCUSTORD to OGGUSER1.TCUSTORD: *** Total statistics since 2012-09-19 19:27:05 *** Total inserts 5.00 Total updates 3.00 Total deletes 2.00 Total discards 0.00 Total operations 10.00 *** Daily statistics since 2012-09-20 00:00:00 *** Total inserts 3.00 Total updates 3.00 Total deletes 2.00 Total discards 0.00 Total operations 8.00 *** Hourly statistics since 2012-09-20 14:00:00 *** No database operations have been performed. *** Latest statistics since 2012-09-19 19:27:05 *** Total inserts 5.00 Total updates 3.00 Total deletes 2.00 Total discards 0.00 Total operations 10.00 End of Statistics. GGSCI (host01) >

The command Stats esalesaa does the same thing. If the process stops for any reason, all statistics are lost.

View statistics for the target Replicat.


Host02 - Linux

GGSCI (host02) > Send Replicat rsalesab, Stats Sending STATS request to REPLICAT RSALESAB ... Start of Statistics at 2012-09-20 14:10:51. Replicating from OGGUSER1.TCUSTMER to OGGUSER2.TCUSTMER: *** Total statistics since 2012-09-19 19:49:15 *** Total inserts 5.00 Total updates 1.00 Total deletes 0.00 Total discards 0.00 Total operations 6.00 *** Daily statistics since 2012-09-20 00:00:00 *** Total inserts 3.00 Total updates 1.00 Total deletes 0.00 Total discards 0.00 Total operations 4.00 *** Hourly statistics since 2012-09-20 14:00:00 *** No database operations have been performed. *** Latest statistics since 2012-09-19 19:49:15 *** Total inserts 5.00 Total updates 1.00 Total deletes 0.00 Total discards 0.00 Total operations 6.00 Replicating from OGGUSER1.TCUSTORD to OGGUSER2.TCUSTORD: *** Total statistics since 2012-09-19 19:49:15 *** Total inserts 5.00 Total updates 3.00 Total deletes 2.00 Total discards 0.00 Total operations 10.00 *** Daily statistics since 2012-09-20 00:00:00 *** Total inserts 3.00 Total updates 3.00 Total deletes 2.00 Total discards 0.00 Total operations 8.00 *** Hourly statistics since 2012-09-20 14:00:00 *** No database operations have been performed. *** Latest statistics since 2012-09-19 19:49:15 *** Total inserts 5.00 Total updates 3.00 Total deletes 2.00 Total discards 0.00 Total operations 10.00 End of Statistics. GGSCI (host02) >

Notice that previously the statistics for the primary Extract were from schema OGGUSER1 to OGGUSER1 since a Data Pump was involved (stayed local to the source host). Here in the Replicat it is clearer that it is from schema OGGUSER1 to OGGUSER2.

There are other kinds of statistics that are available as well.
 

6.3 Stopping and Deleting Processes

Do not do this yet, but if you ever needed to stop an individual process, you simply enter Stop Extract esalesaa, or Stop * to stop all of them on a host. Obviously you would replace esalesaa with the process name you wish to stop.

Do not do this yet, but if you ever needed to delete an individual process, you must first make sure you are connected to the database (DBLogin) and you enter Delete Extract esalesaa, or Delete ER * to delete all of them on a host. GGSCI will prompt you, "Are you sure?" and you say, y. You can eliminate the question by appending an exclamation point to the end of the command. For example, Delete ER * ! assumes the answer is "yes."

 

7. Configuring Bidirectional Support

You should have unidirectional replication working already. At the simplest implementation, bidirectional replication is just two unrelated unidirectional replications. So, adopting the naming/numbering convention from the Overview, one direction will be the "sales" set of files from "a" to "b" (esalesaa, psalesab, dsalesab, rsalesab), and the other direction will be the "sales" set of files from "b" to "a" (esalesbb, psalesba, dsalesba, rsalesba). To configure bidirectional replication support, perform the following steps:

 
7.1 Configuring Extract
On source host02, create the primary Extract parameter file. This is almost an exact repeat of Topic 3.1.1. See that section for additional notes.


Host02 - Linux

[oggadm2@host02 ogg02] ./ggsci Oracle GoldenGate Command Interpreter for Oracle ... many lines omitted for clarity ... GGSCI (host02) 1> Obey startup.oby ... many lines omitted for clarity ... GGSCI (host02) > Edit Param esalesbb -- Primary Extract from host02 to host01 -- Created by Joe Admin on 10/11/2012 -- SETENV(ORACLE_SID = "orcl02") Extract esalesbb ExtTrail ./dirdat/bb UserID oggadm2@orcl02, Password pswd2a TranLogOptions ExcludeUser oggadm2 Table ogguser2.tcust*; GGSCI (host02) >

The difference between this Extract parameter file and the one on host01 (other than the obvious name changes) is that TranLogOptions is now active (uncommented).

Create the Extract group and the local Extract trail file.


Host02 - Linux

GGSCI (host02) > Add Extract esalesbb, TranLog, Begin Now EXTRACT added. GGSCI (host02) > Add ExtTrail ./dirdat/bb, Extract esalesbb, Megabytes 5 EXTTRAIL added. GGSCI (host02) >

Create the secondary Extract (data pump) parameter file. This is almost an exact repeat of Topic 3.2.1. See that section for additional notes.


Host02 - Linux

GGSCI (host02) > Edit Param psalesba -- Data pump (secondary Extract) from host02 to Host01 -- Created by Joe Admin on 10/11/2012 Extract psalesba RmtHost host01, MgrPort 15001, Compress RmtTrail ./dirdat/ba Passthru Table ogguser2.tcust*; GGSCI (host02) >

Create the data pump group and the remote Extract trail file.


Host02 - Linux

GGSCI (host02) > Add Extract psalesba, ExtTrailSource ./dirdat/bb EXTRACT added. GGSCI (host02) > Add RmtTrail ./dirdat/ba, Extract psalesba, Megabytes 5 RMTTRAIL added. GGSCI (host02) >

Make sure the Extract processes were created and registered correctly. This is almost an exact repeat of Topic 3.3.1. See that section for additional notes.


Host02 - Linux

GGSCI (host02) > Info All Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT STOPPED ESALESBB 00:00:00 00:03:35 EXTRACT STOPPED PSALESBA 00:00:00 00:00:15 REPLICAT RUNNING RSALESAB 00:00:00 00:00:05 GGSCI (host02) >

You have successfully configured the Extract from host02 to host01 (the return path of the bidirectional solution).
 
7.2 Configuring Replicat
On target host01, create the Replicat parameter file. This is almost an exact repeat of Topic 4.1.1.


Host01 - Linux

[oggadm1@host01 ogg01]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle ... many lines omitted for clarity ... GGSCI (host01) 1> Obey startup.oby ... many lines omitted for clarity ... but make sure everything started. GGSCI (host01) > Edit Param rsalesba -- Delivery from host02 to host01 -- Created by Joe Admin on 10/11/2012 Replicat rsalesba UserID oggadm1@orcl01, password pswd1a AssumeTargetDefs -- SourceDefs dirdef/dsalesab.def DiscardFile dirrpt/rsalesba.dsc, Append Map ogguser2.tcustmer, Target ogguser1.tcustmer; Map ogguser2.*, Target ogguser1.*; GGSCI (host01) >

You could define and copy the SourceDefs again from host02 to host01, but since they happen to be the same table structures, you can use AssumeTargetDefs. In fact, you could have used AssumeTragetDefs in rsalesab as well.

Create the Replicat process.


Host01 - Linux

GGSCI (host01) > Add Replicat rsalesba, ExtTrail ./dirdat/ba REPLICAT added. GGSCI (host01) >

Fix the existing Extract process to acocmodate bidirectional replication. Prevent loopbacks with the ExcludeUser feature.
Uncomment the line that enables this feature. This matches the same thing host02.


Host01 - Linux

GGSCI (host01) > Stop esalesaa Sending STOP request to EXTRACT ESALESAA ... Request processed. GGSCI (host01) > edit param esalesaa -- Primary Extract from host01 to host02 -- Created by Joe Admin on 10/11/2012 -- SETENV(ORACLE_SID = "orcl01") Extract esalesaa ExtTrail ./dirdat/aa UserID oggadm1@orcl01, Password pswd1a TranLogOptions ExcludeUser oggadm1 Table ogguser1.tcust*; GGSCI (host01) >

Make sure the Replicat process was created and registered correctly.


Host01 - Linux

GGSCI (host01) > Info All Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT STOPPED ESALESAA 00:00:00 00:04:22 EXTRACT RUNNING PSALESAB 00:00:00 00:00:02 REPLICAT STOPPED RSALESBA 00:00:00 00:06:57 GGSCI (host01) >

It is okay for the Time Since Chkpt to exceed 10 seconds if the Status is STOPPED, but if the Status is RUNNING then the time should be less than 10 seconds.

Start all processes on the target host01.


Host01 - Linux

GGSCI (host01) > Start * Sending START request to MANAGER ... EXTRACT ESALESAA starting EXTRACT PSALESAB is already running. Sending START request to MANAGER ... REPLICAT RSALESBA starting GGSCI (host01) > Info All Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING ESALESAA 00:00:00 00:00:00 EXTRACT RUNNING PSALESAB 00:00:00 00:00:02 REPLICAT RUNNING RSALESBA 00:00:00 00:00:01 GGSCI (host01) > Exit [oggadm1@host01 ogg01]

Start all processes on the source host02.


Host02 - Linux

GGSCI (host02) > Start * Sending START request to MANAGER ... EXTRACT ESALESBB starting Sending START request to MANAGER ... EXTRACT PSALESBA starting REPLICAT RSALESAB is already running. GGSCI (host02) > Info All Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING ESALESBB 00:00:00 00:00:01 EXTRACT RUNNING PSALESBA 00:00:00 00:00:02 REPLICAT RUNNING RSALESAB 00:00:00 00:00:04 GGSCI (host02) >

All Extract and Replicat processes are configured and started from host02 to host01. The earlier Extract and replicat from host01 to host02 are still running.
 
7.3 Generating Traffic
INSERT a new row on host01.


Host01 - Linux

[oggadm1@host01 ogg01] sqlplus ogguser1@orcl01/pswd1u SQL> INSERT INTO tcustmer VALUES ('WHIZ','WHIZBANG CORP','HOSTA','AA'); 1 row created. SQL> SELECT * FROM tcustmer; CUST NAME CITY ST ---- ------------------------------ -------------------- -- DAVE DAVE'S PLANES INC. TALLAHASSEE FL BILL BILL'S USED CARS DENVER CO ANN ANN'S BOATS NEW YORK NY WHIZ WHIZBANG CORP HOSTA AA WILL BG SOFTWARE CO. SEATTLE WA JANE ROCKY FLYER INC. DENVER CO 6 rows selected. SQL> commit; Commit complete. SQL> exit [oggadm1@host01 ogg01]

Make sure the GGSCI processes are still running on host02.


Host02 - Linux

GGSCI (host02) > Info All Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING ESALESBB 00:00:00 00:00:05 EXTRACT RUNNING PSALESBA 00:00:00 00:00:05 REPLICAT RUNNING RSALESAB 00:00:00 00:00:06 GGSCI (host02) >

Verify that the Whizbang row was replicated to host02.


Host02 - Linux

GGSCI (host02) > Exit [oggadm2@host02 ogg02] sqlplus ogguser2@orcl02/pswd2u SQL> SELECT * FROM tcustmer; CUST NAME CITY ST ---- ------------------------------ -------------------- -- WILL BG SOFTWARE CO. SEATTLE WA JANE ROCKY FLYER INC. DENVER CO DAVE DAVE'S PLANES INC. TALLAHASSEE FL BILL BILL'S USED CARS DENVER CO ANN ANN'S BOATS NEW YORK NY WHIZ WHIZBANG CORP HOSTA AA 6 rows selected. SQL>

Do not worry if the row order is different. Leave SQL*Plus running for the next step.

INSERT a new row on host02.


Host02 - Linux

SQL> INSERT INTO tcustmer VALUES ('FOO','FOOBAR INC','HOSTB','CC'); 1 row created. SQL> SELECT * FROM tcustmer; CUST NAME CITY ST ---- ------------------------------ -------------------- -- WILL BG SOFTWARE CO. SEATTLE WA JANE ROCKY FLYER INC. DENVER CO DAVE DAVE'S PLANES INC. TALLAHASSEE FL BILL BILL'S USED CARS DENVER CO ANN ANN'S BOATS NEW YORK NY WHIZ WHIZBANG CORP HOSTA AA FOO FOOBAR INC HOSTB CC 7 rows selected. SQL> commit; Commit complete. SQL> exit [oggadm2@host02 ogg02]

Make sure the GGSCI processes are still running on host02.


Host02 - Linux

[oggadm2@host02 ogg02] ./ggsci GGSCI (host02) > Info All Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING ESALESBB 00:00:00 00:00:06 EXTRACT RUNNING PSALESBA 00:00:00 00:00:03 REPLICAT RUNNING RSALESAB 00:00:00 00:00:04 GGSCI (host02) >

Verify that the Foobar row was replicated to host01.


Host01 - Linux

[oggadm1@host01 ogg01] sqlplus ogguser1@orcl01/pswd1u SQL> SELECT * FROM tcustmer; CUST NAME CITY ST ---- ------------------------------ -------------------- -- DAVE DAVE'S PLANES INC. TALLAHASSEE FL BILL BILL'S USED CARS DENVER CO ANN ANN'S BOATS NEW YORK NY WHIZ WHIZBANG CORP HOSTA AA FOO FOOBAR INC HOSTB CC WILL BG SOFTWARE CO. SEATTLE WA JANE ROCKY FLYER INC. DENVER CO 7 rows selected. SQL> exit [oggadm1@host01 ogg01]

Both host01 and host02 should have the same rows. (If you are curious, the reason the row order is different is because the two rows you inserted went into the "holes" left by earlier deletes and rollbacks that never got replicated.)

This does not take into account the possibility of collision detection and resolution (CDR), which is beyond the scope of this OBE.

 

Summary

Oracle GoldenGate can do far more than was demonstrated in this simple exercise.
In this tutorial, you have learned how to:

• Install and configure the Oracle GoldenGate software
• Configure and start Extract, Data Pump, and Replicat processes
• Generate sample data and test the validity of the replication
• Manage the reports produced by the processes

Resources

Help topics relevant to the topic of this tutorial:

• Oracle GoldenGate Product Documentation version 11.2.1 (E35209-01) and other older versions
• Courses from Oracle University
• External Web sites for related information
• To learn more about Oracle GoldenGate, refer to additional OBEs in the Oracle Learning Library

Credits

• Lead Curriculum Developer: Steve Friedberg.
• Other Contributors: Richard Johnston, Hadi Koesnodihardjo, Simon Whitworth, Joe deBuzna, Chris Lawless.