[Home] [Help]
PACKAGE BODY: APPS.FND_NET_SERVICES
Source
1 package body FND_NET_SERVICES as
2 /* $Header: AFCPNETB.pls 120.9 2010/07/28 05:18:49 upinjark ship $ */
3
4 -- This package uses dynamic SQL to query fnd_nodes, due to various
5 -- backward compatibility issues.
6
7 type alt_instance_type is table of varchar2(255) index by binary_integer;
8
9 function fmtline(p_text varchar2,
10 p_length number,
11 p_pad varchar2 default ' ') return varchar2
12 as
13 begin
14
15 return (rpad(substr(nvl(p_text,' '),1,p_length),p_length,p_pad) || ' ' );
16
17 end;
18
19 function fmtuline(p_length number) return varchar2
20 as
21 begin
22
23 return fmtline('-',p_length,'-');
24
25 end;
26
27 function platformNameToNo(platform_name varchar2) return varchar2
28 as
29 platform_code varchar2(20);
30 begin
31
32 if ( platform_name = 'Solaris' ) then
33 platform_code := '23';
34 elsif ( platform_name = 'HP-UX' ) then
35 platform_code := '59';
36 elsif ( platform_name = 'HPUX_IA64' ) then
37 platform_code := '197';
38 elsif ( platform_name = 'UNIX Alpha' ) then
39 platform_code := '87';
40 elsif ( platform_name = 'IBM AIX' ) then
41 platform_code := '212';
42 elsif ( platform_name = 'Intel_Solaris' ) then
43 platform_code := '173';
44 elsif ( platform_name = 'Linux' ) then
45 platform_code := '46';
46 elsif ( platform_name = 'LINUX_X86-64' ) then
47 platform_code := '226';
48 elsif ( platform_name = 'Windows NT' ) then
49 platform_code := '912';
50 elsif ( platform_name = 'LINUX_ZSER' ) then
51 platform_code := '209';
52 else
53 platform_code := '453';
54 end if;
55
56 return platform_code;
57 end;
58
59 /*==========================================================================*/
60
61 function buildNodeName(p_host_name varchar2,
62 p_domain varchar2,
63 p_platform varchar2) return varchar2
64 as
65
66 -- This function doesn't use %type as we have to allow for
67 -- the node_id column not even being available!
68
69 l_platform_code varchar2(20) := platformNameToNo(p_platform);
70 l_node_name varchar2(255):= p_host_name;
71 l_node_id number;
72
73 begin
74
75 if ( l_platform_code = '87' )
76 then
77
78 -- Special case for Alpha. afcpnode.sql still appends domain, although
79 -- no longer required.
80
81 l_node_name := p_host_name || '.' || p_domain;
82
83 l_node_id := null;
84
85 -- Do backwards compatibility checks. Use dynamic SQL as node_id
86 -- may not exist.
87
88 begin
89
90 execute immediate 'select a.node_id ' ||
91 ' from fnd_nodes a ' ||
92 ' where upper(a.node_name) = upper(:v1) '
93 into l_node_id
94 using l_node_name;
95
96 exception
97 when no_data_found then
98 l_node_id := null;
99 when others then
100 raise;
101 end;
102
103 -- When afcpnode.sql is fixed to remove Alpha check, we can then
104 -- make the default the host name. Uncomment next four lines.
105
106 -- if ( l_node_id is null )
107 -- then
108 -- l_node_name := p_host_name;
109 -- end if;
110
111 end if;
112
113 return l_node_name;
114
115 end;
116
117 /*==========================================================================*/
118
119 procedure do_descriptor_resolve ( p_descRec fnd_tns_alias_descriptions%rowtype,
120 p_alias_name varchar2,
121 p_alias_set_name varchar2,
122 p_Service_Name varchar2,
123 p_Instance_Name varchar2
124 )
125 as
126 l_root_desc fnd_tns_alias_descriptions%rowtype;
127 l_descRec fnd_tns_alias_descriptions%rowtype := p_descRec;
128 l_sourceDesc fnd_tns_alias_descriptions%rowtype;
129
130 l_valid_service number;
131 l_valid_instance number;
132 l_db_guid raw(16);
133 l_address_list_guid raw(16);
134
135 cursor c1(p_tns_description_guid raw) is
136 select a.Tns_Alias_Address_List_Guid,a.sequence_number,
137 a.failover,a.load_balance
138 from fnd_tns_alias_address_lists a
139 where a.Tns_Alias_Description_Guid = p_tns_description_guid
140 and a.sequence_number >= 0
141 order by a.sequence_number;
142
143 cursor c2(p_tns_address_list_guid raw) is
144 select a.Listener_Port_Guid
145 from fnd_tns_alias_addresses a
146 where a.Tns_Alias_Address_List_Guid = p_tns_address_list_guid
147 order by a.Listener_Port_Guid;
148
149 begin
150
151 -- We always do descriptor resolve, as there's no guarantee that an
152 -- resolved entry doesn't have new service/instance values.
153
154 -- Get the root descriptor for Service/Instance_Guid.
155
156 select a.*
157 into l_root_desc
158 from fnd_tns_alias_descriptions a
159 where a.tns_alias_guid = l_descRec.tns_alias_guid
160 and a.sequence_number = 0;
161
162 -- Get db_guid
163
164 select a.Db_Guid
165 into l_db_guid
166 from fnd_database_services a
167 where a.Db_Service_Guid = l_root_desc.Db_Service_Guid;
168
169 -- Is it a valid service ?
170
171 select count(*)
172 into l_valid_service
173 from fnd_database_services a
174 where a.Db_Guid = l_db_guid
175 and a.Service_Name = p_Service_Name;
176
177 -- Is it a valid Instance ?
178
179 if ( p_instance_name is not null )
180 then
181
182 select count(*)
183 into l_valid_instance
184 from fnd_database_instances a, fnd_database_services b,
185 fnd_db_service_members c
186 where a.db_guid = l_db_guid
187 and a.Instance_Name = p_Instance_name
188 and a.db_guid = b.db_guid
189 and b.Service_Name = p_Service_Name
190 and b.Db_Service_Guid = c.Db_Service_Guid
191 and c.Instance_Guid = a.Instance_Guid
192 and c.db_guid = b.db_guid;
193
194 end if;
195
196 if ( l_valid_service = 0 or
197 ( l_valid_instance = 0 and p_instance_name is not null ) )
198 then
199
200 -- Can't resolve - remove any addresses/address_lists and mark unresolved.
201
202 l_descRec.sequence_number := abs(l_descRec.sequence_number) * -1 ;
203 l_descRec.Db_Service_Guid := null;
204 l_descRec.Db_Instance_Guid:= null;
205 l_descRec.Service_Name := p_Service_Name;
206 l_descRec.Instance_Name := p_Instance_name;
207
208 delete from fnd_tns_alias_addresses a
209 where a.Tns_Alias_Address_List_Guid
210 in ( select b.Tns_Alias_Address_List_Guid
211 from fnd_tns_alias_address_lists b
212 where b.Tns_Alias_Description_Guid
213 = l_descRec.Tns_Alias_Description_Guid );
214
215 delete from fnd_tns_alias_address_lists a
216 where a.Tns_Alias_Description_Guid
217 = l_descRec.Tns_Alias_Description_Guid ;
218
219 fnd_app_system.register_tns_description
220 ( Alias_Name => p_alias_name,
221 Sequence_number=> l_descRec.sequence_number,
222 Failover => l_descRec.failover,
223 Load_Balance => l_descRec.load_balance,
224 Service_GUID => l_descRec.Db_Service_Guid,
225 Instance_Guid => l_descRec.Db_Instance_Guid,
226 Service_Name => l_descRec.Service_Name,
227 Instance_Name => l_descRec.Instance_Name,
228 Tns_Alias_Description_Guid =>
229 l_descRec.Tns_Alias_Description_Guid,
230 alias_set_name=> p_alias_set_name
231 );
232
233 return;
234
235 end if;
236
237 -- We can now resolve this service/instance.
238
239 -- Get the source descriptor
240
241 if ( p_instance_name is null )
242 then
243
244 select a.*
245 into l_sourceDesc
246 from fnd_tns_alias_descriptions a, fnd_databases b,
247 fnd_tns_aliases c
248 where b.db_guid = l_db_guid
249 and b.Default_Tns_Alias_Guid = c.tns_alias_guid
250 and c.tns_alias_guid = a.tns_alias_guid
251 and a.sequence_number = 0;
252
253 else
254
255 select a.*
256 into l_sourceDesc
257 from fnd_tns_alias_descriptions a, fnd_database_instances b,
258 fnd_tns_aliases c
259 where b.db_guid = l_db_guid
260 and b.Instance_Name = p_instance_name
261 and b.Default_Tns_Alias_Guid = c.tns_alias_guid
262 and c.tns_alias_guid = a.tns_alias_guid
263 and a.sequence_number = 0;
264
265 end if;
266
267 -- Update descriptor
268
269 l_descRec.sequence_number := abs(l_descRec.sequence_number) ;
270 l_descRec.Db_Service_Guid := l_sourceDesc.Db_Service_Guid;
271 l_descRec.Db_Instance_Guid:= l_sourceDesc.Db_Instance_Guid;
272 l_descRec.failover := l_sourceDesc.failover;
273 l_descRec.Load_Balance := l_sourceDesc.Load_Balance;
274
275 -- We need to keep the service/name as originally set by the alt list, to
276 -- enable us to resolve all non-zero descriptors. This is because even
277 -- a resolved descriptor needs to be re-built on each call to
278 -- register_dbnode.
279
280 -- l_descRec.Service_Name := l_sourceDesc.Service_name;
281 -- l_descRec.Instance_Name := l_sourceDesc.Instance_name;
282
283 fnd_app_system.register_tns_description
284 ( Alias_Name => p_alias_name,
285 Sequence_number=> l_descRec.sequence_number,
286 Failover => l_descRec.failover,
287 Load_Balance => l_descRec.load_balance,
288 Service_GUID => l_descRec.Db_Service_Guid,
289 Instance_Guid => l_descRec.Db_Instance_Guid,
290 Service_Name => l_descRec.Service_Name,
291 Instance_Name => l_descRec.Instance_Name,
292 Tns_Alias_Description_Guid =>
293 l_descRec.Tns_Alias_Description_Guid,
294 alias_set_name=> p_alias_set_name
295 );
296
297 -- Delete current addresses/list and clone from source.
298
299 delete from fnd_tns_alias_addresses a
300 where a.Tns_Alias_Address_List_Guid
301 in ( select b.Tns_Alias_Address_List_Guid
302 from fnd_tns_alias_address_lists b
303 where b.Tns_Alias_Description_Guid
304 = l_descRec.Tns_Alias_Description_Guid );
305
306 delete from fnd_tns_alias_address_lists a
307 where a.Tns_Alias_Description_Guid
308 = l_descRec.Tns_Alias_Description_Guid ;
309
310 for f_addrlist in c1(l_sourceDesc.Tns_Alias_Description_Guid) loop
311
312 l_address_list_guid := sys_guid();
313
314 fnd_app_system.register_tns_address_list
315 ( TNS_ALIAS_DESCRIPTION_GUID =>
316 l_descRec.Tns_Alias_Description_Guid,
317 Sequence_Number => f_addrlist.sequence_number,
318 Failover => f_addrlist.failover,
319 Load_Balance => f_addrlist.load_balance,
320 Tns_Alias_Address_List_Guid
321 => l_address_list_guid
322 );
323
324
325 for f_addr in c2(f_addrlist.Tns_Alias_Address_List_Guid) loop
326
327 fnd_app_system.register_tnsalias_address
328 ( TNS_ALIAS_ADDRESS_LIST_GUID => l_address_list_guid,
329 Listener_port_GUID=> f_addr.Listener_Port_GUID
330 );
331
332 end loop;
333
334 end loop;
335
336 end;
337
338 /*==========================================================================*/
339
340 procedure register_alias( p_alias_name varchar2,
341 p_alias_type varchar2,
342 p_Failover varchar2,
343 p_Load_Balance varchar2,
344 p_Service_Guid raw,
345 p_Instance_Guid raw,
346 p_alias_set_name varchar2,
347 p_alias_set_guid raw,
348 p_tns_alias_guid raw,
349 p_Listener_Port_Guid
350 raw,
351 p_alt_instance_table
352 alt_instance_type
353 )
354 as
355
356 alt_instance_table alt_instance_type := p_alt_instance_table;
357
358 l_descRec fnd_tns_alias_descriptions%rowtype;
359
360 l_tns_alias_guid raw(16) := p_tns_alias_guid;
361
362 cursor c1 (p_desc_guid raw)
363 is select a.Tns_Alias_Address_List_Guid,a.sequence_number
364 from fnd_tns_alias_address_lists a
365 where a.Tns_Alias_Description_Guid = p_desc_guid
366 and a.sequence_number >= 0
367 order by a.sequence_number;
368
369 l_address_list_seqno number;
370 l_address_list_guid raw(16);
371
372 l_alt_table_entries number;
373 l_actual_alt_count number;
374
375 l_service_name fnd_tns_alias_descriptions.service_name%type;
376 l_instance_name fnd_tns_alias_descriptions.instance_name%type;
377
378 type l_alt_inst_record is record
379 ( service_name fnd_tns_alias_descriptions.service_name%type,
380 instance_name fnd_tns_alias_descriptions.instance_name%type
381 );
382
383 type l_alt_inst_array is table of l_alt_inst_record index by binary_integer;
384 l_alt_inst_data l_alt_inst_array;
385
386 l_alt_inst_data_match boolean := true;
387
388 cursor c2(p_tns_alias_guid_parm raw)
389 is select a.Tns_Alias_Description_Guid,a.Sequence_Number,
390 abs(a.Sequence_Number) abs_sequence_number
391 from fnd_tns_alias_descriptions a
392 where a.tns_alias_guid = p_tns_alias_guid_parm
393 and a.Sequence_Number <> 0
394 order by abs(a.Sequence_Number);
395
396 cursor c3(p_tns_alias_guid_parm raw,p_actual_count number)
397 is select a.Tns_Alias_Description_Guid,a.Sequence_Number
398 from fnd_tns_alias_descriptions a
399 where a.tns_alias_guid = p_tns_alias_guid_parm
400 and a.Sequence_Number <> 0
401 and abs(a.Sequence_Number) > p_actual_count;
402
403 begin
404
405 fnd_app_system.register_tnsalias
406 ( Alias_Name => p_alias_name,
407 Alias_Type => p_alias_type,
408 Failover => p_Failover,
409 Load_Balance => p_Load_Balance,
410 TNS_ALIAS_GUID => l_tns_alias_guid,
411 alias_set_name => p_alias_set_name
412 );
413
414 -- If TNS ALIAS GUID is null, fetch actual GUID.
415
416 if ( l_tns_alias_guid is null )
417 then
418 select a.tns_alias_guid
419 into l_tns_alias_guid
420 from fnd_tns_aliases a, fnd_tns_alias_sets b
421 where b.tns_alias_set_name = p_alias_set_name
422 and b.tns_alias_set_guid = a.alias_set_guid
423 and a.alias_name = p_alias_name;
424 end if;
425
426 -- Register primary descriptor - zero
427
428 fnd_app_system.register_tns_description
429 ( Alias_Name => p_alias_name,
430 Sequence_Number=> 0,
431 Failover => p_Failover,
432 Load_Balance => p_Load_Balance,
433 Service_GUID => p_Service_Guid,
434 Instance_Guid => p_Instance_Guid,
435 Service_Name => null,
436 Instance_Name => null,
437 Tns_Alias_Description_Guid => null,
438 alias_set_name=> p_alias_set_name
439 );
440
441 select a.*
442 into l_descRec
443 from fnd_tns_alias_descriptions a
444 where a.Tns_Alias_Guid = l_tns_alias_guid
445 and a.Sequence_Number = 0;
446
447 -- Currently there is no way for autoconfig to tell the api about
448 -- multiple address lists - so we just list 0.
449
450 l_address_list_guid := null;
451 l_address_list_seqno:= 0;
452
453 for f_addr_list in c1(l_descRec.Tns_Alias_Description_Guid) loop
454
455 if ( f_addr_list.sequence_number = 0 )
456 then
457 l_address_list_guid := f_addr_list.Tns_Alias_Address_List_Guid;
458 l_address_list_seqno:= 0;
459 exit;
460 end if;
461
462 end loop;
463
464 if ( l_address_list_guid is null )
465 then
466 l_address_list_guid := sys_guid();
467 end if;
468
469 fnd_app_system.register_tns_address_list
470 ( TNS_ALIAS_DESCRIPTION_GUID =>
471 l_descRec.Tns_Alias_Description_Guid,
472 Sequence_Number => l_address_list_seqno,
473 Failover => p_Failover,
474 Load_Balance => p_Load_Balance,
475 Tns_Alias_Address_List_Guid
476 => l_address_list_guid
477 );
478
479 -- Register Address
480
481 fnd_app_system.register_tnsalias_address
482 ( TNS_ALIAS_ADDRESS_LIST_GUID => l_address_list_guid,
483 Listener_port_GUID=> p_Listener_Port_GUID
484 );
485
486 -- Nothing more to do if alt_instance_table is empty
487
488 if ( p_alt_instance_table.count = 0 )
489 then
490 return;
491 end if;
492
493 l_alt_table_entries := 0;
494
495 -- Build the data array.
496
497 for i in 1..p_alt_instance_table.count loop
498
499 exit when p_alt_instance_table(i) is null or
500 p_alt_instance_table(i) = '' ;
501
502 if ( instr(p_alt_instance_table(i),':') = 0 )
503 then
504 l_service_name := p_alt_instance_table(i);
505 l_instance_name:= null;
506 else
507
508 l_service_name := substr(p_alt_instance_table(i),1,
509 instr(p_alt_instance_table(i),':')-1);
510 l_instance_name:= substr(p_alt_instance_table(i),
511 instr(p_alt_instance_table(i),':')+1);
512 end if;
513
514 exit when ( l_service_name is null or l_service_name = '' );
515
516 -- OK, we've got something to do.
517
518 l_alt_inst_data(i).service_name := l_service_name;
519 l_alt_inst_data(i).instance_name:= l_instance_name;
520
521 end loop;
522
523 -- Description records are ordered by sequence, to ensure alt entries
524 -- are generated correctly. But the order from the context file
525 -- could have been changed, and be completely different to the
526 -- current records in fnd_tns_alias_descriptions. Since this can lead to
527 -- all sorts of f/key issues, we check that the order in the alt_inst_data
528 -- matches the current records. If there is any mis-match, we delete all
529 -- the descriptors and start with an empty list.
530
531 select count(*)
532 into l_actual_alt_count
533 from fnd_tns_alias_descriptions a
534 where a.tns_alias_guid = l_tns_alias_guid
535 and a.sequence_number <> 0 ;
536
537 if ( l_actual_alt_count <> l_alt_inst_data.count )
538 then
539 l_alt_inst_data_match := false;
540 end if;
541
542 for i in 1..l_alt_inst_data.count loop
543
544 exit when not l_alt_inst_data_match;
545
546 begin
547 select a.*
548 into l_descRec
549 from fnd_tns_alias_descriptions a
550 where a.tns_alias_guid = l_tns_alias_guid
551 and abs(a.sequence_number) = i;
552
553 if not ( l_descRec.Service_Name = l_alt_inst_data(i).service_name
554 and ( ( l_descRec.Instance_Name is null
555 and l_alt_inst_data(i).instance_name is null )
556 or
557 (l_descRec.Instance_Name=l_alt_inst_data(i).instance_name)
558 )
559 )
560 then
561 l_alt_inst_data_match := false;
562 end if;
563
564 exception
565 when no_data_found then
566 l_alt_inst_data_match := false;
567 end;
568
569 end loop;
570
571 -- If the data array doesn't match, delete all the descriptors.
572
573 if ( not l_alt_inst_data_match )
574 then
575
576 for f_unused in c3(l_tns_alias_guid,0) loop
577
578 delete from fnd_tns_alias_descriptions a
579 where a.Tns_Alias_Description_Guid =
580 f_unused.Tns_Alias_Description_Guid;
581
582 -- Is this a resolved descriptor ?
583
584 if ( f_unused.sequence_number > 0 )
585 then
586
587 -- Need to delete Address_Lists, and addresses.
588
589 delete from fnd_tns_alias_addresses a
590 where a.Tns_Alias_Address_List_Guid
591 in ( select b.Tns_Alias_Address_List_Guid
592 from fnd_tns_alias_address_lists b
593 where b.Tns_Alias_Description_Guid
594 = f_unused.Tns_Alias_Description_Guid );
595
596 delete from fnd_tns_alias_address_lists a
597 where a.Tns_Alias_Description_Guid
598 = f_unused.Tns_Alias_Description_Guid ;
599 end if;
600
601 end loop;
602
603 end if;
604
605 -- Now process the data array and update descriptors.
606
607 for i in 1..l_alt_inst_data.count loop
608
609 -- Do we have an existing description record?
610
611 begin
612 select a.*
613 into l_descRec
614 from fnd_tns_alias_descriptions a
615 where a.tns_alias_guid = l_tns_alias_guid
616 and abs(a.sequence_number) = i;
617
618 exception
619 when no_data_found then
620 l_descRec.Tns_Alias_Description_Guid := null;
621 end;
622
623 if ( l_descRec.Tns_Alias_Description_Guid is null )
624 then
625
626 -- Create an unresolved descriptor.
627
628 fnd_app_system.register_tns_description
629 ( Alias_Name => p_alias_name,
630 Sequence_Number=> i*-1,
631 Failover => p_Failover,
632 Load_Balance => p_Load_Balance,
633 Service_GUID => null,
634 Instance_Guid => null,
635 Service_Name => l_alt_inst_data(i).Service_Name,
636 Instance_Name => l_alt_inst_data(i).Instance_Name,
637 Tns_Alias_Description_Guid => null,
638 alias_set_name=> p_alias_set_name
639 );
640
641 select a.*
642 into l_descRec
643 from fnd_tns_alias_descriptions a
644 where a.tns_alias_guid = l_tns_alias_guid
645 and abs(a.sequence_number) = i;
646
647 end if;
648
649 -- Resolve the descriptor
650
651 do_descriptor_resolve( p_descRec => l_descRec,
652 p_alias_name => p_alias_name,
653 p_alias_set_name => p_alias_set_name,
654 p_Service_Name => l_alt_inst_data(i).Service_Name,
655 p_Instance_Name=> l_alt_inst_data(i).Instance_Name
656 );
657
658 end loop;
659
660 end;
661
662 /*==========================================================================*/
663
664 procedure register_db_alias( p_alias_name varchar2,
665 p_alias_type varchar2,
666 p_Failover varchar2,
667 p_Load_Balance varchar2,
668 p_Service_Guid raw,
669 p_Instance_Guid raw,
670 p_alias_set_name varchar2,
671 p_alias_set_guid raw,
672 p_tns_alias_guid raw,
673 p_Listener_Port_Guid
674 raw,
675 p_alt_instance_table
676 alt_instance_type
677 )
678 as
679 begin
680
681 register_alias( p_alias_name,
682 p_alias_type,
683 p_Failover,
684 p_Load_Balance,
685 p_Service_Guid,
686 p_Instance_Guid,
687 p_alias_set_name,
688 p_alias_set_guid,
689 p_tns_alias_guid,
690 p_Listener_Port_Guid,
691 p_alt_instance_table
692 );
693
694 end;
695
696 /*==========================================================================*/
697
698 procedure register_dbnode(SystemName varchar2,
699 ServerName varchar2,
700 SystemOwner varchar2,
701 SystemCSINumber varchar2,
702 DatabaseName varchar2,
703 InstanceName varchar2,
704 InstanceNumber varchar2,
705 ListenerPort varchar2,
706 ClusterDatabase varchar2,
707 ServiceName varchar2,
708 RemoteListenerName varchar2,
709 LocalListenerName varchar2,
710 HostName varchar2,
711 Domain varchar2,
712 OracleHomePath varchar2,
713 OracleHomeVersion varchar2,
714 OracleHomeName varchar2,
715 InterconnectName varchar2,
716 InstanceSid varchar2,
717 platform varchar2,
718 alt_service_instance_1 varchar2 default null,
719 alt_service_instance_2 varchar2 default null,
720 alt_service_instance_3 varchar2 default null,
721 alt_service_instance_4 varchar2 default null,
722 alt_service_instance_5 varchar2 default null,
723 alt_service_instance_6 varchar2 default null,
724 alt_service_instance_7 varchar2 default null,
725 alt_service_instance_8 varchar2 default null,
726 VirtualHostName varchar2 default null
727 )
728 as
729 l_node_id number;
730 l_node_name varchar2(255);
731 l_Oracle_Home_Id raw(16);
732 l_Server_Guid raw(16);
733 l_System_Guid raw(16);
734 l_db_guid raw(16);
735 l_db_service_guid raw(16);
736 l_db_Default_TNS_Alias_Guid
737 raw(16);
738 l_Listener_GUID raw(16);
739 l_remote_tns_alias_guid raw(16);
740 l_tns_db_alias raw(16);
741 l_tns_sid_alias raw(16);
742
743 l_tns_alias_set_guid_pub raw(16);
744 l_tns_alias_set_guid_int raw(16);
745 l_public_alias_set_name fnd_tns_alias_sets.TNS_Alias_set_Name%type;
746 l_internal_alias_set_name fnd_tns_alias_sets.TNS_Alias_set_Name%type;
747
748 l_listener_port_guid raw(16);
749
750 l_instRec fnd_database_instances%rowtype;
751
752 alt_instance_table alt_instance_type;
753 alt_check_table alt_instance_type;
754 empty_instance_table alt_instance_type;
755 alt_index number;
756 is_alt_duplicate boolean;
757
758 l_resolveDescRec fnd_tns_alias_descriptions%rowtype;
759
760 l_db_alias_exists number;
761
762 cursor c2(p_db_guid raw)
763 is select a.remote_listener_alias
764 from fnd_database_instances a
765 where a.db_guid = p_db_guid
766 and a.remote_listener_alias is not null;
767
768 cursor c3(p_listener_guid raw)
769 is select a.listener_port_guid,a.port
770 from fnd_tns_listener_ports a
771 where a.listener_guid = p_listener_guid;
772
773 -- Cursor c4 was originally designed just to fetch unresolved descriptors
774 -- (seq_no < 0 ). But because we clone alt descriptors we need to rebuild
775 -- every alt descriptor on each call. So we look for all non-zero
776 -- sequence_numbers. When we add a detail table to sit in between
777 -- FND_TNS_ALIASES and FND_TNS_ALIAS_DESCRIPTIONS we can remove this
778 -- restriction.
779
780 cursor c4(p_system_guid raw)
781 is select f.Tns_Alias_Description_Guid,
782 e.alias_name,d.tns_alias_set_name
783 from fnd_system_server_map a, fnd_app_servers b,
784 fnd_tns_alias_set_usage c,fnd_tns_alias_sets d,
785 fnd_tns_aliases e, fnd_tns_alias_descriptions f
786 where a.System_GUID = p_System_Guid
787 and a.Server_GUID = b.Server_GUID
788 and b.server_type = fnd_app_system.C_DB_SERVER_TYPE
789 and a.Server_GUID = c.Server_GUID
790 and c.tns_alias_set_guid = d.tns_alias_set_guid
791 and d.tns_alias_set_type = fnd_app_system.C_ALIAS_SET_NAME_PUB
792 and d.tns_alias_set_guid = e.alias_set_guid
793 and e.tns_alias_guid = f.tns_alias_guid
794 and f.sequence_number <> 0;
795
796 cursor c5(p_db_guid raw,p_instance_guid raw,p_service_name varchar2)
797 is select a.service_name,b.instance_name
798 from fnd_database_services a,fnd_database_instances b
799 where a.db_guid = p_db_guid
800 and b.db_guid = p_db_guid
801 and ( b.Instance_Guid <> p_instance_guid
802 or ( b.Instance_Guid = p_instance_guid and
803 a.service_name <> p_service_name )
804 )
805 order by 1,2;
806
807 begin
808
809 -- Set up the alt instance table.
810
811 alt_instance_table(1) := alt_service_instance_1;
812 alt_instance_table(2) := alt_service_instance_2;
813 alt_instance_table(3) := alt_service_instance_3;
814 alt_instance_table(4) := alt_service_instance_4;
815 alt_instance_table(5) := alt_service_instance_5;
816 alt_instance_table(6) := alt_service_instance_6;
817 alt_instance_table(7) := alt_service_instance_7;
818 alt_instance_table(8) := alt_service_instance_8;
819
820 -- Remove duplicates.
821
822 if ( alt_instance_table(1) is not null )
823 then
824 alt_check_table := alt_instance_table;
825
826 alt_index := 2;
827
828 for i in 2..alt_check_table.count loop
829
830 is_alt_duplicate := false;
831
832 for j in 1..(i-1) loop
833
834 if ( alt_check_table(i) = alt_check_table(j) )
835 then
836 is_alt_duplicate := true;
837 exit;
838 end if;
839 end loop;
840
841 if ( not is_alt_duplicate )
842 then
843 alt_instance_table(alt_index) := alt_check_table(i);
844 alt_index := alt_index + 1;
845 end if;
846 end loop;
847
848 alt_instance_table(alt_index) := null;
849 end if;
850
851 fnd_app_system.register_system(SystemName,SystemOwner,SystemCSINumber,
852 system_guid => null);
853
854 select a.System_Guid
855 into l_System_Guid
856 from fnd_apps_system a
857 where a.name = SystemName;
858
859 l_node_name := buildNodeName( p_host_name => HostName,
860 p_domain => Domain,
861 p_platform => platform );
862
863 fnd_app_system.register_node( name => l_node_name,
864 platform_id => platformNameToNo(platform),
865 forms_tier => 'N',
866 cp_tier => 'N',
867 web_tier => 'N',
868 admin_tier => 'N',
869 p_server_id => null,
870 p_address => null,
871 p_description => '',
872 p_host_name => HostName,
873 p_domain => Domain,
874 db_tier => 'Y',
875 p_virtual_ip => VirtualHostName );
876
877 execute immediate 'select a.node_id ' ||
878 ' from fnd_nodes a ' ||
879 ' where upper(a.node_name) = upper(:v1) '
880 into l_node_id
881 using l_node_name;
882
883 -- Need to create Oracle_Home before FND_SERVER.
884
885 fnd_app_system.register_oraclehome
886 ( name => OracleHomeName,
887 Node_Id => l_node_id,
888 Path => OracleHomePath,
889 Version => OracleHomeVersion,
890 Description => null,
891 File_System_GUID => null,
892 oracle_home_id => null
893 );
894
895 select a.Oracle_Home_Id
896 into l_Oracle_Home_Id
897 from fnd_oracle_homes a
898 where a.node_id = l_node_id
899 and a.path = OracleHomePath;
900
901 -- Register Server
902
903 fnd_app_system.register_server
904 ( Name => ServerName,
905 Node_Id => l_node_id,
906 Internal => 'Y',
907 Appl_Top_Guid => null,
908 Server_type =>
909 fnd_app_system.C_DB_SERVER_TYPE,
910 Pri_Oracle_Home => l_Oracle_Home_Id,
911 Aux_Oracle_Home => null,
912 server_guid => null
913 );
914
915 select a.Server_Guid
916 into l_Server_Guid
917 from fnd_app_servers a
918 where a.name = ServerName;
919
920 -- Register the Server Map
921
922 fnd_app_system.register_servermap ( Server_GUID => l_Server_GUID,
923 System_Guid => l_System_Guid
924 );
925
926 -- Register database
927
928 fnd_app_system.register_database
929 ( db_name => DatabaseName,
930 db_domain => Domain,
931 Default_TNS_Alias_Guid=> null,
932 Is_Rac_db => ClusterDatabase,
933 Version => OracleHomeVersion,
934 db_guid => null
935 );
936
937 select a.db_guid , a.Default_TNS_Alias_Guid
938 into l_db_guid, l_db_Default_TNS_Alias_Guid
939 from fnd_databases a
940 where a.db_name = DatabaseName
941 and a.db_domain = Domain;
942
943 fnd_app_system.register_Database_Asg
944 ( db_name => DatabaseName,
945 assignment => fnd_app_system.C_APP_DB_ASSIGNMENT,
946 db_domain => Domain
947 );
948
949 -- Register Instance
950
951 fnd_app_system.register_Instance
952 ( db_name => DatabaseName,
953 Instance_Name => InstanceName,
954 Instance_Number => InstanceNumber,
955 Sid_GUID => null,
956 Sid => InstanceSid,
957 Default_TNS_Alias_GUID => null,
958 Server_GUID => l_Server_Guid,
959 Local_Listener_Alias => null,
960 Remote_Listener_Alias => null,
961 Configuration => null,
962 Description => null,
963 Interconnect_name => InterconnectName,
964 Instance_guid => null,
965 db_domain => Domain
966 ) ;
967
968 select a.*
969 into l_instRec
970 from fnd_database_instances a
971 where a.db_guid = l_db_guid
972 and a.Instance_Name = InstanceName;
973
974 -- Register Sid - use Sid_Guid already allocated in fnd_database_instances.
975
976 fnd_app_system.register_Sid ( Sid => InstanceSid,
977 sid_guid => l_instRec.sid_guid
978 );
979 -- Register Service and member
980
981 fnd_app_system.register_service
982 ( Service_name => ServiceName,
983 db_name => DatabaseName,
984 db_domain => Domain,
985 Description => null,
986 db_service_guid => null
987 );
988
989 -- Get the common remote tns alias
990
991 l_remote_tns_alias_guid := l_instRec.remote_listener_alias;
992
993 for f_remote in c2(l_db_guid) loop
994
995 l_remote_tns_alias_guid := f_remote.remote_listener_alias;
996 exit;
997
998 end loop;
999
1000 if ( l_remote_tns_alias_guid is null )
1001 then
1002 l_remote_tns_alias_guid := sys_guid();
1003 end if;
1004
1005 select a.db_service_guid
1006 into l_db_service_guid
1007 from fnd_database_services a
1008 where a.db_guid = l_db_guid
1009 and a.Service_name = ServiceName;
1010
1011 fnd_app_system.register_service_Members
1012 ( db_name => DatabaseName,
1013 Instance_Name => InstanceName,
1014 Instance_Type => fnd_app_system.C_PREFERRED_INSTANCE,
1015 db_service_guid => l_db_service_guid,
1016 db_domain => Domain
1017 );
1018
1019 -- Register Alias Sets
1020
1021 l_public_alias_set_name := Servername ||'_' ||
1022 fnd_app_system.C_ALIAS_SET_NAME_PUB;
1023 l_internal_alias_set_name := Servername ||'_' ||
1024 fnd_app_system.C_ALIAS_SET_NAME_INT;
1025
1026 fnd_app_system.register_tnsalias_sets
1027 ( Alias_set_name => l_public_alias_set_name,
1028 Alias_set_type => fnd_app_system.C_ALIAS_SET_NAME_PUB );
1029
1030 select a.tns_alias_set_guid
1031 into l_tns_alias_set_guid_pub
1032 from fnd_tns_alias_sets a
1033 where a.tns_alias_set_name = l_public_alias_set_name;
1034
1035 fnd_app_system.register_aliasset_usage
1036 ( tns_alias_set_guid => l_tns_alias_set_guid_pub,
1037 server_guid => l_server_guid);
1038
1039 fnd_app_system.register_tnsalias_sets
1040 ( Alias_set_name => l_internal_alias_set_name ,
1041 Alias_set_type => fnd_app_system.C_ALIAS_SET_NAME_INT );
1042
1043 select a.tns_alias_set_guid
1044 into l_tns_alias_set_guid_int
1045 from fnd_tns_alias_sets a
1046 where a.tns_alias_set_name = l_internal_alias_set_name;
1047
1048 fnd_app_system.register_aliasset_usage
1049 ( tns_alias_set_guid => l_tns_alias_set_guid_int,
1050 server_guid => l_server_guid);
1051
1052 -- Register Instance Listener
1053
1054 fnd_app_system.register_Listener
1055 ( Listener_Name => ServerName,
1056 Server_Name => ServerName,
1057 tns_alias_name => InstanceName||'_'||
1058 fnd_app_system.C_LOCAL_ALIAS_ID,
1059 listener_guid => null,
1060 alias_set_name => l_internal_alias_set_name
1061 );
1062 select a.Listener_GUID
1063 into l_Listener_GUID
1064 from fnd_tns_listeners a
1065 where a.Server_GUID = l_Server_Guid
1066 and a.Listener_Name = ServerName;
1067
1068 -- Register Listener Ports
1069
1070 -- Special case for Autoconfig. There should only be one port. Since
1071 -- the caller can't pass in old/new values, we check for any existing
1072 -- port entries. If supplied ListenerPort does not exist, then use first
1073 -- available.
1074
1075 l_listener_port_guid := null;
1076
1077 for f_port in c3(l_Listener_GUID) loop
1078
1079 l_listener_port_guid := f_port.listener_port_guid;
1080
1081 exit when f_port.port = ListenerPort;
1082
1083 end loop;
1084
1085 fnd_app_system.register_listener_ports (Listener_name=>ServerName,
1086 Port =>ListenerPort,
1087 server_guid =>l_server_guid,
1088 Listener_Port_Guid
1089 =>l_listener_port_guid
1090 );
1091
1092 select a.listener_port_guid
1093 into l_listener_port_guid
1094 from fnd_tns_listener_ports a
1095 where a.listener_guid= l_Listener_GUID
1096 and a.port = ListenerPort;
1097
1098 -- Register local listener TNS alias
1099
1100 register_db_alias( p_alias_name => InstanceName||'_'||
1101 fnd_app_system.C_LOCAL_ALIAS_ID,
1102 p_alias_type =>
1103 fnd_app_system.C_LOCAL_INST_TNS_ALIAS_TYPE,
1104 p_Failover => 'N',
1105 p_Load_Balance => 'N',
1106 p_Service_GUID => null,
1107 p_Instance_Guid => null,
1108 p_alias_set_name => l_internal_alias_set_name,
1109 p_alias_set_guid => l_tns_alias_set_guid_int,
1110 p_tns_alias_guid => l_instRec.Local_Listener_Alias,
1111 p_Listener_Port_Guid => l_listener_port_guid,
1112 p_alt_instance_table => empty_instance_table
1113 );
1114
1115 -- Create Remote DB listener alias. We supply the Service Name, to make
1116 -- it easier to get all listeners.
1117
1118 -- Set Remote_Listener_Alias in fnd_database_instances if null.
1119
1120 if ( l_instRec.Remote_Listener_Alias is null or
1121 l_instRec.Remote_Listener_Alias <> l_remote_tns_alias_guid )
1122 then
1123 l_instRec.Remote_Listener_Alias := l_remote_tns_alias_guid;
1124
1125 fnd_app_system.register_Instance
1126 ( db_name => DatabaseName,
1127 Instance_Name => l_instRec.Instance_Name,
1128 Instance_Number => l_instRec.Instance_Number,
1129 Sid_GUID => l_instRec.Sid_GUID,
1130 Sid => l_instRec.Sid,
1131 Default_TNS_Alias_GUID => l_instRec.Default_TNS_Alias_GUID,
1132 Server_GUID => l_instRec.Server_GUID,
1133 Local_Listener_Alias => l_instRec.Local_Listener_Alias,
1134 Remote_Listener_Alias => l_instRec.Remote_Listener_Alias,
1135 Configuration => l_instRec.Configuration,
1136 Description => l_instRec.Description,
1137 Interconnect_name => l_instRec.Interconnect_name,
1138 Instance_Guid => l_instRec.Instance_Guid,
1139 db_domain => Domain
1140 ) ;
1141
1142 end if;
1143
1144 register_db_alias
1145 ( p_Alias_Name => DatabaseName||'_'||
1146 fnd_app_system.C_REMOTE_ALIAS_ID,
1147 p_Alias_Type =>
1148 fnd_app_system.C_REMOTE_INST_TNS_ALIAS_TYPE,
1149 p_Failover => 'N',
1150 p_Load_Balance => 'N',
1151 p_Service_GUID => l_db_service_guid,
1152 p_Instance_Guid => null,
1153 p_alias_set_name => l_internal_alias_set_name,
1154 p_alias_set_guid => l_tns_alias_set_guid_int,
1155 p_tns_alias_guid => l_remote_tns_alias_guid,
1156 p_Listener_Port_Guid => l_listener_port_guid,
1157 p_alt_instance_table => empty_instance_table
1158 );
1159
1160 -- Register Load Balance Alias.
1161
1162 register_db_alias
1163 ( p_Alias_Name => DatabaseName||'_'||
1164 fnd_app_system.C_BALANCE_ALIAS_ID,
1165 p_Alias_Type =>
1166 fnd_app_system.C_DB_BALANCE_TNS_ALIAS_TYPE,
1167 p_Failover => 'Y',
1168 p_Load_Balance => 'Y',
1169 p_Service_GUID => l_db_service_guid,
1170 p_Instance_Guid => null,
1171 p_alias_set_name => l_public_alias_set_name,
1172 p_alias_set_guid => l_tns_alias_set_guid_pub,
1173 p_tns_alias_guid => l_db_Default_TNS_Alias_Guid,
1174 p_Listener_Port_Guid => l_listener_port_guid,
1175 p_alt_instance_table => empty_instance_table
1176 );
1177
1178 -- Register Instance Alias, with and without failover
1179
1180 register_db_alias
1181 ( p_Alias_Name => InstanceName,
1182 p_Alias_Type =>
1183 fnd_app_system.C_DB_INST_TNS_ALIAS_TYPE,
1184 p_Failover => 'N',
1185 p_Load_Balance => 'N',
1186 p_Service_GUID => l_db_service_guid,
1187 p_Instance_Guid => l_instRec.Instance_Guid,
1188 p_alias_set_name => l_public_alias_set_name,
1189 p_alias_set_guid => l_tns_alias_set_guid_pub,
1190 p_tns_alias_guid => l_instRec.Default_TNS_Alias_GUID,
1191 p_Listener_Port_Guid => l_listener_port_guid,
1192 p_alt_instance_table => empty_instance_table
1193 );
1194
1195 register_db_alias
1196 ( p_Alias_Name => InstanceName||'_'||
1197 fnd_app_system.C_FAILOVER_ALIAS_ID,
1198 p_Alias_Type =>
1199 fnd_app_system.C_DB_INST_TNS_ALIAS_TYPE,
1200 p_Failover => 'Y',
1201 p_Load_Balance => 'N',
1202 p_Service_GUID => l_db_service_guid,
1203 p_Instance_Guid => l_instRec.Instance_Guid,
1204 p_alias_set_name => l_public_alias_set_name,
1205 p_alias_set_guid => l_tns_alias_set_guid_pub,
1206 p_tns_alias_guid => NULL,
1207 p_Listener_Port_Guid => l_listener_port_guid,
1208 p_alt_instance_table => alt_instance_table
1209 );
1210
1211 -- Register Database Alias as Instance.
1212
1213 -- Nothing to do if already assigned to another db set.
1214
1215 select count(*)
1216 into l_db_alias_exists
1217 from fnd_system_server_map a, fnd_app_servers b,
1218 fnd_tns_alias_set_usage c,fnd_tns_alias_sets d,
1219 fnd_tns_aliases e
1220 where a.System_GUID = l_System_Guid
1221 and a.Server_GUID = b.Server_GUID
1222 and b.server_type = fnd_app_system.C_DB_SERVER_TYPE
1223 and b.server_guid <> l_Server_Guid
1224 and a.Server_GUID = c.Server_GUID
1225 and c.tns_alias_set_guid = d.tns_alias_set_guid
1226 and d.tns_alias_set_type = fnd_app_system.C_ALIAS_SET_NAME_PUB
1227 and d.tns_alias_set_guid = e.alias_set_guid
1228 and e.alias_name = DatabaseName;
1229
1230 if ( l_db_alias_exists = 0 )
1231 then
1232
1233 begin
1234
1235 select a.TNS_ALIAS_GUID
1236 into l_tns_db_alias
1237 from fnd_tns_aliases a
1238 where a.Alias_Name = DatabaseName
1239 and a.alias_set_guid = l_tns_alias_set_guid_pub;
1240
1241 exception
1242 when no_data_found then
1243 l_tns_db_alias := sys_guid();
1244 end;
1245
1246 register_db_alias
1247 ( p_Alias_Name => DatabaseName,
1248 p_Alias_Type =>
1249 fnd_app_system.C_DB_INST_TNS_ALIAS_TYPE,
1250 p_Failover => 'N',
1251 p_Load_Balance => 'N',
1252 p_Service_GUID => l_db_service_guid,
1253 p_Instance_Guid => l_instRec.Instance_Guid,
1254 p_alias_set_name => l_public_alias_set_name,
1255 p_alias_set_guid => l_tns_alias_set_guid_pub,
1256 p_tns_alias_guid => l_tns_db_alias,
1257 p_Listener_Port_Guid => l_listener_port_guid,
1258 p_alt_instance_table => empty_instance_table
1259 );
1260
1261 register_db_alias
1262 ( p_Alias_Name => DatabaseName||'_'||
1263 fnd_app_system.C_FAILOVER_ALIAS_ID,
1264 p_Alias_Type =>
1265 fnd_app_system.C_DB_INST_TNS_ALIAS_TYPE,
1266 p_Failover => 'Y',
1267 p_Load_Balance => 'N',
1268 p_Service_GUID => l_db_service_guid,
1269 p_Instance_Guid => l_instRec.Instance_Guid,
1270 p_alias_set_name => l_public_alias_set_name,
1271 p_alias_set_guid => l_tns_alias_set_guid_pub,
1272 p_tns_alias_guid => NULL,
1273 p_Listener_Port_Guid => l_listener_port_guid,
1274 p_alt_instance_table => alt_instance_table
1275 );
1276 end if;
1277
1278 -- Register InstanceSid if different to database.
1279
1280 select count(*)
1281 into l_db_alias_exists
1282 from fnd_tns_aliases a
1283 where a.Alias_Name = InstanceSid
1284 and a.alias_set_guid = l_tns_alias_set_guid_pub;
1285
1286 if ( l_db_alias_exists = 0 )
1287 then
1288 l_tns_sid_alias := sys_guid();
1289 end if;
1290
1291 register_db_alias
1292 ( p_Alias_Name => InstanceSid,
1293 p_Alias_Type =>
1294 fnd_app_system.C_DB_INST_TNS_ALIAS_TYPE,
1295 p_Failover => 'N',
1296 p_Load_Balance => 'N',
1297 p_Service_GUID => l_db_service_guid,
1298 p_Instance_Guid => l_instRec.Instance_Guid,
1299 p_alias_set_name => l_public_alias_set_name,
1300 p_alias_set_guid => l_tns_alias_set_guid_pub,
1301 p_tns_alias_guid => l_tns_sid_alias,
1302 p_Listener_Port_Guid => l_listener_port_guid,
1303 p_alt_instance_table => empty_instance_table
1304 );
1305
1306 register_db_alias
1307 ( p_Alias_Name => InstanceSid||'_'||
1308 fnd_app_system.C_FAILOVER_ALIAS_ID,
1309 p_Alias_Type =>
1310 fnd_app_system.C_DB_INST_TNS_ALIAS_TYPE,
1311 p_Failover => 'Y',
1312 p_Load_Balance => 'N',
1313 p_Service_GUID => l_db_service_guid,
1314 p_Instance_Guid => l_instRec.Instance_Guid,
1315 p_alias_set_name => l_public_alias_set_name,
1316 p_alias_set_guid => l_tns_alias_set_guid_pub,
1317 p_tns_alias_guid => NULL,
1318 p_Listener_Port_Guid => l_listener_port_guid,
1319 p_alt_instance_table => alt_instance_table
1320 );
1321
1322
1323
1324 -- Now've registered, see if this instance fixes any unresolved
1325 -- descriptors. Note c4 fetches all alt descriptors not just unresolved
1326 -- descriptors. This is due to the model not having a detail table
1327 -- between FND_TNS_ALIASES and FND_TNS_ALIAS_DESCRIPTIONS.
1328
1329 for f_resolve in c4(l_system_guid) loop
1330
1331 select a.*
1332 into l_resolveDescRec
1333 from fnd_tns_alias_descriptions a
1334 where a.Tns_Alias_Description_Guid = f_resolve.Tns_Alias_Description_Guid;
1335
1336 do_descriptor_resolve( p_descRec => l_resolveDescRec,
1337 p_alias_name => f_resolve.alias_name,
1338 p_alias_set_name => f_resolve.tns_alias_set_name,
1339 p_Service_Name => l_resolveDescRec.Service_Name,
1340 p_Instance_Name=> l_resolveDescRec.Instance_Name
1341 );
1342
1343 end loop;
1344
1345 end;
1346
1347 /*==========================================================================*/
1348
1349 procedure register_app_alias( alias varchar2,
1350 type varchar2,
1351 system_guid raw,
1352 alias_set_name varchar2,
1353 alias_set_guid raw,
1354 auto_create boolean
1355 )
1356 as
1357 l_system_guid raw(16) := system_guid;
1358 l_db_guid raw(16);
1359 l_instance_guid raw(16);
1360 l_instance_tns_alias_guid raw(16);
1361 l_db_tns_alias_guid raw(16);
1362 l_db_service_guid raw(16);
1363 l_port_list_tns_guid raw(16);
1364 l_system_name varchar2(100);
1365
1366 l_tns_aliases_rec fnd_tns_aliases%rowtype;
1367 l_tns_descriptions_rec fnd_tns_alias_descriptions%rowtype;
1368
1369 l_db_alias_exists boolean;
1370 l_app_alias_exists boolean;
1371
1372 empty_instance_table alt_instance_type;
1373
1374 cursor c1(p_System_Guid raw) is
1375 select b.db_guid,b.default_tns_alias_guid instance_tns_alias_guid,
1376 a.default_tns_alias_guid db_tns_alias_guid,b.instance_guid
1377 from fnd_databases a,fnd_database_instances b,
1378 fnd_system_server_map c, fnd_app_servers d
1379 where c.system_guid = p_System_Guid
1380 and d.server_guid = c.server_guid
1381 and d.server_type = fnd_app_system.C_DB_SERVER_TYPE
1382 and b.server_guid = d.server_guid
1383 and b.db_guid = a.db_guid;
1384
1385 cursor c2(p_db_guid raw) is
1386 select a.db_service_guid
1387 from fnd_database_services a
1388 where a.db_guid = p_db_guid ;
1389
1390 cursor c3(p_alias_name varchar2,p_alias_set_guid raw,p_alias_type varchar2) is
1391 select a.tns_alias_guid,a.alias_type
1392 from fnd_tns_aliases a
1393 where a.alias_name = p_alias_name
1394 and a.alias_set_guid = p_alias_set_guid
1395 and ( a.alias_type = nvl(p_alias_type,a.alias_type)
1396 or a.alias_type = fnd_app_system.C_DB_INST_TNS_ALIAS_TYPE );
1397
1398 cursor c4(p_tns_alias_guid raw) is
1399 select c.listener_port_guid
1400 from fnd_tns_alias_descriptions a, fnd_tns_alias_address_lists b,
1401 fnd_tns_alias_addresses c
1402 where a.tns_alias_guid = p_tns_alias_guid
1403 and a.sequence_number= 0
1404 and b.Tns_Alias_Description_Guid = a.Tns_Alias_Description_Guid
1405 and c.Tns_Alias_Address_List_Guid = b.Tns_Alias_Address_List_Guid;
1406
1407 cursor c5(p_System_Guid raw,p_alias_name varchar2,p_alias_type varchar2) is
1408 select e.Tns_Alias_Guid,e.Alias_Type
1409 from fnd_system_server_map a,fnd_tns_alias_set_usage b,
1410 fnd_tns_alias_sets c, fnd_app_servers d,
1411 fnd_tns_aliases e
1412 where a.system_guid = p_System_Guid
1413 and a.server_guid = b.server_guid
1414 and b.tns_alias_set_guid = c.tns_alias_set_guid
1415 and c.tns_alias_set_type = fnd_app_system.C_ALIAS_SET_NAME_PUB
1416 and a.server_guid = d.server_guid
1417 and d.server_type = fnd_app_system.C_DB_SERVER_TYPE
1418 and e.alias_set_guid = b.tns_alias_set_guid
1419 and e.alias_name = p_alias_name
1420 and ( e.alias_type = nvl(p_alias_type,e.alias_type)
1421 or e.alias_type = fnd_app_system.C_DB_INST_TNS_ALIAS_TYPE );
1422
1423 cursor c6(p_tns_alias_guid raw) is
1424 select a.Tns_Alias_Description_Guid
1425 from fnd_tns_alias_descriptions a
1426 where a.Tns_Alias_Guid = p_tns_alias_guid
1427 and a.sequence_number >= 0
1428 order by a.sequence_number;
1429
1430 -- Always allow instance aliases, regardless of type.
1431
1432 begin
1433
1434 -- If already a valid db alias, nothing more to do.
1435
1436 for f_valid in c5(l_system_guid,alias,type) loop
1437
1438 return;
1439
1440 end loop;
1441
1442 -- If exists but of wrong type, adjust accordingly.
1443
1444 l_db_alias_exists := false;
1445
1446 for f_valid in c5(l_system_guid,alias,null) loop
1447
1448 l_db_alias_exists := true;
1449
1450 select a.*
1451 into l_tns_aliases_rec
1452 from fnd_tns_aliases a
1453 where a.tns_alias_guid = f_valid.Tns_Alias_Guid;
1454
1455 for f_desc in c6(f_valid.Tns_Alias_Guid) loop
1456
1457 select a.*
1458 into l_tns_descriptions_rec
1459 from fnd_tns_alias_descriptions a
1460 where a.Tns_Alias_Description_Guid = f_desc.Tns_Alias_Description_Guid;
1461
1462 exit;
1463
1464 end loop;
1465
1466 -- Adjust for type and set.
1467
1468 l_tns_aliases_rec.alias_type := type;
1469 l_tns_aliases_rec.Alias_Set_Guid := alias_set_guid;
1470 l_tns_aliases_rec.Tns_Alias_Guid := null;
1471
1472 exit;
1473
1474 end loop;
1475
1476 -- Check app alias.
1477
1478 l_app_alias_exists := false;
1479
1480 for f_app_alias in c3(alias,alias_set_guid,type) loop
1481
1482 return; -- Exact match - nothing to do.
1483
1484 end loop;
1485
1486 -- Exists but wrong type.
1487
1488 for f_app_alias in c3(alias,alias_set_guid,null) loop
1489
1490 l_app_alias_exists := true;
1491
1492 select a.*
1493 into l_tns_aliases_rec
1494 from fnd_tns_aliases a
1495 where a.alias_name = alias
1496 and a.alias_set_guid = alias_set_guid;
1497
1498 for f_desc in c6(l_tns_aliases_rec.Tns_Alias_Guid) loop
1499
1500 select a.*
1501 into l_tns_descriptions_rec
1502 from fnd_tns_alias_descriptions a
1503 where a.Tns_Alias_Description_Guid = f_desc.Tns_Alias_Description_Guid;
1504
1505 exit;
1506
1507 end loop;
1508
1509 l_tns_aliases_rec.alias_type := type;
1510
1511 end loop;
1512
1513 -- Always get the database defaults - needed for switching balance to
1514 -- instance, and setting the port list.
1515
1516 for f_db in c1(l_system_guid) loop
1517
1518 l_db_guid := f_db.db_guid;
1519 l_instance_guid := f_db.instance_guid;
1520 l_instance_tns_alias_guid := f_db.instance_tns_alias_guid;
1521 l_db_tns_alias_guid := f_db.db_tns_alias_guid;
1522
1523 for f_service in c2(l_db_guid) loop
1524
1525 l_db_service_guid := f_service.db_service_guid;
1526
1527 exit;
1528
1529 end loop;
1530
1531 exit;
1532
1533 end loop;
1534
1535 if ( l_db_guid is null )
1536 then
1537 return;
1538 end if;
1539
1540 -- No aliases? - set default guids as required.
1541
1542 if ( not l_db_alias_exists and not l_app_alias_exists )
1543 then
1544
1545 l_tns_aliases_rec.Tns_Alias_Guid := null;
1546 l_tns_aliases_rec.Alias_Name := alias;
1547 l_tns_aliases_rec.Alias_Set_Guid := alias_set_guid;
1548 l_tns_aliases_rec.Alias_Type := type;
1549
1550 l_tns_descriptions_rec.DB_Service_Guid := l_db_service_guid;
1551 l_tns_descriptions_rec.DB_Instance_Guid := l_instance_guid;
1552
1553 end if;
1554
1555 -- Set guid if null.
1556
1557 if ( l_tns_aliases_rec.Tns_Alias_Guid is null )
1558 then
1559 l_tns_aliases_rec.Tns_Alias_Guid := sys_guid();
1560 end if;
1561
1562 -- Adjust for load balance, instance.
1563
1564 if (l_tns_aliases_rec.Alias_Type = fnd_app_system.C_DB_BALANCE_TNS_ALIAS_TYPE)
1565 then
1566
1567 l_tns_aliases_rec.Failover := 'Y';
1568 l_tns_aliases_rec.Load_Balance := 'Y';
1569
1570 l_tns_descriptions_rec.DB_Instance_Guid := null;
1571
1572 else
1573
1574 l_tns_aliases_rec.Failover := 'Y';
1575 l_tns_aliases_rec.Load_Balance := 'N';
1576
1577 if ( l_tns_descriptions_rec.DB_Instance_Guid is null )
1578 then
1579
1580 -- This only occurs when we swap db/instance aliases within a context file.
1581 -- Just choose any instance_guid.
1582
1583 l_tns_descriptions_rec.DB_Instance_Guid := l_instance_guid;
1584 end if;
1585
1586 end if;
1587
1588 -- Build aliases if autocreate specified
1589
1590 if ( auto_create )
1591 then
1592
1593 -- Get the alias guid for the port list.
1594
1595 if (l_tns_aliases_rec.Alias_Type = fnd_app_system.C_DB_INST_TNS_ALIAS_TYPE )
1596 then
1597
1598 l_port_list_tns_guid := l_instance_tns_alias_guid;
1599 else
1600 l_port_list_tns_guid := l_db_tns_alias_guid ;
1601
1602 end if;
1603
1604 for f_address in c4(l_port_list_tns_guid) loop
1605
1606 register_alias
1607 ( p_Alias_Name => l_tns_aliases_rec.Alias_Name,
1608 p_Alias_Type => l_tns_aliases_rec.Alias_Type,
1609 p_Failover => l_tns_aliases_rec.Failover,
1610 p_Load_Balance => l_tns_aliases_rec.Load_Balance,
1611 p_Service_GUID => l_tns_descriptions_rec.DB_Service_Guid,
1612 p_Instance_Guid => l_tns_descriptions_rec.DB_Instance_Guid,
1613 p_alias_set_name => alias_set_name,
1614 p_alias_set_guid => alias_set_guid,
1615 p_tns_alias_guid => l_tns_aliases_rec.Tns_Alias_Guid,
1616 p_Listener_Port_Guid
1617 => f_address.Listener_port_GUID,
1618 p_alt_instance_table
1619 => empty_instance_table
1620 );
1621 end loop;
1622
1623 end if;
1624
1625 end;
1626
1627 /*==========================================================================*/
1628
1629 procedure register_appnode(SystemName in varchar2,
1630 ServerName in varchar2,
1631 SystemOwner in varchar2,
1632 SystemCSINumber in varchar2,
1633 HostName in varchar2,
1634 Domain in varchar2,
1635 RPCPort in varchar2,
1636 PriOracleHomePath in varchar2,
1637 PriOracleHomeVersion in varchar2,
1638 PriOracleHomeName in varchar2,
1639 AuxOracleHomePath in varchar2,
1640 AuxOracleHomeVersion in varchar2,
1641 AuxOracleHomeName in varchar2,
1642 ApplTopPath in varchar2,
1643 ApplTopName in varchar2,
1644 SharedApplTop in varchar2,
1645 ToolsInstanceAlias in out nocopy varchar2,
1646 WebInstanceAlias in out nocopy varchar2,
1647 SidDefaultAlias in out nocopy varchar2,
1648 JDBCSid in out nocopy varchar2,
1649 isFormsNode in varchar2 default 'Y',
1650 isCPNode in varchar2 default 'Y',
1651 isWebNode in varchar2 default 'Y',
1652 isAdminNode in varchar2 default 'Y',
1653 platform in varchar2,
1654 forceMissingAliases
1655 in varchar2 default 'N'
1656 )
1657 as
1658 l_node_id number;
1659 l_node_name varchar2(255);
1660 l_Pri_Oracle_Home_Id raw(16);
1661 l_Aux_Oracle_Home_Id raw(16);
1662 l_Appl_Top_Guid raw(16);
1663 l_Server_Guid raw(16);
1664 l_System_Guid raw(16);
1665 l_Listener_GUID raw(16);
1666 l_listener_port_guid raw(16);
1667
1668 l_auto_create_aliases boolean := false;
1669
1670 l_tns_alias_set_guid_pub raw(16);
1671 l_public_alias_set_name fnd_tns_alias_sets.TNS_Alias_set_Name%type;
1672
1673 l_fndfs_tns_alias raw(16);
1674 l_fndsm_tns_alias raw(16);
1675 l_fndfs_sid_alias raw(16);
1676 l_fndsm_sid_alias raw(16);
1677
1678 l_fndsm_alias varchar2(255);
1679 l_fndfs_alias varchar2(255);
1680
1681 l_fndsm_sid varchar2(255);
1682 l_fndfs_sid varchar2(255);
1683
1684 type l_fndsmfs_record is record
1685 ( alias varchar2(255),
1686 sid varchar2(255)
1687 );
1688
1689 type l_fndsmfs_table is table of l_fndsmfs_record index by binary_integer;
1690 l_fndsmfs l_fndsmfs_table;
1691
1692 empty_instance_table alt_instance_type;
1693
1694 cursor c1(p_System_Guid raw) is
1695 select c.alias_name instance_alias,d.alias_name load_balance_alias,
1696 a.db_name
1697 from fnd_databases a, fnd_database_instances b, fnd_tns_aliases c,
1698 fnd_tns_aliases d
1699 where b.default_tns_alias_guid = c.tns_alias_guid
1700 and b.db_guid = a.db_guid
1701 and a.Default_TNS_Alias_GUID = d.tns_alias_guid
1702 and b.server_guid in ( select x.server_guid
1703 from fnd_system_server_map x, fnd_app_servers y
1704 where x.system_guid = p_System_Guid
1705 and y.server_guid = x.server_guid
1706 and y.server_type =
1707 fnd_app_system.C_DB_SERVER_TYPE
1708 ) ;
1709
1710 cursor c3(p_listener_guid raw)
1711 is select a.listener_port_guid,a.port
1712 from fnd_tns_listener_ports a
1713 where a.listener_guid = p_listener_guid;
1714
1715 begin
1716
1717 if ( upper(forceMissingAliases) = 'Y' )
1718 then
1719 l_auto_create_aliases := true;
1720 end if;
1721
1722 fnd_app_system.register_system(SystemName,SystemOwner,SystemCSINumber,
1723 System_guid=>null);
1724
1725 select a.System_Guid
1726 into l_System_Guid
1727 from fnd_apps_system a
1728 where a.name = SystemName;
1729
1730 l_node_name := buildNodeName( p_host_name => HostName,
1731 p_domain => Domain,
1732 p_platform => platform );
1733
1734 fnd_app_system.register_Node( name => l_node_name,
1735 platform_id => platformNameToNo(platform),
1736 forms_tier => isFormsNode,
1737 cp_tier => isCPNode,
1738 web_tier => isWebNode,
1739 admin_tier => isAdminNode,
1740 p_server_id => null,
1741 p_address => null,
1742 p_description => '' ,
1743 p_host_name => HostName,
1744 p_domain => Domain,
1745 db_tier => 'N' );
1746
1747 execute immediate 'select a.node_id ' ||
1748 ' from fnd_nodes a ' ||
1749 ' where upper(a.node_name) = upper(:v1) '
1750 into l_node_id
1751 using l_node_name;
1752
1753 -- Need to create Oracle_Homes and APPL_TOP before FND_SERVER.
1754
1755 fnd_app_system.register_oraclehome
1756 ( name => PriOracleHomeName,
1757 Node_Id => l_node_id,
1758 Path => PriOracleHomePath,
1759 Version => PriOracleHomeVersion,
1760 Description => null,
1761 File_System_GUID => null,
1762 oracle_home_id => null
1763 );
1764
1765 fnd_app_system.register_oraclehome
1766 ( name => AuxOracleHomeName,
1767 Node_Id => l_node_id,
1768 Path => AuxOracleHomePath,
1769 Version => AuxOracleHomeVersion,
1770 Description => null,
1771 File_System_GUID => null,
1772 oracle_home_id => null
1773 );
1774
1775 fnd_app_system.register_appltop
1776 ( name => ApplTopName,
1777 Node_Id => l_node_id,
1778 Path => ApplTopPath,
1779 Shared => SharedApplTop,
1780 File_System_GUID => null,
1781 appl_top_guid => null
1782 );
1783
1784 -- Get Home Ids
1785
1786 select a.Oracle_Home_Id
1787 into l_Pri_Oracle_Home_Id
1788 from fnd_oracle_homes a
1789 where a.node_id = l_node_id
1790 and a.path = PriOracleHomePath;
1791
1792 select a.Oracle_Home_Id
1793 into l_Aux_Oracle_Home_Id
1794 from fnd_oracle_homes a
1795 where a.node_id = l_node_id
1796 and a.path = AuxOracleHomePath;
1797
1798 select a.appl_top_guid
1799 into l_Appl_Top_Guid
1800 from fnd_appl_tops a
1801 where a.node_id = l_node_id
1802 and a.path = ApplTopPath;
1803
1804 -- Register Server
1805
1806 fnd_app_system.register_server
1807 ( Name => ServerName,
1808 Node_Id => l_node_id,
1809 Internal => 'Y',
1810 Appl_Top_Guid => l_Appl_Top_Guid,
1811 Server_type =>
1812 fnd_app_system.C_APP_SERVER_TYPE,
1813 Pri_Oracle_Home => l_Pri_Oracle_Home_Id,
1814 Aux_Oracle_Home => l_Aux_Oracle_Home_Id,
1815 server_guid => null
1816 );
1817
1818 select a.Server_Guid
1819 into l_Server_Guid
1820 from fnd_app_servers a
1821 where a.name = ServerName;
1822
1823 -- Register the Server Map
1824
1825 fnd_app_system.register_servermap ( Server_GUID => l_Server_GUID,
1826 System_Guid => l_System_Guid
1827 );
1828
1829 -- Register Alias Sets PUBLIC
1830
1831 l_public_alias_set_name := Servername ||'_' ||
1832 fnd_app_system.C_ALIAS_SET_NAME_PUB;
1833
1834 fnd_app_system.register_tnsalias_sets
1835 ( Alias_set_name => l_public_alias_set_name,
1836 Alias_set_type => fnd_app_system.C_ALIAS_SET_NAME_PUB );
1837
1838 select a.tns_alias_set_guid
1839 into l_tns_alias_set_guid_pub
1840 from fnd_tns_alias_sets a
1841 where a.tns_alias_set_name = l_public_alias_set_name;
1842
1843 fnd_app_system.register_aliasset_usage
1844 ( tns_alias_set_guid => l_tns_alias_set_guid_pub,
1845 server_guid => l_server_guid);
1846
1847 -- Register APPS Listener
1848
1849 fnd_app_system.register_listener
1850 ( Listener_Name =>
1851 fnd_app_system.C_APPS_LISTENER_ID
1852 || '_' || ServerName,
1853 Server_name => ServerName,
1854 tns_alias_name =>
1855 fnd_app_system.C_APPS_LISTENER_ID
1856 || '_' || ServerName,
1857 listener_guid => null,
1858 alias_set_name => l_public_alias_set_name
1859 );
1860
1861 select a.Listener_GUID
1862 into l_Listener_GUID
1863 from fnd_tns_listeners a
1864 where a.Server_GUID = l_Server_Guid
1865 and a.Listener_Name = fnd_app_system.C_APPS_LISTENER_ID ||
1866 '_' || ServerName;
1867
1868 -- Register Listener Ports
1869
1870 -- Special case for Autoconfig. There should only be one port. Since
1871 -- the caller can't pass in old/new values, we check for any existing
1872 -- port entries. If supplied rpcPort does not exist, then use first
1873 -- available.
1874
1875 l_listener_port_guid := null;
1876
1877 for f_port in c3(l_Listener_GUID) loop
1878
1879 l_listener_port_guid := f_port.listener_port_guid;
1880
1881 exit when f_port.port = to_number(RPCPort);
1882
1883 end loop;
1884
1885 fnd_app_system.register_listener_ports
1886 ( Listener_name => fnd_app_system.C_APPS_LISTENER_ID
1887 ||'_'|| ServerName,
1888 Port => to_number(RPCPort),
1889 server_guid => l_server_guid,
1890 Listener_Port_Guid
1891 => l_listener_port_guid
1892 );
1893
1894 select a.listener_port_guid
1895 into l_listener_port_guid
1896 from fnd_tns_listener_ports a
1897 where a.listener_guid= l_Listener_GUID
1898 and a.port = to_number(RPCPort);
1899
1900 -- Create FNDFS/SM aliases
1901
1902 l_fndsmfs(1).alias := '_' || Hostname;
1903 l_fndsmfs(1).sid := '_' || SidDefaultAlias;
1904
1905 for i in 1..l_fndsmfs.count loop
1906
1907 l_fndfs_alias := 'FNDFS' || l_fndsmfs(i).alias;
1908 l_fndsm_alias := 'FNDSM' || l_fndsmfs(i).alias;
1909
1910 l_fndfs_sid := 'FNDFS' || l_fndsmfs(i).sid;
1911 l_fndsm_sid := 'FNDSM' || l_fndsmfs(i).sid;
1912
1913 -- For now we only want certain types. Let's keep the loop in case
1914 -- things change.
1915
1916 l_fndfs_sid := 'FNDFS';
1917
1918 begin
1919
1920 select a.TNS_ALIAS_GUID
1921 into l_fndfs_tns_alias
1922 from fnd_tns_aliases a
1923 where a.Alias_Name = l_fndfs_alias
1924 and a.Alias_set_guid = l_tns_alias_set_guid_pub;
1925
1926 exception
1927 when no_data_found then
1928 l_fndfs_tns_alias := sys_guid();
1929 end;
1930
1931 begin
1932
1933 select a.TNS_ALIAS_GUID
1934 into l_fndsm_tns_alias
1935 from fnd_tns_aliases a
1936 where a.Alias_Name = l_fndsm_alias
1937 and a.Alias_set_guid = l_tns_alias_set_guid_pub;
1938
1939 exception
1940 when no_data_found then
1941 l_fndsm_tns_alias := sys_guid();
1942 end;
1943
1944 register_alias
1945 ( p_Alias_Name => l_fndfs_alias,
1946 p_Alias_Type => fnd_app_system.C_FNDFS_TNS_ALIAS_TYPE,
1947 p_Failover => 'N',
1948 p_Load_Balance => 'N',
1949 p_Service_GUID => null,
1950 p_Instance_Guid => null,
1951 p_alias_set_name => l_public_alias_set_name,
1952 p_alias_set_guid => l_tns_alias_set_guid_pub,
1953 p_tns_alias_guid => l_fndfs_tns_alias,
1954 p_Listener_Port_Guid
1955 => l_Listener_port_GUID,
1956 p_alt_instance_table
1957 => empty_instance_table
1958 );
1959
1960 register_alias
1961 ( p_Alias_Name => l_fndsm_alias,
1962 p_Alias_Type => fnd_app_system.C_FNDSM_TNS_ALIAS_TYPE,
1963 p_Failover => 'N',
1964 p_Load_Balance => 'N',
1965 p_Service_GUID => null,
1966 p_Instance_Guid => null,
1967 p_alias_set_name => l_public_alias_set_name,
1968 p_alias_set_guid => l_tns_alias_set_guid_pub,
1969 p_tns_alias_guid => l_fndsm_tns_alias,
1970 p_Listener_Port_Guid
1971 => l_Listener_port_GUID,
1972 p_alt_instance_table
1973 => empty_instance_table
1974 );
1975
1976 end loop;
1977
1978 -- Now set up the aliases correctly. Process DB aliases before Instance
1979 -- aliases, to ensure Instance aliases have priority over db aliases.
1980
1981
1982 register_app_alias( alias => SidDefaultAlias,
1983 type => fnd_app_system.C_DB_BALANCE_TNS_ALIAS_TYPE ,
1984 system_guid => l_System_Guid,
1985 alias_set_name => l_public_alias_set_name,
1986 alias_set_guid => l_tns_alias_set_guid_pub,
1987 auto_create => l_auto_create_aliases );
1988
1989 register_app_alias( alias => ToolsInstanceAlias,
1990 type => fnd_app_system.C_DB_INST_TNS_ALIAS_TYPE,
1991 system_guid => l_System_Guid,
1992 alias_set_name => l_public_alias_set_name,
1993 alias_set_guid => l_tns_alias_set_guid_pub,
1994 auto_create => l_auto_create_aliases );
1995
1996 register_app_alias( alias => WebInstanceAlias,
1997 type => fnd_app_system.C_DB_INST_TNS_ALIAS_TYPE ,
1998 system_guid => l_System_Guid,
1999 alias_set_name => l_public_alias_set_name,
2000 alias_set_guid => l_tns_alias_set_guid_pub,
2001 auto_create => l_auto_create_aliases );
2002
2003 register_app_alias( alias => JDBCSid,
2004 type => fnd_app_system.C_DB_INST_TNS_ALIAS_TYPE ,
2005 system_guid => l_System_Guid,
2006 alias_set_name => l_public_alias_set_name,
2007 alias_set_guid => l_tns_alias_set_guid_pub,
2008 auto_create => l_auto_create_aliases );
2009
2010 end;
2011
2012 /*==========================================================================*/
2013
2014 procedure show_tns_addresses(p_Tns_Alias_Description_Guid raw)
2015 as
2016 cursor c1 is select d.listener_name,c.port,
2017 a.sequence_number,a.failover,a.load_balance
2018 from fnd_tns_alias_address_lists a, fnd_tns_alias_addresses b,
2019 fnd_tns_listener_ports c, fnd_tns_listeners d
2020 where a.Tns_Alias_Description_Guid = p_Tns_Alias_Description_Guid
2021 and a.Tns_Alias_Address_List_Guid =
2022 b.Tns_Alias_Address_List_Guid
2023 and b.listener_port_guid = c.listener_port_guid
2024 and c.Listener_GUID = d.Listener_GUID
2025 order by a.sequence_number,b.listener_port_guid;
2026 begin
2027
2028 for f_listener1 in c1 loop
2029
2030 dbms_output.put_line ( fmtline('SeqNo',8) ||
2031 fmtline('Lsrn Name',30) ||
2032 fmtline('Lsrn Port',20) ||
2033 fmtline('Fov',4) ||
2034 fmtline('Bal',4)
2035 );
2036
2037 dbms_output.put_line ( fmtuline(8) || fmtuline(30) || fmtuline(20) ||
2038 fmtuline(4) || fmtuline(4) );
2039
2040 dbms_output.put_line ( fmtline(f_listener1.sequence_number,8) ||
2041 fmtline(f_listener1.listener_name,30)||
2042 fmtline(f_listener1.port,20) ||
2043 fmtline(f_listener1.Failover,4) ||
2044 fmtline(f_listener1.Load_Balance,4)
2045 );
2046 end loop;
2047
2048 end;
2049
2050 /*==========================================================================*/
2051
2052 procedure show_servicename(p_DB_Service_GUID raw)
2053 as
2054 cursor c1 is select a.service_name
2055 from fnd_database_services a
2056 where a.DB_Service_GUID = p_DB_Service_GUID;
2057 begin
2058
2059 for f_service in c1 loop
2060
2061 dbms_output.put_line ( fmtline('Service Name',30) );
2062
2063 dbms_output.put_line ( fmtuline(30));
2064
2065 dbms_output.put_line ( fmtline(f_service.service_name,30) );
2066
2067 end loop;
2068
2069 end;
2070
2071 /*==========================================================================*/
2072
2073 procedure show_service_instance(p_DB_Service_GUID raw,p_instance_guid raw)
2074 as
2075 cursor c1 is select a.service_name,b.Instance_Name,b.Instance_Number,
2076 c.instance_type
2077 from fnd_database_services a, fnd_database_instances b,
2078 fnd_db_service_members c
2079 where a.DB_Service_GUID = p_DB_Service_GUID
2080 and b.Instance_Guid = p_instance_guid
2081 and a.Db_Service_Guid = c.Db_Service_Guid
2082 and c.Instance_Guid = p_instance_guid ;
2083 begin
2084
2085 for f_service in c1 loop
2086
2087 dbms_output.put_line ( fmtline('Service Name',20) ||
2088 fmtline('Instance Name',20) ||
2089 fmtline('Instance Number',15)||
2090 fmtline('10g-Pref/Ava',15) );
2091
2092 dbms_output.put_line ( fmtuline(20) || fmtuline(20) || fmtuline(15) ||
2093 fmtuline(15) );
2094
2095 dbms_output.put_line ( fmtline(f_service.service_name,20) ||
2096 fmtline(f_service.Instance_Name,20) ||
2097 fmtline(f_service.Instance_Number,15) ||
2098 fmtline(f_service.instance_type,15) );
2099
2100 end loop;
2101
2102 end;
2103
2104 /*==========================================================================*/
2105
2106 procedure show_tnsalias(p_info varchar2, p_tns_alias_guid raw )
2107 as
2108 cursor c1 is select a.alias_name,a.Alias_Type,
2109 a.Failover,a.Load_Balance,
2110 b.tns_alias_set_name,
2111 b.tns_alias_set_type
2112 from fnd_tns_aliases a,fnd_tns_alias_sets b
2113 where a.tns_alias_guid = p_tns_alias_guid
2114 and a.alias_set_guid = b.tns_alias_set_guid;
2115
2116 cursor c2 is select c.tns_alias_description_guid,
2117 c.DB_Service_GUID,c.DB_Instance_Guid,
2118 c.Service_Name,c.Instance_Name,
2119 c.Failover,c.Load_Balance,
2120 c.sequence_number
2121 from fnd_tns_alias_descriptions c
2122 where c.tns_alias_guid = p_tns_alias_guid
2123 order by c.sequence_number;
2124
2125 begin
2126
2127 dbms_output.put_line('>>>>');
2128
2129 dbms_output.put_line ( fmtline('TNS Info for ' || p_info ,50) );
2130
2131 dbms_output.put_line ( fmtuline(50) );
2132
2133 for f_tns_alias in c1 loop
2134
2135 dbms_output.put_line ( fmtline('Alias Name',20) ||
2136 fmtline('Type',20) ||
2137 fmtline('Fov',4) ||
2138 fmtline('Bal',4) ||
2139 fmtline('Alias Set Name',26) ||
2140 fmtline('Set Type',10)
2141 );
2142
2143 dbms_output.put_line ( fmtuline(20) ||
2144 fmtuline(20) ||
2145 fmtuline(4) ||
2146 fmtuline(4) ||
2147 fmtuline(26) ||
2148 fmtuline(10)
2149 );
2150
2151 dbms_output.put_line
2152 ( fmtline(f_tns_alias.alias_name,20) ||
2153 fmtline(f_tns_alias.Alias_Type,20) ||
2154 fmtline(f_tns_alias.Failover,4) ||
2155 fmtline(f_tns_alias.Load_Balance,4) ||
2156 fmtline(f_tns_alias.tns_alias_set_name,26) ||
2157 fmtline(f_tns_alias.tns_alias_set_type,10)
2158 );
2159
2160 for f_desc in c2 loop
2161
2162 dbms_output.put_line ( fmtline('SeqNo',8) ||
2163 fmtline('Fov',4) ||
2164 fmtline('Bal',4) ||
2165 fmtline('SrvName',16) ||
2166 fmtline('InstName',16)
2167 );
2168
2169 dbms_output.put_line ( fmtuline(8 ) ||
2170 fmtuline(4) ||
2171 fmtuline(4) ||
2172 fmtuline(16) ||
2173 fmtuline(16)
2174 );
2175
2176 dbms_output.put_line
2177 ( fmtline(f_desc.sequence_number,8) ||
2178 fmtline(f_desc.Failover,4) ||
2179 fmtline(f_desc.Load_Balance,4) ||
2180 fmtline(f_desc.service_name,16) ||
2181 fmtline(f_desc.instance_name,16)
2182 );
2183
2184 if ( f_tns_alias.Alias_Type
2185 = fnd_app_system.C_LOCAL_INST_TNS_ALIAS_TYPE )
2186 then
2187
2188 show_tns_addresses(f_desc.tns_alias_description_guid);
2189
2190 end if;
2191
2192 if ( f_tns_alias.Alias_Type
2193 = fnd_app_system.C_REMOTE_INST_TNS_ALIAS_TYPE )
2194 then
2195
2196 show_ServiceName(f_desc.DB_Service_GUID);
2197 show_tns_addresses(f_desc.tns_alias_description_guid);
2198
2199 end if;
2200
2201 if ( f_tns_alias.Alias_Type
2202 = fnd_app_system.C_DB_BALANCE_TNS_ALIAS_TYPE )
2203 then
2204
2205 show_ServiceName(f_desc.DB_Service_GUID);
2206 show_tns_addresses(f_desc.tns_alias_description_guid);
2207
2208 end if;
2209
2210 if ( f_tns_alias.Alias_Type
2211 = fnd_app_system.C_DB_INST_TNS_ALIAS_TYPE )
2212 then
2213
2214 show_Service_Instance(f_desc.DB_Service_GUID,
2215 f_desc.DB_instance_guid);
2216 show_tns_addresses(f_desc.tns_alias_description_guid);
2217
2218 end if;
2219
2220 if ( f_tns_alias.Alias_Type
2221 = fnd_app_system.C_FNDFS_TNS_ALIAS_TYPE or
2222 f_tns_alias.Alias_Type
2223 = fnd_app_system.C_FNDSM_TNS_ALIAS_TYPE )
2224 then
2225
2226 show_tns_addresses(f_desc.tns_alias_description_guid);
2227
2228 end if;
2229
2230 end loop;
2231
2232 end loop;
2233
2234 end;
2235
2236 /*==========================================================================*/
2237
2238 procedure show_FNDFSSM(p_Server_Guid raw)
2239 as
2240 l_fndfs_tns_alias raw(16);
2241 l_fndsm_tns_alias raw(16);
2242 l_fndfs_sid varchar2(50);
2243 l_fndsm_sid varchar2(50);
2244
2245 cursor c1 is
2246 select a.TNS_ALIAS_GUID
2247 from fnd_tns_aliases a, fnd_tns_alias_set_usage b
2248 where a.Alias_Name like 'FNDFS%'
2249 and a.Alias_Type = fnd_app_system.C_FNDFS_TNS_ALIAS_TYPE
2250 and b.server_guid = p_Server_Guid
2251 and a.alias_set_guid = b.tns_alias_set_guid;
2252
2253 cursor c2 is
2254 select a.TNS_ALIAS_GUID
2255 from fnd_tns_aliases a, fnd_tns_alias_set_usage b
2256 where a.Alias_Name like 'FNDSM%'
2257 and a.Alias_Type = fnd_app_system.C_FNDSM_TNS_ALIAS_TYPE
2258 and b.server_guid = p_Server_Guid
2259 and a.alias_set_guid = b.tns_alias_set_guid;
2260
2261 begin
2262
2263 for f_fndfs in c1 loop
2264
2265 show_tnsalias('FNDFS-Alias', f_fndfs.tns_alias_guid);
2266
2267 end loop;
2268
2269 for f_fndsm in c2 loop
2270
2271 show_tnsalias('FNDSM-Alias', f_fndsm.tns_alias_guid);
2272
2273 end loop;
2274
2275 end;
2276
2277 /*==========================================================================*/
2278
2279 procedure show_instance ( p_Server_Guid raw )
2280 as
2281 cursor c1 is select db_guid,Instance_Guid,Instance_Name,Instance_Number,
2282 Sid_GUID,Default_TNS_Alias_GUID,sid,
2283 Local_Listener_Alias,Remote_Listener_Alias,
2284 Configuration,Description,Interconnect_name
2285 from fnd_database_instances
2286 where Server_Guid = p_Server_Guid;
2287
2288 cursor c2(p_db_guid raw)
2289 is select db_name,db_domain,default_tns_alias_guid,is_rac_db,version
2290 from fnd_databases
2291 where db_guid = p_db_guid;
2292
2293 cursor c3(p_db_guid raw)
2294 is select assignment
2295 from fnd_database_assignments
2296 where db_guid = p_db_guid;
2297
2298 l_db_assignment varchar2(50);
2299
2300 cursor c4(p_sid_guid raw)
2301 is select sid
2302 from fnd_sids
2303 where sid_guid = p_sid_guid;
2304
2305 l_sid varchar2(50);
2306 l_sid2 varchar2(50);
2307
2308 cursor c5(p_db_guid raw)
2309 is select DB_Service_GUID,db_guid,Service_Name,Description
2310 from fnd_database_services
2311 where db_guid = p_db_guid;
2312
2313 cursor c6(p_db_name varchar2,p_server_guid raw) is
2314 select a.TNS_ALIAS_GUID
2315 from fnd_tns_aliases a, fnd_tns_alias_set_usage b
2316 where a.Alias_Name = p_db_name
2317 and a.Alias_Type = fnd_app_system.C_DB_INST_TNS_ALIAS_TYPE
2318 and b.Server_Guid = p_server_guid
2319 and b.Tns_Alias_Set_Guid = a.Alias_Set_Guid;
2320
2321 l_service_member number;
2322 l_db_name varchar2(50);
2323 l_tns_db_alias raw(16);
2324
2325 begin
2326
2327 dbms_output.put_line('>>>');
2328 dbms_output.put_line('>>> Show Instance');
2329 dbms_output.put_line('>>>');
2330
2331 for f_instance in c1 loop
2332
2333 for f_sid in c4(f_instance.sid_guid) loop
2334
2335 l_sid2 := f_sid.sid;
2336
2337 end loop;
2338
2339 dbms_output.put_line
2340 (
2341 fmtline('Inst Name',10) ||
2342 fmtline('Inst No', 8) ||
2343 fmtline('Config',10) ||
2344 fmtline('Desc',15) ||
2345 fmtline('InterCnt',15) ||
2346 fmtline('SID',10)
2347 );
2348
2349 dbms_output.put_line
2350 (
2351 fmtuline(10) ||
2352 fmtuline( 8) ||
2353 fmtuline(10) ||
2354 fmtuline(15) ||
2355 fmtuline(15) ||
2356 fmtuline(10)
2357 );
2358
2359 dbms_output.put_line
2360 (
2361 fmtline(f_instance.Instance_Name,10) ||
2362 fmtline(f_instance.Instance_Number, 8) ||
2363 fmtline(f_instance.Configuration,10) ||
2364 fmtline(f_instance.Description,15) ||
2365 fmtline(f_instance.Interconnect_name,15) ||
2366 fmtline(f_instance.sid,10)
2367 );
2368
2369 show_tnsalias('Default Inst TNS', f_instance.Default_TNS_Alias_GUID);
2370 show_tnsalias('Local TNS', f_instance.Local_Listener_Alias);
2371 show_tnsalias('Remote TNS', f_instance.Remote_Listener_Alias);
2372
2373 for f_database in c2(f_instance.db_guid) loop
2374
2375 for f_db_assignment in c3(f_instance.db_guid) loop
2376
2377 l_db_assignment := f_db_assignment.assignment;
2378
2379 end loop;
2380
2381 l_db_name := f_database.db_name;
2382
2383 dbms_output.put_line ( fmtline('DB Name',10) ||
2384 fmtline('Domain',20) ||
2385 fmtline('Cluster',8) ||
2386 fmtline('Version',10) ||
2387 fmtline('Assgn',10)
2388 );
2389
2390 dbms_output.put_line ( fmtuline(10) ||
2391 fmtuline(20) ||
2392 fmtuline(8) ||
2393 fmtuline(10) ||
2394 fmtuline(10)
2395 );
2396
2397 dbms_output.put_line
2398 ( fmtline(f_database.db_name,10) ||
2399 fmtline(f_database.db_domain,20) ||
2400 fmtline(f_database.Is_rac_db, 8) ||
2401 fmtline(f_database.Version,10) ||
2402 fmtline(l_db_assignment,10)
2403 );
2404
2405 show_tnsalias('Default DB TNS', f_database.Default_TNS_Alias_GUID);
2406
2407 end loop;
2408
2409 for f_service in c5(f_instance.db_guid) loop
2410
2411 select count(*)
2412 into l_service_member
2413 from fnd_db_service_members a
2414 where a.db_service_guid = f_service.db_service_guid
2415 and a.Instance_Guid = f_instance.Instance_Guid ;
2416
2417 dbms_output.put_line ( fmtline('Service Name',20) ||
2418 fmtline('Description',30) ||
2419 fmtline('Member',10)
2420 );
2421
2422 dbms_output.put_line ( fmtuline(20) ||
2423 fmtuline(30) ||
2424 fmtuline(10)
2425 );
2426
2427 dbms_output.put_line
2428 ( fmtline(f_service.Service_Name,20) ||
2429 fmtline(f_service.Description,30) ||
2430 fmtline(l_service_member,10)
2431 );
2432
2433 for f_alias in c6(l_db_name,p_server_guid) loop
2434
2435 show_tnsalias('DB Inst Alias', f_alias.tns_alias_guid );
2436
2437 end loop;
2438
2439 end loop;
2440
2441 end loop;
2442
2443 end;
2444
2445 /*==========================================================================*/
2446
2447 procedure show ( SystemName in varchar2)
2448 as
2449 cursor c1 is select name,Version,Owner,CSI_Number,System_GUID
2450 from fnd_apps_system
2451 where name = SystemName;
2452
2453 cursor c2(p_System_Guid raw)
2454 is select a.Server_GUID,a.name,a.Node_Id,a.Internal,a.Appl_Top_Guid,
2455 a.Server_type,a.Pri_Oracle_Home,a.Aux_Oracle_Home
2456 from fnd_app_servers a, fnd_system_server_map b
2457 where b.System_Guid = p_System_Guid
2458 and a.Server_GUID = b.Server_GUID
2459 order by a.node_id,a.Internal,a.Server_type,a.name;
2460
2461 cursor c3(p_appl_top_guid raw)
2462 is select name,Path,Shared
2463 from fnd_appl_tops
2464 where appl_top_guid = p_appl_top_guid;
2465
2466 cursor c4(p_oracle_home_id raw)
2467 is select name,Path,Version,Description
2468 from fnd_oracle_homes
2469 where oracle_home_id = p_oracle_home_id;
2470
2471 l_nodeRec fnd_nodes%rowtype;
2472
2473 begin
2474
2475 dbms_output.enable(1000000);
2476
2477 for f_app_rec in c1 loop
2478
2479 dbms_output.put_line
2480 (
2481 fmtline('System Name',15) ||
2482 fmtline('Version',15) ||
2483 fmtline('Owner',20) ||
2484 fmtline('CSI Number',15)
2485 );
2486
2487 dbms_output.put_line
2488 (
2489 fmtuline(15) ||
2490 fmtuline(15) ||
2491 fmtuline(20) ||
2492 fmtuline(15)
2493 );
2494
2495 dbms_output.put_line
2496 (
2497 fmtline(f_app_rec.name,15) ||
2498 fmtline(f_app_rec.version,15) ||
2499 fmtline(f_app_rec.owner,20) ||
2500 fmtline(f_app_rec.CSI_Number,15)
2501 );
2502
2503 for f_server in c2(f_app_rec.System_GUID) loop
2504
2505 execute immediate 'select a.* ' ||
2506 ' from fnd_nodes a ' ||
2507 ' where a.node_id = :v1 '
2508 into l_nodeRec
2509 using f_server.node_id;
2510
2511 dbms_output.put_line('>');
2512
2513 dbms_output.put_line
2514 (
2515 fmtline('Server Name',20) ||
2516 fmtline('Internal',10) ||
2517 fmtline('Type',10) ||
2518 fmtline('Node',10) ||
2519 fmtline('Host',10) ||
2520 fmtline('Domain',15)
2521 );
2522
2523 dbms_output.put_line( fmtuline(20) ||
2524 fmtuline(10) ||
2525 fmtuline(10) ||
2526 fmtuline(10) ||
2527 fmtuline(10) ||
2528 fmtuline(15) );
2529
2530 dbms_output.put_line
2531 (
2532 fmtline(f_server.name,20) ||
2533 fmtline(f_server.internal,10) ||
2534 fmtline(f_server.server_type,10) ||
2535 fmtline(l_nodeRec.node_name,10)
2536
2537 -- Only certain versions of fnd_nodes has host/domain. So to be safe,
2538 -- we just comment out the following lines.
2539
2540 -- fmtline(l_nodeRec.host,10) ||
2541 -- fmtline(l_nodeRec.domain,15)
2542 );
2543
2544 if ( f_server.appl_top_guid is not null )
2545 then
2546
2547 for f_appl_top in c3(f_server.appl_top_guid) loop
2548
2549 dbms_output.put_line('>>');
2550
2551 dbms_output.put_line ( fmtline('App Name|Path',20) ||
2552 fmtline('Shared',10) );
2553
2554 dbms_output.put_line ( fmtuline(20) || fmtuline(10) );
2555
2556 dbms_output.put_line ( fmtline(f_appl_top.name,20) ||
2557 fmtline(f_appl_top.shared,10) );
2558 dbms_output.put_line( f_appl_top.path );
2559
2560 end loop;
2561 end if;
2562
2563 for f_o_home in c4(f_server.pri_oracle_home) loop
2564
2565 dbms_output.put_line('>>');
2566
2567 dbms_output.put_line ( fmtline('Home Name|Path',20) ||
2568 fmtline('Version',15) ||
2569 fmtline('Description',30) );
2570
2571 dbms_output.put_line (fmtuline(20) || fmtuline(15) || fmtuline(30));
2572
2573 dbms_output.put_line ( fmtline(f_o_home.name,20) ||
2574 fmtline(f_o_home.version,15) ||
2575 fmtline(f_o_home.Description,30) );
2576 dbms_output.put_line( f_o_home.path );
2577
2578 end loop;
2579
2580 if ( f_server.aux_oracle_home is not null )
2581 then
2582
2583 for f_o_home in c4(f_server.aux_oracle_home) loop
2584
2585 dbms_output.put_line('>>');
2586
2587 dbms_output.put_line ( fmtline('Home Name|Path',20) ||
2588 fmtline('Version',15) ||
2589 fmtline('Description',30) );
2590
2591 dbms_output.put_line (fmtuline(20) || fmtuline(15) || fmtuline(30));
2592
2593 dbms_output.put_line ( fmtline(f_o_home.name,20) ||
2594 fmtline(f_o_home.version,15) ||
2595 fmtline(f_o_home.Description,30) );
2596 dbms_output.put_line( f_o_home.path );
2597
2598 end loop;
2599 end if;
2600
2601 if ( f_server.server_type = fnd_app_system.C_DB_SERVER_TYPE )
2602 then
2603 show_instance(f_server.Server_Guid);
2604 end if;
2605
2606 if ( f_server.server_type = fnd_app_system.C_APP_SERVER_TYPE )
2607 then
2608 show_FNDFSSM(f_server.Server_Guid);
2609 end if;
2610
2611 end loop;
2612
2613 end loop;
2614
2615 end;
2616
2617 /*==========================================================================*/
2618
2619 procedure remove_dbnode ( SystemName in varchar2,
2620 ServerName in varchar2,
2621 DatabaseName in varchar2,
2622 InstanceName in varchar2,
2623 Domain in varchar2
2624 )
2625 as
2626 l_System_GUID raw(16);
2627
2628 -- Always lock the system when doing major structural changes.
2629
2630 cursor c1(p_SystemName varchar2) is
2631 select a.System_Guid
2632 from fnd_apps_system a
2633 where a.name = p_SystemName
2634 for update of a.System_Guid;
2635
2636 cursor c2(p_System_GUID raw, p_ServerName varchar2) is
2637 select a.Server_GUID,b.PRI_ORACLE_HOME,c.listener_guid
2638 from fnd_system_server_map a, fnd_app_servers b,
2639 fnd_tns_listeners c
2640 where a.System_GUID = p_System_GUID
2641 and a.Server_GUID = b.Server_GUID
2642 and b.name = p_ServerName
2643 and b.server_type = fnd_app_system.C_DB_SERVER_TYPE
2644 and b.Server_GUID = c.Server_GUID(+);
2645
2646 cursor c3(p_Server_GUID raw,p_InstanceName varchar2,
2647 p_DatabaseName varchar2,p_Domain varchar2) is
2648 select a.instance_guid,a.sid_guid,
2649 a.default_tns_alias_guid instance_tns_alias_guid,
2650 a.local_listener_alias,
2651 a.remote_listener_alias,
2652 b.db_guid,
2653 b.default_tns_alias_guid db_tns_alias_guid
2654 from fnd_database_instances a,fnd_databases b
2655 where a.Server_GUID = p_Server_GUID
2656 and a.Instance_Name = p_InstanceName
2657 and a.db_guid = b.db_guid
2658 and b.DB_Name = p_DatabaseName
2659 and b.DB_Domain = p_Domain;
2660 begin
2661
2662 for f_system in c1(SystemName) loop
2663
2664 for f_server in c2(f_system.System_Guid,ServerName) loop
2665
2666 delete from fnd_oracle_homes a
2667 where a.oracle_home_id = f_server.pri_oracle_home
2668 and not exists ( select b.name
2669 from fnd_app_servers b
2670 where ( b.pri_oracle_home
2671 = f_server.pri_oracle_home
2672 or b.aux_oracle_home
2673 = f_server.pri_oracle_home
2674 )
2675 and b.server_guid <> f_server.server_guid
2676 );
2677
2678 if ( f_server.Listener_GUID is not null )
2679 then
2680
2681 delete from fnd_tns_alias_address_lists a
2682 where a.Tns_Alias_Address_List_Guid
2683 in ( select b.Tns_Alias_Address_List_Guid
2684 from fnd_tns_alias_addresses b,fnd_tns_listener_ports c
2685 where b.listener_port_guid = c.listener_port_guid
2686 and c.Listener_GUID = f_server.Listener_GUID )
2687 and 1 = ( select count(*)
2688 from fnd_tns_alias_addresses c
2689 where c.Tns_Alias_Address_List_Guid
2690 = a.Tns_Alias_Address_List_Guid );
2691
2692 -- Note on delete of : fnd_tns_aliases and fnd_tns_alias_descriptions
2693 -- The deletes remove all dangling references, not just the
2694 -- current db node. Since the only dangling references should be
2695 -- this node this seems ok.
2696
2697 -- A descriptor can be unresolved. If it is, it will not have
2698 -- an address list, in which case we can't just delete dangling
2699 -- references. So unresolved descriptors can only be deleted at the
2700 -- server level.
2701
2702 delete from fnd_tns_alias_descriptions a
2703 where not exists ( select 1
2704 from fnd_tns_alias_address_lists b
2705 where b.Tns_Alias_Description_Guid
2706 = a.Tns_Alias_Description_Guid )
2707 and a.sequence_number >= 0;
2708
2709 -- An alias will always have a descriptor, so ok.
2710
2711 delete from fnd_tns_aliases a
2712 where not exists ( select 1
2713 from fnd_tns_alias_descriptions b
2714 where b.Tns_Alias_Guid = a.Tns_Alias_Guid );
2715
2716 delete from fnd_tns_alias_addresses b
2717 where b.Listener_port_GUID
2718 in ( select c.listener_port_guid
2719 from fnd_tns_listener_ports c
2720 where c.Listener_GUID = f_server.Listener_GUID );
2721
2722 delete from fnd_tns_listener_ports a
2723 where a.Listener_GUID = f_server.Listener_GUID;
2724
2725 delete from fnd_tns_listeners a
2726 where a.Listener_GUID = f_server.Listener_GUID;
2727
2728 end if;
2729
2730 for f_instance in c3(f_server.Server_GUID,InstanceName,DatabaseName,
2731 Domain) loop
2732
2733 delete from fnd_database_instances a
2734 where a.instance_guid = f_instance.instance_guid;
2735
2736 delete from fnd_db_service_members a
2737 where a.db_guid = f_instance.db_guid
2738 and a.instance_guid = f_instance.instance_guid;
2739
2740 delete from fnd_sids a
2741 where a.sid_guid = f_instance.sid_guid;
2742
2743 delete from fnd_database_services a
2744 where a.db_guid = f_instance.db_guid
2745 and not exists ( select b.instance_guid
2746 from fnd_db_service_members b
2747 where b.db_service_guid = a.db_service_guid );
2748
2749 delete from fnd_databases a
2750 where a.db_guid = f_instance.db_guid
2751 and not exists ( select b.instance_guid
2752 from fnd_database_instances b
2753 where b.db_guid = a.db_guid );
2754
2755 delete from fnd_database_assignments a
2756 where a.db_guid = f_instance.db_guid
2757 and a.assignment = fnd_app_system.C_APP_DB_ASSIGNMENT
2758 and not exists ( select 1
2759 from fnd_databases b
2760 where b.db_guid = a.db_guid ) ;
2761
2762 end loop;
2763
2764 end loop;
2765
2766 end loop;
2767
2768 end;
2769
2770 /*==========================================================================*/
2771
2772 procedure remove_appnode ( SystemName in varchar2,
2773 ServerName in varchar2
2774 )
2775 as
2776
2777 -- Always lock the system when doing major structural changes.
2778
2779 cursor c1(p_SystemName varchar2) is
2780 select a.System_Guid
2781 from fnd_apps_system a
2782 where a.name = p_SystemName
2783 for update of a.System_Guid;
2784
2785 cursor c2(p_System_GUID raw, p_ServerName varchar2) is
2786 select a.Server_GUID,b.PRI_ORACLE_HOME,c.listener_guid,
2787 b.APPL_TOP_GUID,b.AUX_ORACLE_HOME
2788 from fnd_system_server_map a, fnd_app_servers b,
2789 fnd_tns_listeners c
2790 where a.System_GUID = p_System_GUID
2791 and a.Server_GUID = b.Server_GUID
2792 and b.name = p_ServerName
2793 and b.server_type = fnd_app_system.C_APP_SERVER_TYPE
2794 and b.Server_GUID = c.Server_GUID(+);
2795
2796 begin
2797
2798 for f_system in c1(SystemName) loop
2799
2800 for f_server in c2(f_system.System_Guid,ServerName) loop
2801
2802 delete from fnd_oracle_homes a
2803 where a.oracle_home_id = f_server.pri_oracle_home
2804 and not exists ( select b.name
2805 from fnd_app_servers b
2806 where ( b.pri_oracle_home
2807 = f_server.pri_oracle_home
2808 or b.aux_oracle_home
2809 = f_server.pri_oracle_home
2810 )
2811 and b.server_guid <> f_server.server_guid
2812 );
2813
2814 if ( f_server.aux_oracle_home is not null )
2815 then
2816 delete from fnd_oracle_homes a
2817 where a.oracle_home_id = f_server.aux_oracle_home
2818 and not exists ( select b.name
2819 from fnd_app_servers b
2820 where ( b.pri_oracle_home
2821 = f_server.aux_oracle_home
2822 or b.aux_oracle_home
2823 = f_server.aux_oracle_home
2824 )
2825 and b.server_guid <> f_server.server_guid
2826 );
2827
2828 end if;
2829
2830 if ( f_server.appl_top_guid is not null )
2831 then
2832 delete from fnd_appl_tops a
2833 where a.appl_top_guid = f_server.appl_top_guid
2834 and not exists ( select b.name
2835 from fnd_app_servers b
2836 where b.appl_top_guid = f_server.appl_top_guid
2837 and b.server_guid <> f_server.server_guid
2838 );
2839 end if;
2840
2841 if ( f_server.Listener_GUID is not null )
2842 then
2843
2844 delete from fnd_tns_alias_address_lists a
2845 where a.Tns_Alias_Address_List_Guid
2846 in ( select b.Tns_Alias_Address_List_Guid
2847 from fnd_tns_alias_addresses b,fnd_tns_listener_ports c
2848 where b.listener_port_guid = c.listener_port_guid
2849 and c.Listener_GUID = f_server.Listener_GUID )
2850 and 1 = ( select count(*)
2851 from fnd_tns_alias_addresses c
2852 where c.Tns_Alias_Address_List_Guid
2853 = a.Tns_Alias_Address_List_Guid );
2854
2855 -- Note on delete of : fnd_tns_aliases and fnd_tns_alias_descriptions
2856 -- The deletes remove all dangling references, not just the
2857 -- current db node. Since the only dangling references should be
2858 -- this node this seems ok.
2859
2860 -- A descriptor can be unresolved. If it is, it will not have
2861 -- an address list, in which case we can't just delete dangling
2862 -- references. So unresolved descriptors can only be deleted at the
2863 -- server level.
2864
2865 delete from fnd_tns_alias_descriptions a
2866 where not exists ( select 1
2867 from fnd_tns_alias_address_lists b
2868 where b.Tns_Alias_Description_Guid
2869 = a.Tns_Alias_Description_Guid )
2870 and a.sequence_number >= 0;
2871
2872 -- An alias will always have a descriptor, so ok.
2873
2874 delete from fnd_tns_aliases a
2875 where not exists ( select 1
2876 from fnd_tns_alias_descriptions b
2877 where b.Tns_Alias_Guid = a.Tns_Alias_Guid );
2878
2879 delete from fnd_tns_alias_addresses b
2880 where b.Listener_port_GUID
2881 in ( select c.listener_port_guid
2882 from fnd_tns_listener_ports c
2883 where c.Listener_GUID = f_server.Listener_GUID );
2884
2885 delete from fnd_tns_listener_ports a
2886 where a.Listener_GUID = f_server.Listener_GUID;
2887
2888 delete from fnd_tns_listeners a
2889 where a.Listener_GUID = f_server.Listener_GUID;
2890
2891 end if;
2892
2893 end loop;
2894
2895 end loop;
2896
2897 end;
2898
2899 /*==========================================================================*/
2900
2901 procedure remove_server ( SystemName in varchar2,
2902 ServerName in varchar2
2903 )
2904 as
2905
2906 -- Always lock the system when doing major structural changes.
2907
2908 cursor c1(p_SystemName varchar2) is
2909 select a.System_Guid
2910 from fnd_apps_system a
2911 where a.name = p_SystemName
2912 for update of a.System_Guid;
2913
2914 cursor c2(p_System_GUID raw, p_ServerName varchar2) is
2915 select a.Server_GUID,b.Server_type
2916 from fnd_system_server_map a, fnd_app_servers b
2917 where a.System_GUID = p_System_GUID
2918 and a.Server_GUID = b.Server_GUID
2919 and b.name = p_ServerName;
2920
2921 cursor c3(p_Server_GUID raw) is
2922 select a.instance_name,b.DB_Name,b.DB_Domain
2923 from fnd_database_instances a,fnd_databases b
2924 where a.Server_GUID = p_Server_GUID
2925 and a.db_guid = b.db_guid;
2926
2927 cursor c4(p_Server_GUID raw) is
2928 select a.tns_alias_guid,b.Tns_Alias_Description_Guid,
2929 c.Tns_Alias_Address_List_Guid
2930 from fnd_tns_aliases a,fnd_tns_alias_descriptions b,
2931 fnd_tns_alias_address_lists c,fnd_tns_alias_set_usage d
2932 where d.server_guid = p_server_guid
2933 and d.tns_alias_set_guid = a.alias_set_guid
2934 and a.tns_alias_guid = b.tns_alias_guid
2935 and b.Tns_Alias_Description_Guid = c.Tns_Alias_Description_Guid(+);
2936
2937 begin
2938
2939 for f_system in c1(SystemName) loop
2940
2941 for f_server in c2(f_system.System_Guid,ServerName) loop
2942
2943 if ( f_server.Server_type = fnd_app_system.C_DB_SERVER_TYPE )
2944 then
2945
2946 for f_instance in c3(f_server.Server_GUID) loop
2947
2948 remove_dbnode(SystemName,ServerName,f_instance.db_name,
2949 f_instance.instance_name,f_instance.db_domain);
2950
2951 end loop;
2952
2953 end if;
2954
2955 if ( f_server.Server_type = fnd_app_system.C_APP_SERVER_TYPE )
2956 then
2957
2958 remove_AppNode(SystemName,ServerName);
2959
2960 -- Remove any remaining aliases assigned to set.
2961
2962 for f_alias in c4(f_server.Server_GUID) loop
2963
2964 delete from fnd_tns_alias_descriptions a
2965 where a.Tns_Alias_Description_Guid =
2966 f_alias.Tns_Alias_Description_Guid;
2967
2968 if ( f_alias.Tns_Alias_Address_List_Guid is not null )
2969 then
2970
2971 delete from fnd_tns_alias_address_lists a
2972 where a.Tns_Alias_Address_List_Guid =
2973 f_alias.Tns_Alias_Address_List_Guid;
2974
2975 delete from fnd_tns_alias_addresses a
2976 where a.Tns_Alias_Address_List_Guid =
2977 f_alias.Tns_Alias_Address_List_Guid;
2978
2979 end if;
2980
2981 delete from fnd_tns_aliases a
2982 where a.tns_alias_guid = f_alias.tns_alias_guid;
2983
2984 end loop;
2985
2986 end if;
2987
2988 delete from fnd_tns_alias_sets a
2989 where a.tns_alias_set_guid
2990 in ( select b.tns_alias_set_guid
2991 from fnd_tns_alias_set_usage b
2992 where b.server_guid = f_server.server_guid );
2993
2994 delete from fnd_tns_alias_set_usage a
2995 where a.server_guid = f_server.server_guid;
2996
2997 delete from fnd_system_server_map a
2998 where a.System_GUID = f_system.System_Guid
2999 and a.Server_GUID = f_server.Server_GUID;
3000
3001 delete from fnd_app_servers a
3002 where a.Server_GUID = f_server.Server_GUID;
3003
3004 end loop;
3005
3006 end loop;
3007
3008 end;
3009
3010 /*==========================================================================*/
3011
3012 procedure remove_system ( SystemName in varchar2 )
3013 as
3014
3015 -- Always lock the system when doing major structural changes.
3016
3017 cursor c1(p_SystemName varchar2) is
3018 select a.System_Guid
3019 from fnd_apps_system a
3020 where a.name = p_SystemName
3021 for update of a.System_Guid;
3022
3023 cursor c2(p_System_GUID raw) is
3024 select a.Server_GUID,b.name
3025 from fnd_system_server_map a, fnd_app_servers b
3026 where a.System_GUID = p_System_GUID
3027 and a.Server_GUID = b.Server_GUID;
3028 begin
3029
3030 for f_system in c1(SystemName) loop
3031
3032 for f_server in c2(f_system.System_Guid) loop
3033
3034 remove_Server(SystemName,f_server.Name);
3035
3036 end loop;
3037
3038 delete from fnd_apps_system a
3039 where a.System_GUID = f_system.System_Guid ;
3040
3041 end loop;
3042
3043 end;
3044
3045 /*==========================================================================*/
3046
3047 begin
3048 null;
3049 end FND_NET_SERVICES;