分类目录归档:经验闲谈

财务系统生产环境统计收集模式错误(从未成功)

错误描述:

描述

————————————————————————-
应用对象程序库: Version : 12.0.0

Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.

FNDGSCST module: 统计数据收集模式
————————————————————————-

当前的系统时间为 02-04-2020 00:01:15

————————————————————————-

Starts**02-04-2020 00:01:15
*Ends*02-04-2020 00:46:07
ORA-0000: normal, successful completion
————————————————————————-
FND_FILE 中日志消息开始
————————————————————————-
In GATHER_SCHEMA_STATS , schema_name= ALL percent= 60 degree = 32 internal_flag= NOBACKUP
stats on table AQ$_WF_CONTROL_P is locked
stats on table FND_CP_GSM_IPC_AQTBL is locked
stats on table FND_SOA_JMS_IN is locked
stats on table FND_SOA_JMS_OUT is locked
Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_LINE_TYPE_MAP
ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt**
Error #2: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_LOGS***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #3: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_VAT_REP_RULES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
————————————————————————-
FND_FILE 中日志消息结束
————————————————————————-
成功地重新提交具有请求编号 532437 的并发程序 FNDGSCST,以在 02-05-2020 00:01:01 启动。 (ROUTINE=AFPSRS)

————————————————————————-
正在执行请求完成选项…

Output file size:
0

已完成执行请求完成选项。

————————————————————————-
此请求发布的例外:
Concurrent Request for “统计数据收集模式” has completed with error.

————————————————————————-
已完成并发请求
当前的系统时间为 02-04-2020 00:46:08

 


 

解决方案:
参考文件:11i – 12 Gather Schema Statistics fails with Ora-20001 errors after 11G database Upgrade (Doc ID 781813.1)

具体操作:

通过以下命令查询出有2条及以上条数的记录,

select table_name, column_name, count()
from FND_HISTOGRAM_COLS
group by table_name, column_name
having count(
) > 1;

备份后删除即可。

delete from FND_HISTOGRAM_COLS
where table_name = ‘&TABLE_NAME’
and column_name = ‘&COLUMN_NAME’
and rownum=1;

— Use following SQL to delete obsoleted rows

delete from FND_HISTOGRAM_COLS
where (table_name, column_name) in
(
select hc.table_name, hc.column_name
from FND_HISTOGRAM_COLS hc , dba_tab_columns tc
where hc.table_name =’&TABLE_NAME’
and hc.table_name= tc.table_name ()
and hc.column_name = tc.column_name (
)
and tc.column_name is null
);

commit;

特别说明:日志中只报了3个表的错误,实际上如果只处理这3个表的问题,依然不能正常完成请求,需要将所有大于等于2的条数的数据删除。

Oracle EBS weblogic 禁用筛选器T3导致weblogic服务不能重启问题

背景:
2020年1月15号安全网站出了一个 CVE-2020-2546,于是按照里面的“指示”,做了T3禁用,导致weblogic服务启动失败,EBS应用不能正常登陆,启动失败日常如下:

AdminServer logs are located at /u01/DEV/app/fs1/FMW_Home/user_projects/domains/EBS_domain_DEV/servers/AdminServer/logs

01/17/20-09:13:58 :: adadminsrvctl.sh: exiting with status 1

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

01/17/20-09:24:07 :: adadminsrvctl.sh version 120.10.12020000.10
Validated the passed arguments for the option ebs-get-serverstatus
AdminServer is currently not running.

Validated the passed arguments for the option ebs-nmstart-adminsrv
Checking if the Admin Server is already up.
The Admin Server is not already up.
FMW Version is 11.1.1.9
Checking if the Node Manager is already up..
Connecting to Node Manager …
Successfully Connected to Node Manager.

The Node Manager is already up.

Starting server AdminServer …
Error Starting server AdminServer: weblogic.nodemanager.NMException: Exception while starting server ‘AdminServer’

ERROR: Unable to connect the AdminServer.

StackTrace:
java.io.IOException
at weblogic.management.remote.common.ClientProviderBase.makeConnection(ClientProviderBase.java:209)
at weblogic.management.remote.common.ClientProviderBase.newJMXConnector(ClientProviderBase.java:97)
at javax.management.remote.JMXConnectorFactory.newJMXConnector(JMXConnectorFactory.java:369)
at javax.management.remote.JMXConnectorFactory.connect(JMXConnectorFactory.java:267)
at oracle.apps.ad.util.WLUtil.initMBeanServerConnection(WLUtil.java:131)
at oracle.apps.ad.tools.configuration.EBSProvisioner.ebs_nmstart_adminsrv(EBSProvisioner.java:3682)
at oracle.apps.ad.tools.configuration.EBSProvisioner.ebs_nmstart_adminsrv(EBSProvisioner.java:3915)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at oracle.apps.ad.tools.configuration.EBSProvisioner.main(EBSProvisioner.java:8880)
Caused by: javax.naming.CommunicationException [Root exception is java.net.ConnectException: t3://devfin.guobaojinrong.com:7001: Destination unreachable; nested exception is:
java.net.ConnectException: 拒绝连接; No available router to destination]
at weblogic.jndi.internal.ExceptionTranslator.toNamingException(ExceptionTranslator.java:40)
at weblogic.jndi.WLInitialContextFactoryDelegate.toNamingException(WLInitialContextFactoryDelegate.java:792)
at weblogic.jndi.WLInitialContextFactoryDelegate.getInitialContext(WLInitialContextFactoryDelegate.java:366)
at weblogic.jndi.Environment.getContext(Environment.java:315)
at weblogic.jndi.Environment.getContext(Environment.java:285)
at weblogic.jndi.WLInitialContextFactory.getInitialContext(WLInitialContextFactory.java:117)
at javax.naming.spi.NamingManager.getInitialContext(NamingManager.java:684)
at javax.naming.InitialContext.getDefaultInitCtx(InitialContext.java:307)
at javax.naming.InitialContext.init(InitialContext.java:242)
at javax.naming.InitialContext.<init>(InitialContext.java:216)
at weblogic.management.remote.common.ClientProviderBase.makeConnection(ClientProviderBase.java:193)
… 11 more
Caused by: java.net.ConnectException: t3://devfin.guobaojinrong.com:7001: Destination unreachable; nested exception is:
java.net.ConnectException: 拒绝连接; No available router to destination
at weblogic.rjvm.RJVMFinder.findOrCreateInternal(RJVMFinder.java:216)
at weblogic.rjvm.RJVMFinder.findOrCreate(RJVMFinder.java:170)
at weblogic.rjvm.ServerURL.findOrCreateRJVM(ServerURL.java:165)
at weblogic.jndi.WLInitialContextFactoryDelegate$1.run(WLInitialContextFactoryDelegate.java:345)
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:363)
at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:146)
at weblogic.jndi.WLInitialContextFactoryDelegate.getInitialContext(WLInitialContextFactoryDelegate.java:340)
… 19 more
Caused by: java.rmi.ConnectException: Destination unreachable; nested exception is:
java.net.ConnectException: 拒绝连接; No available router to destination
at weblogic.rjvm.ConnectionManager.bootstrap(ConnectionManager.java:470)
at weblogic.rjvm.ConnectionManager.bootstrap(ConnectionManager.java:321)
at weblogic.rjvm.RJVMManager.findOrCreateRemoteInternal(RJVMManager.java:262)
at weblogic.rjvm.RJVMManager.findOrCreate(RJVMManager.java:199)
at weblogic.rjvm.RJVMFinder.findOrCreateRemoteServer(RJVMFinder.java:238)
at weblogic.rjvm.RJVMFinder.findOrCreateInternal(RJVMFinder.java:200)
… 25 more

AdminServer logs are located at /u01/DEV/app/fs1/FMW_Home/user_projects/domains/EBS_domain_DEV/servers/AdminServer/logs

01/17/20-09:24:24 :: adadminsrvctl.sh: exiting with status 1

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

应用界面登陆也显示weblogic连接失败。

 

解决过程探索:

一般遇到这类问题,第一映像就是先恢复之前的设置,于是去找相关的表或者配置文件,从表中很难找到指向表,于是从另外一个正常的环境进入相应的weblogic主页面,并进入之前配置的页面查看帮助,于是找到了一个比较有指向性的文件(不知道具体文件在哪个目录下,通过find命令搜索),config.xml。

以下文件是来自帮助文件里面的内容:

Changes take effect after you redeploy the module or restart the server.

If this attribute configures a module that you deploy (such as an application or a JDBC data source that is part of an application) or a system resource whose configuration is saved in a descriptor file instead of in the domain’s config.xml file (such as a JDBC data source that is scoped at the system level), the module or resource cannot process the change until you redeploy it or restart its host server. If the module is a component in an application, Oracle recommends that you redeploy the entire application to avoid complications due to intra-application dependencies.

If this attribute configures some other part of the domain (such as a server, a cluster, or an EJB container), the system cannot process the change until you restart the server or cluster.

进入相应目录后,发现这个config.xml文件有很多序列号的文件名,如config39.xml,config38.xml,看这些文件的更新时间,推测应该是每次更新都会做一次备份,于是备份当前的config.xml文件,恢复最近的一个config.xml文件,然后重启weblogic服务,重启(正常重启,登陆页面也正常出现,并可登陆)如下文本:

[appldev@devfin scripts]$ sh adadminsrvctl.sh start

You are running adadminsrvctl.sh version 120.10.12020000.10

Enter the WebLogic Admin password:
Enter the APPS Schema password:
Starting WLS Admin Server…
Refer /u01/DEV/app/fs1/inst/apps/DEV_devfin/logs/appl/admin/log/adadminsrvctl.txt for details

AdminServer logs are located at /u01/DEV/app/fs1/FMW_Home/user_projects/domains/EBS_domain_DEV/servers/AdminServer/logs

adadminsrvctl.sh: exiting with status 0

adadminsrvctl.sh: check the logfile /u01/DEV/app/fs1/inst/apps/DEV_devfin/logs/appl/admin/log/adadminsrvctl.txt for more information …

说明:ebs weblogic尽量少变动,在改动前也一定要做好备份。

相关参考文件:
https://www.oracle.com/security-alerts/cpujan2020.html

https://docs.oracle.com/cd/E23943_01/web.1111/e13707/ssl.htm#SECMG389

http://www.ijiandao.com/2b/baijia/345629.html

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=508979212187743&id=1280374.1&_afrWindowMode=0&_adf.ctrl-state=e2y1sye3v_1027

Oracle EBS R12.1.3登陆环境问题(JSP问题)

错误描述:

Unable to generate forwarding URL. Exception: oracle.apps.fnd.common.AppsException: java.lang.NullPointerException: null Connection

方案参考:
EBS Homepage Errors Unable To Generate Forwarding URL Exception oracle.apps.fnd.common.AppsException java.lang.NullPointerException null Connection (文档 ID 2055421.1)

说明:查看文档,发现官方是认为系统缓存或者某些jsp文件crash造成,因此主要思路是将_pages相关jsp文件进行整体重编译,然后删除相关cache文件来解决。

执行过程(关闭应用及备份删除相关操作、重启应用不在此记录):

[appldev@erpdevap bin]$ ./ojspCompile.pl –compile –flush -p 2
logfile set: /app/DEV/inst/apps/DEV_erpdevap/logs/appl/rgf/ojsp/ojspc_error.log
starting…(compiling all)
using 10i internal ojsp ver: 10
synchronizing dependency file:
enumerating jsps…8095
parsing jsp…8095
writing deplist…8095
initializing compilation:
eliminating children…6024 (-2071)
translating and compiling:
translating jsps…6024/6024 in 1m33s
compiling jsps…6024/6024 in 6m20s
Finished!

跟踪错误日志:

[BEGIN] 2020/1/13 17:38:11
[appldev@erpdevap _pages]$ tailf /app/DEV/inst/apps/DEV_erpdevap/logs/appl/rgf/ojsp/ojspc_error.log
[19659] compiling: 7s elapsed, 50 successful 0 failed
[19658] compiling: 7s elapsed, 50 successful 0 failed
[19659] compiling: 6s elapsed, 50 successful 0 failed
[19659] compiling: 4s elapsed, 50 successful 0 failed
[19658] compiling: 7s elapsed, 50 successful 0 failed
[19659] compiling: 2s elapsed, 50 successful 0 failed
[19658] compiling: 3s elapsed, 50 successful 0 failed
[19659] compiling: 2s elapsed, 24 successful 0 failed
COMPILED: 6024 [failed: 0] in 6m20s
18890 FINISHING Mon Jan 13 17:36:33 2020

[appldev@erpdevap _pages]$ exit
logout

[END] 2020/1/13 17:40:35

Oracle ebs冲销凭证报错APP-SQLGL-08087

错误如图:

原因:

此种现象是因为之前对凭证做过冲销,但是在未过账的时候,将生成的冲销凭证删除,但是冲销的记录在凭证头表字段accrual_rev_period_name留下记录,记录的内容是报错界面默认的冲销期间(2019-10)。

解决方案:

直接更新gl_je_headers表中字段accrual_rev_period_name为空即可正常冲销动作(更新前一定要备份,更新后重新期间可选)。

Oracle EBS R12.2.6系统资产模块折旧账务未传总账问题处理

背景:

某客户在10月8号运行9月折旧后,创建会计发现请求报黄(但是能把9月的折旧数据正常传过去),于是看创建会计科目输出报表(或者看资产模块“子分类帐期间关闭例外报表”),可以发现里面有大量7月31号的数据,都是很明确的提示如下所示内容,无效期间:

原因探索:

此类问题原因很多,本人遇到过以下几种情况,以频率排序:
1、资产传总账时未正常传至GL模块(总账期间正常打开,此种情况可根据例外报表的提示处理,再次传总账即可);

2、资产传总账时未正常传至GL模块(总账期间关闭);

3、交叉验证规则导致生成账务问题(这种情况很少,一般在新机构建立时可能有)。

具体到本次问题,经过追溯,发现用户在7月31号晚上5点03分提交了运行折旧的请求,但是在请求未完成时,立即提交了创建会计科目到总账模块(默认会将资产相关账务处理传总账)。导致7月新增资产数据科目和中转科目都正常到了总账模块(用户检查时检查了中转科目为0,未再做深入核实,8月折旧也出问题,用户未察觉)。

方案1:打开7月期间正常传至7月总账模块,由于用户反馈7、8月份报表已经出具(金融行业),不同意再开7月期间。

方案2:后台修复数据至当期(9月),具体执行命令不再赘述,见以下脚本(根据自身情况限制条件)

–1、备份三个表的问题数据(具体条件根据自身问题限制)
select t.*
from xla_ae_headers t
where 1 = 1
and to_char(t.accounting_date, ‘yyyy-mm-dd’) = ‘2019-07-31’
and t.gl_transfer_status_code = ‘N’;

select *
from xla_ae_lines t
where 1 = 1
and to_char(t.accounting_date, ‘yyyy-mm-dd’) = ‘2019-07-31’
and t.ae_header_id in
(select t.ae_header_id
from xla_ae_headers t
where 1 = 1
and to_char(t.accounting_date, ‘yyyy-mm-dd’) = ‘2019-07-31’
and t.gl_transfer_status_code = ‘N’);

select *
from xla.xla_events# t
where t.event_type_code = ‘DEPRECIATION’
and to_char(t.event_date, ‘yyyy-mm-dd’) = ‘2019-07-31’
order by t.transaction_date desc;

–2、更新三张表,按顺序以免后续无法关联,更新的时候最好看一下条数是否正确
update xla_ae_lines t
set t.accounting_date = to_date(‘2019-09-30’, ‘yyyy-mm-dd’)
where 1 = 1
and to_char(t.accounting_date, ‘yyyy-mm-dd’) = ‘2019-07-31’
and t.ae_header_id in
(select t.ae_header_id
from xla_ae_headers t
where 1 = 1
AND to_char(t.accounting_date, ‘yyyy-mm-dd’) = ‘2019-07-31’
and t.gl_transfer_status_code = ‘N’);

update xla_ae_headers t
set t.accounting_date = to_date(‘2019-09-30’, ‘yyyy-mm-dd’),
t.period_name = ‘2019-09’
where 1 = 1
and to_char(t.accounting_date, ‘yyyy-mm-dd’) = ‘2019-07-31’
and t.gl_transfer_status_code = ‘N’;

update xla_events t
set t.transaction_date = to_date(‘2019-09-30’, ‘yyyy-mm-dd’),
t.event_date = to_date(‘2019-09-30’, ‘yyyy-mm-dd’),
t.event_status_code = ‘P’,
t.process_status_code = ‘P’
where t.event_type_code = ‘DEPRECIATION’
and to_char(t.event_date, ‘yyyy-mm-dd’) = ‘2019-07-31′;

扩展:

LOOKUP_TYPE LANGUAGE LOOKUP_CODE MEANING DESCRIPTION
XLA_EVENT_PROCESS_STATUS US D Draft Draft
XLA_EVENT_PROCESS_STATUS US E Error Error
XLA_EVENT_PROCESS_STATUS US I Invalid Invalid
XLA_EVENT_PROCESS_STATUS US P Processed Processed
XLA_EVENT_PROCESS_STATUS US R Related Event In Error Related Event In Error
XLA_EVENT_PROCESS_STATUS US U Unprocessed Unprocessed

LOOKUP_TYPE LANGUAGE LOOKUP_CODE MEANING DESCRIPTION
XLA_EVENT_STATUS US I Incomplete Incomplete
XLA_EVENT_STATUS US N No Action No Action
XLA_EVENT_STATUS US P Processed Processed
XLA_EVENT_STATUS US U Unprocessed Unprocessed

SELECT * FROM APPS.FND_LOOKUP_VALUES WHERE LOOKUP_TYPE =’XLA_EVENT_PROCESS_STATUS’ ;

SELECT * FROM APPS.FND_LOOKUP_VALUES WHERE LOOKUP_TYPE =’XLA_EVENT_STATUS’ ;

日期无论改到哪天都会在期间的最后一天。