Oracle SCN Research

[Note] I was studying backup and recovery, but I was not very familiar with some problems during the experiment. I went back to the root of the source and wanted to figure it out, therefore, you must understand the relationship between SCN and recovery. I have read many articles over the past two days, including the articles on the eygle website and the posts posted or discussed by Masters such as Biti on itpub, I found that my thinking was vague to clear, and then from clear to fuzzy. The deeper I went, the more I haven't fully figured out yet. So far, I decided to study it later. I wanted to write a summative article by myself, but I found that an article has been well written. I am so lazy and have copied it. Thank you for your work ~

SCN Concept
SCN is a number that increases sequentially. It is used in Oracle to identify each change to the database and its sequence. The maximum value of SCN is 0xffff. ffffffff.

SCN Management Method
Oracle manages SCN in two ways: Single Node and RAC.
In a single-node instance
In a single-node instance, the SCN value exists in the SGA zone and is protected by the system commit number latch. To obtain the current SCN value for any process, obtain the latch first.
RAC/OPS Environment
Oracle uses the queuing mechanism (enqueue) to increase the sequence of SCN between parallel nodes. There are two methods:
Lamport algorithm: Also known as the bakery algorithm, first served the algorithm. This is the same as the queuing mechanism used by many banks. The customer first receives a service number at the bank. Once a window is idle, the customer with the minimum service number can go to the idle window to handle the business.
Commit Broadcast Algorithm: Once a commit is completed, the latest SCN is broadcast to all nodes.
The preceding two algorithms can be switched by adjusting the initialization parameter max_commit_propagation_delay. In most systems (except Compaq tur64 UNIX), the default value of this parameter is 700 seconds (centisecond), using the Lamport algorithm. If the value is less than 100 seconds, Oracle uses the Broadcast Algorithm and records it in the alert. log file.
Several important SCN
Commit SCN
After you submit the commit command, the system assigns the current SCN to the transaction. The information is reflected in the redo buffer and immediately updated to the redo log file.
Offline SCN
When we execute SQL> alter tablespace... When the offline normal command is run, a checkpoint is triggered to write the dirty buffer in the memory to the disk file. After the checkpoint is completed, the data file header updates the checkpoint SCN and offline normal SCN values. The checkpoint SCN value of the database file header can be obtained through the query column x $ kccfe. fecps.
If you execute SQL> alter tablespace... When the offline command uses the temporary or immediate option, instead of the normal option, the offline normal SCN is set to 0. In this way, when the database is restarted and opened in resetlog mode, the tablespace cannot be changed back to the online status.
Checkpoint SCN
When dirty data blocks (dirty blocks) in the database memory are written to various data files, a checkpoint occurs. The current checkpoint SCN value of the database exists in x $ kccdi. discn. Checkpoint SCN plays a vital role in database recovery. No matter how you recover the database, the database can be opened only when the checkpoint SCN of each database file is the same.
Although the parameter "_ allow_resetlogs_upload uption" can force the database to be opened when the checkpoint SCN is inconsistent, such a database must be used as an export for the entire database immediately after it is opened, then the database is rebuilt and the data is imported.
Resetlog SCN
When the database is not completely recovered, the SCN after the specified time cannot be applied to the database. The scn of the resetlog is set to the current database SCN, And the redo log is reset.
Stop SCN
The stop SCN is recorded on the head of the data file. When the database is on, the Stop SCN is set to the maximum value 0xffff. ffffffff. When the database is shut down normally, the Stop SCN is set to the maximum SCN value of the current system. When the database is opened, Oracle compares the stop SCN and checkpoint SCN of each file. If the value is different, it indicates that the database has not been properly closed and needs to be restored.
High and low SCN
The redo log of Oracle records the changes in the database in sequence. After a group of redo log files are fully written, the system automatically switches to the next group of redo log files. The high SCN of the previous redo log is the low scn of the next redo log.
In view v $ log_history, sequence # indicates the serial number of the redo log, first_change # indicates the low SCN of the current redo log, and next_change # indicates the high SCN of the current redo log.
SQL> Col recid format 9999
SQL> Col requence # format 9999
SQL> Col first_change # format 9,999,999,999,999
SQL> Col next_change # format 9,999,999,999,999
SQL> select recid, sequence #, first_change #, next_change # from V $ log_history where rownumrecid sequence # first_change # next_change #
---------------------------------------------------
484 484 1,928,645,840,091 1,928,645,840,436
485 485 1,928,645,840,436 1,928,645,840,636
486 486 1,928,645,840,636 1,928,778,045,209
487 487 1,928,778,045,209 1,929,255,480,725
488 488 1,929,255,480,725 1,930,752,214,033
Relationship between the SCN number and Oracle Database Restoration
The SCN number is closely related to the restoration process of the Oracle database. Only by a good understanding of this layer can we have a profound understanding of the Restoration principle, so that we can effectively solve this problem.

SCN and checkpoint
When the ckpt process occurs at the checkpoint, it writes the current SCN number to the data file header and control file, and notifies the dbwr process to write the data block to the data file.
The ckpt process will also record the RBA (redo byte address) in the control file to indicate the location where the recovery should start from the log. There are four SCN numbers related to checkpoint, three of which exist in the control file and one is stored in the data file header.

The four are:
1. System checkpoint SCN
After the checkpoint is completed, Oracle stores the system checkpoint SCN number in the control file. We can query through the following SQL statement:
Select checkpoint_change # from V $ database;

2. datafile checkpoint SCN
After the checkpoint is completed, Oracle stores the datafile checkpoint SCN number in the control file. We can use the following SQL statement to query the datafile checkpoinnt SCN Number of all data files.
Select name, checkpoint_change # from V $ datafile;

3. Start SCN
Oracle stores the start SCN number in the data file header.
This SCN is used to check whether media recovery is required during database startup.
You can use the following SQL statement to query:
Select name, checkpoint_change # from V $ datafile_header;

4. End SCN (stop SCN) No.
Oracle stores the end SCN number in the control file.
This SCN number is used to check whether instance recovery is required during database startup.
You can use the following SQL statement to query:
Select name, last_change # from V $ datafile;
When the database runs normally, this SCN is null for the online data files that can be read and written.

Let's make a small experiment with the following content:
The SCN number is as follows before the checkpoint process is executed:
System checkpoints SCN 4609061
-- Select checkpoint_change # from V $ database;
Datafile checkpoints SCN 4609061
-- Select name, checkpoint_change # from V $ datafile;
Start SCN 4609061
-- Select name, checkpoint_change # from V $ datafile_header;
End SCN null
-- Select name, last_change # from V $ datafile;
Execute alter system checkpoint. The SCN number is as follows:
System checkpoints SCN 4609630
-- Select checkpoint_change # from V $ database;
Datafile checkpoints SCN 4609630
-- Select name, checkpoint_change # from V $ datafile;
Start SCN 4609630
-- Select name, checkpoint_change # from V $ datafile_header;
End SCN null
-- Select name, last_change # from V $ datafile;
The reasons for SCN discontinuous may be as follows:
1. When a log group is switched
2. When the log_checkpoint_timeout, log_checkpoint_interval, fast_start_io_target, fast_start_mttr_target parameters are set
3. When you run alter system switch logfile
4. When you run alter system checkpoint
5. When you run alter tablespace XXX begin backup and end backup
6. When you run alter tablespace and datafile offline;

SCN number and database startup
During database startup, when system checkpoint SCN, datafile checkpoint SCN, and start SCN are both the same, the database can be started normally without media recovery. media recovery is required if there is a difference between the three. If the end SCN is null during startup, You need to perform instance recovery. During Oracle startup, first check whether media recovery is required, and then check whether instance recovery is required.

SCN number and database Shutdown
If the database is shut down normally, a checkpoint is triggered, and the end SCN Number of the data file is set to the start SCN Number of the corresponding data file.
When the database is started and finds they are consistent, you do not need to perform instance recovery. After the database is started normally, Oracle sets the end SCN number to null. If the database is shut down abnormally, the end SCN number is null.

Why do we need system checkpoint SCN and datafile checkpoint SCN?
Why Does Oracle record the system checkpoint SCN number in the control file?
Datafile checkpoint SCN number?
There are two reasons:
1. For read-only tablespaces, The datafile checkpoint SCN, start SCN, and end SCN numbers of the data files are the same.
The three SCN will be frozen when the tablespace is read-only.
2. If the control file is not the current control file, the system checkpoint is smaller than the start SCN or end SCN number. Record these SCN numbers to identify whether the control file is the current control file.
Recovery database using backup controlfile
When a start SCN number exceeds the system checkpoit SCN number, it indicates that the control file is not the current control file. Therefore, you must use using backup controlfile for recovery. This is one of the reasons for recording systemcheckpoint SCN.
It should be mentioned that when the control file is re-built, the system checkpoint SCN is 0, and the data of the datafile checkpoint SCN comes from the start SCN. According to the preceding description, using backup controlfile must be used for recovery.
Rebuild the control file in two ways (resetlogs and noresetlogs) (the content of this section comes from: http: // space.51cto to remind you not to launch advertisements! /12361284/viewspace-346)
1. when the resetlogs option is used, system checkpoint SCN is classified as 0, the datafile checkpoint SCN of each data file recorded here is from start SCN (that is, it may be obtained from the data file header of the cold backup data file ). According to the preceding description, using backup controlfile must be used for recovery. therefore, system checkpoint SCN = 0 2. when using the noresetlogs option, there must be an online redo log. Otherwise, use the resetlogs option. When the control file is rebuilt, the system checkpoint SCN = datafile checkpoint SCN = lastest checkpoint SCN in online redo log shows that the datafile checkpoint SCN is not read from the start SCN. Instead, it reads the SCN in the latest log file as its own data. At this time, the role of the reconstructed control file in recovery is similar to that of the latest control file, system checkpoint SCN (the checkpoint SCN information of the latest redo log has been read) it may be> Start SCN (because the data file may be recovered from the cold backup). You do not need to add the using backup controlfile clause when restoring.
Supplement to backup controlfile: Backup controlfile only has archive log information at the backup time, and does not have archive log information at the DB crash time. Therefore, online redo log is not automatically applied, instead, the system prompts that the archive log with the serial number lastest archive log sequence + 1 cannot be found. Although you can manually specify the online redo log for full recovery, once the using backup controlfile clause is used, oracle is considered to be incomplete recovery and must open resetlogs! In fact, if you have an old control file and do not want to resetlogs, it is very easy to use the old control file mount and back up to trace, then manually create the control file, use reuse database... noresetlogs. in this way, you can recover the database automatically and open the database without using resetlogs (remember: all online redo logs are required !).
The backup control file cannot be completely restored automatically.
You can manually apply logs for full recovery.
If you create a new instance, the system will automatically perform full recovery (by Biti)
Example
Example Background:
Oracle 8i
Windows
RMAN is used for hot backup. During the backup, many transactions are performed, and alter system checkpoint is also performed.
RMAN> run {
2> allocate channel C1 type disk;
3> backup database filesperset 3 Format 'e:/full _ % P _ % T. Bak ';
4>}
(Here we need to mention that we have added filesperset 3 in this backup file. In this way, the entire database is divided into two backup sets. In this way, the restored data file checkpoint_change # will be different. Otherwise, because there are not many database data files, they will all be included in a backup set, so that even if the insert operation and alter system checkpoint are performed in the backup, there will be no different checkpoint_change #. Because RMAN backup backs up files in a backup set at the same time.
Checkpoint_change # is stored in the header of the data file, so that the backup time for the header of the data file will be very fast .)
Then
RMAN> run {
2> allocate channel C1 type disk;
3> Restore database;
4>}

SQL> select checkpoint_change # from V $ database;
Checkpoint_change #
------------------
2156662354
SQL> SELECT FILE #, checkpoint_change # from V $ datafile;
File # checkpoint_change #
----------------------------
1 2156662355
2 2156662354
3 2156662322
4 2156662354
5 2156662354
6 2156662354
SQL> SELECT FILE #, checkpoint_change # from V $ datafile_header;
File # checkpoint_change # last_change #
-----------------------------------------
1 2156662355
2 2156662349
3 2156662322
4 2156662342
5 2156662349
6 2156662342
It can be seen from this that media recovery is obviously required. Under normal circumstances, you still need to perform instance recovery. Of course, because there is no online log, you can only perform resetlogs.
1. Archiving logs are available
If you have archived logs, you only need to create a recover database until cancel;
Then you can alter database open resetlogs;

SQL> recover database until cancel (using backup controlfile );
ORA-00279: Change 2156621770 generated at 10/07/2005 14:30:06 needed for thread 1
ORA-00289: Suggestion: D: oracle8irdbmsarc00738.001
ORA-00280: Change 2156621770 for thread 1 is in sequence #738

Specify log: {= suggested | filename | auto | cancel}
Auto
ORA-00279: Change 2156621779 generated at 10/07/2005 14:30:51 needed for Thread
1
ORA-00289: Suggestion: D: oracle8irdbmsarc00739.001
ORA-00280: Change 2156621779 for thread 1 is in sequence #739
ORA-00278: Log File 'd: oracle8irdbmsarc00738.001 'no longer needed for this recovery

ORA-00308: cannot open archived log 'd: oracle8irdbmsarc00739.001'
ORA-27041: Unable to open file
OSD-04002: Unable to open file
O/s-error: (OS 2) the system cannot find the specified file.

SQL> alter database open resetlogs;
Database altered.

2. No archived logs
If no logs are archived, there is no online log since the restore comes out.
If checkpoint_change # In V $ datafile_header is the same, because checkpoint_change # In the control file is higher than that in the data file header, the database still needs to perform media recovery.
At this time, the reconstruction control file is still the same, because after the reconstruction control file, the checkpoint_change # In the control file is 0, which is different from the checkpoint_change # in the file header. Media recovery is also required. because the checkpoint_change # In the control file is higher than that in the file header, you must add using backup controlfile for recover.
Note: because there is no online log, you must change the noresetlogs to resetlogs to recreate the control file. Otherwise, the following error is reported:
The ORA-01565: Error in identifying file 'd: oracle8ioradataora8iredo01. Log'
ORA-27041: Unable to open file
For example:
Create controlfile reuse Database "ora8i" resetlogs archivelog
Maxlogfiles 32
Maxlogmembers 2
Maxdatafiles 254
Maxinstances 1
# Maxloghistory 226
Logfile
Group 1 'd: oracle8ioradataora8iredo01. Log 'size 1 m,
Group 2 'd: oracle8ioradataora8iredo02. Log 'size 1 m,
Group 3 'd: oracle8ioradataora8iredo03. Log 'size 1 m
Datafile
'D: oracle8ioradataora8isystem01. DBF ',
'D: oracle8ioradataora8irbs01. DBF ',
'D: oracle8ioradataora8iusers01. DBF ',
'D: oracle8ioradataora8itemp01. DBF ',
'D: oracle8ioradataora8itools01. DBF ',
'D: oracle8ioradataora8iindx01. DBF'
Character Set zhs16gbk
;
The SCN number is as follows:
SQL> select checkpoint_change #, controlfile_change # from V $ database;
Checkpoint_change # controlfile_change #
-------------------------------------
0 0

At this time, because there is no archiving log or online log, the rediscovery cannot be performed.
SQL> recover database using backup controlfile until cancel;
ORA-00279: Change 2156662342 generated at 10/07/2005 17:06:27 needed for thread 1
ORA-00289: Suggestion: D: oracle8irdbmsarc00749.001
ORA-00280: Change 2156662342 for thread 1 is in sequence #749
Specify log: {= suggested | filename | auto | cancel}
Cancel
ORA-01547: Warning: recover succeeded but open resetlogs wocould get error below
ORA-01152: File 1 was not restored from a sufficiently old backup
ORA-01110: Data File 1: 'd: oracle8ioradataora8isystem01. DBF'
ORA-01112: Media recovery not started
Therefore, you cannot alter database open resetlogs.
In this case, you can add the implicit parameter _ allow_resetlogs_uption, and then you can alter database open resetlogs to open the database.

Of course, if checkpoint_change # In V $ datafile_header is different, there is no effective way to open the database.
If it is similar, add the implicit parameter _ allow_resetlogs_uption, And then alter database open resetlogs can still be opened. It is not recommended to add this parameter to Oracle, and this parameter can only be enabled. This parameter is used to open the database with the oldest SCN. Therefore, it is recommended that the SCN Number of the system data file be the oldest; otherwise, a large number of errors may occur.