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 ?

.
.
.

2 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?

Aggregated by orafaq.com

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