1 package body AD_ZD as
2 /* $Header: ADZDXB.pls 120.52.12020000.23 2013/06/21 13:16:40 rputchak ship $ */
3
4 C_PACKAGE CONSTANT VARCHAR2(80) := 'ad.plsql.ad_zd.';
5
6
7
8 /*
9 ** --------------------------------------------------------------------
10 ** Helper Functions
11 ** --------------------------------------------------------------------
12 */
13
14
15 /*
16 ** log message
17 */
18 procedure LOG(X_MODULE varchar2, X_LOG_TYPE varchar2, X_MESSAGE varchar2) is
19 begin
20 ad_zd_log.message(x_module, x_log_type, x_message);
21 end;
22
23 /*
24 ** log error message and raise exception
25 */
26 procedure ERROR(X_MODULE varchar2, X_MESSAGE varchar2) is
27 begin
28 ad_zd_log.message(x_module, 'ERROR', x_message);
29 raise_application_error(-20001, x_message);
30 end;
31
32
33
34 /*
35 ** Execute constructed SQL statement (VARCHAR2 version)
36 ** X_SQL - statement to execute
37 ** X_LOG_MOD - calling module (for logging)
38 ** X_IGNORE - ignore errors
39 **
40 ** Note: ignores "success with compilation error"
41 */
42 procedure EXEC(X_SQL in varchar2, X_LOG_MOD in varchar2, X_IGNORE in boolean default false) is
43 SUCCESS_WITH_COMPILATION_ERROR exception;
44 pragma exception_init(success_with_compilation_error, -24344);
45 begin
46
47 log(x_log_mod, 'STATEMENT', 'SQL: '||x_sql);
48 execute immediate x_sql;
49
50 exception
51 when success_with_compilation_error then
52 -- ignore "success with compilation error"
53 log(x_log_mod, 'STATEMENT', 'Ignored: '||SQLERRM);
54 when others then
55 -- ignore or raise other errors as requested
56 if x_ignore then
57 log(x_log_mod, 'STATEMENT', 'Ignored: '||SQLERRM);
58 else
59 log(x_log_mod, 'ERROR', 'ERROR: '||SQLERRM|| ', SQL: '||x_sql);
60 raise;
61 end if;
62 end;
63
64
65 /*
66 ** Execute constructed SQL statement (LOB version)
67 ** X_SQL - statement to execute
68 ** X_LOG_MOD - calling module (for logging)
69 ** X_IGNORE - ignore errors
70 **
71 ** Note: ignores "success with compilation error"
72 */
73 procedure EXEC(X_SQL in clob, X_LOG_MOD in varchar2, X_IGNORE in boolean default false) is
74 SUCCESS_WITH_COMPILATION_ERROR exception;
75 pragma exception_init(success_with_compilation_error, -24344);
76 L_CUR integer;
77 L_RET integer;
78 begin
79
80 log(x_log_mod, 'STATEMENT', 'SQL(CLOB): '||dbms_lob.substr(x_sql, 3900));
81
82 l_cur := dbms_sql.open_cursor;
83 dbms_sql.parse(l_cur, x_sql, dbms_sql.native);
84 l_ret := dbms_sql.execute(l_cur);
85 dbms_sql.close_cursor(l_cur);
86
87 exception
88 when success_with_compilation_error then
89 if dbms_sql.is_open(l_cur) then
90 dbms_sql.close_cursor(l_cur);
91 end if;
92 -- ignore "success with compilation error"
93 log(x_log_mod, 'STATEMENT', 'Ignored: '||SQLERRM);
94 when others then
95 if dbms_sql.is_open(l_cur) then
96 dbms_sql.close_cursor(l_cur);
97 end if;
98 -- ignore or raise other errors as requested
99 if x_ignore then
100 log(x_log_mod, 'STATEMENT', 'Ignored: '||SQLERRM);
101 else
102 log(x_log_mod, 'ERROR', 'ERROR: '||SQLERRM|| 'SQL(CLOB): '||dbms_lob.substr(x_sql, 3900));
103 raise;
104 end if;
105 end;
106
107
108 /*
109 ** Store statement for deferred for execution
110 ** x_phase - execution phase where statement should be run
111 ** x_sql - sql statement or pl/sql block to be executed.
112 */
113 procedure LOAD_DDL(X_PHASE in varchar2, X_SQL in varchar2) is
114 begin
115 ad_zd_parallel_exec.load(
116 x_phase => x_phase,
117 x_sql => to_clob(x_sql),
118 x_unique => true); -- check for uniqueness
119 end;
120
121
122 /*
123 ** Return APPS schema name
124 */
125 function APPS_SCHEMA return varchar2 is
126 L_SCHEMA varchar2(30);
127 begin
128 select oracle_username into l_schema
129 from system.fnd_oracle_userid
130 where read_only_flag ='U';
131
132 return l_schema;
133 end;
134
135
136 /*
137 ** Return APPLSYS schema name
138 */
139 function APPLSYS_SCHEMA return varchar2 is
140 L_SCHEMA varchar2(30);
141 begin
142 select oracle_username into l_schema
143 from system.fnd_oracle_userid
144 where read_only_flag ='E';
145
146 return l_schema;
147 end;
148
149 /*
150 ** Update LOGON trigger status.
151 X_STATUS - 'ENABLE' or 'DISABLE' the trigger
152 */
153 procedure ALTER_LOGON_TRIGGER(X_STATUS varchar2)
154 is
155 C_MODULE varchar2(80) := c_package||'alter_logon_trigger';
156 begin
157 log(c_module, 'EVENT', 'alter logon trigger : '|| x_status);
158 -- If anyone passes DISABLED or DISABLE or disable
159 if(upper(x_status) like 'DISABLE%') then
160 sys.ad_zd_sys.alter_logon_trigger('DISABLE');
161 elsif (upper(x_status) like 'ENABLE%') then
162 sys.ad_zd_sys.alter_logon_trigger('ENABLE');
163 else
164 error(c_module, 'The status: ' || x_status ||' is not a valid trigger status');
165 end if;
166 end ALTER_LOGON_TRIGGER;
167
168 /*
169 ** Return LOGON trigger status [ENABLED | DISABLED]
170 */
171 function LOGON_TRIGGER_STATUS return varchar2 is
172 L_STATUS varchar2(8) := null;
173 C_MODULE varchar2(80) := c_package||'logon_trigger_status';
174 begin
175
176 select status into l_status
177 from dba_triggers
178 where owner='SYSTEM'
179 and trigger_name='EBS_LOGON';
180
181 return l_status;
182 exception
183 when no_data_found then
184 error(c_module, 'SYSTEM.EBS_LOGON trigger does not exist');
185 end LOGON_TRIGGER_STATUS;
186
187
188 /*
189 ** Is database editioned ('Y'/'N')
190 */
191 function IS_EDITIONS_ENABLED return varchar2 is
192 C_MODULE varchar2(80) := c_package||'is_editions_enabled';
193 L_EDITIONS_ENABLED varchar2(30);
194 begin
195 -- test if this is an editioned database, do nothing if not.
196 select du.editions_enabled
197 into l_editions_enabled
198 from system.fnd_oracle_userid fou, dba_users du
199 where fou.read_only_flag = 'U'
200 and du.username = fou.oracle_username;
201
202 return l_editions_enabled;
203 end;
204
205
206 /*
207 ** Gets the name of the indicated edition type (NULL if none)
208 ** x_edition_type - type of edition to query
209 ** 'RUN' - current run edition
210 ** 'PATCH' - current patch edition
211 ** 'OLD' - old run edition (to be cleaned up after patch)
212 ** NULL - current edition
213 */
214 function GET_EDITION(x_edition_type in varchar2 default NULL) return varchar2 is
215 C_MODULE varchar2(80) := c_package||'get_edition';
216 L_DEFAULT varchar2(30);
217 L_EDITION varchar2(30);
218 begin
219 if x_edition_type is NULL then
220 return sys_context('userenv', 'current_edition_name');
221 end if;
222
223 /* Get default edition first */
224 select property_value into l_default
225 from database_properties
226 where property_name = 'DEFAULT_EDITION';
227
228 if x_edition_type = 'RUN' then
229
230 /* RUNTIME edition is always the default */
231 l_edition := l_default;
232
233 elsif x_edition_type = 'PATCH' then
234
235 /* PATCH edition is always the child of the default */
236 begin
237 select aed.edition_name into l_edition
238 from all_editions AED
239 where aed.parent_edition_name = l_default;
240 exception
241 when no_data_found then
242 l_edition := NULL;
243 end;
244
245 elsif x_edition_type = 'OLD' then
246
247 /* OLD edition is always the parent of the default */
248 begin
249 select aed.parent_edition_name into l_edition
250 from all_editions AED
251 where aed.edition_name = l_default;
252 exception
253 when no_data_found then
254 l_edition := NULL;
255 end;
256
257 else
258
259 log(c_module, 'ERROR', 'Invalid Edition Type: '||x_edition_type);
260 l_edition := NULL;
261
262 end if;
263
264 return l_edition;
265 end;
266
267
268 /*
269 ** Sets the current edition based on type
270 ** x_edition_type - type of edition to set
271 ** 'RUN' - run edition
272 ** 'PATCH' - patch edition
273 **
274 ** Note: this procedure will not take effect until the next top-level SQL call
275 */
276 procedure SET_EDITION(x_edition_type in varchar2) is
277 C_MODULE varchar2(80) := c_package||'set_edition';
278 L_EDITION varchar2(30);
279 begin
280 l_edition := get_edition(x_edition_type);
281
282 if (l_edition is not null) then
283 log(c_module, 'STATEMENT', 'Setting current edition to '||l_edition);
284 dbms_session.set_edition_deferred(l_edition);
285 else
286 error(c_module, 'Unable to set edition. Invalid edition type : '||x_edition_type);
287 end if;
288 end;
289
290
291 /*
292 ** Gets the edition type (NULL is Acestor of OLD or Edition does not exist )
293 ** x_edition - name of the edition
294 ** Pass NULL to find the current edition type
295 */
299 L_EDITION varchar2(30);
296 function GET_EDITION_TYPE(x_edition_name in varchar2 default NULL) return varchar2 is
297 C_MODULE varchar2(80) := c_package||'get_edition_type';
298 L_EDITION_TYPE varchar2(8) := NULL;
300 begin
301 if (x_edition_name is NULL) then
302 l_edition := sys_context('userenv', 'current_edition_name');
303 else
304 l_edition := upper(x_edition_name);
305 end if;
306
307 if l_edition = get_edition('RUN') then
308 l_edition_type := 'RUN';
309 elsif l_edition = get_edition('PATCH') then
310 l_edition_type := 'PATCH';
311 elsif l_edition = get_edition('OLD') then
312 l_edition_type := 'OLD';
313 end if;
314
315 return l_edition_type;
316 end GET_EDITION_TYPE;
317
318
319 /*
320 ** Check if there is enough free space in critical tablespaces
321 ** Free space requirements
322 ** SYSTEM tablespace: 25 GB free
323 ** APPS_TS_SEED tablespace: 5 GB free
324 **
325 ** RETURNS - Y - Enough free space
326 ** - N - Not enough free space
327 */
328 function CHECK_SPACE return varchar2 is
329 C_MODULE varchar2(80) := c_package|| 'check_space';
330 L_ALLOCATED_BYTES number;
331 L_FREE_BYTES number;
332 V_COUNT number := 0;
333
334 cursor C_FREE_SPACE(p_tablespace varchar2) is
335 select df.bytes allocated_bytes,
336 sum(fs.bytes) free_bytes
337 from dba_free_space fs,
338 (select sum(bytes) bytes
339 from dba_data_files
340 where tablespace_name = p_tablespace ) df
341 where fs.tablespace_name = p_tablespace
342 group by df.bytes;
343
344 type TS_NAMES_T is table of varchar2(30);
345 type TS_FREE_T is table of number;
346
347 TS_NAMES ts_names_t; -- tablespace name list
348 TS_FREE ts_free_t; -- tablespace minimum freespace list (gigabytes)
349 TS_COUNT number; -- number of tablespaces to check
350 begin
351
352 -- tablespace requirements
353 ts_names := new ts_names_t('SYSTEM', 'APPS_TS_SEED');
354 ts_free := new ts_free_t (25, 5); -- gigabytes
355 ts_count := 2;
356
357 -- check each tablespace for required freespace
358 for i in 1..ts_count loop
359
360 select count(*) into v_count
361 from dba_data_files
362 where tablespace_name = ts_names(i)
363 and autoextensible = 'yes';
364
365 if (v_count = 0) then
366 open c_free_space(ts_names(i));
367 fetch c_free_space into l_allocated_bytes, l_free_bytes;
368 close c_free_space;
369
370 if l_free_bytes < (ts_free(i)*power(2, 30)) then
371 log(c_module, 'WARNING',
372 'Not enough free space in '||ts_names(i)||' tablespace. '||ts_free(i)||'GB free space required.');
373 return 'N';
374 end if;
375 end if;
376
377 end loop;
378
379 return 'Y';
380 end check_space;
381
382
383
384 /*
385 ** Drop Covered Objects
386 **
387 ** Drop objects in retired editions that have a replacement object in any newer edition.
388 */
389 procedure DROP_COVERED_OBJECTS
390 IS
391 C_MODULE varchar2(80) := c_package||'drop_covered_objects';
392 L_PRIV_COUNT integer;
393 L_OWNER varchar2(30) := 'SYS';
394 L_LAST_EDITION varchar2(30);
395 L_SUCCESS boolean;
396
397 C_USER_CANCEL exception;
398 pragma exception_init(c_user_cancel, -1013);
399
400 -- Covered Objects are
401 -- actual objects in an Old Edition
402 -- that have a replacement object in a newer edition
403 -- Note: to minimize stub invalidation the result order
404 -- drop objects in newer editions first
408 select
405 -- drop objects in dependancy order (dependent object, then parent)
406 -- Never drop objects in the RUN or PATCH edition
407 cursor C_COVERED_OBJECTS is
409 oe.created edition_date
410 , oe.object_name edition_name
411 , decode(co.object_type,
412 'TRIGGER', 1,
413 'PACKAGE BODY', 2,
414 'TYPE BODY', 3,
415 'PROCEDURE', 4,
416 'FUNCTION', 5,
420 'TYPE', 9,
417 'PACKAGE', 6,
418 'VIEW', 7,
419 'SYNONYM', 8,
421 /*other*/ 99 ) drop_order
422 , co.owner
423 , co.object_type
424 , co.object_name
425 from
426 ( select
427 eusr.edition_name
428 , eusr.user_name owner
429 , obj.name object_name
430 , obj.type#
431 , decode(obj.type#,
432 4, 'VIEW',
433 5, 'SYNONYM',
434 7, 'PROCEDURE',
435 8, 'FUNCTION',
436 9, 'PACKAGE',
437 10, 'NON-EXISTENT',
438 11, 'PACKAGE BODY',
439 12, 'TRIGGER',
440 13, 'TYPE',
441 14, 'TYPE BODY', 'ERROR') object_type
442 , obj.namespace namespace
443 , obj.obj# object_id
444 from
445 sys.obj$ obj
446 , ( select
447 xusr.user#
448 , xusr.ext_username user_name
449 , ed.name edition_name
450 from
451 (select * from sys.user$ where type# = 2) xusr
452 , (select * from sys.obj$
453 where owner# = 0 and type# = 57) ed
454 where xusr.spare2 = ed.obj#
455 union
456 select
457 busr.user#
458 , busr.name user_name
459 , ed.name edition_name
460 from
461 (select * from sys.user$ where type#=1 or user#=1) busr
462 , (select * from sys.obj$ where owner#=0 and type#=57) ed
463 where ed.name = 'ORA$BASE' ) eusr
464 where obj.owner# = eusr.user#
465 and obj.type# not in (10, 0, 88)
466 and obj.remoteowner is null ) co
467 , dba_objects_ae oe /* old edition */
468 , dba_objects_ae re /* run edition */
469 , database_properties run /* run edition name */
470 where run.property_name = 'DEFAULT_EDITION'
471 /* run edition */
472 and re.owner = 'SYS'
473 and re.object_type = 'EDITION'
474 and re.object_name = run.property_value
475 /* old edition */
476 and oe.owner = 'SYS'
477 and oe.object_type = 'EDITION'
478 and oe.created < re.created
479 /* covered object */
480 and co.edition_name = oe.object_name
481 and co.object_type in
482 ('SYNONYM', 'VIEW',
483 'PACKAGE', 'PACKAGE BODY', 'TYPE', 'TYPE BODY',
484 'PROCEDURE', 'FUNCTION', 'TRIGGER') /* editioned type */
485 and exists
486 ( select null
487 from dba_objects_ae ro /* replacement object */
488 , dba_objects_ae ne /* newer edition */
489 where ro.owner = co.owner
490 and ro.object_type in (co.object_type, 'NON-EXISTENT')
491 and ro.object_name = co.object_name
492 and ro.namespace = co.namespace
493 and ro.edition_name = ne.object_name
494 and ne.owner = 'SYS'
495 and ne.object_type = 'EDITION'
496 and ne.created > oe.created
497 and ne.created <= re.created )
498 order by edition_date DESC, drop_order, co.owner, co.object_name;
499
500 BEGIN
501 log(c_module, 'PROCEDURE', 'begin');
502
503 -- verify all old editions are retired
504 select count(*)
505 into l_priv_count
506 from dba_tab_privs
507 where privilege = 'USE'
508 and owner = l_owner /* Added for GSCC error */
509 and table_name in ( select parent_edition_name from dba_editions);
510
511 if l_priv_count > 0 then
512 error(c_module, 'Ancestors of Current RUN Edition are not retired');
513 end if;
514
515 -- repeat the procedure until no covered objects remain
516 for loop_count in 1..3 loop
517 l_last_edition := 'NONE';
518 l_success := true;
519
520 -- Drop each covered object, in correct order
521 for objrec in c_covered_objects loop
522 if objrec.edition_name <> l_last_edition then
523 log(c_module, 'STATEMENT', 'Dropping covered objects in '||objrec.edition_name);
524 l_last_edition := objrec.edition_name;
525 end if;
526 begin
527 sys.ad_zd_sys.drop_covered_object(objrec.owner, objrec.object_name, objrec.object_type, objrec.edition_name);
528 exception
529 when c_user_cancel then
530 error(c_module, 'Cleanup cancelled');
531 when others then
532 l_success := false;
533 end;
534 end loop;
535
536 exit when l_success;
537 end loop;
538
539 if not l_success then
540 log(c_module, 'WARNING', 'Could not remove all covered objects');
541 end if;
542
543 commit;
544 log(c_module, 'PROCEDURE', 'end');
545 END;
546
547
548 /*
549 ** --------------------------------------------------------------------
550 ** Edition Control
551 ** --------------------------------------------------------------------
552 */
553
554
555 /*
556 ** Create new database Edition as child of current edition
557 **
558 ** Note: New edition names are expected to sort after old edition names.
559 */
560 procedure CREATE_EDITION is
561 C_MODULE varchar2(80) := c_package||'create_edition';
562 L_EDITION varchar2(30);
563 L_CUR_EDITION varchar2(30);
567 -- must not be an existing patch edition
564 L_DT_FMT varchar2(30) := 'YYYYMMDD_HH24MI';
565 begin
566
568 if ad_zd.get_edition('PATCH') is not null then
569 error(c_module, 'Patch Edition already exists');
570 end if;
571
572 -- Generate new edition name
573 l_cur_edition := ad_zd.get_edition('RUN');
574 l_edition := 'V_'||to_char(SYSDATE, l_dt_fmt);
575
576 if greatest(l_edition,l_cur_edition) = l_cur_edition then
577 l_edition := 'V_'||to_char(to_date(substr(l_cur_edition,3),l_dt_fmt)+1/(24*60),l_dt_fmt);
578 end if;
579
580 log(c_module, 'EVENT', 'Create Edition : '||l_edition);
581 exec('create edition '||l_edition, c_module);
582 exec('grant use on edition '||l_edition||' to PUBLIC', c_module);
583
584 end;
585
586
587 /*
588 ** Retire Edition
589 ** Revoke grants to USE edition, if it exists
590 ** x_edition_type - type of edition to retire
591 ** 'PATCH' - patch edition
592 ** 'OLD' - old edition
593 */
594 procedure RETIRE_EDITION(x_edition_type in varchar2) is
595 C_MODULE varchar2(80) := c_package|| 'retire_edition';
596 L_EDITION varchar2(30);
597 L_GRANTOR varchar2(30);
598 L_OWNER varchar2(30) := 'SYS';
599 begin
600
601 -- get edition and validate
602 l_edition := ad_zd.get_edition(x_edition_type);
603 if (l_edition is null) then
604 error(c_module, 'Edition Type '||nvl(x_edition_type, 'NULL')||' does not exist.');
605 end if;
606 if (l_edition = get_edition('RUN')) then
607 error(c_module, 'Cannot retire RUN edition.');
608 end if;
609
610 -- do it
611 begin
612 select grantor into l_grantor
613 from dba_tab_privs
614 where privilege = 'USE'
615 and owner = l_owner /* Added for GSCC error */
616 and table_name = l_edition
617 and grantee = 'PUBLIC';
618
619 if (l_grantor = 'SYS') then
620 sys.ad_zd_sys.retire_edition(l_edition);
621 else
622 log(c_module, 'EVENT', 'Retire Edition: '||l_edition);
623 exec('revoke use on edition '||l_edition||' from PUBLIC', c_module);
624 end if;
625
626 exception
627 when others then
628 log(c_module, 'STATEMENT', 'Edition '||l_edition||' is already retired');
629 end;
630
631 end;
632
633
634 /*
635 ** Drop old editions which have no actual objects
636 */
637 procedure DROP_OLD_EDITIONS is
638 C_MODULE varchar2(80) := c_package||'drop_old_editions';
639 L_EMPTY varchar2(2);
640 -- Old Editions
641 cursor C_OLD_EDITIONS is
642 select oe.edition_name
643 from dba_editions oe, database_properties re
644 where re.property_name = 'DEFAULT_EDITION'
645 and oe.edition_name < re.property_value
646 order by oe.edition_name;
647 begin
648 log(c_module, 'PROCEDURE', 'begin: no parameter(s)');
649
650 for erec in c_old_editions loop
651 -- test if edition is empty of objects
652 begin
653 select 'N' into l_empty
654 from dba_objects_ae obj
655 where obj.edition_name = erec.edition_name
656 and obj.object_type <> 'NON-EXISTENT'
657 and rownum = 1;
658 exception
659 when no_data_found then
660 l_empty := 'Y';
661 end;
662 exit when l_empty = 'N';
663
664 -- drop empty old edition
665 begin
666 sys.ad_zd_sys.drop_edition(erec.edition_name);
667 exception
668 when others then
672 end loop;
669 log(c_module, 'WARNING', 'Could not drop empty edition '||erec.edition_name);
670 exit;
671 end;
673
674 log(c_module, 'PROCEDURE', 'end');
675 end;
676
677
678 /*
679 ** Drop unwanted database edition
680 ** x_edition_type - type of edition to drop
681 ** 'PATCH' - current patch edition
682 ** 'OLD' - old run edition (to be cleaned up after patch)
683 */
684 procedure DROP_EDITION(x_edition_type in varchar2) is
685 C_MODULE varchar2(80) := c_package||'drop_edition';
686 L_EDITION varchar2(30);
687 begin
688 log(c_module, 'PROCEDURE', 'begin: '||x_edition_type);
689
690 -- get edition and validate
691 l_edition := ad_zd.get_edition(x_edition_type);
692 if (l_edition is null) then
693 error(c_module, 'Edition Type '||nvl(x_edition_type, 'NULL')||' does not exist.');
694 end if;
695 if (l_edition = get_edition('RUN')) then
696 error(c_module, 'Cannot drop RUN edition.');
697 end if;
698
699 -- do it
700 if x_edition_type = 'OLD' then
701 ad_zd.drop_old_editions;
702 else
703 sys.ad_zd_sys.drop_edition(l_edition);
704 end if;
705
706 log(c_module, 'PROCEDURE', 'end');
707 end;
708
709
710 /*
711 ** --------------------------------------------------------------------
712 ** Phase Control
713 ** --------------------------------------------------------------------
714 */
715
716
717 /*
718 ** Prepare System (create Patch Edition)
719 ** X_MODE - unused
720 */
721 procedure PREPARE(X_MODE in varchar2 default NULL) is
722 C_MODULE varchar2(80) := c_package||'prepare';
723 begin
724 log(c_module, 'PROCEDURE', 'begin: '||x_mode);
725 ad_zd_log.clear;
726
727 -- Clear ALL ddls
728 ad_zd_parallel_exec.cleanup;
729
730 -- TODO: verify EBS_LOGON trigger exists and is enabled.
731
732 -- Check freespace: does not work
733 /*
734 if check_space = 'N' then
735 log(c_module, 'WARNING', 'Not Enough free space');
736 end if;
737 */
738
739 log(c_module, 'EVENT', 'Prepare System');
740
741 -- Create Patch Edition
742 ad_zd.create_edition;
743
744 log(c_module, 'PROCEDURE', 'end');
745 -- commit;
746 end;
747
748
749 /*
750 ** Finalize System (step 1 of finalize process)
751 ** X_MODE - controls finalize processing
752 ** QUICK - standard finalize processing (default)
753 ** FULL - recompute dictionary stats
754 */
755 procedure FINALIZE(X_MODE in varchar2 default NULL) is
756 C_MODULE varchar2(80) := c_package||'finalize';
757 begin
758 log(c_module, 'PROCEDURE', 'begin: '||x_mode);
759
760 if (get_edition_type = 'RUN') and (get_edition('PATCH') is not null) then
761 error(c_module, 'Finalize can only be run from the Patch Edition.');
762 end if;
763
764 log(c_module, 'EVENT', 'Finalize System');
765
766 -- Finalize sub components
767 ad_zd_table.finalize;
768 ad_zd_mview.finalize;
769
770 -- Get post-finalize stats
771 if (x_mode = 'FULL') then
772 dbms_stats.gather_fixed_objects_stats;
773 dbms_stats.gather_dictionary_stats;
774 end if;
775
776 log(c_module, 'PROCEDURE', 'end');
777 end;
778
779
780 /*
781 ** Compile System (step 2 of finialize process)
782 ** must be called as a top level command
783 **
787 procedure COMPILE(X_MODE in varchar2 default NULL) is
784 ** X_MODE - NULL = compile current edition
785 ** 'FULL' = compile entire system
786 */
788 C_MODULE varchar2(80) := c_package||'compile';
789 NOT_IMPLEMENTED exception; pragma exception_init(not_implemented, -6550);
790 begin
791 -- For now, use FULL compile mode all the time
792 if x_mode = 'FULL' then
793 log(c_module, 'EVENT', 'Compile System');
794 execute immediate 'begin sys.utl_recomp.recomp_parallel; end;';
795 else
796 begin
797 log(c_module, 'EVENT', 'Compile Edition: '||get_edition);
798 execute immediate 'begin sys.utl_recomp.recomp_parallel(flags => sys.utl_recomp.new_edition); end;';
799 exception
800 when not_implemented then
801 log(c_module, 'STATEMENT', 'Note: Edition-specific compilation not supported on this system.');
802 compile('FULL');
803 end;
804 end if;
805 end;
806
807
808 /*
809 ** Cutover to patch edition
810 **
811 ** X_MODE - indicates how to process cutover actions
812 ** (default) - execute cutover actions inline
813 ** 'QUICK' - assume cutover actions were processed externally
814 ** by parallel workers, just do edition cutover
815 **
816 ** Note: this API now supports running in the Run Edition when
817 ** there is no Patch Edition. This is to support "developer mode",
818 ** which lets you execute cutover actions without actually changing
819 ** editions.
820 */
821 procedure CUTOVER(X_MODE in varchar2 default NULL) is
822 C_MODULE varchar2(80) := c_package||'cutover';
823 L_EDITION varchar2(30);
824
825 begin
826 log(c_module, 'PROCEDURE', 'begin: '||x_mode);
827
828 -- If there is a Patch Edition, then we must be in it
829 l_edition := ad_zd.get_edition('PATCH');
830 if (l_edition is not null) and (l_edition <> sys_context('userenv', 'current_edition_name')) then
831 error(c_module, 'Cutover can only be run from the Patch Edition.');
832 end if;
833
834 log(c_module, 'EVENT', 'Cutover System');
835
836 -- Execute cutover actions
837 if (x_mode is null) then
838 log(c_module, 'EVENT', 'Executing CUTOVER actions');
839
840 -- Execute stored CUTOVER DDL
841 ad_zd_parallel_exec.execute('CUTOVER', 1, 1, NULL);
842
843 -- Cutover sub-components
844 ad_zd_seed.cutover;
845 ad_zd_mview.cutover(x_execute => 1);
846 end if;
847
848 -- switch default edition to patch edition
849 if (l_edition is not null) then
850 log(c_module, 'EVENT', 'Switching to Patch Edition');
851 exec('alter database default edition = '||l_edition, c_module);
852 end if;
853
854 -- Retire Old Edition
855 begin
856 retire_edition('OLD');
857 exception
858 when others then
859 null;
860 end;
861
862 -- Kill Old Sessions
863 log(c_module, 'EVENT', 'Killing old sessions');
864 kill_sessions('OLD');
865
866 log(c_module, 'PROCEDURE', 'end');
867 end;
868
869
870 /*
871 ** Cleanup obsolete objects and data
872 **
873 ** X_MODE
874 ** 'QUICK' - minimal cleanup required to start a new patching cycle
875 ** NULL - standard cleanup, includes drop covered objects
876 ** 'FULL' - also marks unused columns (todo: drop old editions)
877 */
878 procedure CLEANUP(X_MODE in varchar2 default NULL) is
879 C_MODULE varchar2(80) := c_package||'cleanup';
880 L_EDITION varchar2(30);
881 begin
882 log(c_module, 'PROCEDURE', 'begin: '||x_mode);
883
884 l_edition := ad_zd.get_edition('RUN');
885 if (l_edition <> sys_context('userenv', 'current_edition_name')) then
886 error(c_module, 'Cleanup can only be run from the Run Edition');
887 end if;
888
889 if (ad_zd.get_edition('PATCH') is not null) then
890 error(c_module, 'Cannot Cleanup while Patch Edition exists');
891 end if;
892
893 log(c_module, 'EVENT', 'Cleanup System');
894
895 -- Drop covered objects, except in QUICK mode
896 if (x_mode = 'FULL') then
897 log(c_module, 'EVENT', 'Drop Covered Objects');
898 drop_covered_objects;
899 end if;
900
901 -- Execute stored CLEANUP DDL
902 ad_zd_parallel_exec.execute('CLEANUP', 1, 1, NULL);
903
904 -- Cleanup sub-components
905 ad_zd_seed.cleanup;
906 ad_zd_table.cleanup(NULL, NULL, x_mode);
907
911 ad_zd.drop_old_editions();
908 ad_zd_parallel_exec.cleanup(ad_zd_parallel_exec.c_phase_cutover);
909 ad_zd_parallel_exec.cleanup(ad_zd_parallel_exec.c_phase_abort);
910 if ( x_mode = 'FULL') then
912 end if;
913 log(c_module, 'PROCEDURE', 'end');
914 end;
915
916
917 /*
918 ** Abort patch edition
919 **
920 ** X_MODE - unused
921 */
922 procedure ABORT(X_MODE in varchar2 default null) is
923 C_MODULE varchar2(80) := c_package||'abort';
924 L_EDITION varchar2(30);
925 begin
926 log(c_module, 'PROCEDURE', 'begin: '||x_mode);
927
928 -- Must have Patch Edition
929 l_edition := ad_zd.get_edition('PATCH');
930 if (l_edition is null) then
931 error(c_module, 'There is no Patch Edition to abort.');
932 end if;
933 -- Must be in Run Edition
934 if (get_edition_type <> 'RUN') then
935 error(c_module, 'Abort can only be run from the Run Edition.');
936 end if;
937
938 -- Kill sessions connected to patch edition, other wise cannot drop it.
939 log(c_module, 'EVENT', 'Killing Patch Edition Sessions');
940 kill_sessions('PATCH');
941
942 log(c_module, 'EVENT', 'Aborting Patch Edition');
943 -- Cleanup deferred DDLs that are no longer relevant
944 ad_zd_parallel_exec.cleanup('CUTOVER');
945 ad_zd_parallel_exec.cleanup('CLEANUP');
946
947 -- Undo any effectively editioned changes
948 ad_zd_seed.abort;
949 ad_zd_table.abort;
950
951 -- Add product specific APIs
952 exec('begin fnd_conc.cancel_patch_requests; end;', c_module, true);
953
954 -- Drop Pach Edition
955 ad_zd.retire_edition('PATCH');
956 -- per bug 16237350 - we need to kill again right before the drop edition
957 log(c_module, 'EVENT', 'Killing Patch Edition Sessions');
958 kill_sessions('PATCH');
959 -- end fix for bug 16237350
960 ad_zd.drop_edition('PATCH');
961
962 log(c_module, 'PROCEDURE', 'end');
963 end;
964
965 /*
966 ** Kill sessions
967 **/
968 procedure KILL_SESSIONS(x_edition_type VARCHAR2) is
969 C_MODULE varchar2(80) := c_package||'kill_sessions';
970
971 -- Sessions of editions specified by parameter
972 cursor C_KILL is
973 select
977 , v$process p
974 'alter system kill session '||''''||s.sid||','||s.serial#||'''' kill
975 from
976 v$session s
978 , dba_objects_ae e
979 where s.type <> 'BACKGROUND'
980 and p.addr = s.paddr
981 and e.object_id = s.session_edition_id
982 and e.object_name = ad_zd.get_edition(x_edition_type)
983 and e.object_type = 'EDITION';
984 begin
985 log(c_module, 'PROCEDURE', 'begin: ');
986
987 for srec in c_kill loop
988 exec(srec.kill, c_module, true);
989 end loop;
990
991 log(c_module, 'PROCEDURE', 'end');
992 end;
993
994 end AD_ZD;
[Home] [Help]