Research on backup and recovery strategy of Oracle Database

The recovery of a missing database file depends to a large extent on the backup strategy used. Based on the flexibility of recovery, this paper discusses the backup and recovery strategy of Oracle8 database, and gives the project development example of the script file which automates the backup process under Windows 2000 environment.

Keywords Oracle database backup recovery

Introduction

With the increasing importance of information system in various business activities, the maintenance of the availability of system data has become a very important topic at present. In order to maintain the health information system effectively, some measures must be taken to prevent the RDBMS (relational database management system) from being affected by media, operating system, software and other events causing serious damage to the database files, and then to affect the normal operation of the information system.

Basic rules for database backup and recovery

1. Multi-work online redo log files

Each database instance has its own online redo log group, where Oracle first saves all changes to the database in the Redo log buffer, and then the logger process (LGWR) uses the data from the system's shared zone SGA (System Global Area), the redo log buffer write online redo log file, when a disk crashes or an instance fails, the database can be protected by a related online redo log, minimizing the loss, but Oracle creates only one set of redo log files in the default mode (each group has only one project file), In order to reduce the risk of losing these important redo log files, it is necessary to mirror the copy.

When the Oracle-level multi-worker online redo log file, that is, add multiple files to each group to mirror the data so that I/O failure or write loss only damages a copy, thus ensuring that the LGWR daemon can write information to at least one member, and the database can still continue to run. You should also ensure that the members of the log group should not be on the same physical device, as this will weaken the effect of multiple log files.

2. Mirror Copy control file

The control file describes a common database structure that stores a large amount of database state information, including the physical structure and the name, location, and status of the online redo log file at that time. The control file is read by an Oracle instance when the database is started, remains open, and the file content is updated as the operation progresses until the instance is closed. The information that needs to be recovered is synchronized during its opening. Includes checkpoint information, so if the control file is damaged or lost, Oracle will not be able to continue working, so multiple copies of the control file should be kept in the system, and the copies should be placed on different disk devices installed under different disk controllers.

Because Oracle does not provide full support for control file Control_files, you should copy the control files to the defined new location before you restart the database by using the operating system or hardware mirroring on the control file, or you will have an error when the database starts up before you restart it after modifying the initialization file's parameters.

3. Activate the archive process

When the database is running in Noarchivelog mode, you can only make a consistent backup of the database after the database is completely closed, and the archive of online redo logs is disabled, so that when the Oracle instance fails, the database can only be repaired to the point of the most recent full database backup. The instance cannot be recovered at the expiration point. In Archivelog mode, the database can be backed up not only by a consistent backup, but also online when the database is open. With the backup of the database and the online and archived redo log files, users can recover all committed transactions and allow the database to be restored to a specified time, SCN, or log series number, increasing the flexibility of recovery and reducing data loss in the case of failure. Therefore, the database should run in Archivelog mode.

In Archivelog mode, to prevent file corruption and media failure, you should archive the log to a different disk, which specifies multiple target implementations for the archive redo log in the initialization file.

4, database implementation of large changes in backup

Because the schema structure information of the database is saved in the control file, you should back up the control files and the corresponding data files immediately when you make a large change to the database (including changing the table structure, adding, deleting log files, or data files, etc.).

5, use the Resetlogs option to open the database after the backup

After you open the database with the Resetlogs option, you should make a backup of the entire database offline or online, or you will not be able to restore the changes after the reset log.

When the database is opened with the Resetlogs option, Oracle discards duplicate information that is not applied in the recovery and ensures that it will never be used again, and also initializes information about the online logs and redo threads in the control file, and clears the contents of the online logs. Therefore, the serial number of the Resetlogs archive log will not match the required value of the Resetlogs Oracle control file (the checkpoint in the backup file is older than the checkpoint in the control file), that is, the previous archive log file cannot be applied in the recovery. This causes the backup before the resetlogs operation to be useless in the new form.