How to Convert Physical Standby To Active DataGuard

Standby database

SQL> select open_mode , protection_mode , database_role from v$database;

OPEN_MODE      PROTECTION_MODE   DATABASE_ROLE
-------------------- -------------------- ----------------
MOUNTED       MAXIMUM PERFORMANCE  PHYSICAL STANDBY

Let's check the current Config and try to change the mode.

[oracle@sbprimary ~]$ dgmgrl /

DGMGRL for Linux: Release 19.0.0.0.0 - Production on Fri Apr 26 00:10:16 2024

Version 19.19.0.0.0

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

Welcome to DGMGRL, type "help" for information.

Connected to "DBASM"

Connected as SYSDG.

DGMGRL> show configuration

Configuration - DBASM

  Protection Mode: MaxPerformance

  Members:

  DBASM   - Primary database

    DBASMDR - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:

SUCCESS   (status updated 4 seconds ago)

DGMGRL> show database DBASMDR

Database - DBASMDR

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

  Real Time Query:    OFF

  Instance(s):

    DBASMDR

Database Status:

SUCCESS

DGMGRL> EDIT DATABASE 'DBASMDR' SET STATE='READ-ONLY' WITH APPLY INSTANCE=DBASMDR

> ;

Error: ORA-16516: current state is invalid for the attempted operation

Failed.

One thing to note above.

Real Time Query:    OFF

Let's try to fix this.

DGMGRL> edit database 'DBASMDR' set state='APPLY-OFF';

Succeeded.

In Standby

SQL> alter database open read only;

Database altered.

DGMGRL> edit database 'DBASMDR' set state='APPLY-ON';

Succeeded.

After this, Real time Query is ON

DGMGRL> show database verbose DBASMDR

Database - DBASMDR

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

  Active Apply Rate:  0 Byte/s

  Maximum Apply Rate: 0 Byte/s

  Real Time Query:    ON

  Instance(s):

    DBASMDR

  Properties:

    DGConnectIdentifier             = 'dbasmdr'

    ObserverConnectIdentifier       = ''

    FastStartFailoverTarget         = ''

    PreferredObserverHosts          = ''

    LogShipping                     = 'ON'

    RedoRoutes                      = ''

    LogXptMode                      = 'ASYNC'

    DelayMins                       = '0'

    Binding                         = 'optional'

    MaxFailure                      = '0'

    ReopenSecs                      = '300'

    NetTimeout                      = '30'

    RedoCompression                 = 'DISABLE'

    PreferredApplyInstance          = ''

    ApplyInstanceTimeout            = '0'

    ApplyLagThreshold               = '0'

    TransportLagThreshold           = '0'

    TransportDisconnectedThreshold  = '0'

    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                        = 'sbstandby.localdomain.com'

    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.38.4.111)(PORT=1539))(CONNECT_DATA=(SERVICE_NAME=DBASMDR_DGMGRL)(INSTANCE_NAME=DBASMDR)(SERVER=DEDICATED)))'

    TopWaitEvents                   = '(monitor)'

    SidName                         = '(monitor)'

  Log file locations:

    Alert log               : /u02/app/orabase/diag/rdbms/dbasmdr/DBASMDR/trace/alert_DBASMDR.log

    Data Guard Broker log   : /u02/app/orabase/diag/rdbms/dbasmdr/DBASMDR/trace/drcDBASMDR.log

Database Status:

SUCCESS

Now the physical standby has been converted to ADG.

SQL>  select open_mode , protection_mode , database_role from v$database;

OPEN_MODE      PROTECTION_MODE   DATABASE_ROLE

-------------------- -------------------- ----------------

READ ONLY WITH APPLY MAXIMUM PERFORMANCE  PHYSICAL STANDBY