Study on backup and recovery strategy of Oracle database
- 2020-05-15 02:26:30
- OfStack
The ORACLE tutorial you are looking at is the Oracle database backup and recovery strategy study. Recovery of lost database files depends largely on the backup strategy adopted. Based on the flexibility of recovery, this paper discusses the backup and recovery strategy of Oracle8 database, and gives a project development example of the script file that automates the backup process in Windows 2000 environment.
Keywords Oracle database backup restore
The introduction
With the increasing importance of information systems in various business activities, the maintenance of the availability of system data has become an important topic. In order to effectively maintain a healthy information system, specific measures must be taken to prevent the impact of RDBMS (relational database management system) on the normal operation of the information system in the event of serious damage to important database files caused by media, operating systems, software, and other events. This needs in the case of such a system failure the shoe Wan with its strong thin line ⒂ У � signs crow refers to development; Refers to cook a � � ne У Song called naan raise � Zheng Jiao Hui tam nonyl first 【 catfish places will take mei seeks the mound st.paulswortherb/common st.paulswort bai yu � � Fu proud nightmare machinery � � effect mu read shell racle8 database backup and recovery strategy is discussed.
Basic rules for database backup and recovery
1, multi-work online redo log files
Each database instance has its own online remaking log group, in the operation of the database, Oracle first to save all database changes in heavy log buffer, then the logger process (LGWR) data from system SGA pool (System Global Area) the heavy log buffer write online log files, disk crash or instance fails, you can through the related online remaking logging to protect the database, the losses to a minimum, However, Oracle only creates one set of redo log files by default (only one project file per set), so to reduce the risk of losing these important redo log files, you need to mirror them.
Redo log files at the Oracle level multiplex, adding multiple files to each group to mirror the data, so that an I/O failure or write loss only damages one copy, thus ensuring that the LGWR background process can write to at least one member and the database can still run. You should also ensure that members of the log group do not reside on the same physical device, as this would weaken the role of multiple log files.
2, mirror copy control file
The control file describes a common database structure that stores a large amount of information about the database state, including the physical structure and the current name, location, and state of the online redo log file. The control file is read by the Oracle instance at database startup and is kept open and updated as the operation proceeds until the instance is closed. During its opening, it synchronizes the information that needs to be recovered, including checkpoint information, so if the control file is damaged or lost, Oracle will not continue to work, so multiple copies of the control file should be kept on the system and placed on different disk devices installed under different disk controllers.
Since Oracle does not provide full support for control file multiplexing, the control file should be copied to the defined new location before using an operating system or hardware image for the control file, that is, before restarting the database after modifying the control_files parameter of the initialization file, otherwise the database will fail to start.
3. Activate the archiving process
When the database is running in NOARCHIVELOG mode, only after fully closed database the consistency of the database backup, and disable the online remaking log archive at the same time, so the Oracle database instance fails to repair to the nearest full database backup when the 1 point, not the failure points for instance recovery. In ARCHIVELOG mode, the database can not only be backed up by 1, but also be backed up online when the database is open. By backing up the database and redoing log files online and archived, 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 event of a failure. So the database should run in ARCHIVELOG mode.
To prevent file corruption and media failure in ARCHIVELOG mode, the logs should be archived to a different disk, which can be achieved by specifying multiple goals for archiving relogs in the initialization file.
4. Backup when the database is undergoing major changes
Because the schema structure information of the database is saved in the control file, the control file and the corresponding data file should be backed up immediately when making big changes to the database (including changing the table structure, adding or deleting log files or data files, etc.).
5. Use the RESETLOGS option to open the database and backup it
After opening the database with the RESETLOGS option, the entire database should be backed up either offline or online, otherwise the changes made after the reset log cannot be restored.
When the database is opened with the RESETLOGS option, Oracle will discard duplicate information that was not applied in the recovery and will ensure that it will never be used again. It will also initialize information about online logging and rework threads in the control file and clear the contents of the online logging. Therefore, the sequence 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 older than the checkpoint in the control file), that is, the previous archived log file cannot be applied in the recovery, resulting in the backup before RESETLOGS operation becoming useless in the new form.
6. Avoid backing up online redo log files
Since the multi-work online redo log files are proposed and the database is running in ARCHIVELOG mode, the ARCH process can archive the online redo logs without having to back them up. If you rebuild the log file with a backup online redo, it may cause confusion in the log file sequence number, thus damaging the database and getting the exact opposite result.
7. Reset the online log
The online log should be reset after an incomplete recovery or recovery with backup control files.
In order to ensure the 1-ness of the database, all data files must be restored to the same point in time after recovery, but incomplete recovery may lead to a different checkpoint in the data file from other files, resulting in the destruction of the 1-ness of the database. Similarly, the SCN and counters saved in the backup control file may be different from the values in the current log file, thus breaking the 1-ness of the database as well, so the online log should be reset after the above two operations.
8. Logical backup of database
The backups described above are all physical backups, copies of the actual physical database files from one place to another, in addition to the logical backup of the database using the export utility provided by Oracle. Oracle also provides the import utility to recreate the information saved in the logical backup.
Logical backup copies only the data in the database and does not record the backup process of the data location. It USES the SQL statement to export data from the database to an external file in a suitable location, while also detecting block corruption, so it can be used as a supplement to physical backup.
Backup strategy
Considering that most information systems today operate on a weekly basis of 24×7, an online backup is used, otherwise an offline backup can be performed at regular intervals.
Applying the above rules, the following typical backup strategies can be obtained:
Mirror copy redo log files;
Mirror copy control file;
Activate the archiving process, i.e. operate the database in ARCHIVELOG mode;
(4) daily partial online backup of the database (full hot backup of the database every day will increase the burden of the database and unnecessary, but also increase the flexibility of the database recovery);
Make a logical backup of the database every week or several weeks.
Actual project application
When the number of files in the database reaches 1 fixed number, DBA may not remember the name or location of the backup files. Therefore, if the backup process can be automated, the burden of DBA can be effectively alleviated without missing the files that should be backed up. Automatic batch files and script files can be applied to realize the automation of backup and recovery. The following is a brief introduction of the application of the proposed automatic backup and recovery strategy in the actual project development. The correctness and feasibility of the strategy are proved through the practical application.
(1) the following script realizes online redoing log multi-work:
(2) the following VB code automatically sets up online backup batch files and corresponding script files.
Run the obtained automatic batch file, you can automatically online backup of the database.
(3) after restoring the data files from the backup, execute the following script to restore the database.
conclusion
Improve the reliability of database disaster recovery are more and more get the attention of people, in this paper, based on personal experience and recovery in the actual project development process of the flexibility of Oracle database backup and recovery are discussed in this paper, and put forward the typical backup strategy, the user can according to own actual situation and the database structure based on the flexible application.
Keywords Oracle database backup restore
The introduction
With the increasing importance of information systems in various business activities, the maintenance of the availability of system data has become an important topic. In order to effectively maintain a healthy information system, specific measures must be taken to prevent the impact of RDBMS (relational database management system) on the normal operation of the information system in the event of serious damage to important database files caused by media, operating systems, software, and other events. This needs in the case of such a system failure the shoe Wan with its strong thin line ⒂ У � signs crow refers to development; Refers to cook a � � ne У Song called naan raise � Zheng Jiao Hui tam nonyl first 【 catfish places will take mei seeks the mound st.paulswortherb/common st.paulswort bai yu � � Fu proud nightmare machinery � � effect mu read shell racle8 database backup and recovery strategy is discussed.
Basic rules for database backup and recovery
1, multi-work online redo log files
Each database instance has its own online remaking log group, in the operation of the database, Oracle first to save all database changes in heavy log buffer, then the logger process (LGWR) data from system SGA pool (System Global Area) the heavy log buffer write online log files, disk crash or instance fails, you can through the related online remaking logging to protect the database, the losses to a minimum, However, Oracle only creates one set of redo log files by default (only one project file per set), so to reduce the risk of losing these important redo log files, you need to mirror them.
Redo log files at the Oracle level multiplex, adding multiple files to each group to mirror the data, so that an I/O failure or write loss only damages one copy, thus ensuring that the LGWR background process can write to at least one member and the database can still run. You should also ensure that members of the log group do not reside on the same physical device, as this would weaken the role of multiple log files.
2, mirror copy control file
The control file describes a common database structure that stores a large amount of information about the database state, including the physical structure and the current name, location, and state of the online redo log file. The control file is read by the Oracle instance at database startup and is kept open and updated as the operation proceeds until the instance is closed. During its opening, it synchronizes the information that needs to be recovered, including checkpoint information, so if the control file is damaged or lost, Oracle will not continue to work, so multiple copies of the control file should be kept on the system and placed on different disk devices installed under different disk controllers.
Since Oracle does not provide full support for control file multiplexing, the control file should be copied to the defined new location before using an operating system or hardware image for the control file, that is, before restarting the database after modifying the control_files parameter of the initialization file, otherwise the database will fail to start.
3. Activate the archiving process
When the database is running in NOARCHIVELOG mode, only after fully closed database the consistency of the database backup, and disable the online remaking log archive at the same time, so the Oracle database instance fails to repair to the nearest full database backup when the 1 point, not the failure points for instance recovery. In ARCHIVELOG mode, the database can not only be backed up by 1, but also be backed up online when the database is open. By backing up the database and redoing log files online and archived, 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 event of a failure. So the database should run in ARCHIVELOG mode.
To prevent file corruption and media failure in ARCHIVELOG mode, the logs should be archived to a different disk, which can be achieved by specifying multiple goals for archiving relogs in the initialization file.
4. Backup when the database is undergoing major changes
Because the schema structure information of the database is saved in the control file, the control file and the corresponding data file should be backed up immediately when making big changes to the database (including changing the table structure, adding or deleting log files or data files, etc.).
5. Use the RESETLOGS option to open the database and backup it
After opening the database with the RESETLOGS option, the entire database should be backed up either offline or online, otherwise the changes made after the reset log cannot be restored.
When the database is opened with the RESETLOGS option, Oracle will discard duplicate information that was not applied in the recovery and will ensure that it will never be used again. It will also initialize information about online logging and rework threads in the control file and clear the contents of the online logging. Therefore, the sequence 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 older than the checkpoint in the control file), that is, the previous archived log file cannot be applied in the recovery, resulting in the backup before RESETLOGS operation becoming useless in the new form.
6. Avoid backing up online redo log files
Since the multi-work online redo log files are proposed and the database is running in ARCHIVELOG mode, the ARCH process can archive the online redo logs without having to back them up. If you rebuild the log file with a backup online redo, it may cause confusion in the log file sequence number, thus damaging the database and getting the exact opposite result.
7. Reset the online log
The online log should be reset after an incomplete recovery or recovery with backup control files.
In order to ensure the 1-ness of the database, all data files must be restored to the same point in time after recovery, but incomplete recovery may lead to a different checkpoint in the data file from other files, resulting in the destruction of the 1-ness of the database. Similarly, the SCN and counters saved in the backup control file may be different from the values in the current log file, thus breaking the 1-ness of the database as well, so the online log should be reset after the above two operations.
8. Logical backup of database
The backups described above are all physical backups, copies of the actual physical database files from one place to another, in addition to the logical backup of the database using the export utility provided by Oracle. Oracle also provides the import utility to recreate the information saved in the logical backup.
Logical backup copies only the data in the database and does not record the backup process of the data location. It USES the SQL statement to export data from the database to an external file in a suitable location, while also detecting block corruption, so it can be used as a supplement to physical backup.
Backup strategy
Considering that most information systems today operate on a weekly basis of 24×7, an online backup is used, otherwise an offline backup can be performed at regular intervals.
Applying the above rules, the following typical backup strategies can be obtained:
Mirror copy redo log files;
Mirror copy control file;
Activate the archiving process, i.e. operate the database in ARCHIVELOG mode;
(4) daily partial online backup of the database (full hot backup of the database every day will increase the burden of the database and unnecessary, but also increase the flexibility of the database recovery);
Make a logical backup of the database every week or several weeks.
Actual project application
When the number of files in the database reaches 1 fixed number, DBA may not remember the name or location of the backup files. Therefore, if the backup process can be automated, the burden of DBA can be effectively alleviated without missing the files that should be backed up. Automatic batch files and script files can be applied to realize the automation of backup and recovery. The following is a brief introduction of the application of the proposed automatic backup and recovery strategy in the actual project development. The correctness and feasibility of the strategy are proved through the practical application.
(1) the following script realizes online redoing log multi-work:
(2) the following VB code automatically sets up online backup batch files and corresponding script files.
Run the obtained automatic batch file, you can automatically online backup of the database.
(3) after restoring the data files from the backup, execute the following script to restore the database.
conclusion
Improve the reliability of database disaster recovery are more and more get the attention of people, in this paper, based on personal experience and recovery in the actual project development process of the flexibility of Oracle database backup and recovery are discussed in this paper, and put forward the typical backup strategy, the user can according to own actual situation and the database structure based on the flexible application.