oracle資料庫巡檢內容
引自：謝謝：http://space.itpub.net/?uid-24558279-action-viewspace-itemid-718722
巡檢內容
資料庫基本狀況
在本節中主要對資料庫的基本狀況進行檢查，其中包含：檢查Oracle例項狀態，檢查Oracle服務程式，檢查Oracle監聽程式，共三個部分。
例項狀態
SQL> select instance_name,host_name,startup_time,status,database_status from v$instance;
INSTANCE_NAME HOST_NAME STARTUP_TIME STATUS DATABASE_STATUS
---------------- ------------------- -------------------- ---------- ------------ ----
CKDB AS14 2009-5-7 9:3 OPEN ACTIVE
其中“STATUS”表示Oracle當前的例項狀態，必須為“OPEN”；“DATABASE_STATUS”表示Oracle當前資料庫的狀態，必須為“ACTIVE”。
SQL> select name,log_mode,open_mode from v$database;
NAME LOG_MODE OPEN_MODE
--------- ------------ -----------------
CKDB ARCHIVELOG READ WRITE
其中“LOG_MODE”表示Oracle當前的歸檔方式。“ARCHIVELOG”表示資料庫執行在歸檔模式下，“NOARCHIVELOG”表示資料庫執行在非歸檔模式下。在我們的系統中資料庫必須執行在歸檔方式下。
服務程式
$ps -ef|grep ora_|grep -v grep&&ps -ef|grep ora_|grep -v grep|wc –l
oracle 2960 1 0 May07 ? 00:01:02 ora_pmon_CKDB
oracle 2962 1 0 May07 ? 00:00:22 ora_psp0_CKDB
oracle 2964 1 0 May07 ? 00:00:00 ora_mman_CKDB
oracle 2966 1 0 May07 ? 00:03:20 ora_dbw0_CKDB
oracle 2968 1 0 May07 ? 00:04:29 ora_lgwr_CKDB
oracle 2970 1 0 May07 ? 00:10:31 ora_ckpt_CKDB
oracle 2972 1 0 May07 ? 00:03:45 ora_smon_CKDB
oracle 2974 1 0 May07 ? 00:00:00 ora_reco_CKDB
oracle 2976 1 0 May07 ? 00:01:24 ora_cjq0_CKDB
oracle 2978 1 0 May07 ? 00:06:17 ora_mmon_CKDB
oracle 2980 1 0 May07 ? 00:07:26 ora_mmnl_CKDB
oracle 2982 1 0 May07 ? 00:00:00 ora_d000_CKDB
oracle 2984 1 0 May07 ? 00:00:00 ora_s000_CKDB
oracle 2994 1 0 May07 ? 00:00:28 ora_arc0_CKDB
oracle 2996 1 0 May07 ? 00:00:29 ora_arc1_CKDB
oracle 3000 1 0 May07 ? 00:00:00 ora_qmnc_CKDB
oracle 3625 1 0 May07 ? 00:01:40 ora_q000_CKDB
oracle 31594 1 0 Jul20 ? 00:00:00 ora_q003_CKDB
oracle 23802 1 0 05:09 ? 00:00:33 ora_j000_CKDB
19
在檢查Oracle的程式命令輸出後，輸出顯示至少應包括以下一些程式：
. Oracle寫資料檔案的程式，輸出顯示為：“ora_dbw0_CKDB”
. Oracle寫日誌檔案的程式，輸出顯示為：“ora_lgwr_ CKDB”
. Oracle監聽例項狀態的程式，輸出顯示為：“ora_smon_ CKDB”
. Oracle監聽客戶端連線程式狀態的程式，輸出顯示為：“ora_pmon_ CKDB”
. Oracle進行歸檔的程式，輸出顯示為：“ora_arc0_ CKDB”
. Oracle進行檢查點的程式，輸出顯示為：“ora_ckpt_ CKDB”
. Oracle進行恢復的程式，輸出顯示為：“ora_reco_ CKDB”
監聽狀態
/home/oracle>lsnrctl status
LSNRCTL for Linux: Version 10.2.0.2.0 - Production on 23-JUL-2009 14:11:53
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.2.0 - Production
Start Date 07-MAY-2009 09:35:52
Uptime 77 days 4 hr. 36 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /data/oracle/product/10.2.0/network/admin/listener.ora
Listener Log File /data/oracle/product/10.2.0/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=AS14)(PORT=1521)))
Services Summary...
Service "CKDB" has 1 instance(s).
Instance "CKDB", status READY, has 1 handler(s) for this service...
Service "CKDBXDB" has 1 instance(s).
Instance "CKDB", status READY, has 1 handler(s) for this service...
Service "CKDB_XPT" has 1 instance(s).
Instance "CKDB", status READY, has 1 handler(s) for this service...
The command completed successfully
“Services Summary”項表示Oracle的監聽程式正在監聽哪些資料庫例項，輸出顯示中至少應該有“CKDB”這一項。
檢查監聽程式是否存在：
[oracle@AS14 ~]$ ps -ef|grep lsn|grep -v grep
oracle 2954 1 0 May07 ? 00:01:17 /data/oracle/product/10.2.0/bin/tnslsnr LISTENER –inherit
日誌檔案
在本節主要檢查相關的日誌檔案，包含：檢查作業系統的日誌檔案，檢查Oracle日誌檔案，檢查Oracle核心轉儲目錄，檢查Root使用者和Oracle使用者的email，總共四個部分。
# cat /var/log/messages |grep failed
檢視是否有與Oracle使用者相關的出錯資訊。
日誌檔案
[oracle@AS14 ~]$ cat /data/oracle/admin/CKDB/bdump/alert_CKDB.log |grep ora-
[oracle@AS14 ~]$ cat /data/oracle/admin/CKDB/bdump/alert_CKDB.log |grep err
[oracle@AS14 ~]$ cat /data/oracle/admin/CKDB/bdump/alert_CKDB.log |grep fail
Oracle在執行過程中，會在警告日誌檔案(alert_SID.log)中記錄資料庫的一些執行情況：資料庫的啟動、關閉，啟動時的非預設引數；資料庫的重做日誌切換情況，記錄每次切換的時間，及如果因為檢查點(checkpoint)操作沒有執行完成造成不能切換，會記錄不能切換的原因；對資料庫進行的某些操作，如建立或刪除表空間、增加資料檔案；資料庫發生的錯誤，如表空間不夠、出現壞塊、資料庫內部錯誤(ORA－600)等。定期檢查日誌檔案，根據日誌中發現的問題及時進行處理：
問題 | 處理 |
啟動引數不對 | 檢查初始化引數檔案 |
因為檢查點操作或歸檔操作沒有完成造成重做日誌不能切換 | 如果經常發生這樣的情況，可以考慮增加重做日誌檔案組；想辦法提高檢查點或歸檔操作的效率； |
有人未經授權刪除了表空間 | 檢查資料庫的安全問題，是否密碼太簡單；如有必要，撤消某些使用者的系統許可權 |
出現壞塊 | 檢查是否是硬體問題(如磁碟本生有壞塊)，如果不是，檢查是那個資料庫物件出現了壞塊，對這個物件進行重建 |
表空間不夠 | 增加資料檔案到相應的表空間 |
出現ORA-600 | 根據日誌檔案的內容檢視相應的TRC檔案，如果是Oracle的bug，要及時打上相應的補丁 |
Listener日誌：$ORACLE_HOME/network/log
核心轉儲目錄
$ls $ORACLE_BASE/admin/CKDB/cdump/*.trc|wc -l
$ls $ORACLE_BASE/admin/CKDB/udump/*.trc|wc –l
如果上面命令的結果每天都在增長，則說明Oracle程式經常發生核心轉儲。這說明某些使用者程式或者資料庫後臺程式由於無法處理的原因而異常退出。頻繁的核心轉儲特別是資料庫後臺程式的核心轉儲會導致資料庫異常終止。
使用者和Oracle使用者的email
#tail –n 200 /var/mail/root
#tail –n 200 /var/mail/oracle
檢視有無與Oracle使用者相關的出錯資訊。
物件狀態
在本節主要檢查相關Oracle物件的狀態，包含：檢查Oracle控制檔案狀態，檢查Oracle線上日誌狀態，檢查Oracle表空間的狀態，檢查Oracle所有資料檔案狀態，檢查Oracle所有表、索引、儲存過程、觸發器、包等物件的狀態，檢查Oracle所有回滾段的狀態，總共六個部分。
控制檔案狀態
SQL> select status,name from v$controlfile;
STATUS NAME
------- --------------------------------------------------------------------------------
/data/oradata/CKDB/control01.ctl
/data/oradata/CKDB/control02.ctl
/data/oradata/CKDB/control03.ctl
輸出結果應該有3條以上（包含3條）的記錄，“STATUS”應該為空。狀態為空表示控制檔案狀態正常。
線上日誌狀態
SQL> select group#,status,type,member from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- -----------
3 ONLINE /data/oradata/CKDB/redo03.log
2 ONLINE /data/oradata/CKDB/redo02.log
1 ONLINE /data/oradata/CKDB/redo01.log
4 ONLINE /data/oradata/CKDB/redo04.log
5 ONLINE /data/oradata/CKDB/redo05.log
6 ONLINE /data/oradata/CKDB/redo06.log
6 rows selected
輸出結果應該有3條以上（包含3條）記錄，“STATUS”應該為非“INVALID”，非“DELETED”。注：“STATUS”顯示為空表示正常。
表空間的狀態
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
SJ1 ONLINE
ADM_INDEX ONLINE
HOME_DATA ONLINE
HOME_INDEX ONLINE
PHOTO_DATA ONLINE
PHOTO_INDEX ONLINE
。。。。。。。
輸出結果中STATUS應該都為ONLINE。
所有資料檔案狀態
SQL> select name,status from v$datafile;
NAME STATUS
-------------------------------------------------- -------
/data/oradata/CKDB/system01.dbf SYSTEM
/data/oradata/CKDB/undotbs01.dbf ONLINE
/data/oradata/CKDB/sysaux01.dbf ONLINE
/data/oradata/CKDB/users01.dbf ONLINE
/data/oradata/CKDB/sj.dbf ONLINE
/data/oradata/CKDB/HOME_DATA1.dbf ONLINE
/data/oradata/CKDB/HOME_INDEX1.dbf ONLINE
/data/oradata/CKDB/PHOTO_DATA1.dbf ONLINE
/data/oradata/CKDB/PHOTO_INDEX1.dbf ONLINE
/data/oradata/CKDB/BLOG_DATA1.dbf ONLINE
/data/oradata/CKDB/BLOG_INDEX1.dbf ONLINE
/data/oradata/CKDB/AUDIO_DATA1.dbf ONLINE
/data/oradata/CKDB/AUDIO_INDEX1.dbf ONLINE
/data/oradata/CKDB/VIDEO_DATA1.dbf ONLINE
/data/oradata/CKDB/VIDEO_INDEX1.dbf ONLINE
/data/oradata/CKDB/SYS_DATA1.dbf ONLINE
/data/oradata/CKDB/SYS_INDEX1.dbf ONLINE
/data/oradata/CKDB/ADM_DATA1.dbf ONLINE
/data/oradata/CKDB/ADM_INDEX1.dbf ONLINE
/data/oradata/CKDB/perfstat.dbf ONLINE
輸出結果中“STATUS”應該都為“ONLINE”。或者：
SQL> select file_name,status from dba_data_files;
FILE_NAME STATUS
--------------------------------------------- ---------
/data/oradata/CKDB/users01.dbf AVAILABLE
/data/oradata/CKDB/sysaux01.dbf AVAILABLE
/data/oradata/CKDB/undotbs01.dbf AVAILABLE
/data/oradata/CKDB/system01.dbf AVAILABLE
/data/oradata/CKDB/sj.dbf AVAILABLE
/data/oradata/CKDB/perfstat.dbf AVAILABLE
/data/oradata/CKDB/HOME_DATA1.dbf AVAILABLE
/data/oradata/CKDB/HOME_INDEX1.dbf AVAILABLE
/data/oradata/CKDB/PHOTO_DATA1.dbf AVAILABLE
輸出結果中“STATUS”應該都為“AVAILABLE”。
sql>select owner,object_name,object_type from dba_objects where status!='VALID' and owner!='SYS' and owner!='SYSTEM';
no rows selected
如果有記錄返回，則說明存在無效物件。若這些物件與應用相關，那麼需要重新編譯生成這個物件，或者：
SELECT owner, object_name, object_type FROM dba_objects WHERE status= 'INVALID';
SQL> select segment_name,status from dba_rollback_segs;
SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU1$ ONLINE
_SYSSMU2$ ONLINE
_SYSSMU3$ ONLINE
_SYSSMU4$ ONLINE
_SYSSMU5$ ONLINE
_SYSSMU6$ ONLINE
_SYSSMU7$ ONLINE
_SYSSMU8$ ONLINE
_SYSSMU9$ ONLINE
_SYSSMU10$ ONLINE
11 rows selected
輸出結果中所有回滾段的“STATUS”應該為“ONLINE”。
相關資源的使用情況
在本節主要檢查Oracle相關資源的使用情況，包含：檢查Oracle初始化檔案中相關的引數值，檢查資料庫連線情況，檢查系統磁碟空間，檢查Oracle各個表空間使用情況，檢查一些擴充套件異常的物件，檢查system表空間內的內容，檢查物件的下一擴充套件與表空間的最大擴充套件值，總共七個部分。
初始化檔案中相關引數值
SQL> select resource_name,max_utilization,initial_allocation,
limit_value from v$resource_limit;
RESOURCE_NAME MAX_UTILIZATION INITIAL_ALLOCAT LIMIT_VALUE
-------------------- --------------- --------------- ---------------
processes 162 500 500
sessions 168 555 555
enqueue_locks 136 6930 6930
enqueue_resources 111 2660 UNLIMITED
ges_procs 0 0 0
ges_ress 0 0 UNLIMITED
ges_locks 0 0 UNLIMITED
ges_cache_ress 0 0 UNLIMITED
ges_reg_msgs 0 0 UNLIMITED
ges_big_msgs 0 0 UNLIMITED
ges_rsv_msgs 0 0 0
gcs_resources 0 0 0
gcs_shadows 0 0 0
dml_locks 76 2440 UNLIMITED
temporary_table_locks 26 UNLIMITED UNLIMITED
transactions 13 610 UNLIMITED
branches 0 610 UNLIMITED
cmtcallbk 3 610 UNLIMITED
sort_segment_locks 5 UNLIMITED UNLIMITED
max_rollback_segments 11 610 65535
RESOURCE_NAME MAX_UTILIZATION INITIAL_ALLOCAT LIMIT_VALUE
-------------------- --------------- --------------- ---------------
max_shared_servers 1 UNLIMITED UNLIMITED
parallel_max_servers 16 80 3600
22 rows selected
若LIMIT_VALU-MAX_UTILIZATION<=5，則表明與RESOURCE_NAME相關的Oracle初始化引數需要調整。可以透過修改Oracle初始化引數檔案$ORACLE_BASE/admin/CKDB/pfile/initORCL.ora來修改。
檢視當前會話連線數，是否屬於正常範圍。
SQL> select count(*) from v$session;
COUNT(*)
----------
29
select sid,serial#,username,program,machine,status from v$session;
SID SERIAL# USERNAME PROGRAM MACHINE STATUS
---- ---------- ------------ ---------------------------- ------------ --------
1 3 oracle@xz15saledb (PMON) xz15saledb ACTIVE
2 3 oracle@xz15saledb (DBW0) xz15saledb ACTIVE
3 3 oracle@xz15saledb (DBW1) xz15saledb ACTIVE
4 3 oracle@xz15saledb (LGWR) xz15saledb ACTIVE
5 3 oracle@xz15saledb (CKPT) xz15saledb ACTIVE
6 3 oracle@xz15saledb (SMON) xz15saledb ACTIVE
7 3 oracle@xz15saledb (RECO) xz15saledb ACTIVE
8 1 oracle@xz15saledb (CJQ0) xz15saledb ACTIVE
9 3 oracle@xz15saledb (ARC0) xz15saledb ACTIVE
10 3 oracle@xz15saledb (ARC1) xz15saledb ACTIVE
11 11319 ZK AccPrtInv_svr@xz15tuxedo2 (TNS V1-V3) xz15tuxedo2 INACTIVE
13 48876 ZG upload@xz15saleap (TNS V1-V3) xz15saleap INACTIVE
17 20405 ZK AccCreateRpt@xz15tuxedo1 (TNS V1-V3) xz15tuxedo1 INACTIVE
20 12895 ZK OweScanSvr@xz15billdb (TNS V1-V3) xz15billdb INACTIVE
其中：SID 會話(session)的ID號；
SERIAL# 會話的序列號，和SID一起用來唯一標識一個會話；
USERNAME 建立該會話的使用者名稱；
PROGRAM 這個會話是用什麼工具連線到資料庫的；
STATUS 當前這個會話的狀態，ACTIVE表示會話正在執行某些任務，INACTIVE表示當前會話沒有執行任何操作；
如果建立了過多的連線，會消耗資料庫的資源，同時，對一些“掛死”的連線可能需要手工進行清理。如果DBA要手工斷開某個會話，則執行：（一般不建議使用這種方式去殺掉資料庫的連線，這樣有時候session不會斷開。容易引起死連線。建議透過sid查到作業系統的spid,使用ps –ef|grep spidno的方式確認spid不是ORACLE的後臺程式。使用作業系統的kill -9命令殺掉連線）
alter system kill session 'SID,SERIAL#';
注意：上例中SID為1到10(USERNAME列為空)的會話，是Oracle的後臺程式，不要對這些會話進行任何操作。
磁碟空間
如果檔案系統的剩餘空間過小或增長較快，需對其進行確認並刪除不用的檔案以釋放空間。
[oracle@AS14 ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda5 9.7G 3.9G 5.4G 42% /
/dev/sda1 479M 16M 438M 4% /boot
/dev/sda2 49G 19G 28G 41% /data
none 1014M 0 1014M 0% /dev/shm
SQL> select f.tablespace_name,a.total,f.free,round((f.free/a.total)*100) "% Free"
from
(select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by "% Free";
TABLESPACE_NAME TOTAL FREE % Free
------------------------------ ---------- ---------- ----------
OPERATION_DATA 1800 547 30
WAPWEB_DATA 100 36 36
OPERATION_INDEX 500 186 37
SYSTEM 1024 515 50
SYSAUX 1024 534 52
SALE8_TEMP 100 62 62
SJ1 500 348 70
PERFSTAT 500 356 71
……….
HOME_DATA 100 77 77
SYS_INDEX 100 100 100
VIDEO_INDEX 100 100 100
VIDEO_DATA 100 100 100
BLOG_DATA 100 100 100
39 rows selected
如果空閒率%Free小於10%以上（包含10%），則注意要增加資料檔案來擴充套件表空間而不要是用資料檔案的自動擴充套件功能。請不要對錶空間增加過多的資料檔案，增加資料檔案的原則是每個資料檔案大小為2G或者4G，自動擴充套件的最大限制在8G。
sql>select Segment_Name, Segment_Type, TableSpace_Name,
(Extents/Max_extents)*100 Percent
From sys.DBA_Segments
Where Max_Extents != 0 and (Extents/Max_extents)*100>=95
order By Percent;
no rows selected
如果有記錄返回，則這些物件的擴充套件已經快達到它定義時的最大擴充套件值。對於這些物件要修改它的儲存結構引數。
表空間內的內容
select distinct(owner) from dba_tables
where tablespace_name='SYSTEM' and
owner!='SYS' and owner!='SYSTEM'
union
select distinct(owner) from dba_indexes
where tablespace_name='SYSTEM' and
owner!='SYS' and owner!='SYSTEM';
no rows selected
如果記錄返回，則表明system表空間記憶體在一些非system和sys使用者的物件。應該進一步檢查這些物件是否與我們應用相關。如果相關請把這些物件移到非System表空間，同時應該檢查這些物件屬主的預設表空間值。
sql>select a.table_name, a.next_extent, a.tablespace_name
from all_tables a,
(select tablespace_name, max(bytes) as big_chunk
from dba_free_space
group by tablespace_name ) f
where f.tablespace_name = a.tablespace_name
and a.next_extent > f.big_chunk
union
select a.index_name, a.next_extent, a.tablespace_name
from all_indexes a,
(select tablespace_name, max(bytes) as big_chunk
from dba_free_space
group by tablespace_name ) f
where f.tablespace_name = a.tablespace_name
and a.next_extent > f.big_chunk;
no rows selected
如果有記錄返回，則表明這些物件的下一個擴充套件大於該物件所屬表空間的最大擴充套件值，需調整相應表空間的儲存引數。
資料庫備份結果
在本節主要檢查Oracle資料庫備份結果，包含：檢查資料庫備份日誌資訊，檢查backup卷中檔案產生的時間，檢查oracle使用者的email，總共三個部分。
假設：備份的臨時目錄為/backup/hotbakup，我們需要檢查2009年7月22日的備份結果，則用下面的命令來檢查：
#cat /backup/hotbackup/hotbackup-09-7-22.log|grep –i error
備份指令碼的日誌檔案為hotbackup-月份-日期-年份.log，在備份的臨時目錄下面。如果檔案中存在“ERROR:”，則表明備份沒有成功，存在問題需要檢查。
卷中檔案產生的時間
#ls –lt /backup/hotbackup
backup卷是備份的臨時目錄，檢視輸出結果中檔案的日期，都應當是在當天凌晨由熱備份指令碼產生的。如果時間不對則表明熱備份指令碼沒執行成功。
使用者的email
#tail –n 300 /var/mail/oracle
熱備份指令碼是透過Oracle使用者的cron去執行的。cron執行完後作業系統就會發一條Email通知Oracle使用者任務已經完成。檢視Oracle email中今天凌晨部分有無ORA-，Error，Failed等出錯資訊，如果有則表明備份不正常。
資料庫效能
在本節主要檢查Oracle資料庫效能情況，包含：檢查資料庫的等待事件，檢查死鎖及處理，檢查cpu、I/O、記憶體效能，檢視是否有僵死程式，檢查行連結/遷移，定期做統計分析，檢查緩衝區命中率，檢查共享池命中率，檢查排序區，檢查日誌緩衝區，總共十個部分。
set pages 80
set lines 120
col event for a40
select sid,event,p1,p2,p3,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where event not like 'SQL%' and event not like 'rdbms%';
如果資料庫長時間持續出現大量像latch free，enqueue，buffer busy waits，db file sequential read，db file scattered read等等待事件時，需要對其進行分析，可能存在問題的語句。
最高的SQL語句的獲取
SQL>SELECT SQL_TEXT FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS)
WHERE ROWNUM<=5 desc;
SELECT * FROM (SELECT PARSING_USER_ID
EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,
SQL_TEXT FROM V$SQLAREA ORDER BY DISK_READS DESC)
WHERE ROWNUM<10 ;
個系統等待事件的獲取
SELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE 'SQL%' ORDER BY TOTAL_WAITS DESC) WHERE ROWNUM<=5;
COLUMN USERNAME FORMAT A12
COLUMN OPNAME FORMAT A16
COLUMN PROGRESS FORMAT A8
SELECT USERNAME,SID,OPNAME,ROUND(SOFAR*100 / TOTALWORK,0) || '%' AS PROGRESS,TIME_REMAINING,SQL_TEXT FROM V$SESSION_LONGOPS , V$SQL WHERE TIME_REMAINING <> 0 AND SQL_ADDRESS=ADDRESS AND SQL_HASH_VALUE = HASH_VALUE;
最高的程式
SET LINE 240
SET VERIFY OFF
COLUMN SID FORMAT 999
COLUMN PID FORMAT 999
COLUMN S_# FORMAT 999
COLUMN USERNAME FORMAT A9 HEADING "ORA USER"
COLUMN PROGRAM FORMAT A29
COLUMN SQL FORMAT A60
COLUMN OSNAME FORMAT A9 HEADING "OS USER"
SELECT P.PID PID,S.SID SID,P.SPID SPID,S.USERNAME USERNAME,S.OSUSER OSNAME,P.SERIAL# S_#,P.TERMINAL,P.PROGRAM PROGRAM,P.BACKGROUND,S.STATUS,RTRIM(SUBSTR(A.SQL_TEXT, 1, 80)) SQLFROM V$PROCESS P, V$SESSION S,V$SQLAREA A WHERE P.ADDR = S.PADDR AND S.SQL_ADDRESS = A.ADDRESS (+) AND P.SPID LIKE '%&1%';
SQL> SELECT segment_name table_name,COUNT(*) extents FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name HAVING COUNT(*)=(SELECT MAX(COUNT(*)) FROM dba_segments GROUP BY segment_name);
比例
SQL>SELECT DF.TABLESPACE_NAME NAME,DF.FILE_NAME "FILE",F.PHYRDS PYR, F.PHYBLKRD PBR,F.PHYWRTS PYW, F.PHYBLKWRT PBW FROM V$FILESTAT F, DBA_DATA_FILES DF WHERE F.FILE# = DF.FILE_ID ORDER BY DF.TABLESPACE_NAME;
比例
SQL>SELECT SUBSTR(A.FILE#,1,2) "#", SUBSTR(A.NAME,1,30) "NAME", A.STATUS,A.BYTES,B.PHYRDS,B.PHYWRTS FROM V$DATAFILE A, V$FILESTAT B WHERE A.FILE# = B.FILE#;
查詢目前鎖物件資訊：
col sid for 999999
col username for a10
col schemaname for a10
col osuser for a16
col machine for a16
col terminal for a20
col owner for a10
col object_name for a30
col object_type for a10
select sid,serial#,username,SCHEMANAME,osuser,MACHINE,
terminal,PROGRAM,owner,object_name,object_type,o.object_id
from dba_objects o,v$locked_object l,v$session s
where o.object_id=l.object_id and s.sid=l.session_id;
oracle級kill掉該session：
alter system kill session '&sid,&serial#';
作業系統級kill掉session：
#>kill -9 pid
、I/O、記憶體效能
記錄資料庫的cpu使用、IO、記憶體等使用情況，使用vmstat,iostat,sar,top等命令進行資訊收集並檢查這些資訊，判斷資源使用情況。
1. CPU使用情況：
[root@sale8 ~]# top
top - 10:29:35 up 73 days, 19:54, 1 user, load average: 0.37, 0.38, 0.29
Tasks: 353 total, 2 running, 351 sleeping, 0 stopped, 0 zombie
Cpu(s): 1.2% us, 0.1% sy, 0.0% ni,98.8% id, 0.0% wa, 0.0% hi, 0.0% si
Mem: 16404472k total, 12887428k used, 3517044k free, 60796k buffers
Swap: 8385920k total, 665576k used, 7720344k free, 10358384k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
30495 oracle 15 0 8329m 866m 861m R 10 5.4 7:53.90 oracle
32501 oracle 15 0 8328m 1.7g 1.7g S 2 10.6 1:58.38 oracle
32503 oracle 15 0 8329m 1.6g 1.6g S 2 10.2 2:06.62 oracle
。。。。。。。
注意上面的藍色字型部分，此部分內容表示系統剩餘的cpu，當其平均值下降至10%以下的時視為CPU使用率異常，需記錄下該數值，並將狀態記為異常。
2. 記憶體使用情況：
# free -m
total used free shared buffers cached
Mem: 2026 1958 67 0 76 1556
-/+ buffers/cache: 326 1700
Swap: 5992 92 5900
如上所示，藍色部分表示系統總記憶體，紅色部分表示系統使用的記憶體，黃色部分表示系統剩餘記憶體，當剩餘記憶體低於總記憶體的10%時視為異常。
3. 系統I/O情況：
# iostat -k 1 3
Linux 2.6.9-22.ELsmp (AS14) 07/29/2009
avg-cpu: %user %nice %sys%iowait %idle
0.16 0.00 0.05 0.36 99.43
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 3.33 13.16 50.25 94483478 360665804
avg-cpu: %user %nice %sys%iowait %idle
0.00 0.00 0.00 0.00 100.00
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 0.00 0.00 0.00 0 0
如上所示，藍色字型部分表示磁碟讀寫情況，紅色字型部分為cpu IO等待情況。
4. 系統負載情況：
#uptime
12:08:37 up 162 days, 23:33, 15 users, load average: 0.01, 0.15, 0.10
如上所示，藍體字部分表示系統負載，後面的3個數值如果有高於2.5的時候就表明系統在超負荷運轉了，並將此值記錄到巡檢表，視為異常。
select spid from v$process where addr not in (select paddr from v$session);
有些殭屍程式有阻塞其他業務的正常執行，定期殺掉殭屍程式。
遷移
Sql>select table_name,num_rows,chain_cnt From dba_tables Where wner='CTAIS2' And chain_cnt<>0;
注：含有long raw列的表有行連結是正常的,找到遷移行儲存到chained_rows表中,如沒有該表執行../rdbms/admin/utlchain.sql
Sql>analyze table tablename list chained rows;
可透過表chained_rows中table_name,head_rowid看出哪些行是遷移行
如:Sql>create table aa as select a.* from sb_zsxx a,chained_rows b where a.rowid=b.head_rowid and b.table_name ='SB_ZSXX';
sql>delete from sb_zsxx where rowid in (select head_rowid from chained_rows where table_name = 'SB_ZSXX');
sql>insert into sb_zsxx select * from chained_row where table_name = 'SB_ZSXX';
對於採用Oracle Cost-Based-Optimizer的系統，需要定期對資料物件的統計資訊進行採集更新，使最佳化器可以根據準備的資訊作出正確的explain plan。在以下情況更需要進行統計資訊的更新：
1、應用發生變化
2、大規模資料遷移、歷史資料遷出、其他資料的匯入等
3、資料量發生變化
檢視錶或索引的統計資訊是否需更新，如：
Sql>Select table_name,num_rows,last_analyzed From user_tables where table_name ='DJ_NSRXX'
sql>select count(*) from DJ_NSRXX如num_rows和count(*)
如果行數相差很多,則該表需要更新統計資訊，建議一週做一次統計資訊收集，如：
Sql>exec sys.dbms_stats.gather_schema_stats(ownname=>'CTAIS2',cascade => TRUE,degree => 4);
SQL> SELECT a.VALUE + b.VALUE logical_reads,
c.VALUE phys_reads,
round(100*(1-c.value/(a.value+b.value)),4) hit_ratio
FROM v$sysstat a,v$sysstat b,v$sysstat c
WHERE a.NAME='db block gets'
AND b.NAME='consistent gets'
AND c.NAME='physical reads' ;
LOGICAL_READS PHYS_READS HIT_RATIO
------------- ---------- ----------
1273645705 71191430 94.4104
如果命中率低於90%則需加大資料庫引數db_cache_size。
SQL> select sum(pinhits)/sum(pins)*100 from v$librarycache;
SUM(PINHITS)/SUM(PINS)*100
--------------------------
99.5294474716798
如低於95%，則需要調整應用程式使用繫結變數，或者調整資料庫引數shared pool的大小。
SQL> select name,value from v$sysstat where name like '%sort%';
NAME VALUE
---------------------------------------------------------------- ----------
sorts (memory) 6135534
sorts (disk) 8
sorts (rows) 2264742084
如果disk/(memoty+row)的比例過高，則需要調整sort_area_size(workarea_size_policy=false)或pga_aggregate_target(workarea_size_policy=true)。
SQL> select name,value from v$sysstat where name in ('redo entries','redo buffer allocation retries');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 27663705
redo buffer allocation retries 880
如果redo buffer allocation retries/redo entries超過1%，則需要增大log_buffer。
在本節主要檢查Oracle資料庫的安全性，包含：檢查系統安全資訊，定期修改密碼，總共兩個部分。
系統安全日誌檔案的目錄在/var/log下，主要檢查登入成功或失敗的使用者日誌資訊。
檢查登入成功的日誌：
[root@rac2 ~]# grep -i accepted /var/log/secure
Jan 8 08:44:43 rac2 sshd[29559]: Accepted password for root from ::ffff:10.10.10.6 port 1119 ssh2……
檢查登入失敗的日誌：
[root@rac2 ~]# grep -i inval /var/log/secure &&grep -i failed /var/log/secure
Jan 9 10:30:44 rac2 sshd[3071]: Invalid user ydbuser from ::ffff:192.168.3.5
Jan 9 10:30:56 rac2 sshd[3071]: Failed password for invalid user ydbuser from ::ffff:192.168.3.5 port 36005 ssh2
Jan 9 10:30:56 rac2 sshd[3071]: Failed password for invalid user ydbuser from ::ffff:192.168.3.5 port 36005 ssh2
Jan 10 22:44:38 rac2 sshd[21611]: Failed password for root from ::ffff:10.10.10.6 port 1723 ssh2
在出現的日誌資訊中沒有錯誤(Invalid、refused)提示，如果沒有(Invalid、refused)視為系統正常，出現錯誤提示，應作出系統告警通知。
在資料庫系統上往往存在很多的使用者，如：第三方資料庫監控系統，初始安裝資料庫時的演示使用者，管理員使用者等等，這些使用者的密碼往往是寫定的，被很多人知道，會被別有用心的人利用來攻擊系統甚至進行修改資料。需要修改密碼的使用者包括：
資料庫管理員使用者SYS，SYSTEM；其他使用者。
登陸系統後，提示符下輸入cat /etc/passwd，在列出來的使用者中檢視是否存在已經不再使用的或是陌生的帳號。若存在，則記錄為異常。
修改密碼方法：
Sql>alter user USER_NAME identified by PASSWORD;
在本節主要檢查當前crontab任務是否正常，檢查Oracle Job是否有失敗等共六個部分。
任務是否正常
[oracle@AS14 ~]$ crontab -l
是否有失敗
Sql>select job,what,last_date,next_date,failures,broken from dba_jobs Where schema_user='CAIKE';
如有問題建議重建job，如：
exec sys.dbms_job.remove(1);
commit;
exec sys.dbms_job.isubmit(1,'REFRESH_ALL_SNAPSHOT;',SYSDATE+1/1440,'SYSDATE+4/1440');
commit;
SQL> select
2 A.tablespace_name,(1-(A.total)/B.total)*100 used_percent
3 from (select tablespace_name,sum(bytes) total
4 from dba_free_space group by tablespace_name) A,
5 (select tablespace_name,sum(bytes) total
6 from dba_data_files group by tablespace_name) B
7 where A.tablespace_name=B.tablespace_name;
TABLESPACE_NAME USED_PERCENT
------------------------------ ------------
HOME_INDEX 1.5
BLOG_DATA 0.375
VIDEO_DATA 0.25
VIDEO_INDEX 0.25
SYS_DATA 9.5
SYS_INDEX 0.4375
CURRENCY_INDEX 13
UNDOTBS1 2.3055555555
SYSAUX 47.875976562
根據本週每天的檢查情況找到空間擴充套件很快的資料庫物件,並採取相應的措施：
---刪除歷史資料
移動規定資料庫中至少保留6個月的歷史資料，所以以前的歷史資料可以考慮備份然後進行清除以便釋放其所佔的資源空間。
---擴表空間
alter tablespace
注意：在資料庫結構發生變化時，如增加了表空間，增加了資料檔案或重做日誌檔案這些操作，都會造成Oracle資料庫控制檔案的變化，DBA應及進行控制檔案的備份，備份方法是：
執行SQL語句：
alter database backup controlfile to '/home/backup/control.bak';
或：
alter database backup controlfile to trace;
這樣，會在USER_DUMP_DEST(初始化引數檔案中指定)目錄下生成建立控制檔案的SQL命令。
Sql>select index_name,table_name,tablespace_name,status From dba_indexes Where wner='CTAIS2' And status<>'VALID';
注：分割槽表上的索引status為N/A是正常的，如有失效索引則對該索引做rebuild，如：
Sql>alter index INDEX_NAME rebuild tablespace TABLESPACE_NAME;
SELECT owner, constraint_name, table_name, constraint_type, status
FROM dba_constraints
WHERE status ='DISABLE' and constraint_type='P';
如有失效約束則啟用，如：
Sql>alter Table TABLE_NAME Enable Constraints CONSTRAINT_NAME;
SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = 'DISABLED';
如有失效觸發器則啟用，如：
Sql>alter Trigger TRIGGER_NAME Enable;
使用statspack收集統計資料，比較每天對資料庫效能的監控報告,確定是否有必要對資料庫效能進行調整
如有必要,進行效能調整
根據每週的監控,提出空間管理的改進方法
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23141985/viewspace-719355/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 陌生Oracle的資料庫環境的巡檢內容Oracle資料庫
- Oracle巡檢內容Oracle
- Oracle資料庫巡檢Oracle資料庫
- oracle資料庫巡檢(轉）Oracle資料庫
- oracle資料庫巡檢（二）全面檢查Oracle資料庫
- oracle 一般手動巡檢內容Oracle
- Oracle資料庫(RAC)巡檢報告Oracle資料庫
- Oracle資料庫巡檢參考項Oracle資料庫
- Oracle資料庫(單機)巡檢報告Oracle資料庫
- 資料庫巡檢模版資料庫
- oracle資料庫巡檢優化-快速定位資料庫瓶頸Oracle資料庫優化
- oracle資料庫巡檢（一）基本sql語句Oracle資料庫SQL
- SQL Server資料庫巡檢SQLServer資料庫
- oracle資料庫巡檢優化-快速定位資料庫瓶頸（轉）Oracle資料庫優化
- oracle資料庫巡檢最佳化-快速定位資料庫瓶頸Oracle資料庫
- 使用RDA巡檢MYSQL資料庫MySql資料庫
- 資料庫巡檢參考項資料庫
- MySQL資料庫健康檢查--MySQL巡檢MySql資料庫
- 資料庫各個檢視的內容資料庫
- Oracle資料庫的監控內容Oracle資料庫
- MSSQL資料庫健康檢查--SQL Server巡檢SQL資料庫Server
- 資料庫巡檢常用的SQL語句資料庫SQL
- oracle資料庫巡檢最佳化-使用sql語句快速定位資料庫瓶頸Oracle資料庫SQL
- 檢視SQL Server資料庫修改了哪些內容SQLServer資料庫
- Oracle 資料庫巡檢指令碼 單例項 RAC 輸出HTML格式Oracle資料庫指令碼單例HTML
- oracle巡檢（轉）Oracle
- oracle的巡檢Oracle
- oracle資料庫備份和恢復的內容Oracle資料庫
- 小麥苗資料庫巡檢指令碼V7.0，支援Oracle、MySQL、SQL Server和PG資料庫資料庫指令碼OracleMySqlServer
- Oracle 巡檢手冊Oracle
- oracle巡檢工具-RDAOracle
- 資料庫冷備份內容資料庫
- 資料庫鍵方面的內容資料庫
- 資料庫運維工作內容資料庫運維
- 資料庫相關的系統巡檢參考項資料庫
- 資料庫（Oracle）運維工作內容及常用指令碼命令資料庫Oracle運維指令碼
- oracle DBA 巡檢專案Oracle
- oracle健康巡檢筆記Oracle筆記