欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  数据库

删除online日志测试及ORA-600 [4194]错误的处理

程序员文章站 2024-01-31 15:31:28
...

11g通过open resetlogs应该是可以直接OPEN数据库的，打开后要对数据库做一个全备，而10g通过该隐含参数OPEN数据库后，会遭遇到OR

今天做了一个关于破坏online日志的恢复测试，主要三个场景：
测试1：正常关闭数据库后删除非当前日志
测试2：正常关库后，删除在线日志文件
测试3：非正常关闭数据库，并删除当前在线日志文件

我的测试环境是Oracle 10.2.0.1 32bit的数据库，OS版本为Red Hat 5.3，下面看具体测试经过：

测试1：正常关闭数据库后删除非当前日志

[oracle@ora10g ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 6月 24 10:34:53 2015

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

SYS@ora10g> select group#,thread#,status,archived from v$log;

GROUP# THREAD# STATUS ARC
---------- ---------- ---------------- ---
1 1 CURRENT NO
2 1 INACTIVE YES
3 1 ACTIVE YES

SYS@ora10g> set line 130 pages 130
SYS@ora10g> col member for a50
SYS@ora10g> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/ora10g/redo03.log NO
2 ONLINE /u01/app/oracle/oradata/ora10g/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/ora10g/redo01.log NO

SYS@ora10g> conn zlm/zlm
Connected.
ZLM@ora10g> create table t1 as select * from dba_objects where 1=2;

Table created.

ZLM@ora10g> insert into t1 select * from dba_objects where rownum

10 rows created.

ZLM@ora10g> select count(*) from t1;

COUNT(*)
----------
10

ZLM@ora10g> commit; （此处不commit也可，因为对在线日志归档的时候会进行commit操作）

Commit complete.

ZLM@ora10g> alter system archive log current;

System altered.

ZLM@ora10g> select group#,thread#,status,archived from v$log;

GROUP# THREAD# STATUS ARC
---------- ---------- ---------------- ---
1 1 ACTIVE YES
2 1 CURRENT NO
3 1 INACTIVE YES

--删除非当前的在线日志文件（ACTIVE的和INACTIVE的）
[oracle@ora10g backupsets]$ cd /u01/app/oracle/oradata/ora10g/
[oracle@ora10g ora10g]$ pwd
/u01/app/oracle/oradata/ora10g
[oracle@ora10g ora10g]$ ls -l
total 1461348
-rw-r----- 1 oracle oinstall 7520256 Jun 24 10:40 control01.ctl
-rw-r----- 1 oracle oinstall 7520256 Jun 24 10:40 control02.ctl
-rw-r----- 1 oracle oinstall 7520256 Jun 24 10:40 control03.ctl
-rw-r----- 1 oracle oinstall 104865792 Jun 24 10:38 example01.dbf
-rw-r----- 1 oracle oinstall 172032 Nov 29 2014 indx01.dbf
-rw-r----- 1 oracle oinstall 52429312 Jun 24 10:39 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Jun 24 10:39 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Jun 24 10:34 redo03.log
-rw-r----- 1 oracle oinstall 283123712 Jun 24 10:38 sysaux01.dbf
-rw-r----- 1 oracle oinstall 587210752 Jun 24 10:38 system01.dbf
-rw-r----- 1 oracle oinstall 52436992 Jun 23 16:17 temp01.dbf
-rw-r----- 1 oracle oinstall 173023232 Jun 24 10:38 undotbs01.dbf
-rw-r----- 1 oracle oinstall 41951232 Jun 24 10:38 users01.dbf
-rw-r----- 1 oracle oinstall 100671488 Jun 24 10:38 zlm01.dbf
[oracle@ora10g ora10g]$ rm -f redo01.log
[oracle@ora10g ora10g]$ rm -f redo03.log
[oracle@ora10g ora10g]$ ls -l redo*
-rw-r----- 1 oracle oinstall 52429312 Jun 24 10:44 redo02.log
[oracle@ora10g ora10g]$

测试1：正常关闭数据库后删除非当前日志

--正常关闭数据库并重启
ZLM@ora10g> shutdown immediate
ORA-01031: insufficient privileges
ZLM@ora10g> conn / as sysdba
Connected.
SYS@ora10g> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ora10g> startup
ORACLE instance started.

Total System Global Area 524288000 bytes
Fixed Size 1220384 bytes
Variable Size 318767328 bytes
Database Buffers 201326592 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ora10g/redo01.log'

提示无法打开日志组1的日志文件，因为之前在OS层面已经将其删除了

--观察alert日志
[oracle@ora10g ora10g]$ cd /u01/app/oracle/admin/ora10g/bdump/
[oracle@ora10g bdump]$ tail -50f alert_ora10g.log
MMON started with pid=11, OS id=2970
Wed Jun 24 10:45:54 2015
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
CJQ0 started with pid=10, OS id=2968
MMNL started with pid=12, OS id=2972
Wed Jun 24 10:45:55 2015
ALTER DATABASE MOUNT
Wed Jun 24 10:45:58 2015
Setting recovery target incarnation to 8
Wed Jun 24 10:45:58 2015
Successful mount of redo thread 1, with mount id 4202063779
Wed Jun 24 10:45:58 2015
Database mounted in Exclusive Mode
Completed: ALTER DATABASE MOUNT
Wed Jun 24 10:45:59 2015
ALTER DATABASE OPEN
Wed Jun 24 10:45:59 2015
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=16, OS id=2980
Wed Jun 24 10:45:59 2015
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
Wed Jun 24 10:45:59 2015
Errors in file /u01/app/oracle/admin/ora10g/bdump/ora10g_lgwr_2960.trc:
ORA-00313: Message 313 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1]
ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1] [/u01/app/oracle/oradata/ora10g/redo01.log]
ORA-27037: Message 27037 not found; No message file for product=RDBMS, facility=ORA
Linux Error: 2: No such file or directory
Additional information: 3
Wed Jun 24 10:45:59 2015
Errors in file /u01/app/oracle/admin/ora10g/bdump/ora10g_lgwr_2960.trc:
ORA-00313: Message 313 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1]
ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1] [/u01/app/oracle/oradata/ora10g/redo01.log]
ORA-27037: Message 27037 not found; No message file for product=RDBMS, facility=ORA
Linux Error: 2: No such file or directory
Additional information: 3
Wed Jun 24 10:45:59 2015
ARC0: STARTING ARCH PROCESSES
ARC2: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
ARC1 started with pid=17, OS id=2982
Wed Jun 24 10:46:00 2015
ARC1: Becoming the heartbeat ARCH
ARC2 started with pid=18, OS id=2984
Wed Jun 24 10:46:00 2015
ORA-313 signalled during: ALTER DATABASE OPEN...

发现确实是读取redo01.log文件错误，无法OPEN数据库，只停留在MOUNT状态

--清空刚才被删除的2个在线日志文件（相当于重建）
SYS@ora10g> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SYS@ora10g> alter database clear logfile group 1;

Database altered.

相关标签： ORA-600 [4194] 删除online日志测试及OR

上一篇： window php imagick 保存图片不成功

下一篇： Linux下卸载MySQL5_MySQL

推荐阅读