Backup and recovery of Oracle database

The ORACLE tutorial you are looking at is: backup and recovery of Oracle databases. -- when we use a database, we always hope that the contents of the database are 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 top priority. If the database is backed up, it is easy to restore the data. It can be seen how important it is 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. There are three standard backup methods for ORACLE databases: export/import (EXPORT/IMPORT), cold backup, and hot backup. An export backup is a logical backup, and a cold and hot backup is a physical backup.

-- export/import (Export/Import)

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

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

-- Oracle supports three types of output:

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

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

-- (3) full library (Full), all objects in the database will be exported.

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

-- 2. Incremental export/import

Incremental export is a common method of data backup that can only be performed on the entire database and must be exported as SYSTEM. When doing this export, the system is not required to answer any questions. The export file name defaults to export.dmp, and 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) "full" increment export (Complete)

Backup the entire database, e.g.

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

-- (2) "incremental" increment export

Backup data that has changed since the last backup. For example:

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

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

The cumulative export is simply the information that has changed in the database since the last "full" export. For example:

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

Database administrators can schedule a backup schedule that can be done reasonably efficiently in three different ways.

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

-- Monday: fully exported (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 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 additional callback.

-- step 3: import A:

in full increment

-- $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: import F:

for the most recent increment

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

-- ii. Cold backup

A cold backup occurs when the database has been shut down normally, which provides us with a complete database. 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 archive (just copy)

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

- 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 "to a point in time."

- 2. In the whole process of implementing 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 (primarily for efficiency), back up the information to disk, then start the database (so the user can work) and copy the backed up 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 that performing database file system backups while the database is open is invalid

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

-- (3) restart Oracle database

-- $sqldba lmode=y

---- SQLDBA >connect internal;

---- SQLDBA >startup;

-- hot backup

-- hot backup is the method of backing up data in archivelog mode mode while 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, you can do a backup. The hot backup command file consists of three parts:

- 1. Data files are backed up one table space at a time.

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

-- (2) backup the data file

of the table space

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

- 2. Back up the archive log files.

-- (1) temporarily stop the archiving process

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

-- (3) restart archive process

-- (4) backup the archived redo log file

- 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 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:

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

- 2. Jorge backup failed, the result is not available for point in time recovery.

- 3. It is difficult to maintain, so be careful not to "end in failure".