So, the other day I was engaged to check a dataguard, while checking its status with the dg broker I found this error
Error: ORA-16766: Redo Apply is stopped
Checking for it on dg broker:
DGMGRL> show configuration verbose Configuration - DGCONFIG Protection Mode: MaxPerformance Databases: PRIMARY - Primary database STANDBY - Physical standby database Error: ORA-16766: Redo Apply is stopped Properties: FastStartFailoverThreshold = '30' OperationTimeout = '30' FastStartFailoverLagLimit = '30' CommunicationTimeout = '180' ObserverReconnect = '0' FastStartFailoverAutoReinstate = 'TRUE' FastStartFailoverPmyShutdown = 'TRUE' BystandersFollowRoleChange = 'ALL' ObserverOverride = 'FALSE' ExternalDestination1 = '' ExternalDestination2 = '' PrimaryLostWriteAction = 'CONTINUE' Fast-Start Failover: DISABLED Configuration Status: ERROR GMGRL> show database verbose STANDBY Database - STANDBY Enterprise Manager Name: STANDBY Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 3 seconds ago) Apply Lag: 1 hour(s) 50 minutes 6 seconds (computed 0 seconds ago) Apply Rate: 828.00 KByte/s Real Time Query: OFF Instance(s): STANDBY Database Error(s): ORA-16766: Redo Apply is stopped Properties: DGConnectIdentifier = 'STANDBY' ObserverConnectIdentifier = '' LogXptMode = 'ASYNC' DelayMins = '0' Binding = 'optional' MaxFailure = '0' MaxConnections = '1' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'DISABLE' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyParallel = 'AUTO' StandbyFileManagement = 'AUTO' ArchiveLagTarget = '900' LogArchiveMaxProcesses = '4' LogArchiveMinSucceedDest = '1' DbFileNameConvert = '' LogFileNameConvert = '' FastStartFailoverTarget = '' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' SendQEntries = '(monitor)' LogXptStatus = '(monitor)' RecvQEntries = '(monitor)' ApplyLagThreshold = '0' TransportLagThreshold = '0' TransportDisconnectedThreshold = '30' SidName = 'STANDBY' StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SB_SERVER)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STANDBY_DGMGRL)(INSTANCE_NAME=STANDBY)(SERVER=DEDICATED)))' StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST' AlternateLocation = '' LogArchiveTrace = '0' LogArchiveFormat = 'STANDBY_%r_%t_%s.arc' TopWaitEvents = '(monitor)' Database Status: ERROR
The standby state was APPLY-ON but when checked the standby if the MRP process was running I found none!
sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 4 17:05:12 2019 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options 17:05:12 SYS@STANDBY AS SYSDBA> SELECT PROCESS, STATUS FROM v$managed_standby; PROCESS STATUS --------- ------------ ARCH CLOSING ARCH CLOSING ARCH CLOSING ARCH CLOSING RFS IDLE RFS IDLE RFS IDLE RFS WRITING 8 rows selected. Elapsed: 00:00:00.01
Checking further I found that the database was bounced while the MRP was running and that was the only explanation that I could see to see a difference between the dg broker also I could not find any error in the dg broker log $ORACLE_HOME/rdbms/log/drc*.log
After setting the state to apply-off and them to apply-on the issue was resolved.
DGMGRL> edit database STANDBY set state='apply-off'; Succeeded. DGMGRL> edit database STANDBY set state='apply-on'; Succeeded. DGMGRL> show database verbose STANDBY; Database - STANDBY Enterprise Manager Name: STANDBY Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 1 second ago) Apply Lag: 0 seconds (computed 1 second ago) Apply Rate: 0 Byte/s Real Time Query: OFF Instance(s): STANDBY Properties: DGConnectIdentifier = 'STANDBY' ObserverConnectIdentifier = '' LogXptMode = 'ASYNC' DelayMins = '0' Binding = 'optional' MaxFailure = '0' MaxConnections = '1' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'DISABLE' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyParallel = 'AUTO' StandbyFileManagement = 'AUTO' ArchiveLagTarget = '900' LogArchiveMaxProcesses = '4' LogArchiveMinSucceedDest = '1' DbFileNameConvert = '' LogFileNameConvert = '' FastStartFailoverTarget = '' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' SendQEntries = '(monitor)' LogXptStatus = '(monitor)' RecvQEntries = '(monitor)' ApplyLagThreshold = '0' TransportLagThreshold = '0' TransportDisconnectedThreshold = '30' SidName = 'STANDBY' StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SB_SERVER)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STANDBY_DGMGRL)(INSTANCE_NAME=STANDBY)(SERVER=DEDICATED)))' StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST' AlternateLocation = '' LogArchiveTrace = '0' LogArchiveFormat = 'STANDBY_%r_%t_%s.arc' TopWaitEvents = '(monitor)' Database Status: SUCCESS DGMGRL> show configuration Configuration - DGCONFIG Protection Mode: MaxPerformance Databases: PRIMARY - Primary database STANDBY - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS DGMGRL> exit sqlplus / as sysdba
17:17:54 SYS@STANDBY AS SYSDBA> SELECT PROCESS, STATUS FROM v$managed_standby; PROCESS STATUS --------- ------------ ARCH CLOSING ARCH CLOSING ARCH CLOSING ARCH CLOSING RFS IDLE RFS IDLE RFS IDLE RFS IDLE MRP0 APPLYING_LOG 9 rows selected. Elapsed: 00:00:00.00
Hope it helps, unti the next one.
Elisson Almeida