How to control data pump job.

This steps help you to control/stop/resume/kill data pump job. For control data pump job at first you have to identify the job name from DBA_DATAPUMP_JOBS View and then you can control the job using expdp/impdp by providing the value (JOB_NAME) of attach parameter.

1. Identify the jobs which you want to control.

SQL> SELECT JOB_NAME

FROM DBA_DATAPUMP_JOBS

WHERE STATE<>'NOT RUNNING';  2    3

JOB_NAME

------------------------------

SYS_IMPORT_SCHEMA_06

SYS_IMPORT_SCHEMA_07

2. Control the job using expdp/impdp

bash-4.2$ impdp system/password attach=SYS_IMPORT_SCHEMA_07

3. Kill the job

Import>

Import> kill_job

Are you sure you wish to stop this job ([yes]/no): yes

bash-4.2$

ORA-39070: Unable to open the log file. (in RAC/ASM)

Cause : In ASM directory impdp/expdp are not able to create log file.

Solution: Create directory in file system and use this for log file directories.


[oracle@sbldcdb01 ~]$ impdp system/pass directory=DUMPDIR dumpfile=CBSDB_BODAY080816-%U.dmp logfile=impCBSDB_BODAY080816.log parallel=4 remap_schema=CBSDB_BODAY:CBSDB_RAC transform=oid:n

Import: Release 11.2.0.4.0 - Production on Wed Aug 24 12:43:34 2016

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation


[oracle@sbldcdb01 ~]$ PWD
-bash: PWD: command not found
[oracle@sbldcdb01 ~]$
[oracle@sbldcdb01 ~]$ pwd
/home/oracle
[oracle@sbldcdb01 ~]$
[oracle@sbldcdb01 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 24 12:46:19 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> CREATE OR REPLACE DIRECTORY
LOGPDIR AS
'/home/oracle/';
  2    3
Directory created.

SQL> GRANT READ, WRITE ON DIRECTORY LOGPDIR TO PUBLIC;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@sbldcdb01 ~]$ impdp system/pass directory=DUMPDIR dumpfile=CBSDB_BODAY080816-%U.dmp logfile=LOGPDIR:impCBSDB_BODAY080816.log parallel=4 remap_schema=CBSDB_BODAY:CBSDB_RAC transform=oid:n

Import: Release 11.2.0.4.0 - Production on Wed Aug 24 12:47:31 2016

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_03" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_03":  system/******** directory=DUMPDIR dumpfile=CBSDB_BODAY080816-%U.dmp logfile=LOGPDIR:impCBSDB_BODAY080816.log parallel=4 remap_schema=CBSDB_BODAY:CBSDB_RAC transform=oid:n
Processing object type SCHEMA_EXPORT/USER
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE

How to export dump file from Standby Database.

Physical standby database are open in Read Only mode and data pump job perform some write activity in database for completing export/import job, to support this activity Create database link in your UAT or any other database which are opened as read write mode.

1. Create Database Link With Standby Database.

CREATE DATABASE LINK STDBY
 CONNECT TO PRODUSER
 IDENTIFIED BY pass123
 USING '10.11.201.200:1521/dcdbstdby';

2. Now submit data pump job using NETWORK_LINK parameter.

expdp system/pass123 schemas=PRODUSER directory=DUMP_FILE_DIR dumpfile=PRODUSER_DATA_DUMP.dmp logfile=PRODUSER_DATA_DUMP.log NETWORK_LINK=STDBY


This policy can be used to perform import dump file into remote database.

SKIP_CONSTRAINT_ERRORS: How to Skip Constraint Errors during Import.

bash-3.2$ impdp  impdp_test/impdp_test@ORTBDB directory=SBS_DIR dumpfile=testimpdp.dmp logfile=testimpdpda.log TABLE_EXISTS_ACTION=APPEND

Import: Release 11.2.0.3.0 - Production on Sun Mar 1 17:41:31 2015

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 "IMPDP_TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "IMPDP_TEST"."SYS_IMPORT_FULL_01":  impdp_test/********@ORTBDB directory=SBS_DIR dumpfile=testimpdp.dmp logfile=testimpdpda.log TABLE_EXISTS_ACTION=APPEND
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"IMPDP_TEST" 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/TABLE/TABLE
Table "IMPDP_TEST"."IMP_DATA" 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 SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "IMPDP_TEST"."IMP_DATA" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (IMPDP_TEST.PK_ID) violated
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "IMPDP_TEST"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 17:41:34

bash-3.2$ impdp  impdp_test/impdp_test@ORTBDB directory=SBS_DIR dumpfile=testimpdp.dmp logfile=testimpdpda.log TABLE_EXISTS_ACTION=APPEND ignore=Y
LRM-00112: multiple values not allowed for parameter 'table_exists_action'

Import: Release 11.2.0.3.0 - Production on Sun Mar 1 17:44:40 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
LRM-00112: multiple values not allowed for parameter 'table_exists_action'

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
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "ignore=TRUE" Location: Command Line, Replaced with: "table_exists_action=append"
Master table "IMPDP_TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "IMPDP_TEST"."SYS_IMPORT_FULL_01":  impdp_test/********@ORTBDB directory=SBS_DIR dumpfile=testimpdp.dmp logfile=testimpdpda.log TABLE_EXISTS_ACTION=APPEND table_exists_action=append
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"IMPDP_TEST" 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/TABLE/TABLE
Table "IMPDP_TEST"."IMP_DATA" 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 SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "IMPDP_TEST"."IMP_DATA" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (IMPDP_TEST.PK_ID) violated
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "IMPDP_TEST"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 17:44:41

bash-3.2$ impdp  impdp_test/impdp_test@ORTBDB directory=SBS_DIR dumpfile=testimpdp.dmp logfile=testimpdpda.log TABLE_EXISTS_ACTION=APPEND DATA_OPTIONS=skip_constraint_errors

Import: Release 11.2.0.3.0 - Production on Sun Mar 1 17:46:12 2015

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 "IMPDP_TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "IMPDP_TEST"."SYS_IMPORT_FULL_01":  impdp_test/********@ORTBDB directory=SBS_DIR dumpfile=testimpdp.dmp logfile=testimpdpda.log TABLE_EXISTS_ACTION=APPEND DATA_OPTIONS=skip_constraint_errors
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"IMPDP_TEST" 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/TABLE/TABLE
Table "IMPDP_TEST"."IMP_DATA" 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 SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "IMPDP_TEST"."IMP_DATA"                     5.445 KB       1 out of 2 rows
1 row(s) were rejected with the following error:
ORA-00001: unique constraint (IMPDP_TEST.PK_ID) violated

Rejected rows with the primary keys are:
 Rejected row #1:
   column ID: 10
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "IMPDP_TEST"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 17:46:18

bash-3.2$

UDE-00010: multiple job modes requested, schema and tables.

Cause : This error show when you use multiple job in parameter file.

Solution & Example: Use only one parameter like SCHEMAS or TABLES.

When Parameter file are like ..

SCHEMAS=RND
DIRECTORY=DATA_DIR
DUMPFILE=RND_TABLE_QUERY.dmp
LOGFILE=RND_TABLE_QUERY.log
TABLES=RND.DEPT,RND.EMP

C:\Users\rajib.pradhan>expdp "'/ as sysdba'" parfile=D:\app\DATA_PUMP.par

Export: Release 11.2.0.1.0 - Production on Fri Aug 15 00:09:14 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
UDE-00010: multiple job modes requested, schema and tables.

When I have remove SCHEMA parameter like 

DIRECTORY=DATA_DIR

DUMPFILE=RND_TABLE_QUERY.dmp

LOGFILE=RND_TABLE_QUERY.log

TABLES=RND.DEPT,RND.EMP

I have successfully export ...

C:\Users\rajib.pradhan>expdp "'/ as sysdba'" parfile=D:\app\DATA_PUMP.par

Export: Release 11.2.0.1.0 - Production on Fri Aug 15 00:10:45 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" parfile=D:\app\DATA_PUMP.par

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 41.06 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "RND"."EMP"                                 35.00 MB  524288 rows

. . exported "RND"."DEPT"                                7.023 KB      28 rows

Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_TABLE_01 is:

  D:\DUMP\RND_TABLE.DMP

Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 00:11:43

Data Pump Backup Using Parameter File (.par)

This article represent how can use data pump with PARAFILE.

1. Create file with extension .par (Parameter File)  in any directory like.

I have created in (D:\app\) name DATA_PUMP.par

2. Give parameter value. Like

My file contain this value 

DIRECTORY=DATA_DIR

DUMPFILE=RND_TABLE.dmp

LOGFILE=RND_TABLE.log

TABLES=RND.DEPT,RND.EMP

3. Now execute with data pump. Like 

C:\Users\rajib.pradhan>expdp "'/ as sysdba'" parfile=D:\app\DATA_PUMP.par

Export: Release 11.2.0.1.0 - Production on Fri Aug 15 00:10:45 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" parfile=D:\app\DATA_PUMP.par

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 41.06 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "RND"."EMP"                                 35.00 MB  524288 rows

. . exported "RND"."DEPT"                                7.023 KB      28 rows

Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_TABLE_01 is:

  D:\DUMP\RND_TABLE.DMP

Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 00:11:43

C:\Users\rajib.pradhan>

========================================================

With Where Condition 

DIRECTORY=DATA_DIR

DUMPFILE=RND_TABLE_QUERY.dmp

LOGFILE=RND_TABLE_QUERY.log

TABLES=RND.DEPT,RND.EMP

QUERY=RND.EMP:"WHERE DEPARTMENT_ID !=50 ORDER BY EMPLOYEE_ID"

C:\Users\rajib.pradhan>expdp "'/ as sysdba'" parfile=D:\app\DATA_PUMP.par

Export: Release 11.2.0.1.0 - Production on Fri Aug 15 00:37:18 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" parfile=D:\app\DATA_PUMP.par

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 41.06 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "RND"."EMP"                                 9.265 KB       0 rows

. . exported "RND"."DEPT"                                7.023 KB      28 rows

Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_TABLE_01 is:

  D:\DUMP\RND_TABLE_QUERY.DMP

Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 00:37:35

Export table data with where condition.

It is allow to export specific rows from an table using oracle data pump just using QUERY Parameter. Like below ............

expdp rnd/rnd@orcl TABLES=DEPT query=\"where DEPARTMENT_ID=10\" DIRECTORY=DATA_DIR DUMPFILE=EXP_EMP_DEPT.DMP LOGFILE=EXP_EMP_DEPT.LOG


C:\Users\rajib.pradhan>expdp rnd/rnd@orcl TABLES=DEPT query=\"where DEPARTMENT_ID=10\" DIRECTORY=DATA_DIR DUMPFILE=EXP_EMP_DEPT.DMP LOGFILE=EXP_EMP_DEPT.LOG

Export: Release 11.2.0.1.0 - Production on Sun Jun 29 18:24:05 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "RND"."SYS_EXPORT_TABLE_01":  rnd/********@orcl TABLES=DEPT query="where DEPARTMENT_ID=10" DIRECTORY=DATA_DIR DUMPFILE=EXP_EMP_DEPT.DMP LOGFILE=EXP_EMP_DEPT.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/STATISTICS/TABLE_STATISTICS
. . exported "RND"."DEPT"                                6.390 KB       1 rows
Master table "RND"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for RND.SYS_EXPORT_TABLE_01 is:
  D:\DUMP\EXP_EMP_DEPT.DMP
Job "RND"."SYS_EXPORT_TABLE_01" successfully completed at 18:24:24

Table Exports/Imports Using Oracle Data Pump

1. You need to create directory (If you already created no need to create) from sys schema.

Example :
CREATE OR REPLACE DIRECTORY
DATA_DIR AS
'D:\DUMP\';

2. You need to given read & write permeation to specific user which you want to export. 
GRANT READ, WRITE ON DIRECTORY DATA_DIR TO RND;

3. Now you can export list of tables using data pump

You need to connect specific schema which tables you want to export ..

For export

expdp rnd/rnd@orcl TABLES=EMP,DEPT DIRECTORY=DATA_DIR DUMPFILE=EXP_EMP_DEPT.DMP LOGFILE=EXP_EMP_DEPT.LOG

For import (TABLE_EXISTS_ACTION=APPEND parameter allows data to be imported into existing tables.)

impdp rnd/rnd@orcl TABLES=EMP,DEPT DIRECTORY=DATA_DIR TABLE_EXISTS_ACTION=APPEND DUMPFILE=EXP_EMP_DEPT.DMP LOGFILE=IMP_EMP_DEPT.LOG


C:\Users\rajib.pradhan>expdp rnd/rnd@orcl TABLES=EMP,DEPT DIRECTORY=DATA_DIR DUMPFILE=EXP_EMP_DEPT.DMP LOGFILE=EXP_EMP_DEPT.LOG

Export: Release 11.2.0.1.0 - Production on Sun Jun 29 16:56:41 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "RND"."SYS_EXPORT_TABLE_01":  rnd/********@orcl TABLES=EMP,DEPT DIRECTORY=DATA_DIR DUMPFILE=EXP_EMP_DEPT.DMP LOGFILE=EXP_EMP_DEPT.LOG
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "RND"."DEPT"                                7.023 KB      28 rows
. . exported "RND"."EMP"                                 16.83 KB     107 rows
Master table "RND"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for RND.SYS_EXPORT_TABLE_01 is:
  D:\DUMP\EXP_EMP_DEPT.DMP
Job "RND"."SYS_EXPORT_TABLE_01" successfully completed at 16:57:12


C:\Users\rajib.pradhan>impdp rnd/rnd@orcl TABLES=EMP,DEPT DIRECTORY=DATA_DIR TABLE_EXISTS_ACTION=APPEND DUMPFILE=EXP_EMP_DEPT.DMP LOGFILE=IMP_EMP_DEPT.LOG

Import: Release 11.2.0.1.0 - Production on Sun Jun 29 16:58:29 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "RND"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "RND"."SYS_IMPORT_TABLE_01":  rnd/********@orcl TABLES=EMP,DEPT DIRECTORY=DATA_DIR TABLE_EXISTS_ACTION=APPEND DUMPFILE=EXP_EMP_DEPT.DMP LOGFILE=IMP_EMP_DEPT.LOG
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "RND"."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 "RND"."DEPT" 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 "RND"."DEPT"                                7.023 KB      28 rows
. . imported "RND"."EMP"                                 16.83 KB     107 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "RND"."SYS_IMPORT_TABLE_01" completed with 2 error(s) at 16:58:35

Export Meta Data (Without data) Only Using expdp In Oracle.

'/u01/app/oracle/product/11.2.0/db_1/bin/expdp' micr/micr@bachdb schemas=micr directory=MY_OCE CONTENT=METADATA_ONLY dumpfile=MICR_030214.DMP logfile=MICR_030214.LOG

Export: Release 11.2.0.1.0 - Production on Mon Feb 3 16:55:22 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "MICR"."SYS_EXPORT_SCHEMA_01":  micr/********@bachdb schemas=micr directory=MY_OCE CONTENT=METADATA_ONLY dumpfile=MICR_030214.DMP logfile=MICR_030214.LOG
Processing object type SCHEMA_EXPORT/USER
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/XMLSCHEMA/XMLSCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/JAVA_SOURCE/JAVA_SOURCE
Processing object type SCHEMA_EXPORT/JAVA_CLASS/JAVA_CLASS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/JOB
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
Master table "MICR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MICR.SYS_EXPORT_SCHEMA_01 is:
  /u01/XML/OCE/MICR_030214.DMP
Job "MICR"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:56:09

Export Import Oracle Dump File Using expde/impdp With Scheduler

Export Import Dump File 

Step 1:- Create Shell File Name exportimport.sh In (/u01/Script )
Step 2:- chmod u+x /u01/Script/exportimport.sh
Step 3:-


# 1. Set Your Environment Veritable.

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=micrdb
export PATH=$PATH:$ORACLE_HOME/bin

# 2. Declare and initialize value or date time.

SET_DATE=`date +%d%m%Y`
export CDATE

DMP_FILE=backup_$SET_DATE.dmp
LOG_FILE=backup_$SET_DATE.log

export DMP_FILE LOG_FILE

# 3. Export Starting



'/u01/app/oracle/product/11.2.0/db_1/bin/expdp' system/system@micrdb schemas=micr,beftn directory=dmp_bkp dumpfile=$DMP_FILE logfile=EXP$LOG_FILE

# 4. Moving File To Backup Folder.

mv /u01/DMP/$DMP_FILE /u01/DUMP_BACKUP/$DMP_FILE

mv /u01/DMP/$LOG_FILE /u01/DUMP_BACKUP/$LOG_FILE

# 5. Connect To Backup Server Drop, Create User And Given Permission.

su - oracle<<EOO

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=bkpdb
export PATH=$PATH:$ORACLE_HOME/bin

sqlplus /nolog << EOF
CONNECT system/system@bkpdb
SPOOL /u01/emp.lst
SET LINESIZE 100
SET PAGESIZE 50

DROP USER MICR CASCADE;

DROP USER BEFTN CASCADE;

CREATE USER MICR IDENTIFIED BY ERA123MICR
DEFAULT TABLESPACE MICR
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON CHQIMG
QUOTA UNLIMITED ON MICR
QUOTA UNLIMITED ON MICRACK
QUOTA UNLIMITED ON MICRDFT
QUOTA UNLIMITED ON MICRIMG
QUOTA UNLIMITED ON MICRIND
QUOTA UNLIMITED ON MICRINW
QUOTA UNLIMITED ON MICRLOG
QUOTA UNLIMITED ON MICROUT
QUOTA UNLIMITED ON MICRSTP
QUOTA UNLIMITED ON MICRXML;

CREATE USER BEFTN IDENTIFIED BY ERA123BEFTN
DEFAULT TABLESPACE BEFTN
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON BEFTN;

GRANT CONNECT TO MICR, BEFTN;

GRANT DBA TO MICR, BEFTN;

GRANT RESOURCE TO MICR, BEFTN;

GRANT SELECT ON SYS.V_$SESSION TO MICR, BEFTN;

GRANT EXECUTE ON SYS.DBMS_CRYPTO TO MICR,BEFTN;

GRANT SELECT ON SYS.DBA_JOBS TO MICR;

GRANT SELECT ON SYS.DBA_JOBS_RUNNING TO MICR;

GRANT READ, WRITE ON DIRECTORY MY_IMAGE_ICE TO MICR;
GRANT READ, WRITE ON DIRECTORY EFT_XML TO BEFTN;
GRANT READ, WRITE ON DIRECTORY EFT_ORE TO BEFTN;
GRANT READ, WRITE ON DIRECTORY EFT_NOC TO BEFTN;
GRANT READ, WRITE ON DIRECTORY EFT_ICE TO BEFTN;
GRANT READ, WRITE ON DIRECTORY MY_IMAGE_DIR TO MICR;
GRANT READ, WRITE ON DIRECTORY MY_DATA_DIR TO MICR;
GRANT READ, WRITE ON DIRECTORY MY_XML TO MICR;
GRANT READ, WRITE ON DIRECTORY MY_ORE TO MICR;
GRANT READ, WRITE ON DIRECTORY MY_OCE TO MICR;
GRANT READ, WRITE ON DIRECTORY BB_IRE TO MICR;
GRANT READ, WRITE ON DIRECTORY BB_ICE TO MICR;
GRANT READ, WRITE ON DIRECTORY BB_ACK TO MICR;

EXEC DBMS_JAVA.grant_permission('MICR', 'java.io.FilePermission', '<<ALLFILES>>', 'read ,write, execute, delete');
EXEC DBMS_JAVA.grant_permission('MICR','SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
EXEC DBMS_JAVA.grant_permission('MICR','SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');

SPOOL OFF
EXIT;

EOF

EOO

# 6. Set Your Environment Veritable.

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=bkpdb
export PATH=$PATH:$ORACLE_HOME/bin

# 7. Import Backup To Backup Server.


'/u01/app/oracle/product/11.2.0/db_1/bin/impdp' system/system@bkpdb DIRECTORY=DMP_BKP TABLE_EXISTS_ACTION=REPLACE DUMPFILE=$DMP_FILE LOGFILE=IMP$LOG_FILE  SCHEMAS=MICR,BEFTN;