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