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
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进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。
评论
相关阅读
Oracle RAC 一键安装翻车?手把手教你如何排错!
Lucifer三思而后行
548次阅读
2025-04-15 17:24:06
【纯干货】Oracle 19C RU 19.27 发布,如何快速升级和安装?
Lucifer三思而后行
467次阅读
2025-04-18 14:18:38
Oracle SQL 执行计划分析与优化指南
Digital Observer
446次阅读
2025-04-01 11:08:44
XTTS跨版本迁移升级方案(11g to 19c RAC for Linux)
zwtian
445次阅读
2025-04-08 09:12:48
墨天轮个人数说知识点合集
JiekeXu
443次阅读
2025-04-01 15:56:03
【ORACLE】记录一些ORACLE的merge into语句的BUG
DarkAthena
438次阅读
2025-04-22 00:20:37
【ORACLE】你以为的真的是你以为的么?--ORA-38104: Columns referenced in the ON Clause cannot be updated
DarkAthena
413次阅读
2025-04-22 00:13:51
Oracle数据库一键巡检并生成HTML结果,免费脚本速来下载!
陈举超
411次阅读
2025-04-20 10:07:02
Oracle 19c RAC更换IP实战,运维必看!
szrsu
393次阅读
2025-04-08 23:57:08
【活动】分享你的压箱底干货文档,三篇解锁进阶奖励!
墨天轮编辑部
357次阅读
2025-04-17 17:02:24
热门文章
移除DataGuard Standby配置导致Primary启动失败
2023-08-17 21297浏览
使用dblink产生的”SELECT /*+ FULL(P) +*/ * FROM XXXXX P ” 解析
2023-06-20 20894浏览
Troubleshooting 'ORA-28041: Authentication protocol internal error' change password 12c R2 DB
2020-04-08 13645浏览
浅谈ORACLE免费数据库Oracle Database XE (Express Edition) 版
2018-10-31 7593浏览
High wait event ‘row cache mutex’ in 12cR2、19c
2020-08-14 5573浏览