Best Practices to Migrate SQL Server to Oracle Database

SQL Server and Oracle are both mainstream databases on the market, and it’s not strange that database users convert one to another for certain features or specific business needs. This article shows how to convert SQL Server to Oracle database with Oracle SQL Developer step by step.

Overview of SQL Server and Oracle

Before that, let’s get to know what is SQL Server and Oracle and what they can do. SQL Server is a relational database management system developed by Microsoft and combines with a SQL implementation tool T-SQL to use. The database runs on Windows and Linux and supports various transaction processing and analytics applications.

SQL Server provides different editions for clients to choose from. It also has various products, documentation, and on-premises and cloud support. So, this database attracts many global users, in fact, it is one of the most popular databases.

While Oracle database is a multi-model database management system for online transaction processing, data warehousing, and mixed database workloads. It could be used on-prem, on-cloud, or a hybrid cloud installation with some service providers.

Oracle runs on more platforms than SQL Server and supports features like query optimization, rollback in the transaction process, and schema with instance but the latter couldn’t.

Some SQL Server users may want to migrate to the Oracle database, and Oracle provides a free database migration tool, i.e., Oracle SQL Developer. With it, you can;

• Automatically converts column data types to Oracle and resolves object name conflicts;

• Parses and converts stored procedures, functions, triggers, and views from T-SQL to Oracle PL/SQL;

• Creates data transfer scripts and DDL scripts for the target Oracle database generation;

• Gives users access to extensive customization features;

• Produces reports on migration status.

Migrate SQL Server to Oracle with Oracle SQL Developer

Prerequisites:

• Available in Windows Server 2016/2019/2022, Linux, or MacOS X.

• Supports Oracle database 11gR2, 12C, 18c, 19c, 20c.

• Download and unzip the mssqlservermigration zip file into the working directory.

• Download and unzip Oracle SQL Developer 22.2.1.

1.   Create the mwrep user

Note: skip this step if you have a system-orcl connection and mwrep user.

a.     Start the SQL Developer on the desktop (if there isn’t, find the executable file sqldeveloper.exe on Windows or sqldeveloper.sh on Linux) and choose View> Connections.

b.     In the Connections pane, right-click Connections and choose New Connection to launch the new database connection window.

c.     Input system_orcl or other names for connection identification in the Connection Name field. Enter the system and password respectively in the Username and Passwords fields, then click Save Password. Type hostname in the Hostname field, and orcl in the SID field. Click Test.

d.     On the top left side, check the status as Success. Click Save to save the connection or Connect to connect. Once the connection is saved, you can see it under the Connections.

e.     Extend the system orcl connection and a SQL Worksheet that runs SQL against the connection opens automatically if the connection is established.

f.      Create a user for the migration repository in the SQL Worksheet. Click Run Script (F5) icon and a mwrep user is created.

CREATE USER MWREP

IDENTIFIED BY mwrep

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP;

GRANT CONNECT, RESOURCE, CREATE SESSION, CREATE VIEW TO MWREP;

2.   Create the migration repository

Note: skip this step if you already have a mwrep-orcl connection and a migration repository to save the required repository tables and PL/SQL packages.

a.     Navigate to View> Connections> right-click Connections> New Connection to connect to the mwrep user.

b.     Input mwrep_orcl or other names for connection identification in the Connection Name field. Enter more in the Username and Passwords fields, then click Save Password. Type hostname in the Hostname field, and orcl in the SID field. Click Test.

c.     On the top left side, check the status as Success. Click Connect to keep the connection. Once the connection is saved, you can see it under the Connections.

d.     Right-click the mwrep_orcl connection and choose Migration Repository> Associate Migration Repository to open the progress window and click OK.

3.   Capture exported files of SQL Server

a.     In the Oracle SQL Developer, go to Migration> Third-Party Database Offline Capture> Load Database Capture Script Output.

b.     Browse the Capture directory and find the sqlserverxxxx.ocp. Click Close after the process.

c.     Under the Captured Models, find and expand the SQLServerxxxx. Extend dbo to view the captured list.

4.   Check conversion preferences

a.     Navigate to Tools> Preferences> extend Migration> Identifier Options.

b.     Choose Is Quoted Identifier On and click OK.

5.   Convert the Captured database

a.     Right-click the SQLServerxxxxx and choose Apply in the Set Data Map window where it shows the Source Data Type and converted one in the Oracle Model. Click Close after the conversion.

b.     Expand Converted:SQLServerxxxx in the Converted Models tab. Extend dbo Northwind to see the converted objects.

6.   Generate the Oracle database

a.     Choose Converted:SQLServerxxxx and in the content menu, choose Generate. Click Close after the creation.

b.     In the script’s SQL interface, select system orcl from the drop-down and press Run Script (F5).

c.     The connection for the dbo Northwind user is established after the scripts. Right-click Connection to choose New Connection.

d.     Input dbo northwind-migrated orcl or other names for connection identification in the Connection Name field. Enter dbo Northwind in the Username and Passwords fields, then click Save Password. Type hostname in the Hostname field, and orcl in the SID field. Click Test.

e.     On the top left side, check the status as Success. Click Connect to keep the connection. Once the connection is saved, you can see it under the Connections.

f.      Expand the dbo Northwind-migrated orcl connection> Tables> EMPLOYEES table> Data tab. The table is empty now.

7.   Specify offline data move preferences

a.     Navigate to Tools> Preferences> expand Migration> Data Move Options.

b.     The Data Mask format should be the same as Mon dd yyyy HH:mi:ssAM and the Timestamp Mask is the same as Mon dd yyyy HH:mi:ss:ff3AM. Click OK.

8.   Analysis and estimation

Manual intervention is needed for some objects and syntax are not converted automatically. You can use the following reports to estimate the time for manual tasks including fixing each object that failed to be converted manually, checking and evaluating every item, and name changes that affect the application.

• Migration Reports> Migration Summary.

• Migration Reports> Migration Details.

• Migration Reports> Automatic Name Changes.

9.   Import the data

a.     In a DOS command prompt, run the scripts.

<prompt> cd <location where files are>

<prompt> oracle_ctl

Note: unzip the files and they are in the Data directory. oracle_ctl is a bat life that includes data load statements, which users sqlldr to load the data.

b.     Use the Oracle SQL Developer and Refresh the connection.

c.     Click CATEGORIES table after the data of the table is loaded.

d.     In the PICTURE column, double-click the first cell and click …

e.     Check the Image box and click OK.

10.  Test and deployment

Test and verify the database before the migration to make sure it is prepared for migration (Oracle doesn’t provide such a tool for now).

• Verify Database Structure and Security.

• Verify Logical Correctness of Views.

• Test Application.

• Verify Data.

Backup SQL Server and Oracle in Advance

Since database migration is a risky operation that may lead to data loss, semantics error, data corruption, or extended downtime during the process, the list can go on and on. It’s a preventive measure to backup the source database and target database before and after the conversion.

Even if something goes wrong, businesses could perform a data recovery based on the backups to ensure data integrity and safety.

Vinchin Backup & Recovery delivers complete and smart database backup for 6 databases such as Oracle Database, MySQL, SQL Server, PostgreSQL, Postgres Pro, and MariaDB. It also provides scheduled backup, V2V migration, and instant recovery for 10+ virtualizations (VMware, Oracle OLVM, Xen, etc.)

This backup solution makes backup and recovery easier with built-in features:

Automatic backup: make frequent database backups (full, incremental, differential, archive log backup that changes with databases) under schedules and job status email alerts automatically.

Database size reduction: deduplicate and compress database size to save at least 50% of data for storage and cost.

Multi-database management: centrally manage all database backups stored in the Vinchin backup server and control over a web console to cut down costs on administration and other database backup software.

Anti-ransomware protection: shield backup data and backup server from malicious actors via real-time IO monitoring that rejects any unauthorized visits. You could also encrypt the data path for safety during the backup.

60-day free trial of the Enterprise version: experience and test all advanced features with the full-featured Enterprise version of Vinchin Backup & Recovery now.

Takeaway

Since Oracle and SQL Server are both widely used databases, it is not surprising that database customers switch between them depending on their capabilities or business requirements. This blog demonstrates step-by-step how to use Oracle SQL Developer to convert a SQL Server database to an Oracle database (see how to migrate Oracle to SQL Server).

Test and backup the database before migration in case the conversion fails. Consider Vinchin Backup & Recovery with smart backup and recovery strategies at a cost-effective price.