Full load Oracle database migration

The full load phase populates the target database with a copy of the source. This chapter describes the following methods to help you choose the one that best matches your migration scenario.

We recommend that you begin by reviewing the following table to understand the tools suitable for your use case.

This document doesn’t discuss Oracle external tables because their use is similar to Oracle Data Pump. AWS DMS recommends using Oracle Data Pump instead.

Oracle Data Guard and RMAN are the excellent options to migrate an Oracle instance into EC2, however, Amazon RDS doesn’t support these options.

You can use the Amazon DMS Sample Database for Oracle: version 1.0 to run the following migration examples.

The Performance Comparison section to know the performance of these native tools.

The AWS DMS ongoing replication task requires a start position such as system change number (SCN) from the source database. AWS DMS ongoing replication task replicates changes from a position that you specify during the task configuration. If all exported objects are consistent as of same SCN or if there is no data modification after this SCN, we call it as consistent full load.

To avoid data loss and/or duplication with hybrid approach, make sure of the following

To capture the SCN, use the following command before you start the full load.

select current_scn from v$database;

Oracle Export/Import

Oracle Export/Import is a native database migration tool set that is provided as part of the database installation. Oracle replaced Export/Import with Oracle Data Pump, but many DBAs are familiar with Export/Import because of its long history and usage.

Export/Import may be suitable for your use case if:

Export/Import may not be suitable for your use case if:

One limitation of Export/Import is that it performs a serial migration. If you have a large data volume and/or large objects like LOB or CLOB values, then using Export/Import may be slower than the other options.

Example

You use Oracle exp to unload the source database into a dump file, and Oracle imp to load the dump file into the target database. You can run exp and imp on the same host to avoid copying the dump file between database servers.

For example, you can perform the following steps on the source database to migrate the dms_sample schema using exp and imp. The schema includes tables, views, indexes, packages, stored procedures, and other database objects.

First, capture the current SCN on the source database. Use this value to get a consistent image in the database export.

select current_scn from v$database;

CURRENT_SCN
86409924

Then, export the dms_sample schema using the exp utility. Set flashback_scn to the SCN value that you obtained in the previous step to get a consistent export.

exp <user>/<password> file=export_file.dmp owner=dms_sample log=explog_file.log flashback_scn=86409924 statistics=none

Finally, import the export_file.dmp dump file into the target Amazon RDS database

imp <user>/<password>@targetdb fromuser=dms_sample touser=dms_sample file=export_file.dmp log=implog_file.log

This command imports the dump file into the target dms_user schema.

Oracle Data Pump

Oracle Data Pump is a native database migration tool set that is provided as part of the database installation. Such as exp and imp, Oracle Data Pump has separate utilities for export and import. These utilities are expdp and impdp. You can use Oracle Data Pump to migrate data and database objects such as tables, indexes, views, procedure, packages, and functions, and so on.

Oracle Data Pump may be suitable for your use case if:

Oracle Data Pump may not be suitable for your use case if you have a small database, which is less than 10 GB. Though Oracle Data Pump works well for databases of any size, for smaller data sets you have many native tools available as well.

Example

At a high level, we can use the following steps to migrate the dms_sample database.

Export data

To perform a consistent export, make sure that you use the flashback_scn parameter in expdp. Run the following commands on the source database to get the current SCN, and create a database directory to store the dump and log files.

SQL> select current_scn from v$database;

CURRENT_SCN
60045321

SQL> create directory expdp_sample_bkp as '/u001/oraarch';
Directory created.

Now, use the 60045321 SCN and the expdp_sample_bkp directory in the following command to dump the dms_sample schema.

expdp userid=<user>/<password> directory=expdp_sample_bkp dumpfile=exp_dms_sample.dmp logfile=exp_dms_sample.log schemas=dms_sample flashback_scn=60045321

Move the dump file or files

After you export your data, you have the exp_dms_sample.dmp dump file. You can move this file to Amazon RDS using different methods. In this example, we use the Amazon RDS and Amazon S3 integration feature to transfer the dump file to the Amazon RDS instance. For more information, see Amazon S3 integration in the Amazon Relational Database Service User Guide.

The first step is to copy the dump file into the Amazon S3 bucket in the same account and region as the target database.

aws s3 cp exp_dms_sample.dmp s3://YourBucket

Now, use the rdsadmin_s3_tasks package to copy the file from Amazon S3 to your Amazon RDS instance. The file will be copied to the DATA_PUMP_DIR directory which was created as part of your Amazon RDS instance.

select rdsadmin.rdsadmin_s3_tasks.download_from_s3(
    p_bucket_name => 'YourBucket',
    p_directory_name => 'DATA_PUMP_DIR',p_s3_prefix => 'exp%')
as task_id from dual;

The preceding statement returns the ID of the task. You can view the result by displaying the task’s output file.

SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('BDUMP','dbtask-*task-id*.log'));

Replace task-id with the task ID returned by the download_from_s3.

Import the dump file or files

You can use the following command to import the dump file into your Amazon RDS instance.

DECLARE
  v_hdnl NUMBER;
BEGIN
  v_hdnl := DBMS_DATAPUMP.OPEN( operation => 'IMPORT', job_mode  => 'SCHEMA', job_name  => null);
  DBMS_DATAPUMP.ADD_FILE(
    handle    => v_hdnl,
    filename  => 'exp_dms_sample.dmp',
    directory => 'DATA_PUMP_DIR',
    filetype  => dbms_datapump.ku$_file_type_dump_file);
  DBMS_DATAPUMP.ADD_FILE(
    handle    => v_hdnl,
    filename  => 'sample_imp.log',
    directory => 'DATA_PUMP_DIR',
    filetype  => dbms_datapump.ku$_file_type_log_file);
  DBMS_DATAPUMP.METADATA_FILTER(v_hdnl,'SCHEMA_EXPR','IN (''DMS_SAMPLE'')');
  DBMS_DATAPUMP.START_JOB(v_hdnl);
END;

Database Link

In Oracle, a database link enables you to access objects in another database. To migrate tables using a database link, you first create the database link and then run the insert and select statements for individual tables.

Using a database link may suit your use case if:

Database link may not suit your use case if:

Example

The following example migrates the sporting_event_ticket table from the dms_sample schema.

First, create a database link to the source on the target database.

SQL>create database link rdsmigration_link
 CONNECT TO <user> identified by <password>
 USING '(description=(address=(protocol=tcp) (host=<Self Managed Database Hostname>)
 (port=<Listener Port number>)) (connect_data=(sid=<sourcedb sid>)))';

Use the following query to verify that the database link works correctly.

SQL> select sysdate from dual@rdsmigration_link;

Run the following query on the source to generate the DDL statement for the table. You can use the dbms_metadata system package to extract the DDL. After generating the DDL, create the table on the target database.

select dbms_metadata.get_ddl('TABLE','SPORTING_EVENT_TICKET') from dual;

Run the following query on target database to transfer data using the insert and select statements.

insert into dms_sample.sporting_event_ticket select * from sporting_event_ticket@rdsmigration_link;
Commit;

Oracle SQL*Loader

Oracle SQL*Loader or sqlldr is a native database utility. Oracle provides this utility as part of the Oracle installation. Oracle SQL*Loader loads data from flat files into an Oracle database.

Oracle SQL*Loader may be suitable for your use case if:

Oracle SQL*Loader may not be suitable for your use case if:

Example

The migration process includes the following steps.

The following example migrates the sporting_event_ticket table from the dms_sample schema.

Export the table data and create configuration files

Use the SQL Developer to connect to your source database. Choose Tools, and then choose Database Export to open the Export Wizard. Choose the following option to generate the data file, control file, and table DDL for the target database.

To export the sporting_event_ticket table, open the Types of Export page and do the following:

This step exports the sporting_event_ticket data, generates the control file, and the table DDL in separate operating system files.

Create the table on the target database

On the target Amazon RDS database, run the DDL script that was generated by Oracle SQL Developer to create the table.

Load the exported files into the target database

Use SQL*Loader from same host to import data. If required, you can copy the dump files to an Amazon EC2 instance to perform the import.

sqlldr userid=<user>/<password>@targetdb control=sporting_event_ticket.ctl
  log=load.log bad=load.bad discard=load.dsc direct=y skip_index_maintenance=true

This example uses Oracle SQL Developer to generate the configuration file. If you prefer, you can use SQL*Plus to generate the configuration file. For more information, see Oracle SQL*Loader in the Amazon Relational Database Service User Guide.

Oracle SQL Developer Database Copy

Oracle SQL Developer is a graphical SQL client available from Oracle and can be installed on Windows, Linux, or macOS. You can use the Database Copy option in SQL Developer to transfer the data from one database to another; you can choose to copy individual objects or an entire schema.

Oracle SQL Developer database copy may be suitable for your use case if:

Example

Use the following steps to migrate the sporting_event_ticket table to the target database.

Oracle Materialized Views

A materialized view is an object that contains the results of a query. You can use Oracle materialized views to migrate data over a database link. With Oracle materialized views, you can perform a full load migration and keep your target tables continuously in-sync with the source.

Oracle materialized views may be suitable for your use case if:

Oracle materialized views may not be suitable for your use case if You need to migrate objects other than table data.

Example

The following example shows how to migrate the sporting_event_ticket table.

On the target Amazon RDS instance, create a database link to the source database.

create database link rdsmigration_link
CONNECT TO <user> identified by <password>
USING '(description=(address=(protocol=tcp) (host=<Self Managed Database Hostname>)
(port=<Listener Port number>)) (connect_data=(sid=<sourcedb sid>)))';

Test the database link to make sure you can access source database.

Select sysdate from dual@rdsmigration_link;

On source database, create a materialized view log.

create materialized view log on sporting_event_ticket;

On the target Amazon RDS instance, create a materialized view.

create materialized view sporting_event_ticket
  build immediate refresh fast
  as (select *
      from  dms_sample.sporting_event_ticket@rdsmigration);

When you’re ready to switch to the new database, drop the materialized view using the PRESERVE TABLE clause to retain the underlying table and its contents.

drop materialized view sporting_event_ticket preserve table;

For more information, see CREATE MATERIALIZED VIEW in the Oracle documentation.