实例异常中断后重启动数据库遇到ora-00600[3705]错误不能打开数据的案例

问题描述：

一个测试库 实例异常关闭，重新启动后，出现下列错误：

点击(此处)折叠或打开

1. alter database mount exclusive
2. Informational message:
3. Control file 2 has seq# 1476472, lowest 1476471 file 0
4. Setting recovery target incarnation to 2
5. Thu Jun 02 13:51:39 2011
6. Successful mount of redo thread 1, with mount id 2441615654
7. Thu Jun 02 13:51:39 2011
8. Database mounted in Exclusive Mode
9. Completed: alter database mount exclusive
10. Thu Jun 02 13:51:39 2011
11. alter database open
12. Thu Jun 02 13:53:16 2011
13. Beginning crash recovery of 1 threads
14.  parallel recovery started with 3 processes
15. Thu Jun 02 13:53:42 2011
16. Started redo scan
17. Thu Jun 02 13:53:44 2011
18. Completed redo scan
19.  66 redo blocks read, 40 data blocks need recovery
20. Thu Jun 02 13:53:45 2011
21. Started redo application at
22.  Thread 1: logseq 57351, block 73821, scn 4501380149522
23. Thu Jun 02 13:53:46 2011
24. Recovery of Online Redo Log: Thread 1 Group 3 Seq 57351 Reading mem 0
25.   Mem# 0: F:\ORADATA\DBS101\REDO03.LOG
26. Thu Jun 02 13:53:47 2011
27. Recovery of Online Redo Log: Thread 1 Group 1 Seq 57352 Reading mem 0
28.   Mem# 0: F:\ORADATA\DBS101\REDO01.LOG
29. Thu Jun 02 13:53:47 2011
30. Completed redo application
31. Thu Jun 02 13:53:47 2011
32. Completed crash recovery at
33.  Thread 1: logseq 57352, block 68, scn 4501380178792
34.  40 data blocks read, 40 data blocks written, 66 redo blocks read
35. Thu Jun 02 13:54:59 2011
36. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_lgwr_2548.trc:
37. ORA-00600: internal error code, arguments: [3705], [1], [1], [3], [8], [], [], []
39. Thu Jun 02 13:55:01 2011
40. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_lgwr_2548.trc:
41. ORA-00600: internal error code, arguments: [3705], [1], [1], [3], [8], [], [], []
43. Thu Jun 02 13:55:01 2011
44. LGWR: terminating instance due to error 470
45. Thu Jun 02 13:55:01 2011
46. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_psp0_2516.trc:
47. ORA-00470: LGWR process terminated with error
49. Thu Jun 02 13:55:02 2011
50. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_mman_2524.trc:
51. ORA-00470: LGWR process terminated with error
53. Thu Jun 02 13:55:27 2011
54. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_dbw0_2544.trc:
55. ORA-00470: LGWR process terminated with error
57. Thu Jun 02 13:55:27 2011
58. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_ckpt_2556.trc:
59. ORA-00470: LGWR process terminated with error
61. Thu Jun 02 13:55:27 2011
62. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_pmon_2504.trc:
63. ORA-00470: LGWR process terminated with error
65. Thu Jun 02 13:55:27 2011
66. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_reco_2572.trc:
67. ORA-00470: LGWR process terminated with error
69. Thu Jun 02 13:55:28 2011
70. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_smon_2564.trc:
71. ORA-00470: LGWR process terminated with error
73. Thu Jun 02 13:55:37 2011
74. Instance terminated by LGWR, pid = 2548

问题分析；

从alert日志看是在实例恢复时，数据库执行在线重做日志切换，LGWR进程遇到ORA-00600
错误。由于这是测试数据库，允许丢失重做日志文件中的事务。决定清楚在线日志文件中
的内容。

执行下列语句：

点击(此处)折叠或打开

1. alter database clear logfile 'F:\ORADATA\DBS101\REDO03.LOG'
   
2. alter database clear logfile 'F:\ORADATA\DBS101\REDO02.LOG'
   
3. alter database clear logfile 'F:\ORADATA\DBS101\REDO01.LOG'

成功清除了REDO02.LOG和REDO03.LOG中的内容，但是当执行到
F:\ORADATA\DBS101\REDO01.LOG在线重做日志文件时，出现了ORA-00600的错误。可以判断
REDO01.LOG文件有问题。

点击(此处)折叠或打开

1. ---------------------------------------------------------------------------------------
2. Thu Jun 02 14:44:28 2011
3. alter database clear logfile 'F:\ORADATA\DBS101\REDO03.LOG'
4. Thu Jun 02 14:44:29 2011
5. Thread 1 advanced to log sequence 57352 (preparation to clear logfile)
6. Clearing online log 3 of thread 1 sequence number 57351
7. Completed: alter database clear logfile 'F:\ORADATA\DBS101\REDO03.LOG'
8. Thu Jun 02 14:44:33 2011
9. alter database clear logfile 'F:\ORADATA\DBS101\REDO02.LOG'
10. Thu Jun 02 14:44:33 2011
11. Thread 1 advanced to log sequence 57353 (preparation to clear logfile)
12. Clearing online log 2 of thread 1 sequence number 57352
13. Completed: alter database clear logfile 'F:\ORADATA\DBS101\REDO02.LOG'
14. Thu Jun 02 14:44:34 2011
15. alter database clear logfile 'F:\ORADATA\DBS101\REDO01.LOG'
16. Clearing online log 1 of thread 1 sequence number 57352
17. Thu Jun 02 14:44:34 2011
18. Errors in file f:\oracle\product\10.2.0\admin\DBS101\udump\DBS101_ora_3572.trc:
19. ORA-00600: internal error code, arguments: [2768], [1], [], [], [], [], [], []
21. ORA-600 signalled during: alter database clear logfile 'F:\ORADATA\DBS101\REDO01.LOG'...
22. Thu Jun 02 14:45:20 2011
23. alter database open
24. Thu Jun 02 14:45:41 2011
25. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_lgwr_4092.trc:
26. ORA-00600: internal error code, arguments: [3705], [1], [1], [3], [24], [], [], []
28. Thu Jun 02 14:45:42 2011
29. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_lgwr_4092.trc:
30. ORA-00600: internal error code, arguments: [3705], [1], [1], [3], [24], [], [], []
32. Thu Jun 02 14:45:42 2011
33. LGWR: terminating instance due to error 470
34. Thu Jun 02 14:45:42 2011
35. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_dbw0_3880.trc:
36. ORA-00470: LGWR process terminated with error
38. Thu Jun 02 14:45:43 2011
39. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_pmon_2904.trc:
40. ORA-00470: LGWR process terminated with error
42. Thu Jun 02 14:45:43 2011
43. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_reco_1560.trc:
44. ORA-00470: LGWR process terminated with error
46. Thu Jun 02 14:45:43 2011
47. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_smon_1536.trc:
48. ORA-00470: LGWR process terminated with error
50. Thu Jun 02 14:45:43 2011
51. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_ckpt_2184.trc:
52. ORA-00470: LGWR process terminated with error
54. Thu Jun 02 14:45:48 2011
55. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_psp0_3096.trc:
56. ORA-00470: LGWR process terminated with error
58. Thu Jun 02 14:45:48 2011
59. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_mman_3964.trc:
60. ORA-00470: LGWR process terminated with error
62. Instance terminated by LGWR, pid = 4092

然后先增加一组在线日志文件。再尝试open数据库。结果在日志文件中报如下的错误。

点击(此处)折叠或打开

1. ALTER DATABASE
   
2.   ADD LOGFILE GROUP 4 ('F:\ORADATA\DBS101\REDO04.LOG')
   
3.       SIZE 50M;

点击(此处)折叠或打开

1. ---------------------------------------------------------------------------------
2. Successful mount of redo thread 1, with mount id 2441586173
3. Thu Jun 02 14:46:26 2011
4. Database mounted in Exclusive Mode
5. Completed: ALTER DATABASE   MOUNT
6. Thu Jun 02 14:48:34 2011
7. ALTER DATABASE
8.   ADD LOGFILE GROUP 4 ('F:\ORADATA\DBS101\REDO04.LOG')
9.       SIZE 50M
10. Thu Jun 02 14:48:34 2011
11. Completed: ALTER DATABASE
12.   ADD LOGFILE GROUP 4 ('F:\ORADATA\DBS101\REDO04.LOG')
13.       SIZE 50M
14. Thu Jun 02 14:48:40 2011
15. alter database open
16. Thu Jun 02 14:49:02 2011
17. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_lgwr_1724.trc:
18. ORA-00600: internal error code, arguments: [3705], [1], [1], [3], [24], [], [], []
20. Thu Jun 02 14:49:03 2011
21. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_lgwr_1724.trc:
22. ORA-00600: internal error code, arguments: [3705], [1], [1], [3], [24], [], [], []
24. Thu Jun 02 14:49:03 2011
25. LGWR: terminating instance due to error 470
26. Thu Jun 02 14:49:03 2011
27. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_dbw0_2012.trc:
28. ORA-00470: LGWR process terminated with error
30. Thu Jun 02 14:49:03 2011
31. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_psp0_2016.trc:
32. ORA-00470: LGWR process terminated with error
34. Thu Jun 02 14:49:03 2011
35. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_mman_3920.trc:
36. ORA-00470: LGWR process terminated with error
38. Thu Jun 02 14:49:03 2011
39. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_pmon_2616.trc:
40. ORA-00470: LGWR process terminated with error
42. Thu Jun 02 14:49:04 2011
43. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_reco_2324.trc:
44. ORA-00470: LGWR process terminated with error
46. Thu Jun 02 14:49:04 2011
47. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_smon_1556.trc:
48. ORA-00470: LGWR process terminated with error
50. Thu Jun 02 14:49:04 2011
51. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_ckpt_1296.trc:
52. ORA-00470: LGWR process terminated with error
54. Instance terminated by LGWR, pid = 1724

-------------------------------------------------------------------

再查看V$LOG中在线日志文件的状态。REDO01.LOG文件的状态为CLEARING_CURRENT。
CLEARING_CURRENT - Current log is being cleared of a closed thread. The log can
stay in this status if there is some failure in the switch such as an I/O error
writing the new log header。
由上面的解释，REDO01.LOG可能存在了IO问题。

先删除REDO01.LOG文件。

点击(此处)折叠或打开

1. ALTER DATABASE DROP LOGFILE GROUP 1

再尝试打开数据库。仍然出现错误ORA-00600。

-------------------------------------------------------------------

点击(此处)折叠或打开

1. Completed: ALTER DATABASE   MOUNT
2. Thu Jun 02 14:50:40 2011
3. ALTER DATABASE DROP LOGFILE GROUP 1
4. Thu Jun 02 14:50:40 2011
5. Completed: ALTER DATABASE DROP LOGFILE GROUP 1
6. Thu Jun 02 14:50:56 2011
7. alter database open
8. Thu Jun 02 14:51:17 2011
9. Thread 1 advanced to log sequence 57354 (thread open)
10. Thu Jun 02 14:51:31 2011
11. Thread 1 opened at log sequence 57354
12.   Current log# 2 seq# 57354 mem# 0: F:\ORADATA\DBS101\REDO02.LOG
13. Successful open of redo thread 1
14. Thu Jun 02 14:51:31 2011
15. MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
16. Thu Jun 02 14:51:31 2011
17. SMON: enabling cache recovery
18. Thu Jun 02 14:51:32 2011
19. Errors in file f:\oracle\product\10.2.0\admin\DBS101\udump\DBS101_ora_1984.trc:
20. ORA-00600: internal error code, arguments: [2662], [1048], [254423375], [1048], [254423428], [4194313], [], []
22. Thu Jun 02 14:51:34 2011
23. Errors in file f:\oracle\product\10.2.0\admin\DBS101\udump\DBS101_ora_1984.trc:
24. ORA-00600: internal error code, arguments: [2662], [1048], [254423375], [1048], [254423428], [4194313], [], []
26. Thu Jun 02 14:51:34 2011
27. Error 600 happened during db open, shutting down database
28. USER: terminating instance due to error 600
29. Thu Jun 02 14:51:34 2011
30. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_pmon_2808.trc:
31. ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
33. Thu Jun 02 14:51:34 2011
34. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_reco_4024.trc:
35. ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
37. Thu Jun 02 14:51:34 2011
38. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_mman_3696.trc:
39. ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
41. Thu Jun 02 14:51:34 2011
42. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_psp0_3688.trc:
43. ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
45. Thu Jun 02 14:51:35 2011
46. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_dbw0_2816.trc:
47. ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
49. Thu Jun 02 14:51:36 2011
50. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_ckpt_3616.trc:
51. ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
53. Thu Jun 02 14:51:37 2011
54. Errors in file f:\oracle\product\10.2.0\admin\DBS101\bdump\DBS101_lgwr_2696.trc:
55. ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
57. Thu Jun 02 14:52:20 2011
58. Instance terminated by USER, pid = 1984

--------------------------------------------------------------------------
但是alert日志中有一行信息
  Current log# 2 seq# 57354 mem# 0: F:\ORADATA\DBS101\REDO02.LOG
这表明数据库可以成功切换一组重做在线日志，已经克服了REDO01.DBF的问题。

点击(此处)折叠或打开

1. ALTER DATABASE   MOUNT
2. Thu Jun 02 14:53:12 2011
3. Setting recovery target incarnation to 2
4. Thu Jun 02 14:53:13 2011
5. Successful mount of redo thread 1, with mount id 2441606548
6. Thu Jun 02 14:53:13 2011
7. Database mounted in Exclusive Mode
8. Completed: ALTER DATABASE   MOUNT
9. Thu Jun 02 15:00:36 2011
10. alter database open
11. Thu Jun 02 15:00:57 2011
12. Beginning crash recovery of 1 threads
13.  parallel recovery started with 3 processes
14. Thu Jun 02 15:01:27 2011
15. Started redo scan
16. Thu Jun 02 15:01:27 2011
17. Completed redo scan
18.  0 redo blocks read, 0 data blocks need recovery
19. Thu Jun 02 15:01:27 2011
20. Started redo application at
21.  Thread 1: logseq 57354, block 3, scn 4501380149528
22. Thu Jun 02 15:01:27 2011
23. Recovery of Online Redo Log: Thread 1 Group 2 Seq 57354 Reading mem 0
24.   Mem# 0: F:\ORADATA\DBS101\REDO02.LOG
25. Thu Jun 02 15:01:27 2011
26. Completed redo application
27. Thu Jun 02 15:01:27 2011
28. Completed crash recovery at
29.  Thread 1: logseq 57354, block 3, scn 4501380169529
30.  0 data blocks read, 0 data blocks written, 0 redo blocks read
31. Thu Jun 02 15:01:49 2011
32. Thread 1 advanced to log sequence 57355 (thread open)
33. Thu Jun 02 15:02:02 2011
34. Thread 1 opened at log sequence 57355
35.   Current log# 4 seq# 57355 mem# 0: F:\ORADATA\DBS101\REDO04.LOG
36. Successful open of redo thread 1
37. Thu Jun 02 15:02:02 2011
38. MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
39. Thu Jun 02 15:02:02 2011
40. SMON: enabling cache recovery
41. Thu Jun 02 15:02:04 2011
42. Successfully onlined Undo Tablespace 219.
43. Thu Jun 02 15:02:24 2011
44. SMON: enabling tx recovery
45. Thu Jun 02 15:02:25 2011
46. Database Characterset is WE8MSWIN1252
47. Opening with internal Resource Manager plan
48. where NUMA PG = 1, CPUs = 4
49. replication_dependency_tracking turned off (no async multimaster replication found)
50. Starting background process QMNC
51. QMNC started with pid=17, OS id=4772
52. Thu Jun 02 15:03:40 2011
53. Completed: alter database open

再次尝试打开数据库，这次终于可以成功了。alert日志中有一行信息
  Current log# 4 seq# 57355 mem# 0: F:\ORADATA\DBS101\REDO04.LOG
数据库可以成功切换另外一组重做在线日志。

总结：

对Oracle概念的了解有助于对问题的判断。当了解Oracle实例恢复需要做哪些工作
的时候，在结合Oracle的日志文件中的内容，可以猜出问题出在哪里。可以采取相应的操
作。

基于这个，再重新看看Oracle的关于实例恢复的概念。

系统检测进程 (SMON)

SMON负责系统的清楚任务。这些任务包括:
*在实例启动时执行实例恢复。
*由于文件或者表空间离线的错误，有些事务在实例恢复中被忽略了，当文件和表空间在线
 的时候，SMON恢复中断的事务
*清楚无用的临时表空间段。
*合并字典表空间中连续的空闲的区(extent)

当一个事务提交时，LGWR将内存中的重做条目和事务的SCN写到在线重做日志中。然而DBWR
进程是在最有效的时候才将修改的数据块写到数据文件中。基于这个原因，未提交的数据可
能暂时在数据文件中存在，而提交的数据却不存在数据文件中。

如果实例打开数据库失败，这是因为用SHUTDOWN ABORT或者非正常关闭数据库。
事务提交的数据块还没有写到数据文件中，而只在在线日志文件中。这些改变必须重新写到
数据文件中。
当实例失败时，数据文件中包含了还没有提交的改变。这个改变必须回滚，这样才能保持事
务一致。

实例恢复使用在线日志文件和在线数据文件来同步数据文件，保持他们是一致的。

根据在线日志线程的状态来决定是不是需要实例恢复。在数据库打开成为读写模式时重做日
志线程被标志为打开状态。当实例保持一致的状态下关闭时，重做日志线程被标志为关闭状
态。当在控制文件中重做日志线程被标志为打开状态，而且没有活的实例持有这些相应线程
的enqueue，那么这个数据库需要实例恢复。

实例恢复使用检查点来决定哪些改变需要应用到数据库中。检查点保证每一个比检查点SCN
小的提交的数据保存到数据文件中。

实例恢复阶段

实例恢复的第一个阶段称为缓存恢复或者向前回滚，将在线重做日志中记录的改变重新应用
到数据文件中。因为回滚数据也是记录在在线重做日志文件中，向前回滚也重新产生相应的
撤销段。

向前回滚在必要时处理多个在线重做日志文件。在向前重做后，在线重做日志文件中将记录
提交的数据块。这些文件可能也包含没有提交的改变，这些改变已写到数据文件中。这些改
变是在实例失败前就有了，或者缓存恢复时引入，并记入到重做日志文件中。

当向前回滚后，没有提交的改变需要回滚。Oracle使用检查点来保证所有的比检查点SCN小
的每一个提交的改变写到数据文件中。Oracle Database应用撤销块来回滚那些没有提交的
数据，这些数据块是在实例失败前写入，或者是缓存恢复引入的。这个阶段称为回滚或者
事务恢复。

Oracle Database可以同时并发回滚多个事务。那些在实例失败时活动的事务被标志为终
结。新事务可以自己回滚独立的块来获取需要的数据，而不是等待SMON进程恢复终结的事务

检查点Checkpoints进程

在一致数据库关闭，实例恢复，数据库操作时，检查点是一个重要的机制，检查点有下列意
义:

    一个数据结构表示检查点的位置，这是个重做流中的SCN是实例恢复的起始点。

    检查点的位置是由数据缓存区中最旧的脏缓存决定的。检查点位置担当重做流的指针，
保存在控制文件中和每个数据文件头。

    将修改的数据库缓存写到磁盘

数据库使用检查点有下列目标：

    当实例出错或介质错误时，减少恢复时间

    保证脏的数据缓存区定时地写到磁盘中

    当实例保持一致的状态下关闭的时候，保证所有提交的数据写道磁盘

当Oracle Database开始检查点时：

检查点进程(CKPT)负责将检查点写到数据文件头和控制文件中。检查点发生在不同的情况
下。比如Oracle Database使用下列的检查点:

    线程检查点
    
    数据库将指定线程的重做日志中修改的缓存写到磁盘中，线程检查点的集合就是数据库
检查点。线程检查点发生在下列情况：

        一致的数据库关闭

        ALTER SYSTEM CHECKPOINT 语句

        在线重做日志切换

        ALTER DATABASE BEGIN BACKUP 语句

    表空间和文件的检查点

    数据库将重做日志中修改的缓存写到磁盘。表空间的检查点是文件检查点的集合。这些
检查点发生在不同的情况下，包括更改表空间为只读或者离线，收缩一个数据文件，或者执
行ALTER TABLESPACE BEGIN BACKUP语句

    增量检查点

    增量检查点是部分的线程检查点，目的是防止在在线重做日志切换时写入大量的块。
DBWn每3秒检查并决定有没有工作要做。当DBWn写脏缓存时，就向前移动检查点，引起CKPT
进程写检查点的位置到控制文件，但不是数据文件头。

其他类型的检查点包括实例和介质恢复检查点，当shcema对象被删除或截断是的检查点
 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25105315/viewspace-697207/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25105315/viewspace-697207/