Shulou(Shulou.com)06/01 Report--

Oracle archiving error cases

System environment:

Operating system: RedHat EL55

Oracle: Oracle 11.2.0.1.0

Case study:

The archived log space of the customer database server was stuffed up and was not found. After finding that the log switch was hang, DBA forcibly shut down the database, causing the instance to fail to start.

Error phenomenon:

When doing log archiving, the database is hang.

16:56:02 SYS@ prod > alter system switch logfile

Alarm log:

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

The user forces the library to be closed and restarts:

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

Failed to start Instance. Alarm log prompt:

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

Solution:

[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 unarchived log groups:

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

Archive again:

17:26:02 SYS@ prod > alter system switch logfile

Hang again.

View the log:

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

View the archive log storage space:

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 / dsk4

Unexpectedly, the storage space of the archive log has been filled up, so the archive is hang.

Clean up storage space:

Adjust the archive location:

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

Re-archiving, archiving is successful!

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:

@ at this point, the problem is completely solved. The storage space of archived logs must be monitored during inspection, otherwise when the space is full, it will bring great trouble to the database!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.