dataguard 모니터링 스크립트

- Primary 쪽 확인 -

 

set pages 500
select thread#, sequence# from v$thread;

select dest_id, database_mode, recovery_mode, protection_mode,
standby_logfile_count,standby_logfile_active,archived_seq#, status
from v$archive_dest_status
where rownum <=2;

set lines 150
col destination format a30
col error format a20
select dest_id,destination,status,target,schedule,process,error from v$archive_dest
where rownum <=2;

 

- Standby 쪽 확인 -

 

SET HEADING OFF
prompt ==================================================================
prompt = Data Guard Monitor for Standby Site
select '= Time Computed: '||MIN(TIME_COMPUTED)
from v$dataguard_stats
/
prompt ==================================================================

SET PAGESIZE 124
SET HEADING ON
SET FEEDBACK OFF
SET TIMING OFF
COL DB_NAME FORMAT A8
COL HOSTNAME FORMAT A10
COL switchover_status FORMAT A15
COL RECEIVED1 FORMAT 999999
COL APPLIED1 FORMAT 9999
COL RECEIVED2 FORMAT 999999
COL APPLIED2 FORMAT 9999
COL APPLIED_TIME FORMAT A20
COL RECEIVED_TIME2 FORMAT A20
COL APPLIED_TIME2 FORMAT A20
COL RECEIVED_TIME FORMAT A20
COL MRP_DELAY FORMAT A5
COL NAME FORMAT A40
COL VALUE FORMAT A30

SELECT DB_NAME, database_role,switchover_status, open_mode, HOSTNAME
FROM
(
SELECT NAME DB_NAME, database_role, open_mode,switchover_status
FROM V$DATABASE
),
(
SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,'.'),0,LENGTH(HOST_NAME),
(INSTR(HOST_NAME,'.')-1))))) HOSTNAME
FROM V$INSTANCE
)
/

prompt
prompt ==================================================================
prompt = Standby Log Status
prompt ==================================================================
select THREAD#,GROUP#,STATUS from v$standby_log order by GROUP#;

prompt
prompt ==================================================================
prompt = LOG RECEIVED  and APPLIED STATUS
prompt ==================================================================
prompt '                       THREAD#1                           THREAD#2             '
prompt '            ------------------------------   ----------------------------------'

set heading off
SELECT 'RECEIVED'||chr(9)||rtrim(received1)||'-> '||received_time1||chr(9)||rtrim(received2)||'-> '||received_time2
FROM
(select max(sequence#) received1, to_char(max(next_time),'YYYY/MM/DD HH24:MI:SS') RECEIVED_TIME1
from V$ARCHIVED_LOG
where thread#=1
),
(select max(sequence#) received2, to_char(max(next_time),'YYYY/MM/DD HH24:MI:SS') RECEIVED_TIME2
from V$ARCHIVED_LOG
where thread#=2
)
/

SELECT 'APPLIED '||chr(9)||rtrim(applied1)||'-> '||applied_time1||chr(9)||rtrim(applied2)||'-> '||applied_time2
from
(select MAX(SEQUENCE#) applied1, TO_CHAR(MAX(COMPLETION_TIME),'YYYY/MM/DD HH24:MI:SS') APPLIED_TIME1
from V$ARCHIVED_LOG where applied='YES' and thread#=1
),
(select MAX(SEQUENCE#) applied2, TO_CHAR(MAX(COMPLETION_TIME),'YYYY/MM/DD HH24:MI:SS') APPLIED_TIME2
from V$ARCHIVED_LOG where applied='YES' and thread#=2
)
/

prompt
SET HEADING OFF

prompt ==================================================================
prompt = DG Stats
prompt ==================================================================

select
NAME,
VALUE Value
from v$dataguard_stats
/

SET HEADING ON

prompt

prompt ==================================================================
prompt = MRP Status (No Rows Return) => MRP down
prompt ==================================================================

COL MRP_DELAY FORMAT A10

select inst_id,process, mrp_delay_setting
from
(select inst_id,process
 from gv$managed_standby where process like 'MRP%'
),
(
select to_char(delay_mins) MRP_DELAY_SETTING from gv$managed_standby where process = 'MRP0'
)
/

prompt