Hello,
Yes, we use the port from com.kintana.core.server.JDBC_URL to stablish a main connection to DB.
The Broker reports will help you to determine a max out of the pools, but the Socket timeout is an intermittent
connection problem. So I don’t think Broker report will tell much, as we have plenty connections available….right?
Set the following two lines in the logging.conf file, in order to monitor database pool life thread.
The logging.conf file is found under <PPM_HOME>conf folder.
1) Start PPM server
2) To observe life thread turn on the following logging.conf
com.kintana.core.logging.PRODUCT_FUNCTION_LOGGING_LEVEL = com.kintana.core.database.pool,INFO
com.kintana.core.logging.SYSTEM_THRESHOLD = DEBUG
I have some other suggestions/steps:
1. Ensure that SQL plus can connect to database server from PPM box while the error occurs and wait for 5-10 minutes to try executing a query without getting any error.
This will help you to rule out if this is a SQL/JDBC setting in the server.
2. We need your DBA to monitor the oracle logs when this issue occurs,
and search for any Oracle error. Look at oracle DB server log to trace any timeout on DB side.
3-Are these two portlets or one of them custom? Do you get this issue with out of the box portlets?
4-What is the oracle database version and oracle client version? It would be great to have matching versions.
5- Make sure that the JDBC_URL parameter is in the following format and that the values are all correct:
com.kintana.core.server.JDBC_URL=jdbc:oracle:thin:@hostname:port:SID
6- Verify that the port for the database in tnsnames.ora in the PPM server is same port as defined in server.conf for jdbc_url parameter.
7-Are you using ORACLE RAC, if yes, you need some specific format for jdbc_url
8-If none of the above helps, try to reduce database connection idle time to release database connections faster by setting parameter MAX_DB_CONNECTION_IDLE_TIME=10 in server.conf file, then run kUpdateHtml.sh and restart PPM service. After applying this change, monitor the results from the database pool debug…that we set from logging.conf
Oscar Pereira
1) Start PPM server
2) To observe life thread turn on the following logging.conf
com.kintana.core.logging.PRODUCT_FUNCTION_LOGGING_LEVEL = com.kintana.core.database.pool,INFO
com.kintana.core.logging.SYSTEM_THRESHOLD = DEBUG
SYSTEM_THRESHOLD was already set to DEBUG
I added
com.kintana.core.logging.PRODUCT_FUNCTION_LOGGING_LEVEL = com.kintana.core.database.pool,INFO
Results:
STATUS server:Thread-1:SystemOut:2012/08/16-06:38:42.639 EDT: Logging reconfiguration complete
ERROR server:pool-299-thread-2:com.kintana.core.server:2012/08/16-06:49:44.859 EDT: An unknown error has occurred.
For more information, please contact your PPM administrator and provide the following GUID number:
GUID=6DBF1196-EA0F-953E-4677-B530790141D6 nested detail:Io exception: Socket read timed out
An unknown error has occurred. For more information, please contact your PPM administrator and provide the following GUID number:GUID=6DBF1196-EA0F-953E-4677-B530790141D6
at com.kintana.core.server.EntitySecurityAgent.loadEntitySecurity(EntitySecurityAgent.java:142)
at com.kintana.core.server.EntitySecurityAgent.loadEntitySecurity(EntitySecurityAgent.java:226)
at com.kintana.dsh.server.DataSourceDBAdapterImpl.load(DataSourceDBAdapterImpl.java:119)
at com.kintana.dsh.server.DataSourceDBAdapterImpl.load(DataSourceDBAdapterImpl.java:82)
at com.kintana.core.arch.DBAdapter.load(DBAdapter.java:76)
at com.kintana.core.cache.Cache.get(Cache.java:470)
at com.kintana.core.arch.DBAdapter.loadWrapper(DBAdapter.java:101)
at com.kintana.core.arch.Home.load(Home.java:93)
at com.kintana.dsh.server.ITGDashboardDataProvider.getPortletDataSet(ITGDashboardDataProvider.java:2164)
at com.mercury.dashboard.arch.host.ClassLoaderSafeProviderWrapper.getPortletDataSet(ClassLoaderSafeProviderWrapper.java:100)
at com.mercury.dashboard.arch.provider.util.ProviderCacheWrapper.getPortletDataSet(ProviderCacheWrapper.java:61)
at com.mercury.dashboard.app.portlet.BuilderPortletController.getPortletDataSet(BuilderPortletController.java:383)
at com.mercury.dashboard.app.portlet.BuilderPortlet$PrefetchThread.run(BuilderPortlet.java:79)
Caused by: java.sql.SQLRecoverableException: Io exception: Socket read timed out
at com.kintana.core.db.pool.PooledPreparedStatement.executeQuery(PooledPreparedStatement.java:148)
at com.kintana.core.server.EntitySecurityAgent.loadEntitySecurity(EntitySecurityAgent.java:130)
... 12 more
Filtered Out 23 Stack Trace Lines.
To me it looks the same as before. The logging reconfiguration did not seem to add any value.
I could not even tell why the log file reported this error. I logged in, and ran some shared portlets. (did not see failures).
1. Ensure that SQL plus can connect to database server from PPM box while the error occurs and wait for 5-10 minutes to try executing a query without getting any error.
This will help you to rule out if this is a SQL/JDBC setting in the server.
This is a very intermittent error. If the error is seen in the Default Dashboard (2 portlets - 1 portlet gets an error), and you select the Default Dashboard link or hit F5, you are fine (both portlets are good).
Backgound node log reports the error every once in a while.
2. We need your DBA to monitor the oracle logs when this issue occurs,
and search for any Oracle error. Look at oracle DB server log to trace any timeout on DB side.
I will try to get my DBA engaged.
3-Are these two portlets or one of them custom? Do you get this issue with out of the box portlets?
Both are custom. We still had this issue when we were using OOTB portlets.
4-What is the oracle database version and oracle client version? It would be great to have matching versions.
Oracle Database version xxxxxxx (DBA needs to get engaged)
Oracle Client version 11.2.0.1
5- Make sure that the JDBC_URL parameter is in the following format and that the values are all correct:
com.kintana.core.server.JDBC_URL=jdbc:oracle:thin:?@hostname:port:SID
Both nodes have the same value. If this was wrong, Would'nt this be a hard failure?
6- Verify that the port for the database in tnsnames.ora in the PPM server is same port as defined in server.conf for jdbc_url parameter.
They are the same.
7-Are you using ORACLE RAC, if yes, you need some specific format for jdbc_url
I do not think so. (DBA needs to be engaged)
8-If none of the above helps, try to reduce database connection idle time to release database connections faster by setting parameter MAX_DB_CONNECTION_IDLE_TIME=10 in server.conf file, then run kUpdateHtml.sh and restart PPM service. After applying this change, monitor the results from the database pool debug…that we set from logging.conf
(Both nodes have the same values-below, I will try this last)
com.kintana.core.server.MAX_DB_CONNECTION_IDLE_TIME=60
com.kintana.core.server.MAX_DB_CONNECTION_LIFE_TIME=1440
com.kintana.core.server.MAX_DB_CONNECTION_WAIT_TIME=180
com.kintana.core.server.MAX_DB_CONNECTIONs=60
For the Socket Read TO above. I was the only user on the system at the time.
PPM should not have been starved for a connection, should it ?
Continued to adjust my logging file and getting better results:
At startup I do get:
ERROR: Failed to extract DB connection string from JDBC URL (both log files).
I see what is wrong with JDBC_URL in server.conf.
Also,
All socket read time out errors seem to be for pool-xxx-thread-2
On Monday I will adjust JDBC_URL in server.conf and see what happens....
Hi,
If you using some ppm functions need to connect database with SQL*PLUS, there might cause some problems. There is a workaround, config the parameter "com.kintana.core.server.DB_CONNECTION_STRING" in server.conf, using the format as "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhostid)(PORT=dbport)))(CONNECT_DATA=(SERVICE_NAME=dbservicename)))"
dbhostid, dbport, dbservicename need to be modified.
You can refer to doc "Installation and Administration Guide" the description of parameter "DB_CONNECTION_STRING" page 102 and page 391
DB_CONNECTION_STRING
description:
If the JDBC_URL parameter is specified, then the security identifier (SID) of the database on which the PPM Center schema resides is requested. It is assumed that the connect string for this database is the same as the SID. However, this is not always the case. If the connect string (for connecting to the database using SQL*Plus from the server machine) is different than the database SID, add this parameter and supply the correct connect string.
**DB_CONNECTION_STRING(Required if RAC is used)
description:
Oracle RAC (Real Application Clusters) service name.
Regards,
Dayana Campos
My first question, is because there is a defect for:
QCCR1L47387: Using DB Service Names instead of SID
HOW-TO-REPRODUCE:
1. set the property 'com.kintana.core.server.JDBC_URL' with service name, using format jdbc:oracle:thin:@//[HOST][:PORT]/SERVICE
2. set the same property in local-server.conf
3. start ppm server
4. there will be an error message in serverLog.text
Failed to extract DB connection string from JDBC URL ...
This error occurs when creating default DB_CONNECTION_STRING from jdbc_url, if the property DB_CONNECTION_STRING is not used, there won't cause any other problems.
After changing JDBC_URL back to the old form (i.e. host:PORT:SID), the error should be gone.
Remember with all of this I am trying to track down "Socket Read timed out".
Most of the time my PPM System runs fine.
I seem to get these even without a load on the PPM System and a healthy network.
Also, after adjusting my log files for com.kintana.core.database.pool:
ERROR server: pool-x-thread-2 Io exception: Socket read timed out
All socket read timeouts always occur on thread-2.
I am wondering if only my first logical connection is working.
The "connection pool" is like a black box to me.
I think of it as a pool of logical connections over physical connections.
From the PPM configuration I can only identify 1 physical connection. JDBC_URL.
The 1 physical connection confuses me.
Do all of my logical connections multiplex over the 1 physical connection ?
DBA is hard to engage, but I am still trying.
Back to the JDBC_URL:
I think I am chasing my tail.
An error is thrown during PPM Server startup: (But all in all my PPM Sytem runs fine)
ERROR :main:com.kintana.core.server:2012/08/17-05:59:59.681 EDT: Failed to extract DB connection string from JDBC URL
Original server.conf:
com.kintana.core.server.DB_CONNECTION_STRING=xxxxx
com.kintana.core.server.JDBC_URL=jdbc:oracle:thin:@//IPaddress:1521/xxxxx.yyyy.zz
It looks like I am using DB Service Names instead of SID.
Changes to JDBC_URL produce hard failures.
My original JDBC_URL tosses an error but the PPM System works.
Also, you can tell if changes to JDBC_URL will work when you run kupdatehtml.sh (it must try to test the connection):
Updating JBoss configuration files
Generating configuration files for all deployed ITG servers
Generating configuration files for background_services_node
java.sql.SQLException: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
The Connection descriptor used by the client was:
//IPaddress:1521/xxxxx.yyyy
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:460)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:411)
at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:490)
at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:202)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:33)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:474)
at java.sql.DriverManager.getConnection(DriverManager.java:582)
at java.sql.DriverManager.getConnection(DriverManager.java:185)
at com.kintana.core.util.DBUtils.createJdbcConnection(DBUtils.java:859)
Thus, my server.conf is normalized and I am living with the Server startup error for DB connection string.
Looking else where for my "Socket read timed out".
Hi,
it looks to me like your JDBC URL should be properly configured. The format of the parameter is as follows:
com.kintana.core.server.JDBC_URL=jdbc:oracle:thin:@DBSERVER_NAME_OR_IP:LISTENER_PORT:SERVICE_NAME
Keep into account that the field separator is semi-colon (:). I think you should follow these steps:
1. shut down PPM
2. correct server.conf as specified above.
3. run kUpdateHtml.sh - should not throw any error if everything is allright
4. start PPM and check the server log - there should not be any error.
Regarding logical-physical DB connections: PPM creates a specific number of separate connections to the DB, based on the PPM system usage (number of connections may increase while background services are running). The server.conf entry is only defining the connection to the DB instance and it is not related to a single connection in particular.
I am really curious of how your PPM is able to start up and run without a proper JDBC connection string.
If you need more assistance, drop me a PM and we can take it from there.
cheers
alex
Alex,
I am currently using
com.kintana.core.server.JDBC_URL=jdbc:oracle:thin:@//IPaddress:1521/xxxxx.yyyy.zz
You are saying to use:
com.kintana.core.server.JDBC_URL=jdbc:oracle:thin:@DBSERVER_NAME_OR_IP:LISTENER_PORT:SERVICE_NAME
This would make me look like:
com.kintana.core.server.JDBC_URL=jdbc:oracle:thin:@//IPaddress:1521:xxxxx.yyyy.zz
I have tried this and it produces a hard failure - Nothing works
Hi,
Do not use the "//", if you use the DB SID you should not need any / in the JDBC connection string. Second, PPM does some crazy stuff for some "illegal" characters in the configuration files.
Second, when you are using "
xxxxx.yyyy.zz
" - is this the global DB name or SID (which is a single word, most DBAs set it the same as service name) or the tnsnames entry?
The best way is to use only the service name. You should navigate to your tnsnames.ora and find the entry which is similar to the below:
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
(CONNECT_DATA =
(SID = MYDBSID)
)
then, your parameter should be
com.kintana.core.server.JDBC_URL=jdbc:oracle:thin:
@myhost:1521:mydbsid.
Do let us know how it goes.
cheers
alex
The opinions expressed above are the personal opinions of the authors, not of OpenText. By using this site, you accept the Terms of Use. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.), Hewlett Packard Enterprise Company, or Micro Focus. As of January 31, 2023, the Material is now offered by OpenText, a separately owned and operated company. Any reference to the HP, Hewlett Packard Enterprise/HPE, and Micro Focus marks is historical in nature and the HP, Hewlett Packard Enterprise/HPE, and Micro Focus marks are the property of their respective owners.
