Creating Data Guard Physical Standby Database - Part1

Here I will cover the below topics.

• Data guard introduction and Architecture
• Data Guard Modes
• Environment I will use
• Implementation glossary
• Prepare primary and physical Standby Environments
• Create and verify the physical standby database
• Active-Standby database & Real-time query
• Broker configuration
• Set up Fast Start failover and Data Guard Observer
• RMAN backups to the standby database
• Open standby database in the read-write mode for testing new functionality in the application.
  

Introduction:

Oracle Data Guard is one of the software solutions provided by Oracle Corporation to maximize the high availability of Oracle databases. Oracle Data Guard maintains one or maximum up to thirty associated standby databases (up to 30 in 11g and up to 9 in 10g) as alternatives to the primary production database. These standby databases are maintained as transaction consistent copies of the production database.

Architecture:

A standby database can be

Physical standby database - A physical standby database is kept synchronized with the primary database, though Redo Apply, which recovers the redo data, received from the primary database and applies the redo to the physical standby database.

Logical standby database - The logical standby database is kept synchronized with the primary database through SQL Apply, which transforms the data in the redo received from the primary database into SQL statements and then executes the SQL statements on the standby database.

Active Data Guard standby database - A Physical standby database that is open to read access with up-to-date data from the Primary database.

Snapshot Standby database - A fully read-write standby database that is created by converting a physical standby database into a Read-write snapshot standby database.

Data Guard Modes:

Oracle Data Guard can operate in 3 different modes.

Maximum Performance - This mode emphasizes primary database performance over data protection. It requires ASYNC redo transport so that the LGWR process never waits for acknowledgment from the standby database.

Maximum Availability - This mode emphasizes availability as its first priority and zero data loss protection as a very close second priority. It requires SYNC redo transport, thus primary database performance may be impacted by the amount of time required to receive an acknowledgment from the standby that redo has been written to disk.

Maximum Protection - As its name implies, this mode places utmost priority on data protection. It has the same requirements as Maximum Availability but the primary will not acknowledge a commit to the application unless it receives an acknowledgment from at least one standby database in the configuration that the data needed to recover that transaction is safely on disk. If the primary does not receive an acknowledgment from a SYNC standby database, it will stall and eventually abort, preventing any unprotected commits from occurring.

The environment I will use:

1. I have 3 VM with a version of Oracle Enterprise Linux 5 installed. Find the below link for install OEL 5 in Oracle VirtualBox.
      Oracle Enterprise Linux 5.7 installation on Oracle VirtualBox 
2. I will use the Oracle Database 11g Enterprise Edition Release 2,  on the primary database and the standby database. Find the below link for installing oracle 11gR2 software and database creation. 
      Installation of Oracle Database 11gR2 on Oracle Enterprise Linux 5.7
3. Oracle software is installed and a database is already created on the primary site.
4. Oracle software is installed and the database will be created during this demonstration for the standby site.
5. On the third host, I have installed a version of Oracle database 11g Client. This is the Data Guard Observer host.
6. The Oracle Home is on the identical path on both nodes: the primary database and the standby database.
7. Primary database server name is primdb with a database name primdb.
8. Standby database server name is standb with a database name standb.
9. The Data Guard Observer server name is obser.

Implementation glossary:

1. Enable forced logging
2. Create a password file
3. Configure standby redo logs
4. Enable archivelog mode
5. Set up the primary and standby database initialization parameters
6. Configure the listener and tnsnames on both the nodes
7. Create a password file in both the nodes

Prepare primary and 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.

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
PRIMDB    READ WRITE

SQL> select force_logging from v$database;

FOR
---
NO

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FOR
---
YES

SQL>

Create a password file if one does not already exist. Every database in a Data Guard configuration must use a password file, and the password for the SYS user must be identical on every system for redo data transmission to succeed. Move the same Password file to the standby side and rename it with Standby SID.

$ orapwd force=y file=orapwPRIMDB password=sysprimdb

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. If we have 'N' redo log groups then should have 'N+1' standby redo logs. It is recommended that the standby redo log is created on the primary database and propagated to the standby for any switch-over or failover operations.

SQL> select GROUP#, THREAD#, SEQUENCE#, BYTES, BLOCKSIZE, MEMBERS, STATUS from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS  STATUS
    ---------     ---------- ---------- ---------- ---------- ---------- ----------------
         1               1                  19                     52428800        512          1  INACTIVE

         2               1                  20                     52428800        512          1  INACTIVE

         3              1                   21                     52428800        512          1  CURRENT


SQL>  
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> select GROUP#, STATUS, TYPE, MEMBER from v$logfile;

    GROUP# STATUS  TYPE     MEMBER
---------- ------- ------- ----------------------------------------------
         3         ONLINE   /u01/app/oracle/oradata/primdb/redo03.log

         2         ONLINE   /u01/app/oracle/oradata/primdb/redo02.log

         1         ONLINE   /u01/app/oracle/oradata/primdb/redo01.log

         4         STANDBY  /u01/app/oracle/oradata/primdb/redo04.log

         5         STANDBY  /u01/app/oracle/oradata/primdb/redo05.log

         6         STANDBY  /u01/app/oracle/oradata/primdb/redo06.log

         7         STANDBY  /u01/app/oracle/oradata/primdb/redo07.log

7 rows selected.

SQL> 

Follow the steps for enabling archivelog mode - Enable and Disable Archive Log Mode

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 primdb and the DB_UNIQUE_NAME of the standby database is standb. The primary database is configured to ship redo log stream to the standby database. In this example, the standby database service is standb.

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 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     primdb
SQL> show parameter db_unique_name

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

System altered.

SQL> alter system set log_archive_dest_2=
  2  'service=standb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)  DB_UNIQUE_NAME=standb';

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; 

The listener on the primary site must be configured with a static service entry for the primary database. After modified the listener file restart the listener.

[oracle@primdb admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (ADDRESS_LIST=
       (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.98)(PORT=1521))
       (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))   
SID_LIST_LISTENER=
   (SID_LIST=
       (SID_DESC=
          (GLOBAL_DBNAME=primdb)
          (SID_NAME=primdb)                      
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
         )
        )
[oracle@primdb admin]$

Add below TNS entries in both primary and standby databases.

[oracle@primdb admin]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PRIMDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.98)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = primdb)
    )
  )

STANDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.99)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standb)
    )

Create password file and copy it to standby side and rename to standby SID.

[oracle@primdb dbs]$ orapwd force=y file=orapwprimdb password=system
[oracle@primdb dbs]$ ls orapwprimdb 
orapwprimdb
[oracle@primdb dbs]$

Standby server step2:

Same version of oracle software is installed in standby server. The new standby database will have standb as the SID. The listener on the standby site must be configured with a static service entry for the standby database. After modified the listener file restart the listener.

[oracle@primdb admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (ADDRESS_LIST=
       (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.99)(PORT=1521))
       (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))   
SID_LIST_LISTENER=
   (SID_LIST=
       (SID_DESC=
          (GLOBAL_DBNAME=standb)
          (SID_NAME=standb)                      
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
         )
        )
[oracle@standb admin]$

Create required directories for the Fast recover area and the oradata directories.

[oracle@standb ~]$ mkdir -p $ORACLE_BASE/admin/standb/adump
[oracle@standb ~]# mkdir -p /u01/app/oracle/oradata/standb
[oracle@standb ~]# mkdir -p /u01/app/oracle/fast_recovery_area 

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

Hope this will help you... :)

Best Regards,