SQL for troubleshooting services in multitenant environment

Query for troubleshooting services in a multitenant environment:

set lines 200
col name format a40
col network_name format a40
col pdb format a20
col global format a20

select name,network_name,con_id 
from v$active_services
order by con_id,name;
  
select name,network_name,con_id,pdb,global 
from v$services 
order by con_id,name;
 
select con_id#,name,network_name 
from  cdb_service$ 
order by con_id#,name ;
exit

How do I make sure that services are automatically brought online in an Oracle 19c Pluggable Database?

In an Oracle 19c pluggable database, the service_names parameter has no bearing on whether or not the services are brought up automatically at instance or server restart.

If the service was started in the PDB and you then saved as the pluggable database's state, it willl be brought online together with the pluggable database automatically.

First, create and start your desired service from the PDBS:

SQL> alter session set container=sales;

Session altered.

SQL> exec dbms_service.create_service(service_name=>'online_users', network_name=>'online_users');

PL/SQL procedure successfully completed.

SQL> exec dbms_service.start_service('online_users');

PL/SQL procedure successfully completed.

Switch to your root container:

SYS@cdb>SQL>alter session set container=CDB$ROOT;

Session altered.

Then, save the state of your PDB:

SYS@cdb>SQL>alter pluggable database sales save state;

Pluggable database altered.

Or, alternatively, save all current states of your PDBs:

SYS@cdb>SQL> ALTER PLUGGABLE DATABASE ALL SAVE STATE;

Pluggable database altered.

The service_names parameter is actually deprecated in Oracle 19c. The documentation states
To manage your services, Oracle recommends that you instead use the SRVCTL command-line utility, the GDSCTL command-line utility, or the DBMS_SERVICE PL/SQL package.

What is the difference between dba_services and v$services?

Why do we see differences in output between dba_services and v$services view ?

SQL>select name from v$services;

NAME
------------------------------
SYS$BACKGROUND
SYS$USERS
ebs_accnt
accnt
cdbXDB
cdb
accnt_ebs_patch

7 rows selected.



SQL>  select name from dba_services;

NAME
------------------------------
SYS$BACKGROUND
SYS$USERS
accntXDB
cdb
cdbXDB
cdb.skead.no
ebs_PATCH

7 rows selected.

New services were added and few services were stopped recently.

Answer:

The difference is due to the following:

* DBA_SERVICES - This view lists all the services that are in the database. This includes both started and stopped services.
* V$SERVICES - This view lists only the services that were started some time since the instance startup. When you stop a running service, that service will still be listed in this view until the next instance restart.

Oracle states that "The difference between these outputs does not pose any issue."

Source: Differences in outputs between DBA_SERVICES and V$SERVICES view (Doc ID 1496074.1) found at support.oracle.com

How to create and start a service with dbms_service

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:

alter system register;

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;