Shulou(Shulou.com)06/01 Report--

Backup or recovery is a top priority in database maintenance. Although many times the database system runs slowly

But for the loss of database data, it is obvious that the cost of the latter loss is self-evident, and the primary task of DBA is

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

1.1. Physical backup and logical backup

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

Physical backup is the basis of backup or recovery

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

02. Logical backup

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

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

1.2. Backup and recovery tools

01. User-managed backup and recovery is a way of manual backup and recovery. Use operating system commands and SQL*plus to complete the relevant backup and recovery.

02. Use RMAN to backup and restore, support command line and GUI interface, and support third-party tape library backup.

Support backup of databases, tablespaces, data files, control files, archive logs, etc.

You can save frequently using backup recovery scripts

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

Detect corrupted data during backup

Improve backup performance through automatic concurrency, limit Istroke O, etc.

03. Use logical export and import tools such as exp/imp and expdp/impdp. Support for full libraries, users, tables, exports and imports with conditional levels can import structures, and only export data is the best tool for cross-platform migration

1.3. Common backup types

Online database backup-- > using archivelog mode, SCN is inconsistent

Offline database backup-- > using noarchivelog mode, SCN is consistent

The whole database-- > can be backed up in different periods of time to reduce the pressure of Icano and build the whole database.

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

Data file-- > backup in the same tablespace

Control files-- > can be backed up using SQL statements or RMAN

Archive log

Parameter file

1.4. Classification of backups

(1) backup strategy:

a. Overall: backup the control files and all data files that belong to the database

b. Part: back up part of the database

(2) backup type:

a. Full: non-incremental RMAN backup

b. Increment: back up only information that has changed since a previous backup

(3) backup mode:

a. Offline: consistent backup, also known as cold backup

b. Online: inconsistent backup, also known as hot backup

1.5. Classification of recovery

When you need to use a backup to restore:

"when the following types of errors occur, you usually need to use a backup to perform a restore:"

(1) Media failure: the file cannot be read or written due to physical problems with the database file

(2) user error: the data in the database has been mistakenly changed or deleted.

Overview of data recovery:

Crash recovery: automatically recover when an instance error occurs

Media recovery: restore current data files, control files

Oracle database architecture: crash recovery

(1) roll forward the database using online redo log files

(2) rollback uncommitted changes to the Oracle database architecture: restore after a media failure (1) restore data files using backup

(2) use archiving and online redo log files to roll forward the database

Full recovery: restore the database to the most recent point in time

Incomplete recovery (point-in-time recovery): restores the database to the state it was at a specific time or when the undetermined system scn

Complete recovery

Restore using a backup of a database, table space, or data file, and then use an archive, redo log, or incremental backup to update the data to the current point in time

Users can perform full recovery based on databases, tablespaces, and data files

It can be divided into full recovery in non-archive mode and archive mode. Full recovery is mainly for archive mode, and it is difficult to achieve full recovery in non-archive mode, unless the online redo log has not been rewritten at the time of recovery.

Incomplete recovery

It is the same step as a full recovery, except that an incomplete recovery simply restores the data to a specific point in time or to a specific SCN, not the current point in time.

The following situations usually require an incomplete recovery:

Media failure (media failure) causes some or all online redo logs (online redo log) to be corrupted

User user error results in data loss, for example, the user negligently removes the table and submits invalid data to the table

Unable to perform full recovery (complete recovery) due to loss of archive redo log (archived redo log)

The current control file (control file) is missing. You must use the backup control file to open (open) the database.

Recovery instructions for non-archived

1. In non-archived mode, the only option after a data file is lost is to perform a full database restore rather than restore

two。 To restore a complete database is to restore the data files and control files used.

3. Restore premise is to backup, because it is non-archived mode, so can only carry out cold backup, mainly backup all data files and control files. For online redo log files, you don't have to back up the database when you shut down the database normally, but if you do not back up the database properly, it is recommended to back it up so that you don't have to rebuild it.

Recovery in non-archive mode.

1. If the disk where you put the data files is not damaged, but some data files are lost for some reason, then just copy the backed-up database (all data files and control files) to the original place.

two。 If the disk is damaged, it cannot be placed in the original position, but must be placed under the new disk. In this way, it is necessary to modify the information of the parameter file and control file in order to open the database normally.

3. Now to simulate the second case (that is, disk corruption), first make a cold backup of the database, create a new directory / oracle/ubackup, and copy all the files (data files, control files and online redo log files) under / oracle/oradata/itpux 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) stops the restore after the user submits the CANCEL (this option is not valid when using RMAN).

SCN-based recovery (Change-based recovery) restores data to the specified SCN redo log ordinal recovery (Log sequence recovery) restores data to the specified redo log serial number (valid only when using RMAN).

Tablespace recovery by point in time (tablespace point-in-time recoveryTSPITR) can restore one or more tablespaces to a different point in time from other tablespaces in the database, but SYSTEM tablespaces, UNDO tablespaces, or any tablespaces that contain rollback segments (rollback segment) cannot use the TSPITR feature to restore dependent tablespaces at the same time, such as two tables that are dependent and located in different tablespaces.

Restored dependent view

V$reover_file-query the files to be restored. The view information comes from the control file. If the control file is backed up or rebuilt, the information will be inaccurate.

Vandals archivedrecords logwords-query a list of all archived logs.

Vandalism recoverylog words-query all logs that are needed for recovery.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.