We can take backup of a single or multiple rows separately using logical backup.

Like just taking the backup of employees table with the department id of 10:

select * from employees where department_id = 10;

Back up of just this query we can take with logical backup.

Now imagine in a big industry like a huge bank. We need to shift or copy records of a single account to a different database, what then.

In those times this type of backup is used.

expdp system/oracle dirctory=expbackup dumpfile=query.dmp logfile=exp4.log tables=hr.employees query='hr.employees:"where department_id = 10"'

---

---

We can also create script to take logical backup. We are using .par or parameter file extention here. We could have also used shell script here but it would have created some problems here as expdp uses several * which can cause syntax error with shell script. Parameter file completely bypass these errors. So we are using parameter file here.

backup.par


userid=system/oracle
directory=expbackup
dumpfile=query_managers.dmp
logfile=exp_query_1.log
tables=hr.employees
query='hr.employees:"where department_id=30"'   

expdp parfile=backup.par

---

---

We can restore logical backup using ImpDp (Import Data Part) Tool. As we are restoring logical backup we can restore a single tablespace, single schema, single table, full database from the dump file.

The steps are same which is to create directory both physical and logical. We can also use the same directory we used for export (expdp), but the standard practice is to have separate directories for export and import.

mkdir /u01/imp_restore/

---

---

Copying the dump file which we will use to restore in the import directory.

cp /u01/exp_backup/schema_backup.dmp /u01/imp_restore/

---

---

create or replace directory imprestore as '/u01/imp_restore/';

grant read, write on directory imprestore to system;

---

---

impdp system/oracle directory=imprestore dumpfile=schema_backup.dmp logfile=imp1.log schemas=hr

---

---

impdp system/oracle directory=imprestore dumpfile=schema_backup.dmp logfile=imp1.log remap_schema=hr:new_hr

---

---

Now if the tablespace with its data files got deleted, then the impdp will not work as this tool can't create data file itself. It only can write on the already present data file. So the data tablespace with the same name containing the data files of same name has to be created manually before starting the restoration with impdp.

To see the data file names in the tablespace see the sql file version of the full database dump file. Dump file is a binary file so we need to convert it to sql file format to read it.

To get that:

impdp system/oracle directory=imprestore dumpfile=full_backup.dmp logfile=tbs.log sqlfile=full.sql

---

---

The sql file:

---

---

---

Currently we are dealing with the new tablespace so lets search for that one in the sql file. See we created a new tablespace and it appears at the sql file of the new full backup taken after creating the tablespace.

---

---

Now create the tablespace again with the data file with the same name and location. Now running the impdp will restore the data in the tablespace.

impdp system/oracle directory=imprestore dumpfile=tablespace.dmp logfile=imp1.log tablespaces=new

---

---

---

---

---

---

---

impdp system/oracle directory=imprestore dumpfile=fill.dmp logfile=imp1.log full=y parallel=5

---

---

---

---

There the database is restored in the new server. This is also the same steps to restore the database in the same server. See the data in the other database is also written here.

---

---

We can restore a single tablespace, and schemas and tables using the dump file of a full database backup. This because full database backup dumpfile contains the sql of every tablespace creation sql, user creation which uses that tablespace, table creation sql in that tablespace, and finally the insert sqls in that table in that tablespace. So using that dumpfile any data can be restored individually as required.

Same steps we can create the data file and tablespace, then the data in it will be restored.

---

---

---

---

The tablespace is restored with the data in it using the full database backup dump file.