暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

Why Disk space has been made available archive process do not Restart.ORA-19815 ORA-16014

原创 Anbob 2012-12-05
689
We use to import a dump file into a database today. impdp process was hang When the archive log destination disk space becomes full,I found this and drop older archivelog files
from OS immediately. but disk space would be full as soon as I drop some files.and db was hang,I decided to terminate the impdp process,then KILL -9 impdp spid, and same way to
drop some archivelog, But the database still hang.
check alert.log

ARC0: Failed to archive thread 1 sequence 59211 (4)
Wed Dec  5 14:20:58 2012
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 59211 (4)
Wed Dec  5 14:20:59 2012
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive thread 1 sequence 59211 (4)
Wed Dec  5 14:20:59 2012
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 59211 (4)
Wed Dec  5 14:20:59 2012
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive thread 1 sequence 59211 (4)
Wed Dec  5 14:20:59 2012
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 59211 (4)
Wed Dec  5 14:21:00 2012
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive thread 1 sequence 59211 (4)

...
ORA-16038: log 5 sequence# 59211 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 5 thread 1: '/oradata/devdb/redo05.log'
Wed Dec  5 14:21:37 2012
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive thread 1 sequence 59211 (4)
ARCH: Archival stopped, error occurred. Will continue retrying
Wed Dec  5 14:21:37 2012
ORACLE Instance devdb - Archival Error
Wed Dec  5 14:21:37 2012
ORA-16014: log 5 sequence# 59211 not archived, no available destinations
ORA-00312: online log 5 thread 1: '/oradata/devdb/redo05.log'
Wed Dec  5 14:21:37 2012
Errors in file /oracle/admin/devdb/bdump/devdb_arc0_21229.trc:
ORA-16014: log 5 sequence# 59211 not archived, no available destinations
ORA-00312: online log 5 thread 1: '/oradata/devdb/redo05.log'
Wed Dec  5 14:21:37 2012
Errors in file /oracle/admin/devdb/bdump/devdb_arc1_21231.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 21474836480 bytes is 100.00% used, and has 0 remaining bytes available.
Wed Dec  5 14:21:37 2012
************************************************************************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
ARC1: Failed to archive thread 1 sequence 59211 (19809)

Time of emergency,my Colleague kill oracle process .oracle database  shutdown and restart.
Finally I impdp exclude index and constraints and then i manual rebuild them in nologing mode.

but now let's thinking about the problem.Why Disk space has been made available archive process do not Restart?
Consider an Oracle database running in archive log mode with automatic archiving turned on. When the archive log destination disk space becomes full, the automatic archiving
will stop,When all of the online redo logs are filled up, the database will be in a hung state.
The following is a list of the possible ways to fix this problem:
The error ORA-16014 is the real clue for this problem. Once the archive destination becomes full the location also becomes invalid. Normally Oracle does not do a recheck to see
if space has been made available.
Using the command:
alter system archive log all to '/u07/app/oradata/DBADB/archive';
gives Oracle a valid location for the archive logs. Even after using this the archive log destination parameter is still invalid and automatic achive does not work. This
solution is best for systems that do not archive regularly but cannot be shutdown. You can also use this to allow you to do a Shutdown immediate instead of Shutdown abort.
Shutdown and restart of the database resets the archive log destinstation parameter to be valid. Do not forget to make disk space available before starting the database.
Use the REOPEN attribute of the LOG_ARCHIVE_DEST_n parameter to determine whether and when ARCn attempts to re-archive to a failed destination following an error. REOPEN
applies to all errors, not just OPEN errors. REOPEN=n sets the minimum number of seconds before ARCn should try to reopen a failed destination. The default value for n is 300
seconds. A value of 0 is the same as turning off the REOPEN option, in other words, ARCn will not attempt to archive after a failure. If you do not specify the REOPEN keyword,
ARCn will never reopen a destination following an error.
thanks Jeff Hunter
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论