· 5 years ago · Feb 06, 2020, 01:08 PM
1A) Mview jobs are running
2----------------------
31.
4SELECT log_user,what,broken,failures,next_date FROM dba_jobs where log_user in (select schema_name from schemas)
5and what like '%dbms_refresh.refresh%' ;
6
71. it should return 5 rows 4 as bss and 1 as odyssey
8
92. if it returns no rows then either the mviews are on commit or not present
10 If not present run the the ibmsupgradeutilities.exe ticking mviews recreate checkbox.
11 and run the above select statement
12
133. Mview inteschema grants.(run the FOLLOWING SQL) and it should return 3 rows
14
15--great topic here: https://manidba.wordpress.com/2011/08/05/troubleshooting-materialized-view-replication-in-oracle/
16
17 select GRANTEE,OWNER,TABLE_NAME from dba_tab_privs where grantee='ODYSSEY' AND TABLE_NAME LIKE '%MLOG%';
18
19 if it returns nothing run this:
20 GRANT SELECT ON BSS.MLOG$_VERSION TO ODYSSEY;
21 GRANT SELECT ON BSS.MLOG$_VERSION_PART TO ODYSSEY;
22 GRANT SELECT ON IBMS.MLOG$_SYSTEM_DEFAULT TO ODYSSEY;
23
24B).Check if the Master job exists and Active
25---------------------------------------------
26
27select LOG_USER, what,decode(broken,'N','Running','Not Running')"Oracle Job Status" ,failures,next_date,job,
28decode(ibms_job.master_job_running,'S','Stopped','Master job Active') "IBMS Master job Status" from dba_jobs where what like '%ibms_job.run_due_jobs;%'
29
30To stop master job:
31Begin
32Ibms_job.stop;
33End;
34/
35
36select * from dba_jobs
37
38---remove job from other schema without logging on to it
39begin
40sys.dbms_ijob.remove(job => 943244);
41end;
42
431. If there are multiple master job remove the non pilatdba jobs
44 and query again
45
46To remove, connect with the schema of the job you want to remove and run:
47
48begin
49ibms_job.remove_master_job;
50end;
51/
52To create the master job:
53
54Begin
55Ibms_job.create_master_job;
56End;
57/
58
59
602. if "Oracle Job Status" is Not Running
61run the Following
62begin
63ibms_job.remove_master_job;
64ibms_job.create_master_job;
65end;
66/
67and query again to check
683. if "IBMS Master job Status" is Stopped run the following
69
70
71
72begin
73ibms_job.resume;
74end;
75/
76
77and query again
78
79C) XDB validation, run the following
80-------------------------------------
81select owner,object_name,status from dba_objects where owner='XDB' AND STATUS='INVALID';
82if it returns rows run the following
83
84grant execute on utl_file to xdb;
85grant execute on utl_tcp to xdb;
86grant execute on utl_http to xdb;
87grant execute on utl_smtp to xdb;
88
89exec sys.utl_recomp.recomp_parallel(2);
90and re-query ensure returns no rows
91
92Declare
93A varchar2(2000);
94Begin
95A:=sys.run_sql('begin utl_recomp.recomp_parallel(2);end;');
96End;
97
98
99D) Check if apache is running
100---------------------------------
101
102select decode(odyurl.validate_dad,1,'Apache Running','Apache Not configured or not running') from dual;
103
104if not configure Apache Dads/Start apache/Ammend ODYURL
105
106select * from table_line where table_id='ODYURL'
107./opmnctl stopall
108./opmnctl startall
109
110
111E)Sequence Alert!!!Check for any sequence nearing MAXVALUE
112---------------------------------------------------------
113
1141. run the following sql to find the sequences about to reach the max value
115
116declare
117
118 v_check_tx_seq_reuse PLS_INTEGER;
119 v_check_impression_reuse PLS_INTEGER;
120 v_tx_seq_remaining_sql VARCHAR2(100) := 'select /*+ materialize parallel(auto) */ count(*) as reuse_count from TX_SEQ_REUSE_STR';
121 v_tx_seq_available_sql VARCHAR2(100) := 'select /*+ materialize parallel(auto) */ count(*) as reuse_count from TX_SEQ_REUSE_STR_ORIGINAL';
122 v_tx_seq_remaining NUMBER;
123 v_tx_seq_available NUMBER;
124
125 v_cur_seq_pos NUMBER;
126 v_last_free NUMBER;
127 v_diff NUMBER;
128
129 v_clear_area NUMBER;
130 v_percentage NUMBER;
131
132 PROCEDURE output_result (
133 i_last_number NUMBER,
134 i_seq_remaining NUMBER,
135 i_perc_remaining NUMBER,
136 i_sequence_owner VARCHAR2,
137 i_sequence_name VARCHAR2,
138 i_increment_by NUMBER,
139 i_min_seq NUMBER,
140 i_max_seq NUMBER,
141 i_comments VARCHAR2
142 )
143 IS
144 BEGIN
145 dbms_output.put_line(
146 RPAD(i_last_number, 11, ' ')
147 || CHR(9) ||
148 RPAD(i_seq_remaining, 13, ' ')
149 || CHR(9) ||
150 RPAD(i_perc_remaining, 15, ' ')
151 || CHR(9) ||
152 RPAD(i_sequence_owner, 30, ' ')
153 || CHR(9) ||
154 RPAD(i_sequence_name, 30, ' ')
155 || CHR(9) ||
156 RPAD(i_increment_by, 12, ' ')
157 || CHR(9) ||
158 RPAD(i_min_seq, 10, ' ')
159 || CHR(9) ||
160 RPAD(i_max_seq, 10, ' ')
161 || CHR(9) ||
162 i_comments
163 );
164 END output_result;
165
166BEGIN
167
168 dbms_output.put_line(
169 'LAST_NUMBER'
170 || CHR(9) ||
171 'SEQ_REMAINING'
172 || CHR(9) ||
173 'PERC_REMAINING'
174 || CHR(9) ||
175 RPAD('SEQUENCE_OWNER', 30, ' ')
176 || CHR(9) ||
177 RPAD('SEQUENCE_NAME', 30, ' ')
178 || CHR(9) ||
179 'INCREMENT_BY'
180 || CHR(9) ||
181 RPAD('MIN_SEQ', 10, ' ')
182 || CHR(9) ||
183 RPAD('MAX_SEQ', 10, ' ')
184 || CHR(9) ||
185 RPAD('STATUS', 10, ' ')
186 );
187 dbms_output.put_line(
188 '-----------'
189 || CHR(9) ||
190 '-------------'
191 || CHR(9) ||
192 '--------------'
193 || CHR(9) ||
194 '------------------------------'
195 || CHR(9) ||
196 '------------------------------'
197 || CHR(9) ||
198 '------------'
199 || CHR(9) ||
200 '----------'
201 || CHR(9) ||
202 '----------'
203 || CHR(9) ||
204 '------------------------------'
205 );
206
207 -- AUDSES$ needed to be cycled once reached 9 digits, since IBMS audit table catered only for 9 digits
208 FOR v IN
209 (
210 SELECT
211 a.last_number,
212 a.max_value,
213 999999999 - a.last_number as numbers_left,
214 CASE WHEN (999999999 - a.last_number) < 10000000
215 THEN 'Sequence must be altered ASAP'
216 ELSE
217 CASE WHEN (999999999 - a.last_number) < 100000000
218 THEN 'Sequence to be altered soon'
219 ELSE 'No Immediate Danger'
220 END
221 END AS status,
222 a.sequence_name
223 FROM
224 all_sequences a
225 WHERE
226 a.sequence_name = 'AUDSES$'
227 AND a.sequence_owner = 'SYS'
228 AND a.max_value <> 999999999
229 )
230 LOOP
231 output_result (
232 v.LAST_NUMBER,
233 v.numbers_left,
234 0,
235 'SYS',
236 'AUDSES$',
237 1,
238 0,
239 v.max_value,
240 v.status
241 );
242 END LOOP;
243
244 FOR v IN
245 (
246 SELECT
247 a.last_number,
248 a.max_value,
249 a.max_value - a.last_number as numbers_left,
250 CASE WHEN (a.max_value - a.last_number) < 1000000
251 THEN 'Sequence must be altered ASAP'
252 ELSE
253 CASE WHEN (a.max_value - a.last_number) < 10000000
254 THEN 'Sequence to be altered soon'
255 ELSE 'No Immediate Danger'
256 END
257 END AS status,
258 a.sequence_name
259 FROM
260 dba_sequences a
261 WHERE
262 a.sequence_name = 'DBMS_LOCK_ID'
263 AND a.sequence_owner = 'SYS'
264 )
265 LOOP
266 output_result (
267 v.LAST_NUMBER,
268 v.numbers_left,
269 0,
270 'SYS',
271 'DBMS_LOCK_ID',
272 1,
273 0,
274 v.max_value,
275 v.status
276 );
277 END LOOP;
278
279 -- TX_ID reuse
280 SELECT
281 COUNT(*)
282 INTO
283 v_check_tx_seq_reuse
284 FROM
285 table_line tl
286 WHERE
287 tl.table_id = 'PP_CON'
288 AND tl.table_code = 'TX_SEQ'
289 AND tl.logical_1 = 1;
290
291 IF v_check_tx_seq_reuse = 1
292 THEN
293 EXECUTE IMMEDIATE v_tx_seq_remaining_sql INTO v_tx_seq_remaining;
294 EXECUTE IMMEDIATE v_tx_seq_available_sql INTO v_tx_seq_available;
295
296 IF ROUND(v_tx_seq_remaining * 100 / v_tx_seq_available) < 40
297 THEN
298 output_result (
299 0,
300 v_tx_seq_remaining,
301 ROUND(v_tx_seq_remaining * 100 / v_tx_seq_available),
302 'BSS',
303 'TX_SEQ',
304 1,
305 0,
306 v_tx_seq_available,
307 ' '
308 );
309 END IF;
310 END IF;
311
312 BEGIN
313 SELECT
314 CASE WHEN t.object_value > 0 AND t.object_value < 999999999 THEN 1 ELSE 0 END
315 INTO
316 v_check_impression_reuse
317 FROM
318 system_default t
319 WHERE
320 t.object_name = 'IMPRESSIONS_MIGRATIONS_ID';
321 EXCEPTION
322 WHEN no_data_found THEN NULL;
323 END;
324
325 -- IMPRESSION_ID reuse
326 IF v_check_impression_reuse = 1
327 THEN
328 WITH get_last_id AS
329 (
330 SELECT
331 b.last_number AS last_id
332 FROM
333 all_sequences b
334 WHERE
335 b.sequence_owner = 'ODYSSEY'
336 AND b.sequence_name = 'IMPRESSION_SEQ'
337 ),
338 get_min_id AS
339 (
340 SELECT
341 NVL(MIN(im.impression_id), 0) AS min_id
342 FROM
343 impression im
344 WHERE
345 im.impression_id > (SELECT * FROM get_last_id)
346 )
347 SELECT
348 min_id - last_id
349 INTO
350 v_clear_area
351 FROM
352 get_min_id,
353 get_last_id;
354
355 IF v_clear_area < 100000000
356 THEN
357 output_result (
358 0,
359 0,
360 0,
361 ' ',
362 'IMPRESSIONS_SEQ',
363 0,
364 0,
365 0,
366 'Reuse Clear Area is ' || v_clear_area
367 );
368 END IF;
369
370 SELECT
371 ROUND(100 * sum1 / (SELECT b.max_value FROM all_sequences b WHERE b.sequence_owner = 'ODYSSEY' AND b.sequence_name = 'IMPRESSION_SEQ')) AS percentage_usage
372 INTO
373 v_percentage
374 FROM
375 (
376 SELECT COUNT(*) AS sum1
377 FROM impression
378 );
379
380 IF ROUND(v_percentage) > 60
381 THEN
382 output_result (
383 0,
384 0,
385 0,
386 ' ',
387 'IMPRESSIONS_SEQ',
388 0,
389 0,
390 0,
391 'Reuse Percentage is ' || v_percentage
392 );
393 END IF;
394
395 END IF;
396
397 FOR v IN
398 (
399 WITH seq as
400 (
401 SELECT
402 sequence_owner,
403 sequence_name,
404 case when increment_by > 0 then min_value else max_value end as min_seq,
405 case when increment_by > 0 then max_value else min_value end as max_seq,
406 increment_by,
407 last_number
408 from
409 all_sequences a
410 WHERE
411 sequence_owner in (select schema_name from schemas)
412 AND sequence_name NOT IN ( 'LISTS_SEQ','SAVED_SETTINGS_HEADER_SEQ')
413 and cycle_flag <> 'Y'
414 )
415 select
416 last_number,
417 seq_remaining,
418 round(seq_remaining * 100 / seq_available) as perc_remaining,
419 sequence_owner,
420 sequence_name,
421 increment_by,
422 min_seq,
423 max_seq
424 from
425 (
426 SELECT
427 a.*,
428 trunc((max_seq - last_number) / increment_by) as seq_remaining,
429 trunc((max_seq - min_seq) / increment_by) as seq_available
430 from
431 seq a
432 WHERE
433 NOT (
434 (a.sequence_name = 'TX_SEQ' AND v_check_tx_seq_reuse = 1)
435 OR (a.sequence_name = 'IMPRESSION_SEQ' AND v_check_impression_reuse = 1)
436 )
437 )
438 where
439 seq_remaining * 100 / seq_available < 40
440 )
441 LOOP
442 v_check_tx_seq_reuse := 0;
443 v_check_impression_reuse := 0;
444
445 output_result (
446 v.LAST_NUMBER,
447 v.SEQ_REMAINING,
448 v.PERC_REMAINING,
449 v.SEQUENCE_OWNER,
450 v.SEQUENCE_NAME,
451 v.INCREMENT_BY,
452 v.MIN_SEQ,
453 v.MAX_SEQ,
454 ' '
455 );
456
457 END LOOP;
458END ;
459
460
461
462Mail AllDevelopmentManagers@sintecmedia.com with the result if query returns records
463-----------------------------------------------------------------------------
464
465Note
466
467Discovery,ATT and Corus have already reached the MAXVAL for tx_seq sequence
468
469
470
471remove simba jobs:
472remove zazu and rafiki, what ever is there
473select * from dba_jobs
474begin
475sys.dbms_ijob.remove(job => 5295063);
476end;
477
478--duplicate objects
479
480select owner, object_name, object_type, count(*) from dba_objects
481group by owner, object_name, object_type
482having count(*) > 1
483order by owner, object_name, object_type
484
485
486Begin
487sys.dbms_stats.gather_schema_stats(ownname => 'GALAXYBE',estimate_percent => 100,block_sample => false,method_opt => 'for all columns size auto',cascade => true,options => 'gather',no_invalidate => false,gather_temp => false);
488end;
489/
490---
491
492--check datafiles location is in the same place
493
494
495declare
496COMMON_PATH varchar2(200);
497test_path varchar2(200);
498test_path1 varchar2(200);
499windows integer(2);
500linux integer(2);
501DIRECTORIES varchar(10000);
502begin
503
504select file_name into COMMON_PATH from dba_data_files where tablespace_name = 'ODYSSEY_DATA' and rownum <= 1;
505
506windows:=length(COMMON_PATH) - length(replace(COMMON_PATH,'\',null));
507linux:=length(COMMON_PATH) - length(replace(COMMON_PATH,'/',null));
508
509select file_name into test_path from dba_data_files where tablespace_name = 'ODYSSEY_DATA' and rownum <= 1;
510select file_name into test_path1 from dba_data_files where tablespace_name = 'BSS_DATA' and rownum <= 1;
511
512if (windows!=0) then
513 test_path:=substr(test_path, 1, instr(test_path, '\',1,windows));
514 test_path1:=substr(test_path1, 1, instr(test_path1, '\',1,windows));
515
516 if (test_path=test_path1) then
517 COMMON_PATH:=substr(COMMON_PATH, 1, instr(COMMON_PATH, '\',1,windows));
518 --dbms_output.Put_line(COMMON_PATH);
519 else
520 dbms_output.Put_line(test_path||' is different from '||test_path1||'Check the datafiles manually to obtain a common path');
521 end if;
522
523else
524 test_path:=substr(test_path, 1, instr(test_path, '/',1,linux));
525 test_path1:=substr(test_path1, 1, instr(test_path1, '/',1,linux));
526
527 if (test_path=test_path1) then
528 COMMON_PATH:=substr(COMMON_PATH, 1, instr(COMMON_PATH, '/',1,linux));
529 --dbms_output.Put_line(COMMON_PATH);
530 else
531 dbms_output.Put_line(test_path||' is different from '||test_path1||'Check the datafiles manually to obtain a common path');
532 end if;
533end if;
534
535end;