Oracle database service notes

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

From 11gR2 on, create service with srvctl only (or the failover and load balance attributes added by dbms_service would be wiped out if DB is bounced):
srvctl add service -d dbname -s servicename -r preferredinstance1,preferredinstance2 -m BASIC -e SESSION -j SHORT -B SERVICE_TIME
srvctl start service -d dbname
Before 11gR2, create it with srvctl followed by dbms_service.modify_service inside database. See below.
Note: In 12c, failover_method is deprecated and defaults to 'BASIC' if failover_type is set (Ref: Doc 460982.1).

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

To test application continuity with sqlplus:

On server:
$ srvctl modify service -db dbtest -service sand -failovertype TRANSACTION -replay_init_time 900 -failoverretry 30 -failoverdelay 10 -rlbgoal SERVICE_TIME -clbgoal SHORT -commit_outcome TRUE -notification TRUE

On client:
tnsping test
Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<hostname>)(PORT=1521))(CONNECT_DATA=(service_name=sand)))
sqlplus -ac <username>/<password>@test
SQL> select * from testtable;

On server:
$ srvctl stop instance -db dbtest -instance dbtest1 -force #dbtest1 is where the client session is

On client:
SQL> select * from testtable;
That will show the query result, with no intermediate "ORA-25408: can not safely replay call"

Note:
* Do not use "alter system disconnect session" (or "kill session") to simulate instance crash to trigger failover
* Must use srvctl to shutdown instance, cannot use sqlplus to shutdown
* Must pass -force or -f (not -failover)
* To test, sqlplus must have -ac option
* Must run the later SQL *after* srvctl *completely* shuts down instance, not before it shutdown is completed (otherwise will get ORA-41412 "results changed during replay; failover cannot continue", which is a generic, misleading error)

Ref:
https://docs.oracle.com/database/121/ADFNS/adfns_app_continuity.htm

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

From 12c on, you can use "alter system disconnect session" (not "kill session") to test instance crash and the client session will get
ORA-25408: can not safely replay call
on submitting next SQL, and then the SQL next will run fine.
Before 12c, you have to bounce instance to test failover.
In any case, the service must have failover_type set. If it's not set, failover won't happen. Failover_method is deprecated and defaults to 'BASIC' per Doc 460982.1.

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

Service does not register with SCAN listener: see "Service does not register" at ./SingleClientAccessName.txt

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

SCAN listener loses services: see ./SCANListenerLosesServiceRemoteListenerORA-132.txt

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

If you get into a situation add service says it already exists but remove service says it doesn't, try

srvctl remove service -d dbname -s servicename
srvctl remove service -d dbname -s servicename -f
Yes, the first one must be run; sometimes directly running the second will NOT remove service.
If that does not remove, then
crs_unregister ora.dbname.servicename.instance1.srv
crs_unregister ora.dbname.servicename.instance2.srv

Ref: http://apunhiran.blogspot.com/2008/08/prkp-1029-crs-0211-on-srvctl-modifyadd.html

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

#!/usr/bin/perl -w
#svc_on_pref.pl: Check to see if services are running on their preferred instances. Notify us if not.

$DB = "dbtest";
$RECIPIENTEMAIL = 'yong321,fellowdba';

$ENV{PATH}="/usr/bin:/bin:/u01/app/grid/bin";

$_=qx(crsctl stat res -t | DB=$DB perl -nle 'print \$1 if /\^ora.\$ENV{DB}.(.*).svc\$/');
@line = split /\n/;
foreach my $service (@line)
{
  $prefinst = `srvctl config service -d $DB -s $service | awk -F": " '/^Preferred/{print \$2}'`;
  chomp($prefinst);     #there's a newline at the end
  $statusline = `srvctl status service -d $DB -s $service`;
  if ($statusline =~ /is running on instance\(s\) (.*)$/)
  { $runinst = $1;
    if ($prefinst ne $runinst)
    { $msg = "Service $service preferred instance list differs from service running instance list:
Preferred: $prefinst
Running on: $runinst\n";
      print $msg;
      system "mail -s 'Service not running on all preferred instances' $RECIPIENTEMAIL <<EOF
$msg
EOF";
    }
    else
    { print "Service $service preferred instance list equals service running instance list:
Preferred: $prefinst
Running on: $runinst\n";
    }
  }
  else #This service is not even running.
  { print $statusline;
    system "mail -s 'Service not running' $RECIPIENTEMAIL <<EOF
$statusline
EOF";
  }
  @prefinst = ();
  @runinst = ();
}

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

[2015-07 Update related to the above script svc_on_pref.pl]
Instead of using my svc_on_pref.pl script, which "check(s) to see if services are running on their preferred instances",
Chad Cleveland's SQLs to query Oracle Enterprise Manager respository can also do this, if you use OEM to monitor your database.
See http://www.freelists.org/post/oracle-l/11g-RAC-TAPICustom-services-how-to-query-in-data-dictionary,1
But the SQL can be improved as follows:
col database_unique_name for a8
col cluster_name for a20
col service_name for a30
col preferred_instances for a35
col running_instances for a35
select database_unique_name,cluster_name,service_name,preferred_instances,running_instances
from sysman.cm$mgmt_rac_services
where preferred_instances!=running_instances
order by 1,2,3,4;
The advantage is that you can centralize this monitoring instead of installing the svc_on_pref.pl script on each DB cluster. The disadvantage is you have to have OEM.

Note that OEM repository database view sysman.mgmt_rac_services is not kept up-to-date. So use cm$mgmt_rac_services instead.

Ref:
https://community.oracle.com/tech/apps-infra/discussion/4484143/how-to-update-sysman-mgmt-rac-services/

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

If you have the OEM repository database, you can do more things from this central location.

To check for services not setting failover type:

col cm_target_name for a31
col service_name for a29
col resource_name for a40
col failover_type for a10
select a.cm_target_name, a.name service_name, b.resource_name, failover_type
from cm$mgmt_db_services_ecm a, cm$mgmt_rac_services b
where a.name=b.service_name and service_type='user' and (failover_type='NONE' or failover_type is null)
 and substr(a.cm_target_name,1,instr(a.cm_target_name,'_')-1)=substr(b.resource_name,5,instr(b.resource_name,'.',1,2)-5)
order by 4,1,2;

To check for services named the same (or only differing by domain) but for different DBs in the same cluster (this situation is dangerous; I wish Oracle could warn us; see https://community.oracle.com/tech/apps-infra/discussion/4484531/):

col cm_target_name for a16
col db1 for a8
col db2 for a8
col db1service for a26
col db2service for a26
select a.cm_target_name, a.database_unique_name db1, a.service_name db1service, b.database_unique_name db2, b.service_name db2service
from cm$mgmt_rac_services a, cm$mgmt_rac_services b
where a.cm_target_guid=b.cm_target_guid and (a.service_name=b.service_name or a.service_name=b.service_name||'.mycompany.com') and a.database_unique_name!=b.database_unique_name
order by 1,2,3;

To check TNS entries (connect identifiers) for RAC databases that use internal (not user) services

--assume you already loaded connect identifiers along with their services into a table named connstr_svc created as
--create table connstr_svc (connstr varchar2(15), svc varchar2(30));
select a.* from connstr_svc a, cm\$mgmt_rac_services b
 where connstr!=svc and a.svc=b.service_name and service_type='internal'
union
select * from connstr_svc
 where svc in
 (select service_name from cm\$db_cdb_pdb_services_ecm
  where pdb_name!='CDB\$ROOT' and service_name=lower(pdb_name) and cm_target_type='rac_database')
;

Otherwise, if you don't have too many TNS entries, you can check each one against cm$mgmt_rac_services and cm$db_cdb_pdb_services_ecm without temporarily creating a table

Without OEM, you would have to create scripts to do these checks on each database server or cluster.

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

#!/bin/bash
#ck_lsnr_svc_upd.sh: 11g only (10g script is quite different). Check listener service update making 
#sure pmon's on all instances update their info with all SCAN listeners.
#NormalServiceList can be initialled created by `cp ActualServiceList NormalServiceList' after 
#running this script once; it should not be altered in any way (chmod 444 or 400). It stores names 
#of the instance "services" that register with the listeners when everything is running normally 
#(excluding +ASMx and PLSExtProc instances).
#20100813: 11g listener service should check the scan listener; regular listener no longer has all 
#instances' registered. Also, because of complexity of syntax, check it only on local node. So 
#schedule to run this job on all nodes of RAC

cd /u01/app/oracle/scripts/ck_lsnr_svc_upd
export ORACLE_HOME=/u01/app/grid
export PATH=/bin:/usr/bin:$ORACLE_HOME/bin
RECIPIENTS="you@example.com"

function process_diff
{ SCANLSNR=$1
  diff ActualServiceList NormalServiceList > diff.out
  #If ActualServiceList differs from NormalServiceList, alert us. Email content is diff output.
  [[ -s diff.out ]] && mail -s "Some services did not report to $(hostname -s) listener $SCANLSNR. 'diff ActualServiceList NormalServiceList' follows" $RECIPIENTS < diff.out
}

#11g needs lsnrctl service LISTENER_SCAN{number}, not just lsnrctl service
for i in $(ps -ef | grep [S]CAN | perl -nle 'print $& if /LISTENER_SCAN\d/'); do
 if [[ $i ]]; then
  lsnrctl service $i | awk '/^Service "/{print $2}' | egrep -v -- '+ASM|PLSExtProc' | sort | uniq > ActualServiceList
  process_diff $i
 fi
done

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

Not sure if this claim is correct:

http://www.itpub.net/thread-1243195-2-1.html

ToddBao:
goal=>none, LBA checks increase in v$servicemetric.CPUPERCALL
goal=>SERVICE_TIME, LBA checks increase in v$serviemetric.DBTIMEPERSEC/v$serviemetric.CALLSPERSEC
goal=>THROUGHPUT, LBA checks increase in v$serviemetric.DBTIMEPERCALL

Suppose the service has goal set to SERVICE_TIME, we should see dbtimepersec/callspersec to be in the same trend as goodness:
select inst_id, begin_time, elapsedpercall, cpupercall, dbtimepercall, callspersec, dbtimepersec, dbtimepersec/callspersec, goodness, delta
from gv$servicemetric
where (callspersec != 0 or dbtimepersec != 0) and service_name = '&servicename' and flags = 0
order by goodness;

But my test in 10.2.0.4 RAC doesn't seem to show that. Semantically, DBTIMEPERSEC/CALLSPERSEC is the same as DBTIMEPERCALL anyway. And the result of the query proves that.

I wish we could find the algorithm for "internally computed" goodness.