監(jiān)控:oracle數(shù)據(jù)庫(kù)監(jiān)控方案

    來(lái)源: 雪竹頻道2023-08-27 02:48:36
      


    (資料圖)

    一、概述

    監(jiān)控Oracle數(shù)據(jù)庫(kù)的數(shù)據(jù)增長(zhǎng)是數(shù)據(jù)庫(kù)管理員(DBA)和數(shù)據(jù)管理團(tuán)隊(duì)必須進(jìn)行的重要任務(wù)之一。通過(guò)監(jiān)控?cái)?shù)據(jù)增長(zhǎng),可以確保數(shù)據(jù)庫(kù)的運(yùn)行順暢、性能高效,并能夠及時(shí)采取必要的措施來(lái)處理數(shù)據(jù)增長(zhǎng)引起的問(wèn)題。

    監(jiān)控?cái)?shù)據(jù)文件的增長(zhǎng)是非常重要的,因?yàn)閿?shù)據(jù)文件是存儲(chǔ)數(shù)據(jù)庫(kù)中所有數(shù)據(jù)的主要組成部分。DBA可以通過(guò)跟蹤數(shù)據(jù)文件的大小和增長(zhǎng)速度來(lái)了解數(shù)據(jù)增長(zhǎng)的趨勢(shì),并根據(jù)需要調(diào)整數(shù)據(jù)庫(kù)的存儲(chǔ)配置。

    二、解決方案

    2.1 監(jiān)控?cái)?shù)據(jù)庫(kù)的增長(zhǎng)

    SET LINESIZE 200SET PAGESIZE 200COL "Database Size" FORMAT a13COL "Used Space" FORMAT a11COL "Used in %" FORMAT a11COL "Free in %" FORMAT a11COL "Database Name" FORMAT a13COL "Free Space" FORMAT a12COL "Growth DAY" FORMAT a11COL "Growth WEEK" FORMAT a12COL "Growth DAY in %" FORMAT a16COL "Growth WEEK in %" FORMAT a16SELECT(select min(creation_time) from v$datafile) "Create Time",(select name from v$database) "Database Name",ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || " MB" "Database Size",ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) || " MB" "Used Space",ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || "% MB" "Used in %",ROUND((FREE.P / 1024 / 1024 ),2) || " MB" "Free Space",ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || "% MB" "Free in %",ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || " MB" "Growth DAY",ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || "% MB" "Growth DAY in %",ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || " MB" "Growth WEEK",ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || "% MB" "Growth WEEK in %"FROM    (SELECT BYTES FROM V$DATAFILEUNION ALLSELECT BYTES FROM V$TEMPFILEUNION ALLSELECT BYTES FROM V$LOG) USED,(SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREEGROUP BY FREE.P;

    2.2 監(jiān)控?cái)?shù)據(jù)庫(kù)每月的增長(zhǎng)情況

    select to_char(creation_time, "MM-RRRR") "Month", sum(bytes)/1024/1024/1024 "Growth in GB"from sys.v_$datafilewhere to_char(creation_time,"RRRR")="2014"group by to_char(creation_time, "MM-RRRR")order by  to_char(creation_time, "MM-RRRR");

    關(guān)鍵詞:

    責(zé)任編輯:sdnew003

    相關(guān)新聞

    版權(quán)與免責(zé)聲明:

    1 本網(wǎng)注明“來(lái)源:×××”(非商業(yè)周刊網(wǎng))的作品,均轉(zhuǎn)載自其它媒體,轉(zhuǎn)載目的在于傳遞更多信息,并不代表本網(wǎng)贊同其觀點(diǎn)和對(duì)其真實(shí)性負(fù)責(zé),本網(wǎng)不承擔(dān)此類稿件侵權(quán)行為的連帶責(zé)任。

    2 在本網(wǎng)的新聞頁(yè)面或BBS上進(jìn)行跟帖或發(fā)表言論者,文責(zé)自負(fù)。

    3 相關(guān)信息并未經(jīng)過(guò)本網(wǎng)站證實(shí),不對(duì)您構(gòu)成任何投資建議,據(jù)此操作,風(fēng)險(xiǎn)自擔(dān)。

    4 如涉及作品內(nèi)容、版權(quán)等其它問(wèn)題,請(qǐng)?jiān)?0日內(nèi)同本網(wǎng)聯(lián)系。