4 Replies Latest reply: Feb 14, 2014 2:12 AM by Sami RSS

    Sequence Nextval not updating in target schema

    Sami

      Hi All,

       

      Am using Golden gate

       

      Oracle GoldenGate Command Interpreter for Oracle
      Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
      Windows (optimized), Oracle 10g on Oct  5 2011 00:50:35
      
      
      Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
      

       

      configured DDL options to replicate changes from scott schema to scott1 schema

      DDL statements are working fine. such as create, alter, rename.

       

      Even DML statement with sequence also working fine.

      But sequence next value is not updating from schema scott to scott1. (No errors in log files)

       

       

      I have executed sequence.sql on both database schemas( for Golden Gate schema).

       

      Please help me to resolve the above issue

       

      Thanks & Regards

      Sami

        • 1. Re: Sequence Nextval not updating in target schema
          Partha Sarathy S

          Did you include SEQUENCE parameter in EXTRACT?  Can you post a copy of extract and replicat?

          • 2. Re: Sequence Nextval not updating in target schema
            Sami

            Hi Parth,

             

            I have included SEQUENCE parameter in extract and replicate

             

            Extract:

            extract LSCOTTD1
            userid ggs_owner@localdev, password neptune
            ReportCount Every 10 Minutes, Rate
            exttrail E:\DataGaurd\dirdat\sr
            ddl include all &
            EXCLUDE OBJNAME “SAMI.*” &
            EXCLUDE OBJNAME “MONITER.*”
            
            
            SEQUENCE scott.*;
            TABLE SCOTT.*;
            
            

             

            Extract Pump

             

            EXTRACT PSCOTTD1
            USERID ggs_owner@localdev, password neptune
            RMTHOST 10.152.5.38 , MGRPORT 7809
            ReportCount Every 10 Minutes, Rate
            rmttrail E:\DataGaurd\dirdat\sr
            SEQUENCE scott.*;
            table scott.*;
            
            

             

             

            Replicate:

             

            REPLICAT RSCOTTD1
            userid ggs_owner, password neptune
            discardfile E:\DataGaurd\discard\rep1_discard.txt, append, megabytes 10
            ReportCount Every 10 Minutes, Rate
            dl include all &
            EXCLUDE OBJNAME "MONITER.*"
            ASSUMETARGETDEFS
            map scott.*, target scott1.*;
            
            

             

            I have configured SEQUENCE as per http://www.juliandyke.com/Blog/?m=201304 in sequence setup sections.

             

            But I can't able to update the Nextval for target schema scott1.

             

            Thanks & Regards

            Sami

            • 3. Re: Sequence Nextval not updating in target schema
              Sami

              Hi All,

               

              Today morning i have re started the SEQUENCE update process..

               

              by  below steps

               

              DROP SEQUENCE TEST6_SEQ;
              CREATE SEQUENCE TEST6_SEQ
                START WITH 1
                MAXVALUE 99999999999
                MINVALUE 1
                NOCYCLE
                NOCACHE
                ORDER;
              
              
              

               

               

              Before inserting value:

              Source schema:

               

              SequenceSchemaMin ValueMax ValueLast NumberCreated                Last DDL            CycleOrderedCache SizeIncrement By
              TEST6_SEQSCOTT1        999999999991        14-02-2014 11:11:46 AM14-02-2014 11:11:46 AMNo    Yes     0         1

               

              Replicated through GG

              Target Schema:

              SequenceSchemaMin ValueMax ValueLast NumberCreated                Last DDL           CycleOrderedCache SizeIncrement By
              TEST6_SEQSCOTT11       999999999991        14-02-2014 11:11:54 AM14-02-2014 11:11:54 AMNo    Yes     0         1

               

              Running insert from source:

               

              Insert into T    (ID,  NM, GENDER)  Values    (TEST6_SEQ.nextval , 'a', 'M');
              COMMIT;
              
              
              Insert into T    (ID,  NM, GENDER)  Values    (TEST6_SEQ.nextval , 'a', 'M');
              COMMIT;
              
              
              

               

              Result from source:

               

              SELECT * FROM SCOTT.T;

               

              Seq NoNMGender
              1aM
              2aM

               

              After inserting Source Sequence get update next value

               

              SequenceSchemaMin ValueMax ValueLast NumberCreated                Last DDL           CycleOrderedCache SizeIncrement By
              TEST6_SEQSCOTT1        999999999993        14-02-2014 11:11:46 AM14-02-2014 11:11:46 AMNo     Yes  0         1

               

              but replicate process getting abended

               

              2014-02-14 11:15:24  ERROR   OGG-01444  Error in replicating sequence value [ORA-20783:
              ORA-01031: insufficient privileges
              ORA-06512: at "GGS_OWNER.REPLICATESEQUENCE", line 369
              ORA-06512: at line 1, SQL BEGIN  "GGS_OWNER" .replicateSequence (TO_NUMBER(2), TO_NUMBER(0), TO_NUMBER(1), 'SCOTT1', TO_NUMBER(0), 'TEST6_SEQ', 'GGS_OWNER', TO_NUMBER(1), TO_NUMBER (0), ''); END;].
              
              
              

               

               

              I have given access permission as

               

               

              grant execute on GGS_OWNER.UPDATESEQUENCE to scott1;

               

               

              but still am getting above error.

               

              Thanks & Regards

              Sami

              • 4. Re: Sequence Nextval not updating in target schema
                Sami

                Hi All,

                 

                I don't know the proper grant permission to set for ggs_owner.

                 

                I have resolved by granting DBA permission to ggs_owner.

                 

                Thanks & Regards

                Sami