背景：
客户需要限制某些科目只能从接口进入系统，不能手工及类手工方式录入。
思路：
寻求标准方案；客户化ADI。
标准方案：
先查看科目值集处对科目属性的控制，其中有一个控制项是“第三方控制账户”，控制账户内可选择“限制人工日记帐”（此时想看能否可以选择来源进行控制，于是通过sql方式【select * from fnd_lookups t where t.meaning like ‘%限制人工日记%’】找到控制账户的lookup_type”GL_CONTROL_ACCOUNT_SOURCES”，发现不能新增来源），于是从字面理解是否只会对日记账来源为“人工”的进行控制。
测试结果：前台界面确实控制住不能录入，并弹窗提示“您不能使用控制账户。请选择其他账户”，满足需求。
然后通过ADI导入，无论ADI的来源选择何种，均会提示“您不能使用控制账户。请选择其他账户”，满足需求。
最后通过接口表的方式，通过设置过“限制人工日记账”的科目导入生成账务，可以正常生成日记账，满足需求。
完美解决问题，不再讨论客户化的方式。
Resolving error mod_oc4j Failed To find A Failover OC4J Process For Session Request For Destination (Doc ID 785296.1)
Have encountered an issues with oacore crashing in R12 Oracle Applications frequently:
Apache日志$LOG_HOME/ora/10.1.3/Apache
Error Message in error_log:
—————————
mod_oc4j: Failed to find a failover oc4j process for session request for destination: application://oacore (no island or jgroup).
The error ‘mod_oc4j Failed to find a failover oc4j process for session request for destination’ indicates that a session request was received by the Process Manger (PM). The PM was unable to find a java process to route the session to which would be defined as a “island” or “jgroup” and it failed.
The error indicates for some reason the OC4J process has become non responsive to the point where the PM has determined it failed or is no longer available. This error usually occurs when OHS (mod_oc4j) is not able to communicate ( over AJP ) with OC4J.
There are multiple reasons why this error may have happened: Some common reasons are
1. Load: e.g. Too many clients connecting to the application :
– The number of requests are high enough that the process cannot respond to new requests prior to reaching the timeout period.
2. Performance: Heavily loaded JVM or Lack of enough memory for JVM
– The OC4J oacore JVM processes in the middle tier are heavily loaded
– High number of jdbc connections with database there by increasing the number of threads on the java process.
3. Code:
* Internal to Application Server, one of the components are not operating
correctly or efficiently.
* External to ApplicationServer, E-Business code (seeded or custom code) causing
memory leakages.
4. Lack of free ports for AJP communication
Suggestion:
———-
Increae the OACore Heap Configuration to 512/1024 in opmn.xml
Change the below entry in opmn.xml(also modify the entry in xml file) and Bounce the opmn services.
From:
<data id=”java-options” value=”-server -verbose:gc -Xmx512M -Xms512M -XX:MaxPermSize=160M -XX:NewRatio=2
To:
<data id=”java-options” value=”-server -verbose:gc -Xmx1024M -Xms1024M -XX:MaxPermSize=256M -XX:NewRatio=2
注意：mod_oc4j: Failed to find a failover oc4j process for session request for destination: application://form (no cluster or jgroup).
If the error ends with “//form (no island or jgroup)” then oc4j forms process
has issue or if the error ends with “//oacore (no island or jgroup)” then oc4j
oacore process has issue.
In 11i, those changes are made in the $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.conf file, while in R12 in the $ORA_CONFIG_HOME/10.1.3/opmn/conf/opmn.xml
The heap is configured by s_oacore_jvm_start_option, s_forms_jvm_start_options in R12. For 12.1 and higher start with the following and increase as per the user load:
-Xmx1024M -Xms512M -XX:MaxPermSize=256M
-XX:NewRatio=2 -XX:+PrintGCTimeStamps
Also, Add the following parameter to the DBC file:
JDBC\:oracle.jdbc.maxCachedBufferSize=262144
For More Information Please Ref:Guidelines to setup the JVM in Apps Ebusiness Suite 11i and R12 [ID 362851.1]
南陌东城春早时，相逢何处不依依。
桃红李白皆夸好，须得垂杨相发挥。
1、SQL ordered by Gets
select *
from (select substr(sql_text, 1, 40) sql,
buffer_gets,
executions,
buffer_gets / executions “Gets/Exec”,
hash_value,
address
from v$sqlarea
where buffer_gets > 0
and executions > 0
order by buffer_gets desc)
where rownum <= 10;
2、SQL ordered by Reads
select *
from (select substr(sql_text, 1, 40) sql,
disk_reads,
executions,
disk_reads / executions “Reads/Exec”,
hash_value,
address
from v$sqlarea
where disk_reads > 0
and executions > 0
order by disk_reads desc)
where rownum <= 10;
3、SQL ordered by Executions
select *
from (select substr(sql_text, 1, 40) sql,
executions,
rows_processed,
rows_processed / executions “Rows/Exec”,
hash_value,
address
from v$sqlarea
where executions > 0
order by executions desc)
where rownum <= 10;
4、SQL ordered by Parse Calls
select *
from (select substr(sql_text, 1, 40) sql,
parse_calls,
executions,
hash_value,
address
from v$sqlarea
where parse_calls > 0
order by parse_calls desc)
where rownum <= 10;
5、Running Time top 10 sql
select *
from (select t.sql_fulltext,
(t.last_active_time –
to_date(t.first_load_time, ‘yyyy – mm – dd hh24 :mi :ss‘)) * 24 * 60,
disk_reads,
buffer_gets,
rows_processed,
t.last_active_time,
t.last_load_time,
t.first_load_time
from v$sqlarea t
order by t.first_load_time desc)
where rownum < 10;
当建立总账汇总模板时，报错：
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
GLSTPM module: 增加/删除汇总账户
+---------------------------------------------------------------------------+
当前的系统时间为 24-08-2020 09:41:45
+---------------------------------------------------------------------------+
>> main() 24-08-2020 09:41:45
>> glsini() 24-08-2020 09:41:45
STPM0021: 默认优化系数为 0。
<< glsini() 24-08-2020 09:41:45
>> glstexs() 24-08-2020 09:41:45
<< glstexs() 24-08-2020 09:41:45
>> glsfst() 24-08-2020 09:41:45
>> open_cursor() 24-08-2020 09:41:45
<< open_cursor() 24-08-2020 09:41:45
>> load_segnum_vsid() 24-08-2020 09:41:45
<< load_segnum_vsid() 24-08-2020 09:41:45
>> do_fetch_templates() 24-08-2020 09:41:45
SHRD0108: 从 GL_SUMMARY_TEMPLATES 中取回 1 记录
<< do_fetch_templates() 24-08-2020 09:41:45
>> close_cursor() 24-08-2020 09:41:45
<< close_cursor() 24-08-2020 09:41:45
<< glsfst() 24-08-2020 09:41:45
>> glsltmp() 24-08-2020 09:41:45
>> gludlk() 24-08-2020 09:41:45
<< gludlk() 24-08-2020 09:41:45
<< glsltmp() 24-08-2020 09:41:45
>> glstexs() 24-08-2020 09:41:45
<< glstexs() 24-08-2020 09:41:45
>> glsuab() 24-08-2020 09:41:45
<< glsuab() 24-08-2020 09:41:45
>> glstadd() 24-08-2020 09:41:45
>> glucmt() 24-08-2020 09:41:45
<< glucmt() 24-08-2020 09:41:45
STPM0003: 已验证模板 "D-D-LEV1-D-D-D-D-D-D-D-D"，并已定义全部累计组。
>> glsdsh() 24-08-2020 09:41:45
SHRD0119: 从 GL_SUMMARY_HIERARCHIES 中删除 0 记录。
SHRD0119: 从 GL_SUMMARY_HIERARCHY_INT 中删除 0 记录。
>> glucmt() 24-08-2020 09:41:45
<< glucmt() 24-08-2020 09:41:45
<< glsdsh() 24-08-2020 09:41:45
>> glsdah() 24-08-2020 09:41:45
SHRD0119: 从 GL_CODE_COMBINATIONS 中删除 0 记录。
>> glucmt() 24-08-2020 09:41:45
<< glucmt() 24-08-2020 09:41:45
<< glsdah() 24-08-2020 09:41:45
>> glsash() 24-08-2020 09:41:45
SHRD0117: 把 761 记录插入 GL_SUMMARY_HIERARCHIES
>> glucmt() 24-08-2020 09:41:45
<< glucmt() 24-08-2020 09:41:45
<< glsash() 24-08-2020 09:41:45
>> glsmah() 24-08-2020 09:41:45
>> gls_fill_seg_info() 24-08-2020 09:41:45
<< gls_fill_seg_info() 24-08-2020 09:41:45
>> gls_get_max_ccid() 24-08-2020 09:41:45
>> glucmt() 24-08-2020 09:41:45
<< glucmt() 24-08-2020 09:41:45
<< gls_get_max_ccid() 24-08-2020 09:41:45
>> glstmt() 24-08-2020 09:41:45
<< glstmt() 24-08-2020 09:41:45
>> gls_csa_rollup() 24-08-2020 09:41:45
>> glsgsmi() 24-08-2020 09:41:45
SHRD0114: 正在执行 Pro*C 语句 Insert into gl_sum_int ...
SHRD0043: ERROR: ORA-00001: 违反唯一约束条件 (GL.GL_SUMMARY_INT_2_U1)
<x glsgsmi() 24-08-2020 09:41:45
<x gls_csa_rollup() 24-08-2020 09:41:45
SHRD0075: 在 gls_csa_rollup() 的错误.
<x glsmah() 24-08-2020 09:41:45
SHRD0075: 在 glsmah() 的错误.
<x glstadd() 24-08-2020 09:41:45
SHRD0075: 在 glstadd_addtmp() 的错误.
问题处理：
通过脚本检查总账科目设置问题：
applprod:
@/u01/PROD/app/fs1/EBSapps/appl/gl/12.0.0/sql/glxacovl.sql
处理完直接的子父级后：
重新删除之前错误的模板，运行：
增加/删除汇总账户
运行完成后，重新新增模板即可。
具体sql如下：
SELECT vs.flex_value_set_name value_set_name,
fvh1.parent_flex_value ancestor,
gl_flexfields_pkg.get_parent_from_children(val.flex_value_set_id,
fvh1.parent_flex_value,
fvh1.child_flex_value_low,
fvh1.child_flex_value_high,
1) parent_flex_value,
fvh1.child_flex_value_low child_flex_value_low,
fvh1.child_flex_value_high child_flex_value_high,
gl_flexfields_pkg.get_parent_from_children(val.flex_value_set_id,
fvh1.parent_flex_value,
fvh2.child_flex_value_low,
fvh2.child_flex_value_high,
2) parent_flex_value2,
fvh2.child_flex_value_low child_flex_value_low2,
fvh2.child_flex_value_high child_flex_value_high2
FROM fnd_flex_values val,
fnd_flex_value_sets vs,
fnd_flex_value_hierarchies fvh1,
fnd_flex_value_hierarchies fvh2
WHERE val.structured_hierarchy_level IS NOT NULL
AND vs.flex_value_set_id = val.flex_value_set_id
AND fvh1.flex_value_set_id = val.flex_value_set_id
AND fvh1.parent_flex_value = val.flex_value
AND fvh2.flex_value_set_id = val.flex_value_set_id
AND fvh2.parent_flex_value = val.flex_value
AND fvh2.child_flex_value_low BETWEEN fvh1.child_flex_value_low AND fvh1.child_flex_value_high
AND (fvh2.child_flex_value_low <> fvh1.child_flex_value_low OR
fvh2.rowid < fvh1.rowid)
AND fvh2.rowid <> fvh1.rowid
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8