Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 29 May 2012

Discussion on SCN and Checkpoint in Oracle

What is SCN and why it is used? What happens if the SCN in datafile header is not matching with the Control file?
The system change number (SCN) is Oracle's clock, every time we commit the clock increments. The SCN just marks a consistent point in time in the database. The checkpoint SCN in datafile headers are updated after checkpoint. 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.
How to find Current SCN?
SQL> select dbms_flashback.get_system_change_number from dual; VER. Oracle 9i
SQL> select to_char(current_scn) from v$database;                              VER. Oracle 10g
What is checkpoint? Is checkpoint is related to SCN? Why checkpoint numbers get increased non sequence to higher value?
LGWR or CKPT writes the redo log sequence to the datafile headers and control files and tells the DBWR to write dirty buffers from the dirty buffer write queue (buffer cache) to disk. It is a record indicating the point in the redo log where all DB changes prior to this point have been saved in the datafiles.
The database ALWAYS has transactions going on, ALWAYS.  SMON and many other background processes are always doing work, the database (unless it is opened read only) is always doing transactions. Now, since the database never sleeps. Most of those other “programs” do transactions and commit. SQL>select username, program from v$session;
The justification against the 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 ?
SQL> create table s ( x int );
Table created.
SQL> Select dbms_flashback.get_system_change_number scn from dual;
SCN
----------
79178265
SQL> begin
            for i in 1 .. 1000
            Loop
                 insert into s values ( i );
            end loop;
    end;
    /
PL/SQL procedure successfully completed.
SQL> select dbms_flashback.get_system_change_number - &SCN from dual;
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER-79178265
------------------------------------------------
5
It only advanced by 5 - but we did over 1,000 DML statements thus the SCN is not assigned to a SQL statement. The SCN is incremented upon commit.
SQL>
SQL> select dbms_flashback.get_system_change_number scn from dual;
SCN
----------
79178271
SQL> begin
            for i in 1 .. 1000
            loop
               insert into s values ( i );
            COMMIT;
            end loop;
    end;
PL/SQL procedure successfully completed.
SQL> select dbms_flashback.get_system_change_number - &SCN 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, MMON, PMON, etc. they do SQL all of the time - the database never rests
Is there a limitation on the number of SCN that can be generated in a second?
It is depends upon number of commit you are doing
How can we check precision of SCN Timing?
SQL> select time_mp,time_dp,  scn_wrp, scn_bas from smon_scn_time;
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, by using the APIs you can access that information.
SQL>select scn_to_timestamp(scn) ts, min(scn), max(scn)
        from (
           select dbms_flashback.get_system_change_number()-level scn
        from dual
         connect by level <= 100
         )
    Group by scn_to_timestamp(SCN);
    Order by scn_to_timestamp(SCN);
What if the transaction is rolled-back? Does the SCN again increase?
Yes it is, check out this Example
SQL> CREATE TABLE S1 (ENO NUMBER(4), ENAME VARCHAR2(20));
Table created.
SQL> Select dbms_flashback.get_system_change_number scn from dual;
SCN
----------
8806085
SQL> begin
   for i in 1 .. 1000
   loop
   insert into S1 values ( 1, 'SHAAN' );
   rollback;
   end loop;
   end;
   /
PL/SQL procedure successfully completed.
SQL> select scn, scn-8806085 from (
       select dbms_flashback.get_system_change_number scn from dual
      );
SCN SCN-8806085
---------- -------------
8806085    2014
SQL> Select dbms_flashback.get_system_change_number scn from dual;
SCN
----------
8806085
SQL> begin
   for i in 1 .. 10000
   loop
   insert into S1 values ( 1, 'SHAAN' );
   rollback;
   end loop;
   end;
   /
PL/SQL procedure successfully completed.
SQL> select scn, scn-8806085 from (
 select dbms_flashback.get_system_change_number scn from dual
  );
SCN SCN-8806085
---------- -------------
155317184  20180
Even more than if you do not rollback but commit instead
SQL> Create table S2 ( eno number(4));
Table created.
SQL> select dbms_flashback.get_system_change_number scn from dual;
SCN
----------
8828432
SQL> begin
   for i in 1 .. 1000
   loop
     insert into s2 values ( i );
     commit;
   end loop;
  end;
  /
PL/SQL procedure successfully completed.
SQL> select scn, scn-8828432 from (
  select dbms_flashback.get_system_change_number scn from dual
  );
SCN SCN-8828432
---------- -------------
8830391    1959
SQL> select dbms_flashback.get_system_change_number scn from dual;
SCN
----------
8830447
SQL> begin
    for i in 1 .. 1000
    loop
    insert into s2 values ( i );
    commit;
   end loop;
   end;
   /
PL/SQL procedure successfully completed.
SQL> select scn, scn-8830447 from (
  select dbms_flashback.get_system_change_number scn from dual
  );
SCN SCN-8830447
---------- -------------
8842825    12378
Is there any difference between select CURRENT_SCN from v$database and select dbms_flashback.get_system_change_number scn from dual?
For a "normal" database (not standby) they are for all intents and purposes the same. They could be a LITTLE different if you do something like:
SQL>select current_scn, dbms_flashback.get_system_change_number from v$database; since they would be evaluated at two slightly different points in time, but consider them "the same"
What is the difference or similarity between SCN and ORA_ROWSCN? Where does oracle store SCN?
The SCN is like a clock - it is always advancing (use the command “dbms_flashback.get_system_change_number” and wait for few seconds, print it again, it will  have advanced). So, just think of the SCN like a ticker, like time - every time a transaction ends - another unit of time is added, like adding seconds to time where as ora_rowscn is an observed point in time. The ora_rowscn is a value associated to a block or a row on a block that represents the “time” the block/row was last modified.
When alter system checkpoint” command is used?
When we have few dirty buffers of one table in the buffer cache and we issue the command
The checkpoint SCN of the data block is updated and ITL is also updated as:
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0005.020.00002b46  0x00c00235.0d0f.15  --U-    3  fsc 0x0000.00ddffee
0x02   0x0001.012.00002088  0x00c0021d.0b70.07  --U-    1  fsc 0x0000.00df1407
But the header block (after we dump and see) of the file still contains the same SCN as before irrespective of the change in the data block
Where the SCN number resides? Does archive and redo logs also contain SCN numbers?
SCN doe not really reside anywhere, it is like time itself. A value of the SCN, taken at various times, representing the time something happened is stored in many places, sort of like a timestamp would be. Datafiles have SCNs associated with them (times of various operations) control files have them (times of various operations) log files have them (to record times of various operations) undo segments have them (......) they are littered all over the place, they are like timestamps.
Overview DATA DICTIONARY: CHECKPOINT
V$INSTANCE_RECOVERY, V$LOG, V$LOG_HISTORY
V$INSTANCE_RECOVERY: lowest value in last four columns controls checkpoints
redo log file size, log_checkpoint_timeout, log_checkpoint_interval, fast_start_io_target
init parameter: log_checkpoint_interval, log_checkpoint_timeout, log_checkpoints_to_alert
log_checkpoint_interval
– redo log blocks (OS blocks not DB blocks) written before a checkpoint
– If set greater than redo log file size, checkpoints occur at log switches
– Ignored if set to zero.
log_checkpoint_timeout
– number of seconds since last checkpoint before another is performed
– ignored if set to zero
– default = 1800 seconds (30 minutes)
– log_checkpoints_to_alert if true, write checkpoints to alert log
To decrease checkpoints:
– set log_checkpoint_interval larger than the size of the online redo logs
– eliminate time-based checkpoints by setting log_checkpoint_timeout = 0
– increase size of online redo logs
Note: checkpoints DO NOT cause log switches, but log switches cause checkpoints. For Manual check point use “alter system checkpoint”.

37 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Thankful for sharing; Oracle DBA Hadoop Training in Chennai database application gives phenomenal scattered figuring contraptions to your client affiliation issues. It's another headway in IT business winds for the business affiliation.

    ReplyDelete
  3. There are lots of information about latest technology and how to get trained in them, like Hadoop Training Chennai have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get trained in future technologies(Hadoop Training in Chennai). By the way you are running a great blog. Thanks for sharing this.

    ReplyDelete
  4. Well Said. The content provided is true up to my knowledge. This made me to understand the concepts very clear. Thanks for sharing this wonderful information in here. Keep blogging article like this. I have bookmarked this page for future reference as well.


    Hadoop Training Chennai | Big Data Training | JAVA Course in Chennai

    ReplyDelete
  5. Great content. I really enjoyed while reading this content with useful information, keep sharing.
    Hadoop Training in Chennai | Hadoop Training Chennai | FITA Velachery | FITA Academy Chennai.

    ReplyDelete
  6. Thanks for sharing such a great information..Its really nice and informative.
    hadoop training in Chennai

    ReplyDelete
  7. Valuable content.! I get admired by seeing your post. Thanks for sharing this with us.
    Loadrunner Training in Chennai

    ReplyDelete
  8. Thanks for checking the concept of checkpoint of oracle. thanks for sharing.
    dot-net-training in chennai

    ReplyDelete
  9. The ideas which had given for content marketing was very helpful to promote our website content.Thanks for sharing this information with us.In-case if any body wants to undergone salesforce training course,can Enroll from our best salesforce training institute.Here we offered world class training with placement support.For more course details,just visit here Salesforce training in Chennai

    ReplyDelete
  10. Thanks for giving clear detail about system change number (SCN) and checkpoint. the differences are clearly explained. comments for the roolback are informative.
    Informatica Training in Chennai

    ReplyDelete
  11. Thanks a lot for sharing on SCN and Checkpoint in Oracle.Its a valuable content.I have admired a lot by this blog.It made me to understand about checkpoint in oracle.
    Android Training in Chennai

    ReplyDelete
  12. wow,nice infromation.I like ur way of explanation,it can be easily understand about oracle.

    CCNA Training in Chennai

    ReplyDelete
  13. having the coded explanation of scn and checkpoints. all that are coded with many details. keep blogging like this useful information.VMWare Training in Chennai

    ReplyDelete
  14. the concept of SCN and checkpoint oracle is really very nice and informative i got this concept , thanks for sharing.
    sharepoint training in chennai

    ReplyDelete
  15. It is explained in clear way.It is very easy to understand the information.
    Sharepoint admin training in chennai

    ReplyDelete
  16. Your explaining is nice for SCN and checkpoints.Its very easy to understand..Thanks for sharing..
    linux training in chennai

    ReplyDelete
  17. we really feel very happy about the blog you have shared. the explanation is very clear and valuable information. it improves my development skill in SCN and checkpoints. please share the blog like this...


    Veritas cluster training in Chennai

    ReplyDelete
  18. Thanks for sharing this information. The coding explained in a very clear manner.it helps to solve the problems. please update this type of informationssrs training in chennai

    ReplyDelete
  19. Very informative blog that give clear idea about the SCN and checkpoint which starts with the definitions. Good work. Thanks for sharing. We are offering the best training for data warehousing tools with certified experts. join our Datawarehousing Training in Chennai

    ReplyDelete
  20. gathered some knowledge of discussion on scn and checkpoints in oracle. but not having clear ideas about this coding discussion.CCNA Training in Chennai

    ReplyDelete
  21. i gathered some information about the oracle concept it is useful and unique also thanks for discussing this information.



    websphere training in chennai

    ReplyDelete
  22. You have discussed clear about SCN and checkpoints in oracle.Its clearly explained and understandable.
    weblogic training in chennai

    ReplyDelete
  23. This blog is informative. SCN and checkpoint concepts are explained very clearly and easily understandable ios training in chennai

    ReplyDelete
  24. Very informative. Thanks for sharing.

    ReplyDelete
  25. Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing
    php training in chennai
    java training in chennai
    dot net training in chennai

    ReplyDelete
  26. Thanks for giving clear detail about system change number (SCN) and checkpoint. the differences are clearly explained. comments for the roolback are informative.

    J2EE Training in chennai

    ReplyDelete
  27. Wonderful blog.. Thanks for sharing informative blog.. its very useful to me..

    iOS Training in Chennai

    ReplyDelete



  28. Thanks for posting useful information.You have provided an nice article, Thank you very much for this one. And i hope this will be useful for many people.. and i am waiting for your next post keep on updating these kinds of knowledgeable things...Really it was an awesome article...very interesting to read..
    please sharing like this information......
    Android training in chennai
    Ios training in chennai

    ReplyDelete
  29. It's interesting that many of the bloggers your tips helped to clarify a few things for me as well as giving.. very specific nice content. And tell people specific ways to live their lives.Sometimes you just have to yell at people and give them a good shake to get your point across.
    Web Design Company
    Web Development Company
    Mobile App Development Company

    ReplyDelete
  30. It's interesting that many of the bloggers your tips helped to clarify a few things for me as well as giving.. very specific nice content. And tell people specific ways to live their lives.Sometimes you just have to yell at people and give them a good shake to get your point across.
    Web Design Company
    Web Development Company
    Mobile App Development Company

    ReplyDelete
  31. Really it was an awesome article...very interesting to read..You have provided an nice article....Thanks for sharing..
    Web Design Company
    Web Development Company

    ReplyDelete
  32. Being new to the blogging world I feel like there is still so much to learn. Your tips helped to clarify a few things for me as well as giving..
    Android App Development Company

    ReplyDelete