Simple Oracle Database Migration
with dbForge Tools

Oracle database migration methods and tools

The choice of both a method and a tool to migrate an Oracle database depends on the task you are facing: whether you need to transfer a database from one server to another, or move to another platform or server version.

There are quite many tools for database migration — both specialized standalone tools designed for this specific operation and complex universal IDEs that boast a set of useful features, including features that can be used when migrating an Oracle database.

In this article, we will demonstrate to you how to migrate an Oracle database with the help of the best Oracle IDE on the market today — dbForge Studio for Oracle. The solution supports Oracle versions 8i, 9i, 10g, 11g, 12c, 18c, 19c, and 21c.

dbForge Studio for Oracle allows migrating a database by using the following functionality:

• Schema Compare/Data Compare
• Schema Export/Schema Import
• Export Utility/Import Utility

How to copy Oracle database from one server to another

When working with Oracle, the copy database tasks are common. You might need to migrate Oracle database from one server to another or create a copy of the database on the same server under another name for various reasons.

You can easily complete such tasks using the Schema Compare/Data Compare functionality that comes with dbForge Studio for Oracle. Let's look at how you can do this in a real-world example. Suppose, we want to transfer the SandersJ database that resides on one Oracle server to another. Below are the steps showing how you can migrate a database in Oracle.

Step 1: Create an empty schema on the target server

There are several ways to create an Oracle schema, however, we will not describe this step in detail as it is not the topic of this particular article.

Step 2: Synchronize schemas

The idea behind this step is to use the Oracle Schema Compare tool that comes in dbForge Studio for Oracle to synchronize schemas between our empty target database and the source one, Performing this step we essentially copy Oracle schema from one database to another.

In Database Explorer, right-click the source database and go Tools > New Schema Comparison.

In the New Schema Comparison wizard that opens, select the source and target connections, specify the names of the databases to be compared and proceed to configure all the necessary options. Once done, click Compare.

You will be transferred to the schema comparison results page where you can select source objects to be moved to the target database. Click the green arrow button to synchronize databases.

In the Schema Synchronization Wizard that opens, follow the wizard's pages to configure the synchronization options. Click Synchronize to complete the process. After that, the schemas in the two databases will be identical, and you can proceed to the next step — migrating Oracle data.

Step 3: Synchronize data

The final step is to synchronize data between our source and databases by using the Data Compare tool of the Studio. The algorithm behind it is pretty much similar to Schema Compare. First, you need to open Data Comparison Wizard by right-clicking the source database and selecting Tools > New Data Comparison

Next, in the wizard that opens, configure data comparison settings and click Compare.

The data comparison results grid will be displayed almost instantly. In it, select the data you want to migrate to a new database and click the green arrow button to synchronize databases.

In the Data Synchronization Wizard that opens, configure the data synchronization options and click Synchronize. After that, your databases will be identical in terms of both schemas and data.

Export and import Oracle schema

In dbForge Studio for Oracle, to migrate an Oracle database from one server to another you can also use the Import/Export Schema in Oracle functionality. You can also use this functionality for the backup and restore purposes. Let's look at how you can transfer an Oracle database from one server to another in a simple example. Suppose, we again want to transfer the JordanS database that resides on one Oracle server to another.

Step 1: Export Schema

In Database Explorer, right-click the source schema (the one you want to export). In the context menu that appears, select Export & Import > Schema Export.

In the Schema Export Wizard that opens, you need to configure the schema export options and click Export. The wizard's pages will conveniently walk you through the process. Note, that you can select what to copy: database structure, data, or both. Also, you do not have to copy the database in its entirety — you can select the objects to be transferred.

Step 2: Create an empty schema on a new server

On the target server, create an empty schema with the same name as the source database.

Step 3: Import Schema

Under a new connection in Database Explorer, right-click the target schema (the one you've created in the previous step). In the context menu that appears, select Export & Import > Schema Import.

In the Schema Import Wizard that opens, you need to configure the schema import options and click Import.

After that, you will have a copy of the source Oracle database on another server.

Database migration using Import/Export functions

And the third way you can migrate an Oracle database using dbForge Studio for Oracle is via Export and Import Utilities that allow exporting and importing database objects in a matter of seconds. Let's walk through the simple steps of copying an Oracle database.

Step 1. Export the source database data and objects

In Database Explorer, right-click the source database and navigate Export & Import > Schema Utility. Next, in the Export Utility wizard that opens, choose the export mode and click Next. We select to export the entire user's data. On the next page of the wizard, select the user to be exported and click Next.

On the next two pages of the wizard, you need to select the database object for export, configure additional export settings, and provide a path to the output and configuration files. Click Export to finish.

Step 2. Import data to the target database

In this step, we are going to use the Import Utility to import the exported data to a new database, that can reside on the same or another server. In Database Explorer, right-click the target database and navigate Export & Import > Import Utility. In the Import Utility wizard that opens, specify a path to the output and configuration files you got in the previous step and click Next. Then, select the import mode and again click Next.

On the next page of the wizard, use the drop-down list to select the user to import Oracle data to and click Next to proceed to the final page of the wizard. Here, you need to set additional import options and click Import to complete the process.

Oracle database migration with dbForge checklist

dbForge Studio can become your reliable assistant in any database migrations, even the cross-platform ones. With the help of Oracle database migration tools that the Studio offers and dedicated ODBC drivers, you can easily perform Oracle to Postgres migration as well as MySQL to Oracle migration.

To top it all off, let's look at the Oracle database migration checklist, covering all the necessary issues you need to take into account in your database migration strategy.

• Prepare thoroughly for the migration
• Rehearse your migration
• Secure your databases
• Test everything
• Assess the results