Phenomenon:
The operation cannot be completed while a DML operation is in progress. There is no response when exiting Sqlplus---------stuck and unable to exit normally.
Log on to the host of the database and use Sqlplus to log in to the DBA user, prompting for an error.
[Oracle@oel-01 ~]$ Sqlplus Bys/bys
Sql*plus:release 11.2.0.1.0 Production on Sun June 21 17:55:09 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
Ora-00257:archiver error. Connect internal only, until freed.
First, view log prompts a redo log cannot be archived.
[Oracle@oel-01 ~]$ Tail Alert_bys001.log
Ora-19502:write error on file "", Block Number (block size=)
Ora-00312:online Log 3 thread 1: '/u01/app/oracle/oradata/bys001/redo03.log '
Sun June 21 18:01:18 2013
Arch:archival stopped, error occurred. would continue retrying
ORACLE Instance Bys001-archival Error
Ora-16014:log 3 sequence# 219 not archived, no available destinations
Ora-00312:online Log 3 thread 1: '/u01/app/oracle/oradata/bys001/redo03.log '
Errors in FILE/U01/APP/ORACLE/DIAG/RDBMS/BYS001/BYS001/TRACE/BYS001_ARC1_6050.TRC:
Ora-16014:log 3 sequence# 219 not archived, no available destinations
Ora-00312:online Log 3 thread 1: '/u01/app/oracle/oradata/bys001/redo03.log '
Second, check the use of hard disk, found that Oracle_home is the archive directory used already 100%
[Oracle@oel-01 ~]$ Df-h
FileSystem Size Used Avail use% mounted on
/dev/sda2 19G 18G 3.8M 100%/
/DEV/SDA1 99M 21M 74M 22%/boot
Tmpfs 3.0G 529M 2.5G 18%/DEV/SHM
/dev/sda5 4.6G 2.6G 1.9G 58%/backup
Third, here I use Rman to delete the archive log
[oracle@oel-01 ~]$ Rman Target/
Rman> crosscheck Archivelog All;
rman> Delete expired Archivelog all;
Delete the archive log before today
rman> Delete archivelog until time ' sysdate-1 ';
can also be used
rman> Delete Archivelog all; Delete all Archives
Four, to view the disk space, has been released a part.
[Oracle@oel-01 ~]$ Df-h
FileSystem Size Used Avail use% mounted on
/dev/sda2 19G 18G 588M 97%/
/DEV/SDA1 99M 21M 74M 22%/boot
Tmpfs 3.0G 529M 2.5G 18%/DEV/SHM
/dev/sda5 4.6G 1.8G 2.6G 42%/backu
Five, at this time using DBA user login Sqlplus still error, using SYSDBA user login, switch the current log file.
[Oracle@oel-01 ~]$ Sqlplus Bys/bys
Sql*plus:release 11.2.0.1.0 Production on Sun June 21 18:03:48 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
Ora-00257:archiver error. Connect internal only, until freed
Log in using SYSDBA
sys@ bys001>alter system switch logfile; ----may take a long time.
System altered.
sys@ bys001>select group#,status,archived from V$log;
group# STATUS ARC
---------- ---------------- ---
1 Current NO
2 INACTIVE YES
3 ACTIVE NO
The log may be as follows:
[Oracle@bys001 ~]$ Cat Alert_bys1.log
Sat Oct 05 12:44:41 2013
Suspending Mmon action ' metrics monitoring ' for 82800 seconds
Sat Oct 05 12:45:10 2013
Archiver process freed from errors. No Longer stopped
Sat Oct 05 12:45:14 2013
Archived Log entry added for thread 1 sequence ID 0xebe3b9d9 dest 1:
Krse_arc_driver_core:successful archiving of previously failed ORL
Sat Oct 05 12:45:14 2013
Thread 1 advanced to log sequence 114 (LGWR switch)
Current log# 3 seq# 114 mem# 0:/u01/oradata/bys1/redo03.log
Sat Oct 05 12:45:14 2013
Aud:audit Commit Delay exceeded, written a copy to OS Audit Trail
Sat Oct 05 12:45:21 2013
Archived Log entry added for thread 1 sequence 113 ID 0xebe3b9d9 dest 1:
Archived Log Entry 108 added for thread 1 sequence 112 ID 0xebe3b9d9 dest 1:
Six, now the database restore normal, you can log in and operate
sys@ Bys001>conn Bys/bys
Connected.
bys@ Bys001>exit
[Oracle@oel-01 ~]$ Sqlplus Bys/bys
bys@ bys001>truncate table test1;
Table truncated.
Finally, the archive log is backed up and deleted.
Use the Backup archive script as follows:
[Oracle@oel-01 ~]$ Cat archback.sh
#!/bin/sh
#su-oracle
Source/home/oracle/.bash_profile
##########
/u01/app/oracle/product/11.2.0/dbhome_1/bin/rman log/home/oracle/rman-arch ' Date +%y%m%d-%h%m '. Log <<EOF
Connect target/;
run{
Backup Archivelog All Delete input
Format '/backup/archlog/arch_%d_%t_%s ';
}
Exit
More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/