7 Replies Latest reply on Feb 17, 2014 12:17 PM by Partha Sarathy S

    DDL Statement for particular schema

    Sami

      Hi All,

       

      I have 2 schema's in source database namely

      1. dev

      2. scott

       

      Same schema's are available in target database namely

      1.dev

      2. scott1

       

      I need to replicate only scott schema from source database to scott1 target database

       

      So I have used the following extract & replicate such as

       

      extract LSCOTTD1 
      userid ggs_owner@localdev, password neptune
      ReportCount Every 10 Minutes, Rate
      discardfile E:\DataGaurd\discard\ext_discard.txt, append, megabytes 10
      exttrail E:\DataGaurd\dirdat\sr
      ddl &
      include mapped OBJNAME "*"
      SEQUENCE scott.*;
      TABLE SCOTT.*;
      

       

       

      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
      DBOPTIONS _AUTOMATICSEQUENCEFLUSH;
      ddl &
      INCLUDE mapped OBJNAME "*"
      ASSUMETARGETDEFS
      map scott.*, target scott1.*;
      

       

      but it not working when am creating or altering scripts

       

      but if i give

      ddl include all &

      include mapped OBJNAME "*"

      its working for all schemas such as dev,scott

       

      error & discard file are not generated. Kindly help me to resolve this..

       

      Thanks & Regards

      Sami

        • 1. Re: DDL Statement for particular schema
          Partha Sarathy S

          Try this instead in Source

           

          DDL INCLUDE ALL, EXCLUDE OBJNAME "dev.*"

           

          In target, try using

           

          DDL INCLUDE ALL

          because source should filter out all DDL operations in DEV schema and replicate would apply all fetched from source

          • 2. Re: DDL Statement for particular schema
            Sami

            Hi Parth,

             

            Thanks for your reply..

             

             

            Parth272025 wrote:

             

            Try this instead.

             

            DDL INCLUDE ALL, EXCLUDE OBJNAME "dev.*"

            I have tried that option . its working fine.. but include mapped OBJNAME "*" is not working..

            Did any one have idea or suggestion. why is not working..

             

            Thanks & Regards

            Sami

            • 3. Re: DDL Statement for particular schema
              Sami

              Hi Parth

              Parth272025 wrote:

               

              You have used only SCOTT schema and never mentioned DEV schema in any of the processes EXTRACT, PUMP or REPLICAT? If so DEV schema is not even mapped, so its DDL should not be replicated?

              yes. i have never mention DEV schema in any processes.. if give the following

              ddl include all &

              include mapped OBJNAME "*"


              it includes both dev,scott schema...

              if i mention

              DDL &

              INCLUDE MAPPED OBJNAME "*"

              ddl changes not appearing in scott1 target database.


              If am using

              DDL INCLUDE ALL, EXCLUDE OBJNAME "dev.*"

              Its working but my doubt is include mapped objname is not working...


              Thanks & Regards

              Sami

              • 4. Re: DDL Statement for particular schema
                Partha Sarathy S

                Ya. I need to make a few changes to my answer, thats why i removed it.

                • 5. Re: DDL Statement for particular schema
                  Partha Sarathy S

                  Yes what you have mentioned is correct.

                   

                  ddl include all &

                  include mapped OBJNAME "*"

                  How this should work is. DDL INCLUDE ALL should include DDL for all schemas of source database and INCLUDE MAPPED should include only DDLs for objects which are of mapped scope. But I am not sure why it doesn't work that way. I have never tried this way. Might be because of the double quotes around *(asterisk).. Instead try this  and check what you are getting.

                   

                  ddl include all &

                  include mapped OBJNAME *

                  And also

                   

                  ddl include all &

                  include mapped OBJNAME "scott.*"

                  1 person found this helpful
                  • 6. Re: DDL Statement for particular schema
                    Sami

                    Hi Parth,

                     

                    Thanks for your reply.

                     

                    As per gg_wux_admin_v104.pdf in page no 155

                     

                    The following is an example of how to combine DDL parameter options.
                    DDL &
                    INCLUDE UNMAPPED &
                    OPTYPE alter &
                    OBJTYPE ‘table’ &
                    OBJNAME “users.tab*” &
                    INCLUDE MAPPED OBJNAME “*” &
                    EXCLUDE MAPPED OBJNAME "temporary.tab*"
                    
                    

                     

                     

                    INCLUDE MAPPED OBJNAME "*"  is not working..

                     

                    So i have modified as

                     

                    extract LSCOTTD1
                    userid ggs_owner@localdev, password neptune
                    ReportCount Every 10 Minutes, Rate
                    discardfile E:\DataGaurd\discard\ext_discard.txt, append, megabytes 10
                    exttrail E:\DataGaurd\dirdat\sr
                    ddl &
                    include mapped OBJNAME "scott.*"
                    SEQUENCE scott.*;
                    TABLE SCOTT.*;
                    
                    

                     

                    Extract:

                    DDL INCLUDE MAPPED OBJNAME "SCOTT.*"

                     

                    Replicate:

                     

                    DDL INCLUDE ALL is working fine.

                     

                    If am using

                     

                    DDL INCLUDE MAPPED OBJNAME "SCOTT.*" is not working in replicate.

                     

                    Thanks & Regards

                    Sami

                    • 7. Re: DDL Statement for particular schema
                      Partha Sarathy S

                      You don't need any filter at the replicate side right? since you have already done the filter at the extract.