Oracle 归档错误案例

ukula

　　Oracle 归档错误案例
　　系统环境：
　　操作系统： RedHat EL55
　　Oracle :   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 required
　　Thread 1 cannot allocate new log, sequence 5
　　All online logs needed archiving
　　Current log# 1 seq# 4 mem# 0: /dsk1/oradata/prod/redo01a.log
　　Current log# 1 seq# 4 mem# 1: /dsk2/oradata/prod/redo01b.log
　　用户强制关库，重新启动：
　　16:37:38 SYS@ prod>startup
　　ORACLE instance started.
　　Total System Global Area  835104768 bytes

　　Fixed>
　　Variable>　　Database Buffers           54525952 bytes
　　Redo Buffers                2412544 bytes
　　Database mounted.
　　ORA-03113: end-of-file on communication channel

　　Process>
　　Session>　　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>　　ORA-27072: File I/O error
　　Linux-x86_64 Error: 25: Inappropriate ioctl for device
　　Additional information: 4
　　Additional information: 4097
　　Additional information: 765440

　　ORA-19502: write error on file "/dsk4/arch_prod/arch_1_79_827494678.log", block number 4097 (block>　　Sun May 18 15:32:58 2014

　　ARC3 started with pid=23, OS>　　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>　　ORA-27072: File I/O error
　　Linux-x86_64 Error: 25: Inappropriate ioctl for device
　　Additional information: 4
　　Additional information: 4097
　　Additional information: 765440

　　ORA-19502: write error on file "/dsk4/arch_prod/arch_1_79_827494678.log", block number 4097 (block>　　ARCH: I/O error 19502 archiving log 1 to '/dsk4/arch_prod/arch_1_79_827494678.log'
　　ARC1: Becoming the heartbeat ARCH
　　Errors in file /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_11803.trc:
　　ORA-16038: log 1 sequence# 79 cannot be archived

　　ORA-19502: write error on file "", block number  (block>　　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 16038
　　Instance terminated by USER, pid = 11803
　　
　　解决方法：
　　[oracle@rh6 prod]$ sqlplus '/as sysdba'

　　SQL*Plus:>　　Copyright (c) 1982, 2009, Oracle.  All rights reserved.

　　Connected to an>　　16:39:27 SYS@ prod>startup mount;
　　ORACLE instance started.
　　Total System Global Area  835104768 bytes

　　Fixed>
　　Variable>　　Database Buffers           54525952 bytes
　　Redo Buffers                2412544 bytes
　　Database mounted.
　　16:39:39 SYS@ prod>select group#,sequence#,status from v$log;
　　GROUP#  SEQUENCE# STATUS
　　---------- ---------- ----------------
　　1          1 INACTIVE
　　3          3 CURRENT
　　2          2 INACTIVE
　　Elapsed: 00:00:00.06
　　Clear 未归档的日志组：
　　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 archived
　　ORA-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.02
　　16:40:47 SYS@ prod>alter database clear unarchived logfile group 1;

　　Database>　　Elapsed: 00:00:02.46
　　16:41:02 SYS@ prod>alter database open;

　　Database>　　Elapsed: 00:00:06.89
　　再次归档：
　　17:26:02 SYS@ prod>alter system switch logfile;
　　再次hang。。。。。
　　查看日志：
　　ORACLE Instance prod - Can not allocate log, archival required
　　Thread 1 cannot allocate new log, sequence 5
　　All online logs needed archiving
　　Current log# 1 seq# 4 mem# 0: /dsk1/oradata/prod/redo01a.log
　　Current log# 1 seq# 4 mem# 1: /dsk2/oradata/prod/redo01b.log
　　ARC3: Encountered disk I/O error 19502
　　ARC3: 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>　　ORA-27072: File I/O error
　　Linux-x86_64 Error: 25: Inappropriate ioctl for device
　　Additional information: 4
　　Additional information: 4097
　　Additional information: 765440
　　查看归档日志存储空间：
　　16:41:16 SYS@ prod>
　　[root@rh6 ~]# df -h
　　Filesystem            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_dsk4
　　4.0G  3.8G  2.8M 100% /dsk4
　　竟然，归档日志存储空间已经被塞满，所以造成归档被hang。。。。。。
　　清理存储空间：
　　
　　调整归档位置：
　　6:49:44 SYS@ prod>alter system set log_archive_dest_2='location=/dsk4/arch1';

　　System>　　16:51:15 SYS@ prod>alter system set log_archive_dest_state_1=defer;

　　System>　　Elapsed: 00:00:00.04
　　16: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 2014
　　Using STANDBY_ARCHIVE_DEST parameter default value as /dsk4/arch1
　　ALTER SYSTEM SET log_archive_dest_state_1='DEFER' SCOPE=BOTH;
　　Sun May 18 16:53:13 2014

　　Archived Log entry 4 added for thread 1 sequence 2>　　krse_arc_driver_core: Successful archiving of previously failed ORL
　　Sun May 18 16:53:13 2014
　　Thread 1 advanced to log sequence 5 (LGWR switch)
　　Current log# 2 seq# 5 mem# 0: /dsk1/oradata/prod/redo02a.log
　　Current log# 2 seq# 5 mem# 1: /dsk2/oradata/prod/redo02b.log
　　Sun May 18 16:53:14 2014

　　Archived Log entry 5 added for thread 1 sequence 4>　　Sun May 18 16:53:15 2014

　　Archived Log entry 6 added for thread 1 sequence 3>　　@至此，问题彻底解决，对于归档日志的存储空间在巡检中一定要进行监控，否则空间满后，会给数据库带来很大的麻烦！