X

Best Practices from Oracle Development's A‑Team

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

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 this part, we will do the same zero-downtime migration using Oracle SQL Developer to create the target schema and instantiate the tables, and use Oracle GoldenGate to replicate real-time SQL Server database transactions to an Oracle Pluggable Database schema.

Main Article

Oracle SQL Developer provides for the installation of third party tools to facilitate connectivity to non-Oracle databases. To get started, install Oracle SQL Developer after downloading from Oracle TechNetwork.

To connect to Microsoft SQL Server from Oracle SQL Developer, we need to add the jTDS JDBC driver, which may be found on sourceforge.net. Unzip the downloaded file as we'll just be using the jtds.jar file.

Install the jTDS Driver

To install the jTDS JDBC driver, start Oracle SQL Developer. Under the Tools menu, select Preferences.

sqldevel1

In the Preferences window, expand Database and select Third Party JDBC Drivers.

sqldevel2

Select the Add Entry button. In the Select Path Entry window, locate the jtds.jar file and single click the Select button.

sqldevel3

Select the OK button to complete the installation.

sqldevel4

We are now ready to create a connection to the SQL Server Database.

Connect to SQL Server

Your SQL Server Database needs to be configured to accept TPC connections.

To create the SQL Server connection, right click on Connections and select New Connection.

sqldevel5

In the New/Select Database Connection window, select the SQLServer tab.

sqldevel6Specify a name for the connection, and enter the connection criteria.

sqldevel8a

Select the Retrieve database button and choose the database that will be migrated from the drop-down list.

sqldevel9a

Select the Save button and the Connect button to connect to the database.

sqldevel10

Create the Source Oracle GoldenGate Instance

If you are doing an offline migration, you can skip this section. In most instances this is not practical, so we will use Oracle GoldenGate to facilitate an online, real-time migration.

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.

On the source (SQL Server) side, create two Oracle GoldenGate parameter files:

 Classic Extract
 File: dirprm/eljp.prm
extract eljp sourcedb mss08ljp, useridalias gg_db_alias tranlogoptions managesecondarytruncationpoint exttrail ./dirdat/ep reportcount every 10 minutes, rate table dbo.*;
 Extract Data Pump
 File: dirprm/pljp.prm
extract pljp rmthost ******.us.oracle.com, mgrport 7898, compress rmttrail ./dirdat/rp reportcount every 10 minutes, rate table dbo.*;

Create the source Oracle GoldenGate instance by executing the following GGSCI commands:

GGSCI (******) 4> add extract eljp, tranlog, begin now  EXTRACT added. GGSCI (******) 5> add exttrail ./dirdat/ep, extract eljp, megabytes 500  EXTTRAIL added. GGSCI (******) 6> add extract pljp, exttrailsource ./dirdat/ep  EXTRACT added. GGSCI (******) 7> add rmttrail ./dirdat/rp, extract pljp  RMTTRAIL added.

Do NOT start the Oracle GoldenGate Extract processes yet.

Migration Options

Oracle SQL Developer offers two methods for migrating a third party database to Oracle:

Migration Wizard provides functionality to convert migrate all third party database objects (tables, indexes, views, triggers, table data, and so forth) to a new Oracle schema.

Copy to Oracle provides functionality to copy tables and data from third party databases to Oracle, we'll cover that method first.

Migrate Using Copy to Oracle

In order to use Copy to Oracle, we need to create a target instance database user with the following privileges:

SQL> grant connect, resource, create session, create view to ljptpc identified by Oracle1;
Grant succeeded.

SQL> alter user ljptpc quota unlimited on users;
User altered.

Create a Database Connection

In Oracle SQL Developer, create a connection to the migration target Oracle database, right click on Connections and select New Connection.

sqldevel11

Enter a name for the connection, the connection criteria, and select the Test button to validate. If the status returned is "Success", select the Save button.

sqldevel12

Create the Target Oracle GoldenGate Instance

On the target (Oracle) side, modify the Oracle GoldenGate Manager parameter file.

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

The Oracle GoldenGate Manager is responsible for spawning new processes when the GGSCI start command is executed. By default, should a running process fail, i.e.; go into the ABEND state, it will not be restarted by Manager. By specifying this autorestart parameter, Manager will attempt to restart any failed Oracle GoldenGate processes. Manager will make three attempts to restart the process, wait sixty seconds to issue the restart after the process fails, and then reset its retry counter after sixty minutes.

We will be using this functionality to restart the Integrated Replicat as part of our post-migration automation. Be sure to stop and restart the Manager to activate this change.

Create two Integrated Replicat parameter files:

 Integrated Replicat
 File: ./dirprm/rmssljp.prm
replicat rmssljp useridalias gg_db_alias reportcount every 60 seconds, rate -- Create primary keys on the tables -- Executed once at Replicat startup sqlexec 'alter table ljptpc.categories add primary key (categories_id)' sqlexec 'alter table ljptpc.categories_description add primary key (categories_id, language_id)' sqlexec 'alter table ljptpc.customers add primary key (customers_id)' sqlexec 'alter table ljptpc.customers_info add primary key (customers_info_id)' sqlexec 'alter table ljptpc.customers_lookup add primary key (lookup_id)' sqlexec 'alter table ljptpc.next_cust add primary key (customers_id)' sqlexec 'alter table ljptpc.next_order add primary key (orders_id)' sqlexec 'alter table ljptpc.orders add primary key (orders_id)' sqlexec 'alter table ljptpc.orders_products add primary key (orders_products_id)' sqlexec 'alter table ljptpc.orders_status_history add primary key (orders_status_history_id, orders_id, date_added)' sqlexec 'alter table ljptpc.orders_total add primary key (orders_total_id)' sqlexec 'alter table ljptpc.products add primary key (products_id)' sqlexec 'alter table ljptpc.products_description add primary key (products_id, language_id)' 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: ./dirprm/rmssljp_new.prm
replicat rmssljp useridalias gg_db_alias reportcount every 10 minutes, rate 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;

Because Copy to Oracle does not migrate primary key or unique index data needed by Oracle GoldenGate to efficiently apply source database update and delete operations, in the rmssljp.prm parameter file below, I added SQLEXEC statements that will be executed when the Integrated Replicat starts. These statements will alter the tables created by the Copy to Oracle function, adding primary keys that match the source table definitions.

I also included two special settings:

(1) HANDLECOLLISIONS. This will allow the Integrated Replicat to process through the stream of data that was captured and queued up while the migration was running.

(2) The MAP statement EVENTACTIONS option. The event action will queue off of a record added to the source table OGG_MIGRATE and will execute a Linux shell script that will move the file ./dirprm/rmssljp.prm to a file named ./dirprm/rmssljp_migrate.prm, and move the file ./dirprm/rmssljp_new.prm to rmssljp.prm. The Integrated Replicat will go into the ABEND state after the script execution completes and be restarted by Manager.

The SQL Server table used to trigger the event was created as:

CREATE TABLE [dbo].[ogg_migrate](    [mig_ts] [datetime] NOT NULL,    [mig_txt] [varchar](100) NOT NULL,    PRIMARY KEY ([mig_ts],[mig_txt])  )

The script executed by the event action is:

 ./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

 

Add the Integrated Replicat to the Oracle GoldenGate instance by executing the following GGSCI commands:

GGSCI (******) 3> dblogin userid lpenton@oggpdb, password Oracle1  Successfully logged into database OGGPDB. GGSCI (****** as lpenton@a12101s/OGGPDB) 4> add replicat rmssljp, integrated, exttrail ./dirdat/rp  REPLICAT (Integrated) added.

Do NOT start the Integrated Replicat.

Start Real-Time Data Capture on the Source

Getting everything setup could take several hours, 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 (******) 17> 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 (den01eln) 18> info eljp  EXTRACT    ELJP      Initialized   2017-05-16 12:44   Status STOPPED  Checkpoint Lag       00:00:00 (updated 00:00:55 ago)  VAM Read Checkpoint  2017-05-16 12:44:34.717000

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

mssopentran13

In the output shown above, an active transaction started at May 16 2017 12:43:21:740PM AM is still running. Since the Classic Extract was added at May 16 2017 12:44:34.717PM, the transaction will not be captured by Classic Extract. Therefore, the transaction must either (1) be committed so the data is available in the database for the migration, or (2) aborted and restarted so Classic Extract will capture the data.

After aborting the transaction, there are now no transactions running that were started prior to adding the Classic Extract; all source data will be instantiated to the target either by the migration process, or by Oracle GoldenGate.

mssopentran14

Start the Classic Extract and Extract Data Pump. Real-time data will be captured and queued at the target Oracle GoldenGate instance while we run the export.

GGSCI (******) 20> start *ljp Sending START request to MANAGER ...  EXTRACT ELJP starting Sending START request to MANAGER ...  EXTRACT PLJP starting

Run Copy to Oracle

In Oracle SQL Developer, expand the connection for SQL Server, and then expand the database details to list all of the tables.

sqldevel13

Use CTRL + left click to select the tables to be copied.

sqldevel14

With the tables selected, right-click and select Copy To Oracle.

sqldevel15

Select the Oracle connection from the Destination Connection Name drop-down list. Ensure the Include Data box is checked and then select Apply.

sqldevel16

The copy process will begin, create the tables in the Oracle database, and load the data from SQL Server to Oracle.

sqldevel17

If no errors were encountered, the copy will display a finished statement. Click OK to terminate.

sqldevel18

You can verify the copy by expanding the Oracle connection and viewing the tables.

sqldevel19

Complete the Migration

To complete the migration, add a record to the SQL Server ogg_migrate table to trigger the Oracle GoldenGate event.

sqldevel20

Start the Integrated Replicat on the Oracle Database target to apply any data captured and not replicated during the copy.

The GGSCI info and stats commands may be used to view Integrated Replicat activity. When the event trigger transaction is processed, Integrated Replicat will go into the ABEND state and Manager will restart it:

GGSCI (******) 2> view report mgr ...  ***********************************************************************  **                     Run Time Messages                             **  *********************************************************************** ... 2017-05-17 10:28:47  INFO    OGG-00965  REPLICAT RMSSLJP restarted automatically.

Viewing the Integrated Replicat report file will show that it is running using the production set of parameters.

The migration using Copy to Oracle is complete.

 

Migrate Using Oracle SQL Developer Migration Wizard

In my testing for this article, I found the SQL Developer Migration Wizard to be cumbersome. Even though, I am including it as a migration option in case some adventurous soul has absolutely no other option but to use this tool for their SQL Server to Oracle migration.

Create the Oracle Migration Repo User

Migration Wizard needs a dedicated Oracle Database space for the migration repository. In the target Oracle Database, create the user with the following privileges:

CREATE USER migr8repo IDENTIFIED BY Oracle1   DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp; GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE PUBLIC SYNONYM   TO migr8repo WITH ADMIN OPTION; GRANT ALTER ANY ROLE, ALTER ANY SEQUENCE, ALTER ANY TABLE, ALTER TABLESPACE, ALTER ANY TRIGGER, COMMENT ANY TABLE, CREATE ANY SEQUENCE, CREATE ANY TABLE, CREATE ANY TRIGGER, CREATE ROLE, CREATE TABLESPACE, CREATE USER, DROP ANY SEQUENCE, DROP ANY TABLE, DROP ANY TRIGGER, DROP TABLESPACE, DROP USER, DROP ANY ROLE, GRANT ANY ROLE, INSERT ANY TABLE, SELECT ANY TABLE, UPDATE ANY TABLE   TO migr8repo;
Pitfall #1

The above grants came from Oracle documentation related to migration repository creation. However, I encountered privilege errors when creating the repository and after flailing around for a few hours found that the user did not have any privileges on the USERS tablespace. To fix that issue I executed the following grant:

alter user migr8repo quota unlimited on users;
Pitfall #2

So, I had sufficient privileges to create the migration repository and run the migration; however, the migration wizard kept failing without really giving me a definitive reason. Once again after flailing around for several hours I found an obscure reference stating that the migration repository user needed "administrator" level privileges; yet these privileges were not specified. I decided to go with the "big stick" approach; which may not be appropriate for end users migration a production database:

grant dba to migr8repo;

Now that you are aware of the database user pitfalls, we can continue on.

Create a Database Connection

In Oracle SQL Developer, create a connection to the migration target Oracle database, right click on Connections and select New Connection.

sqldevel11

Enter a name for the connection, the connection criteria, and select the Test button to validate. If the status returned is "Success", select the Save button.

sqldevel21a

Create the Target Oracle GoldenGate Instance

On the target (Oracle) side, modify the Oracle GoldenGate Manager parameter file.

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

The Oracle GoldenGate Manager is responsible for spawning new processes when the GGSCI start command is executed. By default, should a running process fail, i.e.; go into the ABEND state, it will not be restarted by Manager. By specifying this autorestart parameter, Manager will attempt to restart any failed Oracle GoldenGate processes. Manager will make three attempts to restart the process, wait sixty seconds to issue the restart after the process fails, and then reset its retry counter after sixty minutes.

We will be using this functionality to restart the Integrated Replicat as part of our post-migration automation. Be sure to stop and restart the Manager to activate this change.

Create two Integrated Replicat parameter files:

 Integrated Replicat
 File: ./dirprm/rmssljp.prm
replicat rmssljp useridalias gg_db_alias reportcount every 60 seconds, rate 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: ./dirprm/rmssljp_new.prm
replicat rmssljp useridalias gg_db_alias reportcount every 10 minutes, rate 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;

In the rmssljp.prm parameter file I included two special settings:

(1) HANDLECOLLISIONS. This will allow the Integrated Replicat to process through the stream of data that was captured and queued up while the migration was running.

(2) The MAP statement EVENTACTIONS option. The event action will queue off of a record added to the source table OGG_MIGRATE and will execute a Linux shell script that will move the file ./dirprm/rmssljp.prm to a file named ./dirprm/rmssljp_migrate.prm, and move the file ./dirprm/rmssljp_new.prm to rmssljp.prm. This turns off HANDLECOLLISIONS without end-user manual intervention. The Integrated Replicat will go into the ABEND state after the script execution completes and be restarted by Manager.

The SQL Server table used to trigger the event was created as:

CREATE TABLE [dbo].[ogg_migrate](    [mig_ts] [datetime] NOT NULL,    [mig_txt] [varchar](100) NOT NULL,    PRIMARY KEY ([mig_ts],[mig_txt])  )

The script executed by the event action is:

 ./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

Add the Integrated Replicat to the Oracle GoldenGate instance by executing the following GGSCI commands:

GGSCI (******) 3> dblogin userid lpenton@oggpdb, password Oracle1  Successfully logged into database OGGPDB. GGSCI (****** as lpenton@a12101s/OGGPDB) 4> add replicat rmssljp, integrated, exttrail ./dirdat/rp  REPLICAT (Integrated) added.

Do NOT start the Integrated Replicat.

Start Real-Time Data Capture on the Source

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

GGSCI (******) 17> 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 the migration.

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

GGSCI (******) 18> info eljp EXTRACT    ELJP      Initialized   2017-05-18 13:23   Status STOPPED Checkpoint Lag       00:00:00 (updated 00:00:02 ago) VAM Read Checkpoint  2017-05-18 13:23:32.994000

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

mssopentran15

In the output shown above, there is an open transaction that started May 18 2017 1:26:53:650PM. This is after the VAM Read Checkpoint of the Classic Extract, so it will be captured and replicated by Oracle GoldenGate.

It is safe to start the Classic Extract and Extract Data Pump. Real-time data will be captured and queued at the target Oracle GoldenGate instance while we run the Migration Wizard.

GGSCI (den01eln) 20> start *ljp Sending START request to MANAGER ...  EXTRACT ELJP starting Sending START request to MANAGER ...  EXTRACT PLJP starting

Create the Migration Repository

Migration Wizard needs a repository to store information is brings over from SQL Server. To create the repository, open the Tools menu, open the Migration submenu, then Repository Management, and select Create Repository.

sqldevel22

In the Create Repository window, select the connection repository connection we created from the drop-down menu, and then select the Create button.

sqldevel23

The repository will be created and then the dialog box will close.

sqldevel24

Associate the repository to the connection by right clicking the connection name and then selecting Migration Repository and Associate Migration Repository.

sqldevel25

The repository will be associated with the connection. Select the OK button to continue.

sqldevel26

Run Migration Wizard

To start Migration Wizard, expand the SQL Server connection, right click on the database to be migrated, and select Migrate to Oracle.

sqldevel27

Migration Wizard will start and present an Introduction window. Select Next to continue.

sqldevel28

Select the connection for the migration repository from the drop-down menu and then click on the Next button.

sqldevel29

Enter a name to identify this migration and select an output disk location to store the log files and scripts created by Migration Wizard. Select Next to continue.

sqldevel30

Verify the connection to the SQL Server database displays as the Source Database. Select Next to continue.

sqldevel31

Ensure the database to be migrated is selected as the database for definition capture. Select Next to continue.

sqldevel32

Specify any data type conversions. The default conversions are listed in the window; however, I want SQL Server DATETIME data to be converted to Oracle TIMESTAMP(6). To make this change, I choose the corresponding Oracle data type for SQL SERVER DATETIME and SMALLDATETIME and select TIMESTAMP(6) from the drop-down menu. This screen also allows you to modify existing conversions or create new conversion rules. When you are done defining data conversions, select Next to continue.

sqldevel32

Choose what SQL Server database objects will be translated and migrated to Oracle; all objects are selected by default. If you do not want to migrate a specific object, select it and click on the left arrow to remove it from the list. Select Next to continue.

sqldevel33

Ensure the migration repository connection is selected as the target database connection. Select Next to continue.

sqldevel34

Verify the connections for the data move. The SQL Server connection should be the source and the migration repository connection the target. Select Next to continue.

sqldevel35

A summary of the options chosen for this migration will be displayed. Verify the choices and select Finish to begin the migration.

sqldevel36

The migration will be run and status displayed.

sqldevel37

Upon completion a message is displayed. Select OK to terminate Migration Wizard.

sqldevel38

Add OGG Migration Trigger Record

Add the OGG trigger record to the SQL Server database so that it will be queued for delivery to the Oracle database. When Integrated Replicat processes this record, it will perform the event automation to restart and put itself into production mode.

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

Verify the Migration

Before continuing you should verify the Migration Wizard results by creating a connection to the new Oracle schema and checking the tables. But what are the login credentials for the new schema? We can obtain this information from the files created in the output directory. Go to the directory and locate the file master.sql.

migrateloc1

Open the file in a text editor and locate the database user create statements. By default a database user (and schema) is created with the same name as the SQL Server database that was migrated and the default password is set to the schema name in uppercase.

-- PROMPT Drop LJPTPC user -- drop user LJPTPC cascade; PROMPT Create user LJPTPC CREATE USER LJPTPC identified by &&LJPTPC_password DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; GRANT CREATE SESSION, RESOURCE, CREATE VIEW, CREATE MATERIALIZED VIEW, CREATE SYNONYM TO LJPTPC;

Using this information, create a new Oracle SQL Developer connection for this database schema. Connect to it and expand the Tables list. The first thing noticed is there are a lot more tables than I expected.

sqldevel39

No worries, we'll deal with those later. For now, let's verify our production tables. Select double click on the CATEGORIES table to view the table schema.

sqldevel40

That looks good, select the Data tab to view what was captured and loaded by Migration Wizard.

sqldevel41

This does not look right. I know my source table contains data and Migration Wizard reported that data was loaded and completed without error. So where is my data?

Pitfall #3

Once again, after several hours of digging through log files to determine why there is not data in any of my migrated tables I stumbled upon the answer. Seems that the database user created by Migration Wizard was not granted any access to the USERS tablespace:

$ (******)[a12101s] /home/oracle/ogg_12.2_12c\> sqlplus ljptpc@oggpdb/LJPTPC SQL*Plus: Release 12.1.0.1.0 Production on Fri May 19 10:36:24 2017 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Last Successful login time: Fri May 19 2017 10:19:40 -04:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> insert into ogg_migrate values (CURRENT_TIMESTAMP, 'Test insert'); insert into ogg_migrate values (CURRENT_TIMESTAMP, 'Test insert')             * ERROR at line 1: ORA-01950: no privileges on tablespace 'USERS'

Give the user access to the tablespace.

$ (******)[a12101s] /home/oracle/ogg_12.2_12c\> sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Fri May 19 10:38:58 2017 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> alter session set container=oggpdb; Session altered. SQL> alter user ljptpc quota unlimited on users; User altered.

Now we can re-run the data load portion of Migration Wizard and load the target tables.

In the Oracle SQL Developer Migration pane, expand the migration project and select the timestamped log file to view the state of each migration task.

sqldevel42

Double-click the word "complete" under the DATAMOVE column. This will start Migration Wizard. Select Next.

sqldevel43

The Move Data pane will display. Select Finish to start the data loads.

sqldevel44

The data loader will start as display status. When the load completes select the OK button.

sqldevel45

Reviewing my tables, I now see that data was loaded.

sqldevel46

Complete the Migration

Start the Integrated Replicat on the Oracle Database target to apply any data captured and not replicated during the copy.

The GGSCI info and stats commands may be used to view Integrated Replicat activity. When the event trigger transaction is processed, Integrated Replicat will go into the ABEND state and Manager will restart it:

GGSCI (******) 2> view report mgr ...  ***********************************************************************  **                     Run Time Messages                             **  *********************************************************************** ... 2017-05-19 11:35:0  INFO    OGG-00965  REPLICAT RMSSLJP restarted automatically.

Viewing the Integrated Replicat report file will show that it is running using the production set of parameters.

The migration using Oracle GoldenGate and Migration Wizard is complete; however, you may want to go back and clean up the unnecessary files in the migrated database, remove the migration repository, change the database user password for the Oracle database, and validate the Oracle database with Oracle GoldenGate Veridata before putting it into production.

Summary

In this article we performed a zero-downtime migration from Microsoft SQL Server to an Oracle Pluggable Database using Oracle SQL Developer functionality and Oracle GoldenGate. We further demonstrated some advanced features of Oracle GoldenGate that allowed me to modify the target Oracle tables programmatically.

In the next part of this blog series, we’ll use just Oracle GoldenGate to perform a zero-downtime migration.

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

 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha

Recent Content