Integrate Oracle Exadata Database Service on Dedicated Infrastructure and Azure Blob Storage

Log in to the Azure console to obtain information for the Azure Blob Storage from which the Oracle Exadata Database Service on Dedicated Infrastructure database will load and query data. This information is required to tell the DBMS_CLOUD package how to connect to the Azure Blob Storage objects.

Obtain the following details:

Azure Blob Credentials: Username and password to access Azure Blob Storage.
Azure Blob Storage Name: Name of the Azure Blob Storage where data is stored, usually in the format blobstoragename.blob.core.windows.net.
Azure Blob URL: URL for the Azure Blob Storage where data is stored, usually in the format https://blobstoragename.blob.core.windows.net/dirname/object_name.
Azure Blob Private Endpoint: IP address of the Azure Blob Storage private endpoint. If a private endpoint does not exist, create one, and note the IP address.

From the OCI menu, select Networking/DNS Management to create a Private DNS Zone. In the new Private DNS Zone, add a record for the Azure Blob Storage name from step 1, to resolve to the Azure Blob private endpoint IP address from step 1.

From the OCI menu, select Networking/DNS Management, and create a DNS Private View. Add the Private DNS Zone created in step 2 to this newly created DNS private view, and activate the changes.

From the OCI menu, select Networking/Virtual Client Network (VCN). Select the VCN created when the OracleDB for Azure for Oracle Exadata Database Service on Dedicated Infrastructure was provisioned (change the compartment if required). Select the DNS resolver link, then add the DNS Private View created previously to the DNS resolver.

This enables name and IP address resolution from Oracle Exadata Database Service on Dedicated Infrastructure to the Azure Blob Storage private endpoint.

Log in to one of the Oracle Exadata Database Service on Dedicated Infrastructure VM Cluster nodes to use the nslookup command to verify the hostname to IP address resolution is working for the Azure SQL Server hostname and private endpoint IP.

For example:

[opc@exa_dbnodex ~]$ nslookup blobstoragename.blob.core.windows.net
Server: 169.254.169.254
Address: 169.254.169.254#53
Non-authoritative answer:
Name: blobstoragename.blob.core.windows.net
Address: IP_address_of_Azure_Blob_private_endpoint
 [opc@exa_dbnodex ~]$

The variables in the example have the following values:

blobstoragename is the Azure Blob Storage name from step 1.
IP_address_of_Azure_Blob_private_endpoint is the IP address of the Azure Blob private endpoint from step 1.

Log in to the Oracle Exadata Database Service on Dedicated Infrastructure pluggable database to create or select a user to have the cloud_user role. Note the user's username and password. Users with the cloud_user role will have the privilege to use the DBMS_CLOUD package.

Install the DBMS_CLOUD package on the Oracle Exadata Database Service on Dedicated Infrastructure database 19c or newer by following the My Oracle Support article How to Setup and Use DBMS_CLOUD Package (Doc ID 2748362.1). See the Review Documentation section for details. Note the following when installing the DBMS_CLOUD package:

In the My Oracle Support article section Grant the minimal privileges to a user or role for using DBMS_CLOUD, use the username created in step 6.
In the My Oracle Support article section Create SSL Wallet with Certificates, this step will add root certificates to the SSL wallet. You can skip the creation of the wallet step because Oracle Exadata Database Service on Dedicated Infrastructure already has a wallet to manage SSL certificates (the Grid Wallet). You should add the root certificates to the Grid Wallet instead, which is located in /var/opt/oracle/dbaas_acfs/grid/tcps_wallets. You will need the Grid Wallet password to add the root certificates. See the My Oracle Support article Get Password of ASM and Wallets (Doc ID 2814361.1) in the Review Documentation section for details about how to obtain the Grid Wallet password.

Log in to the Oracle Exadata Database Service on Dedicated Infrastructure pluggable database to create an Azure credential using the DBMS_CLOUD package installed in the previous step.

[opc@exa-dbnodex ~]$ sudo su - oracle
Last login: Tue Sep 13 20:06:30 UTC 2022
[oracle@exa-dbnodex ~]$ . ./exa_dbname.env
[oracle@exa-dbnodex ~]$sqlplus
Username_From_Step_6/Password_From_Step_6@'(DESCRIPTION
=(ADDRESS=(PROTOCOL=TCP)(HOST=exascan.customersubnet1.cvcn.oraclevcn.com)
(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=pdbna
me.customersubnet1.cvcn.oraclevcn.com)(FAILOVER_MODE=(TYPE=select)(MET
HOD=basic))))'
SQL>BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'AZ_CRED',
username => 'Azure_Blob_Storage_Username_from_Step_1',
password => ‘Azure_Blob_Storage_Password_from_Step_1' );
END;
/

The variables in the example have the following values:

exadb_name is the name of the Oracle Exadata Database Service on Dedicated Infrastructure database.
Username_From_Step_6 is the username from step 6.
Password_From_Step_6 is the password from step 6.
pdbname is the name of the pluggable database.
Azure_Blob_Storage_Username_from_Step_1 is the Azure Blob Storage username from step 1.
Azure_Blob_Storage_Password_from_Step_1 is the Azure Blob Storage password from step 1.

After the Azure credential is created, create an external table to access the Azure Blob Storage object(s) as follows:

SQL>BEGIN
 DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
table_name =>'Table_Name',
credential_name =>'AZ_CRED',
file_uri_list => 'Azure_Blob_URL_From_Step_1',
format => json_object('delimiter' value ','),
column_list => 'Column1_Name Type, Column2_Name Type,
Column3_Name Type' );
END;
/

For example, if the Blob object to access is a comma separated text file with 3 columns, the command may resemble:

SQL>BEGIN
 DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
table_name =>'CHANNELS_EXT',
credential_name =>'AZ_CRED',
file_uri_list
=>'https://odsaexternaltables.blob.core.windows.net/data/channels.txt',
format => json_object('delimiter' value ','),
column_list => 'CHANNEL_ID NUMBER, CHANNEL_DESC VARCHAR2(20),
CHANNEL_CLASS VARCHAR2(20)' );
END;
/

Run a query to verify the Oracle Exadata Database Service on Dedicated Infrastructure pluggable database can query the tables created above.

For example:

select count(*) from Table_Name

Table_Name is the name of a table in your database to query.