Tag: dgmgrl

Database, Errors and Bugs, RAC | Data Guard

DG Broker ORA-16766: Redo Apply is stopped after a Database Restart

 , , ,

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

 

Database, Errors and Bugs, RAC | Data Guard

After Patch: MRP0: Background Media Recovery terminated with error 10485

 , , , , , ,

Ok,
I had that some time ago after applying Patch 27475598 – Oracle JavaVM Component 11.2.0.4.180417 Database PSU.
Why? Well, this is Non RAC-Rolling Installable and also Not Data Guard Standby First Installable.

This means there downtime for this patch, no escape.

I had to (skipping all the standard opatch steps, you can see those on README):

  • Stop DG Replication:
dgmgrl /
show configuration
show database mydg
edit database 'mydg' set state='apply-off';
show database mydg
  • Run postinstall.sql in upgrade mode with only 1 instance on (disable RAC):
cd $ORACLE_HOME/sqlpatch/27475598
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> alter system set cluster_database=false scope=spfile;
SQL> SHUTDOWN
SQL> STARTUP UPGRADE
SQL> @postinstall.sql
SQL> alter system set cluster_database=true scope=spfile;
SQL> SHUTDOWN
SQL> STARTUP

Ok, all good, seems all fine.

But now when starting my DG replication:

dgmgrl /
show configuration
show database mydg
edit database 'mydg' set state='apply-on';
show database mydg

What I see is:

DGMGRL> show database mydg

Database - mydg

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       41 minutes 53 seconds (computed 1 second ago)
  Apply Rate:      (unknown)
  Real Time Query: OFF
  Instance(s):
    myprod

  Database Error(s):
    ORA-16766: Redo Apply is stopped

Database Status:
ERROR

DGMGRL>

And on Database Alert Log:

MRP0: Background Media Recovery terminated with error 10485
Errors in file /u01/app/oracle/diag/rdbms/axwest/greporaprod/trace/greporaprod_pr00_42628.trc:
ORA-10485: Real-Time Query cannot be enabled while applying migration redo.
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
Recovery interrupted!
MRP0: Background Media Recovery process shutdown (greporaprod)

Well, in my case it happens because I use an Active Dataguard, in open read only. The solution? Start you DG in Mount Mode to apply the patching replication!

This is well described as per MOS: MRP process getting terminated with error ORA-10485 (Doc ID 1618485.1).

After getting sync, you can simple promote it to read only mode again.

Hope it helps!