Installing DBMS_CLOUD in an Exadata Cloud service

The DBMS_CLOUD package was developed to allow the Autonomous Database access to Object Storage for data loading, external tables and migration using data pump. The functionality has been extended to other types of Oracle Databases in the cloud from release 19.9, and it is hoped, will actually become built in.

The process is documented in MOS Oracle Support Document 2748362.1 (How To Setup And Use DBMS_CLOUD Package ) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=2748362.1 – check this document for updates in process.

Create a schema owning the DBMS_CLOUD package and install the DBMS_CLOUD code in CDB and all PDBs

requires CDB architecture
must be owned by the C##Cloud$SERVICE for future compatibility.

Create a script to run the install setup

@$ORACLE_HOME/rdbms/admin/sqlsessstart.sql

set verify off
-- you must not change the owner of the functionality to avoid future issues
define username='C##CLOUD$SERVICE'

create user &username no authentication account lock;

REM Grant Common User Privileges
grant INHERIT PRIVILEGES on user &username to sys;
grant INHERIT PRIVILEGES on user sys to &username;
grant RESOURCE, UNLIMITED TABLESPACE, SELECT_CATALOG_ROLE to &username;
grant CREATE ANY TABLE, DROP ANY TABLE, INSERT ANY TABLE, SELECT ANY TABLE,
CREATE ANY CREDENTIAL, CREATE PUBLIC SYNONYM, CREATE PROCEDURE, ALTER SESSION, CREATE JOB to &username;
grant CREATE SESSION, SET CONTAINER to &username;
grant SELECT on SYS.V_$MYSTAT to &username;
grant SELECT on SYS.SERVICE$ to &username;
grant SELECT on SYS.V_$ENCRYPTION_WALLET to &username;
grant read, write on directory DATA_PUMP_DIR to &username;
grant EXECUTE on SYS.DBMS_PRIV_CAPTURE to &username;
grant EXECUTE on SYS.DBMS_PDB_LIB to &username;
grant EXECUTE on SYS.DBMS_CRYPTO to &username;
grant EXECUTE on SYS.DBMS_SYS_ERROR to &username;
grant EXECUTE ON SYS.DBMS_ISCHED to &username;
grant EXECUTE ON SYS.DBMS_PDB_LIB to &username;
grant EXECUTE on SYS.DBMS_PDB to &username;
grant EXECUTE on SYS.DBMS_SERVICE to &username;
grant EXECUTE on SYS.DBMS_PDB to &username;
grant EXECUTE on SYS.CONFIGURE_DV to &username;
grant EXECUTE on SYS.DBMS_SYS_ERROR to &username;
grant EXECUTE on SYS.DBMS_CREDENTIAL to &username;
grant EXECUTE on SYS.DBMS_RANDOM to &username;
grant EXECUTE on SYS.DBMS_SYS_SQL to &username;
grant EXECUTE on SYS.DBMS_LOCK to &username;
grant EXECUTE on SYS.DBMS_AQADM to &username;
grant EXECUTE on SYS.DBMS_AQ to &username;
grant EXECUTE on SYS.DBMS_SYSTEM to &username;
grant EXECUTE on SYS.SCHED$_LOG_ON_ERRORS_CLASS to &username;
grant SELECT on SYS.DBA_DATA_FILES to &username;
grant SELECT on SYS.DBA_EXTENTS to &username;
grant SELECT on SYS.DBA_CREDENTIALS to &username;
grant SELECT on SYS.AUDIT_UNIFIED_ENABLED_POLICIES to &username;
grant SELECT on SYS.DBA_ROLES to &username;
grant SELECT on SYS.V_$ENCRYPTION_KEYS to &username;
grant SELECT on SYS.DBA_DIRECTORIES to &username;
grant SELECT on SYS.DBA_USERS to &username;
grant SELECT on SYS.DBA_OBJECTS to &username;
grant SELECT on SYS.V_$PDBS to &username;
grant SELECT on SYS.V_$SESSION to &username;
grant SELECT on SYS.GV_$SESSION to &username;
grant SELECT on SYS.DBA_REGISTRY to &username;
grant SELECT on SYS.DBA_DV_STATUS to &username;

alter session set current_schema=&username;
REM Create the Catalog objects
@$ORACLE_HOME/rdbms/admin/dbms_cloud_task_catalog.sql
@$ORACLE_HOME/rdbms/admin/dbms_cloud_task_views.sql
@$ORACLE_HOME/rdbms/admin/dbms_cloud_catalog.sql
@$ORACLE_HOME/rdbms/admin/dbms_cloud_types.sql

REM Create the Package Spec
@$ORACLE_HOME/rdbms/admin/prvt_cloud_core.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_task.plb
@$ORACLE_HOME/rdbms/admin/dbms_cloud_capability.sql
@$ORACLE_HOME/rdbms/admin/prvt_cloud_request.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_internal.plb
@$ORACLE_HOME/rdbms/admin/dbms_cloud.sql
@$ORACLE_HOME/rdbms/admin/prvt_cloud_admin_int.plb

REM Create the Package Body
@$ORACLE_HOME/rdbms/admin/prvt_cloud_core_body.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_task_body.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_capability_body.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_request_body.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_internal_body.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_body.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_admin_int_body.plb

-- Create the metadata
@$ORACLE_HOME/rdbms/admin/dbms_cloud_metadata.sql

alter session set current_schema=sys;

@$ORACLE_HOME/rdbms/admin/sqlsessend.sql

Execute the script using catcon.pl

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u sys/ATPwelcome--1234 --force_pdb_mode 'READ WRITE' -b dbms_cloud_install -d /home/oracle/mel -l /home/oracle/mel dbms_cloud_install.sql

Verify the logfiles for errors

Check that there are no invalid objects owned by your C##Cloud$SERVICE user.

 select owner, object_name, object_type from dba_objects where status='INVALID';

Create the wallet containing the necessary certificates to access HTTP URIs and Object Stores

This step needs to be done on all database nodes in when you are in RAC.

Currently the certificates are not automatically included – planned for the future (bug 32065668)

Download from https://objectstorage.us-phoenix-1.oraclecloud.com/p/QsLX1mx9A-vnjjohcC7TIK6aTDFXVKr0Uogc2DAN-Rd7j6AagsmMaQ3D3Ti4a9yU/n/adwcdemo/b/CERTS/o/dbc_certs.tar

The documents say that the wallet needs to be added to the grid home sqlnet.ora in RAC, database home in non-RAC. (need to check if this holds true to single instance but GI installs)

Current wallet location in the GRID HOME sqlnet.ora

WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /u02/app/oracle/admin/grid/grid_wallet)))



[grid@ecs1-dnvwf1 grid_wallet]$ orapki wallet display -wallet .
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Oracle Secret Store entries:
exacs_patch_key
Trusted Certificates:

Can I add my certificates to this?

Nope! It fails constantly – almost certainly because I don’t know the password for this wallet or it’s a different format.

Need to create a new wallet and the keys.

Suggested location /opt/oracle/dcs/commonstore/wallets/ssl is owned by opc…

So, going to go alongside the existing wallet – this is outside the GRID HOME so in theory safe from patching?

As grid:

mkdir -p /u02/app/oracle/admin/grid/ssl_wallet

orapki wallet create -wallet /u02/app/oracle/admin/grid/ssl_wallet -pwd SecretPa$$ -auto_login
 orapki wallet add -wallet /u02/app/oracle/admin/grid/ssl_wallet  -pwd 
SecretPa$$ -trusted_cert -cert /tmp/cert/VeriSign.cer
orapki wallet add -wallet /u02/app/oracle/admin/grid/ssl_wallet  -pwd 
SecretPa$$ -trusted_cert -cert /tmp/cert/BaltimoreCyberTrust.cer
orapki wallet add -wallet /u02/app/oracle/admin/grid/ssl_wallet -pwd 
SecretPa$$ -trusted_cert -cert /tmp/cert/DigiCert.cer

Verify your certs are in the wallet

$ orapki wallet display -wallet /u02/app/oracle/admin/grid/ssl_wallet/
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Trusted Certificates:
Subject:        CN=VeriSign Class 3 Public Primary Certification Authority - G5,OU=(c) 2006 VeriSign\, Inc. - For authorized use only,OU=VeriSign Trust Network,O=VeriSign\, Inc.,C=US
Subject:        CN=Baltimore CyberTrust Root,OU=CyberTrust,O=Baltimore,C=IE
Subject:        CN=DigiCert Global Root CA,OU=www.digicert.com,O=DigiCert Inc,C=US

Configure your Oracle environment to use the new SSL wallet

On both nodes in the cluster – add wallet to sqlnet.ora in GRID HOME


# SSL certificate wallets for object store
WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /u02/app/oracle/admin/grid/ssl_wallet)))

Configure your database with ACEs for DBMS_CLOUD

Now you need to create Access Control Entries to allow communication with object store through https.

Your Exadata must be able to access the service network containing Object Storage. (via service gateway, internet gateway etc.)

As oracle, create a dbms_cloud_aces.sql

set the sslwalletdir before running

Note – this script will still prompt for proxy information as the parser spots the ‘&’ in the commented out area and ask for the value. If you don’t have a proxy, either just hit return when prompted , or delete the proxy lines before running.



@$ORACLE_HOME/rdbms/admin/sqlsessstart.sql

-- you must not change the owner of the functionality to avoid future issues
define clouduser=C##CLOUD$SERVICE

-- CUSTOMER SPECIFIC SETUP, NEEDS TO BE PROVIDED BY THE CUSTOMER
-- - SSL Wallet directory
define sslwalletdir=<Set SSL Wallet Directory>

--
-- UNCOMMENT AND SET THE PROXY SETTINGS VARIABLES IF YOUR ENVIRONMENT NEEDS PROXYS
--
-- define proxy_uri=<your proxy URI address>
-- define proxy_host=<your proxy DNS name>
-- define proxy_low_port=<your_proxy_low_port>
-- define proxy_high_port=<your_proxy_high_port>

-- Create New ACL / ACE s
begin
-- Allow all hosts for HTTP/HTTP_PROXY
dbms_network_acl_admin.append_host_ace(
host =>'*',
lower_port => 443,
upper_port => 443,
ace => xs$ace_type(
privilege_list => xs$name_list('http', 'http_proxy'),
principal_name => upper('&clouduser'),
principal_type => xs_acl.ptype_db));
--
-- UNCOMMENT THE PROXY SETTINGS SECTION IF YOUR ENVIRONMENT NEEDS PROXYS
--
-- Allow Proxy for HTTP/HTTP_PROXY
-- dbms_network_acl_admin.append_host_ace(
-- host =>'&proxy_host',
-- lower_port => &proxy_low_port,
-- upper_port => &proxy_high_port,
-- ace => xs$ace_type(
-- privilege_list => xs$name_list('http', 'http_proxy'),
-- principal_name => upper('&clouduser'),
-- principal_type => xs_acl.ptype_db));
--
-- END PROXY SECTION
--

-- Allow wallet access
dbms_network_acl_admin.append_wallet_ace(
wallet_path => 'file:&sslwalletdir',
ace => xs$ace_type(privilege_list =>
xs$name_list('use_client_certificates', 'use_passwords'),
principal_name => upper('&clouduser'),
principal_type => xs_acl.ptype_db));
end;
/

-- Setting SSL_WALLET database property
begin
-- comment out the IF block when installed in non-CDB environments
if sys_context('userenv', 'con_name') = 'CDB$ROOT' then
execute immediate 'alter database property set ssl_wallet=''&sslwalletdir''';
--
-- UNCOMMENT THE FOLLOWING COMMAND IF YOU ARE USING A PROXY
--
-- execute immediate 'alter database property 
-- set http_proxy=''&proxy_uri''';
end if;
end;
/

@$ORACLE_HOME/rdbms/admin/sqlsessend.sql

Execute this as sys connected to cdb$root

Verify the configuration of DBMS_CLOUD

Use the following script to check your database can access the oracle cloud object storage home page

Need to set the variables sslwalletdir, sslwalletpwd for your environment



-- you must not change the owner of the functionality to avoid future issues
define clouduser=C##CLOUD$SERVICE

-- CUSTOMER SPECIFIC SETUP, NEEDS TO BE PROVIDED BY THE CUSTOMER
-- - SSL Wallet directory and password
define sslwalletdir=<Set SSL Wallet Directory>
define sslwalletpwd=<Set SSL Wallet password>

-- create and run this procedure as owner of the ACLs, which is the future owner
-- of DBMS_CLOUD
CREATE OR REPLACE PROCEDURE &clouduser..GET_PAGE(url IN VARCHAR2) AS
request_context UTL_HTTP.REQUEST_CONTEXT_KEY;
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
data VARCHAR2(32767) default null;
err_num NUMBER default 0;
err_msg VARCHAR2(4000) default null;

BEGIN

-- Create a request context with its wallet and cookie table
request_context := UTL_HTTP.CREATE_REQUEST_CONTEXT(
wallet_path => 'file:&sslwalletdir',
wallet_password => '&sslwalletpwd');

-- Make a HTTP request using the private wallet and cookie
-- table in the request context
req := UTL_HTTP.BEGIN_REQUEST(
url => url,
request_context => request_context);

resp := UTL_HTTP.GET_RESPONSE(req);

DBMS_OUTPUT.PUT_LINE('valid response');

EXCEPTION
WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 3800);
DBMS_OUTPUT.PUT_LINE('possibly raised PLSQL/SQL error: ' ||err_num||' - '||err_msg);

UTL_HTTP.END_RESPONSE(resp);
data := UTL_HTTP.GET_DETAILED_SQLERRM ;
IF data IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('possibly raised HTML error: ' ||data);
END IF;
END;
/
set serveroutput on
BEGIN
&clouduser..GET_PAGE('https://objectstorage.eu-frankfurt-1.oraclecloud.com');
END;
/

set serveroutput off
drop procedure &clouduser..GET_PAGE;

Right – that failed

possibly raised PLSQL/SQL error: -29273 - ORA-29273: HTTP request failed possibly raised HTML error: ORA-12535: TNS:operation timed out

Due to IAM policies I cannot see all the network definitions, but we do have services network to LHR defined. Change the script to access this object storage. https://objectstorage.uk-london-1.oraclecloud.com

New error

possibly raised PLSQL/SQL error: -29273 - ORA-29273: HTTP request failed possibly raised HTML error: ORA-28759: failure to open file

Can the oracle software owner read the wallet? – nope! add g+r to the files in the wallet directory (all nodes!)

[grid@ecs1-dnvwf2 ssl_wallet]$ ls -latr
total 16
drwxr-xr-x 4 grid oinstall 4096 Jun 18 13:39 ..
-rw-r----- 1 grid oinstall    0 Jun 18 13:40 ewallet.p12.lck
-rw-r----- 1 grid oinstall    0 Jun 18 13:40 cwallet.sso.lck
drwxr-xr-x 2 grid oinstall 4096 Jun 18 13:40 .
-rw-r----- 1 grid oinstall 3512 Jun 18 13:40 ewallet.p12
-rw-r----- 1 grid oinstall 3557 Jun 18 13:40 cwallet.sso

Now when executed the script returns the text valid response

SQL> @dbms_cloud_check
old   1: CREATE OR REPLACE PROCEDURE &clouduser..GET_PAGE(url IN VARCHAR2) AS
new   1: CREATE OR REPLACE PROCEDURE C##CLOUD$SERVICE.GET_PAGE(url IN VARCHAR2) AS
old  13: wallet_path => 'file:&sslwalletdir',
new  13: wallet_path => 'file:/u02/app/oracle/admin/grid/ssl_wallet',
old  14: wallet_password => '&sslwalletpwd');
new  14: wallet_password => 'We1come$');

Procedure created.

old   2: &clouduser..GET_PAGE('https://objectstorage.uk-london-1.oraclecloud.com');
new   2: C##CLOUD$SERVICE.GET_PAGE('https://objectstorage.uk-london-1.oraclecloud.com');
valid response

PL/SQL procedure successfully completed.

old   1: drop procedure &clouduser..GET_PAGE
new   1: drop procedure C##CLOUD$SERVICE.GET_PAGE

Procedure dropped.

Configuring users or roles to use DBMS_CLOUD

Grant the minimal privileges to a user or role for using DBMS_CLOUD

You can grant the required privileges to individual users (e.g. execute on dbms_cloud) but it is simpler to manage if you create a role and then grant this to users as required.

In your pdb

define userrole='OBJECT_STORAGE_USER'

-- target sample user
define username='MEL'

create role &userrole;
grant &userrole to &username;

REM the following are minimal privileges to use DBMS_CLOUD
REM - this script assumes core privileges
REM - CREATE SESSION
REM - Tablespace quote on the default tablespace for a user

REM for creation of external tables, e.g. DBMS_CLOUD.CREATE_EXTERNAL_TABLE()
grant CREATE TABLE to &userrole;

REM for using COPY_DATA()
REM - Any log and bad file information is written into this directory
grant read, write on directory DATA_PUMP_DIR to &userrole;

REM
grant EXECUTE on dbms_cloud to &userrole;

Configure ACEs for a user or role to use DBMS_CLOUD

ok – now you have a role, you need to connect to the pdb and set it so your role can use the ACE/ACL to see the wallet

Make sure to set cloudrole and sslwalletdir

Note – this script will still prompt for proxy information as the parser spots the ‘&’ in the commented out area and ask for the value. If you don’t have a proxy, either just hit return when prompted , or delete the proxy lines before running.



@$ORACLE_HOME/rdbms/admin/sqlsessstart.sql

-- target sample role
define cloudrole=OBJECT_STORAGE_USER

-- CUSTOMER SPECIFIC SETUP, NEEDS TO BE PROVIDED BY THE CUSTOMER
-- - SSL Wallet directory
define sslwalletdir=<Set SSL Wallet Directory>

--
-- UNCOMMENT AND SET THE PROXY SETTINGS VARIABLES IF YOUR ENVIRONMENT NEEDS PROXYS
--
-- define proxy_uri=<your proxy URI address>
-- define proxy_host=<your proxy DNS name>
-- define proxy_low_port=<your_proxy_low_port>
-- define proxy_high_port=<your_proxy_high_port>

-- Create New ACL / ACE s
begin
-- Allow all hosts for HTTP/HTTP_PROXY
dbms_network_acl_admin.append_host_ace(
host =>'*',
lower_port => 443,
upper_port => 443,
ace => xs$ace_type(
privilege_list => xs$name_list('http', 'http_proxy'),
principal_name => upper('&cloudrole'),
principal_type => xs_acl.ptype_db));

--
-- UNCOMMENT THE PROXY SETTINGS SECTION IF YOUR ENVIRONMENT NEEDS PROXYS
--
-- Allow Proxy for HTTP/HTTP_PROXY
-- dbms_network_acl_admin.append_host_ace(
-- host =>'&proxy_host',
-- lower_port => &proxy_low_port,
-- upper_port => &proxy_high_port,
-- ace => xs$ace_type(
-- privilege_list => xs$name_list('http', 'http_proxy'),
-- principal_name => upper('&cloudrole'),
-- principal_type => xs_acl.ptype_db));
--
-- END PROXY SECTION
--

-- Allow wallet access
dbms_network_acl_admin.append_wallet_ace(
wallet_path => 'file:&sslwalletdir',
ace => xs$ace_type(privilege_list =>
xs$name_list('use_client_certificates', 'use_passwords'),
principal_name => upper('&cloudrole'),
principal_type => xs_acl.ptype_db));
end;
/

@$ORACLE_HOME/rdbms/admin/sqlsessend.sql

Next you need to generate credentials that allow your user to access the bucket

In the Cloud console go to your user settings page and generate an AUTH TOKEN and note it.

Connect as the database user who will be accessing object storage and execute the create credential process. Each user has their own credential namespace.

BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'your credential name',
username => 'OCI within your tenancy',
password => 'auth token generated for OCI user'
);
END;
/

e.g

BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'CLOUD_OSS_CRED',
username => 'idcs-demotenancy/melanie@oracle.com',
password => 'blahauthtokenblah'
);
END;
/

Test it!

Connect as your dbms_cloud enabled user

select * from  dbms_cloud.list_objects('CLOUD_OSS_CRED','https://objectstorage.uk-london-1.oraclecloud.com/n/demotenancy/b/mel_test/o/');

Upload channels.txt to your bucket

S,Direct Sales,Direct
T,Tele Sales,Direct
C,Catalog,Indirect
I,Internet,Indirect
P,Partners,Others

Create your destination table

CREATE TABLE CHANNELS
   (channel_id CHAR(1),
    channel_desc VARCHAR2(20),
    channel_class VARCHAR2(20)
   );
/

Execute the copy_data process

BEGIN
 DBMS_CLOUD.COPY_DATA(
    table_name =>'CHANNELS',
    credential_name =>'CLOUD_OSS_CRED',
    file_uri_list =>'https://objectstorage.uk-london-1.oraclecloud.com/n/demotenancy/b/mel_test/o/channels.txt',
    format => json_object('delimiter' value ',')
 );
END;
/

Test it more!

With a four line file it didn’t feel particularly quick – the elapsed time was 5.45 seconds first run – 1.37 on subsequent runs.

Obtain another dataset – I wanted something in a flat file, easily read. Selected the GoodReads dataset https://www.kaggle.com/bahramjannesarr/goodreads-book-datasets-10m though there were issues with this that were discovered later.

Start with the smaller/simpler files – The user ratings – this has the columns ID, Name,Rating

Create the destination table

CREATE TABLE RATINGS
   (rating_id varchar(255),
    name VARCHAR2(255),
    rating VARCHAR2(255)
   );

Try loading data using the first file

BEGIN
 DBMS_CLOUD.COPY_DATA(
    table_name =>'RATINGS',
    credential_name =>'CLOUD_OSS_CRED',
    file_uri_list =>'https://objectstorage.uk-london-1.oraclecloud.com/n/demotenancy/b/mel_test/o/user_rating_0_to_1000.csv',
    format => json_object('delimiter' value ',','ignoremissingcolumns' value 'true', 'rejectlimit' value '100','skipheaders' value '1')
 );
END;
/

Filesize	Run 1	Run2
2.55mb	2.52	1.37

Try with multiple files in file_uri_list

BEGIN
 DBMS_CLOUD.COPY_DATA(
    table_name =>'RATINGS',
    credential_name =>'CLOUD_OSS_CRED',
    file_uri_list =>'https://objectstorage.uk-london-1.oraclecloud.com/n/demotenancy/b/mel_test/o/user_rating_0_to_1000.csv,https://objectstorage.uk-london-1.oraclecloud.com/n/demotenancy/b/mel_test/o/user_rating_1000_to_2000.csv',
    format => json_object('delimiter' value ',','ignoremissingcolumns' value 'true', 'rejectlimit' value '100','skipheaders' value '1')
 );
END;
/

Filesize	Run 1	Run2
2.55mb + 2.14mb	1.78	1.64

Right – now to go a bit bigger because it’s starting to feel like the time taken is all setup time. The book files supplied in the zip annoying have different formats, so determine which have the same columns (books 1800 to 5000)

Create a combined file containing the data.

create table books (
id varchar(255),
Name varchar(255),
Authors varchar(255),
ISBN  varchar(255),
Rating  varchar(255),
PublishYear  varchar(255),
PublishMonth  varchar(255),
PublishDay varchar(255),
Publisher  varchar(255),
RatingDist5  varchar(255),
RatingDist4  varchar(255),
RatingDist3  varchar(255),
RatingDist2 varchar(255),
RatingDist1  varchar(255),
RatingDistTotal varchar(255),
CountsOfReview varchar(255),
Language  varchar(255),
PagesNumber  varchar(255),
Description varchar(4000)
);

Multiple individual files – note the data is fugly, and there are a lot of rejects due to multi-line HTML descriptions so have increased the rejectlimit.

BEGIN
 DBMS_CLOUD.COPY_DATA(
    table_name =>'BOOKS',
    credential_name =>'CLOUD_OSS_CRED',
    file_uri_list =>'https://objectstorage.uk-london-1.oraclecloud.com/n/demotenancy/b/mel_test/o/book1800k-1900k.csv,https://objectstorage.uk-london-1.oraclecloud.com/n/demotenancy/b/mel_test/o/book1900k-2000k.csv,https://objectstorage.uk-london-1.oraclecloud.com/n/demotenancy/b/mel_test/o/book2000k-3000k.csv,https://objectstorage.uk-london-1.oraclecloud.com/n/demotenancy/b/mel_test/o/book3000k-4000k.csv,https://objectstorage.uk-london-1.oraclecloud.com/n/demotenancy/b/mel_test/o/book4000k-5000k.csv',
    format => json_object('delimiter' value ',','ignoremissingcolumns' value 'true', 'rejectlimit' value '100000','skipheaders' value '1')
 );
END;
/
select count(*) from books;

Filesize	Run 1	Run 2
29mb + 32mb +276m + 166mb +172mb	8.45	6.54

Larger file made by concatenating those 5 the user files together

BEGIN
 DBMS_CLOUD.COPY_DATA(
    table_name =>'BOOKS',
    credential_name =>'CLOUD_OSS_CRED',
    file_uri_list =>'https://objectstorage.uk-london-1.oraclecloud.com/n/demotenancy/b/mel_test/o/combined_book_1800-500.csv',
    format => json_object('delimiter' value ',','ignoremissingcolumns' value 'true', 'rejectlimit' value '100000','skipheaders' value '1')
 );
END;
/
select count(*) from books;

Filesize	Run 1	Run 2
627mb	4.51	4.31

Counter-intuitively it seems that a single large file performs better than multiple individual files. However, this needs further investigation.

Validation Script

This script from the MOS note can be used to verify the connectivity and get more detailed error messages.



-- user to trouble shoot
define clouduser=<username>

-- CUSTOMER SPECIFIC SETUP, NEEDS TO BE PROVIDED BY THE CUSTOMER
-- - SSL Wallet directory and password
define sslwalletdir=<Set SSL Wallet Directory>
define sslwalletpwd=<Set SSL Wallet password>

-- create and run this procedure as owner of the ACLs, which is the future owner
-- of DBMS_CLOUD
CREATE OR REPLACE PROCEDURE &clouduser..GET_PAGE(url IN VARCHAR2) AS
request_context UTL_HTTP.REQUEST_CONTEXT_KEY;
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
data VARCHAR2(32767) default null;
err_num NUMBER default 0;
err_msg VARCHAR2(4000) default null;

BEGIN

-- Create a request context with its wallet and cookie table
request_context := UTL_HTTP.CREATE_REQUEST_CONTEXT(
wallet_path => 'file:&sslwalletdir',
wallet_password => '&sslwalletpwd');

-- Make a HTTP request using the private wallet and cookie
-- table in the request context
req := UTL_HTTP.BEGIN_REQUEST(
url => url,
request_context => request_context);

resp := UTL_HTTP.GET_RESPONSE(req);

DBMS_OUTPUT.PUT_LINE('valid response');

EXCEPTION
WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 3800);
DBMS_OUTPUT.PUT_LINE('possibly raised PLSQL/SQL error: ' ||err_num||' - '||err_msg);

UTL_HTTP.END_RESPONSE(resp);
data := UTL_HTTP.GET_DETAILED_SQLERRM ;
IF data IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('possibly raised HTML error: ' ||data);
END IF;
END;
/
set serveroutput on
BEGIN
&clouduser..GET_PAGE('https://objectstorage.eu-frankfurt-1.oraclecloud.com');
END;
/

set serveroutput off
drop procedure &clouduser..GET_PAGE;

Oracle, Unix and the world at large

An engineer outsources her memory to the internet

Installing DBMS_CLOUD in an Exadata Cloud service

Create a schema owning the DBMS_CLOUD package and install the DBMS_CLOUD code in CDB and all PDBs

Create the wallet containing the necessary certificates to access HTTP URIs and Object Stores

Configure your Oracle environment to use the new SSL wallet

Configure your database with ACEs for DBMS_CLOUD

Verify the configuration of DBMS_CLOUD

Configuring users or roles to use DBMS_CLOUD

Grant the minimal privileges to a user or role for using DBMS_CLOUD

Configure ACEs for a user or role to use DBMS_CLOUD

Test it!

Test it more!

Validation Script

Leave a comment Cancel reply

Create a schema owning the DBMS_CLOUD package and install the DBMS_CLOUD code in CDB and all PDBs

Create the wallet containing the necessary certificates to access HTTP URIs and Object Stores

Configure your Oracle environment to use the new SSL wallet

Configure your database with ACEs for DBMS_CLOUD

Verify the configuration of DBMS_CLOUD

Configuring users or roles to use DBMS_CLOUD

Grant the minimal privileges to a user or role for using DBMS_CLOUD

Configure ACEs for a user or role to use DBMS_CLOUD

Test it!

Test it more!

Validation Script

Share this:

Related

Leave a comment Cancel reply