-
Notifications
You must be signed in to change notification settings - Fork 2
/
mrp_detail.sql
162 lines (151 loc) · 6.35 KB
/
mrp_detail.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
@@header
/*
*
* Author : Vishal Gupta
* Purpose : Display MRP (Managed Recovery Process) Status
* Version :
* Parameters :
*
* Revision History:
* ===================
* Date Author Description
* --------- ------------ -----------------------------------------
* 09-MAY-14 Vishal Gupta Modified to display foreground MRP process as well
* 12-Feb-13 Vishal Gupta Created
*
*/
Prompt
Prompt *******************
Prompt * MRP Status
Prompt *******************
COLUMN inst_id HEADING "I#" FORMAT 99
COLUMN SID HEADING "SID" FORMAT 99999
COLUMN process HEADING "Process" FORMAT a7
COLUMN pid HEADING "PID" FORMAT 999999
COLUMN status HEADING "Status" FORMAT a15
COLUMN delay_mins HEADING "Delay|Mins" FORMAT 9999
COLUMN client_process HEADING "Client|Process" FORMAT a10
COLUMN client_pid HEADING "Client|PID" FORMAT a10
COLUMN machine HEADING "Client|Machine" FORMAT a10 TRUNCATE
COLUMN thread# HEADING "Th#" FORMAT 99
COLUMN sequence# HEADING "Seq#" FORMAT 999999
COLUMN block# HEADING "Block#" FORMAT 99999999
COLUMN blocks HEADING "Blocks" FORMAT 99999999
COLUMN applied_log HEADING "Current|Log|Applied|(MB)" FORMAT 99,999
COLUMN log_size HEADING "Log|Size|(MB)" FORMAT 99,999
COLUMN received_log HEADING "Current|Logs|Received|(MB)" FORMAT 9,999,999
COLUMN received_total HEADING "Total|Logs|Received|(MB)" FORMAT 9,999,999
COLUMN speed HEADING "Avg|Speed|(KB/s)" FORMAT 9,999,999
COLUMN known_agents HEADING "Known|Agents" FORMAT 999999
COLUMN active_agents HEADING "Active|Agents" FORMAT 999999
COLUMN archlog_first_Time HEADING "ArchLog|FirstTime" FORMAT a18
COLUMN last_applied_redo_time HEADING "Last Applied|Redo Time" FORMAT a18
COLUMN active_apply_rate HEADING "Active|Apply|Rate" FORMAT a15
SELECT m.process
, s.sid
, m.inst_id
, m.status
, m.delay_mins
, s.machine
, m.thread#
, m.sequence#
, m.block#
, m.blocks
, ((m.block# - 1) * 512)/power(1024,2) applied_log
, (SELECT MAX((l.blocks * l.block_size)/power(1024,2) )
from v$archived_log l
WHERE m.thread# = l.thread# AND m.sequence# = l.sequence#
) log_size
, m.known_agents
, m.active_agents
, rp.last_applied_redo_time
--, rp.active_apply_rate
FROM gv$managed_standby m
JOIN gv$process p ON m.inst_id = p.inst_id AND m.pid = p.spid
JOIN gv$session s ON s.inst_id = p.inst_id AND s.paddr = p.addr
JOIN (SELECT MAX(DECODE(r.item,'Last Applied Redo',TO_CHAR(r.timestamp,'DD-MON-YY HH24:MI:SS'),'')) last_applied_redo_time
--, MAX(DECODE(r.item,'Active Apply Rate',TRIM(TO_CHAR(r.sofar,'999,999,999,999')) || ' ' || r.units ,'')) active_apply_rate
FROM gv$recovery_progress r
WHERE r.start_time = (SELECT MAX(start_time) from gv$recovery_progress)
GROUP BY r.start_Time
) rp ON 1=1
--JOIN v$statname sn ON sn.name = 'bytes received via SQL*Net from client'
--JOIN gv$sesstat ss ON ss.inst_id = s.inst_id AND ss.sid = s.sid AND sn.statistic# = ss.statistic#
WHERE 1=1
AND m.process IN ('MRP0','MR(fg)')
UNION ALL
/* Sometimes MRP0 process does not move, while slave recovery process are reading older archived redo logs
so show their process as well
*/
SELECT p.pname
, s.sid
, s.inst_id
, 'Reading'
, NULL
, s.machine
, l2.thread#
, l2.sequence#
-- , l2.first_Time archlog_first_Time
, DECODE(sw.p1text, 'log#',sw.p2 ,'') block#
, DECODE(sw.p1text, 'log#',sw.p3 ,'') blocks
, (sw.p2 * 512)/power(1024,2) Read_MB
, (l2.block_size * l2.blocks)/power(1024,2) log_size_MB
, NULL
, NULL
, TO_CHAR(l2.first_Time,'DD-MON-YY HH24:MI:SS') archlog_first_Time
FROM gv$session_wait sw
JOIN gv$session s ON s.inst_id = sw.inst_id and sw.sid = s.sid and s.program like '%'
JOIN gv$process p ON s.inst_id = p.inst_id and s.paddr = p.addr
JOIN v$archived_log l2 ON l2.sequence# = sw.p1
AND l2.first_time = (select max(first_Time)
from v$archived_log l
where l.sequence# = sw.p1 )
where 1=1
and sw.wait_class <> 'Idle'
and sw.event = 'log file sequential read'
and s.program like '%(PR%'
;
BREAK ON REPORT
COMPUTE SUM LABEL 'Total' OF speed FORMAT 99,999,999 ON REPORT
COMPUTE SUM LABEL 'Total' OF received_log FORMAT 99,999,999 ON REPORT
COMPUTE SUM LABEL 'Total' OF received_total FORMAT 99,999,999 ON REPORT
--Prompt
--Prompt *************************
--Prompt * Standby Redo Logs
--Prompt *************************
SELECT m.process
, s.sid
, m.inst_id
--, m.pid
, m.status
--, m.client_process
--, m.client_pid
, s.machine
, m.thread#
, m.sequence#
, m.block#
, m.blocks
, ((m.block# - 1) * 512)/power(1024,2) received_log
, (ss.value/power(1024,2)) received_total
FROM gv$managed_standby m
JOIN gv$process p ON m.inst_id = p.inst_id AND m.pid = p.spid
JOIN gv$session s ON s.inst_id = p.inst_id AND s.paddr = p.addr
JOIN v$statname sn ON sn.name = 'bytes received via SQL*Net from client'
JOIN gv$sesstat ss ON ss.inst_id = s.inst_id AND ss.sid = s.sid AND sn.statistic# = ss.statistic#
WHERE 1=1
AND m.process NOT IN ('MRP0','ARCH')
AND m.status IN ('IDLE')
AND m.thread# <> 0
ORDER BY m.process
, inst_id
, m.thread#
, m.sequence#
;
PROMPT
@@dg_recovery_progress
Prompt
Prompt *******************
Prompt * MRP processes
Prompt *******************
select process,status,client_process,sequence#,THREAD#,block#,active_agents,known_agents from v$managed_standby;
@@footer