Solved

AQ java errors in recently upgraded 10g database. please help!

Posted on 2007-12-05
8
1,856 Views
Last Modified: 2013-12-10
The code below is being executed in the following environments-
JDK Version - 1.4.2_05
Oracle JMS API-
Sun Solaris 5.9
Oracle 10.2.0.3



           QueueConnectionFactory qcfact = AQjmsFactory.
                getQueueConnectionFactory(System.getProperty("ORAURL"), null);
            qconn = qcfact.createQueueConnection(System.getProperty("ORAUSER"),
                        System.getProperty("ORAPW"));
            QueueSession qsession = qconn.createQueueSession(true,
                Session.CLIENT_ACKNOWLEDGE);
                         
            String queueName = getQueueNameWithoutSchema();
           
            Queue queue = ( (AQjmsSession) qsession).getQueue(System.getProperty("DBOWNER"), queueName);            
           
           
            QueueReceiver qrecv = qsession.createReceiver(queue);
            qconn.start();

            logger.info("Connected to Queue - " + queue.toString());
            while (true) {
              logger.debug("Waiting for " + pc.getPartnerName() + " message");
              TextMessage msg = (TextMessage) qrecv.receive();
            }


these errors are coming in from the application server side, i am not seeing anything in the ALERT log.
Linked-exception]
java.sql.SQLException: ORA-00018: maximum number of sessions exceeded
ORA-06512: at "SYS.DBMS_AQIN", line 263
ORA-06512: at line 1

        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
        at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
        at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:582)
        at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1986)
        at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1144)
        at  oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2152)
        at  oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2035)
        at  oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2876)
        at  oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java :609)
        at  oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java: 537)
        at oracle.jms.AQjmsConsumer.dequeue(AQjmsConsumer.java:1600)
        at oracle.jms.AQjmsConsumer.receiveFromAQ(AQjmsConsumer.java:1013)
        at oracle.jms.AQjmsConsumer.receiveFromAQ(AQjmsConsumer.java:950)
        at oracle.jms.AQjmsConsumer.receive(AQjmsConsumer.java:866)
        at  com.tmobile.wip.partpub.client.PublisherClient.run(PublisherClient.java:89)


[Linked-exception]
java.sql.SQLException: ORA-04030: out of process memory when trying to allocate 40984  bytes (koh-kghu call ,pefduralc)
ORA-06512: at "SYS.DBMS_AQIN", line 651
ORA-06512: at line 1

        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
        at  oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:215)
        at  oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:965)
        at  oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1170)
        at  oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.ja va:3339)
        at  oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java: 3384)
        at oracle.jms.AQjmsConsumer.dequeue(AQjmsConsumer.java:1633)
        at oracle.jms.AQjmsConsumer.receiveFromAQ(AQjmsConsumer.java:1021)
        at oracle.jms.AQjmsConsumer.receiveFromAQ(AQjmsConsumer.java:951)
        at oracle.jms.AQjmsConsumer.receiveFromAQ(AQjmsConsumer.java:929)
        at oracle.jms.AQjmsConsumer.receive(AQjmsConsumer.java:750)
        at  com.tmobile.wip.partpub.client.PublisherClient.run(PublisherClient.java:89)



[Linked-exception]
java.sql.SQLException: ORA-00600: internal error code, arguments:  [kwqidorefetch:err], [604], [ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable to allocate 1048 bytes of shared memory ("shared pool","select  user#,type# from user...","Typecheck","seg:kggfaAllocSeg")
], [], [], [], [], []
ORA-06512: at "SYS.DBMS_AQIN", line 571
ORA-06512: at line 1

        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
        at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
        at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:582)
        at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1986)
        at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1144)
        at  oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2152)
        at  oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2035)
        at  oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2876)
        at  oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java :609)
        at  oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java: 537)
        at oracle.jms.AQjmsConsumer.dequeue(AQjmsConsumer.java:1600)
        at oracle.jms.AQjmsConsumer.receiveFromAQ(AQjmsConsumer.java:1013)
        at oracle.jms.AQjmsConsumer.receiveFromAQ(AQjmsConsumer.java:950)
        at oracle.jms.AQjmsConsumer.receive(AQjmsConsumer.java:866)
        at  com.tmobile.wip.partpub.client.PublisherClient.run(PublisherClient.java:89)


thanks in advance,
daniels
0
Comment
Question by:Daniel Stanley
  • 4
  • 4
8 Comments
 
LVL 20

Expert Comment

by:gatorvip
ID: 20414610
>>ORA-00018: maximum number of sessions exceeded

Check your SESSIONS parameter setting

0
 
LVL 20

Expert Comment

by:gatorvip
ID: 20414626
You're not trying to open the sessions in a loop by any chance, are you?
0
 
LVL 7

Author Comment

by:Daniel Stanley
ID: 20414636
sessions=2225

select count(*) from v$session;

460
0
 
LVL 7

Author Comment

by:Daniel Stanley
ID: 20415050
and yes, the sessions are being opened inside a loop.  is that problem?

daniels
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 20

Expert Comment

by:gatorvip
ID: 20415195
yes, if you're not closing them accordingly. Either increase the # of sessions allowed, or close the sessions when you're done with them.
0
 
LVL 7

Author Comment

by:Daniel Stanley
ID: 20415838
we are not getting ANY errors on the database side; so there is no need to increase the number of connections there.  this problem is localized to the client application server where the java application lives
0
 
LVL 20

Accepted Solution

by:
gatorvip earned 500 total points
ID: 20419202
From the code snippet you posted (which is actually incomplete), it looks to me like you are opening more sessions on the application side than the server allows on the database side. Unless you do it elsewhere, I don't see where you actually end the sessions.

As such, I see two options:
1) Increase the number of sessions allowed on the database
2) Manage your active sessions better on the application side (i.e., close them as soon as you can).

Personally, I think you should go with option 2.
0
 
LVL 7

Author Comment

by:Daniel Stanley
ID: 20423246
pretty sure this is the solution, we've got the session param set already at the instance level but we have a unix parameter for allowable processes that needs to be throttled up to unlimited.   pretty sure that will fix the issue.   thanks for confirming my suspicions..  i'll wait one week to make sure the unix fix works, then award points.

thanks a million for the input, i love this place.

daniels
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now