Migrating from SQL to Oracle

A database migration is the process of moving from one database to another. This can
be done for different reasons:

• SQL is falling out of use, and more people are switching over to Oracle.
• Newer databases are faster and can be more readily adapted to new technologies.
• Some organizations are simply looking for a change in database provider.

Steps to follow

There are a few steps that need to be followed when migrating from SQL to Oracle.
First, check for any functions that might not work. For example, if there are any DATE or
DATETIME functions, they will not work. Next, make sure you can access the database.
If you cannot access the database, then it is likely that there is a lock on the table or
column and your migration may fail. This can be done by simply logging into your SQL
database as an administrator and looking at active lock rows in the table. Check for any
indexes that might have been created when doing a data import in SQL, but aren’t
present in Oracle or are missing entirely from Oracle. This can be done by simply
executing “alter table drop index ” and checking for the resulting rows.

There are some points to consider when making the switch:

1) Back-up the Oracle database if it is less than a year old.

2) If you are moving to Oracle from an SQL Server, export your data using sqlcmd or
other tool that provides support for exporting tables and columns from SQL Server

3) In order to import the data into Oracle, use either Truncate or Drop and insert Then
check your import log file. for any errors.

4) Set up Data Guard for the database you are migrating to Oracle from.

5) If you are moving to Oracle from a non-Oracle database, be sure your vendor has
exported the data and then import it into Oracle.

6) Create a new database on instance level (DB01 in this example), set it as the target
location of your migration, and then use RMAN to start an export and import that is
larger than what would fit on a single instance.

7) If you’re migrating to Oracle from a database on a different server, use RMAN to
complete the migration.

Additional Notes:

1) The most straightforward way of doing this is to back-up your entire database and
then restoring the backup into a new instance of your target Oracle Database. There are
numerous ways you can do this, but a GUID will be required for each restore and export
(as it would need to be unique). You can also use truncate and drop to create the new
tables and then restore the backup.

2) You can use a tool like Import/Export which will generate a new GUID at the end of
each backup. This is necessary in order to get full compatibility between backups (a
problem that may arise if you have not modified your source database in any way since
it was exported). Truncate and Drop will create the new tables. and then restore the
backup. The table to be dropped must already exist in the target database you are
restoring. For instance, if you’re restoring into a new database, drop any tables you
want to create in the restored database and make sure that those tables don’t have any
data at all in them when you restore your backup.

This is a scenario where truncate and drop might be helpful: You want to deploy an
application with some pre-existing data, but not enough to have to recreate tables. You
make an export of the database, truncate and drop some tables in that export, then
import it into a new database.

Conclusion

Are you looking to shift to Oracle for your enterprise? Get in touch with our team today.