EXPDP backup important parameters

Export backup is one of the great feature introduced by Oracle, we can take backup of whole database,single schema, single table or single row of the table by this utility.

Below is simple example for taking export backup of single schema/user:

expdp username@db_name DIRECTORY=directory_name DUMPFILE=dumpfile_name.dmp logfile=logfile_name.log schemas=username

We can use two different directories for dumpfile and logfile to store as shown below:

dumpfile= datapump_dir:expdp_fi_mgr_1010_tables%U.dmp
dumpfile= datapump_dir1:expdp_fi_mgr_1010_tables.log

For taking single table backup use tables parameter :

tables=schema.table1,schema.table2,schema.table3

If you wanted to export table from specific date then use below parameter:

query=table1:"where column_name > '01-JAN-2015'",table2:"where coulumn_name >= '01-JAN-2016'";

There are many parameter of expdp for taking backup. It is very important to take into consideration of expdp parameter because whatever you will export you have import into other schema or database according to user or environment requirement.

 

Say If your export is running and you need to see what all things your command is doing you can do it by following way

First find out the job name of your expdp command :

SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME             OPERATION  JOB_MODE   STATE      
---------- -------------------- ---------- ---------- ------------
SYSTEM     EXP_BACKUP             EXPORT   FULL       EXECUTING

From above EXP_BACKUP is Job_name, you can assign this name in expdp commamd or if you don’t then this name will be assigned by Oracle itself.

Now user attach parameter to see the export backup stats:

[Test@test1 ~]$ expdp attach=EXP_BACKUP
Username: / as sysdba

Export: Release 11.2.0.2.0 - Production on Tue 06 June, 2016 13:32:33
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Password: 

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

Job: EXP_BACKUP
Owner: SYSTEM                         
Operation: EXPORT                         
Creator Privs: TRUE                           
GUID: Code_here
Start Time: Tuesday, 06 June, 2016 13:32:33
Mode: FULL                           
Instance: Instance_name
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name      Parameter Value:
CLIENT_COMMAND        expdp command will display here
State: EXECUTING                      
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: dump_file.dmp path will display here
bytes written: 10,446

Worker 1 Status:
Process Name: DW00
State: EXECUTING                      
Object Schema: Username
Object Type: object_name
Completed Objects: 11
Total Objects: 11
Worker Parallelism: 1

Above will give you all details of expdp command what object it is curently exporting , how many bytes completed ,objects completed, etc.. Now it is showing only 1 Worker, if you use more parallel then that many number of Worker will increase and what they are doing will too get displayed.

Please share If you like..Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInShare on Yummly

4 Comments

Add a Comment
  1. This is exactly what I was looking for. Thanks for wrniitg!

  2. There’s definately a lot to know about this topic. I love all of the points you’ve made.

  3. I just want to tell you that I’m all new to blogging and absolutely savored you’re web blog. Most likely I’m going to bookmark your blog post . You absolutely have remarkable stories. Regards for sharing with us your web site.

Leave a Reply

Your email address will not be published. Required fields are marked *

Fariddin Shaikh's Oracle DBA Blog © 2016 Being DBA
Go Back