Oracle 10g/11g - Create a Physical Standby Database Using Active Data Guard

Introduction

Oracle Data Guard (known as Oracle Standby Database prior to Oracle9i), forms an extension to the Oracle RDBMS and provides organizations with high availability, data protection, and disaster recovery for enterprise databases. Oracle Data Guard provides the DBA with services for creating, maintaining, managing, and monitoring one or more standby databases. The functionality included with Oracle Data Guard enables enterprise data systems to survive both data corruption as well as major disasters.
This article provides instructions for creating and configuring a physical standby database from a primary database using Oracle Database 11g Release 2  operating in maximum performance protection mode. It should be noted that several different methods exist to create a physical standby database configuration and that this is just one of those ways. The methods outlined in this guide present a simple approach that should be easy to implement in most situations. In fact, if you break down the essential tasks required to build a physical standby database, you will see that it is essentially nothing more than taking a backup of the primary database, creating a standby controlfile, transferring the files to the standby host, mounting the standby database, putting the standby database in managed recovery mode (Redo Apply), and starting remote archiving from the primary database (Redo Transport)..
.

Introduction to Oracle Data Guard

The standby database feature of Oracle was first introduced with the release of Oracle 7 in the early 1990's. The design was fairly simple. Oracle used media recovery to apply archive logs to a remote standby database, however, none of the automation we now take for granted was present in this release of the product. DBA's were required to write custom scripts that shipped and applied archive logs to the remote standby database. It wasn't until Oracle8i where some form of automation was introduced that relied on Oracle Net Services to transfer and apply archive redo logs. DBA's were still required to supply scripts that handled gap resolution and resynchronize the primary and standby database when they lost connectivity with one another. Also included in Oracle8i was a set of pre-written scripts that simplified the switchover and failover process.
With the introduction of Oracle9i, the standby database feature was renamed to Oracle Data Guard. In addition to the re-branding of the product, Oracle delivered a comprehensive automated solution for disaster recovery that was fully integrated with the database kernel. Finally, a fully integrated disaster recovery solution without the need to maintain custom written scripts! Oracle9i also provided a vast array of new features which included automatic gap resolution, enhanced redo transport methods (synchronous and asynchronous redo transport), the ability to configure zero data loss, and the concept of protection modes.
Until Oracle9i Release 2, the only standby database type available was the physical standby database. A physical standby database is an identical, block-for-block copy of the primary database and is kept in sync with the primary using media recovery (also referred to as Redo Apply). Oracle introduced a new type of standby database with Oracle9i Release 2 named Logical Standby Database. This new type of standby database keeps in sync with the primary database using SQL Apply (versus Redo Apply used with a physical standby database). A logical standby database remains open for user access while logical records are being received and applied from the primary database which makes this a great candidate for a reporting database.
When the standby database site is hosted in a different geographical location than the primary site, it provides for an excellent High Availability (HA) solution. When creating a standby database configuration, the DBA should always attempt to keep the primary and standby database sites identical as well as keeping the physical location of the production database transparent to the end user. This allows for an easy role transition scenario for both planned and unplanned outages. When the secondary (standby) site is identical to the primary site, it allows predictable performance and response time after failing over (or switching over) from the primary site.

 

Oracle Database Enterprise Edition Requirement

Oracle Data Guard is only available as a bundled feature included within its Enterprise Edition release of the Oracle Database software. It is not available with Oracle Database Standard Edition. With the exception of performing a rolling database upgrade using logical standby database, it is mandatory that the same release of Oracle Database Enterprise Edition be installed on the primary database and all standby databases!
While it remains possible to simulate a standby database environment running Oracle Database Standard Edition, it requires the DBA to develop custom scripts that manually transfer archived redo log files and then manually applying them to the standby database. This is similar to the methods used to maintain a standby database with Oracle 7. The consequence with this type of configuration is that it does not provide the ease-of-use, manageability, performance, and disaster-recovery capabilities available with Data Guard.

 

Standby Database Types

There are two types of standby databases that can be created with Oracle Data Guard — physical or logical. Deciding which of the two types of standby databases to create is critical and depends on the nature of the business needs the organization is trying to satisfy.
A physical standby database is an identical, block-for-block copy of the primary database and is kept in sync with the primary using media recovery. As redo gets generated on the primary database, it gets transferred to the standby database where an RFS process receives the primary redo and applies the change vectors directly to the standby database. A physical standby database is an excellent choice for disaster recovery.
A logical standby database works in a different manner which keeps in sync with the primary by transforming redo data received from the primary database into logical SQL statements and then executes those SQL statements against the standby database. With a logical standby database, the standby remains open for user access in read/write mode while still receiving and applying logical records from the primary. While a physical standby database is an exact physical replica of the primary, a logical standby database is not. Because Oracle is applying SQL statements to the standby database and not performing media recovery (as is done with a physical standby database), it is possible for the logical standby database to contain the same logical data, but at the same time have a different physical structure. A logical standby database is an excellent solution for a reporting database while at the same time retaining the attributes of a disaster recovery solution. Not only does a logical standby database contain the same logical information as the primary, it can also support the creation of additional objects to support improved reporting requirements.

**************************************************************************

I. Before you get started:

1. Make sure the operating system and platform architecture on the primary and standby systems are the same;

2. Install Oracle database software without the starter database on the standby server and patch it if necessary. Make sure the same Oracle software release is used on the Primary and Standby databases, and Oracle home paths are identical.

3. Test the Standby Database creation on a test environment first before working on the Production database.

II. On the Primary Database Side:

 Enable Force Logging:

Any nologging operations performed on the primary database do not get fully logged within the redo stream. As Oracle Data Guard relies on the redo stream to maintain the standby database, this can result in data inconsistencies between the primary and standby along with a massive headache for the DBA to resolve. To prevent this from occurring, one solution is to place the primary database into force logging mode. In this mode, all nologging operations are permitted to run without error, however, the changes will be placed in the redo stream anyway. Although this is considered an optional step, I make it mandatory when designing an Oracle Data Guard configuration for my clients. Overlooking this on a production environment can result in the DBA spending considerable time during the implementation of a disaster recovery situation.

First  you  check database connectivity on both Primary and Standby.

SQL> conn sys/oracle@PROD as sysdba

If not connected, so please create password file. 

1.     Enable forced logging on your primary database:

SQL> ALTER DATABASE FORCE LOGGING;

SQL> select force_logging from v$database;

2. Create a password file if it doesn’t exist.

1) To check if a password file already exists, run the following command: 

SQL> select * from v$pwfile_users;

2) If it doesn’t exist, use the following command to create one:
- On Windows: 
$cd %ORACLE_HOME%\database
$orapwd file=pwdPRIM.ora password=xxxxxxxx force=y
(Note: Replace xxxxxxxxx with the password for the SYS user.)

- On UNIX:
$Cd $ORACLE_HOME/dbs
$Orapwd file=pwdPRIM.ora password=xxxxxxxx force=y
(Note: Replace xxxxxxxxx with your actual password for the SYS user.)

3. Configure a Standby Redo log.

a) The size of the standby redo log files should match the size of the current Primary database online redo log files. To find out the size of your online redo log files:

SQL> select bytes from v$log;

BYTES
----------
52428800
52428800
52428800

b) Use the following command to determine your current log file groups:

SQL> select group#, member from v$logfile;

c) Create standby Redo log groups.

My primary database had 3 log file groups originally and I created 3 standby redo log groups using the following commands:

creates the standby redo logs on the standby. To make sure the primary database is configured for switchover, we must create the standby redo logs on the primary server.

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo04.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo05.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo06.log') SIZE 50M;

4) To verify the results of the standby redo log groups creation, run the following query:
SQL>select * from v$standby_log;

4. Enable Archiving on Primary. 

If your primary database is not already in Archive Log mode, enable the archive log mode:

SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
SQL>archive log list;

5. Set Primary Database Initialization Parameters

Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE), to add the new primary role parameters.

1) Create pfile from spfile for the primary database:
- On Windows:

SQL>create pfile=’\database\pfilePRIM.ora’ from spfile;
(Note- specify your Oracle home path to replace ‘’).

- On UNIX:
SQL>create pfile=’/dbs/pfilePRIM.ora’ from spfile;
(Note- specify your Oracle home path to replace ‘’).

Before you edit Pfile you must check database connectivity on both Primary and Standby.

SQL> conn sys/oracle@PROD as sysdba

2) Edit pfilePRIM.ora to add the new primary and standby role parameters: (Here the file paths are from a windows system. For UNIX system, specify the path accordingly)

db_name=PRIM
db_unique_name=PRIM
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIM,STAN)'
LOG_ARCHIVE_DEST_1=
'LOCATION=F:\Oracle\flash_recovery_area\PRIM\ARCHIVELOG
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=PRIM'
LOG_ARCHIVE_DEST_2=
'SERVICE=STAN LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=STAN'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
remote_login_passwordfile='EXCLUSIVE'
FAL_SERVER=STAN
FAL_CLIENT=PRIM
STANDBY_FILE_MANAGEMENT=AUTO
# Specify the location of the standby DB datafiles followed by the primary location;
DB_FILE_NAME_CONVERT='E:\oracle\product\11.2.0\oradata\STAN\DATAFILE','E:\oracle\product\11.2.0\oradata\PRIM\DATAFILE'
# Specify the location of the standby DB online redo log files followed by the primary location LOG_FILE_NAME_CONVERT=’E:\oracle\product\11.2.0\oradata\STAN\ONLINELOG’,’E:\oracle\product\11.2.0\oradata\PRIM\ONLINELOG’,’F:\Oracle\flash_recovery_area\STAN\ONLINELOG’,’F:\Oracle\flash_recovery_area\PRIM\ONLINELOG’

Parameters Descriptions

---------------------------------------------------------------------------------------------------------------------------------------------

FAL_SERVER:

 specifies the FAL (fetch archive log) server for a standby database. The  value is an Oracle Net service name, which is assumed to be configured  properly on the standby database system to point to the desired FAL server.

FAL_CLIENT:

 specifies the FAL (fetch archive log) client name that is used by the FAL  service, configured through the FAL_SERVER initialization parameter, to refer to the FAL client. The value is an Oracle Net service name, which is assumed to be configured properly on the FAL server system to point to the FAL client (standby database).

log_archive_config='DG_CONFIG:

Recommended. Specify the DG_CONFIG attribute to identify the DB_UNIQUE_NAME for the primary database and each standby database in the Data Guard configuration.The default value of this parameter enables the primary database to send  redo data to remote destinations and enables standby databases to receive redo data. The _CONFIG attribute attribute must be set to enable the dynamic addition of a standby database to a Data Guard configuration that has a Real Application Clusters primary database running in either maximum protection or maximum availability required.

LOG_ARCHIVE_DEST_STATE_n:

Specify a LOG_ARCHIVE_DEST_STATE_n parameter to enable or disable redo transport services to transmit redo data to the specified (or to an alternate) destination. Define a LOG_ARCHIVE_DEST_STATE_n parameter for every LOG_ARCHIVE_DEST_n parameter

6. Create spfile from pfile, and restart primary database using the new spfile.
Data Guard must use SPFILE. Create the SPFILE and restart database.

- On windows:

SQL> shutdown immediate;
SQL> startup nomount pfile=’\database\pfilePRIM.ora’;
SQL>create spfile from pfile=’\database\pfilePRIM.ora’;
-- Restart the Primary database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>Startup;
(Note- specify your Oracle home path to replace ‘’).

- On UNIX:
SQL> shutdown immediate;
SQL> startup nomount pfile=’/dbs/pfilePRIM.ora’;
SQL>create spfile from pfile=’/dbs/pfilePRIM.ora’;
-- Restart the Primary database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>Startup;
(Note- specify your Oracle home path to replace ‘’).

III. On the Standby Database Site:

1. Create a copy of Primary database data files on the Standby Server:
On Primary DB:
SQL>shutdown immediate;

On Standby Server (While the Primary database is shut down):
1) Create directory for data files, for example, on windows, E:\oracle\product\11.2.0\oradata\STAN\DATAFILE. 
On UNIX, create the directory accordingly.

2) Copy the data files and temp files over.

3) Create directory (multiplexing) for online logs, for example, on Windows, E:\oracle\product\11.2.0\oradata\STAN\ONLINELOG and F:\Oracle\flash_recovery_area\STAN\ONLINELOG.
On UNIX, create the directories accordingly.

4) Copy the online logs over.

2. Create a Control File for the standby database:
On Primary DB, create a control file for the standby to use:
SQL>startup mount;
SQL>alter database create standby controlfile as ‘STAN.ctl;
SQL>ALTER DATABASE OPEN;

3. Copy the Primary DB pfile to Standby server and rename/edit the file.

1) Copy pfilePRIM.ora from Primary server to Standby server, to database folder on Windows or dbs folder on UNIX under the Oracle home path.

2) Rename it to pfileSTAN.ora, and modify the file as follows. : (Here the file paths are from a windows system. For UNIX system, specify the path accordingly)

*.audit_file_dest='E:\oracle\product\11.2.0\admin\STAN\adump'
*.background_dump_dest='E:\oracle\product\11.2.0\admin\STAN\bdump'
*.core_dump_dest='E:\oracle\product\11.2.0\admin\STAN\cdump'
*.user_dump_dest='E:\oracle\product\11.2.0\admin\STAN\udump'
*.compatible='11.2.0.3.0'
control_files='E:\ORACLE\PRODUCT\11.2.0\ORADATA\STAN\CONTROLFILE\STAN.CTL','F:\ORACLE\FLASH_RECOVERY_AREA\STAN\CONTROLFILE\STAN.CTL'
db_name='PRIM'
db_unique_name=STAN
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(PRIM,STAN)’
LOG_ARCHIVE_DEST_1=
‘LOCATION=F:\Oracle\flash_recovery_area\STAN\ARCHIVELOG
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=STAN’
LOG_ARCHIVE_DEST_2=
‘SERVICE=PRIM LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=PRIM’
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=PRIM
FAL_CLIENT=STAN
remote_login_passwordfile='EXCLUSIVE'
# Specify the location of the primary DB datafiles followed by the standby location
DB_FILE_NAME_CONVERT=’E:\oracle\product\11.2.0\oradata\PRIM\DATAFILE’,’E:\oracle\product\11.2.0\oradata\STAN\DATAFILE’
# Specify the location of the primary DB online redo log files followed by the standby location
LOG_FILE_NAME_CONVERT=’E:\oracle\product\11.2.0\oradata\PRIM\ONLINELOG’,’E:\oracle\product\11.2.0\oradata\STAN\ONLINELOG’,’F:\Oracle\flash_recovery_area\PRIM\ONLINELOG’,’F:\Oracle\flash_recovery_area\STAN\ONLINELOG’
STANDBY_FILE_MANAGEMENT=AUTO

(Note: Not all the parameter entries are listed here.)

4. On Standby server, create all required directories for dump and archived log destination:
Create directories adump, bdump, cdump, udump, and archived log destinations for the standby database.

5. Copy the standby control file ‘STAN.ctl’ from primary to standby destinations ;

6. Copy the Primary password file to standby and rename it to pwdSTAN.ora.
On Windows copy it to \database folder, and on UNIX copy it to /dbs directory. And then rename the password file.

7. For Windows, create a Windows-based services (optional):
$oradim –NEW –SID STAN –STARTMODE manual

8. Configure listeners for the primary and standby databases.

1) On Primary system: use Oracle Net Manager to configure a listener for PRIM and STAN. Then restart the listener.
$lsnrctl stop
$lsnrctl start

2) On Standby server: use Net Manager to configure a listener for PRIM and STAN. Then restart the listener.
$lsnrctl stop 
$lsnrctl start

9. Create Oracle Net service names.
1) On Primary system: use Oracle Net Manager to create network service names for PRIM and STAN. Check tnsping to both services:
$tnsping PRIM
$tnsping STAN

2) On Standby system: use Oracle Net Manager to create network service names for PRIM and STAN. Check tnsping to both services:
$tnsping PRIM
$tnsping STAN

11. On Standby server, setup the environment variables to point to the Standby database.

Set up ORACLE_HOME and ORACLE_SID.

************* Start Standby Database ***************

SQL>Startup nomount;

SQL>alter database mount standby database;

11. Start Redo apply

Check this query on Primary machine:

SQL> select status, error from v$archive_dest where dest_id = 2;

STATUS    ERROR

--------- ---------------------------------------------------------

VALID

1)     On the standby database, to start redo apply:

SQL>alter database recover managed standby database disconnect from session;

If you ever need to stop log apply services:

SQL> alter database recover managed standby database cancel;

12. Verify the standby database is performing properly:

1) On Standby perform a query:
SQL>select sequence#, first_time, next_time from v$archived_log;

2) On Primary, force a logfile switch:
SQL>alter system switch logfile;

3) On Standby, verify the archived redo log files were applied:
SQL>select sequence#, applied from v$archived_log order by sequence#;

SQL> SELECT FIRST_TIME, FIRST_CHANGE#, NEXT_CHANGE#, SEQUENCE# FROM V$LOG_

HISTORY;

SQL> SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG"

   FROM V$LOG_HISTORY

   GROUP BY THREAD#;

SQL> SELECT NAME, CREATOR, SEQUENCE#, APPLIED, COMPLETION_TIME

   FROM V$ARCHIVED_LOG;

SQL> SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;

13. If you want the redo data to be applied as it is received without waiting for the current standby redo log file to be archived, enable the real-time apply.

To start real-time apply:
SQL> alter database recover managed standby database using current logfile disconnect;

15. To create multiple standby databases, repeat this procedure.

SELECT protection_mode FROM v$database

SQL> select database_role from v$database;

DATABASE_ROLE

----------------

PHYSICAL STANDBY

                               Database Switchover

--------------------------------------------------------------------------------------------------------------------------

A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following statements.

-- Convert primary database to standby

CONNECT / AS SYSDBA

ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

-- Shutdown primary database

SHUTDOWN IMMEDIATE;

-- Mount old primary database as standby database

STARTUP NOMOUNT;

ALTER DATABASE MOUNT STANDBY DATABASE;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

On the original standby database issue the following commands.

-- Convert standby database to primary

CONNECT / AS SYSDBA

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

-- Shutdown standby database

SHUTDOWN IMMEDIATE;

-- Open old standby database as primary

STARTUP;

Once this is complete, test the log transport as before. If everything is working fine, switch the primary database back to the original server by doing another switchover. This is known as a switchback.

                                         Failover

-------------------------------------------------------------------------------------------------------------------------

If the primary database is not available the standby database can be activated as a primary database using the following statements.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

ALTER DATABASE ACTIVATE STANDBY DATABASE;