forked from carlos-sierra/cscripts
-
Notifications
You must be signed in to change notification settings - Fork 0
/
cs_dg_redo_dest_resp_histogram_report.sql
executable file
·100 lines (100 loc) · 3.3 KB
/
cs_dg_redo_dest_resp_histogram_report.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
----------------------------------------------------------------------------------------
--
-- File name: cs_dg_redo_dest_resp_histogram_report.sql
--
-- Purpose: Data Guard (DG) REDO Transport Duration Report
--
-- Author: Carlos Sierra
--
-- Version: 2021/06/15
--
-- Usage: Execute connected to CDB.
--
-- Enter Source and Destination Hosts when requested.
--
-- Example: $ sqlplus / as sysdba
-- SQL> @cs_dg_redo_dest_resp_histogram_report.sql
--
-- Notes: Developed and tested on 12.1.0.2.
--
---------------------------------------------------------------------------------------
--
@@cs_internal/cs_primary.sql
--@@cs_internal/cs_cdb_warn.sql
@@cs_internal/cs_set.sql
@@cs_internal/cs_def.sql
@@cs_internal/cs_file_prefix.sql
--
DEF cs_script_name = 'cs_dg_redo_dest_resp_histogram_report';
DEF cs_hours_range_default = '8760';
--
@@cs_internal/&&cs_set_container_to_cdb_root.
--
COL cs_hours_range_default NEW_V cs_hours_range_default NOPRI;
SELECT TRIM(TO_CHAR(LEAST(TRUNC((SYSDATE - MIN(time)) * 24), TO_NUMBER('&&cs_hours_range_default.')))) AS cs_hours_range_default FROM C##IOD.dbc_redo_dest_histogram
/
--
@@cs_internal/cs_sample_time_from_and_to.sql
@@cs_internal/cs_snap_id_from_and_to.sql
--
COL source_host_name FOR A64 TRUNC;
SELECT DISTINCT host_name AS source_host_name
FROM C##IOD.dbc_redo_dest_histogram
WHERE time BETWEEN TO_DATE('&&cs_sample_time_from.', '&&cs_datetime_full_format.') AND TO_DATE('&&cs_sample_time_to.', '&&cs_datetime_full_format.')
ORDER BY 1
/
PRO
PRO 3. Source Host Name: (opt)
DEF s_host_name = '&3.';
UNDEF 3;
--
COL dest_host_name FOR A64 TRUNC;
SELECT DISTINCT dest_host_name
FROM C##IOD.dbc_redo_dest_histogram
WHERE time BETWEEN TO_DATE('&&cs_sample_time_from.', '&&cs_datetime_full_format.') AND TO_DATE('&&cs_sample_time_to.', '&&cs_datetime_full_format.')
AND host_name = NVL('&&s_host_name.', host_name)
ORDER BY 1
/
PRO
PRO 3. Destination Host Name: (opt)
DEF d_host_name = '&4.';
UNDEF 4;
--
SELECT '&&cs_file_prefix._&&cs_script_name.' cs_file_name FROM DUAL;
--
@@cs_internal/cs_spool_head.sql
PRO SQL> @&&cs_script_name..sql "&&cs_sample_time_from." "&&cs_sample_time_to." "&&s_host_name." "&&d_host_name."
@@cs_internal/cs_spool_id.sql
--
@@cs_internal/cs_spool_id_sample_time.sql
--
PRO SOURCE : "&&s_host_name."
PRO DESTINATION : "&&d_host_name."
--
COL seconds FOR 999,999,990;
COL frequency FOR 999,990;
BREAK ON source_host_name SKIP PAGE DUPL ON dest_host_name SKIP PAGE DUPL;
--
PRO
PRO Data Guard (DG) REDO Transport Duration (v$redo_dest_resp_histogram)
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT host_name AS source_host_name, dest_host_name, time, duration_seconds AS seconds, frequency
FROM C##IOD.dbc_redo_dest_histogram
WHERE time BETWEEN TO_DATE('&&cs_sample_time_from.', '&&cs_datetime_full_format.') AND TO_DATE('&&cs_sample_time_to.', '&&cs_datetime_full_format.')
AND host_name = NVL('&&s_host_name.', host_name)
AND dest_host_name = NVL('&&d_host_name.', dest_host_name)
ORDER BY host_name, dest_host_name, time
/
--
CL BREAK COMPUTE;
--
PRO
PRO SQL> @&&cs_script_name..sql "&&cs_sample_time_from." "&&cs_sample_time_to." "&&s_host_name." "&&d_host_name."
--
@@cs_internal/cs_spool_tail.sql
--
@@cs_internal/&&cs_set_container_to_curr_pdb.
--
@@cs_internal/cs_undef.sql
@@cs_internal/cs_reset.sql
--