Special lab backup and recovery

Backup method: Logical backup and physical backup

Logical backup: The data is backed up by dragging the logical export. The logical backup data can only be dumped at the backup time. Therefore, the recovered data can only be recovered to the data saved during the backup. logical backup is suitable for backing up data tables with few changes. If you use logical backup for full database recovery, you usually need to recreate the database and import the backup data. For databases with high availability requirements, this recovery takes too long and is generally not used. Because logical backup is platform-independent,Logical backup is used as a main means of data migration and migration..

Physical backup: it refers to the backup of the database by dragging the copy of physical files. Physical backup can be divided into cold backup and hot backup. cold backup refers to the backup that is copied after the database is closed. This backup has consistent and complete time point data, and you only need to restore all files to start the database; (All files contain redo log files, contrl file, and datafile). The most common backup method in the production system is hot backup. The database for Hot Backup needs to run in archive mode, hot Backup does not need to close the database, so as to ensure the continuous operation of the system. During recovery, the database can be completely restored through the backup data files and archive log files, the restoration can be carried out until the last archived log (the remaining content can be restored through the redo log file). If the online log exists (redo log), the restoration can continue, achieve full recovery without data loss.

Recovery Principle

Restore the archived redo log file)

Hot Backup Recovery: the process of gradually restoring application logs to the forward is a roll-forward process. The rollback process is actually a re-running process of application logs. After the rollback is completed, the data file contains uncommitted and committed data, and then the application needs to roll back the data to roll back the uncommitted transactions. This process becomes rolling back or transaction recovery.

In general, full recovery is applied to database losses caused by hardware faults. In this case, you need to restore data as much as possible. Incomplete recovery is usually used to recover user errors, for example, the user accidentally deleted the content that should not be deleted.

In actual management, Incomplete recovery is usually performed in four types: time-based recovery ), cancel-based recovery, change-based recovery, and log sequece recovery)

Recovery Based on Time points is most commonly used. Of course, in more cases, we may be faced with damage to a single data file or tablespace. for such faults, you can recover a single file or table space. Recovery usually does not affect the operation of the entire database. The impact may only be related to applications related to the faulty table space.

Checkpoint and RBA

In the internal control of recovery, the Oracle database determines the recovery range by checking the point and RBA information. Normally, the RBA corresponding to the last checkpoint is the recovery start point (Cache-low RBA), and the recovery endpoint is on-disk RBA, that is, the last redo log written to the disk. During instance recovery, the Oracle database will perform automatic instance recovery between cache-low RBA and on-disk RBA.

RBA (redo byte address): It is a pointer to a specific location in the redo logs.

The above start point and end point refer to the last RBA that completes the checkpoint in the redo log file (a pointer pointing to a record inside the redo log, the operation result of this pointer has been written to the disk because the checkpoint has been completed.) This RBA is used as the starting point to the on-disk RBA (only lgwr has written a record to the redo log file ).

Database Running Mode

Archive log list; get the information of the current archive

Production databases run in archive mode. Many users have seen database Faults due to the lack of necessary monitoring and backup policies in archive mode.

Change Database mode (non-archive-> archive)

• Modify the necessary initialization parameters (log_archive_start: used to define whether to enable automatic archiving. After Oracle10g, this parameter is discarded. log_archive_format: used to define the format of the archive file. The default value can be used. log_archive_dest: used to define the path of the archive file. log_archive_dest_n: Multiple archive paths are allowed in Oracle. Generally, you can use the log_archive_dest_1 parameter.
• Shut down the database in Immediate Mode
• Start the instance to the Mount status
• Change the running mode and open the database alter database archivelog; alater database open; archive log list;

Logical backup and recovery? Confirm again

It seems that this method only imports and exports data, but the skeleton (tablespace, table, user, and other information needs to be created in the database first)

Using exp for logical backup, exp tool can extract data from Oracle database, IMP can import data.

However, when using exp backup data for full database recovery, you need to re-establish the database and import the backup data. The recovery process may be extremely long.

1) exp export and character set,

Use imp for logical recovery

The recovery process may be extremely long because you need to re-create a database and import the backup data when restoring data backed up by EMP; if local recovery is performed (only for some users or some tables), it is relatively simple.

Using the data pump (expdp/impdp), unlike exp, expdp adds a major new parameter: Directory, which is used to define a path, this directory corresponds to the server path. You can create a directory by yourself or use the default path.

Physical backup and recovery

Physical backup is only for Oracle files, which is different from logical backup for data. (control file, data file, control file, redo log, archive log (optional), parameters, and passwords). Note that temporary files do not need to be backed up because they are not permanently stored.

Cold backup

Select name from V $ database -- first determine the location of the file to be dumped.

Steps for cold backup:

1) shut down the database normally

2) back up all important files to the backup directory

3) Start the database after the backup is completed

For ease of recovery, cold backup should contain all data files, control files and log files. In this way, when cold Backup recovery is required, you only need to restore all files to the original location, you can start the database.

Hot Backup

Divided into user management hot backup and RMAN

User-managed Hot Backup refers to the backup mode of the tablespace by placing the tablespace in the hot backup mode, and then copying and backing up the tablespace through the operating system tool team file. After the backup is complete, the tablespace backup mode ends. (that is, first set the tablespace backup mode, then copy the file, and then restore the tablespace)

RMAN: you do not need to set the tablespace to the hot standby mode to reduce the impact on the database. In addition, the backup information of RMAN can be managed through the control file or an additional directory database, features are large but relatively complex.

User Management Hot Standby steps: (performance is affected. RMAN is recommended)

1) The begin BACKUP command such as alter tablespace system begin backup must be issued before backup;

2) copy the Operating System File (copy system01.dbf to another location)

3) issue the end BACKUP command to notify the database to complete the backup alter tablespace end backup;

4) Back up archived log files

When the database is restored, the database is directly turned on to the Mount state, and then alter database recover

RMAN backup and recovery? Need to add

Any Oracle database user should use RMAN for Backup recovery management, which is a very important basic skill.

Advantages of RMAN:

RMAN supports all backup types except logical backup, including full backup, Incremental backup, tablespace backup, data file backup, control file backup, and archive log backup.

Automatic backup log generation

The backup script of RMAN has nothing to do with OS, which facilitates migration.

The powerful report function allows you to easily learn the backup availability.

When using RMAN for backup, you need to understand an important concept: to restore the directory, RMAN needs to store the backup information during backup. This information will be used for recovery. If this information is lost, recovery will become extremely difficult and complex. by default, RMAN stores the information in the control file, so the security of the control file becomes extremely important. For important databases, it is not secure to store the information using the control file; oracle also supports storing RMAN backup information in the Catalog Database. You can use an independent database or a tablespace in an existing database, A directory database can back up multiple databases. However, restoring directories also requires backup. Logical backup is usually used.

1) Use nocatlog for backup (that is, the backup information is stored in the control file)

In this way, the security of contrl file becomes very important. Oracle supports automatic backup of control files. You can set automatic backup of control files as follows:

RMAN target/

Configure controlfile autobackup on;

This setting can be queried in RMAN: Show controlfile autobackup; or in the database, select * from V $ rman_configuration;. Pay attention to the backup path, because the new feature of the flash back area is introduced after Oracle10g, automatic backup is stored in the flash back area. If the flash back area is not applicable or Oracle 9i is applied, automatic backup is stored in the $ ORACLE_HOME/DBS directory.

If the control file is faulty, you can use the control file (including spfile) backed up automatically)

Input in RMAN: Restore spfile to 'spfile location 'from autobackup; -- Restore spfile

In rman, enter restore controlfile to 'spfile' from autobackup; -- Restore controlfile

Finally, we strongly recommend that you enable the automatic backup control file.

Full backup:

Backup database tag = leon070301; -- indicates July 3 backups on January 1, 1st.