Oracle Backup and Recovery ____oracle

Oracle system backup and recovery scenario
I. Overview 1.1 Concept of backup

Backup is the process of copying a database to a dump device. Where a dump device refers to a tape or disk that is used to place a copy of a database.

What kind of recovery depends on what kind of backup is available. As a DBA, it is the responsibility to maintain the recoverability of the database from the following three aspects:

Minimizes the number of database failures so that the database remains the most available;

When the database is unavoidable, the recovery time is minimized, so the recovery efficiency is the highest.

• When a database fails, make sure that as little data is lost or not lost, so that the data is most recoverable.

The most important task of disaster recovery is to design a sufficient frequency hard disk backup process. The backup process should meet the recoverability of the system requirements. For example, if a database can have a long shutdown time, you can make a cold backup every week and archive the redo logs, and perhaps we can only consider hot backups for 24*7 systems. It would be ideal if you could back up every day, but consider the reality. Enterprises are looking for ways to reduce maintenance costs, the real solution can be adopted. As long as you plan carefully and try to reach the bottom line of database usability, it is possible to spend a small amount of money on successful backup and recovery. 1.2 How Oracle databases are run

There are two ways to run an Oracle database: One is the Archiving method (Archivelog), and the purpose of archiving is to restore the database to the maximum when the database fails, to ensure that no data has been submitted, and not to file the way (Noarchivelog), Only the database can be recovered to the nearest recycle point (cold or logical). Depending on the high availability of the database and the amount of work that the user can afford to lose, it is strongly required for the production database to be archived; databases that are being developed and debugged can be used in a way that is not archived.

How to change the way the database runs, when you create a database, as part of creating a database, determines how the database is initially archived. In general, it is Noarchivelog way. When the database is created, we need to change the database that needs to be run in the way of archiving to Archivelog.

Before oracle10g, you also need to modify initialization parameters to keep the database in automatic archive mode.
Set the following parameter in Pfile/spfile:Log_archive_start = true; Restart the database This parameter takes effect when the database is in automatic archive mode. It can also be performed manually during the database startup process:Archive log start enables automatic archiving of the database, but the database is still in manual archive mode after the reboot. Starting with oracle10g, Log_archive_start parameters have been abolished

A. Enable archiving mode

Sql>shutdown Immediate

Sql>startup Mount
Sql>alter Database Archivelog

Sql>alter Database Open

B. Stop archiving mode

Sql>shutdown Immediate

Sql>startup Mount

Sql>alter Database Noarchivelog

Sql>alter Database Open

C. Modify the corresponding initialization parameters

Classification of 1.3 Oracle Backups

The simple way of backup, can be divided into logical backup, cold backup (offline backup), hot backup (online backup), in fact, cold backup and hot backup can also be called physical backup

According to the Backup tool, it can be divided into Exp/imp backup, OS copy, RMAN, third party tools, such as Veritas

1.3.1. exp/imp logical backup

Import/Export is the oldest surviving two command-line tool for Oracle, the right thing to say is that exp/imp can only be a good dump tool, especially in small database dumps, table space migration, table extraction, detection logic and physical conflicts and so there is no small credit. Of course, we can also use it as a logical backup after the physical backups of small databases, which is also a good suggestion.

For increasingly large databases, especially TB-level databases and the emergence of more and more data warehouses, Exp/imp is becoming more and more powerless, at this time, the database backup to the Rman and Third-party tools.

1.3.2. Cold backup (offline Backup)

The database must be completely shut down before the file-level backup starts. The close operation must be performed with a shutdown with the normal, immediate, transaction options.

Each file that is used by the database is backed up, and these files include:

☆ All data files

☆ All Control Documents

☆ All online Redo LOG files

☆init.ora file (optional)

General steps for cold backup are:

A. Normal shutdown of the instance to be backed up (instance);

B. Back up the entire database to a directory

C. Start the database

Such as

Sql>connect Internal

Sql>shutdown Immediate

sql>! CP <file> <backup directory> or

Sql>!tar cvf/dev/rmt/0/u01/oradata/prod

Sql>startup

Note: If you use a script to make a cold backup of the database, you must logically check the command to shut down the database, and if the command to shut down the database does not perform properly and the database does not shut down properly, then all cold backups will be invalid.

1.3.3. Hot Backup (PC Backup)

Hot backups are OS-level data backups that are available when the database is open and valid for the user. Hot backups can only be used in Archivelog-mode databases. Before a data file is backed up, the corresponding tablespace must be used by using alter tablespace ... BEGIN Backup to be placed in a backup manner. The data files that make up the table space can then be copied using an operating system command similar to a cold backup. After the data file is copied using the operating system command, alter TABLESPACE should be used ... The end Backup command causes the tablespace to be detached from the hot backup method.

Hot backup is not necessary to back up the online logs, but it must be an archive state, and you may need to use an archive log when the instance is recovered. The current online log must be protected or in a mirror state, the current online log corruption, damage to the database is huge, only the loss of data for the recovery of the database work.

For temporary tablespace, the temporary information is stored, in hot backup You can also consider not to backup, if the temporary file failure, you can delete the data file and table space, rebuild a temporary table space.

Benefits of Hot Backup:

A Can be backed up at the table space or data file level for short backup time.

b The database is still available for backup.

C Can reach a second level recovery (revert to a point in time).

D Almost all database entities can be recovered.

E Recovery is quick and, in most cases, restored when the database is still working.

In hot backup, because the user is still operating the database, it is best that each tablespace is in the shortest time to backup, so that a table space to a table space backup, do not make the table space in the backup state while copying the data files.

Note: If a database is interrupted during a hot backup, such as a power outage, when the database is restarted, the database prompts for the data file to be restored, and you need to tablespace the data file that is in the backup state at the time of the power outage through alter ... End backup is the way to back up. The specific data file or table space is in the backup state, can be obtained through v$backup and V$datafile.

1.3.4. Rman for backup

The following are the advantages of using the Rman Backup tool provided by the database:

A. Support for online hot backup

It means that the backup does not need to shut down the database, and that it can perform various operations of the normal database while backing up.

Meet the needs of the 7*24 system, for this system, the database backup will not affect the Internet or

Access to the database by users of the intranet.

B. Support for multilevel incremental backups

A multilevel incremental backup is a level-n backup that only needs to be backed up by the changes that occur after the last sibling or N-1 level backup. You can use the following figure to illustrate:

The illustration above is an example of an incremental backup that makes an incremental level 0 backup on the first Sunday, then an incremental level 2 backup in Monday and Tuesday, an incremental 1-level backup in Wednesday, and so on. Assuming that the database needs to be restored in Friday, you can restore the database by restoring the first 0-level backup of the second Sunday, restoring the Level 1 backup of Wednesday, and resuming the 2-level backup of Thursday and Friday.