Migration Oracle Database to Oracle Cloud (TPC-H Data)

In this post, we migrate 128 GB TPC-H Data to Oracle Cloud
(Autonomous Database types; ATP and ADW).

Our goal is to show you how you can reproduce this test and migrate your On-Prem Oracle database to the mighty Oracle Cloud.

We assume you are skilled enough to create your Oracle Cloud account and you know the basics of Oracle DBA.

You know how to connect to an “autonomous” database created in OCI.

OCI = Oracle Cloud Infrastructure”

Once you have created your first ATP or ADW Autonomous Database, you can follow these steps:

1

CREATE USER TPCH
IDENTIFIED BY "__YOUR__PASSWORD_HERE___"
ACCOUNT UNLOCK;
grant connect, resource to TPCH;
ALTER USER "TPCH" QUOTA UNLIMITED ON "DATA";

2

BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'AUTH_CRED01',
username => 'daniel.moya@dimensigon.com',
password => '__YOUR_CREDENTIAL_PWD_HERE__');
END;
/

3

  1. Create an Object Storage Bucket
  2. Save the Bucket name and OCID.
  3. Upload the files.

4

  1. Once uploaded, replace the following URL with your OCID and your bucket name.

Please note that it can accept the typical pattern from Oracle Data Pump to specify multiple dump files. (useful)

https://swiftobjectstorage.<cloud-region>.oraclecloud.com/v1/<OCID>/<bucket-name>/<file_name>%U.dmp

Example:

https://swiftobjectstorage.<cloud-region>.oraclecloud.com/v1/<OCID>/<bucket-name>/<file_name>%U.dmp

Loading data using SQL Developer

Now we have the data uploaded to Oracle Cloud and we have the DBMS_CLOUD.CREATE_CREDENTIAL created. Time to start the import data pump through the Wizard.

To use the Wizard, you have previously download the zip to connect to an Oracle Instance in OCI.

Once you can connect with SQL Developer to OCI.

Click on “Data Pump” and use the proper connection.

 

(sorry for the quality)

 

Image captured from a video.

5

In case you want to launch the import manually from SQL Developer. It may work with a similar code to this:

DECLARE
 s varchar2(1000); 
 h1 number;
 errorvarchar varchar2(100):= 'ERROR';
 tryGetStatus number := 0;
begin
 h1 := dbms_datapump.open (operation => 'IMPORT', job_mode => 'TABLE', job_name => 'IMP_TPCH-12_15_25', version => 'COMPATIBLE'); 
 tryGetStatus := 1;
 dbms_datapump.set_parallel(handle => h1, degree => 8); 
 dbms_datapump.add_file(handle => h1, filename => 'IMPORT-'||to_char(sysdate,'hh24_mi_ss')||'.LOG', directory => 'DATA_PUMP_DIR', filetype=>DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE); 
 dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0); 
 dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''TPCH'')'); 
 dbms_datapump.metadata_filter(handle => h1, name => 'NAME_EXPR', value => 'IN(''H_CUSTOMER'',''H_LINEITEM'',''H_NATION'',''H_ORDER'',''H_PART'',''H_PARTSUPP'',''H_REGION'',''H_SUPPLIER'')'); 
 dbms_datapump.add_file(handle => h1, filename => 'https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/frkhxv69rsuw/bucket-20201014-0000/tpch_19c_dump%U.dmp', directory => 'AUTH_CRED01', filetype => 5); 
 dbms_datapump.metadata_remap(handle => h1, name => 'REMAP_TABLESPACE', old_value => 'TPCH_TS', value => UPPER('DATA') ); 
 dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1); 
 dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC'); 
 dbms_datapump.set_parameter(handle => h1, name => 'SKIP_UNUSABLE_INDEXES', value => 0);
 dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0); 
 dbms_datapump.detach(handle => h1); 
 errorvarchar := 'NO_ERROR'; 
EXCEPTION
 WHEN OTHERS THEN
 BEGIN 
 IF ((errorvarchar = 'ERROR')AND(tryGetStatus=1)) THEN 
 DBMS_DATAPUMP.DETACH(h1);
 END IF;
 EXCEPTION 
 WHEN OTHERS THEN 
 NULL;
 END;
 RAISE;
END;
/

6(Opt.)

-- In case the IMPDP did not worked because of bucket or credential errors, check the following:

ALTER DATABASE PROPERTY SET default_bucket=’https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/frkhxv69rsuw’;

ALTER DATABASE PROPERTY SET default_credential = ‘ADMIN.AUTH_CRED01’;

SELECT PROPERTY_VALUE from database_properties WHERE PROPERTY_NAME=’DEFAULT_BUCKET’;

SELECT PROPERTY_NAME, PROPERTY_VALUE from database_properties WHERE PROPERTY_NAME LIKE ‘DEFAULT%’;

7

begin
dbms_stats.GATHER_SCHEMA_STATS(ownname => 'TPCH',degree => 8, cascade => true);
end;
/

8

Run the TPC-H Queries