Data guard corruption real world scenario


Today i have encountered an issue with DR reporting db. we are running one of the production database in 11.2.0.4 which has DR in read only mode (Active data guard). Users pull near real time query. For some reason DR synch got issue for ~12 hours and I had to jump in to resolve the problem.

DR alert log -

Thu Dec 12 15:05:54 2019
Errors in file /u01/app/oracle/diag/rdbms/finstby/finstby/trace/finstby_pr03_3676.trc  (incident=224653):
ORA-00600: internal error code, arguments: [3020], [31], [203686], [130227110], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 31, block# 203686, file offset is 1668595712 bytes)
ORA-10564: tablespace AARNDX
ORA-01110: data file 31: '+DATA/finstby/datafile/aarndx.495.1005993653'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 442879
Incident details in: /u01/app/oracle/diag/rdbms/finstby/finstby/incident/incdir_224653/finstby_pr03_3676_i224653.trc
Use 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 exception
Errors in file /u01/app/oracle/diag/rdbms/finstby/finstby/trace/finstby_pr03_3676.trc:
ORA-00600: internal error code, arguments: [3020], [31], [203686], [130227110], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 31, block# 203686, file offset is 1668595712 bytes)
ORA-10564: tablespace AARNDX
ORA-01110: data file 31: '+DATA/finstby/datafile/aarndx.495.1005993653'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 442879
Thu Dec 12 15:06:03 2019
Errors in file /u01/app/oracle/diag/rdbms/finstby/finstby/trace/finstby_mrp0_3505.trc  (incident=224349):
ORA-00600: internal error code, arguments: [3020], [31], [203686], [130227110], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 31, block# 203686, file offset is 1668595712 bytes)
ORA-10564: tablespace AARNDX
ORA-01110: data file 31: '+DATA/finstby/datafile/aarndx.495.1005993653'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 442879
Incident details in: /u01/app/oracle/diag/rdbms/finstby/finstby/incident/incdir_224349/finstby_mrp0_3505_i224349.trc
Use ADRCI or Support Workbench to package the incident.

This shows datafile #31 got corrupted.

1) Verified if Primary has corruption on file#31

RMAN > validate datafile 31;

channel ORA_DISK_1: validation complete, elapsed time: 00:00:07
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
31   OK     0              30612        2176000         8157002009425
  File Name: +DATA/finprod/datafile/aarndx.570.981325053
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              2137202
  Other      0              8186

Finished validate at 12-DEC-19

RMAN> exit

None corrupted, good news!! - hence decided restore datafile 31 from primary to resolve the issue.

On Standby stop managed recovery
    dgmgrl > edit database 'finstby' set state='APPLY-OFF';


On Primary took a backup of the datafile involved.
     
      rman> Connect target /

      rman>  Backup datafile 31 format '/backup1_fincrp/x/tmp/%U_corrupt_dr' ;


Copied the backup file to DR /u01/app/oracle/DBA/umuj8ffp_1_1_corrupt_dr and registered into catalog

catalog start with '/u01/app/oracle/DBA';


RMAN> restore datafile  31;

restarted DR db in mount mode

DGMGRL> edit database 'finstby' set state='APPLY-ON';


DGMGRL> show database 'finstby';

Database - finstby

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 0 seconds ago)
  Apply Lag:       0 seconds (computed 0 seconds ago)
  Apply Rate:      696.00 KByte/s
  Real Time Query: ON
  Instance(s):
    finstby

Database Status:
SUCCESS

DGMGRL>

That's so cool... DR got in synch with primary. Notified users!!!

Happy Sharing!!