Oracle 归档错误案例

系统环境：
操作系统： RedHat EL55Oracle : Oracle 11.2.0.1.0 案例：客户数据库服务器的归档日志空间被塞满，未发现；DBA在发现日志切换被hang后，强制关了库，导致实例启动失败。错误现象：在做日志归档时，数据库被hang......16:56:02 SYS@ prod>alter system switch logfile; 告警日志：ORACLE Instance prod - Can not allocate log, archival requiredThread 1 cannot allocate new log, sequence 5All online logs needed archivingCurrent log# 1 seq# 4 mem# 0: /dsk1/oradata/prod/redo01a.logCurrent log# 1 seq# 4 mem# 1: /dsk2/oradata/prod/redo01b.log用户强制关库，重新启动：16:37:38 SYS@ prod>startupORACLE instance started.Total System Global Area 835104768 bytesFixed Size 2217952 bytesVariable Size 775948320 bytesDatabase Buffers 54525952 bytesRedo Buffers 2412544 bytesDatabase mounted.ORA-03113: end-of-file on communication channelProcess ID: 13219Session ID: 1 Serial number: 5 Instance 启动失败，告警日志提示： Errors in file /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_11803.trc:ORA-19502: write error on file "/dsk4/arch_prod/arch_1_79_827494678.log", block number 4097 （block size=512）ORA-27072: File I/O errorLinux-x86_64 Error: 25: Inappropriate ioctl for deviceAdditional information: 4Additional information: 4097Additional information: 765440ORA-19502: write error on file "/dsk4/arch_prod/arch_1_79_827494678.log", block number 4097 （block size=512）Sun May 18 15:32:58 2014ARC3 started with pid=23, OS id=11818 Errors in file /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_11803.trc:ORA-19502: write error on file "/dsk4/arch_prod/arch_1_79_827494678.log", block number 4097 （block size=512）ORA-27072: File I/O errorLinux-x86_64 Error: 25: Inappropriate ioctl for deviceAdditional information: 4Additional information: 4097Additional information: 765440ORA-19502: write error on file "/dsk4/arch_prod/arch_1_79_827494678.log", block number 4097 （block size=512）ARCH: I/O error 19502 archiving log 1 to "/dsk4/arch_prod/arch_1_79_827494678.log"ARC1: Becoming the heartbeat ARCHErrors in file /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_11803.trc:ORA-16038: log 1 sequence# 79 cannot be archivedORA-19502: write error on file "", block number （block size=）ORA-00312: online log 1 thread 1: "/dsk1/oradata/prod/redo01a.log"ORA-00312: online log 1 thread 1: "/dsk2/oradata/prod/redo01b.log"USER （ospid: 11803）: terminating the instance due to error 16038Instance terminated by USER, pid = 11803 解决方法：[oracle@rh6 prod]$ sqlplus "/as sysdba"SQL*Plus: Release 11.2.0.1.0 Production on Sun May 18 16:39:26 2014Copyright （c） 1982, 2009, Oracle. All rights reserved.Connected to an idle instance.16:39:27 SYS@ prod>startup mount;ORACLE instance started.Total System Global Area 835104768 bytesFixed Size 2217952 bytesVariable Size 775948320 bytesDatabase Buffers 54525952 bytesRedo Buffers 2412544 bytesDatabase mounted.16:39:39 SYS@ prod>select group#,sequence#,status from v$log;GROUP# SEQUENCE# STATUS---------- ---------- ----------------1 1 INACTIVE3 3 CURRENT2 2 INACTIVEElapsed: 00:00:00.06Clear 未归档的日志组：16:40:25 SYS@ prod>alter database clear logfile group 1;alter database clear logfile group 1*ERROR at line 1:ORA-00350: log 1 of instance prod （thread 1） needs to be archivedORA-00312: online log 1 thread 1: "/dsk1/oradata/prod/redo01a.log"ORA-00312: online log 1 thread 1: "/dsk2/oradata/prod/redo01b.log"Elapsed: 00:00:00.0216:40:47 SYS@ prod>alter database clear unarchived logfile group 1;Database altered.Elapsed: 00:00:02.4616:41:02 SYS@ prod>alter database open;Database altered.Elapsed: 00:00:06.89 再次归档： 17:26:02 SYS@ prod>alter system switch logfile;再次hang。。。。。 查看日志： ORACLE Instance prod - Can not allocate log, archival requiredThread 1 cannot allocate new log, sequence 5All online logs needed archivingCurrent log# 1 seq# 4 mem# 0: /dsk1/oradata/prod/redo01a.logCurrent log# 1 seq# 4 mem# 1: /dsk2/oradata/prod/redo01b.logARC3: Encountered disk I/O error 19502ARC3: Closing local archive destination LOG_ARCHIVE_DEST_1: "/dsk4/arch_prod/arch_1_2_847900609.log" （error 19502）（prod）Errors in file /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_arc3_13316.trc:ORA-19502: write error on file "/dsk4/arch_prod/arch_1_2_847900609.log", block number 4097 （block size=512）ORA-27072: File I/O errorLinux-x86_64 Error: 25: Inappropriate ioctl for deviceAdditional information: 4Additional information: 4097Additional information: 765440 查看归档日志存储空间：16:41:16 SYS@ prod>[root@rh6 ~]# df -hFilesystem Size Used Avail Use% Mounted on/dev/sda2 18G 5.0G 12G 30% /tmpfs 878M 72K 878M 1% /dev/shm/dev/sda1 2.0G 62M 1.8G 4% /boot/dev/sda3 12G 5.7G 5.3G 52% /u01....../dev/mapper/datavg-lv_dsk44.0G 3.8G 2.8M 100% /dsk4竟然，归档日志存储空间已经被塞满，所以造成归档被hang。。。。。。清理存储空间： 调整归档位置：6:49:44 SYS@ prod>alter system set log_archive_dest_2="location=/dsk4/arch1";System altered.16:51:15 SYS@ prod>alter system set log_archive_dest_state_1=defer;System altered.Elapsed: 00:00:00.0416:51:25 SYS@ prod>alter system switch logfile;重新进行归档，归档成功！ALTER SYSTEM SET log_archive_dest_2="location=/dsk4/arch1" SCOPE=BOTH;Sun May 18 16:51:25 2014Using STANDBY_ARCHIVE_DEST parameter default value as /dsk4/arch1ALTER SYSTEM SET log_archive_dest_state_1="DEFER" SCOPE=BOTH;Sun May 18 16:53:13 2014Archived Log entry 4 added for thread 1 sequence 2 ID 0xf7a7caa dest 2:krse_arc_driver_core: Successful archiving of previously failed ORLSun May 18 16:53:13 2014Thread 1 advanced to log sequence 5 （LGWR switch）Current log# 2 seq# 5 mem# 0: /dsk1/oradata/prod/redo02a.logCurrent log# 2 seq# 5 mem# 1: /dsk2/oradata/prod/redo02b.logSun May 18 16:53:14 2014Archived Log entry 5 added for thread 1 sequence 4 ID 0xf7a7caa dest 2:Sun May 18 16:53:15 2014Archived Log entry 6 added for thread 1 sequence 3 ID 0xf7a7caa dest 2: @至此，问题彻底解决，对于归档日志的存储空间在巡检中一定要进行监控，否则空间满后，会给数据库带来很大的麻烦！Oracle RAC环境下归档日志的备份 http://www.linuxidc.com/Linux/2012-01/52394.htmOracle 归档日志 http://www.linuxidc.com/Linux/2011-03/33070.htmOracle 控制文件（CONTROLFILE） http://www.linuxidc.com/Linux/2011-03/33069.htmOracle 联机重做日志文件（ONLINE LOG FILE） http://www.linuxidc.com/Linux/2011-03/33068.htmLinux/Unix shell 脚本中调用SQL,RMAN脚本 http://www.linuxidc.com/Linux/2012-09/70310.htmLinux/Unix shell 脚本清除归档日志文件 http://www.linuxidc.com/Linux/2013-04/82808.htm更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址