Archive Log Problem

[Hakimca]

Hi Everybody,

I got the message 'Can not allocate log, archival required' while exporting a user's objects. 3 of 4 log files had the status 'Stale' and the the fourth nothing.

What's wrong?

Thanks in advance

[Chintz]

What is happening is the following: All of your archive logs (how ever many of them there are) are full. The database is in archive log mode and one of the following has happened:
1) The disk that the database is archiving to has filled up
2) You have the log_archive_start init.ora parameter set to false.

If the second is the problem and you want automatic archiving, you need to shut down the database, change the init.ora parameter, and start up the database. Otherwise, you have to issue the archive command yourself. If you've run out of disk space, back up the existing archive log files to tape (or somewhere else) and delete them so that there is room. You COULD have the scenario in which on-line redo log files are filling up SO fast that they are always full (archiver can't keep up). In that case, tune the on-line redo log files (make them bigger and add more). You will be able to figure this out pretty easily. To see if the disk is full, check the archive_dump_dest init.ora parameter (select name, value from v$parameter where name = 'archive_dump_dest';). See if that disk is full. To check if auto-archiving is on, check the log_archive_start init.ora parameter (select name, value from v$parameter where name = 'log_archive_start';). If it is the last scenario (archiver can't keep up), check the alert log (select name, value from v$parameter where name = 'background_dump_dest'; You should see alterSID.log there).

Hopefully, this answers your question.

Chintz

[gsprince]

Also,

You can enable the automatic archival dynamically by
"alter system archive log start" without restarting the DB.

But, restarting the DB will set the value to whatever is there in the "init.ora"

[sambavan]

add some more REDOLOG files sufficiently large enough. Make suer that you have enough RBS. If you want to keep the archiver going on, make sure that you have enough diskspace for the archiver, so this would give the archiver some time to archive the REDOLOG.

Or you can temperorily disable the archiving by issuing the command

ALTER SYSTEM ARCHIVE LOG STOP;

confirm it through

select archive from v$instance;


Now if you do the export, there will not be any archiving done and it should progress smoothly. But if there is a problem then you would only be able to recover the database until the point of archive and not beyond that.

Good luck,

Sam