Checkpoint classification
1. System checkpoints (recorded in control files)
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
539625
2. Data File Checkpoint (Recorded in Control File)
SQL> select file#,checkpoint_change#,last_change# from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
1 539625
2 539625
3 539625
4 539625
5 539625
3. Data File Header Checkpoint (Recorded in Data File)
SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 539625
2 539625
3 539625
4 539625
5 539625
The above three checkpoints_change should be consistent (except for read-only and offline table spaces) before the database can be opened properly. Otherwise, a step-by-step process will be required. When closing the library normally, a new checkpoint will be generated and written to the three checkpoints_change mentioned above. At the same time, the last_change in the data file will record the checkpoint, that is to say, three checkpoints_change and last_change record the same value.
This can be demonstrated by the following SQL
SQL> shutdown immediate
SQL> startup mount
SQL> select file#,checkpoint_change#,last_change# from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
1 540270 540270
2 540270 540270
3 540270 540270
4 540270 540270
5 540270 540270
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
540270
SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 540270
2 540270
3 540270
4 540270
5 540270
When the database is successfully opened, the last_change# in the data file will be emptied. When the warehouse closes normally, the last checkpoint is restarted. shutdown abort library, this value is empty (interest can be verified by itself), at this time, the database needs to restore instances (without user intervention), after restoring, the database will open normally.
checkpoint_change#,last_change#Actually, it's all from SCN, which can be verified by the following statement:
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
540741
Using the function of the query system SCN number, you can find that checkpoint_change# is close to it. SCN has many trigger conditions and may not be particularly close.
Here are a few examples of full backup recovery, as well as checkpoint_change# in related scenarios.
Question 1: Recovery of Damaged Data Files
At this time, the checkpoint_change# recorded in the control file is larger than that recorded in the data file header, and the database needs media or instance recovery.
SQL> startup Database mounted. ORA-01113: file 1 needs media recovery ORA-01110: data file 1: '/u02/oradata/orcl/system01.dbf'
Restore and the database will open.
SQL> recover database;
ORA-00279: change 539624 generated at 10/18/2011 08:27:31 needed for thread 1
ORA-00289: suggestion : /u02/oradata/orcl/arc/1_5_764840495.dbf
ORA-00280: change 539624 for thread 1 is in sequence #5
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 540768 generated at 10/18/2011 12:17:11 needed for thread 1
ORA-00289: suggestion : /u02/oradata/orcl/arc/1_6_764840495.dbf
ORA-00280: change 540768 for thread 1 is in sequence #6
ORA-00278: log file '/u02/oradata/orcl/arc/1_5_764840495.dbf' no longer needed for this recovery
Log applied.
Media recovery complete.
SQL> alter database open;
Database altered.
Question 2: Control the recovery of file corruption
If the control file is damaged, it is impossible to open the database with the backup control file.
SQL> startup Database mounted. ORA-01122: database file 1 failed verification check ORA-01110: data file 1: '/u02/oradata/orcl/system01.dbf' ORA-01207: file is more recent than controlfile - old controlfile
The data file and control file will be prompted to be new. In fact, the checkpoint_change # recorded in the control file is smaller than the checkpoint_change # in the header of the data file. In this case, the database cannot be opened. But the data can be booted to mount state, where commands can be used
alter database backup controlfile to trace;
Generate a script for the control file in the udump directory. The script can be used to rebuild control files, restore instances or open databases.
If there is no backup control file, the database can only open the nomount state, and can not get the script of the reconstruction control file. If there is no backup control file script, it will be tragic. If the database is not too complex, you can write one by hand.
Question 3: All data files and control files are damaged (of course, backup, log is good)
After restoring data files and controlling files, the database is still unable to open.
SQL> startup Database mounted. ORA-00314: log 1 of thread 1, expected sequence# doesn't match ORA-00312: online log 1 thread 1: '/u02/oradata/orcl/redo01.log'
Tips mean that checkpoints in the log are larger than those recorded in the control file.
SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------
539624
539624
539624
539624
539624
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
539624
539624
539624
539624
539624
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
539624
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 5 10485760 1 NO CURRENT 539571 18-OCT-11
2 1 4 10485760 1 YES INACTIVE 539116 18-OCT-11
3 1 3 10485760 1 YES INACTIVE 537456 18-OCT-11
At this point, you can restore the database with the following commands
SQL> recover database using backup controlfile;
After successful recovery, the checkpoint_change# recorded in v$database did not change.
SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------
602574
602574
602574
602574
602574
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
602574
602574
602574
602574
602574
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
539624
Because of inconsistency, the database is still not open:
SQL> alter database open; alter database open * ERROR at line 1: ORA-01589: must use RESETLOGS or NORESETLOGS option for database open SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01113: file 1 needs media recovery ORA-01110: data file 1: '/u02/oradata/orcl/system01.dbf'
The situation is similar to problem 2 and the solution is the same. shutdown abort,startup nomount, rebuild control file, recover database,alter database open;
Question 4. The database is cold-standby, the data files of the newly created tablespace are damaged and there is no backup.
How to deal with this situation:
restore backup data files;
startup;
It will be prompted that the data file cannot be located and the database cannot be opened.
Alter database create data file `the name of the Unpositioned data file prompted'; - look at checkpoint_change# at this point and you will find that the new one is different from the others.
set autorecovery on
recover database;
alter database open;