Mani's Blog

June 21, 2012

Setup Oracle Dataguard Broker and Switchover

Filed under: Dataguard,Oracle — mani @ 10:31 pm

Objective

Setting up DG Broker, which can be used to switchover and failover the Database to Physical Standby Database. DG Broker is easier to manage though you could do it from SQL Prompt.

Steps

  1. Check the current state of DG Broker on Primary Database
    SQL> show parameter DG_BROKER_START
    NAME                     TYPE                 VALUE
    ------------------------------------ -------------------------------- ------------------------------
    dg_broker_start              boolean                  FALSE
  2. Start the DG Broker On Primary Database
    SQL> alter system set DG_BROKER_START=true scope=both;
    System altered.
    SQL>
  3. Check the current state of DG Broker on Standby Database
    SQL> show parameter DG_BROKER_START
    NAME                     TYPE                 VALUE
    ------------------------------------ -------------------------------- ------------------------------
    dg_broker_start              boolean                  FALSE
  4. Start the DG Broker On Standby Database
    SQL> alter system set DG_BROKER_START=true scope=both;
    System altered.
    SQL>
  5. Configure Primary Database on Primary DB server.
    [oracle@xnodbprtz01 ~]$ dgmgrl
    DGMGRL for Linux: Version 11.1.0.7.0 - 64bit Production
    Copyright (c) 2000, 2005, Oracle. All rights reserved.
    Welcome to DGMGRL, type "help" for information.
    DGMGRL> connect sys/oracle
    Connected.
    DGMGRL> create configuration 'PRETZEL' as primary database is 'XNOPRTZ' connect identified is xnoprtz;
    create configuration 'PRETZEL' as primary database is 'XNOPRTZ' connect identified is xnoprtz;
                                                                            ^
    Syntax error before or at "identified"
    DGMGRL> create configuration 'PRETZEL' as primary database is 'XNOPRTZ' connect identifier is xnoprtz;
    Configuration "PRETZEL" created with primary database "XNOPRTZ"
    DGMGRL>
  6. Verify the configuration of Primary Database.
    DGMGRL> show configuration;
    Configuration
      Name:                PRETZEL
      Enabled:             NO
      Protection Mode:     MaxPerformance
      Databases:
        XNOPRTZ - Primary database
    Fast-Start Failover: DISABLED
    Current status for "PRETZEL":
    DISABLED
    DGMGRL>
  7. Add Standby DB info on Primary DB Server
    DGMGRL> add database 'XNOPRTZDG' as connect identifier is xnoprtzdg maintained as physical;
    Database "XNOPRTZDG" added
    DGMGRL>
  8. Verify the configuration
    DGMGRL> show configuration
    Configuration
      Name:                PRETZEL
      Enabled:             NO
      Protection Mode:     MaxPerformance
      Databases:
        XNOPRTZ   - Primary database
        XNOPRTZDG - Physical standby database
    Fast-Start Failover: DISABLED
    Current status for "PRETZEL":
    DISABLED
    DGMGRL>
  9. Enable DG Configuration
    DGMGRL> enable configuration
    Enabled.
    DGMGRL> show configuration
    Configuration
      Name:                PRETZEL
      Enabled:             YES
      Protection Mode:     MaxPerformance
      Databases:
        XNOPRTZ   - Primary database
        XNOPRTZDG - Physical standby database
    Fast-Start Failover: DISABLED
    Current status for "PRETZEL":
    Warning: ORA-16610: command "EDIT DATABASE XNOPRTZDG SET PROPERTY" in progress
  10. Restart the DG Broker to get rid of ORA-16610 Warning.
    SQL> ALTER SYSTEM SET DG_BROKER_START=FALSE;
    System altered.
    SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE;
    System altered.
    SQL>
  11. Verify the configuration
    DGMGRL> show configuration
    Configuration
      Name:                PRETZEL
      Enabled:             YES
      Protection Mode:     MaxPerformance
      Databases:
        XNOPRTZ   - Primary database
        XNOPRTZDG - Physical standby database
    Fast-Start Failover: DISABLED
    Current status for "PRETZEL":
    SUCCESS
    DGMGRL>
  12. Get detailed status of the database
    DGMGRL> show database verbose 'XNOPRTZ'
    Database
      Name:            XNOPRTZ
      Role:            PRIMARY
      Enabled:         YES
      Intended State:  TRANSPORT-ON
      Instance(s):
        XNOPRTZ
      Properties:
        DGConnectIdentifier             = 'xnoprtz'
        ObserverConnectIdentifier       = ''
        LogXptMode                      = 'ASYNC'
        DelayMins                       = '0'
        Binding                         = 'OPTIONAL'
        MaxFailure                      = '0'
        MaxConnections                  = '1'
        ReopenSecs                      = '300'
        NetTimeout                      = '30'
        RedoCompression                 = 'DISABLE'
        LogShipping                     = 'ON'
        PreferredApplyInstance          = ''
        ApplyInstanceTimeout            = '0'
        ApplyParallel                   = 'AUTO'
        StandbyFileManagement           = 'AUTO'
        ArchiveLagTarget                = '0'
        LogArchiveMaxProcesses          = '30'
        LogArchiveMinSucceedDest        = '1'
        DbFileNameConvert               = ''
        LogFileNameConvert              = ''
        FastStartFailoverTarget         = ''
        StatusReport                    = '(monitor)'
        InconsistentProperties          = '(monitor)'
        InconsistentLogXptProps         = '(monitor)'
        SendQEntries                    = '(monitor)'
        LogXptStatus                    = '(monitor)'
        RecvQEntries                    = '(monitor)'
        HostName                        = 'xnodbprtz01.ningops.com'
        SidName                         = 'XNOPRTZ'
        StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xnodbprtz01.ningops.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XNOPRTZ_DGMGRL.ningops.com)(INSTANCE_NAME=XNOPRTZ)(SERVER=DEDICATED)))'
        StandbyArchiveLocation          = '/arch/XNOPRTZ'
        AlternateLocation               = ''
        LogArchiveTrace                 = '0'
        LogArchiveFormat                = '%t_%s_%r.arc'
        LatestLog                       = '(monitor)'
        TopWaitEvents                   = '(monitor)'
    Current status for "XNOPRTZ":
    SUCCESS
    DGMGRL>

Switchover to Standby

  1. Switchover to Standby
    DGMGRL> show configuration verbose
    Configuration
      Name:                PRETZEL
      Enabled:             YES
      Protection Mode:     MaxPerformance
      Databases:
        XNOPRTZ   - Primary database
        XNOPRTZDG - Physical standby database
    Fast-Start Failover: DISABLED
    Current status for "PRETZEL":
    SUCCESS
    DGMGRL> switchover to XNOPRTZDG
    Site "xnoprtzdg" was not found
    DGMGRL> switchover to 'XNOPRTZDG'
    Performing switchover NOW, please wait...
    New primary database "XNOPRTZDG" is opening...
    Operation requires shutdown of instance "XNOPRTZ" on database "XNOPRTZ"
    Shutting down instance "XNOPRTZ"...
    ORA-01017: invalid username/password; logon denied
    You are no longer connected to ORACLE
    Please connect again.
    Unable to shut down instance "XNOPRTZ"
    You must shut down instance "XNOPRTZ" manually
    Operation requires startup of instance "XNOPRTZ" on database "XNOPRTZ"
    You must start instance "XNOPRTZ" manually
    Switchover succeeded, new primary is "XNOPRTZDG"
    DGMGRL>
    DGMGRL> show configuration verbose
    Error:
    ORA-16570: database needs restart
    ORA-06512: at "SYS.X$DBMS_DRS", line 228
    ORA-06512: at line 1
    DGMGRL> exit
  2. Original Primary DB need to be restarted as below
    [oracle@xnodbprtz01 ~]$ dba
    SQL*Plus: Release 11.1.0.7.0 - Production on Thu Jun 21 20:11:04 2012
    Copyright (c) 1982, 2008, Oracle.  All rights reserved.
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    SQL> shutdown immediate;
    ORA-01109: database not open
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup mount
    ORACLE instance started.
    Total System Global Area 2.6724E+10 bytes
    Fixed Size          2160272 bytes
    Variable Size        1.1543E+10 bytes
    Database Buffers     1.5032E+10 bytes
    Redo Buffers          146423808 bytes
    Database mounted.
    SQL>
  3. Start the apply process
    SQL> alter database recover managed standby database cancel;
    Database altered.
    SQL> ALTER DATABASE OPEN READ ONLY;
    Database altered.
    SQL> alter database recover managed standby database using current logfile disconnect;
    Database altered.
    SQL>
  4. Verify Current Status
    DGMGRL> show configuration
    Configuration
      Name:                PRETZEL
      Enabled:             YES
      Protection Mode:     MaxPerformance
      Databases:
        XNOPRTZDG - Primary database
        XNOPRTZ   - Physical standby database
    Fast-Start Failover: DISABLED
    Current status for "PRETZEL":
    SUCCESS
    DGMGRL>

Switch Back to Primary Database

  1. Switch
    DGMGRL> switchover to 'XNOPRTZ'
    Performing switchover NOW, please wait...
    New primary database "XNOPRTZ" is opening...
    Operation requires shutdown of instance "XNOPRTZ" on database "XNOPRTZDG"
    Shutting down instance "XNOPRTZ"...
    ORA-01017: invalid username/password; logon denied
    You are no longer connected to ORACLE
    Please connect again.
    Unable to shut down instance "XNOPRTZ"
    You must shut down instance "XNOPRTZ" manually
    Operation requires startup of instance "XNOPRTZ" on database "XNOPRTZDG"
    You must start instance "XNOPRTZ" manually
    Switchover succeeded, new primary is "XNOPRTZ"
    DGMGRL>
  2. Verify the Status
    DGMGRL> show configuration verbose
    Configuration
      Name:                PRETZEL
      Enabled:             YES
      Protection Mode:     MaxPerformance
      Databases:
        XNOPRTZ   - Primary database
        XNOPRTZDG - Physical standby database
    Fast-Start Failover: DISABLED
    Current status for "PRETZEL":
    Warning: ORA-16607: one or more databases have failed
    DGMGRL>
  3. Restart the database on Standby box
    [oracle@xnodbprtzdg01 dbs]$ dba
    SQL*Plus: Release 11.1.0.7.0 - Production on Thu Jun 21 20:53:57 2012
    Copyright (c) 1982, 2008, Oracle.  All rights reserved.
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    SQL> shutdown immediate;
    ORA-01109: database not open
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup mount
    ORACLE instance started.
    Total System Global Area 2.6724E+10 bytes
    Fixed Size          2160272 bytes
    Variable Size        1.1543E+10 bytes
    Database Buffers     1.5032E+10 bytes
    Redo Buffers          146423808 bytes
    Database mounted.
    SQL>
  4. Start the apply process
    SQL> alter database recover managed standby database cancel;
     
    Database altered.
     
    SQL> ALTER DATABASE OPEN READ ONLY;
     
    Database altered.
     
    SQL> alter database recover managed standby database using current logfile disconnect;
     
    Database altered.
     
    SQL>
  5. Verify the status finally
    DGMGRL> show configuration verbose
    Configuration
      Name:                PRETZEL
      Enabled:             YES
      Protection Mode:     MaxPerformance
      Databases:
        XNOPRTZ   - Primary database
        XNOPRTZDG - Physical standby database
    Fast-Start Failover: DISABLED
    Current status for "PRETZEL":
    SUCCESS
    DGMGRL>
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: