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

Types of backups in Oracle

which types of backups we can take in Oracle?

1.Logical backup(export & import, expdp & impdp)
2.Physical backup(RMAN backup,HOT backup, COLD backup)





what is logical backup?
http://orcl11gdba.blogspot.in/2015/06/what-is-logical-backup.html

what is hot backup?
http://orcl11gdba.blogspot.in/2014/03/taking-hot-bakcup-database-in-begin.html

how to take cold backup?
http://orcl11gdba.blogspot.in/search/label/Recovery%20using%20COLD%20backup

what is rman backup?
http://orcl11gdba.blogspot.in/2014/05/taking-database-backup-using-rman-method.html

Differences between COLD and HOT backups

What are the differences between COLD and HOT backup? And the benefits associated with each?

S.NO	HOT backup	COLD backup
1	HOT backup is taken while database is in up and running state	COLD backup is taken while the database is in graceful  shut down state
2	For HOT backup database must be in archive log mode	For COLD backup database need not be in archive log mode
3	Using HOT backup we can recover the database till any point in case of disasters	Using COLD backup we can recover the database till the point where we took backup only. Later changes will be unavailable
4	Excessive redo is generated during HOT backup	Since the database need not be in archive log mode we can gain slight  performance
5	It is online backup	It is offline backup
6	HOT backup is inconsistent backup	It is Consistent backup
7	Since HOT backup don’t need downtime we can use this for a database that performs 24*7 operations	Since COLD backup needs downtime we can’t afford to use it for a database that performs 24*7 operations
8	Using HOT backup we will take Control, Redo (optional), Data files for backup.	During COLD backup we will take Control, Redo (optional), Data files for backup same as hot backup.

Related links:

Taking COLD backup:  http://orcl11gdba.blogspot.in/2014/04/recovering-database-using-cold-backup.html

Tanking HOT backup:  http://orcl11gdba.blogspot.in/2014/03/taking-hot-bakcup-database-in-begin.html

     

Taking HOT backup/ Database in begin backup mode

How to take HOT backup? What happens when HOT backup/begin backup mode started?


Hot backup is considered as online backup. this means we are going to take backup when database is in up and running condition.This is more similar to cold backup but we will take the backup by keeping database in begin backup mode.
when we put database in begin backup mode the transactions will continue but will not complete. this means checkpoint will try to update the information to datafiles but SCN in the datafile headers wont increase beyond.but the datafiles wont be freezed. So entire information will be in redo logs only. Log writer takes the full images of modified data block(data+block image. but in general case only transaction data has to be logged).because of this effect heavy redo generation will happen.
once the backup mode is ended it will continue to update the SCN in file headers.
Note:By keeping above in mind it is not suggested to to use hot backup for recovering purpose  in case of big databases that generates GB's of redo per day ; because it will generate huge amount of redo.

Steps to take HOT backup of Full database:

SQL>select log_mode from v$database;     ------make sure database in archive log mode
if the database is in no archive log mode make it to archive log mode by following http://orcl11gdba.blogspot.in/2014/03/enabling-archive-log-mode.html

SQL>alter database begin backup;
now copy the data files from one location to backup location.
cp /u01/oracle/app/oradata/pavan/database_1/*.dbf  /u01/oracle/app/oradata/pavan/database_1/HOT_BKP
once copying done then end the begin backup mode

SQL> alter database end backup;
after performing transactions take log switches to clear the pending redo.

SQL>alter system switch logfile;
now take the copy of archive logs to backup location
cp /u01/oracle/app/oradata/pavan/database_1/flash_recovery_area/database_1/archivelog/* /u01/oracle/app/oradata/pavan/database_1/HOT_BKP
now finally take the backup of control file

SQL>alter database backup controlfile to '/u01/oracle/app/oradata/pavan/database_1/HOT_BKP/control.ctl';


now we have all files taken backup and whenever DB got crashed/media failure happened we can restore the database and recover it.


For recovering database scenario using HOT backup see link http://orcl11gdba.blogspot.in/2014/03/restoring-recovering-database-using-hot.html


Cheers.....!!!!!