Search

Top 60 Oracle Blogs

Recent comments

Understanding the SCN

For the DBAs who want to have a refreser on SCN (system change number), this article article is very nice and explained clearly written by Sandeep Makol. It started on where you ‘ll find info for SCN (controlfile and datafile headers) then goes to the backup and recovery scenarios where knowledge of this “magic number” is very useful.

Below are some useful scripts (with sample output) as well

-- get specific datafile
col name format a50
select 'controlfile' "SCN location",'SYSTEM checkpoint' name,checkpoint_change#
from v$database
union
select 'file in controlfile',name,checkpoint_change#
from v$datafile where lower(name) like '%&&datafile%'
union
select 'file header',name,checkpoint_change#
from v$datafile_header where lower(name) like '%&&datafile%';

SCN location        NAME                           CHECKPOINT_CHANGE#
------------------- -------------------------------------------------- ------------------
controlfile     SYSTEM checkpoint                         1151614
file header     +DATA_1/ivrs/datafile/sysaux.258.652821943            1151614
file header     +DATA_1/ivrs/datafile/system.267.652821909            1151614
file header     +DATA_1/ivrs/datafile/system_02.dbf               1151614
file header     +DATA_1/ivrs/datafile/undotbs1.257.652821933          1151614
file header     +DATA_1/ivrs/datafile/users.263.652821963             1151614
file in controlfile +DATA_1/ivrs/datafile/sysaux.258.652821943            1151614
file in controlfile +DATA_1/ivrs/datafile/system.267.652821909            1151614
file in controlfile +DATA_1/ivrs/datafile/system_02.dbf               1151614
file in controlfile +DATA_1/ivrs/datafile/undotbs1.257.652821933          1151614
file in controlfile +DATA_1/ivrs/datafile/users.263.652821963             1151614

-- get distinct checkpoint_change#
select checkpoint_change#, 'SYSTEM checkpoint in controlfile' "SCN location"
from v$database
union
select distinct checkpoint_change#, 'file in controlfile'
from v$datafile
union
select distinct checkpoint_change#, 'file header'
from v$datafile_header;

CHECKPOINT_CHANGE# SCN location
------------------ --------------------------------
       1151614 SYSTEM checkpoint in controlfile
       1151614 file header
       1151614 file in controlfile

-- get distinct datafile count
select 'controlfile' "SCN location",'SYSTEM checkpoint' name,checkpoint_change#
from v$database
union
select 'file in controlfile',to_char(count(*)),checkpoint_change#
from v$datafile
group by checkpoint_change#
union
select 'file header',to_char(count(*)),checkpoint_change#
from v$datafile_header
group by checkpoint_change#;

SCN location        NAME                           CHECKPOINT_CHANGE#
------------------- -------------------------------------------------- ------------------
controlfile     SYSTEM checkpoint                         1151614
file header     5                                 1151614
file in controlfile 5                                 1151614

-- info from x$kcvfh (All file headers)
SELECT hxfil file_num,substr(hxfnm,1,40) file_name,fhtyp type,hxerr validity, fhscn chk_ch#, fhtnm tablespace_name,fhsta status,fhrba_seq sequence
FROM x$kcvfh;

  FILE_NUM FILE_NAME                      TYPE   VALIDITY CHK_CH#      TABLESPACE_NAME            STATUS   SEQUENCE
---------- ---------------------------------------- ---------- ---------- ---------------- ------------------------------ ---------- ----------
     1 +DATA_1/ivrs/datafile/system.267.6528219      3      0 1151614      SYSTEM               8196        146
     2 +DATA_1/ivrs/datafile/undotbs1.257.65282      3      0 1151614      UNDOTBS1                4        146
     3 +DATA_1/ivrs/datafile/sysaux.258.6528219      3      0 1151614      SYSAUX                  4        146
     4 +DATA_1/ivrs/datafile/users.263.65282196      3      0 1151614      USERS                   4        146
     5 +DATA_1/ivrs/datafile/system_02.dbf           3      0 1151614      SYSTEM                  4        146

.