Search This Blog

Tuesday, February 2, 2016

"ORA-10567: Redo is inconsistent with data block" on Physical Standby 11g

Today is a wonderful day. It's cool and sunny. We are in Spring. But I don't want to talk about the weather at this moment. I am sipping a cup of coffee and typing a note on my Oracle Database experience yesterday.
I have a Dataguard 11g using physical standby. After a long active time and some end of year batch jobs the size of UNDO tablespace so large. The result from checking show that the usage space is very little. So I decide resize the datafile of UNDO tablespace. The commands are success. And the file has a smaller size.
But on the Standby Database, the archivelogs are still recieved from 
Primary Database and cannot be applied. The alert log show the error:
Recovered data files to a consistent state at change 1443961551
Errors in file /oracle/app/diag/rdbms/proddr/PRODDR/trace/PRODDR_pr00_2930.trc:
ORA-00448: normal completion of background process
Errors in file /oracle/app/diag/rdbms/proddr/PRODDR/trace/PRODDR_mrp0_2918.trc:
ORA-00600: internal error code, arguments: [3020], [6], [11627], [25177451], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block
(file# 6, block# 11627, file offset is 95248384 bytes)
ORA-10564: tablespace UNDOTBS1
ORA-01110: data file 6:
'/oradata/PRODDR/undotbs02.dbf'
ORA-10560: block type 'KTU UNDO BLOCK'
MRP0: Background Media Recovery process shutdown (PRODDR)

I have faced the lost of sync on Standby Database before. I have resolved by guide on http://arup.blogspot.com/2009/12/resolving-gaps-in-data-guard-apply.html. But yesterday's error is different. The following are steps that I performed to fix the error.

Check the SCN on Primary:
SQL> select current_scn, scn_to_timestamp(current_scn) from v$database;
CURRENT_SCN   SCN_TO_TIMESTAMP(CURRENT_SCN)
-----------   ---------------------------------------------------------------------------
 1447116920     02-FEB-16 10.43.58.000000000 AM

Check the SCN on Standby:
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
 1446590956

SQL> select scn_to_timestamp(1446590956) from dual;
SCN_TO_TIMESTAMP(1446590956)
---------------------------------------------------------------------------
02-FEB-16 09.26.47.000000000 AM

Check archivelog on Standby: logs are transfered but cannot be applied
SQL> select dest_id, sequence#, applied, deleted, to_char(first_time,'dd/mm/yyyy hh24:mi:ss') first_time
  2  from (
  3    select dest_id, first_time, sequence#, applied, deleted from v$archived_log order by first_time desc, dest_id
  4  )
  5  where rownum<=5
  6  ;
   DEST_ID  SEQUENCE# S APPLIED   DEL FIRST_TIME
---------- ---------- - --------- --- -------------------
         1      19220 A NO        NO  02/02/2016 10:30:18
         1      19219 A NO        NO  02/02/2016 10:13:53
         1      19218 A NO        NO  02/02/2016 09:56:23
         1      19217 A NO        NO  02/02/2016 09:39:35
         1      19216 A NO        NO  02/02/2016 09:30:16

Check process on Standby: MRP-apply process not running
SQL> select process, status from v$managed_standby;
PROCESS   STATUS
--------- ------------
ARCH      CLOSING
ARCH      CLOSING
ARCH      CONNECTED
ARCH      CLOSING
RFS       IDLE
RFS       IDLE

On Primary: backup datafile 6 as shown in the alertlog & copy to Standby host:
rman> backup format '/BACKUP/undofile2%U' datafile 6
# scp /BACKUP/undofile2d8qsrpml_1_1 oracle@standby-host:/BACKUP/

On Standby: catalog the new backuppiece; shutdown, mount DB and restore datafile 6:
rman> catalog start with '/BACKUP/undofile2d8qsrpml_1_1'
alter database recover managed standby database cancel
shutdown immediate;
startup mount;
RMAN> restore datafile 6; Starting restore at 01-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=577 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=768 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=959 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to /oradata/PRODDR/undotbs02.dbf
channel ORA_DISK_1: reading from backup piece /BACKUP/undofile2d8qsrpml_1_1
channel ORA_DISK_1: piece handle=/BACKUP/undofile2d8qsrpml_1_1 tag=TAG20160201T181341
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
Finished restore at 01-FEB-16

Starting apply process:
SQL> alter database recover managed standby database using current logfile disconnect from session

Now you can re-check the archivelog & MRP process on Standby as mentioned above.
Then taking a cup of coffe as the reward after a work hard.


Untimately, I want to say thank you to Bhavani and Arup Nanda. Yours' post help me to solve problems.
Related Posts with Thumbnails