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

EXPDP in Oracle 12c (ORA-39002 ORA-39070 ORA-39087)

DROP DIRECTORY DUMP_DATA;

CREATE OR REPLACE DIRECTORY
DUMP_DATA AS
'G:\InHouse_DB_Backup';



GRANT EXECUTE, READ, WRITE ON DIRECTORY DUMP_DATA TO system WITH GRANT OPTION;

impdp system/sys123 schemas=ORBHRM,ORBWEB,UTILITY directory=DUMP_DATA dumpfile="HRDBRQDB-20150824.dmp" logfile=test.log

C:\Users\Administrator>impdp system/sys123 schemas=ORBHRM,ORBWEB,UTILITY directory=DUMP_DATA dumpfile="HRDBRQDB-20150824.dmp" logfile=test.log

Import: Release 12.1.0.2.0 - Production on Tue Aug 25 14:51:00 2015

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DUMP_DATA is invalid

Note: In Oracle 12 c during expdp and impdp pluggable Database name must be mentioned

impdp system/sys123@orcl_pdb schemas=ORBHRM,ORBWEB,UTILITY directory=DUMP_DATA dumpfile="HRDBRQDB-20150824.dmp" logfile=test.log

Import (IMPDP) Oracle Database backup from network location

Scenario:

Suppose we want to restore backup into destination database server (192.168.10.6) and backup resides in source database server (192.168.10.32)

Solution:

Step1: We need to keep the backup dump file into source database server (192.168.10.32)

Step2: We need to create a database link into source database server (192.168.10.32)

CREATE PUBLIC DATABASE LINK DPUMP
 CONNECT TO SYSTEM
 IDENTIFIED BY
 USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.6)(PORT=1521))(CONNECT_DATA=(SID=orcl)))';

Step3: Now we need to create a directory where database backup will reside 

DROP DIRECTORY data_dump;
CREATE OR REPLACE DIRECTORY data_dump AS '/back/exp/';

GRANT EXECUTE, READ, WRITE ON DIRECTORY SYS.data_dump TO SYSTEM WITH GRANT OPTION;

Step4: Now we need to execute import (impdp) command from source database server (192.168.10.32) to restore
       backup into destination database server (192.168.10.6)

Step5: Put the schemas value which schema you want to import

impdp system/sys123 schemas=scott,hr,test network_link=DPUMP directory=data_dump dumpfile=orcl_full.dmp logfile=orcl_full.log    


  
Step6: Now the data values that you want to import..

Cheers.....