oracle數(shù)據(jù)庫健康檢查方案

1、目錄 TOC o 1-5 h z HYPERLINK l bookmark0 o Current Document 目錄1操作系統(tǒng)檢查2查看本地磁盤和掛載的華為存儲磁盤使用率2查看內(nèi)存使用率2查看CPU使用率2查看10情況2查看當(dāng)前系統(tǒng)時間、運(yùn)行天數(shù)3查看系統(tǒng)日志、遠(yuǎn)程登錄日志3查看服務(wù)器負(fù)載情況3查看數(shù)據(jù)庫日志3查看數(shù)據(jù)庫監(jiān)聽日志大小，超過2G就要處理3 HYPERLINK l bookmark4 o Current Document Oracle數(shù)據(jù)庫健康檢查3檢查oracle數(shù)據(jù)庫的運(yùn)行情況3檢查oracle數(shù)據(jù)庫的實(shí)例運(yùn)行情況4檢查當(dāng)前數(shù)據(jù)庫的實(shí)例狀態(tài)、歸檔模式、實(shí)例已運(yùn)行時間、實(shí)例名

2、稱、數(shù)據(jù)庫名稱和歸檔進(jìn)程狀態(tài)等運(yùn)行時的重要信息。 4查看oracle數(shù)據(jù)庫端設(shè)置的字符集4查看oracle客戶端設(shè)置的字符集4檢查各個表空間的數(shù)據(jù)文件總大小G 5檢查表空間總個數(shù)5檢查各個表空間的數(shù)據(jù)文件總個數(shù)5檢查數(shù)據(jù)庫表空間的使用情況5檢查數(shù)據(jù)庫表空間的剩余空間大小G 6檢查數(shù)據(jù)庫表空間的運(yùn)行狀態(tài)等信息6檢查數(shù)據(jù)庫表空間的數(shù)據(jù)文件狀態(tài)等信息6檢查數(shù)據(jù)庫表空間異常狀態(tài)的數(shù)據(jù)文件7檢查數(shù)據(jù)庫控制文件的信息7 table_name, num_rows, CHAIN CNT, ROUND(CHAIN_CNT/num_rows)*100, 2) as RT%from dba_tableswhere

3、num_rows0 and CHAIN_CNT0order by CHAIN_CNT/num_rows desc) where rownum?/rdbms/admin/awrrpt. sql 生成AWR報告（單實(shí)例時候用這個腳本）SQL?/rdbms/admin/awrrpti. sql生成AWR報告（RAC環(huán)境時候用這個腳本）注意：輸入報告名后回車出現(xiàn)0RA-00904: invalid identified value 解決辦法：用sys用戶來執(zhí)行或者對用戶進(jìn)行授權(quán)grant all on dbms_workload_repository to username; TOC o 1-5 h

4、z 檢查數(shù)據(jù)庫redo文件的信息7檢查數(shù)據(jù)庫歸檔模式相關(guān)信息8檢查數(shù)據(jù)庫的JOB運(yùn)行情況8檢查數(shù)據(jù)庫的對象信息8檢查數(shù)據(jù)庫索引not analyzed9檢查數(shù)據(jù)庫的會話信息9檢查數(shù)據(jù)庫中行遷移的表9檢查數(shù)據(jù)庫中表的碎片大小10檢查數(shù)據(jù)庫回收站的信息10用awr工具來檢查數(shù)據(jù)庫的性能情況10LINUX操作系統(tǒng)檢查.查看本地磁盤和掛載的華為存儲磁盤使用率執(zhí)行命令：df-h.查看內(nèi)存使用率執(zhí)行命令：free -m.查看CPU使用率執(zhí)行命令：top.查看10情況執(zhí)行命令:iostat 2 10.查看當(dāng)前系統(tǒng)時間、運(yùn)行天數(shù)執(zhí)行命令：date, uptime.查看系統(tǒng)日志、遠(yuǎn)程登錄日志執(zhí)行命令：cat /

5、var/log/messages cat /var/log/secure.查看服務(wù)器負(fù)載情況執(zhí)行命令：sar 2 10或者vmstat 2 10.查看數(shù)據(jù)庫日志切換到 oracle 用戶， 進(jìn)入日志目錄 cd /uOl/app/oracle/admin/MICSPROD/bdump/ 查看日志有沒有報錯 命令tail -300 alert_micspord.log|grep ORAtail -300 alert_micspord.log|grep Err.查看數(shù)據(jù)庫監(jiān)聽日志大小，超過2G就要處理切換到oracle用戶， 進(jìn)入日志目錄cd $ORACLE_HOME/network/log查看日志

6、大小命令I(lǐng)s -1thOracle數(shù)據(jù)庫健康檢查.檢查oracle數(shù)據(jù)庫的運(yùn)行情況select dbid,name,created,logmode, openmode, flashbackon, dbunique_name, platformnamefrom v$database;select * from gv$database;.檢查oracle數(shù)據(jù)庫的實(shí)例運(yùn)行情況select instance_number, instance_name, host_name, version, startup_time, status, parallel, archiverfrom v$instance

7、;select * from gv$instance;.檢查當(dāng)前數(shù)據(jù)庫的實(shí)例狀態(tài)、歸檔模式、實(shí)例已運(yùn)行時間、實(shí)例名稱、數(shù)據(jù) 庫名稱和歸檔進(jìn)程狀態(tài)等運(yùn)行時的重要信息。select tl.status,t2. log mode,tl. startup_time,trunc(sysdate - tl. startup_time, 0) | |，天tl. instance_name,t2. name,tl. archiverfrom (select status, startup_time, instancename, archiver from v$instance) tl,(select name

8、, log_mode from v$database) t2;.查看oracle數(shù)據(jù)庫端設(shè)置的字符集select * from nlsdatabaseparameters;14.查看oracle客戶端設(shè): Hl）的字符集select userenv(，language，) from dual;.檢查各個表空間的數(shù)據(jù)文件總大小Gselect sum(bytes)/I024/1024/1024 G from dba_data_files union allselect sum (bytes)/1024/1024/1024 G from dba_temp_files;.檢查表空間總個數(shù)select

9、J tablespace num is | |count (*) from dba_tablespaces;select ? datafile num is union allselect J tempfile num isselect ? datafile num is union allselect J tempfile num is.檢查各個表空間的數(shù)據(jù)文件總個數(shù)count (*) from dba_data_filescount (*) from dba_temp_files;.檢查數(shù)據(jù)庫表空間的使用情況selecta. tablespace name,round(total/1024

10、/1024/1024, 5) total_size_g,round(free/1024/1024/1024, 5) free_size_g,round(total-free)/1024/1024/1024, 5) used_size_g,(round(total-free)/total, 5)*1001|) percentage from(select tablespace name, sum(bytes) total from dba_data_files group by tab1e space_name) a,(select tablespace_name, sum(bytes) fre

11、e from dba_free_space group by tablespace_name) bwhere a. tablespace name=b. tablespace name;select a. tablespace_name,round (total / 1024 / 1024, 3) total_size_m,round (free / 1024 / 1024, 3) free size m,round(total - free) / 1024 / 1024, 3) used_size_m,(round(total - free) / total, 3) * 100 j | %)

12、 percentage from (select tablespace_name, sum (bytes) totalfrom dba data filesgroup by tabiespace_name) a, (select tablespacename, sum(bytes) free from dba_free_spacegroup by tablespacename) b where a. tablespace_name = b. tablespace_name;.檢查數(shù)據(jù)庫表空間的剩余空間大小Gselect tablespace name, round(sum(bytes / 10

13、24 / 1024 / 1024), 5) free_size_gfrom dba_free_space group by tablespace_name;.檢查數(shù)據(jù)庫表空間的運(yùn)行狀態(tài)等信息select tablespace_name, block_size, status, contents, logging, extent_managementext_mag,SEGMENT_SPACE_MANAGEMENT seg_mag, ALLOCATION_TYPEalloc_typefrom DBA_TABLESPACES;.檢查數(shù)據(jù)庫表空間的數(shù)據(jù)文件狀態(tài)等信息select a. tablespa

14、ce_name,a. bytes,FILE_NAME,status,name,b. enabled,creation_time,a. autoextensible autofrom dba_data_files a, v$datafile bwhere a. file_id = b.FILE#union allselect a. tablespace_name,a. bytes,a. FILE_NAME,status,name, b. enabled, b. creation_time, a. autoextensible autofrom dba_temp_files a, v$tempfi

15、le b where a. file_id = b.FILE#;.檢查數(shù)據(jù)庫表空間異常狀態(tài)的數(shù)據(jù)文件select tl.file name文件名稱， tl. status文件狀態(tài)， t2. status表空間狀態(tài)， t2. tabiespace_name 表空間名稱 from(select file name, status, tablespace_name from dba_data_files) tl,(select tab 1 espace name, status from dba_tablespaces where status not in (ONLINE) t2 wheretl.

16、 tablespace_name = t2. tablespace_name;.檢查數(shù)據(jù)庫控制文件的信息select * from v$controlfile; select * from V$CONTROLFILE_RECORD_SECTION;.檢查數(shù)據(jù)庫redo文件的信息select * from v$log; select * from v$logfile;select a. group#, thread#, member, bytes/1024/1024MB,members, archived, a. status from v$log a, v$logfile b where a.

17、 group#=b. group# order by a. thread#;.檢查數(shù)據(jù)庫歸檔模式相關(guān)信息selecttrunc(completion_time, hh) archived_timed,sum(trunc(t. BLOCKS * t. BLOCK_SIZE) / 1024 / 1024) archivedsize,count (*) archived numfromv$archived_log tgroup by trunc (t. COMPLETIONS I ME, hh) order by archivedtimed desc;select thread#, to_char(

18、first_time, 5 yyyy-mm-dd:hh24:mi:ss?) from v$log_historywhere first_time sysdate - 5order by first_time desc;.檢查數(shù)據(jù)庫的JOB運(yùn)行情況select job, last_date, last_sec, next_date, next_sec, broken, failures from dba_jobs;select job_name,job_type,job_action,to_char(start_date, 5 yyyy-mm-dd hh24:mi:ss5),repeat_int

19、erval,enabled,statefrom dba_scheduler_jobs;select log_id, log_date, status, additional_info fromdba_scheduler_job_run_details;.檢查數(shù)據(jù)庫的對象信息一檢查數(shù)據(jù)庫的每個用戶擁有的對象個數(shù)select owner, count(*) from dba objects group by owner;一檢查數(shù)據(jù)庫的無效對象select owner, object_name, OBJECT_TYPE, status from dba_objects where status=INVALID;一檢查數(shù)據(jù)庫是否有禁用的約束 select owner,constraint_name object_name, constraint_type object_type, status, last_changefrom dba_constraintswhere status not in (ENABLED);一檢查數(shù)據(jù)庫是否有禁用的觸發(fā)器select own