failover | StartupForce

Configuring TNS entries for failover – Standby databases

March 24, 2013 Bharat Leave a comment

1. Configure a service name on the primary database

SQL > exec DBMS_SERVICE.CREATE_SERVICE (-
service_name => ' bharat-ha',-
network_name => 'bharat-ha',-
failover_method => 'BASIC',-
failover_type => 'SELECT',-
failover_retries => 180,-
failover_delay => 1);
PL/SQL procedure successfully completed.

sql> alter system set service_names='bharat-ha' scope=both;

2. Start the service . Here we are creating a trigger which will start the service once the instance is detected as primary

SQL > CREATE OR REPLACE TRIGGER manage_dgservice
after startup on database
DECLARE
role VARCHAR(30);
BEGIN
SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
IF role = 'PRIMARY' THEN
DBMS_SERVICE.START_SERVICE('fsfo');
END IF;
END;

3. Add the following line to sqlnet.ora at the client side

SQLNET.OUTBOUND_CONNECT_TIMEOUT = 3

Above statement will assure that the failed connections will not wait for TCP timeout but will immediately proceed to the next host if the primary is unavailable

4. Add the below tns entry to tnsnames.ora

bharat-ha =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.31)(PORT = 1533))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.32)(PORT = 1533))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bharat-ha)
)
)

or add below if service_names is set in spfile

bharat-ha =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.31)(PORT = 1533))
 (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.32)(PORT = 1533))
 (LOAD_BALANCE = yes)
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = bharat-ha)
 (FAILOVER_MODE =
 (TYPE = SELECT) # (TYPE = SESSION) 
 (METHOD = BASIC)
 (RETRIES = 180)
 (DELAY = 5)
 )
 )
 )

Testing Failover Connection

1. Connect from a client server using the ‘bharat-ha’ service, check the instance names

SQL> sho parameter unique
NAME            TYPE      VALUE
-------------- --------  -----------
db_unique_name string     bharat_pri

2. From the client server, connected through the ‘bharat-ha’ service start a long running query and check how long it takes to complete

SQL> select 1,2,3 from dba_source
1 2 3
1 2 3
...
...
1 2 3
1 2 3
140829 rows selected.
Elapsed: 00:0:30.12

3. From the client server, connected through the ‘bharat-ha’ service issue again the same long running query, and in another session kill the primary database

SQL> select 1,2,3 from dba_source 
1 2 3
1 2 3
... 
...

<< db process was killed at this point>>

[oracle@rac1 ~]$ ps -ef | grep pmon
oracle 3120 1 0 16:28 ? 00:00:00 ora_pmon_bharat
oracle 3255 3082 0 16:32 pts/1 00:00:00 grep pmon
[oracle@rac1 ~]$ kill -9 3120

This will trigger a fast start failover. Check the observer log

[W000 03/24 12:53:44.12] Observer started.
13:14:59.76 Sunday, March 24, 2013
Initiating Fast-Start Failover to database "bharat_stby"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "bharat_stby"
13:15:10.77 Sunday, March 24, 2013

At the client screen you will notice that the output from ‘select 1,2,3 from dba_source’ stops and then continues until finished

...
...
 <<<<<< Database process killed here
...
1 2 3
1 2 3
140829 rows selected
Elapsed: 00:01:23.23 <<<<<< the select took longer but finished

4. Check the instance name now

SQL> select instance_name from v$instance;
INSTANCE_NAME
—————-
bharat_stby
Elapsed: 00:00:00.07

Categories: Standby & Dataguard Tags: failover, service names, standby, tns

Testing DG Broker/fast_start failover configuration

March 24, 2013 Bharat Leave a comment

1. To enable DG broker with fast_start failover configuration check the below link

DG Broker Configuration with fast_start failover & Observer

2. Now check whether both the primary and standby are in sync

SQL> select FS_FAILOVER_STATUS,FS_FAILOVER_CURRENT_TARGET,FS_FAILOVER_THRESHOLD,
FS_FAILOVER_OBSERVER_PRESENT,FS_FAILOVER_OBSERVER_HOST from v$database;

On Primary:

FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD
FS_FAIL FS_FAILOVER_OBSERVER
---------------------- ------------------------------ --------------------- 
SYNCHRONIZED                      bharat_stby             30 
YES rac2.localdomain

On Standby

FS_FAILOVER_STATUS  FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD 
FS_FAIL FS_FAILOVER_OBSERVER
---------------------- ------------------------------ ---------------------
SYNCHRONIZED                             bharat_pri            30 
YES               rac1.localdomain

Failover testing:

1. Check that there is enough flashback data available in the primary database, atleast 30-40 mins.

 SQL> select (sysdate - oldest_flashback_time)*24*60 as history 
                                       from v$flashback_database_log;
   HISTORY
   ----------
   2898.61667

2. Create a sample table on the primary

 SQL> create table x as select * from all_objects;
 Table created.
 SQL> select count(*) from x;
    COUNT(*)
     ----------
     12615

3. Kill the instance:

[oracle@rac1 ~]$ ps -ef | grep pmon
oracle 4571 1 0 09:18 ? 00:00:00 ora_pmon_bharat
oracle 5311 4526 0 09:46 pts/1 00:00:00 grep pmon
[oracle@rac1 ~]$ kill -9 4571

4. Check the status in the DG Broker configuration

DGMGRL> show site verbose bharat_pri

5. Also check the observer log

[oracle@rac2 ~]$ cat observer.log

[W000 03/07 09:38:41.76] Observer started.
09:47:24.66  Thursday, March 07, 2013
Initiating Fast-Start Failover to database "bharat_stby"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "bharat_stby"
09:47:42.01  Thursday, March 07, 2013

6. Check the DG Broker configuration

DGMGRL> show configuration
 Configuration - bharatDR
   Protection Mode: MaxAvailability
  Databases:
    bharat_stby - Primary database
    Warning: ORA-16817: unsynchronized fast-start failover configuration 
    (Above warning is because of max availability configuration)
     bharat_pri  - (*) Physical standby database (disabled)
     ORA-16661: the standby database needs to be reinstated
 Fast-Start Failover: ENABLED
 Configuration Status:
WARNING

7. Check the table count on new primary(bharat_stby):

 SQL> select database_role,switchover_status from v$database;
 DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY NOT ALLOWED
 SQL> select count(*) from x;
 COUNT(*)
----------
12615

8. Now Re-instate the old primary as standby

Mount the database and observer will take care of re-instation. Check the observer log below

 SQL> alter database mount
[oracle@rac2 ~]$ tail -f observer.log
[W000 03/07 09:38:41.76] Observer started.
 09:47:24.66 Thursday, March 07, 2013
Initiating Fast-Start Failover to database "bharat_stby"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "bharat_stby"
09:47:42.01 Thursday, March 07, 2013
 09:52:20.86 Thursday, March 07, 2013
Initiating reinstatement for database "bharat_pri"...
Reinstating database "bharat_pri", please wait...
Operation requires shutdown of instance "bharat" on database "bharat_pri"
Shutting down instance "bharat"...
ORA-01109: database not open
 Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "bharat" on database "bharat_pri"
Starting instance "bharat"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "bharat_pri" ...
Reinstatement of database "bharat_pri" succeeded
09:53:44.70 Thursday, March 07, 2013