Database Validation in Data Guard 12c

DGMGRL was never difficult to use; however, it was not providing enough information before switchover/failover to make sure it happened without issues. Even if “show configuration verbose” was indicating that everything had the SUCCESS status, switchover could lead to issues during role changes.

However, Data Guard Broker in #DB12c introduced a new command, VALIDATE DATABASE, which tremendously lightens the process of information gathering to make sure role change operations succeed. It is very helpful in the case of RAC database usage in Data Guard configuration.

Let’s see a test example of the command Data Guard configuration with a primary two instance RAC database and a standalone standby database.

DGMGRL> show configuration
Configuration - dg_d
  Protection Mode: MaxPerformance
  Databases:
  d  - Primary database
    d1 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database d
Database - d
  Role:              PRIMARY
  Intended State:    TRANSPORT-ON
  Instance(s):
    d1
    d2
Database Status:
SUCCESS
DGMGRL> show database d1
Database - d1
  Role:              PHYSICAL STANDBY
  Intended State:    APPLY-ON
  Transport Lag:     0 seconds (computed 0 seconds ago)
  Apply Lag:         0 seconds (computed 0 seconds ago)
  Apply Rate:        205.00 KByte/s
  Real Time Query:   OFF
  Instance(s):
    d1
Database Status:
SUCCESS

The DG configuration has the SUCCESS status. Let’s validate the standby database to see if it is ready for switchover:

DGMGRL> validate database verbose d1
  Database Role:     Physical standby database
  Primary Database:  d
  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)
  Capacity Information:
    Database  Instances        Threads
    d         2                2
    d1        1                2
    Warning: the target standby has fewer instances than the
    primary database, this may impact application performance
  Temporary Tablespace File Information:
    d TEMP Files:   2
    d1 TEMP Files:  1
  Flashback Database Status:
    d:   Off
    d1:  Off
  Data file Online Move in Progress:
    d:   No
    d1:  No
  Standby Apply-Related Information:
    Apply State:      Running
    Apply Lag:        0 seconds
    Apply Delay:      0 minutes
  Transport-Related Information:
    Transport On:      Yes
    Gap Status:        No Gap
    Transport Lag:     0 seconds
    Transport Status:  Success
  Log Files Cleared:
    d Standby Redo Log Files:  Cleared
    d1 Online Redo Log Files:  Cleared
  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups   Standby Redo Log Groups
              (d)                      (d1)
    1         2                        1
    2         2                        1
  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups   Standby Redo Log Groups
              (d1)                     (d)
    1         2                        1
    2         2                        1
  Current Configuration Log File Sizes:
    Thread #   Smallest Online Redo      Smallest Standby Redo
               Log File Size             Log File Size
               (d)                       (d1)
    1          50 MBytes                 50 MBytes
    2          50 MBytes                 50 MBytes
  Future Configuration Log File Sizes:
    Thread #   Smallest Online Redo      Smallest Standby Redo
               Log File Size             Log File Size
               (d1)                      (d)
    1          50 MBytes                 50 MBytes
    2          50 MBytes                 50 MBytes
  Apply-Related Property Settings:
    Property                        d Value                  d1 Value
    DelayMins                       0                        0
    ApplyParallel                   AUTO                     AUTO
  Transport-Related Property Settings:
    Property                        d Value                  d1 Value
    LogXptMode                      ASYNC                    ASYNC
    Dependency                      <empty>                  <empty>
    DelayMins                       0                        0
    Binding                         optional                 optional
    MaxFailure                      0                        0
    MaxConnections                  1                        1
    ReopenSecs                      300                      300
    NetTimeout                      30                       30
    RedoCompression                 DISABLE                  DISABLE
    LogShipping                     ON                       ON
  Automatic Diagnostic Repository Errors:
    Error                       d        d1
    No logging operation        NO       NO
    Control file corruptions    NO       NO
    SRL Group Unavailable       NO       NO
    System data file missing    NO       NO
    System data file corrupted  NO       NO
    System data file offline    NO       NO
    User data file missing      NO       NO
    User data file corrupted    NO       NO
    User data file offline      NO       NO
    Block Corruptions found     NO       NO

As you can see, the database is ready for switchover. There is no lag, and the DG broker has gathered other important bits and pieces, like clearance of standby and redo logs, temp files (there seems to be a bug showing 2 tempfiles for the 2 instance RAC database – does it select data from gv$tempfile?), and the status of data file movements related to the new #db12c feature.

Let’s see what will happen if transfer to standby is turned off:

DGMGRL> edit database d set state='TRANSPORT-OFF';
Succeeded.
DGMGRL> validate database d1
Database Role:     Physical standby database
Primary Database:  d
Ready for Switchover:  No
Ready for Failover:    Yes (Primary Running)
...

The standby database is not ready. But what is the reason for the status? DG log has more information:

08/20/2013 13:45:28
Primary completed health check
EDIT DATABASE d SET STATE = TRANSPORT-OFF
08/20/2013 13:45:41
Switchover to d1 not possible, verification returned error ORA-16466
See database alert log for more details

The alert log doesn’t contain much information. There is only a command to disable standby destination, and the error description gives the following:

$ oerr ora 16466
16466, 0000, "invalid switchover target"
// *Cause:  The switchover target was not a valid, enabled, or active
//          standby database.
// *Action: Fix the problem in the switchover target and reissue the command

The issue is not clearly stated, but the Data Guard configuration should certainly be checked and fixed before a switchover operation.

I tested several different scenarios. One of them was to shut down the standby database the with abort clause (I could not even get results from “validate database d1” since it was not available) and start it back up with disabled transport from the primary database. Data Guard command “show database d1” indicated that lags were unknown (which is related to NULL values in v$dataguard_stats). However, “validate database d1” was showing a constant Apply Lag of “286 days 11 hours 35 minutes 12 seconds”. It was not growing and stated the same thing until I enabled transport from primary. Where did it come from? Who knows…

DGMGRL> show database d1
Database - d1
  Role:              PHYSICAL STANDBY
  Intended State:    APPLY-ON
  Transport Lag:     (unknown)
  Apply Lag:         (unknown)
  Apply Rate:        0 Byte/s
  Real Time Query:   OFF
  Instance(s):
    d1
Database Status:
SUCCESS
DGMGRL> validate database d1
  Database Role:     Physical standby database
  Primary Database:  d
  Ready for Switchover:  No
  Ready for Failover:    Yes (Primary Running)
  Capacity Information:
    Database  Instances        Threads
    d         2                2
    d1        1                2
    Warning: the target standby has fewer instances than the
    primary database, this may impact application performance
  Temporary Tablespace File Information:
    d TEMP Files:   2
    d1 TEMP Files:  1
  Flashback Database Status:
    d:   Off
    d1:  Off
  Standby Apply-Related Information:
    Apply State:      Running
    Apply Lag:        286 days 11 hours 35 minutes 12 seconds
    Apply Delay:      0 minutes
  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups   Standby Redo Log Groups
              (d)                      (d1)
    1         2                        1
    2         2                        1
  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups   Standby Redo Log Groups
              (d1)                     (d)
    1         2                        1
    2         2                        1

Lag came back to 0 seconds when transport was enabled again.

There are definitely areas that should be improved to provide a clear understanding of VALIDATE DATABASE output and messages in drc (and alert…) logs, but this new feature improved and simplified information gathering for Data Guard configuration. This change will allow us to save time during possible role change operations.

Happy Data Guard’ing!