Tags

Installing Oracle Goldengate (OGG) in Windows 7 is pretty much easier than Linux because most configurations are done via GUI tools:

INITIAL PREPARATIONS

1. Download and install MySQL client (I download mine via the MySQL Workbench)

http://dev.mysql.com/downloads/

2. Download and install MySQL ODBC driver (This is to ensure Windows 7 is able to communicate with the MySQL database)

http://dev.mysql.com/downloads/connector/odbc/

3. Download and extract GoldenGate for Windows 7 in a folder

http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html

During installation of MySQL, I made the settings as default to simplify the process but you may go change certain options if you like. Just make sure that you remember the port you assign to MySQL (default is 3306).

The scenario I am going to demonstrate is for initial loading. This means migration a table from a database to an empty table in another database. Usually, the other database would be located on a remote location. But for this example, I will be using a loopback address meaning both source and target tables are in the same system.

INITIAL CONFIGURATION

Locate the installation folder of MySQL and look for the file my.ini (In Windows x64, it could have several folders located in ProgramData, Program Files (x84) or Program Files, make sure you ONLY have one folder containing the configuration file my.ini). MySQL can get confused if more than one my.ini exists in the folder. Mine is located in Program Files. If two my.ini files exist in two different folders, make sure at least that both have the same contents.

1. Open my.ini with a text editor and look for [mysqld] and enter the following lines:

log-bin = “C:/Program Files/MySQL/MySQL Server 5.6/log/localhost.bin”
max_binlog_size=4096
binlog_format=row
socket=/tmp/mysql.sock

Notes: MySQL usually reads the transaction log files to do the changes. It’s best to set this up early. However, the example I am going to demonstrate should directly obtain the data from the tables themselves.mysqld

2. After extracting the Goldengate files in a folder, open the Goldengate directory and look for the ggsci.exe program. Run it and a command line should pop up, enter CREATE SUBDIRS in the command line to create the folders it needs.

CREATING DATABASES

1. Create a database in MySQL as your source database. It should contain at least one table, Preferably, use a script just in case you mess up your configuration later on. MySQL makes this easy by saving your scripts. As an example, I used the following script to create a source table named ‘project’ and a table ‘nbarankings2013’:

CREATE DATABASE IF NOT EXISTS project;
SHOW DATABASES;
USE project;

DROP TABLE IF EXISTS NBArankings2013;

CREATE TABLE NBArankings2013(
TeamID int(10) NOT NULL AUTO_INCREMENT,
NBATeam varchar(255) NOT NULL,
Division varchar(255) NOT NULL,
WinPerc decimal(8,3) NOT NULL, GB  decimal(8,1) NOT NULL,
Conference varchar(255) NOT NULL,
PRIMARY KEY (TeamID)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

INSERT INTO NBArankings2013 (NBATeam, Division, WinPerc, GB, Conference)
VALUES (‘New York’, ‘Atlantic’, ‘0.659’, ’12’, ‘East’),
(‘Brooklyn’, ‘Atlantic’, ‘0.598’, ’17’, ‘East’),
(‘Boston’, ‘Atlantic’, ‘0.506’, ‘24.5’, ‘East’),
(‘Philadelphia’, ‘Atlantic’, ‘0.415’, ’32’, ‘East’),
(‘Toronto’, ‘Atlantic’, ‘0.415’, ’32’, ‘East’),
(‘Indiana’, ‘Central’, ‘0.605’, ‘16.5’, ‘East’),
(‘Chicago’, ‘Central’, ‘0.549’, ’21’, ‘East’),
(‘Milwaukee’, ‘Central’, ‘0.463’, ’28’, ‘East’),
(‘Detriot’, ‘Central’, ‘0.354’, ’37’, ‘East’),
(‘Cleveland’, ‘Central’, ‘0.293’, ’42’, ‘East’),
(‘Miami’, ‘Southeast’, ‘0.805’, ‘0’, ‘East’),
(‘Atlanta’, ‘Southeast’, ‘0.537’, ’22’, ‘East’),
(‘Washington’, ‘Southeast’, ‘0.354’, ’37’, ‘East’),
(‘Charlotte’, ‘Southeast’, ‘0.256’, ’45’, ‘East’),
(‘Orlando’, ‘Southeast’, ‘0.244’, ’46’, ‘East’),
(‘San Antonio’, ‘Southwest’, ‘0.707’, ‘2’, ‘West’),
(‘Memphis’, ‘Southwest’, ‘0.683’, ‘4’, ‘West’),
(‘Houston’, ‘Southwest’, ‘0.549’, ’15’, ‘West’),
(‘Dallas’, ‘Southwest’, ‘0.5’, ’19’, ‘West’),
(‘New Orleans’, ‘Southwest’, ‘0.329’, ’33’, ‘West’),
(‘Oklahoma City’, ‘Northwest’, ‘0.732’, ‘0’, ‘West’),
(‘Denver’, ‘Northwest’, ‘0.695’, ‘3’, ‘West’),
(‘Utah’, ‘Northwest’, ‘0.524’, ’17’, ‘West’),
(‘Portland’, ‘Northwest’, ‘0.402’, ’27’, ‘West’),
(‘Minnesota’, ‘Northwest’, ‘0.376’, ’29’, ‘West’),
(‘LA Clippers’, ‘Pacific’, ‘0.683’, ‘4’, ‘West’),
(‘Golden State’, ‘Pacific’, ‘0.573’, ’13’, ‘West’),
(‘LA Lakers’, ‘Pacific’, ‘0.549’, ’15’, ‘West’),
(‘Sacramento’, ‘Pacific’, ‘0.341’, ’32’, ‘West’),
(‘Phoenix’, ‘Pacific’, ‘0.305’, ’35’, ‘West’);

SELECT *
FROM nbarankings2013
ORDER BY CONFERENCE ASC, GB ASC;

Using MySQL is pretty easy, I am sure you can configure things by yourself. Create another database which will be your target database. Create an empty table with the same name as the table I used above. In this case, I used a target database named ‘projectext’ containing a table ‘nbarankings2013’.

Open up the MySQL command line and confirm that the table from projectext, the target database, is empty:

MySQL> SHOW DATABASES;
MySQL> USE PROJECTEXT;
MySQL> SELECT * FROM nbarankings2013;

CONNECTING THE DATABASE WITH THE OPERATING SYSTEM

1. Open Data Source (ODBC) panel by typing Data Source in the search bar of Windows
2. Create a new connection to the source database by pressing Add and choosing the MySQL driver you’ve installed. Fill out the details, make sure you are using the correct TCP/IP settings. In my case, I used 127.0.0.1 and 3306 as server and port address, respectively. Use the username and password you use to connect to the database in MySQL (I’m pretty sure you’ve configured this when you created the database). Lastly, choose the source database.
3. Click the Test button to check connectivity.
4. Repeat the same setup using the target database this time.

odbc

CONFIGURING GOLDENGATE PROCESSES

1. Start ggsci.exe
2. In the command line, type EDIT PARAMS MGR. This should prompt you to create a new .rpm file which will hold the configuration file of the MGR process. For now, you only need one parameter written at the text file:
PORT 7089

3. type START MGR in the command line to start the manager process. Confirm this with INFO MGR.
4. Next create an Extract process named INITLOD1. Execute the following commands:
ggsci> ADD EXTRACT INITLOD1, SOURCEISTABLE
ggsci> EDIT PARAMS INITLOD1
A new file with pop up and you need to fill out the configuration setup. I used the following:

EXTRACT INITLOD1

DBOPTIONS HOST 127.0.0.1, CONNECTIONPORT 3306
SOURCEDB project, USERID root@project, PASSWORD ******
RMTHOST 127.0.0.1, MGRPORT 7809
RMTFILE ./dirdat/el1.dat, PURGE
TABLE project.*;

5. Create the REPLICAT process. My configuration is as follows:

ggsci> ADD REPLICAT INITLOD2, SPECIALRUN
ggsci> EDIT PARAMS INITLOD2

REPLICAT INITLOD2
SPECIALRUN
HANDLECOLLISIONS

DBOPTIONS HOST 127.0.0.1, CONNECTIONPORT 3306
TARGETDB projectext, USERID root@projectext, PASSWORD Goldengate112! ASSUMETARGETDEFS
EXTFILE ./dirdat/el1.dat
DISCARDFILE ./dirrpt/el1.dsc, PURGE
MAP project.*, TARGET projectext.*;

5. To start the migration process or copying the table from project to the table in projectext, execute the following commands:

ggsci> START EXTRACT INITLOD1 (Several windows should pop up, wait for it to stop)
ggsci> VIEW REPORT INITLOD1 (Look at the bottom of the page if there is no error, if the process was successful, you should be able to see the Run Time Statistics. This tells us how many rows were copied)

Image

Next run the replicat process:

ggsci> START REPLICAT INITLOD2
ggsci> VIEW REPORT INITLOD2

Notes: You can also check with INFO ALL command to see if any of the processes has abended.

CHECKING RESULTS

If all the processes executed with no problem, you should be able to see contents in the table from the target database (in my case, projectext should have received the data).

Execute the MySQL command line client:

MySQL> SHOW DATABASES;
MySQL> USE PROJECTEXT;
MySQL> SELECT * FROM nbarankings2013;

The data should show up.

Advertisements