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=)(PORT=1521))(CONNECT_DATA=(service_name=sand))) sqlplus -ac /@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 < 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.