01 December, 2014

A nasty gotcha in DBCA templates 12c

I want to create four databases on a Linux host. The databases are of two kinds ("big DB" and "small DB"), but otherwise very similar to one another, and I will want to create a new set of four twice a year - a perfect opportunity to use DBCA templates! So, I click through the wizard, starting with the "Custom Database" template, adjusting paths and sizes and character sets and I am soon ready to create the first database. I choose to create an appropriately named template and let DBCA do its magic.
As soon as DBCA has finished creating my "big DB" database, I proceed to create a template for the "small DB" databases, this time starting with my saved template. I then use the newly created "small DB" template to create the smallish databases. So far so good.
I switch the instances in /etc/oratab to autostart and go about doing other important stuff which requires me to restart the machine.
Of the four databases, only one is open - the rest are merely mounted. I run a search for the alert.log files (12c appears to have moved them outside of background_dump_dest) and my head nearly explodes: I find five alert logs; moreover, the paths to some(!) of them include directories named after different databases, as in $ORACLE_BASE/diag/rdbms/dbnameX/sidY/trace/alert_sidY.log!
$ORACLE_HOME/dbs contains one SPFILE and three initSID.ora files!
It turns out that when you create a template from a template (with or without creating a database at the same time), the new template references the original's SPFILE literally. Something like this:
Built in template "Custom Database" ($ORACLE_BASE/product/12.1.0/dbhome_1/assistants/dbca/templates/New_Database.dbt):

Template BIG derived from "Custom Database" ($ORACLE_BASE/product/12.1.0/dbhome_1/assistants/dbca/templates/BIG.dbt):


Template SMALL derived from "BIG" ($ORACLE_BASE/product/12.1.0/dbhome_1/assistants/dbca/templates/SMALL.dbt):


See the problem? The creation procedure of each subsequent database has overwritten the first instance's SPFILE with its own parameters (including - critically - CONTROL_FILES). Even deleting the databases using DBCA is no longer an option - I had to do it manually.

The solution? Copy the line about the SPFILE from New_Database.dbt to BIG.dbt manually - afterwards all templates based on BIG.dbt will behave correctly.

The moral of the story: always let DBCA generate scripts, but do not let it create the database for you before you have checked the scripts SID.sh (SID.bat) and SID.sql.

27 November, 2014

ORA-27107: Farewell, automatic huge pages...

I've just run into this problem head first and without warning while creating a new database:

ORA-27107: AUTO value for USE_LARGE_PAGES parameter is no longer supported

Introduced in,  the value was a quick and somewhat dirty way to use huge pages on Linux machines without the hassle of having to reboot after each change in SGA usage (creation/deletion of instances, changing sga_target, etc.)

While my and instances are happily running with use_large_pages=auto, an attempt to create a fully patched (PSU instance with this value failed.

MetaLink shows a relevant internal enhancement request (Bug 14500387 : PROVIDE A MECHANISM TO DISABLE USE_LARGE_PAGES=AUTO), but there is no evidence it has been implemented. It is not in the list of bugs fixed by the October 2014 PSU.

The only reference I found was in the documentation - who would have thought the docs can get ahead of MetaLink!

Now, if you will excuse me, I need to do some calculating and prealocating of huge pages. What was that root password again?