Description
DBMS_CLOUD is the Oracle package that lets the database connect to cloud object storage (OCI, Amazon S3, Azure Blob Storage) to create credentials, query external tables over cloud files, and bulk-load data with DBMS_CLOUD.COPY_DATA.
On a DBCS 19c database, the database is a multitenant container database (CDB) made up of a root container (CDB$ROOT), one or more pluggable databases (PDBs), and a seed PDB (PDB$SEED). The install must run in all of these containers so that DBMS_CLOUD is available in every PDB, including any new PDBs created afterward.
Step 1 — Create Working Directories
As the oracle user, create a directory for the install files and wallet:
| mkdir -p /home/oracle/dbc/commonstore/wallets/ssl |
Step 2 — Create the DBMS_CLOUD Install Script
Create /home/oracle/dbc/dbms_cloud_install.sql with the following contents:
| @$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 |
Step 3 — Run the Install in All Containers
Run the script across CDB$ROOT, all PDBs, and PDB$SEED using catcon.pl:
| $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl \
-u sys/<sys_pwd> \ –force_pdb_mode ‘READ WRITE’ \ -b dbms_cloud_install \ -d /home/oracle/dbc \ -l /home/oracle/dbc \ dbms_cloud_install.sql |
Replace <sys_pwd> with the actual SYS password. Check the log files afterward to confirm it ran successfully in every container:
Step 4 — Create an SSL Wallet
Download the certificate bundle and load it into a new wallet:
| mkdir -p /home/oracle/dbc/commonstore/wallets/ssl
cd /home/oracle/dbc/commonstore/wallets/ssl wget https://objectstorage.us-phoenix-1.oraclecloud.com/p/KB63IAuDCGhz_azOVQ07Qa_mxL3bGrFh1dtsltreRJPbmb-VwsH2aQ4Pur2ADBMA/n/adwcdemo/b/CERTS/o/dbc_certs.tar tar -xvf ./dbc_certs.tar orapki wallet create -wallet . -pwd <wallet_pwd> -auto_login for i in `ls ./*cer` do orapki wallet add -wallet . -trusted_cert -cert $i -pwd <wallet_pwd> done |
Step 5 — Grant Network Access (ACEs)
Create /home/oracle/dbc/dbc_aces.sql:
| @$ORACLE_HOME/rdbms/admin/sqlsessstart.sql
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 — 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));
— 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 if sys_context(‘userenv’, ‘con_name’) = ‘CDB$ROOT’ then execute immediate ‘alter database property set ssl_wallet=”&sslwalletdir”’; end if; end; / @$ORACLE_HOME/rdbms/admin/sqlsessend.sql |
Run this once, in the root container:
| conn / as sysdba
@@/home/oracle/dbc/dbc_aces.sql |
Step 6 — Verify the Installation
Create /home/oracle/dbc/verify.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 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 it from the root container:
| conn / as sysdba
@/home/oracle/dbc/verify.sql |
A successful result prints valid response.
