Read Data Guard Physical Standby Database Best Practices – Part I

In this second part we will setup the standby database and enable Fast Start Fail Over.

Standby Database Creation

Start the standby database instance in NOMOUNT start up mode:

SQL> startup nomount pfile=initdg2.ora;

Now that the configuration of the standby server is complete, let’s perform the duplication from the primary site.

Primary Server

Use the Recovery Manager (RMAN) to duplicate the primary database to the standby database.
Invoke RMAN; connect to the primary database as the sys user. Make an auxiliary connection to the standby instance:

RMAN> connect target sys

target database Password: 
connected to target database: DG1 (DBID=1753913301)

RMAN> connect auxiliary sys@dg2

auxiliary database Password: 
connected to auxiliary database: DG2 (not mounted)

RMAN> run{
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'dg1','dg2'
set db_unique_name='dg2'
set db_file_name_convert='/dg1/','/dg2/'
set log_file_name_convert='/dg1/','/dg2/'
set control_files='/opt/oradata/dg2/dg2.ctl'
set log_archive_max_processes='5'
set fal_client='dg2'
set fal_server='dg1'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(dg1,dg2)'
set log_archive_dest_2='service=dg1 ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=dg1'
;
}

The RMAN duplication process is finished without errors. After the database duplication, we will force the log switch on the primary database to archive the current redo log group.

SQL> alter system switch logfile;

Standby Server

On the standby database, run the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE command to start redo apply.The USING CURRENT LOGFILE means the redo is applied as soon as it is received on the standby.The DISCONNECT option means the redo apply will run in background session.

SQL> alter database recover managed standby database 
    using current logfile disconnect;

Note that the current log sequence number on the standby is 10.

SQL> select sequence#, first_time, applied
  from v$archived_log
  order by sequence#;

 SEQUENCE# FIRST_TIM APPLIED
---------- --------- ---------
         9 16-JAN-12 YES
        10 16-JAN-12 IN-MEMORY

Primary Server

Let’s perform 3 additional log switches on the primary database. Then we will query the standby database to verify that the log files are applied to the standby.

SQL> alter system switch logfile;

Standby Server

We query the standby database. The logs were successfully transported and applied.

SQL> select sequence#, first_time, applied
    from v$archived_log
   order by sequence#;

 SEQUENCE# FIRST_TIM APPLIED
---------- --------- ---------
         9 16-JAN-12 YES
        10 16-JAN-12 YES
        11 16-JAN-12 YES
        12 16-JAN-12 YES
        13 16-JAN-12 IN-MEMORY

Broker Configuration

Configuring the broker is recommended because it simplifies data guard operations.

The DG_BROKER_START parameter must be set to TRUE.

Standby Server

SQL> alter system set dg_broker_start=TRUE;

The Oracle Net listener must be also configured with an additional static service identifier. The value of the GLOBAL_DBNAME attribute must be set to a concatenation of _DGMGRL.

[oracle@dg2 dbs]$ cat $ORACLE_HOME/network/admin/listener.ora
LISTENER =
  (ADDRESS_LIST=
       (ADDRESS=(PROTOCOL=tcp)(HOST=dg2.localdomain)(PORT=1521))
       (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))   
SID_LIST_LISTENER=
   (SID_LIST=
       (SID_DESC=
          (GLOBAL_DBNAME=dg2)
          (SID_NAME=dg2)                      
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
    )
   (SID_DESC=
          (GLOBAL_DBNAME=dg2_DGMGRL)
          (SID_NAME=dg2)
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
    )
 )
       
[oracle@dg2 dbs]$ lsnrctl status

(Entry truncated)
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg2.localdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
Services Summary...
Service "dg2" has 1 instance(s).
  Instance "dg2", status UNKNOWN, has 1 handler(s) for this service...
Service "dg2_DGMGRL" has 1 instance(s).
  Instance "dg2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Primary Server

SQL> alter system set dg_broker_start=TRUE;

The Oracle Net listener on the primary database should have static service definitions:

[oracle@dg1 ~]$ cat $ORACLE_HOME/network/admin/listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg1.localdomain)(PORT = 1521))
    )
  )

SID_LIST_LISTENER=
   (SID_LIST=
       (SID_DESC=
          (GLOBAL_DBNAME=dg1)
          (SID_NAME=dg1)
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
    )
   (SID_DESC=
          (GLOBAL_DBNAME=dg1_DGMGRL)
          (SID_NAME=dg1)
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
    )
 )

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@dg1 ~]$ lsnrctl start

(Entry truncated)
Services Summary...
Service "dg1" has 1 instance(s).
  Instance "dg1", status UNKNOWN, has 1 handler(s) for this service...
Service "dg1_DGMGRL" has 1 instance(s).
  Instance "dg1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Let’s create a broker configuration and identify the primary database.

[oracle@dg1 ~]$ dgmgrl
DGMGRL> connect sys
Password:
Connected.
DGMGRL> create configuration 'DGConfig1' as primary database is 'dg1'
> connect identifier is dg1;
Configuration "DGConfig1" created with primary database "dg1"
DGMGRL> add database 'dg2' as connect identifier is dg2;
Database "dg2" added
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration

Configuration - DGConfig1

  Protection Mode: MaxPerformance
  Databases:
    dg1 - Primary database
    dg2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Perform a switchover test:

DGMGRL> switchover to dg2;
Performing switchover NOW, please wait...
New primary database "dg2" is opening...
Operation requires shutdown of instance "dg1" on database "dg1"
Shutting down instance "dg1"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "dg1" on database "dg1"
Starting instance "dg1"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "dg2"
DGMGRL> show configuration;

Configuration - DGConfig1

  Protection Mode: MaxPerformance
  Databases:
    dg2 - Primary database
    dg1 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Standby Server

Confirm of the role switch:

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

Primary Server

The former primary database is now the new physical standby database:

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

Switch over to the former primary database:

DGMGRL> switchover to dg1;
Performing switchover NOW, please wait...
New primary database "dg1" is opening...
Operation requires shutdown of instance "dg2" on database "dg2"
Shutting down instance "dg2"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "dg2" on database "dg2"
Starting instance "dg2"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "dg1"
DGMGRL> show configuration

Configuration - DGConfig1

  Protection Mode: MaxPerformance
  Databases:
    dg1 - Primary database
    dg2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database dg2

Database - dg2

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   5 minutes 2 seconds
  Apply Lag:       5 minutes 51 seconds
  Real Time Query: OFF
  Instance(s):
    dg2

Database Status:
SUCCESS

Enable Fast Start Fail Over

The actual configuration is running in Max Performance mode and Fast Start Fail Over is currently disabled.

Primary Server

To configure FSFO, you must first enable flashback database on both the primary and standby databases. (Further reading: Introduction to Oracle Flashback Technology) 

SQL> alter database flashback on;

Redo apply must be stopped to enable flashback database on the standby database:

DGMGRL> connect sys   
Password:
Connected.
DGMGRL> edit database 'dg2' set state='apply-off';
Succeeded.

Standby Server

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

Primary Server

Restart the Redo apply:

DGMGRL>  edit database 'dg2' set state='apply-on';
Succeeded.

The Observer

The server dg3 will act as the observer in the Fast-Start Fail Over configuration. The Oracle client binaries have been installed with administrator option. Confirm the connectivity with both the primary and the standby databases:

[oracle@dg3 ~]$ tnsping dg1

(Entry truncated)
Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.101)(PORT=1521)))
OK (140 msec)
[oracle@dg3 ~]$ tnsping dg2

(Entry truncated)
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.102)(PORT=1521)))
OK (50 msec)
[oracle@dg3 ~]$

The prerequisites for FSFO have been met. So FSFO can be configured, enabled and started. The FSFO observer process will be started using the DGMGRL session and will be logged to a file named observer.log:

[oracle@dg3 admin]$ dgmgrl -logfile ./observer.log
DGMGRL for Linux: Version 11.2.0.1.0 - Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@dg1
Password:
Connected. 
DGMGRL> show fast_start failover

Fast-Start Failover: DISABLED

  Threshold:        30 seconds
  Target:           (none)
  Observer:         (none)
  Lag Limit:        30 seconds
  Shutdown Primary: TRUE
  Auto-reinstate:   TRUE

Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Offline               YES

  Oracle Error Conditions:
    (none)
 
DGMGRL> edit configuration set property FastStartFailoverLagLimit=60;
Property "faststartfailoverlaglimit" updated
DGMGRL> enable fast_start failover;
Enabled.
DGMGRL> start observer;

Primary Server

DGMGRL> show configuration verbose

Configuration - DGConfig1

  Protection Mode: MaxPerformance
  Databases:
    dg1 - Primary database
    dg2 - (*) Physical standby database

  (*) Fast-Start Failover target

Fast-Start Failover: ENABLED

  Threshold:        30 seconds
  Target:           dg2
  Observer:         dg3.localdomain
  Lag Limit:        60 seconds
  Shutdown Primary: TRUE
  Auto-reinstate:   TRUE

Configuration Status:
SUCCESS

Create a shell script and run the shell script in the background; create observer.sh (MOS ID: 1084681.1)

#!/bin/ksh
dgmgrl -echo -logfile /home/oracle/dgmgrl.log << EOF
connect sys/wissem@dg3
start observer
EOF
chmod +x observer.sh
./observer.sh &

Where do you place the Observer?

That all depends on your requirements, the observer can be placed in a third site separated from primary and standby sites with a HA observer; this means 2 hosts; one observer process running on the first host and the other on standby (not running) on the second host. Only one observer can run at any one time.

References

docs.oracle.com/cd/E11882_01/server.112/e25608/toc.htm