The controlfile has a "Checkpoint SCN" and a "Stop SCN" for each datafile. The checkpoint SCN is recorded in the datafile header as well. There is also a "Checkpoint SCN" for each redo thread in the controlfile. Oracle works out whether recovery is needed by comparing these SCNs.
For example, here are extracts of the controlfile and file header dumps for a cold backup of a cleanly shutdown database. I've only shown the first datafile for brevity. Because the thread was checkpointed before it was shutdown, the thread checkpoint SCN and all the datafile checkpoint SCNs and datafile stop SCNs match. Also, note the closed status of the thread and the datafiles. The relevant portions are highlighted below.
THREAD #1 - status:0x6 thread links forward:0 back:0
#logs:2 first:1 last:2 current:2 last used seq#:0x3f8
enabled at scn: 0x0000.00000001 07/28/2000 14:00:08
disabled at scn: 0x0000.00000000 01/01/1988 00:00:00
opened at 02/07/2001 08:44:40 by instance o816
Checkpointed at scn: 0x0000.000efde3 02/09/2001 15:40:34
thread:1 rba:(0x3f8.e73.10)
DATA FILE #1:
(name #4) C:\ORA816\ORADATA\O816\SYSTEM01.DBF
creation size=29696 block size=2048 status=0xe head=4 tail=4 dup=1
tablespace 0, index=1 krfil=1 prev_file=0
unrecoverable scn: 0x0000.0005b05e 11/19/2000 11:11:37
Checkpoint cnt:1196 scn: 0x0000.000efde3 02/09/2001 15:40:34
Stop scn: 0x0000.000efde3 02/09/2001 15:40:34
FILE HEADER:
File Number=1, Blksiz=2048, File Type=3 DATA
Tablespace #0 - SYSTEM rel_fn:1
Creation at scn: 0x0000.00000004 07/28/2000 14:00:32
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
reset logs count:0x1816bc64 scn: 0x0000.00000001 recovered at 02/07/2001 08:44:17
status:0x0 root dba:0x00400219 chkpt cnt: 1196 ctl cnt:1195
begin-hot-backup file size: 0
Checkpointed at scn: 0x0000.000efde3 02/09/2001 15:40:34
thread:1 rba:(0x3f8.e73.10)
Here are the equivalent dumps after an earlier database crash. Note the open status of the thread and datafiles, and while all the checkpoint SCNs match, the stop SCN on the datafiles does not. In this case it is "infinity", meaning that recovery will begin at the checkpoint SCN and should proceed to the end of the redo thread. If a datafile had been offlined before the crash, it would have a stop SCN lower than the end of the thread, and would have been fully recovered once recovery passed that SCN. If that were the only datafile being recovered, Oracle would then be able to stop the recovery.
THREAD #1 - status:0x7 thread links forward:0 back:0
#logs:2 first:1 last:2 current:2 last used seq#:0x3f8
enabled at scn: 0x0000.00000001 07/28/2000 14:00:08
disabled at scn: 0x0000.00000000 01/01/1988 00:00:00
opened at 02/07/2001 08:44:40 by instance o816
Checkpointed at scn: 0x0000.000efb56 02/07/2001 08:44:40
DATA FILE #1:
(name #4) C:\ORA816\ORADATA\O816\SYSTEM01.DBF
creation size=29696 block size=2048 status=0xe head=4 tail=4 dup=1
tablespace 0, index=1 krfil=1 prev_file=0
unrecoverable scn: 0x0000.0005b05e 11/19/2000 11:11:37
Checkpoint cnt:1195 scn: 0x0000.000efb56 02/07/2001 08:44:40
Stop scn: 0xffff.ffffffff 02/07/2001 06:09:59
FILE HEADER:
File Number=1, Blksiz=2048, File Type=3 DATA
Tablespace #0 - SYSTEM rel_fn:1
Creation at scn: 0x0000.00000004 07/28/2000 14:00:32
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
reset logs count:0x1816bc64 scn: 0x0000.00000001 recovered at 02/07/2001 08:44:17
status:0x4 root dba:0x00400219 chkpt cnt: 1195 ctl cnt:1194
begin-hot-backup file size: 0
Checkpointed at scn: 0x0000.000efb56 02/07/2001 08:44:40
thread:1 rba:(0x3f8.2.10)
And here are the equivalent dumps from a hot backup. The status of the datafile shows that it is in hot backup mode, and the checkpoint SCN of the datafile has not been advanced with the thread checkpoint SCN. Thus recovery from this backup would begin at the datafile checkpoint SCN (which was triggered by the BEGIN BACKUP statement).
THREAD #1 - status:0x7 thread links forward:0 back:0
#logs:2 first:1 last:2 current:2 last used seq#:0x3f8
enabled at scn: 0x0000.00000001 07/28/2000 14:00:08
disabled at scn: 0x0000.00000000 01/01/1988 00:00:00
opened at 02/09/2001 16:39:39 by instance o816
Checkpointed at scn: 0x0000.000efe32 02/09/2001 16:40:45
thread:1 rba:(0x3f8.f35.10)
DATA FILE #1:
(name #4) C:\ORA816\ORADATA\O816\SYSTEM01.DBF
creation size=29696 block size=2048 status=0xe head=4 tail=4 dup=1
tablespace 0, index=1 krfil=1 prev_file=0
unrecoverable scn: 0x0000.0005b05e 11/19/2000 11:11:37
Checkpoint cnt:1205 scn: 0x0000.000efe2a 02/09/2001 16:39:52
Stop scn: 0xffff.ffffffff 02/09/2001 16:39:13
FILE HEADER:
File Number=1, Blksiz=2048, File Type=3 DATA
Tablespace #0 - SYSTEM rel_fn:1
Creation at scn: 0x0000.00000004 07/28/2000 14:00:32
Backup taken at scn: 0x0000.000efe2a 02/09/2001 16:39:52 thread:1
reset logs count:0x1816bc64 scn: 0x0000.00000001 recovered at 02/07/2001 08:44:17
status:0x1 root dba:0x00400219 chkpt cnt: 1205 ctl cnt:1204
begin-hot-backup file size: 29696
Checkpointed at scn: 0x0000.000efe2a 02/09/2001 16:39:52
thread:1 rba:(0x3f8.eac.10)
When Oracle finds any mismatch in these SCNs and begins recovery, the question as to whether archived redo logs will be needed (rather than just online log files) is resolve by searching those two sections of the controlfile to find which log file includes the start SCN for recovery. This is done by looking at the "Low SCN" and the "Next SCN" for each log file. Here are extracts of a control file dump for an online log file, the current log file and two archived log files respectively. Note that the first archived log file is in fact a copy of one of the inactive online log files (0x3f7 = 1015), so the online log file could be used for recovery starting at or after SCN 0x0000.000ea9c4. Any recovery from an earlier start SCN would require one or more archived log files.
LOG FILE #1:
(name #1) C:\ORA816\ORADATA\O816\REDO01.LOG
Thread 1 redo log links: forward: 2 backward: 0
siz: 0x4e20 seq: 0x000003f7 hws: 0x7 bsz: 512 nab: 0x81a flg: 0x1 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000ea988
Low scn: 0x0000.000ea9c4 02/06/2001 13:40:19
Next scn: 0x0000.000efb55 02/07/2001 08:44:40
LOG FILE #2:
(name #2) C:\ORA816\ORADATA\O816\REDO02.LOG
Thread 1 redo log links: forward: 0 backward: 1
siz: 0x4e20 seq: 0x000003f8 hws: 0xb bsz: 512 nab: 0xffffffff flg: 0x8 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000ea9c4
Low scn: 0x0000.000efb55 02/07/2001 08:44:40
Next scn: 0xffff.ffffffff 02/06/2001 13:40:19
RECID #1015 Recno 453 Record timestamp 02/07/01 08:44:40 Thread=1 Seq#=1015 Link-Recid=1014
Low scn: 0x0000.000ea9c4 02/06/01 13:40:19 Next scn: 0x0000.000efb55
RECID #1014 Recno 452 Record timestamp 02/06/01 13:40:20 Thread=1 Seq#=1014 Link-Recid=1013
Low scn: 0x0000.000ea988 02/02/01 21:04:49 Next scn: 0x0000.000ea9c4
If you would like to play around with this some more, the commands to dump the controlfile and file headers to your process trace file are as follows.
oradebug setmypid
oradebug dump controlf 10
oradebug dump file_hdrs 10
If you would like to read some more on these concepts, then Rama Velpuri's Oracle8i Backup & Recovery is the classical treatment.
When doing a datafile recovery, what different values in the control file's header are compared to those in that datafile's header before Oracle decides that archive logs are needed? I understand that "Stop SCN" is one of them, but are there other values also?