Re: Primary shows no gap, but standby is missing an archivelog

From: Maureen English <>
Date: Wed, 25 Feb 2015 11:55:57 -0900
Message-ID: <>

Thank you Kenny and Mark!

Yes, I agree, it is all very good advice :-)

Oddly enough, we ran into a similar situation with our production standby last summer. If I remember
correctly, the problem was discovered when I tried to open the standby readonly...which sounds like
Mark's guess is correct. At that time, though, we hadn't checked v$archive_dest_status until after we
opened the standby, at which time I'm pretty sure the gap_status was 'RESOLVABLE GAP'. Luckily, this is a database that gets cloned from our production database, so it's not a big problem.
I really like the idea of using RMAN to manage the archivelogs...I can easily write the archivelog backups
to another filesystem with lots of space, thus keeping them for as long as is needed until the next time
we clone this database.
Another person on the list also shared some queries that will give us more information than what we've
been relying on from v$archive_dest_status. One of those queries monitors the lag status!

I certainly cannot say that my job is boring here....

Thanks again!

  • Maureen

On Wed, Feb 25, 2015 at 6:18 AM, MARK BRINSMEAD <> wrote:

> Very good advice, Maureen.
> Just at a guess, I would think that there is no "gap" reported until such
> time as the standby actually TRIES to apply a redolog and finds that it
> can't. If you haven't deleted the logs yet -- or the standby does not yet
> know that you have -- why would a gap be reported.
> Usually -- and especially when you have a standby database -- you should
> use RMAN to manage deletion of archivelogs (and back them up, first of
> course). If you find yourself in a "crisis", it is often better to change
> your LOG_ARCHIVE_DEST locations to point to a different filesystem with
> more free capacity, even if only for an hour or two. RMAN and DataGuard
> will both know how to find the archivelogs in the place they were actually
> written, so you will have less disruption to your backups and your standby
> that way.
> Finally, you really ought to monitor the lag on your standby databases.
> In past environments I worked in, alarm bells would start ringing as soon
> as a standby fell 15 or 30 minutes behind the primary. Doing this will
> avoid embarassing situations where your standby has been stuck on a "gap"
> for two months and nobody has noticed. (Sometimes "bad things" happen to
> DBAs in situations like this when they are not lucky enough to be the first
> to notice.)
> On Tue, Feb 24, 2015 at 9:26 PM, Kenny Payton <> wrote:
>> I’d have to give your question a little more thought for a good answer
>> but hopefully you have a backup of that log or are prepared to rebuild the
>> standby or ship an incremental backup over and apply it to the standby.
>> I have a suggestion moving forward. If you’re not already using RMAN use
>> it minimally to manage your archivelog deletions. You can define the
>> delete archivelog statement it will not delete archivelogs that don’t meet
>> your requirements unless you provide the force option.
>> On Feb 24, 2015, at 9:19 PM, Maureen English <>
>> wrote:
>> I have a situation where I know what happened and how to prevent it
>> from happening in the future, but I don't know why it looks like the
>> problem shouldn't exist.
>> A while back, I was doing something in our primary database that
>> resulted in a lot of archivelogs being generated. We don't have that
>> much space for archivelogs, so I delete some of them when we start to
>> run out of space. I always run the following query in the primary
>> database, before and after deleting archivelogs, and never delete all
>> of them at the same time:
>> where 3 is the standby. This query consistently returns 'NO GAP', so I figured
>> that everything was fine. However, when restarting the standby today, it now
>> appears that there is an archivelog that has been missing for a very long time.
>> Querying v$archived_log shows when the problem started, but why does v$archive_gap
>> return no rows and v$archive_dest_status return 'NO GAP'? Our standby database is
>> in realtime apply mode.
>> - Maureen

Received on Wed Feb 25 2015 - 21:55:57 CET

Original text of this message