Use PL/SQL to perform Storage Snapshots 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.

I have previously shared how to perform File System snapshots on a Pure Storage FlashBlade using the UTL_HTTP package, in this post I will show how we can use it to perform Pure Storage FlashArray actions.

For the purpose of this post I will be using an Oracle 21c database and a FlashArray to demonstrate the use of the UTL_HTTP package. I will show how we can use the FlashArray REST APIs to perform a consistent multi-volume storage (Protection Group) level storage snapshot.

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:

Create Session

Before we can perform any FlashArray actions we need to create an authorised REST session. We do this using auth/session with the POST method providing the api_token within the body, for example.

POST https://fbstaines02.uklab.purestorage.com/api/1.19/auth/session 
{
  "api_token": "$APITOKEN"
}

The PL/SQL code below forms the URL as per the format above, sets up HTTP headers, body and performs the request.

The HTTP response status code and authorised username are written to dbms_output for viewing. Note: you may need to SET SERVEROUTPUT ON to see any output.

dbms_output.put_line('Create FlashArray Session');
dbms_output.put_line('-----------------------------------------------');

 l_path := p_url || '/api/1.19/auth/session';
 l_body := '{"api_token":"' || p_token ||'"}';

 -- Prepare for Create session
 l_http_request := UTL_HTTP.begin_request(l_path, 'POST' );

 -- set Headers
 UTL_HTTP.set_header(l_http_request, 'user-agent', 'mozilla/4.0');
 UTL_HTTP.set_header(l_http_request, 'content-type', 'application/json');
 UTL_HTTP.set_header(l_http_request, 'Content-Length', length(l_body));
  
 -- set Body of request
 UTL_HTTP.write_text(l_http_request, l_body);

 -- get Response 
 l_http_response := UTL_HTTP.get_response(l_http_request);
 dbms_output.put_line('Response status code: ' || l_http_response.status_code);
  
 -- Loop through the response.
 BEGIN
   LOOP
     UTL_HTTP.read_text(l_http_response, l_text, 32766);
     dbms_output.put_line ('Logon: ' || l_text);
   END LOOP;
 EXCEPTION
   WHEN UTL_HTTP.end_of_body THEN
    dbms_output.put_line ('>');
 END;
 
 -- Loop through HTTP headers
 FOR i IN 1..UTL_HTTP.GET_HEADER_COUNT(l_http_response) LOOP
   UTL_HTTP.GET_HEADER(l_http_response, i, l_name, l_value);
   -- DBMS_OUTPUT.PUT_LINE(l_name || ': ' || l_value);
   IF l_name = 'Set-Cookie' THEN
     l_cookie := l_value;
   END IF;
 END LOOP;

Protection Group Snapshot

Now we have an authorised REST session we can perform a Protection Group Snapshot calling pgroup with a POST method.

From the table above we can see that to create a snapshot we set snap to true and provide a list of the protection groups names to source, for example.

POST https://fbstaines02.uklab.purestorage.com/api/1.19/pgroup 
{
  "snap": true,
  "source": [
    "z-oracle1PG"
  ]
}

The PL/SQL code again sets-up the URL, HTTP headers, body and performs the request.

The HTTP response status code, created timestamp, protection group snapshot name, and source details are written to dbms_output for review.

 dbms_output.put_line('>');
 dbms_output.put_line('Perform Protection Group Snapshot of ' || p_pgroup);
 dbms_output.put_line('-----------------------------------------------');

 l_path := p_url || '/api/1.19/pgroup';
 l_body := '{"snap":"true","source": ["' || p_pgroup ||'" ]}';

 dbms_output.put_line('>' || l_path);
 dbms_output.put_line('' || l_body);

 -- Make a HTTP request and get the response.
 l_http_request := UTL_HTTP.begin_request(l_path, 'POST' );

 -- set Headers
 UTL_HTTP.set_header(l_http_request, 'user-agent', 'mozilla/4.0');
 UTL_HTTP.set_header(l_http_request, 'content-type', 'application/json');
 UTL_HTTP.set_header(l_http_request, 'Content-Length', length(l_body));

 -- set Body of request
 UTL_HTTP.write_text(l_http_request, l_body);

 -- get Response
 l_http_response := UTL_HTTP.get_response(l_http_request);
 dbms_output.put_line('Response status code: ' || l_http_response.status_code);
  
 -- Loop through the response.
 BEGIN
   LOOP
     UTL_HTTP.read_text(l_http_response, l_text, 32766);
     dbms_output.put_line (l_text);
   END LOOP;
 EXCEPTION
   WHEN UTL_HTTP.end_of_body THEN
     UTL_HTTP.end_response(l_http_response);
 END;

Perform FlashArray Protection Group Snapshot

The Oracle fa_snapshot procedure expects three parameters FlashArray URL, API Token and Protection Group Name, and uses the provided parameters to perform a Protection Group Snapshot.

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 the procedure has been installed fa_snapshot can be run with: 

exec fa_snapshot(‘<URL>’,'<API TOKEN>’,'<Protection Group Name>’);

SQL> exec fa_snapshot('https://z-m20-a.uklab.purestorage.com','5d8ad02f-547d-fc24-bb51-fa0d2b0de973','z-oracle1PG');
Create FlashArray Session
-----------------------------------------------
Response status code: 200
Logon: {"username": "pureuser"}
>
Session Cookie:
session=.eJwtzMEKgzAQhOFXKXPOoWqQ1JcJi7sBwSSySQ5FfHdjKczl44c54Q_RSElSxVK1icFOpXpRzYrl_afKmpV_1rwLFpAqfT1x3BIMytbjiVfrJdiZA3_c6qZgh1Ucz2GYyI7cN46My6AV0UTxOTqaykNcNyCbLjU.YvpjZA.a5jJ8rtAM9cCwhKobja4CnRg
pmo; Expires=Mon, 15-Aug-2022 15:46:52 GMT; HttpOnly; Path=/
>
Perform Protection Group Snapshot of z-oracle1PG
-----------------------------------------------
>https://z-m20-a.uklab.purestorage.com/api/1.19/pgroup
{"snap":"true","source": ["z-oracle1PG" ]}
Response status code: 200
[{"name": "z-oracle1PG.15207", "source": "z-oracle1PG", "created": "2022-08-15T15:16:52Z"}]

PL/SQL procedure successfully completed.

If we logon to the FlashArray WebUI we can see Protection Group snapshot has been created as reported by the database procedure.

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.

The example code is available in my GitHub repository