解決Oracle ERP Target node/queue unavailable 問題

最近也算很賽.....只要Clone DB 沒有一次就搞定的,好不容易DB終於Clone成功,ERP AP 也可以運作.....但AP Team 的人反應, Concurrent Manage沒反應

Orz........

看了一下AP 發現 Actual 都是0 ...Status都出現 Target node/queue unavailable
以往Actual 都是0的時候,前人都說Shutdown 在 Restare應該就好了,但..........
 Target node/queue unavailable ???? 啥米鬼阿

網路上查了一下有關於 Target node/queue unavailable的資料,每個都很像但不知道哪個可以用,所以全部都找了一下解答,後來發現最相近的就是Note 555081.1 因為我真的是Clone完之後就不行了XD  

*** Target node/queue unavailable ***

Note 2104027.6:  Conflict Resolution Manager Does Not Start After Install, Upgrade or Patch Application 

Note 736024.1 :  Unable to Bring Up Concurrent Manager after Running Autoconfig. 

Note 735148.1 : How to Troubleshoot Concurrent Manager Start Up Issue Related to Service Manager

Note 218893.1 : How to Create The Service Manager 'FNDSM' on Oracle Applications

Note 260887.1 : Steps to Clean Nonexistent Nodes or IP Addresses from FND_NODES

Note 423170.1 : Concurrent Manager Fails to Start Because of a 'Could not start Service Manager FNDSM__' Error

Note 430812.1 : After Linux Version Upgrade FNDSM Does Not Spawn

Note 555081.1 : After Doing a Clone All the Concurrent Managers Do Not Start

在我看完關於555081的敘述時...我還蠻開心的因為看起來好像就是解決方法,

The issued was found to have been caused by the FND_NODES table having incorrect node entries (old node from the Source Instance) and that no Service Manager was found for the existing node.

但OOXX 的是我不會啊下面寫得密密麻麻的我哪知道怎樣做啊.....步驟1-4我覺得還OK,步驟五就好笑了因為沒跟我說檔名是甚麼我還是用SUN系統的,這下就傷腦筋了好不容易東翻翻西翻翻翻到檔案在哪 

我的步驟是

先用apps登入sqlplus中先看一下現在是怎樣的狀態

語法:  select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME like 'FNDSM%'; 

------------------------------------------------------------------------------------------------

$ sqlplus

SQL*Plus: Release 9.2.0.7.0 - Production on Sun Feb 26 09:28:27 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter user-name: apps

Enter password: OOXX

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.7.0 - Production

SQL> select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME like 'FNDSM%';

CONCURRENT_QUEUE_NAME

------------------------------ 

FNDSM_OOXXERPT1  <<只有一個,但這是有問題的,一開始我也不知道

------------------------------------------------------------------------------------------------

接下然按照所述的進行

1. Stop all middle tier services including the concurrent managers. 

2. Stop the database. 

3. Start the database (請務必將Listener也啟動,不然等你執行DB 的autoconfig以後你就會在Log下看到這樣的訊息

ERROR:

ORA-12541: TNS:no listener

ERRORCODE = 1 ERRORCODE_END

4. Connect SQLPLUS as APPS user and run the following : 

SQL>EXEC FND_CONC_CLONE.SETUP_CLEAN; 

SQL>COMMIT; 

SQL>EXIT; 

執行完這段以後再列出一次你會發現CONCURRENT_QUEUE_NAME空空如也

SQL> select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME like 'FNDSM%';

CONCURRENT_QUEUE_NAME

------------------------------ 

5. Run AutoConfig on all tiers, firstly on the DB tier and then the APPS tiers and webtiers to repopulate the required system tables. 

AutoConfig  from DB path:    $ORACLE_HOME//9.2.0/appsutil/scripts/your_host_name/adautocfg.sh

(DB status was start)

AutoConfig  from AP path:    $ORACLE_HOME/admin/scripts/your_host_name/adautocfg.sh

(AP staus was shutdown)

先做DB tier 完成後再做 AP Ttier ,兩個都完成後再Select 一次

CONCURRENT_QUEUE_NAME

------------------------------

FNDSM_APEXTWERPT1  << 原來的那個

FNDSM_AUTHENTICATION  <<新出現的,如果有人知道這認證是幹嘛的或是會不見得原因也請跟我說一下呢!

(PS: 如果到了步驟六還不能搞定,請繼續參閱後續步驟)

接著把AP開啟,並登入ERP > Concurrent > Manager > Administartor 再看一次 Target node/queue unavailable消失了且Actual 也都有反應.

後續.......每次Clone結束後我都好開心...因為整個過程就是很痛苦...難道都沒有更簡單的方法按個按鈕或是打個指令就可以一路到底嗎? 還是因為我公司的DB 跟ERP 實在太舊了所以只能用一些辦自動的方式搞定

當下好想唱一下小春哥的 神啊~救救我吧

以下資訊轉載從

http://hi.baidu.com/big_tree_ivan/blog/item/8f7df20f2a1014e037d12223.html

=====================================================================

NOTE:555081.1

==============

Applies to:

Oracle Application Object Library - Version: 11.5.9 to 12.0  
This problem can occur on any platform.

Symptoms

In Oracle Application Release 11i and 12 , After doing a clone when attempting to start the concurrent manager using the below :

. adcmctl.sh stop apps/apps under $COMMON_TOP/admin/scripts/<Hostname>/

the below error can be found in ICM logfile :
===========
Could not initialize the Service Manager FNDSM_<HOSTNAME>_<SID>. Verify that <HOSTNAME> has been registered for concurrent processing.
===========

Cause

The issued was found to have been caused by the FND_NODES table having incorrect node entries (old node from the Source Instance) and that no Service Manager was found for the existing node.

Solution

1. Stop all middle tier services including the concurrent managers.

2. Stop the database.

3. Start the database.

4. Connect SQLPLUS as APPS user and run the following :

EXEC FND_CONC_CLONE.SETUP_CLEAN;
COMMIT;
EXIT;

=========================
Article- ID : 260887.1
Title: Steps to Clean Nonexistent Nodes or IP Addresses from FND_NODES
=========================

( Note : In the case of a clone instance, it is strongly recommended to clean the non - existing nodes )

5. Run AutoConfig on all tiers, firstly on the DB tier and then the APPS tiers and webtiers to repopulate the required system tables.

6. Connect to SQLPLUS as APPS user and run the following statement :

select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME like 'FNDSM%'; 
If the above SQL does not returning any value then please do the following:

Go to $FND_TOP/patch/115/sql

Connect SQLPLUS as APPS user and run the following script :

SQL> @afdcm037.sql;

=========================
Article-ID : 218893.1
Title: How to Create The Service Manager 'FNDSM' on Oracle Applications
=========================

(Note : The same "afdcm037.sql" is used for Release 12 also ).

This script will create libraries for FNDSM and create managers for preexisting nodes.

Check again that FNDSM entries now exist:

select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME like 'FNDSM%';

7. Go to cd $FND_TOP/bin
$ adrelink.sh force=y "fnd FNDLIBR"
$ adrelink.sh force=y "fnd FNDSM"
$ adrelink.sh force=y "fnd FNDFS"
$ adrelink.sh force=y "fnd FNDCRM"

8. Run the CMCLEAN.SQL script from the referenced note below (don't forget to commit).

=========================
Article- ID : 134007.1
Title: CMCLEAN.SQL - Non Destructive Script to Clean Concurrent Manager Tables
=========================

9. Start the middle tier services including your concurrent manager.

10. Retest the issue .

==============================****************************************************************************

The cause of this problem is the change of FNDSM trigger in ATG RUP 5.

====================================================
The new FNDSM trigger code is:

CREATE OR REPLACE TRIGGER fndsm
AFTER INSERT ON FND_NODES FOR EACH ROW
BEGIN
if ( :new.NODE_NAME <> 'AUTHENTICATION' ) then
if ( (:new.SUPPORT_CP='Y') or (:new.SUPPORT_FORMS='Y') or (:new.SUPPORT_WEB='Y') ) then
fnd_cp_fndsm.register_fndsm_fcq(:new.NODE_NAME);
end if;
if (:new.SUPPORT_CP = 'Y') then
fnd_cp_fndsm.register_fndim_fcq(:new.NODE_NAME);
end if;
end if;
END;
====================================================

When we run autoconfig on db tier, new row inserted into FND_NODES but FNDSM and FNDIM are not created since db tier not supported CP/FORMS/WEB.
When we run autoconfig on apps tier, this columns are updated to 'Y' but FNDSM trigger is not run because it is fired for insert and not for update.

The Solution is :
1) Recreate the FNDSM trigger same as before but add "AFTER INSERT OR UPDATE ON FND_NODES".
2) Run: exec FND_CONC_CLONE.setup_clean; (with apps)
3) Run autoconfig on db tier and apps tier (in this order)
4) Startup apps tier.

There is no patch at this time to fix this probem, but when ATG RUP 6 will be available it will contain a fix to FNDSM trigger.
Full details in Metalink note 434613.1.

==============================**********************************************************

References

Note 134007.1 - CMCLEAN.SQL - Non Destructive Script to Clean Concurrent Manager Tables
Note 218893.1 - How to Create The Service Manager 'FNDSM' on Oracle Applications
Note 260887.1 - Steps to Clean Nonexistent Nodes or IP Addresses from FND_NODES

http://oracle-apps-dba.blogspot.com/2007/08/after-atg-rup-5-concurrent-managers.html