Oracle Database - Database Service

> Database > Oracle Database

1 - About

An Oracle database is represented to clients as a service. A database can have one or more services associated with it.

When an instance starts, it registers itself with a listener using one or more service names.

A service is identified by a service name, for example, sales.us.example.com. The service name is specified by the SERVICE_NAMES initialization parameter. The DBMS_SERVICE package can also be used to create services.

A client uses a service name to identify the database it must access. The information about the database service and its location in the network is transparent to the client because the information needed for a connection is stored in a repository.

To connect to a database service, client applications use a connect string.

Advertising

3 - Architecture

3.1 - One Service for Each Database

The sales and finance databases are each identified by a service name, sales.us.example.com and finance.us.example.com, respectively. A service name is a logical representation of a database. When an instance starts, it registers itself with a listener using one or more service names. When a client program or database connects to a listener, it requests a connection to a service.

A service name can identify multiple database instances, and an instance can belong to multiple services. For this reason, the listener acts as a mediator between the client and instances and routes the connection request to the appropriate instance. Clients connecting to a service need not specify which instance they require.

3.2 - Multiple Services Associated with One Database

Associating multiple services with one database enables the following functionality:

  • A single database can be identified different ways by different clients.
  • A database administrator can limit or reserve system resources. This level of control enables better allocation of resources to clients requesting one of these services.

4 - How to

4.1 - know the services of a database

4.1.1 - Metadata

SELECT service_id, name FROM v$services;
SERVICE_ID NAME
---------- ----------
         6 pdborcl.HotITem.local
         5 orclXDB
         6 orcl.HotITem.local
         1 SYS$BACKGROUND
         2 SYS$USERS
Advertising

4.1.2 - listener

You can use the listener command below.

lsnrctl service
LSNRCTL for 64-bit Windows: Version 12.1.0.1.0 - Production on 02-OCT-2013 14:48:47

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 2 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "orcl.HotITem.local" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:707 refused:0 state:ready
         LOCAL SERVER
Service "orclXDB.HotITem.local" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: HI-LAPTOP-NGD, pid: 4888>
         (ADDRESS=(PROTOCOL=tcp)(HOST=HI-LAPTOP-NGD.HotITem.local)(PORT=17435))
Service "pdborcl.hotitem.local" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:707 refused:0 state:ready
         LOCAL SERVER
The command completed successfully
db/oracle/database_service.txt · Last modified: 2017/09/06 19:30 by gerardnico