Oracle Cloud：自治数据库（ADW 或 ATP） - 将数据导出到对象存储 (expdp)

在Oracle 18c impdp 中引入了 CREDENTIAL 参数来指定要用于导入的对象存储凭据。从 Oracle 21c (21.3) 开始，还可以将 CREDENTIAL 参数与 expdp 实用程序一起使用。

首先，我们需要一个对象存储桶来导出数据。 可以使用 Oracle Cloud Object Storage 存储桶或 AWS S3 存储桶。 

本示例使用 Oracle Cloud Object Storage 存储桶。

一、创建要导出的东西

1、我们连接到一个自治数据库并创建一个新的测试用户。

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;

2、创建一个我们将导出的测试表。

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

二、对象存储凭证

为对象存储创建凭证。对于 Oracle 对象存储桶，使用我们的 Oracle Cloud 电子邮件和生成的身份验证令牌。

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        => 'grainger@sdoug.com',
    password        => '{my-Auth-Token}'
  ) ;
end;
/

对于 AWS 存储桶，使用 AWS 访问密钥和私有密钥。

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

三、导出到对象存储

可以使用本地 Oracle 21.3 将数据从自治数据库导出到对象存储。

使用 CREDENTIALS 参数指向我们之前创建的数据库凭据。使用对象存储 URI 作为 DUMPFILE 位置。对于 AWS S3，使用 S3 存储桶的 URI。对于 Oracle Cloud，URI 可以采用下面这两种形式之一。

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

以下示例使用 “swiftobjectstorage” URI。

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

$

以下示例使用 “objectstorage” URI。

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

$

四、获取日志文件

如果我们想读取 expdp 日志文件的内容，可以使用 DBMS_CLOUD 包中的 PUT_OBJECT 存储过程将其推送到对象存储。

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;
/

然后可以从对象存储中下载它。