top of page
Search
  • Writer's pictureSudipta Bhaskar

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

1 view0 comments

Recent Posts

See All

ORA-00845: MEMORY_TARGET not supported on this system

srvctl start asm fails with the following error. [oracle@sbstandby ~]$ srvctl start asm PRCR-1079 : Failed to start resource ora.asm CRS-2674: Start of 'ora.LISTENER.lsnr' on 'sbstandby' failed CRS-50

Comments


Post: Blog2_Post
bottom of page