Autonomous Database – Scaling UP/DOWN OCPU’s using Pl/SQL API’s

Autonomous Database provides an option of Scaling UP and DOWN of CPU & Storage. Scaling UP/DOWN of CPU resources is a common practice that most of the customers implement. Take for example, throughout the day load on the system is higher and during night time, only few users extract some information. Scaling DOWN during night time will save on CPU Cost. Similarly, a Weekly/Monthly/Quarterly Load may need additional CPU’s. While this can be done manually from the Console, many of our customers requested a scheduler based approach to achieve this. I thought of writing about this topic.

For this, I will be using OCI Resource Principal, a very simple, secure and easier way of accessing OCI resources and services from an Autonomous Database. You don’t need to setup an authentication token and register that token inside ADB. For OCI Resource Principal, we just need to create Policies and Dynamic Groups. Policies are just like Privileges and Dynamic Groups are like Database Roles. You create a policy and a role, attach a policy to a role and then, this role (dynamic group) can be assigned to a single resource or group of resources.

Following figure shows a typical flow:

So, let’s start implementing this.

I have an Autonomous Database, which is in VivekCompartment. I will implement this for Scaling UP and DOWN based on a schedule.

So, as a first step I will create a Dynamic Group called VivekSharmaDG. This is available under “Identity & Security”. A Dynamic Group requires a Rule that specifies list of resource(s) to be included in this Group. You can have an Individual Resource, like a Single ADB Instance, or group of ADB’s within a compartment.

In my case, I will create a rule that says – Include all Autonomous Databases within a Compartment OCID. This Compartment OCID is of VivekCompartment. This is the compartment where all my ADB’s are created.

All {resource.type=’autonomousdatabase’, resource.compartment.id = ‘<compartment_ocid>’}

Once the Dynamic Group is created, we need to create a Policy. This is again under “Identity & Security”. This policy will have a statement that allows the Dynamic Group to access the OCI Resources. The relevant policy statement will be :

Allow dynamic-group VivekSharmaDG to use autonomous-database-family in compartment VivekCompartment

This means, a single policy will be applied to the Dynamic Group. As the name implies, Dynamic Group is dynamic in nature. You change the rule, to add or remove the resources and the policy will be applied to those resources automatically.

Once done, we need to enable Resource Principal to Access OCI Resources and following procedure in my ADB.

Enable Resource Principal, using

exec dbms_cloud_admin.enable_resource_principal();

Procedure with the relevant API calls.

CREATE OR REPLACE PROCEDURE change_ocpu_count( ocpu_count IN NUMBER )
  IS
  	autonomous_database_details	dbms_cloud_oci_database_update_autonomous_database_details_t;
  	response_body			dbms_cloud_oci_database_autonomous_database_t;
  	response			dbms_cloud_oci_db_database_update_autonomous_database_response_t;
	  
  	adb_ocid   VARCHAR2(200);
	adb_region VARCHAR2(100);

  BEGIN

	
	SELECT json_value(cloud_identity, '$.REGION'), LOWER(json_value(cloud_identity, '$.DATABASE_OCID'))
      	INTO adb_region, adb_ocid FROM v$pdbs;

  	DBMS_OUTPUT.PUT_LINE('ADB OCID:'||adb_ocid);
  	DBMS_OUTPUT.PUT_LINE('ADB Region:'||adb_region);
  	DBMS_OUTPUT.PUT_LINE('New OCPU count:'||ocpu_count);

  	autonomous_database_details := dbms_cloud_oci_database_update_autonomous_database_details_t();
  	autonomous_database_details.cpu_core_count := ocpu_count;

  	response := DBMS_CLOUD_OCI_DB_DATABASE.UPDATE_AUTONOMOUS_DATABASE (
  		autonomous_database_id => adb_ocid,
  		update_autonomous_database_details => autonomous_database_details,
  		region => adb_region,
  		credential_name => 'OCI$RESOURCE_PRINCIPAL'
  	);

  END;
/

Once this is done, you can test this by running the procedure from SQL*Plus and passing the New OCPU Count as an Input to the procedure. If the configuration is set right, the procedure should work absolutely fine. You can now schedule this procedure using DBMS_SCHEDULER to Scale DOWN/UP the CPU resources.

For further details, you may want to read Oracle Documentation link here..