CHECK_FOR_REDO_GAP_V1.SQL

check_for_redo_gap_v1.sql:

************************************

--=> *****************************************

--=> *****<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>****

--=> -------> To close the Gap use <----------

--=> -------> close_redo_gap_v0.sql <---------

--=> *****<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>****

--=> *****************************************

--=>

--=> *****************************************

--=> *****<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>****

--=> --------> CHECK FOR REDO GAP <-----------

--=> *****<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>****

--=> *****************************************

---> Force the Primary to send redo data to the Standby

--=> Database Primary: SQL>

alter system switch logfile;

alter system switch logfile;

alter system checkpoint;

alter system archive log current;

---> Verify database open state

--=> Database Primary: SQL>

set lines 200

set pages 99

set trimspool on

set feedback on

set heading on

COL HOST_NAME     FOR A15 HEADING HOST|NAME

COL INSTANCE_NAME FOR A8 HEADING INST|NAME

COL INSTSTATUS    FOR A10 HEADING INST|STATUS

COL INST_ROLE     FOR A18 HEADING INST|ROLE

COL ACTIVE_STATE  FOR A10 HEADING DB ACTIVE|STATE

COL DBNAME        FOR A8 HEADING DB|NAME

COL DATABASE_ROLE FOR A16  HEADING 'DATABASE|ROLE'

COL DGB           FOR A8 HEADING DATAGUARD|BROKER

COL FS_STATUS     FOR A8 HEADING FS|FAILOVER|STATUS

COL FS_HOST       FOR A8 HEADING FS|FAILOVER|OBSERVER|HOST

COL CURRENT_SEQ1          FOR A15 HEADING CURRENT|APPLIED|ARCH|SEQUENCE

COL DB_UNIQUE_NAME FOR A15

COL PRIMARY_DB_UNIQUE_NAME FOR A15

SELECT B.HOST_NAME,

          B.INSTANCE_NAME,

          B.INSTANCE_ROLE INST_ROLE,

       decode(b.status,'STARTED','Started in NoMount',b.status) inststatus,

       A.NAME DBNAME,

          A.DB_UNIQUE_NAME,

          -- A.PRIMARY_DB_UNIQUE_NAME,

          A.DATABASE_ROLE,

          B.DATABASE_STATUS DBSTATUS,

       A.DATAGUARD_BROKER DGB,

          A.FS_FAILOVER_STATUS FS_STATUS,

          A.FS_FAILOVER_OBSERVER_HOST FS_HOST,

          SEQ1.CURRENT_SEQ1

from v$database a, v$instance b, (SELECT TO_CHAR(MAX(SEQUENCE#)) CURRENT_SEQ1 FROM GV$ARCHIVED_LOG WHERE APPLIED='YES') SEQ1;

---> Make sure the primary database is open and the database role is PRIMARY

--=> Database Primary: SQL>

                                                                                                                       FS       CURRENT

                                                                                                              FS       FAILOVER APPLIED

HOST            INST   INST               INST    DB                       DATABASE                  DATAGUAR FAILOVER OBSERVER ARCH

NAME            NAME   ROLE               STATUS  NAME     DB_UNIQUE_NAME  ROLE         DBSTATUS     BROKER   STATUS   HOST     SEQUENCE

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

osb-prd-ofs-001 ACME   PRIMARY_INSTANCE   OPEN    ACME     ACME_pri        PRIMARY      ACTIVE       ENABLED  DISABLED          82801

---> Make sure the standby database is mounted, the database role is "PHYSICAL STANDBY", and the SEQUENCE is the same.

--=> Database Standby: SQL>

                                                                                                                        FS       CURRENT

                                                                                                               FS       FAILOVER APPLIED

HOST          INST   INST               INST       DB                       DATABASE                  DATAGUAR FAILOVER OBSERVER ARCH

NAME          NAME   ROLE               STATUS     NAME     DB_UNIQUE_NAME  ROLE             DBSTATUS BROKER   STATUS   HOST     SEQUENCE

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

drc-ofs-001   ACME   PRIMARY_INSTANCE   MOUNTED    ACME     ACME_drc        PHYSICAL STANDBY ACTIVE   ENABLED  DISABLED          82801

---> Check to see if a redo gap exists

--=> NOTE1: This view only returns the next gap that is currently blocking managed recovery from continuing.

--=>        After resolving the identified gap and starting managed recovery, the DBA should query the V$ARCHIVE_GAP 

--=>        view again on the physical standby database to determine the next (if any) gap sequence. This process

--=>        should be repeated until there are no more gaps.

--=> Database Standby: SQL>

SET LINES 250 PAGES 99 TRIMSPOOL ON FEEDBACK ON HEADING ON

COL SEQ1 FOR A60 heading 'REDO LOG GAPS'

SELECT DECODE(

             (SELECT ltrim(to_char(COUNT(*),'999,999,999')) from v$archive_gap),'0',

             (select 'NO GAPS FOUND' FROM dual),

            (select 'LOW_SEQ: '||LOW_SEQUENCE#||' <> '||'HIGH_SEQ: '||HIGH_SEQUENCE#  SEQ1 from v$archive_gap)) SEQ1

FROM DUAL;

--=1> No Errors <1=--

--=> Database Standby: SQL>

REDO LOG GAPS

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

NO GAPS FOUND

--=2> Errors <2=--

--=> Database Standby: SQL>

REDO LOG GAPS

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

LOW_SEQ: 68404 <> HIGH_SEQ: 81410

---> GAP size

select 81410 - 68404 from dual;

81410-68404

-----------

      13006

---> Check to see if the Primary and Standby are in sync

--=> DATABASE Standby: SQL>

SET LINES 250 PAGES 99 TRIMSPOOL ON FEEDBACK ON HEADING ON

COL LINE1 HEADING REDO_DATA_TRANSFER_INFORMATION FOR A60

SELECT ltrim(to_char(COUNT(*),'999,999,999'))||' TOTAL_LOGS' LINE1

FROM GV$ARCHIVED_LOG

UNION ALL

SELECT ltrim(to_char(COUNT(*),'999,999,999'))||' LOGS_APPLIED'

FROM GV$ARCHIVED_LOG

WHERE APPLIED='YES'

UNION ALL

SELECT ltrim(to_char(COUNT(*),'999,999,999'))||' LOGS_IN_MEMORY'

FROM GV$ARCHIVED_LOG

WHERE APPLIED='IN-MEMORY'

UNION ALL

SELECT ltrim(to_char(COUNT(*),'999,999,999'))||' LOGS_NOT_APPLIED'

FROM GV$ARCHIVED_LOG

WHERE APPLIED='NO'

UNION ALL

SELECT '=================' FROM DUAL

UNION ALL

SELECT DECODE(

             (SELECT ltrim(to_char(COUNT(*),'999,999,999')) FROM GV$ARCHIVED_LOG WHERE APPLIED='NO'),'0',

             (SELECT TO_CHAR(MAX(SEQUENCE#))||' MAX ARCH LOG SEQ APPLIED' FROM GV$ARCHIVED_LOG WHERE APPLIED='YES'),

              (SELECT TO_CHAR(MAX(SEQUENCE#))||' MAX ARCH LOG SEQ NOT APPLIED' FROM GV$ARCHIVED_LOG WHERE APPLIED='NO')) sql1

FROM DUAL

UNION ALL

SELECT DECODE(

             (SELECT ltrim(to_char(COUNT(*),'999,999,999')) FROM GV$ARCHIVED_LOG WHERE APPLIED='NO'),'0',

             ('ALL ARCH LOG SEQ APPLIED'),

              (SELECT TO_CHAR(MIN(SEQUENCE#))||' MIN ARCH LOG SEQ NOT APPLIED' FROM GV$ARCHIVED_LOG WHERE APPLIED='NO')) sql1

FROM DUAL;

--=> NOTE1: The "MAX ARCH LOG SEQ APPLIED" should be the within 1 or 2 the same SEQUENCE as the

--=>        "MAX ARCH LOG SEQ NOT APPLIED" on when DQL is run on Primary database.

--=> NOTE2: Ignore the "LOGS_APPLIED" when run on a Primary database.

--=> DATABASE Standby: SQL>

REDO_DATA_TRANSFER_INFORMATION

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

1,082 TOTAL_LOGS

1,081 LOGS_APPLIED

1 LOGS_IN_MEMORY

0 LOGS_NOT_APPLIED

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

81986 MAX ARCH LOG SEQ APPLIED

ALL ARCH LOG SEQ APPLIED

 

---> Verify MRP is working

SET LINES 250 PAGES 99 TRIMSPOOL ON FEEDBACK ON HEADING ON

COL DB_UNIQUE_NAME FOR A20 HEADING 'DATABASE|UNIQUE NAME'

COL DEST_ID FOR 9999 HEADING DEST|ID

COL STATUS FOR A20 HEADING 'ARCHIVE|DEST STATUS'

COL RECOVERY_MODE FOR A25 HEADING RECOVERY|MODE

COL ARCHIVED_SEQ# FOR 99999999 HEADING ARCHIVED|SEQ#

COL GAP_STATUS FOR A20

COL ERROR FOR A45

SELECT A.DB_UNIQUE_NAME,

       B.DEST_ID,

       B.STATUS,

          B.DATABASE_MODE,

          B.RECOVERY_MODE,

          B.ARCHIVED_SEQ#,

          NVL(B.GAP_STATUS,'NO GAP') GAP_STATUS,

          NVL(B.ERROR,'NO ERRORS') ERROR

FROM V$DATABASE A, V$ARCHIVE_DEST_STATUS B

WHERE DEST_ID IN (1,2)

ORDER BY DEST_ID;

--=1> Error <1=--

--=> DATABASE Primary: SQL>

DATABASE              DEST ARCHIVE                              RECOVERY                   ARCHIVED

UNIQUE NAME             ID DEST STATUS          DATABASE_MODE   MODE                           SEQ# GAP_STATUS           ERROR

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

ACME_pri              1 VALID                OPEN            IDLE                          81765 NO GAP               NO ERRORS

ACME_pri              2 VALID                MOUNTED-STANDBY IDLE                          81765 UNRESOLVABLE GAP     NO ERRORS

--=> DATABASE Standby: SQL>

DATABASE              DEST ARCHIVE                              RECOVERY                   ARCHIVED

UNIQUE NAME             ID DEST STATUS          DATABASE_MODE   MODE                           SEQ# GAP_STATUS           ERROR

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

ACME_drc              1 VALID                MOUNTED-STANDBY IDLE                          81765                      ERR-81891

ACME_drc              2 VALID                UNKNOWN         IDLE                              0 NO GAP               NO ERRORS

--=1> No Errors <1=--

--=> Database Primary: SQL>

DATABASE              DEST ARCHIVE                              RECOVERY                   ARCHIVED

UNIQUE NAME             ID DEST STATUS          DATABASE_MODE   MODE                           SEQ# GAP_STATUS           ERROR

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

ACME_pri              1 VALID                OPEN            IDLE                          81765 NO GAP               NO ERRORS

ACME_pri              2 VALID                MOUNTED-STANDBY MANAGED REAL TIME APPLY       81765 NO GAP               NO ERRORS

--=> DATABASE Standby: SQL>

DATABASE              DEST ARCHIVE                              RECOVERY                   ARCHIVED

UNIQUE NAME             ID DEST STATUS          DATABASE_MODE   MODE                           SEQ# GAP_STATUS           ERROR

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

ACME_drc              1 VALID                MOUNTED-STANDBY MANAGED REAL TIME APPLY       81765 NO GAP               NO ERRORS

ACME_drc              2 VALID                UNKNOWN         IDLE                              0 NO GAP               NO ERRORS

---> Look at MRP error messages

SET LINES 250 PAGES 99 TRIMSPOOL ON FEEDBACK ON HEADING ON

COL MESSAGE_NUM FOR 9999 HEADING MESSAGE|NUM

COL FACILITY FOR A25

COL SEVERITY FOR A14

COL TIMESTAMP FOR A22

COL MESSAGE FOR A100

select MESSAGE_NUM,

       FACILITY,

       SEVERITY,

          to_char(TIMESTAMP,'mm-dd-yyyy hh:mi:ss AM') TIMESTAMP,

          MESSAGE

from V$DATAGUARD_STATUS

where  upper(SEVERITY) not in ('CONTROL','INFORMATIONAL')

order by MESSAGE_NUM asc;

--=> DATABASE Primary: SQL>

MESSAGE  DEST

    NUM    ID FACILITY                  SEVERITY       TIMESTAMP              MESSAGE

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

    199     1 Log Transport Services    Warning        01-17-2014 01:17:12 AM ARC0: Standby redo logfile selected for thread 1

                                                                                    sequence 81481 for destination LOG_ARCHIVE_DEST_2

...

    450     1 Log Transport Services    Warning        01-17-2014 01:17:44 AM ARC2: Standby redo logfile selected for thread 1

                                                                                    sequence 81482 for destination LOG_ARCHIVE_DEST_2

54 rows selected

--=> DATABASE Standby: SQL>

MESSAGE

    NUM FACILITY                  SEVERITY       TIMESTAMP              MESSAGE

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

    154 Remote File Server        Warning        01-17-2014 01:15:02 AM RFS[10]: No standby redo logfiles available for thread 1

    155 Remote File Server        Warning        01-17-2014 01:15:35 AM RFS[8]:  No standby redo logfiles available for thread 1

    157 Remote File Server        Warning        01-17-2014 01:16:06 AM RFS[11]: No standby redo logfiles available for thread 1

    167 Remote File Server        Warning        01-17-2014 01:19:37 AM RFS[13]: No standby redo logfiles available for thread 1

4 rows selected

---> Get number of days since log data was applied

SET LINES 100 PAGES 60

COL CURRENT_DATE FOR A20 HEADING CURRENT|DATE

COL MOST_RECENT_TIME_LOGS_APPLIED FOR A20 HEADING MOST_RECENT|TIME_LOGS_APPLIED

col APPLY_LAG_DAYS for 9999.99 heading APPLY|LAG|DAYS

SELECT THREAD#,

       TO_CHAR(SYSDATE,'MON-DD-YYYY HH:MI AM') CURRENT_DATE,

       TO_CHAR(MAX(FIRST_TIME),'MON-DD-YYYY HH:MI AM') MOST_RECENT_TIME_LOGS_APPLIED,

          round(SYSDATE-MAX(FIRST_TIME),3) APPLY_LAG_DAYS

FROM V$ARCHIVED_LOG

WHERE APPLIED='YES'

GROUP BY THREAD#;

--=> Database Standby: SQL>

                                                        APPLY

           CURRENT              MOST_RECENT               LAG

   THREAD# DATE                 TIME_LOGS_APPLIED        DAYS

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

         1 JAN-08-2014 01:24 PM DEC-19-2013 10:33 PM    19.62

--=> NOTE: APPLY_LAG_DAYS should allways be < 1.0 (less than one day)

--=> *****************************************

--=> *****<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>****

--=> -------> To close the Gap use <----------

--=> -------> close_redo_gap_v0.sql <---------

--=> *****<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>****

--=> *****************************************