How to manage an oracle backup

Feb 12, 2015

1. Export

To export data from the database, use exp tool to create a dump file of the database:

$ exp USERNAME@DBNAME FILE=mydb.dmp

2. Clean up

Be aware that importation of the dump over existing database does not rollback data modifications and does not remove data that's been added after the dump. It only recover removed data.

To perform a clear rollback to the dumped data, one should first drop existing data. To clean up, first generate drop.sql file (that holds drop data instructions) and then run it on target database.

$ echo  "SELECT 'DROP ' || object_type || ' ' || object_name || ' ' ||
  CASE(object_type)
    WHEN 'TABLE'
    THEN ' CASCADE CONSTRAINTS;' ELSE ';'
  END
  FROM user_objects
  WHERE object_type IN ('TABLE','VIEW','PACKAGE','PROCEDURE','FUNCTION','SEQUENCE');
 
 
PURGE RECYCLEBIN;
 
 
exit
"  > generate_drop.sql
 
$ sqlplus USERNAME@DBNAME @generate_drop.sql  | grep -i ^drop > drop.sql ; echo exit >> drop.sql # generate drop.sql file
$ sqlplus USERNAME@DBNAME @drop.sql # drop data

3. Import

To restore from the backup dump file use imp command.

# import the dump 
$ imp USERNAME@DBNAME FILE=mydb.dmp DESTROY=y IGNORE=y
# import the dump of another account
$ imp USERNAME@DBNAME FROMUSER=OTHERUSER TOUSER=USERNAME FILE=otherdb.dmp DESTROY=y IGNORE=y # to import the dump from another database