Over the past couple of years I have implemented Oracle Data Guard physical standby around a dozen times. Every time I fine-tune the procedure a little more - either eliminating an unnecessary step or discovering some new and often undocumented Oracle functionality.
My preference when building a standby environment is to keep the design as symmetrical as possible. This preference mainly affects naming conventions.
The DB_NAME parameter specifies the name of the database and is the same at all locations. In this example the DB_NAME parameter is PROD.
The DB_UNIQUE_NAME parameter allows a location specific alias to be created for a database. I always try to avoid using names indicative of the role such as PROD_PRIMARY and PROD_STANDBY. These names work well until a switchover is performed at which point the switch-back operation can become really confusing and difficult to document. Therefore I usually try to use geographical values for the DB_UNIQUE_NAME parameter such as PROD_EAST and PROD_WEST. The following discussion uses these values.
The DB_UNIQUE_NAME parameter defaults to the value of the DB_NAME parameter. This is useful when you are creating a new database; fewer parameters to specify; but is not so efficient if that database will become part of a standby configuration.
The DB_UNIQUE_NAME parameter is actually used by Oracle in a number of places. It is used to specify:
For example if I use DBCA to create a database using the DB_NAME of PROD, the DB_UNIQUE_NAME will default to PROD and will result in the following:
Under normal conditions this is probably what you want. However for a Data Guard standby database you may however, want the location specific value for the DB_UNIQUE_NAME in which case:
It is easy enough to modify the DB_UNIQUE_NAME parameter after the database has been created, but then you are left with a few problems.
So what happens when we rename DB_UNIQUE_NAME from PROD to PROD_EAST?
It is possible to modify the DB_UNIQUE_NAME parameter, but the result is a bit messy; not so good for standards-conscious organizations.
How can you work around this? Well the answer is really simple; set the DB_UNIQUE_NAME parameter during database creation. The parameter can be set in the Initialization Parameters page in the DBCA.
However, setting the DB_UNIQUE_NAME parameter in the DBCA will probably result in a failure during database creation. The cause of the failure is the CREATE SPFILE file which is one of the first statements executed during database creation.
If the DB_UNIQUE_NAME is PROD_EAST then by default the CREATE SPFILE statement will attempt to create an SPFILE called +DATA/PROD_EAST/spfilePROD_EAST.ora. However at this stage the +DATA/PROD_EAST directory will not exist within the +DATA disk group and the statement will fail. This appears to be an issue with the CREATE SPFILE statement; other statements such as CREATE DATABASE appear to create the directory if it does not already exist.
You can workaround this problem by creating the directory manually using SQL*Plus or ASMCMD. For example:
$ asmcmd mkdir +DATA/PROD_EAST
If the directory is created before DBCA is executed then the database should be created successfully with the correct names in the OCR, ASM and diagnostic areas.
However, this should really not be necessary; I don't have access to the source code, but I'm guessing that it would be trivial for Oracle to fix the CREATE SPFILE to create intermediate ASM directories if they do not exist. So I am hoping the Oracle product manager for ASM will read this and fix the problem in the next patch set....