Introduction

Last year I wrote a number of posts on Big Data and shared how we can access CSV, Parquet and Avro file formats directly from an on-premises Oracle 19c database.

In these posts I demonstrated how we can access different file types using SQL external tables on a file system, unfortunately I quickly run into issues with my on-premises database due to the missing the DBMS_CLOUD package, which at the time was only available in the Cloud.

Great News!

Oracle has now made the DBMS_CLOUD package available for use for on-premises for Oracle 19.9+, note for the purpose of this blog I will be using 19.10, but 19.9 should be the same.

The DBMS_CLOUD is pre-installed, configured and maintained in Oracle Autonomous Database, but for on-premises databases we have to do this manually.

The installation and setup of the DBMS_CLOUD is well documented on MOS (DOC ID 2748362.1), so let’s use it.

Step 1 – Installing DBMS_CLOUD

Logon to Oracle Support, navigate to How To Setup And Use DBMS_CLOUD Package (Doc ID 2748362.1) and capture the SQL provided and create a sql script e.g. dbms_cloud_install.sql

The DBMS_CLOUD package needs to be run into the CDB and every PDB, this is easily done using catcon.pl e.g.

$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u sys/<password> --force_pdb_mode 'READ WRITE' -b dbms_cloud_install -d /home/oracle/dbc -l /home/oracle/dbc dbms_cloud_install.sql

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/dbc/dbms_cloud_install_catcon_738.lst]

catcon::set_log_file_base_path: catcon: See [/home/oracle/dbc/dbms_cloud_install*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/home/oracle/dbc/dbms_cloud_install_*.lst] files for spool files, if any

catcon.pl: completed successfully

Check for errors in the log file, if ok move on.

Step 2- Confirm DBMS_CLOUD installation

Connect to ROOT to see all containers and check for DBMS_CLOUD objects.

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Mar 26 15:59:50 2021
Version 19.10.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0

SQL> select con_id, owner, object_name, status, sharing, oracle_maintained from cdb_objects where object_name = 'DBMS_CLOUD' order by con_id;

...

Confirm that all C##CLOUD$SERVICE objects are VALID.

Step 3 – Create and configure Oracle Wallet

Oracle does not currently ship with certificates but does provide a link within the MOS Note to a tar file (dbc_certs.tar) for the 3 certificates.

• VeriSign.cer
• BaltimoreCyberTrust.cer
• DigiCert.cer

Untar the certificates.

$ tar -xvf /mnt/oracle/dbc_certs.tar 
BaltimoreCyberTrust.cer
DigiCert.cer
VeriSign.cer

And now use orapki wallet to create a new wallet, I am doing this into my GI Oracle Home.

$ cd $ORACLE_HOME/wallets/ssl
$ orapki wallet create -wallet . -pwd <password> -auto_login
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2020, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

$ orapki wallet add -wallet . -trusted_cert -cert /home/oracle/dbc/VeriSign.cer -pwd <password>
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2020, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

$ orapki wallet add -wallet . -trusted_cert -cert /home/oracle/dbc/BaltimoreCyberTrust.cer -pwd <password>
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2020, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

$ orapki wallet add -wallet . -trusted_cert -cert /home/oracle/dbc/DigiCert.cer -pwd <password>
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2020, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

Let’s now check our certifcates

$ orapki wallet display -wallet .
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2020, Oracle and/or its affiliates. All rights reserved.

Requested Certificates: 
User Certificates:
Trusted Certificates: 
Subject:        CN=DigiCert Global Root CA,OU=www.digicert.com,O=DigiCert Inc,C=US
Subject:        CN=Baltimore CyberTrust Root,OU=CyberTrust,O=Baltimore,C=IE
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

Step 4 – Configure Use of SSL Wallet

To have your SSL wallet take effect you need to point to the newly created ssl wallet for your Oracle installation by adding it to your sqlnet.ora on the Server side. Note: if you are on a RAC installation then you have to adjust this on all nodes. e.g.

$ cd $GRID_HOME/network/admin

$ cat sqlnet.ora 
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

WALLET_LOCATION=
(SOURCE=(METHOD=FILE)(METHOD_DATA=
(DIRECTORY=/u01/app/19.0.0/grid/wallets/ssl)))

There is no need to bounce your Listener, so on to the next step.

Step 5 – Configure the database Access Control Entries for DBMS_CLOUD

As before cut ‘n’ paste Oracle SQL from MOS (DOC ID 2748362.1) into a local file e.g. dbc_aces.sql

Update the SQL script to reference the sslwalletdir e.g.

...
-- CUSTOMER SPECIFIC SETUP, NEEDS TO BE PROVIDED BY THE CUSTOMER
-- - SSL Wallet directory
define sslwalletdir=/u01/app/19.0.0/grid/wallets/ssl
...

Now run the modified sql script, hitting return at the prompts.

$ sqlplus / as sysdba
SQL> @dbc_aces.sql
...
old   4: execute immediate 'alter database property set ssl_wallet=''&sslwalletdir''';
new   4: execute immediate 'alter database property set ssl_wallet=''/u01/app/19.0.0/grid/wallets/ssl''';
Enter value for proxy_uri: 
old   8: -- execute immediate 'alter database property set http_proxy=''&proxy_uri''';
new   8: -- execute immediate 'alter database property set http_proxy=''''';

PL/SQL procedure successfully completed.

Session altered.

SQL> select * from database_properties where property_name in ('SSL_WALLET','HTTP_PROXY');

PROPERTY_NAME PROPERTY_VALUE                   DESCRIPTION
------------- -------------------------------- ----------------------
SSL_WALLET    /u01/app/19.0.0/grid/wallets/ssl Location of SSL Wallet

Step 6 – Verify Configuration of DBMS_CLOUD

Before moving on it’s a good idea to check Oracle can read the certificates ok using the Oracle provided script, updated with your Wallet location, password and GET_PAGE details e.g

-- CUSTOMER SPECIFIC SETUP, NEEDS TO BE PROVIDED BY THE CUSTOMER
-- - SSL Wallet directory and password
define sslwalletdir=/u01/app/19.0.0/grid/wallets/ssl
define sslwalletpwd=<password>
..
&clouduser..GET_PAGE('https://objectstorage.uk-london-1.oraclecloud.com');
..

Run the script an confirm you see a ‘valid response’

SQL> @oci_check.sql 
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:/u01/app/19.0.0/grid/wallets/ssl',
old  14: wallet_password => '&sslwalletpwd');
new  14: wallet_password => '<password>');

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

Step 7 – Configure user access to DBMS_CLOUD

Using the script provided, create a local file e.g. dbc_user.sql, update username to your PDB user and run in.

...
-- target sample user
define username='SOE'
...

Step 8 – Configure role to use DBMS_CLOUD

Again cut & paste the SQL script into a local file e.g. dbc_cloud_user.sql, and update with SSL Wallet directory and run in.

...
-- CUSTOMER SPECIFIC SETUP, NEEDS TO BE PROVIDED BY THE CUSTOMER
-- - SSL Wallet directory
define sslwalletdir=/u01/app/19.0.0/grid/wallets/ssl
...

Step 9 – Create DBMS_CLOUD Credentials

We also need to create Access Control Entries (ACEs) to allow communication with Object Stores through https, create another file e.g. oci_credentials.sql update and run in.

BEGIN
  DBMS_CLOUD.drop_credential(credential_name => 'OCI_TOKEN');
END;
/

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'OCI_TOKEN',
    username => 'OCI User Name',
    password => 'Auth token created for OCI user'
  );
END;
/

Step 10 – Test Access

Let’s now check out our access with the Oracle 19c list_objects function, this provides us the ability to show objects in the specified location within an object store. 

DBMS_CLOUD.LIST_OBJECTS (
       credential_name      IN VARCHAR2,
       location_uri         IN VARCHAR2)
   RETURN TABLE;

OK, using the details above we can now see objects in our Oracle Cloud Infrastructure (OCI) Object Store

SQL> select OBJECT_NAME, BYTES
from dbms_cloud.list_objects(
     'OCI_TOKEN',
     'https://objectstorage.uk-london-1.oraclecloud.com/n/xxxxxxxxxxxx/b/bigdata/o/'
     );


OBJECT_NAME		       BYTES
------------------------- ----------
movie.avro		      331459
sales_extended.parquet	     9263650

Summary

In this post I have shared how to set-up the Oracle DBMS_CLOUD package within our on-premises database.

In Part 2 I will share how I created my OCI Object Store, token and uploaded objects into the bucket.

---

[twitter-follow screen_name=’RonEkins’ show_count=’yes’]