Oracle database service notes ---------------------------------------------------------------------------------------------------- On a RAC database, if you save state when a service is up, your subsequent "srvctl disable service" command will be ignored; the service will still be started after you bounce the instance. To correct the problem, stop the service (srvctl stop service ...), discard and then save state (alter pluggable database discard|save state), start the service (srvctl start service ...). You may choose to do it on one node at a time to minimize the duration when the service is down if service downtime is a concern. In Oracle Doc 2757584.1, we see a very brief mention of pdb_svc_state$:[note] "When saving the state of PDBs , the details of the PDBs and its state will be saved in DBA_PDB_SAVED_STATES and *the running service details of the respective PDBs are recorded in pdb_svc_state$*". It outght to go on to say: "as a result, this service cannot be disabled by 'srvctl disable'". Various Oracle documents recommend not saving state on a RAC database saying it's not needed and the service will be started on instance bounce anyway. That's bad advice, because there *are* times you want to disable a service, e.g. when you patch a database and don't want users to connect to it. Ref: stevecao.wordpress.com/2019/05/29/service-started-on-several-instances-in-rac/) https://community.oracle.com/mosc/discussion/4539071/srvctl-disable-service-has-no-effect/ SR 3-32657559441 : RAC DB disabled service is started after instance bounce [note] You can select * from pdb_svc_state$ but it's better to combine it with a documented view such as gv$active_services to see its content: col inst_name for a25 col service for a30 select a.inst_id, inst_name, name service, creation_date,con_id from gv$active_services a, pdb_svc_state$ b where a.inst_id=b.inst_id and a.name_hash=b.svc_hash order by 1,2; ---------------------------------------------------------------------------------------------------- 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.