When you add service names, they are automatically registered with listener by PMON. To add a service name you can alter service_names parameter or you can user DBMS_SERVICE.
-bash-3.2$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 10 15:15:32 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL>
SQL>
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string oratest
SQL>
SQL>
SQL>
SQL> alter system set service_names='oratest,oratest1';
System altered.
SQL> show parameter service_names;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string oratest,oratest1
SQL>
SQL>
SQL> host
bash-3.2$ lsnrctl status
LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 10-FEB-2012 15:16:32
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 11.2.0.3.0 - Production
Start Date 10-FEB-2012 09:35:57
Uptime 0 days 5 hr. 40 min. 35 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/solaris10/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=solaris10)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "oratest" has 1 instance(s).
Instance "oratest", status READY, has 1 handler(s) for this service...
Service "oratest1" has 1 instance(s).
Instance "oratest", status READY, has 1 handler(s) for this service...
Service "oratestXDB" has 1 instance(s).
Instance "oratest", status READY, has 1 handler(s) for this service...
The command completed successfully
bash-3.2$
bash-3.2$
bash-3.2$
bash-3.2$ exit
exit
SQL> exec dbms_service.create_service('oratest2','oratest2');
PL/SQL procedure successfully completed.
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string oratest,oratest1
SQL> host
bash-3.2$ lsnrctl status
LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 10-FEB-2012 15:20:14
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 11.2.0.3.0 - Production
Start Date 10-FEB-2012 09:35:57
Uptime 0 days 5 hr. 44 min. 16 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/solaris10/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=solaris10)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "oratest" has 1 instance(s).
Instance "oratest", status READY, has 1 handler(s) for this service...
Service "oratest1" has 1 instance(s).
Instance "oratest", status READY, has 1 handler(s) for this service...
Service "oratestXDB" has 1 instance(s).
Instance "oratest", status READY, has 1 handler(s) for this service...
The command completed successfully
bash-3.2$ exit
exit
SQL>
SQL> exec dbms_service.start_service('oratest2');
PL/SQL procedure successfully completed.
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string oratest1, oratest, oratest2
SQL> host
bash-3.2$ lsnrctl status
LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 10-FEB-2012 15:20:54
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 11.2.0.3.0 - Production
Start Date 10-FEB-2012 09:35:57
Uptime 0 days 5 hr. 44 min. 57 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/solaris10/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=solaris10)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "oratest" has 1 instance(s).
Instance "oratest", status READY, has 1 handler(s) for this service...
Service "oratest1" has 1 instance(s).
Instance "oratest", status READY, has 1 handler(s) for this service...
Service "oratest2" has 1 instance(s).
Instance "oratest", status READY, has 1 handler(s) for this service...
Service "oratestXDB" has 1 instance(s).
Instance "oratest", status READY, has 1 handler(s) for this service...
The command completed successfully
bash-3.2$
Manual registration of service names with listener, in case PMON is not available:
SQL>
SQL> alter system register;
System altered.
SQL> exec dbms_service.create_service('oratest2','oratest2');
ReplyDeletePL/SQL procedure successfully completed.
Nice post sir, but didnot understand y did u executed that above mentioned script can u please give me explanation
Hi Sai,
ReplyDeleteThank you for your comment.
The post contains 2 ways of creating a service.
This is the second one.
Hi I start new service but it did not show in sqlplus
ReplyDelete