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');


Thanks
A Rahim Khan

Advertisements
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: