In Oracle : Oracle Service Name

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle DBA

Real Application Cluster (RAC) Maintenance tasks Backup and Recovery Database Architecture Replication Oracle Performance Tuning Oracle DBA - Other articles Oracle scripts/ Selects for DBA Oracle Errors (ORA-nnnnn)

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle DBA -> Oracle Database Architecture

-> Oracle Service Name

 

 Oracle Service Name

 

 

Look at these cases and see how an Oracle service is named function of the pfile/ spfile parameters. Listener.ora could add services as well.

 

Case nr. 1

 

DB_NAME                       = 'myDB'

DB_DOMAIN                   - not set in the pfile
DB_UNIQUE_NAME       - not set in the pfile
INSTANCE_NAME         - not set in the pfile

SERVICE_NAMES           - not set in the pfile

C:\>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 21-AUG-2010 11:43:17

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cata1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date 21-AUG-2010 11:27:43
Uptime 0 days 0 hr. 15 min. 34 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\Oracle10g\OH\network\admin\listener.ora
Listener Log File D:\Oracle10g\OH\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cata1)(PORT=1521)))
Services Summary...
Service "myDB_XPT" has 1 instance(s).
Instance "mydb", status READY, has 1 handler(s) for this service...
Service "mydb" has 1 instance(s).
Instance "mydb", status READY, has 1 handler(s) for this service...

The command completed successfully

 

 

Case nr. 2

 

DB_NAME                         = 'myDB'

DB_DOMAIN                   - not set in the pfile
DB_UNIQUE_NAME       - not set in the pfile
INSTANCE_NAME         - not set in the pfile

SERVICE_NAMES           = 'myDB'

 

The result is the same as in the case nr. 1

 

 

Case nr. 3

 

DB_NAME                         = 'myDB'

DB_DOMAIN                   - not set in the pfile
DB_UNIQUE_NAME       - not set in the pfile
INSTANCE_NAME         - not set in the pfile

SERVICE_NAMES           = 'myDB2'

 

C:\>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 21-AUG-2010 12:03:38

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cata1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date 21-AUG-2010 12:03:19
Uptime 0 days 0 hr. 0 min. 19 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\Oracle10g\OH\network\admin\listener.ora
Listener Log File D:\Oracle10g\OH\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cata1)(PORT=1521)))
Services Summary...
Service "myDB2" has 1 instance(s).
Instance "mydb", status READY, has 1 handler(s) for this service...

Service "myDB_XPT" has 1 instance(s).
Instance "mydb", status READY, has 1 handler(s) for this service...
Service "mydb" has 1 instance(s).

Instance "mydb", status READY, has 1 handler(s) for this service...
The command completed successfully

 

 

Case nr. 4

 

DB_NAME                        = 'myDB'

DB_DOMAIN                   = 'myDomain.ca'
DB_UNIQUE_NAME       - not set in the pfile
INSTANCE_NAME         - not set in the pfile

SERVICE_NAMES           - not set in the pfile

 

C:\>lsnrctl status

 

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 21-AUG-2010 12:08:10

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cata1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date 21-AUG-2010 12:07:58
Uptime 0 days 0 hr. 0 min. 12 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\Oracle10g\OH\network\admin\listener.ora
Listener Log File D:\Oracle10g\OH\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cata1)(PORT=1521)))
Services Summary...
Service "myDB.myDomain.ca" has 1 instance(s).
Instance "mydb", status READY, has 1 handler(s) for this service...

Service "myDB_XPT.myDomain.ca" has 1 instance(s).
Instance "mydb", status READY, has 1 handler(s) for this service...
The command completed successfully

 

 

Case nr. 5

 

DB_NAME                        = 'myDB'

DB_DOMAIN                   - not set in the pfile
DB_UNIQUE_NAME      = 'myUniqueDB'
INSTANCE_NAME         - not set in the pfile

SERVICE_NAMES           - not set in the pfile

 

C:\>lsnrctl status

 

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 21-AUG-2010 12:20:19

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cata1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date 21-AUG-2010 12:20:08
Uptime 0 days 0 hr. 0 min. 10 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\Oracle10g\OH\network\admin\listener.ora
Listener Log File D:\Oracle10g\OH\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cata1)(PORT=1521)))
Services Summary...
Service "myUniqueDB" has 1 instance(s).
Instance "mydb", status READY, has 1 handler(s) for this service...

Service "myUniqueDB_XPT" has 1 instance(s).
Instance "mydb", status READY, has 1 handler(s) for this service...
The command completed successfully

 

 

Case nr. 6

 

DB_NAME                        = 'myDB'

DB_DOMAIN                   = 'myDomain.ca'
DB_UNIQUE_NAME      = 'myUniqueDB'
INSTANCE_NAME         - not set in the pfile

SERVICE_NAMES           - not set in the pfile

 

C:\>lsnrctl status

 

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 21-AUG-2010 12:23:48

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cata1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date 21-AUG-2010 12:23:37
Uptime 0 days 0 hr. 0 min. 11 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\Oracle10g\OH\network\admin\listener.ora
Listener Log File D:\Oracle10g\OH\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cata1)(PORT=1521)))
Services Summary...
Service "myUniqueDB.myDomain.ca" has 1 instance(s).
Instance "mydb", status READY, has 1 handler(s) for this service...

Service "myUniqueDB_XPT.myDomain.ca" has 1 instance(s).
Instance "mydb", status READY, has 1 handler(s) for this service...
The command completed successfully

 

 

Case nr. 7

 

DB_NAME                          = 'myDB'

DB_DOMAIN                    = 'myDomain.ca'
DB_UNIQUE_NAME        = 'myUniqueDB'
INSTANCE_NAME           = 'myInstance'

SERVICE_NAMES           - not set in the pfile

 

C:\>lsnrctl status

 

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 21-AUG-2010 12:26:27

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cata1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date 21-AUG-2010 12:26:19
Uptime 0 days 0 hr. 0 min. 10 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\Oracle10g\OH\network\admin\listener.ora
Listener Log File D:\Oracle10g\OH\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cata1)(PORT=1521)))
Services Summary...
Service "myUniqueDB.myDomain.ca" has 1 instance(s).
Instance "myInstance", status READY, has 1 handler(s) for this service...
Service "myUniqueDB_XPT.myDomain.ca" has 1 instance(s).
Instance "myInstance", status READY, has 1 handler(s) for this service...
The command completed successfully

 

 listener.ora is always:

 

LISTENER =
   (DESCRIPTION_LIST =
             (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = cata1)(PORT = 1521))
              )
   )

 

for these cases.

 

Case nr. 8 - listener.ora can add services to the Oracle database

 

DB_NAME                           = 'myDB'

DB_DOMAIN                      = 'myDomain.ca'
DB_UNIQUE_NAME          = 'myUniqueDB'
INSTANCE_NAME             = 'myInstance'

SERVICE_NAMES              = 'myService'

 

listener.ora is

-- The listener.ora defines the service names for the database (using GLOBAL_DBNAME in the listener definition).

 

LISTENER =
   (DESCRIPTION_LIST =
       (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = cata1)(PORT = 1521))
       )
   )

SID_LIST_LISTENER=
    (SID_LIST=
             (SID_DESC= (GLOBAL_DBNAME=DB10.myDomanin.fr)(ORACLE_HOME=D:\ORACLE\db10)(SID_NAME=myDB))
             (SID_DESC= (GLOBAL_DBNAME=myDB2)(ORACLE_HOME=D:\ORACLE\db10)(SID_NAME=myDB)))
 

 

C:\>lsnrctl status

 

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 21-AUG-2010 12:50:01

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cata1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date 21-AUG-2010 12:45:54
Uptime 0 days 0 hr. 4 min. 7 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\Oracle10g\OH\network\admin\listener.ora
Listener Log File D:\Oracle10g\OH\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cata1)(PORT=1521)))
Services Summary...
Service "DB10.myDomanin.fr" has 1 instance(s).
Instance "myDB", status UNKNOWN, has 1 handler(s) for this service...
Service "myDB2" has 1 instance(s).
Instance "myDB", status UNKNOWN, has 1 handler(s) for this service...

Service "myService.myDomain.ca" has 1 instance(s).
Instance "myInstance", status READY, has 1 handler(s) for this service...
Service "myUniqueDB.myDomain.ca" has 1 instance(s).
Instance "myInstance", status READY, has 1 handler(s) for this service...
Service "myUniqueDB_XPT.myDomain.ca" has 1 instance(s).
Instance "myInstance", status READY, has 1 handler(s) for this service...

The command completed successfully

 

 

Notes:

 

1) It is possible to force the service registration with the listener using:

    alter system register

 

2) These cases are tested on Oracle 10.2

 

 

In-Oracle.com  -> Oracle DBA -> Oracle Database Architecture

-> Oracle Service Name

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.