In sweet memories of my ever loving brother "kutty thambi " ARUN KUMAR

Wednesday, January 27, 2010

Configuration of 10g Data Guard Broker and Observer for Switchover

 Configuring Data Guard Broker for Switchover, General Review.

 On a previous document, 10g Data Guard, Physical Standby Creation, step by step I did describe how to implement a Data Guard
configuration; on this document I'm adding how to configure the broker and observer, setup the database to Maximum Availability and
managing switchover from Data Guard Manager, DGMGRL.
Data Guard Broker permit to manage a Data Guard Configuration, from both the Enterprise Manager Grid Control console, or from a
terminal in command line mode. In this document I will explore command line mode.
Pre requisites include the use of 10g Oracle server, using spfile on both the primary and standby and a third server for the Observer,
and configure the listeners to include a service for the Data Guard Broker.

The Enviroment
     • 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 ANTONY  
     • Standby database JOHN

 Step by Step Implementation of Data Guard Broker
Enable Data Guard Broker Start on the Primary and Standby databases
    SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;
    System altered.
Setup the Local_Listener parameter on both the Primary and Standby databases
    SQL> ALTER SYSTEM SET LOCAL_LISTENER='LISTENER_VMRACTEST' SCOPE=BOTH;
    System altered.
Setup the tnsnames to enable communication with both the Primary and Standby databases
The listener.ora should include a service named global_db_nameDGMGRL to enable the broker to start the databases on the event of
switchover. This configuration needs to be included on both servers.
Listener.ora on Node 1
    LISTENER_VMRACTEST =
     (DESCRIPTION_LIST =
       (DESCRIPTION =
         (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.localdomain)(PORT = 1521)(IP = FIRST))
       )
     )
    SID_LIST_LISTENER_VMRACTEST =
     (SID_LIST =
       (SID_DESC =
         (GLOBAL_DBNAME = antony)
         (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1  )
         (SID_NAME = antony)
       )
       (SID_DESC =
         (SID_NAME= antony)
         (GLOBAL_DBNAME = antony_DGMGRL)
         (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1 )
       )
     )
Listener.ora on Node 2
   LISTENER_VMRACTEST =
     (DESCRIPTION_LIST =
       (DESCRIPTION =
         (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.localdomain)(PORT = 1521)(IP = FIRST))
       )
     )
    SID_LIST_LISTENER_VMRACTEST =
     (SID_LIST =
       (SID_DESC =
         (GLOBAL_DBNAME = john)
         (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1  )
         (SID_NAME = john)
       )
       (SID_DESC =
         (SID_NAME= john)
         (GLOBAL_DBNAME = john_DGMGRL)
         (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1 )
       )
     )
Tnsnames.ora on Node 1, 2 and the observer node
   ANTONY =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.localdomain)(PORT = 1521))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = antony_DGMGRL)
      )
    )
   JOHN =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST =  rac2.localdomain)(PORT = 1521))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = john_DGMGRL)
      )
 )
Setup the Broker configuration files
   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/dr1antony.dat
dg_broker_config_file2               string      /u01/app/oracle/product/10.2.0
                                                 /db_1/dbs/dr2antony.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/dr1john.dat
dg_broker_config_file2               string      /u01/app/oracle/product/10.2.0
                                                 /db_1/dbs/dr2john.dat

Next create from within the DGMGRL the configuration
[oracle@rac1 ~]$ 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@antony
Connected.
DGMGRL> create configuration ANTONY AS
> PRIMARY DATABASE IS antony
> CONNECT IDENTIFIER IS antony;

Configuration "antony" created with primary database "antony"


Add the standby to the configuration and check it

DGMGRL> ADD DATABASE john AS
> CONNECT IDENTIFIER IS john
> MAINTAINED AS PHYSICAL;
Database "john" added

DGMGRL> SHOW CONFIGURATION;

Configuration
  Name:                antony
  Enabled:             NO
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    antony - Primary database
    john   - Physical standby database

Current status for "antony":
DISABLED

DGMGRL> SHOW DATABASE VERBOSE john;

Database
  Name:            john
  Role:            PHYSICAL STANDBY
  Enabled:         NO
  Intended State:  OFFLINE
  Instance(s):
    john

  Properties:
    InitialConnectIdentifier        = 'john'
    LogXptMode                      = 'ARCH'
    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          = '30'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '/u01/app/oracle/oradata/antony/, /u01/app /oracle/oradata/john/'
    LogFileNameConvert              = '/u01/app/oracle/oradata/antony/, /u01/app /oracle/oradata/john/'
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'rac2'
    SidName                         = 'john'
    LocalListenerAddress            = '(ADDRESS=(PROTOCOL=TCP)(HOST=rac2.localdo main)(PORT=1521))'
    StandbyArchiveLocation          = '/u01/app/oracle/oradata/john/arch/'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.arc'
    LatestLog                       = '(monitor)'
    TopWaitEvents                   = '(monitor)'

Current status for "john":
DISABLED

DGMGRL> show database verbose antony;

Database
  Name:            antony
  Role:            PRIMARY
  Enabled:         NO
  Intended State:  OFFLINE
  Instance(s):
    antony

  Properties:
    InitialConnectIdentifier        = 'antony'
    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          = '30'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '/u01/app/oracle/oradata/john/, /u01/app/o racle/oradata/antony/'
    LogFileNameConvert              = '/u01/app/oracle/oradata/john/, /u01/app/o racle/oradata/antony/'
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'rac1'
    SidName                         = 'antony'
    LocalListenerAddress            = '(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.localdo main)(PORT=1521))'
    StandbyArchiveLocation          = '/u01/app/oracle/oradata/antony/arch/'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.arc'
    LatestLog                       = '(monitor)'
    TopWaitEvents                   = '(monitor)'

Current status for "antony":
DISABLED

DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;

Configuration
  Name:                antony
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    antony - Primary database
    john   - Physical standby database

Current status for "antony":
SUCCESS

DGMGRL> enable database john;
Enabled.
DGMGRL> SHOW DATABASE VERBOSE john;

Database
  Name:            john
  Role:            PHYSICAL STANDBY
  Enabled:         YES
  Intended State:  ONLINE
  Instance(s):
    john

  Properties:
    InitialConnectIdentifier        = 'john'
    LogXptMode                      = 'ARCH'
    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          = '30'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '/u01/app/oracle/oradata/antony/, /u01/app/oracle/oradata/john/'
    LogFileNameConvert              = '/u01/app/oracle/oradata/antony/, /u01/app/oracle/oradata/john/'
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'rac2'
    SidName                         = 'john'
    LocalListenerAddress            = '(ADDRESS=(PROTOCOL=TCP)(HOST=rac2.localdomain)(PORT=1521))'
    StandbyArchiveLocation          = '/u01/app/oracle/oradata/john/arch/'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.arc'
    LatestLog                       = '(monitor)'
    TopWaitEvents                   = '(monitor)'

Current status for "john":
SUCCESS

Enabling the configuration and databases
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;

Configuration
  Name:                antony
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    antony - Primary database
    john   - Physical standby database

Current status for "antony":
SUCCESS

DGMGRL> enable database john;
Enabled.
DGMGRL> SHOW DATABASE VERBOSE john;

Database
  Name:            john
  Role:            PHYSICAL STANDBY
  Enabled:         YES
  Intended State:  ONLINE
  Instance(s):
    john

  Properties:
    InitialConnectIdentifier        = 'john'
    LogXptMode                      = 'ARCH'
    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          = '30'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '/u01/app/oracle/oradata/antony/, /u01/app/oracle/oradata/john/'
    LogFileNameConvert              = '/u01/app/oracle/oradata/antony/, /u01/app/oracle/oradata/john/'
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'rac2'
    SidName                         = 'john'
    LocalListenerAddress            = '(ADDRESS=(PROTOCOL=TCP)(HOST=rac2.localdomain)(PORT=1521))'
    StandbyArchiveLocation          = '/u01/app/oracle/oradata/john/arch/'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.arc'
    LatestLog                       = '(monitor)'
    TopWaitEvents                   = '(monitor)'

Current status for "john":
SUCCESS

Enabling Fast Start Failover and the Observer

These are the steps required to enable and check Fast Start Failover and the Observer:
1. Ensure standby redologs are configured on all databases.
on primary:
SQL> SELECT TYPE,MEMBER FROM V$LOGFILE;

TYPE    MEMBER
------- --------------------------------------------------
ONLINE  /u01/app/oracle/oradata/antony/redo03.log
ONLINE  /u01/app/oracle/oradata/antony/redo02.log
ONLINE  /u01/app/oracle/oradata/antony/redo01.log
STANDBY /u01/app/oracle/oradata/antony/redoby04.log
STANDBY /u01/app/oracle/oradata/antony/redoby05.log
STANDBY /u01/app/oracle/oradata/antony/redoby06.log

On standby:

SQL> SELECT TYPE,MEMBER FROM V$LOGFILE;

TYPE       MEMBER
---------- --------------------------------------------------
ONLINE     /u01/app/oracle/oradata/john/redo03.log
ONLINE     /u01/app/oracle/oradata/john/redo02.log
ONLINE     /u01/app/oracle/oradata/john/redo01.log
STANDBY    /u01/app/oracle/oradata/john/redoby04.log
STANDBY    /u01/app/oracle/oradata/john/redoby05.log
STANDBY    /u01/app/oracle/oradata/john/redoby06.log

2. Ensure the LogXptMode Property is set to SYNC.
Note: These commands will succeed only if database is configured with standby redo logs.
DGMGRL> EDIT DATABASE antony SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated
DGMGRL> EDIT DATABASE john SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated

3.Specify the FastStartFailoverTarget property

DGMGRL> EDIT DATABASE antony SET PROPERTY FastStartFailoverTarget='john';
Property "faststartfailovertarget" updated
DGMGRL> EDIT DATABASE john SET PROPERTY FastStartFailoverTarget='antony';
Property "faststartfailovertarget" updated

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

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Operation requires shutdown of instance "antony" on database "antony"
Shutting down instance "antony"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "antony" on database "antony"
Starting instance "antony"...
ORACLE instance started.
Database mounted.

note: if ORA-12514: TNS:listener does not currently know of service requested in connect descriptor Failed.
You are no longer connected to ORACLE
Please connect again.
you must start instance (primary database) manually
SQL> conn / as sysdba
SQL> startup mount;

5. Enable Flashback Database on the Primary and Standby Databases.
 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;

SQL> ALTER DATABASE FLASHBACK ON;

Enable fast start failover

[oracle@rac1 ~]$ 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@antony;
Connected.
DGMGRL> show configuration verbose;

Configuration
  Name:                antony
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Fast-Start Failover: DISABLED
  Databases:
    antony - Primary database
    john   - Physical standby database

Current status for "antony":
SUCCESS

DGMGRL> show database john;

Database
  Name:            john
  Role:            PHYSICAL STANDBY
  Enabled:         YES
  Intended State:  ONLINE
  Instance(s):
    john

Current status for "john":
SUCCESS

DGMGRL> ENABLE FAST_START FAILOVER;
Enabled.
start the observer
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 BASE_PATH=/u01/app/oracle/oracle/scripts/general:/opt/CTEact/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/bin:/etc:/usr/local/maint/oracle:/usr/ccs/bin:/usr/openwin/bin:/usr/dt/bin:/usr/local/bin:.
export PATH=$ORACLE_HOME/bin:$BASE_PATH
dgmgrl << eof
connect sys/oracle@antony
START OBSERVER;
eof
---------------- script end on previous line --------------------
[oracle@rac3 ~]$ nohup ./startobserver &
nohup: appending output to `nohup.out'
[1] 27392
Verify the fast-start failover configuration.
[oracle@rac3 ~]$ 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@antony
Connected.
DGMGRL> show configuration verbose

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

Fast-Start Failover
  Threshold: 30 seconds
  Observer:  rac1

Current status for "antony":
SUCCESS

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 antony

Database
  Name:            antony
  Role:            PRIMARY
  Enabled:         YES
  Intended State:  ONLINE
  Instance(s):
    antony

Current status for "antony":
SUCCESS

DGMGRL> show database john

Database
  Name:            john
  Role:            PHYSICAL STANDBY
  Enabled:         YES
  Intended State:  ONLINE
  Instance(s):
    john

Current status for "john":
SUCCESS

DGMGRL>

EXECUTE THE SWITCHOVER:

 DGMGRL> SWITCHOVER TO john;
Performing switchover NOW, please wait...
Operation requires shutdown of instance "antony" on database "antony"
Shutting down instance "antony"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "john" on database "john"
Shutting down instance "john"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "antony" on database "antony"
Starting instance "antony"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "john" on database "john"
Starting instance "john"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "john"
DGMGRL>


DGMGRL> show configuration verbose

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

Fast-Start Failover
  Threshold: 30 seconds
  Observer:  rac1

Current status for "antony":
SUCCESS

DGMGRL> show database john

Database
  Name:            john
  Role:            PRIMARY
  Enabled:         YES
  Intended State:  ONLINE
  Instance(s):
    john

Current status for "john":
SUCCESS

DGMGRL> show database antony

Database
  Name:            antony
  Role:            PHYSICAL STANDBY
  Enabled:         YES
  Intended State:  ONLINE
  Instance(s):
    antony

Current status for "antony":
SUCCESS

Reference:
Oracle® Data Guard Concepts and Administration
10g Release 2 (10.2)
Part Number B14239-04
http://download.oracle.com/docs/cd/B19306_01/server.102/b14230/cli.htm#BGBFEAFG
Posted by Rajeshkumar Govindarajan at Wednesday, January 27, 2010
Labels: ,

3 comments:

Firdous Ahmed Bhat said...

Hello,

I like your document , so i was configuring it according to your way,

but i got the error.. while adding standby database.
DGMGRL> create configuration FIR as
> primary database is fir
> connect identifier is fir;
Configuration "fir" created with primary database "fir"
DGMGRL> add database stand as
> connect identifier is stand
> maintained as physical;
Error: ORA-16796: one or more properties could not be imported from the database

but when i checked instance running on stand by its working , even tnsping is working , can u plz through light on it.

one more thing do we need to add observer tnsname same way like primary and standby eg broker is test_DGMGRL. i hope u reply soon

January 12, 2011 at 12:11 PM
Unknown said...

hi rajesh thanks for help n support

February 3, 2011 at 12:53 PM
Anonymous said...

Hi Bro! Your posts are simply remarkable.Can one implement DG Broker without an observer?
Could you also chnage the current post to the previous names of white and black.Thanks.

Riz

March 29, 2011 at 4:07 AM

Post a Comment

Subscribe to: Post Comments (Atom)
free counters
 
Share/Bookmark