Monday, October 17, 2016

Queries to diagnose dataguard synchronization issues


select max(al.sequence#) "Last Seq Recieved", max(lh.sequence#) "Last Seq Applied" from v$archived_log al, v$log_history lh;


To Verify Managed Recovery...

select process,status,thread#,sequence#,block#,blocks from v$managed_standby;

To determine the level of synchronization.

SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#,APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS;


Archive Log received

SELECT REGISTRAR, CREATOR, THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# FROM V$ARCHIVED_LOG order by sequence# ;


Archive Log Applied

SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# FROM V$LOG_HISTORY;

SELECT MESSAGE FROM V$DATAGUARD_STATUS;

SELECT TO_CHAR(TRUNC(FIRST_TIME),'Mon DD') "DG Date",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'9999') "12AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'9999') "01AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'9999') "02AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'9999') "03AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'9999') "04AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'9999') "05AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'9999') "06AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'9999') "07AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'9999') "08AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'9999') "09AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'9999') "10AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'9999') "11AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'9999') "12PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'9999') "1PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'9999') "2PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'9999') "3PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'9999') "4PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'9999') "5PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'9999') "6PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'9999') "7PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'9999') "8PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'9999') "9PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'9999') "10PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'9999') "11PM"
FROM V$LOG_HISTORY
GROUP BY TRUNC(FIRST_TIME)
ORDER BY TRUNC(FIRST_TIME) DESC
/

select round((sysdate-max(first_time))*24,2) "Hrs" from v$log_history;


After you issue the first sql statement to start you can actually check if it's running with the following sql (on the standby):

SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;

if it says idle you know, you have failed to start.


SELECT L.SEQUENCE#, L.FIRST_TIME, (CASE WHEN L.NEXT_CHANGE# < p.read_scn then 'YES' when l.FIRST_CHANGE# < P.applied_scn then 'CURRENT' ELSE 'NO' END) APPLIED
FROM DBA_LOGSTDBY_LOG L, DBA_LOGSTDBY_PROGRESS P ORDER BY SEQUENCE#

=============================================================================
Example

select max(al.sequence#) "Last Seq Recieved", max(lh.sequence#) "Last Seq Applied" from v$archived_log al, v$log_history lh;

Last Seq Recieved Last Seq Applied
----------------- ----------------
              355              336

select process,status,thread#,sequence#,block#,blocks from v$managed_standby;

PROCESS                     STATUS                                  THREAD#
--------------------------- ------------------------------------ ----------
 SEQUENCE#     BLOCK#     BLOCKS
---------- ---------- ----------
ARCH                        CLOSING                                       1
       350      63489        515

ARCH                        CLOSING                                       1
       355          1          1

RFS                         RECEIVING                                     1
       339          1          1


PROCESS                     STATUS                                  THREAD#
--------------------------- ------------------------------------ ----------
 SEQUENCE#     BLOCK#     BLOCKS
---------- ---------- ----------
RFS                         WRITING                                       1
       356      13714     204800


SQL> recover managed standby database cancel;
ORA-16136: Managed Standby Recovery not active


SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;

RECOVERY_MODE
---------------------
IDLE

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;

RECOVERY_MODE
---------------------
MANAGED

select process,status,thread#,sequence#,block#,blocks,DELAY_MINS from v$managed_standby;

PROCESS                     STATUS                                  THREAD#
--------------------------- ------------------------------------ ----------
 SEQUENCE#     BLOCK#     BLOCKS
---------- ---------- ----------
ARCH                        CLOSING                                       1
       350      63489        515

ARCH                        CLOSING                                       1
       355          1          1

RFS                         RECEIVING                                     1
       339          1          1

RFS                         WRITING                                       1
       356      13897     204800

MRP0                        APPLYING_LOG                                  1
       350      50121      64003


SQL> select max(al.sequence#) "Last Seq Recieved", max(lh.sequence#) "Last Seq Applied" from v$archived_log al, v$log_history lh;

Last Seq Recieved Last Seq Applied
----------------- ----------------
              355              352

SQL> select max(al.sequence#) "Last Seq Recieved", max(lh.sequence#) "Last Seq Applied" from v$archived_log al, v$log_history lh;

Last Seq Recieved Last Seq Applied
----------------- ----------------
              355              355

SQL> select round((sysdate-max(first_time))*24,2) "Hrs" from v$log_history;

       Hrs
----------
      2.75


alter database register logfile '/data/a01/OTTPRDDR/OTTPRD01_1_18527_728567327.arc';


ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oracle/stbyctrl7feb.ctl';

startup nomount

alter database mount standby database;
alter database recover managed standby database disconnect from session;


No comments:

Post a Comment