Backup Strategy in ORACLE

COLD BACKUP (OFFLINE)

When the Database is DOWN (No Archive Mode)
Consistent Backup
Files - Datafiles, Control files.

Backup
Step 1: Stop the Listeners
Step 2: Shutdown the database normal or immediate
Step 3: Copy the determined files (datafiles & control files) to the selected backup disk.
# cp /ora10gdata/nagios/*.* /ora10gdata/backup

Recovery
Step4: Move to copied files to the base (actual) location.
# cp /ora10gdata/backup/ *.* / ora10gdata/nagios/
Step 5: Alter database open resetlogs;


v$recover_file;


HOT BACKUP (ONLINE)

When the Database is UP (Archive Mode)
Inconsistent Backup
Files - Datafiles, Control

Convert to Archive Mode
1. Modify the pfile
i) log_archive_format=%s%arch
i) log_archive_format= /arch
2. Startup pfile=”/…” mount;
3. Alter database archivelog;
4. Alter database open;

Sql>archive loglist;


Backup (1)
Step 1: Alter system switch logfile;
Step 2: Alter database begin backup; (or) Alter tablespace TS1 begin backup;
# Select status from v$backup;
Step 3: Copy the datafiles alone belong to the tablespace to the selected backup disk.
# cp /ora10gdata/nagios/*.* /ora10gdata/backup
Step 4: Alter database end backup; (Or) Alter tablespace TS1 end backup;
Step 5: Alter system switch logfile;

Backup (2)
Step 6: Backup the control files
# alter database backup controlfile to ‘/ora10gdata/backup/ctl.bak’;
Or
# alter database backup controlfile to trace;

Recovery

Step 7: Goto the location /ora10gdata/backup and remove temp files.
rm –rf temp.*
Step 8: Copy the backup files to the original disk.
# cp /ora10gdata/backup /*.* / ora10gdata/nagios/
# cp /ora10gdata/backup /*.bak / ora10gdata/nagios/
# cp /ora10gdata/backup /*.bak / ora10gdata/nagios/ control01.ctl
# cp /ora10gdata/backup /*.bak / ora10gdata/nagios/ control02.ctl
# cp /ora10gdata/backup /*.bak / ora10gdata/nagios/ control03.ctl


Step 9: sqlplus ‘/as sysdba’
sql>Startup pfile nomount;

recover database until cancel using backup controlfile;
(Or) recover database until change SCN;
(0r) recover database until time;
Step 5: Alter database open resetlogs;