Data Guard Broker Setup for MAA Architecture

The Environment
===============

• 2 Linux servers, Oracle Distribution 2.6.9-55 EL i686 i386 GNU/Linux, the Primary and Standby databases are located on these servers.
• 1 Linux server, RH Linux 2.6.9-42.ELsmp x86_64 GNU/Linux, The Data Guard Broker Observer is located on this server.
• Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
• ssh is configured for user oracle on both nodes
• Oracle Home is on identical path on both nodes
• Primary database primary
• Standby database standby

Once you have your primary and standby databases up and running these are the steps to follow:

1.Enable Data Guard Broker Start on the Primary and Standby databases.

2.Setup the Local_Listener parameter if the listener port is not the standard 1521, on both the Primary and Standby databases.

3.Setup the listener and tnsnames to enable communication with both the Primary and Standby databases.

4.Setup the Broker configuration files

5.Check properties

6.Enable the configuration and databases

7.Enable Fast Start Failover and the Observer

Step by Step Implementation of Data Guard Broker

SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

System altered.

( This is not required if Listener is running on 1521 port )

SQL> ALTER SYSTEM SET LOCAL_LISTENER=’LISTENER’ SCOPE=BOTH;

System altered.

On Primary Database:

====================

listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1/)

(PROGRAM = extproc)

)

(SID_DESC =

(SID_NAME = primary)

(GLOBAL_DBNAME = primary_DGMGRL)

(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1/)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

)

On Standby Database:

====================

listner.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1/)

(PROGRAM = extproc)

)

(SID_DESC =

(SID_NAME = standby)

(GLOBAL_DBNAME = standby_DGMGRL)

(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1/)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = quartz)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

)

TNSNAMES.ORA on All three hosts ( Primary , standby & Observer )

================================================================

PRIMARY =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.235.149)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = primary)

)

)

STANDBY =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.235.58)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = standby)

)

)

The broker configuration files are automatically created when the broker is started using ALTER SYSTEM SET DG_BROKER_START=TRUE. The default destination can be modified using the parameters DG_BROKER_CONFIG_FILE1 and DG_BROKER_CONFIG_FILE2

On Primary:

SQL> show parameters DG_BROKER_CONFIG;

NAME TYPE VALUE

———————————– ———– ——————————

dg_broker_config_file1 string /u01/app/oracle/product/10.2.0/db_1/dbs/dr1primary.dat

dg_broker_config_file2 string /u01/app/oracle/product/10.2.0/db_1/dbs/dr2primary.dat

On Standby:

SQL> show parameters DG_BROKER_CONFIG;

NAME TYPE VALUE

———————————– ———– ——————————

dg_broker_config_file1 string /u01/app/oracle/product/10.2.0/db_1/dbs/dr1primary.dat

dg_broker_config_file2 string /u01/app/oracle/product/10.2.0/db_1/dbs/dr2primary.dat

Next create DGMGRL the configuration

$dgmgrl

DGMGRL for Linux: Version 10.2.0.1.0 – Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type “help” for information.

DGMGRL> CONNECT sys/oracle@primary

Connected.

DGMGRL> CREATE CONFIGURATION dg AS

> PRIMARY DATABASE IS primary

> CONNECT IDENTIFIER IS primary;

Configuration “primary” created with primary database “primary”

Add the standby to the configuration and check it

DGMGRL> ADD DATABASE standby AS

> CONNECT IDENTIFIER IS standby

> MAINTAINED AS PHYSICAL;

Database “standby” added

DGMGRL> show configuration;

Configuration

Name: dg

Enabled: NO

Protection Mode: MaxPerformance

Fast-Start Failover: DISABLED

Databases:

primary – Primary database

standby – Physical standby database

Current status for “dg”:

SUCCESS

DGMGRL> show database verbose ‘primary’;

Database

Name: primary

Role: PRIMARY

Enabled: YES

Intended State: OFFLINE

Instance(s):

primary

Properties:

InitialConnectIdentifier = ‘primary’

ObserverConnectIdentifier = ”

LogXptMode = ‘ASYNC’

Dependency = ”

DelayMins = ‘0′

Binding = ‘OPTIONAL’

MaxFailure = ‘0′

MaxConnections = ‘1′

ReopenSecs = ‘300′

NetTimeout = ‘180′

LogShipping = ‘ON’

PreferredApplyInstance = ”

ApplyInstanceTimeout = ‘0′

ApplyParallel = ‘AUTO’

StandbyFileManagement = ‘auto’

ArchiveLagTarget = ‘0′

LogArchiveMaxProcesses = ‘2′

LogArchiveMinSucceedDest = ‘1′

DbFileNameConvert = ‘/u01/database/standby, /u01/database/primary’

LogFileNameConvert = ‘/u01/database/standby, /u01/database/primary’

FastStartFailoverTarget = ”

StatusReport = ‘(monitor)’

InconsistentProperties = ‘(monitor)’

InconsistentLogXptProps = ‘(monitor)’

SendQEntries = ‘(monitor)’

LogXptStatus = ‘(monitor)’

RecvQEntries = ‘(monitor)’

HostName = ‘primary’

SidName = ‘primary’

LocalListenerAddress = ‘(ADDRESS=(PROTOCOL=tcp)(HOST=primary)(POR

T=1521))’

StandbyArchiveLocation = ‘?/dbs/arch’

AlternateLocation = ”

LogArchiveTrace = ‘0′

LogArchiveFormat = ‘%t_%s_%r.dbf’

LatestLog = ‘(monitor)’

TopWaitEvents = ‘(monitor)’

Current status for “primary”:

SUCCESS

DGMGRL> enable configuration;

Enabled.

DGMGRL> show configuration;

Configuration

Name: dg

Enabled: YES

Protection Mode: MaxAvailability

Fast-Start Failover: DISABLED

Databases:

primary – Primary database

standby – Physical standby database

Current status for “dg”:

SUCCESS

DGMGRL> enable data
base primary;

Enabled.

These are the steps required to enable and check Fast Start Failover and the Observer:

1.Ensure standby redo logs are configured on all databases.

2.Ensure the LogXptMode Property is set to SYNC.

3.Specify the FastStartFailoverTarget property.

4.Upgrade the protection mode to MAXAVAILABILITY, if necessary.

5.Enable Flashback Database on the primary and standby databases, if necessary.

6.Enable fast start failover.

7.Start the observer.

8.Performing a Switchover Operation

SQL> set lines 120

SQL> col member for a80

SQL> select type,member from v$logfile order by type;

TYPE MEMBER

——- ——————————————————————————–

ONLINE /u01/database/primary/redo02.log

ONLINE /u01/database/primary/redo03.log

ONLINE /u01/database/primary/redo01.log

STANDBY /u01/database/primary/stdby_redo04.rdo

STANDBY /u01/database/primary/stdby_redo05.rdo

STANDBY /u01/database/primary/stdby_redo01.log

These commands will succeed only if database is configured with standby redo logs.

DGMGRL> connect sys/oracle@primary

Connected.

DGMGRL> EDIT DATABASE primary SET PROPERTY ‘LogXptMode’='SYNC’;

Property “LogXptMode” updated

DGMGRL> EDIT DATABASE standby SET PROPERTY ‘LogXptMode’='SYNC’

Property “LogXptMode” updated.

DGMGRL> EDIT DATABASE primary SET PROPERTY FastStartFailoverTarget=’standby’;

Property “faststartfailovertarget” updated.

DGMGRL> EDIT DATABASE standby SET PROPERTY FastStartFailoverTarget=’primary’;

Property “faststartfailovertarget” updated.

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 92276304 bytes

Database Buffers 188743680 bytes

Redo Buffers 2973696 bytes

Database mounted.

On Both databases

To enter the standby into Flashback mode you must shutdown the both databases, then while the primary is down execute the following commands on the standby:

SQL> ALTER SYSTEM SET UNDO_RETENTION=3600 SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET UNDO_MANAGEMENT=’AUTO’ SCOPE=SPFILE;

System altered.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 92276304 bytes

Database Buffers 188743680 bytes

Redo Buffers 2973696 bytes

Database mounted.

SQL> ALTER DATABASE FLASHBACK ON;

Database altered.

DGMGRL> ENABLE FAST_START FAILOVER;

Enabled.

Start the observer from a third server on background. You may use a script like this:

—————- script start on next line ——————–

#!/bin/ksh

# startobserver

export ORACLE_BASE=/u01/app/oracle/

export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1/

export PATH=$ORACLE_HOME/bin:$PATH

dgmgrl << eof

connect sys/oracle@primary

START OBSERVER;

eof

—————- script end on previous line ——————–

$nohup ./startobserver &

Perform a Switchover

Connect to DGMGRL on the observer server:

Check that primary and standby are healthy

This check must return ‘SUCCESS’ as the status for both databases, otherwise it means there is a configuration problem.

DGMGRL> show database ‘primary’

Database

Name: primary

Role: PRIMARY

Enabled: YES

Intended State: ONLINE

Instance(s):

primary

Current status for “primary”:

SUCCESS

DGMGRL> show database ’standb

Database

Name: standby

Role: PHYSICAL STANDBY

Enabled: YES

Intended State: ONLINE

Instance(s):

standby

Current status for “standby”:

SUCCESS

DGMGRL> SWITCHOVER TO standby;

Performing switchover NOW, please wait…

Operation requires shutdown of instance “primary” on database “primary”

Shutting down instance “primary”…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires shutdown of instance “standby” on database “standby”

Shutting down instance “standby”…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance “primary” on database “primary”

Starting instance “primary”…

ORACLE instance started.

Database mounted.

Operation requires startup of instance “standby” on database “standby”

Starting instance “standby”…

ORA-01081: cannot start already-running ORACLE – shut it down first

Unable to start instance “standby”

You must start instance “standby” manually

Switchover succeeded, new primary is “standby”

There are currently no comments highlighted.