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

1. Summary

There are three standard modes for backup and recovery of Oracle, which are roughly divided into two categories: backup recovery (physical) and import and export (logical).

Backup and recovery can be divided into non-archived mode (Nonarchivelog-style) and archived mode (Archivelog-style) according to the working mode of the database.

In general, we call the non-archived mode cold backup, while the archived mode is called hot backup accordingly.

Logical backup-cold backup

The data can be extracted from the database by Export, and the extracted data can be sent back to the Oracle database by Import.

Physical backup-cold backup

Cold backup database is to back up all the key files, including data files, control files and online REDO LOG files, and copy them to another location after shutting down the database.

In addition, cold backups can also include backups of parameter files and password files, but these two backups can be selected as needed.

Cold backup is actually a kind of physical backup, which is a process of backing up database physical files.

Because the cold backup backs up all database files except the redo log, it is also called a full database backup.

Physical backup-Hot backup

Hot backup is a method that uses archive log mode to back up the database when the database is running.

Hot backup requires the database to operate in archive log mode and requires a lot of file space.

Once the database is in archive log mode, it can be backed up, and when the backup is performed, it can only be done at the data file level or tablespace.

Backup mode classification

II. Logical backup

Now let's first introduce the method of logical backup. The data can be extracted from the database by Export, and the extracted data can be sent back to Oracle database by Import.

Theoretical basis: Export and Import provided by Oracle have three different modes of operation (that is, the data output (input) type of backup):

(1)。 Table mode (T) exports the specified table for backup

(2)。 Full library (Full) exports all objects in the database

(3)。 User mode (U) can export all data objects corresponding to the specified user.

In the import and export backup mode, a very powerful method is provided, which is incremental export / import, but it must be used as a System to complete incremental import and export, and can only be implemented for the entire database.

Incremental exports can be divided into three categories:

(1)。 Full incremental export (Complete Export) will export the entire database file to backup

Exp system/ password @ database name inctype=complete file= storage directory\ yyyymmdd.dmp (for easy retrieval and subsequent query, we usually name backup files after dates or other characters with clear meaning)

(2)。 Incremental incremental export (Incremental Export) will only back up the results that have changed since the last backup

Exp system/ password @ database name inctype=incremental file= storage directory\ yyyymmdd.dmp

(3)。 Cumulative incremental export (Cumulate Export) is used to derive information about changes in the database since the last full incremental export.

Exp system/ password @ database name inctype=cumulative file= storage directory\ yyyymmdd.dmp

Usually, what DBA people have to do is to follow the standards specified by the enterprise or their own habits (if it is the standard specified by themselves, it is recommended to write a description of the plan)

In general, we perform daily incremental backups in the following generally accepted ways:

Mon: full backup (A)

Tue: incremental export (B)

Wed: incremental export (C)

Thu: incremental export (D)

Fri: cumulative export (E)

Sat: incremental export (F)

Sun: incremental export (G)

In this way, we can ensure the integrity of weekly data, as well as fast recovery and maximum data loss.

When recovering, assuming that the accident occurred over the weekend, DBA can follow these steps to restore the database:

Step 1: regenerate the database structure with the command CREATE DATABASE

Step 2: create an additional rollback large enough.

Step 3: fully incremental import A:

Imp system/ password @ database name inctype=RESTORE FULL=Y FILE=A

Step 4: cumulative increment import E:

Imp system/ password @ database name inctype=RESTORE FULL=Y FILE=E

Step 5: recent incremental import F:

Imp system/ password @ database name inctype=RESTORE FULL=Y FILE=F

Usually, even if the import and export backup that DBA needs to do is completed, as long as the backup is made scientifically according to the law, the loss of data can be reduced to a minimum and more reliable services can be provided.

In addition, DBA had better make a detailed description of each backup to make the recovery of the database more reliable.

Third, cold backup

When there is a problem with the database, the cold backup will only be restored to the point in time when the backup is back up, and all the new data and modification records can not be restored when the backup is completed until the database has problems and needs to be restored.

Hot backup can only be carried out in archive mode, while cold backup does not need archive mode. Archiving mode will have a certain impact on system performance, especially disk Ihamo, but relative to the benefits and security of archiving, it is believed that most DBA will choose archiving mode.

1. Advantages:

Just copy the file, which is a very fast backup method.

Just copy the file back and you can restore it to a certain point in time.

Combined with the database archiving mode, the database can be well recovered.

The amount of maintenance is less, but the security is relatively high.

2. Disadvantages:

The database must be closed during a cold backup of the database.

When cold backup is used alone, the database can only be restored at a certain point in time.

If disk space is limited, cold backup can only copy backup data to other external storage such as tape, which is slower.

Cold backup cannot be restored by table or by user.

3. Basic process

When the database can be temporarily closed, we need to transfer its data-related files at this stable time to a secure area.

When the database is destroyed, copy the backup database related files back to the original location from the security zone.

In this way, a fast, secure and other data transfer is completed.

Because it is in a closed state where the database does not provide services, it is called a cold backup.

Cold backup has many excellent features, such as the one we mentioned above, which is fast, convenient, and efficient. A complete cold backup step should be:

Step1. First shut down the database (shutdown normal)

Step2. Copy related files to the security zone (use operating system commands to copy all data files, log files, control files, parameter files, password files, etc. (including paths) of the database)

Step3. Restart the database (startup). We can use a script to complete the above steps:

Su-oracle

< sqlplus /nolog 　　 connect / as sysdba 　　 shutdown immediate; 　　 !cp 文件备份位置（所有的日志、数据、控制及参数文件）; 　　 startup; 　　 exit; Step4. 这样，我们就完成了一次冷备份，请确定你对这些相应的目录（包括写入的目标文件夹）有相应的权限。 Step5. 恢复的时候，相对比较简单了，我们停掉数据库，将文件拷贝回相应位置，重启数据库就可以了，当然也可以用脚本来完成。 四、热备份 热备份支持在数据库不停机的情况下进行备份，冷备份是必须在数据库shutdown以后使用操作系统的命令对表空间进行拷贝进行的备份。 热备份适用于重要的数据库，比如7x24不能中断的生产系统，同时可以保证数据库在出现崩溃时结合归档日志可以恢复到任意一个时间点，不会造成数据丢失。 1、优点： 可在表空间或数据文件级备份，备份时间短。 可达到秒级恢复(恢复到某一时间点上)。 可对几乎所有数据库实体作恢复。 恢复是快速的，在大多数情况下在数据库仍工作时恢复。 备份时数据库仍可用。 2、缺点： 因难以维护，所以要特别仔细小心，不允许"以失败而告终"。 若热备份不成功，所得结果不可用于时间点的恢复。 不能出错，否则后果严重。 3.基本流程 当我们需要做一个精度比较高的备份，而且我们的数据库不可能停掉（少许访问量）时， 这个情况下，我们就需要归档方式下的备份，就是下面讨论的热备份。 热备份可以非常精确的备份表空间级和用户级的数据，由于它是根据归档日志的时间轴来备份恢复的，理论上可以恢复到前一个操作，甚至就是前一秒的操作。具体步骤如下： Step1. 通过视图v$database,查看数据库是否在Archive模式下: SQL>

Select log_mode from v$database

If it's not Archive mode,

Then set the database to run in archive mode: SQL > shutdown immediate

SQL > startup mount

SQL > alter database archivelog

SQL > alter database open

If the Automaticarchival is displayed as "Enabled", the database is archived automatically. Otherwise, manual archiving is required, or the archiving method is changed to automatic archiving, such as:

For normal shutdown database, add the following parameters to init.ora in the parameter file

SQL > shutdown immediate

Modify init.ora:

LOG_ARCHIVE_START=TRUE

LOG_ARCHIVE_DEST1=ORACLE_HOME/admin/o816/arch (the location of archived daily values can be defined by yourself)

SQL > startup

Then, restart the database, and the Oracle database will work in Archive mode in an automatic archiving manner.

The parameter LOG_ARCHIVE_DEST1 is the specified path for archiving log files. It is recommended that there is a different hard disk from the Oracle database file. On the one hand, it can reduce the competition of disk I and O, and on the other hand, it can also avoid the loss of files after the destruction of the hard disk where the database files are located.

The archive path can also be directly specified as other physical storage devices such as tape, but factors such as read and write speed, writeability conditions, and performance may be taken into account.

Note: when the database is in ARCHIVE mode, make sure that the specified archive path is writable, otherwise the database will hang until all archived information can be archived.

In addition, in order to create a valid backup, when the database is created, it must perform a cold backup of the whole database, that is, the database needs to run in archived mode, and then close the database normally and back up all the database files.

This backup is the basis of the entire backup because it provides a copy of all database files. (reflects the cooperative relationship between cold backup and hot backup, as well as strong ability)

Step2. Back up tablespace files:

(1)。 First, change the tablespace file to backup mode ALTER TABLESPACE tablespace_name BEGIN BACKUP

(2)。 Then, copy the tablespace file to the security zone! CP tablespace_name D_PATH

(3)。 Finally, turn off ALTER TABLESPACE tablespace_name END BACKUP in the backup mode of the tablespace

Step3. Backup of archived log files:

Stop the archiving process-- > back up the archived log files-- > start the archiving process

If there are many log documents, we write them to a file as a recovery reference: $files ls / arch*.dbf;export files

Step4. Backup control files:

SQL > alter database backup controlfile to 'controlfile_back_name (usually 2004-11-20)' reuse

Of course, we can also write the above as a script and execute it when needed:

Sample script:

Su-oracle < sqlplus / nolog

Connect / as sysdba

ALTER TABLESPACE tablespace_name BEGIN BACKUP

! CP tablespace_name D_PATH

ALTER TABLESPACE tablespace_name END BACKUP

Alter database backup controlfile to 'controlfile_back_name' reuse

! files ls / arch*.dbf;export files

For the recovery of hot backup, the recovery of archived database requires not only an effective log backup but also an effective full database backup in archived mode.

In theory, archive backup can have no data loss, but the requirements for hardware and operators are relatively high.

When we use archived backup, the physical backup of the whole library is also very important.

Under the archiving mode, the recovery of the database requires that all logs from the full backup to the point of failure should be intact.

Recovery steps: LOG_ARCHIVE_DEST_1

Shutdown database.

Put the fully backed up data files in the directory of the original system.

Place all archive logs that are fully backed up to the point of failure to the location specified by the parameter LOG_ARCHIVE_DEST_1.

Log in to an empty instance using sqlplus. (connect / as sysdba)

And then startup mount

Set autorecovery on

Recover database

Alter database open

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.