Home » Article » Oracle Database : Menghitung size dan jumlah file archive log dalam sehari

Oracle Database : Menghitung size dan jumlah file archive log dalam sehari

Pages

October 2011
M T W T F S S
« Sep   Nov »
 12
3456789
10111213141516
17181920212223
24252627282930
31  

RSS Link

  • An error has occurred; the feed is probably down. Try again later.

Twitter Updates

Blog Stats

  • 345,611 hits

Archives

Pengunjung


Berikut script kecil dan sederhana untuk menghitung jumlah dan size archive log (offline redo log) dalam sehari untuk database server Oracle.

SQL> SELECT A.*,
Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb
FROM
(
SELECT
To_Char(First_Time,'YYYY-MM-DD') DAY,
Count(1) Count#,
Min(RECID) Min#,
Max(RECID) Max#
FROM
v$log_history
GROUP
BY To_Char(First_Time,'YYYY-MM-DD')
ORDER
BY 1 DESC
) A,
(
SELECT
Avg(BYTES) AVG#,
Count(1) Count#,
Max(BYTES) Max_Bytes,
Min(BYTES) Min_Bytes
FROM
v$log
) B;

Catatan : Script ini hanya diperuntukkan untuk single instance. Perlu dilakukan modifikasi apabila ingin dijalankan di multiple instance seperti RAC.

Berikut contoh hasil outputnya :

DAY            COUNT#       MIN#       MAX# DAILY_AVG_MB
———- ———- ———- ———- ————
2011-10-14        784     467077     467860        39200
2011-10-13       1627     465450     467076        81350
2011-10-12       1569     463881     465449        78450
2011-10-11       1587     462294     463880        79350
2011-10-10        985     461309     462293        49250
2011-10-09        778     460531     461308        38900
2011-10-08       1790     458741     460530        89500
2011-10-07       2679     456062     458740       133950
2011-10-06       1592     454470     456061        79600
2011-10-05       1562     452908     454469        78100
2011-10-04       2255     450653     452907       112750

DAY            COUNT#       MIN#       MAX# DAILY_AVG_MB
———- ———- ———- ———- ————
2011-10-03       3269     447384     450652       163450
2011-10-02        499     446885     447383        24950
2011-10-01       1457     445428     446884        72850
2011-09-30       1554     443874     445427        77700
2011-09-29       1857     442017     443873        92850
2011-09-28       1556     440461     442016        77800
2011-09-27       1582     438879     440460        79100
2011-09-26        904     437975     438878        45200
2011-09-25        633     437342     437974        31650
2011-09-24       1496     435846     437341        74800
2011-09-23       1415     434431     435845        70750

DAY            COUNT#       MIN#       MAX# DAILY_AVG_MB
———- ———- ———- ———- ————
2011-09-22       1556     432875     434430        77800
2011-09-21       1518     431357     432874        75900
2011-09-20       1487     429870     431356        74350
2011-09-19       1048     428822     429869        52400
2011-09-18        604     428218     428821        30200
2011-09-17       1508     426710     428217        75400
2011-09-16       1510     425200     426709        75500
2011-09-15       1556     423644     425199        77800
2011-09-14       1654     421990     423643        82700
2011-09-13        187     421803     421989         9350

Selamat mencoba !!


1 Comment

  1. Raffaell says:

    Cantik, copy & paste dalam cheatsheet
    Thanks for share

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: