How to take a DB Dump and Restore it in Oracle RAC 11gR2

In this post I'll explain you how to take a DB dump in Oracle with deprecated imp/exp and using oracle data pump.

First lets create a user schema.

create user yasaas identified by yasas;
grant create session, grant any privilege to yasas;
grant all privileges to yasas;

Above commands will give most of the privileges to the user that are required for DB operations. But when exporting and importing the user needs database export and import privileges. So execute the following to give necessary privileges to the user.

grant exp_full_database to yasas;
grant imp_full_database to yasas;

Note: If you do not set the above previledges it will cause the following error

ORA-31631: privileges are required

ORA-39161: Full database jobs require privileges

Now add some tables and data to the schema. And we are ready to export the schema. You should note that there are two ways to do this

• Using deprecated  imp/exp command which came with 10G
• Using Oracle Datapump

If you use the data pump you will not be able to import the same dump to a diferent oracle server.

First lets see how to export and import the dump via exp command.

Exporting via exp command

Execute the following commands to export the dp to a dump file. Folllowing are OS commands.

exp yasas/yasas@192.168.23.112:1521/rac.localdomain owner=yasas file=mydbdump.dmp log=dump_log.log

If you are executing the command from the server it self you can do it like following

exp yasas/yasas owner=yasas file=mydbdump.dmp log=export_dump.log

Note : If you want to export the entire DB you can parse the full=y parameter instead of owner=yasas and talso not that the export log will be written to the specified file.

Importing the exported dump via imp command

You can import the dump file with following command

imp yasas/yasas file=mydbdump.dmp log=import_dump.log full=y

Note : if there are multiple sachems in the dump you can use 'fromuser' and 'touser' parameters to import this, also ignore=y is also use full to ignore any errors when importing. 

Exporting via expdp command

Before exporting the dump you should set the data pump directory, all the dumps and logs will be created here. First lets create a physicaldirectory.

mkdir /home/oracle/datapump

Now login to the DB via SQLPLUS or other client and execute the following.

create or replace directory sql_data_pump as '/home/oracle/datapump';
grant read,write on DIRECTORY sql_data_pump to yasas;
grant exp_full_database to yasas;

Now lets Export the dumps. These are OS commands.

expdp yasas/yasas schemas=yasas dumpfile=mydbdump.dmp logfile=explog.log;

Importing via impdp command

As shown above for the import you need to set data pump directory.

create or replace directory sql_data_pump as '/home/oracle/datapump';
grant read,write on DIRECTORY sql_data_pump to yasas;
grant imp_full_database to yasas;

Lets import the dump now,

impdp yasas/yasas schemas=yasas dumpfile=mydbdump.dmp logfile=implog.log;

Note : If you are executing this in a windows environment you might need to add directory parameter to the above command and give the data_pump directory name that was set.

e.g : impdp yasas/yasas schemas=yasas directory=sql_data_pump dumpfile=mydbdump.dmp logfile=implog.log;

So that's it, please drop a comment if you have any queries.