Data Migration notes from ms SQL to Oracle, sqloracle

Data Migration notes from ms SQL to Oracle, sqloracle

Data Migration notes from ms SQL to Oracle

I. Task background

The old system uses the ms SQL Server database and the new system uses the Oracle database. Now we need to migrate the data in the old system to the new system. After the old data is converted according to the agreed rules, can be used or queried in the new system. In addition, the implementers of the new system do not know the data structure of the old system, and the data structures used by the two old and new systems are significantly different. For ease of description, the maintenance personnel of the old system are abbreviated as A and the implementers of the new system are abbreviated as B.

Ii. Overall Thinking

1. A and B should work out an intermediate table based on their respective information;

2. A extracts data from ms SQL Server, exports the data as an intermediate table, and saves the data as A txt file;

3. B. Import the data in the txt file to the Oracle intermediate table;

4. B combines the data in the Oracle intermediate table into the data table used by the business logic.

Iii. Implementation

Step 1 involves specific business logic, which is omitted here.

Step 2: Use the BCP command of ms SQL Server to export data from ms SQL Server and use batch processing to export multiple tables (only one table is listed here). For details, see:

Step 2 use the sqlldr command of Oracle to import the data exported in the previous step to Oracle. For details, see:

　　

Control file:

Step 1 involves the conversion of specific business logic. Here we use Merge to Merge data and be compatible with the needs of incremental import. This is also omitted.

Finally, use batch processing to form a file. You only need to execute the batch processing file to export the file from ms SQL Server, the entire data migration process to import to Oracle and execute Procedure in Oracle.

For the directory structure, see:

　　

Note:

Bad: stores error messages during sqlldr execution

Bat: stores the SQL batch files in the SQL folder for export, import, and execution. For details, see:

Execute the SQL batch file in the SQL Folder:

　　　　

Files in the bat directory:

　　　　

Ctl: stores the CTL files required by sqlldr.

Data: stores data exported by BCP and imported by sqlldr.

SQL: stores the SQL statements executed by Oracle. Add "EXIT;" to the end of each SQL statement in each SQL file.

DATA_Export_Import_3in1.bat: Merge and execute all batch files in bat in sequence, and output the execution log information for each step, as shown in. Run this file to complete the data migration process.

　　

Iv. Summary

At the beginning of this task, I used the copy table function of SQL Developer-copy the table from the ms SQL Server database to Oracle. For details, see:

　　

Using this method, you can smoothly copy data tables to Oracle, and the operation is convenient and concise, but the speed is slow. The comparison of the two methods is shown in the table below (the statistical results of multiple derivatives in the actual operation):

　　

As shown in the preceding table, the time consumed by using the "BCP & SQLLDR" method is much less than that of "SQL Developer" in the case of the same data volume.

In addition, we also found that the use of "BCP & SQLLDR" has the following features:

1. When exporting data, you can filter the data to be exported and write SQL statements in the BCP command;

2. You can simply format the data to be imported (such as removing spaces and replacing unwanted characters) and process the data in the CTL file of SQLLDR.

3. Logs can be obtained throughout the execution process, making it easier to handle errors.

 

Certificate ---------------------------------------------------------------------------------------------------------------------------------------

The above content is my practice summary. If you have any shortcomings, please note.

　

How can I transfer SQL data to oracle?

I don't know what you mean by "Changing ORACLE Database". I only tell you that if your company has a set of programs that want to change the background database, importing data into oracle is not complicated, just do what my brother said on the first floor. But the trouble is that SQL server uses TSQL, Oracle uses PL/SQL, and the two SQL languages are not fully compatible (for example, many functions and even syntax are different ), this means that it is not enough to import data to oracle, And the foreground program needs to be modified again. If the program is large, this part of work is quite heavy.

The above comments are for your reference only.

How can I migrate data from SQLSERVER to an ORACLE database?

You can use powerbuilder, pl/SQL developer, or SQL loader. The best tool is SQL loader. However, this tool is flexible, complex, and powerful.