[Home] [Help]
PACKAGE BODY: ORACLE_OCM.MGMT_CONFIG
Source
1 PACKAGE body MGMT_CONFIG AS
2
3 JOB_NAME CONSTANT VARCHAR(40) := 'MGMT_CONFIG_JOB';
4 STATS_JOB_NAME CONSTANT VARCHAR(40) := 'MGMT_STATS_CONFIG_JOB';
5
6 /*
7 Checks to see if the job already exists
8 */
9 FUNCTION job_exists (job_name_in VARCHAR) RETURN BOOLEAN IS
10 l_job_cnt NUMBER;
11 BEGIN
12 select count(*) into l_job_cnt from
13 dba_scheduler_jobs WHERE job_name = job_name_in and owner ='ORACLE_OCM';
14 if l_job_cnt = 0
15 THEN
16 return FALSE;
17 ELSE
18 return TRUE;
19 END IF;
20 END job_exists;
21
22 /*
23 Submit a job to collect the configuration.
24 Basically, a job with what->collect_config
25 */
26 procedure submit_job IS
27 BEGIN
28 IF not job_exists(JOB_NAME) THEN
29 sys.dbms_scheduler.create_job(
30 job_name => JOB_NAME,
31 job_type => 'STORED_PROCEDURE',
32 job_action => 'ORACLE_OCM.MGMT_CONFIG.collect_config',
33 start_date=> SYSTIMESTAMP,
34 repeat_interval => 'freq=daily;byhour=01;byminute=01;bysecond=01',
35 end_date => NULL,
36 enabled => TRUE,
37 auto_drop => FALSE,
38 comments => 'Configuration collection job.');
39 COMMIT;
40 ELSE
41 RAISE_APPLICATION_ERROR(-20000,'Cannot resubmit. A job '''|| JOB_NAME
42 || '''already exists.');
43 END IF;
44 IF not job_exists(STATS_JOB_NAME) THEN
45 sys.dbms_scheduler.create_job(
46 job_name => STATS_JOB_NAME,
47 job_type => 'STORED_PROCEDURE',
48 job_action => 'ORACLE_OCM.MGMT_CONFIG.collect_stats',
49 start_date=> SYSTIMESTAMP,
50 repeat_interval => 'freq=monthly;interval=1;bymonthday=1;byhour=01;byminute=01;bysecond=01',
51 end_date => NULL,
52 enabled => TRUE,
53 auto_drop => FALSE,
54 comments => 'OCM Statistics collection job.');
55 COMMIT;
56 ELSE
57 RAISE_APPLICATION_ERROR(-20001,'Cannot resubmit. A job '''|| STATS_JOB_NAME
58 || '''already exists.');
59 END IF;
60 END submit_job;
61
62 /*
63 Submit a job to collect the configuration.
64 Basically, a job with what->collect_config_metrics(<collection directory>
65 */
66 procedure submit_job_for_inst(inst_id IN BINARY_INTEGER, p_inst_num IN BINARY_INTEGER,
67 p_job_name IN VARCHAR2,
68 p_job_action IN VARCHAR2, p_job_action2 in VARCHAR2) IS
69 l_job NUMBER;
70 l_par v$instance.PARALLEL%TYPE;
71 l_instNum v$instance.INSTANCE_NUMBER%TYPE;
72 BEGIN
73 BEGIN
74 IF not job_exists(p_job_name || '_' || inst_id) THEN
75 sys.dbms_scheduler.create_job(
76 job_name => p_job_name || '_' || inst_id,
77 job_type => 'PLSQL_BLOCK',
78 job_action => p_job_action ,
79 start_date => NULL,
80 repeat_interval => NULL,
81 enabled => FALSE,
82 auto_drop => TRUE,
83 comments => 'OCM collection job run for an instance.');
84 BEGIN
85 -- Use the instance_id attribute.
86 -- This may throw exception if not implemented in the version of
87 -- the database. We would be ignoring the exception it that case.
88 DBMS_SCHEDULER.SET_ATTRIBUTE (p_job_name || '_' || inst_id,'instance_id',inst_id);
89 EXCEPTION
90 WHEN OTHERS THEN NULL;
91 END;
92 DBMS_SCHEDULER.ENABLE (p_job_name || '_' || inst_id);
93 -- Run the job synchronously
94 -- DBMS_SCHEDULER.RUN_JOB(p_job_name || '_' || inst_id,FALSE);
95 COMMIT;
96 END IF;
97 EXCEPTION
98 WHEN OTHERS THEN
99 -- Don't raise an exception otherwise it fills the alert/trace
100 DBMS_OUTPUT.put_line('Do not raise an exception');
101 -- RAISE_APPLICATION_ERROR(-20000,'SQLERRM: ' || SQLERRM || ' SQLCODE: '|| SQLCODE);
102 END;
103
104 -- create 2nd job if specified
105 IF p_job_action2 is NOT NULL THEN
106 select PARALLEL into l_par from v$instance;
107 IF l_par = 'YES' THEN
108 select instance_number into l_instNum from v$instance;
109 IF l_instNum <> p_inst_num THEN
110 BEGIN
111 IF not job_exists(p_job_name || '_2_' || inst_id) THEN
112 sys.dbms_scheduler.create_job(
113 job_name => p_job_name || '_2_' || inst_id,
114 job_type => 'PLSQL_BLOCK',
115 job_action => p_job_action2 ,
116 start_date => NULL,
117 repeat_interval => NULL,
118 enabled => FALSE,
119 auto_drop => TRUE,
120 comments => 'OCM 2nd job run for RAC instance.');
121 BEGIN
122 -- Use the instance_id attribute.
123 -- This may throw exception if not implemented in the version of
124 -- the database. We would be ignoring the exception it that case.
125 DBMS_SCHEDULER.SET_ATTRIBUTE (p_job_name || '_2_' || inst_id,'instance_id',inst_id);
126 EXCEPTION
127 WHEN OTHERS THEN NULL;
128 END;
129 DBMS_SCHEDULER.ENABLE (p_job_name || '_2_' || inst_id);
130 COMMIT;
131 END IF;
132 EXCEPTION
133 WHEN OTHERS THEN
134 -- Don't raise an exception otherwise it fills the alert/trace
135 DBMS_OUTPUT.put_line('Do not raise an exception');
136 END;
137 END IF;
138 END IF;
139 END IF;
140 END submit_job_for_inst ;
141
142 /*
143 Runs the configuration collection job now.
144 */
145 procedure run_now IS
146 BEGIN
147 DBMS_SCHEDULER.RUN_JOB(JOB_NAME);
148 DBMS_SCHEDULER.RUN_JOB(STATS_JOB_NAME);
149 COMMIT;
150 END run_now;
151
152 /*
153 Print the job details.
154 */
155 procedure print_job_details IS
156 BEGIN
157 dbms_output.put_line('Configuration collection job name: ' || JOB_NAME);
158 dbms_output.put_line('Statistics collection job name: ' || STATS_JOB_NAME);
159 dbms_output.put_line('Job Schedule: DAILY');
160 END print_job_details;
161
162 /*
163 Stop (and drop) the job.
164 */
165 procedure stop_job IS
166 BEGIN
167
168 BEGIN
169 dbms_scheduler.disable(job_name, TRUE);
170 BEGIN
171 dbms_scheduler.stop_job(job_name, TRUE);
172 EXCEPTION WHEN others THEN
173 IF sqlcode = -27366 THEN NULL; --Suppress job not running error
174 ELSE raise;
175 END IF;
176 END;
177 dbms_scheduler.drop_job(job_name, TRUE);
178 EXCEPTION WHEN others THEN
179 IF sqlcode = -27475 THEN NULL; --Suppress job not existing error
180 ELSE raise;
181 END IF;
182 END;
183
184 BEGIN
185 dbms_scheduler.disable(stats_job_name, TRUE);
186 BEGIN
187 dbms_scheduler.stop_job(stats_job_name, TRUE);
188 EXCEPTION WHEN others THEN
189 IF sqlcode = -27366 THEN NULL; --Suppress job not running error
190 ELSE raise;
191 END IF;
192 END;
193 dbms_scheduler.drop_job(stats_job_name, TRUE);
194 EXCEPTION WHEN others THEN
195 IF sqlcode = -27475 THEN NULL; --Suppress job not existing error
196 ELSE raise;
197 END IF;
198 END;
199
200 COMMIT;
201 END stop_job;
202
203 /*
204 Config collection job
205 */
206 procedure collect_config IS
207 CURSOR l_res_cur IS select inst_id,instance_number from gv$instance;
208 BEGIN
209 FOR inst_id_row in l_res_cur LOOP
210 submit_job_for_inst(inst_id_row.inst_id, inst_id_row.instance_number, JOB_NAME,
211 'BEGIN ORACLE_OCM.MGMT_DB_LL_METRICS.COLLECT_CONFIG_METRICS(''ORACLE_OCM_CONFIG_DIR''); END;',
212 'BEGIN ORACLE_OCM.MGMT_DB_LL_METRICS.WRITE_DB_CCR_FILE(''ORACLE_OCM_CONFIG_DIR2'', TRUE); END;');
213 END LOOP;
214 END collect_config;
215
216 /*
217 Statistics collection job
218 */
219 procedure collect_stats IS
220 CURSOR l_res_cur IS select inst_id, instance_number from gv$instance;
221 BEGIN
222 FOR inst_id_row in l_res_cur LOOP
223 submit_job_for_inst(inst_id_row.inst_id, inst_id_row.instance_number, STATS_JOB_NAME,
224 'BEGIN ORACLE_OCM.MGMT_DB_LL_METRICS.collect_stats_metrics(''ORACLE_OCM_CONFIG_DIR''); END;',
225 NULL);
226 END LOOP;
227 END collect_stats;
228
229 END MGMT_CONFIG;