1 2 Previous Next 17 Replies Latest reply on Feb 8, 2018 6:00 PM by rp0428

    ora-00600 with union all

    netikras

      Hi,

       

      so today I've whitnessed something new. After doing some pen-testing on the application I found the database damaged in some way.

       

      Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

       

      after this error pops up JDBC connection becomes broken and cannot be reused. Connection does not break if I'm running this via sqlplus.

      This happens ONLY when (there is union all in the query) && (there are 2 or more columns) && (there is a count(*)) && (there is order by clause). Skip either of the components and the query executes just fine. Happens with any view/table I have tried.

       

      Noticed this first on a view using union all in its query as java app started spitting out errors suggesting DB connection has been closed prematurely.

       

      Does that mean there's no way out but a fresh database?

       

       

      ---

      Some food for google's cybermonkeys to fiest on:

       

       

      select count(*) from (

        select

          0 as id1,

          1 as id2

        from dual

      union all

        select

          0 as id1,

          1 as id2

        from dual

        order by id1 desc

      )

      ;

       

      ORA-00600: internal error code, arguments: [qctcte1], [0], [], [], [], [], [], [], [], [], [], []

      00600. 00000 -  "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"

      *Cause:    This is the generic internal error number for Oracle program

                 exceptions. It indicates that a process has encountered a low-level,

                 unexpected condition. The first argument is the internal message

                 number. This argument and the database version number are critical in

                 identifying the root cause and the potential impact to your system.

        • 1. Re: ora-00600 with union all
          Joerg.Sobottka

          Hm, maybe you should patch your database to 11.2.0.4 - you have a real old version.

          Nevertheless, have a look at the alert.log, there you will find more information about the ora-0600 (e.g. trace files). With the Information there you can have a look at: ORA-600/ORA-7445/ORA-700 Error Look-up Tool (Doc ID 153788.1)

           

           

          • 2. Re: ora-00600 with union all
            Mark D Powell

            netikras, if you have access to Oracle support or someone from your site does the following support document should be of interest.  There are a couple of bugs that may be of interest.
            - -
            ORA-600 [qctcte1] (Doc ID 248095.1)
            - -
            HTH -- Mark D Powell --

            PS - I would remove the order by from the subquery.  Being you are getting a count why would it even matter to the result?

            • 3. Re: ora-00600 with union all
              netikras

              Heyy, thanks for a lightning-fast reply!

               

              However... I don't think anyone is interested in upgrading the database version for my project It's quite an old project and its managers are... well... let's say "not very invested into project's tech side" So I just gotta keep the show on the road with whatever I have.

               

              The alert.log file does not have a lot to say. And the trc files is something I don't think I'll live long enough to understand The link you gave requires an account eligible for ORA support (which I don't have.. hence I'm here, in a community forum)

               

              [root@ora ~]# tail /u01/app/oracle/diag/rdbms/project1/project1/trace/alert_project1.log

              Trace dumping is performing id=[cdmp_20180207162723]

              Wed Feb 07 16:27:47 2018

              Errors in file /u01/app/oracle/diag/rdbms/project1/project1/trace/project1_ora_27543.trc  (incident=11303):

              ORA-00600: internal error code, arguments: [qctcte1], [0], [], [], [], [], [], [], [], [], [], []

              Incident details in: /u01/app/oracle/diag/rdbms/project1/project1/incident/incdir_11303/project1_ora_27543_i11303.trc

              Wed Feb 07 16:27:48 2018

              Sweep [inc][11303]: completed

              Sweep [inc2][11303]: completed

              Wed Feb 07 16:27:49 2018

              Trace dumping is performing id=[cdmp_20180207162749]

               

              [root@ora ~]# ll /u01/app/oracle/diag/rdbms/project1/project1/incident/incdir_11303/project1_ora_27543_i11303.trc

              -rw-r----- 1 oracle dba 3070656 Feb  7 16:27 /u01/app/oracle/diag/rdbms/project1/project1/incident/incdir_11303/project1_ora_27543_i11303.trc

              [root@ora ~]# less /u01/app/oracle/diag/rdbms/project1/project1/incident/incdir_11303/project1_ora_27543_i11303.trc

              [root@ora ~]# du -sh /u01/app/oracle/diag/rdbms/project1/project1/incident/incdir_11303/project1_ora_27543_i11303.trc

              3.0M    /u01/app/oracle/diag/rdbms/project1/project1/incident/incdir_11303/project1_ora_27543_i11303.trc

               

              [root@ora ~]# wc -l /u01/app/oracle/diag/rdbms/project1/project1/incident/incdir_11303/project1_ora_27543_i11303.trc

              57784 /u01/app/oracle/diag/rdbms/project1/project1/incident/incdir_11303/project1_ora_27543_i11303.trc

               

              [root@ora ~]#

              • 4. Re: ora-00600 with union all
                netikras

                Nope, I have no access to ORA support :/ And regd the count...

                 

                select count(*) from my_awesome_view;

                 

                and the view uses the order by.

                • 5. Re: ora-00600 with union all
                  Joerg.Sobottka

                  There is a bug with union all statements Bug 9826065  ORA-600 [qctcte1] from UNION ALL with identical expressions in select list).

                  It is already fixed, but as you don't want/can't upgrade the database you can try to use the workaround.

                   Set "_optimizer_cost_based_transformation"=off or Set _fix_control='7597159:off'
                  1 person found this helpful
                  • 6. Re: ora-00600 with union all
                    John Thorton

                    netikras wrote:

                     

                    Nope, I have no access to ORA support :/ And regd the count...

                     

                    select count(*) from my_awesome_view;

                     

                    and the view uses the order by.

                    SELECT COUNT(*) returns a single scalar value so there is NOTHING to ORDER BY!

                     

                    What is wrong order for single row results?

                    • 7. Re: ora-00600 with union all
                      netikras

                      I know there is nothing to order in scalar. But there is smth to order in a view. And I'm counting rows appearing on the view.

                       

                      • 8. Re: ora-00600 with union all
                        netikras

                        So the first setting did not help me at all. The second one however seems to fix whatever the issue is this (_fix_control='7597159:off'). Is it safe to use? What am I losing by disabling this 7597159 (fix?) ?

                        • 9. Re: ora-00600 with union all
                          Mark D Powell

                          netikras, you are going to lose a specific query transformation by the CBO that may result in some other queries degrading.  If any other queries degrade or not can likely only be determined by heavy testing.  If possible, you should check to see if this parameter can be set at the session level then set it only in those cases where the error is encountered.

                          - -

                          Try comparing the before and after (no fix, fix applied) plans for the SQL to see if you can spot the transformation.

                          - -

                          HTH -- Mark D Powell --

                          • 10. Re: ora-00600 with union all
                            SeánMacGC

                            Does this also error (aliasing the columns in anything but the first UNION (ALL) block is unusual, and not useful at all):

                             

                            select count(*) from (

                              select

                                0 as id1,

                                1 as id2

                              from dual

                            union all

                              select

                                0,

                                1

                              from dual

                            );

                            • 11. Re: ora-00600 with union all
                              netikras

                              What the hell....? It worked. It really worked! Why did it work? And what does that mean? And what's *wrong* with aliasing columns in union block?

                              • 12. Re: ora-00600 with union all
                                AndrewSayer

                                netikras wrote:

                                 

                                What the hell....? It worked. It really worked! Why did it work? And what does that mean? And what's *wrong* with aliasing columns in union block?

                                It doesn’t have an order by clause.

                                 

                                I find it confusing that you are pen testing an out of support version, there is going to be bugs and there is going to be security holes.

                                 

                                If you have little control over patching, then just make sure your application doesn’t call SQL like you’ve shared, that should be easy as it will fail testing anyway

                                • 13. Re: ora-00600 with union all
                                  netikras

                                  Oh, right.... Thanks. I'm still working on my morning coffee so I might overlook obvious things And the qry does fail with order by clause.

                                   

                                  interestingly enough the union all thing was NOT failing before pen-testing the app. I guess I'll have to remodel the view anyway

                                  • 14. Re: ora-00600 with union all
                                    Joerg.Sobottka

                                    netikras wrote:

                                     

                                    What the hell....? It worked. It really worked! Why did it work? And what does that mean? And what's *wrong* with aliasing columns in union block?

                                    This is, why it is called a bug and fixed at a later Oracle database version ;-)

                                    Bugs are NOT necessarily logical.

                                    Please mark your question as answered, if you don't need any more help.

                                    1 2 Previous Next