13 Replies Latest reply on Jan 21, 2018 10:40 AM by Beauty_and_dBest

    ORA-01555

    Beauty_and_dBest

      EBS R12.1.3

      11gR1

      OL6

       

      Hi ALL,

       

      We have schedule  FNDCPPUR module Concurrent program to run daily, but it gets ORA-01555 error

      Capture.PNG

      +---------------------------------------------------------------------------+

      Application Object Library: Version : 12.0.0

      Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.

      FNDCPPUR module: Purge Concurrent Request and/or Manager Data

      +---------------------------------------------------------------------------+

      Current system time is 03-JAN-2018 12:15:06

      +---------------------------------------------------------------------------+

      APP-FND-01564: ORACLE error 1555 in request

       

      Cause: request failed due to ORA-01555: snapshot too old: rollback segment number 16 with name "_SYSSMU16_1232623801$" too small

       

       

      How do I resolve this please....

       

      I already set all data_files including APPS_UNDOTS1  autoextend on.

       

       

      SQL> show parameter undo

       

      NAME                                 TYPE        VALUE

      ------------------------------------ ----------- ------------------------------

      undo_management                      string      AUTO

      undo_retention                       integer     2000

      undo_tablespace                      string      APPS_UNDOTS1

       

       

      KInd regards,

      jc

        • 1. Re: ORA-01555
          mdtaylor

          Hi jc,

           

          In addition to increasing your undo_retention to maybe 7200, you should also consider doubling APPS_UNDOTS1 as you have to have enough space to hold the UNDO to avoid the ORA-0155 errors.

           

          See the System Managed Undo section of the R12 init parameters guide:

           

          Database Initialization Parameters for Oracle E-Business Suite Release 12 (Doc ID 396009.1)

           

          Regards,

          Michael

          • 2. Re: ORA-01555
            Beauty_and_dBest

            Thanks Michael,

             

            But it is already autoextensible why need to double it?

            Does this mean > ORA-01555: snapshot too old: rollback segment number 16 with name "_SYSSMU16_1232623801$" too small

            That rollback segment 16 can not autoextend along with its parent tablesapace?

             

            Kind regards,

            • 3. Re: ORA-01555
              mdtaylor

              Normally, I prefer to not have TEMP and APPS_UNDOTS1 on autoextend to prevent runaway processes from blowing up the database size.

               

              Also, there is a patch for this issue:

               

              FNDCPPUR Fails ORA-01555: snapshot too old: rollback segment too small (Doc ID 1913432.1)

               

              Regards,

              Michael

              • 4. Re: ORA-01555
                Beauty_and_dBest

                Thanks Michael,

                 

                I will check that.

                I just monitored it now and this purge I'm not aware is running for a long time now like 12 hours? Is this normal? or It  is  hanging?

                So maybe for the past sched, it was not able to complete and was duplicating its run  when it reached the next day sched?

                 

                 

                Kind regards,

                • 5. Re: ORA-01555
                  mdtaylor

                  Hi jc,

                   

                  That seems too long.  Please run:

                   

                  select sysdate - min(actual_start_date) from fnd_concurrent_requests;

                   

                  If it is 30 or 40, Run the purge process for 5 or 10 day less, then work back toward 15 days retention.  The note says break up the job into smaller chunks.

                   

                  Also consider applying the recommended patch from the note above.

                   

                  Regards,

                  Michael

                  • 6. Re: ORA-01555
                    Beauty_and_dBest

                    Thanks Michael,

                     

                    Sorry for delayed response, so many task to do  and meetings to attend today

                     

                    SQL> select count(*) from fnd_concurrent_requests;

                     

                      COUNT(*)

                    ----------

                       1176110

                     

                    SQL> select sysdate - min(actual_start_date) from fnd_concurrent_requests;

                     

                    SYSDATE-MIN(ACTUAL_START_DATE)

                    ------------------------------

                                        251.484016

                     

                    So it can not handle 235 days of logs if our purge parameter is AGE 15 days? 

                     

                    What if I will delete per 10 days , to be sure it will get finished?

                     

                    Is this the correct parameter?

                     

                    I will submit by  10 day AGE to purge as in  240, 230, 220, ....etc

                     

                    Capture2.JPG

                     

                    When I put 240 days AGE to purge, it got successful run with output below:

                    Purged 0 entrie(s) from FND_CONCURRENT_REQUESTS :-09-JAN-2018 18:37:19

                    Purged 0 entrie(s) from FND_FILE_TEMP :-09-JAN-2018 18:37:21

                    Purged 392645 entrie(s) from FND_CRM_HISTORY :-09-JAN-2018 18:38:16

                    Purged 0 entrie(s) from FND_TM_EVENTS :-09-JAN-2018 18:38:16

                    Purged 4580 entrie(s) from FND_TEMP_FILES :-09-JAN-2018 18:38:18

                    Purged 0 entrie(s) from FND_ENV_CONTEXT :-09-JAN-2018 18:42:38

                    Purged 12 entrie(s) from FND_DUAL :-09-JAN-2018 18:42:39

                    Purged 0 entrie(s) from FND_CONFLICTS_DOMAIN :-09-JAN-2018 18:42:39

                     

                    Why is  none deleted in FND_CONCURRENT_REQUESTS?

                     

                     

                     

                    Is it okay to run all the PURGE concurrent programs below to freeup space?

                     

                    Capture.JPG

                     

                     

                    Kind regards,

                    • 7. Re: ORA-01555
                      Beauty_and_dBest

                      Hi Michael,

                       

                      How do I count each range how many records can be deleted?

                       

                      I tried DAYS AGE 200, 180, 170, 160  and they are very small rows. But when I tried 150, it now hits ORA-01555.

                      This means a period of 10 days has so much data to purge? How to find these date range?

                       

                       

                      Kind regards,

                      • 8. Re: ORA-01555
                        mdtaylor

                        Maybe see what programs are running every few minutes and making many thousands or millions of rows in FND_CONCURRENT_REQUESTS:

                         

                        SELECT CP.CONCURRENT_PROGRAM_NAME "Program", count(*)

                        FROM APPLSYS.FND_CONCURRENT_REQUESTS CR,

                              APPLSYS.FND_USER U,

                              APPLSYS.FND_CONCURRENT_PROGRAMS CP,

                              APPLSYS.FND_RESPONSIBILITY_TL R

                        WHERE CR.REQUESTED_BY = U.USER_ID

                           AND CR.PROGRAM_APPLICATION_ID = CP.APPLICATION_ID

                           AND CR.CONCURRENT_PROGRAM_ID = CP.CONCURRENT_PROGRAM_ID

                           AND CR.RESPONSIBILITY_APPLICATION_ID = R.APPLICATION_ID

                           AND CR.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID 

                           AND R.LANGUAGE = 'US'

                        GROUP BY CP.CONCURRENT_PROGRAM_NAME

                        HAVING COUNT(*) > 2000

                        ORDER BY COUNT(*) DESC;

                         

                        You can run Purge Concurrent Request and/or Manager Data for All Entity and use Count as the mode.  Run it for the top most frequently run programs and only keep the last 200 rows.

                         

                        Then go back and try to reduce the age back down to 15 days.  Be sure to use All for the Entity to clean up concurrent manager logs as well, not just Requests.

                         

                        Regards,

                        Michael

                        • 9. Re: ORA-01555
                          Beauty_and_dBest

                          Thanks Michael,

                           

                           

                           

                          Program                      COUNT(*)

                          ------------------------------ ----------

                          WSHINTERFACES                  425715
                          INCTCM                         320785
                          FNDWFBG                        155417
                          WICTMS                         111963
                          WSCMTM                          42157
                          FNDOAMCOL                       20091
                          WSHRDPIK                        15611
                          WSHRDXCP                        15611
                          CMCACW                          14932
                          WSHINTERFACE                    13941
                          RCVDLPDT                         5663
                          RAXMTR                           4973
                          RAXTRX                           4918
                          RVCTP                            4460
                          POXPOPDF                         4204
                          F1DRSIMO                    
                          3029

                           

                          16 rows selected.

                           

                           

                          If I run Purge Concurrent Request and/or Manager Data for All Entity and use Count as the mode, It will delete latest one as well

                           

                           

                          Kind regards,

                          • 10. Re: ORA-01555
                            #MVivek

                            Hi Micheal,

                             

                            For me it is running for 30 days and retention is 90 days.

                             

                            Required details are given  below -

                             

                            SQL> select count(*) from fnd_concurrent_requests;

                             

                             

                              COUNT(*)

                            ----------

                                436738

                             

                            SQL> select sysdate - min(actual_start_date) from fnd_concurrent_requests;

                             

                             

                            SYSDATE-MIN(ACTUAL_START_DATE)

                            ------------------------------

                                                1782.82171

                             

                            SQL> SELECT CP.CONCURRENT_PROGRAM_NAME "Program", count(*)

                            FROM APPLSYS.FND_CONCURRENT_REQUESTS CR,

                              2    3        APPLSYS.FND_USER U,

                              4        APPLSYS.FND_CONCURRENT_PROGRAMS CP,

                              5        APPLSYS.FND_RESPONSIBILITY_TL R

                              6  WHERE CR.REQUESTED_BY = U.USER_ID

                              7     AND CR.PROGRAM_APPLICATION_ID = CP.APPLICATION_ID

                              8     AND CR.CONCURRENT_PROGRAM_ID = CP.CONCURRENT_PROGRAM_ID

                              9     AND CR.RESPONSIBILITY_APPLICATION_ID = R.APPLICATION_ID

                            10     AND CR.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID

                               AND R.LANGUAGE = 'US'

                            11   12  GROUP BY CP.CONCURRENT_PROGRAM_NAME

                            13  HAVING COUNT(*) > 2000

                            14  ORDER BY COUNT(*) DESC;

                             

                             

                            Program                          COUNT(*)

                            ------------------------------ ----------

                            XLAACCUP                            26573

                            GLLEZL                              25980

                            XXMBB_LOAD_VALIDATE                 24677

                            XXMBBIMPRTFILEPRG_GT                24677

                            XXMRGBALCCID_CHILD                  21489

                            XXMBBCRTEVTCHILD                    21197

                            GLPPOSS                             21185

                            FNDOAMCOL                           12341

                            XXMBB-XCOMUPLOAD3                   11721

                            XLAACCPB                            10395

                            XXGLLEDGERV2                         7965

                            XXMBBVDRTRANSFERV2                   7965

                            XXMBBIMPRTFILE_GT                    7337

                            XXMBBIMPRTFILEMAIN                   7236

                            XXGLRECON1                           7017

                            XXGLRECONDET2                        6944

                            XXGLCHKINBNDPROC                     6898

                            XXEGLDLBALCUR                        6820

                            XXEGLDLBAL                           6820

                            GLCRVL                               5750

                            XXGLRECON3DET                        5733

                            FNDRSSTG                             4735

                            XXMBB_CRTEVT_TRX_PRL                 3795

                            XXMBBVDRTRANSFER                     3712

                            XXMBBFSAHCOREOUT_THREAD              3276

                            XXMBBFSAHCOREIN_THREAD               3276

                            XXMBBFSAHPNBOUT_THREAD               3240

                            XXMBBFSAHPNBIN_THREAD                3240

                            XXMBBFSAHNCOUT_THREAD                3240

                            XXMBBFSAHETQOUT_THREAD               3240

                            XXMBBFSAHETQIN_THREAD                3240

                            XXMBBFSAHNCIN_THREAD                 3240

                            XXGLDLBALALL                         3211

                            XXMBBARCHPRG_RNG_PURGE_CHILD         3012

                            FNDWFBG                              2611

                            XXMBB-XCOMUPLOAD2                    2300

                            XXGLIMPJOUR                          2209

                            XXMBB_CRTEVT_JRN                     2195

                            XXGLIMPORTJOUR                       2185

                             

                             

                            39 rows selected.

                            • 11. Re: ORA-01555
                              Beauty_and_dBest

                              Hi Vivek,

                               

                               

                              How do you purge per table?

                               

                               

                              Thanks,

                              • 12. Re: ORA-01555
                                Bashar.

                                Hi,

                                 

                                I suggest to run the purge request for each application in a separate run. Set the Age parameter to 150 for a start.

                                Start with Inventory, then run it for Shipping Execution.

                                If they complete successfully, run it for all applications.

                                Next, try 100 for the Age parameter.

                                 

                                Regards,

                                Bashar

                                • 13. Re: ORA-01555
                                  Beauty_and_dBest

                                  Thanks Bhasar and ALL,

                                   

                                  FNDCPURR  has two options > MANAGER or REQUEST, which has bigger space consumed?

                                   

                                  How do I count each for the Manager and for Request?

                                   

                                  if I chose to run ALL, it will error with duplciates.

                                   

                                   

                                  Kind regards,