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