Oracle Services Management

The Oracle Services are a wonderful tool to administer applications within an Oracle instance :

Resource Manager / Control Application access / RAC and so on …

Remember these limits to avoid « maximum number of services exceeded »,

we’ll come back to them later.

Maximum number of Oracle Services ( minus 3 for default background Services )

10GR2  115

11GR2  150

12CR1 1024

0] Creation/Drop

0.1 Creation

Call under SYSTEM user the CREATE_service.sql with Service name as argument

cat CREATE_service.sql

---CREATE_service.sql

-- P1 : Nom du service

def serv_name = &1

Begin

  DBMS_SERVICE.CREATE_SERVICE(

     service_name => '&serv_name',

     network_name => '&serv_name',

     goal => DBMS_SERVICE.GOAL_NONE,

     failover_method => DBMS_SERVICE.FAILOVER_METHOD_NONE,

     failover_type => NULL,

     failover_retries => NULL,

     failover_delay => NULL,

     dtp => NULL,

     aq_ha_notifications => NULL,

     clb_goal => NULL);

End;

/

undefine serv_name

exit

Then Call under SYSTEM user the START_service.sql with Service name & ORACLE_SID as arguments

cat START_service.sql

-- START_service.sql

-- P1 : Nom du service

-- P2 : Nom de l'instance => SID

def serv_name = &1

def inst_name = &2

Begin

  DBMS_SERVICE.START_SERVICE(

     service_name => '&serv_name',

     instance_name => '&inst_name');

End;

/

Don’t forget to set this new Service as Startable next instance boot : following section.

0.2 Drop

sqlplus system/$system_psw @STOP_service.sql $1 $ORACLE_SID

$ cat STOP_service.sql

-- STOP_service.sql

-- P1 : Service Name

-- P2 : Instance name => SID

def serv_name = &1

def inst_name = &2

Begin

  DBMS_SERVICE.STOP_SERVICE(

     service_name => '&serv_name',

     instance_name => '&inst_name');

End;

/

declare service varchar2(512);

Begin

 select replace(value,', ',Chr(39)||','||Chr(39)) into service from v$parameter where name='service_names';

 execute immediate 'alter system set service_names='''||service||'''';

End;

/

undefine serv_name

undefine inst_name

exit

sqlplus system/$system_psw @DELETE_service.sql $1

$ cat DELETE_service.sql

-- DELETE_service.sql

-- P1 : Service name

def serv_name = &1

Begin

  DBMS_SERVICE.DELETE_SERVICE(

     service_name => '&serv_name');

End;

/

undefine serv_name

exit

1] Set Service start at instance bounce and the problem hitting ORA-32021: parameter value longer than 255 characters

1.0 The problem

We defined Oracle Services to access Application Data in our Databases.

The Services, once created should be set as startable at instance bounce.

Even if the Service Name is a 3-digits string, when adding a new Service, we rapidly reached the Maximum number of Characters in the SERVICE_NAMES instance parameter.

SQL> alter system set service_names='SV1,…,SVCN' ;

alter system set service_names='SV1,…,SVCN'

*

ERROR at line 1:

ORA-32021: parameter value longer than 255 characters

To correct this, 2 possibilities

-       Sol 1 :Use a trigger to start the Oracle Services

Or better :

-       Sol 2 :Build the service_names parameter with multiple strings instead of only one

1.1 Solution 1 : Trigger

Goal	Trigger to Start Oracle Services on Startup Database
Version	10gR2
Views	V$INSTANCE DBA_SERVICES
Packages	DBMS_SERVICE
Log as	SYS

CREATE OR REPLACE TRIGGER SYS.START_SERVICES AFTER STARTUP on DATABASE

declare

 nn number;

 INST_NAME varchar2(16);

begin

  execute IMMEDIATE 'select INSTANCE_NAME from v$instance' INTO INST_NAME;

  for v_loop in (select name from dba_services order by 1) LOOP

    SELECT count(1) INTO nn from v$active_services WHERE UPPER(name)=UPPER(v_loop.name);

    IF (nn = 0) then

      DBMS_SERVICE.START_SERVICE(service_name => ''||v_loop.name||'' , instance_name => ''||INST_NAME||'');

       end IF;

  end loop;

end;

/

Another version that takes care of Dataguard Standby DB, owned by SYS user :

CREATE OR REPLACE TRIGGER START_SERVICES AFTER STARTUP on DATABASE

declare

inst_name varchar2(16) ;

nn number;

begin

 select INSTANCE_NAME INTO INST_NAME from v$instance;

 execute immediate 'alter system set service_names='''||inst_name||'.cg13.fr''';

 for v_loop in (select name from dba_services where upper(name) not like '%DGB' and upper(name) not like '%DGMGRL' and upper(name) not like ''||substr(inst_name,1,4)||'%' order by 1) LOOP

   SELECT count(1) INTO nn from v$active_services WHERE UPPER(name)=UPPER(v_loop.name);

   IF (nn = 0) then

     DBMS_SERVICE.START_SERVICE(service_name => ''||v_loop.name||'');

   end IF;

 end loop;

end;

/

1.2 Solution 2 : Set service_names parameter

Build the parameter from the existing services DBA_SERVICES

select ''''||name||''',' from dba_services order by 1

and remove 3 default Services : sys$users , background & instance_name, to get :

SQL> ALTER SYSTEM SET service_names='SVC1','SVC2',…,'SVCN' ;

System altered.

SQL> select count(1) from v$services ;

  COUNT(1)

----------

        75

SQL> select count(1) from dba_services ;

  COUNT(1)

----------

        75

Mon Oct 03 11:02:30 GMT+02:00 2011ALTER SYSTEM SET service_names='SVC1',…,'SVCN' SCOPE=BOTH;

SQL> select value from v$parameter where name='service_names';

VALUE

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

SVC1, …, SVCN

Now, adding a service to the Instance startup is a piece of cake :

set serveroutput on

declare service varchar2(512);

Begin

-- select value into service from v$parameter where name='service_names';

 select replace(value,', ',Chr(39)||','||Chr(39)) into service from v$parameter where name='service_names';

 DBMS_OUTPUT.PUT_LINE('Oracle Services : ' ||service);

 execute immediate 'alter system set service_names='''||service||'''';

End;

/

From memory

Mon Jan 28 10:57:50 GMT+01:00 2013

ALTER SYSTEM SET service_names='SVC1',…,'SVCN' SCOPE=MEMORY SID='INSTANCE';

To Spfile

Mon Jan 28 10:57:50 GMT+01:00 2013

ALTER SYSTEM SET service_names='SVC1',…,'SVCN','' SCOPE=BOTH;

Mon Jan 28 10:58:05 GMT+01:00 2013Thread 1 advanced to log sequence 961374 (LGWR switch)

SQL> select value from v$parameter where name='service_names';

VALUE

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

SVC1, …, SVCN,

create ok start ok

set serveroutput on size 100000

Begin

  DBMS_SERVICE.START_SERVICE(

     service_name => 'SCH',

     instance_name => 'INSTANCE');

End;

/

PL/SQL procedure successfully completed.

SQL> !lsnrctl stat | grep -i auc

Service "SCH.corp.fr" has 1 instance(s).

is in v$parameter2 / is not in v$parameter

sqlplus usr/<psw>@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=HOST)(Port=1521))(CONNECT_DATA=(service_name=auc.corp.fr)))"

1.3 Solution 2 : NEW VERSION with V$PARAMETER2

One difference between v$parameter & v$parameter2 is that the column "value" of v$parameter is VARCHAR2 (512 Byte) when the values are given by v$parameter2 with multiple rows.

It makes a big difference when talking about Service_Name for instance

we easily reach the max value of v$parameter, but still can get the full list from v$parameter2 !

-- total list of services

select name from dba_services -- has 3 more services: instance name , SYS$USERS & SYS$BACKGROUND

minus

-- in-memory services, better than v$parameter which max number of characters

select value from v$parameter2 where name='service_names'

order by 1 ;

New Script :

SET SERVEROUTPUT ON

DECLARE

CURSOR services_c IS select value from v$parameter2 where name='service_names' order by 1 ;

service varchar2(2048) ;

BEGIN

 FOR v_service IN services_c LOOP

-- DBMS_OUTPUT.PUT_LINE('svc = '||v_service.value);

   service := ''||service||''','''||v_service.value ;

   END LOOP;

   select regexp_replace(service,''',','',1,1) into service from dual ;

   select regexp_replace(service,'$','''') into service from dual ;

   -- DBMS_OUTPUT.PUT_LINE('svc = '||service);

   execute immediate 'alter system set service_names='||service ;

 END;

/

Mon Jan 28 16:59:02 GMT+01:00 2013ALTER SYSTEM SET service_names='SVC1',…,'SVCN' SCOPE=BOTH;

2] Package to manage DB access

Goal	Open or Close Database access, based on Service Name ( or on Roles if not existing )
Version	10gR2
Views	V$ACTIVE_SERVICES DBA_ROLE_PRIVS V$SESSION v$locked_object a , dba_objects
Packages	DBMS_SERVICE
Log as	SYSTEM

2.1 Pre-requisite

-       an application is defined as a 3 characters code

-       an Oracle Service is created for application access = the Application code

-       to locate sessions of application without an Oracle Service, we may use the application Roles named starting with the App-code.

Direct Grants on SYS objects to SYSTEM are necessary for this package to be valid, because Roles are not taken into account in PL/SQL ( 10g constraint ) :

GRANT EXECUTE ON SYS.DBMS_SERVICE TO SYSTEM;

GRANT SELECT ON  SYS.DBA_OBJECTS TO SYSTEM;

GRANT SELECT ON  SYS.DBA_ROLE_PRIVS TO SYSTEM ;

GRANT SELECT ON  SYS.V_$ACTIVE_SERVICES to system ;

GRANT SELECT ON  SYS.V_$LOCKED_OBJECT TO SYSTEM;

GRANT SELECT ON  SYS.V_$SESSION TO SYSTEM;

grant alter system to system ;

2.2 The package has the following procedures with only argument : the Application Code.

STOP_APP – Application wide, to kill every Applicative running sessions

-       stops Oracle Service if exists,

-       then kill sessions found out using Service Package or Roles

START_APP – Application wide, to restart Oracle Service if available

BEFORE_BTC – Application wide, to kill only « blocking » sessions that

-       have level 3 locks on application objects

-       may prevent batch program to run

BEFORE_BTC_ALL same – Instance wide

CREATE OR REPLACE package app_admin

as

-- 20/03/11 SCH

-- 05/01/12 validée par DC

-- app : Trigramme Application

-- exécution sous compte Exploitation ( ex: ORA_BAT )

        procedure stop_app (app in varchar2);

        procedure start_app (app in varchar2);

        procedure before_btc (app in varchar2);

        procedure before_btc_all ;

end;

/

CREATE OR REPLACE package body app_admin

as

sql_stmt  varchar2 (500);

cn integer;

nb number;

ret number;

procedure stop_app (app in varchar2)

is

-- stops Oracle Service if exists,

-- then kill sessions found out using Service Package or Roles

-- usage : execute system.app_admin.stop_app('APPLI');

-- 1/ If there is an existing Oracle Service

-- then use dbms_service package

nn number;

cursor cur is

 select 'alter system kill session '''||SID||','||SERIAL#||'''' cmd , username from v$session

 where exists

 ( select '1' from dba_role_privs where upper(granted_role) like '%'||upper(app)||'%' and grantee = username and grantee != 'SYSTEM' )

 and status != 'KILLED' order by username ;

begin

SELECT count(1) INTO nn from v$active_services WHERE UPPER(name)=UPPER(app);

IF (nn = 1) then

 dbms_output.put_line('Kill sessions using dbms_Service for application : ' ||app);

 DBMS_SERVICE.STOP_SERVICE(service_name => app);

 DBMS_SERVICE.DISCONNECT_SESSION(service_name => app,disconnect_option => DBMS_SERVICE.IMMEDIATE);

else

begin

-- 2/ use role instead

 dbms_output.put_line('Kill sessions using Role for application : ' ||app);

 nb:=0 ;

 for c_tab in cur loop

  begin

    dbms_output.put_line('Kill session username : '||c_tab.username);

 sql_stmt := c_tab.cmd;

 cn := dbms_sql.open_cursor;

 dbms_sql.parse(cn, sql_stmt,dbms_sql.native);

 ret:=dbms_sql.execute(cn);

 dbms_sql.close_cursor(cn);

 nb:=nb+1;

 exception

  when others then

   dbms_output.put_line(sqlerrm);

  end;

 end loop;

dbms_output.put_line(nb||' killed sessions');

end ;

end IF;

end stop_app;

procedure start_app (app in varchar2)

is

-- starts Service if Not already started

-- usage : execute system.app_admin.start_app('APPLI');

nn number;

begin

SELECT count(1) INTO nn from v$active_services WHERE UPPER(name)=UPPER(app);

IF (nn = 0) then

 DBMS_SERVICE.START_SERVICE(service_name => app);

end IF;

end start_app;

procedure before_btc (app in varchar2)

is

-- Kill sessions having locks on application objects

-- batch program may use a dedicated Batch Service ( SVC1 , BTC )

-- usage : execute system.app_admin.before_btc('APPLI');

cursor cur_app is

select distinct 'alter system kill session '''||SID||','||SERIAL#||'''' cmd , username , object_type , owner , object_name

from (

select b.username username, c.object_type object_type , c.owner owner , c.object_name object_name , b.sid sid, b.serial#

from v$locked_object a , v$session b , dba_objects c

where a.object_id = c.object_id and b.sid = a.session_id and a.locked_mode = '3' and owner = upper(app)

and b.service_name not in ( 'SVC1' , 'BTC') ) ;

begin

 dbms_output.put_line('Kill potential blocking sessions for application : ' ||app);

 nb:=0 ;

 for c_tab in cur_app loop

  begin

    dbms_output.put_line('Kill session username : '||c_tab.username||' , Object: '||c_tab.object_type||' '||c_tab.object_name||'');

 sql_stmt := c_tab.cmd;

 cn := dbms_sql.open_cursor;

 dbms_sql.parse(cn, sql_stmt,dbms_sql.native);

 ret:=dbms_sql.execute(cn);

 dbms_sql.close_cursor(cn);

 nb:=nb+1;

 exception

  when others then

   dbms_output.put_line(sqlerrm);

  end;

 end loop;

dbms_output.put_line(nb||' killed sessions');

end before_btc;

procedure before_btc_all

is

-- Kill sessions having blocking locks on application objects - Instance wide

-- usage : execute system.app_admin.before_btc_all ;

cursor cur is

select distinct 'alter system kill session '''||SID||','||SERIAL#||'''' cmd , username , object_type , owner , object_name

from (

select b.username username, c.object_type object_type , c.owner owner , c.object_name object_name , b.sid sid, b.serial#

from v$locked_object a , v$session b , dba_objects c

where a.object_id = c.object_id and b.sid = a.session_id and a.locked_mode = '3') ;

begin

 dbms_output.put_line('Kill ALL potential blocking sessions');

 nb:=0 ;

 for c_tab in cur loop

  begin

    dbms_output.put_line('Kill session User: '||c_tab.username||' , Object: '||c_tab.object_type||' '||c_tab.owner||'.'||c_tab.object_name||'');

 sql_stmt := c_tab.cmd;

 cn := dbms_sql.open_cursor;

 dbms_sql.parse(cn, sql_stmt,dbms_sql.native);

 ret:=dbms_sql.execute(cn);

 dbms_sql.close_cursor(cn);

 nb:=nb+1;

 exception

  when others then

   dbms_output.put_line(sqlerrm);

  end;

 end loop;

dbms_output.put_line(nb||' killed sessions');

end before_btc_all;

end app_admin;

/

2.3 Calling example

• STOP APPLICATION using Service

SQL> set serveroutput on

SQL> exec app_admin.stop_app('APPLI');

Kill sessions using dbms_Service for application : APPLI

PL/SQL procedure successfully completed.

Users side :

SQL> select count(1) from user_tables ;

select count(1) from user_tables

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

• STOP APPLICATION using Roles

SQL> set serveroutput on

SQL> exec system.app_admin.stop_app('<SCHEMA>');

Kill sessions using Role for application : <SCHEMA>

Kill session username : SMITH

…

Kill session username : MARTIN

33 killed sessions

PL/SQL procedure successfully completed.

Users side :

killed

SQL> select count(1) from user_tables ;

select count(1) from user_tables

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

• START APPLICATION

SQL> set serveroutput on

SQL> exec app_admin.start_app('APPLI');

PL/SQL procedure successfully completed.

Service is now available :

$ lsnrctl stat | grep -i hfb

Service "APPLI" has 1 instance(s).

• BEFORE_BTC

-- connect auc

SQL> update auc set a = 1000 ; --> lock mode 3

-- then kill blocking session

SQL> set serveroutput on

SQL> exec system.app_admin.before_btc('SCH');

Kill potential blocking sessions for application : SCH

Kill session username : SCH , Object: TABLE SCH

1 killed sessions

PL/SQL procedure successfully completed.

-- user’s session

SQL> commit ;

commit

*

ERROR at line 1:

ORA-00028: your session has been killed

• BEFORE_BTC_ALL

SQL> set serveroutput on

SQL> execute system.app_admin.before_btc_all ;

Kill ALL potential blocking sessions

Kill session User: SCH0143UT , Object: TABLE SCH0143UT.ENVI

Kill session User: SCH , Object: TABLE SCH.SCH

2 killed sessions

PL/SQL procedure successfully completed.

3] Other stuff

• find unused existing services

thanks to USERS_MON connection audit table , Real-Time V$SESSION , and Past Sessions from ASH

select distinct name from dba_services A where

not exists ( select '1' from adb.users_mon_31122014 B where A.name = replace(B.service_name,'.domain.name','') )

and not exists ( select '1' from adb.users_mon B where A.name = replace(B.service_name,'.domain.name','') )

and not exists ( select '1' from v$session B where A.name = schemaname )

and not exists ( select '1' from dba_hist_active_sess_history a , dba_users b where A.name = b.username and a.user_id = b.user_id )

order by 1

4] Connect Strings

4.1 JDBC Thin Driver

jdbc:oracle:thin:@<host>:<tcp_port>/<service_name>

jdbc:oracle:thin:@DNS_ALIAS:1521/SRV.domain.fr

more complex syntax, close to TNSNAMES

jdbc:oracle:thin:@(description=(FAILOVER=ON)(address=(protocol=TCP)(Host=bipopeo01.mc2.renault.fr)(port=1556))(address=(protocol=TCP)(Host=bipopeo02.mc2.renault.fr)(port=1556))(connect_data=(service_name=bipopeo01)))

Conf TOMCAT with LDAP access

jdbc:oracle:thin:@ldap://oidsrv-1.corp.fr:389/APPLI_PRO,cn=OracleContext,dc=corp,dc=fr

4.2 SQLPLUS command line

Easy Connect Naming

sqlplus user/psw@<host>:<tcp_port>/<service_name>

sqlplus APPLI/GAM@SRV_NAME:1523/ORA_SRV.corp.fr

sqlplus usr/psw@"cn=APPLI_PRO,cn=OracleContext,dc=corp,dc=fr"

4.3 Reverse on LDAP

ldapsearch -h oidsrv-1.corp.fr -p 389 -D cn=orcladmin -w <psword> -b "cn=OracleContext,dc=corp,dc=fr" -s one "objectclass=orclNetService" orclnetdescstring

ldapsearch -h oidsrv-1.corp.fr -p 389 -D cn=orcladmin -w <psword> -b "cn=OracleContext,dc=corp,dc=fr" -s one "objectclass=alias" orclnetdescstring | grep -ic btc