Failover testing

Configuration

  • Service : my_rac_svc
  • RAC nodes : grac41,grac42
  • RAD DB    : grac4

Testing Session FAILOVER

Create and start service as the oracle software owner
Check database status
[grid@grac41 ~]$ srvctl status database -d grac4
Instance grac41 is running on node grac41
Instance grac42 is running on node grac42

Add service and verify service status
$ srvctl add service -d grac4 -s my_rac_svc -r grac41,grac42 -P basic -e session
$ srvctl status service   -d grac4 -s my_rac_svc  
Service report is not running.
$  srvctl start service   -d grac4 -s my_rac_svc
$ srvctl status service   -d grac4 -s my_rac_svc
Service my_rac_svc is running on instance(s) grac41,grac42

$ srvctl config service   -d grac4 -s my_rac_svc
Service name: my_rac_svc
Service is enabled
Server pool: grac4_my_rac_svc
Cardinality: 2
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: SESSION
Failover method: NONE
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: BASIC
Edition:
Preferred instances: grac41,grac42
Available instances:

Identify your SCAN Address
[grid@grac41 ~]$ srvctl config scan
SCAN name: grac4-scan.grid4.example.com, Network: 1/192.168.1.0/255.255.255.0/eth1
..
Verify with sqlplus using scan address and newly create service
[oracle@grac41 ~]$ sqlplus scott/tiger@grac4-scan.grid4.example.com/my_rac_svc
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
grac42

Create a 2.nd session
[oracle@grac41 ~]$  sqlplus scott/tiger@grac4-scan.grid4.example.com/my_rac_svc
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
grac41
--> Don't disconnect here !

Now we have 2 sessions connected to instance grac41 and grac42
Stop session on grac42
[oracle@grac41 ~]$ srvctl stop instance -d grac4 -i grac42 -o immediate

Try to select from connection to instance grac42
SQL> select instance_name from v$instance;
select instance_name from v$instance
*
ERROR at line 1:
ORA-25408: can not safely replay call

--> We  get an error  as expected -->Disconnect
SQL> quit

Reconnect
$ sqlplus scott/tiger@grac4-scan.grid4.example.com/my_rac_svc
SQL>  select instance_name from v$instance;
INSTANCE_NAME
----------------
grac41
--> Now both session are connected to instance grac41

2 thoughts on “Failover testing”

  1. Hi ,

    first thanks for sharing this great test , however there is somthing i don’t understand

    what do you mean by

    ERROR at line 1:
    ORA-25408: can not safely replay call

    –> We get an error as expected –>Disconnect”

    is that expect behavior ?

    Regards,
    Sam

    1. Hi Sam,

      Note the sqlplus session has lost their connectivity as the service failed over and we don’t have enabled the sqlplus to receive FAN Events. Note we don’t use TAF anyway in this setup. So currently I expect this type of error.

      In generell ORA-25408 means that a session fails in the middle of executing a DML or DDL or a PL/SQL program such as a
      stored procedure, a function, or a package, it will receive the ORA-25408 error, and the database will roll back the transaction. But we don’t have a running DML, PLSQL active in this test !

      So as a first step to fix ORA-25408 error your should enable sqlplus to receive FAN events by starting sqlplus with -f switch

      % sqlplus -f …

      This enables SQL*Plus to receive FAN events when connected to a Real Application Cluster
      (RAC) database. In this mode a service or instance failure is transparently handled
      with transaction status messages if applicable.

      br Helmut

Leave a Reply

Your email address will not be published. Required fields are marked *