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

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

1.环境准备

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

SQL>
 

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

SQL>

SQL> archive log list;

Database log mode No Archive Mode

Automatic archival Disabled

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

Oldest online log sequence 9

Current log sequence 11

SQL>

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

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

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

GROUP# MEMBERS ARC STATUS

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

1 1 NO INACTIVE

2 1 NO CURRENT

3 1 NO INACTIVE

SQL>
然后,通过ls命令查看数据文件,删除第一个重做日志文件组(该文件组只有一个日志成员)。

[oracle@ hoegh HOEGH]$ ls
 

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

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

example01.dbf redo03.log temp01.dbf

[oracle@hoegh HOEGH]$

[oracle@hoegh HOEGH]$

[oracle@hoegh HOEGH]$ rm redo01.log

[oracle@hoegh HOEGH]$ ls

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

SQL>
 

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 941600768 bytes

Fixed Size 1348860 bytes

Variable Size 515902212 bytes

Database Buffers 419430400 bytes

Redo Buffers 4919296 bytes

Database mounted.

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

Process ID: 5196

Session ID: 125 Serial number: 5

SQL>

SQL> select status from v$instance;

ERROR:

ORA-03114: not connected to ORACLE

SQL>

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

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

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

Wed Jul 08 21:59:30 2015

MMON started with pid=15, OS id=5443

Wed Jul 08 21:59:30 2015

MMNL started with pid=16, OS id=5445

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

starting up 1 shared server(s) ...

ORACLE_BASE from environment = /u01/app/oracle

Wed Jul 08 21:59:39 2015

alter database mount

Wed Jul 08 21:59:43 2015

Successful mount of redo thread 1, with mount id 2105928075

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: alter database mount

Wed Jul 08 22:11:45 2015

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

Wed Jul 08 22:11:59 2015

alter database open

Wed Jul 08 22:11:59 2015

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

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

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

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

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

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

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

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

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

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

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

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

Wed Jul 08 22:12:00 2015

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

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

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

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

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

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/0abfb8b076b01f3c68bf3b28cc777a71.html