分类目录归档:经验闲谈

Oracle sysaux表空间占用问题处理

现象:

oracle sysaux表空间占用96%。通过awrinfo.sql报告核实组件:SM/AWR占用27,317.2 MB;SM/OPTSTAT 390.1 MB,大部分是AWR报告占用。

解决方法:

删除快照,回收表及index。

1、修改统计信息的保持时间,默认为31天,这里修改为7天,过期的统计信息会自动被删除

select dbms_stats.get_stats_history_retention from dual;

exec dbms_stats.alter_stats_history_retention(7);

select dbms_stats.get_stats_history_retention from dual;

2、删除AWR快照,最后再次查看SYSAUX表空间使用率(非业务时间,分段操作,以免引起临时空间增大)

select min(snap_id),max(snap_id) from dba_hist_snapshot;//查询最最小和最大快照ID

begin

dbms_workload_repository.drop_snapshot_range( low_snap_id => 32155, high_snap_id => 32300, dbid => 2950336419);

end;

3、回收降低水位

alter table WRH$_EVENT_HISTOGRAM move partition WRH$_EVENT__2950336419_2060;
alter index WRH$_EVENT_HISTOGRAM_PK rebuild partition WRH$_EVENT__2950336419_2060;

Oracle ebs R12.2.3 升级数据库后过账变慢(11.0.2.3.0-11.0.2.4.0)

背景:
oracle ebs R12.2.3对应库版本11.0.2.3.0,升级数据库版本为:11.0.2.4.0

现象:升级完成后,突然发现过账很缓慢,单个2行的日记账过账7分钟,批量过账同样耗时严重。

方案:metalink 2195917.1

主要变更内容(变更后,一般过账1-2秒完成):

配置文件:初始化 SQL 语句 – 自定义

在地点层修改为:

begin fnd_ctl.fnd_sess_ctl(”,”,”,”,”,’ALTER SESSION SET”_FIX_CONTROL”=”13704562:OFF”‘);end;

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

错误描述:

描述

————————————————————————-
应用对象程序库: 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