Start/Stop Redo Transport & Apply in Data Guard (DGMGRL + SQL) in Oracle

Leave a reply

In Data Guard, there are two main processes:

  1. Redo Transport – sends logs from primary to standby
  2. Redo Apply – applies logs on standby

If either stops, lag will happen.

CHECK CURRENT STATUS of Dataguard 

SHOW DATABASE VERBOSE standby;
Welcome to DGMGRL, type "help" for information.
Connected to "PRIMARY"
Connected as SYSDG.
DGMGRL> 
DGMGRL> 
DGMGRL> show database verbose standby
Database - standby
  Role:                PHYSICAL STANDBY
  Intended State:      APPLY-ON
  Transport Lag:       0 seconds (computed 1 second ago)
  Apply Lag:           0 seconds (computed 1 second ago)
  Average Apply Rate:  84.00 KByte/s
  Active Apply Rate:   3.26 MByte/s
  Maximum Apply Rate:  5.47 MByte/s
  Real Time Query:     ON
  Instance(s):
    ORCL
  Properties:
    AlternateLocation               = ''
    ApplyInstanceTimeout            = '0'
    ApplyInstances                  = '0'
    ApplyLagThreshold               = '30'
    ApplyParallel                   = 'AUTO'
    ArchiveLocation                 = ''
    Binding                         = 'OPTIONAL'
    DGConnectIdentifier             = 'standby'
    DelayMins                       = '0'
    FastStartFailoverTarget         = ''
    InconsistentLogXptProps         = '(monitor)'
    LogShipping                     = 'ON'
    LogXptMode                      = 'ASYNC'
    LogXptStatus                    = '(monitor)'
    MaxFailure                      = '0'
    NetTimeout                      = '30'
    ObserverConnectIdentifier       = ''
    PreferredApplyInstance          = ''
    PreferredObserverHosts          = ''
    RecvQEntries                    = '(monitor)'
    RedoCompression                 = 'DISABLE'
    RedoRoutes                      = ''
    ReopenSecs                      = '300'
    SendQEntries                    = '(monitor)'
    SidName                         = '(monitor)'
    StandbyAlternateLocation        = ''
    StandbyArchiveLocation          = ''
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standby)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STANDBY_DGMGRL)(INSTANCE_NAME=ORCL)(SERVER=DEDICATED)))'
    TopWaitEvents                   = '(monitor)'
    TransportDisconnectedThreshold  = '30'
    TransportLagThreshold           = '30'
    UserManagedParams               = ''
  Log file locations:
    '/opt/oracle/diag/rdbms/standby/ORCL/trace'
Database Status:
SUCCESS
DGMGRL> show database standby
Database - standby
  Role:                PHYSICAL STANDBY
  Intended State:      APPLY-ON
  Transport Lag:       0 seconds (computed 1 second ago)
  Apply Lag:           0 seconds (computed 1 second ago)
  Average Apply Rate:  82.00 KByte/s
  Real Time Query:     ON
  Instance(s):
    ORCL
Database Status:
SUCCESS

Check the Current Status with SQL Command for Dataguard

MRP process means redo apply is running.

SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;
SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;
PROCESS   STATUS
--------- --------------------
DGRD	  ALLOCATED
DGRD	  ALLOCATED
ARCH	  CONNECTED
ARCH	  CONNECTED
ARCH	  CONNECTED
ARCH	  CLOSING
RFS	  IDLE
RFS	  IDLE
RFS	  IDLE
MRP0	  APPLYING_LOG

STOP REDO APPLY (APPLY SERVICE) in Oracle Dataguard with DGMGRL command

This command stops redo apply on standby.

EDIT DATABASE standby SET STATE='APPLY-OFF';

DGMGRL> EDIT DATABASE standby SET STATE='APPLY-OFF';
Succeeded.
DGMGRL> show database standby

Database - standby

  Role:                PHYSICAL STANDBY
  Intended State:      APPLY-OFF
  Transport Lag:       0 seconds (computed 1 second ago)
  Apply Lag:           0 seconds (computed 1 second ago)
  Average Apply Rate:  (unknown)
  Real Time Query:     OFF
  Instance(s):
    ORCL

Database Status:
SUCCESS

Using SQL Query: Run on Standby Server

This cancels managed recovery process

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

START REDO APPLY in Oracle Dataguard Environment

EDIT DATABASE standby SET STATE='APPLY-ON';
DGMGRL> EDIT DATABASE standby SET STATE='APPLY-ON';
Succeeded.
DGMGRL>

With SQL Commands:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

STOP REDO TRANSPORT (PRIMARY SIDE)

This stops sending redo logs from primary to standby.

EDIT DATABASE primary SET STATE='TRANSPORT-OFF';
DGMGRL> EDIT DATABASE primary SET STATE='TRANSPORT-OFF';
Succeeded.
DGMGRL> 
DGMGRL> show database primary
Database - PRIMARY
  Role:                PRIMARY
  Intended State:      TRANSPORT-OFF
  Redo Rate:           122 Byte/s  in 15 seconds (computed 3 seconds ago) 
  Instance(s):
    ORCL
Database Status:
SUCCESS

Using SQL

This disables log shipping to standby

ALTER SYSTEM SET log_archive_dest_state_2=DEFER;

START REDO TRANSPORT using DGMGRL command

EDIT DATABASE primary SET STATE='TRANSPORT-ON';

Using SQL: this resume log shipping.

ALTER SYSTEM SET log_archive_dest_state_2=ENABLE;

Transport controls log shipping and Apply controls log application. Using DGMGRL, you can control both easily with just one command and easy to handle and it automate the process.

Check the error detail in Dataguard.

SELECT DEST_ID, STATUS, ERROR FROM V$ARCHIVE_DEST;