Oracle财务系统服务器停复机后易忽略点

    在某些情况下,各类系统所在的服务器会被执行停复机操作,这是不可避免的。对于一般的系统,重启服务器后,可能直接重启相应的数据库和应用即可正常使用,但是对Oracle EBS财务系统,除了正常的启动数据库和应用的服务以外(如果是RAC还需单独启动RAC服务),还需要进行另外一项额外的工作,启动VNC服务,以便能在系统运行输出报表的数据调用图形化界面错误,具体的错误画面如下图所示,错误代码为:REP-3000: Internal error starting Oracle Toolkit.

VNC

此时只需按照下面的操作命令执行即可:
以root用户登录,运行命令:
    命令1:vncserver
    用VNC-Viewer登录,密码是第一次运行vncserver提示所输入的密码,如果忘记,可直接网上搜索更改VNC密码的文档,很简单的重置密码操作,
    运行命令:
    命令2:xhost +
    环境检查,运行命令,弹出时钟窗口为正常(apptest为应用用户,10.0.1.1为服务器IP):
 以应用用户apptest登录,运行命令:
    命令3: su – apptest
    命令4: export DISPLAY=10.0.1.1:1.0
    命令5: xclock

出现如下图所示的现象即可:

xclock

如果在操作中发现未知错误,通过以下命令 ps -ef|grep -i vnc 找出vnc的服务,全部kill -9 id,重新按照步骤操作即可。

EBS内部管理器节点问题(克隆后)

    在项目上遇到这样一个情况,从一个有双节点的EBS环境中克隆的一套系统测试系统的时候,当时在测试一个报表的时候出现警告,看日志说是内部管理器有问题,我就依此找到了内部管理器,发现内部管理器节点为空,于是找同事帮忙,定义新的节点,路径为:系统管理员/并发/管理器/定义/,定义方法相信接触的EBS的人都知道的,最后重新启动adcmctl.sh应用服务即可。非正常内部管理器和定义内部管理器如下图所示:

b

a

 

users reports back that reports are not running with no manager error (see below), and when you check manager status, all managers have Description- Target Node/Queue Unavailable

Solution One:


1. . Run the following in SQL*Plus:


SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;


COMMIT;


EXIT;

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


Db Tier:


cd $ORACLE_HOME/appsutil/scripts/<inst>/adautocfg.sh

Apps Tier:


cd $ADMIN_SCRIPTS_HOME/adautocfg.sh

 

Solution Two:


1. Run cmclean.sql with Apps tier down


2. restart all services and check

Solution Three (if All else fails, check all the details  below:)

SQL> select node_name,target_node,control_code from fnd_concurrent_queues;

SQL> update apps.fnd_concurrent_queues set node_name = ‘Node NAME’ where node_name=’Existing Node Name’;

SQL> select NODE_NAME,NODE_MODE,STATUS from fnd_nodes;

SQL> select control_code,target_node,node_name,CONCURRENT_QUEUE_NAME from fnd_concurrent_queues;

SQL> UPDATE fnd_concurrent_queues set control_code = null;

SQL> select TARGET_NODE,NODE_NAME from fnd_concurrent_queues where node_name='<Existing Node Name>’;

SQL> select TARGET_NODE,NODE_NAME from fnd_concurrent_queues where TARGET_NODE='<Existing Node Name>’;

SQL> update fnd_concurrent_queues set NODE_NAME='<Node Name>’ where NODE_NAME='<Source/Existing Node Name>’;

SQL> update fnd_concurrent_queues set TARGET_NODE='<Node Name>’ where TARGET_NODE='<Source/Exixting Node Name>’;

SQL> UPDATE fnd_concurrent_queues set target_node = ‘<Node Name>’;

SQL> UPDATE fnd_concurrent_queues set node_name = ‘<Node Name>’;

SQL> Commit;

SQL> select control_code,target_node,node_name,CONCURRENT_QUEUE_NAME from fnd_concurrent_queues;

SQL>select TARGET_NODE,NODE_NAME from fnd_concurrent_queues where node_name='<Node Name>’;

SQL>select TARGET_NODE,NODE_NAME from fnd_concurrent_queues where TARGET_NODE='<Node Name>’;

Solution four:

SQL> set linesize 1000;

SQL> column CONTROL_CODE format A15

SQL> select CONCURRENT_QUEUE_NAME, CONTROL_CODE , TARGET_NODE, NODE_NAME from FND_CONCURRENT_QUEUES where concurrent_queue_name like ‘OAMGCS_%’;

Sample Output:

CONCURRENT_QUEUE_NAME          CONTROL_CODE    TARGET_NODE                    NODE_NAME

——————————                        —————                 —————————–    ——————————

OAMGCS_SUPTEBSAL1                            E                                                                      SUPTEBSAL1

To implement the solution, please execute the following steps:

1. Please set control_code to null for the OAMGCS concurrent queue on the specific node that is affected by this issue.

SQL>  update FND_CONCURRENT_QUEUES

      set control_code = null

      where concurrent_queue_name = ‘OAMGCS_<hostname>’; <Ur Existing Node Name>

2. Make sure the Target_node is correct for the OAMGCS manager:

SQL> update FND_CONCURRENT_QUEUES

           set TARGET_NODE='<correct node >’

           where CONCURRENT_QUEUE_NAME=’OAMGCS_<hostname>’;

SQL> commit;

References:


Conflict Resolution Manager Shows Target Node/Queue Unavailable [ID 732709.1]


OAM Generic Collection Service shows State: “The target node/queue unavailable”. [ID 393706.1]


After Cloning all the Concurrent Managers do not start for the cloned Instance [ID 555081.1]


Conflict Resolution Manager Shows Target Node/Queue Unavailable [ID 732709.1]


Concurrent Managers Do Not Start After Cloning Nodes Not Updated In Conc_queues [ID 466532.1]

Summary of Possible Reasons and Solutions for the Problem Where All Concurrent Requests Stuck in Pending Phase [ID 182154.1]

Output Post Processor is Down with Actual Process is 0 And Target Process is 1 [ID 858813.1]


Using Load-Balancers with Oracle E-Business Suite Release 12 [ID 380489.1]


Documentation For Specific Load Balancer Hardware [ID 727171.1]


Sharing The Application Tier File System in Oracle E-Business Suite Release 12 [ID 384248.1]


Add new node to application —- 384248.1

Common Error: Concurrent Manager shows “Target Node/Queue Unavailable “

如何通过程序方式解密EBS系统用户密码

此方法已经通过测试,且是借鉴行内前辈的方式,表示感谢,同时需要注意,此程序在公司正式环境中严禁使用,会带来安全问题:
—将以下代码编译至目标系统—-
CREATE OR REPLACE PACKAGE cux_fnd_web_sec AUTHID CURRENT_USER AS
FUNCTION encrypt(key IN VARCHAR2, VALUE IN VARCHAR2) RETURN VARCHAR2;
FUNCTION decrypt(key IN VARCHAR2, VALUE IN VARCHAR2) RETURN VARCHAR2;
END;
/
CREATE OR REPLACE PACKAGE BODY cux_fnd_web_sec AS
FUNCTION encrypt(key IN VARCHAR2, VALUE IN VARCHAR2) RETURN VARCHAR2 AS
LANGUAGE JAVA NAME ‘oracle.apps.fnd.security.WEBSessionManagerProc.encrypt(java.lang.String,java.lang.String) return java.lang.String’;
FUNCTION decrypt(key IN VARCHAR2, VALUE IN VARCHAR2) RETURN VARCHAR2 AS
LANGUAGE JAVA NAME ‘oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String’;
END;
/

—查询出密匙—
SELECT usr.encrypted_user_password
FROM fnd_user usr
WHERE usr.user_name = ‘TEST’
—返回明文,将查询出的结果作为条件带入下面SQL命令—
SELECT cux_fnd_web_sec.decrypt(‘APPS’, ‘ZH226036FE93A20FAED89C4B92838B7C2A7F7133E613B028C482354D32F3C471AAB024794EAC70570FD2443717865F7BA824’)
FROM dual;
结果为: TEST

—批量获取,ORACLE为apps用户密码
declare
l_info varchar2(10000);
cursor user_info is
SELECT usr.user_name, usr.encrypted_user_password
FROM fnd_user usr
where 1 = 1
and to_char(usr.start_date, ‘yyyy’) >= ‘2012’
order by usr.creation_date asc;
begin
for c1 in user_info loop
l_info := cux_fnd_web_sec.decrypt(‘ORACLE’, c1.encrypted_user_password);
dbms_output.put_line(‘用户名:’ || c1.user_name || ‘ 密码:’ || l_info);
end loop;
end;

 

–后台重置密码
DECLARE
v_user_name    VARCHAR2(30) := UPPER(‘test01’);
v_new_password VARCHAR2(30) := ‘test01’;
v_status       BOOLEAN;
BEGIN
v_status := fnd_user_pkg.ChangePassword(username    => v_user_name,
newpassword => v_new_password);
IF v_status = TRUE THEN
dbms_output.put_line(‘The password reset successfully for the User:’ ||
v_user_name);
COMMIT;
ELSE
DBMS_OUTPUT.put_line(‘Unable to reset password due to’ || SQLCODE || ‘ ‘ ||
SUBSTR(SQLERRM, 1, 100));
ROLLBACK;
END IF;
END;

–直接初始化

DECLARE
P_USER_NAME FND_USER.USER_NAME%TYPE;
P_INIT_PASSWORD VARCHAR2(30);—初始化密码,非加密的。

l_change_flag VARCHAR2(10);
l_reason varchar2(2000);
BEGIN
—输入参数(用户名和初始化的密码)
P_USER_NAME := ‘SYSADMIN’;
P_INIT_PASSWORD := ‘sinosoft123’;

———
—处理–
L_change_FLAG := fnd_web_sec.change_password(P_USER_NAME,P_INIT_PASSWORD);

IF L_change_FLAG = ‘Y’ THEN
— Bug 7016473 – During an administrative reset, set the last_logon_date to NULL
— instead of SYSDATE. last_logon_date should reflect the date the user last
— logged in successfully, not the date the user’s password was reset.
— This does not regress the fix for bug 4690441 because in fnd_web_sec.disable_user
— if last_logon_date is NULL, the last_update_date will be used which is the same
— date of the sysadmin reset, so the effect is the same.

— Reset password_date field to null to force password
— expiration the next time user logs on.

UPDATE FND_USER
SET last_logon_date= NULL
,password_date = NULL
–,LAST_UPDATE_DATE = SYSDATE
–,LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE USER_NAME = P_USER_NAME;

COMMIT;
—-
DBMS_OUTPUT.PUT_LINE(‘成功初始化用户(‘||P_USER_NAME||’)的密码为:’||P_INIT_PASSWORD);
ELSE
—显示为什么不可以修改
l_reason := fnd_message.get;
fnd_message.set_name(‘FND’, ‘FND_CHANGE_PASSWORD_FAILED’);
fnd_message.set_token(‘USER_NAME’, P_USER_NAME);
fnd_message.set_token(‘REASON’, l_reason);
app_exception.raise_exception;
END IF;
END;

Report开发取责任ID问题

   在oracle EBS的report客户化开发中,fnd_profile.value('RESP_ID')无效,百思不解。后研究发现,在report中使用fnd_profile.value的oracle标准方法:
   1. 添加用户参数p_conc_request_id;
   2. 在BeforeReport和AfterReport两个report trigger中,添加srw.user_exit('FND SRWEXIT')即可在report中正常使用fnd_profile.value;

编译EBS R12 Form是出现ksh: frmcmp_batch: not found错误解决方法

    编译EBS R12 Form是出现以下错误:ksh: frmcmp_batch: not found.
    通过env|grep ORACLE_HOME查看,里面的环境变量是否有ORACLE_HOME,如果没有,则可以确定是导致此问题出现的原因。编辑".profile"文件,具体如下:
    vi  .profile 加入以下内容:". /u01/TEST/apps/apps_st/appl/APPSTMPPROD_test.env”,
再次通过命令"env|grep ORACLE_HOME"可以看到ORACLE_HOME=/u01/TEST/apps/tech_st/10.1.2
最后再次通过Form编译命令编译相关Form,成功!

技术笔记(小P的技术记录博客)