Archive Log Full in Oracle
What happens if a archive log destination is filled up in oracle. This is a common problem Oracle DBA’s usually face.
When our Oracle database running in archive log mode with automatic archiving turned on. And When the archive
log destination disk space becomes full, the automatic archiving will stop.
When this happens we will get errors like ORA-00257
ORA-00257: archiver error. Connect internal only, until freed.
Cause: The archiver process received an error while trying to archive a redo log.
If the problem is not resolved soon, the database will stop executing transactions.
The most likely cause of this message is the destination device is out of space to store the redo log file.
Action: Check archiver trace file for a detailed description of the problem.
Also verify that the device specified in the initialization parameter ARCHIVE_LOG_DEST is set up properly for
When all of the online redo logs are filled up, the database will be in a hung state.
If we attempt to manually archive the files with commands “archive log next” or “archive log all”
you may receive errors
1. Increase the filesyatem where the archive logs are been generated.
If it is a veritas filesyatem we can resize in run time.
The commands are.
Check how much space is available in diskgroup.
vxassist — create, relayout, convert, mirror, backup, grow, shrink, delete, and move volumes
maxsize – Reports the maximum size a volume could be created with given the attributes specified and
given the free storage available.
root@datahouse1# vxassist -g lduprd_dg maxsize
Maximum volume size: 182728704 (89223Mb)
Resize the volume.
The example below resizes a filesystem to 8 GB.
root@datahouse1# /usr/lib/vxvm/bin/vxresize -g lduprd_dg lduprd_db231_lv +8g
2. Delete all the archivelogs in the archive destination.
Then connect to recovery catalog and target database and issue the command in rman prompt.
allocate channel for maintenance type disk;
change archivelog all crosscheck;
Note :- Crosscheck command checks whether the specified backups and copies exist. If RMAN cannot find
backup pieces, it marks them as having the status expired. It marks all other types of absent files–image
copies and archived redo logs–as deleted.
If the files are on disk, RMAN queries the file headers. For other device types, RMAN queries the media
manager to see whether the file exists in the media management catalog.
Take a incremental level 0 database backup from rman.
3. Take the backup of all archivelogs through RMAN
Backup archivelog all delete input.
4. Use the command:
alter system archive log all to ‘/u07/app/oradata/DBADB/archive';
The command archives all redo log file groups that are full but have not been archived.
The 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.
5. Use the REOPEN attribute of the LOG_ARCHIVE_DEST_n init<SID>.ora parameter.
Or run the following comand:
alter system set LOG_ARCHIVE_DEST_n = ‘reopen’
The REOPEN and the NOREOPEN attributes of the LOG_ARCHIVE_DEST_n parameter specify the minimum number
of seconds before the archiver processes (ARCn) or the log writer process (LGWR) should try again to
access a previously failed destination. You can turn off the attribute by specifying NOREOPEN.
6.Use the commands:
alter system archive log stop;
alter system archive log start;
Hope this may help you …