【Oracle数据恢复】ORA-600[4194]错误一例

Comments

1. admin says

   2010/08/26 at 21:25

   Problem Description:
   ====================
   Your users cannot log onto the database. You see the following errors in
   the alert log: Ora-600 [4194], Ora-1578, and Ora-1110.

   ORA-600 [4194] [a] [b] [ ] [ ] [ ]
   Meaning: undo record number mismatch while adding an undo record to an undo
   block. This is done by the application of redo.

   ORA-01578: ORACLE data block corrupted (file # %s, block # %s)”
   Cause: The data block indicated was corrupted, mostly due to software
   errors.
   Action: Try to restore the segment containing the block indicated. This
   may involve dropping the segment and recreating it. If there
   is a trace file, report the errors in it to your ORACLE
   representative.

   ORA-01110: “data file %s: ‘%s'”
   Cause: Reporting file name for details of another error
   Action: See associated error message

   Problem Explanation:
   ====================

   Oracle is unable to roll back an uncommitted transaction in its transaction
   table. You have corruption in the rollback segment or corruption in an object
   which the rollback segment is trying to apply undo record.

   Problem References:
   ===================

   Note:39283.1 OERR: 600 4194 undo record number mismatch while adding undo
   record

   Search Words:
   =============

   CORRUPTION, ROLLBACK, SEGMENT, CORRUPT, NEEDS, RECOVERY, ORA-1545,
   Ora-600 [4137], Ora-600 4193]

   Solution Description:
   =====================

   In all cases, the ideal solution for a corrupted ROLLBACK SEGMENT is to
   restore your backup and do a point in time recovery to just prior to when
   the problem occurred.

   Solution Explanation:
   =====================

   Recover to a point in time before corruption occurred.

   PS:
   Hdr: 8240762 10.2.0.4 RDBMS 10.2.0.4 TXN MGMT LOCAL PRODID-5 PORTID-212 ORA-600
   Abstract: UNDO CORRUPTION DURING SHRINK CAUSES ORA-600 [4193]

   RELEASE NOTES:
   ]]Undo corruption during shrink causes ORA-600 [4193]

   REDISCOVERY INFORMATION:
   If you see an ORA-600 [4193] and the same undo block is used for two differen
   t transactions (due to some corruption in the Free Block Pool during shrink ope
   rations), then you may have hit this bug.
   WORKAROUND:
   None

   回复
2. admin says

   2010/08/26 at 21:27

   PURPOSE:
   This article discusses the internal error “ORA-600 [4194]”, what
   it means and possible actions. The information here is only applicable
   to the versions listed and is provided only for guidance.

   ERROR:
   ORA-600 [4194] [a] [b]

   VERSIONS:
   versions 6.0 to 10.1

   DESCRIPTION:

   A mismatch has been detected between Redo records and rollback (Undo)
   records.

   We are validating the Undo record number relating to the change being
   applied against the maximum undo record number recorded in the undo block.

   This error is reported when the validation fails.

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

   FUNCTIONALITY:
   Kernel Transaction Undo called from Cache layer

   IMPACT:
   PROCESS FAILURE
   POSSIBLE ROLLBACK SEGMENT CORRUPTION

   SUGGESTIONS:

   This error may indicate a rollback segment corruption.

   This may require a recovery from a database backup depending on
   the situation.

   If the Known Issues section below does not help in terms of identifying
   a solution, please submit the trace files and alert.log to Oracle
   Support Services for further analysis.

   回复
3. admin says

   2011/07/06 at 23:01

   xid: 0x00d4.02a.0007d2f0 –>> usn 212

   1- select segment_id, segment_name, status, file_id from dba_rollback_segs;

   Find the segment_id = 212 and take the segment_name of it and do:

   2- ALTER SYSTEM DUMP UNDO header ““;

   Upload the trace file generated and the output of the first query (in a txt file)

   回复
4. Ask_Maclean_liu_Oracle says

   2015/02/14 at 21:58

   APPLIES TO:Oracle Database – Enterprise Edition – Version 9.2.0.1 to 11.2.0.3 [Release 9.2 to 11.2]Information in this document applies to any platform.***Checked for relevance on 04-Dec-2013***SYMPTOMSThe 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 – Maximum Undo record number in Undo blockArg – Undo record number from Redo blockSince 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.CHANGESThis issue generally occurs when there is a power outage or hardware failure that initially crashes the database. On startup, the database does the normal roll forward (redo) and then rollback (undo), this is where the error is generated on the rollback.CAUSEThis also can be cause by the following defectBug 8240762 Abstract: Undo corruptions with ORA-600 [4193]/ORA-600 [4194] or ORA-600 [4137] after SHRINKDetails: 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 transactionsORA-600 [4137] for a transaction rollbackSOLUTIONBest 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 instance2. set the following parameters in the pfile undo_management = manual event = ‘10513 trace name context forever, level 2’3. >startup restrict pfile=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. There are many options from this moment and Oracle Support Analyst can offer different solutions for the bad undo segments.If all offline then continue to the next step5. Create new undo tablespace – example>create undo tablespace datafile size 2000M;6. Drop old undo tablespace>drop tablespace including contents and datafiles;7. >shutdown immediate;8 >startup mount;9 modify the pfile with the new undo tablespace name>alter system set undo_tablespace = ” scope=pfile;10. >shutdown immediate;11. >startup; Startup using the normal 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.

   回复
5. Ask_Maclean_liu_Oracle says

   2015/02/14 at 21:58

   APPLIES TO:Oracle Database – Enterprise Edition – Version 9.2.0.1 to 11.2.0.3 [Release 9.2 to 11.2]Information in this document applies to any platform.***Checked for relevance on 04-Dec-2013***SYMPTOMSThe 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 – Maximum Undo record number in Undo blockArg – Undo record number from Redo blockSince 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.CHANGESThis issue generally occurs when there is a power outage or hardware failure that initially crashes the database. On startup, the database does the normal roll forward (redo) and then rollback (undo), this is where the error is generated on the rollback.CAUSEThis also can be cause by the following defectBug 8240762 Abstract: Undo corruptions with ORA-600 [4193]/ORA-600 [4194] or ORA-600 [4137] after SHRINKDetails: 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 transactionsORA-600 [4137] for a transaction rollbackSOLUTIONBest 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 instance2. set the following parameters in the pfile undo_management = manual event = ‘10513 trace name context forever, level 2’3. >startup restrict pfile=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. There are many options from this moment and Oracle Support Analyst can offer different solutions for the bad undo segments.If all offline then continue to the next step5. Create new undo tablespace – example>create undo tablespace datafile size 2000M;6. Drop old undo tablespace>drop tablespace including contents and datafiles;7. >shutdown immediate;8 >startup mount;9 modify the pfile with the new undo tablespace name>alter system set undo_tablespace = ” scope=pfile;10. >shutdown immediate;11. >startup; Startup using the normal 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.

   回复