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.
Bang!
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):
      {ORACLE_HOME}/dbs/spfile{SID}.ora

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

      {ORACLE_HOME}/dbs/spfileBIG.ora

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

      {ORACLE_HOME}/dbs/spfileBIG.ora

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.

No comments: