oracle datapump backup restore in Linux
You can easily take data pump backup of an Oracle Schema in Linux environment following those easy steps. I have taken backup of SCOTT Schema and restore it again from this Backup.
However, SCOTT Schema is locked in Default Installation. You can unlock it using following sql command.
ALTER USER SCOTT ACCOUNT UNLOCK;
First of all, connect to system(Linux) as root and create a directory, change the owner to oracle and provide necessary write permission.
mkdir scott_datapump chown oracle:oinstall scott_datapump chmod g+w scott_datapump
Now, connect to Oracle server using sqlplus, grant those necessary permission to SCOTT user and system.
sqlplus /nolog conn / as sysdba; grant dba to scott; grant create any directory to scott; grant EXP_FULL_DATABASE to scott; grant IMP_FULL_DATABASE to scott; grant read,write on directory scott_datapump to system;
Again connect to oracle server using SCOTT, create directory for taking data pump backup.
sqlplus /nolog conn scott/tiger as sysdba; create directory scott_datapump as '/scott_datapump';
For taking data pump backup, connect to system(Linux) as oracle user and run the following command.
expdp scott/tiger schemas=scott directory=SCOTT_DATAPUMP dumpfile=scott_13072011.dmp logfile=scott_13072011.log
For backup restoring, connect to oracle server and drop user scott and create again to restore the backup.
sqlplus /nolog conn / as sysdba; drop user scott cascade; create user scott identified by tiger; grant resource, connect, create view to scott; grant dba to scott;
Connect to oracle server as scott and create directory.
create or replace directory scott_datapump as '/scott_datapump';
To import data pump backup, connect to system(Linux) as oracle user and run the following command.
impdp scott/tiger schemas=scott directory=scott_datapump dumpfile=scott_13072011.dmp logfile=scott_13072011.log
for user/schema remapping, execute the following command.
impdp scott/tiger schemas=scott remap_schema scott:scott directory=scott_datapump dumpfile=scott_13072011.dmp logfile=scott_13072011.log
Finally, connect to oracle server as sysdba and execute following stored procedure.
sqlplus /nolog conn / as sysdba; exec dbms_stats.gather_schema_stats('scott');
A Rahim Khan