User Managed Backup --- Oracle

User Managed Backup

1. Physical Backup

1. Cold Backup (Consistent Backup)
2. Hot Backup (Inconsistent Backup)

2. Logical Backup

1. Export / Import
2. Expdp /Impdp (available on oracle10g onwards)

Now we see the physical backup.


Cold Backup: (Consistent Backup)

A cold (or off-line) backup is a backup performed while the database is off-line and unavailable to its users. Cold backups can be taken regardless if the database is in ARCHIVELOG or NOARCHIVELOG mode.It is easier to restore from off-line backups as no recovery (from archived logs) would be required to make the database consistent.

Why you say cold backup is consistent backup?


After shutdown the database we have taken the cold backup. During this time all datafile headers SCN are same. When we restore the cold backup, no redo is needed in case of recovery.We had taken backup datafile, logfile, controlfile, parameter file & password file.

Cold Backup Steps:

1. Get the file path information using below query

Select name from v$datafile;
select member from v$logfile;
select name from v$controlfile;

2.Taken the password file & parameter file backup ( Optional)
3.Alter system switch logfile;
4.Shutdown immediate
5.Copy all the data files /control files /log files using os command & placed in backup path.
6.Startup the database.

Hot Backup (Inconsistent backup)

A hot (or on-line) backup is a backup performed while the database is open and available for use (read and write activity). Except for Oracle exports, one can only do on-line backups when the database is ARCHIVELOG mode.

Why you say Hot backup is inconsistent backup?

While database running we have taken the hot backup. During this time backup tablespace datafile headers SCN are not same with another tablespace datafiles. When we restore the hot backup, redo is needed for recovery.

Hot Backup Steps:

1.Get the file path information using below query.
Select tablespace_name, file_name from dba_data_files order by 1;

2. Put the tablespace in begin backup mode Using os command to copy the datafiles belongs to begin backup mode tablespace & placed in backup path.
3.Once copied the datafile, release the tablespace from begin backup mode to end backup
4.Repeat the steps 1-3 for all your tablespaces.
5.Taken the controlfile backup

Alter Database backup controlfile to ‘/u01/backup/control01.ctl’; ---> à Binary format

Below one is readable format. (Used for cloning)

Alter Database backup controlfile to trace;
Alter database backup controlfile to trace as ‘/u01/backup/control01.ora’;

6.Backup all your archive log files between the previous backup and the new backup as well.
7.Taken the password file & parameter file backup ( Optional)

Example:
steps:

1.Alter tablespace system begin backup;
To ensure the begin backup mode tablespace using below query
Select * from v$backup; (refer the Change#, Time column)

2. Host cp /u01/oracle/raja/system1.DBF /u01/backup/system01.dbf à using os command to copy the datafile.
3. Alter tablespace system end backup;
To ensure the begin backup mode tablespace using below query
Select * from v$backup;