Login

Migrating from Oracle 10G XE to SQL Anywhere 10

In a previous article, a set of tables from Oracle 10G XE were transferred to SQL 2005 Server using SQL 2005 Server’s Export/Import Wizard. This article is about migrating a table from Oracle 10G XE to SQL Anywhere 10.

In an earlier article migrating an MS Access database to the SQL Anywhere 9 server was considered, where the front end created on MS Access was retained. SQL Anywhere 10 is still in its beta and has more than 200 new features and enhancements.

Details of table migrated

The Employees table from the HR database in Oracle 10G XE whose details are shown in the next picture will be migrated to SQL Anywhere 10’s demonstration database, demo10. It will be helpful to review the articles on Oracle 10G XE as well as SQL Anywhere 10 (to appear on the ASP Free website soon) so that the skills necessary to work with these databases are acquired. The Employees table has 11 columns with 107 rows of data. While foreign keys can be migrated using the Migrate Database Wizard, they will not be in this exercise. This exercise steps through and documents the process of using the Migrate Database Wizard in SQL Anywhere 10.

{mospagebreak title=Initial steps to take before migration}

The migration is from Oracle 10G XE to SQL Anywhere 10; therefore, both databases must be installed and working. For both databases you need the credentials to access the databases and permissions to work with the objects. For Oracle 10G XE the HR database will be used with the credentials Username: hr, Password: hr. For the SQL Anywhere 10 database the demonstration database demo10 will be used with User ID: dba and Password: sql. The following two pictures show how you may start and interact with the databases. In order to access the Migrate Database Wizard in SQL Anywhere 10, you will also need to start Sybase Central, the administration tool.

Creating an ODBC source for Oracle 10G XE

The migration wizard requires ODBC access to the Oracle 10G XE server in order to migrate the table. The ODBC DSN needs to be created. The ODBC DSN can be created from the Windows operating system’s ODBC Manager. The next picture shows JayOra, a System DSN, created using the ODBC Manager. The ODBC source may also be created from within Sybase Central, or during the migration process.

The ODBC Oracle driver configuration is shown in the next picture. This needs to be correctly configured, otherwise the migration will fail. The Description item on this form refers to the machine on which the Oracle database is installed.

Assuming you have started the SQL Anywhere Network Server, you can connect to the demo10 database using Sybase Central. In Sybase Central click on the main menu item Connections, and from the drop-down click on Connect with SQL Anywhere 10. In the dialog that pops up, enter the credentials and click on the Database tab. In this tabbed form, click on the drop-down related to Server Name: and choose demo10. With this you will be connected to the demo10 database and be able to access all the objects as shown in the next picture.

Stepping through the Migrate Database Wizard

You can start the wizard from the main menu as shown in the next picture by following the trail and clicking on the hyperlink, Tools –>SQL Anywhere 10->Migrate database…. This opens up the dialog as shown in the next picture.

This Welcome window explains briefly the migration process which consists of setting up a remote server on SQL Anywhere to access the database in question, which can be from any one of several vendors. Clicking on the Next button opens up the next window, where a database has to be chosen as shown. As you can see, demo10 is the only database.

Clicking on the Next button takes you to the screen where you need to Select aRemote Server from which you will be migrating. There is already one remote server, which will not be used in this tutorial.

You also have the option of creating a new remote server. For this article a new one will be created by clicking on the button Create Remote Server Now…. This opens up the window shown in the next picture by calling up the Remote Server Wizard. The naming of the remote server is important. For this tutorial the name OracleXE has been chosen.

After choosing the name, click on the button Next to choose the type of server used. The table to be migrated will come from that remote server. Several choices are shown; Oracle has been chosen.

Click on the Next button in the above window to pop open the next step of this wizard, as shown in the picture. Here you must provide the connection information. There are two choices, ODBC or JDBC. The default ODBC is the appropriate selection for the ODBC Connection that will be used. In the connection information textbox, type in the ODBC DSN created earlier, namely, JayOra.

{mospagebreak title=Continuing the migration}

When you click on the Next button you have the option of making it read only or updatable. For this exercise, the read only option is chosen as shown in the next picture.

Clicking on the Next button opens the window where you need to provide an external login for the current user (dba, sql). This is where you type in the Oracle 10g XE credentials. You can also test the connection, which succeeded in this example, as you can see.

Clicking on the Finish button will take you back to an earlier step in the Migrate Database Wizard. But this time your remote server has joined the collection of remote servers.

Highlight the new remote server OracleXE, and click on the Next button. This is the step where the tables to be migrated must be chosen. You can see all the Oracle tables in the Available Tables area. You can add and remove all the tables to the right by clicking the buttons between the left and right text areas. You may also select a single table and move it to the right, as shown in the picture for the Employees table.

When you click the Next button you will be taken to a window where you need to provide an owner for this table. In this case, DBA has been chosen as shown.

Clicking on the Next button in the above screen will lead you to the step where some migration options need to be chosen. You can migrate data as in this case, or migrate both data and foreign keys, as well as create proxy tables. The options chosen are shown in the picture.

{mospagebreak title=Finishing the migration}

When you hit the Finish button, the program goes through the migration process, and, depending on the amount of data, you will be presented with a message confirming success, or otherwise, as shown. Here the migration of the Employees table was successful.

The next picture shows the employees table that was migrated recently. There is another pre-existing Employees table owned by another user.

The next picture shows the details of the migrated table, which may now be compared with the original Oracle 10G XE’s table. As you can see, the Employees table was migrated with all the data intact. The data type, scale, and size were all migrated correctly as well.

The following picture shows a small portion of the data from the migrated table.

Summary

The migration of the table from Oracle 10G XE to SQL Anywhere 10 was effortless and easy once the connection was correctly configured. The wizard provides a lot of guidance during migration, which is very helpful. During the migration process you have the option of creating a new owner for the migrated table. You may also create a new remote server, including an ODBC DSN for the remote server. The wizard caters to all well established database vendors; it also supports a generic server for which an ODBC driver is available. The author would like to thank David Jonker, Product Manager at Sybase iAnywhere, for helpful suggestions in the preparation of this article.