Backup and restore of Oracle database

The ORACLE tutorial you are looking at is: backup and restore the Oracle database. -- when we use a database, we always hope that the content of the database is reliable and correct, but due to the failure of the computer system (including machine failure, media failure, wrong operation, etc.), the database may be damaged sometimes, then how to recover the data as soon as possible becomes a priority. If the database is normally backed up, then it is easy to restore the data. Thus, it can be seen that it is very important to do a good backup of the database. The following author will take ORACLE7 as an example to talk about the backup and recovery of the database under 1. There are three standard backup methods for ORACLE databases: export/import (EXPORT/IMPORT), cold backup, and hot backup. Export backup is a logical backup, cold backup and hot backup is a physical backup.

-- 1. Export/import (Export/Import)

-- data can be extracted from the database with Export, and the extracted data can be returned to the Oracle database with Import.

-- 1. Simple data export (Export) and data import (Import)

-- Oracle supports three types of output:

-- (1) table mode (T mode), export the data of the specified table.

-- (2) user mode (U mode), which will export all objects and data of the specified user.

-- (3) all objects in the database are exported in the full library mode (Full mode).

-- the process of data export (Import) is the reverse of data import (Export), and their data flows are different.

-- 2. Incremental export/import

Incremental export is a common data backup method that can only be implemented for the entire database and must be exported as SYSTEM. When such an export is made, no questions are required to be answered by the system. The default export file name is export.dmp. If you do not want your output file to be named export.dmp, you must specify the file name to use on the command line.

Incremental exports include three types:

-- (1) "complete" incremental export (Complete)

-- backup the entire database, for example:

-- $exp system/manager inctype=complete file= 990702.dmp

-- (2) "incremental" incremental export

-- data changed after 1 backup. Such as:

-- $exp system/manager inctype=incremental file=990702.dmp

-- (3) "cumulative" incremental export (Cumulative)

The cumulative export is simply the information that has changed in the database since the last "complete" export. Such as:

-- $exp system/manager inctype=cumulative file= 990702.dmp

-- the database administrator can schedule a backup calendar, which can be done reasonably and efficiently in three different ways using the data export.

-- for example, the backup of a database can be arranged as follows:

-- Monday: fully exported (A)

-- Tuesday: incremental export (B)

-- week 3: incremental export (C)

-- Thursday: incremental export (D)

-- Friday: cumulative export (E)

-- week 6: incremental export (F)

-- Sunday: incremental export (G)

If the database is accidentally compromised on a Sunday, the database administrator can restore the database by following these steps:

-- step 1: rebuild the database structure with the command CREATE DATABASE;

Step 2: create a large enough append callback.

-- step 3: fully incremental import A:

-- $imp system./manager inctype= RECTORE FULL=Y FILE=A

-- step 4: cumulative increment into E:

-- $imp system/manager inctype= RECTORE FULL=Y FILE =E

-- step 5: latest increment import F:

-- $imp system/manager inctype=RESTORE FULL=Y FILE=F

-- 2. Cold backup

Cold backup occurs when the database is normally shut down, and when it is normally shut down, a complete database will be provided to us. Cold backup is a term for copying critical files to another location. Cold backup is the fastest and safest way to backup Oracle information. The advantages of cold backup are:

- 1. Is a very fast backup method (just copy the file)

- 2. Easy to file (just copy)

- 3. Easy to restore to a point in time (just copy the file back)

- 4. Can be combined with the archiving method to restore the "latest state" of the database.

- 5. Low maintenance, high safety.

But cold backup also has the following disadvantages:

- 1. When used alone, you can only provide recovery "up to a point in time."

- 2. In the whole process of implementing the backup, the database must be backed up and cannot do other work. That is, 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 slow.

- 4. Cannot be restored by table or by user.

If possible (depending on efficiency), backup the information to disk, then start the database (so the user can work) and copy the backup information to tape (so the database can work). Files that must be copied in 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 cold backups must be performed with the database closed, and it is not valid to perform a database file system backup while the database is open

Here's a complete example of doing a cold backup:

-- (1) close database $sqldba lmode=y

---- SQLDBA > connect internal;

---- SQLDBA > shutdown normal;

-- (2) backup all time files, redo log files, control files and initialization parameter files with copy command

---- SQLDBA > ! cp < file > < backup directory >

-- (3) restart Oracle database

- $sqldba lmode = y

---- SQLDBA > connect internal;

---- SQLDBA > startup;

-- 3. Hot backup

-- hot backup is the method of backing up data using archivelog mode when the database is running. So, if you have a cold backup from last night and a hot backup from today, you can use that data to recover more information in the event of a problem. Hot backups require the database to operate in Archivelog mode and require a large amount of file space. Once the database is running in the archivelog state, the backup can be done. The hot backup command file consists of three parts:

- 1. Data files are backed up with 1 table space and 1 table space.

-- (1) set the table space to the backup state

-- (2) backup the data files of the table space

-- (3) restore the table space to a normal state

- 2. Backup and archive log files.

-- (1) temporarily stop the archiving process

-- (2) log files in the archive redo log target directory

-- (3) restart the archive process

-- (4) backup the archived redo log files

- 3. Back up the copy with the alter database backup controlfile command

-- the advantages of hot backup are:

- 1. Can be backed up in table space or data file level, backup time is short.

- 2. The database is still available when backed up.

- 3. Second - level recovery (recovery to a point in time).

- 4. You can restore almost any database entity.

- 5. Recovery is fast, in most cases while the database is still working.

-- the disadvantages of hot backup are:

- 1. Don't make mistakes, or the consequences will be serious.

- 2. If the backup is unsuccessful, the result cannot be used for point-in-time recovery.

- 3. Be careful not to "end in failure" because it is difficult to maintain.