Re: Disabling ARCHIVELOG problem

"Chloe Crowder" <chloe.crowder_at_bl.uk> wrote in message news:f27559a3.0408310058.5365b1f9_at_posting.google.com...
> Hi
>
> We're running 8.1.7 on Solaris. In order to run a large batch job I've
> been asked to disable archive logging (I know - I don't agree either).
> When I tried to do so I got an error message:-
>
> ORA-01143 cannot disable media recovery - file XXXX needs media
> recovery
>
> The documentation suggests either recovering or dropping the
> tablespace.
>
> Out of curiousity I went to recover datafile and was prompted for an
> archive log from 6/11/04 (presumably 11th June, otherwise we have a
> *real* problem). However our backup cycle only goes back 28 days, so
> we probably can't get this far back.
>
> This is our production database and everything appears to be working
> correctly. We would be very reluctant to drop the tablespace.
>
> We perform backups on a daily basis (mixed full and incremental) using
> RMAN and Veritas NetBackup.
>
> Can anyone explain what causes the problem on an otherwise error-free
> system, and/or suggest a work round.
>

Hi Chloe,

The "problem" is this. You currently have a file that Oracle believes requires media recovery. Basically the header on this file is not in sync with the rest of the database. Why is this the case, who knows ? Perhaps the tablespace was taken offline "immediate" or some such at some point in time...

The point though is that to get this file to resync with the rest of the database, Oracle needs to apply all the redo from 6/11/04 until the current point in time, where 6/11/04 is the date of the checkpoint info in the header of this problem data file. It must apply *all* the redo from this time up to the *current point of time* else it can't possibly be resync'ed.

If you were to now disable archiving, Oracle has no way of *guaranteeing* all the necessary redo logs will be available. You overwrite a redo log without archiving it and all chance to recover this file is gone as you will have created a "hole" in the redo stream.

Therefore Oracle is trying to protect you. It will not allow you to "abandon" this poor file by allowing you to disable logging as such abandonment will likely render recovery impossible.

So you either recovery it in which case all is well again, the *whole* database is now in sync and you can disable away. Or you drop the tablespace as it's a useless limb ready for amputation until it's recovered anyways.

Make sense ?

Cheers

Richard Received on Tue Aug 31 2004 - 06:46:00 CDT