Three ways to backup and restore Oracle databases

Oracle databases have three standard backup methods, namely, export/import (EXP/IMP), hot and cold backup. Exporting spare parts is a logical backup, and cold and hot backups are physical backups.

First, export/import (Export/import)

Export can be used to extract data from the database, the use of import can be extracted from the data sent back to the Oracle database.

1, simple Export data (export) and imported data (import)

Oracle supports three different types of output:

(1), table mode (t mode), the data of the specified table is exported.

(2), the user mode (U way), will specify the user's all objects and data export.

(3), the whole library (full method), 瘵 all objects in the database export.

The process of data import is the inverse process of data export, which imports the data file into the database and exports the database data to the data file respectively.

2. Incremental Export/Import

Incremental export is a common method of data backup that can only be implemented for the entire database and must be exported as system. When making this export, the system does not require any questions to be answered. The export file name defaults to Export.dmp, and if you do not want your output file to be named Export.dmp, you must indicate the file name you want to use on the command line.

An incremental export consists of three types:

(1), "full" incremental export (Complete)

That is, back up three databases, such as:

Exp System/manager Inctype=complete File=040731.dmp

(2), "incremental" incremental export

Back up data that was changed after the last backup, such as:

Exp System/manager inctype=incremental File=040731.dmp

(3), "cumulative" incremental export

Cumulative export is the export of information that has changed in the database since the last "full" export. Like what:

Exp System/manager inctype=cumulative File=040731.dmp

The database administrator can schedule a backup schedule that is reasonably and efficiently accomplished with three different ways of exporting data.

For example, the task of a database can be arranged as follows:

Monday: Full Backup (A)

Tuesday: Incremental Export (B)

Wednesday: Incremental Export (C)

Thursday: Incremental Export (D)

Friday: Cumulative Export (E)

Saturday: Incremental Export (F)

Sunday: Incremental Export (G)

If the database is accidentally compromised in Sunday, the database administrator can follow the steps to reply to the database:

Step one: Use the command create database to regenerate the databases structure;

Step Two: Create an additional rollback that is large enough.

Step three: Full incremental import a:

Imp System/manager inctype=restorefull=y file=a

Step Fourth: Cumulative incremental import E:

Imp System/manager inctype=restorefull=y file=e

Fifth Step: Recent incremental import F:

Imp System/manager inctype=restorefull=y file=f

Second, cold backup

Cold backup occurs when the database has been shut down properly and provides us with a complete database when it shuts down properly. A term for copying critical files to a different location during a cold backup. The quickest and safest way to back up Oracle information for a cold backup. The advantages of cold backup are:

1, is a very fast backup method (just copy the file)

2, easy to file (simple copy can)

3, easy to restore to a point in time (just copy the file back)

4, can be combined with the archiving method, do the database "best state" recovery.

5, low maintenance, high safety.

However, cold backup also has the following disadvantages:

1, when used alone, can only provide to "a point in time" recovery.

2, the implementation of the entire process of backup, the database must be backed up and can not do other work. In other words, the database must be closed during a cold backup.

3, if the disk space is limited, can only copy to tape and other external storage devices, the speed will be very slow.

4, can not be recovered by the table or by the user.

If possible (primarily for efficiency), you should back up the information to disk, then start the database (so that the user can work) and copy the backed-up information to the tape (the database can work as well as the copy). Files that must be copied in a cold backup include:

1. All data files

2. All Control Documents

3, all online redo log files

4, Init.ora file (optional)

It is worth noting that cold backups must be done in the case of a database shutdown and that performing a database file system backup is not valid when the database is open.

The following is a complete example of a cold backup.

(1) Close the database

Sqlplus/nolog sql>;connect/as sysdba Sql>;shutdown Normal;

(2) use copy command to back up all time files, redo log files, control files, initialization parameter files

{$PageTitle =sql>;cp;;}

(3) Restart Oracle database

Sql>;startup

Third, hot backup

Hot backup is a way to back up the database in Archivelog mode, in case the database is running. So if you have a cold backup last night and you have today's hot backup files, you can use this data to recover more information in the event of a problem. Hot backup requires the database to operate in Archivelog mode and requires a large amount of file space. Once the database is running in Archivelog state, it can be backed up. The command file for hot backup consists of three parts:

1. data file a table space for a table space backup.

(1) Set table space to backup state

(2) Data files for the backup table space

(3) Return tablespace to normal state

2. Backup Archive log file

(1) Temporarily stop the archiving process

(2) The files in the archive Rede log target directory under log

(3) Restart the archive process

(4) Backup archived redo log files

3. Use ALTER DATABASE Bachup controlfile command to back up control files

The advantages of hot backup are:

1. Can be backed up at the table space or database file level, backup time is short.

2. The database is still available for backup.

3. Can achieve a second level recovery (restore to a point in time).

4. Restore to virtually all database entities

5. Recovery is fast, and in most cases love the database while it is still working.

The shortage of hot backup is:

1. No mistakes, or serious consequences

2. If the hot backup is unsuccessful, the results can not be used for point-in-time recovery

3. Because it is difficult to maintain, it is particularly careful not to allow "failure to end".