A couple archive log / sequence number questions...

  • From: Mason Loring Bliss <mason@xxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 17 Oct 2007 17:53:43 -0400

Hi, all!

I have a couple questions. I've started moving archive logs from our main
database server to a number of standby servers. I noted that rsync on SuSE/
SLES 9 (default operation / no checksumming) didn't notice a change in an
archive log, resulting in that stand-by server getting out of date. Turning
on checksumming for rsync, despite being harder on the disks, seems to have
remedied that, but I'm wondering if there's a way to determine what archive
logs have been applied.

Two things I've found so far confuse me a bit.

First, it seems like I can say this from sqlplus:

    select SEQUENCE#,APPLIED,STATUS from v$archived_log order by SEQUENCE#;

On our primary (open, running) server, I see, at the end:

---------- --- -
     33632 NO  A
     33633 NO  A
     33634 NO  A
     33635 NO  A

On a stand-by, I see:

     33632 NO  D
     33632 NO  A
     33633 NO  D

---------- --- -
     33633 NO  A
     33634 NO  D
     33634 NO  A

I'm not sure why the "applied" column in "no" in each case. When I run
through "recover database" in rman, it shows logs applying up until the end
of the newly available data, and bringing the database up to test shows the
data as being fresh, as expected. I'm also not sure why I'm seeing each
sequence number twice on the stand-bys.

I've also found that I can say "list archivelog all" in rman, but is that
showing me what's on-disk, rather than what has been applied?

Finally, when I say "recover database" it will give me an error saying that
it's looking for a particular sequence number, after applying everything it
can that's new, but running that just to elicit the error so I can see where
we are seems a bit harsh. Plus, I can't do that on an open database, and I'd
like to have one method to apply to both open *and* stand-by databases.

What's the preferred method to query the sequence number of the last applied
archive log on a stand-by database, so I can compare with the primary and
with other stand-by databases to make sure no one is falling behind?

Thanks in advance for clues!

Mason Loring Bliss  ((   If I have not seen as far as others, it is because
 mason@xxxxxxxxxxx   ))   giants were standing on my shoulders. - Hal Abelson

Other related posts: