Changing delaymins property of a standby through broker

Changing delaymins property of a standby through broker

Sometimes we dont want MRP process to apply the redo changes immediately to standby database, so in this case we have to use delaymins feature. Let us see how we can work with it :-

My standby database is sby1

DGMGRL> edit database sby1 set property 'DelayMins' = '2';

At primary

SQL> @role

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PRIMARY          READ WRITE

SQL> @logswitch

System altered.

### On standby

SQL> select archived,sequence#,applied,to_Char(completion_Time,'dd-mon-yyyy hh24:mi:ss') from v$archived_log
...
....
......

ARC  SEQUENCE# APPLIED   TO_CHAR(COMPLETION_T
--- ---------- --------- --------------------
YES         70 YES       29-nov-2015 10:02:19
YES         71 NO        29-nov-2015 10:02:36

## As we can see Applied is NO

## Now after we set delaymins to 0 then immediately MRP will apply the changes to standby

DGMGRL> edit database sby1 set property 'DelayMins' = '0';
Property "DelayMins" updated

## And now we will see applied column will have YES

SQL> @arch
..
.....
........
ARC  SEQUENCE# APPLIED   TO_CHAR(COMPLETION_T
--- ---------- --------- --------------------
YES         70 YES       29-nov-2015 10:02:19
YES         71 YES       29-nov-2015 10:02:36
YES         72 YES       29-nov-2015 10:02:55
YES         73 YES       29-nov-2015 10:06:06

## Finally we confirm and see all the properties with delaymins for our standby database sby1

DGMGRL> show database verbose sby1

Database - sby1

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    sby1

  Properties:
    DGConnectIdentifier             = 'sby1'
    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          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = 'prod, sby1'
    LogFileNameConvert              = 'prod, sby1'
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'node4.example.com'
    SidName                         = 'sby1'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node4.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=sby1_DGMGRL)(INSTANCE_NAME=sby1)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS