Kellyn,
This is a cool script to detect gaps in the physical standby setup.
Does anyone has something like this to detect gaps in the logical
standby setup?
Thanks,
Roger Xu
On Tue, Jan 12, 2010 at 5:14 PM, Kellyn Pedersen <kjped1313@xxxxxxxxx
<mailto:kjped1313@xxxxxxxxx>> wrote:
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 steps-
sqlplus -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`@${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 <http://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@xxxxxxxxxxxx
<mailto:Lu.Jiang@xxxxxxxxxxxx>>/* wrote:
From: Jiang, Lu <Lu.Jiang@xxxxxxxxxxxx
<mailto:Lu.Jiang@xxxxxxxxxxxx>>
Subject: Find the latest SCN # in phisical standby
To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx
<mailto:oracle-l@xxxxxxxxxxxxx>>
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
