Oracle Database Service for Azure – connecting Azure VM and Power App

I have connected a Database Admin Azure VM running Oracle’s SQL Developer (Windows version) and a Microsoft Power Platform application displaying Oracle’s HR demo schema (via on-premises data gateway on Azure VM connecting with Power Platform’s Oracle Premium Connector) to the same Oracle Database hosted on OCI. I used Oracle-Azure Cloud-Link by the fully automated Oracle Database Service for Azure in the background. In this article I am sharing my design, learnings and key takeaways from this exercise I enjoyed a lot.

Previously, I used Oracle Application Express for Oracle Databases. Oracle APEX is really cool, it is a rapid application development tool offered by Oracle, based on the underlying database schema.

When I saw Microsoft Power Platform, my first impression was that citizen developers will have the demand to use Oracle Databases. What is an on-premises data gateway? | Microsoft Learn solves this problem - with that, you can connect Microsoft Power Platform to Oracle Databases. However, latency is still there, so ideally you need to move the database to Azure and deploy that gateway in Azure too to eliminate latency. This is how to install such Install an on-premises data gateway | Microsoft Learn

Below is my updated design, on the left side you see Azure, on the right side you find Oracle Cloud. I am not using an on-premises datacenter in this demo/deployment.

I wanted to test both database Admin VM (light blue arrow) and a Power App (light green arrow) to connect to Oracle Base DB via OCI-Azure Cloud Link. They all worked very well :)

If you have Azure experience and are new to Oracle Cloud it will take some time to understand how similar purpose objects are called in OCI. For example, Azure Resource Group is called OCI Compartment. Networking foundations, such as Virtual Networks or Subnets are the same in each cloud. Policies on subnets, such as Ingress and Egress rules are automatically generated when you provision your first database.

I was testing several VM shapes to run Base Database. For me VM.Standard2.2 worked the best, it was quite fast and still affordable in price. I used the popular 19.15 database, Standard edition.

It took me some time to understand how **Database System** (VM shape), **Database Name** and **Pluggable Database Name** relate to each other.

I missed the idea of Pluggable Databases and Introduction to the Multitenant Architecture (oracle.com) but it is never too late to learn new things from Oracle :)

After selecting the Azure Virtual Network the only major (deployment stopper) mistake you can make is to use the wrong network and subnet mask

OCI CIDR is

a new network that is not existing in OCI and is not used in Azure
a new network that will be automatically created in OCI connecting Database System, Database, and Portable DB - you will connect to the portable database with your applications
ideally, a B class (20-bit block) network and the perfect subnet mask is /16 to utilize cloud link
be careful - it is not going to check if you enter a non-private network IP, for example, I was able to deploy with 172.32.0.0/16 which is obviously the wrong range (outside the private network range), and not part of the 20-bit block.

The Internet Engineering Task Force (IETF) has directed the Internet Assigned Numbers Authority (IANA) to reserve the following IPv4 address ranges for private networks:

RFC 1918 name	IP address range	Number of addresses	Largest CIDR block (subnet mask)	Host ID size	Mask bits	Classful description
24-bit block	10.0.0.0 – 10.255.255.255	16777216	10.0.0.0/8 (255.0.0.0)	24 bits	8 bits	single class A network
20-bit block	172.16.0.0 – 172.31.255.255	1048576	172.16.0.0/12 (255.240.0.0)	20 bits	12 bits	16 contiguous class B networks
16-bit block	192.168.0.0 – 192.168.255.255	65536	192.168.0.0/16 (255.255.0.0)	16 bits	16 bits	256 contiguous class C networks

Source: Private network - Wikipedia

Be careful, you can accidentally use non-private network IPs, 20-bit block range is 16-31 (172.16.0.0 – 172.31.255.255)

When you submit this wizard you can track what is going on using Tenancy Explorer and ORM Jobs.

Deployment jobs are running Terraform (Terraform by HashiCorp) code in OCI. This is how Cloud Link and DBS/DB/PDB get deployed.

I checked Terraform code deploys resources in OCI. Very interesting to see what’s getting deployed in the background. Remember, you can deploy this manually as well, but I think fully automated mode saves a lot of time, especially for proof of concept presales exercises.

This is an example of Security Rules in OCI - hosting Database System VM and Database Instance. Oracle DB is using TCP/1521*, and SSH 22 is allowed as well.

* An Oracle client connects to the server using the port address of the listener, which is normally defined as TCP port 1521 during Oracle installation. sqlnet service is defined as using TCP port 1521.

These are the security rules automatically deployed by the TF code above as part of the Base DB creation process. This is very nice, everything gets configured automatically. Well done!

You can also track the deployment on the “Oracle Database Service for Azure” portal.

If you make a mistake with network configuration deployment will fail at ~19 minutes. If you do everything right, **it will take 1 hour and ~33 minutes to complete the deployment.** That’s quite good considering/comparing Oracle Enterprise Linux and Oracle DB Instance semi-automated on-premises deployment task.

Again, the **best way to see what’s going on is to use the Tenancy Explorer in OCI** and observe objects in the mapped resource group.

It is very important to understand how Azure objects are mapped/mirrored in OCI. **Compartments are nested in OCI,** unlike Azure Resource Groups. **Under the root, you have the CloudLink_Azure. Below the CloudLink you have Subscription ID mapped and Resource Group mapped.**

When the deployment is completed, you can get the connection string to DB and PDB using both OCI and Azure-like portal.

I connected to the database instance. Please do not mix this with the Pluggable Database, that’s different. This is quite handy, you can get the connection string from both Oracle and Azure-like portal.

Let’s connect from Power Platform. In order to do that, I had to deploy On-premises data gateway on the Azure hosted Admin VM.

This error message means that I have no license for Power Platform :) OMG. I enabled E5 trial in my Azure Active Directory and assigned a license to the user configured in the onprem data GW.

Fun fact. It was the licensing issue and not the misconfiguration above. Without proper Power Platform licenses on-prem data GW will not work. And remember, Oracle Connector is a Premium Connector that needs to be covered by proper licensing.

After allocating the proper licenses it started to work.

Now I was able to add Oracle DB to Power Platform App

Remember, we are using the Pluggable Database connection string

What I did is quite simple, I updated a salary in HR.EMPLOYEES table for Steven King. COMMIT is needed to close the transaction :-)

And after refreshing the data source the Power App grid immediately shows the updated data. That’s it. Simple and easy. I am using Azure Admin VM and Microsoft Cloud Power App with OCI database backend - and everything works nice and fast via free and low latency Oracle-Azure Cloud-Link. Nice!

I was planning more sophisticated experiments such as testing database latency and comparing with same cloud (Oracle DB in Azure), other clouds (Oracle DB in AWS) or on-premises based Oracle Databases, etc. but this is quite time consuming, so I decided to drop this idea, for now :)

This is a nice screenshot of Tenancy Explorer - VM/DB and Dynamic Routing Gateway - both automatically provisioned.

When you are done with the demo, make sure to delete (terminate) the same way as you created. So use the Azure-like portal and never delete the managed objects from OCI directly.

You can check and confirm the deletion in OCI, but again, delete must be invoked from Azure-like portal not OCI directly.

When it comes to price, I was mainly paying for Oracle Standard Database License for 2 days.

Yellow is Database cost, compute is green, almost nothing.

You can check the billing on the Azure-like portal. Before you get scared on the 2 days consumption (7224 USD = 2 970 924 HUF), it shows probably every currency in USD without converting. So this is 7224 HUF and not USD. There is quite a big difference. I am not yet an enterprise customer of Oracle Hungary :)

The OCI portal shows consumption in a proper currency.

And here is the real invoice - main usage is the Database, just like the cost analysis showed before.

Key takeaway, there is no charge on Azure side. The fantastic time saver full automation is free, the cloud-link is free and provides low latency, it is easy to use. Well done, I can’t wait to see how this evolves over time.