4 Replies Latest reply: Jul 13, 2010 8:54 PM by baskar.l RSS

    ORA-00604: error occurred at recursive SQL level 1

    cptkirkh
      This morning my DB quit accepting logins. After bouncing it everything was fine but i would like to n know why. The alert log listed the following:

      Errors in file f:\oracle\product\10.2.0\admin\porky\bdump\porky_smon_4036.trc:
      ORA-00604: error occurred at recursive SQL level 1
      ORA-00018: maximum number of sessions exceeded

      So i went to that file and this was just the beginnign of a large file:
      Dump file f:\oracle\product\10.2.0\admin\porky\bdump\porky_smon_4036.trc
      Fri Jul 09 08:51:43 2010
      ORACLE V10.2.0.3.0 - Production vsnsta=0
      vsnsql=14 vsnxtr=3
      Oracle Database 10g Release 10.2.0.3.0 - Production
      Windows Server 2003 Version V5.2 Service Pack 2
      CPU : 4 - type 586, 2 Physical Cores
      Process Affinity : 0x00000000
      Memory (Avail/Total): Ph:874M/3967M, Ph+PgF:2872M/5863M, VA:301M/3031M
      Instance name: porky

      Redo thread mounted by this instance: 1

      Oracle process number: 8

      Windows thread id: 4036, image: ORACLE.EXE (SMON)


      *** SERVICE NAME:(SYS$BACKGROUND) 2010-07-09 08:51:43.348
      *** SESSION ID:(164.1) 2010-07-09 08:51:43.348
      *** 2010-07-09 08:51:43.348
      SMON: following errors trapped and ignored:
      ORA-00018: maximum number of sessions exceeded
      *** 2010-07-09 08:51:44.380
      SMON: following errors trapped and ignored:
      ORA-00604: error occurred at recursive SQL level 1
      ORA-00018: maximum number of sessions exceeded
      *** 2010-07-09 08:51:46.005
      SMON: following errors trapped and ignored:
      ORA-00604: error occurred at recursive SQL level 1
      ORA-00018: maximum number of sessions exceeded
      *** 2010-07-09 08:51:47.020
      SMON: following errors trapped and ignored:
      ORA-00604: error occurred at recursive SQL level 1
      ORA-00018: maximum number of sessions exceeded
      *** 2010-07-09 08:51:48.036
      SMON: following errors trapped and ignored:
      ORA-00604: error occurred at recursive SQL level 1
      ORA-00018: maximum number of sessions exceeded

      What is the best way to find out what was the issue? I know it was just the max number of sessions was reached but is there a way to find out why? Also for sessions in the parameter file i read that i should put 2.5*max_users. Is that how i should configure that number? I read that you should take 2.5* the max_utilization number found for sessions when you run 'select * from v$resource_limit' Is that correct? Thanks
        • 1. Re: ORA-00604: error occurred at recursive SQL level 1
          Lubiez Jean-Valentin
          Hello,

          ORA-00018: maximum number of sessions exceeded
          It seems that you reached the maximum of sessions allowed on the database.

          What is the result of this:
          show parameter processes
          May be you'll have to increase the processes parameter.

          Then, from time to time, you may monitore your sessions so as to see if some application is opening a lot of sessions on the database.

          Also, you may monitore the number of Oracle processes on the server.


          Hope this help.
          Best regards,
          Jean-Valentin
          • 2. Re: ORA-00604: error occurred at recursive SQL level 1
            cptkirkh
            Thanks. Is there anyway to know which process it was after the fact or will i need to enable the monitoring first?

            Edited by: user8602786 on Jul 13, 2010 6:34 PM

            here is the output of the show parameter processes:

            NAME TYPE VALUE
            ------------------------------------ ----------- -------------
            aq_tm_processes integer 0
            db_writer_processes integer 1
            gcs_server_processes integer 0
            job_queue_processes integer 10
            log_archive_max_processes integer 2
            processes integer 150

            Edited by: user8602786 on Jul 13, 2010 6:41 PM
            • 3. Re: ORA-00604: error occurred at recursive SQL level 1
              sb92075
              Is there anyway to know which process it was after the fact or will i need to enable the monitoring first?
              Problem is not any specific session, but the sum total of all sessions combined.
              • 4. Re: ORA-00604: error occurred at recursive SQL level 1
                baskar.l
                Hi,

                You can find the number of processes per username being used and can check why those username have more number of sessions. Some external application which get connected to the DB again and again may all be inactive. So you can tune those applications.

                thanks,
                baskar.l