Dynamic Views for Oracle RDS

There are no specific Oracle dynamic views that can directly answer all of these questions about RDS. However, I can provide some insights on how you might approach these questions:

1. To determine if an Oracle 19c database is on-premise or in the AWS Cloud, there's no single dynamic view that will give you this information. AWS RDS doesn't expose specific views for this purpose. You would typically know this based on how you connect to the database and your organization's infrastructure setup.

2. Similarly, there's no Oracle dynamic view that can tell you if the cloud database is based on RDS or EC2. This information is part of the AWS infrastructure setup, not something Oracle database itself tracks.

3. For determining if an Oracle RDS is based on CDB or non-CDB, you can use some standard Oracle views. While not specific to RDS, you can query V$DATABASE or CDB_PDBS. If the database is a CDB, V$DATABASE will have CON_ID > 0, and CDB_PDBS will return rows. If it's a non-CDB, V$DATABASE will have CON_ID = 0, and CDB_PDBS won't exist.

It's important to note that in RDS for Oracle, you always connect to the tenant database (PDB) rather than the CDB, even if it's a CDB architecture. Starting from Oracle Database 21c on RDS, only the multitenant architecture (CDB) is supported. For Oracle 19c on RDS, both CDB and non-CDB architectures are possible, depending on how the instance was created.

Remember, the most reliable way to get AWS-specific information is through AWS management tools and APIs, not through Oracle's dynamic views.
Sources
Oracle RDS Architecture | AWS re:Post
About RDS's oracle cdb issue | AWS re:Post
RDS for Oracle releases - Amazon Relational Database Service

1. There is no dynamic view available to identify whether your oracle 19c database is hosted on on-premise or AWS cloud. I would recommend verifying your organization architecture to identify it.

2. There is no single dynamic view available to identify this. But, typical differences between Oracle database hosted on Amazon RDS and EC2 are:

a) Amazon RDS is a managed service, so you will not have access to connect to the database host. For oracle databases created on EC2, you can login to the EC2 machine that is hosting your oracle database. b) In Amazon RDS, we have a user named "RDSADMIN" that is used by our internal automation mechanisms. This user is NOT created by default in oracle databases hosted on EC2 machines or on-premise. You can execute following query to identify whether RDSADMIN user is available in your oracle database.

select username, account_status from dba_users where username='RDSADMIN';

3. You can query CDB column from V$DATABASE dynamic view. The CDB column will return "YES" for CDB database. It will return "NO" for non CDB databases.

select CDB from V$DATABASE;

Hope it helps and if it does, I would appreciate if answer can be accepted so that community can benefit for clarity when searching for similar issues/queries!

Thanks!