forked from carlos-sierra/cscripts
-
Notifications
You must be signed in to change notification settings - Fork 0
/
cdb_attributes_setup.sql
executable file
·185 lines (178 loc) · 6.82 KB
/
cdb_attributes_setup.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
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
-- Create cdb_attributes Table and merge_cdb_attributes Procedure for IOD Fleet Inventory
CREATE TABLE c##iod.cdb_attributes (
-- soft PK
version VARCHAR2(10),
db_domain VARCHAR2(64),
db_name VARCHAR2(9),
-- columns
host_name VARCHAR2(64),
disk_config VARCHAR2(16),
host_shape VARCHAR2(64),
host_class VARCHAR2(64),
num_cpu_cores NUMBER,
num_cpu_threads NUMBER,
maxed_out NUMBER,
cdb_weight NUMBER,
load_avg NUMBER,
load_p90 NUMBER,
load_p95 NUMBER,
load_p99 NUMBER,
aas_on_cpu_avg NUMBER,
aas_on_cpu_p90 NUMBER,
aas_on_cpu_p95 NUMBER,
aas_on_cpu_p99 NUMBER,
u02_size_1m NUMBER,
u02_used_1m NUMBER,
u02_available_1m NUMBER,
u02_size NUMBER,
u02_used NUMBER,
u02_available NUMBER,
fs_u02_util_perc NUMBER,
fs_u02_at_80p VARCHAR2(10),
fs_u02_at_90p VARCHAR2(10),
fs_u02_at_95p VARCHAR2(10),
db_version VARCHAR2(17),
dg_members NUMBER,
pdbs NUMBER,
kiev_flag VARCHAR2(1),
kiev_pdbs NUMBER,
wf_flag VARCHAR2(1),
wf_pdbs NUMBER,
casper_flag VARCHAR2(1),
casper_pdbs NUMBER,
-- extension
realm_type VARCHAR2(12), -- Commercial | Government
realm_type_order_by NUMBER,
realm VARCHAR2(12), -- R1, OC1, OC2, OC3, OC4
realm_order_by NUMBER,
region VARCHAR2(64),
region_acronym VARCHAR2(10),
region_order_by NUMBER,
locale VARCHAR2(4),
locale_order_by NUMBER
)
TABLESPACE iod
/
ALTER TABLE c##iod.cdb_attributes ADD (
disk_config VARCHAR2(16),
host_shape VARCHAR2(64),
host_class VARCHAR2(64)
)
/
ALTER TABLE c##iod.cdb_attributes ADD (
db_version VARCHAR2(17)
)
/
CREATE UNIQUE INDEX c##iod.cdb_attributes_pk
ON c##iod.cdb_attributes
(version, db_domain, db_name)
COMPRESS ADVANCED LOW
TABLESPACE IOD
/
-- SELECT * FROM c##iod.cdb_attributes WHERE (version, db_domain, db_name) IN (
-- SELECT DISTINCT version, db_domain, db_name
-- FROM c##iod.cdb_attributes
-- --WHERE version > SYSDATE - 30
-- GROUP BY version, db_domain, db_name
-- HAVING COUNT(*) > 1
-- )
-- /
-- DELETE c##iod.cdb_attributes WHERE version < SYSDATE - 30;
CREATE OR REPLACE
PROCEDURE c##iod.merge_cdb_attributes (
p_version IN VARCHAR2,
p_host_name IN VARCHAR2,
p_db_domain IN VARCHAR2,
p_disk_config IN VARCHAR2,
p_host_shape IN VARCHAR2,
p_host_class IN VARCHAR2,
p_num_cpu_cores IN NUMBER,
p_num_cpu_threads IN NUMBER,
p_maxed_out IN NUMBER,
p_cdb_weight IN NUMBER,
p_load_avg IN NUMBER,
p_load_p90 IN NUMBER,
p_load_p95 IN NUMBER,
p_load_p99 IN NUMBER,
p_aas_on_cpu_avg IN NUMBER,
p_aas_on_cpu_p90 IN NUMBER,
p_aas_on_cpu_p95 IN NUMBER,
p_aas_on_cpu_p99 IN NUMBER,
p_u02_size_1m IN NUMBER,
p_u02_used_1m IN NUMBER,
p_u02_available_1m IN NUMBER,
p_u02_size IN NUMBER,
p_u02_used IN NUMBER,
p_u02_available IN NUMBER,
p_fs_u02_util_perc IN NUMBER,
p_fs_u02_at_80p IN VARCHAR2,
p_fs_u02_at_90p IN VARCHAR2,
p_fs_u02_at_95p IN VARCHAR2,
p_db_name IN VARCHAR2,
p_db_version IN VARCHAR2,
p_dg_members IN NUMBER,
p_pdbs IN NUMBER,
p_kiev_pdbs IN NUMBER,
p_wf_pdbs IN NUMBER,
p_casper_pdbs IN NUMBER
)
IS
r c##iod.cdb_attributes%ROWTYPE;
BEGIN
r.version := p_version;
r.host_name := LOWER(TRIM(p_host_name));
r.db_domain := LOWER(TRIM(p_db_domain));
r.disk_config := LOWER(TRIM(p_disk_config));
r.host_shape := LOWER(TRIM(p_host_shape));
r.host_class := UPPER(TRIM(p_host_class));
r.num_cpu_cores := p_num_cpu_cores;
r.num_cpu_threads := p_num_cpu_threads;
r.maxed_out := p_maxed_out;
r.cdb_weight := p_cdb_weight;
r.load_avg := p_load_avg;
r.load_p90 := p_load_p90;
r.load_p95 := p_load_p95;
r.load_p99 := p_load_p99;
r.aas_on_cpu_avg := p_aas_on_cpu_avg;
r.aas_on_cpu_p90 := p_aas_on_cpu_p90;
r.aas_on_cpu_p95 := p_aas_on_cpu_p95;
r.aas_on_cpu_p99 := p_aas_on_cpu_p99;
r.u02_size_1m := p_u02_size_1m;
r.u02_used_1m := p_u02_used_1m;
r.u02_available_1m := p_u02_available_1m;
r.u02_size := p_u02_size;
r.u02_used := p_u02_used;
r.u02_available := p_u02_available;
r.fs_u02_util_perc := p_fs_u02_util_perc;
r.fs_u02_at_80p := p_fs_u02_at_80p;
r.fs_u02_at_90p := p_fs_u02_at_90p;
r.fs_u02_at_95p := p_fs_u02_at_95p;
r.db_name := UPPER(TRIM(p_db_name));
r.db_version := TRIM(p_db_version);
r.dg_members := p_dg_members;
r.pdbs := p_pdbs;
r.kiev_pdbs := p_kiev_pdbs;
r.wf_pdbs := p_wf_pdbs;
r.casper_pdbs := p_casper_pdbs;
--
r.region := C##IOD.IOD_META_AUX.get_region(r.host_name);
r.locale := C##IOD.IOD_META_AUX.get_locale(r.db_domain);
r.locale_order_by := C##IOD.IOD_META_AUX.get_locale_order_by(r.db_domain);
r.realm_type := C##IOD.IOD_META_AUX.get_realm_type(r.region);
IF r.realm_type = 'C' THEN r.realm_type := 'Commercial'; ELSE r.realm_type := 'Government'; END IF;
r.realm_type_order_by := C##IOD.IOD_META_AUX.get_realm_type_order_by(r.region);
r.realm := C##IOD.IOD_META_AUX.get_realm(r.region);
r.realm_order_by := C##IOD.IOD_META_AUX.get_realm_order_by(r.region);
r.region_acronym := C##IOD.IOD_META_AUX.get_region_acronym(r.region);
r.region_order_by := C##IOD.IOD_META_AUX.get_region_order_by(r.region);
--
IF p_kiev_pdbs > 0 THEN r.kiev_flag := 'Y'; ELSE r.kiev_flag := 'N'; END IF;
IF p_wf_pdbs > 0 THEN r.wf_flag := 'Y'; ELSE r.wf_flag := 'N'; END IF;
IF p_casper_pdbs > 0 THEN r.casper_flag := 'Y'; ELSE r.casper_flag := 'N'; END IF;
--
DELETE c##iod.cdb_attributes WHERE version = r.version AND db_domain = r.db_domain AND db_name = r.db_name;
INSERT INTO c##iod.cdb_attributes VALUES r;
COMMIT;
END merge_cdb_attributes;
/
SHOW ERRORS;