What is logical backup?

What is logical backup?

Logical backup is nothing but taking backup of logical objects (tables, views, indexes, Constraints, stats etc) into a dump file.

There are 2 methods to perform logical backup.

1.EXPDP/IMPDP

2.EXP/IMP

EXPDP is introduced in Oracle 10g version whereas EXP is existing from previous oracle versions.

Logical backup can be prformed for tables,indexes,schemas,tablespaces and complete database. Below are the examples for performing logical backups.

Full Database Export

expdp SYSTEM/password directory=export_dir full=y dumpfile=exp_fulldb.dmp logfile=exp_fulldb.log

exp SYSTEM/password file=full.dmp log=full.log full=y

Schema Level Export

expdp SYSTEM/password  directory=export_dir schemas=scott,test dumpfile=exp_schemadb.dmp logfile=exp_schemadb.log

exp SYSTEM/password  USERID=scott/tiger OWNER=(SCOTT,ALI) FILE=exp_own.dmp

Table Level Export

expdp SYSTEM/password  directory=export_dir tables=scott.emp,test.bonus dumpfile=exp_table.dmp logfile=exp_table.log

exp SYSTEM/password  USERID=scott/tiger TABLES=(scott.emp,scott.sales) FILE=exp_tab.dmp

Now move this dumpfiles to target database, place in import_dir directory and import the data.

Once the data is exported it will be placed in directory export_dir with given filenames. the export status can be seen in log files.

Schema Level Import

impdp SYSTEM/password  directory=import_dir schemas=app_schema,testschema dumpfile=imp_schemadb.dmp logfile=imp_schemadb.log

imp SYSTEM/password  USERID=scott/tiger OWNER=(SCOTT,ALI) FILE=exp_own.dmp

Table Level Import

impdp directory=import_dir tables=scott.emp,test.bonus dumpfile=imp_tabledb.dmp logfile=imp_fulldb.log

imp SYSTEM/password FIlE=exp_tab.dmp  TABLES=(dept,emp)

Full Database Import

impdp directory=import_dir full=y dumpfile=exp_fulldb.dmp logfile=imp_fulldb.log

imp SYSTEM/password  FULL=y FIlE=full.dmp log=full.log