Import Method using IMPDP to apply only incremental rows

Import Method using IMPDP to apply only incremental rows 

Step 1: Take a full backup of emp table

[oracle@oracledb ~]$ expdp system/passwd direcctory=dpump tables=scott.emp dumpfile=emp.dmp logfile=emp.log
LRM-00101: unknown parameter name 'direcctory'

[oracle@oracledb ~]$ expdp system/sys123 directory=dpump tables=scott.emp dumpfile=emp.dmp logfile=emp.log

Export: Release 11.2.0.3.0 - Production on Sat Mar 25 12:11:41 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** directory=dpump tables=scott.emp dumpfile=emp.dmp logfile=emp.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."EMP"                               8.562 KB      14 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /u01/emp.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 12:11:57

SQL> select empno from emp;

     EMPNO
----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876

     EMPNO
----------
      7900
      7902
      7934

14 rows selected.

Step 2: Now insert one row into EMP table

SQL> insert into emp values(1001,'sirat','MGR','7369',sysdate,1000,null,20);

1 row created.

SQL> commit;

Commit complete.

Step 3: Now take a incremental backup using QUERY and DATA_ONLY parameter in IMPDP command

[oracle@oracledb ~]$ cat data.par
tables=SCOTT.EMP
directory=dpump
DUMPFILE=emp_incre.dmp
logfile=emp_incre.log
query=SCOTT.EMP:"where empno=1001"
content=DATA_ONLY

[oracle@oracledb ~]$

[oracle@oracledb ~]$ expdp system/passwd parfile=data.par

Export: Release 11.2.0.3.0 - Production on Sat Mar 25 21:32:14 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** parfile=data.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
. . exported "SCOTT"."EMP"                               8.031 KB       1 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /u01/emp_incre.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 21:32:16

[oracle@oracledb ~]$


Step 4: Delete one row from emp table

SQL> delete from emp where empno=1001;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select empno from emp;

     EMPNO
----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876

     EMPNO
----------
      7900
      7902
      7934

14 rows selected.

SQL>


Step 5: Apply incremental backup APPEND option in IMPDP command

[oracle@oracledb ~]$ impdp system/passwd directory=dpump dumpfile=emp_incre.dmp logfile=imp1.log table_exists_action=append

Import: Release 11.2.0.3.0 - Production on Sat Mar 25 21:36:31 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=dpump dumpfile=emp_incre.dmp logfile=imp1.log table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."EMP"                               8.031 KB       1 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 21:36:34

[oracle@oracledb ~]$

Now check Data append in emp table or not

SQL> select empno from emp;

     EMPNO
----------
      1001
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844

     EMPNO
----------
      7876
      7900
      7902
      7934

15 rows selected.

SQL>


Thats it!!!!!!!!!!!!!!!!!!!!!!!!!

Reference :  http://www.acehints.com/2012/05/datapump-impdp-tableexistsaction-append.html