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.

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

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