They have a small amount of 9iR2, 11.2 and most are 12.2 or going to 19c or are already on 19c.
All of the existing databases are single instance and they are moving upgraded databases to single instance databases as well in 19c. Oracle deprecated non-CDB architecture in 12c and its de-supported in 21c and I think I read that its not possible in 23c to create a single instance database.
So, for now they are supported in 19c and have their single instance databases that look and feel like older databases. I have spoken to them in the past about multitenant architecture and said a lot of our customers are converting to single PDB in the root container. This customer does not like this as in effect (their words) they have to maintain two databases for every single previous database. They can of course use multitenant properly and have more than one database per container. I have suggested this and they still think that yes, they could have 3 PDBs in a CDB but that is still 1.3 databases per original database!
So, they asked the question. Can we install our application in the CDB and run it from there and in effect have a single instance database. This was not a new idea for me as I have thought about this maybe ten years ago when multitenant first came out and I knew that Oracle would make this a default architecture at some point. I considered this idea to put the application only in the root container as an option many years ago and whilst its a great thought experiment unless Oracle supports this then there is likely to be problems cropping up over time that need to be solved. I had a blog in my list of potential blogs for many years BUT as I was asked this yesterday its time to write this blog now!!
So, as a thought experiment, can we simply put the application in the root container and remove pluggable databases and treat the CDB/root as a single instance database? probably
There are issues of course. As a thought experiment I am not hindered by production and support. The first things that come to mind are that we as customers of the Oracle database are allowed to access the CDB and have COMMON rights and make settings, profiles, roles etc so we are intended by Oracle to be there. Can we create code (PL/SQL), tables, views etc in the root container? yes of course. Can we put a whole application there? well yes but there could be issues on naming and upgrade etc.
A DBA could for instance create tables and views legitimately in the CDB and some PL/SQL to maintain the PDBs and the CDB as part of that and that to be is fine. Maybe the DBA has some tools, utilities, some stats etc that he collected in each CDB; that is fine BUT can we put a whole home grown application or legacy application in the root container. We probably can BUT there will probably be a lot more problems. Oracle put APEX in the root container originally in 12c and application containers provide the idea that you install the application once in the application container making support faster and easier and then create a PDB in the application container for each end user / customer database maybe with customer data in the PDB and core data and code in the application container. So the principal exists to put the application in the root container or application container.
If we try and install a legacy application into the CDB the first problem is the naming of users and roles which start by default C##; so the application code and all interfaces and database schemas may need to change to add C##. If this is a third party vendor then they are not likely to help or support you.
We could use the undocumented parameter "_oracle_script=true" and install the application and this would allow use of non-C## names in the database BUT every user and role or profile or table or.... that you create is likely to have the ORACLE_MAINTAINED column set to YES as well as code. This can be a problem for the seed and upgrades particularly because of the ORACLE_MAINTAINED flag.
What about the common_user_prefix parameter:
[oracle@oel19cee ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 11 10:14:00 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
SQL> select sys_context('userenv','con_name') from dual;
SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT
SQL>
SQL> sho parameter common
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
common_user_prefix string C##
unified_audit_common_systemlog string
SQL>
What if we set this to a NULL string?
SQL> alter system set common_user_prefix='' scope=spfile;
System altered.
SQL>
Re-start the database:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 830470160 bytes
Fixed Size 9140240 bytes
Variable Size 738197504 bytes
Database Buffers 75497472 bytes
Redo Buffers 7634944 bytes
Database mounted.
Database opened.
SQL>
Check the parameter in the root container:
C:\Users\Pete>sqlplus sys/oracle1@//192.168.56.77:1521/orcl.localdomain as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 11 10:37:03 2023
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> sho parameter common
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
common_user_prefix string
unified_audit_common_systemlog string
SQL>
Now create a user and role and table and see:
SQL> create user testtest identified by testtest;
User created.
SQL>
SQL> create role testrole;
Role created.
SQL>
SQL> grant create session to testtest;
Grant succeeded.
SQL> grant create table to testtest;
Grant succeeded.
SQL> grant unlimited tablespace to testtest;
Grant succeeded.
SQL> connect testtest/testtest@//192.168.56.77:1521/orcl.localdomain
Connected.
SQL>
So we have a user TESTTEST that does not include C## and we have connected to the CDB and now create a table:
SQL> create table test (col1 number);
Table created.
SQL>
So, lets confirm TESTTEST and TESTROLE are COMMON and that the table TEST is not ORACLE_MAINTAINED:
SQL> col username for a30
SQL> col common for a3
SQL> col oracle_maintained for a1
SQL> set lines 220
SQL> select username, common, oracle_maintained from dba_users where username like 'TEST%';
USERNAME COM O
------------------------------ --- -
TESTTEST YES N
SQL>
And the table:
SQL> col owner for a30
SQL> col object_name for a30
SQL> col oracle_maintained for a1
SQL> set lines 220
SQL> select owner,object_name,oracle_maintained from dba_objects where object_name='TEST';
OWNER OBJECT_NAME O
------------------------------ ------------------------------ -
TESTTEST TEST N
SQL>
So, we can make objects and users and roles with non-C## names and without making them Oracle maintained. So its possible. This is a simplistic view and there could be other issues in putting an application only in the CDB/root
All privileges are then COMMON but that would not really matter if they only used the CDB as there would be no PDBs to use these rights in.
Some things have to be done in the CDB such as LogMiner so it is probably acceptable to use the CDB in my mind BUT complete testing would be needed and other issues resolved and the question of support from vendors or Oracle means that we cannot suggest this legitimately as an option but it is a nice thought experiment that I think might work for people who still want just one database in a multitenant architecture.
NOTE: This is a thought experiment and I am not advising anyone to do this in production;
