Oracle Archive Error Cases

Oracle Archive Error Cases

System environment:

Operating system: RedHat EL55

Oracle:oracle 11.2.0.1.0

Case:

The archive log space of the client database server is stuffed and not found; The DBA forces the library to shut down after discovering that the log switch was hang, causing the instance to fail to start ...

Error phenomena:

When doing the log archive, the database is hang ...

16:56:02 [email protected] 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 forcibly shuts down the library and restarts:

16:37:38 [email protected] 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 boot failure, 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: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# 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

Workaround:

[Email protected] prod]$ sqlplus '/as sysdba '

Sql*plus:release 11.2.0.1.0 Production on Sun 18 16:39:26 2014

Copyright (c) 1982, Oracle. All rights reserved.

Connected to an idle instance.

16:39:27 [email protected] 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 [email protected] 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 non-archived log groups:

16:40:25 [email protected] 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 [email protected] Prod>alter database clear unarchived logfile Group 1;

Database altered.

elapsed:00:00:02.46

1 6:41:02 [email protected] prod>alter database open;

Database altered.

elapsed:00:00:06.89

Archive again:

17:26:02 [email protected] prod>alter system switch logfile;

Hang on again .....

To view logs:

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

To view the archive log storage space :

16:41:16 [email protected] prod>

[Email protected] ~]# 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

Unexpectedly, the archive log storage space has been stuffed, so the archive was hang ...

To clean up storage space:

To adjust the archive location :

6:49:44 [email protected] prod>alter system set log_archive_dest_2= ' Location=/dsk4/arch1 ';

System altered.

16:51:15 [email protected] prod>alter system set Log_archive_dest_state_1=defer;

System altered.

elapsed:00:00:00.04

16:51:25 [email protected] prod>alter system switch logfile;

Re-archive, archive successful!

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 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, for the archive log storage space in the patrol must be monitored, otherwise the space is full, will bring great trouble to the database!

This article is from the "Tianya blog," Please make sure to keep this source http://tiany.blog.51cto.com/513694/1413258