Amazon RDS Oracle in Multitenant

AWS has just added the possibility to create your oracle Database as as CDB (Container Database), the "new" architecture of Oracle where an instance can manage multiple databases, adding a new level between the heavy instance and lightweight schema:

At the time I'm writing this, I see it only in the "old" console ("original interface") not in "new database creation flow". It is displayed as a different Edition, however it is exactly the same price even when license is included.

The CDB name is always RDSCDB but you can choose the PDB name as "Database name" - I left the default "ORCL" here:

 ORACLE19C@//pdb.cywlwrcont2f.us-east-1.rds.amazonaws.com/ORCL> select con_id, cdb, dbid, con_dbid, name, created, log_mode, open_mode, database_role, force_logging, platform_name, flashback_on, db_unique_name from v$database; CON_ID CDB DBID CON_DBID NAME CREATED LOG_MODE OPEN_MODE DATABASE_ROLE FORCE_LOGGING PLATFORM_NAME FLASHBACK_ON DB_UNIQUE_NAME _________ ______ ________________ ______________ _________ ____________ _______________ _____________ ________________ ________________ ___________________ _______________ _________________ 0 YES 3,360,638,310 490,545,968 RDSCDB 07-MAY-21 NOARCHIVELOG READ WRITE PRIMARY NO Linux x86 64-bit NO RDSCDB_A ORACLE19C@//pdb.cywlwrcont2f.us-east-1.rds.amazonaws.com/ORCL> SELECT pdb_id,pdb_name,dbid,con_uid,guid,status,con_id FROM dba_pdbs; PDB_ID PDB_NAME DBID CON_UID GUID STATUS CON_ID _________ ___________ ______________ ______________ ___________________________________ _________ _________ 3 ORCL 490,545,968 490,545,968 C3395C709E011676E0530100007F3932 NORMAL 3 ORACLE19C@//pdb.cywlwrcont2f.us-east-1.rds.amazonaws.com/ORCL> select service_id, name, network_name, creation_date, pdb, sql_translation_profile from dba_services; SERVICE_ID NAME NETWORK_NAME CREATION_DATE PDB SQL_TRANSLATION_PROFILE _____________ _______ _______________ ________________ _______ __________________________ 7 ORCL ORCL 26-MAY-21 ORCL ORACLE19C@//pdb.cywlwrcont2f.us-east-1.rds.amazonaws.com/ORCL> 

This is not a best practice, but there's no services declared there which mean that I can connect only with the default service registered from the PDB name. The documentation even recommends to connect with (CONNECT_DATA=(SID=pdb_name)) - I filled a feedback about this as this is a bad practice for 20 years.

I use EZCONNECT and create my own service:

ORACLE19C@//pdb.cywlwrcont2f.us-east-1.rds.amazonaws.com/ORCL> connect oracle19c/franck@//pdb.cywlwrcont2f.us-east-1.rds.amazonaws.com/ORCL Connected. ORACLE19C@//pdb.cywlwrcont2f.us-east-1.rds.amazonaws.com/ORCL> exec dbms_service.start_service(service_name=>'MY_APP') PL/SQL procedure successfully completed. ORACLE19C@//pdb.cywlwrcont2f.us-east-1.rds.amazonaws.com/ORCL> select name,network_name,creation_date,con_id from v$active_services 2 / NAME NETWORK_NAME CREATION_DATE CON_ID _________ _______________ ________________ _________ orcl orcl 26-MAY-21 3 MY_APP MY_APP 26-MAY-21 3 

I can now connect as oracle19c/franck@//pdb.cywlwrcont2f.us-east-1.rds.amazonaws.com/MY_APP

Even if it is multitenant and I have only one PDB there, the whole CDB is mine:

 ORACLE19C@//pdb.cywlwrcont2f.us-east-1.rds.amazonaws.com/ORCL> select listagg(rownum ||': '||con_id_to_con_name(rownum),', ') con_name from xmltable('1 to 5000') where con_id_to_con_name(rownum) is not null; CON_NAME ____________________________________ 1: CDB$ROOT, 2: PDB$SEED, 3: ORCL 

This lists all containers around me. Of course, I cannot go to CDB$ROOT as I have only a local user here.

 ORACLE19C@//pdb.cywlwrcont2f.us-east-1.rds.amazonaws.com/ORCL> show parameter max_pdbs NAME TYPE VALUE -------- ------- ----- max_pdbs integer 5 

The MAX_PDBS is set to 5 anyway because of Oracle detection of AWS hypervisor (see Oracle disables your multitenant option when you run on EC2)

 ORACLE19C@//pdb.cywlwrcont2f.us-east-1.rds.amazonaws.com/ORCL> select listagg(role,',') within group (order by role) from session_roles; LISTAGG(ROLE,',') ______________________________________________________________________________________________________________________________________________________________ AQ_ADMINISTRATOR_ROLE,AQ_USER_ROLE,CAPTURE_ADMIN,CONNECT,CTXAPP,DATAPUMP_EXP_FULL_DATABASE,DATAPUMP_IMP_FULL_DATABASE,DBA,EM_EXPRESS_ALL,EM_EXPRESS_BASIC ,EXECUTE_CATALOG_ROLE,EXP_FULL_DATABASE,GATHER_SYSTEM_STATISTICS,HS_ADMIN_EXECUTE_ROLE,HS_ADMIN_SELECT_ROLE,IMP_FULL_DATABASE,OEM_ADVISOR,OEM_MONITOR ,OPTIMIZER_PROCESSING_RATE,PDB_DBA,RDS_MASTER_ROLE,RECOVERY_CATALOG_OWNER,RESOURCE,SCHEDULER_ADMIN,SELECT_CATALOG_ROLE,SODA_APP,XDBADMIN,XDB_SET_INVOKER ORACLE19C@//pdb.cywlwrcont2f.us-east-1.rds.amazonaws.com/ORCL> select * from dba_sys_privs where grantee='PDB_DBA'; GRANTEE PRIVILEGE ADMIN_OPTION COMMON INHERITED __________ ____________________________ _______________ _________ ____________ PDB_DBA CREATE PLUGGABLE DATABASE NO NO NO PDB_DBA CREATE SESSION NO NO NO ORACLE19C@//pdb.cywlwrcont2f.us-east-1.rds.amazonaws.com/ORCL> show parameter pdb_lockdown NAME TYPE VALUE ------------ ------ --------------------- pdb_lockdown string RDSADMIN_PDB_LOCKDOWN ORACLE19C@//pdb.cywlwrcont2f.us-east-1.rds.amazonaws.com/ORCL> select * from v$lockdown_rules; RULE_TYPE RULE CLAUSE CLAUSE_OPTION STATUS USERS CON_ID ____________ ___________________________ _____________________________ ________________ __________ ________ _________ STATEMENT ALTER PLUGGABLE DATABASE DISABLE ALL 3 STATEMENT ALTER PLUGGABLE DATABASE ADD SUPPLEMENTAL LOG DATA ENABLE ALL 3 STATEMENT ALTER PLUGGABLE DATABASE DROP SUPPLEMENTAL LOG DATA ENABLE ALL 3 STATEMENT ALTER PLUGGABLE DATABASE ENABLE FORCE LOGGING ENABLE ALL 3 STATEMENT ALTER PLUGGABLE DATABASE OPEN RESTRICTED FORCE ENABLE ALL 3 STATEMENT ALTER PLUGGABLE DATABASE RENAME GLOBAL_NAME ENABLE ALL 3 

I have many roles, including RDS_MASTER_ROLE, DBA and PDB_DBA (CREATE PLUGGABLE DATABASE) and it seems that the only lockdown profile rues are about ALTER PLUGGABLE DATABASE.

The documentation says that the RDSADMIN user is a common user. How is it possible?

 ORACLE19C@//pdb.cywlwrcont2f.us-east-1.rds.amazonaws.com/MY_APP> select username, account_status, lock_date, expiry_date, created, profile, password_versions, common, oracle_maintained from dba_users; USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DATE CREATED PROFILE PASSWORD_VERSIONS COMMON ORACLE_MAINTAINED _________________________ ___________________ ____________ ______________ ____________ ___________ ____________________ _________ ____________________ XS$NULL EXPIRED & LOCKED 07-MAY-21 07-MAY-21 DEFAULT 11G YES Y OUTLN LOCKED 07-MAY-21 07-MAY-21 DEFAULT YES Y SYS OPEN 07-MAY-21 RDSADMIN 11G 12C YES Y SYSTEM OPEN 07-MAY-21 RDSADMIN 11G 12C YES Y APPQOSSYS LOCKED 07-MAY-21 07-MAY-21 DEFAULT YES Y DBSFWUSER LOCKED 07-MAY-21 07-MAY-21 DEFAULT YES Y GGSYS LOCKED 07-MAY-21 07-MAY-21 DEFAULT YES Y ANONYMOUS LOCKED 07-MAY-21 07-MAY-21 DEFAULT YES Y CTXSYS OPEN 07-MAY-21 DEFAULT YES Y GSMADMIN_INTERNAL LOCKED 07-MAY-21 07-MAY-21 DEFAULT YES Y XDB LOCKED 07-MAY-21 07-MAY-21 DEFAULT YES Y DBSNMP LOCKED 07-MAY-21 07-MAY-21 RDSADMIN YES Y GSMCATUSER LOCKED 07-MAY-21 07-MAY-21 DEFAULT YES Y REMOTE_SCHEDULER_AGENT LOCKED 07-MAY-21 07-MAY-21 DEFAULT YES Y SYSBACKUP LOCKED 07-MAY-21 07-MAY-21 DEFAULT YES Y GSMUSER LOCKED 07-MAY-21 07-MAY-21 DEFAULT YES Y SYSRAC LOCKED 07-MAY-21 07-MAY-21 DEFAULT YES Y ORACLE19C OPEN 22-NOV-21 26-MAY-21 DEFAULT 11G 12C NO N AUDSYS LOCKED 07-MAY-21 07-MAY-21 DEFAULT YES Y DIP LOCKED 07-MAY-21 07-MAY-21 DEFAULT YES Y SYSKM LOCKED 07-MAY-21 07-MAY-21 DEFAULT YES Y SYS$UMF LOCKED 07-MAY-21 07-MAY-21 DEFAULT YES Y SYSDG LOCKED 07-MAY-21 07-MAY-21 DEFAULT YES Y RDSADMIN OPEN 26-MAY-21 RDSADMIN 11G 12C YES N 24 rows selected. ORACLE19C@//pdb.cywlwrcont2f.us-east-1.rds.amazonaws.com/MY_APP> show parameter common%prefix NAME TYPE VALUE ------------------------- ------ -------- common_user_prefix string 

Yes, RDSADMIN is a common user, probably created with COMMON_USER_PREFIX='' as we see no C## here. That's not really a problem if it is correctly managed, and anyway, for the moment there's no plug and clone operations on this PDB.

This is a start to support the Oracle Multitenant architecture. I hope we will be able to benefit from multitenant: multiple PDBs (you can have up to 3 without additional license, in any edition), data movement (imagine a cross-region refreshable PDB with ability to switchover...), thin clones...

On Performance Insight, we see the CDB level statistics without a PDB dimension ("pdb" is the name of my RDS instance here)

Note that in order to connect to your Oracle database, the easiest is to download SQLcl:

 wget -qc https://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-latest.zip && unzip -qo sqlcl-latest.zip sqlcl/bin/sql oracle19c/franck@//pdb.cywlwrcont2f.us-east-1.rds.amazonaws.com/MY_APP 

This is how I connected to run all this.