PROGRAMATICALLY SCALING UP AND DOWN AN AUTONOMOUS DATABASE IN PL/SQL BATCH PROCESSING WORKLOADS

Let B a PL/SQL batch process that must run in a specific window timeframe. You have stress test B and you know how much CPU utilises to complete in the specific window. The following recipe based in OCI REST database service api endpoints can be called at the beginning of B to scale up the database and then called at the end to scale down.

The code

Grab the information remarked in bold such as user ocid, tenancy and the like. Also locate the endpoint according to your home region. set the parameter numCores according to your needs and run the code.

-- for oci rest api's we need a credential for signing api
 BEGIN
 DBMS_CLOUD.CREATE_CREDENTIAL (
     credential_name => 'MYDBCRED',
     user_ocid => 'ocid1.user....',
     tenancy_ocid => 'ocid1.tenancy....',
     private_key => 'MIIEp...Fw==',
     fingerprint => 'b...a'); 
 END;
 /
 -- scale database
 declare
     resp DBMS_CLOUD_TYPES.resp;
     numCores int := 2;
     region varchar2(32) := 'eu-frankfurt-1';
     endpoint varchar2(64) := 'https://database.eu-frankfurt-1.oraclecloud.com';
     atpid varchar2(128) := 'ocid1.autonomousdatabase....';
 begin
     -- list bucket content
     resp := DBMS_CLOUD.send_request(credential_name => 'MYDBCRED',uri => 
         endpoint || '/20160918/autonomousDatabases/' || atpid,
         method => 'PUT', body => UTL_RAW.cast_to_raw(JSON_OBJECT(
             'cpuCoreCount' value numCores)));
     dbms_output.put_line('-------');
     dbms_output.put_line('result: ' || DBMS_CLOUD.get_response_text(resp));
 end;

If everything was fine you’ll see in the output a json response with the information of the instance. In addition you can see that the database starts to scale in online mode, that is, with no loss of service at all. After a few moments you’ll see your database scaled according to your request.

That’s all, hope it helps! 🙂