Use of the ORACLE_RESETLOGS command

I think everyone is familiar with the use of the alter database open resetlogs command of ORACLE_RESETLOGS command. Have you ever wondered why this resetlogs option should be used? When will it be used? What is its mechanism? What are their roles? We all know that data must be checked for consistency at startup, and oracle needs to perform two checks in the open stage: www.2cto.com 1. check whether the checkpoint cnt in the data file header is consistent with the checkpoint cnt in the control file. The purpose is to check whether the data file is from the same version, rather than from the backup. If this check item passes, perform the second check. SQL code SELECT CHECKPOINT_CHANGE # FROM V $ DATAFILE_HEADER; SELECT CHECKPOINT_CHANGE # FROM V $ DATABASE; 2. Check whether the starting scn of the data file header is consistent with the ending scn of the file recorded in the control file. If the starting scn of the data file header is the same as the ending scn of the file in the control file, it indicates that the data file does not need to be restored; otherwise, the file www.2cto.com needs to be restored. If both the above two steps pass the check, you can open the database, lock the data file, and set the ending scn of each data file in the control file to infinity. When we open data under certain conditions, we will prompt you to use the resetlogs option to open the database. Why should we use resetlogs? Why is it used? There are a lot of question marks. Let's analyze them in detail. The role of resetlogs prevents old data from entering the database (ensuring Database Consistency), which is why you must immediately perform full backup for the database when using resetlogs to open the database. "Resetlogs data" is stored in the control file, data file header, and redo log header. When open resetlogs is executed, consistency can be checked through this content. When to use resetlogs 1. incomplete recovery 2. restore with backup control file 3. how does the newly created control file restore the resetlogs principle and mechanism ensure the consistency of the resetlogs when opening the database? 1) In open resetlogs, oracle should compare and check the control file and data dictionary file $. If a data file exists in file $ but does not exist in the control file, create a file entry in the control file (MISSINGnnn 'nnn 'is the file_id in decimal format), and mark the file as offline and need to be restored. If the file actually exists, you can use the following SQL statement to change it to the correct file name. SQL> alter database rename file 'missingnn' to '<filename>'; then the data file is recovered and online. 2) if a data file exists in the control file instead of the data dictionary file $, delete the record entries of this file in the control file directly (oracle regards file $ as correct, subject to it) 3) when using the old backup to control file recovery, if a data file is not registered in the control file (an error indicating that the control file is old), you have to recreate the data file so that the data file can be registered in the control file, then the system automatically uses redo/archivelog to restore the data file. After the control file and file $ file are consistent, oracle will perform the following checks to open resetlogs 4) the data file version must be smaller than the current database version (counter) 5) offline data files must be online or directly dropped. 6) fuzzy bit cannot be set for all data files. All data files must have the same checkpoint (checkpoint SCN) www.2cto.com so far, the prerequisites for open resetlogs have been met. What exactly does resetlogs do? (Re-use redo log) 1) all online logfile information is re-stored in the control file. Select a logfile file for the valid thread as the current logfile 2) log header is updated to log seq #3) all online data file headers are updated by the new checkpoint and the new 'resetlogs data', and offline data files are marked as media recovery required. Resetlogs data: the current scn and counter are called "resetlogs data". If the oracle database consistency check fails, the database cannot be opened, that is, open resetlogs fails. But it is not absolutely not open. You can bypass the oracle consistency check by implicit parameter "_ allow_resetlogs_curruption, however, this will cause Database Inconsistency (the file may have different scn or fuzzy bit settings). After opening, the database must be rebuilt (we recommend that you ANALYZE the TABLE... validate structure cascade; or imp/exp)