Infrastructure at your Service

Franck Pachot

Amazon RDS Oracle in Multitenant

By May 26, 2021 AWS, Oracle 2 Comments

By Franck Pachot

.
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:


[email protected]//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

[email protected]//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

[email protected]//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

[email protected]//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:

[email protected]//pdb.cywlwrcont2f.us-east-1.rds.amazonaws.com/ORCL>

connect oracle19c/[email protected]//pdb.cywlwrcont2f.us-east-1.rds.amazonaws.com/ORCL
Connected.

[email protected]//pdb.cywlwrcont2f.us-east-1.rds.amazonaws.com/ORCL>

 exec dbms_service.start_service(service_name=>'MY_APP')

PL/SQL procedure successfully completed.

[email protected]//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/[email protected]//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:


[email protected]//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.


[email protected]//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)


[email protected]//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

[email protected]//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


[email protected]//pdb.cywlwrcont2f.us-east-1.rds.amazonaws.com/ORCL>

 show parameter pdb_lockdown

NAME         TYPE   VALUE
------------ ------ ---------------------
pdb_lockdown string RDSADMIN_PDB_LOCKDOWN

[email protected]//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?


[email protected]//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.

[email protected]//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/[email protected]//pdb.cywlwrcont2f.us-east-1.rds.amazonaws.com/MY_APP

This is how I connected to run all this.

2 Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Franck Pachot
Franck Pachot

Principal Consultant / Database Evangelist
Oracle ACE Director, Oracle Database OCM 12c
AWS Database Specialty certified, AWS Data Hero
Oak Table member

RSS for this blog: feed
Twitter: @FranckPachot
LinkedIn : www.linkedin.com/in/franckpachot
Podcast en français: DBPod