DG Broker Configuration with fast_start failover & Observer

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

Standby Databases – Oracle 11g

Types:

Physical Standby

A physical standby database is an exact, block-for-block copy of a primary database. It is maintained as an exact copy through a process called Redo Apply, in which redo data received from a primary database is continuously applied to a physical standby database using the database recovery mechanisms.

Logical Standby

A logical standby database is initially created as an identical copy of the primary database, but it later can be altered to have a different structure. The logical standby database is updated by executing SQL statements. This allows users to access the standby database for queries and reporting at any time. Thus, the logical standby database can be used concurrently for data protection and reporting operations.

Data Guard automatically applies information from the archived redo log file or standby redo log file to the logical standby database by transforming the data in the log files into SQL statements and then executing the SQL statements on the logical standby database. Because the logical standby database is updated using SQL statements, it must remain open. Although the logical standby database is opened in read/write mode, its target tables for the regenerated SQL are available only for read-only operations. While those tables are being updated, they can be used simultaneously for other tasks such as reporting, summations, and queries. Moreover, these tasks can be optimized by creating additional indexes and materialized views on the maintained tables.

A logical standby database has some restrictions on datatypes, types of tables, and types of DDL and DML operations

Snapshot Standby

A snapshot standby database is a fully updatable standby database that is created by converting a physical standby database into a snapshot standby database. A snapshot standby database receives and archives, but does not apply, redo data from its primary database. Redo data received from the primary database is applied when a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby databases.

Protection Modes:

Maximum Availability

This protection mode provides the highest level of data protection that is possible without compromising the availability of a primary database. Transactions do not commit until all redo data needed to recover those transactions has been written to the online redo log and to the standby redo log on at least one synchronized standby database. If the primary database cannot write its redo stream to at least one synchronized standby database, it operates as if it were in maximum performance mode to preserve primary database availability until it is again able to write its redo stream to a synchronized standby database.

This mode ensures that no data loss will occur if the primary database fails, but only if a second fault does not prevent a complete set of redo data from being sent from the primary database to at least one standby database.

Maximum Performance

This protection mode provides the highest level of data protection that is possible without affecting the performance of a primary database. This is accomplished by allowing transactions to commit as soon as all redo data generated by those transactions has been written to the online log. Redo data is also written to one or more standby databases, but this is done asynchronously with respect to transaction commitment, so primary database performance is unaffected by delays in writing redo data to the standby database(s).

This protection mode offers slightly less data protection than maximum availability mode and has minimal impact on primary database performance.

This is the default protection mode.

Maximum Protection

This protection mode ensures that no data loss will occur if the primary database fails. To provide this level of protection, the redo data needed to recover a transaction must be written to both the online redo log and to the standby redo log on at least one synchronized standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down, rather than continue processing transactions, if it cannot write its redo stream to at least one synchronized standby database.

Because this data protection mode prioritizes data protection over primary database availability, Oracle recommends that a minimum of two standby databases be used to protect a primary database that runs in maximum protection mode to prevent a single standby database failure from causing the primary database to shut down.

Standby File Management:

AUTO:
In AUTO mode, operations like adding data file, creating tablespaces are automatically handled by the Data guard services.

MANUAL:

In MANUAL mode, DBA’s have to manually copy the new data file to standby database.