Oracle archiving 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 full and not found. After the DBA finds that the log switch is hang, the database is forcibly shut down, causing instance startup failure.

Error:

When archiving logs, 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 forcibly closes the database 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

 

Instance startup failed. Alarm log prompt:

 

Errors in file/u01/app/oracle/diag/rdbms/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/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_000079_827494678.log'

ARC1: Becoming the heartbeat ARCH

Errors in file/u01/app/oracle/diag/rdbms/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 @ rh6 prod] $ sqlplus '/as sysdba'

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

Copyright (c) 1982,200 9, 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.

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

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 clearunarchived 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;

Again hang .....

 

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/trace/prod_arc3_132.16.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 @ rh6 ~] # Df-h

Filesystem Size Used Avail Use % Mounted on

/Dev/sda2 18G 5.0G 12G 30%/

Tmpfs 878 M 72 K 878 M 1%/dev/shm

/Dev/sda1 2.0G 62 M 1.8G 4%/boot

/Dev/sda3 12G 5.7G 5.3G 52%/u01

......

/Dev/mapper/datavg-lv_dsk4

4.0G 3.8G 2.8 M 100%/dsk4

In fact, the archive log storage space is full, so the archive is hang ......

To clear a bucket:

 

Adjust the archiving location:

At 6:49:44 SYS @ prod> alter system set log_archive_dest_2 = 'location =/dsk4/arch1 ';

System altered.

At 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;

Archive again. 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 = 'delete' 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 previusly 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 for archiving logs must be monitored during inspection. Otherwise, the database will be greatly troubled when the space is full!

Backup of archived logs in Oracle RAC Environment

Oracle archiving logs

Oracle Control File)

Oracle ONLINE redo LOG FILE)

Linux/Unix shell scripts call SQL and RMAN scripts

Linux/Unix shell script to clear archived log files