maximum size of ORALCE_SID

The maximum length of ORACLE_SID environment variable seems to be a mystery in many places.
Recently I reviews a document where the max length was defined as 8 characters. I was quite sure this was not right, but I didn't know the correct value. A short ressearch did not find anythign useful in the docs - the Best available there was RAC installation guide for Linux & UNIX:

The Oracle Service Identifier (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 prefix can contain up to 12 characters. 

It always refers to a ORACLE_SID prefix - but never explains the total length. As it's the RAC documentation, I assume it's more about RAC capabilities (some internals in srvctl and supporting binaries / structures).

Some other trustworthy sources at Oracle are very vague in this area:

Tom Kyte (2002):

its really 4 characters as the convention (ORCL for example).
The reason is in support of 8.3 filenames. ALRTORCL.LOG, INITORCL.ORA, etc. We use 4, you use 4 and we can get 8 character filenames.

and later there (2007):

I would stick with 8 or less - better safe than sorry.

Connor McDonald (2017):

So even if you find a platform that does more than 8, I would never go more than that.

Another approach can be to identify Data Dictionary views which report the Oracle System ID.
One is v$instance.INSTANCE_NAME with Datatype VARCHAR2(16).
Another is v$thread.INSTANCE with Datatype VARCHAR2(80).
Oracle is really consequent in it's ambiguity!


So it's worth to do some tests!

My lab is 18c & 19c on Linux. Maybe other OS might show other limits!

The longest ORACLE_SID I could set and use to start an instance was

30 characters 

long!
(In my case I used QWERTZUIOP1234567890ASDFGHJKLY)

With this ORACLE_SID set, a proper value is returned by v$thread

SQL> select instance from v$thread;

INSTANCE
--------------------------------------------------------------------------------
QWERTZUIOP1234567890ASDFGHJKLY

but v$instance does not show anything:

select  instance_name  from v$instance;

INSTANCE_NAME
----------------


SQL>

With an ORACLE_SID of 16 characters (QWERTZUIOP123456) v$instance is fine:

select  instance_name  from v$instance;

INSTANCE_NAME
----------------
QWERTZUIOP123456

SQL>

With any ORACLE_SID of 31 characters or more I got

ORA-00600: internal error code, arguments: [OSDEP_INTERNAL], [], [], [], [], [], [], [], [], [], [], []
ORA-27302: failure occurred at: slsid1
ORA-27303: additional information: Unable to get environment variable ORACLE_SID

Now I at least tested a possible max. length of ORACE_SID (18c & 19c on Linux) of 30 characters.
Whenever I use the System ID in scripts, I should query v$thread, NOT v$instance.
I recommend to use not more than 16 characters so it's still visible in v$instance also.

glitches on runInstaller attachHome

Cloning ORACLE_HOMEs should be quite well known by now.

Still sometimes there are some glitchs to take care of.
In this case I cloned an existing ORACLE_HOME (dbhome_1) to a new one (dbhome_2) so I can apply a patch and reduce the downtime to <switching ORACLE_HOME> plus <running datapatch>.

Running the runInstaller was straight forward:

./runInstaller -silent -attachHome -invPtrLoc /var/opt/oracle/oraInst.loc \
 ORACLE_HOME="/path_to/oracle/product/18.0.0/dbhome_2" \
 ORACLE_HOME_NAME="OraDB18Home2" CLUSTER_NODES="{MY_NODE}" -local

But when I tried to start the instance (in an Oracle Restart configuration) a strange error occured:

srvctl start database -db MY_DATABASE

PRCR-1079 : Failed to start resource ora.MY_DATABASE.db
CRS-5017: The resource action "ora.MY_DATABASE.db start" encountered the following error:
ORA-12777: A non-continuable error encountered.  Check the error stack for additional information [ksm_check_ob_paths:1], [ORACLE_BASE], [], [].
ORA-08275: Environment variable unset
. For details refer to "(:CLSN00107:)" in "/ORACLE_BASE/diag/crs/MY_NODE/crs/trace/ohasd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.MY_DATABASE.db' on 'rznjvh175' failed

and the tracefile only contains lines of limited help:

2019-12-03 10:37:17.014 : USRTHRD:748464: {0:6:2} Thread:ASM DedicatedThread AsmCommonAgent::DedicatedThread::run  220 isRemoteListenSet=0
2019-12-03 10:37:17.651 :CLSDYNAM:51523: [ora.MYDATABASE.db]{0:0:36611} [start] ORA-12777: A non-continuable error encountered.  Check the error stack for additional information [ksm_check_ob_paths:1], [ORACLE_BASE], [], [].
ORA-08275: Environment variable unset

With no real luck at googling or searching MOS I had a lucky punch on
orabase command returns no value instead of ORACLE_BASE value. (Doc ID 2225573.1)

So I decided to edit the file /path_to/oracle/product/18.0.0/dbhome_2/install/orabasetab
to

/path_to/oracle/product/18.0.0/dbhome_2:/path_to/oracle:OraDB18Home2:N:

That made the trick and HAS can start the instance with new ORACLE_HOME.

I would assume runInstaller should to this task, and I have no idea why it was not done.
Just one more line on my list of post-activities.