CODEWITHSUNDEEP
grailsgrails-orm
suman j
suman j

Reputation: 6960

grails oracle connection timeout

I have below datasource config in grails.

dataSource_staging_oracle {
    dbCreate = "none"
    pooled = true
    url = "jdbc:oracle:thin:@//my-box-oracle.com:1521/DB1"
    driverClassName = "oracle.jdbc.OracleDriver"
    username = "USER_1"
    password = "encryptedpassword"
    passwordEncryptionCodec = PropertiesCodec
}
dataSource_prod_oracle {
    dbCreate = "none"
    pooled = true
    autoReconnect = true
    url = "jdbc:oracle:thin:@//my-oracle-prod-box.com:1521/DB2"
    driverClassName = "oracle.jdbc.OracleDriver"
    username = "user_2"
    password = "encrypted_password"
    passwordEncryptionCodec = PropertiesCodec
}

Application is able to fetch the DB connection and work fine. However after 1-2 hours of processing, I see connection closed exceptions.

 ERROR util.JDBCExceptionReporter  - Closed Connection

I believe this has to do with datasource config properties. What grails settings help me in refreshing the connections? I already have set autoReconnect and pooled to true

I use grails 2.3.3

Upvotes: 1

Views: 1609

Answers (2)

suman j
suman j

Reputation: 6960

Below config helped me fix my problem.

dataSource_staging {
            dbCreate = "none"
            pooled = true
            autoReconnect = true
            url = "jdbc:oracle:thin:@//my-box-oracle.com:1521/DB1"
            driverClassName = "oracle.jdbc.OracleDriver"
            username = "USER_1"
            password = "encryptedpassword"
            passwordEncryptionCodec = PropertiesCodec
            properties {
                validationQuery = "SELECT 1 FROM DUAL"
                testOnBorrow = true
                testWhileIdle = true
                testOnReturn = false
                timeBetweenEvictionRunsMillis = 5000
                minEvictableIdleTimeMillis = 60000
                maxAge = 10 * 60000
                maxWait = 10000
                maxIdle = 25
                maxActive = 50
                initialSize = 5
            }
        }

Upvotes: 0

Nathan
Nathan

Reputation: 3200

Datasource properties will inform the database what the middleware server will expect from the Database. If your Oracle server does not agree with the settings Grails would like to use, expect sometimes cryptic error messages.

Using pooled connections tells Oracle to hold a number of connections open for the application. If the application is running 1-2 hours and continuously processing, I expect the pool may be running out of valid connections. For some background please view the links included below.

Check out the Grails configuration docs, section 4.4, to see advanced datasource configuration options. Do you have a validation query defined? (eg select 1 from dual;)

Check out this AskTom post for a clear explanation of connection pooling.

Previous StackOverflow question with MySQL but similar error.

Upvotes: 1

Related Questions