Oracle DBMS_DATAPUMP and MySQL Integration with Amazon S3 - Oracle to Aurora MySQL Migration Playbook
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Oracle DBMS_DATAPUMP and MySQL Integration with Amazon S3

Feature compatibility Amazon SCT / Amazon DMS automation level Amazon SCT action code index Key differences

One star feature compatibility

No automation

N/A

No equivalent tool

Oracle Usage

The DBMS_DATAPUMP package provides Oracle Data Pump functionality that can be run within the database.

The DBMS_DATAPUMP package subprograms are:

  • ADD_FILE — Adds a relevant file to the dump file set.

  • ATTACH — Connects the DATAPUMP job.

  • DATA_FILTER — Filters rows.

  • DETACH — Disconnects from a DATAPUMP operation.

  • GET_DUMPFILE_INFO — Retrieves information about a specified dump file.

  • GET_STATUS — Retrieves status of the running DATAPUMP operation.

  • LOG_ENTRY — Writes a message into the log file.

  • METADATA_FILTER — Filters the items to be include in the operation.

  • METADATA_REMAP — Remaps the object to new names.

  • METADATA_TRANSFORM — Specifies transformations to be applied to objects.

  • OPEN — Declares a new job.

  • SET_PARALLEL — Set the parallelism of the job.

  • SET_PARAMETER — Specifies job processing options.

  • START_JOB — Runs a job.

  • STOP_JOB — Terminates a job.

  • WAIT_FOR_JOB — Runs a job until it either completes normally or stops.

Examples

The following example shows how to export the HR schema. It assumes all directories have already been created and the user has all required privileges.

DECLARE
loopidx NUMBER;
job_handle NUMBER;
percent_done NUMBER;
job_state VARCHAR2(30);
err ku$_LogEntry;
job_status ku$_JobStatus;
job_desc ku$_JobDesc;
obj_stat ku$_Status;
BEGIN

job_handle := DBMS_DATAPUMP.OPEN('EXPORT','SCHEMA',NULL,'EXP_SAMP','LATEST');

DBMS_DATAPUMP.ADD_FILE(job_handle,'hr.dmp','DMPDIR');

DBMS_DATAPUMP.METADATA_FILTER(job_handle,'SCHEMA_EXPR','IN (''HR'')');

DBMS_DATAPUMP.START_JOB(job_handle);

percent_done := 0;
job_state := 'UNDEFINED';
while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
dbms_datapump.get_status(job_handle,
dbms_datapump.ku$_status_job_error +
dbms_datapump.ku$_status_job_status +
dbms_datapump.ku$_status_wip,-1,job_state,obj_stat);
job_status := obj_stat.job_status;

/* HERE YOU CAN PRINT THE STATUS */

if job_status.percent_done != percent_done then
  percent_done := job_status.percent_done;
end if;

if (bitand(obj_stat.mask,dbms_datapump.ku$_status_wip) != 0) then
  err := obj_stat.wip;
else
  if (bitand(obj_stat.mask,dbms_datapump.ku$_status_job_error) != 0)
  then
    err := obj_stat.error;
    else
    err := null;
  end if;
end if;

if err is not null then
  loopidx := err.FIRST;
  while loopidx is not null loop
    loopidx := err.NEXT(loopidx);
  end loop;
end if;
end loop;

dbms_datapump.detach(job_handle);
END;
/

For more information, see Overview of Oracle Data Pump in the Oracle documentation.

MySQL Usage

There is no feature in MySQL fully equivalent to the Oracle DBMS_DATAPUMP package, but there are tools and features that achieve the same functionality.

To export data from the database to the file system, use the SELECT INTO OUTFILE S3 command. To import data from the filesystem, use the LOAD DATA FROM S3 command.

To achieve the most functionality, this feature can be mixed with metadata tables and events to handle the operations.

For more information, see Oracle External Tables and MySQL Integration with Amazon S3.

Summary

Feature Oracle DBMS_DATAPUMP Aurora integration with S3

Add a relevant file to the dump file set

ADD_FILE

Use metadata table

Connect the DATAPUMP job

ATTACH

Query session status

Filter rows to be handled

DATA_FILTER

Use WHERE clause in your SELECT

Disconnect from DATAPUMP operation

DETACH

Not required

Retrieve information about a specified dump file

GET_DUMPFILE_INFO

Use metadata table

Retrieve the status of the running DATAPUMP operation

GET_STATUS

Query session status

Write a message into the log file

LOG_ENTRY

Write to metadata tables

Filter the items included in the operation

METADATA_FILTER

Export the objects

Remap the object to new names

METADATA_REMAP

LOAD DATA INTO different table name

Specified transformations to be applied to objects

METADATA_TRANSFORM

Not required

Declare a new job

OPEN

Use LOAD DATA or SAVE OUTFILE

Set the parallelism of the job

SET_PARALLEL

Use parallel in your SELECT

Specify job-processing options

SET_PARAMETER

Not required

Run a job

START_JOB

Use LOAD DATA or SAVE OUTFILE

Terminate a job

STOP_JOB

Kill session

Run a job until it either completes normally or stops

WAIT_FOR_JOB

Use LOAD DATA or SAVE OUTFILE