Three ways to backup and restore Oracle databases

Transferred from blueskys567 original Oracle database backup and recovery of three ways , 2006-10.

There are deletions.

The Oracle database has three standard backup methods, namely Export/import (exp/imp), hot and cold backup .

Exporting a spare is a logical backup , and cold and hot backups are physical backups .

First, export/import (Export/import)

With export, data can be extracted from the database, and the extracted data can be sent back to the Oracle database using import.

1. Simple Export data (exports) and importing data (import)
Oracle supports three different types of output:
(1), table mode (t ), the data of the specified table is exported.
(2), user mode (U-mode), all objects and data of the specified user are exported.
(3), all-in-a-way ( full mode), partners all objects in the database export.
The process of data import (import) is the inverse process of data export, which imports the data files into the database and exports the database data to the data file.

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 such an export is made, the system does not require any questions to be answered. The exported file name defaults to Export.dmp, and if you do not want your output file to be named Export.dmp, you must indicate the name of the file you want to use on the command line.
There are three types of incremental exports:

(1), "full" incremental export (complete)
Back up three databases, such as:

Exp System/manager Inctype=file=040731. DMP

(2), "incremental" incremental export
Back up the data that was changed after the last backup, such as:

Exp System/manager Inctype=file=040731. DMP

(3), "cumulative" incremental export
The cumulative export method is to export information that has changed in the database since the last "full" export. Like what:

Exp System/manager Inctype=file=040731. DMP

The database administrator can schedule a backup schedule that is reasonably and efficiently done in three different ways of exporting data.
For example, the task of the 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, in Sunday, the database was accidentally compromised, the database administrator could follow the steps to reply to the database:
Step one: Regenerate the database structure with the command create DatabASE ;
Step Two: Create an additional rollback that is large enough.
Step Three: Fully incremental import a:
Imp System/manager inctype=restore full=y file=a
Fourth Step: Cumulative incremental Import E:
Imp System/manager inctype=restore full=y file=e
Fifth step: The most recent incremental import F:
Imp System/manager inctype=restore full=y file=f

Second, cold backup

A cold backup occurs when the database has been shut down properly and provides us with a complete database when it shuts down normally. A way to copy critical files to another location during cold backup. The fastest and safest method for backing up Oracle information for cold backups. The advantages of cold backup are:
1, is very fast backup method (just copy files)
2, easy to archive (simple copy can)
3, easy to restore to a point in time (just copy the file back)
4, can combine with the archiving method, do the database "best state" recovery.
5, low maintenance, high security.

However, cold backup also has the following disadvantages:
1, when used alone, can only provide to "a point in time" recovery.
2, re-implementation of the whole process of backup, the database must be backed up and not be able to do other work. In other words, during a cold backup, the database must be off state.
3, if the disk space is limited, can only be copied to the 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 (mostly efficiency), you should back up the information to disk, then start the database (making the user work) and copy the backed up information to tape (the database works as well). Files that must be copied in a cold backup include:
1. All data Files
2. All Control Files
3. All online redo log files
4. Init.ora file (optional)

It is important to note that a cold backup 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 in an open state.

Here is a complete example of a cold backup.

(1) Closing 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, initialize parameter files
SQL>;CP <file>; <backup directory>;

(3) Restart Oracle database
Sql>;startup

Third, hot backup

A hot backup is a method of backing up a database using the Archivelog mode mode in the case of a database run. So if you have a cold backup of 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 the Archivelog state, it can be backed up. The command file for a hot backup consists of three parts:

1. Data file a table space for a table space backup.
(1) Set table space as backup state
(2) Backing up table space data files
(3) The reply table space is the normal state

2. Backup Archive log file
(1) Temporarily stop the archive process
(2) Log those files in the archive Rede log target directory
(3) Restart the archive process
(4) Backing up the archived redo log file

3. Use the ALTER DATABASE Bachup controlfile command to back up the control file
The advantages of hot backup are:
1. Backup is available at the table space or database file level, and the backup time is short.
2. The database can still be used when backing up.
3. Can reach a second level of recovery (to a point in time).
4. Recover from virtually any database entity
5. Recovery is fast and in most cases the love database is still working when recovering.

The disadvantages of hot backup are:
1. No mistakes, or serious consequences.
2. If the hot backup is unsuccessful, the resulting results are not available for point-in-time recovery
3. It is difficult to maintain, so be careful not to allow "failure".

Three ways to backup and restore Oracle databases