Backup and restore of Database Schema using oracle Export Utility

This article explains the backup and restore of an Oracle database schema

Preparing for backup and restore

1. SQL> connect sys/password as sysdba
Connected.
2. SQL> grant create any directory to user;
Grant succeeded.
3.SQL> grant EXP_FULL_DATABASE to user;
Grant succeeded.
4. SQL> grant IMP_FULL_DATABASE to user;
Grant succeeded.
5. SQL> exit

Now logina s user and create the directory

SQL> create or replace directory MyDir as '/u1/oracle'

Now MyDir can be used to export and import the database dumps

Note:  Create a directory structure in the system before executing the export command. Otherwise, "invalid file operation at SYS.UTL.FILE." error will be thrown

Oracle Backup (expdp)

USe the below command to export the database schema to the particular directory

[oracle]$ expdp user/password schemas=user directory=MyDir dumpfile=DemoExport.dmp logfile=User_01232.log

Oracle Restore (impdp)

Create the directory where the dumps are exported
Create the user tablespace

Now run the import command again

$ impdp user/****** schemas=user directory=MyDir dumpfile=DemoImport.DMP logfile=User_01233.log

Thus, backup and restore of schema is done succesfully