Disable Active DataGuard

How to disable the active dataguard in the database, I know if we use active dataguard we have to pay extra license but sometimes if we start the physical standby db with "startup"command then inadvertently active dataguard feature gets enabled.

Solution

The active data guard would be enabled automatically when starting a physical standby and the DG broker is used. 

There is an unofficially way to disable using a hidden parameter, which is _query_on_physical=false

Since it is a underscore parameter, you will have to contact Oracle Support before using it. 

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