Oracle GoldenGate on Linux: Heterogeneous Replication from Oracle 11g to DB2 9.7 and vice versa
Overview
- 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 logs. This OBE will not use an Initial Load.
- Extract:
- Runs on the source to capture transaction data to trail files.
- 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.
- 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.
- 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 GoldenGate on DB2 9.7, Linux-64 ( Oracle Software Delivery Cloud ), version 11.2.1.0.1, part number V32402-01. This is the part number for 64-bit Linux.
- Oracle 11gR2 Database installed on the Red host.
- DB2 9.7 Database installed on the Blue host
- Oracle and DB2 DDL and DML files needed by this OBE, which
can be downloaded here.
- 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=ogg11r2 on host01 (Red host,) and two databases GGS_SRC and GGS_TRG on host02 (Blue host.)
- 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.
- 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/).
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 hosts running the Oracle 11g RDBMS and the DB2 Release 9.7 RDBMS.
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:
Scenario
There are two Linux hosts (or two Virtual Machines) running a 64 bit version of Linux. One (the red host, host01) is running Oracle 11g. The other (the blue host, host02) is running DB2 9.7. Host01 has a user/schema src_user01 with a password of Welcome1. The user/schema src_user01 is used when Oracle is a replication source. An additional user/schema trg_user01 with a password of Welcome1 exists The user/schema trg_user01 is used when Oracle is a replication target.
Host02 has two databases, OGG_SRC, used when DB2 is a replication source and GGS_TRG, used when DB2 is a replication target. There are three tables, defined in the source environments (both Oracle and DB2) (ECONOMIC_ENTITY, GDP_BY_YEAR and GDP_GROWTH_BY_YEAR) containing world economic data, which need to be replicated to their respective target environments.
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).
In summary, the environment is:
| Host Name | Color | OS | SID/DB Name | User | Password | Mgr Port Source | Mgr Port Target | Default Directory |
|---|---|---|---|---|---|---|---|---|
| host01 | Red | Linux 64-bit | ogg11r2 | user01 | Welcome1 | 7809 | 7909 | /home/user01 |
| host02 | Blue | Linux 64-bit | orcl02 | user01 | Welcome1 | 7809 | 7909 | /home/user01 |
Both Oracle and DB2 have been installed in "standard" locations. IBM DB2 has been installed in /opt/ibm/db2/V9.7 and Oracle 11gR2 has been installed in /u01/app/oracle/product/11.2.0/dbhome_1.
The user "user01" in the Red host (Oracle) has the following directory structure
| Directory | Purpose |
|---|---|
| /home/user01 | Default Directory |
| /home/user01/oracle | Oracle Replication Software |
| /home/user01/oracle/ggs_src | Oracle GoldenGate installation for Replication Source |
| /home/user01/oracle/ggs_trg | Oracle GoldenGate installation for Replication Target |
The user "user01" in the Blue host (DB2) has the following directory structure
| Directory | Purpose |
|---|---|
| /home/user01 | Default Directory |
| /home/user01/db2 | DB2 Replication Software |
| /home/user01/db2/ggs_src | Oracle GoldenGate installation for Replication Source |
| /home/user01/db2/ggs_trg | Oracle GoldenGate installation for Replication Target |
Alternative Configuration
This OBE can also be run on a single host, where both the Oracle RDBMS and the DB2 RDBMS are installed.
In summary, the environment is:
| Host Name | Color | OS | SID | User | Password | Mgr Port Source | Mgr Port Target | Default Directory |
|---|---|---|---|---|---|---|---|---|
| host01 | Red/Blue | Linux 64-bit | ogg11r2 | user01 | Welcome1 | 7809 | 7909 | /home/user01 |
The user "user01" in the Single host (Red/Blue) has the following directory structure
| Directory | Purpose |
|---|---|
| /home/user01 | Default Directory |
| /home/user01/db2 | DB2 Replication Software |
| /home/user01/db2/ggs_src | Oracle GoldenGate installation for DB2 Replication Source |
| /home/user01/db2/ggs_trg | Oracle GoldenGate installation for DB2 Replication Target |
| /home/user01/oracle | Oracle Replication Software |
| /home/user01/oracle/ggs_src | Oracle GoldenGate installation for Oracle Replication Source |
| /home/user01/oracle/ggs_trg | Oracle GoldenGate installation for Oracle Replication Target |
When a single host simulates two environments it may be helpful
to change the /etc/hosts
file to make aliases for host01.example.com
and host02.example.com.
Furthermore, in a single host environment the user user01 must
be able to run both sqlplus
and db2. The
environment variable LD_LIBRARY_PATH
must therefore include reference to both Oracle and DB2
libraries:
[user01@host1 ~]$ echo $LD_LIBRARY_PATH
/u01/app/oracle/product/11.2.0/dbhome_1/lib:/home/db2inst1/sqllib/lib64:/home/db2inst1/sqllib/lib32
Similarly, the PATH
environment variable must be set to include paths to both Oracle
and DB2 executables:
[user01@host1 ~]$ echo $PATH
/home/db2inst1/sqllib/db2tss/bin:/home/db2inst1/sqllib/misc:/home/db2inst1/sqllib/adm:/home/db2inst1/sqllib/bin:/u01/app/oracle
/product/11.2.0/dbhome_1/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/user01/bin:/home/user01/bin
Hardware and Software Requirements
The following is a list of hardware and software requirements:
Hardware
Software
Prerequisites
Before starting this tutorial, you should:
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:
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:
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.
Click the Back browser button.
Select Oracle GoldenGate for Non Oracle Database v11.2.1 Media Pack for Linux x86-64.
Select Oracle GoldenGate v11.2.1 for DB2 9.7 on Linux x86-64.
Make sure you are looking at part number V32402-01
Click Download.
Copy or use FTP to move the V32402-01.zip file to the Blue Host (DB2)
Unpack the software in its directory on host01 (red).
Create the installation directories to receive the Oracle GoldenGate software.
| Host01 - Linux |
[user01@host01 ~]$ cd ~ [user01@host01 ~]$ mkdir -p oracle/ggs_src [user01@host01 ~]$ mkdir -p oracle/ggs_trg [user01@host01 ~]$ cd ~/oracle/ggs_src [user01@host01 ggs_src]$ |
Copy the downloaded zip from wherever you downloaded it to ~/oracle/ggs_src/. Unzip it and untar (extract) it.
| Host01 - Linux |
[user01@host01 ggs_src]$ cp /stage/V34339-01.zip . [user01@host01 ggs_src]$ 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 [user01@host01 ggs_src]$ 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 [user01@host01 ggs_src]$ |
Be mindful of the trailing dot in the copy command.
Start the GoldenGate Software Command Interface
(GGSCI). Create the default empty subdirectories.
IMPORTANT! GGSCI for Oracle
will not start if the LD_LIBRARY_PATH
environment variable is either undefined or if it does
not point to the ${ORACLE_HOME}/lib
directory.
Make sure LD_LIBRARY_PATH is correctly set before launching GGSCI.
| Host01 - Linux |
[user01@host01 ggs_src]$ ./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 /home/user01/oracle/ggs_src Parameter files /home/user01/oracle/ggs_src/dirprm: already exists Report files /home/user01/oracle/ggs_src/dirrpt: created Checkpoint files /home/user01/oracle/ggs_src/dirchk: created Process status files /home/user01/oracle/ggs_src/dirpcs: created SQL script files /home/user01/oracle/ggs_src/dirsql: created Database definitions files /home/user01/oracle/ggs_src/dirdef: created Extract data files /home/user01/oracle/ggs_src/dirdat: created Temporary files /home/user01/oracle/ggs_src/dirtmp: created Stdout files /home/user01/oracle/ggs_src/dirout: created GGSCI (host01) 2> Exit [user01@host01 ggs_src]$ |
If a directory already exists, the installation leaves the contents of that directory alone.
Do the same steps for the target replication directory (ggs_trg) host. Unpack the software in its directory (ggs_trg).
Change directory to the replication target directory to receive the Oracle GoldenGate software.
| Host01 - Linux |
[user01@host01 ~]$ cd ~ [user01@host01 ~]$ cd oracle/ggs_trg [user01@host01 ggs_trg]$ |
Copy the downloaded zip from wherever you downloaded it to ggs_trg/. Unzip it and untar (extract) it.
| Host01 - Linux |
[user01@host01 ggs_trg]$ cp /stage/V34339-01.zip . [user01@host01 ggs_trg]$ 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 [user01@host01 ggs_trg]$ 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 [user01@host01 ggs_trg]$ |
Start the GoldenGate Software Command Interface (GGSCI). Create the default empty subdirectories.
| Host01 - Linux |
[user01@host01 ggs_trg]$ ./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 /home/user01/oracle/ggs_trg Parameter files /home/user01/oracle/ggs_trg/dirprm: already exists Report files /home/user01/oracle/ggs_trg/dirrpt: created Checkpoint files /home/user01/oracle/ggs_trg/dirchk: created Process status files /home/user01/oracle/ggs_trg/dirpcs: created SQL script files /home/user01/oracle/ggs_trg/dirsql: created Database definitions files /home/user01/oracle/ggs_trg/dirdef: created Extract data files /home/user01/oracle/ggs_trg/dirdat: created Temporary files /home/user01/oracle/ggs_trg/dirtmp: created Stdout files /home/user01/oracle/ggs_trg/dirout: created GGSCI (host01) 2> Exit [user01@host01 ggs_trg]$ |
If a directory already exists, the installation
leaves the contents of that directory alone. You have
successfully installed Oracle GoldenGate
on the Red Host for both the source and target
GoldenGate instances.
You must now install the Oracle GoldenGate software on the Blue Host (DB2.)
Connect to the Blue Host (host02) as "user01"
Create the installation directory to receive the Oracle GoldenGate software.
| Host02 - Linux |
[user01@host02 ~]$ cd ~ [user01@host02 ~]$ mkdir -p db2/ggs_src [user01@host02 ~]$ mkdir -p db2/ggs_trg [user01@host02 ~]$ cd ~/db2/ggs_src [user01@host02 ggs_src]$ |
Copy the downloaded zip from wherever you downloaded it to ~/db2/ggs_src/. Unzip it and untar (extract) it.
| Host02 - Linux |
[user01@host02 ggs_src]$ cp /stage/V32402-01.zip . [user01@host02 ggs_src]$ unzip V32402-01.zip Archive: V32402-01.zip inflating: ggs_Linux_x64_db297_64bit.tar inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf inflating: Oracle GoldenGate 11.2.1.0.1 README.txt inflating: Oracle GoldenGate 11.2.1.0.1 README.doc [user01@host02 ggs_src]$ tar -xvpf ggs_Linux_x64_db297_64bit.tar ./ ./chkpt_db2_create.sql ./demo_db2_create.sql ./extract ./server ./sqlldr.tpl ./zlib.txt ... many lines omitted for clarity ... ./UserExitExamples/ExitDemo/readme.txt ./freeBSD.txt ./convchk ./retrace ./dirprm/ ./dirprm/jagent.prm [user01@host02 ggs_src]$ |
Be mindful of the trailing dot in the copy command.
Start the GoldenGate Software Command Interface (GGSCI). Create the default empty subdirectories.
| Host02 - Linux |
[user01@host02 ggs_src]$ ./ggsci Oracle GoldenGate Command Interpreter for DB2 Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230 Linux, x64, 64bit (optimized), DB2 9.7 on Apr 23 2012 06:02:54 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (host02) 1> Create Subdirs Creating subdirectories under current directory /home/user01/db2/ggs_src Parameter files /home/user01/db2/ggs_src/dirprm: already exists Report files /home/user01/db2/ggs_src/dirrpt: created Checkpoint files /home/user01/db2/ggs_src/dirchk: created Process status files /home/user01/db2/ggs_src/dirpcs: created SQL script files /home/user01/db2/ggs_src/dirsql: created Database definitions files /home/user01/db2/ggs_src/dirdef: created Extract data files /home/user01/db2/ggs_src/dirdat: created Temporary files /home/user01/db2/ggs_src/dirtmp: created Stdout files /home/user01/db2/ggs_src/dirout: created GGSCI (host02) 2> Exit [user01@host02 ggs_src]$ |
If a directory already exists, the installation leaves the contents of that directory alone.
Do the same steps for the target replication directory (ggs_trg) host. Unpack the software in its directory (ggs_trg).
Create the installation directory to receive the Oracle GoldenGate software.
| Host02 - Linux |
[user01@host02 ~]$ cd ~ [user01@host02 ~]$ cd db2/ggs_trg [user01@host02 ggs_trg]$ |
Copy the downloaded zip from wherever you downloaded it to ggs_trg/. Unzip it and untar (extract) it.
| Host02 - Linux |
[user01@host01 ggs_trg]$ cp /stage/V32402-01.zip . [user01@host02 ggs_trg]$ unzip V32402-01.zip Archive: V32402-01.zip inflating: ggs_Linux_x64_db297_64bit.tar inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf inflating: Oracle GoldenGate 11.2.1.0.1 README.txt inflating: Oracle GoldenGate 11.2.1.0.1 README.doc [user01@host01 ggs_trg]$ tar -xvpf ggs_Linux_x64_db297_64bit.tar ./ ./chkpt_db2_create.sql ./demo_db2_create.sql ./extract ./server ./sqlldr.tpl ./zlib.txt ... many lines omitted for clarity ... ./UserExitExamples/ExitDemo/readme.txt ./freeBSD.txt ./convchk ./retrace ./dirprm/ ./dirprm/jagent.prm [user01@host02 ggs_trg]$ |
Start the GoldenGate Software Command Interface (GGSCI). Create the default empty subdirectories.
| Host02 - Linux |
[user01@host02 ggs_trg]$ ./ggsci Oracle GoldenGate Command Interpreter for DB2 Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230 Linux, x64, 64bit (optimized), DB2 9.7 on Apr 23 2012 06:02:54 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (host02) 1> Create Subdirs Creating subdirectories under current directory /home/user01/db2/ggs_trg Parameter files /home/user01/db2/ggs_trg/dirprm: already exists Report files /home/user01/db2/ggs_trg/dirrpt: created Checkpoint files /home/user01/db2/ggs_trg/dirchk: created Process status files /home/user01/db2/ggs_trg/dirpcs: created SQL script files /home/user01/db2/ggs_trg/dirsql: created Database definitions files /home/user01/db2/ggs_trg/dirdef: created Extract data files /home/user01/db2/ggs_trg/dirdat: created Temporary files /home/user01/db2/ggs_trg/dirtmp: created Stdout files /home/user01/db2/ggs_trg/dirout: created GGSCI (host02) 2> Exit [user01@host02 ggs_trg]$ |
You have successfully installed Oracle GoldenGate on the Blue Host for both the source and target GoldenGate instances.
2. Creating and Preparing the Databases
On the Red Host (Oracle) you must create the OGG11R2 instance using theDBCA utility.
You will create the src_user01 and the trg_user01 users/schemas.
On the Blue Host (DB2) you must create the GGS_SRC and GGS_TRG databases
After the Oracle schemas and the DB2 databases are created you must run some DDL scripts to create the required replication objects.
Creating Directories for Database Files
On both hosts you must create the containers for the database files.
| Host01 - Linux |
[root@host01 ~]# mkdir -p /u02/oradata [root@host01 ~]# chown -R oracle.oinstall /u02/oradata |
| Host02 - Linux |
[root@host02 ~]# mkdir -p /u02/db2/GGS_SRC [root@host02 ~]# mkdir -p /u02/db2/GGS_TRG [root@host02 ~]# chown -R db2inst1.dasadm1 /u02/db2 |
| Host02 - Linux |
[root@host02 ~]# mkdir -p /u02/db2_backups/GGS_SRC [root@host02 ~]# mkdir -p /u02/db2_backups/GGS_TRG [root@host02 ~]# chown -R db2inst1.dasadm1 /u02/db2_backups |
2.1 Creating the Oracle OGG11R2 Instance
The database creation step starts. After a while a
"Database Configuration Feedback" page is displayed on
screen stating that the database has been correctly
configured.
Click Exit.
| Host01 - Linux |
[oracle@host01 ~] cd /u02/oradata/ogg11r2/ [user01@host01 ~]$ ls -l total 1725748 -rw-r-----. 1 oracle oinstall 9781248 Oct 15 10:50 control01.ctl -rw-r-----. 1 oracle oinstall 52429312 Oct 14 15:04 redo01.log -rw-r-----. 1 oracle oinstall 52429312 Oct 15 02:00 redo02.log -rw-r-----. 1 oracle oinstall 52429312 Oct 15 10:48 redo03.log -rw-r-----. 1 oracle oinstall 754982912 Oct 15 10:40 sysaux01.dbf -rw-r-----. 1 oracle oinstall 754982912 Oct 15 10:46 system01.dbf -rw-r-----. 1 oracle oinstall 30416896 Oct 14 23:04 temp01.dbf -rw-r-----. 1 oracle oinstall 78651392 Oct 15 10:46 undotbs01.dbf -rw-r-----. 1 oracle oinstall 5251072 Oct 15 05:15 users01.dbf [oracle@host01 ogg11r2]$ chmod +r *.log [oracle@host01 ogg11r2]$ ls -l total 1725748 -rw-r-----. 1 oracle oinstall 9781248 Oct 15 10:50 control01.ctl -rw-r--r--. 1 oracle oinstall 52429312 Oct 14 15:04 redo01.log -rw-r--r--. 1 oracle oinstall 52429312 Oct 15 02:00 redo02.log -rw-r--r--. 1 oracle oinstall 52429312 Oct 15 10:50 redo03.log -rw-r-----. 1 oracle oinstall 754982912 Oct 15 10:50 sysaux01.dbf -rw-r-----. 1 oracle oinstall 754982912 Oct 15 10:46 system01.dbf -rw-r-----. 1 oracle oinstall 30416896 Oct 14 23:04 temp01.dbf -rw-r-----. 1 oracle oinstall 78651392 Oct 15 10:50 undotbs01.dbf -rw-r-----. 1 oracle oinstall 5251072 Oct 15 05:15 users01.dbf [oracle@oraol62 ogg11r2]$ |
Make sure the user user01 can access the Oracle Redo log files. Open a terminal window, connect to the Red Host as user01 and use the command ls to display the content of the directory /u02/oradata/ogg11r2.
You must now set up the Oracle Listener (if a
listener is not already created) and the TNS
entry for the newly created database. Launch the NETCA application
by typing netca
at the OS prompt.
The Oracle Net Configuration Assistant wizard dialog
window will appear on screen.
Select Listener configuration and click Next. If the only option available is Add and the other options such as Reconfigure, Delete and Rename are greyed out, it means that no listener was created. In this case accept the only available option, Add, and click Next.
Otherwise, if all the other options are available, (which means a listener has been configured) click on Reconfigure and make sure that a listener named LISTENER has been configured:
If the listener named LISTENER appears in the drop-down list box, you can click on the Back button until you reach the initial screen, which allows you to choose the option Local Net Service Name Configuration.
If the listener LISTENER
does not exist, proceed to create it. If the listener
LISTENER does
exist, skip the steps below until you choose the
option Local
Net Service Name Configuration.
Click Listener Configuration then click Add. Enter LISTENER in the Listener name field and click Next.
Select TCP as the listener protocol and click Next.
Accept the default port number (1521) to be used by the listener named LISTENER. Click Next
Accept the default option (No) when asked if you want to create another listener. Click Next
You are back to the initial screen, which allows you to choose the option Local Net Service Name Configuration. Click Next
Select Add to create a new net service name. Click Next
Enter ogg11r2 as the new service name. Click Next
Select TCP as the protocol used by ogg11r2. Click Next
Enter host01 as the Host name for the service name ogg11r2. Accept the default (1521) for the TCP/IP port number. Click Next
Accept the default (No do not test) to skip the database test. Click Next
Enter ogg11r2 to accept the new Net service Name. Click Next
Accept the default (No) to opt not to configure another service name and click Next
The Net Service Name Configuration wizard is complete. Click Next then click Finish to exit the configuration wizard.
Logout from the "oracle" account
2.2 Creating the DB2 Databases GGS_SRC and GGS_TRG
IMPORTANT! The
databases just created are automatically put by DB2 in
"backup pending" mode.
This means that you will not be able to connect to
those databases until a backup has been performed. You
can use Control
Center to backup the two databases GGS_SRC
and GGS_TRG.
Use the directories you created previously (/u02/db2_backups/GGS_SRC
and /u02/db2_backups/GGS_SRC)
to store the backups of the databases.
The Path Browser dialog window pops up. Navigate to the /u02/db2_backups/GGS_SRC directory and click Ok.
The database backup starts.
Repeat the same steps to backup the GGS_TRG database.
The DB2 source replication database, GGS_SRC, will be accessed by the user user01. You must grant the DBADM authority to the user01 user.
In Control Center right-click on the GGS_SRC database node when the pop-up window appears, click Authorities... When the Database Authorities window appears, click Add User... and select the user user01 from the drop down list box.
Click the drop down list box placed under the DBADM autority and select Yes to grant DBADM to the user01 user.
Logout from the "db2inst1" account
2.3 Unloading and Unpacking the SQL scripts
On both hosts, create a ddl directory under the default directory and expand the OBE_DDL_files.zip there.
In this example the OBE_DDL_files.zip file is downloaded into the /tmp directory.
| Host01 - Linux |
[user01@host01 ~] cd ~ [user01@host01 ~]$ mkdir ddl [user01@host01 ~]$ cd ddl [user01@host01 ddl]$ unzip /tmp/OBE_DDL_files.zip Archive: /tmp/OBE_DDL_files.zip inflating: oracle_table_creation.sql inflating: db2_table_creation.sql inflating: drop_tables.sql inflating: economic_entity.sql inflating: gdp_by_year.sql inflating: gdp_by_year_2008.sql inflating: gdp_by_year_2009.sql inflating: gdp_by_year_2010.sql inflating: gdp_growth_by_year.sql inflating: gdp_growth_by_year_2008.sql inflating: gdp_growth_by_year_2009.sql inflating: gdp_growth_by_year_2010.sql |
Repeat the same step on the Blue Host
| Host02 - Linux |
[user01@host02 ~] cd ~ [user01@host02 ~]$ mkdir ddl [user01@host02 ~]$ cd ddl [user01@host02 ddl]$ unzip /tmp/OBE_DDL_files.zip Archive: /tmp/OBE_DDL_files.zip inflating: oracle_table_creation.sql inflating: db2_table_creation.sql inflating: drop_tables.sql inflating: economic_entity.sql inflating: gdp_by_year.sql inflating: gdp_by_year_2008.sql inflating: gdp_by_year_2009.sql inflating: gdp_by_year_2010.sql inflating: gdp_growth_by_year.sql inflating: gdp_growth_by_year_2008.sql inflating: gdp_growth_by_year_2009.sql inflating: gdp_growth_by_year_2010.sql |
3. Configuring the Environment
- 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.
The configuration of the environment is done by editing ASCII files and running OS utilities. To configure the environment, perform the following steps:
3.1 Configuring the Oracle 11g Database on the Red Host
This section must be done in SQL*Plus as sysdba.
On host01, verify that LOG_MODE is set to ARCHIVELOG.
| Host01 - Linux |
[user01@host01 ~]$ 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. You specified ARCHIVELOG mode when you created the database using DBCA. If you did not create the database in ARCHIVELOG mode, 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 Oracle GoldenGate Administrator and the 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_AUTH command, the entire string after EXEC is without spaces or line breaks.
| Host01 - Linux |
SQL> CREATE USER src_user01 IDENTIFIED BY Welcome1; User created. SQL> CREATE USER trg_user01 IDENTIFIED BY Welcome1; User created. SQL> CREATE USER oggadm IDENTIFIED BY Welcome1; User created. SQL> GRANT CONNECT, RESOURCE, UNLIMITED TABLESPACE TO src_user01; Grant succeeded. SQL> GRANT CONNECT, RESOURCE, UNLIMITED TABLESPACE TO trg_user01; Grant succeeded. SQL> GRANT dba TO oggadm; Grant succeeded. SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE (grantee=>'OGGADM',privile ge_type=>'capture',grant_select_privileges=>true, do_grants=>TRUE); PL/SQL procedure successfully completed. SQL> exit [user01@host01 ~]$ |
The database on host01 is configured to support Oracle Goldengate. One user and an administrator are provisioned for the Red Host.
3.2 Configuring the DB2 9.7 Database on the Blue Host
This section must be done using the DB2 command line utility db2.
On host02, verify that the LOGRETAIN configuration parameter is set to RECOVERY and that the USEREXIT configuration parameter is set to ON for the GGS_SRC database.
Connect to the Blue Host as db2inst1 to change the database parameters. The user user01 cannot modify database parameters unless it is granted SYSADM authority.
| Host02 - Linux |
[db2inst1@host02 ~]$ db2 get db cfg for ggs_src | grep -i logretain Log retain for recovery enabled (LOGRETAIN) = OFF [db2inst1@host02 ~]$ db2 get db cfg for ggs_src | grep -i userexit User exit for logging enabled (USEREXIT) = OFF [db2inst1@host02 ~]$ db2 update db cfg for ggs_src using logretain recovery |
3.3 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 |
[user01@host01 ~]$ cd ~/oracle/ggs_trg [user01@host01 ggs_trg]$ vi GLOBALS CheckpointTable oggadm.oggchkpt [user01@host01 ggs_trg]$ |
Checkpoint tables are only used by the Replicat, so there is no need to create a check point table in the source Oracle GoldenGate instance (ggs_src.)
Create the GLOBALS file on the Blue Host (host02) in the Oracle GoldenGate installation directory for replication target (ggs_trg.)
| Host02 - Linux |
[user01@host02 ~]$ cd ~/db2/ggs_trg [user01@host02 ggs_trg]$ vi GLOBALS CheckpointTable user01.oggchkpt [user01@host02 ggs_trg]$ |
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 |
[user01@host01 ~] cd ~/oracle/ggs_src [user01@host01 ggs_src]$ vi startup.oby DBLogin UserID oggadm@ogg11r2, Password Welcome1 Start Mgr Info Mgr Info CheckpointTable Set Editor gedit [user01@host01 ggs_src]$ |
Repeat the step for the OBEY file in the directory for the replication target (ggs_trg.)
| Host01 - Linux |
[user01@host01 ~]$ cd ~/oracle/ggs_trg [user01@host01 ggs_trg]$ vi startup.oby DBLogin UserID oggadm@ogg11r2, Password Welcome1 Start Mgr Info Mgr Info CheckpointTable Set Editor gedit [user01@host01 ggs_trg]$ |
Create the optional startup.oby (Obey) file on the Blue Host (host02) in both Oracle GoldenGate installation directories (ggs_src and ggs_trg.)
| Host02 - Linux |
[user01@host02 ~]$ cd ~/db2/ggs_src [user01@host02 ggs_src]$ vi startup.oby DBLogin SourceDb ggs_src, UserID user01, Password Welcome1 Start Mgr Info Mgr Info CheckpointTable Set Editor gedit [user01@host02 ggs_src]$ |
Repeat the same step for the replication target directory (ggs_trg.)
| Host02 - Linux |
[user01@host02 ~]$ cd ~/db2/ggs_trg [user01@host02 ggs_trg]$ vi startup.oby DBLogin SourceDb ggs_trg, UserID user01, Password Welcome1 Start Mgr Info Mgr Info CheckpointTable Set Editor gedit [user01@host02 ggs_trg]$ |
Create the Manager parameter (mgr.prm) file on host01 in dirprm/.
Start GGSCI. Edit the file with no extension. Add the two lines.
| Host01 - Linux |
[user01@host01 ~]$ cd ~/oracle/ggs_src [user01@host01 ggs_src]$ ./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 Port 7809 PurgeOldExtracts ./dirdat/*, UseCheckpoints GGSCI (host01) 2> Info mgr Manager is DOWN! GGSCI (host01) 3> exit [user01@host01 ggs_src]$ |
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 file for the replication target instance, in the ggs_trg directory.
| Host01 - Linux |
[user01@host01 ggs_src]$ cd ~/oracle/ggs_trg [user01@host01 ggs_trg]$ ./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 Port 7909 PurgeOldExtracts ./dirdat/*, UseCheckpoints GGSCI (host01) 2> Info mgr Manager is DOWN! GGSCI (host01) 3> exit [user01@host01 ggs_trg]$ |
Create the Manager parameter (mgr.prm) file on the Blue Host (host02) in dirprm/ for both replication source and replication target directories.
| Host02 - Linux |
[user01@host02 ~]$ cd ~/db2/ggs_src [user01@host02 ggs_src]$ ./ggsci Oracle GoldenGate Command Interpreter for DB2 Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230 Linux, x64, 64bit (optimized), DB2 9.7 on Apr 23 2012 06:02:54 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. |
Now, it is the replication target turn:
| Host02 - Linux |
[user01@host02 ggs_src]$ cd ~/db2/ggs_trg [user01@host02 ggs_trg]$ ./ggsci Oracle GoldenGate Command Interpreter for DB2 Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230 Linux, x64, 64bit (optimized), DB2 9.7 on Apr 23 2012 06:02:54 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (host02) 1> Edit Param mgr Port 7909 PurgeOldExtracts ./dirdat/*, UseCheckpoints GGSCI (host02) 2> Info mgr Manager is DOWN! GGSCI (host02) 3> [user01@host01 ggs_trg]$ |
Start the Manager on the Red Host, replication source instance, using the Obey files. Alternatively, you could type each of the lines every time you start GGSCI.
| Host01 - Linux |
[user01@host01 ~]$ cd ~/oracle/ggs_src [user01@host01 ggs_src]$ ./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> Obey startup.oby GGSCI (host01) 2> DBLogin UserID oggadm@ogg11r2, Password Welcome1 Successfully logged into database. GGSCI (host01) 3> Start Mgr Manager started. GGSCI (host01) 4> Info Mgr Manager is running (IP port host01.example.com.7809). GGSCI (host01) 5> Info CheckpointTable No checkpoint table specified, using GLOBALS specification (oggadm.oggchkpt)... |
Start the Manager for the replication target instance.
| Host01 - Linux |
[user01@host01 ggs_src]$ cd ~/oracle/ggs_trg [user01@host01 ggs_trg]$ ./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> Obey startup.oby GGSCI (host01) 2> DBLogin UserID oggadm@ogg11r2, Password Welcome1 Successfully logged into database. GGSCI (host01) 3> Start Mgr Manager started. GGSCI (host01) 4> Info Mgr Manager is running (IP port host01.example.com.7909). GGSCI (host01) 5> Info CheckpointTable No checkpoint table specified, using GLOBALS specification (oggadm.oggchkpt)... Checkpoint table oggadm.oggchkpt does not exist. GGSCI (host01) 6> Set Editor gedit GGSCI (host01) 7> exit [user01@host01 ggs_trg]$ |
Start the Manager on the Blue Host (host02) for the replication source using the Obey files.
IMPORTANT! If
you are running this OBE ona single host simulating
two environments, you must first stop the Oracle
GoldenGate Manager process for the two Oracle
instances
(directories
/home/user01/oracle/ggs_src and /home/user01/oracle/ggs_trg.)
If you are running this OBE on two different hosts,
you don't need to stop the two Manager processes on
the Red Host.
| Host02 - Linux |
[user01@host02 ~]$ cd ~/db2/ggs_src [user01@host02 ggs_src]$ ./ggsci Oracle GoldenGate Command Interpreter for DB2 Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230 Linux, x64, 64bit (optimized), DB2 9.7 on Apr 23 2012 06:02:54 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (host02) 1> Obey startup.oby GGSCI (host02) 2> DBLogin SourceDb ggs_src, UserID user01, Password Welcome1 Successfully logged into database. GGSCI (host02) 3> Start Mgr Manager started. GGSCI (host02) 4> Info Mgr Manager is running (IP port host02.example.com.7809). GGSCI (host02) 5> Info CheckpointTable No checkpoint table specified, using GLOBALS specification (user01.oggchkpt)... |
Start the Manager on the Blue Host (host02) for the replication target using the Obey files.
| Host02 - Linux |
[user01@host02 ggs_src]$ cd ~/db2/ggs_trg [user01@host02 ggs_trg]$ ./ggsci Oracle GoldenGate Command Interpreter for DB2 Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230 Linux, x64, 64bit (optimized), DB2 9.7 on Apr 23 2012 06:02:54 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (host02) 1> Obey startup.oby GGSCI (host02) 2> DBLogin SourceDb ggs_trg, UserID user01, Password Welcome1 Successfully logged into database. GGSCI (host02) 3> Start Mgr Manager started. GGSCI (host02) 4> Info Mgr Manager is running (IP port host02.example.com.7909). GGSCI (host02) 5> Info CheckpointTable No checkpoint table specified, using GLOBALS specification (user01.oggchkpt)... Checkpoint table user01.oggchkpt does not exist. GGSCI (host02) 6> Set Editor gedit GGSCI (host02) 7> exit [user01@host02 ggs_trg]$ |
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
3.4 Creating Tables
Create the checkpoint table on host01. This is not needed on the replication source instance, so you only need to create the checkpoint table on the replication target Oracle GoldenGate instance.
| Host01 - Linux |
[user01@host01 ~] cd ~/oracle/ggs_trg [user01@host01 ggs_trg]$ ./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> DBLogin UserID oggadm@ogg11r2, Password Welcome1 Successfully logged into database. GGSCI (host01) 2> Add CheckpointTable No checkpoint table specified, using GLOBALS specification (oggadm.oggchkpt). Successfully created checkpoint table oggadm.oggchkpt. GGSCI (host01) 3> Exit [user01@host01 ggs_trg]$ |
One checkpoint table will suffice for the whole schema.
Create the checkpoint table on the Blue Host (host02.)
| Host02 - Linux |
[user01@host02 ~]$ cd ~/db2/ggs_trg [user01@host02 ggs_trg]$ ./ggsci Oracle GoldenGate Command Interpreter for DB2 Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230 Linux, x64, 64bit (optimized), DB2 9.7 on Apr 23 2012 06:02:54 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (host02) 1> DBLogin SourceDb ggs_trg, UserID user01, Password Welcome1 Successfully logged into database. GGSCI (host02) 2> Add CheckpointTable No checkpoint table specified, using GLOBALS specification (user01.oggchkpt). Successfully created checkpoint table user01.oggchkpt. GGSCI (host02) 2> Exit [user01@host02 ggs_trg]$ |
Create empty source and target sample tables on host01 in the schemas src_user01 and trg_user01.
You can use any SQL utility you like to run the script. If you have no preference, use sqlplus.
| Host01 - Linux |
[user01@host01 ~] cd ~/ddl [user01@host01 ddl]sqlplus src_user01/Welcome1@ogg11r2 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 SQL> @oracle_table_creation.sql Table created. Table created. Table created. SQL> select * from cat; TABLE_NAME TABLE_TYPE ------------------------------ ----------- ECONOMIC_ENTITY TABLE GDP_BY_YEAR TABLE GDP_GROWTH_BY_YEAR TABLE SQL> connect trg_user01/Welcome1@ogg11r2 Connected. SQL> @oracle_table_creation.sql Table created. Table created. Table created. SQL> select * from cat; TABLE_NAME TABLE_TYPE ------------------------------ ----------- ECONOMIC_ENTITY TABLE GDP_BY_YEAR TABLE GDP_GROWTH_BY_YEAR TABLE SQL> exit [user01@host01 ddl]$ |
Create empty source and target sample tables on the Blue Host (host02) in databases ggs_src and ggs_trg.
| Host02 - Linux |
[user01@host02 ~] cd ~/ddl [user01@host02 ddl]$ db2 connect to ggs_src user user01 using Welcome1 Database Connection Information Database server = DB2/LINUXX8664 9.7.5 SQL authorization ID = USER01 Local database alias = GGS_SRC [user01@host02 ddl]$ ls drop_tables.sql gdp_by_year_2008.sql gdp_by_year_2010.sql gdp_growth_by_year_2008.sql |
3.5 Adding Transaction Data
Restart GGSCI and run Add TranData for the whole user schema used as a replication source (ggs_src.)
| Host01 - Linux |
[user01@host01 ~]$ cd ~/oracle/ggs_trg [user01@host01 ggs_trg]$ ./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> dblogin userid oggadm@ogg11r2, password Welcome1 Successfully logged into database. GGSCI (host01) 2> info trandata src_user01.* Logging of supplemental redo log data is disabled for table SRC_USER01.ECONOMIC_ENTITY. Logging of supplemental redo log data is disabled for table SRC_USER01.GDP_BY_YEAR. Logging of supplemental redo log data is disabled for table SRC_USER01.GDP_GROWTH_BY_YEAR. GGSCI (host01) 3> add trandata src_user01.* Logging of supplemental redo data enabled for table SRC_USER01.ECONOMIC_ENTITY. Logging of supplemental redo data enabled for table SRC_USER01.GDP_BY_YEAR. Logging of supplemental redo data enabled for table SRC_USER01.GDP_GROWTH_BY_YEAR. GGSCI (host01) 4> info trandata src_user01.* Logging of supplemental redo log data is enabled for table SRC_USER01.ECONOMIC_ENTITY. Columns supplementally logged for table SRC_USER01.ECONOMIC_ENTITY: ENTITY_ID. Logging of supplemental redo log data is enabled for table SRC_USER01.GDP_BY_YEAR. Columns supplementally logged for table SRC_USER01.GDP_BY_YEAR: ENTITY_ID, GDP_YEAR. Logging of supplemental redo log data is enabled for table SRC_USER01.GDP_GROWTH_BY_YEAR. Columns supplementally logged for table SRC_USER01.GDP_GROWTH_BY_YEAR: ENTITY_ID, GDP_YEAR. GGSCI (host01) 5> |
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.
You must also add trandata information on the Blue Host (host02,) for the source replication schema (ggs_src.)
| Host02 - Linux |
[user01@host02 ~]$ cd ~/db2/ggs_src [user01@host02 ggs_src]$ ./ggsci Oracle GoldenGate Command Interpreter for DB2 Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230 Linux, x64, 64bit (optimized), DB2 9.7 on Apr 23 2012 06:02:54 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (host02) 1> dblogin sourcedb ggs_src, userid user01, password Welcome1 2012-10-02 15:37:36 INFO OGG-03036 Database character set identified as UTF-8. |
The transaction data has been enabled for all user tables involved with Oracle GoldenGate.
3.6 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 |
[user01@host01 ~]$ cd ~/oracle/ggs_src [user01@host01 ggs_src]$ ./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) > Edit Param usr1tabs -- defgen column definitions for src_user01.* from host01 (a) to host02 (b) DefsFile dirdef/usr1tabs.def, Purge UserID oggadm@ogg11r2, Password Welcome1 Table src_user01.*; GGSCI (host01) > Exit [user01@host01 ggs_src] ./defgen paramfile dirprm/usr1tabs.prm *********************************************************************** Oracle GoldenGate Table Definition Generator for Oracle ... many lines omitted for clarity ... DefsFile dirdef/usr1tabs.def, Purge UserID oggadm@ogg11r2, Password ******** Table src_user01.*; Expanding wildcard src_user01.*: Retrieving definition for SRC_USER01.ECONOMIC_ENTITY Retrieving definition for SRC_USER01.GDP_BY_YEAR Retrieving definition for SRC_USER01.GDP_GROWTH_BY_YEAR Definitions generated for 3 tables in dirdef/usr1tabs.def [user01@host01 ggs_src]$ |
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/usr1tabs.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 /home/user01/db2/ggs_trg, the scp command is entered all on one line.
| Host01 - Linux |
[user01@host01 ggs_src] scp dirdef/usr1tabs.def user01@host02:/home/user01/db2/ggs_trg
/dirdef
he authenticity of host 'host02 (127.0.0.1)' can't be established.
RSA key fingerprint is 82:e2:d9:21:9e:a7:9c:7d:d8:c9:cf:92:ee:80:13:9e.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'host02' (RSA) to the list of known hosts.
user01@host02's password:
usr1tabs.def 100% 1816 1.8KB/s 00:00
[user01@host01 ggs_src]$
|
Of course you replace the asterisks with your password, for example Welcome1.
Make sure the files arrived intact on host02 in the proper directory.
| Host02 - Linux |
[user01@host02 ~]$ cd ~/db2/ggs_trg [user01@host02 ggs_trg]$ ls -l dirdef total 4 -rw-r--r--. 1 user01 oggusers 1816 Oct 2 18:29 usr1tabs.def [user01@host02 ggs_trg]$ |
Create the column mapping from DB2 source to Oracle target tables using the OS utility.
| Host02 - Linux |
[user01@host02 ggs_trg]$ cd ~/db2/ggs_src [user01@host02 ggs_src]$ ./ggsci Oracle GoldenGate Command Interpreter for DB2 Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230 Linux, x64, 64bit (optimized), DB2 9.7 on Apr 23 2012 06:02:54 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (host02) > Edit Param usr1tabs -- defgen column definitions for user01.* from host02 (b) to host01 (a) DefsFile dirdef/usr1tabs.def, Purge SourceDB ggs_src, UserID user01, Password Welcome1 Table user01.*; GGSCI (host02) > Exit [user01@host01 ggs_src] ./defgen paramfile dirprm/usr1tabs.prm *********************************************************************** Oracle GoldenGate Table Definition Generator for DB2 ... many lines omitted for clarity ... DefsFile dirdef/usr1tabs.def, Purge SourceDB ggs_src, UserID user01, Password ******** 2012-10-02 18:44:48 INFO OGG-03036 Database character set identified as UTF-8. Locale: en_US. 2012-10-02 18:44:48 INFO OGG-03037 Session character set identified as UTF-8. Table user01.*; Expanding wildcard user01.*: Retrieving definition for USER01.ECONOMIC_ENTITY Retrieving definition for USER01.GDP_BY_YEAR Retrieving definition for USER01.GDP_GROWTH_BY_YEAR Definitions generated for 3 tables in dirdef/usr1tabs.def [user01@host02 ggs_src]$ |
Copy the files from source host02 to target host01.
| Host02 - Linux |
[user01@host02 ggs_src]$ scp dirdef/usr1tabs.def user01@host01:/home/user01/oracle/ggs_trg
/dirdef
The authenticity of host 'host02 (127.0.0.1)' can't be established.
RSA key fingerprint is 82:e2:d9:21:9e:a7:9c:7d:d8:c9:cf:92:ee:80:13:9e.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'host01' (RSA) to the list of known hosts.
user01@host01's password:
usr1tabs.def 100% 1816 1.8KB/s 00:00
[user01@host01 ggs_src]$
|
Of course you replace the asterisks with your password, for example Welcome1.
Make sure the files arrived intact on host01 in the proper directory.
| Host01 - Linux |
[user01@host01 ~]$ cd ~/oracle/ggs_trg [user01@host01 ggs_trg]$ ls -l dirdef total 4 -rw-r--r--. 1 user01 oggusers 1816 Oct 2 18:29 usr1tabs.def [user01@host01 ggs_trg]$ |
The column definitions are created and copied. If the table structure changes, you need to do this again.
4. Configuring Data Capture Using Extract (Oracle-to-DB2)
- -- :
- 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.
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:
4.1 Configuring the Primary Extract on the Oracle source instance
On source host01, create the primary Extract parameter file.
Using the naming conventions discussed in the Overview, the path/filename.ext will be dirprm/efinaa.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 |
[user01@host01 ~] cd ~/oracle/ggs_src [user01@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 efinaa -- Primary Extract from host01 to host02 -- SETENV(ORACLE_SID = "ogg11r2") Extract efinaa ExtTrail ./dirdat/aa UserID oggadm@ogg11r2, Password Welcome1 Table src_user01.*; GGSCI (host01) > |
Some of the parameters:
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 efinaa any time.
Create the Extract group and the local Extract trail file.
| Host01 - Linux |
GGSCI (host01) > Add Extract efinaa, TranLog, Begin Now EXTRACT added. GGSCI (host01) > Add ExtTrail ./dirdat/aa, Extract efinaa, 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.
4.2 Configuring the Data Pump (Oracle-to-DB2)
Create the secondary Extract (data pump) parameter file.
Using the naming conventions discussed in the Overview, the path/filename.ext will be dirprm/pfinab.prm. The path and extension will be added automatically.
| Host01 - Linux |
GGSCI (host01) > Edit Param pfinab
-- Data pump (secondary Extract) from host01 to host02
Extract pfinab
RmtHost host02, MgrPort 7909, Compress
RmtTrail ./dirdat/ab
Passthru
Table src_user01.*;
GGSCI (host01) >
|
This Table schema is the source.
You can check your work by entering View Param pfinab any time.
Create the data pump group and the remote Extract trail file.
| Host01 - Linux |
GGSCI (host01) > Add Extract pfinab, ExtTrailSource ./dirdat/aa EXTRACT added. GGSCI (host01) > Add RmtTrail ./dirdat/ab, Extract pfinab, 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.
4.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 EFINAA 00:00:00 00:03:24
EXTRACT STOPPED PFINAB 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: EFINAA
Seqno: 0
RBA: 0
File Size: 5M
Extract Trail: ./dirdat/ab
Extract: PFINAB
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.
5. Configuring Data Delivery Using Replicat (Oracle-to-DB2)
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:
5.1 Configuring the Replicat (Oracle-to-DB2)
On target host02, create the Replicat parameter file.
Create a new terminal window on the Blue Host (host02) and navigate to the ggs_trg direcotry, which hosts the target Oracle GoldenGate instance.
| Host02 - Linux |
[user01@host02 ~]$ cd ~/db2/ggs_trg [user01@host02 ggs_trg]$ ./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 rfinab -- Delivery from host01 to host02 Replicat rfinab TargetDB ggs_trg, UserID user01, Password Welcome1 SourceDefs dirdef/usr1tabs.def DiscardFile dirrpt/rfinab.dsc, Append Map src_user01.*, Target user01.*; 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 rfinab, ExtTrail ./dirdat/ab
REPLICAT added.
GGSCI (host02) >
|
You can check your work by entering View Param rfinab 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 RFINAB 00:00:00 00:00:33
GGSCI (host02) >
|
Since nothing other than the Manager is started yet, the Replicat Status should say STOPPED.
5.2 Starting All Processes
Start all Extract processes on the source.
| Host01 - Linux |
GGSCI (host01) > Start Extract *
Sending START request to MANAGER ...
EXTRACT EFINAA starting
Sending START request to MANAGER ...
EXTRACT PFINAB starting
GGSCI (host01) >
|
Alternatively, you could have entered, Start *, or Start e01* and Start p01*.
Start all Replicat processes on the target.
| Host02 - Linux |
GGSCI (host02) > Start Replicat *
Sending START request to MANAGER ...
REPLICAT RFINAB starting
GGSCI (host02) >
|
Alternatively, you could have entered, Start *, or Start rfin*.
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 RFINAB 00:00:00 00:00:03
GGSCI (host02) >
|
Display more information.
| Host02 - Linux |
GGSCI (host02) > Info rfinab
REPLICAT RFINAB Last Started 2012-10-02 21:04 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint File ./dirdat/ab000000
First Record RBA 0
GGSCI (host02) >
|
Display the most detailed information.
| Host02 - Linux |
GGSCI (host02) > Info rfinab, Detail
REPLICAT RFINAB Last Started 2012-10-02 21:04 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:02 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 /home/user01/db2/ggs_trg
Report file /home/user01/db2/ggs_trg/dirrpt/RFINAB.rpt
Parameter file /home/user01/db2/ggs_trg/dirprm/rfinab.prm
Checkpoint file /home/user01/db2/ggs_trg/dirchk/RFINAB.cpr
Checkpoint table user01.oggchkpt
Process file /home/user01/db2/ggs_trg/dirpcs/RFINAB.pcr
Stdout file /home/user01/db2/ggs_trg/dirout/RFINAB.out
Error log /home/user01/db2/ggs_trg/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 EFINAA 00:14:14 00:00:00
EXTRACT RUNNING PFINAB 00:00:00 00:04:09
GGSCI (host01) >
|
Display detailed information.
| Host01 - Linux |
GGSCI (host01) > Info Extract * EXTRACT EFINAA Last Started 2012-10-02 21:04 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:02 ago) Log Read Checkpoint Oracle Redo Logs 2012-10-02 21:07:33 Seqno 9, RBA 10427392 SCN 0.1166534 (1166534) EXTRACT PFINAB Last Started 2012-10-02 21:04 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:00 ago) Log Read Checkpoint File ./dirdat/aa000000 First Record RBA 1041 GGSCI (host01) > Exit [user01@host01 ggs_src]$ |
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.
6. Generating Data (Oracle-to-DB2)
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:
6.1 Generating INSERTs (Initial Load)
Run the SQL script to INSERT rows.
On source host01, at the OS prompt, run the economic_entity.sql script in sqlplus, which populates the ECONOMIC_ENTITY table.
| Host01 - Linux |
[user01@host01 ~]$ cd ~/ddl [user01@host01 ddl]$ sqlplus src_user01/Welcome1@ogg11r2 @economic_entity.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. ... many lines omitted for clarity ... 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 count(*) FROM economic_entity; COUNT(*) ---------- 235 SQL> set linesize 132 SQL> column economic_entity format a60 SQL> set pagesize 999 SQL> SELECT * FROM economic_entity WHERE continent = 'EUROPE' order by ENTITY_ID; ENTITY_ID ECONOMIC_ENTITY CONTINENT ---------- --------------------------------------- -------------------- 48 Austria EUROPE 49 Belgium-Luxembourg EUROPE 50 Belgium EUROPE 51 Luxembourg EUROPE 52 Denmark EUROPE 53 Finland EUROPE 54 France EUROPE 55 Germany EUROPE 56 Greece EUROPE 57 Ireland EUROPE 58 Italy EUROPE 59 Netherlands EUROPE 60 Portugal EUROPE 61 Spain EUROPE 62 Sweden EUROPE 63 United Kingdom EUROPE 65 Bulgaria EUROPE 66 Cyprus EUROPE 67 Czech Republic EUROPE 68 Estonia EUROPE 69 Hungary EUROPE 70 Latvia EUROPE 71 Lithuania EUROPE 72 Malta and Gozo EUROPE 73 Poland EUROPE 74 Romania EUROPE 75 Slovakia EUROPE 76 Slovenia EUROPE 77 Other Europe EUROPE 79 Iceland EUROPE 80 Norway EUROPE 81 Switzerland EUROPE 83 Albania EUROPE 84 Bosnia Herzegovina EUROPE 85 Croatia EUROPE 86 Macedonia EUROPE 87 Serbia EUROPE 89 Russia EUROPE 90 Ukraine EUROPE 92 Armenia EUROPE 93 Azerbaijan EUROPE 94 Belarus EUROPE 95 Georgia EUROPE 96 Kazakhstan EUROPE 97 Kyrgyzstan EUROPE 98 Moldova EUROPE 99 Tajikistan EUROPE 100 Turkmenistan EUROPE 101 Uzbekistan EUROPE 49 rows selected. SQL> exit [user01@host01 ddl]$ |
Verify that the source GGSCI Extract processes are still running.
On source host01, enter the following command:
| Host01 - Linux |
[user01@host01 ~]$ cd ~/oracle/ggs_src [user01@host01 ggs_src]$ ./ggsci GGSCI (host01) 1> Info All Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EFINAA 00:00:00 00:00:07 EXTRACT RUNNING PFINAB 00:00:00 00:00:08 GGSCI (host01) 2> Exit [user01@host01 ggs_src]$ |
If the Status says ABENDED, then check the process reports (covered as a later topic) to see what the error was.
Verify that the intermediate GGSCI Replicat processes are still running.
On intermediate 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 RFINAB 00:00:00 00:00:07 GGSCI (host02) > Exit [user01@host02 ggs_trg]$ |
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 the replication target, the Blue Host, at the OS prompt, run the following DB2 queries:
| Host02 - Linux |
[user01@host02 ~]$ db2 connect to ggs_trg user user01 using Welcome1 Database Connection Information Database server = DB2/LINUXX8664 9.7.5 SQL authorization ID = USER01 Local database alias = GGS_TRG [user01@host02 ~]$ db2 select count\(*\) from economic_entity 1 ----------- 235 1 record(s) selected. [user01@host02 ~]$ db2 select entity_id,economic_entity,continent from \ |
The source and target database ECONOMIC_ENTITY tables should match.
Upload more economic data to the replication source (ggs_src on the Red Host.) The ddl directory contains several SQL scripts. The gdp_by_year.sql and gdp_growth_by_year.sql scripts upload World GDP data into the GDP_BY_YEAR and GDP_GROWTH_BY_YEAR tables, respectively.
On the Red Host, at the OS prompt, run the two SQL scripts.
| Host01 - Linux |
[user01@host01 ~]$ cd ~/ddl [user01@host01 ddl]$ sqlplus src_user01/Welcome1@ogg11r2 @gdp_by_year.sql SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 3 01:14:24 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. ... many lines omitted for clarity ... 1 row created. commit; Commit complete. SQL> @gdp_growth_by_year.sql 1 row created. ... many lines omitted for clarity ... 1 row created. commit; SQL> select count(*) from gdp_by_year; COUNT(*) ---------- 705 SQL> select count(*) from gdp_growth_by_year; COUNT(*) ---------- 705 SQL> exit [user01@host01 ddl]$ |
Verify that rows have been inserted into the target tables.
On the replication target, the Blue Host, at the OS prompt, run the following DB2 queries:
| Host02 - Linux |
[user01@host02 ~]$ db2 connect to ggs_trg user user01 using Welcome1 Database Connection Information Database server = DB2/LINUXX8664 9.7.5 SQL authorization ID = USER01 Local database alias = GGS_TRG [user01@host02 ~]$ db2 select count\(*\) from gdp_by_year 1 ----------- 705 1 record(s) selected. [user01@host02 ~]$ db2 select count\(*\) from gdp_growth_by_year 1 ----------- 705 1 record(s) selected. [user01@host02 ~]$ |
The source and target database tables GDP_BY_YEAR and GDP_GROWTH_BY_YEAR should match.
6.2 Generating UPDATEs/DELETEs
On the Red Host, at the OS prompt, connect to sqlplus as the
src_user01
user.
In the ECONOMIC_ENTITY
table all economic entities which are not single
countries, like "East Asia Less Japan" or "Other
Western Europe" are listed as "N/A"
in the CONTINENT
column. Change "N/A"
to "Not a continent".
In addition, the GDP_BY_YEAR
and GDP_GROWTH_BY_YEAR
tables contain economic data for the years 2005-2007.
Erase all entries pertaining to the year 2005.
| Host01 - Linux |
[user01@host01 ~] sqlplus src_user01/Welcome1@ogg11r2 SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 3 01:14:24 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> UPDATE ECONOMIC_ENTITY set CONTINENT = 'Not a continent' WHERE CONTINENT = 'N/A'; 36 rows updated. SQL> DELETE FROM GDP_BY_YEAR where GDP_YEAR=2005; 235 rows deleted. SQL> DELETE FROM GDP_GROWTH_BY_YEAR where GDP_YEAR=2005; 235 rows deleted. SQL>commit; Commit complete. SQL>exit [user01@host01 ~]$ |
Verify that the rows were updated and deleted in the target tables.
| Host02 - Linux |
[user01@host02 ~]$ db2 connect to ggs_trg user user01 using Welcome1 Database Connection Information Database server = DB2/LINUXX8664 9.7.5 SQL authorization ID = USER01 Local database alias = GGS_TRG [user01@host02 ~]$ db2 select min\(gdp_year\) from gdp_by_year 1 ----------- 2006 1 record(s) selected. [user01@host02 ~]$ db2 select min\(gdp_year\) from gdp_growth_by_year 1 ----------- 2006 1 record(s) selected. [user01@host02 ~]$ db2 select distinct continent from economic_entity CONTINENT -------------------- AFRICA AMERICAS ASIA EUROPE Not a continent OCEANIA 6 record(s) selected. [user01@host02 ~]$ |
The deletion of the economic data related to 2005 is confirmed as the target database shows that the minimum year for both GDP_BY_YEAR and GDP_GROWTH_BY_YEAR is 2006. The update to the ECONOMIC_ENTITY table was also successful, as no rows with 'N/A' as a continent are displayed by the SELECT DISTINCT statement.
Verify that the source GGSCI Extract processes are still running.
On source hostA, enter the following command:
| Host01 - Linux |
[user01@host01 ~]$ cd ~/oracle/ggs_src [user01@host01 ggs_src]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.0 OGGCORE_11.2.1.0.0_PLATFORMS_120131.1910_FBO Linux, x64, 64bit (optimized), Oracle 11g on Feb 1 2012 00:55:59 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (host01) 1> Info All Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EFINAA 00:00:00 00:00:07 EXTRACT RUNNING PFINAB 00:00:00 00:00:05 GGSCI (host01) 2> |
Verify that the GGSCI Replicat processes are still running.
On the Blue Host target GGSCI should still be running. Enter the following command:
| Host02 - Linux |
[user01@host02 ~]$ cd ~/db2/ggs_trg [user01@host02 ggs_trg]$ ./ggsci Oracle GoldenGate Command Interpreter for DB2 Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230 Linux, x64, 64bit (optimized), DB2 9.7 on Apr 23 2012 06:02:54 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (host02) > Info All Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING RFINAB 00:00:00 00:00:07 GGSCI (host02) > |
The Status should still be RUNNING.
This completes the configuration and operation of the basic heterogeneous unidirectional functionality of Oracle GoldenGate: Extract, Data Pump, and Replicat.
7. Managing the Oracle GoldenGate Environment (Oracle-to-DB2)
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:
7.1 Viewing Reports
View the process reports on the source.
View the process report for the Extract.
| Host01 - Linux |
GGSCI (host01) > Send Extract efinaa, Report Sending REPORT request to EXTRACT EFINAA ... Request processed. GGSCI (host01) > View Report efinaa ********************************************************************** Oracle GoldenGate Capture for Oracle Version 11.2.1.0.0 OGGCORE_11.2.1.0.0_PLATFORMS_120131.1910_FBO Linux, x64, 64bit (optimized), Oracle 11g on Feb 1 2012 01:04:34 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. ... many lines omitted for clarity ... Starting at 2012-10-02 21:04:21 *********************************************************************** 2012-10-03 10:40:02 INFO OGG-01021 Command received from GGSCI: REPORT. |
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.
You can do the same thing to view the Data Pump pfinab report.
View the process reports for the target.
On the Blue Host target, view the process reports for the Replicat.
| Host02 - Linux |
GGSCI (host02) > Send Replicat rfinab, Report Sending REPORT request to REPLICAT RFINAB ... Request processed. GGSCI (host02)> View Report rfinab *********************************************************************** Oracle GoldenGate Delivery for DB2 Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230 Linux, x64, 64bit (optimized), DB2 9.7 on Apr 23 2012 06:13:13 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. Starting at 2012-10-02 21:04:38 *********************************************************************** ... many lines omitted for clarity ... Reading ./dirdat/ab000000, current RBA 299495, 2151 records Report at 2012-10-03 11:21:06 (activity since 2012-10-02 21:20:38) From Table SRC_USER01.ECONOMIC_ENTITY to USER01.ECONOMIC_ENTITY: # inserts: 235 # updates: 36 # deletes: 0 # discards: 0 From Table SRC_USER01.GDP_BY_YEAR to USER01.GDP_BY_YEAR: # inserts: 705 # updates: 0 # deletes: 235 # discards: 0 From Table SRC_USER01.GDP_GROWTH_BY_YEAR to USER01.GDP_GROWTH_BY_YEAR: # inserts: 705 # updates: 0 # deletes: 235 # discards: 0 --More--(75%) |
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.
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.
7.2 Viewing Statistics
View statistics for the source Extract.
| Host01 - Linux |
GGSCI (host01) > Send Extract efinaa, Stats
Sending STATS request to EXTRACT EFINAA ...
Start of Statistics at 2012-10-03 11:32:54.
Output to ./dirdat/aa:
Extracting from SRC_USER01.ECONOMIC_ENTITY to SRC_USER01.ECONOMIC_ENTITY:
*** Total statistics since 2012-10-02 21:20:37 ***
Total inserts 235.00
Total updates 36.00
Total deletes 0.00
Total discards 0.00
Total operations 271.00
*** Daily statistics since 2012-10-03 00:00:00 ***
Total inserts 0.00
Total updates 36.00
Total deletes 0.00
Total discards 0.00
Total operations 36.00
*** Hourly statistics since 2012-10-03 11:00:00 ***
No database operations have been performed.
*** Latest statistics since 2012-10-02 21:20:37 ***
Total inserts 235.00
Total updates 36.00
Total deletes 0.00
Total discards 0.00
Total operations 271.00
Extracting from SRC_USER01.GDP_BY_YEAR to SRC_USER01.GDP_BY_YEAR:
*** Total statistics since 2012-10-02 21:20:37 ***
Total inserts 705.00
Total updates 0.00
Total deletes 235.00
Total discards 0.00
Total operations 940.00
*** Daily statistics since 2012-10-03 00:00:00 ***
Total inserts 705.00
Total updates 0.00
Total deletes 235.00
Total discards 0.00
Total operations 940.00
*** Hourly statistics since 2012-10-03 11:00:00 ***
No database operations have been performed.
*** Latest statistics since 2012-10-02 21:20:37 ***
Total inserts 705.00
Total updates 0.00
Total deletes 235.00
Total discards 0.00
Total operations 940.00
Extracting from SRC_USER01.GDP_GROWTH_BY_YEAR to SRC_USER01.GDP_GROWTH_BY_YEAR:
*** Total statistics since 2012-10-02 21:20:37 ***
Total inserts 705.00
Total updates 0.00
Total deletes 235.00
Total discards 0.00
Total operations 940.00
*** Daily statistics since 2012-10-03 00:00:00 ***
Total inserts 705.00
Total updates 0.00
Total deletes 235.00
Total discards 0.00
Total operations 940.00
*** Hourly statistics since 2012-10-03 11:00:00 ***
No database operations have been performed.
*** Latest statistics since 2012-10-02 21:20:37 ***
Total inserts 705.00
Total updates 0.00
Total deletes 235.00
Total discards 0.00
Total operations 940.00
End of Statistics.
GGSCI (host01) >
|
The command Stats efinaa 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 rfinab, Stats
Sending STATS request to REPLICAT RFINAB ...
Start of Statistics at 2012-10-03 11:36:29.
Replicating from SRC_USER01.ECONOMIC_ENTITY to USER01.ECONOMIC_ENTITY:
*** Total statistics since 2012-10-02 21:20:38 ***
Total inserts 235.00
Total updates 36.00
Total deletes 0.00
Total discards 0.00
Total operations 271.00
*** Daily statistics since 2012-10-03 00:00:00 ***
Total inserts 0.00
Total updates 36.00
Total deletes 0.00
Total discards 0.00
Total operations 36.00
*** Hourly statistics since 2012-10-03 11:00:00 ***
No database operations have been performed.
*** Latest statistics since 2012-10-02 21:20:38 ***
Total inserts 235.00
Total updates 36.00
Total deletes 0.00
Total discards 0.00
Total operations 271.00
Replicating from SRC_USER01.GDP_BY_YEAR to USER01.GDP_BY_YEAR:
*** Total statistics since 2012-10-02 21:20:38 ***
Total inserts 705.00
Total updates 0.00
Total deletes 235.00
Total discards 0.00
Total operations 940.00
*** Daily statistics since 2012-10-03 00:00:00 ***
Total inserts 705.00
Total updates 0.00
Total deletes 235.00
Total discards 0.00
Total operations 940.00
*** Hourly statistics since 2012-10-03 11:00:00 ***
No database operations have been performed.
*** Latest statistics since 2012-10-02 21:20:38 ***
Total inserts 705.00
Total updates 0.00
Total deletes 235.00
Total discards 0.00
Total operations 940.00
Replicating from SRC_USER01.GDP_GROWTH_BY_YEAR to USER01.GDP_GROWTH_BY_YEAR:
*** Total statistics since 2012-10-02 21:20:38 ***
Total inserts 705.00
Total updates 0.00
Total deletes 235.00
Total discards 0.00
Total operations 940.00
*** Daily statistics since 2012-10-03 00:00:00 ***
Total inserts 705.00
Total updates 0.00
Total deletes 235.00
Total discards 0.00
Total operations 940.00
*** Hourly statistics since 2012-10-03 11:00:00 ***
No database operations have been performed.
*** Latest statistics since 2012-10-02 21:20:38 ***
Total inserts 705.00
Total updates 0.00
Total deletes 235.00
Total discards 0.00
Total operations 940.00
End of Statistics.
GGSCI (host02) >
|
!!!CHECK!!! Notice that previously the statistics for the primary Extract were from schema SRC_USER01 to SRC_USER01 since a Data Pump was involved. Here it is clearer that it is from schema SRC_USER01 to USER01.
There are other kinds of statistics that are available as well.
7.3 Stopping and Deleting Processes
Before you can proceed with the remaining part of the OBE, you need to stop several processes, by entering Stop Extract efinaa, or Stop * to stop all of them on a host. Obviously you would replace efinaa with the process name you wish to stop.
Before you can delete an individual process, you must first make sure you are connected to the database (DBLogin) and you enter Delete Extract efinaa, 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."
Connect to the Red Host (host01) and stop all processes running in the Oracle replication source GoldenGate instance (directory /home/user01/oracle/ggs_src). Stop the Oracle GoldenGate Manager process as well.
| Host01 - Linux |
[user01@host01 ~]$ cd ~/oracle/ggs_src [user01@host01 ggs_src]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.0 OGGCORE_11.2.1.0.0_PLATFORMS_120131.1910_FBO Linux, x64, 64bit (optimized), Oracle 11g on Feb 1 2012 00:55:59 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (host01) 1> Obey startup.oby ... many lines omitted for clarity ... but make sure everything started. GGSCI (host01) 2> stop er * Sending STOP request to EXTRACT EFINAA ... Request processed. Sending STOP request to EXTRACT PFINAB ... Request processed. GGSCI (host01) 3> stop mgr Manager process is required by other GGS processes. Are you sure you want to stop it (y/n)? y Sending STOP request to MANAGER ... Request processed. Manager stopped. GGSCI (host01) 4> Exit [user01@host01 ggs_src]$ |
Connect to the Blues Host (host02) and stop all processes running in the DB2 replication target GoldenGate instance (directory /home/user01/db2/ggs_trg). Stop the Oracle GoldenGate Manager process as well.
| Host02 - Linux |
[user01@host02 ~]$ cd ~/db2/ggs_trg [user01@host02 ggs_trg]$ ./ggsci Oracle GoldenGate Command Interpreter for DB2 Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230 Linux, x64, 64bit (optimized), DB2 9.7 on Apr 23 2012 06:02:54 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (host02) 1> Obey startup.oby ... many lines omitted for clarity ... but make sure everything started. GGSCI (host02) 2> stop er * Sending STOP request to REPLICAT RFINAB ... Request processed. GGSCI (host01) 3> stop mgr Manager process is required by other GGS processes. Are you sure you want to stop it (y/n)? y Sending STOP request to MANAGER ... Request processed. Manager stopped. GGSCI (host02) 4> Exit [user01@host02 ggs_trg]$ |
8. Configuring Data Capture Using Extract (DB2-to-Oracle)
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:
8.1 Configuring the Primary Extract on the DB2 source instance
On source host02, create the primary Extract parameter file.
Using the naming conventions discussed in the Overview, the path/filename.ext will be dirprm/efinaa.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.)
| Host02 - Linux |
[user01@host02 ~] cd ~/db2/ggs_src [user01@host02 ggs_src]$ ./ggsci Oracle GoldenGate Command Interpreter for DB2 Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230 Linux, x64, 64bit (optimized), DB2 9.7 on Apr 23 2012 06:02:54 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (host02) 1> Obey startup.oby ... many lines omitted for clarity ... but make sure everything started. GGSCI (host02) > Edit Param efinaa -- Primary Extract from host02 to host01 Extract efinaa ExtTrail ./dirdat/aa SourceDB ggs_src, UserID user01, Password Welcome1 Table user01.*; GGSCI (host02) > |
You can check your work by entering View Param efinaa any time.
Create the Extract group and the local Extract trail file.
| Host02 - Linux |
GGSCI (host02) > Add Extract efinaa, TranLog, Begin Now EXTRACT added. GGSCI (host02) > Add ExtTrail ./dirdat/aa, Extract efinaa, Megabytes 5 EXTTRAIL added. GGSCI (host02) > |
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.
8.2 Configuring the Data Pump (DB2-to-Oracle)
Create the secondary Extract (data pump) parameter file.
Using the naming conventions discussed in the Overview, the path/filename.ext will be dirprm/pfinab.prm. The path and extension will be added automatically.
| Host02 - Linux |
GGSCI (host02) > Edit Param pfinab
-- Data pump (secondary Extract) from host02 to host01
Extract pfinab
RmtHost host01, MgrPort 7909, Compress
RmtTrail ./dirdat/ab
Passthru
Table user01.*;
GGSCI (host02) >
|
This Table schema is the source.
You can check your work by entering View Param pfinab any time.
Create the data pump group and the remote Extract trail file.
| Host02 - Linux |
GGSCI (host02) > Add Extract pfinab, ExtTrailSource ./dirdat/aa EXTRACT added. GGSCI (host02) > Add RmtTrail ./dirdat/ab, Extract pfinab, Megabytes 5 RMTTRAIL added. GGSCI (host02) > |
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.
8.3 Verify the Extract Processes (Optional)
Make sure the Extract processes were created and registered correctly.
| Host02 - Linux |
GGSCI (host02) > Info All
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EFINAA 00:00:00 00:03:24
EXTRACT STOPPED PFINAB 00:00:00 00:01:22
GGSCI (host02) >
|
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.
| Host02 - Linux |
GGSCI (host02) > Info ExtTrail *
Extract Trail: ./dirdat/aa
Extract: EFINAA
Seqno: 0
RBA: 0
File Size: 5M
Extract Trail: ./dirdat/ab
Extract: PFINAB
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.
9. Configuring Data Delivery Using Replicat (DB2-to-Oracle)
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:
9.1 Configuring the Replicat (DB2-to-Oracle)
On target host01, create the Replicat parameter file.
Create a new terminal window on the Red Host (host01) and navigate to the ggs_trg direcotry, which hosts the target Oracle GoldenGate instance.
| Host02 - Linux |
[user01@host01 ~]$ cd ~/oracle/ggs_trg [user01@host01 ggs_trg]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.0 OGGCORE_11.2.1.0.0_PLATFORMS_120131.1910_FBO Linux, x64, 64bit (optimized), Oracle 11g on Feb 1 2012 00:55:59 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (host01) 1> Obey startup.oby ... many lines omitted for clarity ... but make sure everything started. GGSCI (host01) > Edit Param rfinab -- Delivery from host02 to host01 Replicat rfinab UserID oggadm@ogg11r2, Password Welcome1 SourceDefs dirdef/usr1tabs.def DiscardFile dirrpt/rfinab.dsc, Append Map user01.*, Target trg_user01.*; GGSCI (host01) > |
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.
| Host01 - Linux |
GGSCI (host01) > Add Replicat rfinab, ExtTrail ./dirdat/ab
REPLICAT added.
GGSCI (host01) >
|
You can check your work by entering View Param rfinab any time.
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
REPLICAT STOPPED RFINAB 00:00:00 00:00:33
GGSCI (host01) >
|
Since nothing other than the Manager is started yet, the Replicat Status should say STOPPED.
9.2 Starting All Processes
Start all Extract processes on the source.
| Host02 - Linux |
GGSCI (host02) > Start Extract *
Sending START request to MANAGER ...
EXTRACT EFINAA starting
Sending START request to MANAGER ...
EXTRACT PFINAB starting
GGSCI (host2) >
|
Alternatively, you could have entered, Start *, or Start e01* and Start p01*.
Start all Replicat processes on the target.
| Host01 - Linux |
GGSCI (host01) > Start Replicat *
Sending START request to MANAGER ...
REPLICAT RFINAB starting
GGSCI (host01) >
|
Alternatively, you could have entered, Start *, or Start rfin*.
Display information about all processes on the target.
Display summary information.
| Host01 - Linux |
GGSCI (host01) > Info All
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RFINAB 00:00:00 00:00:03
GGSCI (host01) >
|
Display more information.
| Host01 - Linux |
GGSCI (host01) > Info rfinab
REPLICAT RFINAB Last Started 2012-10-02 21:04 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint File ./dirdat/ab000000
First Record RBA 0
GGSCI (host01) >
|
Display the most detailed information.
| Host01 - Linux |
GGSCI (host01) > Info rfinab, Detail
REPLICAT RFINAB Last Started 2012-10-02 21:04 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:02 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 /home/user01/db2/ggs_trg
Report file /home/user01/db2/ggs_trg/dirrpt/RFINAB.rpt
Parameter file /home/user01/db2/ggs_trg/dirprm/rfinab.prm
Checkpoint file /home/user01/db2/ggs_trg/dirchk/RFINAB.cpr
Checkpoint table user01.oggchkpt
Process file /home/user01/db2/ggs_trg/dirpcs/RFINAB.pcr
Stdout file /home/user01/db2/ggs_trg/dirout/RFINAB.out
Error log /home/user01/db2/ggs_trg/ggserr.log
GGSCI (host01) >
|
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 (host02) > Info All
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EFINAA 00:14:14 00:00:00
EXTRACT RUNNING PFINAB 00:00:00 00:04:09
GGSCI (host02) >
|
Display detailed information.
| Host02 - Linux |
GGSCI (host02) > Info Extract * EXTRACT EFINAA Last Started 2012-10-02 21:04 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:02 ago) Log Read Checkpoint Oracle Redo Logs 2012-10-02 21:07:33 Seqno 9, RBA 10427392 SCN 0.1166534 (1166534) EXTRACT PFINAB Last Started 2012-10-02 21:04 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:00 ago) Log Read Checkpoint File ./dirdat/aa000000 First Record RBA 1041 GGSCI (host02) > Exit [user01@host02 ggs_src] |
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.
10. Generating Data (DB2-to-Oracle)
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:
10.1 Generating INSERTs (Initial Load)
Run the SQL script to INSERT rows.
On source host02, at the OS prompt, run the economic_entity.sql script in sqlplus, which populates the ECONOMIC_ENTITY table.
| Host02 - Linux |
[user01@host02 ~]$ cd ~/ddl [user01@host02 ddl]$ db2 connect to ggs_src user user01 using Welcome1 Database Connection Information Database server = DB2/LINUXX8664 9.7.5 SQL authorization ID = USER01 Local database alias = GGS_SRC [user01@host02 ddl]$ db2 -tvf economic_entity.sql Insert into ECONOMIC_ENTITY (ENTITY_ID,ECONOMIC_ENTITY,CONTINENT) values |
There should not be any errors.
Verify that the rows were inserted into the source tables.
| Host02 - Linux |
[user01@host02 ddl]$ db2 SELECT count\(*\) FROM economic_entity 1 ----------- 235 1 record(s) selected. [user01@host02 ddl]$ db2 SELECT ENTITY_ID, ECONOMIC_ENTITY, CONTINENT FROM \ |
Verify that the source GGSCI Extract processes are still running.
On source host02, enter the following command:
| Host02 - Linux |
[user01@host02 ~]$ cd ~/db2/ggs_src [user01@host02 ggs_src]$ ./ggsci Oracle GoldenGate Command Interpreter for DB2 Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230 Linux, x64, 64bit (optimized), DB2 9.7 on Apr 23 2012 06:02:54 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (host02) 1> Info All Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EFINAA 00:00:00 00:00:07 EXTRACT RUNNING PFINAB 00:00:00 00:00:08 GGSCI (host02) 2> Exit [user01@host02 ggs_src]$ |
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 the Red Host, GGSCI should still be running. Enter the following command:
| Host01 - Linux |
GGSCI (host01) > Info All Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING RFINAB 00:00:00 00:00:07 GGSCI (host01) > Exit [user01@host01 ggs_trg]$ |
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 the replication target, the Red Host, at the OS prompt, run the following Oracle SQL queries:
| Host01 - Linux |
[user01@host01 ~]$ sqlplus trg_user01/Welcome1@ogg11r2 SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 3 15:57:29 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 count(*) from economic_entity; COUNT(*) ---------- 235 SQL> set pagesize 999 SQL> set linesize 132 SQL> column economic_entity format a60 SQL> select * from economic_entity where continent = 'EUROPE' order by entity_id; ENTITY_ID ECONOMIC_ENTITY CONTINENT ---------- ------------------------------------------------------------ ----------- 48 Austria EUROPE 49 Belgium-Luxembourg EUROPE 50 Belgium EUROPE 51 Luxembourg EUROPE 52 Denmark EUROPE 53 Finland EUROPE 54 France EUROPE 55 Germany EUROPE 56 Greece EUROPE 57 Ireland EUROPE 58 Italy EUROPE 59 Netherlands EUROPE 60 Portugal EUROPE 61 Spain EUROPE 62 Sweden EUROPE 63 United Kingdom EUROPE 65 Bulgaria EUROPE 66 Cyprus EUROPE 67 Czech Republic EUROPE 68 Estonia EUROPE 69 Hungary EUROPE 70 Latvia EUROPE 71 Lithuania EUROPE 72 Malta and Gozo EUROPE 73 Poland EUROPE 74 Romania EUROPE 75 Slovakia EUROPE 76 Slovenia EUROPE 77 Other Europe EUROPE 79 Iceland EUROPE 80 Norway EUROPE 81 Switzerland EUROPE 83 Albania EUROPE 84 Bosnia Herzegovina EUROPE 85 Croatia EUROPE 86 Macedonia EUROPE 87 Serbia EUROPE 89 Russia EUROPE 90 Ukraine EUROPE 92 Armenia EUROPE 93 Azerbaijan EUROPE 94 Belarus EUROPE 95 Georgia EUROPE 96 Kazakhstan EUROPE 97 Kyrgyzstan EUROPE 98 Moldova EUROPE 99 Tajikistan EUROPE 100 Turkmenistan EUROPE 101 Uzbekistan EUROPE 49 rows selected. SQL> exit [user01@host01 ~]$ |
The source and target database ECONOMIC_ENTITY tables should match.
Upload more economic data to the replication source (ggs_src on the Blue Host.) The ddl directory contains several SQL scripts. The gdp_by_year.sql and gdp_growth_by_year.sql scripts upload World GDP data into the GDP_BY_YEAR and GDP_GROWTH_BY_YEAR tables, respectively.
On the Blue Host, at the OS prompt, run the two SQL scripts.
| Host02 - Linux |
[user01@host02 ~]$ cd ~/ddl [user01@host02 ddl]$ db2 connect to ggs_src user user01 using Welcome1 Database Connection Information Database server = DB2/LINUXX8664 9.7.5 SQL authorization ID = USER01 Local database alias = GGS_SRC [user01@host02 ddl]$ db2 -tvf gdp_by_year.sql insert into gdp_by_year (ENTITY_ID, GDP_YEAR, GDP_VALUE) |
Verify that rows have been inserted into the target tables.
On the replication target, the Red Host, at the OS prompt, run the following ORacle SQL queries:
| Host01 - Linux |
[user01@host01 ~]$ sqlplus trg_user01/Welcome1@ogg11r2 SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 3 01:14:24 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 count(*) from gdp_by_year; COUNT(*) ---------- 705 SQL> select count(*) from gdp_growth_by_year; COUNT(*) ---------- 705 [user01@host01 ~]$ |
The source and target database tables GDP_BY_YEAR and GDP_GROWTH_BY_YEAR should match.
10.2 Generating UPDATEs/DELETEs
On the Blue Host, at the OS prompt, use the db2
utility to connect to the DB2 GGS_SRC
database.
In the ECONOMIC_ENTITY
table all economic entities which are not single
countries, such as "East Asia Less Japan" or "Other
Western Europe" are listed as "N/A"
in the CONTINENT
column. Change "N/A"
to "Not a continent".
In addition, the GDP_BY_YEAR
and GDP_GROWTH_BY_YEAR
tables contain economic data for the years 2005-2007.
Erase all entries pertaining to the year 2005.
| Host02 - Linux |
[user01@host02 ~] db2 (c) Copyright IBM Corporation 1993,2007 Command Line Processor for DB2 Client 9.7.5 You can issue database manager commands and SQL statements from the command prompt. For example: db2 => connect to sample db2 => bind sample.bnd For general help, type: ?. For command help, type: ? command, where command can be the first few keywords of a database manager command. For example: ? CATALOG DATABASE for help on the CATALOG DATABASE command ? CATALOG for help on all of the CATALOG commands. To exit db2 interactive mode, type QUIT at the command prompt. Outside interactive mode, all commands must be prefixed with 'db2'. To list the current command option settings, type LIST COMMAND OPTIONS. For more detailed help, refer to the Online Reference Manual. db2 => connect to ggs_src user user01 using Welcome1 Database Connection Information Database server = DB2/LINUXX8664 9.7.5 SQL authorization ID = USER01 Local database alias = GGS_SRC db2 => UPDATE ECONOMIC_ENTITY set CONTINENT = 'Not a continent' WHERE CONTINENT = 'N/A' DB20000I The SQL command completed successfully. db2 => DELETE FROM GDP_BY_YEAR where GDP_YEAR=2005 DB20000I The SQL command completed successfully. db2 => DELETE FROM GDP_GROWTH_BY_YEAR where GDP_YEAR=2005 DB20000I The SQL command completed successfully. db2 => commit DB20000I The SQL command completed successfully. db2 => |
Verify that the rows were updated and deleted in the target tables.
| Host01 - Linux |
[user01@host01 ~]$ sqlplus trg_user01/Welcome1@ogg11r2 SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 3 21:42:14 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 min(gdp_year) from gdp_by_year; MIN(GDP_YEAR) ------------- 2006 SQL> select min(gdp_year) from gdp_growth_by_year; MIN(GDP_YEAR) ------------- 2006 SQL> select distinct continent from economic_entity; CONTINENT -------------------- AFRICA EUROPE AMERICAS OCEANIA ASIA Not a continent 6 rows selected. SQL> |
The deletion of the economic data related to 2005 is confirmed as the target database shows that the minimum year for both GDP_BY_YEAR and GDP_GROWTH_BY_YEAR is 2006. The update to the ECONOMIC_ENTITY table was also successful, as no rows with 'N/A' as a continent are displayed by the SELECT DISTINCT statement.
Verify that the source GGSCI Extract processes are still running.
On the Blue Host (source) enter the following command:
| Host02 - Linux |
[user01@host02 ~]$ cd ~/db2/ggs_src [user01@host02 ggs_src]$ ./ggsci Oracle GoldenGate Command Interpreter for DB2 Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230 Linux, x64, 64bit (optimized), DB2 9.7 on Apr 23 2012 06:02:54 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (host02) 1> Info All Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EFINAA 00:00:00 00:00:07 EXTRACT RUNNING PFINAB 00:00:00 00:00:05 GGSCI (host02)> |
Verify that the GGSCI Replicat processes are still running.
On the Red Host run GGSCI and enter the following command:
| Host01 - Linux |
[user01@host01 ~]$ cd ~/oracle/ggs_trg [user01@host01 ggs_trg]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.0 OGGCORE_11.2.1.0.0_PLATFORMS_120131.1910_FBO Linux, x64, 64bit (optimized), Oracle 11g on Feb 1 2012 00:55:59 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (host01) > Info All Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING RFINAB 00:00:00 00:00:07 GGSCI (host01) > |
The Status should still be RUNNING.
This completes the configuration and operation of the basic heterogeneous unidirectional functionality of Oracle GoldenGate: Extract, Data Pump, and Replicat.
11. Managing the Oracle GoldenGate Environment (DB2-to-Oracle)
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:
11.1 Viewing Reports
View the process reports on the source.
View the process report for the Extract.
| Host02 - Linux |
GGSCI (host02) > Send Extract efinaa, Report Sending REPORT request to EXTRACT EFINAA ... Request processed. GGSCI (host02) > View Report efinaa ********************************************************************** Oracle GoldenGate Capture for DB2 Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230 Linux, x64, 64bit (optimized), DB2 9.7 on Apr 23 2012 06:10:15 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. Starting at 2012-10-03 14:07:46 *********************************************************************** ... many lines omitted for clarity ... 2012-10-03 10:40:02 INFO OGG-01021 Command received from GGSCI: REPORT. *********************************************************************** * ** Run Time Statistics ** * *********************************************************************** Report at 2012-10-03 21:59:59 (activity since 2012-10-03 15:48:27) Output to ./dirdat/aa: From Table USER01.ECONOMIC_ENTITY: # inserts: 235 # updates: 36 # deletes: 0 # discards: 0 From Table USER01.GDP_BY_YEAR: # inserts: 705 # updates: 0 # deletes: 235 # discards: 0 From Table USER01.GDP_GROWTH_BY_YEAR: # inserts: 705 # updates: 0 # deletes: 235 # discards: 0 ... many lines omitted for clarity ... GGSCI (host02) > |
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.
You can do the same thing to view the Data Pump pfinab report.
View the process reports for the target.
On the Red Host target, view the process reports for the Replicat.
| Host01 - Linux |
GGSCI (host01) > Send Replicat rfinab, Report Sending REPORT request to REPLICAT RFINAB ... Request processed. GGSCI (host01)> View Report rfinab *********************************************************************** Oracle GoldenGate Delivery for Oracle Version 11.2.1.0.0 OGGCORE_11.2.1.0.0_PLATFORMS_120131.1910_FBO Linux, x64, 64bit (optimized), Oracle 11g on Feb 1 2012 01:09:15 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. Starting at 2012-10-03 15:08:13 *********************************************************************** ... many lines omitted for clarity ... *********************************************************************** * ** 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 : 28 (x001c) IO Time : 2012-10-03 21:37:05.979872 IOType : 3 (x03) OrigNode : 255 (xff) TransInd : . (x02) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 0 AuditPos : 54018716 Continued : N (x00) RecCount : 1 (x01) 2012-10-03 21:37:05.979872 Delete Len 28 RBA 282706 Name: USER01.GDP_GROWTH_BY_YEAR ___________________________________________________________________ Reading ./dirdat/ab000000, current RBA 282810, 2151 records Report at 2012-10-03 22:03:59 (activity since 2012-10-03 15:48:37) From Table USER01.ECONOMIC_ENTITY to TRG_USER01.ECONOMIC_ENTITY: # inserts: 235 # updates: 36 # deletes: 0 # discards: 0 From Table USER01.GDP_BY_YEAR to TRG_USER01.GDP_BY_YEAR: # inserts: 705 # updates: 0 # deletes: 235 # discards: 0 From Table USER01.GDP_GROWTH_BY_YEAR to TRG_USER01.GDP_GROWTH_BY_YEAR: # inserts: 705 # updates: 0 # deletes: 235 # discards: 0 --More--(85%) |
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.
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.
11.2 Viewing Statistics
View statistics for the source Extract.
| Host02 - Linux |
GGSCI (host02) > Send Extract efinaa, Stats
Sending STATS request to EXTRACT EFINAA ...
Start of Statistics at 2012-10-03 22:06:52.
Output to ./dirdat/aa:
Extracting from USER01.ECONOMIC_ENTITY to USER01.ECONOMIC_ENTITY:
*** Total statistics since 2012-10-03 15:48:27 ***
Total inserts 235.00
Total updates 36.00
Total deletes 0.00
Total discards 0.00
Total operations 271.00
*** Daily statistics since 2012-10-03 15:48:27 ***
Total inserts 235.00
Total updates 36.00
Total deletes 0.00
Total discards 0.00
Total operations 271.00
*** Hourly statistics since 2012-10-03 22:00:00 ***
No database operations have been performed.
*** Latest statistics since 2012-10-03 15:48:27 ***
Total inserts 235.00
Total updates 36.00
Total deletes 0.00
Total discards 0.00
Total operations 271.00
Extracting from USER01.GDP_BY_YEAR to USER01.GDP_BY_YEAR:
*** Total statistics since 2012-10-03 15:48:27 ***
Total inserts 705.00
Total updates 0.00
Total deletes 235.00
Total discards 0.00
Total operations 940.00
*** Daily statistics since 2012-10-03 15:48:27 ***
Total inserts 705.00
Total updates 0.00
Total deletes 235.00
Total discards 0.00
Total operations 940.00
*** Hourly statistics since 2012-10-03 22:00:00 ***
No database operations have been performed.
*** Latest statistics since 2012-10-03 15:48:27 ***
Total inserts 705.00
Total updates 0.00
Total deletes 235.00
Total discards 0.00
Total operations 940.00
Extracting from USER01.GDP_GROWTH_BY_YEAR to USER01.GDP_GROWTH_BY_YEAR:
*** Total statistics since 2012-10-03 15:48:27 ***
Total inserts 705.00
Total updates 0.00
Total deletes 235.00
Total discards 0.00
Total operations 940.00
*** Daily statistics since 2012-10-03 15:48:27 ***
Total inserts 705.00
Total updates 0.00
Total deletes 235.00
Total discards 0.00
Total operations 940.00
*** Hourly statistics since 2012-10-03 22:00:00 ***
No database operations have been performed.
*** Latest statistics since 2012-10-03 15:48:27 ***
Total inserts 705.00
Total updates 0.00
Total deletes 235.00
Total discards 0.00
Total operations 940.00
End of Statistics.
GGSCI (host02) >
|
The command Stats efinaa does the same thing. If the process stops for any reason, all statistics are lost.
View statistics for the target Replicat.
| Host01 - Linux |
GGSCI (host01) > Send Replicat rfinab, Stats
Sending STATS request to REPLICAT RFINAB ...
Start of Statistics at 2012-10-03 22:09:55.
Replicating from USER01.ECONOMIC_ENTITY to TRG_USER01.ECONOMIC_ENTITY:
*** Total statistics since 2012-10-03 15:48:37 ***
Total inserts 235.00
Total updates 36.00
Total deletes 0.00
Total discards 0.00
Total operations 271.00
*** Daily statistics since 2012-10-03 15:48:37 ***
Total inserts 235.00
Total updates 36.00
Total deletes 0.00
Total discards 0.00
Total operations 271.00
*** Hourly statistics since 2012-10-03 22:00:00 ***
No database operations have been performed.
*** Latest statistics since 2012-10-03 15:48:37 ***
Total inserts 235.00
Total updates 36.00
Total deletes 0.00
Total discards 0.00
Total operations 271.00
Replicating from USER01.GDP_BY_YEAR to TRG_USER01.GDP_BY_YEAR:
*** Total statistics since 2012-10-03 15:48:37 ***
Total inserts 705.00
Total updates 0.00
Total deletes 235.00
Total discards 0.00
Total operations 940.00
*** Daily statistics since 2012-10-03 15:48:37 ***
Total inserts 705.00
Total updates 0.00
Total deletes 235.00
Total discards 0.00
Total operations 940.00
*** Hourly statistics since 2012-10-03 22:00:00 ***
No database operations have been performed.
*** Latest statistics since 2012-10-03 15:48:37 ***
Total inserts 705.00
Total updates 0.00
Total deletes 235.00
Total discards 0.00
Total operations 940.00
Replicating from USER01.GDP_GROWTH_BY_YEAR to TRG_USER01.GDP_GROWTH_BY_YEAR:
*** Total statistics since 2012-10-03 15:48:37 ***
Total inserts 705.00
Total updates 0.00
Total deletes 235.00
Total discards 0.00
Total operations 940.00
*** Daily statistics since 2012-10-03 15:48:37 ***
Total inserts 705.00
Total updates 0.00
Total deletes 235.00
Total discards 0.00
Total operations 940.00
*** Hourly statistics since 2012-10-03 22:00:00 ***
No database operations have been performed.
*** Latest statistics since 2012-10-03 15:48:37 ***
Total inserts 705.00
Total updates 0.00
Total deletes 235.00
Total discards 0.00
Total operations 940.00
End of Statistics.
GGSCI (host01) >
|
!!!CHECK!!! Notice that previously the statistics for the primary Extract were from schema USER01 to USER01 since a Data Pump was involved. Here it is clearer that it is from schema USER01 to TRG_USER01.
There are other kinds of statistics that are available as well.
11.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 efinaa, or Stop * to stop all of them on a host. Obviously you would replace efinaa 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 efinaa, 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."
Summary
- Install and configure the Oracle GoldenGate software
- Configure and start Extract, Data Pump, and Replicat processes in a heterogeneous environment, comprising the Oracle RDBMS and the IBM DB2 RDBMS
- Generate sample data and test the validity of the replication
- Manage the reports produced by the processes
- The same steps have been performed while replicating from Oracle to DB2 and from DB2 to Oracle
- 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
- Lead Curriculum Developer: Elio Bonazzi.
- Other Contributors: Richard Johnston, Hadi Koesnodihardjo, Simon Whitworth, Joe deBuzna, Chris Lawless.
Oracle GoldenGate can do far more than was demonstrated in this simple exercise.
In this tutorial, you have learned how to:
Resources
Credits
To help navigate this Oracle by Example, note the following:
- Hiding Header Buttons:
- Click the Title to hide the buttons in the header. To show the buttons again, simply click the Title again.
- Topic List Button:
- A list of all the topics. Click one of the topics to navigate to that section.
- Expand/Collapse All Topics:
- To show/hide all the detail for all the sections. By default, all topics are collapsed.
- Show/Hide All Images:
- To show/hide all the screenshots. By default, all images are displayed.
- Print:
- To print the content. The content currently displayed or hidden will be printed.
To navigate to a particular section in this tutorial, select the topic from the list.