Fast & Furious Oracle Data Pump: expdp and impdp Directly to OCI Object Storage

Oracle Data Pump does not require dump files to be stored on the database server.

Using DBMS_DATAPUMP, we can export schemas directly from an Oracle database to OCI Object Storage and import them into another database from the same bucket.

The complete flow is:

Source Database
      ↓
OCI Object Storage
      ↓
Target Database

For this example, we will migrate two schemas:

SCHEMA_A
SCHEMA_B

Contents

• 1. Prepare an OCI Object Storage bucket
• 2. Create an Object Storage credential
• 3. Export schemas directly to Object Storage
• 4. Check the export job
• 5. Import directly from Object Storage
• 6. Check the import
• Complete workflow

1. Prepare an OCI Object Storage bucket

First, create an OCI Object Storage bucket that will store the Data Pump dump files.

For example:

oracle-datapump

The database must be able to access this bucket.

2. Create an Object Storage credential

Create a credential in the database:

BEGIN
    DBMS_CLOUD.CREATE_CREDENTIAL(
        credential_name => 'DATAPUMP_CRED',
        username        => '<OCI_USERNAME>',
        password        => '<OCI_AUTH_TOKEN>'
    );
END;
/

The same credential can be used for both export and import.

For export it needs permission to write objects into the bucket.

For import it needs permission to read them.

3. Export schemas directly to Object Storage

The export can be started directly from PL/SQL:

DECLARE
    l_handle NUMBER;
BEGIN
    l_handle := DBMS_DATAPUMP.OPEN(
        operation => 'EXPORT',
        job_mode  => 'SCHEMA',
        job_name  => 'EXP_SCHEMA_A_B'
    );

    DBMS_DATAPUMP.ADD_FILE(
        handle    => l_handle,
        filename  =>
            'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/<namespace>/b/oracle-datapump/o/schema_export_%L.dmp',
        directory => 'DATAPUMP_CRED',
        filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_URIDUMP_FILE
    );

    DBMS_DATAPUMP.METADATA_FILTER(
        handle => l_handle,
        name   => 'SCHEMA_EXPR',
        value  => 'IN (''SCHEMA_A'', ''SCHEMA_B'')'
    );

    DBMS_DATAPUMP.START_JOB(l_handle);

    DBMS_DATAPUMP.DETACH(l_handle);
END;
/

The important part is:

filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_URIDUMP_FILE

This tells Data Pump that the dump file is stored remotely using an Object Storage URI.

In this case:

directory => 'DATAPUMP_CRED'

does not refer to an Oracle DIRECTORY object.

It contains the name of the Object Storage credential.

4. Check the export job

The Data Pump job can be checked using:

SELECT owner_name,
       job_name,
       operation,
       job_mode,
       state,
       degree
FROM dba_datapump_jobs
WHERE job_name = 'EXP_SCHEMA_A_B';

While running, the job will normally be in:

EXECUTING

After the export finishes, the dump files are available directly in the OCI Object Storage bucket.

5. Import directly from Object Storage

The target database can use the same approach for the import.

First, create DATAPUMP_CRED on the target database as well.

Then start the import:

DECLARE
    l_handle NUMBER;
BEGIN
    l_handle := DBMS_DATAPUMP.OPEN(
        operation => 'IMPORT',
        job_mode  => 'SCHEMA',
        job_name  => 'IMP_SCHEMA_A_B'
    );

    DBMS_DATAPUMP.ADD_FILE(
        handle    => l_handle,
        filename  =>
            'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/<namespace>/b/oracle-datapump/o/schema_export_%L.dmp',
        directory => 'DATAPUMP_CRED',
        filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_URIDUMP_FILE
    );

    DBMS_DATAPUMP.START_JOB(l_handle);

    DBMS_DATAPUMP.DETACH(l_handle);
END;
/

The target database now reads the Data Pump dump set directly from OCI Object Storage.

No dump file needs to be manually copied to the database server.

6. Check the import

The import can again be monitored through:

SELECT owner_name,
       job_name,
       operation,
       job_mode,
       state,
       degree
FROM dba_datapump_jobs
WHERE job_name = 'IMP_SCHEMA_A_B';

Complete workflow

The whole migration is therefore very simple:

1. Create OCI Object Storage bucket

2. Create DATAPUMP_CRED on the source database

3. Run DBMS_DATAPUMP EXPORT

4. Dump files are written directly to Object Storage

5. Create DATAPUMP_CRED on the target database

6. Run DBMS_DATAPUMP IMPORT

7. Target database reads the dump directly from Object Storage

The key components are only:

OCI Object Storage bucket
DBMS_CLOUD credential
DBMS_DATAPUMP
KU$_FILE_TYPE_URIDUMP_FILE

For OCI-to-OCI database migrations, this is a fast and clean way to move schemas without managing dump files on database server filesystems.