Oracle Restart Underscore Warning!

Bobby Curtis just posted a great post about adding a database to Oracle Restart, aka srvctl. The post is a must-read, but I do have one thing to add. In Bobby's example, he leaves out the instance_name clause (noted by the -i parameter). This won't cause a problem for him because his database's db_unique_name has no underscores and matches his ORACLE_SID (which can't contain underscores anyway).

In my environment, however, nearly every database has a db_unique_name value that contains an underscore, which we use to denote the datacenter location. For example, in our dataguard configuration for "prod", prod_ny would be in New York where as prod_sf would be in San Francisco. The fact that it is part of a dataguard configuration is irrelevant, we have this configuration with standalone databases (e.g. staging1_ny) as well just to be consistent. In the first example, both databases have a db_name and ORACLE_SID of "prod", and the second example would have an ORACLE_SID of staging1.

However when you add this to Oracle Restart by the typical default syntax, you'll run into a problem. When the instance_name is not specified, srvctl will use the db_unique_name but strip out any underscore characters. This results in an instance_name value of "staging1ny" in the following example, when we were running under just "staging1" previously:

$ srvctl add database -d staging1_ny \

> -o /oracle/app/product/11.2 \

> -p /mnt/staging1/pfile/spfilestaging1.ora \

> -r PHYSICAL_STANDBY -s open -t immediate

$ srvctl config database -d staging1_ny
Database unique name: staging1_ny
Database name:
Oracle home: /oracle/app/product/11.2
Oracle user: oracle
Spfile: /mnt/staging1/pfile/spfilestaging1.ora
Domain:
Start options: open
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Database instance: staging1ny
Disk Groups:
Services:

Now the problem with this is that when you next use srvctl to start the instance, it will run under a different ORACLE_SID than you had used before, and the location of the ADR files (most notably the alert log) will change.

The solution is simply to either use -i to specify the instance_name when adding the database, or use the "srvctl modify database" command to specify it afterward, like this:

$ srvctl modify database -d staging1_ny -i staging1

Some might say this is a corner case but in my shop with many production, dev, and staging databases with many physical and snapshot standbys, it's an everyday part of my life and something that surprised me when I first started trying to herd new databases under Oracle Restart.