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.
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 :
- User can specify the single schema rather than multiple schemas.
- 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.