-
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!
====================================================
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|