This is an interesting point that came up recently, where someone had a DBaaS environment running on an Exadata system for a couple of months, with no issues. It has recently started to return errors on the Database Cloud Self Service portal in the “Placement Algorithm” region:
Constraint : Number Of Databases
Current Resource Usage: 18
Total Resource Available: 10
Maximum Limit Defined: 10
Requested Value: 1
Total Resource that can be used: 10
Result: Failed. Insufficient resource available.
But if I grep for PMON processes, the number of databases running on the machine is 8. Why would I see such an error?
The reason here is fairly simple – there are a couple of databases that are down, and if we include those, the number of databases on the machine has reached the maximum limit defined. But the explanation needs a bit more detail.
So why do we include databases that are down in this count? Well, even in a non-cloud environment, shutting down a database doesn’t release the storage used by that database (thankfully!) A mere shutdown of the database cannot be assumed to reduce the quota count, as the database may be restarted at any time, either manually by the database administrator using Enterprise Manager Cloud Control, EM Express, or SQL*Plus, or automatically, using tools such as cron or init.d(rc2.d) and the machine resources will again be used. Over-allocation in quota is generally used to solve such requirements where not all databases will be up and running all the time.
The next question that comes to mind is whether this algorithm is based only on the number of databases, or machine resources such as CPU and memory when the database is down? At a high level, the answer is the algorithm is controlled by 3 factors:
- The number of RDBMS instances*: this is the number of instance targets on a specific host. This data is fetched from the Enterprise Manager repository, which is why databases that are down are counted. So if you really want to ensure a database that is shutdown does not count towards the total number of databases, then remove the database target from being managed by Enterprise Manager. 🙂
- Memory – there are two flavours here:
- Memory usage based on the host – calculated as the average memory utilization on the host over the last 7 days
- Memory usage based on the databases – calculated as the total sum of memory allocated for the databases on the host
- CPU – the average CPU utilization over the lat 7 days on the host
Hopefully this explains why we call the overall theme of database placement as ‘Optimistic’. 🙂
*You’ll notice I used the term “RDBMS instances” here, NOT “database instance”. There’s a reason for that. It’s one of my pet peeves to see the term “database instance”. In Oracle terms, a database is a set of physical files – the datafiles, redo log files and control files (but not the initialization parameter file, whichever variety – SPFile or the old init.ora text file – you use). An instance is the set of background processes and memory structures in the SGA. While there is most often a one to one mapping between a database and an instance (the exception of course being a Real Application Clusters environment where multiple instances share a single database), there really is no overlap between the term “database” and the term “instance”. Yes, I know our products use the term all the time, and so does our documentation, but to my pedantic way of thinking, they’re WRONG! 🙂