Introduction to Oracle User-managed backup and recovery

In database maintenance, backup or recovery is the most serious issue. Although database systems are slow to run,

but for the loss of database data, obviously the cost of the latter is self-evident, DBA the first job is

is to do everything possible to do a good job of data backup.

1.1 , physical backup and logical backup

on , physical backup is a copy of all physical files, such as data files, control files, archive logs, and so on. The copy can be stored on a local disk or tape, and so on.

Physical backup is the basis for backup or recovery

include cold backup ( non-archive mode ) or hot backup ( archive mode )

Geneva , logical Backup

use tables, stored procedures, and other data Oracle of the Export And other tools are exported to binary files, and then reused as needed Import tool to import the database.

Logical backups are a complement to the way physical backups are used, and are more useful for data migrations.

1.2 , Backup Recovery Tool

on , user-hosted backup and recovery, is a way to manually restore the backup. Use the operating system commands and sql*plus to complete the related backup and recovery.

Geneva , using RMAN to back up the restore, Support command line and GUI interface, supporting third-party tape library backup, powerful features.

Support for backing up databases, tablespaces, data files, control files, archive logs, and more

Can save frequent use of backup recovery scripts

Support incremental backups, skip unused blocks, and control backup speed

Detect corrupted data during a backup

through automatic concurrency, throttling I/O and so on to improve backup performance

Geneva , using Exp/imp , EXPDP/IMPDP the logical export and import tools. support for full-Library, user, table, conditional-level export and import can be directed to the structure, and only the data can be exported is the best tool for cross-platform migration

1.3 , common types of backups

Online database backup - Use Archivelog Mode , SCN inconsistent

Offline Database backup - Use Noarchivelog Mode , SCN Stay Consistent

Entire Database - can be backed up at different time periods, reducing I/O pressure to build the entire database

Table Space - in the Archivelog mode, when in Noarchivelog mode, the tablespace must be read-only or offline

Data Files - Same table space backup

Control Files - can use SQL statement or RMAN to back up

Archive log

Parameter file

1.4 , the classification of the backup

(1) backup policy:

A. Overall: Backup of control files and all data files belonging to the database

B. part: Backing up part of a database

(2) backup type:

A. full: Non-incremental RMAN Backup

B. incremental: Backs up only information that has changed since a previous backup

(3) Backup mode:

A. offline: Consistent backup, also known as cold backup

B. Online: Non-conforming backup, also known as hot backup

1.5 , recovery of classification

When to use a backup for recovery:

When the following types of errors occur, you typically need to perform a recovery using a backup:

(1) media failure: Unable to read or write the file due to physical problems with the database file

(2) user error: Data in the database was mistakenly changed or deleted by mistake

Data Recovery Overview:

Crash recovery: Automatic recovery After an instance error occurs

Media recovery: Restore Current data files, control files

Oracle Database Architecture: Crash Recovery

(1) roll forward database using online redo log file

(2) fallback uncommitted changes to Oracle database architecture: Recovering after media failure (1) restoring data files using a backup

(2) roll forward database using archive and online redo log files

Full recovery: Restore the database to the nearest point in time

Incomplete Recovery (Point-in-time recovery): Restores the database to a specific time or to a pending system SCN state of the time

Full recovery

Working with Databases , Restore a table space or data file backup , re-use archive , redo log or incremental backup updates data to the current point in time

Users can implement a full recovery based on database, tablespace, and data files

Can be divided into non-archive mode and the full recovery in the archive mode, the full recovery is mainly for the archive mode, in non-archival mode is difficult to complete recovery, unless in the recovery, the online redo log has not been rewritten.

Not fully recovered

is the same as full recovery, except that recovering data to a specific point in time or a specific SCN , not the current point in time.

The following conditions usually require incomplete recovery:

Media Failure (media failure) cause some or all of the online redo logs (online redo log) Damage

User Operation Error (user error) cause data Loss , For example , The user removed the table because of negligence , invalid data was submitted to the table

due to archive redo log (archived redo log) lost and unable to complete recovery (complete recovery)

Current Control File (Control file) lost, must be opened using the backup control file (Open) Database

About non-archived recovery instructions

1. in non-archive mode, the only option after the loss of data files is to perform a full database restore, but not restore;

2. Restore the complete database is the data files and control files used to restore;

3. Restore the premise is to backup, because the non-archiving mode, so only cold backup, mainly back up all the data files and control files. For the online redo log file, in the case of the normal shutdown of the database, you can not back it up, but in the case of abnormal shutdown of the database to be backed up, it is recommended to back up, so that you can not rebuild.

Recovery in non-archive mode.

1. If the disk of the data file is not damaged, but for some reason the loss of some data files, so long as the backup of the database (all data files and control files), copy to the original place.

2. If the disk is damaged, it cannot be placed in its original location and must be placed under a new disk. This will modify the parameter file and control file information in order to open the database normally.

3. now simulate the second case (that is, disk corruption), first cold backup of the database, a new directory /oracle/ubackup, in /oracle/oradata/ All files (data files, control files, and online redo log files) under Itpux are copied to the ubackup directory.

Several types of incomplete media recovery:

time-based recovery (time-based recovery) restores data to a specified point in time.

user-controlled recovery ( cancel-based recovery) CANCEL This option is used rman

based on SCN (change-based recovery) SCN restore ( Use only rman

table Space Recovery by point in time (tablespace point-in-time recoverytspitr) you can restore one or more table spaces to a different point in time than other table spaces in the database, but SYSTEM Table Space , UNDO Table Space , or any containing rollback segments (rollback segment) table space is not available Tspitr table spaces that have dependencies on the other tablespace should restore the dependent tablespace at the same time, such as two tables that have dependencies and are in different tablespaces.

Related views of recovery

V$reover_file-- querying files that need to be recovered , The view information is from the control file , information is not allowed if the control file is from backup or rebuild.

v$archived_log-- Query the list of all archived logs.

v$recovery_log-- query All logs that need to be used for recovery.

Introduction to Oracle User-managed backup and recovery