WebCenter Managed Servers taking too long to start up

Issue 

 

In an Oracle WebCenter Portal installation, the process of starting a managed server took around 40 minutes. Checking the start logs, there was a long time between the login and the following line.

 [EL Info]: 2013-11-06 19:35:25.773--ServerSession(645650875)--file:/SOFT/software/middleware/product/11.1.1/oracle_common/modules/oracle.jps_11.1.1/jps-internal.jar_JpsDBDataManagerV2_nonJtaDataSource=594822561 login successful

<Nov 6, 2013 7:58:58 PM CET> <Warning> <J2EE> <BEA-160140> <Unresolved optional package references (in META-INF/MANIFEST.MF): [Extension-Name:
oracle.apps.common.resource

 Also, in the managed server log, it was registered a big delay in database connections:

The thread is waiting for data from DataBase (as the admin as managed server show the same thread)
[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)'" id=23 idx=0x60 tid=24740 prio=5 alive, in native, daemon

 In order to find out the source of this problem, an AWR (Automatic Workload Repository) was requested to DBA. The report showed there was a single query that was taking 400 seconds to execute during server startup:

SELECT LOGID, DN, ACTION, APPLICATIONDN, ATTRNAME, CREATEDATE, ATTRVALUE, VERSION FROM JPS_CHANGELOG WHERE DN LIKE :1 ESCAPE :2 ORDER BY LOGID DESC

That query is against the JPS_CHANGELOG table in the OPSS (Oracle Platform Security Services) schema, where WebCenter security is located. It is a log where all the updates in WebCenter permissions are registered. The size of the table was checked, getting a result of 600.000 rows. Therefore, it needed to be purged.

Solution

 

Oracle explained how to maintain that table here. Basically, the following query should be executed in the OPSS schema:

SQL>delete from jps_changelog where createdate < (select(max(createdate) - 1) from jps_changelog);

SQL>Commit;

It is also recommended to execute the DMS_STATS package to gather database storage statistics.

After that purge:

1. Start of the servers, including AdminServer, only took 4 minutes.
2. JPS_CHANGELOG table size was 2.000 rows.
3. AWR report showed that the query was taking only 40 seconds.

In case you are not sure if WebLogic security is maintained in databse, perform the following steps:

1. Login to Enterprise Manager as administrator.
2. Select WebLogic domain.
3. Navigate to Security -> Security Provider Configuration.
4. In the screen the database storage would appear.

Conclusion 

 

If WebCenter Portal has security delegated in an OPSS schema, it is important to program a periodic purge of the JPS_CHANGELOG table. Otherwise, platform performance would decrease progressively, showing an increase in the startup time of the servers.

References

Oracle® Fusion Middleware Application Security Guide 11g Release 1 (11.1.1):

http://docs.oracle.com/cd/E21764_01/core.1111/e10043/cfgauthr.htm#CHDEGJJA

Oracle Support Service Request:

SR 3-8069279071 : My custom portal node spends more 25 min to startup