You Asked

Hi TOM,

Not long ago it was easy to determine if your DB is on premise or on cloud: their banners (from v$VERSION) were different.
Now Entreprise and Standard original editions can be on premise or on cloud so we can no more determine in which environment we are just looking at the banner.

Starting with 21c, V$PDBS contains a CLOUD_IDENTITY column which is not null if you are on cloud.

So my question, in 12.2 to 19c, how to know, using SQL, if my oracle DB is on premise or cloud?

Bonus: how to know if it is OCI (Oracle Cloud Infrastructure) or ACE (Authorized Cloud Environment) or even neither (and unsupported)?

Regards
Michel

and Connor said...

cloud_identity is present on v$pdbs *if* you are on cloud, eg

SQL> select banner_full from v$version;

BANNER_FULL
----------------------------------------------------------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.1.0

SQL> select cloud_identity
  2  from v$pdbs;

CLOUD_IDENTITY
----------------------------------------------------------------------------------------------------------------------------------
{
  "DATABASE_NAME" : "CMCDONALD1",
  "REGION" : "us-phoenix-1",
...

So if the column is not present on 19c, then you are on-premises

If you're on something before 19c, well... you can be confident you're unsupported :-)

Rating

(4 ratings)

Comments

Michel Cadot, May 21, 2024 - 7:52 am UTC

Thanks for your answer, Connor.

I was misled by 21c documentation which states about CLOUD_IDENTITY column: "This column is available starting with Oracle Database 21c".
https://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/V-PDBS.html#GUID-A399F608-36C8-4DF0-9A13-CEE25637653E

12.2 Database Licensing Information User Manual, section 1.2.1 states that SE2 and EE can be licensed for OCI and ACE.
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dblic/Licensing-Information.html#GUID-AB354617-6614-487E-A022-7FC9A5A08472
It is quite strange to be licensed and unsupported. :-)

I definitely will use your trick to check if I am on cloud.

Do you know a way to check if it is OCI or ACE?

Regards
Michel

May 23, 2024 - 2:19 am UTC

I'll log a bug for the 21c docs. The issue is a common one - we release things in 21c, and people want them in 19c so we backport them.

In terms of OCI/ACE, I would try one/some of the following

select host_name from v$instance
select sys_context('USERENV','SERVER_HOST') from dual;
select * from V$LISTENER_NETWORK;

Michel Cadot, May 23, 2024 - 3:39 pm UTC

Thanks for your answer Connor.

I found an answer In Oracle documentation: :-)
https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/autonomous-database-support-ocid.html#GUID-B5F56425-6B50-4035-9A6F-AE3F563C960A

The content of CLOUD_IDENTIFER as described in this page and the pages it references, gives the answer.

Regards
Michel

good

Socorro, May 26, 2024 - 7:24 am UTC

Thank you so much

May 27, 2024 - 3:10 am UTC

glad we could help

Few more details about Cloud database

Rajeshwaran, Jeyabal, May 27, 2024 - 5:41 am UTC

Few more details about Cloud database or not is available at

https://database-heartbeat.com/2021/08/31/isautonomous/

May 30, 2024 - 5:59 am UTC

Good info

More to Explore

Multitenant

Need more information on Multitenant? Check out the Multitenant docs for the Oracle Database

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

good

Few more details about Cloud database

More to Explore

Multitenant

How to know if my oracle DB is on premise or cloud

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

good

Few more details about Cloud database

More to Explore

Multitenant