Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG

Configuring 10g Data Guard Broker and Observer for failover and switchover

Configuring 10g Data Guard Broker and Observer for failover and switchover

  

 

 

 On a previous blog post, creating a physical standby database in Oracle 10g we looked at how to implement a physical standby database configuration. In this blog post we will look at how to configure the broker and observer, setup the database to Maximum Availability and managing switchover from Data Guard Manager, DGMGRL. 

The Enviroment  

  • 2 Linux servers.
  • Oracle Database 10gR2 Enterprise Edition.
  • ssh is configured for user oracle on both nodes.
  • Oracle Home is on identical path on both nodes.
  • Primary database 10GPRI  
  • Standby database 10GSB

 Pre requisites are Oracle Enterprise Edition 10g on the primary and the standby  server and a third server for the Observer.  On both primary and standby servers databases must use spfile instead of pfile. The listeners on both sides also should be configured with a static service for the Data Guard Broker. 

Data Guard Broker permits to manage a Data Guard Configuration, from both the Enterprise Manager Grid Control console, or from a command line interface. In this post we will explore command line interface for setting a physical standby database Data Guard. 

 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_VMRACLINUXTEST’ 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_name_DGMGRL 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_VMRACLINUXTEST =
     (DESCRIPTION_LIST =
       (DESCRIPTION =
         (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1.gj.com)(PORT = 1521)(IP = FIRST))
       )
     )
    SID_LIST_LISTENER_VMRACLINUXTEST =
     (SID_LIST =
       (SID_DESC =
         (GLOBAL_DBNAME = 10gpri)
         (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1  )
         (SID_NAME = 10gpri)
       )
       (SID_DESC =
         (SID_NAME= 10gpri)
         (GLOBAL_DBNAME = 10gpri_DGMGRL)
         (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1 )
       )
     ) 

Listener.ora on Node 2 

   LISTENER_VMRACLINUXTEST =
     (DESCRIPTION_LIST =
       (DESCRIPTION =
         (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux2.gj.com)(PORT = 1521)(IP = FIRST))
       )
     )
    SID_LIST_LISTENER_VMRACLINUXTEST =
     (SID_LIST =
       (SID_DESC =
         (GLOBAL_DBNAME = 10gsb)
         (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1  )
         (SID_NAME = 10gsb)
       )
       (SID_DESC =
         (SID_NAME= 10gsb)
         (GLOBAL_DBNAME = 10gsb_DGMGRL)
         (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1 )
       )
     ) 

Tnsnames.ora on Node 1, 2 and the observer node 

   10GPRI =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1.gj.com)(PORT = 1521))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = 10gpri_DGMGRL)
      )
    )
   10GSB =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST =  raclinux2.gj.com)(PORT = 1521))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = 10gsb_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/dr110gpri.dat
dg_broker_config_file2               string      /u01/app/oracle/product/10.2.0
                                                 /db_1/dbs/dr210gpri.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/dr110gsb.dat
dg_broker_config_file2               string      /u01/app/oracle/product/10.2.0
                                                 /db_1/dbs/dr210gsb.dat 

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

Configuration “10gpri” created with primary database “10gpri” 

Add the standby to the configuration and check it.   

DGMGRL> ADD DATABASE 10gsb AS
> CONNECT IDENTIFIER IS 10gsb
> MAINTAINED AS PHYSICAL;
Database “10gsb” added 

DGMGRL> SHOW CONFIGURATION; 

Configuration
  Name:                10gpri
  Enabled:             NO
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    10gpri – Primary database
    10gsb   – Physical standby database 

Current status for “10gpri”:
DISABLED 

DGMGRL> SHOW DATABASE VERBOSE 10gsb; 

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

  Properties:
    InitialConnectIdentifier        = ’10gsb’
    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/10gpri/, /u01/app /oracle/oradata/10gsb/’
    LogFileNameConvert              = ‘/u01/app/oracle/oradata/10gpri/, /u01/app /oracle/oradata/10gsb/’
    FastStartFailoverTarget         = ”
    StatusReport                    = ‘(monitor)’
    InconsistentProperties          = ‘(monitor)’
    InconsistentLogXptProps         = ‘(monitor)’
    SendQEntries                    = ‘(monitor)’
    LogXptStatus                    = ‘(monitor)’
    RecvQEntries                    = ‘(monitor)’
    HostName                        = ‘raclinux2′
    SidName                         = ’10gsb’
    LocalListenerAddress            = ‘(ADDRESS=(PROTOCOL=TCP)(HOST=raclinux2.gj.com)(PORT=1521))’
    StandbyArchiveLocation          = ‘/u01/app/oracle/oradata/10gsb/arch/’
    AlternateLocation               = ”
    LogArchiveTraclinuxe                 = ‘0’
    LogArchiveFormat                = ‘%t_%s_%r.arc’
    LatestLog                       = ‘(monitor)’
    TopWaitEvents                   = ‘(monitor)’ 

Current status for “10gsb”:
DISABLED 

DGMGRL> show database verbose 10gpri; 

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

  Properties:
    InitialConnectIdentifier        = ’10gpri’
    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/10gsb/, /u01/app/o raclinuxle/oradata/10gpri/’
    LogFileNameConvert              = ‘/u01/app/oracle/oradata/10gsb/, /u01/app/o raclinuxle/oradata/10gpri/’
    FastStartFailoverTarget         = ”
    StatusReport                    = ‘(monitor)’
    InconsistentProperties          = ‘(monitor)’
    InconsistentLogXptProps         = ‘(monitor)’
    SendQEntries                    = ‘(monitor)’
    LogXptStatus                    = ‘(monitor)’
    RecvQEntries                    = ‘(monitor)’
    HostName                        = ‘raclinux1′
    SidName                         = ’10gpri’
    LocalListenerAddress            = ‘(ADDRESS=(PROTOCOL=TCP)(HOST=raclinux1.gj.com)(PORT=1521))’
    StandbyArchiveLocation          = ‘/u01/app/oracle/oradata/10gpri/arch/’
    AlternateLocation               = ”
    LogArchiveTraclinuxe                 = ‘0’
    LogArchiveFormat                = ‘%t_%s_%r.arc’
    LatestLog                       = ‘(monitor)’
    TopWaitEvents                   = ‘(monitor)’ 

Current status for “10gpri”:
DISABLED 

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

Configuration
  Name:                10gpri
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    10gpri – Primary database
    10gsb   – Physical standby database 

Current status for “10gpri”:
SUCCESS 

DGMGRL> enable database 10gsb;
Enabled.
DGMGRL> SHOW DATABASE VERBOSE 10gsb; 

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

  Properties:
    InitialConnectIdentifier        = ’10gsb’
    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/10gpri/, /u01/app/oracle/oradata/10gsb/’
    LogFileNameConvert              = ‘/u01/app/oracle/oradata/10gpri/, /u01/app/oracle/oradata/10gsb/’
    FastStartFailoverTarget         = ”
    StatusReport                    = ‘(monitor)’
    InconsistentProperties          = ‘(monitor)’
    InconsistentLogXptProps         = ‘(monitor)’
    SendQEntries                    = ‘(monitor)’
    LogXptStatus                    = ‘(monitor)’
    RecvQEntries                    = ‘(monitor)’
    HostName                        = ‘raclinux2′
    SidName                         = ’10gsb’
    LocalListenerAddress            = ‘(ADDRESS=(PROTOCOL=TCP)(HOST=raclinux2.gj.com)(PORT=1521))’
    StandbyArchiveLocation          = ‘/u01/app/oracle/oradata/10gsb/arch/’
    AlternateLocation               = ”
    LogArchiveTraclinuxe                 = ‘0’
    LogArchiveFormat                = ‘%t_%s_%r.arc’
    LatestLog                       = ‘(monitor)’
    TopWaitEvents                   = ‘(monitor)’ 

Current status for “10gsb”:
SUCCESS 

Configuration
  Name:                10gpri
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    10gpri – Primary database
    10gsb   – Physical standby database 

Current status for “10gpri”:
SUCCESS 

Enabling the configuration and databases 

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

Configuration
  Name:                10gpri
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    10gpri – Primary database
    10gsb   – Physical standby database 

Current status for “10gpri”:
SUCCESS 

DGMGRL> enable database 10gsb;
Enabled.
DGMGRL> SHOW DATABASE VERBOSE 10gsb; 

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

  Properties:
    InitialConnectIdentifier        = ’10gsb’
    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/10gpri/, /u01/app/oracle/oradata/10gsb/’
    LogFileNameConvert              = ‘/u01/app/oracle/oradata/10gpri/, /u01/app/oracle/oradata/10gsb/’
    FastStartFailoverTarget         = ”
    StatusReport                    = ‘(monitor)’
    InconsistentProperties          = ‘(monitor)’
    InconsistentLogXptProps         = ‘(monitor)’
    SendQEntries                    = ‘(monitor)’
    LogXptStatus                    = ‘(monitor)’
    RecvQEntries                    = ‘(monitor)’
    HostName                        = ‘raclinux2′
    SidName                         = ’10gsb’
    LocalListenerAddress            = ‘(ADDRESS=(PROTOCOL=TCP)(HOST=raclinux2.gj.com)(PORT=1521))’
    StandbyArchiveLocation          = ‘/u01/app/oracle/oradata/10gsb/arch/’
    AlternateLocation               = ”
    LogArchiveTraclinuxe                 = ‘0’
    LogArchiveFormat                = ‘%t_%s_%r.arc’
    LatestLog                       = ‘(monitor)’
    TopWaitEvents                   = ‘(monitor)’ 

Current status for “10gsb”:
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 the primary site issue the following SQL statements: 

SQL> SELECT TYPE,MEMBER FROM V$LOGFILE; 

TYPE    MEMBER
——- ————————————————–
ONLINE  /u01/app/oracle/oradata/10gsb/redo03.log
ONLINE  /u01/app/oracle/oradata/10gsb/redo02.log
ONLINE  /u01/app/oracle/oradata/10gsb/redo01.log
STANDBY /u01/app/oracle/oradata/10gb/redoby04.log
STANDBY /u01/app/oracle/oradata/10gsb/redoby05.log
STANDBY /u01/app/oracle/oradata/10gsb/redoby06.log 

On the standby site issue the following SQ L statements: 

SQL> SELECT TYPE,MEMBER FROM V$LOGFILE; 

TYPE    MEMBER
——- ————————————————–
ONLINE  /u01/app/oracle/oradata/10gpri/redo03.log
ONLINE  /u01/app/oracle/oradata/10gpri/redo02.log
ONLINE  /u01/app/oracle/oradata/10gpri/redo01.log
STANDBY /u01/app/oracle/oradata/10gpri/redoby04.log
STANDBY /u01/app/oracle/oradata/10gpri/redoby05.log
STANDBY /u01/app/oracle/oradata/10gpri/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 10gpri SET PROPERTY ‘LogXptMode’=’SYNC’;
Property “LogXptMode” updated
DGMGRL> EDIT DATABASE 10gsb SET PROPERTY ‘LogXptMode’=’SYNC’;
Property “LogXptMode” updated 

3.Specify the FastStartFailoverTarget property 

DGMGRL> EDIT DATABASE 10gpri SET PROPERTY FastStartFailoverTarget=’10gsb’;
Property “faststartfailovertarget” updated
DGMGRL> EDIT DATABASE 10gsb SET PROPERTY FastStartFailoverTarget=’10gpri’;
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 “10gpri” on database “10gpri”
Shutting down instance “10gpri”…
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “10gpri” on database “10gpri”
Starting instance “10gpri”…
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@raclinux1 ~]$ 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@10gpri;
Connected.
DGMGRL> show configuration verbose; 

Configuration
  Name:                10gpri
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Fast-Start Failover: DISABLED
  Databases:
    10gpri – Primary database
    10gsb   – Physical standby database 

Current status for “10gpri”:
SUCCESS 

DGMGRL> show database 10gsb; 

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

Current status for “10gsb”:
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@10gpri
START OBSERVER;
eof
—————- script end on previous line ——————–
[oracle@raclinux3 ~]$ nohup ./startobserver
nohup: appending output to `nohup.out’
[1] 27392
Verify the fast-start failover configuration.
[oracle@raclinux3 ~]$ 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@10gpri
Connected.
DGMGRL> show configuration verbose 

Configuration
  Name:                10gpri
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Fast-Start Failover: ENABLED
  Databases:
    10gpri – Primary database
    10gsb   – Physical standby database
           – Fast-Start Failover target 

Fast-Start Failover
  Threshold: 30 seconds
  Observer:  raclinux1 

Current status for “10gpri”:
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 10gpri 

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

Current status for “10gpri”:
SUCCESS 

DGMGRL> show database 10gsb 

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

Current status for “10gsb”:
SUCCESS 

EXECUTE THE SWITCHOVER:  

  

DGMGRL> SWITCHOVER TO 10gsb;
Performing switchover NOW, please wait…
Operation requires shutdown of instance “10gpri” on database “10gpri”
Shutting down instance “10gpri”…
ORA-01109: database not open 

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance “10gsb” on database “10gsb”
Shutting down instance “10gsb”…
ORA-01109: database not open 

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “10gpri” on database “10gpri”
Starting instance “10gpri”…
ORACLE instance started.
Database mounted.
Operation requires startup of instance “10gsb” on database “10gsb”
Starting instance “10gsb”…
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is “10gsb”
DGMGRL> 

DGMGRL> show configuration verbose 

Configuration
  Name:                10gpri
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Fast-Start Failover: ENABLED
  Databases:
    10gpri – Physical standby database
           – Fast-Start Failover target
    10gsb   – Primary database 

Fast-Start Failover
  Threshold: 30 seconds
  Observer:  raclinux1 

Current status for “10gpri”:
SUCCESS 

DGMGRL> show database 10gsb 

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

Current status for “10gsb”:
SUCCESS 

DGMGRL> show database 10gpri 

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

Current status for “10gpri”:
SUCCESS

March 6, 2010 Posted by | oracle | 1 Comment

Creating A DataGuard Broker Configuration Using DGMGRL in Oracle 10g

Creating A DataGuard Broker Configuration Using DGMGRL in Oracle 10g
  
Intro
Though Grid Control offers a graphical interface for much more than just configuring a high availability database, sometimes its Installation maybe considered a huge overhead. Or you have other reasons to favor the command line. So this documentation will help you to create a DataGuard Configuration including a primary and a standby database.
Now we also need to connect to the primary database: CONNECT sys/syspassword. Though DGMGRL will not throw any error when using CONNECT /, this would probably cause some ORA-01031 later since it cannot handle the OS-authentication correctly. So better explicitly specify an account with SYSDBA privileges here. Forget about the AS SYSDBA suffix: This will simply throw an error. Specifying an account with SYSDBA privileges will make DGMGRL connect AS SYSDBA automatically.
 
Preconditions
In order to successfully create the configuration, both your primary and standby database must be up and running using SPFILE. If you did not yet create the SPFILE, you must do so first:

CREATE SPFILE FROM PFILE;

SHUTDOWN IMMEDIATE STARTUP [MOUNT]

As indicated, you have to restart the database after creating the SPFILE, since the database can only use it if it is started with it. The keyword “MOUNT” is put in square brackets, since it applies only to the standby database.
 
Preparations
To make sure we have a clean start, we have to remove all “evidence” left over from possible failed attempts. This is done as follows:

  1. Make sure the listener is configured with a special entry for DGMGRL (see below)
  2. CleanUp archive log destination on the STANDBY database:
    ALTER SYSTEM SET log_archive_dest_1=”;
  3. Stop DG broker process (DMON) on both, primary and standby database:
    ALTER SYSTEM SET dg_broker_start=FALSE SCOPE=spfile SID=’*’;
  4. (Re)Move any existing DG broker configuration files from the following locations: $ORACLE_BASE/admin/<db_unique_name> or $ORACLE_HOME/dbs, namely dr1<db_unique_name>.dat and dr2<db_unique_name>.dat
  5. Start the DG broker process (DMON) again on both, the primary and standby databases:
    ALTER SYSTEM SET dg_broker_start=TRUE SCOPE=spfile SID=’*’;
    Make sure it is running – check output of     SHOW PARAMETER DG
 
Creating the DataGuard configuration
Now it’s time to startup the DataGuard manager CLI interface. On the primary database machine (make sure the $ORACLE_SID is pointing to your primary instance), simply issue the command dgmgrl. This will bring you to the CLI interface command prompt, indicated by DGMGRL> replacing the OS prompt.
 
Create Configuration
To create an initial configuration, we use the CREATE CONFIGURATION command. This will look like following:

CREATE CONFIGURATION ‘sample’ AS

PRIMARY DATABASE IS ‘primary_db’ CONNECT IDENTIFIER IS

primary_db.world;
As this is missing in most documentations, I want to explicitly state what the parameters must be:

  • CREATE CONFIGURATION ‘sample’: ‘sample’ can be anything you want – it is simply the name of the configuration you may want to remember. So you can chose anything suitable here.
  • PRIMARY DATABASE IS ‘primary_db’: ‘primary_db’ must match your primary databases db_unique_name. You can issue the command SHOW PARAMETER db_unique_name in SQL*Plus to verify.
  • CONNECT IDENTIFIER IS primary_db.world: This specifies how the database is resolved, and should reflect the database alias as defined in your tnsnames.ora (or whatever name service you are using)
Once created, you can display the details issuing the SHOW CONFIGURATION command. You may wonder about additional settings being displayed – well, since we did not define more specials, we implicitely adviced the broker to use its defaults for these. You may note that the current status will display “DISABLED”: That’s okay, we are not yet done 🙂
 
Adding the standby database
When creating the configuration, we only specified the primary database – so now it’s time to add our standby database(s). The command to use for this step is ADD DATABASE, and to explain it, we assume the following example:

ADD DATABASE ‘standby_db’ AS CONNECT

IDENTIFIER IS standby_db.world MAINTAINED AS

PHYSICAL;
Not that much to explain here, if you’ve been reading the details on the CREATE CONFIGURATION command above: standby_db again has to reflect the db_unique_name, this time of your standby database. The CONNECT IDENTIFIER again is how the database is resolved. Last but not least, MAINTAINED AS PHYSICAL is quite self-explaining: We just added a physical standby database.
 
Verifying the configuration
Now you may want to check what you configured. As already mentioned before, you may issue the command SHOW CONFIGURATION – this time there will be some more details, since one more database has been added. Similarly, you can display details on the database(s) configured issuing the SHOW DATABASE [VERBOSE] <db_unique_name>; command. You may already have guessed it: the “VERBOSE” keyword will cause a much more verbose output 😉
 
Enabling the configuration
Even after adding our standby database(s), a SHOW CONFIGURATION always told us it is not yet enabled. That’s still right – we have to do so explicitly. And now is the time for it, so we issue a ENABLE CONFIGURATION at the DGMGRL> prompt. If we configured everything correctly, this step will take some time. After the prompt returned, you may issue the SHOW CONFIGURATION command again. Don’t worry if you now get some “…in progress” warnings – in fact, this is a good sign: If it’s in progress, the configuration must be available.
But after a couple of minutes, SHOW CONFIGURATION should end with the word “SUCCESS” – then you are done, congratulations!
 
Troubleshooting
When it comes to errors, the returned errors maybe not that helpful. Sometimes all they say is simply “well, there was some error – go, check and fix that”. To give you an example: Warning: ORA-16607: one or more databases have failed Now go and check!
 
ORA-16607 on SHOW CONFIGURATION
Problem: After creating your configuration and adding the standby database, you issued a SHOW CONFIGURATION as suggested. Instead of the expected SUCCESS, the report ends up with

Warning: ORA-16607: one or more databases have failed

. Checking with oerr ora 16607 was not very helpful (see above), and you neither can find anything in your alert.log nor any trace files.
Cause: Probably at least one of your databases is not using an SPFILE.
Solution: Check whether your databases have an SPFILE associated. It is usually located in $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora. If it does not exist, create it: Login to your database as SYSDBA, and issue the command CREATE SPFILE FROM PFILE;. Even if it exists, to make the database using it you need to restart the instance – it must be used already at startup.
 
ORA-16608 at SWITCHOVER
Problem: You issued a SWITCHOVER TO <standby_db> at the DGMGRL prompt. But instead of initiating the switchover, DGMGRL throws the error

Error: ORA-16608: one or more databases have warnings
Cause: This is similar to the error above – just a different error since you issued a different command.
Solution: See above.
 
ORA-01031 during switchover
Problem: You started a switchover, which is performed successfully by DGMGRL. However, DGMGRL does not startup the databases again but issues an ORA-01031: insufficient privileges.
Cause: You lack some privileges (obviously). Probably you wonder why, since you may have connected with the command CONNECT /. NOTE: DGMGRL does not support OS authentication!
Solution: You need to explicitly connect as a user with the SYSDBA privileges, e.g. CONNECT sys/password.
 
ORA-16675 during SWITCHOVER
Problem: You issued a SWITCHOVER TO <standby> at the DGMGRL prompt. But instead of the switchover to be performed, you get an

Error: ORA-16775: target standby database in broker operation has potential data loss
Cause: Recovery has not caught up (yet).
Solution: Make sure your standby database is mounted and recovery is running. If not, issue a STARTUP MOUNT to mount the standby database, and start managed recovery with ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;. Wait a few minutes, and try again.
 
ORA-12514 during SWITCHOVER
Problem: You perform a switchover using DGMGRL. Though the switchover itself completes successful, databases don’t get started up again. Instead, DGMGRL output holds an

ORA-12514: TNS:listener does not currently know of service requested in connect
Cause: This is a configuration issue: DGMGRL needs a special entry in the listener.ora.
Solution: Details on this can be found in the Metalink note 308943.1. Make sure both, your primary and standby database have an explicit entry in the listener.ora like this:

SID_LIST_LISTENER = (

SID_LIST = (

SID_DESC = (

GLOBAL_DBNAME = <db_unique_name>_DGMGRL.<db_domain> ) ( SERVICE_NAME

= <db_unique_name>.<db_domain> ) ( SID_NAME = <ORACLE_SID> ) ( ORACLE_HOME =

<ORACLE_HOME> ) ) )
Take special care, that:

  • <db_unique_name> reflects the databases unique name (SHOW PARAMETER db_unique_name)
  • <db_domain> reflects the databases domain (SHOW PARAMETER db_domain)
  • and reflect the corresponding settings of the database
Having adjusted your listener.ora files, restart the listener (lsnrctl stop && lsnrctl start). Give your databases a few minutes to register with the listener again, and then – just to be on the safe side – let the listener tell you its configuration: lsnrctl status && lsnrctl services. Check the output if everything matches.
 
Sources
Next to my own experiences, this article is based on:

  • two MetaLink service requests (private)
  • MetaLink document 260112.1 (“10g DGMGRL CLI Configuration”)
  • MetaLink document 308943.1 (“Automatic Restart of Databases during Switchover fail with ORA-12514 in DGMGRL”)

March 6, 2010 Posted by | oracle | 1 Comment

Data Guard Broker Improvements in Oracle 10g

Data Guard Broker Improvements in Oracle 10g

The Oracle 10g Data Guard also has its share of improvements, including the following, which are discussed in more detail next:

  • Broker support for RAC databases
  • Enhancements to the 10g Enterprise Manager Data Guard GUI
  • New Data Guard Broker commands

Broker Support for RAC Databases

If you use the GUI interface to create a standby from a RAC database, the standby will be created as a single-instance database.

Starting in 10g, the Data Guard Broker has added support for a RAC database environment. This means that DBAs who run a RAC environment can now configure and support Data Guard configurations using the Broker GUI or the command-line interface. You can use the same Broker commands and GUI pages you used for a single instance, now for a RAC instance. It is important to understand, however, that the Broker only supports sending redo to one instance of the standby database. The standby database can be either a single instance or a RAC-enabled instance, but in either case, only one instance would act as the receiving instance and apply the redo information. It would be convenient to have a standby RAC database apply redo information in case of standby instance failure. If the receiving instance fails, the Broker automatically detects the failure, selects a new receiving instance on the RAC standby, redirects redo shipment to the new receiving instance, and starts log apply on the instance. Just as in prior releases, to perform any switchover with a RAC database in place, all instances except one must be shut down. Because Data Guard and RAC are integrated, however, the Broker will invoke the shutdown commands and perform all necessary work for the switchover.

Enhancements to the Enterprise Manager Data Guard GUI

The 10g Enterprise Manager has updated the interface for the Data Guard Broker GUI. From the Data Guard home page, shown in Figure 16.2, you can now see an overview of the configuration, a summary of the amount of data shipping and applied to each standby, and the status of the standby databases. As a result, DBAs can now easily manage the Data Guard environment.

Figure 16.2. The Data Guard home page via 10g EM Grid Control.


 

The Performance Overview page, which you access by clicking the Performance Overview link in the Performance section of the Data Guard home page, consists of four new charts:

  • Data Archived (MB). This chart indicates the amount of redo generated on the primary over time.
  • Standby Progress Summary. This chart includes Data Not Applied and Data Not Received statistics.
  • Data Applied. This chart conveys the amount of redo being applied on each standby over time.
  • Log Services Summary. This chart includes detailed information about the current log file being used by each database.

This page, shown in Figure 16.3, is monitored in real-time, manually or automatically (every 30 or 60 seconds). There is no difference in the appearance if the databases are RAC or single-instance. If the primary database is a RAC database, each chart will aggregate the information across all necessary threads.

Figure 16.3. The 10g Data Guard Performance Overview page.


 

Also new in 10g Enterprise Manager is the Add Standby Database wizard, which you access by clicking the Add Standby Database button in the Standby Databases section of the Data Guard home page. Using the Add Standby Database wizard enables individuals who previously did not have an advanced understanding of Data Guard to quickly and easily build and run standby databases in any environment.

Building a standby database with the Standby Database Creation Wizard is as easy as providing a few bits of information about your configuration. Figure 16.4 displays the first screen of the Add Standby Database wizard.

Figure 16.4. The new 10g Standby Database Creation wizard.

[View full size image]



 

To use the new 10g Standby Database Creation wizard to build your new standby environment, your primary database must be in archive log mode. If it is not, you will receive an error message about the NOARCHIVELOG MODE status.

When you use the wizard to build a new standby database, the wizard will first display a window to allow you to choose your backup method. Because a hot or cold backup is needed to begin the process, Data Guard Manager utilizes RMAN to perform this backup process (see Figure 16.5). You may also receive a warning at this point if your database is not in force-logging mode. Force logging is not required, but Oracle recommends that your primary database be placed in force-logging mode to ensure that no no-logging operations can be executed at the primary.

Figure 16.5. Choosing a backup method for the 10g Standby Database Creation wizard.


 

Next, as shown in Figure 16.6, the wizard will prompt you for a working directory and the user admin credentials of the Oracle Home owner. The working directory will be used to house the primary database backup files during the standby build process. You can also choose to delete or retain the working directory for future builds.

Figure 16.6. Providing the backup options for the 10g Standby Database Creation wizard.


 

The wizard’s third step, shown in Figure 16.7, prompts you for the standby instance name as well as the user admin credentials over the standby Oracle Home. In this example we used the same Oracle Home for the primary and standby; note, however, that the primary and standby Oracle Homes could have different user admin credentials.

Figure 16.7. Providing the necessary instance name and host credentials for the 10g Standby Database Creation wizard.

]


 

Next, the wizard verifies the standby file locations as well as the network configuration file location, as shown in Figure 16.8. Because our sample standby database uses the same host as the primary, Data Guard Manager automatically uses the OFA (Oracle Flexible Architecture) file structure.

Figure 16.8. Verifying the standby database and network file locations for the 10g Standby Database Creation wizard.


 

The next screen, shown in Figure 16.9, prompts you for the specific information about your standby configuration. Here you will need your database unique name, target name, and standby archive location.

Figure 16.9. Configuring the new standby database using the 10g Standby Database Creation wizard.



 

Possible Error with Standby Filenames

When using the wizard to build your logical or physical standby database, it is possible that you may run into bug 3655231 if you are using Version 10.1.0.3 or below. If you receive the Can’t find unicode character property definition via main->r or r.pl at unicode/Is/r.pl line 0 error message during your standby build you have hit this bug. The workaround is to remove any standby data file and/or log file names that contain \p. If this exists, either rename the directory or choose a different directory to house the files. Then, rename the files accordingly using the Customize button in the screen shown in Figure 16.8. You could also avoid this issue by applying the 10.1.0.4 patch set or oneoff patch 4039195 to version 10.1.0.2.

 

Finally, the wizard displays a summary of your standby build options, as shown in Figure 16.10. Review your choices. If any are incorrect, click the Back button repeatedly until you reach the relevant screen to correct them. When you’re satisfied with your selections, click the Finish button.

Figure 16.10. Overall summary for new target standby database using the 10g Standby Database Creation wizard.


 

Data Guard Manager proceeds with the build by creating your configuration (see Figure 16.11), preparing and submitting the build job (see Figure 16.12), and adding your standby target to Enterprise Manager Grid Control (see Figure 16.13).

Figure 16.11. Process screen showing the creation of the new standby database environment.



 

Figure 16.12. Process screen showing the necessary jobs submitted to build the new standby database.

[View full size image]



 

Figure 16.13. Process screen showing the necessary jobs submitted to build the new standby database.

[View full size image]


 

When the target is complete, Enterprise Manager redirects you to the Data Guard home page so you can monitor the standby database build (see Figure 16.14).

Figure 16.14. The 10g Data Guard home page showing the new standby database in process.



 

New 10g Broker Commands

With Oracle 10g, most DGMGRL commands have changed. Many have been improved with simplified meanings; others are just brand new due to the changes with the 10g Data Guard Broker configuration model. Following are the more notable changes with the 10g Broker command line syntax.

CREATE CONFIGURATION

One command that has become highly simplified with 10g is the CREATE CONFIGURATION command. In previous releases, the CREATE CONFIGURATION command was highly complex; with 10g, however, all you need to supply is three arguments to create your new configurationconfiguration name, database name, and connect identifieras shown here:

DGMGRL> create configuration ‘DR_CONFIG’ as primary database is ‘PROD’

connect identifier is ‘RAC_PROD’;

  • The configuration name is a name you provide for the Broker configuration. The name, like most names in Oracle, must be made up of alphanumeric characters and can only consist of 30 characters. The preceding example shows DR_CONFIG as the configuration name.
  • The database name is the name that will be used by the Broker to refer to the primary database object. The name must match that value of the corresponding database DB_UNIQUE_NAME parameter. In the preceding example, PROD is the database name.
  • The connect identifier can be a fully specified connect descriptor or name that is reprehensive in an Oracle Net environment (a tnsnames.ora enTRy) to the primary database. In a RAC environment, you must make sure your connect identifier can be used to address at least one instance of the primary database. In the preceding example, RAC_PROD is the connect identifier.

When your configuration is set, you can then use the new ADD DATABASE command to add standby databases to the configuration.

ADD DATABASE

This new Broker command is used to add databases to a configuration. The database name is the DB_UNIQUE_NAME of the standby and the connect identifier must resolve to the standby database. The type of standby (physical or logical) can be interchangeable with this command.

DGMGRL> add database ‘DRPROD’ as connect identifier is ‘RAC_DRPROD’ maintained as physical;


 

REMOVE DATABASE

The REMOVE DATABASE command is used to remove a standby database from your configuration. The database name is the DB_QUIQUE_NAME of the standby you want removed.

DGMGRL> remove database ‘DRPROD’;


 

DISABLE DATABASE (Replaces DISABLE_RESOURCE)

The DISABLE DATABASE command is used to disable Broker management of a standby database within a configuration. This means that all Broker-directed modifications will be ignored for this specific standby database. Also, the Broker will no longer monitor the specified database for health-status checks. The database name is the DB_UNIQUE_NAME of the standby you want to disable.

DGMGRL> disable database ‘DRPROD’;


 

ENABLE DATABASE (Replaces ENABLE_RESOURCE)

The ENABLE DATABASE command is used to restore Broker management of a standby database that was previously disabled. The database name is the DB_UNIQUE_NAME of the standby you want to disable.

DGMGRL> enable database ‘DRPROD’;


 

EDIT DATABASE (Replaces ALTER_RESOURCE)

The EDIT DATABASE command is used to set a new property value to your specified database, rename your database, or modify the state of the database. The database name is the DB_UNIQUE_NAME of the standby you want to disable.

DGMGRL> edit database ‘DRPROD’ set property ‘logarchivetrace’=’127’;

DGMGRL> edit database ‘DRPROD’ set state=’read-only’;

DGMGRL> edit database ‘DRPROD’ rename to ‘DRPROD_STDBY’;


 

EDIT CONFIGURATION (Replaces ALTER_CONFIGURATION)

The EDIT CONFIGURATION command is used to modify the current protection mode setting for a predefined configuration.

DGMGRL> edit configuration set protection mode as maxprotection;

Valid protection modes for a configuration are MAXPROTECTION, MAXAVAILABILITY, and MAXPERFORMANCE. The default is MAXPERFORMANCE.

EDIT INSTANCE

The EDIT INSTANCE command is used to modify the value of a property for a specified instance. For these commands, if your instance name is unique across the configuration, then the database name is not required.

DGMGRL> edit instance ‘DRPROD_N1’ on database ‘DRPROD’ set property lsbymaxservers = ‘4’;

Properties that can be modified with the EDIT INSTANCE command include the following:

  • StandbyArchiveLocation. Location of archived redo logs arriving from a primary database.
  • AlternateLocation. Alternate location to be used if the standby can no longer archive to StandbyArchiveLocation.
  • LogArchiveTrace. Integer value to denote the progression of the archiving of redo logs on the primary and standby databases.
  • LogArchiveFormat. Format for filenames of archived redo log files.
  • LsbyMaxSga. Number of megabytes allocated from the System Global Area (SGA) for the log apply services cache. The default value is one quarter (1/4) of the value set for SHARED_POOL_SIZE parameter.
  • LsbyMaxServers. Number of parallel query servers specifically reserved for log apply services.

FAILOVER

The new FAILOVER command specifies that a standby database will take on the role of a primary database.

DGMGRL> failover to DRPROD_STDBY;

If the Data Guard Broker configuration is in Maximum Protection or Maximum Availability mode, a failover will force the protection mode to be MAXIMUM PERFORMANCE. After the failover has occurred, the DBA can change this mode if needed.

SHOW

The new SHOW command displays the current settings of your configuration model. The SHOW command can be used at the configuration, database, or instance level.

DGMGRL> show configuration;

DGMGRL> show database ‘DRPROD’;

DGMGRL> show instance ‘RAC_DRPROD’;


 

SWITCHOVER

The new SWITCHOVER command performs a switchover operation to the database object corresponding to the named site.

DGMGRL> switchover to DRPROD_STDBY;

March 6, 2010 Posted by | oracle | 23 Comments