SQL Migration to Oracle instance

Nohup./command.sh > Output 2>&1 &

　　

SQL Migration to Oracle instance

In daily operations, we often have different types of migrations of databases, much of which is migrating from SQL Server to Oracle, with a similar project in the previous phase, and I've documented some of these considerations.

First, the programme of migration

Previously, the SQL---Oracle migration, the use of SQL Server DTS has also written Sqlloader scripts themselves, but both scenarios are not very satisfied, DTS often error, Sqlldr manual editing is laborious, a little attention will be written wrong.

This is a reference to http://www.cnblogs.com/hiizsk/archive/2011/07/10/2102452.html.

Working with Oracle SQL developer is a good result.

Here are a few things to pay special attention to, and it's important to say it in advance.

1. ORACLE SQL Developer is not PL/developer, so be careful.
2. Oracle SQL Developer version issue, above address reference document or download from Oracle official site I did not test success, Instead to be a relatively old version of 3.0.04 's version of the trial succeeded, at the end of the document I provided a download, this version is no JRE, corresponding to 6 is not 7 Ah, please install yourself, in the first run when prompted to specify the path of the JRE6, the specified right can be normal operation.
3. The above document does not mention how to import, this is very important, you need to upload the exported document to the server to execute.
4. Coding, this is the most frustrating content, because SQL server2008 R2 does not support BCP's UTF-8 export, but Oracle production machine is now basically AL32UTF8, so you need to manually transcode the contents of the exported Data folder to Utf-8. EditPlus has a batch transcoding function, very easy to small file batch operation is very good, my more than 480 files are basically done by it. For files greater than 100M EditPlus completely hang out, can only use UltraEdit, I use it to execute 2G more than one file, a few minutes to fix.

Ii. Practical steps

This step is a lot of reference to the content of the absolute, ah, copyright is his AH. I have added and amended some of the content.

1. Part I: Getting tools

Not recommended to go to Oracle official website download, although support to Oracle 12C, anyway I was useless it to fix my SQL Server R2 to Oracle 11G conversion, can use if the person successfully told me under.

Go directly to the end of the document to download the provided content, pay attention to self-installation jre6

2. Download the SQL SERVER driver

"Click menu Help , select Check for updates , pop up Check Update wizard window" I have not succeeded, I had to download the jtds-1.2.2-dist myself, this I also provided a download. The associated methods are as follows

Start the Develop-----tool-----preferences-------Database-----Third-party JDBC drivers, add entries select the Jtds file, and then restart develop.

1. Connection Oracle and the SQL, Create an account

Basic and the same as the document (not clear script see http://www.cnblogs.com/hiizsk/archive/2011/07/10/2102452.html), here to say, the default newly created user's default tablespace is user, I do not recommend this , a new table space is created, and then the Migratons user's tablespace is created using the new, and try not to affect the default tablespace.

In addition, after the script executes, the default space for the created table is user, and it is recommended that you create a new table space before importing the data, moving the tables to a new table space such as Newtbs.

Select '  '| | table_name| | ' move tablespace Newtbs; '  from User_all_tables;

3. Database Migration Wizard, etc.

You can follow the document completely, and the last step is offline.

(1) SQL Server in the schema to Oracle The mode in which the name is processed

This part doesn't have to be done, I have a better way to skip it.

(2) Transfer data

From this section, the document vague, in fact, this is the implementation of the import error-prone place. First export Data Execution unload_script [Server] [username] [Password]

This execution can be performed natively using CMD, taking note of the resulting directory structure as follows

Export data can be executed under 2014-09-28_17-02-42 Unload_script.bat

My project exports around 10G about more than 10 minutes.

Next is the transcoding work, in the data directory, the selected file using EditPlus or UltraEdit can be used to transcode the file to UTF8 format.

(3) Import data

Upload all the files to the Linux server (your Oracle won't run under Windows?). ）

(1) Modify the shell file

Modify the oracle_ctl.sh file under the dbo file

Increase

Export Nls_lang=American_america. Al32utf8

Note that the value here is the NLS value of your destination Oracle and can be queried on its own

If your file has a large file similar to mine over 500M, modify the default SQLLDR statement

Default:

Sqlldr $1/$2 control=control/dbo_jzprod.uf_bud_payoutdetthird.ctl log=log/dbo_jzprod.uf_bud_ Payoutdetthird.log

Add the following statement in a parallel append way, skipping the index

direct= true parallel= true skip_index_maintenance=true

(2) Modify the control file

If you use the above direct=true data, the corresponding control file also needs to be modified, as the above Dbo_jzprod.uf_bud_payoutdetthird,control file in the control directory, such as:

' Data/dbo_jzprod. CARDCOMBINATIONDETAIL.dat'"str ' <EORD>'"'  <EOFD>'trailing nullcols

Need to add append before into

(3) Implementation

Execution of the shell, it may be very easy for everyone, but there are some things to note

./**.sh no problem, but we need to be aware that we need to execute the shell in the dbo directory

Because the default developer gives us a lot of layers of sh, we need to perform the most inner sh

3.1:^m's question:

We edit the SH, execution will error, VI will find that each line inside the last existence of a ^m

Use the following code.

:1, $ s/^m//G

^m Input Method: Ctrl + V, ctrl+m

3.2: Running in the background

It takes a long time to run the shell directly, and we use the nohup background process to run

Nohup./command. SH 2>&1 &

Note that this is the return exit until you exit the process, and then restart a new process

Tail–f output observations are actually progressing.

3.3 After the final export succeeds, the index needs to be rebuilt and migrated to a separate tablespace, with separate tablespace storage for LOB fields, etc.

Oracle SQL Developer:http://pan.baidu.com/s/1hq7oiug

Jtds:http://pan.baidu.com/s/1sjo7vop

SQL Migration to Oracle instance