SCN
October 3, 2006 - 9am Central time zone
Reviewer: Arun from India
Hello,
Can you let me know where and how can I read the SCN related to a transaction (DML). i.e. if I
issued an update statement and later i want to use flashback on the same, how would I know the SCN
corresponding to the same transaction?
Followup October 3, 2006 - 10am Central time zone:
you cannot really - the SCN is assigned AFTER THE COMMIT (well, as part of the commit processing).
So, best you can do is grab the scn right after you commit.
Besides, flashback will never see any uncommitted work (even your own!)
SCN Change
November 10, 2006 - 8am Central time zone
Reviewer: Devendra from India
Dear Tom
I would like to know more about SCN .
The SCN is incremented whenever a transaction commits.
Suppose i do update in one table which is stored in two different datafiles it will update all
datafiles header & write information in control file after commit.
Before opening the database SMON will check the control file & datafile headers for the same SCN.
If the SCN in datafile header is not matching with the Control file that means datafile need
recovery.
From where i can get the current scn number.
select dbms_flashback.get_system_change_number from dual;
version (oracle 9i)
Thanks in advance
Devendra
Followup November 10, 2006 - 9am Central time zone:
scn is a number incremented with the completion of each and every transaction.
a commit does not write to datafiles at all. it does not update control files at all.
If you are interested in "how Oracle works" at this level - caching of blocks, the interaction
between redo and undo and so on - you might be interested in my current book "Expert Oracle
Database Architecture", I cover all of this stuff with examples and so on.
SCN Change
November 10, 2006 - 10am Central time zone
Reviewer: Devendra from India
Dear Tom,
Thanks for your reply.
Then when oracle will change the SCN number
Where i can get this book(Hard copy).Is this book published
Thanks in advance
Devendra
Followup November 10, 2006 - 2pm Central time zone:
when will oracle change the SCN? It will do that every time a transaction ends..
see home page for link to book.
Amazon.com has it
November 10, 2006 - 11am Central time zone
Reviewer: Charlie B. from Fairfax, VA USA
http://www.amazon.com/Expert-Oracle-Database-Architecture-Programming/dp/1590595300/sr=8-1/qid=11631
71304/ref=pd_bbs_sr_1/102-3482505-1184113?ie=UTF8&s=books
I don't think the book has come out in hardback. Mine isn't.
SCN for select statements
November 20, 2008 - 7am Central time zone
Reviewer: Beebo from UAE
Hello Tom ,
I read your explanation regarding SCN, you mentioned that it is generated for committed
transactions, but I read that , quote "The System Change Number is assigned many times through the
course of a session. Each DML (including SELECT) statement will get a SCN. And each DDL will get a
SCN. The statement receives a SCN when it starts. The assignment does not wait for commit or
rollback. The system uses the SCN to know the order the statements were given to the database" end
Quote
my question is, SCN number, is it a number to identify a committed transaction? or is it a number
just to identify the sequence of statements executed against the database ?
The link is down below
http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1246224,00.html
Followup November 24, 2008 - 11am Central time zone:
see, this is exactly why I like to provide a little "evidence that what I'm about to say is probably true"
Ok, say every DML was assigned an SCN. How would or could we show that is probably true?
Well, by reading out the SCN, doing a ton of DML, rereading the current SCN and outputing the difference between the two. We would presume that if an SCN was assigned to a sql statement (and it must be unique by statement if we were to use it to 'order' the sql) than the difference would be greater than or equal to the number of DML statements...
So, let us try:
ops$tkyte%ORA9IR2> create table t ( x int );
Table created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> column scn new_val scn
ops$tkyte%ORA9IR2> select dbms_flashback.get_system_change_number scn from dual;
SCN
----------
79178265
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> begin
2 for i in 1 .. 1000
3 loop
4 insert into t values ( i );
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select dbms_flashback.get_system_change_number - &SCN from dual;
old 1: select dbms_flashback.get_system_change_number - &SCN from dual
new 1: select dbms_flashback.get_system_change_number - 79178265 from dual
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER-79178265
------------------------------------------------
5
It only advanced by 5 - but we did over 1,000 dml statements (our plsql block did some dml to parse itself...). The SCN is not assigned to a SQL statement (more on this later, there is a NUGGET OF TRUTH hidden in that statement, but it got a little twisted here).
The SCN is incremented upon commit - and the SCN might jump large increments in a distributed environment as the instances "align" themselves with each other.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> column scn new_val scn
ops$tkyte%ORA9IR2> select dbms_flashback.get_system_change_number scn from dual;
SCN
----------
79178271
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> begin
2 for i in 1 .. 1000
3 loop
4 insert into t values ( i );
5 COMMIT;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select dbms_flashback.get_system_change_number - &SCN from dual;
old 1: select dbms_flashback.get_system_change_number - &SCN from dual
new 1: select dbms_flashback.get_system_change_number - 79178271 from dual
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER-79178271
------------------------------------------------
1016
See, now if you COMMIT 1,000 times, the SCN does jump by 1,000 (the other jumps are the background processes, they are always doing stuff - smon, mmom, pmon, etc - they do SQL all of the time - the database never rests :) )
Now, for the nugget of truth. Oracle does this thing called mutliversioning and read-consistency. So, what I think the author of that answer might have meant was "when you execute a query, Oracle looks at the current SCN and ensures that the data returned/touched by that query is as of that SCN - you get a read consistent result set"
If you
a) open a cursor as 9am when the SCN is 1234
b) go away for 3 hours
c) come back and see that the SCN is now 1000 (700+ transactions later)
d) start fetching the data - any block NOT COMMITTED as of SCN 1234 will be rolled back until we have the committed version of the block as of SCN 1234
This is true for all DML - and can be slightly altered if you use read only or serializable transactions.
If you use those, then the SCN your data will be 'as of' is fixed at the time your transaction begins - NOT as of the time it is executed (which is the default read committed isolation)
So, when reading information on the internet look for
a) version information (very relevant)
b) a date/timestamp - so we can see if this is from 1995 or 2005
c) AN EXAMPLE DEMONSTRATING THAT WHAT IS SAID IS PROBABLY TRUE
d) the ability TO COMMENT on the material or at least provide feedback to the author (prefer COMMENT, heavily prefer commenting)
I stress the probably - because a test case can be flawed, many of mine are from time to time :) but it gives *everyone* a chance to review the conditions and say "yeah, but...."
If I see stuff without a version, without a date, without an example - I tend to either ignore it, or set out to show it is probably right/wrong.
Unless there is no ability to comment - then I might just ignore it utterly since if I show it wrong - what then?? I cannot fix it.
March 30, 2009 - 2am Central time zone
Reviewer: A reader
You mentioned that
the SCN is assigned AFTER THE COMMIT
However even there is no commit in my personal database,
When I query
"select current_scn from v$database;"
the scn changes every second...
I also want to ask something else.
After a checkpoint, this number is stored in the headers of controlfiles and datafiles.
Is that correct?
or scn is always stored in control files which changes every second?
Followup March 30, 2009 - 4pm Central time zone:
I said that in response to
Can you let me know where and how can I read the SCN related to a transaction (DML). i.e. if I
issued an update statement and later i want to use flashback on the same, how would I know the SCN
corresponding to the same transaction?
they wanted the SCN for *their* current transaction, that doesn't exist until their transaction completes. THEIR scn isn't available yet.
checkpoints are almost always happening these days, it is a continuous process, and we update datafile headers from time to time yes - you can see them in the control files and datafile headers (change#'s are the fields to look for)
March 31, 2009 - 4pm Central time zone
Reviewer: A reader
Thanks Tom, Does the scn that changes every second (select current_scn from v$database) stored in
controlfile everytime or only after the checkpoint. I mean if the current scn is 100, it is written
controlfile, when it is 101, again it is written controlfile,and so on... One more thing is the scn
also written to redologs and archivelogs...
Followup April 1, 2009 - 8am Central time zone:
the scn increments with every commit, not every second. It is not a "ticker".
It is written to the controlfiles when the control files are updated - which happens as the result of a few things, one being "end of official checkpoint".
April 1, 2009 - 3pm Central time zone
Reviewer: A reader
Even there is no commit in my database( I am the only user). The scn again always increments. Why
Tom? One more thing, the chechpoint scn of the datafiles stored in controlfile, does controlfile
also store checkpoint change# for itself?
Followup April 1, 2009 - 4pm Central time zone:
hah, you are NEVER the only user.
ops$tkyte%ORA10GR2> select username, program from v$session;
USERNAME PROGRAM
------------------------------ ------------------------------------------------
OPS$TKYTE sqlplus@dellpe (TNS V1-V3)
oracle@dellpe (q001)
oracle@dellpe (q000)
oracle@dellpe (QMNC)
oracle@dellpe (MMNL)
oracle@dellpe (MMON)
oracle@dellpe (CJQ0)
oracle@dellpe (RECO)
oracle@dellpe (SMON)
oracle@dellpe (CKPT)
oracle@dellpe (LGWR)
oracle@dellpe (DBW0)
oracle@dellpe (MMAN)
oracle@dellpe (PSP0)
oracle@dellpe (PMON)
15 rows selected.
the database never sleeps. Most of those other "programs" do transactions and commit.
April 2, 2009 - 5am Central time zone
Reviewer: A reader
hey Tom
Does the scn stored in redologs or archivelogs?
I know it is stored in controlfile and datafiles but Im not sure about redo and archives
Followup April 2, 2009 - 10am Central time zone:
yes, it is. archives are just copies of redo, so it is in both
April 3, 2009 - 1am Central time zone
Reviewer: Prasad from San Francisco,CA
Hi Tom,
Is there a limitation on the number of scns that can be generated in a second ? Thanks
Followup April 3, 2009 - 7am Central time zone:
physics and the speed of light mostly.
how many commits can you do in a second on your machine?
a second is a rather arbitrary unit of time - one that a computer doesn't really care about. The number of commits per second is regulated by your hardware and the amount of work you try to do on that hardware.
April 3, 2009 - 5am Central time zone
Reviewer: A reader
so,
after checkpoint issued, scn is written to both datafiles,controlfiles and redologs.
Followup April 3, 2009 - 7am Central time zone:
scn's are written to redo logs continuously - when you commit they are emitted into the redo stream.
and semantically speaking, after the checkpoint COMPLETES, not after the checkpoint is INITIATED
April 3, 2009 - 9am Central time zone
Reviewer: Aman.... from India
Sir,
You have mentioned somewhere above (and I have read the same in oracle docs as well) that the now
the precision of the scn timing is +/- 3 seconds compared to 5 minutes <10g releases. How does we
can see it? The result that I am seeing from smon_scn_time is some what not matching with it, the
difference of teh time is 5 seconds.
Please have a look here,
SQL> select time_mp,time_dp, scn_wrp, scn_bas, scn from smon_scn_time
TIME_MP TIME_DP SCN_WRP SCN_BAS SCN
--------- ------------------ ---------- ---------- ----------
683207443 03-apr-09:11:50:43 0 751619 751619
683207755 03-apr-09:11:55:55 0 751743 751743
683208043 03-apr-09:12:00:43 0 751904 751904
683208355 03-apr-09:12:05:55 0 752029 752029
683208643 03-apr-09:12:10:43 0 752188 752188
683208955 03-apr-09:12:15:55 0 752329 752329
683209243 03-apr-09:12:20:43 0 752449 752449
683209555 03-apr-09:12:25:55 0 752595 752595
683209843 03-apr-09:12:30:43 0 752737 752737
683210164 03-apr-09:12:36:04 0 752963 752963
683210443 03-apr-09:12:40:43 0 753082 753082
TIME_MP TIME_DP SCN_WRP SCN_BAS SCN
--------- ------------------ ---------- ---------- ----------
683210744 03-apr-09:12:45:44 0 753228 753228
683211068 03-apr-09:12:51:08 0 753601 753601
683211344 03-apr-09:12:55:44 0 753733 753733
683211662 03-apr-09:13:01:02 0 753873 753873
683211944 03-apr-09:13:05:44 0 754011 754011
683212265 03-apr-09:13:11:05 0 754173 754173
683212544 03-apr-09:13:15:44 0 754288 754288
683212862 03-apr-09:13:21:02 0 754437 754437
683213144 03-apr-09:13:25:44 0 754549 754549
683213465 03-apr-09:13:31:05 0 754780 754780
683213540 03-apr-09:13:32:20 0 754811 754811
How do we see that the precision is now to 3 seconds or is it some thing that is used by Oracle
itself?
In the last, is there any ML which depicts smon_scn_time table in some detail? I tried a lot to
search for its description but got no success.
Best regards
Aman....
Followup April 3, 2009 - 9am Central time zone:
It is internally done. if you look at that table - all of the columns - there is a field TIM_SCN_MAP, it is hidden in there.
"ML" ? not even a shadow of a clue what ML is or means.
But no, smon_scn_time is just an internal table, your interface to it are the API's we provide to convert scn's into times
April 3, 2009 - 10am Central time zone
Reviewer: Aman.... from India
Sir,
I am sorry, I meant Metalink, really sorry.
So the tim_scn_map column contains that mapping but its a RAW column and as you mentioned, oracle
has "hacked" the mapping in this column. I did try to get that mapping out but somehow , I got no
where. My intention is to see that 3 seconds precision some how, is there any way I can get that
sir?
Thanks and regards
Aman....
Followup April 3, 2009 - 11am Central time zone:
I told you how.
by using the APIs to access that information.
ops$tkyte%ORA10GR2> select scn_to_timestamp(scn) ts, min(scn), max(scn)
2 from (
3 select dbms_flashback.get_system_change_number()-level scn
4 from dual
5 connect by level <= 100
6 )
7 group by scn_to_timestamp(scn)
8 order by scn_to_timestamp(scn)
9 /
TS MIN(SCN) MAX(SCN)
------------------------------- ---------- ----------
03-APR-09 10.00.32.000000000 AM 82525082 82525120
03-APR-09 10.00.38.000000000 AM 82525121 82525121
03-APR-09 10.00.41.000000000 AM 82525122 82525122
03-APR-09 10.00.44.000000000 AM 82525123 82525123
03-APR-09 10.00.47.000000000 AM 82525124 82525124
03-APR-09 10.00.50.000000000 AM 82525125 82525125
03-APR-09 10.00.53.000000000 AM 82525126 82525126
03-APR-09 10.00.56.000000000 AM 82525127 82525127
03-APR-09 10.00.59.000000000 AM 82525128 82525128
03-APR-09 10.01.02.000000000 AM 82525129 82525129
03-APR-09 10.01.05.000000000 AM 82525130 82525130
03-APR-09 10.01.08.000000000 AM 82525131 82525131
03-APR-09 10.01.11.000000000 AM 82525132 82525132
03-APR-09 10.01.14.000000000 AM 82525133 82525133
03-APR-09 10.01.17.000000000 AM 82525134 82525134
03-APR-09 10.01.20.000000000 AM 82525135 82525135
03-APR-09 10.01.23.000000000 AM 82525136 82525136
03-APR-09 10.01.26.000000000 AM 82525137 82525137
03-APR-09 10.01.29.000000000 AM 82525138 82525138
03-APR-09 10.01.32.000000000 AM 82525139 82525139
03-APR-09 10.01.35.000000000 AM 82525140 82525140
03-APR-09 10.01.38.000000000 AM 82525141 82525141
03-APR-09 10.01.41.000000000 AM 82525142 82525142
03-APR-09 10.01.44.000000000 AM 82525143 82525143
03-APR-09 10.01.47.000000000 AM 82525144 82525144
03-APR-09 10.01.50.000000000 AM 82525145 82525145
03-APR-09 10.01.53.000000000 AM 82525146 82525146
03-APR-09 10.01.56.000000000 AM 82525147 82525147
03-APR-09 10.01.59.000000000 AM 82525148 82525148
03-APR-09 10.02.02.000000000 AM 82525149 82525149
03-APR-09 10.02.05.000000000 AM 82525150 82525150
03-APR-09 10.02.08.000000000 AM 82525151 82525151
03-APR-09 10.02.11.000000000 AM 82525152 82525152
03-APR-09 10.02.14.000000000 AM 82525153 82525153
03-APR-09 10.02.17.000000000 AM 82525154 82525154
03-APR-09 10.02.20.000000000 AM 82525155 82525155
03-APR-09 10.02.23.000000000 AM 82525156 82525156
03-APR-09 10.02.26.000000000 AM 82525157 82525157
03-APR-09 10.02.29.000000000 AM 82525158 82525158
03-APR-09 10.02.32.000000000 AM 82525159 82525159
03-APR-09 10.02.35.000000000 AM 82525160 82525160
03-APR-09 10.02.38.000000000 AM 82525161 82525161
03-APR-09 10.02.41.000000000 AM 82525162 82525162
03-APR-09 10.02.44.000000000 AM 82525163 82525163
03-APR-09 10.02.47.000000000 AM 82525164 82525164
03-APR-09 10.02.50.000000000 AM 82525165 82525165
03-APR-09 10.02.53.000000000 AM 82525166 82525166
03-APR-09 10.02.56.000000000 AM 82525167 82525167
03-APR-09 10.02.59.000000000 AM 82525168 82525168
03-APR-09 10.03.02.000000000 AM 82525169 82525169
03-APR-09 10.03.05.000000000 AM 82525170 82525170
03-APR-09 10.03.08.000000000 AM 82525171 82525171
03-APR-09 10.03.11.000000000 AM 82525172 82525172
03-APR-09 10.03.14.000000000 AM 82525173 82525173
03-APR-09 10.03.17.000000000 AM 82525174 82525174
03-APR-09 10.03.20.000000000 AM 82525175 82525175
03-APR-09 10.03.23.000000000 AM 82525176 82525176
03-APR-09 10.03.26.000000000 AM 82525177 82525177
03-APR-09 10.03.29.000000000 AM 82525178 82525178
03-APR-09 10.03.32.000000000 AM 82525179 82525179
03-APR-09 10.03.35.000000000 AM 82525180 82525180
03-APR-09 10.03.38.000000000 AM 82525181 82525181
62 rows selected.
see how they are all 3 seconds apart?
April 3, 2009 - 2pm Central time zone
Reviewer: aman
Sir,
Its my heartiest wish to know SQL even just 1% of how much you know! Thanks a bunch, I got it.
regards
Aman....
April 27, 2009 - 2am Central time zone
Reviewer: A reader
Dear Tom
You told me that;
Even if there is no users in the database, there are always transactions therefore commits and SCN
increments after every commit.
I am just wondering, if these transactions recorded in redologs ?
or
only users transactions recorded in redologs
Followup April 27, 2009 - 2pm Central time zone:
every transaction is recorded there, yes.
The background processes are just users in a special sense.
May 10, 2009 - 10pm Central time zone
Reviewer: A reader
Sir you mentioned that
"scn's are written to redo logs continuously - when you commit they are emitted into the redo
stream."
Since SCN changes every commit, it will be written to redologs every commit.However,I dont
understand the what does emmitting into redo stream means.Can you explain it?
Followup May 11, 2009 - 5pm Central time zone:
emit just means to "put into", "to send forth", "discharge"
we put them into the redo stream continuously - we 'emit' them continuously.
someone wrote:
... after checkpoint issued, scn is written to both datafiles,controlfiles and redologs. ...
I just said "we are doing that all of the time"
May 11, 2009 - 5pm Central time zone
Reviewer: A reader
Thanks sir I understand that SCN is written redologs continuesly. Is it same for datafiles and
controlfiles ? or is it written after checkpoint
more about SCN
May 13, 2009 - 10pm Central time zone
Reviewer: Rainey
dear TOM :
When we dump a block,we can got the scn information about the block.if this scn is the same as
the one in the datafile,controlfile or redo logfile,or if the scn information are same in all the
blocks of datafiles
Thanks a lot
Followup May 14, 2009 - 10am Central time zone:
you'll never find them all to be the same/to match up. They are all recorded at differing points in time.
May 14, 2009 - 10pm Central time zone
Reviewer: Rainey
Dear Tom
As you say above,the SCN information in the block is the the time when the block was changed and
commited,it is the transaction time of that time,but the SCN information in the datafile
head,controlfile time or redo logfile is about the current time. Is that right?
Best regards
Followup May 15, 2009 - 1pm Central time zone:
think about it - if the files are not continuously updated in a "quantum mechanics way" (eg: truly simultaneously and constantly) - they can never be "current time"
The information in the datafiles, the controlfiles provide a baseline, a marker such that "stuff that happened before this need not be applied to us again"
May 31, 2009 - 7am Central time zone
Reviewer: Scofield
sir;
The checkpoint scn in datafile headers are updated after checkpoint.
As far as I read from this thread;
scn's are written to redo logs continuously, it should also stored in controlfile continuesly(every
commit) so that controlfile will know where redochain ends ..
Is that right?
Followup June 1, 2009 - 7pm Central time zone:
... The checkpoint scn in datafile headers are updated after checkpoint. ...
sure, and lots of other times - many millions of times - but sure.
... scn's are written to redo logs continuously, it should also stored in
controlfile continuesly(every commit) so that controlfile will know where
redochain ends ..
.....
no, not at all. the stuff in the control file talks about the DATAFILES - not the redo log files. why would the stuff in the redo log files affect the processing of the controlfiles which show information about DATAFILES?
June 6, 2009 - 9pm Central time zone
Reviewer: A reader
Sir
you mentioned that:
"the stuff in the control file talks about the DATAFILES - not the redo log files"
But controlfile should know the info in redologs because when I the control file is older than
redologs , only way to open to db is with resetlogs option,since it doesnt know where redo chain
ends. How come this happen?
Followup June 8, 2009 - 1pm Central time zone:
there is a ton of information in the control files, the backup history, the archive history, stuff about datafiles. A ton of stuff.
someone wrote above:
... scn's are written to redo logs continuously, it should also stored in
controlfile continuesly(every commit) so that controlfile will know where
redochain ends ..
and I said "no, not really, control file scn related stuff that is recorded - about the datafiles - we do not write stuff in there every commit"
that is all.
if you use a backup controlfile - we lost history (of archives) we lost backup information and so on - that is why you need to "guide us" if you nuke your control files - because we lost information about the archives/redo.
June 10, 2009 - 4am Central time zone
Reviewer: A reader
Sir, In the following scenerios, I have to use "recover using backup controlfile"
1-) if the controlfile is older than redo (doesnt know redo chain ends)
or
2-) datafile CKPT SCN ahead to the controlfile CKPT SCN.
Are there any scenerios? In short what is purpose of using "recover database using backup
controlfile"?
I want to know your enlightining explanation rather than reading same things from manuels..
Followup June 10, 2009 - 12pm Central time zone:
1) how would the controlfile be older than the redo - unless you restored an older controlfile - in which case "of course you would say 'using backup controlfile' since you obviously restored a backup????
So, the answer is "sure", but also "huh? it should be obvious since the way to get a control file older than redo is to.... well... restore it from a backup...
2) again, how would this happen? if you restore a backup controlfile. so once again, it is a backup.
... I want to know your enlightining explanation rather than reading same things
from manuels.....
you should rethink that position, I'm flattered and all - but seriously, start with the documentation - ask questions after you read it.
We'll both be a lot happier that way.
July 13, 2009 - 5am Central time zone
Reviewer: A reader
Sir;
In Oracle we can issue "recover until time".
Does oracle store time as well as scn for every transaction or simply convert scn to time?
Followup July 14, 2009 - 5pm Central time zone:
it converts time into an SCN that is approximately the scn in place at that time.
time based recovery is very "approximately", it will be "around that time". We do not have a precise mapping of time to SCN - and recovery is always SCN based. It just determines the nearest SCN it knows about close to that time.
October 10, 2009 - 2am Central time zone
Reviewer: A reader
Hi sir;
Even there is no active user in the database, there is always activity by oracle processes and scn
increments frequently.
What kind of action oracle perfoms in the background so that current_scn increments every commit?
To verify dataguard sync
October 24, 2009 - 2am Central time zone
Reviewer: Deep from usa
Thanks Tom for good explaination on SCN.
We have maximize availabilty dataguard configuration and want to check how many SCNs we lost when
do the failover.So we were trying to see how much SCN difference we get between two databases.So My
question is,
1. Lets say primary crashed when SCN was 1000 and at standby the SCN 995 is available.Now if we
open the standby database in read only mode then will it have 995 SCN and will it keep on
incrementing thereafter?
2. Primary database crashed at 1000 SCN, so when we bring up the database will it have 1000 SCN in
the beginning when its open. OR will the SCN keep incrementing during db open process even though
all the transactions have assigned SCN before crash. I mean will there be SCN increments during the
db open process?
Do you suggest anything else to measure the data loss due to failover?
Thanks...Deep
Followup October 26, 2009 - 1pm Central time zone:
1) if you open read only, it is read only - a commit increments the system change number, you don't commit in a read only database.
2) when you recover the primary database, the scn will almost immediately increase - yes.
But, to answer your question - what would the fact that primary was scn 10000 and standby was at 10000-n tell you as far as data loss goes?
In fact, if you open standby read only, and then you recover primary - the primary will start sending data to standby again - meaning there is NO data loss?
November 6, 2009 - 10pm Central time zone
Reviewer: edwin from Cambodia
Hi Tom;
If I take the datafile offline, I will need recovery until stop scn of the datafile.
The reason behind this, checkpoint is not issued.
I know the task of checkpoint (write dirty blocks to disk,etc) but what is the relation between
checkpoint and recovery?
Dirty blocks can be written after some other checkpoints, why do I need recovery here?
Followup November 11, 2009 - 1pm Central time zone:
... If I take the datafile offline, I will need recovery until stop scn of the
datafile.
...
not if you take it offline normal? Not sure what you mean. If you take it offline immediate (can only be done in archive log mode) then it will need recovery because blocks that are in the cache but not yet on disk in that datafile will need to be recreated when you want to online it again - and we need the redo to do that (that is why you have to be in archive log mode for offline immediate to work - we need the archives to recover that file later)
so, no, in general offline of a datafile will NOT necessitate recovery - in most cases it will not.
only if you offline immediate would we need to do that.
November 8, 2009 - 9am Central time zone
Reviewer: A reader
Dear Tom,
I have a table that has no date column at a remote site. The remote site is on 9i. I need to select
data of that table on daily basis and insert that into another table at another server which is on
10g through database links.
Lets suppose i configured jobs at hourly basis to select data and insert it to 10g table.But how
should i compare the time.
For example.
insert into t1_10g select * from t1_9i@db_lnk where ???
Can I use SCN for this purpose?
I cannot add a new column in the table.
Thanks
Followup November 11, 2009 - 2pm Central time zone:
you are out of luck then.
Please think about this - it is the equivalent of "I need to do magic here"
a) I have no date columns
b) but how should I compare time
(a) and (b) conflict with eachother.
Data replication cannot be performed without lots of work on BOTH SIDES of the network. The source system must help you and the target system must do what it needs to do. You cannot magically do something without changing *something*.
ora_rowscn didn't exist way back in 9i
even if it did, it isn't at the row level by default, you'd need to rebuild a table to get it.
If you cannot look at your data and tell me "what day was it inserted", we cannot tell you either.
You'll need to use your knowledge of the data to figure this out and figure out if you can even do it.
Suppose you only need to capture inserts, not updates, not deletes. Suppose the table in question has a primary key populated by a sequence. You could:
a) remember the last primary key you pulled in your data - start with 0 the first time.
b) go to the source database and insert into your_table select * from their_table@remote where key > :that_value
c) remember the highest key you just pulled - eg: update some_local_table set high_key = (select max(key) from your_table)
But you will have to analyze your data and understand how YOU can find out what has been "added, changed, removed" on your own
Or you can use replication (streams for example, materialized views, whatever) if you really need this data copied
Or - best way every - move the stuff from your database into that other database (consolidate) and just have access to the current stuff
November 14, 2009 - 3pm Central time zone
Reviewer: Edwin from Cambodia
Thanks Tom;
You said that:
"If you take it offline immediate (can only be done in archive log mode) then it will need recovery
because blocks that are in the cache but not yet on disk in that datafile will need to be recreated
when you want to online it again - and we need the redo to do that"
1-)
Suppose,I forced checkpoint and perfom offline immediate immediately? Why do I again need recovery?
or
Suppose there is no block in buffercache and I perform offline immediate, why do I again need
recovery?
2-)Suppose I have a dirty block in buffer cache and I perform offline immediate, does this dirty
block remain in
buffer cache until I bring up the datafile?
Sorry Tom, it is little bit difficult to grasp
Followup November 15, 2009 - 3pm Central time zone:
1) because you cannot atomically "checkpoint + offline immediate"
In fact, you cannot atomically "checkpoint"
suppose the checkpoint take 5 seconds. During that 5 seconds - would you like to guess how many blocks I can dirty in the cache for that datafile? Answer: it is a lot, only 1 is necessary, but it is greater than 1.
You cannot say what you say, because you don't know if there are any dirty blocks or not and neither do we.
2) no, it would not. You could shutdown and restart - blowing the SGA entirely away.
needed magic thats why went to the best magician!!
November 16, 2009 - 6am Central time zone
Reviewer: A reader
Dear Tom,
I needed a magic and I came to the best magician. :-)
I have configured something like this.It solves the purpose with approximation.
I have used composite primary key as filter.Whats I a m doing is:
insert into t1 select * from t1@dblnk where (col1,col2,col3,col4) not in (select
col1,col2,col3,col4 from t1);
Followup November 23, 2009 - 9am Central time zone:
I'm not a magician - not by any means. Actually, if you ask me, that is fairly close to an insult :)
that approach will work for teeny tiny bits of data, but not long term.
and I hope that table is never deleted from nor updated.
Actually, since you are pretty much pulling the entire table locally every time anyway - why not just refresh full - just create a materialized view refresh complete and be done with it. No need to filter anything, just *replace* the entire set - you are pulling the entire set.
How is SCN tracked in case of Memory process commit
December 14, 2009 - 8am Central time zone
Reviewer: Abhisek from India
Hi Tom,
I understand when you say that I am not the lone user in the system. Other background process are
there which commit the special changes that are bound to.
My question is:
1. If commit through memory process all along while a user is reading or writing data, how is
non-dirty read ensured? Is there a role for undo as well?
2. Can I see the process name that are doing commit everytime?
3. Do we have a function that acts just opposite to scn_to_timestamp means converts a timestamp to
SCN.. I know its unnecessary but I am just curious..
Please help me in resolving the queries
Followup December 14, 2009 - 10am Central time zone:
1) I could not parse that sentence.
What does "if commit through memory process" mean?
What does "is there a role for undo as well?" mean?
Basically, you open a query. We ask ourselves "what time is it", we remember that time (scn) - say 12350.
As we are hitting database blocks for your query, we look at the transaction header in the block - we look at the last transaction(s) to hit that block and ask the undo segment headers "when did this transaction commit". The undo segment header can tell us
a) it committed a long time ago, this block is good enough for you.
b) it committed at scn 12345 (and 12345 <= 12350 - so this block is good)
c) it committed at scn 12455 (and 12555 > 12350 - this block will be rolled back)
d) it has not committed yet (and this block will be rolled back)
In the cases of (a) and (b), we use the block as is
In the cases of (c) and (d), we ask the undo segment for the rollback data needed to put the block back the way it was before the last change - and we REPEAT the process until we get an (a) or (b) answer - then we use that block version.
2) you can use log miner to see what is in the redo, information will be in there - might not be the process, but it really isn't necessary to know anyway.
3) http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions176.htm#SQLRF06326
How is SCN tracked in case of Memory process commit
December 15, 2009 - 12am Central time zone
Reviewer: Abhisek from India
Thanks Tom for the reply. I am sorry for a confusing question. With your reply to a query in
reference, I meant we know that we are NEVER the only user.
select username, program from v$session;
Now, since the database never sleeps. Most of those other "programs" do transactions and commit.
So does that mean that after the commit due to these commits as well, the data is written to the
datafiles from the dirty/pinned blocks? Or are these special commit that dont have any relation
with database writes?
Followup December 15, 2009 - 9am Central time zone:
I have no clue still what "Memory process commit " is, does, or means.
A commit by any other name would still persist data.
A commit is a commit is a commit - period. I don't care who issues it, it is a commit - whether it comes from PMON, SMON, or you.
SCN
December 15, 2009 - 10am Central time zone
Reviewer: Abhisek from India
Hi Tom,
I am so sorry for being dumb.. By Memory process I meant the process like SMON, PMON etc..
Thanks a lot.
Followup December 15, 2009 - 10am Central time zone:
those are called background processes for the database - or by their names. There is no such thing as a "memory process"
SCN
December 17, 2009 - 11pm Central time zone
Reviewer: Manoj from India
Hi Tom,
As far as I could understand going through replies, that SCN number is incremented by background
processes, even though I am single user and not doing anything. Please correct me if I am wrong.
If I am correct, then once user start transaction and they also commit, how are the commits from
background processes and users handled?
How is dirty read avoided? Can you please explain with some example so that I can easily understand.
Followup December 18, 2009 - 12pm Central time zone:
that scn number is being incremented by some process issuing "commit". The background processes - all of them - have the ability to do that, SMON will do it regularly, like clockwork.
A commit is a commit, like a rose is a rose is a rose. I don't care if a 'background' does it, or a foreground, or above ground, below ground, in ground - it doesn't matter. A commit is a commit is a commit is a commit.
... How is dirty read avoided? Can you please explain with some example so that I
can easily understand. ...
http://www.oracle.com/technology/oramag/oracle/05-nov/o65asktom.html
January 30, 2010 - 12am Central time zone
Reviewer: A reader
Hi Sir;
What if the transaction is rolled-back? Does the scn again increase?
Followup February 1, 2010 - 9am Central time zone:
yes:
ops$tkyte%ORA11GR2> create table t ( x int );
Table created.
ops$tkyte%ORA11GR2> column scn new_val s
ops$tkyte%ORA11GR2> select dbms_flashback.get_system_change_number scn from dual;
SCN
----------
155294980
ops$tkyte%ORA11GR2> begin
2 for i in 1 .. 1000
3 loop
4 insert into t values ( i );
5 rollback;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> select scn, scn-&s from (
2 select dbms_flashback.get_system_change_number scn from dual
3 );
old 1: select scn, scn-&s from (
new 1: select scn, scn- 155294980 from (
SCN SCN-155294980
---------- -------------
155297004 2024
ops$tkyte%ORA11GR2> column scn new_val s
ops$tkyte%ORA11GR2> select dbms_flashback.get_system_change_number scn from dual;
SCN
----------
155297004
ops$tkyte%ORA11GR2> begin
2 for i in 1 .. 10000
3 loop
4 insert into t values ( i );
5 rollback;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> select scn, scn-&s from (
2 select dbms_flashback.get_system_change_number scn from dual
3 );
old 1: select scn, scn-&s from (
new 1: select scn, scn- 155297004 from (
SCN SCN-155297004
---------- -------------
155317184 20180
even more than if you do not rollback - but commit instead
ops$tkyte%ORA11GR2> create table t ( x int );
Table created.
ops$tkyte%ORA11GR2> column scn new_val s
ops$tkyte%ORA11GR2> select dbms_flashback.get_system_change_number scn from dual;
SCN
----------
155317244
ops$tkyte%ORA11GR2> begin
2 for i in 1 .. 1000
3 loop
4 insert into t values ( i );
5 commit;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> select scn, scn-&s from (
2 select dbms_flashback.get_system_change_number scn from dual
3 );
old 1: select scn, scn-&s from (
new 1: select scn, scn- 155317244 from (
SCN SCN-155317244
---------- -------------
155318290 1046
ops$tkyte%ORA11GR2> column scn new_val s
ops$tkyte%ORA11GR2> select dbms_flashback.get_system_change_number scn from dual;
SCN
----------
155318290
ops$tkyte%ORA11GR2> begin
2 for i in 1 .. 10000
3 loop
4 insert into t values ( i );
5 commit;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> select scn, scn-&s from (
2 select dbms_flashback.get_system_change_number scn from dual
3 );
old 1: select scn, scn-&s from (
new 1: select scn, scn- 155318290 from (
SCN SCN-155318290
---------- -------------
155328505 10215
ops$tkyte%ORA11GR2>
we are definitely optimized to commit, rolling back is expensive.
February 6, 2010 - 12am Central time zone
Reviewer: A reader
Thanks for the example sir;
I understand that Scn increases both commit and rollback.
Regarding the rollback, scn increases much more than commit.
One last question:
Is there any difference between:
select CURRENT_SCN from v$database;
and
select dbms_flashback.get_system_change_number scn from dual;
|