Logical Backup:

Taking backup in sql format. In logical backup we can take the backup of a schema, table, tablespace, a single row etc. In physical backup it takes the backup block by block i,e the backup of files but in logical backup it takes the backup in sql format i,e from the creating of the database to the now all the sql statements executed on the database will be saved as a backup.

Logical backup's size will be less than physical backup instead of the actual files the sql statements are getting stored as backup.

It doesn't store the actual data but stores all the insert, update, delete, grant, revoke, alter etc statements run on the database. So the database can be recovered using those statements.

Using ExpDp (Export Data pump), Restoration using ImpDp( Import Data Pump)

Creating the directories:

Physical:

mkdir /u01/exp_backup

---

---

Logical:

create directory expbackup as '/u01/exp_backup';

We created the physical directory but we need a logical directory in the database to start the backup.

---

---

Granting read write access to an user:

grant read, write on directory expbackup to system;

---

---

dba_directories: A view to see the logical directories in the database.

---

---

---

Now use the tool to create a backup.

Full Database Backup:

Command: expdp system/oracle directory=expbackup dumpfile=full_backup.dmp logfile=exp.log full=y parallel=4

Process:

---

---

---

---

In the location:

---

A schema:

expdp system/oracle directory=expbackup dumpfile=schema_backup.dmp logfile=exp1.log schemas=hr parallel=4

---

---

A table:

expdp system/oracle directory=expbackup dumpfile=table_backup.dmp logfile=exp2.log tables=hr.employees parallel=4

or we can also take backup of multiple tables.

expdp system/oracle directory=expbackup dumpfile=table_backup.dmp logfile=exp2.log tables=hr.employees,hr.location parallel=4

---

---

A tablespace:

expdp system/oracle directory=expbackup dumpfile=tablespace_backup.dmp logfile=exp4.log tablespaces=users parallel=4

---