Oracle Apps DBA Backup Procedures (HOT & COLD Both)

Oracle Apps BackUp Procedures (HOT & COLD Both)

BACKUP PROCEDURES :

It is a very important aspect of any database which should be planned carefully as recovery depends upon the back up strategy which are being followed. Backup strategy depends upon the mode of database. Different methods are adopted for the database running in archive log mode or database running in no-archive log mode .

Criteria

1. When database is running in archive-log mode:-

--> Cold backup :-  

In init.ora, search for parameter control_files to find the name of control
file for that database. Query the v$datafiles and v$logfiles views to find the names
of datafiles and redo logfiles associated with the database. Use the operating system
command to take the backup of these files. *

In init.ora, search for parameter log_archive_dest to find the location of archived files.

Use the operating system command to take the backup of these files.

Frequency: This backup has to be taken weekly.

Only Oracle database related file should be backedup. 

It will solve these problems: - 

[a] Downtime of database will be less.
[b] Retrieval from cartridge will take less time.

[c] Less numbers of cartridges will be required.

File System Backup: - 

Generally its frequency should be low. It will act as a backup for all the files (Oracle+O.S.+Other). It will be needed if all the disks crash. If you are creating important files on server then its frequency should be increased as decided by the site incharge.

--> Hot backup :-

In init.ora, search for parameter control_files to find the name of control file for that database. Query the v$datafiles views to find the names of datafiles associated with the database.

Use the operating system command to take the backup of these files.*

In init.ora, search for parameter log_archive_dest to find the location of archived files.

Use the operating system command to take the backup of these files. *

Frequency: This backup has to be taken daily.

Logical Backup: -

[1] Ideally Complete database export should be taken daily. It is also called base backup.

[2] Take incremental export daily except on weekends. On weekends, cumulative database export should be taken. When cumulative export is taken, one should remove incremental export to save space on disk. On month end, take complete database export and remove previously stored cumulative export logical backups.

[3] Take important user level export daily. Either of the above option can be implemented at the site but order of prefrence should be first try [1], if not then use [2] else last option should be [3].

 
Cartridges Strategy : -

If you are taking complete database export then use three different sets of cartridges ( Grand father , Father and Son concepts .) on three different days . And rotate these cartridges again .

For Incremental Backups use six different sets of cartridges on six different days . And rotate these cartridges again after successfully completion of Cummulative database export backup .

For Cummulative Backups use different cartridges in every week and rotate those cartridges in the next month after successfully completion of Complete database export backup.

Recovery : -

In day to day operation the most common type of failure is table drop or partial data loss in any table or instance failure. Using export backed up dump file (expdat.dmp) one can recover first two type of problems. 

For instance failure, simply restart the database, oracle will automatically recover the database (Instance recovery).

For more complicated type of problems like media crash (data file loss etc.),  Refer : -Oracle Apps Recovery Procedures 

 

 

 

2. When database is running in no archive-log mode:-

--> Cold backup : -

 
In init.ora, search for parameter control_files to find the name of control
file for that database. Query the v$datafiles and v$logfiles views to find the names
of datafiles and redo logfiles associated with the database. Use the operating system
command to take the backup of these files. Ideally this backup should be taken daily.

Logical Backup: -

[1] Ideally Complete database export should be taken daily. It is also called base backup.

[2] Take incremental export daily except on weekends. On weekends, cumulative database export should be taken. When cumulative export is taken, one should remove incremental export to save space on disk. On month end, take complete database export and remove previously stored cumulative export logical backups. 

[3] Take important user level export daily. Either of the above option can be implemented at the site but order of prefrence should be first try [1], if not then use [2] else last option should be [3]

Cartridges Strategy : -

If you are taking complete database export then use three different sets of cartridges ( Grand father , Father and Son concepts .) on three different days . And rotate these cartridges again .

For Incremental Backups use six different sets of cartridges on six different days . And rotate these cartridges again after successfully completion of Cummulative database export backup .

For Cummulative Backups use different cartridges in every week and rotate those cartridges in the next month after successfully completion of Complete database export backup.

Recovery : -

It is a very important process and it should be done very carefully. In day to day operation the most common type of failure is table drop or partial data loss in any table or instance failure. Using export backed up dump file (expdat.dmp) one can recover first two type of problems. For instance failure, simply restart the database, oracle will automatically recover the database (Instance recovery). For more complicated type of problems like media crash (data file loss etc.),  

 

Commands to be used in copying file(s) to backup device :

In Unix : 

[a] cpio -ocBv < [name of file] > [/dev/rmt0.1|/dev/rmt0]
or
find / -name [pattern] -depth -print|cpio -ocBv > [/dev/rmt0.1|/dev/rmt0]

**************

[b] tar -cvf [name of file] > [/dev/rmt0.1|/dev/rmt0]

or
tar -cvf /

Complete file system backup : -

In Unix :

[a] find / -name -depth -print|cpio -ocBv > [/dev/rmt0.1|/dev/rmt0]

or

[b] To copy all files of unix to backup device

tar -cvf /

 

Commands to be used in restoring file(s) from backup device :

 

In Window NT :

Use backup utility to copy the necessary files.

In Unix :

 [a] cpio -icBv < [/dev/rmt0.1|/dev/rmt0]
orcpio -icBv “[pattern]” < [/dev/rmt0.1|/dev/rmt0]

[b] tar -xvf < [/dev/rmt0.1|/dev/rmt0]

In Window NT :

Use restore utility to copy the necessary files from backup device.

 

 

 

Regards ,

Srini