DBMS_CLOUD Package for Oracle Databases
DBMS_CLOUD package is cloud native package developed for Oracle Database to leverage Oracle Cloud Infrastructure (OCI). The following blog will describe steps to install the package manually on the Oracle Database 19.10 and above. This package is natively deployed on Autonomous database and you find subprograms here.
Step 1 : Create a script called dbms_cloud_install.sql which will consist of the code that is essential to install dbms_cloud which is owned by schema C##CLOUD$SERVICE. The following steps will create users and grant privileges. By default, this user is locked and no connections can be made.
@$ORACLE_HOME/rdbms/admin/sqlsessstart.sqlset 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.sqlREM 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.plbREM 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.sqlalter session set current_schema=sys;@$ORACLE_HOME/rdbms/admin/sqlsessend.sql
Step 2: Run the script using catcon.pl that will enable you to execute scripts at Container and Pluggable database at once.
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u sys/<your_sys_password> --force_pdb_mode 'READ WRITE' -b dbms_cloud_install -d /home/oracle/dbc -l /home/oracle/dbc dbms_cloud_install.sql
Step 3: Verify the installed package by running the below code snippet. For errors check the log file
REM from within ROOT to see all containers
select con_id, owner, object_name, status, sharing, oracle_maintained from cdb_objects where object_name = 'DBMS_CLOUD' order by con_id;REM within an individual container only
select owner, object_name, status, sharing, oracle_maintained from dba_objects where object_name = 'DBMS_CLOUD'
Step 4: You will need to create a wallet to securely access the OCI APIs. Currently the certificates are unavailable, so you’ll have to manually download the certificates from here
wget https://objectstorage.us-phoenix-1.oraclecloud.com/p/QsLX1mx9A-vnjjohcC7TIK6aTDFXVKr0Uogc2DAN-Rd7j6AagsmMaQ3D3Ti4a9yU/n/adwcdemo/b/CERTS/o/dbc_certs.tar
Step 5: Move the certificates to wallet location (if you have downloaded it at a different location)
mv ~/dbc/dbc_certs.tar /opt/oracle/dcs/commonstore/wallets/ssl
Step 6: Untar the certificates tar file
tar -xfv dbc_certs.tar
Step 7: Once the untar is complete create the wallet and add the certificates to the wallet
$ orapki wallet create -wallet . -pwd <PASSWORD> -auto_login$ orapki wallet add -wallet . -trusted_cert -cert /opt/oracle/dcs/commonstore/wallets/ssl/VeriSign.cer -pwd <PASSWORD>$ orapki wallet add -wallet . -trusted_cert -cert /opt/oracle/dcs/commonstore/wallets/ssl/BaltimoreCyberTrust.cer -pwd <PASSWORD>$ orapki wallet add -wallet . -trusted_cert -cert /opt/oracle/dcs/commonstore/wallets/ssl/DigiCert.cer -pwd <PASSWORD>
Step 8: Once the wallet is added, create an entry in sqlnet.ora file to use the wallet file
Step 9: Create access control entries to allow access to OCI resources via https calls and wrap the below code in dbc_aces.sql file and execute as SYSTEM or SYS
@$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
Step 10: Once the code setup is complete you can test the working by wrapping the test code in a sql script and run the code as SYSTEM or SYS
-- 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;
Step 11: Once the code is configured and tested create users and roles that can utilize dbms_cloud package
In the case below, we create a role called CLOUD_USER and assign user SCOTT to it (which is not mandatory, you can provide necessary grants to the user directly)
set verify off-- target sample role
define userrole='CLOUD_USER'-- target sample user
define username='SCOTT'create role &userrole;
grant cloud_user 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 userREM 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;
Step 12: Provide necessary access to role CLOUD_USER which in turn will provide access to SCOTT to make successful https calls to OCI
@$ORACLE_HOME/rdbms/admin/sqlsessstart.sql-- target sample role
define cloudrole=CLOUD_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
Step 13: Once the setup is complete, please verify by making making a request to OCI by setting up your credentials and calling subprogram like list_objects from object storage
Now you have successfully setup dbms_cloud package and you can find further references in the mos note. You can leverage the subprograms and develop cloud native applications from your database and utilize cost efficitent Object Store to store perform cold storage of data in form flat files and read as Hybrid Tables. Stay tuned for more database related development on Oracle Cloud!
About Author:
Phani Turlapati is a Cloud Engineer at Oracle Inc. He’s been with Oracle for over 4 years. He started out at Solution engineering hub at Oracle focusing on various design solutions to help customer to value-add to their businesses. He’s a part of Database Team focusing on all things database. Prior to Oracle he had worked as Software Engineer developing application on Java Platform.
The blog is my view and is no way related to Oracle
Feel free to comment on the above post. Your feedback is valuable!
