Demystifying DBMS_CLOUD on OCI DBCS.
Oracle Autonomous Database includes the DBMS_CLOUD PL/SQL package to extend the functionality of Oracle Database to support autonomous and cloud operations but a DBCS system will not have this package installed by default and needs to be installed manually if required.
DBMS_CLOUD package comes with various sub programs that comes handy to use when we need to use this package , be it could be accessing the files stored in OCI OSS or if there is a need to put the files from your database into cloud storage.
Let’s start by understanding in brief as how easily we can install it on database’s where it doesn’t comes by default and what all we can do using this useful package.
To start with navigate to metalink note 2748362.1 and create a script using the sql’s provided. The intention of this script is to create a common user which will be the owner of DBMS_CLOUD package. The required privileges will be granted to this common user and dbms_cloud specific objects, packages and package bodies will get created.
Once the script gets executed successfully, we should see DBMS_CLOUD object in cdb_objects.
Currently Oracle does not ship the certs as part of RUs, so we need to download the necessary certificates from https://objectstorage.us-phoenix-1.oraclecloud.com/p/QsLX1mx9A-vnjjohcC7TIK6aTDFXVKr0Uogc2DAN-Rd7j6AagsmMaQ3D3Ti4a9yU/n/adwcdemo/b/CERTS/o/dbc_certs.tar
We’ll be adding the downloaded certs to the trusted certificates.
Do note that by default an Oracle database does not allow any outside communication, so you need to enable the appropriate Access Control Entries. In case your database is behind a firewall, you need to provide the information about your Internet Gateway and configure the Access Control Entries appropriately.
A wrapper script provided in referenced metalink id needs to be executed that will enabled the appropriate ACE’s.
A proper installation of DBMS_CLOUD should show a valid response when trying to verify the access to Object Storage.
And to provide all the functionality of DBMS_CLOUD to a user or role you need to enable the appropriate Access Control Entries, similar to the ones of DBMS_CLOUD.
Lastly you just need to create the credentials as you would do in any cloud based database and verify object storage access using DBMS_CLOUD.LIST_OBJECTS package.
And once DBMS_CLOUD package gets installed successfully , it can be optimized by using versatile sub programs that it has to offer as seen below.
If you would like to read more on this topic you can find out at https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/dbms-cloud-subprograms.html#GUID-CC237BEC-92CF-4051-82BE-76ADBB57F4B5.