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
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.
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.
Create and configure Oracle Wallet
Create SSL Wallet with Certificates
In order to safely access HTTP URIs and Object Stores, a wallet is required with appropriate certificates for the object stores.
Currently Oracle does not ship the certs as part of RUs
You can download the necessary certificates from https://objectstorage.us-phoenix-1.oraclecloud.com/p/QsLX1mx9A-vnjjohcC7TIK6aTDFXVKr0Uogc2DAN-Rd7j6AagsmMaQ3D3Ti4a9yU/n/adwcdemo/b/CERTS/o/dbc_certs.tar
All Object Storage access is done through https and requires the database to have the certifications of all trusted locations. You need to create a security wallet containing the certificates.
- The wallet has to be created with auto login capabilities.
- On RAC installations, the wallet must either be accessible for all nodes centrally or you have to create the wallet on all nodes for local wallet storage.
The wallet can be stored at any location that is accessible by the user you installed the SW for – typically oracle, but it is recommended to manage the security wallet similar to the tde wallet provided by default for Cloud installation in Oracle OCI: by default, TDE wallets in Oracle Database Cloud installations are stored at /opt/oracle/dcs/commonstore/wallets/tde/$ORACLE_UNQNAME.
It is recommended to store the SSL wallet in an equivalent location, for example /opt/oracle/dcs/commonstore/wallets/ssl. Assuming you are choosing this location and have unpacked the certs in /home/oracle/dbc, you have to issue the following commands to create the necessary wallet.
If you are already having a wallet for SSL certificates then you do not have to create a new wallet but rather add the required certs to the existing wallet.
Create a wallet to allow HTTPS access to the cloud URIs.
Download the dbc_certs.tar file from the link provided in the MOS note. These instructions assume it is placed on the “/tmp” directory.
mkdir -p /home/oracle/dbc/commonstore/wallets/ssl cd /home/oracle/dbc/commonstore/wallets/ssl tar -xvf /tmp/dbc_certs.tar
Create a wallet and load the certificates. We are using the wallet password “MyPassword1”, but you should change it to something more secure for your installation.
orapki wallet create -wallet . -pwd MyPassword1 -auto_login
orapki wallet add -wallet . -trusted_cert -cert ./VeriSign.cer -pwd MyPassword1
orapki wallet add -wallet . -trusted_cert -cert ./BaltimoreCyberTrust.cer -pwd MyPassword1
orapki wallet add -wallet . -trusted_cert -cert ./DigiCert.cer -pwd MyPassword1
#create wallet for all *.cer files
#!/bin/bash
for file in *.cer; do
if [ -f "$file" ]; then
theCMD="orapki wallet add -wallet . -trusted_cert -cert "$file" -pwd MyPassword1"
echo $theCMD
eval $theCMD
fi
done
Edit the “sqlnet.ora” file, adding in the following entry to identify the wallet. For read-only Oracle homes, this will be in the “/u01/app/oracle/homes/OraDB21Home1/network/admin/sqlnet.ora” file. For regular Oracle homes it will be in the “$ORACLE_HOME/network/admin/sqlnet.ora” location.
WALLET_LOCATION= (SOURCE=(METHOD=FILE)(METHOD_DATA= (DIRECTORY=/home/oracle/dbc/commonstore/wallets/ssl)))
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.
There is no need to bounce your Listener, so on to the next step.
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.
Create Access Control Entries (ACEs)
We need to create an Access Control Entry (ACE) so the C##CLOUD$SERVICE can access the cloud services.
Create a file called “/home/oracle/dbc/dbc_aces.sql” with the following contents. Edit the sslwalletdir setting if you have altered the location.
@$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=/home/oracle/dbc/commonstore/wallets/ssl
--
-- 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
Run script in the root container.
conn / as sysdba @@/home/oracle/dbc/dbc_aces.sql
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
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
verify the Installation
Create a file called “/home/oracle/dbc/verify.sql” with the following contents. Edit the wallet path and password as required.
-- 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=/home/oracle/dbc/commonstore/wallets/ssl
define sslwalletpwd=MyPassword1
-- 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;
Run the script. The script should produce the phrase “valid response”.
conn / as sysdba @/home/oracle/dbc/verify.sql
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.
List the Contents of a Bucket
This section of the article assumes you have an object storage bucket on Oracle Cloud and you’ve defined an Auth Token to access it. You can read how to create a bucket and an Auth Token in this article.
Create a test user.
conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba --drop user testuser1 cascade; create user testuser1 identified by testuser1 quota unlimited on users; grant connect, resource to testuser1;
Make sure the test user can create credentials and has access to the DBMS_CLOUD package.
grant create credential to testuser1;
grant execute on dbms_cloud to testuser1;
Connect to the test user and create a credential.
conn testuser1/testuser1@//localhost:1521/pdb1
# query
select credential_name,
username,
enabled
from user_credentials
order by credential_name;
begin
dbms_credential.drop_credential(
credential_name => 'oci_obj_credential');
end;
/
begin
dbms_credential.create_credential(
credential_name => 'oci_obj_credential',
username => 'xianfeng.liu@oracle.com',
password => '2uePE_Rd;YK>YxM_n;aS');
end;
/
Test Access
OK, using the details above we can now see objects in our Oracle Cloud Infrastructure (OCI) Object Store
We can now use the LIST_OBJECTS table function to get a list of objects in the bucket.
select object_name
from dbms_cloud.list_objects(
'oci_obj_credential',
'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/ocichina001/b/bucket-felix/o/');
OBJECT_NAME
--------------------------------------------------------------------------------
Image 930.png
SQL>
create or replace directory tmp_dir as '/home/oracle/tmp';
grant read, write on directory tmp_dir to testuser1;
expdp testuser1/OrcHuanying__123@db.sub03020226070.vcnb.oraclevcn.com:1521/pdb03.sub03020226070.vcnb.oraclevcn.com tables=emp credential=OCI_OBJ_CREDENTIAL dumpfile='https://objectstorage.ap-tokyo-1.oraclecloud.com/n/ocichina001/b/bucket-felix/o/exp%L.dmp' logfile=expdp_emp.log directory=tmp_dir exclude=statistics
