惜分飞

ORA-600 4194/ORA-600 4193/ORA-600 4137故障解决

发表于 2016 年 8 月 4 日由惜分飞

对于常见的undo异常错误,ORA-600 4193,ORA-600 4194,ORA-600 4137等错误的处理一般步骤.
适用版本

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.2.0.4 [Release 9.2 to 11.2]
Information in this document applies to any platform.

报错现象

The following error is occurring in the alert.log right before the database crashes.

ORA-00600: internal error code, arguments: [4194], [#], [#], [], [], [], [], []

This error indicates that a mismatch has been detected between redo records and rollback (undo) records.

ARGUMENTS:

Arg [a] - Maximum Undo record number in Undo block
Arg [b] - Undo record number from Redo block

Since we are adding a new undo record to our undo block, we would expect that the new record number
 is equal to the maximum record number in the undo block plus one. Before Oracle can add 
a new undo record to the undo block it validates that this is correct. If this validation fails,
 then an ORA-600 [4194] will be triggered.

报错原因

This also can be cause by the following defect

Bug 8240762 Abstract: Undo corruptions with ORA-600 [4193]/ORA-600 [4194] or ORA-600 [4137] after SHRINK

Details: 
Undo corruption may be caused after a shrink and the same undo block may be used 
for two different transactions causing several internal errors like:
ORA-600 [4193] / ORA-600 [4194] for new transactions
ORA-600 [4137] for a transaction rollback

处理步骤

Best practice to create a new undo tablespace.
This method includes segment check.

Create pfile from spfile to edit
>create pfile from spfile;

1. Shutdown the instance

2. set the following parameters in the pfile
    undo_management = manual
    event = '10513 trace name context forever, level 2'

3. >startup restrict pfile=<initsid.ora>

4. >select tablespace_name, status, segment_name from dba_rollback_segs where status != 'OFFLINE';

This is critical - we are looking for all undo segments to be offline - System will always be online.

If any are 'PARTLY AVAILABLE' or 'NEEDS RECOVERY' - Please open an issue with Oracle Support or update the current SR.

If all offline then continue to the next step

5. Create new undo tablespace - example
>create undo tablespace <new undo tablespace> datafile <datafile> size 2000M;

6. Drop old undo tablespace
>drop tablespace <old undo tablespace> including contents and datafiles;

7. >shutdown immediate;

8 >startup nomount;  --> Using your Original spfile

9 modify the spfile with the new undo tablespace name

  Alter system set undo_tablespace = '<new tablespace created in step 5>' scope=spfile;

10. >shutdown immediate;

11. >startup;  --> Using spfile
 


The reason we create a new undo tablespace first is to use new undo segment numbers
 that are higher then the current segments being used.
This way when a transaction goes to do block clean-out 
the reference to that undo segment does not exist and continues with the block clean-out.

参考：tep by step to resolve ORA-600 4194 4193 4197 on database crash (Doc ID 1428786.1)

发表在 Oracle备份恢复 | 标签为 ORA-600, ORA-600 4137, ORA-600 4193, ORA-600 4194, ORA-600恢复 | 评论关闭

ORA-600 3020

发表于 2016 年 8 月 4 日由惜分飞

ORA-600 3020官方解释说明

ERROR:
  Format: ORA-600 [3020] [a] [b] {c} [d] [e]

VERSIONS:
  version 6.0 and above

DESCRIPTION:

  This is called a 'STUCK RECOVERY'.

  There is an inconsistency between the information stored in the redo
  and the information stored in a database block being recovered.

ARGUMENTS:

For Oracle 9.2 and earlier:
  Arg [a] Block DBA
  Arg [b] Redo Thread
  Arg 1 Redo RBA Seq
  Arg [d] Redo RBA Block No
  Arg [e] Redo RBA Offset.

For Oracle 10.1
  Arg [a] Absolute file number of the datafile.
  Arg [b] Block number
  Arg 1 Block DBA

FUNCTIONALITY:
  kernel cache recovery parallel

IMPACT:
  INSTANCE FAILURE during recovery.

SUGGESTIONS:

  There have been cases of receiving this error when RECOVER has
  been issued, but either some datafiles were not restored to disk,
  or the restoration has not finished.

  Therefore, ensure that the entire backup has been restored and that
  the restore has finished PRIOR to issuing a RECOVER database command.

  If problems continue, consider restoring from a backup and doing a
  point-in-time recovery to a time PRIOR to the one implied by
  the ORA-600[3020] error.

  Example:

     SQL> recover database until time 'YYYY-MON-DD:HH:MI:SS';

  This error can also be caused by a lost update.

  During normal operations, block updates/writes are being performed to
  a number of files including database datafiles, redo log files,
  archived redo log files etc.

  This error can be reported if any of these updates are lost for some
  reason.

  Therefore, thoroughly check your operating system and disk hardware.

  In the case of a lost update, restore an old copy of the datafile and
  attempt to recover and roll forward again.

相关bug信息

NB	Prob	Bug	Fixed	Description
	III	16384983	11.2.0.4.6, 11.2.0.4.BP15, 12.1.0.2, 12.2.0.0	RMAN bad backup – causes recovery to fail with ORA-600 [3020]
	III	16057129	11.2.0.3.BP22, 11.2.0.4, 12.1.0.1.2, 12.1.0.2	Exadata cell optimized incremental backup can skip some blocks to backup
	I	22302666	12.2.0.0	ORA-753 ORA-756 or ORA-600 [3020] with KCOX_FUTURE after RMAN Restore / PITR with BCT after Open Resetlogs in 12c
	II	21629064	12.1.0.2.160719, 12.2.0.0	ORA-600 [3020] KCOX_FUTURE by RECOVERY for KTU UNDO BLOCK SEQ:254 sometime after RMAN Restore of UNDO datafile in Source Database
	III	20509482	11.2.0.4.BP20, 12.1.0.2.160119, 12.1.0.2.DBBP09, 12.2.0.0	ORA-600 [3020] / ORA-752 Wrong Results after Parallel Direct Load or RMAN ORA-600 [krcrfr_nohist] in RAC (caused by fix for bug 9962369)
	II	19445860	12.2.0.0	ORA-1172 or ORA-600 [3020] Stuck recovery in RAC after attempted block rebuild
	III	18284763	11.2.0.4.BP13, 12.1.0.2, 12.2.0.0	ORA-600 [3020] on ASSM blocks in Standby Database after CONVERT TO PHYSICAL or ORA-8103 ORA-600 [4552] in non-standby after FLASHBACK
	III	18268201	12.1.0.2, 12.2.0.0	ORA-600 [3020] ORA-10567 and ORA-10560: block type ’0′ / ORA-600 [kdBlkCheckError] ORA-600 [ktfbbset-2] after flashback which reversed a datafile extend – superseded
	I	16891789	11.2.0.4, 12.1.0.2, 12.2.0.0	ORA-600 [3020] or ORA-752 if db_lost_write_protect is enabled. Bystander standby recovers wrong redo log after switchover or failover.
+	III	17761775	11.2.0.3.9, 11.2.0.3.BP22, 11.2.0.4.2, 11.2.0.4.BP03, 12.1.0.1.3, 12.1.0.2	ORA-600 [kclchkblkdma_3] ORA-600 [3020] or ORA-600 [kcbchg1_16] Join of temp and permanent table in RAC might lead to corruption – superseded
	I	16844448	11.2.0.3.9, 11.2.0.3.BP22, 11.2.0.4, 12.1.0.2	ORA-600 [3020] after flashback database in a RAC
	III	15999982	11.2.0.3.BP24, 11.2.0.4, 12.1.0.2	ORA-600 [kcl_sanity_check_cr_1] ORA-600 [kclchkblkdma_3] in RAC or ORA-752 ORA-600 [3020] during recovery
	II	21425496		ORA-752 or ORA-600 [3020] on recovery of Block Cleanout Operation OP:4.6
	I	9847338		Session hang after applying the patch for Bug 9587912 which causes ORA-600 [3020]
+	III	13467683	11.2.0.2.9, 11.2.0.2.BP15, 11.2.0.3.3, 11.2.0.3.BP04, 11.2.0.4, 12.1.0.1	Join of temp and permanent tables in RAC might cause corruption of permanent table. Regression by bug 10352368
E	-	12831782	11.2.0.2.BP11, 11.2.0.3.BP01, 11.2.0.4, 12.1.0.1	ORA-600 [3020] / ORA-333 Recovery of datafile or async transport do not read mirror if there is a stale block
	II	12821418	11.2.0.3.8, 11.2.0.3.BP18, 11.2.0.4, 12.1.0.1	Direct NFS appears to be sending zero length windows to storage device. It may also cause Lost Writes
	I	12582839	11.2.0.3, 12.1.0.1	ORA-8103/ORA-600 [3020] on RMAN recovered locally managed tablespace
P	I	12330911	12.1.0.1	EXADATA LSI firmware for lost writes
	III	11689702	11.2.0.2.5, 11.2.0.2.BP13, 11.2.0.2.GIPSU05, 11.2.0.3, 12.1.0.1	ORA-600 [3020] during recovery after datafile RESIZE (to smaller size)
+	II	10425010	11.2.0.3, 12.1.0.1	Stale data blocks may be returned by Exadata FlashCache
	-	10329146	11.2.0.1.BP10, 11.2.0.2.2, 11.2.0.2.BP03, 11.2.0.2.GIBUNDLE02, 11.2.0.2.GIPSU02, 11.2.0.3, 12.1.0.1	Lost write in ASM with multiple DBWs and a disk is offlined and then onlined
	II	10218814	11.2.0.2.2, 11.2.0.2.BP02, 11.2.0.3, 12.1.0.1	ORA-600 [3020] during recovery / on standby
+	II	10209232	11.1.0.7.7, 11.2.0.1.BP08, 11.2.0.2.1, 11.2.0.2.BP02, 11.2.0.2.GIBUNDLE01, 11.2.0.3, 12.1.0.1	ORA-1578 / ORA-600 [3020] Corruption. Misplaced Blocks and Lost Write in ASM
*	III	10205230	11.2.0.1.6, 11.2.0.1.BP09, 11.2.0.2.2, 11.2.0.2.BP04, 11.2.0.3, 12.1.0.1	ORA-600 / corruption possible during shutdown in RAC
	II	10094823	11.2.0.2.4, 11.2.0.2.BP09, 11.2.0.3, 12.1.0.1	Block change tracking on physical standby can cause data loss
	-	10071193	11.2.0.2.BP02, 11.2.0.3, 12.1.0.1	Lost write / ORA-600 [kclchkblk_3] / ORA-600 [3020] in RAC – superceded
	-	9587912	11.2.0.2, 12.1.0.1	ORA-600 [3020] in datafile that went offline/online in a RAC instance
	-	8774868	11.2.0.1.2, 11.2.0.1.BP06, 11.2.0.2, 12.1.0.1	OERI[3020] reinstating primary
+	III	8769473	11.2.0.2, 12.1.0.1	ORA-600 [kcbzib_5] on multi block read in RAC. Invalid lock in RAC. ORA-600 [3020] in Recovery
P	II	8635179	10.2.0.5, 11.2.0.2, 12.1.0.1	Solaris: directio may be disabled for RAC file access. Corruption / Lost Write
+	II	8597106	11.2.0.1.BP06, 11.2.0.2, 12.1.0.1	Lost Write in ASM when normal redundancy is used
+E	II	17752121	11.2.0.3.9, 11.2.0.3.BP22, 11.2.0.4.2, 11.2.0.4.BP03	ORA-600 [kclchkblkdma_3] ORA-600 [3020] RAC diagnostic/fix to avoid a block being modified in Shared Mode and prevent corruption – Superseded
	III	8826708	10.2.0.5, 11.2.0.2	ORA-600 [3020] for block type 0x3a (58) during recovery for block restored by RMAN backup
	I	16579042	11.2.0.4	ORA-600 [kjbmpocr:alh] ORA-600 [kclchkblkdma_3] by LMS in RAC which may lead to corruption
	-	11684626	11.2.0.1	ORA-600 [3020] on standby involving “BRR” redo when db_lost_write_protect is enabled
	-	8230457	10.2.0.4.1, 10.2.0.5, 11.1.0.7.1, 11.2.0.1	Physical standby media recovery gets OERI[krr_media_12]
+	II	7680907	10.2.0.5, 11.1.0.7.1, 11.2.0.1	ORA-600 [kclexpandlock_2] in LMS / instance crash. Incorrect locks in RAC. ORA-600 [3020] in recovery
	II	4637668	10.2.0.3, 11.1.0.6	IMU transactions can produce out-of-order redo (OERI [3020] on recovery)
	-	4594917	9.2.0.8, 10.2.0.2, 11.1.0.6	Write IO error can cause incorrect file header checkpoint information
	-	4453449	10.2.0.2, 11.1.0.6	OERI:3020 / corruption errors from multiple FLASHBACK DATABASE
	III	7197445	10.2.0.4.1, 10.2.0.5	Standby Recovery session cancelled due to ORA-600 [3020] “CHANGE IN FUTURE OF BLOCK”
	II	5610267	10.2.0.5	MRP terminated by ORA-600[krr_media_12] / OERI:3020 after flashback
	-	3762714	9.2.0.7, 10.1.0.4, 10.2.0.1	ALTER DATABASE RECOVER MANAGED STANDBY fails with OERI[3020]
	I	3560209	10.2.0.1	OERI[3020] stuck recovery under RAC
	-	3397181	9.2.0.5, 10.1.0.3, 10.2.0.1	ALTER SYSTEM KILL SESSION of recovery slave causes stuck recovery
*	-	3381950	10.2.0.1	Backups from RAC DB before Data Guard Failover cannot be used
	-	3535712	9.2.0.6, 10.1.0.4	OERI[3020] / ORA-10567 from RAC with standby in max performance mode
	-	4594912	9.2.0.8, 10.1.0.2	Incorrect checkpoint possible in datafile headers
	-	3635331	9.2.0.6, 10.1.0.4	Stuck recovery (OERI:3020) / ORA-1172 on startup after a crash
	II	2322620	9.2.0.1	OERI:3020 possible on recovery of LOB DATA
P+	-	656370	7.3.3.4, 7.3.4.0, 8.0.3.0	AlphaNT only: Corrupt Redo (zeroed byte) OERI:3020

发表在 Oracle备份恢复 | 标签为 ORA-600 3020 | 评论关闭

ORA-600 kdsgrp1

发表于 2016 年 8 月 3 日由惜分飞

在硬件恢复,断电,redo异常等恢复case中ORA-600 [kdsgrp1]是一个比较常见的错误,这里该出来官方关于该错误的解释说明和处理方法

RROR:
  Format: ORA-600 [kdsgrp1]

VERSIONS:
  versions 10.1 and above



DESCRIPTION:

 This error was introduced in 10g with the fix to Bug 2442351, it provides
 for an extra health check on a block, we detected a null row header,
 see Note:2442351.9 for more information.

 Error may be caused by:

 Case 1. A row referenced in an index that does not exist in the table.
 Case 2. An non-existent rowid pointed to by a chained row.

 Trace Examples:

 Case 1. Mismatch between table and index:
====================================================

 Trace file has:

 row 02433566.13 continuation at
 file# 9 block# 210278 slot 20 not found

 The file=9 block=210278 is rdba=0x02433566 which was taken from an index:
 
 row#3[7549] flag: ------, lock: 0, len=85, data:(6):  02 43 35 66 00 14

 But the slot 20 does not exist in the table block:
 
 tab 0, row 1, @0x1e62
 tl: 2 fb: --HDFL-- lb: 0x3
 tab 0, row 12, @0x191a
 tl: 2 fb: --HDFL-- lb: 0x1
 tab 0, row 17, @0x1675
 tl: 2 fb: --HDFL-- lb: 0x2    
 tab 0, row 21, @0x1459       
 tl: 2 fb: --HDFL-- lb: 0x4

 ORA-1499 may be produced by analyze:

 analyze table <table name> validate structure cascade;

 Case 2. A row points to another rowid which does not exist (Chained row does not exist).
============================================================================================
 
 Trace file has:

 row 1186b11a.ffffffff continuation at
 file# 70 block# 441621 slot 1 not found 

 It means that row with rdba 0x1186b11a continues in file# 70 block# 441621 slot 1.  
 But the information in file# 70 block# 441621 slot 1 does not exist.  It is:

 tab 0, row 16, @0xd7f    ---> This is the slot with the problem.
 tl: 29 fb: -------- lb: 0x0  cc: 11
 nrid:  0x1186bd15.1      ---> It points to rdba=0x1186bd15 slot 1 
(file# 70 block# 441621 slot 1) but that row does not exist in that block.

 For this case ANALYZE TABLE .. VALIDATE STRUCTURE is not detecting this logical corruption
Referece Bug 6858313

Run an export (exp) or Full Table Scan to identify if there is a permanent invalid chained row.

 Workaround for Case 2:
 
 The row producing the ORA-600 [kdsgrp1] can be skipped by setting the Event 10231

 Note that a testcase has concluded that event 10231 does not skip rows in an Index Organized Table (IOT)
 when there is an invalid nrid as explained in Case 2.  It only works for regular tables.

 Event 43810 skip corrupt block in IOT?s (10.2.0.4) 
nor  parameter _index_scan_check_skip_corrupt (11g) work for this case 2 on IOTs either.

FUNCTIONALITY:
  Kernel Data layer Seek/Scan

IMPACT:

  PROCESS FAILURE
  POSSIBLE PHYSICAL CORRUPTION

ORA-600 4194/ORA-600 4193/ORA-600 4137故障解决

ORA-600 3020

ORA-600 kdsgrp1

文章分类

最近发表

ORA-600 4194/ORA-600 4193/ORA-600 4137故障解决

ORA-600 3020

ORA-600 kdsgrp1

标签云

文章分类

最近发表