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 your 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”
Even easier. Query the CLOUD_IDENTITY column of v$pdbs:
SQL> select cloud_identity from v$pdbs;
-- look for
"SERVICE" : "ATP"
If you are using a non-privileged user:
SQL> select sys_context('userenv', 'cloud_service') as cloud_service from dual;
CLOUD_SERVICE
---------------
OLTP
Always Free
Autonomous Database Always Free instances are limited to a database size of 20 GB while paid instances have a size of at least 1 TB. Check the BASE_SIZE in the cloud_identity column:
SQL> select cloud_identity from v$pdbs;
...
"BASE_SIZE" : 21474836480 -- = 20 GB --> you are running on always free
...
SQL> select cloud_identity from v$pdbs;
...
"BASE_SIZE" : 1099511627776 -- = 1 TB
...
Additional Information
The CLOUD_IDENTITY column of the v$pdbs view provides more insight into your Autonomous Database:
SQL> select cloud_identity from v$pdbs;
-- Shared or Dedicated Exadata Infrastructure
"INFRASTRUCTURE" : "Shared"
-- Availability Domain where the Exadata Infrastructure resides
"AVAILABILITY_DOMAIN" : "AD-1"
-- Outbound IP address to white list if needed
"OUTBOUND_IP_ADDRESS" :
[
"132.145.249.45"
]
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 getting 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.
Database Heartbeat 