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

This article mainly explains "the principle of backup and recovery of ORACLE database". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn the principles of ORACLE database backup and recovery.

1. Backup and recovery methods of ORACLE database

1. Backup and recovery of user-managed ORACLE database

User-managed ORACLE database backup and recovery refers to the method of using SQL statement commands combined with OS commands to back up and restore DB (also known as OS backup and recovery).

DB files ──── copy or cp ────→ backup files

User-managed backup refers to the method of backing up database physical files by executing the OS (Copy) command.

User-managed recovery refers to the method of dumping backup files to DB when a media failure occurs in DB, and then executing the SQL command (recover tablespace) to restore DB.

Users tablespace backup file ──── copy or cp ────→ dump Users tablespace ─ recover ───→ restored Users tablespace

2backup and recovery of ORACLE database managed by Magi rman

The backup and recovery of ORACLE database managed by RMAN (Recovery Manager) refers to the method of backing up and restoring DB using the RMAN command. Because this backup and recovery method requires the help of the server process of the target DB, it is also known as server-managed ORACLE database backup and recovery.

RMAN backup refers to the method of backing up DB physical files by executing the RMAN backup command.

01.DBF 02.DBF 03.DBF 04.DBF ─ backup Command ────→ Demo_1.BAK

RMAN recovery refers to the method of dumping and restoring DB using the RMAN command when a media failure occurs in DB.

Users tablespace latest backup file Users restore ────→ dumped Users tablespace ─ recover ────→ restored Users tablespace

3. Backup and recovery of logical ORACLE database

Logical backup refers to the process of using the Oracle utility program EXPDP or EXP to export part or all of the DB structure and its data and store them in the OS file. This process is also known as export (DB must be in the OPEN state).

DEPT EMP ─ EXPORT ────→ OS File

Logical recovery refers to the process of importing the object structure and its data from the logical backup file into DB by using the Oracle tool program IMPDP or IMP after the DB object is accidentally deleted or truncated.

DEPT EMP ←──── IMPORT ─ OS File

Starting with Oracle Database 10g, data pump export and import tools EXPDP and IMPDP are provided. EXPDP and IMPDP are faster than EXP and IMP.

EXP and IMP are client-side tools that can be used either on the client side or on the server side.

EXPDP and IMPDP are server-side utility programs, which can only be used on the Oracle server side, not on the Oracle client side.

IMP can only export files using EXP, not EXPDP; IMPDP can only export files using EXPDP, not EXP.

II. Backup and recovery of ORACLE database to formulate recovery strategy

As a DBA, the most important administrative responsibility is the backup and recovery of ORACLE database.

When developing a recovery strategy, DBA should consider the handling of user errors, media failures, and block corruption.

1. Make a recovery strategy for user errors

When formulating the backup and recovery strategy of ORACLE database, we should consider how to deal with the unforeseen error operations of users or applications, such as deleting tables, misloading broken tables, batch updating data and so on.

There are the following ways to handle user errors:

If you have exported data from a mismanipulated table using a logical backup, you can import the data into the mismanipulated table in some cases. The premise of this technique is that the data of the table is derived regularly, and the changes in the data between exports are not particularly important.

You can perform a point-in-time incomplete recovery to restore the tablespace or DB to the point of failure. This ORACLE database backup and recovery method can avoid table data loss. When using a point-in-time incomplete recovery, a backup must exist before the point of failure, and all archived and redo logs at the backup point and the point of failure must exist.

Use FLASHBACK to quickly recover table data. When using FLASHBACK TABLE to recover deleted tables, make sure that deleted tables still exist in the database recycle bin; when using FLASHBACK TABLE to recover table data affected by misoperation of DML, you must ensure that the ROW MOVEMENT feature of the table is activated.

2. Make the recovery strategy for media failure.

While the database is running, a media failure occurs when other external factors prevent Oracle from reading and writing DB files.

Typical media failures include physical failures (such as head damage), overwriting or corrupting DB files.

During the normal operation of DB, media failures are far less than user errors or application errors, but the ORACLE database backup and recovery strategy should be prepared for media failures. The type of media failure determines the recovery techniques that need to be used, for example, the strategy for restoring DB files is different from that for restoring control files, and the recovery strategy for SYSTEM tablespaces is different from that for data tablespaces.

3. Develop a recovery strategy for block damage

If only a small number of blocks of one or more data files are damaged, a block media recovery can be performed and a full data file recovery can be avoided. Using the PL/SQL system package DBMS_REPAIR can handle corrupted data. In addition, RMAN's BLOCKRECOVER command can be used to recover corrupted blocks.

Third, formulate the backup and recovery strategy of ORACLE database.

Data recovery strategy is the basis of backup and recovery of ORACLE database. When formulating backup strategies, in addition to providing the necessary backup types for various recovery strategies, DBA also needs to take into account business, operational, technical, software and hardware requirements. When making backup and recovery plans, be sure to keep in mind "preparedness".

ORACLE database backup and recovery strategy 1, diversified redo logs

The purpose of diversified redo logs is to prevent the damage of log members, thereby improving the safe running time of DB (Mean-Time-Between-Failures, abbreviated as MTBF). When you redo logs in multiple ways, you should distribute different log members of the same log group to different disks to prevent disk corruption. Assuming that a log group contains only one log member and its only log member has a media failure, DB will stop running when you switch to that log group and media recovery must take place. If a log group contains multiple log members, and a log member has a media failure, DB can still run normally, and DBA only needs to delete the damaged log members.

ORACLE database backup and recovery strategy 2, diversified control files

The purpose of diversified control files is to prevent the damage of control files, thereby reducing the recovery time of control files (Mean-Time-To-Recover, abbreviated as MTTR). When multiplying control files, different control files should be distributed to different disks to prevent disk corruption. If the database contains only one control file and the only control file has a media failure, then DB will not be able to load and the control file must be re-established or restored. If DB contains multiple control files and a media failure occurs in a control file, then DBA only needs to modify the initialization parameter control_files and does not need to re-establish or restore the control file.

ORACLE database backup and recovery strategy 3 to determine the log mode of operation

The redo log records all the transaction changes of Oracle Database, and the Oracle database has two log operation modes: NOARCHIVELOG and ARCHIVELOG. When DB is in ARCHIVELOG mode, the redo log can be overwritten only after archiving, and all transaction changes are retained in the archive log; when DB is in NOARCHIVELOG mode, the redo log can be overwritten directly, and all past transaction changes are lost.

(1) the characteristics of NOARCHIVELOG mode.

An online backup cannot be performed. "if you want to make a backup, you must close DB."

"you cannot use any recovery techniques for archived logs (full recovery, FLASHBACK DATABASE, DBPITR, TSPITR)."

When there is a media failure in a data file, there are two ways to deal with it: the first method is to delete all the objects contained in the data file, and then delete the data file, and the rest of the data training will still work normally, but all the data that has damaged the data file will be lost; the second method is to dump the recent full backup, but all the data changes since the backup and recovery of the ORACLE database have been lost.

(2) the characteristics of ARCHIVELOG mode.

Dedicated space needs to be allocated for archive logs, and generated archive logs need to be managed.

Online backup can be performed when DB is opened without affecting the business operation of the database.

A variety of recovery techniques (full recovery, FLASHBACK DATABASE, DBPITR, TSPITR) are available.

ORACLE database backup and recovery strategy 4, choose backup retention strategy

Backup retention policies are used to set rules for retaining backup files to meet recovery and other needs, backup retention policies can be defined based on redundancy (redundancy) or recovery window (recovery window), backup files that do not meet the retention policy are called old files (obsolete), and these stale backups can be deleted. The backup retention policy must be implemented using RMAN.

RMAN > CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS

RMAN > CONFIGURE RETENTION POLICY TO REDUNDANCY 3

ORACLE database backup and recovery strategy 5 to keep old backups

There are several reasons for retaining early data files and archive log backups:

When you restore a database to a point in time before the most recent backup, you must use the data files and archive logs from the previous backup.

"if the recent backup is corrupted and the DB has media failures, the database can be fully restored using the data files from the earlier backup and all archive logs since the earlier backup."

ORACLE database backup and recovery strategy 6 to determine the backup cycle

Backup cycles are also necessary when formulating backup strategies, and a reasonable backup cycle can reduce media recovery time (MTTR). The backup cycle should be determined according to the frequency of database changes, and the more frequently the database changes, the shorter the backup cycle should be.

ORACLE database backup and recovery strategy 7 to perform backup after a change in the physical structure of the database

The physical structure of the database changes when the tablespace is established or deleted, the data file is added, and the data file name is changed. When the physical structure of the database changes, the control files should be backed up in ARCHIVELOG mode, and a full database backup should be carried out in NOARCHIVELOG mode.

ORACLE database backup and recovery strategy 8, frequently used tablespaces for backup

An Oracle database often contains many tablespaces, but DML operations may be performed frequently on only a small number of tablespaces. If the tablespace data changes frequently, increase the number of backups to reduce the recovery time (MTTR)

If the tablespace data changes slowly, reduce the number of backups; read-only tablespaces need to be backed up only once because their data will not change.

ORACLE database backup and recovery strategy 9, backup after NOLOGGING operation

To speed up data loading, you can specify the NOLOGGING option when loading data, creating tables, and indexing. When the NOLOGGING option is specified, data changes are not recorded in the redo log. To ensure that the data can be recovered if the tablespace is corrupted, the corresponding tablespace must be rebacked up.

ORACLE database backup and recovery strategy 10, using EXP and EXPDP to export data

To prevent an object from being accidentally deleted or truncated, you can use EXP or EXPDP to perform a logical backup, while after an object is accidentally deleted or truncated, you can use IMP or IMPDP to import its structure and data. Logical backup and recovery increases the strategic flexibility of database backup and recovery. However, this method is not a substitute for physical backup of database files, nor does it provide full recovery.

ORACLE database backup and recovery strategy 11, do not back up redo logs

Unlike archived logs, redo logs should not be backed up, and backing up redo logs has "disadvantages rather than advantages". In ARCHIVELOG mode, when the redo log fills up, its contents are automatically dumped into the archive log; in NOARCHIVELOG mode, only a full backup can be made after shutdown, and all data files and control file backups are in exactly the same state, so there is no need to use redo logs during the dump backup. ORACLE database backup and recovery the most effective way to prevent redo log corruption is to diversify redo logs and distribute different log members of the same log group to different disks.

At this point, I believe you have a deeper understanding of "the principle of ORACLE database backup and recovery". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.