6 Replies Latest reply on May 15, 2013 4:20 PM by APC

    ORA-03127: no new operations allowed until the active operation ends

    bdzevel
      Hello,

      I'm a software developer, not an Oracle DBA (not even close), so please excuse the lack of know-how.

      Our product runs a lot of stored procedures in the background to do various things. These stored procedures obviously include a ton of select statements, insert statements, etc. Some of them get pretty complex. Once in a while, we run across the following error: "ORA-03127: no new operations allowed until the active operation ends(3127)." Once this happens, pretty much everything breaks with this error for a while. Eventually (LOOOONG time), this error "resolves itself" and things start working again. Conceptually, I understand that there seems to be some blocking operation on the DB, but because we run a LOT of stored procedures and SQL statements, it's extremely difficult to pin this down.

      I realize this is a very broad question, but is there any good way to debug this issue? I'll be glad to provide additional information as you see fit. At this point I'm not sure what relevant details I could provide, but if you ask, I will try to accommodate.

      Thanks.
        • 1. Re: ORA-03127: no new operations allowed until the active operation ends
          Tubby
          bdzevel wrote:
          Hello,

          I'm a software developer, not an Oracle DBA (not even close), so please excuse the lack of know-how.

          Our product runs a lot of stored procedures in the background to do various things. These stored procedures obviously include a ton of select statements, insert statements, etc. Some of them get pretty complex. Once in a while, we run across the following error: "ORA-03127: no new operations allowed until the active operation ends(3127)." Once this happens, pretty much everything breaks with this error for a while. Eventually (LOOOONG time), this error "resolves itself" and things start working again. Conceptually, I understand that there seems to be some blocking operation on the DB, but because we run a LOT of stored procedures and SQL statements, it's extremely difficult to pin this down.

          I realize this is a very broad question, but is there any good way to debug this issue? I'll be glad to provide additional information as you see fit. At this point I'm not sure what relevant details I could provide, but if you ask, I will try to accommodate.

          Thanks.
          Error ORA - 03127

          See Billy's reply in the above thread as to the why.

          As for how to go about fixing this, you would need to find the code that isn't waiting for a response before shooting another request off.

          Cheers,
          • 2. Re: ORA-03127: no new operations allowed until the active operation ends
            Hoek
            Start with removing all WHEN OTHERS - exception handlers, if any...
            At this point I'm not sure what relevant details I could provide, but if you ask, I will try to accommodate.
            It is explained here, mentioning at least a database version is of great importance, as well as a (narrowed down) example of the code you're executing:
            {message:id=9360002}
            I understand that there seems to be some blocking operation on the DB
            ORA-03127: no new operations allowed until the active operation ends
            Cause: An attempt was made to execute a new operation before the active non-blocking operation completed or a new operation was attempted before all the pieces of a column were inserted or fetched.
            Action: Execute the new operation after the non-blocking operation completes. If piecewise binds/defines were done, execute the new operation after all the pieces have been inserted or fetched.
            • 3. Re: ORA-03127: no new operations allowed until the active operation ends
              APC
              bdzevel wrote:
              Hello,

              I'm a software developer, not an Oracle DBA (not even close),
              That's bad luck - not generally, just with regards to this issue, because this is a generic issue with a variety of possible causes:

              #1 - incompatible versions of the client and the database. The database is supposed to have backwards support for older versions of the client, but Googling reveals that people with much older Oracle clients (two major releases) have expereinced this problem, and solved it by upgrading the client to a more recent version.

              #2 - database with a unicode characterset and clients which are configured to use some other characterset which doesn't support unicode.

              #3 - BLOB or CLOB operations, perhaps combined with one of the previous two issues.

              #4 - and there's the issue Billy discussed in that other thread regarding OCI calls.

              With regards to the OCI calls then that's an architectural issue with your product, and you should be able to figure out whether it applies in your case. Otherwise, I'm afraid you've going to have to do some debugging to establish which procedure causes this problem. You say you have a lot of stored procedures. I hope they're properly instrumented to produce solid trace and debug messages.

              Cheers, APC
              • 4. Re: ORA-03127: no new operations allowed until the active operation ends
                bdzevel
                @Hoek: Well, unfortunately, I'm not sure what operation is blocking future calls. Like I said, we make tons of calls on the database, and at some point it "just" starts failing with this message.

                EDIT: However, there is a set of stored procedures that fail just before these errors pop up. I've checked previous logs and it seems that these particular stored procedures always fail just before the "no more operations allowed" errors start coming in. I'm looking into these procs to see if I see anything nasty in there.

                I didn't write these procs - and they're quite long - so I'm not sure that I can post a condensed snippet of code just yet. I'll see what I can do.

                Some information I know nothing about that I'm pulling from our GUI:
                DB version: 11.2.0.1.0
                Client version: 11.2.0.3.0
                Provider: OraOLEDB11.dll
                OLE DB Version: 02.01

                Edited by: bdzevel on May 15, 2013 8:55 AM
                • 5. Re: ORA-03127: no new operations allowed until the active operation ends
                  bdzevel
                  P.S.> We make calls to these stored procedures using ADO, which calls OLEDB, which then makes OCI calls. So there are a few layers of separation between us and the underlying native API.
                  • 6. Re: ORA-03127: no new operations allowed until the active operation ends
                    APC
                    bdzevel wrote:
                    I didn't write these procs - and they're quite long - so I'm not sure that I can post a condensed snippet of code just yet. I'll see what I can do.
                    I wouldn't worry about that too much: it's unlikely that anybody here will be able to cast and expert eye over your code and then say "Aha! there's the badger!" To be honest you may already have got the best from us with those steers.

                    However, ADO is not an esoteric technology and it seems to work quite well for most people, so it's likely to be an implemnation detail which is tripping you up. In which case you would be better off talking with ADO experts who understand the architecture better. If that seems like a good idea you should take yourself over to one of [url https://forums.oracle.com/forums/category.jspa?categoryID=44]OTN's .Net forums.

                    Cheers, APC