Backup and recovery of Oracle databases

When we use a database, we always hope that the database content is reliable and correct, but due to computer system faults (hardware faults, software faults, network faults, process faults and system faults) the operation of the database system is affected, the correctness of the data in the database is affected, or even the database is damaged, so that all or part of the data in the database is lost. Therefore, when

When we use a database, we always hope that the database content is reliable and correct, but due to computer system faults (hardware faults, software faults, network faults, process faults and system faults) the operation of the database system is affected, the correctness of the data in the database is affected, or even the database is damaged, so that all or part of the data in the database is lost. Therefore, when

When we use a database, we always hope that the database content is reliable and correct, but due to computer system faults (hardware faults, software faults, network faults, process faults and system faults) the operation of the database system is affected, the correctness of the data in the database is affected, or even the database is damaged, so that all or part of the data in the database is lost. Therefore, when the above fault occurs, we hope to re-establish a complete database, which is called database recovery. The restoration subsystem is an important part of the database management system. The recovery process changes with the structure affected by the fault type.

I. Restore the structure used by the database

Oracle databases use several structures to protect data that may fail: database backup, logs, rollback segments, and control files.

Database Backup is composed of the operating system backup that makes up the physical files of the Oracle database. When a media fault occurs, the database is restored and the destroyed data files or control files are recovered using backup files.

Logs. Each Oracle database instance is provided to record all modifications made in the database. An instance log consists of at least two log files. When the instance fails or the media fails, the database is partially restored. Changes in the Database Log are applied to the data file, modify the database data to the time when a fault occurs. Database logs are composed of two parts: Online logs and archived logs.

Each running Oracle database instance has an online log, which works with the Oracle background process LGWR and immediately records all modifications made to the instance. Online logs are composed of two or more files that are expected to be allocated and used cyclically.

Archive logs are optional. Once an Oracle database instance is filled with online logs, an archive file of online logs can be formed. Archived online log files are uniquely identified and merged into archived logs.

The rollback segment is used to store the old value of the modified value of ongoing transactions (for uncommitted transactions). This information is used to cancel any uncommitted modifications during database recovery.

Control files are generally used to store the status of the physical structure of a database. Some status information in the control file is used to guide Oracle during instance recovery and media recovery.

Ii. Online logs

Each instance of an Oracle database has an associated online log. An online log consists of multiple online log files. The online log file is filled with log items. The data recorded in the log items is used to reconstruct all modifications made to the database. The background process LGWR writes online log files cyclically. When the current online log file is full, LGWR is written to the next online log file. It can be used when the checkpoint of the last available online log file has been completed. If archiving is not implemented, a filled online log file can be used once the checkpoint containing the online log file is completed. At any time, only one online log file is written into the storage log item, which is called an active or current online log file. Other online log files are inactive online log files.

Oracle stops writing an online log file and starts writing it to another online log file. The log switch appears when the current online log file is fully filled and must be written to the next online log file. You can also force the log switch by DBA. When each log switch appears, each online log file is assigned a new log serial number. If an online log file is archived, it contains its log serial number.

The Oracle background process DBWR writes all the modified database buffers (including submitted and uncommitted) in the SGA to the data file. Such an event is called a checkpoint. Check Points are implemented for the following reasons:

◆ Check points ensure that data segments frequently changed in the memory are written to the data file at intervals. Because DBWR uses the least recently used algorithm, the frequently modified data segment is never used as the least recently used block. If the checkpoint does not appear, it will never be written to the disk.

◆ As all database modifications have been recorded to the data file until the check point, the log items that come before the checkpoint no longer need to be applied to data files when the instance is restored, so the checkpoint can speed up instance recovery.

Although the checkpoint has some overhead, Oracle does not stop the activity and does not affect the current transaction. Because DBWR constantly writes the database buffer to the disk, a checkpoint does not have to write many data blocks at a time. One checkpoint ensures that all data blocks modified since the previous checkpoint are written to the disk. The checkpoint always appears no matter whether the filled online log file is archived or not. If archiving is implemented, the checkpoint must be completed and the filled online log files must be archived before LGWR can reuse the online log files.

A Checkpoint can appear on all data files of a database (called a database checkpoint) or on a specified data file. The following describes when checkpoints occur and what the situation is:

◆ A database checkpoint is automatically displayed at each log switch. If the previous database checkpoint is being processed, the checkpoint implemented by the log switch is better than the current checkpoint.

◆ Set the database CHECKPOINT implemented by the initialization parameter according to LOG-CHECKPOINT-INTERVAL. When the number of predefined LOG blocks is filled (since the last database CHECKPOINT), a Database CHECKPOINT is implemented. Another parameter LOG-CHECKPOINT-TIMEOUT can be set to implement a database checkpoint after a specified number of seconds since the previous database checkpoint starts. This option is useful when a very large log file is used. It adds checkpoints between the beginning of the log. The database checkpoint started by the initialization parameter can be started only after the previous checkpoint is completed.

◆ When an online tablespace starts backup, only one checkpoint is implemented for the data files that constitute the space. This checkpoint is overwhelmed by any ongoing checkpoint.

◆ When DBA takes a tablespace offline, it only implements a checkpoint for the online files that constitute the tablespace.

◆ When DBA closes an instance normally or immediately, Oracle implements a database checkpoint before the instance is closed. This checkpoint prevails over any operation checkpoint.

◆ DBA may require a database checkpoint, which prevails over any operation checkpoint.

Check Point mechanism: when the check point is displayed, the background process of the Check Point remembers the location of the next log line written to the online file, the database write background process is notified to write the modified database buffer in SGA to the data file on the disk. Then, CKPT modifies the headers of all control files and data files to reflect the last checkpoint. When the checkpoint does not occur, DBWR only writes the least recently used database buffer to the disk when necessary to prepare a buffer for the new data.

Image online Log File: to securely mirror the instance's online log file to its online Log File ORACLE, the image function is provided. When an image online log file is available, LGWR simultaneously writes the same log information to multiple identical online log files. Log files are divided into groups. The log files in each group are called members. All the members in each group are active at the same time and are assigned the same log serial number by LGWR. If you use image online logs, you can create an online log file group. Each member in the group must be of the same size.

Image online Log Mechanism: LGWR always looks for all the members in the group, writes all the members in the group in parallel, converts them to all the Members in the next group, and writes them in parallel.

Each database instance has its own online log group. These online log groups can be mirrored or not, known as online log clues of instances. In a typical configuration, a database instance accesses an ORACLE database, so only one clue exists. However, in an ORACLE Parallel Server, two or more instances access a single database in parallel. In this case, each instance has its own clue.

Iii. Archiving logs

When Oracle wants to archive a filled online log file group, it must create an archive log, or an offline log. It is useful for database backup and recovery:

◆ Database backup and online and archive log files Ensure that all committed transactions can be recovered in case of operating system or disk faults.

◆ When the database is opened and used by the normal system, if the archived logs are permanently retained, online backup can be performed and used.

If your database requires that no data is lost in any disk fault event, archive logs must exist. The DBA may need to perform additional management operations to archive existing online log files.

Archiving mechanism: It depends on archiving settings. the archive of online log groups can be automatically archived by Oracle background process ARCH or manually archived by user process issuing statements. When the log group changes to inactive and the log switch points to the next group, ARCH can archive a group to access any or all members of the group and complete the archive group. Online log files can be reused for LGWR only after being archived. When using archive, you must specify the archive target to point to a storage device, which is different from a device with data files, online log files, and control files, ideally, archive log files are permanently moved to offline storage devices, such as tapes.

Databases can run in NOARCHIVELOG or ARCHIVELOG modes. When a database is used in NOARCHIVELOG mode, online logs cannot be archived. The Database Control file specifies that the filled group does not need to be archived, so when the filled group is active, the group can be reused by LGWR after the checkpoint of the log switch is completed. This method can only protect database instance faults, but cannot protect medium (Disk) faults. You can use the information stored in online logs to recover instance faults.

If the database is in ARCHIVELOG mode, you can archive online logs. Specify in the control file that the filled Log File Group cannot be reused before archiving. Once the group is not active, the archiving process can immediately use the group.

When the instance starts, the ARCH process can be started by setting the parameter LOG-ARCHIVE-START. Otherwise, the ARCH process cannot be started when the instance starts. However, DBA can start or stop automatic archiving interactively. Once the online log file group becomes inactive, the ARCH process automatically archives it.

If the database runs in ARCHIVELOG mode, DBAs can manually archive non-active Log File groups, whether automatic archiving or not.

Iv. Database Backup

No matter what backup or recovery mode is designed for the ORACLE database, operating system backup for database data files, log files, and control files is absolutely required. It is part of the policy to protect medium faults. Operating System Backup has full backup and partial backup:

◆ Full backup

A full backup will constitute an operating system backup for All database files, online log files, and control files of the ORACLE database. A full backup is performed after the database is shut down normally and cannot be performed after the instance fails. At this time, all the files that constitute the database are closed and consistent with the current point. The database cannot be fully backed up when it is opened. Data files obtained from full backup are useful in any media recovery mode.

◆ Partial backup

Some backups are any operating system backups except full backups, which can be enabled or disabled in the database. For example, all data file backups, single data file backups, and control file backups in a single tablespace. Some backups are only useful for running databases in ARCHIVELOG mode. Because of the archived logs, data files can be recovered from some backups. The recovery process is consistent with other parts of the database.

V. database recovery

◆ Instance fault recovery

When an instance fails unexpectedly (such as power failure or background process failure) or unexpectedly (issue a shutdoum abort statement), The instance must be restored. When the instance is restored, the database is restored to the transaction consistency state before the first fault. If an instance fault is found in the online backup, the media must be restored. In other cases, Oracle automatically performs instance recovery when the next database is started (new instances are assembled and opened. If needed, the instance is automatically restarted from the assembly status to the open status, which is handled:

(1) Roll Forward the data that is not recorded in the recovered data file. This data is recorded in online logs, including restoring the content of the rollback segment.

(2) Roll Back uncommitted transactions and regenerate the operation specified by the rollback segment in step 1.

(3) release the resources that are being processed by the firm at the time of the fault.

(4) resolves any pending distributed transactions that are being committed in a phase at the time of failure.

◆ Medium fault recovery

A media fault occurs when a file or part of a file or a disk cannot be read or written. There are two types of media fault recovery, which are determined by the archiving method of database operation.

◆ If the database is runable, so that its online logs can only be reused but cannot be archived, the media is restored to the simple recovery with the latest full backup. Manual re-execution must be performed in full backup mode.

◆ If the database can run, its online logs are archived, and the restoration of the media fault is an actual restoration process, restructured the damaged database and restored it to a specified transaction consistent state before the media failure.

In either case, the restoration of a media fault always restores the entire database to a transaction consistent state before the fault. If the database is running in ARCHIVELOG mode, different types of media can be restored: Full media recovery and incomplete media recovery.

All lost modifications can be restored after full media recovery. It is only possible when all necessary logs are available. Different types of complete media recovery are available, which determines the availability of file destruction and databases. Example:

◆ Disable database recovery. When the database can be assembled but closed, it cannot be used normally at all. In this case, you can recover all or a single medium that destroys data files.

◆ Enable recovery of the offline tablespace of the database. When the database is opened, full media recovery can be processed. The tables in the database that are not damaged can be used online, while the space that is captured by loss is offline. All the data files are used as recovery units.

◆ Recover a single data file between offline tables of the database. When the database is opened, full media recovery can be processed. The tables in the database that are not damaged are available online, while the tables that are damaged are offline. The data files that are damaged by the specified tablespace can be recovered.

◆ Use the full media recovery of the backup control file. When all copies of the control file are damaged due to disk failure, the media can be restored without data loss.

Incomplete media recovery refers to media recovery when full media recovery is impossible or not required. Restructured the damaged database to restore a transaction consistency state before a media fault or before a user error. Incomplete media recovery has different types of use, depending on the need for incomplete media recovery. The following types are available: undo, time-based, and modification-based Incomplete recovery.

Revocation-based recovery: in some cases, incomplete media recovery must be controlled, and DBA can cancel the operation at the specified point. Revocation-based recovery has been damaged by media faults in one or more log groups (online or archived) and cannot be used in the recovery process. Therefore, media recovery must be controlled, as a result, the recovery operation is aborted after the latest and undamaged log groups are used for data files.

Time-based and modification-based recovery: If the DBA wants to restore to a specified point in the past, the restoration of incomplete media is ideal. It can be used in the following situations:

◆ When the user accidentally deletes a table and notices the estimated time of incorrect submission, the DBA can immediately shut down the database and restore it to the time before the user's error.

◆ Due to system failure, part of an online log file is damaged, so the active log file is suddenly unavailable and the instance is suspended. In this case, media recovery is required. You can use the intact part of the current online log file for restoration. DBA uses time-based recovery to stop the restoration process once valid Online logs have been applied to the data file.

In both cases, the end point of incomplete media recovery can be specified by the time point or system modification number (SCN.

Http://tech.ccidnet.com/art/11217/20070926/1225173_1.html