Failover is a one way process where your primary database goes down due to some reasons and to get back the production live without any data loss, you convert your existing Physical
Standby database to start behaving as Primary database..
The following conditions must be met before you can use the broker:
■ Primary and standby DB’s must be on same version
■ You must use a SPFILE to ensure the broker can persistently reconcile values between broker properties.
■ DG_BROKER_START parameter must be set to TRUE.
■ DG_BROKER_CONFIG_FILE file should be place in the shared area for RAC.
■ Oracle Net Services network files must be set up on the primary database and on the standby database.
■ To enable DGMGRL to restart instances during the course of broker operations, a service with a specific name must be statically registered with the local listener of each instance.
■ Ensure the COMPATIBLE initialization parameter is set to the same value on all systems.
■ Flashback database should be enable for fast start failover.
Introduction of FSFO observer:-
The Dataguard Broker Observer (Observer) is used when fast start failover is required. Fast start failover is simply an automatic failover to the standby database.This is not often
desired as most often even the most urgent failover/switchover scenarios require configuration of the Application once the database has been activated at the DR site.
The observer will initiate the failover under the following conditions automatically:
By default, the observer will initiate failover to the target standby if and only if ALL of the following are true:
• observer is running
• observer and the standby both lose contact with the primary
•
o Note: if the observer loses contact with the primary, but the standby does not, the observer can determine that the primary is still up via the standby.
• observer is still in contact with the standby
• durability constraints are met
• failover threshold timeout has elapsed
In Oracle 11g, the user can configure failover conditions:
User configurable failover conditions (11g and later)
Oracle Database 11g Rel 1 introduced user configurable failover conditions that can trigger the observer to initiate failover immediately.
Health conditions
Broker can be configured to initiate failover on any of the following conditions. Conditions shown in blue are enabled by default.
• Datafile Offline (due to IO errors)
• Corrupted Controlfile
• Corrupted Dictionary
• Inaccessible Logfile (due to IO errors)
• Stuck Archiver
Primary database Server : server1.soumya.com
Standby database Server : server2.soumya.com
Observer database server: observer.soumya.com
Primary database: prim
Standby database: stand
Observer database: observer
Here I have already done a physical standby server setup and its working properly.So I will only show how to configure DGMRL and use Fast-Start Failover using it.
Here is host file entry for for all server , make sure its present in each of the server.
# cat /etc/hosts
[oracle@server2 ~]$ cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.2.102 server1.soumya.com server1
192.168.2.104 server2.soumya.com server2
192.168.2.105 observer.soumya.com observer
Step 1:- Check parameter DG_BROKER_START on primary and standby:
SQL> show parameter DG_BROKER_START
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean FALSE
Step 2:- Set DG_BROKER_START parameter to true on primary:
SQL> alter system set DG_BROKER_START=true scope=both;
System altered.
SQL> show parameter DG_BROKER_START
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean TRUE
Step 3:- Define a service for the listener that includes the db_unique_name_DGMGRL.db_domain values for the GLOBAL_DBNAME
Update the listener.ora file on primary and standby database
In primary database :-
[oracle@server1 ~]$ cat /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = prim.soumya.com)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = prim)
)
)
(SID_DESC =
(GLOBAL_DBNAME = prim_DGMGRL.soumya.com)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = prim)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server1.soumya.com)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
INBOUND_CONNECT_TIMEOUT_LISTENER=120
-- save and quit (:wq)
In standby database:-
[oracle@server2 ~]$ cat /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stand.soumya.com)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = stand)
)
)
(SID_DESC =
(GLOBAL_DBNAME = stand_DGMGRL.soumya.com)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = stand)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server2.soumya.com)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
INBOUND_CONNECT_TIMEOUT_LISTENER=120
-- save and quit (:wq)
Tnsnames.ora entry for all server:-
[oracle@ ~]$cat /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
prim =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server1.soumya.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRIM)
)
)
stand =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server2.soumya.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STAND)
)
)
observer
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = observer.soumya.com )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = observer)
)
)
Once changes are done , restart the listener.
Step 4: Now lets create the data guard broker configurations:-
[oracle@server1 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/redhat@prim
Connected.
DGMGRL> create configuration 'DBTEST'
> as primary database is 'prim'
> connect identifier is prim;
Configuration "DBTEST" created with primary database "prim"
DGMGRL> show configuration
Configuration - DBTEST
Protection Mode: MaxPerformance
Databases:
prim - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
Step 7 :- Now add standby database in data guard broker configuration:-
DGMGRL> add database 'stand' as
> connect identifier is stand
> maintained as physical;
Database "stand" added
DGMGRL>
DGMGRL> show configuration
Configuration - DBTEST
Protection Mode: MaxPerformance
Databases:
prim - Primary database
stand - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
Step 8:-Now enable the configurations
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration
Configuration - DBTEST
Protection Mode: MaxPerformance
Databases:
prim - Primary database
stand - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database verbose stand
Database - stand
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
stand
Properties:
DGConnectIdentifier = 'stand'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'auto'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'prim, stand'
LogFileNameConvert = 'prim, stand'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'server2.soumya.com'
SidName = 'stand'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server2.soumya.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=stand_DGMGRL)(INSTANCE_NAME=stand)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/u01/app/oracle/flash_recovery_area'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
DGMGRL> show database verbose prim;
Database - prim
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
prim
Properties:
DGConnectIdentifier = 'prim'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'auto'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'stand, prim'
LogFileNameConvert = 'stand, prim'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'server1.soumya.com'
SidName = 'prim'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server1.soumya.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=prim_DGMGRL)(INSTANCE_NAME=prim)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/u01/app/oracle/flash_recovery_area'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;
Configuration - DBTEST
Protection Mode: MaxPerformance
Databases:
prim - Primary database
stand - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> enable database stand;
Enabled.
DGMGRL> SHOW DATABASE VERBOSE stand;
Database - stand
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
stand
Properties:
DGConnectIdentifier = 'stand'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'auto'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'prim, stand'
LogFileNameConvert = 'prim, stand'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'server2.soumya.com'
SidName = 'stand'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server2.soumya.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=stand_DGMGRL)(INSTANCE_NAME=stand)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/u01/app/oracle/flash_recovery_area'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
Step 8 :-Ensure standby redologs are configured on primary & standby databases.
on primary Database:
SQL> set lines 200
SQL> col member for a50
SQL>
SQL> SELECT TYPE,MEMBER FROM V$LOGFILE;
TYPE MEMBER
------- --------------------------------------------------
ONLINE /u01/app/oracle/oradata/prim/redo03.log
ONLINE /u01/app/oracle/oradata/prim/redo02.log
ONLINE /u01/app/oracle/oradata/prim/redo01.log
STANDBY /u01/app/oracle/oradata/prim/redos4A.log
STANDBY /u01/app/oracle/oradata/prim/redos5A.log
STANDBY /u01/app/oracle/oradata/prim/redos6A.log
STANDBY /u01/app/oracle/oradata/prim/redos7A.log
7 rows selected.
On Standby Database:-
SQL> set lines 200
SQL> col member for a50
SQL> SELECT TYPE,MEMBER FROM V$LOGFILE;
TYPE MEMBER
------- --------------------------------------------------
ONLINE /u01/app/oracle/oradata/stand/redo03.log
ONLINE /u01/app/oracle/oradata/stand/redo02.log
ONLINE /u01/app/oracle/oradata/stand/redo01.log
STANDBY /u01/app/oracle/oradata/stand/redos4A.log
STANDBY /u01/app/oracle/oradata/stand/redos5A.log
STANDBY /u01/app/oracle/oradata/stand/redos6A.log
STANDBY /u01/app/oracle/oradata/stand/redos7A.log
7 rows selected.
Step 9:- Configure the LogXptMode Property is set to SYNC
DGMGRL> EDIT DATABASE prim SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated
DGMGRL> EDIT DATABASE stand SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated
Step 10:-Configure the FastStartFailoverTarget property
DGMGRL> EDIT DATABASE prim SET PROPERTY
> FastStartFailoverTarget='stand';
Property "faststartfailovertarget" updated
DGMGRL> EDIT DATABASE stand SET PROPERTY
> FastStartFailoverTarget='prim';
Property "faststartfailovertarget" updated
Step 11:- Upgrade the protection mode to MAXAVAILABILITY, if required.
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Succeeded.
Step 12:-To enable the Flashback Mode run below queries on primary & standby database.
SQL> alter system set db_recovery_file_dest_size=1G;
System altered.
SQL> alter system set db_recovery_file_dest='/u01/app/oracle/flash_recovery_area';
System altered.
SQL> ALTER SYSTEM SET UNDO_RETENTION=3600 SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET UNDO_MANAGEMENT='AUTO' SCOPE=SPFILE;
System altered.
Step 13:- Now restart the database one by one. and start the database in mount stage.But NOT TOGETHER.First on primary.
on Primary:-
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 314574840 bytes
Database Buffers 92274688 bytes
Redo Buffers 4308992 bytes
Database mounted.
SQL> ALTER DATABASE FLASHBACK ON;
Database altered.
SQL> alter database open;
Database altered.
SQL> select FLASHBACK_ON from v$database;
FLASHBACK_ON
------------------
YES
On standby:-
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 314574840 bytes
Database Buffers 92274688 bytes
Redo Buffers 4308992 bytes
Database mounted.
SQL> alter database recover managed standby database cancel;
SQL> ALTER DATABASE FLASHBACK ON;
Database altered.
SQL> alter database open;
SQL> alter database recover managed standby database using current logfile disconnect from session;
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
SQL> select FLASHBACK_ON from v$database;
FLASHBACK_ON
------------------
YES
Step 14:-Enable fast start failover
[oracle@server1 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/redhat@prim
Connected.
DGMGRL> show configuration verbose;
Configuration - DBTEST
Protection Mode: MaxAvailability
Databases:
prim - Primary database
stand - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database stand;
Database - stand
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
stand
Database Status:
SUCCESS
DGMGRL> ENABLE FAST_START FAILOVER;
Enabled.
Step 15:- Start the observer
At Oberserver server(192.168.2.105):-
[oracle@observer ~]$ mkdir -p /u01/FSFO
[oracle@observer ~]$ cd /u01/FSFO
[oracle@observer FSFO] ~]$ vi startobserver.sh
#!/bin/bash
# startobserver
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export BASE_PATH=/u01/FSFO:/opt/CTEact/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/bin:/etc:/usr/local/maint/oracle:/usr/ccs/bin:/usr/openwin/bin:/usr/dt/bin:/usr/local/bin:.
export PATH=$ORACLE_HOME/bin:$BASE_PATH
dgmgrl << eof
connect sys/redhat@prim
START OBSERVER;
eof
-- save & exit (:wq)
[oracle@observer FSFO] ~]$chmod 775 startobserver.sh
Now start the observer:-
[oracle@observer FSFO] ~]$sh startobserver.sh
DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> Connected.
DGMGRL> Observer started
Step 16:-Verify the fast-start failover configuration
DGMGRL> SHOW CONFIGURATION VERBOSE;
Configuration - DBTEST
Protection Mode: MaxAvailability
Databases:
prim - Primary database
stand - (*) Physical standby database
(*) Fast-Start Failover target
Fast-Start Failover: ENABLED
Threshold: 30 seconds
Target: stand
Observer: observer.soumya.com
Lag Limit: 30 seconds (not in use)
Shutdown Primary: TRUE
Auto-reinstate: TRUE
Configuration Status:
SUCCESS
DGMGRL> show database prim
Database - prim
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
prim
Database Status:
SUCCESS
DGMGRL> show database stand
Database - stand
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
stand
Database Status:
SUCCESS
Step 17:- EXECUTE THE SWITCHOVER
DGMGRL> switchover to stand;
Performing switchover NOW, please wait...
New primary database "stand" is opening...
Operation requires shutdown of instance "prim" on database "prim"
Shutting down instance "prim"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "prim" on database "prim"
Starting instance "prim"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Failed.
Warning: You are no longer connected to ORACLE.
Please complete the following steps to finish switchover:
start up instance "prim" of database "prim"
Start the database at primary server(192.168.2.102)
SQL> startup
Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 360712184 bytes
Database Buffers 46137344 bytes
Redo Buffers 4308992 bytes
Database mounted.
Database opened.
[oracle@server1 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/redhat@prim
Connected.
DGMGRL> show configuration verbose
Configuration - DBTEST
Protection Mode: MaxAvailability
Databases:
stand - Primary database
prim - (*) Physical standby database
(*) Fast-Start Failover target
Fast-Start Failover: ENABLED
Threshold: 30 seconds
Target: prim
Observer: observer.soumya.com
Lag Limit: 30 seconds (not in use)
Shutdown Primary: TRUE
Auto-reinstate: TRUE
Configuration Status:
SUCCESS
DGMGRL> show database prim
Database - prim
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
prim
Database Status:
SUCCESS
Step 18:-Failover to standby(old primary)
Note:- We will need to run this from the new standby database
[oracle@server1 ~]$ dgmgrl sys/redhat@prim
DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> failover to prim;
Performing failover NOW, please wait...
Failover succeeded, new primary is "prim"
DGMGRL> exit
Now once failover is completed, we will find that our old primary(prim) is back again in as primary database.
[oracle@server1 ~]$ sqlplus / as sysdba
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
prim
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
But we will need to start the standby database as it has been shutdown by dataguard broker process.
At standby server(192.168.2.104):-
SQL> startup mount
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 352323576 bytes
Database Buffers 54525952 bytes
Redo Buffers 4308992 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
Done...
Please share your ideas and opinions about this topic.
If you like this post, then please share with others.
Please subscribe on email for every updates on mail.
