What is the instance name?

If your Oracle SID doesn’t match your instance name in init.ora, this is quite confusing.

Check my previous post, what is sid in oracle

In the instance_name column of the view v$instance, as well as in USERENV context, it matches the ORACLE_SID of the underlying operating system.

SQL> var ORACLE_SID varchar2(9)
SQL> set autoprint on
SQL> exec dbms_system.get_env('ORACLE_SID',:ORACLE_SID)
PL/SQL procedure successfully completed.
ORACLE_SID
------------
ORA001
SQL> select sys_context('USERENV','INSTANCE_NAME') from dual;
SYS_CONTEXT('USERENV','INSTANCE_NAME')
---------------------------------------
ORA001
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
ORA001
SQL>

This is not the same as the init.ora parameter

SQL> select name, value, description from v$parameter where name='instance_name';

NAME          VALUE     DESCRIPTION
------------- --------- ----------------------------------------
instance_name INS001    instance name supported by the instance
SQL>

The instance_name doesn’t have to match anything. It’s of relevance if you use ADR. And you probably do. Background dump dest and family are deprecated now. In your ADR docu you’ll read

{ORACLE_BASE}/diag/rdbms/{DB_UNIQUE_NAME}/{SID}/trace

But this SID is actually your init.ora instance name. And not your ORACLE_SID.

1 thought on “What is the instance name?

  1. Stuart Barkley

    Old thread but still very relevant. Thank you Laurent! This is the only post I found that actually cleared this confusion up for me.

Comments are closed.