How to Perform Backup and Restore Using expdp impdp Utility in Oracle 12c

YouTube video

In this article, we will guide you through the process of performing a backup and restore using the expdp/impdp utility in Oracle 12c. This utility allows you to take a logical backup of a schema and then restore it, ensuring that your data is protected in case of any issues or data loss. We will take you step-by-step through the process, explaining each step in detail. So, let’s get started!

Step 1: Creating a User and Granting Privileges

Before we begin, we need to create a user and grant the necessary privileges. In this example, we will give the user DBA privileges for testing purposes. However, in a production environment, it is not recommended to grant such privileges to any regular user. Here is the SQL code to create the user:

sql
CREATE USER test_user IDENTIFIED BY password;
GRANT DBA TO test_user;

Step 2: Importing Data into the Schema

Next, we will import some data into the created schema. We will create a table and insert a few values into it. The following SQL code demonstrates this:

sql
CREATE TABLE test_table (id NUMBER, name VARCHAR2(50));
INSERT INTO test_table VALUES (1, 'John');
INSERT INTO test_table VALUES (2, 'Jane');
COMMIT;

Now, you can verify the data in the table using the following query:

sql
SELECT * FROM test_table;

Step 3: Taking an Export Backup

Before we proceed with the restore process, we need to take a backup of the schema. We should make sure that a proper directory is created to store the export backup. Here is the SQL to create the directory:

sql
CREATE DIRECTORY export_backup AS '/path/to/backup/directory';

To perform the export backup, use the expdp command-line utility. Here is an example of the command:

sql
expdp test_user/password DIRECTORY=export_backup DUMPFILE=test_backup.dmp LOGFILE=test_backup.log SCHEMAS=test_user

This command will create an export backup file named “test_backup.dmp” and a log file named “test_backup.log” in the specified directory.

Step 4: Dropping the Schema

Now, we will simulate a scenario where the schema needs to be dropped. We will drop the previously created schema using the following SQL command:

sql
DROP USER test_user CASCADE;

After dropping the schema, if you try to access any objects belonging to the user, you will receive an error message indicating that the objects do not exist.

Step 5: Restoring the Schema from the Export Backup

To restore the schema from the export backup, we will use the impdp utility. Here is an example of the command:

sql
impdp test_user/password DIRECTORY=export_backup DUMPFILE=test_backup.dmp LOGFILE=test_restore.log

This command will import the schema and all its objects from the export backup file. The log file “test_restore.log” will provide details about the import process.

Conclusion

In this article, we have covered the process of performing a backup and restore using the expdp/impdp utility in Oracle 12c. We explained each step in detail, from creating a user and importing data to taking an export backup and restoring the schema. By following these steps, you can ensure that your data is protected and easily recoverable in case of any issues or data loss. In future articles, we will delve into more advanced topics such as refreshing schemas and moving data between different table spaces. Thank you for reading, and we hope this article was helpful!

Keywords: Oracle, Backup and Restore, expdp, impdp, Utility, Oracle 12c, DBA, Schema.

Note: This article assumes basic familiarity with Oracle database management and SQL queries.