2

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..

Improve this question
3
  • database name is the name of the database. SID is short for session id - a unique identifier for each session in a database.
    – Edgar Allan Bayron
    Commented May 28, 2018 at 6:12
  • @EdgarAllanBayron: I think in this case SID refers to the System Identifier
    – user1822
    Commented May 28, 2018 at 6:29
  • 1
    From the manual: "*The system identifier (SID) is a unique name for an Oracle database instance on a specific host."
    – user1822
    Commented May 28, 2018 at 6:31

1 Answer 1

Reset to default
2

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.

Improve this answer
4
  • First of all, thank you so much for your kindness. As your reply, I can think SID can be DB_NAME and DB_NAME + INSTANCE_NO. It's up to the environment. The thing is SID represents Instance and multiple instances can have the same SID. Correct???
    – owcred601
    Commented May 28, 2018 at 7:59
  • And also, database name is stored in DB_NAME in database files and spfile, pfile. So, db_name and sid is totally different.
    – owcred601
    Commented May 28, 2018 at 8:04
  • If I create a CDB, and then PDB. PDB can't have SID?
    – owcred601
    Commented May 28, 2018 at 8:46
  • @owcred601 PDBs reside in the same database (CDB) and its instance(s). So they do not have their own SIDs, because they are in the same instance. Services are used for connecting to PDBs. Actually, services should be used for connecting all databases. SID should have been forgotten at least a decade ago. This stands for application and client connections. For administrative purposes, SID still has its use.
    – Balazs Papp
    Commented May 28, 2018 at 8:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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