Sudipta Bhaskar
Apr 253 min
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