DBMS_CLOUD : Installation on 19c and 21c On-Prem Databases

This article describes how to install the DBMS_CLOUD package in on-prem 19c and 21c databases. This package is already installed in databases on the Oracle Cloud.

◉ Reference

This article is a run through of the installation instructions in this MOS note.

- How To Setup And Use DBMS_CLOUD Package (Doc ID 2748362.1)

You should always refer to this note, as the instructions may change over time.

◉ Install DBMS_CLOUD

Create a directory to hold the installation files and an SSL wallet.

mkdir -p /home/oracle/dbc/commonstore/wallets/ssl

Create a file called "/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

Run the script in all containers including this seed. This will make new containers already include the DBMS_CLOUD package installation.

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl \

  -u sys/SysPassword1 \

  --force_pdb_mode 'READ WRITE' \

  -b dbms_cloud_install \

  -d /home/oracle/dbc \

  -l /home/oracle/dbc \

  dbms_cloud_install.sql

Check the log files once the command is complete, to make sure it has run in all containers.

◉ Create a 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

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)))

◉ 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

◉ 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

◉ 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.

- Oracle Cloud Infrastructure (OCI) : Create an Object Storage Bucket

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

begin

  dbms_credential.drop_credential(

    credential_name => 'obj_store_cred');

end;

/

begin

  dbms_credential.create_credential(

    credential_name => 'obj_store_cred',

    username        => 'me@example.com',

    password        => 'my-auth-token');

end;

/

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(

         'obj_store_cred',

         'https://objectstorage.uk-london-1.oraclecloud.com/n/{my-namespace}/b/ob-bucket/o/');

OBJECT_NAME

--------------------------------------------------------------------------------

Image 930.png

SQL>

Source: oracle-base.com