Introduction:
Nowadays it's a typical scenario to have several RBDMS systems from different vendors such as Oracle, IBM DB2, MS SQL, Postgres, MySQL or others in the same organization. In some cases, a decision to unify everything under one system or couple the systems may be recommended. In that case it will require that data be migrated or replicated between systems-- which in most of the cases could be a daunting task without having the proper tool and proper plan for the job. This article presents an approach to this scenario: executing an initial data load followed by subsequent replication from IBM DB2 LUW to Oracle Database 12c using Oracle GoldenGate.
Environment Setup
Source Database:
Operating System: Oracle Virtual Box with SUSE Enterprise Linux 12.3
Database: IBM DB2 is 10.5.7.1
Oracle GoldenGate: Oracle GoldenGate v12.2.0.1.1
Hostname: db2linux
IP address: 192.168.56.111
Target Database:
Operating System: Oracle Virtual Box with Oracle Enterprise Linux 6.5
Oracle Database: Oracle Database 12c v12.1.0.2.0
Oracle GoldenGate: Oracle GoldenGate v12.2.0.1.11.2.0.0
Hostname: orcl
IP address: 192.168.56.101
In these series, we will cover the initial load. In the second part, unidirectional and bidirectional replication will be covered.
Setting up for initial object migration
To perform the initial migration of objects I will use the migration capability provided by SQL Developer. The first step is to establish a connection to the IBM DB2 Sample database. You can use SQL Developer to connect and navigate to non-Oracle databases by installing the afferent JDBC drivers.
Tools menu->Preferences->Database->Third party JDBC Drivers
To obtain the IBM DB2 JDBC driver files, download them from IBM DB2 support page or copy them from your DB2 installation directory which in my case is located in /opt/ibm/db2/V10.5/java. Deploy db2jcc4.jar and db2jcc_license_cu.jar in your local directory and add them to SQL Developer using the Add Entry… button.
Next, we should create a connection to IBM DB2 SAMPLE database. The SAMPLE database is used mostly in DB2 tutorials and can be created with db2sampl command. Create a connection to DB2 database and name it SAMPLE using hostname db2linux, user db2inst1, port 50001, and password in our case, test1234.
Test the connection to SAMPLE database and list the tables. All these tables will be migrated as we will demonstrate in the rest of the article.
SQL Developer is using a repository for performing migrations. Next, we have created a user that will be the repository owner, called SQLMIGR as follows:
SQL> create user sqlmigr identified by test1234;
User created.
SQL> grant connect,resource,create view to sqlmigr;
Grant succeeded.
SQL> alter user sqlmirg quota unlimited on users ;
User altered.
As root user create a directory for migration files and make oracle owner as follows:
[root@orcl sqldeveloper]# mkdir /migrfiles
[root@orcl sqldeveloper]# chown -R oracle:oinstall /migrfiles/
Proceed to install the repository. Tools menu->Migration…->Migrate. The migration wizard will be launched, create a connection named SQLMIGR using SQLMIGR user and test1234 password, next click on the Next button and the migration repository will be created.
Once the repository is created the wizard will go to the next step to define a migration project. Name it db2tooracleproj and choose the location for the migration scripts as follows:
Next step is to choose the source database, in our case will be SAMPLE. Let the mode as it is by default (Online).
Push next, in the selected databases list will have SAMPLE. This is what we want so push next.
Next, we will reach the wizard’s convert step. This grid contains the standard type conversion list from DB2 to Oracle. At this step, customizations can be made if it is desired. Push next.
The next page is related to source translations from SQL PL to PL/SQL (procedures, functions, and triggers) and constraints, views translation.
Next chose the target DB. Here we will choose the offline mode, all the migration code will be written to files. We have no interest in data migration so we will check also Proceed to Summary Page.
Press Finish. A pop-up migration window will launch.
Once the migration scripts are created, navigate to the destination directory (/migrfiles/db2tooracleproj/generated/2016-05-15_11-58-06) and execute the master.sql script connected as a dba user. By default the tables will be placed under the user used for connecting to DB2, namely DB2INST1, users and table names can be customized inside the script if it is desired.
We preferred to execute the script without any modification.
Ignore any errors related to views, stored procedures, functions, the point is to have the tables, indexes, and constraints converted correctly.
Being an initial load, disable all the foreign constraints otherwise the replication will fail. We will enable them after the initial load.
As db2inst1 user execute:
SQL>
declare
**v\_str varchar2(300);**
**cursor t\_cur is select table\_name,constraint\_name from user\_constraints where**
constraint_type='R';
**begin**
**for r in t\_cur**
**loop**
**execute immediate**
**'ALTER TABLE ' || r.table\_name ||' DISABLE CONSTRAINT ' || r.constraint\_name ;**
end loop;
end;
SQL> PL/SQL procedure successfully completed.
Initial load from DB2 to Oracle
Configure Oracle GoldenGate for IBM DB2 LUW
For the rest of the article series, we will use ggs_admin user for Goldengate operations.
IBM DB2 is using external user management, there is nothing like exclusive database users, this is a little bit different than what we have in Oracle by default. In Oracle, this type of authentication is called external authentication. Therefore, the user must be created first as operating system user then the privileges must be granted from DB2 directly.
As root user we will create ggs_admin user, then from DB2, grant database administrator authority (DBADM) as follows:
db2inst1@db2linux:~> db2 'connect to sample'
Database Connection Information
Database server = DB2/LINUXX8664 10.5.7
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
db2inst1@db2linux:~> db2 'grant dbadm on database to user ggs_admin'
DB20000I The SQL command completed successfully.
db2inst1@db2linux:~>
We extracted the Oracle Goldengate for DB2 LUW archive under /u01/goldengate12c with db2inst1 user as owner and created the subdirs as follows:
GGSCI (db2linux) 4> create subdirs
Creating subdirectories under current directory /u01/goldengate12c
Parameter files /u01/goldengate12c/dirprm: created
Report files /u01/goldengate12c/dirrpt: created
Checkpoint files /u01/goldengate12c/dirchk: created
Process status files /u01/goldengate12c/dirpcs: created
SQL script files /u01/goldengate12c/dirsql: created
Database definitions files /u01/goldengate12c/dirdef: created
Extract data files /u01/goldengate12c/dirdat: created
Temporary files /u01/goldengate12c/dirtmp: created
Credential store files /u01/goldengate12c/dircrd: created
Masterkey wallet files /u01/goldengate12c/dirwlt: created
Dump files /u01/goldengate12c/dirdmp: created
Configure and start the manager. Wwe will use port 7890 on IBM DB2 side.
db2inst1@db2linux:/u01/goldengate12c> ./ggsci
Oracle GoldenGate Command Interpreter for DB2
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
Linux, x64, 64bit (optimized), DB2 10.5 on Dec 11 2015 22:49:17
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (db2linux) 1> edit params mgr
PORT 7890
GGSCI (db2linux) 1> start mgr
Next create an extract parameter file called DB2ORALD and add the parameters as follows:
GGSCI (db2linux) 1> edit params DB2ORALD
EXTRACT DB2ORALD
SOURCEDB SAMPLE
USERIDALIAS ggs_admin
rmthost orcl, mgrport 7809
rmttask replicat, group lddb2
table DB2INST1.PROJECT;
table DB2INST1.PROJACT ;
table DB2INST1.ACT ;
table DB2INST1.IN_TRAY;
table DB2INST1.ORG;
table DB2INST1.STAFF;
table DB2INST1.SALES;
table DB2INST1.STAFFG;
table DB2INST1.EMPMDC;
table DB2INST1.PRODUCT;
table DB2INST1.INVENTORY;
table DB2INST1.CUSTOMER;
table DB2INST1.PURCHASEORDER;
table DB2INST1.CATALOG;
table DB2INST1.CL_SCHED;
table DB2INST1.DEPARTMENT;
table DB2INST1.EMPPROJACT;
table DB2INST1.EMPLOYEE;
table DB2INST1.EMP_PHOTO;
table DB2INST1.EMP_RESUME;
table DB2INST1.SUPPLIERS;
table DB2INST1.PRODUCTSUPPLIER;
Define the extract for initial load as follows:
GGSCI (db2linux) 8> add extract db2orald, sourceistable
EXTRACT added.
GoldentGate configuration on Oracle Database side
Configure the manager, assign port 7809 and allow access from db2linux as follows:
GGSCI (orcl) 1> edit params mgr
PORT 7809
ACCESSRULE, PROG *, IPADDR 192.168.56.111, ALLOW
GGSCI (orcl) 1> start mgr
Configure the initial load replicat, name it LDDB2 and define the parameters as follows:
GGSCI (orcl) 1> edit params LDDB2
replicat LDDB2
SETENV (ORACLE_SID=ORCL)
userid ggs_admin, password ggs_admin
Handlecollisions
Assumetargetdefs
MAP DB2INST1.*, TARGET DB2INST1.*
Define the replicat as specialrun as follows:
GGSCI (orcl) 71> add replicat LDDB2, specialrun
REPLICAT added.
The initial load
From the db2 side start the extract as follows:
GGSCI (db2linux) 13> start db2orald
Sending START request to MANAGER ...
EXTRACT DB2ORALD starting
If you execute a tail command against ggserr.log you will see that the processing is initiated :
2016-05-15 11:25:05 INFO OGG-00993 Oracle GoldenGate Capture for DB2, db2orald.prm:
EXTRACT DB2ORALD started.
2016-05-15 11:25:11 INFO OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm:
Processing table DB2INST1.PROJECT.
2016-05-15 11:25:16 INFO OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm:
Processing table DB2INST1.PROJACT.
2016-05-15 11:25:18 INFO OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm:
Processing table DB2INST1.ACT.
2016-05-15 11:25:20 INFO OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm:
Processing table DB2INST1.IN_TRAY.
2016-05-15 11:25:20 INFO OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm:
Processing table DB2INST1.ORG.
2016-05-15 11:25:20 INFO OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm:
Processing table DB2INST1.STAFF.
2016-05-15 11:25:21 INFO OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm:
Processing table DB2INST1.SALES.
2016-05-15 11:25:24 INFO OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm:
Processing table DB2INST1.STAFFG.
2016-05-15 11:25:25 INFO OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm:
Processing table DB2INST1.EMPMDC.
2016-05-15 11:25:28 INFO OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm:
Processing table DB2INST1.PRODUCT.
2016-05-15 11:25:32 INFO OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm:
Processing table DB2INST1.INVENTORY.
2016-05-15 11:25:38 INFO OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm:
Processing table DB2INST1.CUSTOMER.
2016-05-15 11:25:44 INFO OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm:
Processing table DB2INST1.PURCHASEORDER.
2016-05-15 11:25:50 INFO OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm:
Processing table DB2INST1.CATALOG.
2016-05-15 11:25:51 INFO OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm:
Processing table DB2INST1.CL_SCHED.
2016-05-15 11:25:59 INFO OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm:
Processing table DB2INST1.DEPARTMENT.
2016-05-15 11:26:08 INFO OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm:
Processing table DB2INST1.EMPPROJACT.
2016-05-15 11:26:13 INFO OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm:
Processing table DB2INST1.EMPLOYEE.
2016-05-15 11:26:19 INFO OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm:
Processing table DB2INST1.EMP_PHOTO.
2016-05-15 11:26:27 INFO OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm:
Processing table DB2INST1.EMP_RESUME.
2016-05-15 11:26:39 INFO OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm:
Processing table DB2INST1.SUPPLIERS.
2016-05-15 11:26:44 INFO OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm:
Processing table DB2INST1.PRODUCTSUPPLIER.
2016-05-15 11:26:50 INFO OGG-00991 Oracle GoldenGate Capture for DB2, db2orald.prm:
EXTRACT DB2ORALD stopped normally.
Pay attention to field types such as XMLType and DECFLOAT, these are not supported by Goldengate. Therefore, a different method must be implemented to migrate these fields to Oracle, convert XMLType fields into BLOB or CLOB types, DECFLOAT can be converted to DECIMAL or bring them after migration programmatically. Here is a warning message from ggserr.log from the DB2 side involving XMLType:
2016-05-15 11:25:05 WARNING OGG-05506 Oracle GoldenGate Capture for DB2, db2orald.prm:
Table DB2INST1.SUPPLIERS, column ADDR, contains an unsupported data type: XML.The column
will be excluded.
Finally, enable back the foreign constraints as follows:
As db2inst user execute:
declare
v_str varchar2(300);
cursor t_cur is select table_name,constraint_name from user_constraints where constraint_type='R';
begin
for r in t_cur
loop
execute immediate 'ALTER TABLE ' || r.table_name ||' ENABLE CONSTRAINT ' || r.constraint_name ;
end loop;
end;
PL/SQL procedure successfully completed.
Summary
In this article, we demonstrated an initial load from IBM DB2 LUW to Oracle 12c using Oracle GoldenGate 12c. We used the DB2 SAMPLE database, converted and created the tables to Oracle syntax and executed them against and ORACLE database named ORCL, followed by the initial load. The scenario can be used for initial data migration or to prepare an environment for further replications which will be demonstrated in part II of the series. Briefly, the steps are the following :
- - Create a connection to DB2 database
- - Create a migration repository or use an existent one.
- - Create a migration project and generate the DDL scripts.
- - Execute the DDL scripts on Oracle database.
- - Install Oracle GoldenGate at source and destination.
- - Create additional database users for maintaining Golden Gate.
- - Configure managers, extract and replicat on source and destination.
- - Initiate the initial load
- - Troubleshoot the problems