Sunday, May 7, 2017

Data Guard Sync Status

Data Guard Sync Status


Method :1
=========

PRIMARY
========


sqlplus "/ as sysdba"
set pages 50000 lines 32767col OPEN_MODE for a10
col HOST_NAME for a10
col DATABASE_ROLE for a7

select NAME,DB_UNIQUE_NAME,INSTANCE_NAME,HOST_NAME,OPEN_MODE,LOG_MODE,DATABASE_STATUS,DATABASE_ROLE,SWITCHOVER_STATUS,PROTECTION_MODE,PROTECTION_LEVEL,CONTROLFILE_TYPE,STATUS,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;

-----------------------------------------------------------------------------------------------------------------------

SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_LEVEL,SWITCHOVER_STATUS,STATUS,LOGINS FROM V$DATABASE,GV$INSTANCE;

-----------------------------------------------------------------------------------------------------------------------
select THREAD#,SEQUENCE#,STATUS,ARCHIVED from v$log;

-----------------------------------------------------------------------------------------------------------------------
select dest_id,database_mode,recovery_mode,protection_mode,archived_seq#,applied_seq# from v$archive_dest_Status where dest_id in (1,2);

-----------------------------------------------------------------------------------------------------------------------

set pages 50000 lines 32767Set scan off
Set feed off
BREAK ON ROW SKIP 1
COL "THREAD" for a10
COL "PR-ARCHIVED" for a15
COL "STBY-ARCHIVED" for a15
COL "STBY-APPLIED" for a 15
COL "SHIPPING GAP(PR -> STBY)" for a20
COL "APPLIED GAP(STBY -> STBY)" for a20
set head off
select 'sysdate: '|| to_char(sysdate,'DD-Mon-YYYY Hh24:Mi:ss') from dual;
select '****************Standby Log ship and Log Apply Status*****************' from dual;
set head on
select DEST_ID,DESTINATION,TARGET,STATUS from V$ARCHIVE_DEST where DESTINATION is not null;
select * from (
select
lpad(t1,4,' ') "Thread",
lpad(pricre,9,' ') "PR - Archived",
lpad(stdcre,10,' ') "STBY - Archived",
lpad(stdnapp,9,' ') "STBY - Applied",
lpad(pricre-stdcre,13,' ') "Shipping GAP (PR -> STBY)",
lpad(stdcre-stdnapp,15,' ') "Applied GAP (STBY -> STBY)"
from
(select max(sequence#) stdcre, thread# t1 from v$archived_log where standby_dest='YES'  and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) group by thread#) a,
(select max(sequence#) stdnapp, thread# t2 from v$archived_log where standby_dest='YES'  and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) and applied='YES' group by thread#) b,
(select max(sequence#) pricre, thread# t3 from v$archived_log where standby_dest='NO' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and  thread# in (1,2,3,4) group by thread#) c
where a.t1=b.t2 and b.t2=c.t3 and c.t3=a.t1) order by 1
/

THREAD PR-ARCHIVED STBY-ARCHIVED STBY-APPLIED SHIPPING GAP(PR -> STBY) APPLIED GAP(STBY -> STBY)
------ ----------- ------------- ------------ ------------------------ -------------------------


Switch log minimun 3 times
--------------------------
alter system switch logfile;       or
alter system switch all logfile;   or
alter system archive log current;

set pages 50000 lines 32767Set scan off
Set feed off
BREAK ON ROW SKIP 1
COL "THREAD" for a10
COL "PR-ARCHIVED" for a15
COL "STBY-ARCHIVED" for a15
COL "STBY-APPLIED" for a 15
COL "SHIPPING GAP(PR -> STBY)" for a20
COL "APPLIED GAP(STBY -> STBY)" for a20
set head off
select 'sysdate: '|| to_char(sysdate,'DD-Mon-YYYY Hh24:Mi:ss') from dual;
select '****************Standby Log ship and Log Apply Status*****************' from dual;
set head on
select DEST_ID,DESTINATION,TARGET,STATUS from V$ARCHIVE_DEST where DESTINATION is not null;
select * from (
select
lpad(t1,4,' ') "Thread",
lpad(pricre,9,' ') "PR - Archived",
lpad(stdcre,10,' ') "STBY - Archived",
lpad(stdnapp,9,' ') "STBY - Applied",
lpad(pricre-stdcre,13,' ') "Shipping GAP (PR -> STBY)",
lpad(stdcre-stdnapp,15,' ') "Applied GAP (STBY -> STBY)"
from
(select max(sequence#) stdcre, thread# t1 from v$archived_log where standby_dest='YES'  and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) group by thread#) a,
(select max(sequence#) stdnapp, thread# t2 from v$archived_log where standby_dest='YES'  and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) and applied='YES' group by thread#) b,
(select max(sequence#) pricre, thread# t3 from v$archived_log where standby_dest='NO' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and  thread# in (1,2,3,4) group by thread#) c
where a.t1=b.t2 and b.t2=c.t3 and c.t3=a.t1) order by 1
/

THREAD PR-ARCHIVED STBY-ARCHIVED STBY-APPLIED SHIPPING GAP(PR -> STBY) APPLIED GAP(STBY -> STBY)

------ ----------- ------------- ------------ ------------------------ -------------------------

==================================================================================================



Method :2
=========

STANDBY
========

sqlplus "/ as sysdba"

set pages 50000 lines 32767
col OPEN_MODE for a10
col HOST_NAME for a10
col DATABASE_ROLE for a7

select NAME,DB_UNIQUE_NAME,INSTANCE_NAME,HOST_NAME,OPEN_MODE,LOG_MODE,DATABASE_STATUS,DATABASE_ROLE,SWITCHOVER_STATUS,PROTECTION_MODE,PROTECTION_LEVEL,CONTROLFILE_TYPE,STATUS,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;

SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_LEVEL,SWITCHOVER_STATUS,STATUS,LOGINS FROM V$DATABASE,GV$INSTANCE;

select THREAD#,SEQUENCE#,STATUS,ARCHIVED from v$log;

SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS,THREAD#, DELAY_MINS FROM GV$MANAGED_STANDBY;

select dest_id,database_mode,recovery_mode,protection_mode,archived_seq#,applied_seq# from v$archive_dest_Status where dest_id in (1,2);

PROMPT
PROMPT
PROMPT Run on Standby Database. This script checks last log applied and last log received time
PROMPT
PROMPT

col time format a40
select 'Last applied  : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
    from v$archived_log
    where sequence# = (select max(sequence#) from v$archived_log where applied='YES')
    union
    select 'Last received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
    from v$archived_log
   where sequence# = (select max(sequence#) from v$archived_log);
PROMPT last sequence# received and the last sequence# applied to standby database.
PROMPT
select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from v$log_history
where first_time=(select max(first_time) from v$log_history)
group by thread#) lh
where al.thrd = lh.thrd;

==================================================================================================================

Method :3
=========
PRIMARY
========

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied",

(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG

WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)

FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------

Switch log minimun 3 times
--------------------------
alter system switch logfile;       or
alter system switch all logfile;   or
alter system archive log current;

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied",

(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG

WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)

FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
                                                                   0 OR min
==================================================================================================================

Method :4
=========
PRIMARY
========

Switch log minimun 3 times
--------------------------
alter system switch logfile;       or
alter system switch all logfile;   or
alter system archive log current;
select thread#,max(sequence#) from v$archived_log where archived='YES' group by thread# order by 1;

STANDBY
========
select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread# order by 1;

==================================================================================================================

Method :5
=========

STANDBY
========

SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS,THREAD#, DELAY_MINS FROM GV$MANAGED_STANDBY;

PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------
MRP0      N/A            1000 APPLIED_LOG

select THREAD#,SEQUENCE#,STATUS,ARCHIVED from v$log;  ------ the status of redo log files
LGWR RFS 1000 CURRENT

Both the above current values must be same.

No comments: