Research on the backup and recovery policies of Oracle databases

The ORACLE tutorial is: Research on the backup and recovery policies of Oracle databases. Abstract restoring lost database files depends largely on the backup policy used. Based on the flexibility of recovery, this article discusses the backup and recovery policies of Oracle8 databases, and provides a project development example of script files that automate the backup process in Windows 2000.

Keywords: Oracle Database Backup Recovery

Introduction

With the increasing importance of information systems in various business activities, maintaining the availability of system data has become a very important topic. To effectively maintain a healthy information system, some measures must be taken to prevent the impact of RDBMS (relational database management system) when critical database files are seriously damaged due to media, operating systems, software, and other events ), this affects the normal operation of the information system. In this case, we need to carry out a poor performance when such a system fails; refers to the backup and restoration strategies for the racle8 database of the Qingyi yi Mu yi Yu discussed.

Basic database backup and recovery rules

1. Rebuild log files online with multiple workers

Each database instance has its own online replaying log group. When operating the database, Oracle first saves all database changes in the replaying log buffer, followed by the logger process (LGWR) write the data from the replaying log buffer of the SGA (System Global Area) in the System shared Area to the online replaying log file. When the disk crashes or the instance fails, you can use the related online re-engineering logs to protect the database and minimize the loss, however, Oracle creates only one group of duplicate log files by default (each group has only one project file). To reduce the risk of losing these important duplicate log files, therefore, you need to copy the image.

At the Oracle-level multi-job online re-engineering log files, that is, adding multiple files to each group for image data. In this way, I/O failure or write loss only damages one copy, this ensures that the LGWR background process can write information to at least one member, and the database can continue to run. At the same time, ensure that the members of the log group should not be located on the same physical device, because this will weaken the role of multiple log files.

2. Image copy Control File

The control file describes the general database structure. It stores a large amount of database status information, including the name, location, and status of the physical structure and the reconnection log file. The control file is read by the Oracle instance when the database is started. Keep it open and the file content is updated with the operation until the instance is closed. When it is opened, it can synchronize the information to be restored, including the checkpoint information. Therefore, if the control file is damaged or lost, Oracle cannot continue to work, therefore, multiple copies of control files should be kept in the system, and these copies should be placed on different disk devices installed under different disk controllers.

Since Oracle does not provide complete support for multiple workers in the control file, the operating system or hardware image should be used for the control file, that is, before the database is restarted after the control_files parameter of the initialization file is modified, the control file should be copied to the new location defined; otherwise, an error will occur when the database is started.

3. Activate the archiving process

When the database is running in NOARCHIVELOG mode, you can only perform consistent database backup after the database is completely shut down, and disable online replaying log archiving, in this way, when an Oracle instance fails, the database can only be restored to the point at which the latest complete database backup is performed, and the instance cannot be recovered at the point where the database fails. In ARCHIVELOG mode, databases can not only perform consistent backup, but also perform online backup when the database is opened. You can recover all committed transactions and allow the database to be restored to a specified time, SCN, or log serial number through database backup, on-line, and archived duplicate log files, this increases the flexibility of recovery and reduces data loss during failures. Therefore, the database should run in ARCHIVELOG mode.

In ARCHIVELOG mode, logs should be archived to different disks to prevent file damage and media faults. This can be achieved by specifying multiple targets for archiving duplicate logs in the initialization file.

4. Backup when the database is greatly changed

Because the control file stores the schema structure information of the database, the database is greatly changed (including changing the table structure, adding or deleting log files or data files) the control file and corresponding data file should be backed up immediately.

5. Use the RESETLOGS option to open the database and back up the database

After you open the database with the RESETLOGS option, the entire database should be backed up offline or online. Otherwise, the changes after the reset log cannot be restored.

When you use the RESETLOGS option to open a database, Oracle will discard the duplicate information that is not applied in the restoration and make sure that the database is never used again, at the same time, the information about the online log and replaying thread in the initialization control file is cleared. Therefore, the serial number of the archived log before RESETLOGS will not match the required value of the Oracle control file after RESETLOGS (the checkpoint in the backup file is earlier than the checkpoint in the control file ), that is to say, you cannot apply the previously archived log files during restoration, which makes the backup before the RESETLOGS operation useless in the new form.

6. avoid replaying log files online after backup.

Because the paper proposes that multiple engineers repeat log files online and the database runs in ARCHIVELOG mode, the ARCH process can repeat online log archiving, so it does not have to be backed up. If you re-create a log file with the backup online, it may cause confusion of the log file serial number, which may damage the database and result in counterproductive results.

7. Reset online logs

Online logs should be reset after Incomplete recovery or backup control files are used for recovery.

To ensure Database Consistency, all data files must be restored to the same time point after recovery, but incomplete recovery may lead to a different check point in the data file than other files, the Database Consistency is damaged. Similarly, the SCN and counters stored in the backup control file may be different from the values in the current log file, which also damages the Database Consistency, therefore, the online log should be reset after the preceding two operations are completed.

8. Logical backup of the database

The preceding backups are physical backups. Physical database files are copied from one place to another. In addition, you can use the export utility provided by Oracle to perform logical backup of the database. Oracle also provides the corresponding import utility to recreate the information stored in the logical backup.

Logical backup only copies the data in the database, but does not record the backup process of the data location. It uses SQL statements to export data from the database to an external file that is stored in a proper location, and can detect data block corruption. Therefore, it can be used as a supplement to physical backup.

Backup Policy

Because most of the information systems operate 24x7 every week, online backup is used. Otherwise, offline backup can be performed at a certain time.

Apply the preceding rules to obtain the following typical backup policies:

① Copy the image as a log file;

② Image copy control file;

③ Activate the archiving process, that is, operate the database in ARCHIVELOG mode;

④ Perform partial online backup of the database every day (full hot backup of the database every day will not only increase the burden on the database, but also increase the flexibility of database recovery );

⑤ Perform a logical backup of the database every one week or several weeks.

Actual Project Application

When the number of files in the database reaches a certain level, the DBA may not be able to remember the name or location of the backup file. Therefore, if the backup process is automated, the DBA's burden can be effectively reduced, at the same time, no files to be backed up will be omitted. Automatic batch processing and script files can be used to automate backup and recovery. The following describes how to apply the automatic backup and recovery policies proposed in this article in actual project development. the correctness and feasibility of this policy are proved through practical application.

(1) The following script allows you to rerun logs online:


(2) The following VB code automatically creates a batch file for online backup and corresponding script files.


After running the automatic batch processing file, you can automatically back up the database online.

(3) After recovering the data file from the backup, execute the following script to restore the database program.


Conclusion

Increasing the reliability of database recovery after disaster recovery is attracting more and more attention, this article discusses Oracle database backup and recovery based on my experience in actual project development and the flexibility in restoration, and puts forward typical backup strategies, you can flexibly use the database structure based on your actual situation.