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 0Standby 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 0Gayet başarılı herhangi bir gecikme durumu yok :)).
Ayrıca GAP Kontrolü için;
SELECT a.resetlogs_id, DECODE (a.thread#, 1, 'node1', 2, 'node2') HOST, b.last_seq prmy_last_file,
a.applied_seq stdby_last_file, CASE WHEN b.last_seq - a.applied_seq > 2 THEN '=>' ELSE to_char(b.last_seq - a.applied_seq) END archive_difference, TO_CHAR (a.latest_apply_time, 'dd/mm/yyyy hh24:mi:ss') stdby_latest_time
FROM (SELECT resetlogs_id, thread#, MAX (sequence#) applied_seq, MAX (next_time) latest_apply_time
FROM v$archived_log
WHERE applied = 'YES'
GROUP BY resetlogs_id, thread#) a,
(SELECT resetlogs_id, thread#, MAX (sequence#) last_seq
FROM v$archived_log
GROUP BY resetlogs_id, thread#) b
WHERE a.thread# = b.thread#
ORDER BY a.thread#;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;MRP Stop Start
alter database recover managed standby database cancel;
alter database recover managed standby database disconnect from session;
alter database recover managed standby database using current logfile disconnect from session;
Bir yanıt yazın