Manage Infrastructure with REST APIs from within the Oracle Database using UTL_HTTP

In this blog post I will share how the Oracle UTL_HTTP package can been used to enable full stack automation by calling external application and infrastructure REST APIs from within an Oracle 19c or 21c database.

For the purpose of this post I will be using an Oracle 21c database and a Pure Storage FlashBlade NFS server to demonstrate the use of the UTL_HTTP package. I will show how to call FlashBlade REST APIs to list and create file system snapshots directly from an Oracle database.

Oracle UTL_HTTP

The Oracle UTL_HTTP package provides the ability to perform HyperText Transfer Protocol(Secure) HTTP(s) callouts from SQL and PL/SQL. This can be used to access data locally, over the Internet or call application and infrastructure APIs using HTTP(s).

Note: When the package fetches data from a Web site using HTTPS, it requires an Oracle Wallet which can be created by either Oracle Wallet Manager or the orapki utility. Non-HTTPS fetches do not require an Oracle wallet. 

REST APIs

REST APIs (also known as RESTful APIs) provide the ability to interact with applications and infrastructure. Today most modern solutions expose all, or some functionality to public APIs via HTTP(s) methods, for example:

• GET – used to retrieve information from the given server using a given URI.
• POST- used to send data to the server
• PUT – used to update data on the server
• DELETE – used to delete data on the sever
• PATCH – modify specific fields of data on the server

File System Snapshots

The Pure Storage FlashBlade provides a comprehensive set of REST APIs to automate all the functionality provided by the WebUI.

For example, we can Create, Delete, Get and Update file system snapshot attributes using the FlashBlade REST APIs, using the below:

• Create File System Snapshot
  • POST /1.12/file-system-snapshots
• Delete File System Snapshot
  • DELETE /1.12/file-system-snapshots
• Get File System Snapshot(s)
  • GET /1.12/file-system-snapshots
• Update File System Snapshot attributes
  • PATCH /1.12/file-system-snapshots

All URIs are relative to https://purity_fb_server/api

Create NFS File System

I will start by creating a new file system called ora-test on the FlashBlade NFS server by logging on and navigating to Storage -> File System

Now I have a new file system, I will mount the NFS exported file system and perform an ls -al to check for snapshots, for example.

Mount and Check Snapshot Status

# mount 10.225.112.76:/ora-test /mnt/ora-test -t nfs
# ls -al /mnt/ora-test/.snapshot
total 0
drwxr-xr-x. 1 root root 0 Aug 10 12:09 .
drwxrwxrwx. 2 root root 0 Aug 10 12:09 ..

Perform FlashBlade File System Snapshot

The Oracle fb_snapshot package use the UTL_HTTP POST and GET methods to Create and List FlashBlade File System Snapshots and takes 3 parameters URL, API Token and Filesystem Name

Note: as I will be using HTTPS, I have pre-configured an Oracle Wallet, I have previously shared how to create a Wallet, obtain a certificate and and install it, you can re-familiarise yourself here.

Once installed the database procedure can be run with exec fb_snapshot(<URL>,<API TOKEN>,<File System Name>)

SQL> exec fb_snapshot('https://fbstaines02.uklab.purestorage.com','T-da03a759-bb3f-4995-9c6c-fee48d7c1a98','ora-test');
Create FlashBlade Session
-----------------------------------------------
Response status code: 200
Logon: {"username":"pureuser"}
>
x-auth-token: 75c16f38-f628-4888-b796-41663d1561a2
>
Perform FileSystem Snapshot of ora-test
-----------------------------------------------
>https://fbstaines02.uklab.purestorage.com/api/1.12/file-system-snapshots?sources=ora-test&send=false
Response status code: 200
{"items":[{"name":"ora-test.2022_08_10_13_56","suffix":"2022_08_10_13_56","created":1660136196000,"policy":{"name":null,"id":null,"resource_type":null,"location":null,"is_local":null,"display_name":nu
ll},"destroyed":false,"source_destroyed":null,"time_remaining":null,"id":"b05be6f6-7771-e708-7e2c-64aa8bb6505c","owner":{"name":"ora-test","id":"e8d16880-fe19-6224-a443-0f729f298e32","resource_type":"
file-systems"},"owner_destroyed":false,"source":"ora-test","source_id":"e8d16880-fe19-6224-a443-0f729f298e32","source_is_local":true,"source_location":{"name":"fbstaines02","id":"66992c13-6d81-433f-bb
31-5fc33e76fa5f","resource_type":"arrays"},"source_display_name":"ora-test"}]}
>
>
List FileSystem Snapshots of ora-test
-----------------------------------------------
>https://fbstaines02.uklab.purestorage.com/api/1.12/file-system-snapshots?sort=created&names_or_sources=ora-test
Response status code: 200
{"total":{"name":null,"suffix":null,"created":null,"policy":null,"destroyed":null,"source_destroyed":null,"time_remaining":null,"id":null,"owner":null,"owner_destroyed":null,"source":null,"source_id":
null,"source_is_local":null,"source_location":null,"source_display_name":null},"pagination_info":{"total_item_count":1,"continuation_token":null},"items":[{"name":"ora-test.2022_08_10_13_56","suffix":
"2022_08_10_13_56","created":1660136196000,"policy":{"name":null,"id":null,"resource_type":null,"location":null,"is_local":null,"display_name":null},"destroyed":false,"source_destroyed":false,"time_re
maining":null,"id":"b05be6f6-7771-e708-7e2c-64aa8bb6505c","owner":{"name":"ora-test","id":"e8d16880-fe19-6224-a443-0f729f298e32","resource_type":"file-systems"},"owner_destroyed":false,"source":"ora-t
est","source_id":"e8d16880-fe19-6224-a443-0f729f298e32","source_is_local":true,"source_location":{"name":"fbstaines02","id":"66992c13-6d81-433f-bb31-5fc33e76fa5f","resource_type":"arrays"},"source_dis
play_name":"ora-test"}]}

PL/SQL procedure successfully completed.

The procedure could be called from within code, with Oracle SQL Developer or SQL*Plus, and also run from RMAN block to perform a File System snapshot before and /or after a backup to create immutable snapshots.

Check File System Snapshot

From the OS we can now see the the database procedure has created a FlashBlade file system snapshot in the .snapshot directory

# ls -al /mnt/ora-test/.snapshot
total 0
drwxr-xr-x. 1 root root 0 Aug 10 13:56 .
drwxrwxrwx. 2 root root 0 Aug 10 12:09 ..
drwxrwxrwx. 2 root root 0 Aug 10 12:09 ora-test.2022_08_10_13_56

From the FlashBlade we can also see the file system snapshot.

Summary

In this post I have shared how the Oracle UTL_HTTP can be used to call external REST APIs to automate application and infrastructure tasks from within an Oracle database.

In my next post I will share how we can consume FlashArray REST APIs to take Volume snapshots.

If you want to learn ore the above example code is available in a GitHub repository