I have installed Oracle 19.6 on an EC2 for our Multitenant Workshop training. And of course, during the workshop we create a lot of PDBs. If you don’t have paid for the Enterprise Edition plus the Multitenant Option you can create at most 3 pluggable database. But with this option you can create up to 252 pluggable databases. Does it worth the price, which according to the public price list is USD 47,500 + 17,500 per processor, which means per-core because Oracle doesn’t count the core factor when your Intel processors are in AWS Cloud (according to the Authorized Cloud Environments paper)? Probably not because Oracle detects where you run and bridles some features depending whether you are on the Dark or the Light Side of the public cloud (according to their criteria of course).
At one point I have 3 pluggable databases in my CDB:
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED _________ ____________ ____________ _____________ 2 PDB$SEED READ ONLY NO 3 CDB1PDB01 MOUNTED 4 CDB1PDB03 MOUNTED 5 CDB1PDB02 MOUNTED
I want to create a 4th one:
SQL> create pluggable database CDB1PDB04 from CDB1PDB03; create pluggable database CDB1PDB04 from CDB1PDB03 * ERROR at line 1: ORA-65010: maximum number of pluggable databases created
It fails. The maximum number of pluggable databases is defined by MAX_PDBS, but I defined nothing in my SPFILE:
SQL> show spparameter max_pdbs SID NAME TYPE VALUE --- -------- ------- ----- * max_pdbs integer
I thought that the default was 4098 (which is incorrect anyway as you cannot create more than 4096) but it is actually 5 here:
SQL> show parameter max_pdbs NAME TYPE VALUE -------- ------- ----- max_pdbs integer 5
Ok… this parameter is supposed to count the number of user pluggable databases (the ones with CON_ID>2) and I have 3 of them here. The limit is 5 and I have an error mentioning that I’ve reached the limit. That’s not the first time I see wrong maths with this parameter. But there’s worse as I cannot change it:
SQL> alter system set max_pdbs=6; alter system set max_pdbs=6 * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-65334: invalid number of PDBs specified
I can change it in the SPFILE but it doesn’t help me to create more pluggable databases:
SQL> alter system set max_pdbs=200 scope=spfile; System altered. SQL> startup force; Total System Global Area 2147482744 bytes Fixed Size 9137272 bytes Variable Size 587202560 bytes Database Buffers 1543503872 bytes Redo Buffers 7639040 bytes Database mounted. Database opened. SQL> show parameter max_pdbs NAME TYPE VALUE -------- ------- ----- max_pdbs integer 200 SQL> create pluggable database CDB1PDB04 from CDB1PDB03; create pluggable database CDB1PDB04 from CDB1PDB03 * ERROR at line 1: ORA-65010: maximum number of pluggable databases created
Something bridles me. There’s a MOS Note ORA-65010 When Oracle Database Hosted on AWS Cloud (Doc ID 2328600.1) about the same problem but that’s in 188.8.131.52 (before MAX_PDBS was introduced) which is supposed to be fixed in AUG 2017 PSU. But here I am 3 years later in 19.6 (the January 2020 Release Update for the latest version available on-premises).
So, Oracle limits the number of pluggable databases when we are on a public cloud provider which is not the Oracle Public Cloud. This limitation is not documented in the licensing documentation which mentions 252 as the Enterprise Edition limit, and I see nothing about “Authorized Cloud Environments” limitations for this item. This, and the fact that it can come and go with Release Updates put customers at risk when running on AWS EC2: financial risk and availability risk. I think there are only two choices, on long term, when you want to run your database on a cloud: go to Oracle Cloud or leave for another Database.
How does the Oracle instance know on which public cloud you run? All cloud platforms provide some metadata through HTTP api. I have straced all sendto() and recvfrom() system calls when starting the instance:
strace -k -e trace=recvfrom,sendto -yy -s 1000 -f -o trace.trc sqlplus / as sysdba <<<'startup force'
And I searched for Amazon and AWS here:
This is clear: the instance has a function to detect the cloud provider (kgcs_clouddb_provider_detect) when initializing the SGA in a multitenant architecture (kpdbInitSga) with the purpose of detecting non-oracle clouds (kscs_is_non_oracle_cloud). This queries the AWS metadata (documented on Retrieving Instance Metadata):
[[email protected] ~]$ curl http://169.254.169.254/latest/meta-data/services/domain amazonaws.com/
When Oracle software sees the name of the enemy in the domain name amazonaws.com, it sets an internal limit for the number of pluggable databases that overrides the MAX_PDBS setting. Ok, I don’t need this metadata and I’m root on EC2 so my simple workaround is to block this metadata API:
[[email protected] ~]# iptables -A OUTPUT -d 169.254.169.254 -j REJECT [[email protected] ~]# iptables -L Chain OUTPUT (policy ACCEPT) target prot opt source destination REJECT udp -- anywhere 10.0.0.2 udp dpt:domain reject-with icmp-port-unreachable REJECT all -- anywhere 10.0.0.2 reject-with icmp-port-unreachable
Then restart the instance and it works: I can set or reset MAX_PDBS and create more pluggable databases.
I can remove the rule
[[email protected] ~]# iptables -D OUTPUT -d 169.254.169.254 -j REJECT
If, for watever reason I want to revert back.
Finally, because they had many bugs with the MAX_PDBS soft limit, there’s a parameter to disable it and this disables also the hard limit:
SQL> alter system set "_cdb_disable_pdb_limit"=true scope=spfile; System altered.
Thanks to Mauricio Melnik for the heads-up on that:
MOS 2538172.1 _cdb_disable_pdb_limit=true …. maybe?
— Mauricio Melnik (@maurimelnik) March 26, 2020
However, with this parameter you cannot control anymore the maximum number of PDBs so don’t forget to monitor your AUX_COUNT in DBA_FEATURE_USAGE_STATISTICS.
Here was my discovery when preparing the multitenant workshop lab environment. Note that given the current situation where everybody works from home when possible, we are ready to give this training full of hands-on exercises though Microsoft Teams and AWS EC2 virtual machines. Two days to be comfortable when moving to CDB architecture, which is what should be done this year when you plan to stay with Oracle Database for the future versions.
In order not to sound too negative here, this limit on AWS platforms has been removed in the past and this may be a bug re-introduced with the change from 1 to 3 PDBs in Standard Edition.