Porting a SQL SERVER 2008 database to Oracle 11g using Oracle SQL Developer

Oracle's official SQL Developer comes with Oracle migration Workbench.

What is Oracle SQL Developer? On the official page, this is how it is presented:

Oracle SQL Developer is a free and fully supported graphical tool for database development. With SQL Developer, you can browse database objects, run SQL statements and SQL scripts, and edit and debug PL + SQL Stateme Nts. You can also run any number of provided reports, as well as create and save your own. SQL Developer enhances productivity and simplifies your database development tasks.

In addition, through a third-party driver package, the tool also supports the connection and management of various major database servers.

The following is the process of migrating a Microsoft SQL Server 2008 database to Oracle 11G using this tool to record and share for the sake of accumulation.

Part I: Getting tools

Part II: Establishment of a data archive (migration Repository)

Part III: Database Migration Wizard

Part IV: Schema in SQL Server to schemas in Oracle, name processing

Part V: Transferring data

Part VI: Stored Procedures and functions

First, of course, the tools are available, which can be downloaded directly to the Oracle SQL Developer Official download page. Windows 32-bit Direct Connect address: Windows 32bit Sql Developer. Oracle SQL Developer requires JDK support, download packages with JDK included on the download page, and download options to support various operating systems.

After downloading, unzip to any location. Run the program directly. The first time you run, you need to specify the directory for the JDK.

After starting the first thing we're going to do, don't do anything else, download the SQL Server driver first. Click menu Help, select Check Update, pop Up Check Update wizard window, the first page is generally nonsense, direct next, and so on after the update center list is loaded, only select "Third party SQL Developer extensions", click Next, wait for the update search finished, In the list, locate and select the Jtds JDBC Driver, such as:

Next, on the License Agreement page, click on the "I Agree" button and then point to the next, and so on after the download is finished, close the window, pop-up prompts need to restart the program to complete the update, click "Yes". After the program restarts.

When the environment is ready, start to get to the topic transplant database.

Establishment of a repository (migration Repository)

One, connect to Oracle

In the connection window to the left of the program, click the Plus button to add a DBA connection to the Oracle database, such as:

After the configuration is complete, click the Connect button to connect to the database.

Second, establish the user

Open the connection to Oracle data, create a new user, we want to create a so-called "repository" in that user's schema, and use that user to build the SQL Server ported username and do some other work. As described in Help, this user requires at least the following permissions and roles:

Roles
CONNECT with admin option,resource with admin OPTION

Privileges
ALTER any role,alter any sequence,alter any table,alter tablespace,alter any trigger,comment any table,create any SEQUENCE , CREATE any table,create any trigger,create VIEW with admin option,create public synonym with admin option,create ROLE
CREATE User,drop any sequence,drop any table,drop any trigger,drop user,drop any role,grant any role,insert any Table,sele CT any table,update any TABLE

The following statement directly establishes a user named migrations:

--Create the user
Create user Migrations

Identified by migrations
Default Tablespace USERS
Temporary tablespace TEMP
Profile DEFAULT;
--Grant/revoke role privileges
Grant connect to migrations with admin option;
Grant resource to migrations with admin option;
--Grant/revoke System privileges
Grant alter any role to migrations;
Grant ALTER any sequence to migrations;
Grant alter any table to migrations;
Grant ALTER any trigger to migrations;
Grant alter tablespace to migrations;
Grant Comment any table to migrations;
Grant create any sequence to migrations;
Grant create any table to migrations;
Grant create any trigger to migrations;
Grant create any view to migrations;
Grant create materialized view to migrations with admin option;
Grant create public synonym to migrations with admin option;
Grant create role to migrations;
Grant create session to migrations with admin option;
Grant create synonym to migrations with admin option;
Grant create tablespace to migrations;
Grant create user to migrations;
Grant CREATE view to migrations with admin option;
Grant drop any role to migrations;
Grant drop any sequence to migrations;
Grant drop any table to migrations;
Grant drop any trigger to migrations;
Grant drop tablespace to migrations;
Grant drop user to migrations;
Grant grant any role to migrations;
Grant insert any table to migrations;
Grant Select any table to migrations;
Grant unlimited tablespace to migrations with admin option;
Grant update any table to migrations;

Click the Plus button in the connection again to add a connection that uses the newly created user.

After the connection, right-click on the connection, select the migration repository-associated with the Migration repository, the program will be under the user to create the migration repository required tables, stored procedures and so on, pop up a dialog box to show the progress of the current establishment, wait a moment is established.

Database Migration Wizard

One, establish a connection to the source SQL Server database

In the Connection window, click the Green Plus button to open the Connection Configuration window and press the connection established to SQL Server:

Because of the use of dynamic products, so that the original SQL Server Configuration page default 1433 is often no way to connect to the SQLSERVER2008 database, modified to 1434. ----------------------------this place. You must set the port to 1433 to guide the data, otherwise only the table will not be able to guide the data

Second, start the Database Migration Wizard

After you establish a connection to SQL Server, you begin porting the database. There are two places to start the Database Migration Wizard, one is to right-click on the new SQL Server connection, choose to migrate to Oracle, the other is in the menu tool-porting-porting, starting the wizard, the first page of course is the introduction of the wizard, through the introduction, we can know that the entire migration process has 7 steps, And there are two prerequisites, such as:

Click Next directly.

Iii. Selecting a data archive

On this page, we can also build connections and repositories, which is what we do in the previous section. Of course Choose the migrations Connection we just established, such as:

The truncation here means that the repository is emptied, and after we have established a migration project, all of the database structure information, statistics, and conversion records are stored in this repository, so long as the project name is different, the porting work will not be affected even if the repository is not emptied. Of course, if you want to be more "refreshing", you can check it. This is something.

Iv. Naming the Transformation project

After selecting the repository, click Next to enter a name and a comment for our conversion project in order to identify it. In addition, you need to specify a script generation directory to hold the scripts generated during the wizard's execution.

V. Select the source database connection

Then proceed to the next step, select the source database, there are online and offline two modes, online is directly connected to the source data to fetch, while offline is sqldeveloper not directly connected to the source database, the sqldeveloper provided by the script to pre-capture the database, After you get the database structure file, import it through the wizard.

This is of course choosing the SQL Server Connection we just established.

Vi. Select Capture Source database

In this step, the two multi-box is displayed, and all the optional databases in SQL Server are listed on the left, and the database is moved to the selected database on the right by the middle button. Such as:

Vii. Data type conversion options

On this page, the data type conversion correspondence that is built into all systems is listed. Can be modified, generally do not make changes directly to the next step. Such as:

Viii. Selecting the target database

As with the selection of the source database, the selection of the target database also has two ways of online and offline. Still select online. Select the ORCL (migrations) connection.

IX. Mobile Data Options

This page also provides two ways to move data online or offline. The so-called online although "supposedly" can move data from the source database directly online to Oracle, but I tried several times did not succeed, the reason is unclear. Finally I chose the offline way to move the data. The so-called off-line mobile data is generated in the script directory we specify to generate a "data movement" script to unload the data in the source database (that is, export) and load the data in the target database. Select OK, and then click Next.

Ten, the option is complete, start execution

At the end of the wizard, there is a "summary" page for confirmation. When you are sure, click Finish. The migration will begin execution.

Schemas in SQL Server to schema in Oracle, name processing

By performing the Migration Wizard, if there is no error, then the table structure will basically be established, in the connection to open the ORCL (migrations) connection, find the bottom of the other users, assuming that your table is in the DBO schema, then find the Dbo_ source database name, expand it, You can see that all the tables in the original database are already in the table. But why does it appear as a dbo_ source database name? I don't like this kind of user name very much. I want it to be displayed as the source database name, how do I do it?

In the lower left corner of the program, the Porting Project pane, where you can see the name of the transplant project that we just created. Expand it to show the date and time we performed the wizard, continue to expand, and see two nodes, called the captured database object and the converted database object, such as:

Right-click on the captured Database object, select Convert, and open the Data Migration Wizard again. Click Next to jump directly to the conversion page, this time the conversion page is slightly different from the previous part of the conversion page. The difference is that there is an extra tab, called the object name, select it to open, as shown in:

In this page, a list of data is displayed that lists the source database's table name, field name, constraint name, view name, schema name, and all identifiers such as the original identifier, the new identifier, whether it was converted, the original qualified name, the identifier type, and so on. Because the 30-character identifier is not supported in Oracle, the Migration Wizard automatically truncates the original identifier when it encounters more than 30 characters, and adds a suffix such as _1 if there is a duplicate name.

Obviously, here, it transforms our original dbo schema name into the Dbo_ database name, and we just need to find it and change the new identifier to the data name to achieve this part of our requirements.

Once the modifications are complete, click Next, then complete the wizard with the previous settings and submit the changes to the repository. Note that this time the wizard did not help me to establish new user and table structure and so on, but also need further action.

In the Migration Project pane of the main interface, right-click on the converted database object, select Build target, open the Migration Wizard again, and once again press our selection to complete the wizard, this time, is really to build the user name we want.

Then, the auto-generated old user name and mode are deleted. Do not elaborate on the specific operation.

Transfer data

The previous section said that the Migration Wizard automatically generated the table structure we needed in the Oracle database, but it didn't help me to successfully import the data into the past. So we use the way to generate offline data movement scripts to guide the data.

In the project page of the Migration Wizard, we select the directory that the script generates, open the directory, and you can see a subdirectory called Data movement \ Project name \ DateTime \, which contains the file as shown:

Look to see that it contains 4 script files, two for Windows, and two for *nix. The data export script named Unload_script is used to generate a data export file from the source database, with the data import script named Oracle_ctl.

Open cmd, browse to the current directory, and execute the following command to export the data:

>unload_script [Server] [username] [Password]

Where the server is the source database server, username and password are the user name passwords that can access the source database. Wait a moment. After the export is complete, execute the following command to import the data:

>oracle_ctl [oracleusername] [password]

You do not need to specify the database name here, because it is already specified in the script.

Wait a moment after the import is complete. It's pretty simple. Do not understand why online is not successful, do not do the same job.

Porting a SQL SERVER 2008 database to Oracle 11g using Oracle SQL Developer