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