📋Steps in this guide1/7

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

This article describes how to install the DBMS_CLOUD package in on-prem 19c, 21c and 23ai databases.

oracle 21cconfigurationintermediate

by OracleDba

35 views

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 (23.7 onward)

From Oracle 23ai (23.7) the process to install the package has changed, and is described in the documentation here . First we create the schema. Next we install the packages. Now jump to the Create a Wallet section.

Code/Command (click line numbers to comment):

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl \
  -u sys/SysPassword1 \
  --force_pdb_mode 'READ WRITE' \
  -b dbms_cloud_install \
  -d $ORACLE_HOME/rdbms/admin/ \
  -l /tmp \
  catclouduser.sql

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl \
  -u sys/SysPassword1 \
  --force_pdb_mode 'READ WRITE' \
  -b dbms_cloud_install \
  -d $ORACLE_HOME/rdbms/admin/ \
  -l /tmp \
  dbms_cloud_install.sql

Install DBMS_CLOUD (pre-23.7)

Create a directory to hold the installation files and an SSL wallet. Create a file called "/home/oracle/dbc/dbms_cloud_install.sql" with the following contents. If you are installing into a database using the non-CDB architecture, you will need to switch the username from to . Run the script in all containers including this seed. This will make new containers already include the package installation. Check the log files once the command is complete, to make sure it has run in all containers.

Code/Command (click line numbers to comment):

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

@$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

$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

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

Code/Command (click line numbers to comment):

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 MyPassword1 -auto_login

for i in `ls ./*cer`
do
orapki wallet add -wallet . -trusted_cert -cert $i -pwd MyPassword1
done

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 can access the cloud services. Create a file called "/home/oracle/dbc/dbc_aces.sql" with the following contents. Edit the setting if you have altered the location. If you are installing into a database using the non-CDB architecture, you will need to switch the username from to . Run script in the root container.

Code/Command (click line numbers to comment):

@$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=
--
-- UNCOMMENT AND SET THE PROXY SETTINGS VARIABLES IF YOUR ENVIRONMENT NEEDS PROXYS
--
-- define proxy_uri=
-- define proxy_host=
-- define proxy_low_port=
-- define 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

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. If you are installing into a database using the non-CDB architecture, you will need to switch the username from to . Run the script. The script should produce the phrase "valid response".

Code/Command (click line numbers to comment):

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

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. Make sure the test user can create credentials and has access to the package. Connect to the test user and create a credential. This can use the auth key or API key method. We can now use the LIST_OBJECTS table function to get a list of objects in the bucket. For more information see: - DBMS_CLOUD Family of Packages - 23ai - How To Setup And Use DBMS_CLOUD Package (Doc ID 2748362.1) - DBMS_CLOUD Package - Oracle Cloud Infrastructure (OCI) : Create an Object Storage Bucket - Oracle Cloud : Autonomous Database (ADW or ATP) - Load Data from an Object Store (DBMS_CLOUD) Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

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;

grant create credential to testuser1;
grant execute on dbms_cloud to testuser1;
-- Ignore any failures of below commands.
grant execute on dbms_cloud_pipeline to testuser1;
grant execute on dbms_cloud_repo to testuser1;
grant execute on dbms_cloud_notification to testuser1;

conn testuser1/testuser1@//localhost:1521/pdb1

-- Auth Key
begin
  dbms_cloud.drop_credential(
    credential_name => 'obj_store_cred');
end;
/

begin
  dbms_cloud.create_credential(
    credential_name => 'obj_store_cred',
    username        => 'OracleIdentityCloudService/[email protected]',
    password        => 'my-auth-token');
end;
/


-- API Key
begin
  dbms_cloud.drop_credential(
    credential_name => 'obj_store_cred');
end;
/

begin
  dbms_cloud.create_credential(
	credential_name => 'obj_store_cred',
	user_ocid       => 'ocid1.user.oc1..aaaaaaaaveuky????',
	tenancy_ocid    => 'ocid1.tenancy.oc1..aaaaaaaaey????',
	private_key     => '-----BEGIN PRIVATE KEY-----
????
-----END PRIVATE KEY-----
OCI_API_KEY',
	fingerprint     => 'b9:1d:51:69:3f:50:9b:a2:4b:??:??:??:??:??:??:??');
end;
/

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!