Archive

Posts Tagged ‘switchover’

Testing DG Broker/fast_start failover configuration

March 24, 2013 Leave a comment

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

https://startupforce.wordpress.com/2013/03/24/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

 

This completes testing 🙂


Advertisements

DG Broker Configuration with fast_start failover & Observer

March 24, 2013 Leave a comment

1. By default 2 dg broker configuration files will be maintained by the databases. To change the default locations,

SQL> ALTER SYSTEM SET DG_BROKER_START=FALSE;
SQL> alter system set dg_broker_config_file2='<PATH>/dr2bharat.dat';
SQL> alter system set dg_broker_config_file1=’<PATH>/dr1bharat.dat';

2.On both Primary as well as Standby database start the Data Guard Broker process

SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

3. Edit the listener.ora on both nodes to add a static entry for DGMGRL
This is to prevent the ORA-12154 error which can be observed on startup of the standby database after performing a switchover.

Ensure that the GLOBAL_DBNAME is set to db_unique_name_DGMGRL.db_domain

SID_LIST_LISTENER = (SID_LIST = 
                       (SID_DESC =
                           (GLOBAL_DBNAME = bharat_pri.db_domain)
                           (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_2)
                           (SID_NAME = bharat))
                       (SID_DESC = 
                            (GLOBAL_DBNAME = bharat_pri_DGMGRL.db_domain)
                            (ORACLE_HOME =/u01/app/oracle/product/11.2.0/db_2)
                            (SID_NAME = bharat))
                     )

4. Create the configuration

$ dgmgrl
$ connect sys/pwd
DGMGRL> CREATE CONFIGURATION 'bharatDR'
AS
PRIMARY DATABASE IS bharat_pri
CONNECT IDENTIFIER IS bharat_pri;

5. Add the Standby database to the configuration

DGMGRL> ADD DATABASE bharat_stby
AS
CONNECT IDENTIFIER IS bharat_stby;

6. Enable the configuration

DGMGRL> SHOW CONFIGURATION
DGMGRL> ENABLE CONFIGURATION

7. View the Standby and Primary database properties

DGMGRL> show database bharat_pri
DGMGRL> show database bharat_stby
DGMGRL> show database verbose bharat_pri
DGMGRL> show database verbose bharat_stby

Testing Switchover:

 DGMGRL> switchover to bharat_stby
Performing switchover NOW, please wait...
New primary database "bharat_stby" is opening...
Operation requires shutdown of instance "bharat" on database "bharat_pri"
Shutting down instance "bharat"...
ORA-01031: insufficient privileges
 Warning: You are no longer connected to ORACLE.
 Please complete the following steps to finish switchover:
shut down instance "bharat" of database "bharat_pri"
start up and mount instance "bharat" of database "bharat_pri"

To avoid manual startup as above connect as sys/<pwd>

DGMGRL> connect sys
Password:
DGMGRL> switchover to bharat_pri
Performing switchover NOW, please wait...
New primary database "bharat_pri" is opening...
Operation requires shutdown of instance "bharat" on database "bharat_stby"
Shutting down instance "bharat"...
ORA-01109: database not open
 Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "bharat" on database "bharat_stby"
Starting instance "bharat"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "bharat_pri"

Testing Failover:

1. Kill your oracle instance

[oracle@rac1 admin]$ ps -ef | grep pmon
oracle 7223 1 0 00:28 ? 00:00:00 ora_pmon_bharat
oracle 7545 5073 0 00:38 pts/1 00:00:00 grep pmon
[oracle@rac1 admin]$ kill -9 7223

2. Check the config

DGMGRL> show configuration
 Configuration - bharatDR
 Protection Mode: MaxAvailability
Databases:
bharat_pri - Primary database
bharat_stby - Physical standby database
 Fast-Start Failover: DISABLED
 Configuration Status:
ORA-01034: ORACLE not available
ORA-16625: cannot reach database "bharat_pri"
DGM-17017: unable to determine configuration status

3. Now failover

DGMGRL> failover to bharat_stby
Performing failover NOW, please wait...
Failover succeeded, new primary is "bharat_stby"

4. Now your old standby is the new primary and, as expected, the original primary needs to be reinstated:

Mount the database bharat_pri. (Re-instate works only if flashback is enabled)

DGMGRL> reinstate 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

Enabling Fast-Start failover

  DGMGRL> edit database 'bharat_pri' set property 'LogXptMode'='SYNC';
  DGMGRL> edit database 'bharat_stby' set property 'LogXptMode'='SYNC'; 
  DGMGRL> edit database 'bharat_pri' set PROPERTY 
                                         fastStartFailoverTarget='bharat_stby';
  Property “faststartfailovertarget” updated
  DGMGRL> edit database 'bharat_stby' set PROPERTY 
                                          FastStartFailoverTarget='bharat_pri'; 
  Property “faststartfailovertarget” updated 
  DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY; Succeeded.

Start Observer:

Script for starting observer (Start in remote server) 
nohup dgmgrl sys/manager@bharat_pri "start observer" 
                                 -logfile $HOME/observer.log &

Enable fast start failover

DGMGRL> ENABLE FAST_START FAILOVER;
 Enabled
Oracle database internals by Riyaj

Discussions about Oracle performance tuning, RAC, Oracle internal & E-business suite.

StartupForce

Where all the Action Is !!!!

Pavan DBA's Blog

The DBA Knowledge Store

ORACLE-BASE - Latest Articles

Where all the Action Is !!!!