A script to check RAC log switch
The following script reports how much time passed between log switches in a time period.
SELECT C.INSTANCE,
C.THREAD#,
B.SEQUENCE# "START SEQUENCE",
TO_CHAR (B.FIRST_TIME, 'DD-MM-YYYY HH24:MI:SS') "START TIME",
A.SEQUENCE# "END SEQUENCE",
TO_CHAR (A.FIRST_TIME, 'DD-MM-YYYY HH24:MI:SS') "END TIME",
TO_CHAR (
TRUNC (SYSDATE)
+ NUMTODSINTERVAL ( (A.FIRST_TIME - B.FIRST_TIME) * 86400,
'SECOND'),
'HH24:MI:SS')
DURATION
FROM V$LOG_HISTORY A, V$LOG_HISTORY B, V$THREAD C
WHERE A.SEQUENCE# = B.SEQUENCE# + 1
AND A.THREAD# = C.THREAD#
AND B.THREAD# = C.THREAD#
AND A.FIRST_TIME BETWEEN TO_DATE ('28-12-2012 00:00:00',
'DD-MM-YYYY HH24:MI:SS')
AND TO_DATE ('29-12-2012 00:00:00',
'DD-MM-YYYY HH24:MI:SS')
ORDER BY 4;
The out put will be like this,
INSTANCE THREAD# START SEQUENCE START TIME END SEQUENCE END TIME DURATION
-------------- ---------- -------------- ------------------- ------------ ------------------- --------------
oracle2 2 35813 28-12-2012 15:40:53 35814 28-12-2012 15:48:29 00:07:36
oracle2 2 35814 28-12-2012 15:48:29 35815 28-12-2012 15:56:03 00:07:34
oracle1 1 41283 28-12-2012 15:48:29 41284 28-12-2012 15:56:02 00:07:33
oracle1 1 41284 28-12-2012 15:56:02 41285 28-12-2012 16:03:37 00:07:35
oracle2 2 35815 28-12-2012 15:56:03 35816 28-12-2012 16:03:38 00:07:35
oracle1 1 41285 28-12-2012 16:03:37 41286 28-12-2012 16:11:10 00:07:33
oracle2 2 35816 28-12-2012 16:03:38 35817 28-12-2012 16:11:11 00:07:33
oracle1 1 41286 28-12-2012 16:11:10 41287 28-12-2012 16:18:38 00:07:28
oracle2 2 35817 28-12-2012 16:11:11 35818 28-12-2012 16:18:39 00:07:28
oracle1 1 41287 28-12-2012 16:18:38 41288 28-12-2012 16:26:16 00:07:38
oracle2 2 35818 28-12-2012 16:18:39 35819 28-12-2012 16:26:16 00:07:37
No comments:
Post a Comment