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 |
.
Really interesting topics..
I have one question concerning redolog sequence, what would it mean if I have a gap in between consecutive sequence number.
THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
———- ———- ————- ————
2 118 5966403 5966562
2 119 5966562 5966577
2 120 5966577 5970293
2 121 5970293 5970310
2 122 5977084 5977094
There seems to be a gap between sequence 121 and 122. I have noticed that after a dataguard switchover on a RAC configuration.
Do I have to consider this as normal or this is normal after a switchover ?
Thanks for any inputs.
Hmm.. interesting, how many nodes you have on the physical standby?
I will check on some of my notes regarding switchover on RAC standby and I’ll get back to you.
I have 2 nodes on the primary site and 2 nodes on the standby site.
I can reproduce this behaviour, i checked alert.log and differents v$ view, and even a SET log_archive_trace=8191, but i don’t see why i have this type of gap.
I noticed this since I have a backup script which chech possible gap in archivelog.. otherwise just checking sequence#, I would have not noticed it.
Except that when I tried RMAN recovery, I have a weird behaviour.
Thanks for your help.
What was the weird behavior that you encountered on recovery?
Yes, you are right.. I’m more on checking the gap for the sequence# too and actually checking the data when doing the switchover.
BTW, I’m interested to see you backup script. Can you mail it at karlarao@gmail.com ?
Thanks!
By the way, I loggued an SR since I found this really weird.. I guess most people won’t check the gap at the SCN level, thet rather check the gap at the sequence number.
Here is the SR answer :
This is not a bug – development analyzed this under
Bug 9027206: NEXT_CHANGE# DOES NOT MATCH FIRST_CHANGE# IN RAC
Status92 – Closed, Not a Bug
When a thread get disabled, it is possible to have a gap between the
next_change# and the following first_change# because the thread is known as
inactive/disabled and don’t need to record any scn history during that time.
I guess by looking at DBA_HIST_THREAD, I should be able to take this “special case” out of my SCN gap check script.