Re: Find the latest SCN # in phisical standby
- From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>, Lu.Jiang@xxxxxxxxxxxx
- Date: Tue, 12 Jan 2010 15:14:46 -0800 (PST)
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
"Go away before I replace you with a very small and efficient shell script..."
--- On Tue, 1/12/10, Jiang, Lu <Lu.Jiang@xxxxxxxxxxxx> wrote:
From: Jiang, Lu <Lu.Jiang@xxxxxxxxxxxx>
Subject: Find the latest SCN # in phisical standby
To: "ORACLE-L" <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
Other related posts:
