Chapter 2. Configuring the Oracle Data Guard Physical Standby Database

In this chapter, the installation of the physical standby database will be covered in three steps. The first step will be to prepare the environment for the installation, especially the preinstallation tasks on the database. Then the second step for creating a physical standby database will be covered. In the last step, the Data Guard installation will be verified to see if it is installed correctly.

In this chapter, we'll discuss the following topics:

• Planning and understanding requirements

• Preparation for the configuration

• Step-by-step instructions to create the physical standby database

• Verifying the physical standby database configuration (post-installation steps)

Before preparing the configuration, you should know the business criticality of your database, how to avoid failures, and how much data you are ready to lose.

Preconfiguration for Data Guard

The Data Guard configuration contains a primary database that transmits redo data to a standby database. The standby database is remotely located from the primary database for disaster recovery and backup operations. You can also configure the standby database at the same location as the primary database. However, for disaster-recovery purposes and to make it highly available, it's strongly recommended to configure standby in a geographically remote location.

Before implementing a Data Guard configuration, take into account concepts such as high data availability, efficient systems utilization, and data protection.

• Availability: Outages should be tolerated transparently and should be recovered quickly in case of server failures or any network failures

• Protection: Ensure minimum data loss; standby data should be isolated from production faults such as storage failures, site failures, data corruptions, or operator errors

• Utilization: Standby resources should...

Time for action – enabling the archive log mode

Perform the following steps on the primary database:

1. Check whether archiving has been enabled or disabled, as follows:

   SQL> archive log list
   Database log mode                No Archive Mode
   Automatic archival               Disabled
   Archive destination              USE_DB_RECOVERY_FILE_DEST
   Oldest online log sequence       6
   Current log sequence             8
   

2. Perform a clean shutdown, as follows:

   SQL> shutdown immediate
   Database closed.
   Database dismounted.
   ORACLE instance shut down.
   

   Tip

   Ensure that you have performed a clean shutdown; if not, you may see this error: ORA-00265: instance recovery required, cannot set ARCHIVELOG mode.

3. Start the database in the mount state.

   SQL>startup mount
   ORACLE instance started.
   Total System Global Area      818401280 bytes
   Fixed Size                    2217792 bytes
   Variable Size                 515901632 bytes
   Database Buffers              297795584 bytes
   Redo Buffers                  2486272 bytes...

Time for action – enabling force logging

Perform the following steps on the primary database:

1. Check the force logging status as follows:

   SQL> select name, force_logging from v$database;
   NAME      FOR
   --------- ---
   ORCL      NO
   

2. Enable the force logging mode as follows:

   Enabling Force Logging on Primary Database is mandatory. 
   SQL> alter database force logging;
   Database altered.
   

3. Check the force logging status again as follows:

   SQL> select name,force_logging from v$database;
   NAME      FOR
   --------- ---
   ORCL      YES
   

   In the alert log, you'll see following lines:

   alter database force logging
   ALTER DATABASE FORCE LOGGING command is waiting for existing direct writes to finish. This may take a long time.
   Completed: alter database force logging
   

What just happened?

We've put the primary database in the force logging mode, which is required for the Data Guard physical standby database to work properly.

Standby redo logs

Standby redo logfiles are used by a standby database to store the redo...

Time for action – configuring standby redo logs on primary

Run the following procedures on the primary database to create standby redo logfiles:

1. Check the ORL's members and the sizes of each member as follows:

   SQL> select a.group#, a.status, a.bytes/1024/1024 SizeMB, b.member from v$log a, v$logfile b where a.group#=b.group# order by group#;
   
   GROUP# STATUS       SizeMB MEMBER
   ------ -------- ---------- --------------------------------------
        1 INACTIVE        100 /u01/app/oracle/oradata/orcl/redo01.log
        2 CURRENT         100 /u01/app/oracle/oradata/orcl/redo02.log
        3 INACTIVE        100 /u01/app/oracle/oradata/orcl/redo03.log
        4 INACTIVE        100 /u01/app/oracle/oradata/orcl/redo04.log
   

   Tip

   In this single instance of the primary database, we have four redo log groups, each with one member and a size of 100 MB. We should create at least five standby redo log groups.

2. Add the standby redo logfiles as shown in the following example:

   SQL> alter database add standby logfile...

Time for action – enabling FRA

Perform the following steps on the primary database now. We'll be enabling FRA on the standby database later.

1. Check the default FRA location as follows:

   SQL> show parameter db_recovery_file_dest
   NAME                                 TYPE        VALUE
   ------------------------------------ ----------- -----------
   db_recovery_file_dest                string
   

2. Configure the FRA size.

   SQL> alter system set db_recovery_file_dest_size=4g;
   System altered.
   

3. Configure the FRA destination.

   SQL> alter system set db_recovery_file_dest='/u01/app/oracle/flash_recovery_area';
   System altered.
   

4. Control the FRA configuration.

   SQL> show parameter db_recovery_file_dest
   NAME                        VALUE
   ----------------------      ------------------------
   db_recovery_file_dest       /u01/app/oracle/flash_recovery_area
   db_recovery_file_dest_size  4G
   

Tip

In RAC databases, use the keyword sid='*'; this ensures that the change will apply to all instances in the cluster.

What just...

Creating the physical standby database

In order to create a physical standby database, we first need to install Oracle database binaries to the standby database server and then start a standby database instance. Installing Oracle binaries is out of this book's scope, so it's assumed a standby server is ready with the Oracle database software installed. We will start by covering a standby database instance and copying database files from primary to standby, but first let's look at the initialization parameters that we need to set on standby before starting the instance.

Standby database related initialization parameters

The following are the important Data-Guard-related initialization parameters we set on physical standby databases.

FAL_SERVER

This parameter specifies from where the standby database should request missing archived logs if there is a gap in the logs. It is used only when the database is in the standby role and has a gap in the received archived logs.

A redo gap occurs when the...

Time for action – starting the physical standby instance and making it ready for the RMAN duplicate

Execute the following steps to start a database instance on the standby server and make it ready for the RMAN duplicate operation.

1. Create a service in Windows.

   If you are creating a Data Guard configuration in Windows, you must create a service using the oradim utility as follows:

   oradim -NEW -SID <sid> -STARTMODE manual -PFILE C:\app\oracle\product\11.2.0\admin\<sid>\pfile\init.ora
   

   You can skip this step if the environment is not Windows.

2. Set the standby database initialization parameters:

   Copy the PFILE from the primary system to the standby system under the $ORACLE_HOME/dbs directory with the proper name (initINDIA.ora in our example). Make changes as needed if the control file locations will be different on the standby database and then change locations. The diagnostic destination and memory must be checked. You also need to set the standby-related parameters we've just covered...

Time for action – running an RMAN duplicate

Perform the following steps to create a standby database with the RMAN duplicate method:

1. Check the primary database status; it must be either open or mount.

   SQL> select db_unique_name,database_role,open_mode from v$database;
   DB_UNIQUE_ DATABASE_ROLE    OPEN_MODE
   ---------- ---------------- --------------------
   turkey_un  PRIMARY          READ WRITE
   

2. Run the RMAN command from the standby system. Connect the primary and standby instances using Oracle Net Service names.

   [oracle@oracle-stbydbs]$ rman target sys/free2go@turkey auxiliary sys/free2go@india
   Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jul 26 18:41:06 2012
   Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
   connected to target database: ORCL (DBID=1316772835)
   connected to auxiliary database: ORCL (not mounted)
   

   RMAN will show the connected sessions as shown previously, which provided the primary status—either open or mounted. Also, standby is in the...

Post-installation steps

In this section, we'll verify the standby database status, start Redo Apply to synchronize the standby database with the primary database, and see how we check the status of Redo Apply at the end.

Verifying the standby database configuration

After creating the physical standby database and enabling redo transport services, you may want to verify the standby database configuration and also check if the database changes are being successfully transmitted from the primary database to standby.

Time for action – verifying the standby database configuration

Run the following actions to verify the standby database configuration and redo transport services:

1. Connect the standby database using SQL*Plus and check for the database role and status to ensure the database role is the physical standby.

   SQL> select db_unique_name,database_role,open_mode from v$database;
   DB_UNIQUE_NAME  DATABASE_ROLE    OPEN_MODE
   --------------- ---------------- --------------------
   india_un        PHYSICAL STANDBY MOUNTED
   

2. Check the standby database, SPFILE.

   SQL> show parameter spfile
   NAME   TYPE    VALUE
   ------ ------- --------------------------------------------------
   spfile string  /u01/app/oracle/product/11.2.0/db_1/dbs/spfileINDIA.ora
   

   Tip

   If you have started the standby instance with PFILE, you should create an SPFILE and start an instance again using the new SPFILE.

3. Use the v$datafile view to check the location of the datafiles in the standby database. The standby database datafile must be under the...

Time for action – starting, stopping, and monitoring MRP

Before starting Redo Apply services, the physical standby database must be in the MOUNT status. From 11g onwards, the standby database can also be in the OPEN mode. If the redo transport service is in the ARCH mode, the redo will be applied from the archived redo logfiles after being transferred to the standby database. If the redo transport service is in LGWR, the Log network server (LNS) will be reading the redo buffer in SGA and will send redo to Oracle Net Services for transmission to the standby redo logfiles of the standby database using the RFS process. On the standby database, redo will be applied from the standby redo logs.

Redo apply can be specified either as a foreground session or as a background process; it can also be started with real-time apply.

Tip

To execute the following commands, the control file must be a standby control file. If you execute these commands in a database in the primary mode, Oracle will return an...

Time for action – verifying synchronization between the primary and standby databases

By using the following steps, you can control whether the standby database is synchronized with primary:

1. On the standby database, query the V$ARCHIVED_LOG view for the archived and applied sequences.

   For the last archived sequence, use the following:

   SQL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG;
   MAX(SEQUENCE#)
   --------------
              145
   

   For the last applied sequence, use the following:

   SQL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED='YES';
   MAX(SEQUENCE#)
   --------------
              144
   

   From the preceding two queries, we see that the latest sequence, 145, is being archived or written into the standby redo logfiles. There's expected to be a lag of one sequence between archived and applied columns.

2. Check the status of the latest log sequence.

   SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
   
    SEQUENCE# APPLIED
   ---------- ---------
          140 YES
          141 YES
     ...

Time for action – testing real-time apply

If real-time apply is enabled, the apply services can apply redo data without waiting for the current standby redo logfile to be archived. This allows faster role transitions because you avoid waiting for a redo log to be transported to the standby database and then applied. In this example, we'll see how changes are transferred and applied to the standby database. The redo log that includes changes is not archived on primary.

1. In order to use real-time apply, the redo transport service from primary to standby must use LGWR. Run the following query on the primary database and check the log archive destination configuration.

   SQL> show parameter log_archive_dest_2
   NAME                TYPE       VALUE
   ------------------- --------   ----------
   log_archive_dest_2  string     SERVICE=INDIA LGWR ASYNC VALID_FOR                                                                                       =(ONLINE_LOGFILES,PRIMARY_ROLE)                    DB_UNIQUE_NAME...

Summary

We have finished this chapter by describing Data Guard physical standby database creation, configuration, and controlling. We used the RMAN duplicate from the active database method, which is the easiest and most efficient way of creating a physical standby database. This method doesn't require a backup staging disk area in either primary or standby servers because it performs a direct copy from primary files to standby. This chapter also covered pre and post steps of creating a standby database with RMAN duplicate. We learned starting, stopping, and monitoring Redo Apply and the synchronization method of physical standby databases, including real-time apply. In the next chapter, we'll learn about building a Data Guard logical standby database environment.