hi all,
having a dataguard testbed on 10gr2 here and trying to find some status selects concerning the log gap between primary and physical standby. one of the used views, namely v$archive_dest_status, does show, as i would interprete it, not always current and exact values for the log sequence#. the destinations are as follows:
defined primary
*.log_archive_dest_1='location=j:\oradata\dgp\arch valid_for=(all_logfiles,all_roles) db_unique_name=dgp reopen=10'
*.log_archive_dest_2='service=dgs lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=dgs reopen=10'
*.standby_archive_dest='j:\oradata\dgp\stbarch'
defined standby
*.log_archive_dest_1='location=e:\oradata\dgs\arch valid_for=(all_logfiles,all_roles) db_unique_name=dgs reopen=10'
*.log_archive_dest_2='service=dgp lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=dgp reopen=10'
*.standby_archive_dest='e:\oradata\dgs\stbarch'
to give you an example at a given time from primary:
SQL> select * from (select dest_id, standby_dest, fal, CREATOR, REGISTRAR, applied, sequence#
2 from v$archived_log
3 order by recid desc) where rownum < 26;
DEST_ID STA FAL CREATOR REGISTR APP SEQUENCE#
---------- --- --- ------- ------- --- ----------
2 YES NO LGWR LGWR YES 3872
1 NO NO ARCH ARCH NO 3872
2 YES NO LGWR LGWR YES 3871
1 NO NO ARCH ARCH NO 3871
SQL> select dest_id, archived_seq# as last_arch_seq, applied_seq# as last_appl_seq
2 from v$archive_dest_status where status not in ('INACTIVE', 'DEFERED');
DEST_ID LAST_ARCH_SEQ LAST_APPL_SEQ
---------- ------------- -------------
1 3872 0
2 3871 3871
... at the same time from standby:
SQL> select * from (select dest_id, standby_dest, fal, CREATOR, REGISTRAR, applied, sequence#
2 from v$archived_log
3 order by recid desc) where rownum < 26;
DEST_ID STA FAL CREATOR REGISTR APP SEQUENCE#
---------- --- --- ------- ------- --- ----------
1 NO NO ARCH RFS YES 3872
1 NO NO ARCH RFS YES 3871
SQL> select dest_id, archived_seq# as last_arch_seq, applied_seq# as last_appl_seq
2 from v$archive_dest_status where status not in ('INACTIVE', 'DEFERED');
DEST_ID LAST_ARCH_SEQ LAST_APPL_SEQ
---------- ------------- -------------
1 3872 0
2 0 0
11 3871 3871
--> here v$archive_dest_status@primary pretends that the last shipped and recovered sequence# at dest_id = 2, standby, is 3871. this can't be true according to v$archived_log@primary as well as another direct lookup with the file system. also v$archive_dest_status@standby and v$archived_log@standby say it is 3872.
furthermore, it seems that v$archive_dest_status has a wired update policy under the hat somehow. another example: i do cancel managed recovery on standby:
alter database recover managed standby database cancel;
and do some manual log switches on primary. log shipping continues to work but no log will be applied on standby. ok, applied sequence# deviate between the destinations:
primary:
DEST_ID STA FAL CREATOR REGISTR APP SEQUENCE#
---------- --- --- ------- ------- --- ----------
2 YES NO LGWR LGWR NO 3878
1 NO NO ARCH ARCH NO 3878
2 YES NO LGWR LGWR NO 3877
1 NO NO ARCH ARCH NO 3877
2 YES NO LGWR LGWR NO 3876
1 NO NO ARCH ARCH NO 3876
2 YES NO LGWR LGWR NO 3875
1 NO NO ARCH ARCH NO 3875
1 NO NO ARCH ARCH NO 3874
2 YES NO LGWR LGWR NO 3874
2 YES NO LGWR LGWR YES 3873
1 NO NO ARCH ARCH NO 3873
2 YES NO LGWR LGWR YES 3872
1 NO NO ARCH ARCH NO 3872
DEST_ID LAST_ARCH_SEQ LAST_APPL_SEQ
---------- ------------- -------------
1 3878 0
2 3877 3873
standby:
DEST_ID STA FAL CREATOR REGISTR APP SEQUENCE#
---------- --- --- ------- ------- --- ----------
1 NO NO ARCH RFS NO 3878
1 NO NO ARCH RFS NO 3877
1 NO NO ARCH RFS NO 3876
1 NO NO ARCH RFS NO 3875
1 NO NO ARCH RFS NO 3874
1 NO NO ARCH RFS YES 3873
1 NO NO ARCH RFS YES 3872
DEST_ID LAST_ARCH_SEQ LAST_APPL_SEQ
---------- ------------- -------------
1 3878 0
2 0 0
11 3877 3873
now i restart managed recovery:
alter database recover managed standby database disconnect;
i can see the recovery progress easily from the alert log. also v$archived_log keeps up with the changes
primary:
DEST_ID STA FAL CREATOR REGISTR APP SEQUENCE#
---------- --- --- ------- ------- --- ----------
2 YES NO LGWR LGWR YES 3878
1 NO NO ARCH ARCH NO 3878
2 YES NO LGWR LGWR YES 3877
1 NO NO ARCH ARCH NO 3877
2 YES NO LGWR LGWR YES 3876
1 NO NO ARCH ARCH NO 3876
2 YES NO LGWR LGWR YES 3875
1 NO NO ARCH ARCH NO 3875
1 NO NO ARCH ARCH NO 3874
2 YES NO LGWR LGWR YES 3874
2 YES NO LGWR LGWR YES 3873
1 NO NO ARCH ARCH NO 3873
2 YES NO LGWR LGWR YES 3872
1 NO NO ARCH ARCH NO 3872
standby:
DEST_ID STA FAL CREATOR REGISTR APP SEQUENCE#
---------- --- --- ------- ------- --- ----------
1 NO NO ARCH RFS YES 3878
1 NO NO ARCH RFS YES 3877
1 NO NO ARCH RFS YES 3876
1 NO NO ARCH RFS YES 3875
1 NO NO ARCH RFS YES 3874
1 NO NO ARCH RFS YES 3873
1 NO NO ARCH RFS YES 3872
however, v$archive_dest_status does not
primary:
DEST_ID LAST_ARCH_SEQ LAST_APPL_SEQ
---------- ------------- -------------
1 3878 0
2 3877 3873
standby:
DEST_ID LAST_ARCH_SEQ LAST_APPL_SEQ
---------- ------------- -------------
1 3878 0
2 0 0
11 3877 3873
i found by accident, that another manual log switch at primary updates v$archive_dest_status to the current but not correct values (one sequence# behind).
crazy.
any experiences concerning v$archive_dest_status at your site? comments are very much appreciated.
regards peter
Edited by: user12024118 on Sep 6, 2010 11:14 PM