Why am I seeing “Result: Failed. Insufficient resource available” messages?

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:

  1. 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. 🙂
  2. 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
  3. 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! 🙂

Pete

After 22 years of working at Oracle in just about every role except Marketing and Support, I am now working as a Senior Managed Services Consultant with Data Intensity, specializing in Oracle Database technology, High Availability and Disaster Recovery solutions. I am also a member of the OakTable Network, and have presented at RMOUG Training Days, Hotsos Symposia, Oracle OpenWorld conferences, and other user group events. I have co-authored the Expert Oracle Enterprise Manager 12c and Practical Oracle Database Appliance books published by Apress, and am one of the authors of the Building Database Clouds in Oracle Database 12c book published by Addison Wesley.

Leave a Reply

Your email address will not be published. Required fields are marked *

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