KNOWLEDGE CENTRAL

In Mid-1999, while preparing to upgrade my Oracle certification to Oracle 8i (I was already certified on Oracle7), I read an Oracle book titled “Oracle 8i: New Features for Administrators”. In that book I read of an abstract concept called Services that made no sense to me then. Until then, everything I had read and done with Oracle software was pretty explainable and easily understandable – Services were different. They enabled one to create and use a name to connect to the database that was different from the database – in effect aliasing the database!!

Using Services, all you needed was to either:

  1. set a simple parameter in the initialization file called SERVICE_NAMES that PMON could register with the listener and connections using that alias could then be accepted and served (as long as they came to the correct port).

 

DB_ALIAS =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = mynode )(PORT = 1521))

)
(CONNECT_DATA =(SERVICE_NAME = SVCNAME))
)

# NOTE: SVCNAME Comes matches parameter you have in init.ora file

  1. Even without setting SERVICE_NAMES parameter, you could give a name (alias) to the database and connect to it successfully as long as you specified the real name of the database in the “CONNECT_DATA (SID=DBNAME)” part of your tnsnames.ora file.
DB_ALIAS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (COMMUNITY = tcp.world)
(PROTOCOL = TCP )
(Host = serverx.mycompany.com ) (Port = 12345 )))
(CONNECT_DATA = (SID= SID_NAME))
)

In initial configurations, this ability to alias a database name was thought of more as a security mechanism than anything else. It seemed like you were able to “mask” the true name of the database.

But as technology has evolved into a more Service-Oriented Architecture, the robustness of these Oracle Services has emerged – but so are the challenges.

What are Oracle Services and what can you do with them?

Services are logical groupings of a database resource(s) to which database clients and applications can connect to for purposes of High Availability and/or performance management and/or configuration management

Each service can be configured with its resources and utilization grouped or aggregated – this could be a group of database users or group of application servers or group of users by locality, etc.

The services are usually named by their business unit of function – eg sales, orders, shipping, etc. So overall, the names are descriptive – but besides being descriptive, the names are:

  • Not Case- ensitive.
  • 3 and 63 characters long.
  • Fully qualified domain name cannot be more than 255 characters.
  • Only contains characters “a” to “z”, “0” to “9”, and “-”.

In Oracle 8i and 9i there was no default value for SERVICE_NAME – however, SERVICE_NAMES defaults to DBNAME.DOMAIN_NAME. When not explicitly set, a domain, name is picked up from parameter DOMAIN_NAME.

Database Internal/Default Services:

In 10g/11g, there are additional default services that exist/preconfigured i.e. they exist regardless of how the database was created or the setting of SERVICE_NAMES:

  • SYS$BACKGROUND – to group and accommodate work by background processes
  • SYS$USERS – to group and accommodate work by users. It is the default service for all sessions that are not part of any application service
  • DB_NAME – this service name is almost always registered with the same name as the DB_UNIQUE_NAME

Creating Services:

Services can be set up by one of the following methods:

1)     Setting up DB initialization parameter SERVICE_NAMES in initSID.ora or spfileSID.ora

initSID.ora or spfileSID.ora parameter entry

SERVICE_NAMES=PRD

2)     Using OEM (Grid Control or DBConsole).

- Generally uses the point/click method

- Offers many other available options that are visible when you describe package DBMS_SERVICE

- Only available for RAC databases.

3)     Using RAC utility srvctl – creates the services in cluster registry – but does not set them in the spfile/init.ora file.

$ srvctl add service -d db_unique_name -s service_name -r
prefered_instance_list [-a available_inst_list]

NOTE: If this option is set, never set SERVICE_NAMES in the initSID.ora

One key advantage of creating services using srvctl relative to DBMS_SERVICES is that it gives you the flexibility to manage the services on all instances from any active node of the cluster. When you start services using DBMS_SERVICE package, the services are started on the node, although th estatus in the OCR will be set to offline.

4)     Using DBMS_SERVICE package (CREATE_SERVICE procedure).

SQL> desc dbms_service

PROCEDURE CREATE_SERVICE

Argument Name                  Type                    In/Out Default?

------------------------------ ----------------------- ------ --------

SERVICE_NAME                   VARCHAR2                IN

NETWORK_NAME                   VARCHAR2                IN

GOAL                           NUMBER                  IN     DEFAULT

DTP                            BOOLEAN                 IN     DEFAULT

AQ_HA_NOTIFICATIONS            BOOLEAN                 IN     DEFAULT

FAILOVER_METHOD                VARCHAR2                IN     DEFAULT

FAILOVER_TYPE                  VARCHAR2                IN     DEFAULT

FAILOVER_RETRIES               NUMBER                  IN     DEFAULT

FAILOVER_DELAY                 NUMBER                  IN     DEFAULT

CLB_GOAL                       NUMBER                  IN     DEFAULT
SQL> exec dbms_service.create_service(service_name=>'PRD',network_name=>'PRD');

PL/SQL procedure successfully completed.

Storage of Services Configuration Information:

Once created, information about services is stored in one of the following places:

  • initSID.ora/spfileSID.ora

Parameters include SERVICE_NAMES

  • tnsnames.ora

- On Server side:

local_listener, remote_listener

- On Client side:

CONNECT_DATA section of the tnsnames.ora

  • Oracle Cluster Registry (OCR).

- Resource mappings & dependencies

  • Data Dictionary tables and views:

- V$SERVICES

- V$SERVICE_EVENT

- V$ACTIVE_SERVICES

- DBA_SERVICES

- V$SERVICE_EVENT

What can you do with services – besides connecting to the database?

  • Scalability/Load Balancing – Services offer a mechanism to scale the application workload across available instances of a RAC deployment. There are 2 types of load balancing:
    • Client-Side Connect Time Load balance

Achieved by adding LOAD_BALANCE=YES|ON|TRUE in the tnsnames.ora within the ADDRESS_LIST section. It works by randomizing connections to the database available instances.

PRD=

(DESCRIPTION=

(FAILOVER=on)

(LOAD_BALANCE=on)

(ADDRESS_LIST=

(ADDRESS=

(PROTOCOL=TCP)

(HOST=prd2-vip.domain.com)

(PORT=1521)

)

(ADDRESS=

(PROTOCOL=TCP)

(HOST=prd1-vip.domain.com)

(PORT=1521)

)

)

(CONNECT_DATA=

(FAILOVER_MODE=

(TYPE=select)

(METHOD=basic)

)

(SERVICE_NAME=REPORTs)

)

)

 

    • Server-side Listener connection load balance – allows the listener to route the incoming user connections to the least-loaded node and then to the least loaded instance using information from the Load Balance Advisory.
LISTENERS_PRD=

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = prd1-vip)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = prd2-vip)(PORT = 1521))

(CONNECT_DATA=

(SERVICE_NAME=PRD)))

To enable this feature, remote_listener parameter must be configured in the RAC instances pointing at the listener on the remote node.

REMOTE_LISTENER= LISTENERS_PRD

OR

      

 SQL> alter system set remote_listener='LISTNERS_PRD'
    • Client-side Transparent Application Failover – allows client to re-establish connections lost due to an instance crash. These connections are re-established against the surviving instance in the RAC configuration.

Two types of failover methods are supported – BASIC & PRECONNECT. The BASIC method re-establishes the connection at failover time. In the PRECONNECT method, shadow connections are established to another available instance anticipating the failover. PRECONECT further supports two failover types – SESSION & SELECT

- SESSION configuration re-establishes the lost connection and all previously running transactions are cancelled.

- SELECT configuration re-establishes the lost connection and also replays the transactions that were in progress.

In both failover types uncommitted transactions are rolled back.

NOTE: In JDBC connections, TAF does not work very well – so to get it working right Fast Connection Failover (FCF) must be configured.

Configuration: TAF policy can be configured in the client-side TNS connection entry with:

TAF=
(DESCRIPTION =
(ADDRESS_LIST =
(LOAD_BALANCE = yes)
(ADDRESS = (PROTOCOL = TCP)(HOST = prd1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = prd2-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sales.domain.com)
(failover_mode=(type=select)(method=basic)retries=5)(delay=15))
)
)
    • Server-Side Transparent Application Failover – starting Oracle 10g onwards, Server-side TAF can be configured using srvctl utility:

1. Create the service:

srvctl add service -d prd -s server_taf -r "prd1,prd2" -P BASIC

2. Start the service server_taf

srvctl start service -d prd -s server_taf

3. Check service is running

srvctl config service -d prd

prdtest PREF: prd1 prd2 AVAIL:

server_taf PREF: prd1 prd2 AVAIL

Starting with Oracle 10g onward, TAF can be enabled for JDBC Thin and JDBC OCI drivers using a new Fast Connection Failover (FCF) feature.  To use this option, some modification of application code is necessary so that any SQL exception is thrown in response to instance outage is registered Oracle Notification Service (ONS) that issues a connection close().

When services don’t work as expected (Bugs that I have run into relating to Services):

  • 9939306 – SERVICES NOT COMING UP AFTER SWITCHOVER USING SRVCTL START DATABASE
  • When Oracle 10g RAC database or instance is stopped using srvctl or SQL*Plus, the associated services are left OFFLINE. Upon subsequent database or instance startup, these services are not automatically started. Work around that I have used is to write a small Perl FAN Callout and place it under $ORA_CRS_HOME/racg/usrco.
  • Bug 8232287  – I run into this in Oracle 10.2.0.3 & Oracle 11.1.0.7: PMON stops to detect services that are up and running – resulting in connections against them being refused.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>