Difference between DB_NAME and DB_UNIQUE_NAME


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
  • the database name in the OCR;
  • the database directory name within ASM disk groups
  • the diagnostic directory name. 
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:
  • The database will be called PROD in the OCR
  • The database directory name within ASM will be PROD e.g. +DATA/PROD/ and +FRA/PROD/
  • The diagnostic location will be $ORACLE_BASE/diag/rdbms/prod 
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:
  • The database will be called PROD_EAST in the OCR (the database name is still PROD)
  • The database directory name within ASM will be PROD_EAST e.g. +DATA/PROD_EAST/ and +FRA/PROD_EAST/
  • The diagnostic location will be $ORACLE_BASE/diag/rdbms/prod_east
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.
  • The database will be called PROD in the OCR. The original data files etc will have been created in +DATA/PROD
  • Any archived redo logs will be in +FRA/PROD
  • The diagnostic location is $ORACLE_BASE/diag/rdbms/prod
So what happens when we rename DB_UNIQUE_NAME from PROD to PROD_EAST?
  • SRVCTL does not allow databases to be renamed so the entries for the database and instances must be deleted and recreated using the new name of PROD_EAST.
  • If we really care then we need to copy all the data files and redo logs from +DATA/PROD to +DATA/PROD_EAST and to update the control file
  • I have never figured out how to relocate files in the FRA; it may not even be possible.
  • The diagnostic location is created automatically as $ORACLE_BASE/diag/rdbms/prod_east,  but the previous location will still exist and contains some useful files notably the alert.log generated during database creation.
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 Oracle product manager will read this and fix the problem in the next patch set….

at Wednesday, August 22, 2012  

6 comments:

Anonymous said... April 13, 2013 at 5:54 AM  

Very useful info. Thank you.

Anonymous said... April 19, 2013 at 12:29 PM  

Now i have cleared the concept , Thanks

Anonymous said... June 12, 2013 at 12:19 PM  

Excellent explanation. Many thanks for sharing.

Regards,
Amit

Anonymous said... June 13, 2013 at 5:32 AM  

Good Explanation ... Thanks

Anonymous said... August 6, 2013 at 11:21 PM  

Excellent Info for the db_unique_name. Thank you so much.

Sitao Chen said... March 19, 2014 at 11:19 AM  

Thanks for the documentation. I still don't understand why the CREATE SPFILE statement will work if the db_unique_name is the same as the db_name, as there is still no directory PROD been created at that stage.

Post a Comment

Powered by Blogger.