Logical Backup (Datapump (expdp and impdp) )

Datapump (expdp and impdp) :

-->Introduced in oracle 10g

expdp help=y
impdp help=y

Advantages in Expdp and Impdp :

  1. Time Consuming Process
  2. Dumpfiles will not over written
  3. dumpfiles are in universal location
  4. can assaign a job name
  5. can stop and ongoing export operation
  6. can estimate the size of dumpfile.


  To create the directory Pump in db level and o/s level

Db level :

   sys>> create directory dpump as /disk1/oradata/ORCL/dpump
 
To drop the directory Pump

   sys>> drop directory dpump

O/S Level:
   
       $mkdir -p /disk1/oradata/ORCL/dpump

Master Process :

  -Name of the process(DMmn)
  -For a particular job only one master process will be there

Work process:

  -4 work process will be invoke in export process
  paralle=4
  PARALLEL Change the number of active workers for current job.

Theory :

  --During datapump operation a table called master table will be create to track of dp operation

  --This master table is created in the schema of the user running the import/export operation

  --this table is created by process called master process based on Job name

  --Only one master process runs per Job

  --Once the Job has finished it dumps a table content into dump files and delete the tables

  --there is another process called worker process (DWnn)

      This is the process that actually performs the work. we can have number of worket process is running in same Job

   Commands :

   expdp dumpfile=full.dmp logfile=full.log full=y directory=dpump

                   :/ as sysdba

  To Override the Dump file in the location :

     REUSE_DUMPFILES -- Overwrite destination dump file if it exists [y].
     expdp dumpfile=full.dmp logfile=full.log full=y directory=dpump reuse_dmpfiles=y

  To estimate the size of the dump
    
     ESTIMATE_ONLY -- Calculate job estimates without performing the export.
     expdp dumpfile=full.dmp logfile=full.log full=y directory=dpump estimate_only=y

  To assign the job name of the export operation :
     
     JOB_NAME -- Name of export job to create.
     expdp dumpfile=full.dmp logfile=full.log full=y directory=dpump job_name=y
  
   To Stop the Job

    Ctrl+C
    
export>Stop_job=immediate   (STOP_JOB Orderly shutdown of job execution and exits the client.)

Valid keyword values are: IMMEDIATE.

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

To resume the privious export Job

     $expdp attach=full
     To see the master table name
       
     export>> continue_client

Check the status of job name with the below command :

     sys>> select table_name from dba_tables where table_name=<jobname>



 Different types of imp and exp

    1.Full DB Backup
    2.Table Spaces Backup
    3.Users level Backup
    4.Tables Backup
    5.Query's Backup

expdp help=y gives the all parameters related to export
impdp help=y gives the all parameters related to import

1.Full DB Backup :
 
  Export Syntax :
 
   expdp dumpfile=full.dmp logfile=full.log full=y directory=dpump

            : / as sysdba

  Import Syntax
  
   impdp dumpfile=full.dmp logfile=full.log full=y directory=dpump

            : / as sysdba


 2.Table Spaces Backup
    Export Syntax :

     expdp dumpfile=ts.dmp logfile=ts.log tablespaces=<table spacename> directory=dpump

            : / as sysdba

    Import Syntax :
  
     impdump file=ts.dmp logfile=ts.log full=y directory=dpump

             : / as sysdba

3.Users level Backup
   
    Export Syntax :
      
        expdp dumpfile=usr.dmp logfile=usr.log schemas=<user name> directory=dpump

              : owner username/password
         
    
    Import Syntax :

impdp dumpfile=usr.dmp logfile=usr.log remap_schema=<export username>:<import  username> directory=dpump

              : owner username/password
         
   
4.Tables Backup

    Export Syntax :

         expdp dumpfile=table.dmp logfile=table.log tables=username.tablename1,username.tablename2 directory=dpump

               : owner username/password


                 
     Import Syntax :  
    
impdp dumpfile=table.dmp logfile=table.log remap_schema=<export username>:<import username> directory=dpump

                        : owner username/password
         
5.Query's Backup

     Export Syntax :

         expdp dumpfile=query.dmp logfile=query.log tables=username.<tablename> query=\'where deptno=30\' directory=dpump

               : owner username/password

     Import Syntax :
    
         impdp dumpfile=query.dmp logfile=query.log tables=username.<tablename> remap_schema=<export username>:<import username> directory=dpump

                                    : owner username/password

Draw backs:

1. we cannot perform the incremental backups