different_oracle

Original page is by Marco Mischke All about Oracle naming - Marco Mischke but it's too good to lose so in case his site goes down…

All about Oracle naming

Todays blog post is meant to bring some light to the different names that are used in an Oracle database environment. I’ve been asked these questions about naming a million times. And there are so many of these naming parameters:

ORACLE_SID environment variable
SID_NAME and GLOBAL_DB_NAME in listener.ora
the TNS-Alias and SERVICE_NAME parameter in tnsnames.ora
DB_NAME, DB_UNIQUE_NAME, SERVICE_NAMES and INSTANCE_NAME in [s]pfile

These parameters are all there for a reason, but it is not obvious how they interact. So let’s start with a single database server and see how the startup procedure for an instance works:

set ORACLE_SID environment variable to something, e.g. ORCL

sqlplus “/ as sysdba”
startup

Oracle tries to find the parameters in $ORACLE_HOME/dbs/spfile<ORACLE_SID>.ora e.g. spfileORCL.ora,
if there is no spfile, it tries the pfile $ORACLE_HOME/dbs/init<ORACLE_SID>.ora e.g. initORCL.ora,
if there is no pfile, it tries the general pfile $ORACLE_HOME/dbs/init.ora.
Processes are started and named ora_<process>_<ORACLE_SID>, e.g. ora_smon_ORCL
if a password file should be used, Oracle looks for “$ORACLE_HOME/dbs/orapw<ORACLE_SID>” and opens it
the “instance_name” parameter defines the name of the instance and defaults to $ORACLE_SID
the “db_unique_name” parameter uniquely identifies the database system and defaults to “db_name”. This important for Data Guard environments where you have the same database running several times
the trace directories in DIAGNOSTIC_DEST are named diag/rdbms/<db_unique_name>/<ORACLE_SID>
the freshly started instance registers the following things with the listener
the “instance_name” for the default service which is “db_unique_name”.”db_domain”
the “instance_name” for any other service given in “service_names” parameter
the parameter “db_name” is used to check if the database files really belong to to this instance

Let's look at the following example where I set all the parameters to different values to outline their usage:

[oracle@vm104 dbs]$ export ORACLE_SID=ORCL
 
[oracle@vm104 dbs]$ cat initORCL.ora
db_name=ORCLDB
db_unique_name=ORCLUQ
db_domain=DOMAIN.COM
instance_name=ORCLINST
service_names=ORCLSVC.FOO.ORG
memory_target=800M

[oracle@vm104 dbs]$ sqlplus / as sysdba
 
SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 9 10:26:14 2016
 
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
 
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.
 
Total System Global Area  838860800 bytes
Fixed Size                  2929936 bytes
Variable Size             520096496 bytes
Database Buffers          310378496 bytes
Redo Buffers                5455872 bytes
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics
and Real Application Testing options

[oracle@vm104 dbs]$ ps -ef|grep ora_smon | grep -v grep
oracle   10260     1  0 10:26 ?        00:00:00 ora_smon_ORCL

[oracle@vm104 dbs]$ ls /u01/app/oracle/diag/rdbms/orcluq/ORCL/
alert  hm        incpkg  lck  metadata       metadata_pv  sweep
cdump  incident  ir      log  metadata_dgif  stage        trace

[oracle@vm104 dbs]$ lsnrctl status
 
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 09-AUG-2016 10:31:51
 
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
 
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                07-JUN-2016 12:58:30
Uptime                    62 days 21 hr. 33 min. 21 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/grid/12.1.0/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/vm104/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.16.36.104)(PORT=1521)))
Services Summary...
Service "ORCLSVC.FOO.ORG" has 1 instance(s).
  Instance "ORCLINST", status BLOCKED, has 1 handler(s) for this service...
Service "ORCLUQ.DOMAIN.COM" has 1 instance(s).
  Instance "ORCLINST", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully

So that’s it for the local naming. Now we come to the networking and to the corresponding files “tnsnames.ora” and “listener.ora”. There are other parameter to take care of.

You might need to add some static services to the listener if you want to do RMAN duplicates or simple want to connect as SYSDBA to a non-running instance from a remote host. It is similar to connecting locally, but instead of setting the environment variables, we just tell the listener the details. And we will need to set up the clients tnsnames.ora properly.

First the listener, we can define static services for each listener following this pattern:

SID_LIST_<name of listener> =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = <name that will be used as service>)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
      (SID_NAME=<that will be ORACLE_SID>)
    )
  )

This will result in the listener.ora:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL_GLB_NAME.MYDOMAIN.COM)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
      (SID_NAME=ORCL)
    )
  )

Now let’s check what happens to the listener services:

[oracle@vm104 ~]$ lsnrctl reload
 
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 09-AUG-2016 10:47:18
 
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
The command completed successfully

[oracle@vm104 ~]$ lsnrctl status
 
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 09-AUG-2016 10:47:23
 
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                07-JUN-2016 12:58:30
Uptime                    62 days 21 hr. 48 min. 52 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/grid/12.1.0/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/vm104/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
Services Summary...
Service "ORCL_GLB_NAME.MYDOMAIN.COM" has 1 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...

Ok, so far, so good. To establish a connection from a remote host, we need to modify the “tnsnames.ora” on the remote host.

First the pattern:

<My TNS Alias>=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vm104)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = <service name that is known to the listener>)
    )
  )

We can use any value for “My TNS Alias” here, but we have to care about the SERVICE_NAME. So the “tnsnames.ora” will look like this:

ORCL_ARTIFICIAL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vm104)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL_GLB_NAME.MYDOMAIN.COM)
    )
  )

And to finally connect to the idle instance remotely, we need a password file to authenticate the user:

[oracle@vm104 dbhome_1]$ orapwd file=$ORACLE_HOME/dbs/orapwORCL password=oracle
 
[oracle@vm104 dbhome_1]$ ls $ORACLE_HOME/dbs/*ORCL*
/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initORCL.ora
/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwORCL

Now this is the point when we can use “My TNS Alias” for connecting to our idle instance:

[oracle@vm104 dbhome_1]$ sqlplus sys/oracle@ORCL_ARTIFICIAL as sysdba
 
SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 9 11:03:49 2016
 
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
 
Connected to an idle instance.
 
SQL> startup nomount
ORACLE instance started.
 
Total System Global Area  838860800 bytes
Fixed Size                  2929936 bytes
Variable Size             520096496 bytes
Database Buffers          310378496 bytes
Redo Buffers                5455872 bytes

This is the complete way:

hand the alias <My TNS Alias> to SQL*Plus
find alias <My TNS Alias> in tnsnames.ora and get <hostname>, <port> and <registered SERVICE_NAME>
connect to given <hostname> and <port> and ask for connection to <registered SERVICE_NAME>
if the listener does not know this <registered SERVICE_NAME> this will fail
the listener forks a process for <ORACLE_SID> given in the SID_NAME parameter of the SID_LIST
this process tries to find the password file orapw<ORACLE_SID> and authenticate the user
the startup procedure is then identical to the one I described above

I hope this helps to get a better understanding of what parameter is being used for what purpose and how they interact. If you have any questions or additional hints, please feel free to comment.

Please go and credit the original author for his work https://dbamarco.wordpress.com/2016/08/09/all-about-oracle-naming/