8.01800 What are the ongoing and periodic back-up facilities and how do they work
Ongoing backups are run online while there are active users in the database. Periodic backups are scheduled offline when activity is minimal. Also, full and partial backups are scheduled and they can be all online, offline, or a combination.
Backups should be taken enough times to have archives in case of data loss. However, this has to be done without interfering with production or operation times.
Scheduling backups
Two types of operations are 5X8 and 7X24 shops.
5X8 shops are available 5 days a week, 8 hours a day.
It is easier to schedule backups for these shops because the database can be shutdown when the database is not used. The frequency of backups depends on the activity in the database. Potential schedules are:
If the database is small, daily offline full backups can be performed.
If the database is large, weekly offline full backups with daily incrementals could be done.
7X24 shops must be available 7 days a week, 24 hours a day. It is difficult to plan a backup strategy because there is no downtime available. The frequency of the backup depends on the size and activity of the database. Potential schedules are:
If the database is small, perform a nightly full online backup.
If the database is large, perform an online backup every weekend and incremental online backups during the week.
For example:
Who does full daily backups?
A small business with a small database that is only used during the day
Who does online backups?
A large business that is open 24 hours a day and 7 days a week. The database is very active and can not be shutdown to perform full offline backups.
What are full offline and online backups
Full offline backup
This backup is also referred as a consistent backup. To perform this backup, the database has to be shutdown. After shutdown, the data files and control files are backed up using the operating system or a third party backup software. This is the recommended backup because the database is at a fixed state and no changes are done to the database. This backup is done at the late hours and when activity is minimal.
Full online backup
This backup is done when the database is running. This is
accomplished by setting the data files in backup mode, backing up the data
files, and then removing the backup mode from the data files. Also, the redo
log files are
archived and backed up.
How are the online and offline backups done?
To perform these tasks, there are graphical utilities and commands such as Oracle’s Backup Manager and the Export command. Also, SQL*PLUS can be used for backups.
The following process can be used to backup the database using conventional operating system utilities. Once the backup files are created to disk, they can be stored to tape using commands such as NT backup or UNIX tar.
Offline backup
Shutdown database
Backup data files and control files
Restart the database
Online backup
Database continues running
Data is set to backup mode, ALTER TABLESPACE tablespace_name BEGIN BACKUP
Perform backup
Data is removed from backup mode
Process is repeated for other data sections of the database. For example, the first backup could be done for accounting and the next for the marketing data.
What files are backed up during an offline and online backup?
For an offline backup
All data files
All control files
All online redo logs
Init.ora file which contains information about how to start an Oracle database.
For an online backup
For Oracle, all these files are backed up from Oracle’s SQL*Plus. Each is placed in backup mode with and SQL command, backed-up, then the backup mode is removed. After this, a checkpoint is performed to synchronize unwritten buffers to disk.
System dbs1.dbf
Users users.dbf
Temp temp.dbf
Rollback rbs1.dbf
Tools tools.dbf
How do redo log files make online backups possible?
Redo log files
Are used to store changes to the databases. Transactions are written to the redo log before a commit. When a redo log file is full, the next redo log file is used. The Log Writer LGWR continues with the top redo log file once the bottom redo log file is full.
Archive redo log files
Archive redo log files are the files that are backed up during an online backup. They are identical to the online redo log files. They are created when the online redo log process is stopped.
Backup of redo logs steps
Temporarily stop the archiving process to prevent the writing to the redo log files.
Record the name of the archived redo log files.
Restart the archiving process
Backup the archived redo log files and delete them
Checkpoint
Checkpoint is done in a regular basis to ensure that data blocks in memory that changed, since the previous checkpoint, are written to disk. The checkpoint needs a redo log file to complete. However, during the archiving of the redo log, the redo log is not available. This causes the checkpoint to be suspended or to not complete. At this time, the database is also suspended temporarily, and once the archive redo log is done, the redo log becomes available for the checkpoint. Finally, a fast checkpoint is performed to complete as soon as possible and continue normal operation.
References:
Oracle in a Nutshell, A Desktop Quick Reference, O’Reilly, Rick Greenwald & David C. Kreines, December 2002, First Edition, Part III Tools and Utilities, 15. Backup and Recovery
Oracle8i A Beginner’s Guide, Oracle Press, Michael Abbey, Michael J. Corey, Ian Abramson, 1999, 16 Backup and Recovery
Oracle8 teach yourself in 21 days, Sams Publishing, Edward Whalen, Steve Adrien DeLuca, 1998 First Edition, Week 3 at a Glance, 16 Understanding Effective Backup Techniques
Oracle8 Server, Release 8.0.5, Administration, Backup and Recovery, What are you backing up - Manual
Oracle8 DBA Handbook, Oracle Press, Kevin Loney, 1998, 10 Optimal Backup and Recovery Procedures
Dr. Peeter Kirs Database Adminstration Power Point presentation, MIT 5314