LAST APPLIED / LAST RECEIVED - DataGuard

SET LINESIZE 2000
COL TIME FORMAT A20
COL NAME FORMAT A90
SELECT 'LAST APPLIED  : ' LOGS, TO_CHAR(NEXT_TIME,'DD-MON-YYYY HH24:MI:SS') TIME, SEQUENCE#, INCARNATION#, NAME
  FROM ( SELECT DISTINCT INC_B.INCARNATION#,
                FIRST_VALUE(ARC.NEXT_TIME) OVER (ORDER BY ARC.SEQUENCE# DESC) NEXT_TIME,
                FIRST_VALUE(ARC.SEQUENCE#) OVER (ORDER BY ARC.SEQUENCE# DESC) SEQUENCE#,
                FIRST_VALUE(ARC.NAME     ) OVER (ORDER BY ARC.SEQUENCE# DESC) NAME
           FROM V$DATABASE_INCARNATION INC_B, V$ARCHIVED_LOG ARC
          WHERE ARC.APPLIED = 'YES' AND
                ARC.RESETLOGS_CHANGE# = INC_B.RESETLOGS_CHANGE# AND ARC.RESETLOGS_TIME = INC_B.RESETLOGS_TIME AND
                INC_B.INCARNATION# = ( SELECT (CASE DBS.DATABASE_ROLE
                                                 WHEN 'SNAPSHOT STANDBY' THEN INC_A.PRIOR_INCARNATION#
                                                 ELSE INC_A.INCARNATION# END ) INCARNATION
                                         FROM V$DATABASE DBS, V$DATABASE_INCARNATION INC_A
                                        WHERE INC_A.STATUS ='CURRENT' ) )
UNION
SELECT 'LAST RECEIVED : ' LOGS, TO_CHAR(NEXT_TIME,'DD-MON-YYYY HH24:MI:SS') TIME, SEQUENCE#, INCARNATION#, NAME
  FROM ( SELECT DISTINCT INC_B.INCARNATION#,
                FIRST_VALUE(ARC.NEXT_TIME) OVER (ORDER BY ARC.SEQUENCE# DESC) NEXT_TIME,
                FIRST_VALUE(ARC.SEQUENCE#) OVER (ORDER BY ARC.SEQUENCE# DESC) SEQUENCE#,
                FIRST_VALUE(ARC.NAME     ) OVER (ORDER BY ARC.SEQUENCE# DESC) NAME
           FROM V$DATABASE_INCARNATION INC_B, V$ARCHIVED_LOG ARC
          WHERE ARC.RESETLOGS_CHANGE# = INC_B.RESETLOGS_CHANGE# AND ARC.RESETLOGS_TIME = INC_B.RESETLOGS_TIME AND
                INC_B.INCARNATION# = ( SELECT (CASE DBS.DATABASE_ROLE
                                                 WHEN 'SNAPSHOT STANDBY' THEN INC_A.PRIOR_INCARNATION#
                                                 ELSE INC_A.INCARNATION# END ) INCARNATION
                                         FROM V$DATABASE DBS, V$DATABASE_INCARNATION INC_A
                                        WHERE INC_A.STATUS ='CURRENT' ) ) ;