Back to the basics: Tracking Cloud Usage and Cost with Autonomous Database Using SQL

This was long time in my waiting list, "How do I track usage/cost of my cloud resources?" A very common question that I hear a lot. Let me walk through the options I know:

1 I can use cost analysis tool. It is very handy for visualizations, I can add filters, change grouping dimensions. I can have daily and monthly reports including forecast for the interested period. I can also save my reports, download report output in different formats. I can even schedule reports and it will be created under a bucket. Down sides are: it is built for visual consumption, extracting data is manual, I can't go beyond one year.

2 I can use cost and usage reports. They are csv files generated every six hours in an Oracle owned bucket but can be accessed with some cross tenancy configuration. These files are retained for one year. See official documentation for details. I need to find other tools to import and analyze data in csv files. Here is a really good example for this purpose.

3 I can also use the REST API to get a Usage Summary. Similar is also available in cli. Depending on the requirement this might be a good fit, but most of the time it is too much for most of the customers.

4 I can configure an Autonomous Database and use it to Track Oracle Cloud Infrastructure Resources, Cost and Usage Reports with Autonomous Database Views . I am going to focus on this because with minimal effort it can deliver great value. I can query data with simple sql, integrate it into any reporting or monitoring tool and I can go beyond one year limit by storing csv report data inside my database. Here I will list steps and some sample scripts:

a There are some prerequsite steps that need to be completed. You can check official documentation or my other blog post where I discuss resource principals to access OCI resources.

Note:The resource principal token is cached for two hours. Therefore, if you change the policy or the dynamic group, you have to wait for two hours to see the effect of your changes. This note is from documentation .

b Once the policies and resource principals are in place, I can use resourse views. There are multiple views but I am interested in two of them: OCI_COST_DATA and OCI_USAGE_DATA. I didn't check the source but I am guessing PIPELINED functions and/or external tables are involved. I could just use the views but there are two problems I need to solve: running query on the view is slow, and underlying data is changing, data older than one year will vanish. For this reason I am going to create a materialized view based on an existing table. This temporary table will be refreshed every 6 hours and in an another table I will be accumulating all the data. I am creating primary keys to detect the difference for each run.

c I am creating a procedure which will refresh the temp table with latest data, merge new rows into actual table, then schedule the next run.

d I just need to execute the procedure once, then the ball will be rolling on its own.

e DBMS_SCHEDULER job details can be tracked with the following views.

f Finally I will have all the data accumulated in my table, even long after the csv usage/cost files are deleted. Here are some SQL queries to start with.

Different Ways to Access Cloud Resources from Autonomous Database

When working with DBMS_CLOUD package or cloud REST APIs , I need database instance to be authenticated and authorized. Mainly there are two ways of doing this.

1I can use my own credentials or any IAM users credentials. For this purpose I need to use DBMS_CLOUD.create_credential procedure that comes in three different signatures.

aI can create an Auth token from console or using cli

then using this token and my user I can create a credential. Just to avoid confusion with below script, I use my email address as username in my tenancy.

bAnother way is to introduce my API signing RSA keys to OCI, then use it to create a credential. For generating my own key pair I can use openssl as described here in official documentation . I can also use the console which can generate the keys for me and I can download it. Using console I can upload my existing keys too.

After the API key added to OCI, console will display a configuration that can be used with SDK, CLI or REST calls.

CLI doesn't offer a command for adding API keys but I can always use REST API with http raw request, again response will display required information to use API key with SDK and CLI

Note:Use \n as new line feed for formatting your encoded public/private key

Now I can use a different version of create_credential procedure

Note:Both credentials (Auth Token and API Key) are directly linked to my OCI IAM user.

2I can also use Resource Principals to authorize my ATP instance. Previous method is tied to an IAM user (notice both Auth Token and API Key are created under user), resource principal uses Dynamic Groups to identify the instance and IAM no user is required.

aFirst I need a Dynamic Group to identify my instances. I generally use tagging, but sometimes allowing all autonomous instances is also fine.

bThen with a policy I grant priviliges to the members of that dynamic group

Note:The resource principal token is cached for two hours. Therefore, if you change the policy or the dynamic group, you have to wait for two hours to see the effect of your changes. This note is from documentation .

Here is the complete list of cli commands with some outputs for the same purpose:

cAnd I connect to the database and enable Resource Principal to Access Oracle Cloud Infrastructure Resources .

Testing

1I can see that my credential is visible and enabled in all_credentials. For testing I am just listing objects under an object storage bucket

2I can list objects under a bucket using any of the credentials.

Here is some SQL for testing

Putting it altogether: How to deploy scalable and secure APEX on OCI

Oracle APEX is very popular, and it is one of the most common usecases that I see with my customers. Oracle Architecture Center offers a reference architecture for this purpose: Deploy a secure production-ready Oracle Autonomous Database and Oracle APEX application . If you are comfortable with terraform or willing to learn it, I would definitely recommend using it. Even if it doesn't fit your requirements entirely it is a good starting point.

Having said that, I wanted to crack it open and see what's in it (I know typical boy's fun...), and in the end I come up with a series of blog posts while building the reference architecture piece by piece. Good for understanding what is under the hood and excellent for showing the value of terraform after doing all the work manually.

So here I start with the final architecture. I will explain the components and provide the links while doing so to help you build your own.

Quick links to the posts in the series

Part 1: Accessing Autonomous Database over Private Endpoint using SQL Developer
Part 2: Installing Customer Managed ORDS on Compute Instance for Autonomous Database
Part 3: Serving APEX in Private Subnet behind Public Load Balancer
Part 4: Securing APEX Admin Resources with Load Balancer
Part 5: Autoscaling ORDS servers in private subnet behind public load balancer

1Backbone of everything is Oracle database for APEX applications. I have an Autonomous database instance with Transaction processing workload type with autoscaling enabled. It is deployed with a Private Endpoint in a private subnet. You can check official documentation for creating one. For accessing your ATP instance you can see Part 1: Accessing Autonomous Database over Private Endpoint using SQL Developer

2Although ATP comes with Oracle managed ORDS, I want to install my own ORDS server on compute vm. In Part 2: Installing Customer Managed ORDS on Compute Instance for Autonomous Database I install and configure java and ORDS, also do the required networking configuration.

3For improving security posture, both database endpoint and ORDS instance is placed in a private subnet. For exposing APEX application I follow the steps in Part 3: Serving APEX in Private Subnet behind Public Load Balancer . This part is all about load balancer configuration, backend health check, SSL termination and troubleshooting connection issues. It can be helpful for any kind of load balancer / application configuration and problem solving.

4In a real life deployment, I need to find a way to access admin resources yet be able to protect them from public internet access. For this purpose, I am securing some URLs with load balancer redirect rules as load balancer sits in between as a reverse proxy. I can still access those admin resources through private subnet using Fastconnect, VPN or bastion service. These topics covered in Part 4: Securing APEX Admin Resources with Load Balancer

5Autonomous database will scale up to 3x according to CPU requirements, that is easy configuration. For the middleware part, I use metrics based auto scaling for adding ORDS instances when existing instances in the pool have 80% or more CPU utilization. I cover scaling configuration along with testing in Part 5: Autoscaling ORDS servers in private subnet behind public load balancer

I also recommend checking my colleague John Lathouwers's GitHub , he has some nice scripts.

How can I mount Object Storage as NFS target on Windows with rclone?

This is one of the most common requirements: How can I mount Oracle Object Storage as an NFS target on my Windows environment? Normally this is done with Storage Gateway on Linux environments, but unfortunately Windows operating system is not supported yet. Next best thing is to use rclone . So here is how to do it.

1First I start with prerequisities, install oci cli as described on the link and get it working

2I also needed to install WinFsp .

3Download rclone , unzip and put on your PATH.

4Configure rclone as described here

5Usage is simple. All commands are listed here . Assuming that your remote is named as remote:

Part 5: Autoscaling ORDS servers in private subnet behind public load balancer

I want to autoscale my ORDS servers based on CPU metric, so whenever the servers are busy and CPU utilized over 80% then a new node will be added to the instance pool and the backendset. You will need a loadbalancer configuration which I explained in this blog post. I have compute nodes that are running ORDS in standalone mode, you can find installation and configuration steps here .

On the compute node, I've installed java and ords and also configured to access database. Now I want to use this installation as a template for creating other nodes when I want to autoscale. Unfortunately if I use the instance as a source to my instance configuration, it will not include anything from the boot volume, but just the base image that the instance is launched from. More detail is here .

1So for that reason I will start by creating a custom image

I can use this oci cli command for the same purpose

2Then I create an instance configuration with the placement (compartment and AD) information

using my custom image which I've created in the first step

placing instance in my private subnet where all my ORDS nodes will be and attaching the network security group which will allow load balancer communication

I am adding my ssh key just in case I might need to access the servers

I also make sure that Bastion agent is enabled, plus put some piece of cloud-init script you don't see it here but it will not make a difference

I can also do the same thing with oci cli, you can find ords-instance-details file on my github

3Now I am going to create an instance pool which will create instances using the instance configuration I've just created.

Instance pool is distrubuting instances to availability domains for risk mitigation. Note that my subnets are regional, and instance will be in private subnet

And the launched instances will be placed in load balancer backend set. I am providing port information for health check.

Here is the oci cli command that can be used for creating the same instance pool in above screenshots. You can find ords-placement-configurations.json file on my github.

4I want my instance pool to scale-out and scale-in according to CPU utilization. For that purpose I am creating an autoscaling configuration for my recently created instance pool

Instance pool will scale-out according to CPU utilization metric, if the CPU usage is above threshold it will add one instance to the pool

I want the pool shrink with a scale-in threshold that will remove one instance. I want the pool have at least 3 instance at all times and don't want the pool grow beyond 6 instances.

Here is the oci cli command to create the autoscaling configuration, you can download ords-autoscaling-policies.json file from my github.

5For testing purposes I will use the following script. Script finds all the instances in the pool, and creates some CPU load which will trigger auto scaling policy to scale out. I've made some configuration to issue commands remotely which is explained here . Base custom image has some packages already installed for stress testing, you can read about it here

How fast can I launch multiple OCI compute instances using CLI?

This topic popped up couple of times so I decided to write on it. The first time it was about an ISV who developed a testing tool and they needed to run hundreds of instances for 10-20 minutes for simulating a heavy web traffic then of course terminate instances. And all has to happen as fast as possible to reduce cost and deliver results quickly. Second time was about cleaning up a mess, not so nice at all.

1So here I start with launch instances script. The script is looping from 1 to 10 and measuring execution time and returning OCID of the compute instance. As you can see it is around 2 seconds per instance, pretty good.

2The instances are running and I can see them on the console and also I can find them with cli

3Now the second part, cleaning up. First I am finding all the running instances with display name like test, then terminating them one by one. Terminate command is taking 1 seconds per instance on average

Run Commands Remotely on OCI Compute Instances

This is an OCI feature that has to be configured and enabled on compute instances. Here is the official documentation and this is a powerful feature if you set it up correctly. So here I start.

My plan is to tag instances accross the tenancy who can run commands, and based on that tags include instances into Dynamic Groups and with a policy allow instances to use the feature.

1 I start with creating a tag namespace which will store my tag keys

Following oci cli command can be used to create the tag namespace. You may notice we are using http raw request rather than matching cli command, as cli tool doesn't offer a specific command for tag operations. Personally I find REST API more useful than OCI CLI Command Reference . Rest API documentation offers real sample scripts, not generated ones like the cli

2 Now I am adding tag keys. I am not interested in cost tracking, but I want a list of values that users can select from rather than a free form string.

Following cli scripts can be used to create keys under a namespace

3 The tags are created so I can tag the instances either using the console.

If updating using oci cli you must also provide the existing tags. So first I get the instance, copy defined-tags.

4 I need compute instances to identify themselves as a member of IAM Dynamic Groups as described here . So for that reason I am creating a Dynamic Group with the condition of having a tag value.

Following cli script can be used to create the dynamic group

5 Now I will create/update a policy to allow my recently created Dynamic Group to run commands. All requirements explained here on official documentation .

Again similar to adding tags to instances, if updating an existing policy I need provide all the existing statements and version date.

6 Now it is time for testing. First I create the command and wait 3-4 minutes before it succeeded

I can use cli to create the command.

And the results of the command as well as the exit code is displayed

This is really powerful, you can pass script files, instruct to import script from Object Storage, store output as text or on Object Storage. It can be used as part of any automation purpose, data processing, configuration even for debug and restore purposes. It is up to your imagination.

Troubleshooting

1Official documentation advises me to check cloud agent log file for agent log

2After restarting the agent I get my commands running

3A succesful execution log looks like this:

4The commands are run with oacrun user, I've added the user to sudoers.

Stress test on OCI compute instance with stress-ng

I needed to create a CPU load on compute instances to test metrics based auto scaling.

1I have Oracle Linux 8 image as base image. So this is what I have done. First add repository and install pakages.

2After installing the stress-ng , I can push some load on CPU

3Console also displays the CPU utilization is increased

Using OCI CLI in interactive mode

This was one of the most handy discoveries for me, as it saved me a LOT of time without going back and forth searching on documentation and the command line interface. Thank you Aykut for showing this to me!

Just use the -i option

-i, --cli-auto-prompt Use the CLI in interactive mode.

It is capable of displaying and auto-completing sub-commands and also displaying required parameters

On top of that, it can querry and list values from your tenancy, let you search with human readable names of resources and convert those resource names back to OCIDs, that's how cli works

I am using this more and more over the time, and most of the time I don't need to go back and check the documentation! Quite useful.