ANBOB™

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

打赏

微信扫一扫，打赏作者吧～