use services to logically group database workloads and consolidate applications

June 6, 2012 — Leave a comment

nowadays everybody is talking about consolidation to reduce costs and ( maybe ) to simplify management of the infrastructure. the current trend seems to be:

  • buy big boxes ( exadata, for example ) and put many databases on one physical host.
  • buy big boxes, use one of the virtualization or partition technologies ( vmware, lpars, ldoms, oracle vm, solaris zones, …. ) and put the database on the virtual hosts
  • use RAC in combination with cheaper boxes and try to scale by adding more cheaper boxes once the workload increases

what less people seem to think about ( correct me if I’m wrong ): why not consolidate some of the smaller applications which do not need that much resources into the same database ? this will reduce licensing costs and ( maybe ) simplifies administration, too. of course, there are things to consider:

  • each application must use the same database version
  • if you need to patch, all applications will be affected
  • if you need to upgrade, all applications will be affected
  • if there is unplanned or planned downtime, all applications will be affected
  • security inside the database becomes more important: permissions, roles …
  • if one of the applications goes crazy how do you manage that it does not affect the other applications that much that they are not usable anymore ?
  • how to quickly identify an application which has troubles ?

having thought about these points maybe you’ll give it a try. especially for identifying the applications quickly and react on issues one concept that should be implemented is services. if you ever worked with RAC, services should not be new to you, but services are useful in single instances, too. as services do not require any changes to the application implementing them is straight forward:

  • if you use oracle restart or a clustered grid infratructure the easiest way to manage services is by using the srvctl utility
  • if you don’t use oracle restart, single instance services should be managed by the dbms_service package

this post will discuss both methods and give some examples. obviously the first step you need to do is to create a service ( I will create two services to show how one can connect to a specific service later ).

the srvctl way:

srvctl add service -d DB112 -s BEER -y AUTOMATIC -B SERVICE_TIME

… where:

  • -d: is the database unique name
  • -s: is the service name
  • -y: is the flag for the management policy
  • -B: is the runtime load balancing goal

note: there are some other parameters one can specify, but as these parameter are important for failover, load balancing, dataguard and rac I will not discuss them right now.

service created. does oracle restart report the newly created service ? yes, of course:

crsctl stat res ora.db112.beer.svc
NAME=ora.db112.beer.svc
TYPE=ora.service.type
TARGET=OFFLINE
STATE=OFFLINE

does the database report the service ?

SYS@DB112> select name from all_services;
NAME
----------------------------------------------------------------
SYS$BACKGROUND
SYS$USERS
DB112

… not yet. lets start the service:

srvctl start service -d DB112 -s beer

… and check what oracle restart reports for the service now:

crsctl stat res ora.db112.beer.svc
NAME=ora.db112.beer.svc
TYPE=ora.service.type
TARGET=ONLINE
STATE=ONLINE on oracleplayground

seems to be online. does the database report the service now? :

SYS@DB112> select name from all_services;
NAME
----------------------------------------------------------------
SYS$BACKGROUND
SYS$USERS
DB112
BEER

yes. once the service is started the database reports the service in the appropriate views. to quickly check the attributes and state of the service:

srvctl config service -d DB112 -s beer -v
Service name: BEER
Service is enabled
Cardinality: SINGLETON
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: NONE
Failover method: NONE
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: SERVICE_TIME
TAF policy specification: NONE
Edition: 

thats all you need to do. a new service is created and the service is up and running. one more thing to consider: will the service be started automatically once the database is restarted? :

srvctl stop database -d DB112
srvctl start database -d DB112
crsctl stat res ora.db112.beer.svc
NAME=ora.db112.beer.svc
TYPE=ora.service.type
TARGET=ONLINE
STATE=ONLINE on oracleplayground

yes, it will.

for creating the second service i will use the dbms_service package.

the dbms_service way:

BEGIN
  DBMS_SERVICE.CREATE_SERVICE (
      service_name => 'MOREBEER'
    , network_name => 'MOREBEER'
    , goal => DBMS_SERVICE.GOAL_THROUGHPUT
    , dtp => NULL
    , aq_ha_notifications => NULL
    , failover_method => NULL
    , failover_type => NULL
    , failover_retries => NULL
    , failover_delay => NULL
    , clb_goal => NULL
    , edition => NULL
   );
END;
/

in contrast to the srvctl method the database already knows about the service now:

select name from all_services;
NAME
----------------------------------------------------------------
SYS$BACKGROUND
SYS$USERS
DB112
BEER
MOREBEER

lets start the service:

BEGIN
  dbms_service.start_service (
     service_name => 'MOREBEER'
   , instance_name => 'DB112'
  );
END;
/

thats it. service up and running.

to make things a little easier for us oracle already registered the services with listener:

lsnrctl services listener_db112
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 22-MAY-2012 12:46:25
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_DB112)))
Services Summary...
Service "BEER" has 1 instance(s).
  Instance "DB112", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "DB112" has 1 instance(s).
  Instance "DB112", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "MOREBEER" has 1 instance(s).
  Instance "DB112", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

… and modified the services parameter:

show parameter service_names
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
service_names			     string	 BEER, MOREBEER

quickly check if the database starts up the second service if we bounce the instance:

srvctl stop database -d db112
srvctl start database -d db112
lsnrctl services listener_db112
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 22-MAY-2012 12:53:45
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_DB112)))
Services Summary...
Service "BEER.fun" has 1 instance(s).
  Instance "DB112", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "DB112.fun" has 1 instance(s).
  Instance "DB112", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

no. only the service which was created through srvctl is up and running. if you want services to startup automatically you created with the dbms_service package you’ll need to create triggers similar to this:

CREATE OR REPLACE TRIGGER MOREBEER_STARTUP
AFTER STARTUP ON DATABASE
BEGIN
  dbms_service.start_service (
     service_name => 'MOREBEER'
   , instance_name => 'DB112'
  );  
END;
/
CREATE OR REPLACE TRIGGER MOREBEER_SHUTDOWN
BEFORE SHUTDOWN ON DATABASE
BEGIN
  dbms_service.stop_service (
     service_name => 'MOREBEER'
   , instance_name => 'DB112'
  );  
END;
/

restart the database and check if it works:

srvctl stop database -d db112
srvctl start database -d db112
lsnrctl services listener_db112
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 22-MAY-2012 12:58:15
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_DB112)))
Services Summary...
Service "BEER.fun" has 1 instance(s).
  Instance "DB112", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "DB112.fun" has 1 instance(s).
  Instance "DB112", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "MOREBEER.fun" has 1 instance(s).
  Instance "DB112", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

works. both services will now survive a database restart. time to connect to the services…

as I will use sqlplus for the connections I will need two more entries in my tnsnames.ora:

BEER.fun =
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS= (PROTOCOL = TCP)(Host = oracleplayground.fun)(Port = 1521))
  )
    (CONNECT_DATA =
      (SERVICE_NAME = BEER.fun)
    )
  )
MOREBEER.fun =
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS= (PROTOCOL = TCP)(Host = oracleplayground.fun)(Port = 1521))
  )
    (CONNECT_DATA =
      (SERVICE_NAME = MOREBEER.fun)
    )
  )

… quickly check if I can reach the listener:

tnsping beer
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 22-MAY-2012 13:03:44
Copyright (c) 1997, 2011, Oracle.  All rights reserved.
Used parameter files:
/opt/oracle/product/base/11.2.0.3/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS_LIST= (ADDRESS= (PROTOCOL = TCP)(Host = oracleplayground.fun)(Port = 1521))) (CONNECT_DATA = (SERVICE_NAME = BEER)))
OK (10 msec)
tnsping morebeer
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 22-MAY-2012 13:04:04
Copyright (c) 1997, 2011, Oracle.  All rights reserved.
Used parameter files:
/opt/oracle/product/base/11.2.0.3/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS_LIST= (ADDRESS= (PROTOCOL = TCP)(Host = oracleplayground.fun)(Port = 1521))) (CONNECT_DATA = (SERVICE_NAME = MOREBEER)))
OK (10 msec)

fine. for the following I just did a seperate connection to each service and generated some load. once the connections are established you can use the service views to query data about your services:

ALL_SERVICES
V$SERVICEMETRIC
V$SERVICEMETRIC_HISTORY
V$SERVICES
V$SERVICE_EVENT
V$SERVICE_STATS
V$SERVICE_WAIT_CLASS
V$ACTIVE_SERVICES

if there are performance issues awr displays some performance data by service, too:

service stats in awr

as well as the ash report:

service stats in ash

this is the foundation you may use when thinking about application consolidation into a single database. of course you should go further and integrate the services with the database resource manager to minimize impacts between the services or applications … and by the way: splitting a single application into services might be a good idea, too

Advertisements

No Comments

Be the first to start the conversation!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s