Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am somewhat confused by these terms in the init.ora file:

  1. db_domain
  2. db_name
  3. instance_name

plus the database name which is provided to the create database command. I do not completely understand how they sum up. I have read the Oracle docs several times but still failed to understand. As far as I understand, a database can be mounted and used by several instances in a RAC which I do not use. This means that db_name has to be the same as in create database?!

This is my setup:

I have 2 machines host1, host2. Both are in the same subdomain dom1.company.net. Both have an instance which hosts the same database but one in for the production system and the other one is the test system.

If I name both data, do I have a name collision?

Thanks for clearing up.

share|improve this question
2  
I'd strongly recommend not giving your test & production databases the same name. Accidents can & will happen. –  Phil Mar 23 '12 at 9:14
    
I might append a 't' to the test database name. But they are on different machines. How could that case you have described happen? –  Michael-O Mar 23 '12 at 10:24
1  
Same username/password and an incorrect tnsnames? I really wouldn't take the chance if both networks are available from dev/test machines. –  Phil Mar 23 '12 at 10:26
    
I second what Phil is saying - any measure you can take to make the two more distinctive is worth taking IMO. –  Jack Douglas Mar 23 '12 at 10:32
    
Phil, passwords won't resemble. Only distinct passwords are allowed. –  Michael-O Mar 23 '12 at 11:22
add comment

2 Answers 2

up vote 2 down vote accepted

No, you do not have a name collision. Your DB_DOMAIN, DB_NAME, and INSTANCE_NAME can all be identical as long as the databases are on different hosts (as you have indicated). However, as others have stated using the same DB_NAME is a bad idea for anything other than perhaps a recovery operation. A policy enforcing distinct passwords will be broken. Connections will be confused. Most things you can do to ensure that changes are not inadvertently made on production are probably worth the hassle.

Here is the relevant documentation:

DB_DOMAIN

In a distributed database system, DB_DOMAIN specifies the logical location of the database within the network structure. You should set this parameter if this database is or ever will be part of a distributed system. The value consists of the extension components of a global database name, consisting of valid identifiers (any alphanumeric ASCII characters), separated by periods.

Note: Oracle recommends that you specify DB_DOMAIN as a unique string for all databases in a domain.

This parameter allows one department to create a database without worrying that it might have the same name as a database created by another department. If one sales department's DB_DOMAIN is JAPAN.ACME.COM, then their SALES database (SALES.JAPAN.ACME.COM) is uniquely distinguished from another database with DB_NAME = SALES but with DB_DOMAIN = US.ACME.COM.

If you omit the domains from the name of a database link, Oracle expands the name by qualifying the database with the domain of your local database as it currently exists in the data dictionary, and then stores the link name in the data dictionary.

DB_NAME

DB_NAME specifies a database identifier of up to 8 characters. This parameter must be specified and must correspond to the name specified in the CREATE DATABASE statement.

If you have multiple databases, the value of this parameter should match the Oracle instance identifier of each one to avoid confusion with other databases running on the system. The value of DB_NAME should be the same in both the standby and production initialization parameter files.

INSTANCE_NAME

In a Real Application Clusters environment, multiple instances can be associated with a single database service. Clients can override Oracle's connection load balancing by specifying a particular instance by which to connect to the database. INSTANCE_NAME specifies the unique name of this instance.

In a single-instance database system, the instance name is usually the same as the database name.

share|improve this answer
    
Deducing from response I presume I could use the same SERVICE_NAME for both DB types as long as they are on different hosts. E.g. db.dom1.company.net running on host test-host and prod-host. –  Michael-O Mar 23 '12 at 20:34
    
Yes, but the same bad idea info applies. –  Leigh Riffel Mar 23 '12 at 21:05
    
So I should append e.g. a t to all test database names? –  Michael-O Mar 23 '12 at 22:22
1  
That would be reasonable or you could pre-pend it. Saying Test MyDB makes more sense to me than MyDB Test, on the other hand they will sort together when you append it. Any way you like, just make it clear. –  Leigh Riffel Mar 26 '12 at 13:35
add comment

The primary difference is in your TNSNAMES.ORA file deployed for your clients. The connection names there will differentiate which server+instance combination you intend on working with.

The DB_NAME / INSTANCE_NAME has a couple of purposes, both for failover databases, or RAC (cluster) databases. These would share a db_name, but have unique instance_names.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.