Logical Backup (Exp and Imp)

Backup's :

There are two types of backup's

1.Logical backup

     1.Exp & Imp
     2.Expdb & Impdb ( Datapump)

2.Physical Backup

     1.Cold Backup
     2.Hot Backup
     3.Rman

1.Logical backup

     1.Exp & Imp

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
exp help=y gives the all parameters related to export
imp help=y gives the all parameters related to import

1.Full DB Backup :

  Export Syntax :
 
   exp file=full.dmp log=full.log full=y

            : / as sysdba

  Import Syntax
  
   imp file=full.dmp log=full.log full=y

            : / as sysdba


 2.Table Spaces Backup

    Export Syntax :

     exp file=ts.dmp log=ts.log tablespaces=<table spacename>

            : / as sysdba

    Import Syntax :
  
     imp file=ts.dmp log=ts.log full=y

             : / as sysdba

3.Users level Backup
   
    Export Syntax :
      
        exp file=usr.dmp log=usr.log owner=<user name>

              : owner username/password
         
    
    Import Syntax :

        imp file=usr.dmp log=usr.log fromuser=<export username> touser=<import username>

              : owner username/password
         
   
4.Tables Backup

    Export Syntax :
         exp file=table.dmp log=table.log tables=username.tablename1,username.tablename2

               : owner username/password


                 
     Import Syntax :  
    
         imp file=table.dmp log=table.log fromuser=<export username> touser=<import username>

              : owner username/password
         
5.Query's Backup :

     Export Syntax :

         exp file query.dmp log=query.log tables=username.<tablename> query=\'where deptno=30\'

               : owner username/password

     Import Syntax :
    
         imp file=query.dmp log=query.log tables=username.<tablename> fromuser=<export username> touser=<import username> ignore=y

                 : owner username/password


  Incrementals Backup :

There are 3 types of  Incremental Backups

    1.Complete
    2.Incremental
    3.Cumulative

Complete Backup :

Syntax:

         exp file=comp.dmp log=comp.log inctype=complete

Incremental Backup: 

Syntax:

         exp file=comp.dmp log=comp.log inctype=incremental
   
Cumulative :

Syntax:

         exp file=comp.dmp log=comp.log inctype=cumulative


Drawbacks in Exp and Imp :

  1. Time Consuming Process
  2. Dumpfiles willbe over written
  3. Dumpfiles are in scattered location
  4. cannot assaign a job name
  5. cannot stop and ongoing export operation
  6. cannot estimate the size of dumpfile.