Search My Oracle Blog

Custom Search

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

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016