Oracle 歸檔錯誤案例

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 Size                  2217952 bytes

Variable Size             775948320 bytes

Database Buffers           54525952 bytes

Redo Buffers                2412544 bytes

Database mounted.

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

Process ID: 13219

Session 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 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 size=512)

Sun May 18 15:32:58 2014

ARC3 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 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 size=512)

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 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 16038

Instance terminated by USER, pid = 11803

解決方法：

[oracle@rh7 prod]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Sun May 18 16:39:26 2014

Copyright (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 bytes

Fixed Size                  2217952 bytes

Variable Size             775948320 bytes

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 altered.

Elapsed: 00:00:02.46

16: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 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 size=512)

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@rh7 ~]# 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/arch2';

System altered.

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

System altered.

Elapsed: 00:00:00.04

16:51:25 SYS@ prod>alter system switch logfile;

重新進行歸檔，歸檔成功！

ALTER SYSTEM SET log_archive_dest_2='location=/dsk4/arch2' SCOPE=BOTH;

Sun May 18 16:51:25 2014

Using STANDBY_ARCHIVE_DEST parameter default value as /dsk4/arch2

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 ID 0xf7a7caa dest 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 ID 0xf7a7caa dest 2:

Sun May 18 16:53:15 2014

Archived Log entry 6 added for thread 1 sequence 3 ID 0xf7a7caa dest 2:

@至此，問題徹底解決，對于歸檔日志的存儲空間在巡檢中一定要進行監控，否則空間滿后，會給數據庫帶來很大的麻煩！