10 Replies Latest reply on Jan 5, 2016 12:11 PM by Beauty_and_dBest

    Migrating  SEQUENCES

    Beauty_and_dBest

      Hi ALL,

       

      EBS R12.2.4

      11gR2

      OEL6

       

       

      We have migrated out EBS 11i to EBS R12.2.4

      The problem is all the sequence numbers were reset to 0, causing transaction number duplication issue.

      How can I reset all the sequences to the last number (from old instance) + 100 on the new instance?

      I understand EBS has 12,000+ sequences

       

       

      Kind regards,

      jc

        • 1. Re: Migrating  SEQUENCES
          Pravin Takpire

          This should not happen. Upgrade most of the time ( I would say never ) set the sequences current value to 0. Looks like either there was problem in upgrade or there is some bug.

          Give some examples.

          regards

          Pravin

          1 person found this helpful
          • 2. Re: Migrating  SEQUENCES
            Beauty_and_dBest

            Hi Pravin,

             

            It is not really logical upgrade but physical one

            I mean we install another EBS R12.2.4 on top of the existing EBS 11i.

            The process in 11i is continued or carried out on to the new system R12.2.

             

            Some of the issues occurred are:

            Based on the attached note, I believe we cannot continue the sequential numbering of the wip jobs from the old system.  I tried testing by updating the wip job number after the generation.  While it did save the updated number, the next job that is created did not follow the sequence I altered.

             

            Thanks

            • 3. Re: Migrating  SEQUENCES
              VishnuVinnakota

              Hi,

               

                      In this case as its not exactly an Technical Upgrade, you will have to work with the Techno/Functional Team as well as Oracle to fix the data issues.

               

                       Based on the dependency of data, we can export/import data but for all the 12000 sequences might not be possible being realistic.

              1 person found this helpful
              • 4. Re: Migrating  SEQUENCES
                PamNL

                I did write a script to re-install sequences once. What I did is check the max number in the current table and used start with for the sequence on installation. Maybe you can find some use for it in your case?

                 

                set term off

                set feedback off

                 

                CONNECT &&3/&&4

                 

                set feedback on

                set serveroutput on size 1000000

                set term on

                 

                DECLARE

                 

                  v_nr NUMBER := 0;

                  v_statement VARCHAR2(2000);

                  v_error     VARCHAR2(240);

                  v_exists    date;

                  v_owner     VARCHAR2(100);

                  v_start_with NUMBER;

                  v_seq_name   VARCHAR2(240);

                  v_max_nr     NUMBER;

                  v_exist      NUMBER;

                 

                  CURSOR C_Seqs

                  IS

                 

                  SELECT 'XXX_DATA_LAYER_COLS_ALL_S' Seq_Name,'XXX_DATA_LAYER_COLS_ALL' Tab_Name,'' Column_Name FROM DUAL

                  UNION SELECT 'XXX_DATA_LAYERS_ALL_S','XXX_DATA_LAYERS_ALL','' FROM DUAL

                  UNION SELECT 'XXX_DOT_BLOCK_S','XXX_DOT_BLOCKS','' FROM DUAL

                  UNION SELECT 'XXX_DOT_CONDITION_S','XXX_DOT_BLOCK_CONDITIONS','' FROM DUAL

                  UNION SELECT 'XXX_DOT_EVENT_S','XXX_DOT_EVENTS','' FROM DUAL

                  UNION SELECT 'XXX_DOT_LETTER_S','XXX_DOT_LETTERS','LETTER_ID' FROM DUAL

                  UNION SELECT 'XXX_DOT_SECTION_S','XXX_DOT_SECTIONS','' FROM DUAL

                  UNION SELECT 'XXX_DOT_TEMPLATE_S','XXX_DOT_TEMPLATES','' FROM DUAL

                  UNION SELECT 'XXX_DOT_WF_S','','' FROM DUAL

                  UNION SELECT 'XXX_LCM_ERRORS_S' Seq_Name, 'XXX_LCM_ERRORS' Tab_Name,'ERROR_ID' column_name FROM DUAL

                  UNION SELECT 'XXX_LCM_SELECTION_S','','' FROM DUAL

                  UNION SELECT 'XXX_LCM_CONC_S','XXX_LCM_REQUESTS','REQUEST_ID' FROM DUAL

                  UNION SELECT 'XXX_LCM_XDO_USAGES_S','XXX_LCM_STYLESHEET_USAGES','' FROM DUAL

                  UNION SELECT 'XXX_DOT_MASTER_S','XXX_DOT_TEMPLATE_USAGE','USAGE_ID' FROM DUAL

                  UNION SELECT 'XXX_LCM_TMP_S','','' FROM DUAL

                  UNION SELECT 'XXX_LCM_CUSTOM_S','XXX_LCM_CUSTOM_LINES','' FROM DUAL

                  UNION SELECT 'XXX_LCM_PERF_S','XXX_LCM_PERF_DETAILS','ID' FROM DUAL

                  UNION SELECT 'XXX_LCM_BATCH_S','XXX_LCM_BATCH_PROCESSOR','ID' FROM DUAL

                  ;

                 

                BEGIN

                 

                  v_nr := 1;

                  FOR S in C_Seqs

                  LOOP

                  BEGIN

                 

                   

                   

                    DBMS_OUTPUT.Put_Line ('[' || lpad (v_nr,3,' ') || '] Sequence ' || S.Seq_Name || ' checking ...');

                 

                    v_start_with := 0;

                   

                    begin

                      select sequence_name

                      ,      last_number

                      into   v_seq_name

                      ,      v_start_with

                      from   xxx_lcm_sequences

                      where  sequence_name = S.Seq_Name

                      ;

                   

                     

                      exception

                        when others then

                          v_start_with := 0;

                          v_seq_name   := null;

                    end;

                   

                   

                    v_exist      := 0;

                   

                    BEGIN

                      SELECT 1

                      INTO   v_exist

                      FROM   ALL_OBJECTS

                      WHERE  Object_Name = S.Seq_Name

                      AND    Owner = upper ('&&3')

                      AND    Object_Type = 'SEQUENCE'

                      ;

                     

                      EXCEPTION

                        WHEN Others THEN

                          v_exist := 0;

                         

                 

                    END;

                   

                    IF v_seq_name IS NOT NULL

                    THEN

                      DBMS_OUTPUT.Put_Line ('- Sequence ' || S.Seq_Name || ' was owned by APPS and at ' || v_start_with || '.');

                    END IF; -- Owned by apps before ...

                   

                    IF v_exist = 0

                    THEN

                   

                   

                    IF S.Column_Name IS NOT NULL

                    THEN

                    BEGIN

                      v_statement := 'SELECT NVL (Max (' || S.Column_Name || '),0) FROM ' || S.Tab_Name;

                      execute immediate v_statement into v_max_nr;

                     

                      IF v_max_nr <= v_start_with

                      THEN

                        DBMS_OUTPUT.Put_Line ('- Max ' || S.Column_Name || ' from ' || S.Tab_Name || ' is ' || v_max_nr || ': Ok');

                      ELSE

                        DBMS_OUTPUT.Put_Line ('- Max ' || S.Column_Name || ' from ' || S.Tab_Name || ' is ' || v_max_nr || ': Increasing to ' || to_char (v_max_nr+1));

                      END IF;

                     

                      IF v_max_nr > v_start_with

                      THEN

                        v_start_with := v_max_nr+1;

                      END IF;

                     

                      EXCEPTION

                        WHEN Others THEN

                          v_error := SQLERRM;

                    

                          --DBMS_OUTPUT.Put_Line ('- ERROR Cannot retrieve max ' || S.Column_Name || ' from ' || S.Tab_Name || ':' || v_error);

                          --DBMS_OUTPUT.Put_Line (substr (v_statement,1,240));

                    END;

                    END IF;

                   

                 

                   

                    IF S.Tab_Name IS NOT NULL AND S.Column_Name IS NULL

                    THEN

                    BEGIN

                     v_statement := 'SELECT Count(*) FROM ' || S.Tab_Name;

                      execute immediate v_statement into v_max_nr;

                     

                     

                     

                      IF v_max_nr <= v_start_with

                      THEN

                        DBMS_OUTPUT.Put_Line ('- Nr of recs in ' || S.Tab_Name || ' is ' || v_max_nr || ': Ok');

                      ELSE

                        DBMS_OUTPUT.Put_Line ('- Nr of recs in ' || S.Tab_Name || ' is ' || v_max_nr || ': Increasing to ' || to_char (v_max_nr+10000));

                      END IF;

                     

                      IF v_max_nr > v_start_with

                      THEN

                       

                        v_start_with := v_max_nr+10000;

                      END IF;

                     

                      EXCEPTION

                        WHEN Others THEN

                          v_error := SQLERRM;

                          --DBMS_OUTPUT.Put_Line ('- ERROR Cannot retrieve nr of records in ' || S.Tab_Name || ':' || v_error);

                          --DBMS_OUTPUT.Put_Line (substr (v_statement,1,240));

                    END;

                    END IF;

                   

                    END IF; -- Did not exist ..

                 

                  

                 

                    IF v_exist = 0

                    THEN

                   

                    IF v_start_with > 0

                    THEN

                        v_statement := 'CREATE SEQUENCE ' || S.Seq_Name || ' start with ' || v_start_with;

                    ELSE

                      v_statement := 'CREATE SEQUENCE ' || S.Seq_Name;

                    END IF;

                   

                    execute immediate (v_statement);

                   

                    DBMS_OUTPUT.Put_Line ('- Sequence ' || S.Seq_Name || ' created with start value ' || v_start_with || '.');

                    ELSE

                    DBMS_OUTPUT.Put_Line ('- Sequence ' || S.Seq_Name || ' already exists.');

                    END IF;

                   

                    v_nr := v_nr + 1;

                       

                    EXCEPTION

                      WHEN Others tHEN

                        v_error := SQLERRM;

                        IF Instr (v_error,'ORA-00955') > 0

                        THEN

                          DBMS_OUTPUT.Put_Line ('- Sequence (or object named like) ' || S.Seq_Name || ' already exists.');

                          v_nr := v_nr + 1;

                        ELSE

                          DBMS_OUTPUT.Put_Line ('- ERROR on creating sequence ' || S.Seq_Name || ': ' || v_error);

                        END IF;

                   

                  END;

                 

                  BEGIN

                 

                 

                  v_statement := 'GRANT ALL ON ' || S.Seq_Name || ' TO &&1';

                  execute immediate (v_statement);

                 

                  EXCEPTION

                      WHEN Others tHEN

                        v_error := SQLERRM;

                        DBMS_OUTPUT.Put_Line ('Grant on ' || S.Seq_Name || ' to &&1 failed due to ' || v_error);

                 

                  END;

                 

                 

                  END LOOP;

                 

                END;

                /

                1 person found this helpful
                • 5. Re: Migrating  SEQUENCES
                  Pravin Takpire

                  I would suggest you don't follow the method that you used for upgrade. It is not straightforward process where you can copy objects or use 11i objects in R12. FOllow the proper procedure of upgrade, otherwise you will land up in many other issues.

                  regards

                  Pravin

                  1 person found this helpful
                  • 6. Re: Migrating  SEQUENCES
                    Beauty_and_dBest

                    Thanks ALL,

                     

                    Are those scripts above supported in EBS? or are they applicable on generic oracle database only?

                     

                    Since this is a fresh instance, the sequence would start at 1.  So we need to reset it from last used number + 1  from R11, as  mentioned.  However, I cannot find  a setup that would do just that.  Per my understanding (if this is correct) from the Oracle note I sent, this requires change at the backend/database (view and not table).  If we decide to do this..let's perform this first at clone and test.  The note made mention about data corruption, although it pertains to the alteration of sequential numbering and not on the resetting.

                     

                    Kind regards,

                    • 7. Re: Migrating  SEQUENCES
                      ora_tech

                      Hi,

                       

                      Yes Its always recommended to TEST if there are new changes in TEST Environment.

                       

                      thanks,

                      X A H E E R

                      1 person found this helpful
                      • 8. Re: Migrating  SEQUENCES
                        Beauty_and_dBest

                        Thanks ALL,

                         

                        For now, the affected module is WIP (work in progress), How do I know which sequence is being used by WIP?

                         

                         

                        Kind regards,

                        • 9. Re: Migrating  SEQUENCES
                          Pravin Takpire

                          I would suggest you login to etrm.oracle.com and check for sequences for WIP module

                          regards

                          Pravin

                          1 person found this helpful