dataguard_monitor.txt - 墨天轮文档

dataguard_monitor.txt

forever

115

5页

5次

2024-08-05

5墨值下载

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

--------

--Description :Oracle dataguard monitor script

--Author :如人饮水冷暖自知

--Remark :2015/8/05

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

--------

1.查询 dataguard 归档复制延迟

select LOG_ARCHIVED-LOG_APPLIED "LOG_GAP" from

(SELECT MAX(SEQUENCE#) LOG_ARCHIVED

FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'),

(SELECT MAX(SEQUENCE#) LOG_APPLIED

FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES');

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

--------

2.检查备库状态

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM

V$MANAGED_STANDBY ;

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

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

ARCH CONNECTED 0 0 0 0

MRP0 WAIT_FOR_LOG 1 53056 0 0

RFS IDLE 0 0 0 0

RFS IDLE 1 53056 10935 2

RFS IDLE 0 0 0 0

9 rows selected.

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

--------

3.应用序列检查：

SQL> SELECT al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq

Applied" FROM

2 (select thread# thrd, MAX(sequence#) almax FROM v$archived_log WHERE

resetlogs_change#=(SELECT resetlogs_change#

3 FROM v$database) GROUP BY thread#) al, (SELECT thread# thrd, MAX(sequence#)

lhmax FROM v$log_history

WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY

thread#) lh WHERE al.thrd = lh.thrd; 4

Thread Last Seq Received Last Seq Applied

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

1 49482 49482

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

--------

4.检查 STNADBY 数据库中的消息/错误

set pagesize 2000

set lines 2000

col MESSAGE for a90

select message,timestamp from V$DATAGUARD_STATUS where timestamp > sysdate -

1/6;

MESSAGE

TIMESTAMP

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

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

RFS[48]: No standby redo logfiles created

05-AUG-15

Media Recovery Log /uv1010/arch/MRSX/arch_MRSX_779539386_1_49481.log

05-AUG-15

Media Recovery Waiting for thread 1 sequence 49482 (in transit)

05-AUG-15

RFS[48]: No standby redo logfiles created

05-AUG-15

Media Recovery Log /uv1010/arch/MRSX/arch_MRSX_779539386_1_49482.log

05-AUG-15

Media Recovery Waiting for thread 1 sequence 49483 (in transit)

05-AUG-15

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

--------

5.查询每小时 redo 日志

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

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

--------

6.归档序列号最后来自主数据库，哪个最后应用于备用数据库

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last in Sequence", APPL.SEQUENCE#

"Last Applied Sequence", (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;

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

--------

7.MRP 流程等待状态查询

select a.event, a.wait_time, a.seconds_in_wait from gv$session_wait a,

gv$session b where a.sid=b.sid and

b.sid=(select SID from v$session where PADDR=(select PADDR from v$bgprocess

where NAME='MRP0'));