http://www.oracle-home.ro/Oracle_Database/Architecture/Oracle-service-name.html

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