Am I Autonomous or non-Autonomous, that is the Question

Introduction

As more and more customers are moving their databases to Oracle Cloud, they have a mix of on-premises, Oracle Database Cloud Service, and Oracle Autonomous databases in their environments.

DBAs and developers usually use SQL scripts to automate several tasks in the database. In some cases, they want to execute some tasks only on Autonomous and others only on non-Autonomous databases. As it is good practice to have just one version of your scripts to maintain consistency and avoid the maintenance overhead of different versions, customers often ask how to determine from within SQL if we are running on Autonomous Database or not? And then, based on that, to execute a specific SQL or PL/SQL code or not.

Through my discussions with customers and colleagues, I have seen different approaches that I want to show and discuss in this blog post. If you are just interested in the best solution, jump directly to approach 5.

The Environment

  • Oracle Autonomous Database on Shared Infrastructure.
  • Oracle database from the Marketplace running on OCI Compute to emulate an on-premises database.

Approaches to find out whether running on Autonomous

Approach 1: check parameter values

Check for database parameters if they are set to a specific value, e.g. lockdown profile is set in Autonomous Databases:

SQL> select value from v$parameter where name = 'pdb_lockdown';
VALUE
-------
DWCS

However, this is not very reliable as you might start using PDB lockdown profiles on your non-Autonomous databases as well at some point if you do not already do.

Approach 2: check the existence of DBMS_CLOUD package

DBMS_CLOUD PL/SQL package is available in all Oracle Autonomous Databases to enable you to access data stored on Object Storage:

SQL> select owner, object_name, object_type 
from dba_objects 
where object_name = 'DBMS_CLOUD';

OWNER                OBJECT_NAME          OBJECT_TYPE
-------------------- -------------------- --------------------
PUBLIC               DBMS_CLOUD           SYNONYM
C##CLOUD$SERVICE     DBMS_CLOUD           PACKAGE
C##CLOUD$SERVICE     DBMS_CLOUD           PACKAGE BODY

However, starting from database version 19.9, DBMS_CLOUD has also been made available for non-Autonomous databases. You could still check for the owner, but you could also use the same name C##CLOUD$SERVICE when you install the DBMS_CLOUD on your non-Autonomous databases. Hence unreliable either.

Approach 3: check the number of database instances

As Autonomous Database on Shared Infrastructure runs on Exadata Full Racks, you’ll get 8 active instances, independent of how many instances you Autonomous Database services are available on:

SQL> select inst_number, inst_name
from v$active_instances
order by inst_number;

INST_NUMBER INST_NAME
----------- ------------------------------------------------------------
          1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx:ehz1pod1
          2 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx:ehz1pod2
          3 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx:ehz1pod3
          4 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx:ehz1pod4
          5 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx:ehz1pod5
          6 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx:ehz1pod6
          7 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx:ehz1pod7
          8 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx:ehz1pod8

8 rows selected.

However, you also might have other 8-node RACs in your environment. Even if you check for the instance name starting with many x’s and ends with pod1 to pod8, it is not 100% reliable as Oracle might change these configurations in the background without having any impact on your databases.

Approach 4: check the service names

Oracle Autonomous Databases provide pre-defined service names:

SQL> select name
from cdb_services
order by name;

NAME
----------------------------------------------------------------
VIBPPCKZTXTGJIP_ADBPUBLIC
VIBPPCKZTXTGJIP_ADBPUBLIC_high.adb.oraclecloud.com
VIBPPCKZTXTGJIP_ADBPUBLIC_low.adb.oraclecloud.com
VIBPPCKZTXTGJIP_ADBPUBLIC_medium.adb.oraclecloud.com

As these service names cannot be changed or deleted, it is quite reliable to figure out if you are running on an Autonomous Database by querying the service names. But is this 100% reliable? Well, you still could create services with exactly these names on your non-Autonomous databases, even though I can’t imagine why you would do that, but it is technically possible.

Approach 5: check the CLOUD_IDENTITY column of the v$pdbs view

Fortunately, there is a 100% reliable approach to figure out if you are running on Autonomous Database using SQL:

SQL> select cloud_identity
from v$pdbs;

CLOUD_IDENTITY
----------------------------------------------------------------------------------------------------------------------------------
{
  "DATABASE_NAME" : "ADBPUBLIC",
  "REGION" : "eu-frankfurt-1",
  "TENANT_OCID" : "OCID1.TENANCY.OC1..AAAAAAAAHWQ3YKAWJ2W6H72ERRKTEGOYCRUTMF3TVJBWKVM75QZVJLW4TO6A",
  "DATABASE_OCID" : "OCID1.AUTONOMOUSDATABASE.OC1.EU-FRANKFURT-1.ABTHELJSTULZNVIUOORCDGZI23J5RKVUE3FRER6BVD54SOH5ST7KLSJAQ97A",
  "COMPARTMENT_OCID" : "ocid1.compartment.oc1..aaaaaaaabpe3gy2keoxpobzij76k2uxhq4uskq6nilcmxeydsliyqxnbvdhq",
  "OUTBOUND_IP_ADDRESS" :
  [
    "132.145.239.59"
  ]
}

The CLOUD_IDENTITY column does not exist on non-Autonomous databases:

SQL> select cloud_identity
from v$pdbs;

       *
ERROR at line 1:
ORA-00904: "CLOUD_IDENTITY": invalid identifier

Should other cloud databases contain this column in the future, you can still tell with 100% confidence whether this is an Autonomous Database by looking at the OCID that includes the string “AUTONOMOUSDATABASE”.

Determine the Workload Type of the Autonomous Database

Sometimes I also get the question if it were possible to determine the workload type of the Autonomous Database using SQL, i.e. if we have Autonomous Transaction Processing (ATP), Autonomous Data Warehouse (ADW), Autonomous JSON (AJD), or Autonomous APEX (APX).

The CLOUD_IDENTITY column of the v$pdbs view unfortunately does not provide this information.

From what we know, ADW provides three pre-defined database services (_low, _medium, _high) for client connections, while ATP provides five (_low, _medium, _high, _tp, _tpurgent):

-- ADW
SQL> select count(*) from cdb_services where name like '%.adb.oraclecloud.com';

  COUNT(*)
----------
         3

-- ATP
SQL> select count(*) from cdb_services where name like '%.adb.oraclecloud.com';

  COUNT(*)
----------
         5

However, Autonomous JSON and APEX also have the same 5 pre-defined services and are not distinguishable from ATP using this query result.

The best approach is to use the DBMS_CLOUD.SEND_REQUEST procedure to set up REST API calls from within your Autonomous Database to get all information available about the Autonomous Database.

Create the credentials, one time activity:

SQL> BEGIN
	DBMS_CLOUD.CREATE_CREDENTIAL (
		credential_name => 'CLOUD_CRED', 
		user_ocid => 'ocid1.user.oc1..aaaaaaaasnkrajs53vdpaiksdewotv4eyfhs6paxjpgrza2qfqrztrdyofwa', 
		tenancy_ocid => 'ocid1.tenancy.oc1..aaaaaaaahwq3ykawj2w6h72errlkbgoycrutmf3tvkiukvm64qzvjlw4to6a', 
		private_key => '-----BEGIN RSA PRIVATE KEY----- xxx -----END RSA PRIVATE KEY-----', 
		fingerprint => 'be:6e:54:5b:f7:43:85:8a:00:b5:e5:5c:51:91:0f:65'); 
END; 
/

Send a GET request with the OCID of your Autonomous Database:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
	resp DBMS_CLOUD_TYPES.resp;
	autonomous_database_ocid VARCHAR2(512) := 'ocid1.autonomousdatabase.oc1.eu-frankfurt-1.antheljtgxomteaaz7zgf7cpfwkzzmbjzwluu3n2jaunpo73koubxk3gvypa';
BEGIN
	resp := DBMS_CLOUD.send_request(
		credential_name => 'CLOUD_CRED',
		uri     => 'https://iaas.eu-frankfurt-1.oraclecloud.com/20160918/autonomousDatabases/'||autonomous_database_ocid,
		method  => DBMS_CLOUD.METHOD_GET,
		headers => JSON_OBJECT('opc-request-id' value 'get-autonomous-database')
	);
	dbms_output.put_line('response: ' || DBMS_CLOUD.get_response_text(resp));
END;
/

Look for the “dbWorkload” value in the response. This will be as follows:

  • For ATP: “dbWorkload”:”OLTP”
  • For ADW: “dbWorkload”:”DW”
  • For AJD: “dbWorkload”:”AJD”
  • For APX: “dbWorkload”:”APEX”

Summary

In some cases, you need to determine whether your SQL scripts are running on an Oracle Autonomous or a non-Autonomous database. Based on that you’d need to decide to run a specific code or not. There are many approaches to get this information using a SQL statement from within the database. The most reliable one is to query the CLOUD_IDENTITY column of the v$pdbs view.

By using REST API calls you are able to send any request to any of your OCI resources, and with that, you can just get all the information about your Autonomous Database, e.g. the workload type, from within your Autonomous Database itself using SQL.

Further Reading

Would you like to get notified when the next post is published?