[Update 2011-05] In 11gR2, 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. #Create it in OCR first #Make sure no instance appears in both preferred and available instance lists (10g clusterware bug 8256800) #or it would only be in available list and stopping it would crash instance or cause other issues (Note:783703.1) srvctl add service -d dbname -s servicename -r preferredinstance1,preferredinstance2 -a backupinstance1,backupinstance2 srvctl start service -d dbname -s servicename srvctl status service -d dbname -s servicename --Make it failover-able exec dbms_service.modify_service('servicename', dbms_service.goal_service_time, aq_ha_notifications=>true, failover_method=>'BASIC', failover_type=>'SESSION', failover_retries=>3, failover_delay=>5) select * from dba_services; --Make it usable exec dbms_service.start_service('servicename', dbms_service.all_instances) --not needed if `srvctl start' already started it select * from v$active_services; ---------------------------------------------------------------------------------------------------- Service does not register with SCAN listener: see "Service does not register" at ../SingleClientAccessName.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.sh: Check to see if services are running on their preferred instances. Notify us if not. #Tested on 11.2.0.3 $DB = "ORCL"; $RECIPIENTEMAIL = 'yong321,fellowdba'; $ENV{PATH}="/usr/bin:/bin:/u01/app/11.2.0/grid/bin"; $_=`crs_stat | awk -F. '/^NAME.*\.svc\$/ {print \$3}' | sort | uniq`; #Get all service names (domain stripped) @line = split /\n/; foreach (@line) { $service = "$_.mycompany.com"; #Our domain names are guaranteed to be this. $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.