Freitag, 13. Dezember 2019

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 
(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;


but v$instance does not show anything:

select  instance_name  from v$instance;



With an ORACLE_SID of 16 characters (QWERTZUIOP123456) v$instance is fine:
select  instance_name  from v$instance;



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.

Keine Kommentare: