It is necessary to have a clear understanding of Oracle database backup and recovery, first there is a need to have a sufficient understanding of several running status of the database. The operating status of the Oracle database is mainly divided into three, and they are:
Nomount (non-installation) Oracle just reads configuration information in the INI file and initializes the SGA area. Mount Oracle reads the control file in addition to the INI file, and get information about the physical structure of the database. Open Database To check that all files are at the same time, restore the error to the unfinished transaction rollback and eventually allow users to access.
The database's backup is mainly divided into three types: cold backup; hot backup; logical backup
The database's backup is not the focus of this article, only a summary introduction here, and the Oracle database backup mainly:
Cold Backup mainly refers to the full backup of the database that is performed in a state where the database is turned off, and the backup content includes all data files, control files, online log files, INI files. Hot Backup refers to a database in a database in which a database is backed up. To use a hot backup, you must use the database to run in the Archive Log archive. Export (logical backup) is the simplest backup method that can be exported by a table, a user or the entire database, and supports all three ways of all, accumulating, increment. Using this method, the database must be in an open state, and if the database is not in the Restrict state, the consistency of the exported data will not be guaranteed.
The restoration of the database can be divided into two categories: complete recovery; incomplete recovery;
Fully restore refers to the time point of restoring the database to a fault, does not lose any data. Not completely restore means that all changes after the time point will be lost at this point in time. If there is no special need, we recommend that you should use fully recovery.
The recovery process of the Oracle database is divided into two steps. First, all the redo-to-use data files will be stored in the redo log file, and then all the uncommitted transactions are rolled back so that all data is restored to the occurrence. At that moment at the disaster. The database recovery can only be used on the data file before the failure, restores it to the fault, and cannot keep the data file back roll back to the previous time. For example, we have a 2001/1/1 database backup. When 2001/5/1 allows us to discover data in the database, I hope to restore the database to 2001/4/30, we can only recover first. 2001/1/1 The database backup then uses the redo record to make it rolled into 2001/4/30, and the 2001/5/1 database is not rolled back to 2001/4/30. .
For the recovery scheme of the design database, we will classify the possible mistakes, and the Oracle database is mainly divided into 5 categories:
SQL statement failed thread failed instance failed user action failed storage device failed
If the first three failures occur, we don't need our people to interfere, the Oracle system will automatically recover. For the failure of the user's operating type (such as misunderstanding), the remedies we take mainly include the latest logic backup or not completely recovery to a point in time. Table space-based time point recovery (Tspitr) is introduced from Oracle 8, which can be restored separately to the tablespace containing the error operation to the specified time without having to recover the entire database. When the error operation finds compares timely and the amount of data, it is also possible to consider generating reverse SQL using the LogMiner.
The failure of the storage device is complicated. It is also the focus of this article. The failure of the storage device will inevitably make the file placed on it unavailable, let's first divide the documents involved in the Oracle database, mainly To: Oracle's system file, refer to Oracle's run file, various application database control file database online redo log file data file archive log file
Avoiding the first file failure mainly relies on the system administrator to make the operating system level backup, which can only rely on the operating system backup after the accident occurs.
The structure of the entire database is recorded, and important information such as the condition of each data file, the system SCN, checkpoint counter, etc., which will give the user to specify three locations to store the control file, and they mirror, When any of the failures, simply comment from the INI file to comment out of the fault data file. When all control all failed, you can execute Create ControlFile in Nomount mode to regenerate control files, but must provide redo log, data file, file name, and information, as well as MaxLogfiles, MaxDataFiles, MaxInstances and other information. If you run the ALTER DATABASE BACKUP ControlFile to 'XXX' before you fail, you can use the generated script to rebuild or use the backup file to override if you are using the old control file. Use the Recover XXX Using Backup ControlFile option to recover and use the ResetLogs option to open the database. If it is lost, the online log file is lost, and it is divided into two cases. 1. Lost is active log files; 2. Lost is the currently activated log file.
If it is the first case, and the failed log file group has multiple members, you can first put the database ShutDown, then use the operating system command to cover the corridor member file in the log file group (in Each of the same log files in the same log member group), if its physical location is not available to copy it to a new drive, use the ALTER DATABASE RENAME FILE 'XXXX' to 'XXXX' to change the file location, The database is then launched, and a cold backup is immediately made normally. If there is only one log group in the corrupted log group, first mount on the database, convert it to the NOARCHIVELOG mode, execute the ALTER DATABASE Add Logfile Member 'XXX' to group 'x' Add a member to the relevant group, and then execute the ALTER DATABASE DROP LOGFILE MEMBER 'BAD_FILE' deletes the damaged log file, because the structure of the database is changed, then the database is required to change the database back to the ArchiveLog mode and make a cold backup.
If lost is the currently activated log file, the database is not mirroring and all members in the current log group become unavailable. First restore all data files from the nearest full backup, start the database to the Mount state. If the original log file is not available, use the ALTER DATABASE RENAME FILE 'XXX' to 'XXX' to change the location of the file. Then, use the Recover Database Until Cancel command to recover the database until the last archive log is used, enter Cancel. Then open the database with ALTER DATABASE OPEN RESETLOGS. If there is no problem, make a cold backup immediately. note! All information contained in damaged Redo logs will be lost, that is, data that has been submitted before the database crash may be lost. This will lose weight for certain requirements, so that each log group should be allowed to have multiple log members, and the media failure is prevented from different drives. The status of data files is also divided into a variety of situations. 1. Loss the data file contained in the System tablespace; 2. Lost non-SYSTEM data files without rollback segments; .
If damaged is a data file in the system table space. The only way is to recover damaged data files from the previous backup (if the original location is not available to change the location of the new file using the alter Database rename command), then execute the Recover Database / DataFile to reply the database in the state of the database mount. In order to open the database. Note: When the SYSTEM table space or the data file is offline, the database cannot be turned on, so all recovery work must be done in the mount state.
When the lost data file does not belong to the system tablespace and does not contain a rolling segment, there is a restore in two states of the database --- in the status of the database Open or in the status of the database mount. If the user is eager to access the data in which the non-damaged portion in the database is taken for a long time, it takes a long time to make the damaged data file, open the database to the user, and then restore damaged data files. Finally, it will be online. The steps are as follows: First, in the database mount, the related data file or table space is offline ALTER DATABASE DATAFILE XXX OFFLINE, and then the database Open, so that the database is not damaged first for users to access, then the Recover is performed again. DataFile / TableSpace, after completion, use ALTER DATABASE DATAFILE / TABLESPACE 'XXX' Online to make it recovered online. Of course, users can also choose to complete all recovery work with Recover Database / DataFile, and open all data files to users access.
If the lost data file is the last case, it is a non-system tablespace data file containing a returning segment. You can also choose to recover the status of the database first OPEN or in the mount state. However, in relation to the previous situation, when the data file containing the returning segment is corrupted, if the first offline opens the database, the table that is not submitted before the database crashes will not be able to access, that is to say The objects involved in the returning segment are not allowed to be accessed. Moreover, when all data files that contain back rolls are in the OFFLINE state, the database cannot perform any DML operation, so when the database Open state restores the data file containing the returned segment, you can create several temporary rollback segments for data use. Create Rollback Segment Temp1 TableSpace System; ALTER ROLLBACK Segment Temp1 Online; Note: After using this method, all the original returns will be in the OFFLINE, and Alter Rollback Segment Rbsxx online will be manually used; allowing them to restore online status so that they can be used normally by the database. If all recovery is completed in the database mount state, the above steps are not required. If the data file is lost, the user finds the backup of the data file before the fault, and has not used the ResetLogs option to open the database since the lost data file is earliered. That is to say, the user's control file is created before the damaged data file is created. The archived log includes all the redo records of the damaged data file. The user has a recovery method, and the user can first take the damaged data file or table space offline ALTER DATABASE DATAFILE / TABLESPACE XXX Offline, then execute ALTER DATABASE CREATE DATAFILE 'NEW / XXX.DBF' as 'old / xxx.dbf' The database will rebuild an empty data file according to the information stored in the control file, then execute the Recover TableSpace / DataFile to use all the redo records to the data file, so that it is fully restored to the current state, and then restore it. Online.
If the last file is lost - the physical location where the archive or archive file is not available, first the shutdown database is immediately made as a cold backup. Then modify the archive log file destination path in the INI file, restart the database. In the future, disasters only need to recover the relevant files from the latest backups, and the database is not required to return the file file before the database is required. The parameters such as log_archive_duplex_dest and log_archive_duff_dest and log_archive_dest_1 ... 5 after Oracle 8 allow for multiple archived files to different locations, even to remote servers to ensure the reliability of archive files.
Finally, the precautions when the database recovery:
1. This article discusses the default premise for all situations that the database runs in archivelog mode and involves generally common situations and the most basic recovery method. The recovery manager RMAN provided using Oracle can also complete the above tasks. If the runtime environment is complicated, it is recommended to use RMAN to make backups and recovery.
2. Once the database has disaster, it is best to make a complete cold backup before recovery, so that you can generate errors when you recover, you can also remedy. A large part of the data is lost due to incorrect recovery operations.
3. After the database completes the recovery, especially after using the ResetLogs option, turn off the database to turn a complete cold backup. Because, to prevent the abandoned redo logs, the resetLogs option will recreate the Redo log file and clear the count, which will make the previously do all backups will be unavailable (in general ). 4. Pay special attention to the database fully recovery, when recovering the corrupted file from the backup of the failed time point, do not enable the Redo log file in the backup, otherwise it can only be performed Restore and to lose part of the data
