Concept of Oracle backup recovery

-- ====================================

-- Oracle backup recovery concept

-- ====================================

During database maintenance, backup or recovery is the top priority. Although the database system runs slowly in many cases, it is clear that the cost of the latter is the loss of database data.

It is self-evident. Therefore, it is the minimum requirement for DBA to improve the system performance when data is not lost. We will not go into details about what backup and recovery are.

I. Physical backup and logical backup

Physical backup

It is a copy of all physical files, such as data files, control files, and archiving logs. This instinct is stored on a local disk or tape.

Physical backup is the basis for backup or recovery

Including cold backup (non-archive mode) or hot backup (archive Mode)

Logical backup

Use Oracle's export and other tools to export data such as tables and stored procedures to binary files, and then use the import tool to import data to the database as needed.

Logical backup is a supplement to the physical backup method, which is mostly used for data migration.

Ii. backup and recovery tools

1. RMAN is used for backup and recovery. It supports command line and GUI interfaces and third-party tape library backup.

Supports backing up databases, tablespaces, data files, control files, and archiving logs.

Frequently Used Backup Recovery scripts can be saved

Supports Incremental backup, skipping unused blocks, and controlling backup speed

Detect corrupted data during Backup

Improve backup performance through automatic concurrency and restrictions on I/O

2. User-managed backup and recovery is a manual backup and recovery method. Use the operating system commands and SQL * plus to complete related backup and recovery.

Iii. backup and recovery policies

1. Multiplexing Control Files and multiple concurrent backups

2. multiplexing online redo log files

3. Run the database in ARCHIVELOG mode and archive the redo logs to multiple locations.

4. Always back up physical data files and create as many copies as possible to a reliable location

Iv. Important data structures for backup and recovery

1. Data Files

2. Online redo log files

3. Control File

4. Undo automatically managed

5. Optional backup files (parameter files and password files)

V. Common Backup Types

Online Database Backup --> archivelog mode, inconsistent SCN

Offline database backup --> Use noarchivelog mode, and ensure consistent SCN

The entire database --> can be backed up in different time periods to reduce the I/O pressure, thus building the entire database

Tablespace --> In archivelog mode, when it is in noarchivelog mode, the tablespace must be read-only or offline

Data File --> same tablespace backup

Control file --> you can use SQL statements or RMAN to back up data.

Archive logs

Parameter file

Vi. Backup Classification

The concepts of logical backup and physical backup are mentioned above. The following describes the re-classification based on the backup content, size, and nature.

1. Full backup and partial backup

Full backup: contains all data files and at least one control file, parameter file, and password file.

Partial backup: contains zero or multiple tablespaces, zero or multiple data files, and may contain control files. Some backups are only valid in archive mode.

2. Complete backup and Incremental Backup

Full backup: a complete copy of one or more data files, including data blocks from the beginning of the backup.

Incremental Backup: contains the data blocks that have been modified or added since the last backup.

It can also be divided

Incremental difference: all changed data blocks since the backup of the backup level and the backup level. incremental difference is the default Incremental backup mode.

Accumulative increment: all changed blocks since backup

Several forms of Incremental Backup

Level 0 Incremental Backup: it is the basis of all backups and is a complete backup that contains all data blocks.

Level 1 incremental differential backup: contains the data blocks changed since the last level 1 Cumulative backup or differential backup

Level 1 Cumulative Incremental Backup: contains only the data blocks changed since the last level 0 backup

Incremental Backup supports archivelog and noarchivelog modes. It can also be enabled or disabled. However, only RMAN can implement Incremental backup.

3. offline backup and online backup

Offline backup: backup that occurs when the database is closed, also known as consistent backup or cold backup. After the database is shut down consistently, the control file SCN is consistent with the data file header SCN.

Online backup: backup that occurs during database usage, also known as non-consistent backup or hot backup. Online backup of a data file is not synchronized with any specific SCN and Control Files

It can be either full backup or partial backup, which can be completed using RMAN or operating system commands

Only in archivelog Mode

4. Image copies and backup Sets

Image copy: a complete copy of a file without any compression. Each byte is the same as the source file. Neither Incremental Backup nor tape backup is supported.

Backup set: A logical structure consisting of one or more physical files called piece. Backup files can be data files, control files, and archive log files.

Supports data compression and Incremental backup.

You can back up data to a disk or tape.

VII. Restoration and recovery

The database recovery policy uses the latest backup to restore the database, and then uses archive logs and online logs to restore the database to the latest or specific status.

Restoration: The process of retrieving the required content from the latest backup file and copying it back to the original location is called restoration.

It can be restored Based on databases, tablespaces, data files, control files, and parameter files.

Restoration: Based on restoration, the database is refreshed to the latest SCN using archived and online logs to maintain database consistency.

Recovery Type

Instance recovery

In RAC, when an instance crashes, the surviving instance automatically uses the online log to roll forward committed transactions, uncommitted transactions, and release locks.

Crash recovery

It refers to the failure of all instances in a single or multi-instance environment. During crash recovery, the instance must first open the database and then perform the recovery operation.

Generally, the first instance that opens the database automatically performs crash recovery after it crashes or shuts down and exits.

Media recovery

Media recovery is usually performed in response to a media fault and based on user commands.

You can use online or archive logs to make the restored backup up-to-date or update it to a specific time point.

Media restoration can restore the entire database and a tablespace to a specified time point.

Can be divided into full recovery or incomplete recovery

Full recovery: Archive and online logs are used together with backups of databases, tablespaces, or data files to update them to the latest time point.

Procedure

A. Take the damaged data file offline

B. restore damaged data files

C. restore damaged data files

D. Bring restored data files online

Incomplete recovery: Archive and online logs are used together with backups of databases, tablespaces, or data files to update them to a previous time point or SCN.

Procedure

A. Load the database

B. Restore all data files. You can also choose to restore control files.

C. Restore the database to a certain time point, sequence, or system change number

D. Use the RESETLOGS keyword to open the database

Incomplete recovery

Incomplete recovery options

Time-based recovery, also known as time point recovery, restores the database to a specified time point.

Based on the tablespace time point recovery, you can restore one or more tablespaces to a time point different from the rest of the database.

Based on the canceled recovery, it is restored until the CANCEL command is executed.

Based on the Change recovery or log sequence recovery, if the O/S command is used, the change-based recovery will continue until a specified scn in the redo record is restored.

Flashing back from human errors

Use the flash back feature to recover from human errors

Recovery Tools

Restore with RMAN

RMAN can restore the data file from the backup machine or image copy to the current location or new location. When archiving logs are required, RMAN will automatically restore and apply the archived logs.

RMAN supports full media recovery and incomplete media recovery

Basic imperative restore and recover for RMAN recovery

Use SQL * plus for restoration

Determine the files to restore. You can usually query the view V $ RECOVER_FILE.

Recover files with medium fault and damage from the backup. When you do not have a backup, you can use the necessary redo logs and the control file contains the name of the damaged file.

If the file cannot be restored to its original location, you must reposition the restored file and update the new location to the control file.

Restore necessary archived and redone log files.

Oracle video tutorial follow: http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html