How to Migrate Data from SQL Server to Oracle In Easy Steps?

Microsoft SQL Server is a relational database management system. The purpose of the system is to manage and store information. Various business intelligence, analytics, and transaction processing operations are supported by the system.

Oracle is a computer technology company known for its Java-based software and services. Oracle sells cloud-engineering services and systems as well as database management systems through its four main business segments: cloud and license, hardware, and services.

This article discusses the different steps to link SQL Server to Oracle database extensively. In addition to that, it also describes SQL Server and Oracle briefly.

What is Microsoft SQL Server?

Developed in 1989, SQL Server is a Relational Database Management System (RDBMS) developed by Microsoft. SQL Server is based on SQL, a widely-used programming language for working with relational databases. SQL Server has been available on Linux since 2016, but it has previously only been available on Windows operating systems.

Key Features of  Microsoft SQL Server

• Analytics Support: Organizations seek insights into stored and managed data to make better decisions. With Microsoft SQL, you can analyze your stored data using integration with Microsoft SQL Server Analysis (SSAS) which is an online analytical processing tool.
• Multiple Integrations: SQL enables users to perform various tasks like Data Extraction, Transformation, Data Migration, Data Loading, and more on their stored data using Microsoft SQL Server Integration Services (SSIS).

Using SQL Server as a Data Warehouse can also be advantageous for your organization. Leverage its robust feature set by migrating data to SQL Server for a better analysis and reporting with Hevo.

What is Oracle?

Oracle is a Relational Database System(RDBMS) developed by Oracle Corporation on 16 June 1977. Oracle is the most famous among all the relational databases, sometimes also called Oracle DB. Oracle is the RDBMS that implements object-oriented features like user-defined types, inheritance, and polymorphism, so it is also called an Object-Relational Database Management System (ORDBMS). SQL queries are used to access data from Oracle.

Key Features of Oracle

• Reliability: The main reason for choosing Oracle is its reliability. OracleDB provides the most secured and private database services to its clients.
• Availability: The OracleDB is never offline or out of service. It offers and maintains the 24*7 availability of the database.
• Scalability and Performance: Oracle has features like Real Application Clustering and Portability which make it highly scalable.
• Portability: The Oracle database can be ported over 100 different hardware platforms and around 20 networking protocols.

Benefits of connecting SQL Server to Oracle

A few Benefits of connecting SQL Server to Oracle are:

• You get access to data stored in Oracle right from the SQL server without the need for coding. 
• You can perform different actions on data stored in Oracle from SQL Server, such as distributed queries. 
• You can address data stored in Oracle the same way you would SQL Server.

Steps for Connecting SQL Server to Oracle 

• SQL Server to Oracle: Creating the mwrep User
• SQL Server to Oracle: Creating the Migration Repository
• SQL Server to Oracle: Capturing the Microsoft SQL Server Exported Files
• SQL Server to Oracle: Checking Conversion Preferences
• SQL Server to Oracle: Converting to the Oracle Model
• SQL Server to Oracle: Generating and Executing the Script to Create the Oracle Database Objects
• SQL Server to Oracle: Checking Offline Data Move Preferences
• SQL Server to Oracle: Analysis and Estimation
• SQL Server to Oracle: Migrating the Data
• SQL Server to Oracle: Testing and Deployment

1. Creating the mwrep User

This is the first step to connecting SQL Server to Oracle. Perform the following actions to create a new database user:

It should be noted that you can skip these steps if you already have a system orcl connection and a mwrep user.

• Step 1: Use the desktop icon to launch Oracle SQL Developer.
• Step 2: Select the Connections option under View.

• Step 3: Right-click Connections in the Connections tab and choose New Connection. You’ll see a window asking you to choose a new database connection.

• Step 4: Fill in the correct details. In the Username and Password fields, type system and your password, respectively. By checking the Save Password box. In the Hostname field, type hostname>, and in the SID field, type orcl. Select Test.

• Step 5: Verify the connection’s status on the bottom left side (above the Help button). Success should be written there. Click Connect to keep the connection.

• Step 6: The connection has been saved, and the Connections tab shows it listed under Connections.

• Step 7: Extend the system orcl connection. It should be noted that a SQL Worksheet automatically opens when a connection is established.

• Step 8: Create a user for the migration repository by entering the code below in the SQL Worksheet.

CREATE USER MWREP 
IDENTIFIED BY mwrep 
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;
GRANT CONNECT, RESOURCE, CREATE SESSION, CREATE VIEW TO MWREP;

• Step 9: Use the “Run Script (F5)” icon to execute the script.

• Step 10: The mwrep user was successfully created.

2. Creating the Migration Repository

You must set up a repository to house the necessary repository tables and PL/SQL packages before you can convert Microsoft SQL Server to Oracle. Follow these steps to achieve this:

You can skip these steps if you already have a migration repository and a mwrep orcl connection.

• Step 1: To establish a connection with the mwrep user, click on Connections in the Connections tab and choose New Connection. You’ll see a window asking you to choose a new database connection. Note that you must choose View > Connections to make this tab visible.

• Step 2: Put in the correct details in the connection box. Use mwrep for the Username and Password fields. In the Hostname field, type hostname>, and in the SID field, type orcl. Select Test.

• Step 3: Verify the connection’s status on the bottom left side (above the Help button). Success should be written there. Click Connect to keep the connection.

• Step 4: The connection has been saved, and the Connections tab shows it listed under Connections.

• Step 5: You can choose Migration Repository > Associate Migration Repository by right-clicking the mwrep orcl connection.

Next, Your new repository will be installed.

3. Capturing the Microsoft SQL Server Exported Files

Follow these steps to import the scripts from the captured Microsoft SQL Server to Oracle SQL Developer:

• Step 1: In the Migration menu, choose Third-Party Database Offline Capture > Load Database Capture Script Output.

• Step 2: Sqlserver2005.ocp can be found by browsing the Capture directory.

• Step 3: The Captured Models tab displays SQLServer2005. Expand SQLServer2005.

• Step 4: To view the list of captured objects, expand dbo.

4. Checking Conversion Preferences

Reviewing the conversion preferences at this time is crucial. Follow these steps to accomplish this:

• Step 1: To access Preferences, choose Tools.

• Step 2: Select Identifier Options after expanding Migration.

• Step 3: Verify that Is Quoted Identifier On is chosen. Select OK.

5. Converting to the Oracle Model

Follow these steps to transform the captured model into the Oracle model:

• Step 1: Convert to Oracle Model can be chosen by right-clicking the captured model SQLServer2005.

• Step 2: The Source Data Type and what it will be converted to in the Oracle Model is displayed in the Set Data Map window, which appears. Select Apply.

• Step 5: Next, your Model will be converted. The converted objects can be seen by expanding dbo Northwind.

6. Generating and Executing the Script to Create the Oracle Database Objects

Follow these steps to create the SQL script with DDL statements that will be used to create the objects in an Oracle Database:

• Step 1: Select Generate from the context menu by selecting Converted:SQLServer2005.

• Step 2: Oracle SQL is currently being created.

• Step 3: The script’s SQL is displayed. From the drop-down menu next to the right icon, choose system orcl.

Next, you can run the script by clicking F5.

• Step 4: You can now establish a connection for the dbo Northwind user after your scripts have completed running successfully. New Connection can be chosen by right-clicking Connection.

• Step 5: Fill in the correct details and dbo Northwind in the Username and Password fields. By checking the Save Password box. In the Hostname field, type hostname>, and in the SID field, type orcl. Select Test.

• Step 6: Verify the connection’s status on the bottom left side (above the Help button). Success should be written there. Click Connect to keep the connection.

• Next, you will see that your table is created, however the data is yet to be loaded.

7. Checking Offline Data Move Preferences

For the Offline Data Move scripts, particularly the Oracle SQL*Loader control files, to reference the correct format, a date format mask can be specified in the preferences. Follow these steps to accomplish this:

• Step 1: To access Preferences, choose Tools.

• Step 2: Select Data Move Options after expanding Migration.

• Step 3: Check that the Date Mask format is the same as what is listed below.

• Step 4: Make sure the Timestamp Mask is the same as what is listed below.

Select OK.

7. Analysis and Estimation

It may be simple to switch from Microsoft SQL Server to Oracle database. However, it is important to note that not all objects and syntax are migrated automatically. Consequently, manual intervention is needed.

The estimated time needed for manual tasks can be determined by analyzing the Capture Model and determining the quantity, kind, and complexity of objects.

You can use the reports below to estimate your tasks and projects.

• Migration Reports> Migration Summary
• Migration Reports> Migration Details
• Migration Reports> Automatic Name Changes

Time estimate for:

• Manually fix each object that couldn’t be converted.
• Verify and evaluate each item.
• Application modifications brought on by name changes

8. Migrating the Data

Follow these steps to import the data using the provided scripts:

• Step 1: Run the following commands at a DOS command prompt:

<prompt> cd <location where files are>
<prompt> oracle_ctl

You unzipped the files, and they are now in the Data directory. A bat file called oracle ctl contains instructions for loading the data. The data is loaded using sqlldr.

• Step 2: The command is successfully carried out.

• Step 3: Use Oracle SQL Developer instead, then retry the connection.

• Step 4: The table’s data has been successfully loaded.

• Step 5: Click on the CATEGORIES table.

• Step 6: The first cell in the PICTURE column should be double-clicked and click …

• Step 7: Click OK after selecting the Image checkbox.

9. Testing and Deployment

Depending on how complex the database to be migrated is, testing may account for a sizeable portion of the migration project. Oracle does not currently offer the tools required to automatically validate a migrated database. This stage needs to be planned for and taken into account before the migration begins.

Your project plan needs to include the following:

• Verify Database Structure
• Verify Database Security
• Verify Data
• Verify the Logical Correctness of Views
• Test Application

Refer for additional resources on migrating data MySQL to Oracle.

Conclusion

In conclusion, migrating from SQL Server to Oracle can bring significant advantages, including enhanced scalability, advanced security features, and powerful database management tools. However, the migration process requires careful planning and execution to ensure data integrity and minimize downtime. 

By understanding the key differences between the two platforms and leveraging the right tools and best practices, you can achieve a smooth and successful transition. Sign up for Hevo’s 14-day free trial to know more about seamless SQL and Oracle Migrations.

Frequently Asked Questions