DEV Community

Franck Pachot for AWS Heroes

Posted on • Originally published at blog.dbi-services.com

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.

Top comments (0)