Archive Log Mode Problem

[MikeLacey]

I've never used achive log mode and I've been asked to help out with a database that does.

It's giving the error: ORA-00257 which oerr reports as:
[tt]
00257, 00000, "archiver error. Connect internal only, until freed."
// *Cause: The archiver process recieved an error while trying to archive a log. Unless the problem is resolved soon, the database will stop executing transactions.
// *Action: By far the most likely cause of the error is the archive destination device is out of space. Check archiver trace file for detail description of the problem.
[/tt]

So it looks as though there's a space problem, and the production database has stopped processing transactions.

As I said, I've not used this before and would appreciate any pointers/information. (Help!, in other words.)

Mike_Lacey@Cargill.Com
Mike
michael.j.lacey@ntlworld.com

 

[Nebuchednezzar]

The most likely problem is that the drive that archive log files are being copied to is full. When it has no space to send the files to, the archive process hangs up, which causes other process like log writer to back up...Eventually, your whole database locks up.

check to see what the log_archive_dest parameter is set to in the init.ora file. If you're not sure where this file is, but you can still run queries through server manager, then run the query SELECT * FROM V$PARAMETER WHERE NAME LIKE 'log_arch%';

Once you find the log_archive_dest (the directory that archived log files are being sent to), you know where your space issue is. To get going quickly, you could simply delete the files...BUT...these are the files you would need to do point in time recovery so I would recommend copying them to another drive, or even better, putting them to tape (probably just move them to another drive for now so you can get back up and running quickly).

Just for FYI...the way we have our system running is...
every night, all archive log files in the archive_dump_dest location are put to tape...right after that, any files > 2 days old are deleted. That way, the directory never fills up, but I have 2 days worth of archived log files on hand should I need them.

 

[DBAwhosaysNIE]

you can also zip them w/pretty good compression (I get ~70%) and keep that many more between flushes (I HAVE to as my DBs switch a 100M log file/~10 min w/daily hot bkup).

if you don't already have one you may want to request an alpha pager from your boss and crontab (/at w/NT) script(s) to monitor this and other things (instance/listener running, ORA- in alert file, new bdump/udump files, etc.). For archived logs I have mine set to compress new ones every three hours and page me if I have less than ~2 hr worth of space left for new ones (based on size & average frequency).

 

[MikeLacey]

thanks guys, it *was* the archive dir getting full
Mike
michael.j.lacey@ntlworld.com