ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 误删重做日志文件组导致启动数据库报错ORA-03113

误删重做日志文件组导致启动数据库报错ORA-03113

原创 Oracle 作者：Hoegh 时间：2015-07-09 13:53:55 0 删除 编辑
        听不止一个人说起，学技术就要不断地折腾、搞破坏，比如说备份恢复，你就可以尝试删文件，不管是日志文件、临时文件、数据文件还是system文件。删了之后，重启数据库肯定报错，有的甚至当时数据库就挂掉，这样你就可以学着恢复，一破一立之间，很多常规的备份恢复手段也就算是领教了。我今天就尝试着把虚拟机上的一个重做日志文件组删除。

1.环境准备

我们在Oracle11g中进行测试，数据库处于非归档状态。

点击(此处)折叠或打开

  1. SQL>

  3. SQL> select * from v$version;

  5.  

  7. BANNER

  9. --------------------------------------------------------------------------------

  11. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

  13. PL/SQL Release 11.2.0.3.0 - Production

  15. CORE 11.2.0.3.0 Production

  17. TNS for Linux: Version 11.2.0.3.0 - Production

  19. NLSRTL Version 11.2.0.3.0 - Production

  21.  

  23. SQL>

  25. SQL> archive log list;

  27. Database log mode No Archive Mode

  29. Automatic archival Disabled

  31. Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch

  33. Oldest online log sequence 9

  35. Current log sequence 11

  37. SQL>

2.删除一个重做日志文件组，重启数据库报错

首先，通过查询v$log视图来获取数据库重做日志文件组的状态。

点击(此处)折叠或打开

  1. SQL> select GROUP#,MEMBERS,ARCHIVED,STATUS from v$log;

  3.  

  5.     GROUP# MEMBERS ARC STATUS

  7. ---------- ---------- --- ----------------

  9.          1 1 NO INACTIVE

  11.          2 1 NO CURRENT

  13.          3 1 NO INACTIVE

  15.  

  17. SQL>
然后，通过ls命令查看数据文件，删除第一个重做日志文件组（该文件组只有一个日志成员）。

点击(此处)折叠或打开

  1. [oracle@ hoegh HOEGH]$ ls

  3. control01.ctl redo01.log sysaux01.dbf undotbs01.dbf

  5. control02.ctl redo02.log system01.dbf users01.dbf

  7. example01.dbf redo03.log temp01.dbf

  9. [oracle@hoegh HOEGH]$

  11. [oracle@hoegh HOEGH]$

  13. [oracle@hoegh HOEGH]$ rm redo01.log

  15. [oracle@hoegh HOEGH]$ ls

  17. control01.ctl control02.ctl example01.dbf redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
重启数据库，数据库报错。

点击(此处)折叠或打开

  1. SQL>

  3. SQL> shu immediate

  5. Database closed.

  7. Database dismounted.

  9. ORACLE instance shut down.

  11. SQL> startup

  13. ORACLE instance started.

  15.  

  17. Total System Global Area 941600768 bytes

  19. Fixed Size 1348860 bytes

  21. Variable Size 515902212 bytes

  23. Database Buffers 419430400 bytes

  25. Redo Buffers 4919296 bytes

  27. Database mounted.

  29. ORA-03113: end-of-file on communication channel

  31. Process ID: 5196

  33. Session ID: 125 Serial number: 5

  35.  

  37.  

  39. SQL>

  41. SQL> select status from v$instance;

  43. ERROR:

  45. ORA-03114: not connected to ORACLE

  47.  

  49.  

  51. SQL>


3.查看报警日志文件，定位问题

ORA-03113报错是一个非常经典的报错，报错原因多种多样，从报错信息中并看不出是什么原因导致的报错，我们可以到报警日志文件中查看有价值的线索。

点击(此处)折叠或打开

  1. [oracle@enmoedu1 trace]$ tail -40 alert_HOEGH.log

  3. Wed Jul 08 21:59:30 2015

  5. MMON started with pid=15, OS id=5443

  7. Wed Jul 08 21:59:30 2015

  9. MMNL started with pid=16, OS id=5445

  11. starting up 1 dispatcher(s) for network address \'(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))\'...

  13. starting up 1 shared server(s) ...

  15. ORACLE_BASE from environment = /u01/app/oracle

  17. Wed Jul 08 21:59:39 2015

  19. alter database mount

  21. Wed Jul 08 21:59:43 2015

  23. Successful mount of redo thread 1, with mount id 2105928075

  25. Database mounted in Exclusive Mode

  27. Lost write protection disabled

  29. Completed: alter database mount

  31. Wed Jul 08 22:11:45 2015

  33. Time drift detected. Please check VKTM trace file for more details.

  35. Wed Jul 08 22:11:59 2015

  37. alter database open

  39. Wed Jul 08 22:11:59 2015

  41. Errors in file /u01/app/oracle/diag/rdbms/HOEGH/HOEGH/trace/HOEGH_lgwr_5435.trc:

  43. ORA-00313: open failed for members of log group 1 of thread 1

  45. ORA-00312: online log 1 thread 1: \'/u01/app/oracle/oradata/HOEGH/redo01.log\'

  47. ORA-27037: unable to obtain file status

  49. Linux Error: 2: No such file or directory

  51. Additional information: 3

  53. Errors in file /u01/app/oracle/diag/rdbms/HOEGH/HOEGH/trace/HOEGH_lgwr_5435.trc:

  55. ORA-00313: open failed for members of log group 1 of thread 1

  57. ORA-00312: online log 1 thread 1: \'/u01/app/oracle/oradata/HOEGH/redo01.log\'

  59. ORA-27037: unable to obtain file status

  61. Linux Error: 2: No such file or directory

  63. Additional information: 3

  65. Errors in file /u01/app/oracle/diag/rdbms/HOEGH/HOEGH/trace/HOEGH_ora_5451.trc:

  67. ORA-00313: open failed for members of log group 1 of thread

  69. ORA-00312: online log 1 thread 1: \'/u01/app/oracle/oradata/HOEGH/redo01.log\'

  71. USER (ospid: 5451): terminating the instance due to error 313

  73. Wed Jul 08 22:12:00 2015

  75. System state dump requested by (instance=1, osid=5451), summary=[abnormal instance termination].

  77. System State dumped to trace file /u01/app/oracle/diag/rdbms/HOEGH/HOEGH/trace/HOEGH_diag_5425.trc

  79. Dumping diagnostic data in directory=[cdmp_20150708221200], requested by (instance=1, osid=5451), summary=[abnormal instance termination].

  81. Instance terminated by USER, pid = 5451
其中，黄色标注部分为关键信息，我们知道“/u01/app/oracle/oradata/HOEGH/redo01.log”这个文件找不到了。

4.启动数据库到mount状态，重建重做日志文件组

从报警日志可以看出，第一组重做日志文件组丢了，我们可以通过sql语句“alter database  clear logfile group 1;”重建日志文件组；确认日志文件创建成功后，将数据库切换到open状态。

点击(此处)折叠或打开

  1. SQL> startup nomount

  3. ORACLE instance started.

  5.  

  7. Total System Global Area 941600768 bytes

  9. Fixed Size 1348860 bytes

  11. Variable Size 515902212 bytes

  13. Database Buffers 419430400 bytes

  15. Redo Buffers 4919296 bytes

  17. SQL> alter database mount;

  19.  

  21. Database altered.

  23.  

  25. SQL> select group#,sequence#,archived,status from v$log;

  27.  

  29.     GROUP# SEQUENCE# ARC STATUS

  31. ---------- ---------- --- ----------------

  33.          1 7 NO INACTIVE

  35.          3 6 NO INACTIVE

  37.          2 8 NO CURRENT

  39.  

  41. SQL>

  43. SQL>

  45. SQL>

  47. SQL>

  49. SQL> alter database clear logfile group 1;

  51.  

  53. Database altered.

  55.  

  57. SQL> select group#,sequence#,archived,status from v$log;

  59.  

  61.     GROUP# SEQUENCE# ARC STATUS

  63. ---------- ---------- --- ----------------

  65.          1 0 NO UNUSED

  67.          3 6 NO INACTIVE

  69.          2 8 NO CURRENT

  71.  

  73.  

  75. 启动数据库到open状态

  77. SQL>

  79. SQL> alter database open;

  81.  

  83. Database altered.

  85.  

  87. SQL>

  89. SQL> select group#,sequence#,archived,status from v$log;

  91.  

  93.     GROUP# SEQUENCE# ARC STATUS

  95. ---------- ---------- --- ----------------

  97.          1 0 NO UNUSED

  99.          2 8 NO CURRENT

  101.          3 6 NO INACTIVE
此时我们再次查看文件列表，结果如下。

点击(此处)折叠或打开

  1. [oracle@hoegh HOEGH]$ ls

  3. control01.ctl control02.ctl example01.dbf redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf

  5. [oracle@hoegh HOEGH]$

5.手动切换重做日志文件组

为了确保新建的日志文件组可用，我们可以手动切换日志文件组，改变新建日志文件组的状态（由UNUSED改为其他）。

点击(此处)折叠或打开

  1. SQL>

  3. SQL> alter system switch logfile;

  5.  

  7. System altered.

  9.  

  11. SQL> select group#,sequence#,archived,status from v$log;

  13.  

  15.     GROUP# SEQUENCE# ARC STATUS

  17. ---------- ---------- --- ----------------

  19.          1 9 NO CURRENT

  21.          2 8 NO ACTIVE

  23.          3 6 NO INACTIVE

  25.  

  27. SQL> alter system switch logfile;

  29.  

  31. System altered.

  33.  

  35. SQL> select group#,sequence#,archived,status from v$log;

  37.  

  39.     GROUP# SEQUENCE# ARC STATUS

  41. ---------- ---------- --- ----------------

  43.          1 9 NO ACTIVE

  45.          2 8 NO ACTIVE

  47.          3 10 NO CURRENT

  49.  

  51. SQL> alter system switch logfile;

  53.  

  55. System altered.

  57.  

  59. SQL> select group#,sequence#,archived,status from v$log;

  61.  

  63.     GROUP# SEQUENCE# ARC STATUS

  65. ---------- ---------- --- ----------------

  67.          1 9 NO INACTIVE

  69.          2 11 NO CURRENT

  71.          3 10 NO INACTIVE

  73.  

  75. SQL>
其中，
current:表示该日志组为当前日志组,oracle正在使用该日志组;
active:当current redo组发生日志切换时,状态会改变为active,在这个状态下,如果数据库为归档模式,archive进程会归档active日志组;如果发生数据库crash,该日志组也是实例恢复必需的日志组;
inactive:当active日志组归档完毕并且oracle判断不需要进行实例恢复时,会将其状态修改为inactive,等待下一轮的使用;所以当日志组为inactive的时候,如果数据库为归档模式.那么日志肯定是归档完成了。

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

0
0
上一篇： ORA-01157&ORA-01110故障解决
下一篇： 使用物理备份恢复SYSTEM表空间
请登录后发表评论 登录
全部评论
Hoegh

注册时间：2015-03-13

  • 博文量
    132
  • 访问量
    2219956

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593