Today came a requirement to see how frequently log switch is taking place and how much of archive logs are being generated and at what rate…
I certainly went through the alert log first and found it too be very time exauhsting…so then looked for the better option..
Then came accross the view V$LOG_HISTORY…and it did the job for me…
Used the following query to fetch the number of archive logs created hourly..
[code]
SELECT * FROM (
SELECT * FROM (
SELECT TO_CHAR(FIRST_TIME, ‘DD/MON’) AS "DAY",
TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’00’, 1, 0)), ’99’) "00:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’01’, 1, 0)), ’99’) "01:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’02’, 1, 0)), ’99’) "02:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’03’, 1, 0)), ’99’) "03:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’04’, 1, 0)), ’99’) "04:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’05’, 1, 0)), ’99’) "05:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’06’, 1, 0)), ’99’) "06:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’07’, 1, 0)), ’99’) "07:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’08’, 1, 0)), ’99’) "08:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’09’, 1, 0)), ’99’) "09:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’10’, 1, 0)), ’99’) "10:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’11’, 1, 0)), ’99’) "11:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’12’, 1, 0)), ’99’) "12:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’13’, 1, 0)), ’99’) "13:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’14’, 1, 0)), ’99’) "14:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’15’, 1, 0)), ’99’) "15:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’16’, 1, 0)), ’99’) "16:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’17’, 1, 0)), ’99’) "17:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’18’, 1, 0)), ’99’) "18:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’19’, 1, 0)), ’99’) "19:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’20’, 1, 0)), ’99’) "20:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’21’, 1, 0)), ’99’) "21:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’22’, 1, 0)), ’99’) "22:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’23’, 1, 0)), ’99’) "23:00 hr"
FROM V$LOG_HISTORY
WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
GROUP BY TO_CHAR(FIRST_TIME, ‘DD/MON’)
) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, ‘YYYY DD/MON’) DESC
) WHERE ROWNUM < 8
11:08:45 SYSTEM>
11:08:45 SYSTEM>/
DAY 00:00 hr 01:00 hr 02:00 hr 03:00 hr 04:00 hr 05:00 hr 06:00 hr 07:00 hr 08:00 hr 09:00 hr 10:00 hr 11:00 hr 12:00 hr 13:00 hr 14:00 hr 15:00 hr 16:00 hr 17:00 hr 18:00 hr 19:00 hr 20:00 hr 21:00 hr 22:00 hr 23:00 hr
———- ———– ———– ———– ———– ———– ———– ———– ———– ———– ———– ———– ———– ———– ———– ———– ———– ———– ———– ———– ———– ———– ———– ———– ———–
16/SEP 19 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
15/SEP 26 20 24 34 18 5 15 10 19 9 14 5 7 4 6 7 14 4 7 8 6 18 16 20
14/SEP 26 25 22 37 16 5 17 10 41 15 13 6 22 11 9 7 14 4 8 7 6 18 15 20
13/SEP 27 22 21 37 19 8 18 12 21 13 16 7 10 11 9 8 30 6 8 7 6 10 9 20
12/SEP 20 19 16 35 16 4 13 7 20 3 10 2 3 2 1 2 10 2 4 10 11 6 7 18
11/SEP 27 19 17 32 14 3 11 5 16 2 12 2 4 2 4 3 10 3 6 6 7 3 9 15
10/SEP 22 16 24 37 18 5 19 11 23 11 21 22 8 7 7 7 12 3 8 7 4 7 6 16
7 rows selected.
[/code]
Hope it helps…
There is another one for the same cause but rather than hourly it will show the result day wise..
[code]
11:08:49 SYSTEM>
11:11:44 SYSTEM>col c1 format a10 heading "Month"
11:11:44 SYSTEM>col c2 format a25 heading "Archive Date"
11:11:44 SYSTEM>col c3 format 999 heading "Switches"
11:11:44 SYSTEM>
11:11:44 SYSTEM>compute AVG of C on A
11:11:44 SYSTEM>compute AVG of C on REPORT
11:11:44 SYSTEM>
11:11:44 SYSTEM>break on A skip 1 on REPORT skip 1
11:11:44 SYSTEM>
11:11:44 SYSTEM>select
11:11:44 to_char(trunc(first_time), ‘Month’) c1,
11:11:44 to_char(trunc(first_time), ‘Day : DD-Mon-YYYY’) c2,
11:11:44 count(*) c3
11:11:44 from
11:11:44 v$log_history
11:11:44 where
11:11:44 trunc(first_time) > last_day(sysdate-100) +1
11:11:44 group by
11:11:44 trunc(first_time);
Month Archive Date Switches
———- ————————- ——–
July Friday : 02-Jul-2010 234
July Saturday : 03-Jul-2010 214
July Sunday : 04-Jul-2010 184
July Monday : 05-Jul-2010 234
July Tuesday : 06-Jul-2010 303
July Wednesday : 07-Jul-2010 312
July Thursday : 08-Jul-2010 341
July Friday : 09-Jul-2010 284
July Saturday : 10-Jul-2010 210
July Sunday : 11-Jul-2010 192
July Monday : 12-Jul-2010 313
July Tuesday : 13-Jul-2010 327
July Wednesday : 14-Jul-2010 330
July Thursday : 15-Jul-2010 280
July Friday : 16-Jul-2010 239
July Saturday : 17-Jul-2010 228
July Sunday : 18-Jul-2010 197
July Monday : 19-Jul-2010 253
July Tuesday : 20-Jul-2010 283
July Wednesday : 21-Jul-2010 285
July Thursday : 22-Jul-2010 269
July Friday : 23-Jul-2010 270
July Saturday : 24-Jul-2010 232
July Sunday : 25-Jul-2010 198
July Monday : 26-Jul-2010 276
July Tuesday : 27-Jul-2010 325
July Wednesday : 28-Jul-2010 290
July Thursday : 29-Jul-2010 290
July Friday : 30-Jul-2010 268
July Saturday : 31-Jul-2010 311
August Sunday : 01-Aug-2010 213
August Monday : 02-Aug-2010 278
August Tuesday : 03-Aug-2010 327
August Wednesday : 04-Aug-2010 323
August Thursday : 05-Aug-2010 315
August Friday : 06-Aug-2010 311
August Saturday : 07-Aug-2010 244
August Sunday : 08-Aug-2010 218
August Monday : 09-Aug-2010 383
August Tuesday : 10-Aug-2010 368
August Wednesday : 11-Aug-2010 355
August Thursday : 12-Aug-2010 318
August Friday : 13-Aug-2010 311
August Saturday : 14-Aug-2010 360
August Sunday : 15-Aug-2010 214
August Monday : 16-Aug-2010 305
August Tuesday : 17-Aug-2010 323
August Wednesday : 18-Aug-2010 301
August Thursday : 19-Aug-2010 322
August Friday : 20-Aug-2010 299
August Saturday : 21-Aug-2010 262
August Sunday : 22-Aug-2010 219
August Monday : 23-Aug-2010 304
August Tuesday : 24-Aug-2010 335
August Wednesday : 25-Aug-2010 331
August Thursday : 26-Aug-2010 316
August Friday : 27-Aug-2010 252
August Saturday : 28-Aug-2010 248
August Sunday : 29-Aug-2010 235
August Monday : 30-Aug-2010 310
August Tuesday : 31-Aug-2010 341
September Wednesday : 01-Sep-2010 331
September Thursday : 02-Sep-2010 338
Month Archive Date Switches
———- ————————- ——–
September Friday : 03-Sep-2010 313
September Saturday : 04-Sep-2010 221
September Sunday : 05-Sep-2010 221
September Monday : 06-Sep-2010 288
September Tuesday : 07-Sep-2010 322
September Wednesday : 08-Sep-2010 385
September Thursday : 09-Sep-2010 364
September Friday : 10-Sep-2010 321
September Saturday : 11-Sep-2010 232
September Sunday : 12-Sep-2010 241
September Monday : 13-Sep-2010 355
September Tuesday : 14-Sep-2010 374
September Wednesday : 15-Sep-2010 316
September Thursday : 16-Sep-2010 20
77 rows selected.
11:11:47 SYSTEM>
[/code]