Oracle – Difference Between SID and Database Name

oracle

all.

I've searched this topic in Google and there are several information, but it makes me confused more..

At first, I thought SID is the unique key for "database" not "instance", and must be matched with "DB_NAME".

However, In a post, it says SID doesn't need to match DB NAME, global name, unique name, and instance name.. with some practical configuration. In another post, it says SID is combination of database name + instance number..

In my short knowledge,

Instance is server ORACLE processes are running, which contains SGA.

Database is just storage that user applications' data is stored. When instance runs, it access to DB, mount the control files, open the data files and redo logs.

If the above comment is right, I think SID must be Database, and.. could be DB_NAME..

Now I'm confusing Database, Instance, SID, and DB_NAME.

Thank you for reading..

Best Answer

Database is just storage that user applications' data is stored.

Yes. It is a collection of the files (datafile, controlfile, redo log, tempfile, block change tracking file, etc.) on the disk.

Instance is server ORACLE processes are running, which contains SGA.

Yes. Instance is the collection of processes + SGA.

Typically one instance belongs to one database, but a RAC database can have multiple instances on multiple nodes.

db_name is the name of the database. It is stored in the database files as and pfile/spfile as well.

SID identifies the instance by the name. It can be changed by setting the instance_name parameter. It is not stored in the database files, only in the pfile/spfile. (Note that it is not a strictly unique identifier, multiple instances can exist on the same server with the same name, running from different Oracle Homes).

SID (instance_name) defaults to db_name.

In case of a cluster database, SID (instance_name) defaults to db_name + instance_number.

On top of the above, SID (instance_name) can be changed by setting instance_name to a custom value.

Related Question