27 September, 2024

Video on DataGuard Snapshot Standby

 I have posted a new video on creating a "Snapshot Standby" database in DataGuard.

Typically such a Standby can be used for "destructive testing" like D.R. simulation with dummy transactions, application upgrades, schema changes.

If your concern is about the Physical Standby being unavailable for long note :

(1) Redo Shipping to the Standby can continue as ArchiveLogs (of course, this means the Standby will be lagging the Primary by the typical interval between Archive Log switches)

(2) You can configure 2 Standbys of which one continues to be a Physical Standby with real time redo shipping and the other is periodically used to test schema changes / application upgrades and even for frequent D.R. testing

I have previously posted a few Blog posts on scenarios like Multiple Standbys, Standbys with Lag, Destructive testing in Standbys.  See the list of posts here.



01 September, 2024

Video on DataGuard Switchover -- with RAC and Single Instance

 I've posted a demonstration of DataGuard Switchover, using RAC and Single Instance as the Primary/Standby pair.


Fundamentally, there is no difference if either or both of the databases are RAC or Single Instance.


A Switchover is a Graceful operation, with No-Data-Loss as the Primary sends the "End-Of-Redo Marker" to the Standby at which point Reversal of Roles happens.  Therefore, you can execute Switchover between the two databases (servers / data centres / sites) multiple times without loss of data.


A Failover, on the other hand, involves data loss and the erstwhile Primary does not revert to a Standby role but must be recreated / refreshed as a Standby from the new Primary.

28 July, 2024

A few dgmgrl (DataGuard Broker) commands

 I haven't create a video with commentary but here are a few dgmgrl commands.

In the output "RACDB" (or "racdb") is a 2-node RAC database (managed by ClusterWare) and "STDBY" (or "stdby") is a Single Node non-RAC database not using Oracle Restart.  Both are 19c (19.23)

Initial Setup :

[oracle@srv1 ~]$ dgmgrl sys
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sat Jul 27 02:31:53 2024
Version 19.23.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Password:
Connected to "RACDB"
Connected as SYSDBA.
DGMGRL> remove configuration;
Removed configuration
DGMGRL> create configuration racdb_dg as primary database is RACDB connect identifier is RACDB;
Configuration "racdb_dg" created with primary database "racdb"
DGMGRL> add database STDBY as connect identifier is STDBY maintained as physical;
Database "stdby" added
DGMGRL> show configuration;

Configuration - racdb_dg

  Protection Mode: MaxPerformance
  Members:
  racdb - Primary database
    stdby - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
DISABLED
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;

Configuration - racdb_dg

  Protection Mode: MaxPerformance
  Members:
  racdb - Primary database
    Warning: ORA-16905: The member was not enabled yet.

    stdby - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
WARNING   (status updated 177 seconds ago)

DGMGRL> show configuration ;

Configuration - racdb_dg

  Protection Mode: MaxPerformance
  Members:
  racdb - Primary database
    Warning: ORA-16905: The member was not enabled yet.

    stdby - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
WARNING   (status updated 182 seconds ago)

DGMGRL>
DGMGRL> show configuration ;

Configuration - racdb_dg

  Protection Mode: MaxPerformance
  Members:
  racdb - Primary database
    stdby - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 44 seconds ago)

DGMGRL>
DGMGRL> show configuration lag;

Configuration - racdb_dg

  Protection Mode: MaxPerformance
  Members:
  racdb - Primary database
    stdby - Physical standby database
            Transport Lag:      0 seconds (computed 0 seconds ago)
            Apply Lag:          0 seconds (computed 0 seconds ago)

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 23 seconds ago)

DGMGRL>
In the initial setup, it takes time to actually enable the databases so we see the "ORA-16095: the member was not enabled yet" warning messages.
Later, it takes time for communication of the Transport and Apply lag information so I present the lag only some significant seconds later.
Note that DataGuard is configured as "Maximum Performance" mode (not Maximum Availability as I show in my previous video)



The VALIDATE commands :


GMGRL> validate database racdb;

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Managed by Clusterware:
    racdb:  YES

DGMGRL> validate database stdby;

  Database Role:     Physical standby database
  Primary Database:  racdb

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    racdb:  On
    stdby:  Off

  Capacity Information:
    Database  Instances        Threads
    racdb     2                2
    stdby     1                2
    Warning: the target standby has fewer instances than the
    primary database, this may impact application performance

  Managed by Clusterware:
    racdb:  YES
    stdby:  NO

  Current Configuration Log File Sizes:
    Thread #   Smallest Online Redo      Smallest Standby Redo
               Log File Size             Log File Size
               (racdb)                   (stdby)
    1          512 MBytes                100 MBytes
    2          512 MBytes                100 MBytes

DGMGRL>
Here we see some information :
(1) The configuration is Ready for Swtichover (from "racdb" as Primary to "stdb")

(2) Flashback Database is configured on "racdb" but not on "stdby"

(3) Although "racdb" has two instances (two redo threads), "stdby" has only 1 instance (and redo thread)  {Thus we know it is possible to have a Single Instance Standby Database for a MultiNode RAC --- I won't go into a debate / discussion on such a configuration if comments appear in response to this blog post, suffice to say that IMHO, it can make sense to do so}

(4) "racdb" is managed by ClusterWare but "stdby" is not {Thus we know that the Standby does not have to be in ClusterWare / Oracle Restart configuration}

(5)  Although "racdb" has Redo Log files of 512MB, "stdby" is, unfortunately, configured with some smaller Redo Logs



Some "Verbose" Configuration Information :


DGMGRL> show configuration verbose

Configuration - racdb_dg

  Protection Mode: MaxPerformance
  Members:
  racdb - Primary database
    stdby - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverLagGraceTime   = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'
    ConfigurationWideServiceName    = 'RACDB_CFG'
    FastStartFailoverLagType        = 'APPLY'

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS

DGMGRL>
DGMGRL> show database verbose racdb;

Database - racdb

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    RACDB1
    RACDB2

  Properties:
    DGConnectIdentifier             = 'racdb'
    ObserverConnectIdentifier       = ''
    FastStartFailoverTarget         = ''
    PreferredObserverHosts          = ''
    LogShipping                     = 'ON'
    RedoRoutes                      = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyLagThreshold               = '30'
    TransportLagThreshold           = '30'
    TransportDisconnectedThreshold  = '30'
    ApplyParallel                   = 'AUTO'
    ApplyInstances                  = '0'
    StandbyFileManagement           = ''
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '0'
    LogArchiveMinSucceedDest        = '0'
    DataGuardSyncLatency            = '0'
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = ''
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    ArchiveLocation                 = ''
    AlternateLocation               = ''
    StandbyArchiveLocation          = ''
    StandbyAlternateLocation        = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    LogXptStatus                    = '(monitor)'
    SendQEntries                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName(*)
    StaticConnectIdentifier(*)
    TopWaitEvents(*)
    SidName(*)
    (*) - Please check specific instance for the property value

  Log file locations(*):
    (*) - Check specific instance for log file locations.

Database Status:
SUCCESS

DGMGRL>
DGMGRL> show instance verbose "RACDB1"

Instance 'RACDB1' of database 'racdb'

  PFILE:
  Properties:
    HostName                        = 'srv1'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.91)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=RACDB_DGMGRL)(INSTANCE_NAME=RACDB1)(SERVER=DEDICATED)))'
    TopWaitEvents                   = '(monitor)'
    SidName                         = '(monitor)'

  Log file locations:
    Alert log               : /u01/app/oracle/diag/rdbms/racdb/RACDB1/trace/alert_RACDB1.log
    Data Guard Broker log   : /u01/app/oracle/diag/rdbms/racdb/RACDB1/trace/drcRACDB1.log

Instance Status:
SUCCESS

DGMGRL>
Here we see some more information :
(1) Timeouts (which I've left at defaults)

(2) Fast Start Failover and Observer information (which I am not using in this configuration)

(3) That Log Transport is Async, with 0 Delay and Compression Disabled.

(3) The Alert Log and DG Broker Log file location  (for "racdb", I extract the information for only the first instance, "RACDB1")



Connect Identifiers :

DGMGRL> validate static connect identifier for racdb;
Oracle Clusterware on database "racdb" is available for database restart.

DGMGRL> validate static connect identifier for stdby;
Oracle Clusterware is not configured on database "stdby".
Connecting to database "stdby" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=stdby)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STDBY_DGMGRL)(INSTANCE_NAME=STDBY)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...
Succeeded.
The static connect identifier allows for a connection to database "stdby".
For "racdb" being managed by ClusterWare, I had specified "RACDB" {which is the SCAN address} as the Connect Identifier (with "primary database is RACDB connect identifier is RACDB" in the first configuration command) For "stdby", not being managed by ClusterWare or Oracle Restart, it DGMGRL entry for the Listener is required in this manner :
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = STDBY)
      (SID_NAME = STDBY)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = STDBY_DGMGRL)
      (SID_NAME = STDBY)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (ENVS="TNS_ADMIN=/u01/app/oracle/product/19.0.0/dbhome_1/network/admin")
    )
  )
So, if either your Primary or Standby or both are Not managed by ClusterWare or Oracle Restart, you need to have a DGMGRL entry for the Listener !