15 May, 2012

CURRENT_SCN and CHECKPOINT_CHANGE#

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 :

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:

Jakub said...

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.

Mai Thanh Hai said...

Hi Hemant's,

When does the V$DATAFILE_HEADER.CHECKPOINT_CHANGE# change?

Anonymous said...

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

Hemant K Chitale said...

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