Create the service. The first parameter name is the service name, can be up to 64 characters long.
The second parameter is the network name of the service as used in SQLNet connect descriptors for client connections
# sqlplus / as sysdba
SQL> exec dbms_service.create_service(service_name=>'online_users', network_name=>'online_users');
Start the service:
SQL> exec dbms_service.start_service('online_users');
Update the service_names parameter. Without this, your new service would exist, but have to be manually started with every database restart:
SQL> alter system set service_names='proddb01, online_users' scope=both;
Check the parameter settings:
show parameter service
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
service_names string online_users, proddb01
A typical connect string when using JDBC would be
connect string : jdbc:oracle:thin:@//prodserver01:1521/online_users
If you are relying on tns naming, in other words, using a local tnsnames.ora file, make sure you set up your tnsnames.ora correctly.
The following is an example of a tnsnames.ora entry for the service we just created:
online_users =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myserver.mydomain.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = online_users)
)
)
Note: If you have specified a value for the database parameter domain_name, you need to add this to the SERVICE_NAME-clause of your tnsnames.ora entry, otherwise you will have trouble connecting:
show parameter domain
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string mydomain.com
In the case shown above, we have indeed specified a value for domain_name, and therefore the service_name will inherit this value by default.
So make sure you specify the FQDN in the SERVICE_NAME clause for your tnsnames.ora entry, like this:
(SERVICE_NAME = online_users.mydomain.com)
The command
lsnrctl services
will show you how the services are automatically registered with the listener.
The first 3 services are automatically created and always created by default with any Oracle installation. The last service registered with the listener is the one we created manually previously in this post:
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver.mydomain.com)(PORT=1521)))
Services Summary...
Service "proddb01" has 1 instance(s).
Instance "proddb01", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "proddb01.mydomain.com" has 1 instance(s).
Instance "proddb01", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0 state:ready
LOCAL SERVER
Service "proddb01XDB.mydomain.com" has 1 instance(s).
Instance "proddb01", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=myserver.mydomain.com)(PORT=19394))
Service "online_users.mydomain.com" has 1 instance(s).
Instance "proddb01", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:3 refused:0 state:ready
LOCAL SERVER
If for some reason the service is not registered automatically with the listener within reasonable time, you can try to force a registration, like this:
If your listener is running on the default port 1521, there should be
no need to touch your listener configuration, except stop/start/reload.
If you use a non-default listener configuration, such as a port != 1521 or a listener name != LISTENER, you need to configure the parameter local_listener.
In the exammple below, I configure the listener to run on port 1526 instead of 1521:
alter system set local_listener='(address=(protocol=tcp)(host=myserver)(port=1526))' scope=both;
Once again, note that in this case, the parameter db_domain is set so you only need to include the server name, not the domain name. If you do use the FQDN, it doesn't make any difference to your setup.
Verify that your connection can be used by using sqlplus to create a connection:
sqlplus scott/tiger@online_users
SQL> select sys_context('userenv','SERVICE_NAME') FROM DUAL;
SYS_CONTEXT('USERENV','SERVICE_NAME')
--------------------------------------------------------------------------------
online_users.mydomain.com
To remove a service:
-- stop the service
SQL> exec dbms_service.stop_service('online_users');
-- after stopping the service, it may be deleted
SQL> exec dbms_service.delete_service('online_users');
PL/SQL procedure successfully completed.
A useful query for checking relevant parameters:
set lines 200
column name format a30
column network_name format a30
column value format a60
select name,value
from v$system_parameter
where name in ('service_names','db_domain','local_listener');
SELECT name,
network_name
FROM v$active_services
ORDER BY 1;
