The V$DATABASE view provides the current SCN at the database level.
The V$DATAFILE view provides the SCN at which the last checkpoint was issued for each datafile.
For example :
The V$DATAFILE view provides the SCN at which the last checkpoint was issued for each datafile.
For example :
SQL> select current_scn from v$database; CURRENT_SCN --------------- 5288289 SQL> select checkpoint_change#, count(*) from v$datafile group by checkpoint_change#; CHECKPOINT_CHANGE# COUNT(*) ------------------ --------------- 5287941 14 SQL> select checkpoint_change#, count(*) from v$datafile_header group by checkpoint_change#; CHECKPOINT_CHANGE# COUNT(*) ------------------ --------------- 5287941 14 SQL>In this case, V$DATABASE's current_scn is ahead of those of the datafiles last checkpoint. We know that this is acceptable -- because the database SCN is continuously being incremented while datafiles are checkpointed only on occasion. The above values are after a database instance startup.
What if I checkpoint selective datafiles ?
SQL> alter system checkpoint; System altered. SQL> select checkpoint_change#, count(*) from v$datafile group by checkpoint_change#; CHECKPOINT_CHANGE# COUNT(*) ------------------ --------------- 5288402 14 SQL> select checkpoint_change#, count(*) from v$datafile_header group by checkpoint_change#; CHECKPOINT_CHANGE# COUNT(*) ------------------ --------------- 5288402 14 SQL> alter tablespace users offline; Tablespace altered. SQL> alter tablespace users online; Tablespace altered. SQL> select checkpoint_change#, count(*) from v$datafile group by checkpoint_change#; CHECKPOINT_CHANGE# COUNT(*) ------------------ --------------- 5288402 13 5288445 1 SQL> select checkpoint_change#, count(*) from v$datafile_header group by checkpoint_change#; CHECKPOINT_CHANGE# COUNT(*) ------------------ --------------- 5288402 13 5288445 1 SQL>
Here I have caused the datafile for the USERS tablespace to be at a higher checkpoint scn than the other datafiles.
Question : In which scenarios could the checkpoint_change# value differ between V$DATAFILE and V$DATAFILE_HEADER ?
.
.
.
4 comments:
I guess there will be difference between V$DATAFILE and V$DATAFILE_HEADER after the datafile(s) in scope will be in "hot-backup" mode (ALTER DATABASE BEGIN BACKUP), used e.g. during split mirror storage-based backups.
Hi Hemant's,
When does the V$DATAFILE_HEADER.CHECKPOINT_CHANGE# change?
V$DATAFILE.checkpoint_change# comes from control file and V$DATAFILE_HEADER.checkpoint_change# comes from file header. One scenario I can think of is, when a datafile is restored and the database is still in mount stage the checkpoint_change# of V$DATAFILe and V$DATAFILE_HEADER will be different.
-DBDude
Yes, if I restore a datafile from a backup, V$DATAFILE_HEADER is read from the datafile itself but V$DATAFILE is from the controlfile. So they could be different.
Hemant
Post a Comment