Re: Find the latest SCN # in phisical standby
Date: Wed, 13 Jan 2010 12:37:50 +1100
Message-ID: <OFCCBB3FA6.88B73C7F-ONCA2576AA.00086B77-CA2576AA.0008F585_at_westpac.com.au>
'Apply LAG' is 0, means redo apply has caught up with primary, check 'transport lag' in v$dataguard_stat if you want to know the more accurate delay time (when the lag is less than 1 archive log file).
Regards,
Kellyn Pedersen <kjped1313_at_yahoo.com>
Sent by: oracle-l-bounce_at_freelists.org
13/01/2010 10:16 AM
Please respond to
kjped1313_at_yahoo.com
To
ORACLE-L <oracle-l_at_freelists.org>, Lu.Jiang_at_umassmed.edu
cc
Subject
Re: Find the latest SCN # in phisical standby
When I worked with standby's, I always compared from v$log_history-
select next_change# from v$log_history
where recid =
(select max(recid) from v$log_history);
And compared it to the primary with a monitoring script that did this type
of stepssqlplus
-s << EOF | read STDBY_CURRENT_ARCH
connect / as sysdba
set feedback off
set pagesize 0
select sequence# from v\$log_history
where recid =
(select max(recid) from v\$log_history);
EOF
echo Standby archive log: ${STDBY_CURRENT_ARCH}
sqlplus -s << EOF | read PRIMARY_CURRENT_ARCH
system/`crypt oracle < ${WORKING_DIR}/.pwd`_at_${REMOTE_SID}
set feedback off
set pagesize 0
select sequence#
from v\$archived_log
where recid =
(select max(recid) from v\$archived_log);
EOF
echo Primary archive log: ${PRIMARY_CURRENT_ARCH}
let ARCH_GAP_ACTUAL=${PRIMARY_CURRENT_ARCH}-${STDBY_CURRENT_ARCH}
echo Archive Log Gap: $ARCH_GAP_ACTUAL
if (( ${ARCH_GAP_ACTUAL} > ${ARCH_GAP_TOLERANCE} ))
then
print "${HOST} standby archive log gap is > ${ARCH_GAP_TOLERANCE} logs
behind ${REMOTE_SID} at ${DATE}" > ${OUT_FILE}
print "${HOST} standby archive log gap is > ${ARCH_GAP_TOLERANCE} logs
behind ${REMOTE_SID} at ${DATE}"
then mail, etc., so forth and so on....
Kellyn Pedersen
Multi-Platform DBA
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen
www.dbakevlar.blogspot.com
"Go away before I replace you with a very small and efficient shell script..."
- On Tue, 1/12/10, Jiang, Lu <Lu.Jiang_at_umassmed.edu> wrote:
From: Jiang, Lu <Lu.Jiang_at_umassmed.edu> Subject: Find the latest SCN # in phisical standby To: "ORACLE-L" <oracle-l_at_freelists.org> Date: Tuesday, January 12, 2010, 3:43 PM
Hi all,
I have set up a real time apply physical standby and trying to find how Real Time it is. However it seems that it is hard to find the latest SCN# on physical standby database, v$database (current_scn) only gets updated to the last scn which has been archived. Here is what I got:
SQL> select recovery_mode from v$archive_dest_status where dest_id=2; RECOVERY_MODE
MANAGED REAL TIME APPLY SQL> select name, value, time_computed from v$dataguard_stats where name='apply lag';
NAME VALUE TIME_COMPUTED --------------- -------------------- ------------------------------ apply lag +00 00:00:00 12-JAN-2010 15:26:02
Primary last change time:
SQL> select scn_to_timestamp(current_scn) from v$database; SCN_TO_TIMESTAMP(CURRENT_SCN)
12-JAN-10 03.05.41.000000000 PM Standby last change time: - not updated after last log archived
SQL> select current_scn from v$database; CURRENT_SCN
579664324
SQL> select scn_to_timestamp(579664324) from v$database; SCN_TO_TIMESTAMP(579664324)
11-JAN-10 11.27.27.000000000 PM SQL> select max(FIRST_CHANGE#),max(NEXT_CHANGE#) from v$archived_log; MAX(FIRST_CHANGE#) MAX(NEXT_CHANGE#)
------------------ -----------------
579266831 579664325
Could someone share some light on this?
Thanks,
Lu
Please consider our environment before printing this email.
WARNING - This email and any attachments may be confidential. If received in error, please delete and inform us by return email. Because emails and attachments may be interfered with, may contain computer viruses or other defects and may not be successfully replicated on other systems, you must be cautious. Westpac cannot guarantee that what you receive is what we sent. If you have any doubts about the authenticity of an email by Westpac, please contact us immediately.
It is also important to check for viruses and defects before opening or using attachments. Westpac's liability is limited to resupplying any affected attachments.
This email and its attachments are not intended to constitute any form of financial advice or recommendation of, or an offer to buy or offer to sell, any security or other financial product. We recommend that you seek your own independent legal or financial advice before proceeding with any investment decision.
Westpac Institutional Bank is a division of Westpac Banking Corporation, a company registered in New South Wales in Australia under the Corporations Act 2001 (Cth). Westpac is authorised and regulated in the United Kingdom by the Financial Services Authority and is registered at Cardiff in the United Kingdom as Branch No. BR 106. Westpac operates in the United States of America as a federally chartered branch, regulated by the Office of the Comptroller of the Currency.
Westpac Banking Corporation ABN 33 007 457 141.
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jan 12 2010 - 19:37:50 CST