Using GoldenGate Veridata

 

 

DB2 to Oracle

Hands-on Migration Workshop

 

 

Using GoldenGate Veridata

Lab Workbook

 

 

Oracle Platform Technology Solutions

 

 

Maqsood Alam, Sr. Platform Products Manager

 

DB2 to Oracle Hands-on Migration Workshop – Using GoldenGate Veridata

 

 

1.  Overview

Oracle GoldenGate Veridata compares one set of data to another and identifies the data that is out of

synchronization. Veridata supports high-volume, 24x7 replication environments where downtime to

compare data sets is not an option. By accounting for data that is being replicated while a comparison

takes place, Veridata can run concurrently with data transactions and replication, while still producing an

accurate comparison report.

GoldenGate Veridata will map column data types across different types of databases automatically, or

you can map columns manually in cases where the automatic mapping is not sufficient to accommodate

format differences, such as the case with heterogeneous data sources and targets.

The self-paced labs presented in this workshop provides the novice Veridata user with a basic

understanding of the functionality of the application.

Topics covered in this lab include:

·   Oracle GoldenGate Veridata Architecture

·   Starting the Veridata Environment

·   Configuring the Veridata Source and Target Connections

·   Compare pair generation

·   In-sync comparison example

·   Out-of-sync comparison example

2.  Oracle GoldenGate Veridata Architecture

 

 

 

·

GoldenGate Veridata Server

 

 

The GoldenGate Veridata Server performs the following functions:

o   Coordinate the execution of GoldenGate Veridata tasks

 

 

 

©20 10 Oracle Corporation. All Rights Reserved.

Page 2 o f 24

 

DB2 to Oracle Hands-on Migration Workshop – Using GoldenGate Veridata

 

 

 

o

o

o

o

Sort rows (optional)

Compare data

Confirm out-of-sync data

Produce a report for review

 

 

 

·

GoldenGate Veridata Web

 

 

GoldenGate Veridata Web is a client to GoldenGate Veridata Server. It is a browser-based graphical

user interface for these activities:

o   Configure comparison objects and rules

o   Initiate comparisons

o   Review the status and output of comparisons

o   Review out-of-sync data

·   GoldenGate Veridata Repository

The GoldenGate Veridata repository is a collection of database objects that persists configuration

information to disk, saving it permanently as a user environment.

·   GoldenGate Veridata Agent

The GoldenGate Veridata Agent executes the following database-related requests on behalf of the

GoldenGate Veridata Server:

o   Fetch and return blocks of rows to compare

o   Return column-level detail for out-of-sync rows

·   GoldenGate Veridata CLI (Command Line Interface)

GoldenGate Veridata includes a command line client to GoldenGate Veridata Server. The program

name is vericom. This interface enables you to run comparisons, specify overrides, and stop

GoldenGate Veridata Server by using automated programs. Configuration and monitoring functions

must be performed through GoldenGate Veridata Web.

3.  Startup the Veridata Environment

Veridata Software components are pre-installed and configured in the supplied Virtual Machine p_w_picpath.

The Veridata Server and the Web are installed in the /u01/app/veridata_server directory. The

Server Repository is created in the ORCL Oracle instance, using the schema user VERIDATA.

There are two agents that are installed in the VM for the corresponding data sources and destinations. The

Java Agent that talks to the Oracle database which is our destination database is installed and configured

in the /u01/app/veridata_agent_oracle directory. The Java Agent for the DB2 data source is

installed and configured in the /u01/app/veridata_agent_oracle directory.

Veridata runs as a web service which may be accessed from any computer on your corporate network.

Before the service can be accessed, Veridata Server, Veridata Web, and Veridata Agents must be started.

 

 

 

©20 10 Oracle Corporation. All Rights Reserved.

Page 3 o f 24

 

DB2 to Oracle Hands-on Migration Workshop – Using GoldenGate Veridata

 

 

Note: Before you start the Veridata components, you need to start the Oracle and the DB2 databases if

you have not yet done so. Follow the instructions from the earlier labs for starting these components.

3.1. Login as the oracle user and execute the following commands to start the Veridata Server:

 

 

 

[oracle@ogg11g ogg_db2]$ cd /u01/app/veridata_server/server/bin [oracle@ogg11g bin]$ ./veridata_server.sh start [oracle@ogg11g bin]$ ps | grep veridata 4151 pts/3     00:00:19 veridata

 

 

 

3.2. Run the commands specified below to start the Oracle Veridata Web Server:

 

 

 

[oracle@ogg11g bin]$ cd /u01/app/veridata_server/web/bin [oracle@ogg11g bin]$ ./veridata_web.sh start CATALINA_HOME: /u01/app/veridata_server/web JRE_HOME: /u01/app/veridata_server/jre JAVA_OPTS: -Xmx512m -Djava.awt.headless=true - Dveridata.log.dir=/u01/app/veridata_server/shared/logs Using CATALINA_BASE:    /u01/app/veridata_server/web Using CATALINA_HOME:    /u01/app/veridata_server/web Using CATALINA_TMPDIR: /u01/app/veridata_server/web/temp Using JRE_HOME:        /u01/app/veridata_server/jre

 

 

 

3.3. Run the commands specified below to start the Oracle Veridata Agent for Oracle. The Agent is

the Java based agent and hence will show up as a Java process to the OS.

 

 

 

[oracle@ogg11g agent]$ cd /u01/app/veridata_agent_oracle/agent [oracle@ogg11g agent]$ ./agent.sh start [oracle@ogg11g agent]$ ps -f | grep Agent oracle     9237      1  0 22:06 pts/3     00:00:00 /usr/java/jdk1.6.0_15/bin/java -Xmx256M -Djava.awt.headless=true - Djava.library.path=/u01/app/oracle/product/11.2.0/dbhome_1/lib -jar /u01/app/veridata_agent_oracle/agent/JavaAgent.jar

 

 

 

3.4. Run the commands specified below to start the Oracle Veridata Agent for DB2.

 

 

 

[oracle@ogg11g agent]$ cd /u01/app/veridata_agent_db2/agent [oracle@ogg11g agent]$ ./agent.sh start [oracle@ogg11g agent]$ ps -f | grep Agent oracle     9237      1  0 22:06 pts/3     00:00:00 /usr/java/jdk1.6.0_15/bin/java -Xmx256M -Djava.awt.headless=true - Djava.library.path=/u01/app/oracle/product/11.2.0/dbhome_1/lib -jar /u01/app/veridata_agent_oracle/agent/JavaAgent.jar oracle     4221      1  0 14:28 pts/3     00:00:40 /usr/java/jdk1.6.0_15/bin/java -Xmx256M -Djava.awt.headless=true - Djava.library.path=/u01/app/oracle/product/11.2.0/dbhome_1/lib -jar /u01/app/veridata_agent_db2/agent/JavaAgent.jar

 

 

 

 

©20 10 Oracle Corporation. All Rights Reserved.

Page 4 o f 24

 

DB2 to Oracle Hands-on Migration Workshop – Using GoldenGate Veridata

 

 

3.5. Start a web browser and go to “http://localhost:8830/veridata” to access the Veridata

logon page. Enter the credentials specified below to login.

·   Username: oracle

·   Password: oracle

 

 

3.6. If the login is successful, the Veridata main screen will be displayed.

 

 

4.  Configure the Veridata Source and Target Connections

 

 

 

©20 10 Oracle Corporation. All Rights Reserved.

Page 5 o f 24

 

DB2 to Oracle Hands-on Migration Workshop – Using GoldenGate Veridata

 

 

4.1. To begin the Veridata compare configuration, click “Connection Configuration” from the menu

on the left side of the home page.

 

 

4.2. On the Connection Configuration page, click “New”.

 

 

4.3. On the New Connection Assistant pop-up, enter the following:

·   Name: DB2_Source

·   Description: Connection to the DB2 Source tables.

Click the “Next” button.

 

 

4.4. Enter the following on the GoldenGate Agent Connection pop-up:

 

 

 

©20 10 Oracle Corporation. All Rights Reserved.

Page 6 o f 24

 

DB2 to Oracle Hands-on Migration Workshop – Using GoldenGate Veridata

 

 

 

·

·

·

Host Name: localhost

Port: 7901

Datasource Type: DB2

 

 

Click the “Verify” button.

 

 

Once you see the “Datasource type verified” message, click the “Next” button.

4.5. For the data source connection information for DB2, enter the following:

·   User: db2

·   Password: oracle

Click the “Test Connection” button.

 

 

When you see the “Datasource connection was successful” message, click the “Finish” button.

4.6. Select the “Create another Connection” box. This time you will configure the Oracle Database

connection for the destination schema. Click “OK”.

 

 

 

©20 10 Oracle Corporation. All Rights Reserved.

Page 7 o f 24

 

DB2 to Oracle Hands-on Migration Workshop – Using GoldenGate Veridata

 

 

4.7. The New Connection Assistant pop-up will display again. Enter the following information:

·   Name: Oracle_Destination

·   Description: Connection to the Target Oracle tables

Click the “Next” button.

 

 

4.8. Enter the following on the New Connection Assistant pop-up:

·   Host Name: localhost

·   Port: 7900

·   Datasource Type: Oracle

Click the “Verify” button. You should see a “Datasource type verified” message displayed.

 

 

 

©20 10 Oracle Corporation. All Rights Reserved.

Page 8 o f 24

 

DB2 to Oracle Hands-on Migration Workshop – Using GoldenGate Veridata

 

 

Click the “Next” button to continue.

4.9. For the data source connection information, enter the following:

o   User: DB2

o   Password: oracle

·   Click the “Test Connection” button. You should see a “Datasource connection was successful”

message displayed as shown below.

 

 

 

·

·

Click the “Finish” button.

Click “OK” to exit out of the New Connection Assistant. You have now completed configuring

the Source and Destination connections, and these connections should appear in the Existing

Connections section as shown below.

 

 

 

©20 10 Oracle Corporation. All Rights Reserved.

Page 9 o f 24

 

DB2 to Oracle Hands-on Migration Workshop – Using GoldenGate Veridata

 

 

5.  Setup Veridata Groups, Compare Pairs, Profiles and Jobs

When you are comparing schemas, you first need to define a group that comprises a source and a

destination schema. Then you will define a compare pair using the schemas from the group.

5.1. Create a New Group. Select “Group Configuration” from the left pane.

 

 

 

·

Select “New” from the Group Configuration menu.

 

 

 

·

On the New Group Assistant pop-up, enter the following:

o   Name: DB2_Oracle_Group_Compare

o   Description: Compare the DB2 and Oracle Schemas

 

 

 

©2010 Oracle Corporation. All Rights Reserved.

Page 10 of 24

 

DB2 to Oracle Hands-on Migration Workshop – Using GoldenGate Veridata

 

 

 

·

Click “Next”.

For the Source and Target connections, from the drop down menu choose DB2_Source as the

source and Oracle_Destination as the target. Then select the “Finish” button.

 

 

 

·

From the New Group Assistant pop-up, select the “Go to Compare Pair Configuration” check

box, and then select the “Ok” button.

 

 

5.2. On the Compare Pair Configuration screen select the tab labeled “Pattern Mapping”.

 

 

 

·

·

·

Using the drop down menus, select DB2 as the Source Schema (on the DB2 Database) and DB2

as the Target Schema (on the Oracle Database).

In the Table Mapping Method section, take the default (Map Source and Target tables using

exact names).

Leave all other selections as the default. Click the “Generate Mappings” button.

 

 

 

©2010 Oracle Corporation. All Rights Reserved.

Page 11 of 24

 

DB2 to Oracle Hands-on Migration Workshop – Using GoldenGate Veridata

 

 

 

·

A total of 25 compare pairs should be generated.

 

 

 

·

Select the “Preview” tab.

 

 

 

·

To display all the 25 compare pairs on one page, select 50 (from the default 10) in the “Show

Items per page” drop-down.

 

 

 

·

From the 25 Compare Pairs displayed, uncheck the VACT, V PROJACT and V STAFAC1 pairs.

These are the pairs related to Views (not Table) and we will not be comparing them. Your

selection should look like the picture below.

 

 

 

©2010 Oracle Corporation. All Rights Reserved.

Page 12 of 24

 

DB2 to Oracle Hands-on Migration Workshop – Using GoldenGate Veridata

 

 

 

·

Click the “Save” button. When the “Existing Compare Pair” tab displays, select “50” again in

the “Show Items per page” drop-down to show all the 22 (25 take away 3) compare pairs on one

page.

 

 

 

·

Click the “Select Page” check box to select all the 22 compare pairs.

 

 

 

©2010 Oracle Corporation. All Rights Reserved.

Page 13 of 24

 

DB2 to Oracle Hands-on Migration Workshop – Using GoldenGate Veridata

 

 

 

·

·

To automatically setup column mapping between the source and target tables, select the

“Validate Column Mapping” button.

When the validation completes, the column “Validation Status” will change from a grey

question mark to a green check.

 

 

 

©2010 Oracle Corporation. All Rights Reserved.

Page 14 of 24

 

DB2 to Oracle Hands-on Migration Workshop – Using GoldenGate Veridata

 

 

 

·

Compare pair generation is now complete.

 

 

5.3. Create a New Profile. From the Configuration Menu on the left side of the screen, select

“Profile Configuration”.

 

 

 

·

In the Profile Configuration section, click “New” to create a new Profile.

 

 

 

©2010 Oracle Corporation. All Rights Reserved.

Page 15 of 24

 

DB2 to Oracle Hands-on Migration Workshop – Using GoldenGate Veridata

 

 

 

·

In the “New Profile Assistant” pop up, enter the following:

o   Name: DB2_to_Oracle_Profile

o   Description: Profile for DB2 to Oracle Demo

o   Leave the other “Create new profile using copy of existing profile” box unchecked

(default).

Click “Finish”.

 

 

 

·

You will get a “Your new profile was created successfully” message. Click “OK”.

 

 

 

·

You will be taken to a Profile Configuration screen which allows you to set Profile Settings.

Observe some of the default values by expanding the Category section. What do you think these

values mean?

 

 

 

©2010 Oracle Corporation. All Rights Reserved.

Page 16 of 24

 

DB2 to Oracle Hands-on Migration Workshop – Using GoldenGate Veridata

 

 

 

·

Do not make any changes to the Profile Settings. Click “Save”.

 

 

5.4. Create a New Job. From the Configuration Menu on the left side of the screen, select “Job

Configuration”.

 

 

 

·

In the Job Configuration section, click “New” to create a new Job.

 

 

 

·

In the “New Job Assistant” pop up, enter the following:

o   Name: DB2_to_Oracle_Job

o   Description: Veridata Job for DB2 to Oracle Demo

Click “Next”.

 

 

 

©2010 Oracle Corporation. All Rights Reserved.

Page 17 of 24

 

DB2 to Oracle Hands-on Migration Workshop – Using GoldenGate Veridata

 

 

 

·

You need to link the group created earlier (DB2_Oracle_Group_Compare) to this Job. Check the

DB2_Oracle_Group_Compare.

Click “Next”.

 

 

 

·

In the next window displayed, you can optionally override the datasource connections for the

groups selected. Do not make any changes on this window and accept the defaults.

Click “Next”.

 

 

 

©2010 Oracle Corporation. All Rights Reserved.

Page 18 of 24

 

DB2 to Oracle Hands-on Migration Workshop – Using GoldenGate Veridata

 

 

 

·

On this screen, you can provide the Profile information to the Job. Select

“DB2_to_Oracle_Profile” created earlier from the drop-down.

Click “Finish”.

 

 

 

·

You should see a Job successfully created message. Click “OK”.

 

 

 

·

The new Job “DB2_to_Oracle_Job” should be displayed in the Existing Jobs section of the Job

Configuration screen.

 

 

6.  Run Jobs and Perform Sanity Checks

In this step, you will run the Compare Job just created and perform an initial baseline compare. You will

then submit a few transactions on the DB2 system and verify that Veridata can display the gap. Lastly,

you will start GoldenGate Replication and verify the gaps get cleared.

6.1. Perform the initial compare. From the Job Configuration screen, ensure that the

DB2_to_Oracle_Job is selected. Click “Run”.

 

 

 

©2010 Oracle Corporation. All Rights Reserved.

Page 19 of 24

 

DB2 to Oracle Hands-on Migration Workshop – Using GoldenGate Veridata

 

 

 

·

·

You will be taken to the Run/Execute Job screen. This screen allows you to filter Compare Pairs

that are associated with this Job.

Leave all selections as default and scroll to the bottom of the screen. Click on “ Run Job”.

 

 

 

·

The Job will be started and you can monitor the Job progress on the screen.

 

 

 

·

Once the Job completes, click on View By Compare Pair link on the left side of the window in

the Finished Jobs section.

 

 

 

©2010 Oracle Corporation. All Rights Reserved.

Page 20 of 24

 

DB2 to Oracle Hands-on Migration Workshop – Using GoldenGate Veridata

 

 

 

·

The initial run indicates that the two databases are fully in-sync.

 

 

6.2. Using SQL Developer, perform an update on the DB2 source database.

·   Open SQL Developer and connect to the DB2 SOURCE Database connection named

SAMPLE@DB2. This connection should have been created in the steps outlined in the SQL

Developer lab.

·   In the SQL Worksheet for SAMPLE@DB2, enter the below SQL and Run it by selecting the text

and Clicking    .

 

 

 

·

Update the EMPLOYEE row selected in the earlier step using the SQL below. Verify the row

has been updated. The DB2 database is configured for Auto Commit and you do not have to

perform a manual commit for this transaction.

 

 

 

©2010 Oracle Corporation. All Rights Reserved.

Page 21 of 24

 

DB2 to Oracle Hands-on Migration Workshop – Using GoldenGate Veridata

 

 

 

·

·

Run the Veridata Compare Job by clicking on Job Configuration - > Run -> Run Job. For

detail instructions refer to section 6.1.

Verify the Job results by clicking View by Compare Pair. Notice that now you have the

EMPLOYEE table which is reported as being out of sync.

 

 

 

©2010 Oracle Corporation. All Rights Reserved.

Page 22 of 24

 

DB2 to Oracle Hands-on Migration Workshop – Using GoldenGate Veridata

 

 

 

·

Click on the “View” link in the Rows Out of Sync column for the EMPLOYEE table.

 

 

 

·

Browse to the end of the Out-of-Sync report screen and to the Operation Summary section.

Verify that the row that is reported as being out-of-sync is indeed the one that was updated

earlier.

 

 

6.3. Start the GoldenGate Replicat processes to sync-up the source and destination systems.

·   Open a new Terminal session as the oracle user and execute the commands specified below to

start the Replicat process.

 

 

 

[oracle@localhost ~]$ cd /u01/app/ogg_oracle [oracle@localhost ogg_oracle]$ ./ggsci GGSCI> START REPLICAT RORADP Sending START request to MANAGER ... REPLICAT RORADP starting GGSCI> INFO REPLICAT RORADP REPLICAT    RORADP     Last Started 2010-09-30 23:45    Status RUNNING Checkpoint Lag        02:20:07 (updated 00:00:06 ago) Log Read Checkpoint   File /u01/app/ogg_oracle/dirdat/dp000000 2010-09-30 21:24:56.000941   RBA 965

 

 

 

 

©2010 Oracle Corporation. All Rights Reserved.

Page 23 of 24

 

DB2 to Oracle Hands-on Migration Workshop – Using GoldenGate Veridata

 

 

 

·

·

Run the Veridata Compare Job by clicking on Job Configuration - > Run -> Run Job. For

detail instructions refer to section 6.1.

Verify the Job results by clicking View by Compare Pair. You should not see any out-of-sync

objects in the Compare Pair status chart.

 

 

 

·

This completes the GoldenGate Veridata Lab.

 

 

 

©2010 Oracle Corporation. All Rights Reserved.

 

Page 24 of 24