Oracle GoldenGate: Microsoft SQL Server to Oracle Migration (Part 3)

Introduction

In Part 1 of this article, we performed a zero-downtime migration of a live Microsoft SQL Server Database to an Oracle Pluggable Database using the Microsoft SQL Server Import and Export Wizard and Oracle GoldenGate. In Part 2, we performed the same zero-downtime migration using Oracle SQL Developer and Oracle GoldenGate.

In this part, we again perform the same zero-downtime migration, this time using only Oracle GoldenGate to instantiate the Oracle tables and complete the synchronization between the SQL Server source and Oracle target databases. We are also going to use GoldenGate Macros to consolidate common parameter file settings, GGSCI Obey files for executing multiple commands simultaneously, and scripting for automation of the instantiation process.

Main Article

Oracle GoldenGate may be used to instantiate and synchronize dissimilar databases, as in this case when migrating a Microsoft SQL Server database to Oracle. However, Oracle GoldenGate does not provide a method for automatically creating the target Oracle tables; this must be done manually and it is up to the DBA to ensure data type conversions are performed correctly.

Create Target Tables

The first thing we need to do is define and create the target Oracle Database tables. Since SQL Server and Oracle have different data type definitions, we need to review the source (SQL Server) table metadata and perform a data type conversion. The following table details the data types used in our source tables and the corresponding Oracle data type.

SQL Server Data Type  Oracle Data Type
 BIGINT  NUMBER(20)
 SMALLINT  NUMBER(5)
 TINYINT  NUMBER(3)
 DATETIME  TIMESTAMP
 DECIMAL  NUMBER
 CHAR  CHAR
 VARCHAR  VARCHAR2

 

Below is a list of other data type conversions which you may find useful.

SQL Server  Oracle Comment
BINARY RAW
BIT NUMBER(3)
FLOAT FLOAT(53)
IMAGE LONG RAW
INT NUMBER(10)
MONEY NUMBER(19,4)
NCHAR NCHAR If size is 1000 or less, otherwise LONG
NTEXT LONG If Oracle DB Character Set = Unicode, otherwise not supported
NVARCHAR NVARCHAR
NVARCHAR (MAX) LONG
NUMERIC NUMBER
REAL FLOAT(24)
SMALLDATETIME DATE
SMALLMONEY NUMBER(10,4)
SYSNAME NVARCHAR
TEXT LONG
TIMESTAMP RAW
VARBINARY RAW
VARBINARY (MAX) LONG RAW
VARCHAR (MAX) LONG
XML XMLTYPE

 

Once the target Oracle tables are created, we can begin configuring Oracle GoldenGate to perform the database instantiation and real-time transactional data replication.

Before continuing, ensure the SQL Server database has been configured to allow replication by Oracle GoldenGate and Oracle GoldenGate has been installed on the SQL Server Database server as specified in the Installing and Configuring Oracle GoldenGate for Microsoft SQL Server reference guide, and the Oracle Database server as specified in the Installing and Configuring Oracle GoldenGate for Oracle reference guide.

Configure Source Oracle GoldenGate Instance

On the source (SQL Server) database server, we need to configure an Oracle GoldenGate Classic Extract and Extract Data Pump to capture and move real-time transactional data to the Oracle GoldenGate instance on the target (Oracle) database server. We also need to configure an Extract to perform direct reads for the SQL Server tables for the target instantiation; but first we need to decide what method to use, remote task or remote trail.

Remote Task vs Remote Trail

When using Oracle GoldenGate to perform target table instantiation, a crucial decision is which instantiation method to choose. Each has benefits and disadvantages.

Remote Task provides direct communication between the Direct Read Extract and the Replicat performing the data apply. This is best used for small tables, but larger tables may also be instantiated by splitting the load process across multiple initial load task groups. This instantiation method is faster that the remote trail option; however, there is no recoverability in the event of a target database issue. If the Replicat fails for any reason, the Direct Read Extract terminates. This requires a truncation of the target tables and a full instantiation of the affected tables. Classic Replicat is used for remote task loads; Coordinated Apply and Integrated Replicat is not supported for this instantiation method.

Remote Trail is a bit slower than the remote task option; however, Coordinated Apply and Integrated Replicat may be used to perform the instantiation, so the performance impact is minimal when using one of these apply options. When using the remote trail option, Oracle GoldenGate maintains a data apply checkpoint; which is a record of what data has been consumed from the Oracle GoldenGate Trail and committed to the target database. In the event of a Replicat failure, the instantiation is fully recoverable as a Replicat restart will pick up from the last checkpoint before the failure. Likewise, a full instantiation may be re-executed by repositioning the Replicat to the first record of the first trail; that is, as long as the Oracle GoldenGate Trails remain on disk.

For this migration, we will be using the Remote Trail option as it is “best practice”.

Configure Source Instantiation Groups

To determine the number of instantiation groups I’ll need, it would be good to know how much data is in my busier tables. Querying the database for row counts and average bytes per row is a good place to start. In SQL Server Management Studio, I ran the following queries:

mssoraogg1

mssoraogg2

As you may see, the tables in this database are fairly small in size, with the two large tables containing 506k and 369k rows. Those two tables, dbo.orders_products and dbo.orders_total, I’ll split across three dedicated Direct Read Extracts. The remaining busy tables will each be assigned a dedicated Direct Read Extract and all remaining tables in the database will be grouped into one Direct Read Extract.

When working with an Oracle GoldenGate configuration where there are multiple parameter files that share common settings, I prefer to use GoldenGate Macros to minimize typing and typos during parameter file creation. My parameter files for this migration contain the same settings for (1) SQL Server database login credentials, (2) target host and GoldenGate Manager port information, (3) specifics for creating and writing the files to retain the data read from the tables, and (4) specifics on what tables to split across multiple Direct Read Extracts. My GoldenGate Macro file looks like this (I wrote an earlier blog about GoldenGate Macros you can review if you want to know more):

MACRO #dbconnect
BEGIN
  sourcedb mss08ljp, useridalias gg_db_alias
END;

MACRO #orahost
BEGIN
  rmthost ******.us.oracle.com, mgrport 7898, compress
END;

MACRO #orafile
PARAMS (#fname)
BEGIN 
  rmtfile ./dirdat/iload/#fname, megabytes 1000, maxfiles 999, purge
END;

MACRO #tsplit
PARAMS (#tname, #numof, #last)
BEGIN
  table #tname, filter (@RANGE (#numof,#last));
END;

Given this, my Direct Read Extract configuration will be this:

extract eilda
nolist
include ./dirprm/macrolib.mac
list
#dbconnect()
#orahost()
#orafile(ia)
table dbo.categories;
table dbo.categories_description;
table dbo.customers_lookup;
table dbo.next_cust;
table dbo.next_order;
table dbo.ogg_migrate;
table dbo.products;
table dbo.products_description;
table dbo.products_to_categories;

extract eildb
nolist
include ./dirprm/macrolib.mac
list
#dbconnect()
#orahost()
#orafile(ib)
table dbo.customers;

extract eildc
nolist
include ./dirprm/macrolib.mac
list
#dbconnect()
#orahost()
#orafile(ic)
table dbo.customers_info;

extract eildd
nolist
include ./dirprm/macrolib.mac
list
#dbconnect()
#orahost()
#orafile(id)
table dbo.orders;

extract eilde
nolist
include ./dirprm/macrolib.mac
list
#dbconnect()
#orahost()
#orafile(ie)
#tsplit(dbo.orders_products, 1, 3);

extract eildf
nolist
include ./dirprm/macrolib.mac
list
#dbconnect()
#orahost()
#orafile(if)
#tsplit(dbo.orders_products, 2, 3);

extract eildg
nolist
include ./dirprm/macrolib.mac
list
#dbconnect()
#orahost()
#orafile(ig)
#tsplit(dbo.orders_products, 3, 3);

extract eildh
nolist
include ./dirprm/macrolib.mac
list
#dbconnect()
#orahost()
#orafile(ih)
table dbo.orders_status_history;

extract eildi
nolist
include ./dirprm/macrolib.mac
list
#dbconnect()
#orahost()
#orafile(ii)
#tsplit(dbo.orders_total, 1, 3);

extract eildj
nolist
include ./dirprm/macrolib.mac
list
#dbconnect()
#orahost()
#orafile(ij)
#tsplit(dbo.orders_total, 2, 3);

extract eildk
nolist
include ./dirprm/macrolib.mac
list
#dbconnect()
#orahost()
#orafile(ik)
#tsplit(dbo.orders_total, 3, 3);

Configure Online Capture Groups

To complete the source side setup, create a Classic Extract to capture real-time transactional data from the SQL Server database logs and an Extract Data Pump to transmit this captured data over TPC/IP to the target Oracle GoldenGate Instance.

extract eljp
nolist
include ./dirprm/macrolib.mac
list
#dbconnect()
tranlogoptions managesecondarytruncationpoint
exttrail ./dirdat/ep
reportcount every 10 minutes, rate
table dbo.*;

extract pljp
nolist
include ./dirprm/macrolib.mac
list
#orahost()
rmttrail ./dirdat/rp
reportcount every 10 minutes, rate
table dbo.*;

Configure Target Oracle GoldenGate Instance

On the target (Oracle Database) side, we need to configure a Replicat that will apply the real-time change data capture operations received from the source side (SQL Server Database) Extract Data Pump. This Replicat can be any of the three apply methods supported for the Oracle Database: (1) Classic Replicat, (2) Coordinated Apply, or (3) Integrated Replicat. For Oracle 12c Databases, best practice dictates the use of Integrated Replicat as it provides the best overall performance of the three.

We also need to configure eleven Replicats to perform target table instantiation by applying the data received from the source side Direct Read Extracts. Because these Replicats will only be run once, I prefer to use Coordinated Apply as it provides better performance than Classic Replicat and has less database overhead than Integrated Replicat. I am also setting each Coordinated Apply to use multiple apply processes, or threads, so we can reduce the load time by parallelizing the workload.

Configure Target Instantiation Groups

As previously noted, we need to configure eleven Coordinated Apply groups to perform the target table instantiation. Because I am setting up multiple Replicats, with each containing similar parameter settings, I use GoldenGate Macros. My target side macro file looks like this:

MACRO #dbconnect
PARAMS (#cnum, #cint)
BEGIN
  useridalias oggalias
  reportcount every #cnum #cint, rate
END;

MACRO #mapthread
PARAMS (#sname, #tname, #threadno)
BEGIN
  map #sname, target #tname, thread (#threadno);
END;

MACRO #mapthreadrange
PARAMS (#sname, #tname, #lowid, #highid)
BEGIN
  map #sname, target #tname, threadrange (#lowid-#highid);
END;

My target instantiation Replicats configuration is:

replicat rilda
nolist
include ./dirprm/macrolib.mac
list
#dbconnect(60,seconds)
#mapthread(dbo.categories,ljptpc.categories,1)
#mapthread(dbo.categories_description,ljptpc.categories_description,2)
#mapthread(dbo.customers_lookup,ljptpc.customers_lookup,3)
#mapthread(dbo.next_cust,ljptpc.next_cust,3)
#mapthread(dbo.next_order,ljptpc.next_order,3)
#mapthread(dbo.ogg_migrate,ljptpc.ogg_migrate,3)
#mapthread(dbo.products,ljptpc.products,4)
#mapthread(dbo.products_description,ljptpc.products_description,5)
#mapthread(dbo.products_to_categories,ljptpc.products_to_categories,6)

replicat rildb
nolist
include ./dirprm/macrolib.mac
list
#dbconnect(60,seconds)
#mapthreadrange(dbo.customers,ljptpc.customers,1,8)

replicat rildc
nolist
include ./dirprm/macrolib.mac
list
#dbconnect(60,seconds)
#mapthread(dbo.customers_info,ljptpc.customers_info,1)

replicat rildd
nolist
include ./dirprm/macrolib.mac
list
#dbconnect(60,seconds)
#mapthreadrange(dbo.orders,ljptpc.orders,1,10)

replicat rilde
nolist
include ./dirprm/macrolib.mac
list
#dbconnect(60,seconds)
#mapthreadrange(dbo.orders_products,ljptpc.orders_products,1,2)

replicat rildf
nolist
include ./dirprm/macrolib.mac
list
#dbconnect(oggpdb)
#mapthreadrange(dbo.orders_products,ljptpc.orders_products,1,2)

replicat rildg
nolist
include ./dirprm/macrolib.mac
list
#dbconnect(60,seconds)
#mapthreadrange(dbo.orders_products,ljptpc.orders_products,1,2)

replicat rildh
nolist
include ./dirprm/macrolib.mac
list
#dbconnect(60,seconds)
#mapthread(dbo.orders_status_history,ljptpc.orders_status_history,1)

replicat rildi
nolist
include ./dirprm/macrolib.mac
list
#dbconnect(60,seconds)
#mapthreadrange(dbo.orders_total,ljptpc.orders_total,1,2)

replicat rildj
nolist
include ./dirprm/macrolib.mac
list
#dbconnect(60,seconds)
#mapthreadrange(dbo.orders_total,ljptpc.orders_total,1,2)

replicat rildk
nolist
include ./dirprm/macrolib.mac
list
#dbconnect(60,seconds)
#mapthreadrange(dbo.orders_total,ljptpc.orders_total,1,2)

Configure Oracle GoldenGate Manager

I am going to use GoldenGate functionality as an automation mechanism for switching from instantiation to real-time data apply mode in our Integrated Replicat. This automation will put the Integrated Replicat into the ABEND state, so we set the Oracle GoldenGate Manager to restart the group when the state changes. Modify the Manager parameter file to include the setting in bold:

port 7898
purgeoldextracts ./dirdat/*, usecheckpoints
ACCESSRULE, PROG *, IPADDR *, PRI 1, ALLOW
dynamicportlist 15000-15100
autorestart er *, retries 3, waitseconds 60, resetminutes 60

Create Instantiation Trail Location

In the Direct Read Extracts I set the location of the GoldenGate Extract File each creates as ./dirdat/iload on the target server. We need to create that location:

$ (******)[a12101s] /home/oracle/ogg_12.2_12c/dirdat\> mkdir iload
$ (******)[a12101s] /home/oracle/ogg_12.2_12c/dirdat\> ls
iload

Configure Integrated Replicat

To complete the target Oracle GoldenGate side setup, configure an Integrated Replicat to apply queued and real-time transactional data captured from SQL Server. Because we will be capturing real-time transactional data while we are running the Direct Read Extracts to instantiate the target tables, we know the GoldenGate Trail read by Integrated Extract will contain transactional data that was replicated during the instantiation process. We need to take this “overlap data” into account when first starting the Integrated Extract; and we do this via a special parameter HANDLECOLLISIONS.

The HANDLECOLLISONS parameter is designed to be used as part of the target instantiation process and must be removed when switching to production operating mode from instantiation mode. We will use the Oracle GoldenGate EVENTACTIONS option to the MAP statement, along with some automation to perform this switch-over without manual intervention. To accomplish this, I create two Integrated Replicat parameter files with the following settings:

File: rmssljp.prm

replicat rmssljp
nolist
include ./dirprm/macrolib.mac
list
#dbconnect(60,seconds)
handlecollisions
map dbo.ogg_migrate, target ljptpc.ogg_migrate,
eventactions (shell ‘./dirsql/comp_migration.sh’, abort);
map dbo.categories, target ljptpc.categories;
map dbo.categories_description, target ljptpc.categories_description;
map dbo.customers, target ljptpc.customers;
map dbo.customers_info, target ljptpc.customers_info;
map dbo.customers_lookup, target ljptpc.customers_lookup;
map dbo.next_cust, target ljptpc.next_cust;
map dbo.next_order, target ljptpc.next_order;
map dbo.orders, target ljptpc.orders;
map dbo.orders_products, target ljptpc.orders_products;
map dbo.orders_status_history, target ljptpc.orders_status_history;
map dbo.orders_totals, target ljptpc.orders_totals;
map dbo.products, target ljptpc.products;
map dbo.products_description, target ljptpc.products_description;
map dbo.products_to_categories, target ljptpc.products_to_categories;

File: rmssljp_new.prm

replicat rmssljp
#dbconnect(10,minutes)
map dbo.*, target ljptpc.*;

We need to also create the shell script called by the EVENTACTIONS setting, ./dirsql/comp_migration.sh:

cd /ogg_ateam/oracle/ogg_12.2_12c
mv ./dirprm/rmssljp.prm ./dirprm/rmssljp_migrate.prm
mv ./dirprm/rmssljp_new.prm ./dirprm/rmssljp.prm
./ggsci < ./dirsql/clean_instance.oby

Then the ./dirsql/clean_instance.oby file piped into GGSCI:

dblogin useridalias oggalias
delete replicat rilda
delete replicat rildb
delete replicat rildc
delete replicat rildd
delete replicat rilde
delete replicat rildf
delete replicat rildg
delete replicat rildh
delete replicat rildi
delete replicat rildj
delete replicat rildk
delete checkpointtable lpenton.ggchkpt !
shell rm -rf ./dirdat/iload
exit

Create the Oracle GoldenGate Instances

With all of the configuration done, we can create the source and target Oracle GoldenGate instances. When creating instances with multiple groups, I prefer to have GGSCI execute obey files as this is more efficient and less error prone than typing multiple ADD statements on a command line.

Create the Source Instance

My obey file, ./dirprm/add_ogg_groups.oby looks like this:

start mgr
dblogin sourcedb mss08ljp, useridalias gg_db_alias
add trandata dbo.*
add extract eilda, sourceistable
add extract eildb, sourceistable
add extract eildc, sourceistable
add extract eildd, sourceistable
add extract eilde, sourceistable
add extract eildf, sourceistable
add extract eildg, sourceistable
add extract eildh, sourceistable
add extract eildi, sourceistable
add extract eildj, sourceistable
add extract eildk, sourceistable
add extract eljp, tranlog, begin now
add exttrail ./dirdat/ep, extract eljp, megabytes 500
add extract pljp, exttrailsource ./dirdat/ep
add rmttrail ./dirdat/rp, extract pljp, megabytes 500
status all
info *, task
info exttrail *
info trandata dbo.*

To create the instance, start GGSCI, and run the obey file.

Oracle GoldenGate Command Interpreter for SQL Server
Version 12.2.0.1.161018 24793923
Windows x64 (optimized), Microsoft SQL Server on Dec  1 2016 23:01:51
Operating system character set identified as windows-1252.

Copyright (C) 1995, 2016, Oracle and/or its affiliates. All rights reserved.

GGSCI (******) 1> obey ./dirprm/add_ogg_groups.oby

In the results, we see the instance is created and everything is ready to start both real-time data capture and the Direct Read Extracts.

GGSCI (****** as ggadmin@MSS08LJP) 20> status all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     STOPPED     ELJP        00:00:00      00:00:04
EXTRACT     STOPPED     PLJP        00:00:00      00:00:02


GGSCI (****** as ggadmin@MSS08LJP) 21> info *, task

EXTRACT    EILDA     Initialized   2017-05-25 08:46   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
                     First Record         Record 0
Task                 SOURCEISTABLE

EXTRACT    EILDB     Initialized   2017-05-25 08:46   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
                     First Record         Record 0
Task                 SOURCEISTABLE

EXTRACT    EILDC     Initialized   2017-05-25 08:46   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
                     First Record         Record 0
Task                 SOURCEISTABLE

EXTRACT    EILDD     Initialized   2017-05-25 08:46   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
                     First Record         Record 0
Task                 SOURCEISTABLE

EXTRACT    EILDE     Initialized   2017-05-25 08:46   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
                     First Record         Record 0
Task                 SOURCEISTABLE

EXTRACT    EILDF     Initialized   2017-05-25 08:46   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
                     First Record         Record 0
Task                 SOURCEISTABLE

EXTRACT    EILDG     Initialized   2017-05-25 08:46   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
                     First Record         Record 0
Task                 SOURCEISTABLE

EXTRACT    EILDH     Initialized   2017-05-25 08:46   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
                     First Record         Record 0
Task                 SOURCEISTABLE

EXTRACT    EILDI     Initialized   2017-05-25 08:46   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
                     First Record         Record 0
Task                 SOURCEISTABLE

EXTRACT    EILDJ     Initialized   2017-05-25 08:46   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
                     First Record         Record 0
Task                 SOURCEISTABLE

EXTRACT    EILDK     Initialized   2017-05-25 08:46   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
                     First Record         Record 0
Task                 SOURCEISTABLE


GGSCI (****** as ggadmin@MSS08LJP) 22> info exttrail *

       Extract Trail: ./dirdat/ep
        Seqno Length: 6
   Flip Seqno Length: yes
             Extract: ELJP
               Seqno: 0
                 RBA: 0
           File Size: 500M

       Extract Trail: ./dirdat/rp
        Seqno Length: 6
   Flip Seqno Length: yes
             Extract: PLJP
               Seqno: 0
                 RBA: 0
           File Size: 500M


GGSCI (****** as ggadmin@MSS08LJP) 23> info trandata dbo.*

Logging of supplemental log data is enabled for table dbo.categories_description
Logging of supplemental log data is enabled for table dbo.products
Logging of supplemental log data is enabled for table dbo.products_description
Logging of supplemental log data is enabled for table dbo.products_to_categories
Logging of supplemental log data is enabled for table dbo.customers
Logging of supplemental log data is enabled for table dbo.customers_info
Logging of supplemental log data is enabled for table dbo.orders
Logging of supplemental log data is enabled for table dbo.orders_products
Logging of supplemental log data is enabled for table dbo.orders_status_history
Logging of supplemental log data is enabled for table dbo.orders_total
Logging of supplemental log data is enabled for table dbo.next_cust
Logging of supplemental log data is enabled for table dbo.next_order
Logging of supplemental log data is enabled for table dbo.customers_lookup
Logging of supplemental log data is enabled for table dbo.ogg_migrate
Logging of supplemental log data is enabled for table dbo.categories

Create the Target Instance

My obey file, ./dirprm/add_ogg_groups.oby looks like this:

start mgr
dblogin useridalias oggalias
add checkpointtable lpenton.ggchkpt
add replicat rilda, coordinated, exttrail ./dirdat/iload/ia, checkpointtable lpenton.ggchkpt
add replicat rildb, coordinated, exttrail ./dirdat/iload/ib, checkpointtable lpenton.ggchkpt
add replicat rildc, coordinated, exttrail ./dirdat/iload/ic, checkpointtable lpenton.ggchkpt
add replicat rildd, coordinated, exttrail ./dirdat/iload/id, checkpointtable lpenton.ggchkpt
add replicat rilde, coordinated, exttrail ./dirdat/iload/ie, checkpointtable lpenton.ggchkpt
add replicat rildf, coordinated, exttrail ./dirdat/iload/if, checkpointtable lpenton.ggchkpt
add replicat rildg, coordinated, exttrail ./dirdat/iload/ig, checkpointtable lpenton.ggchkpt
add replicat rildh, coordinated, exttrail ./dirdat/iload/ih, checkpointtable lpenton.ggchkpt
add replicat rildi, coordinated, exttrail ./dirdat/iload/ii, checkpointtable lpenton.ggchkpt
add replicat rildj, coordinated, exttrail ./dirdat/iload/ij, checkpointtable lpenton.ggchkpt
add replicat rildk, coordinated, exttrail ./dirdat/iload/ik, checkpointtable lpenton.ggchkpt
add replicat rmssljp, integrated, exttrail ./dirdat/rp
status all

To create the instance, start GGSCI, and run the obey file.

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.160823 21425179_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Sep 30 2016 15:02:26
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2016, Oracle and/or its affiliates. All rights reserved.

GGSCI (******) 1> obey ./dirprm/add_ogg_groups.oby

In the results, we see the instance is created and everything is ready to the migration.

GGSCI (****** as lpenton@a12101s/OGGPDB) 42> status all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
JAGENT      STOPPED
REPLICAT    STOPPED     RILDA       00:00:00      00:00:12
REPLICAT    STOPPED     RILDB       00:00:00      00:00:11
REPLICAT    STOPPED     RILDC       00:00:00      00:00:10
REPLICAT    STOPPED     RILDD       00:00:00      00:00:09
REPLICAT    STOPPED     RILDE       00:00:00      00:00:08
REPLICAT    STOPPED     RILDF       00:00:00      00:00:07
REPLICAT    STOPPED     RILDG       00:00:00      00:00:06
REPLICAT    STOPPED     RILDH       00:00:00      00:00:05
REPLICAT    STOPPED     RILDI       00:00:00      00:00:04
REPLICAT    STOPPED     RILDJ       00:00:00      00:00:03
REPLICAT    STOPPED     RILDK       00:00:00      00:00:02
REPLICAT    STOPPED     RMSSLJP     00:00:00      00:00:01

Execute the Migration

We are now ready to instantiate the target database tables. But first, we need to make sure there are no long running transactions in the SQL Server source.

Check for Long Running Transactions

Getting everything setup could take awhile, so I want to make sure I am not capturing unnecessary data that will be moved during the export. In GGSCI on the source side (SQL Server), alter the starting checkpoint for Classic Extract:

GGSCI (****** as ggadmin@MSS08LJP) 25> alter eljp, begin now
EXTRACT altered.

Before starting the Classic Extract, we need to check the database to ensure there are no active long running transactions. Transactions that were started prior to adding the Classic Extract must either be committed or aborted before starting export.

In GGSCI, execute the INFO command to get the VAM Read Checkpoint for the Classic Extract:

GGSCI (****** as ggadmin@MSS08LJP) 26> info eljp

EXTRACT    ELJP      Initialized   2017-05-25 09:39   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:49 ago)
VAM Read Checkpoint  2017-05-25 09:39:51.810000

In SQL Server Management Studio, use the DBCC OPENTRAN command to view running database transactions.

dbccopentran

In the output shown above, the current open transaction has a start time later than the VAM Read Checkpoint of Classic Extract. This transaction will be captured by Classic Extract, so we don’t need to do anything. If the transaction had a start time earlier that the Classic Extract VAM Read Checkpoint, we would need to wait for the transaction to commit, abort the transaction, or force the transaction to commit before proceeding.

Start the Extracts

To start the Extracts, in GGSCI on the source side obey the file ./dirprm/start_migration.oby; which contains the following commands:

start extract eljp
start extract pljp
start extract eilda
start extract eildb
start extract eildc
start extract eildd
start extract eilde
start extract eildf
start extract eildg
start extract eildh
start extract eildi
start extract eildj
start extract eildk
status all
info *, task
GGSCI (****** as ggadmin@MSS08LJP) 51> obey ./dirprm/start_migration.oby

GGSCI (****** as ggadmin@MSS08LJP) 65> status all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     ELJP        00:32:13      00:00:01
EXTRACT     RUNNING     PLJP        00:00:00      00:00:06


GGSCI (****** as ggadmin@MSS08LJP) 66> info *, task

EXTRACT    EILDA     Last Started 2017-05-25 10:12   Status RUNNING
Checkpoint Lag       Not Available
Log Read Checkpoint  Table dbo.products_to_categories
                     2017-05-25 10:12:02  Record 28
Task                 SOURCEISTABLE

EXTRACT    EILDB     Last Started 2017-05-25 10:12   Status RUNNING
Checkpoint Lag       Not Available
Log Read Checkpoint  Table dbo.customers
                     2017-05-25 10:12:03  Record 30256
Task                 SOURCEISTABLE

EXTRACT    EILDC     Last Started 2017-05-25 10:12   Status RUNNING
Checkpoint Lag       Not Available
Log Read Checkpoint  Table dbo.customers_info
                     2017-05-25 10:12:04  Record 30256
Task                 SOURCEISTABLE

EXTRACT    EILDD     Last Started 2017-05-25 10:12   Status RUNNING
Checkpoint Lag       Not Available
Log Read Checkpoint  Table dbo.orders
                     2017-05-25 10:12:07  Record 92361
Task                 SOURCEISTABLE

EXTRACT    EILDE     Last Started 2017-05-25 10:12   Status RUNNING
Checkpoint Lag       Not Available
Process ID           9664
Log Read Checkpoint  Table dbo.orders_products
                     2017-05-25 10:12:05  Record 0
Task                 SOURCEISTABLE

EXTRACT    EILDF     Last Started 2017-05-25 10:12   Status RUNNING
Checkpoint Lag       Not Available
Process ID           12608
Log Read Checkpoint  Table dbo.orders_products
                     2017-05-25 10:12:06  Record 0
Task                 SOURCEISTABLE

EXTRACT    EILDG     Last Started 2017-05-25 10:12   Status RUNNING
Checkpoint Lag       Not Available
Process ID           12916
Log Read Checkpoint  Table dbo.orders_products
                     2017-05-25 10:12:08  Record 1
Task                 SOURCEISTABLE

EXTRACT    EILDH     Last Started 2017-05-25 10:07   Status STARTING
Checkpoint Lag       Not Available
Process ID           3044
Log Read Checkpoint  Table dbo.orders_status_history
                     2017-05-25 10:07:57  Record 92362
Task                 SOURCEISTABLE

EXTRACT    EILDI     Last Started 2017-05-25 10:07   Status STARTING
Checkpoint Lag       Not Available
Process ID           12056
Log Read Checkpoint  Table dbo.orders_total
                     2017-05-25 10:08:01  Record 123304
Task                 SOURCEISTABLE

EXTRACT    EILDJ     Last Started 2017-05-25 10:07   Status STARTING
Checkpoint Lag       Not Available
Process ID           1480
Log Read Checkpoint  Table dbo.orders_total
                     2017-05-25 10:08:02  Record 123036
Task                 SOURCEISTABLE

EXTRACT    EILDK     Last Started 2017-05-25 10:08   Status STARTING
Checkpoint Lag       Not Available
Process ID           12912
Log Read Checkpoint  Table dbo.orders_total
                     2017-05-25 10:08:02  Record 123104
Task                 SOURCEISTABLE

Start the Instantiation Replicats

To start the instantiation replicats, in GGSCI on the target side obey the file ./dirprm/start_migration.oby; which contains the following commands:

start replicat rilda
start replicat rildb
start replicat rildc
start replicat rildd
start replicat rilde
start replicat rildf
start replicat rildg
start replicat rildh
start replicat rildi
start replicat rildj
start replicat rildk
status all
GGSCI (****** as lpenton@a12101s/OGGPDB) 106> obey ./dirprm/start_migration.oby

GGSCI (****** as lpenton@a12101s/OGGPDB) 107> status all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
JAGENT      STOPPED
REPLICAT    RUNNING     RILDA       00:00:00      00:00:00
REPLICAT    RUNNING     RILDB       00:00:00      00:00:02
REPLICAT    RUNNING     RILDC       00:00:00      00:00:03
REPLICAT    RUNNING     RILDD       00:00:00      00:00:08
REPLICAT    RUNNING     RILDE       00:00:00      00:00:01
REPLICAT    RUNNING     RILDF       00:00:00      00:00:01
REPLICAT    RUNNING     RILDG       00:00:00      00:00:00
REPLICAT    RUNNING     RILDH       00:00:00      00:00:10
REPLICAT    RUNNING     RILDI       00:00:00      00:00:09
REPLICAT    RUNNING     RILDJ       00:00:00      00:00:00
REPLICAT    RUNNING     RILDK       00:00:00      00:00:07
REPLICAT    STOPPED     RMSSLJP     00:00:00      00:14:08

Complete the Migration

To complete the migration, we need to wait until the source side Direct Read Extracts have completed reading from all of the tables. To see their status, execute the GGSCI command INFO *, TASK.

GGSCI (****** as ggadmin@MSS08LJP) 67> info *, task

EXTRACT    EILDA     Last Started 2017-05-25 10:12   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table dbo.products_to_categories
                     2017-05-25 10:12:02  Record 28
Task                 SOURCEISTABLE

EXTRACT    EILDB     Last Started 2017-05-25 10:12   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table dbo.customers
                     2017-05-25 10:12:03  Record 30256
Task                 SOURCEISTABLE

EXTRACT    EILDC     Last Started 2017-05-25 10:12   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table dbo.customers_info
                     2017-05-25 10:12:04  Record 30256
Task                 SOURCEISTABLE

EXTRACT    EILDD     Last Started 2017-05-25 10:12   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table dbo.orders
                     2017-05-25 10:12:07  Record 92361
Task                 SOURCEISTABLE

EXTRACT    EILDE     Last Started 2017-05-25 10:12   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table dbo.orders_products
                     2017-05-25 10:12:09  Record 168964
Task                 SOURCEISTABLE

EXTRACT    EILDF     Last Started 2017-05-25 10:12   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table dbo.orders_products
                     2017-05-25 10:12:10  Record 168840
Task                 SOURCEISTABLE

EXTRACT    EILDG     Last Started 2017-05-25 10:12   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table dbo.orders_products
                     2017-05-25 10:12:11  Record 169218
Task                 SOURCEISTABLE

EXTRACT    EILDH     Last Started 2017-05-25 10:12   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table dbo.orders_status_history
                     2017-05-25 10:12:12  Record 92362
Task                 SOURCEISTABLE

EXTRACT    EILDI     Last Started 2017-05-25 10:12   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table dbo.orders_total
                     2017-05-25 10:12:12  Record 123304
Task                 SOURCEISTABLE

EXTRACT    EILDJ     Last Started 2017-05-25 10:12   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table dbo.orders_total
                     2017-05-25 10:12:13  Record 123036
Task                 SOURCEISTABLE

EXTRACT    EILDK     Last Started 2017-05-25 10:12   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table dbo.orders_total
                     2017-05-25 10:12:14  Record 123104
Task                 SOURCEISTABLE

When all Direct Read Extracts report their state as STOPPED, insert the event trigger record into the source table ogg_migrate:

use LJPTPC;
insert into dbo.ogg_migrate values (CURRENT_TIMESTAMP, 'Migration complete');

On the target side, we need to check each Coordinated Apply to see if it has processed all data captured by the Direct Read Extracts. To do this, we get the size of each EXTFILE:

$ (******)[a12101s] /home/oracle/ogg_12.2_12c/dirdat/iload> ls -l
total 244264
-rw-r—–. 1 oracle oinstall    46769 May 25 12:10 ia000000
-rw-r—–. 1 oracle oinstall  6235324 May 25 12:10 ib000000
-rw-r—–. 1 oracle oinstall  6022919 May 25 12:10 ic000000
-rw-r—–. 1 oracle oinstall 68059690 May 25 12:10 id000000
-rw-r—–. 1 oracle oinstall 32683840 May 25 12:11 ie000000
-rw-r—–. 1 oracle oinstall 32662112 May 25 12:11 if000000
-rw-r—–. 1 oracle oinstall 32737732 May 25 12:11 ig000000
-rw-r—–. 1 oracle oinstall 13948615 May 25 12:11 ih000000
-rw-r—–. 1 oracle oinstall 19255357 May 25 12:11 ii000000
-rw-r—–. 1 oracle oinstall 19214867 May 25 12:11 ij000000
-rw-r—–. 1 oracle oinstall 19229180 May 25 12:11 ik000000

Then we execute the GGSCI INFO command for each Coordinated Apply and check the Log Read Checkpoint RBA value returned.

GGSCI (****** as lpenton@a12101s/OGGPDB) 129> info rilda*

REPLICAT   RILDA     Last Started 2017-05-25 12:44   Status RUNNING
COORDINATED          Coordinator                      MAXTHREADS 25
Checkpoint Lag       00:00:00 (updated 00:00:07 ago)
Process ID           16986
Log Read Checkpoint  File ./dirdat/iload/ia000000000
                     2017-05-25 12:20:52.726521  RBA 46769

REPLICAT   RILDA001  Last Started 2017-05-25 12:44   Status RUNNING
COORDINATED          Replicat Thread                  Thread 1
Checkpoint Lag       00:00:00 (updated 00:00:05 ago)
Process ID           16995
Log Read Checkpoint  File ./dirdat/iload/ia000000
                     2017-05-25 12:10:53.233849  RBA 46769

REPLICAT   RILDA002  Last Started 2017-05-25 12:44   Status RUNNING
COORDINATED          Replicat Thread                  Thread 2
Checkpoint Lag       00:00:00 (updated 00:00:04 ago)
Process ID           16996
Log Read Checkpoint  File ./dirdat/iload/ia000000
                     2017-05-25 12:10:53.233849  RBA 46769

REPLICAT   RILDA003  Last Started 2017-05-25 12:44   Status RUNNING
COORDINATED          Replicat Thread                  Thread 3
Checkpoint Lag       00:00:00 (updated 00:00:07 ago)
Process ID           16997
Log Read Checkpoint  File ./dirdat/iload/ia000000
                     2017-05-25 12:10:53.233849  RBA 46769

REPLICAT   RILDA004  Last Started 2017-05-25 12:44   Status RUNNING
COORDINATED          Replicat Thread                  Thread 4
Checkpoint Lag       00:00:00 (updated 00:00:05 ago)
Process ID           16998
Log Read Checkpoint  File ./dirdat/iload/ia000000
                     2017-05-25 12:10:53.233849  RBA 46769

REPLICAT   RILDA005  Last Started 2017-05-25 12:44   Status RUNNING
COORDINATED          Replicat Thread                  Thread 5
Checkpoint Lag       00:00:00 (updated 00:00:01 ago)
Process ID           16999
Log Read Checkpoint  File ./dirdat/iload/ia000000
                     2017-05-25 12:10:53.233849  RBA 46769

REPLICAT   RILDA006  Last Started 2017-05-25 12:44   Status RUNNING
COORDINATED          Replicat Thread                  Thread 6
Checkpoint Lag       00:00:00 (updated 00:00:06 ago)
Process ID           17000
Log Read Checkpoint  File ./dirdat/iload/ia000000
                     2017-05-25 12:10:53.233849  RBA 46769

When each Coordinated Apply Thread has a Log Read Checkpoint RBA value equal to the file size returned by the ls command, it has completed loading and may be stopped. Continue checking until all Coordinated Apply groups have fully consumed the data from their read files.

GGSCI (****** as lpenton@a12101s/OGGPDB) 144> status all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
JAGENT      STOPPED
REPLICAT    STOPPED     RILDA       00:00:00      00:01:43
REPLICAT    STOPPED     RILDB       00:00:00      00:01:19
REPLICAT    STOPPED     RILDC       00:00:00      00:01:07
REPLICAT    STOPPED     RILDD       00:00:00      00:00:51
REPLICAT    STOPPED     RILDE       00:00:00      00:00:34
REPLICAT    STOPPED     RILDF       00:00:00      00:00:21
REPLICAT    STOPPED     RILDG       00:00:00      00:00:04
REPLICAT    STOPPED     RILDH       00:00:00      00:00:04
REPLICAT    STOPPED     RILDI       00:00:00      00:00:04
REPLICAT    STOPPED     RILDJ       00:00:00      00:00:03
REPLICAT    STOPPED     RILDK       00:00:00      00:00:01
REPLICAT    STOPPED     RMSSLJP     00:00:00      00:31:15

To complete the migration, start the Integrated Replicat.

GGSCI (****** as lpenton@a12101s/OGGPDB) 150> start rmssljp

Sending START request to MANAGER ...
REPLICAT RMSSLJP starting

GGSCI (****** as lpenton@a12101s/OGGPDB) 151> info rmssljp

REPLICAT   RMSSLJP   Last Started 2017-05-25 13:05   Status RUNNING
INTEGRATED
Checkpoint Lag       00:00:00 (updated 00:34:41 ago)
Process ID           23560
Log Read Checkpoint  File ./dirdat/rp000000000
                     First Record  RBA 0

The Integrated Replicat will process all queued data until it reaches the trigger event. At that time a Linux shell will be started and the comp_migration.sh script will be executed. Upon completion of the script execution, the Integrated Replicat will go into the ABEND state, after sixty seconds Manager will restart the process.

GGSCI (****** as lpenton@a12101s/OGGPDB) 317> info rmssljp

REPLICAT   RMSSLJP   Last Started 2017-05-25 13:09   Status ABEND
INTEGRATED
Checkpoint Lag       00:00:00 (updated 00:00:03 ago)
Process ID           24810
Log Read Checkpoint  File ./dirdat/rp000000000
                     2017-05-25 11:43:30.856309  RBA 310250


GGSCI (****** as lpenton@a12101s/OGGPDB) 318> !
info rmssljp

REPLICAT   RMSSLJP   Last Started 2017-05-25 13:10   Status RUNNING
INTEGRATED
Checkpoint Lag       00:00:00 (updated 00:00:05 ago)
Process ID           29406
Log Read Checkpoint  File ./dirdat/rp000000000
                     2017-05-25 13:25:58.705987  RBA 596397


GGSCI (****** as lpenton@a12101s/OGGPDB) 319> status all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
JAGENT      STOPPED
REPLICAT    RUNNING     RMSSLJP     00:00:00      00:00:07

The Microsoft SQL Server to Oracle migration is now complete. Before placing the Oracle database into production, we should validate it against the SQL Server database via Oracle GoldenGate Veridata.

Summary

In this article we presented details on how to use Oracle GoldenGate to instantiate and perform real-time data apply to manually created Oracle Database tables as part of a zero-downtime Microsoft SQL Server to Oracle database migration. The other two articles in this series provided details on how to use SQL Server Management Studio to create and instantiate the Oracle Database tables (Part 1), and how to use Oracle SQL Developer to create and instantiate the Oracle Database tables (Part 2). In both cases, Oracle GoldenGate was used to perform real-time data capture for a zero-downtime migration.

For more information on what other articles are available for Oracle GoldenGate please view our index page.

Add Your Comment