10 Replies Latest reply on Jul 18, 2014 11:16 AM by Sami

    Importing partial dump over full dump

    Sami

      Dear All,

       

      Am using

       

      SQL> select * from v$version;
      
      
      BANNER
      ----------------------------------------------------------------
      Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
      PL/SQL Release 10.2.0.1.0 - Production
      CORE    10.2.0.1.0      Production
      TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
      NLSRTL Version 10.2.0.1.0 - Production
      
      
      
      

       

      I have requirement that Due to huge size and time taking more than 8 hours to take dump using expdp.

      So I plan to take full on week end and partial dump that is (few tables have been partition into 3 parts which has more 2 GB  such part1,part2,part3. Part3 always have latest data(maxvalue) .

       

      I Used to backup of all tables(non partition tables) with part3 partition table data's.

      Above scenario working fine.

       

      Facing problem in the following scenario is

      Now  I needs to import the full dump on week end and import partial dump on week days on DR.

       

      Full dump is working fine.

       

      While importing partial dump I needs import latest partial data's with all tables(non partition ) without distributing the existing data's(Part1, Part2 partition data)

       

      Partial Import with append option

       

      Starting "SCOTT"."SYS_IMPORT_FULL_01":  scott/******** REMAP_SCHEMA=scott:scott1 directory=DUMPDIR dumpfile=SCOTT_PART_09-07-14.DMP logfile=imp_scott_09-07-14.log TRANSFORM=SEGMENT_ATTRIBUTES:n TABLE_EXISTS_ACTION=append EXCLUDE=CONSTRAINT
      Processing object type TABLE_EXPORT/TABLE/TABLE
      ORA-39152: Table "SCOTT1"."TEMP" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
      ORA-39152: Table "SCOTT1"."DEPT" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
      ORA-39152: Table "SCOTT1"."EMP" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
      ORA-39152: Table "SCOTT1"."BONUS" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
      ORA-39152: Table "SCOTT1"."SALGRADE" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
      Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
      ORA-31693: Table data object "SCOTT1"."TEMP":"TEMP_PART2" failed to load/unload and is being skipped due to error:
      ORA-00001: unique constraint (SCOTT1.TEMP_BAK_PK) violated
      ORA-31693: Table data object "SCOTT1"."DEPT" failed to load/unload and is being skipped due to error:
      ORA-00001: unique constraint (SCOTT1.PK_DEPT) violated
      ORA-31693: Table data object "SCOTT1"."EMP" failed to load/unload and is being skipped due to error:
      ORA-00001: unique constraint (SCOTT1.PK_EMP) violated
      . . imported "SCOTT1"."SALGRADE"                         5.585 KB       5 rows
      . . imported "SCOTT1"."BONUS"                                0 KB       0 rows
      Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
      Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
      Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
      Job "SCOTT"."SYS_IMPORT_FULL_01" completed with 8 error(s) at 15:12:59
      

       

      APPEND option doesn't working or not appending the new records to table. Its skipped due to above error. since I have used EXCLUDE=CONSTRAINT option as well.

      I have tried  REPLACE option which deletes the existing partition data(part1, Part2) but my requirement is  Part1, Part2 should not be removed from table.

      Kindly guide me to resolve the above error.

       

      Thanks & Regards

      Sami.

        • 1. Re: Importing partial dump over full dump
          Srini Chavali-Oracle

          Not an answer to your question, but pl be aware that exports are not backups - 1.10 Feature Comparison of Backup Methods

           

          You should be using RMAN

          • 2. Re: Importing partial dump over full dump
            Richard Harrison .

            Hi,

            I think we need to take a step back here and ask what do you actually want to acheive with this?

             

            At one point you refer to this as 'DR' - i would then ask why not use dataguard or some other technology?

            If you just want a copy for some other purpose then rman would seem to be the right tool?

             

            Your export could likely be speeded up also by using parallel options.

             

            You are also on an unpatched version of 10.2 for whcih there are numerous datapump bugs - you should really be patched up.

             

            Can you describe exactly want you want to do?

             

            Regards,

            Rich

            1 person found this helpful
            • 3. Re: Importing partial dump over full dump
              Sami

              Hi Richard,

               

              Thanks for your reply and sorry for late response.

              Richard. for your reference I have explained my scenario in original post

              If there is bug in datadump then i have to choose other tools like rman.

              Richard, Can you list out me patches for 10.2 or else where can I search for it?

               

              Thanks & Regards

              Sami

              • 4. Re: Importing partial dump over full dump
                Richard Harrison .

                Hi,

                Your requirement is still not 100% clear to me - here is what i think you want...

                 

                1) full dump once per week - this works fine

                2) Partial dump - excluding the partitioned tables

                 

                So for point 2 - why don't you just exclude the partitoned tables completely from the extract so it doesn't try to import them?

                 

                Something like exclude=TABLE:" IN ('PART1','PART2')"

                 

                Regards,

                Rich

                • 5. Re: Importing partial dump over full dump
                  Sami

                  Dear Richard,

                   

                  Thanks for your reply.

                  Richard Harrison . wrote:

                   

                  Hi,

                  Your requirement is still not 100% clear to me - here is what i think you want...

                   

                  1) full dump once per week - this works fine

                  2) Partial dump - excluding the partitioned tables

                   

                  So for point 2 - why don't you just exclude the partitoned tables completely from the extract so it doesn't try to import them?

                   

                  Something like exclude=TABLE:" IN ('PART1','PART2')"

                   

                  Regards,

                  Rich

                  I have loaded the full dump using the following command

                  scott/******** REMAP_SCHEMA=scott:scott1 directory=DUMPDIR dumpfile=SCOTT_09-07-2014.DMP logfile=imp_scott_full_17-07-2013.log TRANSFORM=SEGMENT_ATTRIBUTES:n TABLE_EXISTS_ACTION=REPLACE 
                  
                  

                  Log file:

                   

                  ;;; 
                  Import: Release 10.2.0.1.0 - Production on Thursday, 17 July, 2014 15:27:58
                  
                  
                  Copyright (c) 2003, 2005, Oracle.  All rights reserved.
                  ;;; 
                  Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
                  With the Partitioning, OLAP and Data Mining options
                  Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
                  Starting "SCOTT"."SYS_IMPORT_FULL_01":  scott/******** REMAP_SCHEMA=scott:scott1 directory=DUMPDIR dumpfile=SCOTT_09-07-2014.DMP logfile=imp_scott_full_17-07-2013.log TRANSFORM=SEGMENT_ATTRIBUTES:n TABLE_EXISTS_ACTION=REPLACE 
                  Processing object type SCHEMA_EXPORT/USER
                  ORA-31684: Object type USER:"SCOTT1" already exists
                  Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
                  Processing object type SCHEMA_EXPORT/ROLE_GRANT
                  Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
                  Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
                  Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
                  ORA-31684: Object type SEQUENCE:"SCOTT1"."TEMP_SEQ" already exists
                  Processing object type SCHEMA_EXPORT/TABLE/TABLE
                  Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
                  . . imported "SCOTT1"."TEMP":"TEMP_PART1"                58.21 KB    2700 rows
                  . . imported "SCOTT1"."TEMP":"TEMP_PART2"                50.41 KB    2300 rows
                  . . imported "SCOTT1"."DEPT"                             5.648 KB       4 rows
                  . . imported "SCOTT1"."EMP"                              7.812 KB      14 rows
                  . . imported "SCOTT1"."SALGRADE"                         5.585 KB       5 rows
                  . . imported "SCOTT1"."BONUS"                                0 KB       0 rows
                  Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
                  Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
                  Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
                  Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
                  ORA-31684: Object type PROCEDURE:"SCOTT1"."PROC_EXPORT_PARTITION" already exists
                  Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
                  Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
                  Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
                  
                  

                   

                  Following SQL shows that both partition have records

                   

                  SELECT table_name,partition_name,num_rows FROM user_tab_partitions where partition_name like 'TEMP_PART%'
                  ORDER BY table_name, partition_name;
                  

                   

                  
                  
                  TABLE_NAME                     PARTITION_NAME                   NUM_ROWS
                  ------------------------------ ------------------------------ ----------
                  TEMP                           TEMP_PART1                           2700
                  TEMP                           TEMP_PART2                           2300
                  
                  
                  2 rows selected.
                  
                  
                  
                  
                  

                   

                  As per you suggestion I gone the partial dump import using exclude option

                   

                  scott/******** REMAP_SCHEMA=scott:scott1 directory=DUMPDIR dumpfile=SCOTT_PART_09-07-14.DMP logfile=imp_scott_part_17-07-2013.log TRANSFORM=SEGMENT_ATTRIBUTES:n EXCLUDE=TABLE:"IN ( 'TEMP:TEMP_PART1')" TABLE_EXISTS_ACTION=REPLACE 
                  
                  

                  Partial Log file:

                   

                  ;;; 
                  Import: Release 10.2.0.1.0 - Production on Thursday, 17 July, 2014 15:22:31
                  
                  
                  Copyright (c) 2003, 2005, Oracle.  All rights reserved.
                  ;;; 
                  Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
                  With the Partitioning, OLAP and Data Mining options
                  Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
                  Starting "SCOTT"."SYS_IMPORT_FULL_01":  scott/******** REMAP_SCHEMA=scott:scott1 directory=DUMPDIR dumpfile=SCOTT_PART_09-07-14.DMP logfile=imp_scott_part_17-07-2013.log TRANSFORM=SEGMENT_ATTRIBUTES:n EXCLUDE=TABLE:"IN ( 'TEMP:TEMP_PART1')" TABLE_EXISTS_ACTION=REPLACE 
                  Processing object type TABLE_EXPORT/TABLE/TABLE
                  Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
                  . . imported "SCOTT1"."TEMP":"TEMP_PART2"                58.21 KB    2700 rows
                  . . imported "SCOTT1"."DEPT"                             5.648 KB       4 rows
                  . . imported "SCOTT1"."EMP"                              7.812 KB      14 rows
                  . . imported "SCOTT1"."SALGRADE"                         5.585 KB       5 rows
                  . . imported "SCOTT1"."BONUS"                                0 KB       0 rows
                  Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
                  Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
                  Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
                  Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
                  Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
                  Job "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at 15:22:34
                  

                   

                  SELECT table_name,partition_name,num_rows FROM user_tab_partitions where partition_name like 'TEMP_PART%'
                  ORDER BY table_name, partition_name;
                  

                   

                  
                  
                  TABLE_NAME                     PARTITION_NAME                   NUM_ROWS
                  ------------------------------ ------------------------------ ----------
                  TEMP                           TEMP_PART1                              0
                  TEMP                           TEMP_PART2                           2700
                  
                  
                  2 rows selected.
                  
                  
                  
                  
                  
                  
                  

                   

                  EXCLUDE=TABLE:"IN ( 'TEMP:TEMP_PART1')" also deletes the record from TEMP_PART1

                   

                  My requirement is when ever am importing partial dump it has to import  TEMP_PART2 without deleting the old records ie without deleting TEMP_PART1 records.

                   

                  I hope the above scenario with help you to understand my requirement.

                   

                  Thanks & Regards

                  Sami

                  • 6. Re: Importing partial dump over full dump
                    Richard Harrison .

                    Hi,

                    OK - got you now.

                     

                    The problem you have is that TABLE_EXISTS_ACTION referes to the whole table and not individual partitions - i don't think there is a way round that just using datapump.

                     

                    I think you'll have to have 2 jobs and a non datapump step too...

                     

                    1) load all tables apart from partitioned one with replace/truncate option

                    2) truncate the partition you want to put the fresh data into

                    3) load just that partition from the dumpfile with the append option

                     

                    Another approach might be to use the query option on the source side to only fetch the new rows since the full export (if that is possible based on information you have in the table)

                     

                    However i would still suggest if this is for some kind of DR system that this isn;t the best approach - you should be looking at dataguard/rman

                     

                    Regards,

                    Rich

                    • 7. Re: Importing partial dump over full dump
                      Sami

                      Thanks Richard i will try those options

                       

                      Thanks & Regards

                      Sami

                      • 8. Re: Importing partial dump over full dump
                        Sami

                        Dear Richard,

                         

                        Your suggestion has worked successfully with ACCESS_METHOD=EXTERNAL_TABLE option.

                         

                        http://blog.csdn.net/tianlesoftware/article/details/6090757

                         

                        Import: Release 10.2.0.1.0 - Production on Friday, 18 July, 2014 14:09:03

                         

                         

                        Copyright (c) 2003, 2005, Oracle.  All rights reserved.

                         

                         

                        Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

                        With the Partitioning, OLAP and Data Mining options

                        Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

                        Starting "SCOTT"."SYS_IMPORT_FULL_01":  scott/******** REMAP_SCHEMA=scott:scott1 directory=DUMPDIR dumpfile=SCOTT_18-JULY-2014.DMP logfile=imp_scott_part1_18-07-2013.log TRANSFORM=SEGMENT_ATTRIBUTES:n TABLE_EXISTS_ACTION=append ACCESS_METHOD=EXTERNAL_TABLE

                        Processing object type TABLE_EXPORT/TABLE/TABLE

                        ORA-39152: Table "SCOTT1"."TEMP" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append

                        Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

                        . . imported "SCOTT1"."TEMP":"TEMP_PART2"                79.69 KB    3800 rows

                        Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

                        Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

                        Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

                        Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

                        Job "SCOTT"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 14:09:08

                         

                        Now the problem is am manually I rebuild the index before importing dump and  gather the statistics for TEMP table after importing dump. Even though it importing table, index statistics .


                        Is import doesn't have any statistics ?


                        Thanks & Regards

                        Sami

                        • 9. Re: Importing partial dump over full dump
                          Richard Harrison .

                          Hi,

                          The line from the logfile explains why

                           

                          "Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append"

                           

                          So even though the stats stuff is mentioned in the logfile its actually being skipped - this makes sense as you may be loading a single row into a 1 billion row table - so you wouldn't want the stats to say '1 row'.  The stats you have are just for what is in the dumpfile - they are not relevant for existing table + dumpfile (well in the general case anyway - you 'know' the stats are relevant as you truncated the table first).

                           

                          You could do yet another import after this one and just do include=statistics......  to just do those stats?

                           

                          Cheers,

                          Rich

                          • 10. Re: Importing partial dump over full dump
                            Sami

                            Dear Richard,

                             

                            Thanks very much for your support.

                            Thanks & Regards

                            Sami