Oracle
Data Guard
Troubleshooting

ORA-16766: Redo Apply is stopped

ORA-16766

Without any correlated error is alert.log

Symptoms

From Broker (dgmgrl) using "show database orcl2"

Where orcl2 is the standby database...  Database Error(s):    ORA-16766: Redo Apply is stopped

Resolution

From Broker (dgmgrl)...

edit database orcl2 set state=APPLY-ON;

Where orcl2 is the standby database...

ORA-16766  with ORA-00600 [3020]

IMPORTANT: Be sure you are encountering exactly the same issue as the one described before following any resolution steps. Follow all steps at your own risk. If in any doubt, raise a ticket with Oracle. If the corrupted segment on the Primary is more important to you than an ASH partition in SYSAUX, raise a ticket with Oracle. The author offers no warranty and accepts no liability in respect of this information or its use.

Symptoms

Oracle Data Guard Standby on Oracle database 11.2.0.3 and AIX 7100-03-03-1415 

From Broker (dgmgrl)...

Configuration - my_dg_config
Protection Mode: MaxPerformanceDatabases:orcl1 - Primary databaseorcl2 - Physical standby databaseError: ORA-16766: Redo Apply is stopped
Fast-Start Failover: DISABLED
Configuration Status:ERROR

In alert.log (Standby)...

Thu Jun 03 07:18:01 2021Errors in file /u01/app/oracle/diag/rdbms/orcl2/ATLAS/trace/ORCL2_pr0g_28180694.trc (incident=5007783):ORA-00600: internal error code, arguments: [3020], [3], [806149], [13389061], [], [], [], [], [], [], [], []ORA-10567: Redo is inconsistent with data block (file# 3, block# 806149, file offset is 2309005312 bytes)ORA-10564: tablespace SYSAUXORA-01110: data file 3: '/u02/ORCL2/datafile/sysaux.269.732121993'ORA-10560: block type '0'Incident details in: /u01/app/oracle/diag/rdbms/orcl2/ORCL2/incident/incdir_5007783/ORCL2_pr0g_28180694_i5007783.trcUse ADRCI or Support Workbench to package the incident.See Note 411.1 at My Oracle Support for error and packaging details.Slave exiting with ORA-600 exceptionErrors in file /u01/app/oracle/diag/rdbms/orcl2/ORCL2/trace/ORCL2_pr0g_28180694.trc:ORA-00600: internal error code, arguments: [3020], [3], [806149], [13389061], [], [], [], [], [], [], [], []ORA-10567: Redo is inconsistent with data block (file# 3, block# 806149, file offset is 2309005312 bytes)ORA-10564: tablespace SYSAUXORA-01110: data file 3: '/u02/ORCL2/datafile/sysaux.269.732121993'ORA-10560: block type '0'Thu Jun 03 07:18:03 2021Errors in file /u01/app/oracle/diag/rdbms/orcl2/ORCL2/trace/ORCL2_mrp0_22347904.trc (incident=5007647):ORA-00600: internal error code, arguments: [3020], [3], [806149], [13389061], [], [], [], [], [], [], [], []ORA-10567: Redo is inconsistent with data block (file# 3, block# 806149, file offset is 2309005312 bytes)ORA-10564: tablespace SYSAUXORA-01110: data file 3: '/u02/ORCL2/datafile/sysaux.269.732121993'ORA-10560: block type '0'Incident details in: /u01/app/oracle/diag/rdbms/orcl2/ORCL2/incident/incdir_5007647/ORCL2_mrp0_22347904_i5007647.trcThu Jun 03 07:18:03 2021Dumping diagnostic data in directory=[cdmp_20210603071803], requested by (instance=1, osid=28180694 (PR0G)), summary=[incident=5007783].Thu Jun 03 07:18:03 2021Sweep [inc][5007783]: completedSweep [inc][5007647]: completedSweep [inc2][5007783]: completedUse ADRCI or Support Workbench to package the incident.See Note 411.1 at My Oracle Support for error and packaging details.Recovery Slave PR0G previously exited with exception 600 

Resolution

Temporarily stop any database backups, archivelog backups and snapshot standby automation (comment out in crontab.)

Our issue was previously known to be caused by a corruption of an ASH Partition on the Primary database instance. We confirm this is the same issue by passing the file# and block# number from the alert.log to this SQL on the Primary database instance (ORCL1)...

On the Standby database instance (ORCL2)... Disable the Data Guard Broker and mark corrupt blocks as corrupt...

ALTER SYSTEM SET dg_broker_start=false;

ALTER DATABASE RECOVER AUTOMATIC STANDBY DATABASE ALLOW 200 CORRUPTION;

Once the alert log indicates the blocks have been marked corrupt, we can Ctrl-C the recovery and restart managed recovery...

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Once recovery seems to be functioning normally (see Standby alert.log)...

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER SYSTEM SET dg_broker_start=TRUE;

use Broker to check status...

dgmgrl /

show configuration;

The Standby should now be back in sync.

Now we need to clear up the corruption on the Primary...

ALTER TABLE sys.wrh$_active_session_history TRUNCATE PARTITION wrh$_active_1569719909_117014;

Reinstate any database backups, archivelog backups and snapshot standby automation (uncomment in crontab).

ORA-16857: standby disconnected from redo... 

Symptoms

From Broker (dgmgrl) using "show database orcl2"

Where orcl2 is the standby database...ORA-16857: standby disconnected from redo source for longer than specified threshold

Alert.log shows...

FAL[client]: Failed to request gap sequence GAP - thread 1 sequence 25832-25931 DBID 2976126852 branch 1030433862FAL[client]: All defined FAL servers have been attempted.------------------------------------------------------------Check that the CONTROL_FILE_RECORD_KEEP_TIME initializationparameter is defined to a value that's sufficiently largeenough to maintain adequate log switch information to resolvearchivelog gaps.------------------------------------------------------------

Resolution

record the latest SCN applied to the Standby Database, then create an incremental Backup from that SCN on Primary Database using RMAN together with a Backup of the current Controlfile (as Standby Controlfile). 

Bibliography


https://support.oracle.com
  • Resolving ORA-00752 or ORA-600 [3020] During Standby Recovery (Doc ID 1265884.1)
  • ORA-19599 When backing up an archivelog that is corrupt (Doc ID 461239.1)
  • Resolving ORA-16857 on DGMGRL (Doc ID 2498020.1)
  • Data Guard Gap Detection and Resolution Possibilities (Doc ID 1537316.1)
  • Steps to perform for Rolling Forward a Physical Standby Database using RMAN Incremental Backup. (Doc ID 836986.1)
  • Rolling Forward a Physical Standby Using Recover From Service Command in 12c (Doc ID 1987763.1)