DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: how to find out checkpoint occurrence?

  1. #1
    Join Date
    Nov 1999
    Location
    Kuwait
    Posts
    122

    Unhappy how to find out checkpoint occurrence?

    Is there a way to find out the checkpoint occurrence other than the LOG_CHECKPOINTS_TO_ALERT = TRUE?

    I have set LOG_CHECKPOINTS_TO_ALERT = TRUE and have set LOG_CHECKPOINT_INTERVAL to a nonzero value or set LOG_CHECKPOINT_TIMEOUT to a nonzero value. Now i want to find out that the checkpoint has occurred at the correct time or after the correct interval, is there any sql script or something which could tell me this.... other then goin to my alert file and checkin it out?

    Hope I’m making sense

    NK
    ====================================================
    Stand up for your principles even if you stand alone!
    ====================================================

  2. #2
    Join Date
    Oct 2002
    Posts
    807
    I don't see why you'd be inclined to get checkpoint information..or what you'd even do with it! Esp. since Oracle does NOT flush buffers to disk immediately upon issuing a checkpoint anymore (it does it in what is known as a 'lazy' fashion now). It used to be the case..but not anymore.

    In anycase, I don't know of a way to get the information without looking at the alertlog. But yes, I can tell you a way to get the information from the database by accessing the alertlog in the background. Use the DBMS_LOB package. Modify this code appropriately. http://www.dbasupport.com/oracle/ora9i/alert_log2.shtml

    It'll do the job.

  3. #3
    Join Date
    Nov 1999
    Location
    Kuwait
    Posts
    122

    For Information Purpose!

    We have set LOG_CHECKPOINTS_TO_ALERT = TRUE and have set LOG_CHECKPOINT_INTERVAL to a nonzero value OR set LOG_CHECKPOINT_TIMEOUT to a nonzero value. Now we want to find out that the checkpoint has occurred at the correct time or after the correct interval, is there any sql script or something which could tell me this.... other then goin back to alert file and checking it out?

    In other words, Is there a way to find out the checkpoint occurrence other than the LOG_CHECKPOINTS_TO_ALERT = TRUE?

    Answer:

    Setting LOG_CHECKPOINTS_TO_ALERT = TRUE logs all the checkpoints to the Alert file, which could be viewed when ever needed. And in case if the LOG_CHECKPOINTS_TO_ALERT is set to FALSE, checkpoints progress and occurrence could be viewed from V$THREAD, V$DATAFILE,X$KCCRT and X$KCCCP.

    A) SELECT DISTINCT
    CHECKPOINT_CHANGE#,
    CHECKPOINT_TIME
    FROM V$DATAFILE

    B)SELECT
    CHECKPOINT_CHANGE#,
    CHECKPOINT_TIME
    FROM V$THREAD

    C)SELECT
    RTCKP_SCN,
    RTCKP_TIM
    FROM X$KCCRT

    -The results should be the same from all above queries to ensure the consistency of the database.
    -This will provide us the last CHECKPOINT OCCURENCE on the database.

    To know the total number of CHECKPOINT(S) occurred on the database since the instance startup use the following query

    SELECT NAME, VALUE FROM V$SYSSTAT
    WHERE NAME LIKE '%checkpoint%'

    This will provide the total number of checkpoints occured since the last startup

    To monitor the incremental checkpoint use the following query:

    SELECT
    CPODS,
    CPODT,
    CPSDR_SEQ
    FROM X$KCCCP

    If we want to inquire about the CHECKPOINT HISTORY we can do that by ALTER SESSION SET EVENTS 'immediate trace name controlf level 10'

    This will dump the contents of the control file to udump for us to have a look.
    NK
    ====================================================
    Stand up for your principles even if you stand alone!
    ====================================================

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width