GAP between primary and standby databases


bash-3.2$ ps -afe | grep ora_smon
 oracle 14484 19070 0 00:53:06 pts/6 0:00 grep ora_smon
 oracle 18902 1 0 Dec 07 ? 9:39 ora_smon_KBAZER1

bash-3.2$ sqll
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jan 4 00:53:12 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management and Real Application Testing options
SQL> set linesize 1000
SQL> set pagesize 100
SQL> 
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------------------------------------------------------------------
KBAZER READ WRITE
SQL> select name,open_mode from gv$database;
NAME OPEN_MODE
--------- --------------------------------------------------------------------------------
KBAZER READ WRITE
KBAZER READ WRITE

SQL> column applied_time for a30
SQL> set linesize 140
SQL> select to_char(sysdate,'mm-dd-yyyy hh24:mi:ss') "Current Time" from dual;
Current Time
----------------------------------------------------------------------------
01-04-2014 00:56:51
SQL> SELECT DB_NAME, APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED LOG_GAP ,
 2 (case when ((APPLIED_TIME is not null and (LOG_ARCHIVED-LOG_APPLIED) is null) or 
 3 (APPLIED_TIME is null and (LOG_ARCHIVED-LOG_APPLIED) is not null) or 
 4 ((LOG_ARCHIVED-LOG_APPLIED) > 5)) 
 5 then 'Error! Log Gap is ' 
 6 else 'OK!' 
 7 end) Status
 8 FROM
 9 (
 10 SELECT INSTANCE_NAME DB_NAME
 11 FROM GV$INSTANCE
 12 where INST_ID = 1
 13 ),
 14 (
 15 SELECT MAX(SEQUENCE#) LOG_ARCHIVED
 16 FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES' and THREAD#=1
 17 ),
 18 (
 19 SELECT MAX(SEQUENCE#) LOG_APPLIED
 20 FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=1
 21 ),
 22 (
 23 SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME
 24 FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=1
 25 )
 26 UNION
 27 SELECT DB_NAME, APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED LOG_GAP,
 28 (case when ((APPLIED_TIME is not null and (LOG_ARCHIVED-LOG_APPLIED) is null) or 
 29 (APPLIED_TIME is null and (LOG_ARCHIVED-LOG_APPLIED) is not null) or 
 30 ((LOG_ARCHIVED-LOG_APPLIED) > 5)) 
 31 then 'Error! Log Gap is ' 
 32 else 'OK!' 
 33 end) Status
 34 from (
 35 SELECT INSTANCE_NAME DB_NAME
 36 FROM GV$INSTANCE
 37 where INST_ID = 2
 38 ),
 39 (
 40 SELECT MAX(SEQUENCE#) LOG_ARCHIVED
 41 FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES' and THREAD#=2
 42 ),
 43 (
 44 SELECT MAX(SEQUENCE#) LOG_APPLIED
 45 FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=2
 46 ),
 47 (
 48 SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME
 49 FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=2
 50 )
 51 /
DB_NAME APPLIED_TIME LOG_GAP STATUS
---------------- ------------------------------ ---------- ------------------------------------------------------------------------
KBAZER1 04-JAN/00:40 0 OK!
KBAZER2 04-JAN/00:40 1 OK!

gap
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: