Monitoring
Data Guard (Primary & Physical Standby)
Archive gap logs (DR)
Select thread#, low_sequence#, high_sequence# from
v$archive_gap;
transport and apply lag (DR)
col name for a13
col value for a13
col unit for a30
set lines 132
Select name, value, time_computed from v$dataguard_stats
where name in ('transport lag', 'apply lag');
Identify the
missing logs on the primary (On Primary)
Select L.thread#, L.sequence#
from
(select thread#, sequence# from v$archived_log where dest_id=1) L
where L.sequence# not in (select sequence# from v$archived_log where dest_id=2 and thread# = L.thread#);
apply rate
and active monitoring (DR)
select to_char(start_time, 'DD-MON-RR HH24:MI:SS')
start_time, item , sofar from v$recovery_progress
where item in ('Active Apply Rate', 'Average Apply Rate', 'Redo Applied');
where item in ('Active Apply Rate', 'Average Apply Rate', 'Redo Applied');
Note: the redo applied is measured in megabytes, while the average apply rate and the active apply rate is measured in kilobytes.
Viewing the
status of the managed recovery process (DR)
col client_pid for a10;
select pid, process, status, client_process, client_pid, thread#, sequence#, block#, blocks from v$managed_standby;
select pid, process, status, client_process, client_pid, thread#, sequence#, block#, blocks from v$managed_standby;
Verify the
last sequence# received and the last sequence# applied to standby database by
following query: (DR)
SELECT al.thrd "Thread", almax "Last Seq
Received", lhmax "Last Seq Applied"
FROM ( SELECT thread# thrd, MAX (sequence#) almax
FROM v$archived_log
WHERE
resetlogs_change# = (SELECT resetlogs_change# FROM v$database)
GROUP BY
thread#) al,
( SELECT thread# thrd, MAX (sequence#) lhmax
FROM v$log_history
WHERE
first_time = (SELECT MAX (first_time) FROM v$log_history)
GROUP BY
thread#) lh WHERE al.thrd = lh.thrd;
In order to
know when your log last applied as well as last received log time issue
following query: (DR)
SELECT 'Last Applied
: ' Logs, TO_CHAR (next_time, 'DD-MON-YY:HH24:MI:SS') Time FROM
v$archived_log
WHERE sequence# =
(SELECT MAX (sequence#) FROM v$archived_log WHERE applied = 'YES')
UNION
SELECT 'Last Received : ' Logs,TO_CHAR (next_time,
'DD-MON-YY:HH24:MI:SS') Time
FROM v$archived_log
WHERE sequence# = (SELECT MAX (sequence#) FROM v$archived_log);
To check
Redo apply mode: (On Primary)
SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS where
dest_id=2;
Archive Lag
Histogram (DR)
col name format a10
select * from V$STANDBY_EVENT_HISTOGRAM;
Redo switch
report of primary database
SET PAGESIZE 9999
col day format a15
SELECT A.*, Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb
FROM(SELECT To_Char(First_Time,'YYYY-MM-DD') DAY, Count(1) Count#, Min(RECID)
Min#, Max(RECID) Max# FROM gv$log_history GROUP BY
To_Char(First_Time,'YYYY-MM-DD') ORDER BY 1 DESC) A,(SELECT Avg(BYTES) AVG#,
Count(1) Count#, Max(BYTES) Max_Bytes, Min(BYTES) Min_Bytes FROM gv$log ) B;
Identify the
current sequence#
Please use the queries below to identify the current sequence# on the
primary for each thread, the last received sequence# on the standby and the
last applied sequence# on the standby for each thread so that you will know
whether the standby is in sync with the primary, whether there is a transport
lag or an apply lag.
Primary:
SQL > select thread#, max(sequence#) "Last Primary
Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
PhyStdby:
SQL > select thread#, max(sequence#) "Last Standby
Seq Received"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
PhyStdby:
SQL > select thread#, max(sequence#) "Last Standby
Seq Applied"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and applied='YES'
group by thread# order by 1;
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and applied='YES'
group by thread# order by 1;
Active Apply Rate (DR)
set linesize 400
col Values for a65
col Recover_start for a21
select to_char(START_TIME,'dd.mm.yyyy hh24:mi:ss')
"Recover_start",to_char(item)||' = '||to_char(sofar)||'
'||to_char(units)||' '|| to_char(TIMESTAMP,'dd.mm.yyyy hh24:mi')
"Values" from v$recovery_progress where start_time=(select
max(start_time) from v$recovery_progress);
set lines 200 pages 2000
Col "Current time" for a30
col PROC for a15
col process format a8
col spid format a8
col event format a50 tru
col SIW format 999999
select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')
"Current time"
,s.process
, p.spid
, substr(s.program,
-6) PROC
, s.event
, s.p1
, s.p2
, s.p3
,
s.seconds_in_wait SIW
, s.seq#
from v$session s, v$process p
where p.addr = s.paddr and (s.program like '%MRP%' or
s.program like '%PR0%' or s.program like '%DBW%' or s.program like '%CKPT%')
order by s.process;
set linesize 150 pagesize 2000
col value format 9999999999999999
select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')
"Current time", value
from v$sysstat
where name = 'redo
size'
/
set linesize 200 pagesize 2000
col units format a20
col comments format a15
select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')
"Current time"
, ITEM
, SOFAR
, TOTAL
, UNITS
,
to_char(TIMESTAMP,'DD-MON-YYYY HH24:MI:SS') "Timestamp"
from
v$recovery_progress
where total = 0;
col name format a30
col value format a18
col unit format a35
col time_computed format a22
select
NAME
, VALUE
, UNIT
, DATUM_TIME
, TIME_COMPUTED
from
v$dataguard_stats
order by
time_computed
/
No comments:
Post a Comment