Data Guard implementation for Oracle 10gR2

This configuration has been successfully tested on Oracle 10g (Release 2). We will set up this configuration on the following machines.

---

PRIMARY SITE: (Information)

• Machine IP: 192.168.1.46
• Database name (db_name): ORCL
• Database Unique Name (db_unique_name): primary
• TNS Service Name: to_standby (Through this service, the primary machine will be connected to STANDBY machine)

STANDBY SITE: (Information)

• Machine IP: 192.168.1.96
• Database name (db_name): ORCL
• Database Unique Name (db_unique_name): standby
• TNS Service Name: to_primary (Through this service, the standby machine will be connected to PRIMARY machine)

CONFIGURATION ON PRIMARY

Create pfile from spfile on the primary database:

SQL> Create pfile=’C:\oracle\product\10.2.0\db_1\dbs\spfilePrimary.ora’ from spfile;

Then make/add following settings in the initPrimary.ora file on the PRIMARY Machine.

db_unique_name=’PRIMARY’
FAL_Client=’to_primary’
FAL_Server=’to_standby’
Log_archive_config=’DG_CONFIG=(primary,standby)’
Log_archive_dest_1=’
  Location=c:\oracle\backup 
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) 
  db_unique_name=primary’
Log_archive_dest_2=’
  Service=to_standby lgwr async 
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
  db_unique_name=standby’
Log_archive_dest_state_1=ENABLE
Log_archive_dest_state_2=DEFER
Service_names=’primary’
Standby_File_Management=’AUTO’

Create password file using ‘cmd’.

C:\> orapwd file=%ORACLE_HOME%\database\PWDOrcl.ora password=oracle entries=5 [force=y].

Force option is used to replace an existing password file. Now startup the PRIMARY database to MOUNT stage.

SQL> startup mount;

Make the following changes: Take the database to Archive Mode.

SQL> Alter database ArchiveLog;

• Enable Force Logging.

SQL> Alter database Force Logging;

On the PRIMARY site, also create standby redo logfile for the Standby database. This standby redo logfile will be used for Dataguard Observer later on. If you don’t want to use DG broker (observer), then there is no need to create standby redo logfile.

SQL>Alter database add standby logfile 
(‘C:\oracle\product\10.2.0\oradata\ORCL\StandbyRedo.log’) size 150m;

Now shutdown the primary database.

SQL> shutdown immediate;

Copy all the Datafiles and standby redo logfile from PRIMARY site to the same location on the STANDBY site. Then again startup the PRIMARY database to mount stage.

SQL> startup mount;

Now create a standby controlfile on the PRIMARY site.

SQL> Alter database create standby controlfile as ‘c:\oracle\backup\standcontrol.ctl’;

Now copy this new created standby control file to the standby site where other database file like datafiles, logfiles and control files are located. Rename this file to Control01.ctl, Control02.ctl and Control03.ctl.

Create spFile from pfile.

SQL> Create spfile from pfile;

Restart the primary database.

Now on PRIMARY site create a service in TNSnames.ora file through which the PRIMARY site will be connected to the Standby machine.

TO_STANDBY =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.96)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SERVICE_NAME = standby)
   )
 )

Also check the connectivity from the SQL Prompt.

SQL> connect sys/oracle@to_standby as sysdba
Connected.

Service can also be created through Net Manager utility available with Oracle Server. Connectivity can also be checked there. Register the Primary Database in the Listener.ora file. Then stop and start the listener in the ‘cmd’.

> Lsnrctl stop
> Lsnrctl start

Query the DATABASE_ROLE column from V$DATABASE to view the role of primary database. It should return ‘PRIMARY’.

CONFIGURATION ON STANDBY

Check the mode of Archiving by following command:

SQL> Archive Log List

Then create pfile from spfile on the standby database:

SQL> Create pfile from spfile=’C:\oracle\product\10.2.0\db_1\dbs\spfileStandby.ora’;

Then make/add following settings in the initStandby.ora file on the STANDBY Machine.

db_unique_name=’STANDBY’
FAL_Client=’to_standby’
FAL_Server=’to_primary’
Log_archive_config=’DG_CONFIG=(primary,standby)’
Log_archive_dest_1=’
  Location=c:\oracle\backup
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  db_unique_name=standby’
Log_archive_dest_2=’
  Service=to_primary
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  db_unique_name=primary’
Log_archive_dest_state_1=ENABLE
Log_archive_dest_state_2=ENABLE
Service_names=’STANDBY’
Standby_File_Management=’AUTO’
db_file_name_convert='/home/sanath/primary/','/home/sanath/standby/'
log_file_name_convert='/home/sanath/primary/','/home/sanath/standby/'
lock_name_space=standby

Create password file using ‘cmd’.

C:\> orapwd file=%ORACLE_HOME%\database\PWDOrcl.ora password=oracle entries=5 [force=y].
Force option is used to replace an existing password file.

Now on STANDBY site create a service in TNSnames.ora file through which the STANDBY site will be connected to the PRIMARY machine.

TO_PRIMARY =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.46)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SERVICE_NAME = primary)
   )
 )

Check the connectivity from the SQL Prompt.

SQL> connect sys/oracle@to_primary as sysdba
Connected.

Service can also be created through Net Manager utility available with Oracle Server. Connectivity can also be checked there. Register the Standby Database in the Listener.ora file. Then stop and start the listener in the ‘cmd’.

> Lsnrctl stop
> Lsnrctl start

Create spfile from pfile.

SQL> Create spfile from pfile;

Restart the database. Now startup the STANDBY database to mount stage.

SQL> startup mount

Enable Force Logging.

SQL> Alter database Force Logging;

Query the column DATABASE_ROLE from V$DATABASE to view the role of standby database. It should return ‘PHYSICAL STANDBY’.

LOG SHIPPING

On PRIMARY site enable Log_archive_dest_state_2 to start shipping archived redo logs.

SQL> Alter system set Log_archive_dest_state_2=ENABLE scope=both;
System Altered.

Check the sequence # and the archiving mode by executing following command.

SQL> Archive Log List

Then switch the logfile.

SQL> Alter system switch logfile;
System Altered.

Now on the PRIMARY site check the status of Standby Archiving destination.

SQL> Select Status, Error from v$Archive_dest where dest_id=2;

The STATUS should return – VALID. If it returns Error, then check the connectivity between the Primary and Standby machines.

START PHYSICAL LOG APPLY SERVICE.

On the STANDBY database execute the following command to start Managed Recovery Process (MRP). This command is executed on Mount stage.

SQL> Alter Database Recover Managed Standby Database;
Database Altered.

By executing the above command the current session will become hanged because MRP is a foreground recovery process. It waits for the logs to come and apply them. To avoid this hanging, you can execute the following command with DISCONNECT option.

SQL> Alter Database Recover Managed Standby Database Disconnect;
 Database Altered.

Now the session will be available to you and MRP will work as a background process and apply the redo logs.

You can check whether the log is applied or not by querying V$ARCHIVED_LOG.

SQL> Select Name, Applied, Archived from v$Archived_log;

This query will return the name of archived files and their status of being archived and applied.

ROLE TRANSITION :

In Dataguard configuration, two roles are defined; Primary and Standby. Primary database is the production database which is used by the users. For high availability purpose, the dataguard provides a standby database which remains available side by side to the primary database. Standby databases can be more than one and can be at remote locations as well.

Oracle Data Guard supports two role transition operations:

Switchover:

Switchover allows the primary database to switch roles with one of its standby databases. There is no data loss during a switchover. After a switchover, each database continues to participate in the Data Guard configuration with its new role.

Failover:

Failover transitions a standby database to the primary role in response to a primary database failure. If the primary database was not operating in either maximum protection mode or maximum availability mode before the failure, some data loss may occur. After a failover, the failed database no longer participates in the Data Guard configuration. It needs to be reinstated to become an active part of Data Guard configuration.

Manual Switchover:

On the PRIMARY Database: (Open stage)

Query V$DATABASE to check the role of Primary Database.

SQL> Select Database_role
from v$Database;

It will return “PRIMARY”,

Now check the Switchover Status of the Primary Database.

SQL> Select switchover_status from v$Database;

It will return “SESSIONS ACTIVE”.

Now you are ready to perform a manual switchover. Execute the following command using “WITH SESSION SHUTDOWN” option.

SQL> Alter Database Commit to Switchover to Physical Standby with session Shutdown;
Database Altered.

Now your PRIMARY Database has become Physical Standby. Before To verify this change, You must Shutdown the database and again Start it to mount stage. Again query the Database_role column of V$DATABASE. Now it will return “PHYSICAL STANDBY”.

On the PHYSICAL STANDBY Database: (Mount stage) Query V$DATABASE to check the role of Standby Database.

SQL> Select Database_role from v$Database;

It will return “PHYSICAL STANDBY”,

Now check the Switchover Status of the Standby Database.

SQL> Select switchover_status from v$Database;

It will return “SESSIONS ACTIVE”.

Now cancel the MRP which is running in the background of the Standby Database. Execute the following command:

SQL> Alter database Recover Managed Standby Database Cancel;
Database Altered.

Now you are ready to perform a manual switchover from Physical Standby to Primary. Execute the following command using “WITH SESSION SHUTDOWN” option.

SQL> Alter Database Commit to Switchover to PRIMARY with session Shutdown;
Database Altered.

Now your PHYSICAL STANDBY Database has become PRIMARY. To verify this change, again query the Database_role column of V$DATABASE. Now it will return “PRIMARY”.

Shutdown the database and again Start it to Open stage.

DATAGUARD BROKER :

The Data Guard broker is a distributed management framework that automates and centralizes the creation, maintenance, and monitoring of Data Guard configurations. You can use either the Oracle Enterprise Manager graphical user interface (GUI) or command-line interface (CLI) to automate and simplify:

• Creating and enabling Data Guard configurations, including setting up log transport services and log apply services.

• Managing an entire Data Guard configuration from any system in the configuration.

• Managing and monitoring Data Guard configurations that contain Real Application Clusters primary or standby databases.

BROKER CONFIGURATION:

On both Primary and Standby sites, change the initialization parameter in the spfile to enable the Data guard broker.

•SQL> Alter system set dg_broker_start=True scope=both;
System Altered.

On the PRIMARY site, open the ‘cmd’ and start Command Line Interface (CLI) of the Dataguard Broker (DGMGRL).

C:\> dgmgrl
DGMGRL for 32-bit Windows: Version 10.2.0.1.0 - Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.

DGMGRL>_

Now connect to the database through the service you made previously.

DGMGRL> connect sys/oracle@to_primary
Connected.

Create broker configuration.

DGMGRL> create configuration ‘broker1’ as
> primary database is ‘primary’
> connect identifier is to_primary;

(‘to_primary’ in Connect identifier is the service name through which the broker is connected to the PRIMARY database)

Add Standby Database to the above configuration.

DGMGRL> Add database ‘standby’ as
> connect identifier is to_standby 
> maintained as physical;

(‘to_standby’ in Connect identifier is the service name through which the broker is connected to the STANDBY database)

Now the configuration has been set up but it is still disabled. You can view the configuration by executing:

DGMGRL> show configuration
Configuration
 Name:                	broker1
 Enabled:             	NO
 Protection Mode:     	MaxPerformance
 Fast-Start Failover: 	DISABLE
 Databases:
   primary - Physical standby database
   standby - Primary database

Current status for "broker1":
DISABLE

The next step is to ENABLE the configuration ‘broker1’.

DGMGRL> enable configuration;
Enabled

Again view the configuration.

DGMGRL> show configuration

Configuration
 Name:                	broker1
 Enabled:             	YES
 Protection Mode:     	MaxPerformance
 Fast-Start Failover: 	DISABLE
 Databases:
   primary - Physical standby database
   standby - Primary database

Current status for "broker1":
SUCCESS

Switchover:

Now we are ready to switch over the PRIMARY database Role to STANDBY database Role.

DGMGRL> switchover to ‘Standby’;
…..
…..

Primary Database Successfully converted to Physical Standby. You can again switch over the Standby Database to Primary by executing following command.

DGMGRL> switchover to ‘Primary’;
…..
…..

Standby Database is successfully converted to Primary Database.

Failover:

Failover can be done through the same configuration without any alteration. You simply need to execute following command:

DGMGRL> failover to ‘Standby’;
….
Failover to standby succeeded.

And also…

DGMGRL> failover to ‘Primary’;
….
Failover to primary succeeded.

DATAGUARD OBSERVER :

Observer is a utility that is available with the dataguard. Its basic purpose is to keep the database available to the clients all the time. It is started on a separate location other than Primary and Standby locations. After starting the observer, it starts watching continuously both the sites. Whenever the PRIMARY database fails due to any reason and the connection between the PRIMARY site and the observer breaks, the observer waits for a certain time which is set up during setting the broker configuration. When this time passes, the observer automatically starts the failover process. As a result, the Physical Standby database is converted to PRIMARY database. After this failover, the users again connect to the database. This connection redirects them automatically to the new PRIMARY database.

OBSERVER CONFIGURATION:

There are some important configuration settings that are needed to be set before starting the Dataguard (DG) Observer. Main thing is to set the STANDBY database to Maximum Availability protection mode. DG broker configuration must also be set to “MaxAvailability” protection mode. After this we need to enable “fast_start failover” parameter of the broker configuration. Without setting these options, broker will not allow to proceed for observer to work for remote failover. Following are the steps to enable the observer.

OBSERVER SITE: (Information)

OBSERVER Machine IP: 192.168.1.65 PRIMARY Machine IP: 192.168.1.46 STANDBY Machine IP: 192.168.1.96

TNS Service Name for PRIMARY site: to_primary (Through this service, the observer machine will be connected to PRIMARY machine)

TNS Service Name for STANDBY site: to_standby (Through this service, the observer machine will be connected to STANDBY machine)

(NOTE: Fresh DG broker configuration will be created for implementation of Observer. Therefore, remove any previously created configuration.)

Step 1:

Create TNS services on the observer machine for PRIMARY site and STANDBY site through which the observer machine will be connected to the primary and standby databases.

TNS service name for PRIMARY site is ‘to_primary’. TNS service name for STANDBY site is ‘to_standby’.

Step 2:

Before starting the broker CLI (DGMGRL) we have to enable FLASHBACK on both the Primary and Standby databases.

At mount stage execute:

SQL> Alter database Flashback on;
Database altered.

Step 3:

On the STANDBY site, edit Log_Archive_Dest_2 parameter and add three properties i.e. LGWR AFFIRM SYNC. These properties are needed to bring the broker protection mode to MaxAvailability.

SQL> Alter System set Log_archive_dest_2=’Service=to_primary
LGWR AFFIRM SYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
db_unique_name=primary’;
System altered.

Step 4:

Now execute the following command on the primary database to change the protection mode from MAXIMUM PERFORMANCE to MAXIMUM AVAILABILITY.

SQL> Alter database set primary database to maximize availability;

Shut down the PRIMARY database and then restart it. Check the protection mode on both PRIMARY and STANDBY databases.

SQL> select protection_mode from v$database.

It should return MAXIMUM AVAILABILITY.

Step 5:

Now start the DG CLI (DGMGRL) to start the broker.

C:\> dgmgrl
DGMGRL for 32-bit Windows: Version 10.2.0.1.0 - Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information. DGMGRL>

DGMGRL> connect sys/oracle@primary
Connected.

Check any previously created configuration and remove it.

DGMGRL> show configuration;

If a configuration is displayed then remove it.

DGMGRL> remove configuration;
Removed.

Now create a new configuration.

DGMGRL> create configuration ‘broker1’ as
> primary database is ‘primary’
> connect identifier is to_primary;
Configuration “broker1” created with primary database “primary”

DGMGRL> add database ‘standby’ as
> connect identifier is to_standby 
> maintained as physical;
Database “broker1” added

DGMGRL> show configuration;
Configuration
 Name:                	broker1
 Enabled:             	NO
 Protection Mode:     	MaxPerformance
 Fast-Start Failover: 	DISABLE
 Databases:
   primary - Physical standby database
   standby - Primary database
Current status for "broker1":
DISABLE

DGMGRL> enable configuration;
Enabled.

DGMGRL> show configuration;
Configuration
 Name:                	broker1
 Enabled:             	YES
 Protection Mode:     	MaxPerformance
 Fast-Start Failover: 	DISABLE
 Databases:
   primary - Physical standby database
   standby - Primary database

Current status for "broker1":
SUCCESS

DGMGRL> edit database PRIMARY set property LogXptMode=’SYNC’;
Property "logxptmode" updated

DGMGRL> edit database STANDBY set property LogXptMode=’SYNC’;
Property "logxptmode" updated

DGMGRL> edit configuration set protection mode as MaxAvailability;
Succeeded.

DGMGRL> enable fast_start failover;
Enabled.

DGMGRL> show configuration;

Configuration
 Name:                	dg1
 Enabled:             	YES
 Protection Mode:     	MaxAvailability
 Fast-Start Failover: 	ENABLED
 Databases:
   primary - Physical standby database
                - Fast-Start Failover target
   standby - Primary database

Current status for "dg1": Warning: ORA-16608: one or more databases have warnings

If you check this error by querying StatusReport property of the PRIMARY database, you will find that Fast Start Failover OBSERVER is not started. Simply start the observer.

DGMGRL> show database primary StatusReport

Now start the observer.

DGMGRL> start observer
Observer Started.

The Observer has started its working. The current session of ‘cmd’ will not be returned to you because it is a foreground process. It will continuously observe PRIMARY site. To check the configuration, open another ‘cmd’ window and check the configuration.

DGMGRL> show configuration;

Configuration

Name: broker1 Enabled: YES Protection Mode: MaxAvailability Fast-Start Failover: Enabled Databases: standby - Primary database primary - Physical standby database - Fast – Start Failover target

Fast-Start Failover 

Threshold: 30 seconds Observer: orat

Current status for “broker1”:
SUCCESS

___________________________________________

At this stage every thing is going on normally. An identical copy of PRIMARY database is being made at the STANBY site. If due to any reason, PRIMARY database crashes, the observer will start its working.

DGMGRL> start observer
Observer started.

15:21:21.69 Wednesday, March 07, 2007
Initiating fast-start failover to database “standby”…
Performing failover NOW, please wait…
Operation requires shutdown of instance “orcl” on database “standby”
Shutting down instance “orcl”…
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

Operation requires startup of instance “orcl” on database “standby” Starting instance “orcl”…

Data Guard With Real Time

Redo data is applied to the standby database as soon as it is received from the primary database.
In Oracle9i, this apply has to wait till an archive log is created on the standby database For Redo Apply:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE

For SQL Apply:

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE

When real time apply is enabled, RECOVERY_MODE column in V$ARCHIVE_DEST_STATUS displays “MANAGED REAL TIME APPLY”

Real Time Apply – Benefits

• Standby databases now more closely synchronized with the primary
• More up-to-date, real-time reporting
• Faster switchover and failover times
• Reduces planned and unplanned downtime
• Better Recovery Time Objective (RTO) for DR

Note

For Real Time We must create standby log files .Redo logs can be created even after the standby has been created

Create the SRL's :

   SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
       (‘C:\oracle\product\10.2.0\oradata\ORCL\StandbyRedo4.log’)  SIZE 100M;
   
   SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5
       (‘C:\oracle\product\10.2.0\oradata\ORCL\StandbyRedo5.log’)  SIZE 100M;
   
   SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6
       (‘C:\oracle\product\10.2.0\oradata\ORCL\StandbyRedo6.log’)  SIZE 100M; 

Converting To Real time

Real-time apply : When real-time apply is enabled, the log apply services can apply redo data as it is received, without waiting for the current standby redo log file to be archived. In this example we are going to use LGWR on the primary for redo transport just to prove that a committed record on the primary without switching a log will show up on the standby. However real-time apply will work with both LGWR and ARCH using SRL's.

• Set up log_archive_dest_2 on the primary with LGWR ASYNC or SYNC

log_archive_dest_2='SERVICE=to_standby LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=standby '
                     

• shutdown and startup the primary or if done dynamically switch a log file
• You will see the following message in the alert log

    *LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2

Enable Real Time

• On the standby cancel out of the current managed recovery

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
    

• Place it back in recovery with Real time apply

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Test Case

Create a table on Primary.

SQL>  create table test
 2   ( name varchar2(30));

Table created.

Now Insert some records and commit.

SQL>  insert into test
 2  values
 3  ('Michel');
 1 row created.

SQL> commit;
Commit complete.

Now on Standby Run these commands.

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL>ALTER DATABASE OPEN READ ONLY;
Database altered.

Now check the table and record which you inserted.

SQL> select * from test;

NAME
------------------------------
Michel

We can see our table and record without any log switch.

After Checking your Real Time work Don't Forget to put your database back in recover mode. Place the standby back in managed recover mode:

  SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Database altered.

• This will take the standby directly from read only mode and place it in managed recovery mode.