📋Steps in this guide1/5

Oracle Cloud : Autonomous Database (ADW or ATP) - Export Data to an Object Store (expdp)

This article demonstrates how to export data from an Autonomous Data Warehouse (ADW) or Autonomous Transaction Processing (ATP) service on the Oracle Cloud using the expdp utility.

oracle 21cconfigurationintermediate

by OracleDba

17 views

Assumptions

For the export to work you will have to make a connection from an Oracle client to the ADW or ATP database. You can see the necessary setup to do this here . The Oracle 18c utility introduced the parameter to specify the object store credential to be used for an import. From Oracle 21c (21.3) we can also use the parameter with the utility. We need an object store bucket to export the data to. The export is only supported to an Oracle Cloud Object Storage bucket, but the import also works from an AWS S3 bucket and Azure Blob Storage. The following article describes how to create an Oracle Cloud Object Storage bucket. - Oracle Cloud Infrastructure (OCI) : Create an Object Storage Bucket

Create Something to Export

We connect to an autonomous database and create a new test user. We create a test table which we will export.

Code/Command (click line numbers to comment):

conn admin/MyPassword123@obatp_high

create user testuser1 identified by "MyPassword123";
alter user testuser1 quota unlimited on data;
grant create session to testuser1;
grant dwrole to testuser1;

create table testuser1.t1 as
select level as id,
       'Description for ' || level as description
from   dual
connect by level <= 1000;
commit;

Object Store Credentials

Create a credential for your object store. For an Oracle object storage bucket we use our Oracle Cloud email and the Auth Token we generated. For AWS buckets we use our AWS access key and secret access key. Remember, this is only supported for imports.

Code/Command (click line numbers to comment):

conn admin/MyPassword123@obatp_high

begin
  dbms_cloud.drop_credential(credential_name => 'obj_store_cred');
end;
/

begin
  dbms_cloud.create_credential (
    credential_name => 'obj_store_cred',
    username        => '[email protected]',
    password        => '{my-Auth-Token}'
  ) ;
end;
/

begin
  dbms_cloud.create_credential (
    credential_name => 'obj_store_cred',
    username        => 'my AWS access key',
    password        => 'my AWS secret access key'
  );
end;
/

Export to Object Store

We can use a local Oracle 21.3 installation to export data from the autonomous database to an object store. We use the parameter to point to the database credential we created earlier. We use an object store URI for the location. For AWS S3, use the URI of your S3 bucket. For Oracle Cloud the URI can take either of these forms. The following example uses the "swiftobjectstorage" URI. The following example uses the "objectstorage" URI.

Code/Command (click line numbers to comment):

https://swiftobjectstorage.{region}.oraclecloud.com/v1/{namespace}/{bucket-name}/{file-name}.dmp

https://objectstorage.{region}.oraclecloud.com/n/{namespace}/b/{bucket-name}/o/{file-name}.dmp

expdp admin/MyPassword123@obatp_high \
      tables=testuser1.t1 \
      directory=data_pump_dir \
      credential=obj_store_cred \
      dumpfile=https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/t1.dmp \
      exclude=statistics

Export: Release 21.0.0.0.0 - Production on Tue Sep 7 18:36:39 2021
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Starting "ADMIN"."SYS_EXPORT_TABLE_01":  admin/********@obatp_high tables=testuser1.t1 directory=data_pump_dir
  credential=obj_store_cred dumpfile=https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/t1.dmp exclude=statistics
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TESTUSER1"."T1"                            32.60 KB    1000 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "ADMIN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ADMIN.SYS_EXPORT_TABLE_01 is:
  https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/t1.dmp
Job "ADMIN"."SYS_EXPORT_TABLE_01" successfully completed at Tue Sep 7 18:37:14 2021 elapsed 0 00:00:26

$

expdp admin/MyPassword123@obatp_high \
      tables=testuser1.t1 \
      directory=data_pump_dir \
      credential=obj_store_cred \
      dumpfile=https://objectstorage.uk-london-1.oraclecloud.com/n/my-namespace/b/ob-bucket/o/t1.dmp \
      exclude=statistics

Export: Release 21.0.0.0.0 - Production on Tue Sep 7 19:05:47 2021
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Starting "ADMIN"."SYS_EXPORT_TABLE_01":  admin/********@obatp_high tables=testuser1.t1 directory=data_pump_dir
  credential=obj_store_cred dumpfile=https://objectstorage.uk-london-1.oraclecloud.com/n/my-namespace/b/ob-bucket/o/t1.dmp exclude=statistics
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TESTUSER1"."T1"                            32.60 KB    1000 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "ADMIN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ADMIN.SYS_EXPORT_TABLE_01 is:
  https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/t1.dmp
Job "ADMIN"."SYS_EXPORT_TABLE_01" successfully completed at Tue Sep 7 19:06:06 2021 elapsed 0 00:00:15

$

Get the Log File

If we want to read the contents of the log file we can push it across to the object store using the procedure in the package. It can then be downloaded from the object store. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

conn admin/MyPassword123@obatp_high

begin
  dbms_cloud.put_object(
    credential_name => 'obj_store_cred',
    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/export.log',
    directory_name  => 'data_pump_dir',
    file_name       => 'export.log');
end;
/

Comments (0)

Please to add comments

No comments yet. Be the first to comment!