Oracle数据库文件损坏修复(断电情况下) 岁月无情

现场情况：
    1、数据库没有作归档，
    2、数据都存放在system表空间
    3、没有备份
状况：
    操作系统由于磁盘原因出现宕机，用户强行按电源关闭系统，数据库无法启动。
处理：
   

Sql代码

  1. SQL> recover database;   
  2. ORA-00283: recovery session canceled due to errors   
  3. ORA-12801: error signaled in parallel query server P002   
  4. ORA-10562: Error occurred while applying redo to data block (file# 1, block#4568)   
  5. ORA-10564: tablespace SYSTEM   
  6. ORA-01110: data file 1: '/opt/oracle/oradata/orcl/system01.dbf'  
  7. ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 576   
  8. ORA-00600: internal error code, arguments: [6101]   
  9.      

SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-12801: error signaled in parallel query server P002

ORA-10562: Error occurred while applying redo to data block (file# 1, block#4568)

ORA-10564: tablespace SYSTEM

ORA-01110: data file 1: '/opt/oracle/oradata/orcl/system01.dbf'

ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 576

ORA-00600: internal error code, arguments: [6101]

 


检查日志信息如下：

Oracle代码

  1. Mon Nov 19 15:38:50 2007  
  2. ALTER DATABASE RECOVER  database     
  3. Mon Nov 19 15:38:50 2007  
  4. Media Recovery Start   
  5.  parallel recovery started with 3 processes   
  6. Mon Nov 19 15:38:50 2007  
  7. Recovery of Online Redo Log: Thread 1 Group 3 Seq 16 Reading mem 0  
  8.   Mem# 0 errs 0: /opt/oracle/oradata/orcl/redo03.log   
  9. Mon Nov 19 15:38:50 2007  
  10. Errors in file /opt/oracle/admin/orcl/bdump/orcl_p002_7917.trc:   
  11. ORA-00600: internal error code, arguments: [6101], [0], [17], [0], [], [], [], []   
  12. Mon Nov 19 15:38:50 2007  
  13. Errors in file /opt/oracle/admin/orcl/bdump/orcl_p000_7913.trc:   
  14. ORA-00600: internal error code, arguments: [3020], [2], [882], [8389490], [], [], [], []   
  15. ORA-10567: Redo is inconsistent with data block (file# 2, block# 882)   
  16. ORA-10564: tablespace UNDOTBS1   
  17. ORA-01110: data file 2: '/opt/oracle/oradata/orcl/undotbs01.dbf'  
  18. ORA-10560: block type 'KTU UNDO BLOCK'  
  19. Mon Nov 19 15:38:51 2007  
  20. Errors in file /opt/oracle/admin/orcl/bdump/orcl_p000_7913.trc:   
  21. ORA-00600: internal error code, arguments: [3020], [2], [882], [8389490], [], [], [], []   
  22. ORA-10567: Redo is inconsistent with data block (file# 2, block# 882)   
  23. ORA-10564: tablespace UNDOTBS1   
  24. ORA-01110: data file 2: '/opt/oracle/oradata/orcl/undotbs01.dbf'  
  25. ORA-10560: block type 'KTU UNDO BLOCK'  
  26. Mon Nov 19 15:38:51 2007  
  27. Errors in file /opt/oracle/admin/orcl/bdump/orcl_p002_7917.trc:   
  28. ORA-10562: Error occurred while applying redo to data block (file# 1, block# 4568)   
  29. ORA-10564: tablespace SYSTEM   
  30. ORA-01110: data file 1: '/opt/oracle/oradata/orcl/system01.dbf'  
  31. ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 576  
  32. ORA-00600: internal error code, arguments: [6101], [0], [17], [0], [], [], [], []   
  33. Mon Nov 19 15:38:54 2007  
  34. Errors in file /opt/oracle/admin/orcl/bdump/orcl_p001_7915.trc:   
  35. ORA-00600: internal error code, arguments: [kddummy_blkchk], [1], [1658], [6101], [], [], [], []   
  36. Mon Nov 19 15:38:54 2007  
  37. Errors in file /opt/oracle/admin/orcl/bdump/orcl_p001_7915.trc:   
  38. ORA-10562: Error occurred while applying redo to data block (file# 1, block# 1658)   
  39. ORA-10564: tablespace SYSTEM   
  40. ORA-01110: data file 1: '/opt/oracle/oradata/orcl/system01.dbf'  
  41. ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 237  
  42. ORA-00607: Internal error occurred while making a change to a data block   
  43. ORA-00600: internal error code, arguments: [kddummy_blkchk], [1], [1658], [6101], [], [], [], []   
  44. Mon Nov 19 15:38:54 2007  
  45. Media Recovery failed with error 12801  
  46. ORA-283 signalled during: ALTER DATABASE RECOVER  database  ...  

Mon Nov 19 15:38:50 2007

ALTER DATABASE RECOVER  database 

Mon Nov 19 15:38:50 2007

Media Recovery Start

 parallel recovery started with 3 processes

Mon Nov 19 15:38:50 2007

Recovery of Online Redo Log: Thread 1 Group 3 Seq 16 Reading mem 0

  Mem# 0 errs 0: /opt/oracle/oradata/orcl/redo03.log

Mon Nov 19 15:38:50 2007

Errors in file /opt/oracle/admin/orcl/bdump/orcl_p002_7917.trc:

ORA-00600: internal error code, arguments: [6101], [0], [17], [0], [], [], [], []

Mon Nov 19 15:38:50 2007

Errors in file /opt/oracle/admin/orcl/bdump/orcl_p000_7913.trc:

ORA-00600: internal error code, arguments: [3020], [2], [882], [8389490], [], [], [], []

ORA-10567: Redo is inconsistent with data block (file# 2, block# 882)

ORA-10564: tablespace UNDOTBS1

ORA-01110: data file 2: '/opt/oracle/oradata/orcl/undotbs01.dbf'

ORA-10560: block type 'KTU UNDO BLOCK'

Mon Nov 19 15:38:51 2007

Errors in file /opt/oracle/admin/orcl/bdump/orcl_p000_7913.trc:

ORA-00600: internal error code, arguments: [3020], [2], [882], [8389490], [], [], [], []

ORA-10567: Redo is inconsistent with data block (file# 2, block# 882)

ORA-10564: tablespace UNDOTBS1

ORA-01110: data file 2: '/opt/oracle/oradata/orcl/undotbs01.dbf'

ORA-10560: block type 'KTU UNDO BLOCK'

Mon Nov 19 15:38:51 2007

Errors in file /opt/oracle/admin/orcl/bdump/orcl_p002_7917.trc:

ORA-10562: Error occurred while applying redo to data block (file# 1, block# 4568)

ORA-10564: tablespace SYSTEM

ORA-01110: data file 1: '/opt/oracle/oradata/orcl/system01.dbf'

ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 576

ORA-00600: internal error code, arguments: [6101], [0], [17], [0], [], [], [], []

Mon Nov 19 15:38:54 2007

Errors in file /opt/oracle/admin/orcl/bdump/orcl_p001_7915.trc:

ORA-00600: internal error code, arguments: [kddummy_blkchk], [1], [1658], [6101], [], [], [], []

Mon Nov 19 15:38:54 2007

Errors in file /opt/oracle/admin/orcl/bdump/orcl_p001_7915.trc:

ORA-10562: Error occurred while applying redo to data block (file# 1, block# 1658)

ORA-10564: tablespace SYSTEM

ORA-01110: data file 1: '/opt/oracle/oradata/orcl/system01.dbf'

ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 237

ORA-00607: Internal error occurred while making a change to a data block

ORA-00600: internal error code, arguments: [kddummy_blkchk], [1], [1658], [6101], [], [], [], []

Mon Nov 19 15:38:54 2007

Media Recovery failed with error 12801

ORA-283 signalled during: ALTER DATABASE RECOVER  database  ...


从上面信息中抓取了一个信息：

Oracle代码

  1. ORA-10562: Error occurred while applying redo to data block (file# 1, block# 1658)  

ORA-10562: Error occurred while applying redo to data block (file# 1, block# 1658)


针对这个错误解决如下：

Oracle代码

  1. ORA-10562: Error occurred while applying redo to data block (file# string, block# string)   
  2. Cause: See other errors on error stack.   
  3. Action: Investigate why the error occurred and how important is the data block. Media and standby database recovery usually can continue if user allows recovery to corrupt this data block。  

ORA-10562: Error occurred while applying redo to data block (file# string, block# string)

Cause: See other errors on error stack.

Action: Investigate why the error occurred and how important is the data block. Media and standby database recovery usually can continue if user allows recovery to corrupt this data block。


从日志信息可以基本判断问题如下：
当前在线日志损坏，导致undo回滚段出现问题，又由于系统突然掉电，系统表空间在重启实例后要进行实例恢复，当前在线日志损坏，系统表空间无法进行recover，因而出现了上面的错误。
找到了问题，解决访问就有了。
由于没有备份，数据库也运行在非归档模式下，所以恢复如下步骤：

Sql代码

  1. SQL>startup mount   
  2. SQL>recover database using backup controlfile until cancel;   
  3. SQL>alter database open resetlogs;   
  4. SQL> startup mount   
  5. SQL> alter system set “_allow_resetlogs_corruption”=true scope=spfile;   
  6. SQL>shutdown immediate   
  7. SQL> startup mount   
  8. SQL> alter database open resetlogs;   
  9. SQL> startup  

SQL>startup mount

SQL>recover database using backup controlfile until cancel;

SQL>alter database open resetlogs;

SQL> startup mount

SQL> alter system set “_allow_resetlogs_corruption”=true scope=spfile;

SQL>shutdown immediate

SQL> startup mount

SQL> alter database open resetlogs;

SQL> startup


基本恢复步骤：

Sql代码

  1. SQL>startup mount   
  2. SQL>recover database using backup controlfile until cancel;   
  3. Cancel   
  4. SQL>alter database open resetlogs;   
  5. #此时会提示system表空间需要恢复，但是由于当前日志损坏，无法进行恢复，所以需要加入#隐含参数，oracle才不会监测scn的一致性，才能打开数据库。   
  6. #重启数据库加入隐含参数   
  7. SQL> startup mount   
  8. SQL> alter system set “_allow_resetlogs_corruption”=true scope=spfile;   
  9. SQL> shutdown immediate   
  10. SQL> startup mount   
  11. SQL> alter database open resetlogs;   
  12. alter database open resetlogs   
  13. *   
  14. ERROR at line 1:   
  15. ORA-01092: ORACLE instance terminated. Disconnection forced   
  16. #不管这些，再次登录sqlplus起动数据库   
  17. SQL*Plus: Release 10.2.0.1.0 - Production on Fri Nov 16 08:03:43 2007   
  18. Copyright (c) 1982, 2005, Oracle.  All rights reserved.   
  19. Connected to:   
  20. Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production   
  21. With the Partitioning, OLAP and Data Mining options   
  22. SQL>startup  

SQL>startup mount

SQL>recover database using backup controlfile until cancel;

Cancel

SQL>alter database open resetlogs;

#此时会提示system表空间需要恢复，但是由于当前日志损坏，无法进行恢复，所以需要加入#隐含参数，oracle才不会监测scn的一致性，才能打开数据库。

#重启数据库加入隐含参数

SQL> startup mount

SQL> alter system set “_allow_resetlogs_corruption”=true scope=spfile;

SQL> shutdown immediate

SQL> startup mount

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

#不管这些，再次登录sqlplus起动数据库

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Nov 16 08:03:43 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL>startup


数据库正常打开，exp备份需要的数据，然后重新建库，导入数据。

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/huyinyang/p/3023262.html