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. The system supports various business intelligence, analytics, and transaction processing operations.

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

This article extensively discusses the different steps to link SQL Server to Oracle database. In addition, it 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 for better decisions. With Microsoft SQL, you can analyze your stored data by integrating it 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 benefit your organization. Leverage its robust feature set by migrating data to SQL Server for 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 secure and private database services to its clients.
• Availability: OracleDB is never offline or out of service. It offers and maintains 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 hardware platforms and around 20 networking protocols.

Read about the differences between SQL Server and Oracle to know how they differ. 

Benefits of connecting SQL Server to Oracle

A few Benefits of connecting these two tools are:

• Connecting the databases can help break down data silos, enabling better data sharing and collaboration across different departments or teams.
• Developers can write applications that interact with SQL Server and Oracle databases using a unified interface.
• Connecting the databases can reduce data redundancy and minimize the need to maintain separate data copies.

Steps for Connecting SQL Server and Oracle 

1. Creating the mwrep User

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. Check the ‘Save Password’ box. In the Hostname field, enter ‘<hostname>’, and in the SID field, type ‘orcl.’ Finally, select ‘Test’ to proceed.

• Step 5:  Verify the connection 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 data 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: Choose “Convert to Oracle Model” by right-clicking the captured model SQLServer2005.

• Step 2: The source data type and its corresponding conversion in the Oracle model are displayed in the Set Data Map window that appears. Click ‘Apply’ to proceed.

• 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 validate a migrated database automatically. 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.

Challenges Faced by Users in Connecting SQL Server and Oracle

Connecting SQL Server and Oracle also involves considerations and potential challenges:

• Complexity: Establishing and maintaining a connection between the two databases can involve some complexity, especially in complex environments.
• Data Type Discrepancies: SQL Server and Oracle have different data type systems. You might encounter issues when mapping data types between the two, which can lead to data loss or unexpected behavior.
• Performance: Performance can be impacted by network latency and the volume of data being transferred between the databases.
• Security: Ensuring secure access and data exchange between the databases requires careful security planning and implementation.

Best Practices for Performing This Integration

• Convert data to Oracle format: If necessary, convert data types and formats to be compatible with Oracle.
• Data volume: Consider using data partitioning or parallel loading techniques for large datasets to improve performance.
• Downtime: Minimize downtime during the migration by using techniques like change data capture or data replication.
• Validate data integrity: Verify that the migrated data is accurate and complete.
• Optimize performance: Tune the Oracle database for optimal performance.

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