1. What types of failures can occur?
Data loss can occur for various reasons. Here are some of the most common types.
Statement failure (No Recovery Required)
Logical failure in the handling statement in a program. For example, a user issues a statement that is not a valid SQL construction. When occurs, Oracle automatically undoes any effects of the statement and returns control to the user.
Process failure (No Recovery Required)
Failure in a user process accessing Oracle, i.e., an abnormal disconnection or process termination. If the user process fails while modifying the database, Oracle background processes undo the effects of uncommitted transactions.
Instance failure (No Recovery Required)
Problem that prevents an Oracle instance, i.e., the SGA and background processes, from continuing to function. Instance failure can result from a hardware problem such as a power outage, or a software problem such as an operating system crash. Recover automatically at next startup.
User or application error (Recovery Required)
A user mistake that results in the loss of data. For example deletion of payroll table. Such user errors can require the database or object to be recovered to a point in time before the error occurred. To allow recovery from user error and accommodate other unique recovery requirements, Oracle provides Flashback Technology.
Media failure (Recovery Required)
A physical problem that arises when Oracle tries to write or read a file that is required to operate the database. A common example is a disk head crash that causes the loss of all data on a disk drive.
2. What information should be backed up?
A database contains a wide variety of types of data. When developing backup strategy, DBAs must decide what information they want to copy.
Backups can be combined in a variety of ways. For example, a DBA can decide to take weekly whole database backups, to ensure a relatively current copy of original database information, but take daily backups of the most accessed tablespaces. The DBA can also multiplex the all important control file and archived redo log as an additional safeguard.
3. Which backup method should be used?
Oracle provides users a choice of several basic methods for making backups. The methods include:
- Recovery Manager (RMAN) - A oracle client that establishes a connection with a server process and automates the movement of data for backup and recovery operations.
- Oracle Enterprise Manager - A GUI interface that invokes Recovery Manager.
- Oracle Data Pump - The utility makes logical backups by writing data from an Oracle database to operating system files. This data can later be imported into a database.
- User Managed - The database is backed up manually by executing commands specific to the user's operating system.
3.1. Making Recovery Manager Backups
Recovery Manager (RMAN) is a powerful program that allows users to make an RMAN backup or image copy of their data. When the user specifies files or archived logs using the RMAN BACKUP command, By default RMAN creates a backup set as output.
When a RMAN command is issued, such as backup or restore, Recovery Manager establishes a connection to an Oracle server process. The server process then back up the specified data-file, control file, or archived log from the target database.
The recovery catalog is a central repository containing a variety of information useful for backup and recovery. RMAN automatically establishes the names and locations of all the files needed to back up.
Recovery Manager also supports incremental backups - backups of only those blocks that have changed since a previous backup. In traditional backup methods, all the data-blocks ever used in a data-file must be backed up.
RMAN Backup Destinations: Disk and Media Managers
- RMAN can create and manage backups on disk and on tape, back up backups originally created on disk to tape
- Devices used for tape backup are often referred to as SBT (System Backup to Tape) devices.
- RMAN interacts with SBT devices through software known as a media management layer, or media manager.
The Flash Recovery Area
The Automatic Disk-Based Backup and Recovery feature simplifies managing disk space and files related to backup and recovery, by managing all backup and recovery related files in a flash recovery area. You set the flash recovery area location and size on disk, using the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE initialization parameters. You also specify a retention policy that dictates when backups may be discarded. RMAN then manages your backup storage, deleting obsolete backups and backups already copied to tape when space is needed, but keeping as many backups on disk as space permits. This minimizes restores from tape during data recovery operations to shorten restore and recovery times.
3.2. Oracle Enterprise Manager
Although Recovery Manager is commonly used as a command-line utility, the Backup Wizards in Oracle Enterprise Manager is the GUI interface that enables backup and recovery via a point-and-click method.
Oracle Enterprise Manager (EM) supports Backup and Recovery features commonly used by users.
- Backup Configurations to customize and save commonly used configurations for repeated use
- Backup and Recovery wizards to walk the user through the steps of creating a backup script and submitting it as a scheduled job
- Backup Job Library to save commonly used Backup jobs that can be retrieved and applied to multiple targets
- Backup Management to view and perform maintenance on RMAN backups.
3.3. Using the Data Pump for Supplemental Backup Protection
Physical backups can be supplemented by using the Data Pump utility to make logical backups of data. Logical backups store information about the schema objects created for a database. Data Pump writes data from a database into Oracle files in a proprietary format, which can then be imported into a database using the Import utility.
3.4. User Managed Backups
Operating system commands can be used such as the UNIX dd or tar command to make backups. Backup operations can also be automated by writing scripts. The user can make a backup of the whole database at once or back up individual tablespaces, datafiles, control files, or archived logs. A whole database backup can be supplemented with backups of individual tablespaces, datafiles, control files, and archived logs. O/S commands can also be used to perform these backups if the database is down or if the database is placed into hot backup mode to take an online backup.
4. Should backups be made online or offline?
Online backups or offline backups can be made using either Recovery Manager or O/S commands. An online backup, also known as an open backup, is a backup of one or more database files that is made while the database is open. An offline backup, also known as a closed backup, is a backup of one or more database files that made after the database has been closed cleanly. If the database must be open and available all the time, then online backups are the only option. If there is a time of little or no activity on the database, then the user may decide to take periodic offline backups of the whole database.
5. How often should backups be made?
A backup strategy should be tailored to company needs. For example, if the company can afford to lose data in the event of a disk failure, backups may not need to be performed very often. The advantage of taking infrequent backups is that the user frees Oracle’s resources for other operations.
The disadvantage is the company may end up either losing data or recovering data but spending a long time doing so.
If the database must be available twenty-four hours a day, seven days a week, it should be backed up frequently. Otherwise, a disaster can wipe out the database and destroy the business — or at least cause a serious setback. In this case, it may be necessary to take daily backups, multiplex online redo logs, and archive redo logs to several different locations. The user can even maintain a standby database in a different city that is a constantly updated replica of the original database.
6. How can dangerous backup techniques be avoided?
Although it may seem that online redo logs should be backed up along with the datafiles and control file, this technique is dangerous. Online redo logs should not be backed up for the following reasons:
- If the database is in ARCHIVELOG mode, ARCH is already archiving the redo logs.
- Online logs can be protected against media failure by multiplexing them, i.e., having multiple log members per group, on different disks and disk controllers.
- If the database is in NOARCHIVELOG mode, the only type of backups that should be performed are closed, consistent, whole database backups. The files in this type of backup are all consistent and do not need recovery, so the online logs are not needed.
Backing up online redo logs can be dangerous since the user may accidentally restore them. There are a number of situations where restoring the online logs can cause significant problems in the recovery process. For example, it’s easy to make a simple mistake when a crisis occurs. When restoring the whole database, it is possible to accidentally restore the online redo logs, thus overwriting the current logs with older, useless backups. This action forces the DBA to perform an incomplete recovery instead of the intended complete recovery, thereby losing the ability to recover valuable transactions.
7. Should a standby database be created?
A standby database maintains a duplicate, or standby copy of the primary (also known as production) database and provides continued primary database availability in the event of a disaster. A standby database is constantly in recovery mode. If a disaster occurs, the standby database can be taken out of recovery mode and activated for online use.
A standby database is intended for recovery of the primary database. However, with Oracle8i, the standby database can be opened in query mode so batch reporting can be offloaded from the primary site. Once the standby database is activated after disasters, it cannot be returned to standby recovery mode unless it is re-created as another standby database.
Warning: Activating a standby database resets the online logs of the standby database. Therefore, after activation, the logs from the standby database and production database are incompatible. The user must place the data files, log files, and control files of the primary and standby databases on separate physical media. Therefore, it is impossible to use the same control file for both the primary and standby databases.
Index of Introduction to backup and recovery article.
- Types of Backup
- Key data structures
- Common questions, should know before starting backup and recovery process
- Types of Error and Failures
- What information should be backed up?
- Which backup method should be used?
- Should backups be made online or offline?
- How often should backups be made?
- How can dangerous backup techniques be avoided?
- Should a standby database be created?
- Understanding basic recovery strategy
| Related Articles | Latest Articles |