Changing the Protection Mode from Maximum Protection to Maximum Availability

Changing protection mode and 'LogXptMode' for Physical Standby DB (affect spfile of Primary)

DGMGRL> show database verbose 'PEMDEVSTBY';

Database - PEMDEVSTBY

  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: 2.00 KByte/s

  Active Apply Rate:  774.00 KByte/s

  Maximum Apply Rate: 1.38 MByte/s

  Real Time Query:    ON

  Instance(s):

    PEMDEVSTBY

  Properties:

    DGConnectIdentifier             = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=eunloo12c)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PEMDEVSTBY)(SERVER=DEDICATED)))'

    ObserverConnectIdentifier       = ''

    LogXptMode                      = 'ASYNC'

    RedoRoutes                      = ''

    DelayMins                       = '0'

    Binding                         = 'optional'

    MaxFailure                      = '0'

    MaxConnections                  = '1'

    ReopenSecs                      = '300'

    NetTimeout                      = '30'

    RedoCompression                 = 'DISABLE'

    LogShipping                     = 'ON'

    PreferredApplyInstance          = ''

    ApplyInstanceTimeout            = '0'

    ApplyLagThreshold               = '0'

    TransportLagThreshold           = '0'

    TransportDisconnectedThreshold  = '30'

    ApplyParallel                   = 'AUTO'

    StandbyFileManagement           = 'AUTO'

    ArchiveLagTarget                = '0'

    LogArchiveMaxProcesses          = '4'

    LogArchiveMinSucceedDest        = '1'

    DbFileNameConvert               = '/ora-data/oradata/PEMDEV/sysdata, /ora-data/oradata/PEMDEV_STBY'

    LogFileNameConvert              = '/ora-data/oradata/PEMDEV/redolog, /ora-data/oradata/PEMDEV_STBY'

    FastStartFailoverTarget         = ''

    InconsistentProperties          = '(monitor)'

    InconsistentLogXptProps         = '(monitor)'

    SendQEntries                    = '(monitor)'

    LogXptStatus                    = '(monitor)'

    RecvQEntries                    = '(monitor)'

    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=EUNLOO12c)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PEMDEVSTBY_DGMGRL)(INSTANCE_NAME=PEMDEVSTBY)(SERVER=DEDICATED)))'

    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'

    AlternateLocation               = ''

    LogArchiveTrace                 = '0'

    LogArchiveFormat                = 'c%t_%s_%r.dbf'

    TopWaitEvents                   = '(monitor)'

Database Status:

SUCCESS

DGMGRL> edit database 'PEMDEVSTBY' SET PROPERTY 'LogXptMode' = 'SYNC';

Property "LogXptMode" updated

Changing the Protection Mode

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;

Succeeded.

DGMGRL> show database verbose 'PEMDEVSTBY';

Database - PEMDEVSTBY

  Role:               PHYSICAL STANDBY

  Intended State:     APPLY-ON

  Transport Lag:      0 seconds (computed 0 seconds ago)

  Apply Lag:          0 seconds (computed 0 seconds ago)

  Average Apply Rate: 2.00 KByte/s

  Active Apply Rate:  717.00 KByte/s

  Maximum Apply Rate: 1.38 MByte/s

  Real Time Query:    ON

  Instance(s):

    PEMDEVSTBY

  Properties:

    DGConnectIdentifier             = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=eunloo12c)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PEMDEVSTBY)(SERVER=DEDICATED)))'

    ObserverConnectIdentifier       = ''

    LogXptMode                      = 'SYNC'

    RedoRoutes                      = ''

    DelayMins                       = '0'

    Binding                         = 'optional'

    MaxFailure                      = '0'

    MaxConnections                  = '1'

    ReopenSecs                      = '300'

    NetTimeout                      = '30'

    RedoCompression                 = 'DISABLE'

    LogShipping                     = 'ON'

    PreferredApplyInstance          = ''

    ApplyInstanceTimeout            = '0'

    ApplyLagThreshold               = '0'

    TransportLagThreshold           = '0'

    TransportDisconnectedThreshold  = '30'

    ApplyParallel                   = 'AUTO'

    StandbyFileManagement           = 'AUTO'

    ArchiveLagTarget                = '0'

    LogArchiveMaxProcesses          = '4'

    LogArchiveMinSucceedDest        = '1'

    DbFileNameConvert               = '/ora-data/oradata/PEMDEV/sysdata, /ora-data/oradata/PEMDEV_STBY'

    LogFileNameConvert              = '/ora-data/oradata/PEMDEV/redolog, /ora-data/oradata/PEMDEV_STBY'

    FastStartFailoverTarget         = ''

    InconsistentProperties          = '(monitor)'

    InconsistentLogXptProps         = '(monitor)'

    SendQEntries                    = '(monitor)'

    LogXptStatus                    = '(monitor)'

    RecvQEntries                    = '(monitor)'

    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=EUNLOO12c)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PEMDEVSTBY_DGMGRL)(INSTANCE_NAME=PEMDEVSTBY)(SERVER=DEDICATED)))'

    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'

    AlternateLocation               = ''

    LogArchiveTrace                 = '0'

    LogArchiveFormat                = 'c%t_%s_%r.dbf'

    TopWaitEvents                   = '(monitor)'

Database Status:

SUCCESS

DGMGRL> show configuration;

Configuration - PEMDEV

  Protection Mode: MaxAvailability

  Members:

  PEMDEV     - Primary database

    PEMDEVSTBY - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS   (status updated 33 seconds ago)

DGMGRL>

DGMGRL> show database verbose 'PEMDEV';

Database - PEMDEV

  Role:               PRIMARY

  Intended State:     TRANSPORT-ON

  Instance(s):

    PEMDEV

  Properties:

    DGConnectIdentifier             = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=eunloo12c)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PEMDEV)(SERVER=DEDICATED)))'

    ObserverConnectIdentifier       = ''

    LogXptMode                      = 'ASYNC'

    RedoRoutes                      = ''

    DelayMins                       = '0'

    Binding                         = 'optional'

    MaxFailure                      = '0'

    MaxConnections                  = '1'

    ReopenSecs                      = '300'

    NetTimeout                      = '30'

    RedoCompression                 = 'DISABLE'

    LogShipping                     = 'ON'

    PreferredApplyInstance          = ''

    ApplyInstanceTimeout            = '0'

    ApplyLagThreshold               = '0'

    TransportLagThreshold           = '0'

    TransportDisconnectedThreshold  = '30'

    ApplyParallel                   = 'AUTO'

    StandbyFileManagement           = 'AUTO'

    ArchiveLagTarget                = '0'

    LogArchiveMaxProcesses          = '4'

    LogArchiveMinSucceedDest        = '1'

    DbFileNameConvert               = ''

    LogFileNameConvert              = ''

    FastStartFailoverTarget         = ''

    InconsistentProperties          = '(monitor)'

    InconsistentLogXptProps         = '(monitor)'

    SendQEntries                    = '(monitor)'

    LogXptStatus                    = '(monitor)'

    RecvQEntries                    = '(monitor)'

    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=EUNLOO12c)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PEMDEV_DGMGRL)(INSTANCE_NAME=PEMDEV)(SERVER=DEDICATED)))'

    StandbyArchiveLocation          = '/ora-arch/PEMDEV/archivelog'

    AlternateLocation               = ''

    LogArchiveTrace                 = '0'

    LogArchiveFormat                = '%t_%s_%r.dbf'

    TopWaitEvents                   = '(monitor)'

Database Status:

SUCCESS

Tried to Modify the ‘LogXptMode’ from SYNC to ASYNC when the database in Maximum Availabilty  Mode.

DGMGRL> edit database 'PEMDEVSTBY' SET PROPERTY 'LogXptMode' = 'ASYNC';

Error: ORA-16900: change of LogXptMode or RedoRoutes property violates overall protection mode

Modified the ‘LogXptMode’ from ASYNC to SYNC for the Primary DB ( affect spfile of Physical Standby)

DGMGRL> show database verbose 'PEMDEV';

Database - PEMDEV

  Role:               PRIMARY

  Intended State:     TRANSPORT-ON

  Instance(s):

    PEMDEV

  Properties:

    DGConnectIdentifier             = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=eunloo12c)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PEMDEV)(SERVER=DEDICATED)))'

    ObserverConnectIdentifier       = ''

    LogXptMode                      = 'ASYNC'

    RedoRoutes                      = ''

    DelayMins                       = '0'

    Binding                         = 'optional'

    MaxFailure                      = '0'

    MaxConnections                  = '1'

    ReopenSecs                      = '300'

    NetTimeout                      = '30'

    RedoCompression                 = 'DISABLE'

    LogShipping                     = 'ON'

    PreferredApplyInstance          = ''

    ApplyInstanceTimeout            = '0'

    ApplyLagThreshold               = '0'

    TransportLagThreshold           = '0'

    TransportDisconnectedThreshold  = '30'

    ApplyParallel                   = 'AUTO'

    StandbyFileManagement           = 'AUTO'

    ArchiveLagTarget                = '0'

    LogArchiveMaxProcesses          = '4'

    LogArchiveMinSucceedDest        = '1'

    DbFileNameConvert               = ''

    LogFileNameConvert              = ''

    FastStartFailoverTarget         = ''

    InconsistentProperties          = '(monitor)'

    InconsistentLogXptProps         = '(monitor)'

    SendQEntries                    = '(monitor)'

    LogXptStatus                    = '(monitor)'

    RecvQEntries                    = '(monitor)'

    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=EUNLOO12c)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PEMDEV_DGMGRL)(INSTANCE_NAME=PEMDEV)(SERVER=DEDICATED)))'

    StandbyArchiveLocation          = '/ora-arch/PEMDEV/archivelog'

    AlternateLocation               = ''

    LogArchiveTrace                 = '0'

    LogArchiveFormat                = '%t_%s_%r.dbf'

    TopWaitEvents                   = '(monitor)'

Database Status:

SUCCESS

DGMGRL> edit database 'PEMDEV' SET PROPERTY 'LogXptMode' = 'SYNC';

Property "LogXptMode" updated

DGMGRL> show database verbose 'PEMDEV';

Database - PEMDEV

  Role:               PRIMARY

  Intended State:     TRANSPORT-ON

  Instance(s):

    PEMDEV

  Properties:

    DGConnectIdentifier             = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=eunloo12c)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PEMDEV)(SERVER=DEDICATED)))'

    ObserverConnectIdentifier       = ''

    LogXptMode                      = 'SYNC'

    RedoRoutes                      = ''

    DelayMins                       = '0'

    Binding                         = 'optional'

    MaxFailure                      = '0'

    MaxConnections                  = '1'

    ReopenSecs                      = '300'

    NetTimeout                      = '30'

    RedoCompression                 = 'DISABLE'

    LogShipping                     = 'ON'

    PreferredApplyInstance          = ''

    ApplyInstanceTimeout            = '0'

    ApplyLagThreshold               = '0'

    TransportLagThreshold           = '0'

    TransportDisconnectedThreshold  = '30'

    ApplyParallel                   = 'AUTO'

    StandbyFileManagement           = 'AUTO'

    ArchiveLagTarget                = '0'

    LogArchiveMaxProcesses          = '4'

    LogArchiveMinSucceedDest        = '1'

    DbFileNameConvert               = ''

    LogFileNameConvert              = ''

    FastStartFailoverTarget         = ''

    InconsistentProperties          = '(monitor)'

    InconsistentLogXptProps         = '(monitor)'

    SendQEntries                    = '(monitor)'

    LogXptStatus                    = '(monitor)'

    RecvQEntries                    = '(monitor)'

    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=EUNLOO12c)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PEMDEV_DGMGRL)(INSTANCE_NAME=PEMDEV)(SERVER=DEDICATED)))'

    StandbyArchiveLocation          = '/ora-arch/PEMDEV/archivelog'

    AlternateLocation               = ''

    LogArchiveTrace                 = '0'

    LogArchiveFormat                = '%t_%s_%r.dbf'

    TopWaitEvents                   = '(monitor)'

Database Status:

SUCCESS