How to take backup of Oracle database Schema?

In my previous articles i have given the basic idea about multiple oracle interview questions and advanced interview questions. In this article i would like you to give the step by step process of taking the oracle database schema backup. Always there is a question – How to take backup of Oracle database? Is there any commands to take backup of Oracle database? So i would like to explain the answer of the questions above in detail. There are so many situations where we require to take backup from oracle database.

What is mean by backup ? Why it is important?

The backup is nothing but the copy of the data. What exactly the backup includes. The backup includes all the data of oracle schema or oracle database including the control files as well as the data files. There are two types of backups in oracle :

1.Full Backup of Database

The full backup of database means the backup of database with control files and data files.

2.Incremental backup

If user wants to take part of database then that backup is called as incremental backup.

The Full backup as well as incremental backup strategies are most important because u require to take backup of database on timely manner for security purpose at the time of disastrous situations.

How to take backup of Oracle Database Schema step by step :

In this section i would like to give how to take backup of oracle database stepwise in detailed steps. The database backup is nothing but the full database backup. So there are so many times requirements to take the backup of schema of oracle. The oracle schema contains the data, database objects owned by database users. In this section i would like to give you simple steps with examples so that user can take export of database schema with using the following steps.

Step 1 : Check the privileges of User and give DBA privileges to specified user

The common mistake most of the programmers are doing is they are not checking the privileges for the specified user who wants to take export of the database. User should have the Sysdba privileges to export the full data from schema.

You can use following command to check the privileges :

SELECT * FROM DBA_TAB_PRIVS where user_name= ‘User_name’;

If the user dont have privileges use following command to give correct privileges.

Grant DBA to User_Name;

These different queries are important to grant the access to the user_name.

Step 2 : Create a directory anywhere in system

The second step is to create a directory anywhere in the system where user can store the export of the database.

For this example i have created following directory.

D:\ Exporting \Data export

Step 3 : Log in to user with sys users

The third step is to log in with the sys user and check the DBA privileges.

Use following command,

C:\>  sqlplus  /  as  sysdba

It is always easy to log in direct with system user and take the export of the database.

You can create directory object and grant privileges using following commands,

SQL>CREATE DIRECTORY  Export_customer AS  ‘D:\Exporting\Data Export’;

SQL>GRANT  read,  write  ON  DIRECTORY  Export_customer TO  Customer;

Step 4 : Granting DATAPUMP_EXP_FULL_DATABASE role.

Apart from granting read and write privilege on the directory to the user we also need to grant DATAPUMP_EXP_FULL_DATABASE role to the user who wants to perform the export. 

Regarding DATPUMP_EXP_FULL_DATABASE :

1. User can specify the single schema rather than multiple schemas.
2. User can export the non schema data information with this role.

Let’s grant this role to user Customer,

SQL>GRANT  DATAPUMP_EXP_FULL_DATABASE  TO  Customer;

Step 5 : Export the schema

User can use the Expdp command to export the database schema data.

Syntax :

expdp system/******** schemas= Your_Schema_Name parallel=”Check this value with DBA” directory=Directory_Name dumpfile=Schemaname%u.dmp logfile=Schema_name.log exclude=index, cluster, indextype, materialized_view, materialized_view_log, materialized_zonemap, db_link;

Example :

If you want to export the data for Customer_Full schema use following command.

C:\> expdp  Customer/Customer@ORCL  DIRECTORY = Export_customer  DUMPFILE =exp_schm_scott.dmp  LOGFILE=scott_lg.log
 SCHEMAS = Customer_Full

The above command will export the data from Customer Schema. You can export the command.

Step 6 : Exporting the multiple schemas

If you want to export the data from multiple schemas you can use the comma in between the multiple schemas.Lets say there are multiple schemas Customer_Full and Client. You need to use the comma in between the two schemas.

C:\> expdp  Customer/Customer@ORCL  DIRECTORY = Export_customer  DUMPFILE =exp_schm_scott.dmp  LOGFILE=scott_lg.log
 SCHEMAS = Customer_Full,Client

These are methods we are using to export the data from multiple schemas.