database halted ,cannot create archived redo logs how to recover?

[TheSlob]

hi,

how do i recover from a database that was halted becuase of insufficient disk space while archiving redo log files?

thanks

[reydp]

free-up the disk were your archive log files is written by copying to other disk or backup and delete it.
And bounce your db back.
All the transactions that will written to redo logs successfully will just be recovered ad instance startup.

If your system is hanging, then you have no choice but to reboot it to make sure all oracle processes is anew.

[vinit]

i don't see a boot is necessary in this case. Just free up some space and your db will work fine. The reason it is hanging is because it cannot record redo info since all the redo logs are full and cannot be archived due to lack of space.

[reydp]

hi vinit,
but don't you think if your oracle server left hanging for a long time still the ARCH process is only the one that is affected?
Anyway you're right if only the ARCH process was left hanging since it can't do its task to writes redo log entries to archive log, but I believe your system will succumb if still transactions are issued against the server.

[pando]

well it depends, I had to reboot once coz the OS was NT in UNIX never had to

Yea NT Rulez

[vinit]

Rey - the fact that the system (assuimng Oracle) hangs is indirectly affected by ARCH process hanging. If DB is able to create redo entries, then all start functioning again. A reboot may be necessary if the ARCH process hangs due to some other reasons .

[reydp]

vinit,
what if the redo log becomes full? Is LGWR can still function normally? how about CKPT or DBWR?
Though this doesn't happened to me, I always thought its going to be a chain reaction between processes when ARCH is hanging.

[moff]

Hi,

If the archive destination becomes full the ARCH process will stall. This will cause the database to stall until the arch process can continue. In alot of cases freeing up space in the archive destination will allow the arch process to continue automatically but sometimes this process times out and needs to be reopened. This can be done by bouncing the database or if this is not possible reopening the errored destination.
Below are the steps for reopening the destination....

select dest_id,status from v$archive_dest;

The output should look something like the following:

DEST_ID STATUS
---------- ---------
1 ERROR
2 INACTIVE
3 INACTIVE
4 INACTIVE
5 INACTIVE

alter system set log_archive_dest_state_# = enable;
where the # is the destination number

Utilize the DEST_ID to determine the location to REOPEN

col name format a30;
col value format a40;
set lines 132 pages 1000;

select name,value
from v$parameter
where name = 'log_archive_dest'
or name = 'log_archive_dest_#';
where the # is the destination number in error.

The output of this SELECT statement will be the destination that needs to be re-enabled and re-opened. The use of either LOG_ARCHIVE_DEST or LOG_ARCHIVE_DEST_# is determined by individual system configuration.

alter system
set log_archive_dest_#='location={archive destination} REOPEN';

where the # is the destination number
alter system switch logfile;

Issue the following command,
select dest_id,status from v$archive_dest;

Execution of this statement is simply to verify that the logs have written without error. If there are any other instances on the server, force a logfile switch to ensure they are creating and applying logs normally. If there are errors, identify the destination and correct the problems with the method stated above.
Finally, verify that the archive files exist in all destination locations; if some are missing, copy them to the missing location(s).

Hope the above helps...

Cheers
Moff.

[vinit]

rey - if redo log is full , it will skip to the next log until all logs are full. The unavailability of space for ARCH does not affect the DB until all logs are written and not archived.

[reydp]

Originally posted by vinit
rey - if redo log is full , it will skip to the next log until all logs are full. The unavailability of space for ARCH does not affect the DB until all logs are written and not archived.

hi vinit,
My earier post I mentioned that when a db server is left hanging for a long time and still transactions are made against the server, this is tantamount of saying that there come a time that all redo logs will be filled, and that is the time trouble comes.

I'm sure we both agreed to a certain degree somehow. :-))