Standby Database Durumunu İzleme

Standby Veritabanımız primary veritabanımız ile senkron bir şekilde çalışıyor mu? Standby Veritabanı, primary nin arkasında mı kalıyor? Fark ne kadar, GAP oluştu mu?, Problem var mı? Varsa kaynağı nedir? vb. soruların cevapları bizim için oldukça önemlidir. Standby ve primary veritabanlarımızın durumlarını ve veri akışını izlememiz için bazı view ler mevcut standby durumumuzu ya bu view ler aracılıyla, Data Guard Broker (dgmrl) ile veya Enterprise Manager ile izleyebiliriz. İlk olarak ilgili viewlere bir göz atalım.

v$managed_standby : Background processlerin durumunu gösteriri bize, hadi bakalım;

 desc v$managed_standby;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROCESS                                            VARCHAR2(9)
 PID                                                VARCHAR2(24)
 STATUS                                             VARCHAR2(12)
 CLIENT_PROCESS                                     VARCHAR2(8)
 CLIENT_PID                                         VARCHAR2(40)
 CLIENT_DBID                                        VARCHAR2(40)
 GROUP#                                             VARCHAR2(40)
 RESETLOG_ID                                        NUMBER
 THREAD#                                            NUMBER
 SEQUENCE#                                          NUMBER
 BLOCK#                                             NUMBER
 BLOCKS                                             NUMBER
 DELAY_MINS                                         NUMBER
 KNOWN_AGENTS                                       NUMBER
 ACTIVE_AGENTS                                      NUMBER
 CON_ID                                             NUMBER

Standby kısmında

SQL> select PROCESS,STATUS,THREAD#,SEQUENCE# from v$managed_standby;


PROCESS   STATUS          THREAD#  SEQUENCE#
--------- ------------ ---------- ----------
ARCH      CLOSING               2         22
DGRD      ALLOCATED             0          0
DGRD      ALLOCATED             0          0
ARCH      CLOSING               2         26
ARCH      CLOSING               1         38
ARCH      CLOSING               2         27
RFS       IDLE                  2          0
RFS       IDLE                  2         28
RFS       IDLE                  0          0
RFS       IDLE                  1          0
RFS       IDLE                  1         41

PROCESS   STATUS          THREAD#  SEQUENCE#
--------- ------------ ---------- ----------
RFS       IDLE                  0          0
RFS       IDLE                  0          0
RFS       IDLE                  0          0
MRP0      APPLYING_LOG          2         28


15 rows selected.


MRP APPLYING_LOG yazıyor ise redo apply işlemi çalışıyordur.

Primary Kısmında


SQL> select PROCESS,STATUS,THREAD#,SEQUENCE# from v$managed_standby;

PROCESS   STATUS          THREAD#  SEQUENCE#
--------- ------------ ---------- ----------
ARCH      CLOSING               2         27
DGRD      ALLOCATED             0          0
DGRD      ALLOCATED             0          0
ARCH      CLOSING               2         24
ARCH      CLOSING               2         25
ARCH      CLOSING               2         26
LNS       WRITING               2         28

DGRD      ALLOCATED             0          0
DGRD      ALLOCATED             0          0

9 rows selected.

LNS Writting yazıyor Log Network Service çalışıyor, SGA içerisindeki redo buffer kayıtlarımız ONS (Oracle Network Service) gönderiliyor demekki, çok güzel.

Ayrıca bir kontrol daha yapalım. LAG var mı? var ise ne kadar geriden geliyor?

Primary Tarafta;

SQL> 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
---------- ---------------------- --------------------- ----------
         1                     40                    40          0

         1                     40                    40          0
         2                     27                    27          0

         2                     27                    27          0

Standby Tarafında

SQL> 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
---------- ---------------------- --------------------- ----------
         1                     40                    40          0
         2                     27                    27          0

Gayet başarılı herhangi bir gecikme durumu yok :)).

Ayrıca aşağıdaki v$dataguard_stats viewi bize dataguard istatistics bilgilerinide verecektir. Lag olumuşsa burada görünecektir.

SQL> set lines 200
SQL> column value format a20
SQL> select SOURCE_DB_UNIQUE_NAME,NAME,VALUE from v$dataguard_stats;
SOURCE_DB_UNIQUE_NAM NAME                             VALUE
-------------------- -------------------------------- --------------------
racdb                transport lag                    +00 00:00:00
racdb                apply lag                        +00 00:00:00
racdb                apply finish time
                     estimated startup time           78


Dataguard durumu ile ilgili v$dataguard_status view inden bilgi alınabilir.

Select
   to_char(timestamp,'YYYY-MON-DD HH24:MI:SS') Timestamp,
   Facility,
   Severity,
   Message
From
   v$dataguard_status
Order by
   Timestamp desc;
select *
  from (select TIMESTAMP,
               completion_time "ArchTime",
               SEQUENCE#,
               round((blocks * block_size) / (1024 * 1024), 1) "Size Meg",
               round((TIMESTAMP - lag(TIMESTAMP, 1, TIMESTAMP)
                      OVER(order by TIMESTAMP)) * 24 * 60 * 60,
                     1) "Diff(sec)",
               round((blocks * block_size) / 1024 /
                     decode(((TIMESTAMP - lag(TIMESTAMP, 1, TIMESTAMP)
                             OVER(order by TIMESTAMP)) * 24 * 60 * 60),
                            0,
                            1,
                            (TIMESTAMP - lag(TIMESTAMP, 1, TIMESTAMP)
                             OVER(order by TIMESTAMP)) * 24 * 60 * 60),
                     1) "KB/sec",
               round((blocks * block_size) / (1024 * 1024) /
                     decode(((TIMESTAMP - lag(TIMESTAMP, 1, TIMESTAMP)
                             OVER(order by TIMESTAMP)) * 24 * 60 * 60),
                            0,
                            1,
                            (TIMESTAMP - lag(TIMESTAMP, 1, TIMESTAMP)
                             OVER(order by TIMESTAMP)) * 24 * 60 * 60),
                     3) "MB/sec",
               round(((lead(TIMESTAMP, 1, TIMESTAMP) over(order by TIMESTAMP)) -
                     completion_time) * 24 * 60 * 60,
                     1) "Lag(sec)"
          from v$archived_log a, v$dataguard_status dgs
         where a.name = replace(dgs.MESSAGE, 'Media Recovery Log ', '')
           and dgs.FACILITY = 'Log Apply Services'
         order by TIMESTAMP desc)
 where rownum < 10;