FILED UNDER DB_NAME, DB_UNIQUE_NAME, INSTANCE_NAME, ORACLE_SID
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.