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! šŸ™‚

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.