8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Oracle Cloud : Autonomous Database (ADW or ATP) - Import Data from an Object Store (impdp)
This article demonstrates how to import data into an Autonomous Data Warehouse (ADW) or Autonomous Transaction Processing (ATP) service on the Oracle Cloud using the impdp
utility.
The examples in this article are based on the Autonomous Data Warehouse (ADW), but the same method works fine for the Automated Transaction Processing (ATP) service too.
- Export Your Existing Data
- Object Store Credentials
- Import Data from S3
- Get the Log File
- Autonomous Transaction Processing (ATP)
Related articles.
- Oracle Cloud : Autonomous Data Warehouse (ADW) - Create Service
- Oracle Cloud : Autonomous Transaction Processing (ATP) - Create Service
- Oracle Cloud : Autonomous Database (ADW or ATP) - Load Data from an Object Store (DBMS_CLOUD)
- Autonomous Database
- Cloud : All Articles
Export Your Existing Data
We have a schema called TEST
in an Oracle 18c instance on Oracle Database Cloud Service (DBaaS). The schema has two tables (EMP and DEPT), which we want to transfer to the Autonomous Data Warehouse (ADW) or Autonomous Transaction Processing (ATP).
Create a directory object.
conn / as sysdba ALTER SESSION SET CONTAINER = pdb1; CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp/'; GRANT READ, WRITE ON DIRECTORY temp_dir TO test;
Export the schema. The ADW documentation suggests the EXCLUDE
and DATA_OPTIONS
options in the example below. These options are not necessary for ATP service. For such a small import it is silly to use the PARALLEL
clause, but we want to produce multiple dump files.
expdp test/test@pdb1 \ schemas=test \ parallel=2 \ version=12.2 \ directory=temp_dir dumpfile=emp_dept%u.dmp logfile=expdp_emp_dept.log \ exclude=index, cluster, indextype, materialized_view, materialized_view_log, materialized_zonemap, db_link \ data_options=group_partition_table_data
This resulted in the following dump files.
/tmp/emp_dept01.dmp /tmp/emp_dept02.dmp
These dump files were uploaded to an AWS S3 bucket.
I found I had to set the version=12.2
option during the export or I would receive the following error during the import into ADW and ATP.
Connected to: Oracle Database 18c Enterprise Edition Release 12.2.0.1.0 - 64bit Production ORA-39002: invalid operation ORA-39358: Export dump file version 18.0.0 not compatible with target version 12.2.0
It would appear there is something interesting about the version of 18c used for ADW.
Object Store Credentials
We need to create a credential containing a username and password for the connection to the object store. If you are an AWS S3 bucket the username and password are as follows.
- username : AWS access key
- password : AWS secret access key
The credentials are dropped and created using the DROP_CREDENTIAL
and CREATE_CREDENTIAL
procedures of the DBMS_CLOUD
package respectively.
BEGIN DBMS_CLOUD.drop_credential(credential_name => 'OBJ_STORE_CRED'); END; / BEGIN DBMS_CLOUD.create_credential ( credential_name => 'OBJ_STORE_CRED', username => 'my AWS access key', password => 'my AWS secret access key' ) ; END; /
Import Data from S3
For the import to work you will have to make a connection from an Oracle client to the ADW database. You can see the necessary setup to do this here.
From an 18c client we can issue the following type of import. The CREDENTIAL
option specifies the object store credential to be used for the import. The DUMPFILE
option specifies the URIs of the dump files in the object store. The TRANSFORM
and EXCLUDE
options are the recommended settings in the ADW documentation, but are not necessary for the ATP service. In this case we are using REMAP_SCHEMA
to place the objects into a schema called MY_USER on ADW.
impdp admin/MyPassword123@obadw_high \ directory=data_pump_dir \ credential=obj_store_cred \ dumpfile=https://s3-eu-west-1.amazonaws.com/my-sh-data-bucket/emp_dept01.dmp,https://s3-eu-west-1.amazonaws.com/my-sh-data-bucket/emp_dept02.dmp \ parallel=2 \ remap_schema=test:my_user \ partition_options=merge \ transform=segment_attributes:n \ transform=dwcs_cvt_iots:y transform=constraint_use_default_index:y \ exclude=index, cluster, indextype, materialized_view, materialized_view_log, materialized_zonemap, db_link
From a 12.2 or earlier client we need to set the default credential for the database on ADW.
ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'ADMIN.OBJ_STORE_CRED';
We can issue the following type of import. The CREDENTIAL
option isn't used and instead the "default_credential:" prefix is used before each object store URI. The rest of the parameters are the same as the previous example.
impdp admin/MyPassword123@obadw_high \ directory=data_pump_dir \ dumpfile=default_credential:https://s3-eu-west-1.amazonaws.com/my-sh-data-bucket/emp_dept01.dmp,default_credential:https://s3-eu-west-1.amazonaws.com/my-sh-data-bucket/emp_dept02.dmp \ parallel=2 \ remap_schema=test:my_user \ partition_options=merge \ transform=segment_attributes:n \ transform=dwcs_cvt_iots:y transform=constraint_use_default_index:y \ exclude=index, cluster, indextype, materialized_view, materialized_view_log, materialized_zonemap, db_link
I ran both these imports from the 18c client on my DBaaS service, but remember, it is initiating the import process on the ADW database.
From 19c onward we can use wildcards in URL-based dump file names, making import from multiple files into Autonomous Databases easier. In the following example we replace the number on the file name with the "*" wildcard.
impdp admin/MyPassword123@obadw_high \ directory=data_pump_dir \ credential=obj_store_cred \ dumpfile=https://s3-eu-west-1.amazonaws.com/my-sh-data-bucket/emp_dept0*.dmp \ parallel=2 \ remap_schema=test:my_user \ partition_options=merge \ transform=segment_attributes:n \ transform=dwcs_cvt_iots:y transform=constraint_use_default_index:y \ exclude=index, cluster, indextype, materialized_view, materialized_view_log, materialized_zonemap, db_link
Get the Log File
If we want to read the contents of the impdp
log file we can push it across to the object store using the PUT_OBJECT
command.
BEGIN DBMS_CLOUD.put_object( credential_name => 'obj_store_cred', object_uri => 'https://s3-eu-west-1.amazonaws.com/my-sh-data-bucket/import.log', directory_name => 'DATA_PUMP_DIR', file_name => 'import.log'); END; /
It can then be downloaded from the object store.
Autonomous Transaction Processing (ATP)
The method for importing data from a dump file in an object store is the same for the Autonomous Transaction Processing (ATP) service as the Autonomous Data Warehouse (ADW) service.
Remember you are able to create a variety of access structures in ATP that you can't in ADW.
For more information see:
- Importing Data Using Oracle Data Pump
- DBMS_CLOUD
- Oracle Cloud : Autonomous Data Warehouse (ADW) - Create Service
- Oracle Cloud : Autonomous Transaction Processing (ATP) - Create Service
- Oracle Cloud : Autonomous Database (ADW or ATP) - Load Data from an Object Store (DBMS_CLOUD)
- Autonomous Database
- Cloud : All Articles
Hope this helps. Regards Tim...