MS SQL to Oracle Data Migration note

MS SQL to Oracle Data Migration note

I. Background of the task

The old system uses the MS SQL Server database, the new system uses the Oracle database, and now the data from the old system needs to be migrated to the new system, and the old data can be used or queried in the new system after being transformed by the agreed rules. In addition, the implementation personnel of the new system are not clear about the data structure of the old system, and the data structure of the two old and new systems are different. For the convenience of the narrative, the old system maintenance personnel referred to as a, the new system implementation personnel referred to as B.

Second, the general idea

1, A and B according to their understanding of the information, together with the development of the intermediate table;

2, a from MS SQL Server extracts data, and exported as the intermediate table data, saved as TXT file;

3, b The data in the TXT file into the Oracle intermediate table;

4, B merges the data from the Oracle intermediate tables into the data tables used by the business logic.

Third, the concrete realization

The 1th step, which involves specific business logic, is omitted here.

2nd, use the MS SQL Server BCP command to export data from MS SQL Server, and use batch processing to export multiple tables together (only one table is listed here), see:

3rd step using Oracle's SQLLDR command to import the data from the previous step to Oracle, see:

　　

Control files:

The 4th step involves the transformation of the specific business logic, where merge data is used, and the need for incremental import is compatible. Also omitted here.

Finally, the three steps are used to make the batch process coherent to form a file, and then simply execute this batch file to complete the entire data migration process from MS SQL Server export to the import of Oracle and the execution of procedure in Oracle.

Directory structure See:

　　

Description

Bad: Store error messages that occurred during SQLLDR execution

BAT: A batch file that stores SQL in the export, import, and execute SQL folders, see:

Execute a batch file of SQL in the SQL folder:

　　　　

Bat Directory files:

　　　　

CTL: The CTL file required to store the SQLLDR

Data: Storage of BCP export and SQLLDR import

SQL: Holds SQL executed by Oracle, with an "EXIT" at the end of the SQL statement in each SQL file.

Data_export_import_3in1.bat: Executes all batch files in the bat sequentially, and outputs the execution log information for each step, see. Run this file to complete the data migration for the entire process.

　　

Iv. Summary

At the beginning of this task, the SQL Developer Copy Table feature was used-copy the table from the MS SQL Server database to Oracle, see:

　　

Using this method, the data tables can be successfully copied to Oracle, and the operation is convenient and concise, but the speed is relatively slow, the comparison of the two methods is shown in the table below (by the results of multiple derivative of the actual operation):

　　

As you can see from the table above, using the BCP & Sqlldr method takes much less time than the SQL Developer method in the same amount of data.

In addition, in practice, it is found that the use of "BCP & Sqlldr" has the following features:

1, you can re-export the data to be exported to filter, by writing SQL in the bcp command;

2. You can do some simple formatting of the data to be imported (such as removing whitespace, replacing unwanted characters, etc.), by processing it in the CTL file of Sqlldr.

3, the entire implementation process can be obtained log, the problem of error processing more convenient.

--------------------------------------------------------------------------------------------------------------- ------------------------

The above content is a personal practice summary, if there are deficiencies, please point out.