Data Guard Physical Standby Database Best Practices – Part I

Introduction

Oracle Data Guard is one of the software solutions provided by Oracle Corporation to maximize high availability of Oracle databases. Oracle Data Guard maintains one or many secondary databases as alternatives to the primary production database.

Data Guard Architecture

Oracle Data Guard supports both physical standby and logical standby sites.

Physical Standby: When the primary database transactions generate redo entries, a redo apply process keeps up the secondary databases with the exact block copies of the primary database.

Logical Standby: SQL apply processes read the redo and convert it to SQL transactions. These are then applied to the secondary database.

Data Guard Modes

Oracle Data Guard can operate in 3 different modes:

• Maximum Protection: Transactions are not allowed to commit until all redo data are written to the online redo logs and propagated to at least one synchronized secondary database. If for any reason, the primary database cannot propagate its redo stream to one secondary database, the primary will shutdown to ensure maximum protection mode.
• Maximum Performance: Transactions are not allowed to commit as soon as the redo are written to the online redo logs. The redo stream is asynchronously propagated to the secondary databases to ensure maximum performance mode.
• Maximum Availability: Transactions are not allowed to commit until all redo data are written to the online redo logs and propagated to at least one synchronized secondary database. If for any reason, the primary database cannot propagate its redo stream to one secondary database, the primary will NOT shutdown and operates as it it were in maximum performance mode until issues are fixed.

Glossary

• Role transition: Changing the role of each database component from primary database to the secondary database or from secondary database to the primary database.
• Switchover: Planned role transition for testing. Manual intervention.
• Fail Over: Unplanned failure. Manual or Automatic intervention. Automatic role transition is the recommended.
• Primary database: Where the users are connected to access to the database.
• Standby database: Exists in the the disaster recovery (DR) site. Where the users are connected in the case of planned role transition (Switchover) or in the case of unplanned failure (Fail Over).
• Data Guard Observer: Process monitors both primary and standby databases and performs an automatic fail over when necessary.
• The Broker: The management framework for Oracle Data Guard. It comes integrated into the oracle database enterprise edition.
• Fast Start Fail Over (FSFO): Automatic fail over to the standby database occurs in case of failure. FSFO requires the broker.

The Environment

• I have 3 VM with version of Oracle Enterprise Linux 5 installed.
• I will use the Oracle Database 11g Enterprise Edition Release 2; 11.2.0.1.0 on the primary database and the standby database.
• Oracle software is installed and a database is already created on the primary site.
• Oracle software is installed and the database will be created during this demonstration for the standby site.
• On the third host, I have installed a version of Oracle database 11g Client with administrator option; 11.2.0.1.0. This is the Data Guard Observer host.
• The Oracle Home is on identical path on both nodes: primary database and the standby database.
• Primary database server name is dg1 with a database name dg1.
• Standby database server name is dg2 with a database name dg2.
• The Data Guard Observer server name is dg3.

For the rest of the document, I am going to implement a physical Data Guard environment in maximum performance mode. The whole document will be split in 2 parts:

1. Prepare the Data Guard environment.
2. Create the standby database and verity the physical standby database. I will then Configure the Data Guard Broker, enable Fast Start Fail Over (FSFO) and run a Data Guard Observer.

Implementation notes

These are the steps to follow:

1. Enable forced logging
2. Create a password file
3. Configure a standby redo log
4. Enable archiving
5. Set up the primary database initialization parameters
6. Configure the listener and tnsnames to support the database on both nodes

Note: It is recommended to apply all patches on the primary and the standby before setting up the Data Guard environment.

Prepare the primary and the physical Standby Environments

Primary Server: Step 1

Oracle Data Guard requires the primary database to be run in FORCE LOGGING mode. This means that statements using the NOLOGGING option will still generate redo information to maintain the Data Guard standby databases.

[oracle@dg1 ~]$ sqlplus / as sysdba

SQL> select force_logging from v$database;

FOR
---
NO

SQL> alter database force logging;

Database altered.

SQL> 

The standby log files are required to store redo received from the primary database. Standby redo logs must be equal or larger to the largest redo log file in the primary database. The standby redo log must have at least one more redo log group than the redo log on the primary database. It is recommended that the standby redo log is created on the primary database and propagated to the standby for any switch over or fail over operations.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1          7   52428800        512          2 YES INACTIVE                828952 15-JAN-12        849105 16-JAN-12
         2          1          8   52428800        512          2 NO  CURRENT                 849105 16-JAN-12    2.8147E+14
         3          1          6   52428800        512          2 YES INACTIVE                822304 15-JAN-12        828952 15-JAN-12

SQL> alter database add standby logfile size 50M;

Database altered.

SQL> 
SQL> alter database add standby logfile size 50M;

Database altered.

SQL> alter database add standby logfile size 50M;

Database altered.

SQL> alter database add standby logfile size 50M;

Database altered.

SQL> 

SQL> select * from v$logfile;

Next, set the LOG_ARCHIVE_CONFIG parameter. The LOG_ARCHIVE_CONFIG parameter enables or disables the sending of redo streams to the standby sites. The DB_UNIQUE_NAME of the primary database is dg1 and the DB_UNIQUE_NAME of the standby database is dg2. The primary database is configured to ship redo log stream to the standby database. In this example, the standby database service is dg2.

Next, STANDBY_FILE_MANAGEMENT is set to AUTO so that when Oracle files are added or dropped from the primary database, these changes are made to the standby databases automatically. The STANDBY_FILE_MANAGEMENT is only applicable to the physical standby databases.

Setting the STANDBY_FILE_MANAGEMENT parameter to AUTO is is recommended when using Oracle Managed Files (OMF) on the primary database. Next, the primary database must be running in ARCHIVELOG mode.

SQL> show parameter db_name

NAME                                 TYPE       VALUE
------------------------------------ ---------- ------------------------------
db_name                              string     dg1
SQL> show parameter db_unique_name

NAME                                 TYPE       VALUE
------------------------------------ ---------- ------------------------------
db_unique_name                       string     dg1
SQL> alter system set log_archive_config='dg_config=(dg1,dg2)';

System altered.

SQL> alter system set log_archive_dest_2=
  2  'service=dg2 async valid_for=(online_logfile,primary_role) db_unique_name=dg2';

System altered.

SQL> alter system set standby_file_management=AUTO;  

System altered.

SQL> 

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     7
Next log sequence to archive   9
Current log sequence           9
SQL> exit;

Ensure the Oracle Net Services aliases for both the primary database and standby database are added to the tnsnames.ora file.

[oracle@dg1 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
dg1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dg1.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dg1)
    )
  )
dg2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dg2.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dg2)
    )
  )

[oracle@dg1 ~]$ 

Copy the updated tnsnames.ora file to the standby site (host).

Standby Server: Step 2

The Oracle database binaries have already been installed at this location ($ORACLE_HOME). The new standby database will have dg2 as the SID.

The listener on the standby site must be configured with a static service entry for the standby database. Restart the listener after modification.

[oracle@dg2 admin]$ cat 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)
         )
        )
       
[oracle@dg2 admin]$ lsnrctl start

Create audit directory files under $ORACLE_BASE/admin/$ORACLE_SID/adump. In my example, I will also create the Fast recover area and the oradata directories.

[oracle@dg2 ~]$ mkdir -p $ORACLE_BASE/admin/dg2/adump
[root@dg2 ~]# mkdir -p /opt/oradata
[root@dg2 ~]# mkdir -p /opt/fast_recovery_area

Next, create a simple parameter file on the standby hist with the DB_NAME value.

[oracle@dg2 dbs]$ echo DB_NAME=dg2 > initdg2.ora
[oracle@dg2 dbs]$ cat initdg2.ora 
DB_NAME=dg2
[oracle@dg2 dbs]$ 

The primary database password file must be copied to the standby system for redo authentication.

[oracle@dg2 dbs]$ scp dg1:$ORACLE_HOME/dbs/orapwdg1 orapwdg2

In the next part of the document, we will create the standby database, verity the physical standby database. We will then configure the Broker, set up the Fast Start failover and the Data Guard Observer.

References:

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

en.wikipedia.org/wiki/Oracle_Data_Guard