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 EMPLOY EE 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