Archive Log Full in Oracle

Posted on August 26, 2009. Filed under: Oracle | Tags: , , , |

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
archiving.

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

solutions :-

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.

e.g -

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;
release channel;

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.

LOG_ARCHIVE_DEST_n=’<path>  REOPEN=30′

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 …

By Partho

About these ads

Make a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

One Response to “Archive Log Full in Oracle”

RSS Feed for Parthokonar's Blog Comments RSS Feed

Great, Marvelous, Outstanding, Understanding,Fantastic,Awesome, Gruesome, Handsome,blossom etc and all that.
Great work indeed. Great effort and pain might have gone into it.
It has benefitted me a lot. Expect more like this from you in days to come
Keep it up.

Regards
Shashi singh


Where's The Comment Form?

  • Calender

    August 2009
    M T W T F S S
    « Jul   Sep »
     12
    3456789
    10111213141516
    17181920212223
    24252627282930
    31  

Liked it here?
Why not try sites on the blogroll...

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: