Oracle Agile Application or Quick Search performance issue and how to resolve?

When you come across any performance issue on agile first, you need to verify schema integrity by running agile9_check.sql.

The agile9_check.sql it verifies Database Schema version with Agile Schema version, and it MUST match. Also, it validates schema objects against Agile schema version and report any errors or warnings.

You may see ERRORS/WARNINGS
ERROR: Missing or INVALID index (columns) XXXXXXXXX on table XXXXX.
ERROR: Missing or INVALID FTS CTX indexes XXXXXXX.
WARNING: ACTIVITY_BASELINE_XXXXX does not belong to AGILE DB. Please Drop the Table if not required.

In case of any Errors/Warnings you need we need to fix the issue. If you have created any custom objects you will see WARNING that object doesn't  below to Agile DB and you can ignore. 

Once user fix the schema integrity download listFTSInfo.sql and run the script as Agile user and it will generate listFTSInfo_xxxxxxxxxx.log file. You need to search for "029", where it says "List Index Percent of Fragmentation" where you can see the indexes that has fragmentation.

If you see any fragmentation, it will effect Quick search and stuck threads will be generated.

<[STUCK] ExecuteThread: '19' for queue: 'weblogic.kernel.Default (self-tuning)' has been busy for "1,247" seconds working on the request "Http Request Information: weblogic.servlet.internal.ServletRequestImpl@2f2fb94[GET /Agile/PCMServlet]
", which is more than the configured time (StuckThreadMaxTime) of "1,200" seconds in "server-failure-trigger". Stack trace:
java.net.SocketInputStream.socketRead0(Native Method)

To resolve the issue, Log into sqlplus as agile database user, and run agile_ctx_recreate.sql found under %Oracle_base%\admin\{SID}\create\{agile_schema_username} to rebuild CTX indexes.

Oracle support recommends stopping Agile application, run agile_ctx_recreate.sql and Start the Application.

It is highly advisable to run complie_Invalid_objects.sql, agile9stats.sql to recompile invalid objects and collect stats to improve the performance.

Refer Oracle Agile support doc for Full Text search (FTS) Enablement, synchronization, Indexing and optimization.

Frequently Asked Questions on Agile Product Lifecycle Management (PLM) Full Text Search (FTS) Enablement, Synchronization, Indexing, and Optimization (Doc ID 1503311.1)

For Agile Stuck thread issue, refer below Oracle support Doc ID

Agile Managed Servers Goes to Warning State, Having Stuck Thread at com.agile.cs.query.QuerySessionBean (Doc ID 2919154.1)

Thanks & Regards

https://oracleracexpert.com, Oracle ACE

BEA-002936 maximum thread constraint or BEA-000337 [STUCK] ExecuteThread in WebLogic

I come across an issue and found below error message in server log file. As per oracle note Doc ID 1356278.1 this message is only informational and can be safely ignored. But our planning application crashed and we had to restart the services.

####<Sep 8, 2022 11:06:22 AM PDT> <Info> <WorkManager> <SCLWPA2188> <Planning0> <[ACTIVE] ExecuteThread: '29' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <> <1662660382282> <BEA-002936> <maximum thread constraint ASYNC_REP_FLUSH_WM is reached>
####<Sep 8, 2022 11:08:39 AM PDT> <Info> <WorkManager> <SCLWPA2188> <Planning0> <[ACTIVE] ExecuteThread: '31' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <> <1662660519981> <BEA-002936> <maximum thread constraint ASYNC_REP_FLUSH_WM is reached>

In many cases the error may clear after some time. After research I found that WebLogic Server associates some internal work managers whenever a JMS server is created and below are the parameters

weblogic.jms.<JMSServer_NAME>.AsyncPush
Min threads = 1, max threads = unlimited, high fair share

weblogic.jms.<JMSServer_NAME>.Limited
Min threads = 1, max threads = 8, normal fair share.

weblogic.jms.<JMSServer_NAME>.System
Min threads = 1, max threads = unlimited, high fair share.

The work managers will expand up to the max limit and will shrink back when the load returns to normal, however shrinking doesn’t start unless there are few idle state threads.

As you see the work managers have a limited thread count of 8, but this limit is internal and cannot be changed without contacting Oracle support.  

Users may also see STUCK thread issues

Sep 3, 2022 11:23:31 AM EDT> <Error> <WebLogicServer> <abcsystems.orass.com> <server-cc113sp> <[ACTIVE] ExecuteThread: '33' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <> <45443435326649> <BEA-000337> <[STUCK] ExecuteThread: 45 for queue: 'weblogic.kernel.Default (self-tuning)' has been busy for “550" seconds working on the request "Workmanager: default, Version: 0, Scheduled=true, Started=true, Started time: 678511 ms

The root cause Thread gets stuck if it is continually working (not idle) for a set period of time. This time is called the StuckThreadMaxTime, and has a default value of 600 seconds. The stuck thread cannot complete its current work or accept any new work the server logs the message. Note that if all threads gets stuck then server health state will change to either “warning” or “critical”

The stuck thread errors may disappear after some time if you see these errors frequently then you should start tuning to avoid issues. Note that restart will clear these errors.

I would recommend you go to WebLogic configuration and finetune your parameters based upon the current settings such as “Stuck thread max time” and “Stuck Thread Timer Interval”

To fine tune the parameter go to weblogic console à Domain à Environment à Server à <Managed Server> à Configuration > Tuning

Make sure you apply changes and reboot the server for new changes to take into effect. You can also refer below WLS documentation and Metalink note for more details.

WLS documentation : https://docs.oracle.com/cd/E23943_01/apirefs.1111/e13952/taskhelp/tuning/TuningExecuteThreads.html
Metalink Note: Note 1302472.1 - Information about STUCK Threads Condition and Tuning in Weblogic Server

Thanks & Regards

http://oracleracexpert.com, Oracle ACE Pro

weblogic.jdbc.extensions.PoolDisabledSQLException

We have come across content pool suspended issue in WebLogic as pool got exhausted. All new connections failings and existing connections are running very slow. Which intern created issue in Agile PLM as it is using WebLogic.

weblogic.jdbc.extensions.PoolDisabledSQLException: weblogic.common.resourcepool.ResourceDisabledException: Pool AgileContentPool is Suspended, cannot allocate resources to applications..

        at weblogic.jdbc.common.internal.JDBCUtil.wrapAndThrowResourceException(JDBCUtil.java:265)

        at weblogic.jdbc.jts.Driver.newConnection(Driver.java:900)

        at weblogic.jdbc.jts.Driver.createLocalConnection(Driver.java:220)

        at weblogic.jdbc.jts.Driver.connect(Driver.java:170)

        at weblogic.jdbc.common.internal.RmiDataSource.getConnectionInternal(RmiDataSource.java:523)

        at weblogic.jdbc.common.internal.RmiDataSource.getConnection(RmiDataSource.java:516)

        at weblogic.jdbc.common.internal.RmiDataSource.getConnection(RmiDataSource.java:512)

<2019-09-10 10:38:56,366> <PCMHelperSessionBean_9xz6y2_Impl:ERROR> Session terminated...

To find the root causes examine the thread dumps while the error is occurring and pay attention to stuck thread.

If there are long running queries find out what it is running and users may see ORA-00020: Maximum number of processes reached.

Possible reasons :

1. Database running some custom or un-optimized long running queries
2. Application forgot to release the connection back to pool due to recent config changes, which can result connection leak.
3. Inactive connection timeout value may need to change to support system growth/load.

We have verified at DB level and everything working fine except more connections hanging around and not releasing. After investigation we found that most of the connections going though one server due to recent patch and didn’t update the Pcclient.jnlp, Jndiurl.properties..etc

After updating the config we see that user connections going trough all servers and connection load balance happening as expected and issue has been resolved.

Hope this post helps to resolve Agile Content pool suspended issue.

Thanks,
Satishbabu Gunukula, Oracle ACE

http://www.oracleracexpert.com

How to find Oracle Database and Oracle WebLogic Server Version?

There are several ways to find Oracle Database and Oracle WebLogic Version

Oracle Database
1. Select v$instance for only Database version

SQL> select version from V$INSTANCE;
VERSION
-----------------
11.2.0.4.0

2. Select v$version table in database

SQL> Select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 – Production

Identifying Your Oracle Database Software Release

·         10 is the major database release number

·         1 is the database maintenence release number

·         0 is the application server release number

·         1 is the component-specific release number

·         0 is the platform-specific release number

3.  Search for “Enterprise Edition” in alert_SID.log file

$cat alert_ORCL.log |grep "Enterprise Edition"
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Oracle Weblogic:

1.Find WebLoic version from WebLogic Jar file using below command

For ex:-
$cd /u01/home/ wls1211/wlserver_12.1/server/lib/
java -cp weblogic.jar weblogic.version

2. Check registry.xml under $MW_HOME and search for earch for “component name=”WebLogic Server””

For ex: cat /u01/home/ wls1211/registry.xml | grep 'component name="WebLogic Server"'
<component name="WebLogic Server" version="12.1.1.0" InstallDir="/u01/home/oracle/Agile/wls1211/wlserver_12.1">

3. You can also find version by searching “WebLogic Server” in WebLogic Admin server logfile
$DOMAIN_HOME/servers/AdminServer/admin/AdminServer.log

Regards,

Satishbabu Gunukula, Oracle ACE

http://www.oracleracexpert.com

java.sql.SQLRecoverableException: IO Error: Connection reset

I have come across below error during Oracle Agile Managed Server deployment (Agile 9.3.3). When trying to startup the Managed server which is using Weblogic12c, it didn’t startup and found below error in logfile.

Your environment has been set.
AgileAuthenticationProviderImpl.initialize
log4j:WARN No appenders could be found for logger (com.agile.util.sql.OracleConnectionImpl).
log4j:WARN Please initialize the log4j system properly.
java.sql.SQLRecoverableException: IO Error: Connection reset
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:467)
at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:546)
at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:236)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:280)
at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:207) at
oracle.jdbc.pool.OracleConnectionPoolDataSource.getPhysicalConnection(OracleConnectionPool Data Source.java:139) at oracle.jdbc.pool.OracleConnectionPoolDataSource.getPooledConnection (Oracle ConnectionPool Data Source.java:88) at........ com.bea.common.security.internal.service.LoginModuleWrapper$1.run(LoginModuleWrapper.java:110)
at java.security.AccessController.doPrivileged(Native Method) at com.bea.common.security.internal.service.LoginModuleWrapper.login(LoginModuleWrapper.java:106)
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 javax.security.auth.login.LoginContext.invoke(LoginContext.java:762)
at javax.security.auth.login.LoginContext.access$000(LoginContext.java:203)
at javax.security.auth.login.LoginContext$4.run(LoginContext.java:690)
at javax.security.auth.login.LoginContext$4.run(LoginContext.java:688)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.login.LoginContext.invokePriv(LoginContext.java:687)
at javax.security.auth.login.LoginContext.login(LoginContext.java:595) at com.bea.common.security.internal.service.JAASLoginServiceImpl.login(JAASLoginServiceImpl.java:113)
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 com.bea.common.security.internal.utils.Delegator$ProxyInvocationHandler.invoke(Delegator.java:57)
at com.sun.proxy.$Proxy13.login(Unknown Source) at weblogic.security.service.internal.WLSJAASLoginServiceImpl$ServiceImpl.login(WLSJAASLoginServiceImpl.java:89) at com.bea.common.security.internal.service.JAASAuthenticationServiceImpl.authenticate(JAASAuthenticationServiceImpl.java:82)
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 com.bea.common.security.internal.utils.Delegator$ProxyInvocationHandler.invoke(Delegator.java:57)
at com.sun.proxy.$Proxy34.authenticate(Unknown Source) at weblogic.security.service.WLSJAASAuthenticationServiceWrapper.authenticate(WLSJAASAuthenticationServiceWrapper.java:40) at weblogic.security.service.PrincipalAuthenticator.authenticate(PrincipalAuthenticator.java:338) at weblogic.security.service.CommonSecurityServiceManagerDelegateImpl.doBootAuthorization(CommonSecurityServiceManagerDelegateImpl.
java:930) at weblogic.security.service.CommonSecurityServiceManagerDelegateImpl. initialize(CommonSecurityServiceManagerDelegateImpl.java:1054) at
weblogic.security.service.SecurityServiceManager.initialize(SecurityServiceManager.java:873)
at weblogic.security.SecurityService.start(SecurityService.java:148)
at weblogic.t3.srvr.SubsystemRequest.run(SubsystemRequest.java:64)
at weblogic.work.ExecuteThread.execute(ExecuteThread.java:256)
at weblogic.work.ExecuteThread.run(ExecuteThread.java:221)
Caused by: java.net.SocketException: Connection reset
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:118)
at java.net.SocketOutputStream.write(SocketOutputStream.java:159)
at oracle.net.ns.DataPacket.send(DataPacket.java:210)
at oracle.net.ns.NetOutputStream.flush(NetOutputStream.java:230)
at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:312)
at oracle.net.ns.NetInputStream.read(NetInputStream.java:260)
at oracle.net.ns.NetInputStream.read(NetInputStream.java:185)
at oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket (T4CSocketInputStreamWrapper. java:124) at oracle.jdbc.driver.T4CSocketInputStream Wrapper.read(T4CSocketInputStreamWrapper.java:80)
at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1137)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:290)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4CTTIoauthenticate.doOSESSKEY(T4CTTIoauthenticate.java:404)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:385)
... 59 more
java.sql.SQLRecoverableException: IO Error: Connection reset

You will also see below error in the log file, but these errors will mislead you

<Jun 19, 2014 2:13:44 PM PDT> <Critical> <Security> <BEA-090402> <Authentication denied: Boot identity not valid; The user name and/or password from the boot identity file (boot.properties) is not valid. The boot identity may have been changed since the boot identity file was created. Please edit and update the boot identity file with the proper values of username and password. The first time the updated boot identity file is used to start the server, these new values are encrypted.>

<Jun 19, 2014 2:13:44 PM PDT> <Critical> <WebLogicServer> <BEA-000386> <Server subsystem failed. Reason: weblogic.security.SecurityInitializationException: Authentication denied: Boot identity not valid; The user name and/or password from the boot identity file (boot.properties) is not valid. The boot identity may have been changed since the boot identity file was created. Please edit and update the boot identity file with the proper values of username and password. The first time the updated boot identity file is used to start the server, these new values are encrypted.
...(CommonSecurityServiceManagerDelegateImpl.java:960) at weblogic.security.service.CommonSecurityServiceManagerDelegateImpl.initialize(CommonSecurityServiceManagerDelegateImpl.java:1054) at weblogic.security.service.SecurityServiceManager.initialize(SecurityServiceManager.java:873)
at weblogic.security.SecurityService.start(SecurityService.java:148)
at weblogic.t3.srvr.SubsystemRequest.run(SubsystemRequest.java:64)
Truncated. see log file for complete stacktrace
Caused By: javax.security.auth.login.LoginException: java.lang.ExceptionInInitializerError
 at com.agile.admin.security.weblogic.WLSLoginModule.login
(WLSLoginModule.java:193) at com.bea.common.security.internal.service.LoginModuleWrapper$1.run(LoginModuleWrapper. java: 110 at java.security.AccessController.doPrivileged(Native Method)
at com.bea.common.security.internal.service.LoginModuleWrapper.login(LoginModuleWrapper .java:106) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)


Findings/troubleshooting

1. When trying to startup the Managed server the weblogic taking more time than expected

2. If you read the highlight error then you will understand what the exact cause for this error is “java.sql.SQLRecoverableException: IO Error: Connection reset”.

3. Found below warning in alert<sid>.log
WARNING: inbound connection timed out (ORA-3136)

4. The userid/password is correct

By looking the above findings it seems that weblogic taking more time and it was not able to connect to the database and timed out with ORA-3136 error. It looks like inbound connection timeout occurred.

Proposed Solution: I have changed the SQLNET.INBOUND_CONNECT_TIMEOUT from default (60 sec) value to 0(indefinite).

After changing the parameter the weblogic Instance comes online without any issues.

It looks like there is bug in Weblogic due to which it is not able to connect to database with in the specified time. Still troubleshooting to find the root cause, if I find anything will try to update in the post.

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

Exception in thread "Thread-1" java.lang.ExceptionInInitializerError

We are using the Weblogic 12.1.1with Oracle Agile 9.3.3. After applying the hot fix, when we try to start the application we received the below error message

Please upgrade your admin data schema to 9.3.385.2
Shutting down the server...

<May 30, 2014 1:15:23 PM PDT> <Alert> <WebLogicServer> <BEA-000396> <Server shutdown has been requested by <WLS Kernel>.>
Exception in thread "Thread-1" java.lang.ExceptionInInitializerError  at weblogic.wsee.runtime.JMSStoreRoutableIDMapper.shutdown(JMSStoreRoutableIDMapper.java:479)
at weblogic.wsee.runtime.WebServicesRuntime.shutdown(WebServicesRuntime.java:236)
at weblogic.wsee.runtime.WseeService.haltService(WseeService.java:65)
at weblogic.server.ServiceActivator.halt(ServiceActivator.java:113)
at weblogic.t3.srvr.ServerServicesManager.haltInternal(ServerServicesManager.java:504)
at weblogic.t3.srvr.ServerServicesManager.halt(ServerServicesManager.java:336)
at weblogic.t3.srvr.T3Srvr.shutdown(T3Srvr.java:1122)
at weblogic.t3.srvr.T3Srvr.forceShutdown(T3Srvr.java:1026)
at weblogic.t3.srvr.T3Srvr$2.run(T3Srvr.java:1039)
Caused by: java.lang.IllegalStateException: Shutdown in progress
at java.lang.ApplicationShutdownHooks.add(ApplicationShutdownHooks.java:66)
at java.lang.Runtime.addShutdownHook(Runtime.java:211)
at weblogic.wsee.persistence.LogicalStore.<clinit>(LogicalStore.java:159)

From the error it is clear that the admin data schema needs to be upgrade. That means the hot fix have Database component and that needs to run after the hot fix.

I found a Database script i.e. oracle_933HF30.sql and applied to upgrade the admin data schema.

Now the application comes online without any issues.

Regards
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com