//
you're reading...
Database, Oracle

Length of ORACLE_SID, DB_NAME, and INSTANCE_NAME

This is for 11gR2.

The length of DB_NAME is limited to 8 characters;

DB_UNIQUE_NAME is limited to 30 characters. DB_UNIQUE_NAME  can contain alphanumeric, underscore (_), dollar ($), and pound (#) characters, but must begin with an alphabetic character.

INSTANCE_NAME defaults to ORACLE_SID. Oracle document does not specify the limit for INSTANCE_NAME length. However if you describe V$INSTANCE view you can see that column INSTANCE_NAME  has VARCHAR2(16), indicating the  limit of 16 characters.

Because INSTANCE_NAME defaults to ORACLE_SID you need to be careful when deciding SID before creating a database. Oracle does not clearly define the limit for SID (system identifier). Following are what I found from two 11gR2 documents about ORACLE_SID.

===========================================================

Oracle® Database Installation Guide, 11g Release 2 (11.2) for Linux, E10840-04  (Same description in other Unix installation documents too)

When you enter the Global Database Name, Oracle Universal Installer automatically populates the SID field with the database name, but you can change this SID to another name in Advanced installation. Oracle Universal Installer limits the SID to 12 alphanumeric characters and the SID cannot contain an underscore (_), dollar ( $), or pound (#).

Oracle® Real Application Clusters Installation Guide, 11g Release 2 (11.2) for Linux and UNIX,  E17214-08

The SID prefix is the first 8 characters of the database name. The SID prefix can contain only the characters a-z, A-Z, and 0-9. The SID prefix cannot contain operating system special characters, so if you use special characters in the first 8 characters of the database name, then these special characters are omitted in the SID prefix. There is a single SID prefix for every database. The SID prefix for a database must be unique within the cluster.
For an Oracle RAC database, each instance has a unique identifier, ORACLE_SID, which consists of the SID prefix and an instance number. The ORACLE_SID for Oracle RAC database instances is generated differently, depending on how you choose to manage the database. If you select a policy-managed database, then Oracle generates the SID in the format name_#, where name is the first eight alphanumeric characters of
DB_UNIQUE_NAME, and # is the instance number. If you select an admin-managed database, then DBCA generates the SID for the instance names in advance, and the SID is in the format name#.

For example, if the DB_UNIQUE_NAME for a database is orl$racprod2551, then the following SID values are used:

Database or Instance Type                          Value used for ORACLE_SID

—————————————               ———————————————-
Single-instance Oracle database                orlracpr
Policy-managed Oracle RAC instance     orlracpr_1
Admin-managed Oracle RAC instance    orlracpr1

============================================================

It is interesting to note that in single instance installation, Oracle says SID should not contain an underscore (_). But for RAC the example given here has an underscore in SID.

Advertisements

About Hong Wang

I am an Oracle DBA, working in Oracle database since version 7.3. Worked in both application development and production support. Many experiences in real world complicate problems and database projects. This blog serves as a collections of notes I write on my database studies as well as issues I encountered/solved. Your comments are well welcome.

Discussion

7 thoughts on “Length of ORACLE_SID, DB_NAME, and INSTANCE_NAME

  1. is the instance number varied in the same database or permanent for a particular database ?

    SID=database name + instance number

    Posted by ravi | April 9, 2012, 12:07 am
  2. The system identifier (SID) for each database must begin with an alphabetic character, and is limited by the installer to 12 alphanumeric characters in length.
    “Oracle® Real Application Clusters Installation Guide
    11g Release 2 (11.2) for Microsoft Windows
    Part Number E10818-04”

    Posted by Volchek | March 2, 2013, 12:43 pm
  3. Can we get ORACLE_SID for a particular instance from some table depending on instance_name or inst_id ?

    Posted by Gaurav | August 7, 2013, 9:29 am
  4. You define ORACLE_SID before you create an instance. INSTANCE_NAME defaults to ORACLE_SID. When you log on to a database server and don’t know any ORACLE_SID, the best way to get it is to grep pmon(or smon) process on your server.
    % ps -ef|grep pmon|grep -v grep
    oracle 18765 1 0 2012 ? 00:19:30 ora_pmon_O60MWT
    oracle 30736 1 0 Apr22 ? 00:05:28 ora_pmon_R60MWT

    Posted by hongwang | August 7, 2013, 9:23 pm
  5. Thanks a lot for the explanation. Your blog cleared many doubts I was having. Just one little thing. As you said that INSTANCE_NAME defaults to ORACLE_SID. Can the instance name be altered while the instance is running? i.e. Can ORACLE_SID and INSTANCE_NAME be different in any case?

    Posted by Gaurav | August 11, 2013, 6:02 am
    • INSTANCE_NAME cannot be altered when the instance is running. However you can change it in pfile and restart the instance using that pfile. I don’t see any meaning of doing so in reality though.

      Thanks,
      Hong

      Posted by hongwang | August 21, 2013, 2:48 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: