Installing DMBS_Cloud to local Oracle DB on Windows and read Object Storage

I currently have Oracle DB installed on my Windows machine locally and it’s sort of my development playground that I do experiments on. I was trying to experiment with the DBMS_Cloud package — namely to connect to files in OCI object storage and read them into my database as external tables.

This functionality doesn’t come natively with a local Oracle DB and DBMS_Cloud package must be installed. The official documentation is linked here: https://support.oracle.com/epmos/faces/DocContentDisplay?_afrLoop=72889481607664&id=2748362.1&_afrWindowMode=0&_adf.ctrl-state=uxtaf6sgf_160

1) Missing files

First of all, Oracle tells us to run this script through catcon.pl:

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

Immediately I already ran into my first problem — I do not have all those files Oracle lists up there un my rdbms/admin/ directory.

I checked my Oracle version by doing

SELECT * FROM v$version;

I have 19.3 — which is pretty much already their latest stable release version for Windows listed on their downloads page:

Unfortunately, according to the official docs, we need “Oracle Database 19c beginning with 19.9 and in Oracle Database 21c beginning with 21.3”, which thankfully we can find at the lower part of the page…

I’ve downloaded the 21c package, unzipped it — and located all the files that were needed in the script above, e.g. dbms_cloud_task_catalog.sql, prvt_cloud_core.plb …etc.

I̶ ̶d̶i̶d̶n̶’̶t̶ ̶w̶a̶n̶t̶ ̶t̶o̶ ̶b̶o̶t̶h̶e̶r̶ ̶u̶p̶g̶r̶a̶d̶i̶n̶g̶ ̶m̶y̶ ̶d̶a̶t̶a̶b̶a̶s̶e̶ ̶s̶o̶ ̶I̶ ̶s̶i̶m̶p̶l̶y̶ ̶c̶o̶p̶y̶ ̶a̶n̶d̶ ̶p̶a̶s̶t̶e̶d̶ ̶t̶h̶e̶ ̶f̶i̶l̶e̶s̶ ̶t̶h̶a̶t̶ ̶I̶ ̶n̶e̶e̶d̶e̶d̶ ̶a̶n̶d̶ ̶p̶a̶s̶t̶e̶d̶ ̶i̶t̶ ̶b̶a̶c̶k̶ ̶i̶n̶t̶o̶ ̶m̶y̶ ̶e̶x̶i̶s̶t̶i̶n̶g̶ ̶O̶r̶a̶c̶l̶e̶’̶s̶ ̶i̶n̶s̶t̶a̶l̶l̶a̶t̶i̶o̶n̶’̶s̶ ̶r̶d̶b̶m̶s̶/̶a̶d̶m̶i̶n̶ ̶f̶o̶l̶d̶e̶r̶.̶

Upgrade your database to 21c.

That solves our missing files problem.

2) Running the script in catcon.pl

Now we can go and execute the script that Oracle suggests us to use:

$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

This turned out not to be that simple on Windows… As a complete noob at bash scripting, I was going back and forth between Command Prompt and Powershell trying to make it work.

The environment variable $ORACLE_HOME was the part that gave me the most trouble. One of the reasons is becasue I have two installations of Oracle DB on my computer — so I had to be very specific in which Oracle DB I was pointing to.

In fact, I just found out today, that in Command Prompt, environment variables are written as %ORACLE_HOME% . In Powershell, environment variables are written as $Env:ORACLE_HOME .

If you are a complete noob like me, here are some steps that may possibly help you out:

1. Go to the Windows “System Properties” -> Advanced -> “Environment Variables” dialog and make sure that you have
   - a variable named ORACLE_HOME that points to your Oracle installation folder
   - a variable named Path (you should already have one) and append YOUR_ORACLE_DIRECTORY/bin to it
   I’m pretty sure that whether it’s in User variables (top part) or System variables (bottom part) doesn’t really matter.

2. Then follow instructions on this page:

To configure operating system environment variables for your database instance on Windows systems:

Use either regedit or the Oracle Administration Assistant for Windows to make sure the ORACLE_HOME and ORACLE_SID parameters are set to the correct values in the HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_HOME_NAME registry subkey.

3. Now that you set up your environment variables, open Windows Powershell.

Try

echo $Env:ORACLE_HOME

This should return your desired Oracle db directory location. If not, that means you didn’t successfully set the environment variable for ORACLE_HOME. Go back and try to focus on fixing that first.

Once you have that returning the correct directory, I personally chose to execute the command from the Oracle home directory — so just

cd $Env:ORACLE_HOME

Then run:

perl/bin/perl rdbms/admin/catcon.pl -u sys/YOUR_PASSWORD_HERE --force_pdb_mode 'READ WRITE' --sqlplus_dir C:\OracleApp\WINDOWS.X64_193000_db_home\bin -b dbms_cloud_install_log -d rdbms/admin -l rdbms/admin/tmplogs dbms_cloud_install.sql

Some notes on the above:

• the perl/bin/perl part may give you an error about how they don’t recognize the command. It just means that you need to add your Perl executable to the Path variable in your environment variables. So just append C:\MyOracleDirectory\perl\bin to the Path variable. And it should work.
• I added --sqlplus_dir because I had two Oracle DB instances installed on my computer. So it was important for me to specify the sqlplus directory
• -b simply means the base name of your log files. You can name this anything you want
• -d refers to the directory of your sql files, e.g. where dbms_cloud_install.sql is located
• -l is simply the directory where you want to store the logs

4. Verify that it ran successfully.

First of all go and read the logs (it’s located in the folder you specified above with the -l command). Make sure there were no errors — if there were errors, debug and rerun.

Afterwards, run this in your database:

select con_id, owner, object_name, status, sharing, oracle_maintained from cdb_objects where object_name = 'DBMS_CLOUD' order by con_id;

You should get something like this.

select owner, object_name, status, sharing, oracle_maintained from dba_objects where object_name = 'DBMS_CLOUD'

3) Create SSL Wallet with Certificates

So this is where what I did heavily differed from the documentation. Instead of using orapki to create the wallet — which I have tried but was never able to get it to work; I kept running into “Failure to open file”.

I never managed to resolve the problem, despite granting the system permissions to the wallet to every user I can imagine. In the end, the only thing that fixed it was to use Oracle’s Wallet Manager GUI tool to create the wallet.

You should be able to find it under Start -> Programs -> Oracle -> Wallet Manager.

Open it and create a new wallet. Enter a wallet password. Leave wallet type as “standard”.

Then choose to “Import Trusted Certificate”:

Download the certs file from the official documentation, unzip it, and put it in a directory.

Choose the three certificates laid out in the documentation:

• Verisign.cer
• BaltimoreCyberTrust.cer
• DigiCert.cer

Then save your wallet. Note down the directory in which it’s saved at. You’re going to need it later.

Then make sure to check “Auto-login”.

4) Configure your Oracle environment to use the new SSL wallet

Since I’m only on a local installation on Windows, I got confused when they mentioned all this stuff about servers…

To have your SSL wallet taken into effect you need to point to the newly created ssl wallet for your Oracle installation by adding it to your sqlnet.ora on the Server side. If you are on a RAC installation then you have to adjust this on all nodes.

For cloud installations without Grid infrastructure, the default location of this file is $ORACLE_HOME/network/admin.
For cloud installations with Grid infrastructure, the default location of this file is $GRID_HOME/network/admin.
If you already had a wallet for SSL certificates and added the certificates to the existing one then this step is not necessary.

Server side? I don’t have a server…

After some fooling around, I finally figured out “Server side” only refers to the $ORACLE_HOME/network/admin directory. You should find sqlnet.ora file in there.

So basically open up that file and paste in at the bottom the path in which your wallet above was saved at

WALLET_LOCATION=
(SOURCE=(METHOD=FILE)(METHOD_DATA=
(DIRECTORY=C:\OracleApp\WINDOWS.X64_193000_db_home\owm\wallets\wynnelo)))

5) Configure the Database with ACEs for DBMS_CLOUD

For this part, I was initially very confused about where to run this script… apparently, what you’re supposed to do is copy this script below (I’ve removed the commented parts about proxies) and save it as a file called dbc_aces.sql . You should save dbc_aces.sqlfile in the same directory as your sqlplus.exe , which in my case was the $ORACLE_HOME/bin folder.

Remember to replace the sslwalletdir path with your own path.

@$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=C:\OracleApp\WINDOWS.X64_193000_db_home\owm\wallets\wynnelo
-- 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
-- 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''';
end if;
end;
/
@$ORACLE_HOME/rdbms/admin/sqlsessend.sql

Then open up sqlplus.exe. When it asks you for a username, type in sys/<password> as sysdba

Then run:

@@dbc_aces.sql

Verify that the above steps ran correctly by running

select * from database_properties where property_name in ('SSL_WALLET','HTTP_PROXY');

6) Verify Configuration of DBMS_CLOUD

From the official docs:

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

Copy and paste the above and save it as a script in $ORACLE_HOME/bin or whichever directory your sqlplus.exe is at.

Then open sqlplus, login again as sysdba, and run the above script by typing @@your_script.sql

You should see “valid response” in the console output if you’ve done everything correctly. PHEW.

It actually took me several hours to do this becasue I could never get orapki to work.

7) Grant the minimal privileges to a user for using DBMS_CLOUD

For this step, I just copied the script from the documentation and modified the username from SCOTT to my own username. I ran this as sys in Oracle SQL Developer.

set verify off
-- target sample user
define username='SCOTT'
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 user
REM for creation of external tables, e.g. DBMS_CLOUD.CREATE_EXTERNAL_TABLE()
grant CREATE TABLE to &username;
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 &username;
REM
grant EXECUTE on dbms_cloud to &username;

8) Configure ACEs for a user to use DBMS_CLOUD

Same with this step. I just copied the script below and ran it in Oracle SQL Developer. Make sure you input your own SSL wallet directory:

-- target sample user
define clouduser=SCOTT
-- CUSTOMER SPECIFIC SETUP, NEEDS TO BE PROVIDED BY THE CUSTOMER
-- - SSL Wallet directory
define sslwalletdir=<Set SSL Wallet Directory>
-- 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;

9) Create credentials and verify

Next, to actually test if we can connect to Object Store, we need to create an auth token.

I did not really want to use my root account — so I’m going to create an IAM user and create an auth token for that user and use that user instead. So first I go to Identity & Security from the navigation menu:

Go to the Users page and Create User:

I created another user and put them under the Administrator group so they would also have most admin rights. Then go to the user’s details page and click Auth tokens.

Then just click “Generate Token”, enter some description for the token, and click “Generate Token”.

There! We got our token string.

Now run this, filling in credential_name with any thing you want to name your credential. Username would be the username (which usually is an email) of the user you just created. Then password is the token string you just generated.

BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'your credential name',
username => 'OCI within your tenancy',
password => 'auth token generated for OCI user'
);
END;
/

Now when we run the below

select * from dbms_cloud.list_objects(<'CredentialName'>,'https://objectstorage.<region>.oraclecloud.com/n/<ObjectStorageNameSpace>/b/<BucketName>/o/');

we should be able to see the list of objects in our bucket!

If you see “certificate validation failure” — you likely messed up in the granting permissions part. As in the user you’re trying to run the script with probably can’t access your wallet. Try running steps 7 and 8 again.

Troubleshooting

1. Cannot log into databases

After all the setup above, I found that I couldn’t log into my databases anymore. I got an error message that says the network adaptor could not establish the connection Vendor code 17002 .

I googled around and it was suggested it’s becasue my listener was down, and I went to Start -> Services -> OracleOraDB19Home1TNSListener and found that it indeed was down.

I tried to start it up from right-clicking and clicking start, but it told me that it started then stopped again due to some error.

I went to command prompt and tried to start it up from there by typing: lsnrctl start / lsnrctl status . It tells me that

TNS-12560: TNS:protocol adapter error
 TNS-00583: Valid node checking: unable to parse configuration parameters

After some more googling, I figured out it’s because it can’t parse the configuration parameters in my network/admin/sqlnet.ora file.

Apparently we couldn’t just copy and paste the configuration given by the documentation above in step 4. We had to format it with the proper indentation and lines:

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = C:\OracleApp\WINDOWS.X64_193000_db_home\owm\wallets\wynnelo)
    )
  )

After fixing that, I was finally able to start my listener.