Oracle GoldenGate on Linux: Unidirectional Replication from MySQL to Oracle 11gR2
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 ASCII files. This OBE will not use an Initial Load.
- Extract:
- Runs on the source to capture transaction data to trail files. There are two versions of Extract: Classic (this OBE) and Integrated.
- Data Pump:
- Optional, but highly recommended. Sends trail files from the source to the target over an IP network. Technically it is a secondary Extract.
- Replicat:
- Delivers data to the target database. Normally the Replicat runs on the target.
- Two Linux hosts: one source and one target. The example uses Oracle Enterprise Linux OEL 5.7, 64-bit. A different version of the Oracle GoldenGate software will run on Windows, or 32-bit Linux as well. It is possible to have the source and target be on the same host, but that is conceptually harder to visualize what is happening. If you do have only one host, it may be helpful to change the /etc/hosts file to make aliases for host01.example.com and host02.example.com.
- Oracle GoldenGate installed on both hosts
- Oracle GoldenGate for MySQL on Linux x86-64 ( Oracle Software Delivery Cloud ), version 11.2.1.0.1, part number V32399-01. This is the part number for 64-bit Intel Linux.
- Oracle GoldenGate for Oracle on Linux x86-64 ( Oracle Software Delivery Cloud ), version 11.2.1.0.3, part number V34339-01. This is the part number for 64-bit Intel Linux.
- Oracle MySQL 5.5 Database installed on the source (red) host01
- MySQL-client-5.5.27-1.rhel5.x86_64.rpm
- MySQL-server-5.5.27-1.rhel5.x86_64.rpm
- mysql-connector-odbc-5.2.2-1.rhel5.x86_64.rpm
- Oracle 11gR2 Database installed on the target (green) host02
- Have root access to the Linux software (write access to /etc)
- Have web access to download the software and documentation.
- Create a database instance with SID=orcl01 on host01, and a database instance with SID=orcl02 on host02.
- Modify tnsnames.ora on both hosts so that they can talk to each other by SID.
- 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: the source is MySQL and the target is Oracle 11gR2 database.
Time to Complete
Approximately 2 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: host01 (red) running MySQL and host02 (green) running Oracle 11gR2. Host01 has a user/schema ogguser1 with a password of pswd1u, and an administrator with a user/schema oggadm1 with a password of pswd1a.
Host02 has a user/schema ogguser2 with a password of pswd2u, and an administrator with a user/schema oggadm2 with a password of pswd2a. There are tables on host01 (TCUSTMER and TCUSTORD) that need to be replicated to host02. Pay attention to the color of the screen banners to know which commands are going to which host. Also note whether you are entering GGSCI commands, SQL commands, or OS commands (the prompt will guide you).
Once the unidirectional replication is working, there is an optional desire to go bidirectional. That requires an additional instance of the Oracle GoldenGate software (a second install directory).
In summary, the environment is:
| Host Name | Color | OS | Database | DB Name/ID | DB Users | DB Passwords | Mgr Port | Install Directory |
|---|---|---|---|---|---|---|---|---|
| host01 | Red | Linux 64-bit | MySQL 5.5 | oggdb1 | root ogguser1 oggadm1 |
pswd1r pswd1u pswd1a |
15001 | /u01/app/oracle/ogg01 |
| host02 | Green | Linux 64-bit | Oracle 11g | orcl02 | sys ogguser2 oggadm2 |
pswd2s pswd2u pswd2a |
15002 | /u01/app/oracle/ogg02 |
Do not confuse the root Linux OS user with the root MySQL DBA user.
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 MySQL Installation and Setup Guide. Read the Installation Guide for MySQL.
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 two software zip files 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. This will go into the target instance.
Go back and get the MySQL version of Oracle GoldenGate. Click Search Again.
Click Non Oracle Database.
Make sure you are looking at part number V32399-01 for "Oracle GoldenGate V11.2.1.0.1 for MySQL 5.x on Linux x86-64."
Click Download. This will go into the source instance.
Unpack the source software in its directory on host01 (red).
Create the installation directory to receive the Oracle GoldenGate for MySQL software.
| Host01 - MySQL on Linux |
[oggadm1@host01 ~]$ cd /u01/app/oracle [oggadm1@host01 oracle]$ mkdir ogg01 [oggadm1@host01 ogg01]$ cd ogg01/ [oggadm1@host01 ogg01]$ |
Copy (cp or scp) the downloaded V32399*.zip from wherever you downloaded it to ogg01/. Unzip it and untar (extract) it.
| Host01 - MySQL on Linux |
[oggadm1@host01 ogg01]$ cp /stage/V32399-01.zip . [oggadm1@host01 ogg01]$ unzip V32399-01.zip Archive: V32399-01.zip inflating: ggs_Linux_x64_MySQL_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 [oggadm1@host01 ogg01]$ tar -xvpf ggs_Linux_x64_MySQL_64bit.tar ./ ./extract ./server ./sqlldr.tpl ./zlib.txt ... many lines omitted for clarity ... ./freeBSD.txt ./convchk ./retrace ./dirprm/ ./dirprm/jagent.prm [oggadm1@host01 ogg01]$ |
Be mindful of the trailing dot in the copy command.
Start the GoldenGate Software Command Interface (GGSCI). Create the default empty subdirectories.
| Host01 - MySQL on Linux |
[oggadm1@host01 ogg01]$ ./ggsci Oracle GoldenGate Command Interpreter for MySQL Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230 Linux, x64, 64bit (optimized), MySQL Enterprise on Apr 23 2012 04:34:25 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (host01) 1> Create Subdirs Creating subdirectories under current directory /u01/app/oracle/ogg01 Parameter files /u01/app/oracle/ogg01/dirprm: already exists Report files /u01/app/oracle/ogg01/dirrpt: created Checkpoint files /u01/app/oracle/ogg01/dirchk: created Process status files /u01/app/oracle/ogg01/dirpcs: created SQL script files /u01/app/oracle/ogg01/dirsql: created Database definitions files /u01/app/oracle/ogg01/dirdef: created Extract data files /u01/app/oracle/ogg01/dirdat: created Temporary files /u01/app/oracle/ogg01/dirtmp: created Stdout files /u01/app/oracle/ogg01/dirout: created GGSCI (host01) 2> Exit [oggadm1@host01 ogg01]$ |
If a directory already exists, the installation leaves the contents of that directory alone.
Do the same steps for the other host. Unpack the target software in its directory on host02 (green).
Create the installation directory to receive the Oracle GoldenGate software. You might find it convenient to define $ORACLE_BASE and $OGG_HOME (optional).
| Host02 - Oracle 11g on Linux |
[oggadm2@host02 ~]$ cd /u01/app/oracle [oggadm2@host02 oracle]$ mkdir ogg02 [oggadm2@host02 ogg02]$ cd ogg02/ [oggadm2@host02 ogg02]$ |
Copy (cp or scp) the downloaded V34339*.zip from wherever you downloaded it to ogg02/. Unzip it and untar (extract) it.
| Host02 - Oracle 11g on Linux |
[oggadm2@host02 ogg02]$ cp /stage/V34339-01.zip . [oggadm2@host02 ogg02]$ unzip V34339-01.zip Archive: V34339-01.zip inflating: fbo_ggs_Linux_x64_ora11g_64bit.tar inflating: Oracle_GoldenGate_11.2.1.0.3_README.doc inflating: Oracle GoldenGate_11.2.1.0.3_README.txt inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.3.pdf [oggadm2@host02 ogg02]$ tar -xvpf fbo_ggs_Linux_x64_ora11g_64bit.tar UserExitExamples/ UserExitExamples/ExitDemo_more_recs/ UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.HPUX ... many lines omitted for clarity ... ulg.sql usrdecs.h zlib.txt [oggadm2@host02 ogg02]$ |
Start the GoldenGate Software Command Interface (GGSCI). Create the default empty subdirectories.
| Host02 - Oracle 11g on Linux |
[oggadm2@host02 ogg02]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (host02) 1> Create Subdirs Creating subdirectories under current directory /u01/app/oracle/ogg02 Parameter files /u01/app/oracle/ogg02/dirprm: already exists Report files /u01/app/oracle/ogg02/dirrpt: created Checkpoint files /u01/app/oracle/ogg02/dirchk: created Process status files /u01/app/oracle/ogg02/dirpcs: created SQL script files /u01/app/oracle/ogg02/dirsql: created Database definitions files /u01/app/oracle/ogg02/dirdef: created Extract data files /u01/app/oracle/ogg02/dirdat: created Temporary files /u01/app/oracle/ogg02/dirtmp: created Stdout files /u01/app/oracle/ogg02/dirout: created GGSCI (host02) 2> Exit [oggadm2@host02 ogg02]$ |
If a directory already exists, the installation leaves the contents of that directory alone.
You have successfully installed Oracle GoldenGate on Linux hosts host01 and host02.
2. Configuring the Environment
- DBLogin:
- Connects to the DB using the userid@SID/password specified. The SourceDB is only for MySQL databases. The @SID is only for Oracle databases, and 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:
2.1 Configuring the Source MySQL Database
This section must be done as the OS user who owns (installed) MySQL. That would usually be either root or an OS user named mysql. When done with this section 2.1, revert back to OS user oggadm1.
On the
| Host01 - MySQL on Linux |
[oggadm1@host01 ogg01] su - mysql Password: ******** [mysql@host01 ~] gedit .bashrc ... many lines omitted for clarity ... # User specific aliases and functions MYSQL_HOME=/usr/bin ORACLE_BASE=/u01/app/oracle ORACLE_SID=orcl01 ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1 PATH=$PATH:$ORACLE_HOME/bin:/usr/sbin LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_BASE/ogg01 export MYSQL_HOME ORACLE_SID ORACLE_BASE ORACLE_HOME PATH LD_LIBRARY_PATH [mysql@host01 ~] |
Your installation directories and environment variables may be different.
Edit the MySQL server configuration file. If the file does not yet exist, create the file /etc/my.cnf. You can find templates in /usr/share/mysql/my-small.cnf.
| Host01 - MySQL on Linux |
[mysql@host01 ~] gedit /etc/my.cnf
[mysqld]
log-bin=/var/lib/mysql/host01-bin
max_binlog_size=4096
binlog_format=row
socket=/tmp/mysql.sock
[client]
socket=/tmp/mysql.sock
[mysql@host01 ~]
|
You may have other lines in there as well.
As root, start the MySQL service. As mysql, verify that MySQL did start.
| Host01 - MySQL on Linux |
[mysql@host01 ~] su - root Password: ******** [root@host01 ~] service mysql start Starting MySQL.. [ OK ] [root@host01 ~] exit [mysql@host01 ~] mysqladmin version mysqladmin Ver 8.42 Distrib 5.5.27, for Linux on x86_64 Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Server version 5.5.27-log Protocol version 10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 1 min 48 sec Threads: 1 Questions: 1 Slow queries: 0 Opens: 33 Flush tables: 1 Open tables: 26 Queries per second avg: 0.009 [mysql@host01 ~] |
You can also see some interesting information with mysqladmin variables.
Create users, passwords, and privileges.
Make sure the MySQL DBA user root has a password. If it already does, then skip this sub-step and go on to making the other MySQL users. Use the users and passwords described in the Overview.
| Host01 - MySQL on Linux |
[mysql@host01 ~] mysqladmin -u root password 'pswd1r' [mysql@host01 ~] mysqladmin -u root -h host01.example.com password 'pswd1r' [mysql@host01 ~] |
If you already have a root password, then use that everywhere you see pswd1r.
Sign on as MySQL DBA user root and create the other users. The option -ppswd1r is actually -p<your_pswd> where <your_pswd> is your password in clear text. To make it prompt for and not display your password, just use -p.
| Host01 - MySQL on Linux |
[mysql@host01 ~] mysql -u root -ppswd1r Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.5.27-log MySQL Community Server (GPL) Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE USER 'ogguser1'@'localhost' IDENTIFIED BY 'pswd1u'; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER 'oggadm1'@'localhost' IDENTIFIED BY 'pswd1a'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO 'ogguser1'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO 'oggadm1'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> |
In real life, the users would probably have less privileges.
Create the database, and test it from MySQL and from the OS.
| Host01 - MySQL on Linux |
mysql> CREATE DATABASE oggdb1; Query OK, 1 row affected (0.00 sec) mysql> USE oggdb1; Database changed mysql> CREATE TABLE junk (this CHAR(5), that VARCHAR(50)); Query OK, 0 rows affected (0.00 sec) mysql> SHOW TABLES; +------------------+ | Tables_in_oggdb1 | +------------------+ | junk | +------------------+ 1 row in set (0.00 sec) mysql> DESCRIBE junk; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | this | char(5) | YES | | NULL | | | that | varchar(50) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> EXIT; Bye [mysql@host01 ~] mysqlshow -u oggadm1 -ppswd1a oggdb1 Database: oggdb1 +--------+ | Tables | +--------+ | junk | +--------+ [mysql@host01 ~] |
Verify that the MySQL ODBC connector is installed. As the root Linux user, run the following package manager query command:
| Host01 - MySQL on Linux |
[mysql@host01 ~] su - root Password: ******** [root@host01 ~] rpm -qa *odbc* mysql-connector-odbc-5.2.2-1.rhel5 [root@host01 ~] |
If it is not installed, fetch the rpm and install it.
If it doesn't already exist, as root, create the /usr/local/etc/odbc.ini file. Add (or verify) the following in the file:
| Host01 - MySQL on Linux |
[root@host01 ~] gedit /usr/local/etc/odbc.ini
[ODBC Data Sources]
OGGDB1 = MyODBC 3.51 Driver DSN
OGGDB1
Driver = /usr/lib/libmyodbc3.so
Description = Connector/ODBC 3.51 Driver DSN
Server = localhost
Port = 3306
User = oggadm1
Password = pswd1a
Database = oggdb1
Option = 3
Socket = /tmp/mysql.sock
[root@host01 ~]
|
Make sure Oracle GoldenGate OS administrators can read the MySQL /var/lib/mysql/host* log files. Modify the user (usermod) to add them (-a) to the MySQL group (-G mysql).
| Host01 - MySQL on Linux |
[root@host01 ~] usermod -a -G mysql oggadm1 [root@host01 ~] id oggadm1 uid=54321(oggadm1) gid=54321(oinstall) groups=54321(oinstall),54322(dba),103(mysql) [root@host01 mysql] exit [mysql@host01 ~] exit [oggadm1@host01 ~] exit |
You will need to exit out of that terminal session for this new group addition to take effect. To make sure it worked, you should be able to list the /var/lib/mysql/host* files as user oggadm1. Change oggadm1 to your OS user name throughout this step.
The database on host01 is configured to support Oracle Goldengate. Users on each database are provisioned: one (or more) user and an administrator for each host.
2.2 Configuring the Target Oracle 11gR2 Database
This section must be done in SQL*Plus as a database administrator (for example sys) with sysdba privileges.
Create the Oracle GoldenGate administrator and Oracle GoldenGate user/schema owners on host02.
| Host02 - Oracle 11g on Linux |
[oggadm2@host02 ogg02] sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 18 14:44:06 2012 SQL> CREATE USER ogguser2 IDENTIFIED BY pswd2u; User created. SQL> CREATE USER oggadm2 IDENTIFIED BY pswd2a; User created. SQL> GRANT CONNECT, RESOURCE, UNLIMITED TABLESPACE TO ogguser2; Grant succeeded. SQL> GRANT dba TO oggadm2; Grant succeeded. SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE (grantee=>'OGGADM2',privile ge_type=>'capture',grant_select_privileges=>true, do_grants=>TRUE); PL/SQL procedure successfully completed. SQL> exit [oggadm2@host02 ogg02] |
In real life, user oggadm2 would probably have fewer privileges.
Verify that you can talk to the target database.
Test database communication with tnsping.
| Host02 - Oracle 11g on Linux |
[oggadm2@host02 ogg02]$ tnsping orcl02;
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 17-SEP-2012 18:52
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(
HOST = host02.example.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl02.
example.com)))
OK (10 msec)
[oggadm2@host02 ogg02]$
|
The results should be OK. If it is, then skip to the next step. If it is not OK, then do the following to examine tnsnames.ora.
| Host02 - Oracle 11g on Linux |
[oggadm2@host02 ogg02]$ more $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_
1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL02 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = host02.example.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl02.example.com)
)
)
[oggadm2@host02 ogg02]$
|
You should have orcl02 defined in tnsnames.ora.
The databases on host01 and host02 are configured to support Oracle Goldengate. Users on each database are provisioned: one (or more) user and an administrator for each host.
2.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 - MySQL on Linux |
[oggadm1@host01 ~] cd $ORACLE_BASE/ogg01 [oggadm1@host01 ogg01] gedit GLOBALS -- Created by Joe Admin 10/11/2012 on host01 CheckpointTable oggdb1.oggchkpt [oggadm1@host01 ogg01] |
Checkpoint tables are only used by the Replicat, so this will not be used until the bidirectional section. Having it present and unused does not hurt anything.
Create the GLOBALS file on host02 (green) in the Oracle GoldenGate installation directory.
| Host02 - Oracle 11g on Linux |
[oggadm2@host02 ogg02]$ gedit GLOBALS
-- Created by Joe Admin 10/11/2012 on host02
CheckpointTable oggadm2.oggchkpt
[oggadm2@host02 ogg02]$
|
Any change to the GLOBALS file requires a restart of GGSCI.
Create the optional startup.oby (Obey) file on host01 (red) in the Oracle GoldenGate installation directory.
These are GGSCI commands that are done almost every time you start GGSCI, and they do not persist between sessions, so you will find yourself entering them many, many times, and therefore it is convenient to create a startup obey file.
If you have several different databases or schemas that you login to on a regular basis, you may want to make startup01.oby, startup02.oby, startup03.oby, and so on. This will be revisited later in the naming conventions of process files.
| Host01 - MySQL on Linux |
[oggadm1@host01 ogg01] gedit startup.oby
-- Created by Joe Admin 10/11/2012 on host01 (MySQL)
DBLogin SourceDB oggdb1, UserID oggadm1, Password pswd1a
Start Mgr
Info Mgr
Info CheckpointTable
Set Editor gedit
[oggadm1@host01 ogg01]
|
Create the optional startup.oby (Obey) file on host02 (green) in the Oracle GoldenGate installation directory.
| Host02 - Oracle 11g on Linux |
[oggadm2@host02 ogg02]$ gedit startup.oby
-- Created by Joe Admin 10/11/2012 on host02 (Oracle 11g)
DBLogin UserID oggadm2@orcl02, Password pswd2a
Start Mgr
Info Mgr
Info CheckpointTable
Set Editor gedit
[oggadm2@host02 ogg02]$
|
Create the Manager parameter (mgr.prm) file on host01 in dirprm/.
Start GGSCI. Edit the file with no extension. Add the two lines (plus comments).
| Host01 - MySQL on Linux |
[oggadm1@host01 ogg01] ./ggsci Oracle GoldenGate Command Interpreter for MySQL Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230 Linux, x64, 64bit (optimized), MySQL Enterprise on Apr 23 2012 04:34:25 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (host01) 1> Edit Param mgr -- Created by Joe Admin 10/11/2012 on host01 Port 15001 PurgeOldExtracts ./dirdat/*, UseCheckpoints GGSCI (host01) 2> Info mgr Manager is DOWN! GGSCI (host01) 3> |
Note: If you do it correctly, GGSCI automatically adds the .prm extension and stores the file in the dirprm/ directory. If you wrongly add the extension yourself, GGSCI converts the filename to UPPERCASE and stores it in the installation directory which renders the file practically unusable.
Create the Manager parameter (mgr.prm) file on host02 in dirprm/.
| Host02 - Oracle 11g on Linux |
[oggadm2@host02 ogg02]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (host02) 1> Edit Param mgr -- Created by Joe Admin 10/11/2012 on host02 Port 15002 PurgeOldExtracts ./dirdat/*, UseCheckpoints GGSCI (host02) 2> Info mgr Manager is DOWN! GGSCI (host02) 3> |
Notice that the port number is different from host01 to host02. If the Oracle GoldenGate instances are on different hosts, you don't have to make the ports different, as long as the combination of IP address plus port is unique. Nevertheless, it is a good practice to make the ports unique.
Start the Manager on host01 using the Obey files. Alternatively, you could type each of the lines every time you start GGSCI.
| Host01 - MySQL on Linux |
GGSCI (host01) 3> Obey startup.oby
GGSCI (host01) 4> -- Created by Joe Admin 10/11/2012 on host01
GGSCI (host01) 5> DBLogin SourceDB oggdb1, UserID oggadm1, Password pswd1a
Successfully logged into database.
GGSCI (host01) 6> Start Mgr
Manager started.
GGSCI (host01) 7> Info Mgr
Manager is running (IP port host01.example.com.15001).
GGSCI (host01) 8> Info CheckpointTable
No checkpoint table specified, using GLOBALS specification (oggdb1.oggchkpt)...
Checkpoint table oggdb1.oggchkpt does not exist.
GGSCI (host01) 9> Set Editor gedit
GGSCI (host01) 10>
|
You will create the checkpoint table in the next step.
Start the Manager on host02 using the Obey files.
| Host02 - Oracle 11g on Linux |
GGSCI (host02) 3> Obey startup.oby
GGSCI (host02) 4> -- Created by Joe Admin 10/11/2012 on host02
GGSCI (host02) 5> DBLogin UserID oggadm2@orcl02, Password pswd2a
Successfully logged into database.
GGSCI (host02) 6> Start Mgr
Manager started.
GGSCI (host02) 7> Info Mgr
Manager is running (IP port host02.example.com.15002).
GGSCI (host02) 8> Info CheckpointTable
No checkpoint table specified, using GLOBALS specification (oggadm2.oggchkpt)...
Checkpoint table oggadm2.oggchkpt does not exist.
GGSCI (host02) 9> Set Editor gedit
GGSCI (host02) 10>
|
You will create the checkpoint table in the next step.
The global and startup files are all created, and the background Manager processes are started. You can verify which processes are running at any time by entering the command:
GGSCI (host) > Info All
2.4 Creating Tables
Create the checkpoint table on host01. (This is not needed on the source, so this is will not be used for unidirectional replication, but will be used for bidirectional replication when host01 becomes the target.)
| Host01 - MySQL on Linux |
GGSCI (host01) 10> Add CheckpointTable No checkpoint table specified, using GLOBALS specification (oggdb1.oggchkpt). Successfully created checkpoint table oggdb1.oggchkpt. GGSCI (host01) 11> Exit [oggadm1@host01 ogg01] |
One checkpoint table will suffice for the whole schema.
Create the checkpoint table on host02.
| Host02 - Oracle 11g on Linux |
GGSCI (host02) 10> Add CheckpointTable No checkpoint table specified, using GLOBALS specification (oggadm2.oggchkpt). Successfully created checkpoint table oggadm2.oggchkpt. GGSCI (host02) 11> Exit [oggadm2@host02 ogg02]$ |
Create empty source sample tables on host01 in database oggdb1.
If you already have sample tables, you can use those. If not, Oracle GoldenGate software comes with a script demo_mysql_create.sql to create two small sample tables TCUSTMER and TCUSTORD.
| Host01 - MySQL on Linux |
[oggadm1@host01 ogg01] mysql -u ogguser1 -ppswd1u oggdb1 < demo_mysql_create.sql [oggadm1@host01 ogg01] mysqlshow -u ogguser1 -ppswd1u oggdb1 Database: oggdb1 +----------+ | Tables | +----------+ | TCUSTMER | | TCUSTORD | | junk | | oggchkpt | +----------+ [oggadm1@host01 ogg01] |
You are looking to see that the TCUST* tables were created. Notice that they are UPPERCASE.
Create empty target sample tables on host02 in schema ogguser2. You can use any SQL utility you like to run the script. If you have no preference, use sqlplus.
| Host02 - Oracle 11g on Linux |
[oggadm2@host02 ogg02] sqlplus ogguser2@orcl02/pswd2u @demo_ora_create.sql SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 18 14:44:06 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options DROP TABLE tcustmer * ERROR at line 1: ORA-00942: table or view does not exist Table created. DROP TABLE tcustord * ERROR at line 1: ORA-00942: table or view does not exist Table created. SQL> exit [oggadm2@host02 ogg02]$ |
The ORA-00942 "errors" are okay to ignore. If you run the script a second time you won't see those errors.
Warning! If you run the script a second time, it will drop the tables!
Oracle 11gR2 does support replicating DDL such as CREATE TABLE, and therefore the empty tables could be created on the target in other ways before any DML replication begins.
You can check on the tables available at any time by entering the commands:
GGSCI (host) > List Tables * GGSCI (host) > List Tables ogguser2.*
2.5 Creating Column Definitions (defgen)
Create the column mapping from source to target tables using the OS utility.
This utility is required if the tables have a different (heterogeneous) structure, but is still highly recommended even if the tables have the same (homogeneous) structure. If they do have the same structure, you may be able to use the AssumeTargetDefs parameter instead of the SourceDefs parameter. This is covered later.
Please review the File and Process Naming Conventions in the Overview Topic.
| Host01 - MySQL on Linux |
[oggadm1@host01 ogg01] gedit dirprm/dsalesab.prm -- defgen column definitions for Sales: TCUST* from host01 (a) to host02 (b) -- Created by Joe Admin on 10/11/2012 DefsFile dirdef/dsalesab.def, Purge SourceDB oggdb1, UserID oggadm1, Password pswd1a Table oggdb1.TCUST*; [oggadm1@host01 ogg01] ./defgen paramfile dirprm/dsalesab.prm *********************************************************************** Oracle GoldenGate Table Definition Generator for MySQL ... many lines omitted for clarity ... Expanding wildcard oggdb1.TCUST*: Retrieving definition for oggdb1.TCUSTMER Retrieving definition for oggdb1.TCUSTORD Definitions generated for 2 tables in dirdef/dsalesab.def [oggadm1@host01 ogg01] |
You can create definitions for a single table, multiple tables (using wildcards), an entire schema, or multiple schemas at a time. Having "excess" (that is, unused by Oracle GoldenGate) definitions in the file is not a problem. You may want to examine the contents of dirdef/dsalesab.def before sending it over to host02.
Copy the files from source host01 to target host02.
Use whatever method you wish to copy the .def files to the target dirdef directory: cut-n-paste, scp, ftp, and so on. The example will use secure shell copy (scp). Do not break the command line after /u01/app/oracle/og, the scp command is entered all on one line.
| Host01 - MySQL on Linux |
[oggadm1@host01 ogg01] scp dirdef/dsalesab.def oggadm2@host02:/u01/app/oracle/og g02/dirdef The authenticity of host 'host02 (192.0.2.29)' can't be established. RSA key fingerprint is 8e:60:d0:a7:fc:55:6e:d9:81:bb:c9:90:19:f4:a8:11. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added 'host02,192.0.2.29' (RSA) to the list of known hosts. oggadm2@host02's password: ******** dsalesab.def 100% 1866 1.8KB/s 00:00 [oggadm1@host01 ogg01] |
Of course you replace the asterisks with your password, for example pswd2a.
Make sure the files arrived intact on host02 in the proper directory.
| Host02 - Oracle 11g on Linux |
[oggadm2@host02 ogg02]$ ls -l dirdef
total 4
-rw-r--r-- 1 oggadm2 oinstall 1877 Sep 18 13:43 dsalesab.def
[oggadm2@host02 ogg02]$
|
The column definitions are created and copied. If the table structure changes, you need to do this again.
3. Configuring Data Capture Using Extract
- -- :
- A comment. Useful for self-documenting files, as well as storing parameters inline that may be used later.
- ExtTrail:
- Not really "external" if you are using a data pump.
- aa:
- A sequence number is appended 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:
- This log destination file name is from /etc/my.cnf, parameter log-bin=. For MySQL only, AltLogDest replaces the GGSCI Add TranData command that is done on the Oracle databases. Also TranLogOptions is 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:
3.1 Configuring the Primary Extract
On source host01, create the primary Extract parameter file.
Using the naming conventions discussed in the Overview, the path/filename.ext will be dirprm/esalesaa.prm. The path and extension will be added automatically. (The primary extract can write a trail file directly to the target host, in which case the suffix would be "ab", but since it is going to a local data pump before leaving the source host, the suffix is "aa". All of this is just convention, you can name the processes anything you want.)
| Host01 - MySQL on Linux |
[oggadm1@host01 ogg01] ./ggsci Oracle GoldenGate Command Interpreter for Oracle ... many lines omitted for clarity ... GGSCI (host01) 1> Obey startup.oby ... many lines omitted for clarity ... but make sure everything started. GGSCI (host01) > Edit Param esalesaa -- Primary Extract from host01 to host02 -- Created by Joe Admin on 10/11/2012 Extract esalesaa ExtTrail ./dirdat/aa SourceDB oggdb1, UserID oggadm1, Password pswd1a TranLogOptions AltLogDest /var/lib/mysql/host01-bin.index Table oggdb1.TCUST*; 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 esalesaa any time.
Create the Extract group and the local Extract trail file.
| Host01 - MySQL on Linux |
GGSCI (host01) > Add Extract esalesaa, TranLog, Begin Now EXTRACT added. GGSCI (host01) > Add ExtTrail ./dirdat/aa, Extract esalesaa, Megabytes 5 EXTTRAIL added. GGSCI (host01) > |
The Megabytes 5 is optional. The default is 100 Megabytes.
The primary Extract has been created and configured, but not started. Leave GGSCI running for the next step.
3.2 Configuring the Data Pump
Create the secondary Extract (data pump) parameter file.
Using the naming conventions discussed in the Overview, the path/filename.ext will be dirprm/psalesab.prm. The path and extension will be added automatically.
| Host01 - MySQL on Linux |
GGSCI (host01) > Edit Param psalesab
-- Data pump (secondary Extract) from host01 to host02
-- Created by Joe Admin on 10/11/2012
Extract psalesab
RmtHost host02, MgrPort 15002, Compress
RmtTrail ./dirdat/ab
Passthru
Table oggdb1.TCUST*;
GGSCI (host01) >
|
This Table schema is the source.
You can check your work by entering View Param psalesab any time.
Create the data pump group and the remote Extract trail file.
| Host01 - MySQL on Linux |
GGSCI (host01) > Add Extract psalesab, ExtTrailSource ./dirdat/aa EXTRACT added. GGSCI (host01) > Add RmtTrail ./dirdat/ab, Extract psalesab, Megabytes 5 RMTTRAIL added. GGSCI (host01) > |
The Megabytes 5 is optional. The default is 100 Megabytes.
The data pump reads from the local trail file aa and writes to the remote trail file ab. The remote trail file that will be created will be named dirdat/ab000000, then when that one fills up the next will be dirdat/ab000001, then dirdat/ab000002, and so on. Since the two sets of trail files are on different hosts (even though they are in directories with the same names), the files could be named the same thing (for example ab). The different file name is chosen just to illustrate that the parameter RmtTrail is creating a different trail file.
The secondary Extract has been created and configured, but not started. Leave GGSCI running for the next step.
3.3 Verify the Extract Processes (Optional)
Make sure the Extract processes were created and registered correctly.
| Host01 - MySQL on Linux |
GGSCI (host01) > Info All
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED ESALESAA 00:00:00 00:03:24
EXTRACT STOPPED PSALESAB 00:00:00 00:01:22
GGSCI (host01) >
|
Since nothing other than the Manager is started yet, the Extract Status should say STOPPED.
Make sure the trail files were created and registered correctly.
| Host01 - MySQL on Linux |
GGSCI (host01) > Info ExtTrail *
Extract Trail: ./dirdat/aa
Extract: ESALESAA
Seqno: 0
RBA: 0
File Size: 5M
Extract Trail: ./dirdat/ab
Extract: PSALESAB
Seqno: 0
RBA: 0
File Size: 5M
GGSCI (host01) >
|
Since nothing has started yet, the sequence numbers (Seqno) and relative byte addresses (RBA) should say 0.
Leave GGSCI running for the next step.
4. Configuring Data Delivery Using Replicat
Data delivery, also known as Replicat (the "e" is left off intentionally), is done at the target side. To configure data delivery, perform the following steps:
4.1 Configuring the Replicat
On target host02, create the Replicat parameter file.
| Host02 - Oracle 11g on Linux |
[oggadm2@host02 ogg01]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle ... many lines omitted for clarity ... GGSCI (host02) 1> Obey startup.oby ... many lines omitted for clarity ... but make sure everything started. GGSCI (host02) > Edit Param rsalesab -- Delivery from host01 to host02 -- Created by Joe Admin on 10/11/2012 Replicat rsalesab UserID oggadm2@orcl02, password pswd2a -- AssumeTargetDefs SourceDefs dirdef/dsalesab.def DiscardFile dirrpt/rsalesab.dsc, Append Map oggdb1.TCUSTMER, Target ogguser2.tcustmer; Map oggdb1.*, Target ogguser2.*; GGSCI (host02) > |
The Map statement can be simple (as shown here), or very complex, transforming columns and fields. For example, you could combine three source columns first_name, middle_init, last_name into a single target column full_name.
Note the UPPERCASE TCUSTMER on the MySQL source side, and the lowercase tcustmer on the Oracle 11g target side. Even though MySQL is case-sensitive, it does not require "quotes" around the names. That kind of picky syntax makes the wildcards for object naming more attractive.
Create the Replicat process.
| Host02 - Oracle 11g on Linux |
GGSCI (host02) > Add Replicat rsalesab, ExtTrail ./dirdat/ab
REPLICAT added.
GGSCI (host02) >
|
You can check your work by entering View Param rsalesab any time.
Make sure the Replicat process was created and registered correctly.
| Host02 - Oracle 11g on Linux |
GGSCI (host02) > Info All
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED RSALESAB 00:00:00 00:00:33
GGSCI (host02) >
|
Since nothing other than the Manager is started yet, the Replicat Status should say STOPPED.
4.2 Starting All Processes
Start all Extract processes on the source.
| Host01 - MySQL on Linux |
GGSCI (host01) > Start Extract *
Sending START request to MANAGER ...
EXTRACT ESALESAA starting
Sending START request to MANAGER ...
EXTRACT PSALESAB starting
GGSCI (host01) >
|
Alternatively, you could have entered, Start *, or Start e* and Start p*.
Start all Replicat processes on the target.
| Host02 - Oracle 11g on Linux |
GGSCI (host02) > Start Replicat *
Sending START request to MANAGER ...
REPLICAT RSALESAB starting
GGSCI (host02) >
|
Alternatively, you could have entered, Start *, or Start rsales*.
Display information about all processes on the target.
Display summary information.
| Host02 - Oracle 11g on Linux |
GGSCI (host02) > Info All
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RSALESAB 00:00:00 00:00:03
GGSCI (host02) >
|
Display more information.
| Host02 - Oracle 11g on Linux |
GGSCI (host02) > Info rsalesab
REPLICAT RSALESAB Last Started 2012-09-19 19:13 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:04 ago)
Log Read Checkpoint File ./dirdat/ab000001
2012-10-04 18:25:55.695076 RBA 1538
GGSCI (host02) >
|
Display the most detailed information.
| Host02 - Oracle 11g on Linux |
GGSCI (host02) > Info rsalesab, Detail
REPLICAT RSALESAB Last Started 2012-09-19 19:13 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:09 ago)
Log Read Checkpoint File ./dirdat/ab000000
First Record RBA 0
Extract Source Begin End
./dirdat/ab000000 * Initialized * First Record
./dirdat/ab000000 * Initialized * First Record
Current directory /u01/app/oracle/ogg02
Report file /u01/app/oracle/ogg02/dirrpt/RSALESAB.rpt
Parameter file /u01/app/oracle/ogg02/dirprm/rsalesab.prm
Checkpoint file /u01/app/oracle/ogg02/dirchk/RSALESAB.cpr
Checkpoint table oggadm2.oggchkpt
Process file /u01/app/oracle/ogg02/dirpcs/RSALESAB.pcr
Stdout file /u01/app/oracle/ogg02/dirout/RSALESAB.out
Error log /u01/app/oracle/ogg02/ggserr.log
GGSCI (host02) >
|
In all cases, the Status should be RUNNING, and the time since the last update or checkpoint should be under 10 seconds.
Display information about all processes on the source.
Display summary information.
| Host01 - MySQL on Linux |
GGSCI (host01) > Info All
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ESALESAA 00:00:00 00:00:04
EXTRACT RUNNING PSALESAB 00:00:00 00:00:05
GGSCI (host01) >
|
Display detailed information.
| Host01 - MySQL on Linux |
GGSCI (host01) > Info Extract * EXTRACT ESALESAA Last Started 2012-10-04 19:05 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:09 ago) VAM Read Checkpoint 2012-10-04 18:17:09.149937 EXTRACT PSALESAB Last Started 2012-10-04 19:05 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:04 ago) Log Read Checkpoint File ./dirdat/aa000000 2012-10-04 19:05:16.056626 RBA 1012 GGSCI (host01) > Exit [oggadm1@host01 ogg01] |
Similar to the Replicat, the Status should be RUNNING, and the time since the last update or checkpoint should be under 10 seconds.
Everything should show a status of RUNNING. The source tables are still empty. No data has flowed yet, nothing has replicated yet.
5. Generating Data
The Oracle GoldenGate software comes with SQL scripts to generate sample data traffic. There is a difference in how you replicate an existing table (more complicated) versus starting with a new empty table (simpler). To generate sample data against an empty set of tables, perform the following steps:
5.1 Generating INSERTs (Initial Load)
Run the SQL script to INSERT rows.
On source host01, at the OS prompt, run the demo_mysql_insert.sql script in sqlplus.
| Host01 - MySQL on Linux |
[oggadm1@host01 ogg01] mysql -u ogguser1 -ppswd1u oggdb1 < demo_mysql_insert.sql
[oggadm1@host01 ogg01]
|
There should not be any errors.
Verify that the rows were inserted into the source tables. Remember, the table names are CASE SENSITIVE.
| Host01 - MySQL on Linux |
[oggadm1@host01 ogg01] mysql -u ogguser1 -ppswd1u oggdb1 mysql> SELECT * FROM TCUSTMER; +-----------+------------------+---------+-------+ | CUST_CODE | NAME | CITY | STATE | +-----------+------------------+---------+-------+ | JANE | ROCKY FLYER INC. | DENVER | CO | | WILL | BG SOFTWARE CO. | SEATTLE | WA | +-----------+------------------+---------+-------+ 2 rows in set (0.00 sec) SQL> SELECT * FROM TCUSTORD; +-----------+---------------------+--------------+----------+---------------+--- -------------+----------------+ | CUST_CODE | ORDER_DATE | PRODUCT_CODE | ORDER_ID | PRODUCT_PRICE | PR ODUCT_AMOUNT | TRANSACTION_ID | +-----------+---------------------+--------------+----------+---------------+--- -------------+----------------+ | JANE | 1995-11-11 13:52:00 | PLANE | 256 | 133300.00 | 1 | 100 | | WILL | 1994-09-30 15:33:00 | CAR | 144 | 17520.00 | 3 | 100 | +-----------+---------------------+--------------+----------+---------------+--- -------------+----------------+ 2 rows in set (0.00 sec) SQL> exit [oggadm1@host01 ogg01] |
Verify that the source GGSCI Extract processes are still running.
On source host01, enter the following command:
| Host01 - MySQL on Linux |
[oggadm1@host01 ogg01] ./ggsci GGSCI (host01) 1> Info All Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING ESALESAA 00:00:00 00:00:00 EXTRACT RUNNING PSALESAB 00:00:00 00:00:06 GGSCI (host01) 2> Exit [oggadm1@host01 ogg01] |
If the Status says ABENDED, then check the process reports (covered as a later topic) to see what the error was.
Verify that the target GGSCI Replicat processes are still running.
On target host02, GGSCI should still be running. Enter the following command:
| Host02 - Oracle 11g on Linux |
GGSCI (host02) > Info All Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING RSALESAB 00:00:00 00:00:05 GGSCI (host02) > Exit [oggadm2@host02 ogg02] |
If the Status says ABENDED, then check the process reports (covered as a later topic) to see what the error was.
Verify that rows have been inserted into the target tables.
On target host02, at the OS prompt, run the following SQL queries:
| Host02 - Oracle 11g on Linux |
[oggadm2@host02 ogg02] sqlplus ogguser2@orcl02/pswd2u SQL> SELECT * FROM tcustmer; CUST_CODE NAME CITY STATE ---------- ------------------------------ -------------------- ------ WILL BG SOFTWARE CO. SEATTLE WA JANE ROCKY FLYER INC. DENVER CO SQL> SELECT * FROM tcustord; CUST ORDER_DAT PRODUCT_ ORDER_ID PRODUCT_PRICE PRODUCT_AMOUNT TRANSACTION_ID ---- --------- -------- ---------- ------------- -------------- -------------- WILL 30-SEP-94 CAR 144 17520 3 100 JANE 11-NOV-95 PLANE 256 133300 1 100 SQL> exit [oggadm2@host02 ogg02] |
The source and target database tcust* tables should match. Do not worry if the row order is different. In Oracle 11g, if you do not explicitly ask for an ORDER BY, do not expect one.
There are alternative ways of doing the initial load on the target if the source already has rows in it, but this is the simplest way when both tables are initially empty.
5.2 Generating UPDATEs/DELETEs
Run the SQL script to UPDATE and DELETE rows.
On source host01, at the OS prompt, run the demo_ora_misc.sql script in sqlplus.
| Host01 - MySQL on Linux |
[oggadm1@host01 ogg01] mysql -u ogguser1 -ppswd1u oggdb1 < demo_mysql_misc.sql
[oggadm1@host01 ogg01]
|
There should not be any errors.
Verify that the rows were updated and deleted in the source tables.
| Host01 - MySQL on Linux |
[oggadm1@host01 ogg01] mysql -u ogguser1 -ppswd1u oggdb1 mysql> SELECT * FROM TCUSTMER; +-----------+--------------------+-------------+-------+ | CUST_CODE | NAME | CITY | STATE | +-----------+--------------------+-------------+-------+ | ANN | ANN'S BOATS | NEW YORK | NY | | BILL | BILL'S USED CARS | DENVER | CO | | DAVE | DAVE'S PLANES INC. | TALLAHASSEE | FL | | JANE | ROCKY FLYER INC. | DENVER | CO | | WILL | BG SOFTWARE CO. | SEATTLE | WA | +-----------+--------------------+-------------+-------+ 5 rows in set (0.00 sec) mysql> SELECT * FROM TCUSTORD; +-----------+---------------------+--------------+----------+---------------+--- -------------+----------------+ | CUST_CODE | ORDER_DATE | PRODUCT_CODE | ORDER_ID | PRODUCT_PRICE | PR ODUCT_AMOUNT | TRANSACTION_ID | +-----------+---------------------+--------------+----------+---------------+--- -------------+----------------+ | BILL | 1995-12-31 15:00:00 | CAR | 765 | 14000.00 | 3 | 100 | | BILL | 1996-01-01 00:00:00 | TRUCK | 333 | 25000.00 | 15 | 100 | | WILL | 1994-09-30 15:33:00 | CAR | 144 | 16520.00 | 3 | 100 | +-----------+---------------------+--------------+----------+---------------+--- -------------+----------------+ 3 rows in set (0.00 sec) mysql> exit [oggadm1@host01 ogg01] |
Three rows inserted into TCUSTMER, three rows inserted into TCUSTORD, four rows updated in TCUSTORD, two rows deleted from TCUSTORD.
Verify that the source GGSCI Extract processes are still running.
On source host01, enter the following command:
| Host01 - MySQL on Linux |
[oggadm1@host01 ogg01] ./ggsci GGSCI (host01) 1> Info All Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING ESALESAB 00:00:00 00:00:09 EXTRACT RUNNING PSALESAB 00:00:00 00:00:01 GGSCI (host01) 2> |
If the initial INSERTs worked, the odds are that the UPDATEs and DELETEs will work.
Verify that the target GGSCI Replicat processes are still running.
On target host02, GGSCI should still be running. Enter the following command:
| Host02 - Oracle 11g on Linux |
GGSCI (host02) > Info All
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RSALESAB 00:00:00 00:00:09
GGSCI (host02) >
|
The Status should still be RUNNING.
Verify that rows have been updated and deleted in the target tables.
On target host02, run the following SQL queries:
| Host02 - Oracle 11g on Linux |
[oggadm2@host02 ogg02] sqlplus ogguser2@orcl02/pswd2u SQL> SELECT * FROM tcustmer; CUST_CODE NAME CITY STATE ---------- ------------------------------ -------------------- ------ WILL BG SOFTWARE CO. SEATTLE WA JANE ROCKY FLYER INC. DENVER CO DAVE DAVE'S PLANES INC. TALLAHASSEE FL BILL BILL'S USED CARS DENVER CO ANN ANN'S BOATS NEW YORK NY SQL> SELECT * FROM tcustord; CUST ORDER_DAT PRODUCT_ ORDER_ID PRODUCT_PRICE PRODUCT_AMOUNT TRANSACTION_ID ---- --------- -------- ---------- ------------- -------------- -------------- WILL 30-SEP-94 CAR 144 16520 3 100 BILL 31-DEC-95 CAR 765 14000 3 100 BILL 01-JAN-96 TRUCK 333 25000 15 100 SQL> exit [oggadm2@host02 ogg02] |
You might notice that there is an implied ORDER BY for MySQL that is not present in Oracle 11g, so the sort order in the tables may be different.
This completes the configuration and operation of the basic unidirectional functionality of Oracle GoldenGate: Extract, Data Pump, and Replicat.
6. Managing the Oracle GoldenGate Environment
There are reports generated during and after the processes run. These reports contain information, warnings, and errors (if any) related to that run of the process. To view the reports, perform the following steps:
6.1 Viewing Reports
View the process reports on the source.
View the process report for the Extract.
| Host01 - MySQL on Linux |
GGSCI (host01) 2> Send Extract esalesaa, Report Sending REPORT request to EXTRACT ESALESAA ... Request processed. GGSCI (host01) 3> View Report esalesaa *********************************************************************** Oracle GoldenGate Capture for MySQL Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230 Linux, x64, 64bit (optimized), MySQL Enterprise on Apr 23 2012 05:23:34 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. Starting at 2012-10-05 14:21:51 *********************************************************************** Operating System Version: Linux Version #1 SMP Wed Jul 27 21:02:33 EDT 2011, Release 2.6.32-200.13.1.el5uek Node: host01.example.com Machine: x86_64 soft limit hard limit Address Space Size : unlimited unlimited Heap Size : unlimited unlimited File Size : unlimited unlimited CPU Time : unlimited unlimited Process id: 9979 Description: *********************************************************************** ** Running with the following parameters ** *********************************************************************** ESALESAA.rpt (25%) |
You can press [Enter] to move one line at a time, or press [Space] to move a page at a time. Go to the bottom of the report.
| Host01 - MySQL on Linux |
***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************
Report at 2012-10-05 14:51:09 (activity since 2012-10-05 14:24:18)
Output to ./dirdat/aa:
From Table oggdb1.TCUSTMER:
# inserts: 5
# updates: 1
# deletes: 0
# discards: 0
From Table oggdb1.TCUSTORD:
# inserts: 5
# updates: 3
# deletes: 2
# discards: 0
***********************************************************************
** Run Time Warnings **
***********************************************************************
GGSCI (host01) 4>
|
You can do the same thing to view the Data Pump psalesab report.
View the process reports for the target.
On the target host02, view the process reports for the Replicat.
| Host02 - Oracle 11g on Linux |
GGSCI (host02) > Send Replicat rsalesab, Report Sending REPORT request to REPLICAT RSALESAB ... Request processed. GGSCI (host02)> View Report rsalesab *********************************************************************** Oracle GoldenGate Delivery for Oracle Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:37:31 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. Starting at 2012-10-05 14:22:18 *********************************************************************** Operating System Version: Linux Version #1 SMP Wed Jul 27 21:02:33 EDT 2011, Release 2.6.32-200.13.1.el5uek Node: host02.example.com Machine: x86_64 soft limit hard limit Address Space Size : unlimited unlimited Heap Size : unlimited unlimited File Size : unlimited unlimited CPU Time : unlimited unlimited Process id: 23557 Description: *********************************************************************** ** Running with the following parameters ** *********************************************************************** --More--(25%) |
You can press [Enter] to move one line at a time, or press [Space] to move a page at a time. Go to the bottom of the report.
| Host02 - Oracle 11g on Linux |
***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************
Last record for the last committed transaction is the following:
___________________________________________________________________
Trail name : ./dirdat/ab000000
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: B (x42)
RecLength : 61 (x003d) IO Time : 2012-09-20 02:04:41.000792
IOType : 3 (x03) OrigNode : 255 (xff)
TransInd : . (x02) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 20 AuditPos : 6237080
Continued : N (x00) RecCount : 1 (x01)
2012-09-20 02:04:41.000792 Delete Len 61 RBA 3775
Name: oggdb1.TCUSTORD
___________________________________________________________________
Reading ./dirdat/ab000000, current RBA 3932, 16 records
Report at 2012-10-05 14:54:23 (activity since 2012-10-05 14:24:19)
From Table oggdb1.TCUSTMER to OGGUSER2.TCUSTMER:
# inserts: 5
# updates: 1
# deletes: 0
# discards: 0
From Table oggdb1.TCUSTORD to OGGUSER2.TCUSTORD:
# inserts: 5
# updates: 3
# deletes: 2
# discards: 0
***********************************************************************
** Run Time Warnings **
***********************************************************************
GGSCI (host02) >
|
The count of inserts/updates/deletes for the Replicat should match the number for the Extract. If there were any discards, they should be examined for the reason why they were discarded.
Reports are generated when you ask for a report to be sent (as you just did), or when a process stops. Reports are named/numbered processname.rpt for the most current one, and then processname0.rpt, processname1.rpt, processname2.rpt for the most recent, up to processname9.rpt for the oldest. As each new report is created, all the old reports "move down one" getting renamed/renumbered until the oldest is discarded. No more than 11 (the most recent with no number and the previous 0-9) are kept at a time.
6.2 Viewing Statistics
View statistics for the source Extract.
| Host01 - MySQL on Linux |
GGSCI (host01) > Send Extract esalesaa, Stats
Sending STATS request to EXTRACT ESALESAA ...
Start of Statistics at 2012-09-20 14:08:06.
Output to ./dirdat/aa:
Extracting from oggdb1.TCUSTMER to oggdb1.TCUSTMER:
*** Total statistics since 2012-09-19 19:27:05 ***
Total inserts 5.00
Total updates 1.00
Total deletes 0.00
Total discards 0.00
Total operations 6.00
*** Daily statistics since 2012-09-20 00:00:00 ***
Total inserts 3.00
Total updates 1.00
Total deletes 0.00
Total discards 0.00
Total operations 4.00
*** Hourly statistics since 2012-09-20 14:00:00 ***
No database operations have been performed.
*** Latest statistics since 2012-09-19 19:27:05 ***
Total inserts 5.00
Total updates 1.00
Total deletes 0.00
Total discards 0.00
Total operations 6.00
Extracting from oggdb1.TCUSTORD to oggdb1.TCUSTORD:
*** Total statistics since 2012-09-19 19:27:05 ***
Total inserts 5.00
Total updates 3.00
Total deletes 2.00
Total discards 0.00
Total operations 10.00
*** Daily statistics since 2012-09-20 00:00:00 ***
Total inserts 3.00
Total updates 3.00
Total deletes 2.00
Total discards 0.00
Total operations 8.00
*** Hourly statistics since 2012-09-20 14:00:00 ***
No database operations have been performed.
*** Latest statistics since 2012-09-19 19:27:05 ***
Total inserts 5.00
Total updates 3.00
Total deletes 2.00
Total discards 0.00
Total operations 10.00
End of Statistics.
GGSCI (host01) >
|
The command Stats esalesaa does the same thing. If the process stops for any reason, all statistics are lost. Depending on when you do the stats compared to when you did the DML, you may or may not have Hourly statistics.
View statistics for the target Replicat.
| Host02 - Oracle 11g on Linux |
GGSCI (host02) > Send Replicat rsalesab, Stats
Sending STATS request to REPLICAT RSALESAB ...
Start of Statistics at 2012-09-20 14:10:51.
Replicating from oggdb1.TCUSTMER to OGGUSER2.TCUSTMER:
*** Total statistics since 2012-09-19 19:49:15 ***
Total inserts 5.00
Total updates 1.00
Total deletes 0.00
Total discards 0.00
Total operations 6.00
*** Daily statistics since 2012-09-20 00:00:00 ***
Total inserts 3.00
Total updates 1.00
Total deletes 0.00
Total discards 0.00
Total operations 4.00
*** Hourly statistics since 2012-09-20 14:00:00 ***
No database operations have been performed.
*** Latest statistics since 2012-09-19 19:49:15 ***
Total inserts 5.00
Total updates 1.00
Total deletes 0.00
Total discards 0.00
Total operations 6.00
Replicating from oggdb1.TCUSTORD to OGGUSER2.TCUSTORD:
*** Total statistics since 2012-09-19 19:49:15 ***
Total inserts 5.00
Total updates 3.00
Total deletes 2.00
Total discards 0.00
Total operations 10.00
*** Daily statistics since 2012-09-20 00:00:00 ***
Total inserts 3.00
Total updates 3.00
Total deletes 2.00
Total discards 0.00
Total operations 8.00
*** Hourly statistics since 2012-09-20 14:00:00 ***
No database operations have been performed.
*** Latest statistics since 2012-09-19 19:49:15 ***
Total inserts 5.00
Total updates 3.00
Total deletes 2.00
Total discards 0.00
Total operations 10.00
End of Statistics.
GGSCI (host02) >
|
Notice that previously the statistics for the primary Extract were from schema oggdb1 to oggdb1 since a Data Pump was involved (stayed local to the source host). Here in the Replicat it is clearer that it is from database oggdb1 to schema OGGUSER2.
There are other kinds of statistics that are available as well.
6.3 Stopping and Deleting Processes
Do not do this yet, but if you ever needed to stop an individual process, you simply enter Stop Extract esalesaa, or Stop * to stop all of them on a host. Obviously you would replace esalesaa with the process name you wish to stop.
Do not do this yet, but if you ever needed to delete an individual process, you must first make sure you are connected to the database (DBLogin) and you enter Delete Extract esalesaa, or Delete ER * to delete all of them on a host. GGSCI will prompt you, "Are you sure?" and you say, y. You can eliminate the question by appending an exclamation point to the end of the command. For example, Delete ER * ! assumes the answer is "yes."
Summary
- Install and configure the Oracle GoldenGate software
- Configure and start Extract, Data Pump, and Replicat processes
- Generate sample data and test the validity of the replication
- Manage the reports produced by the processes
- 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: Steve Friedberg.
- 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
Help topics relevant to the topic of this tutorial:
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.