Oracle DBA 日常管理
目的：這篇文檔有很詳細(xì)的資料記錄著對(duì)一個(gè)甚至更多的 ORACLE 數(shù)據(jù)庫(kù)每天的，每月的，
每年的運(yùn)行的狀態(tài)的結(jié)果及檢查的結(jié)果，在文檔的附錄中你將會(huì)看到所有檢查，修改的 SQL
和 PL/SQL 代碼。
目錄
1. 日常維護(hù)程序
A ． 檢查已起的所有實(shí)例
B ． 查找一些新的警告日志
C ． 檢查 DBSNMP 是否在運(yùn)行
D ． 檢查數(shù)據(jù)庫(kù)備份是否正確
E ． 檢查備份到磁帶中的文件是否正確
F ． 檢查數(shù)據(jù)庫(kù)的性能是否正常合理，是否有足夠的空間和資源
G ． 將文檔日志復(fù)制到備份的數(shù)據(jù)庫(kù)中
H ． 要?？?DBA 用戶手冊(cè)
2. 晚間維護(hù)程序
A ．收集 VOLUMETRIC 的數(shù)據(jù)
3. 每周維護(hù)工作
A ． 查找那些破壞規(guī)則的 OBJECT
B ． 查找是否有違反安全策略的問(wèn)題
C ． 查看錯(cuò)誤地方的 SQL*NET 日志
D ． 將所有的警告日志存檔
E ． 經(jīng)常訪問(wèn)供應(yīng)商的主頁(yè)
4. 月維護(hù)程序
A ． 查看對(duì)數(shù)據(jù)庫(kù)會(huì)產(chǎn)生危害的增長(zhǎng)速度
B ． 回顧以前數(shù)據(jù)庫(kù)優(yōu)化性能的調(diào)整
C ． 查看 I/O 的屏頸問(wèn)題
D ． 回顧 FRAGMENTATION
E ． 將來(lái)的執(zhí)行計(jì)劃
F ． 查看調(diào)整點(diǎn)和維護(hù)
5. 附錄
A ． 月維護(hù)過(guò)程
B ． 晚間維護(hù)過(guò)程
C ． 周維護(hù)過(guò)程
6. 參考文獻(xiàn)
----------------------------------------------------------------
一．日維護(hù)過(guò)程
A ．查看所有的實(shí)例是否已起
確定數(shù)據(jù)庫(kù)是可用的，把每個(gè)實(shí)例寫入日志并且運(yùn)行日?qǐng)?bào)告或是運(yùn)行測(cè)試
文件。當(dāng)然有一些操作我們是希望它能自動(dòng)運(yùn)行的。
可選擇執(zhí)行：用 ORACLE 管理器中的‘ PROBE‘ 事件來(lái)查看
B ．查找新的警告日志文件
1. 聯(lián)接每一個(gè)操作管理系統(tǒng)
2. 使用‘ TELNET‘ 或是可比較程序
3. 對(duì)每一個(gè)管理實(shí)例，經(jīng)常的執(zhí)行 $ORACLE_BASE/<SID>/bdump 操
作，并使其能回退到控制數(shù)據(jù)庫(kù)的 SID 。
4. 在提示下，使用 UNIX 中的‘ TAIL ‘命令查看 alert_<SID>.log ，或是
用其他方式檢查文件中最近時(shí)期的警告日志
5. 如果以前出現(xiàn)過(guò)的一些 ORA_ERRORS 又出現(xiàn)，將它記錄到數(shù)據(jù)庫(kù)
恢復(fù)日志中并且仔細(xì)的研究它們，這個(gè)數(shù)據(jù)庫(kù)恢復(fù)日志在〈 FILE 〉中
C ．查看 DBSNMP 的運(yùn)行情況
檢查每個(gè)被管理機(jī)器的‘ DBSNMP‘ 進(jìn)程并將它們記錄到日志中。
在 UNIX 中，在命令行中，鍵入 ps –ef | grep dbsnmp, 將回看到 2 個(gè)
DBSNMP 進(jìn)程在運(yùn)行。如果沒(méi)有，重啟 DBSNMP 。
D ．查數(shù)據(jù)庫(kù)備份是否成功
E ．檢查備份的磁帶文檔是否成功
F ．檢查對(duì)合理的性能來(lái)說(shuō)是否有足夠的資源
1. 檢查在表空間中有沒(méi)有剩余空間。
對(duì)每一個(gè)實(shí)例來(lái)說(shuō)，檢查在表空間中是否存在有剩余空間來(lái)滿足當(dāng)天
的預(yù)期的需要。當(dāng)數(shù)據(jù)庫(kù)中已有的數(shù)據(jù)是穩(wěn)定的，數(shù)據(jù)日增長(zhǎng)的平均
數(shù)也是可以計(jì)算出來(lái)，最小的剩余空間至少要能滿足每天數(shù)據(jù)的增 長(zhǎng)。
A ） 運(yùn)行‘ FREE.SQL‘ 來(lái)檢查表空間的剩余空間。
B ） 運(yùn)行‘ SPACE.SQL‘ 來(lái)檢查表空間中的剩余空間百分率
2. 檢查回滾段
回滾段的狀態(tài)一般是在線的，除了一些為復(fù)雜工作準(zhǔn)備的專用 段，它一般狀態(tài)是離線的。
a) 每個(gè)數(shù)據(jù)庫(kù)都有一個(gè)回滾段名字的列表。
b) 你可以用 V$ROLLSTAT 來(lái)查詢?cè)诰€或是離線的回滾段的現(xiàn)在狀 態(tài) .
c) 對(duì)于所有回滾段的存儲(chǔ)參數(shù)及名字， 可用
DBA_ROLLBACK_SEGS 來(lái)查詢。但是它不如 V$ROLLSTAT 準(zhǔn)確。
3. 識(shí)別出一些過(guò)分的增長(zhǎng)
查看數(shù)據(jù)庫(kù)中超出資源或是增長(zhǎng)速度過(guò)大的段，這些段的存儲(chǔ)參 數(shù)需要調(diào)整。
a ） 收集日數(shù)據(jù)大小的信息， 可以用
‘ ANALYZE5PCT.SQL ‘。如果你收集的是每晚的信息， 則可跳過(guò)這一步。
b ） 檢查當(dāng)前的范圍，可用‘ NR.EXTENTS.SQL‘ 。
c ） 查詢當(dāng)前表的大小信息。
d ） 查詢當(dāng)前索引大小的信息。
e ） 查詢?cè)鲩L(zhǎng)趨勢(shì)。
4. 確定空間的范圍。
如果范圍空間對(duì)象的 NEXT_EXTENT 比表空間所能提供的最大范
圍還要大，那么這將影響數(shù)據(jù)庫(kù)的運(yùn)行。如果我們找到了這個(gè)目標(biāo)，可
以用‘ ALTER TABLESPACE COALESCE‘ 調(diào)查它的位置，或加另外 的數(shù)據(jù)文件。
A ）運(yùn)行‘ SPACEBOUND.SQL‘ 。如果都是正常的，將不返回任何行。
5. 回顧 CPU ，內(nèi)存，網(wǎng)絡(luò)，硬件資源論點(diǎn)的過(guò)程
A ）檢查 CPU 的利用情況，進(jìn)到 x:\web\phase2\default.htm =>system
metrics=>CPU 利用頁(yè)， CPU 的最大限度為 400 ，當(dāng) CPU 的占用保持
在 350 以上有一段時(shí)間的話，我們就需要查看及研究出現(xiàn)的問(wèn)題。
G ．將存檔日志復(fù)制到備用數(shù)據(jù)庫(kù)中
如果有一個(gè)備用數(shù)據(jù)庫(kù)，將適當(dāng)?shù)拇鏅n日志復(fù)制到備用數(shù)據(jù)庫(kù)的期望
位置，備用數(shù)據(jù)庫(kù)中保存最近期的數(shù)據(jù)。
H. 經(jīng)常查閱 DBA 用戶手冊(cè)
如果有可能的話，要廣泛的閱讀，包括 DBA 手冊(cè)，行業(yè)雜志，新聞 組或是郵件列表。
-------------------------------------------------------------
二．晚間維護(hù)過(guò)程
大部分的數(shù)據(jù)庫(kù)產(chǎn)品將受益于每晚確定的檢查進(jìn)程的運(yùn)行。
A. 收集 VOLUMETRIC 數(shù)據(jù)
1. 分析計(jì)劃和收集數(shù)據(jù)
更準(zhǔn)確的分析計(jì)算并保存結(jié)果。
a ） 如果你現(xiàn)在沒(méi)有作這些的話，用‘ MK VOLFACT.SQL‘ 來(lái)創(chuàng)建測(cè)定體積的 表。
b ） 收集晚間數(shù)據(jù)大小的信息，用‘ ANALYZE COMP.SQL‘ 。
c ） 收集統(tǒng)計(jì)結(jié)果，用‘ POP VOL.SQL‘ 。
d ） 在空閑的時(shí)候檢查數(shù)據(jù)，可能的話，每周或每個(gè)月進(jìn)行。
我是用 MS EXCEL 和 ODBC 的聯(lián)接來(lái)檢查數(shù)據(jù)和圖表的增長(zhǎng)
-------------------------------------------------------------
三．每周維護(hù)過(guò)程
A ． 查找被破壞的目標(biāo)
1. 對(duì)于每個(gè)給定表空間的對(duì)象來(lái)說(shuō)， NEXT_EXTENT 的大小是相同的，如
12/14/98 ，缺省的 NEXT_EXTENT 的 DATAHI 為 1G ， DATALO 為 500MB ，
INDEXES 為 256MB 。
A ） 檢查 NEXT_EXTENT 的設(shè)置，可用‘ NEXTEXT 。 SQL‘ 。
B ） 檢查已有的 EXTENTS ，可用‘ EXISTEXT 。 SQL‘ 。
2. 所有的表都應(yīng)該有唯一的主鍵
a ） 查看那些表沒(méi)有主鍵，可用‘ NO_PK.SQL‘ 。
b ） 查找那些主鍵是沒(méi)有發(fā)揮作用的，可用‘ DIS_PK.SQL‘ 。
c ） 所有作索引的主鍵都要是唯一的，可用‘ NONUPK 。 SQL‘ 來(lái)檢 查。
3. 所有的索引都要放到索引表空間中。運(yùn)行‘ MKREBUILD_IDX 。 SQL‘
4. 不同的環(huán)境之間的計(jì)劃應(yīng)該是同樣的，特別是測(cè)試環(huán)境和成品環(huán)境之間的 計(jì)劃應(yīng)該相同。
a ） 檢查不同的 2 個(gè)運(yùn)行環(huán)境中的數(shù)據(jù)類型是否一致，可用
‘ DATATYPE.SQL ‘。
b ） 在 2 個(gè)不同的實(shí)例中尋找對(duì)象的不同點(diǎn)， 可用
‘ OBJ_COORD.SQL ‘。
c ） 更好的做法是，使用一種工具，象尋求軟件的計(jì)劃管理器那樣的 工具。
B ． 查看是否有危害到安全策略的問(wèn)題。
C ． 查看報(bào)錯(cuò)的 SQL*NET 日志。
1. 客戶端的日志。
2. 服務(wù)器端的日志。
D ． . 將所有的警告日志存檔
E ． . 供應(yīng)商的主頁(yè)
1. ORACLE 供應(yīng)商
http://www.oracle.com
http://technet.oracle.com
http://www.oracle.com/support
http://www.
2. Quest Software
http://www.
3. Sun Microsystems
http://www.
----------------------------------------------------------------
四．月維護(hù)過(guò)程
A ．查看對(duì)數(shù)據(jù)庫(kù)會(huì)產(chǎn)生危害的增長(zhǎng)速度
1. 從以前的記錄或報(bào)告中回顧段增長(zhǎng)的變化以此來(lái)確定段增長(zhǎng)帶來(lái)危害
B ． 回顧以前數(shù)據(jù)庫(kù)優(yōu)化性能的調(diào)整
1. 回顧一般 ORACLE 數(shù)據(jù)庫(kù)的調(diào)整點(diǎn)，比較以前的報(bào)告來(lái)確定有害的發(fā)展 趨勢(shì)。
C ． 查看 I/O 的屏頸問(wèn)題
1. 查看前期數(shù)據(jù)庫(kù)文件的活動(dòng)性，比較以前的輸出來(lái)判斷有可能導(dǎo)致屏頸 問(wèn)題的趨勢(shì)。
D ． 回顧 FRAGMENTATION
E ． 計(jì)劃數(shù)據(jù)庫(kù)將來(lái)的性能
1. 比較 ORACLE 和操作系統(tǒng)的 CPU ，內(nèi)存，網(wǎng)絡(luò)，及硬盤的利用率以此
來(lái)確定在近期將會(huì)有的一些資源爭(zhēng)奪的趨勢(shì)
2. 當(dāng)系統(tǒng)將超出范圍時(shí)要把性能趨勢(shì)當(dāng)作服務(wù)水平的協(xié)議來(lái)看
F ． 完成調(diào)整和維護(hù)工作
1. 使修改滿足避免系統(tǒng)資源的爭(zhēng)奪的需要，這里面包括增加新資源或使預(yù)期 的停工。
----------------------------------------------------------------
五．附錄
A. 日常程序
-- free.sql
--To verify free space in tablespaces
--Minimum amount of free space
--document your thresholds:
--<tablespace_name> = <amount> m
SELECT tablespace_name, sum ( blocks ) as free_blk , trunc ( sum ( bytes ) /
(1024*1024) ) as free_m, max ( bytes ) / (1024) as big_chunk_k, count (*) as num_chunks
FROM dba_free_space GROUP BY tablespace_name
1. Space.sql
-- space.sql
-- To check free, pct_free, and allocated space within a tablespace
-- 11/24/98
SELECT tablespace_name, largest_free_chunk
, nr_free_chunks, sum_alloc_blocks, sum_free_blocks
, to_char(100*sum_free_blocks/sum_alloc_blocks, ‘09.99‘) || ‘%‘
AS pct_free
FROM ( SELECT tablespace_name , sum(blocks) AS sum_alloc_blocks
FROM dba_data_files GROUP BY tablespace_name )
, ( SELECT tablespace_name AS fs_ts_name
, max(blocks) AS largest_free_chunk
, count(blocks) AS nr_free_chunks
, sum(blocks) AS sum_free_blocks FROM dba_free_space
GROUP BY tablespace_name ) WHERE tablespace_name = fs_ts_name
2. analyze5pct.sql
-- analyze5pct.sql
-- To analyze tables and indexes quickly, using a 5% sample size
-- (do not use this script if you are performing the overnight
-- collection of volumetric data)
-- 11/30/98
BEGIN
dbms_utility.analyze_schema ( ‘&OWNER‘, ‘ESTIMATE‘, NULL, 5 ) ;
END ;
/
3. nr_extents.sql
-- nr_extents.sql
-- To find out any object reaching <threshold>
-- extents, and manually upgrade it to allow unlimited
-- max_extents (thus only objects we *expect* to be big
-- are allowed to become big)
-- 11/30/98
SELECT e.owner, e.segment_type , e.segment_name , count(*) as nr_extents ,
s.max_extents
, to_char ( sum ( e.bytes ) / ( 1024 * 1024 ) , ‘999,999.90‘) as MB
FROM dba_extents e , dba_segments s
WHERE e.segment_name = s.segment_name
GROUP BY e.owner, e.segment_type , e.segment_name , s.max_extents
HAVING count(*) > &THRESHOLD
OR ( ( s.max_extents - count(*) ) < &&THRESHOLD )
ORDER BY count(*) desc
4. spacebound.sql
-- spacebound.sql
-- To identify space-bound objects. If all is well, no rows are returned.
-- If any space-bound objects are found, look at value of NEXT extent
-- size to figure out what happened.
-- Then use coalesce (alter tablespace <foo> coalesce .
-- Lastly, add another datafile to the tablespace if needed.
-- 11/30/98
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
B. 每晚處理程序
1. mk_volfact.sql
-- mk_volfact.sql (only run this once to set it up; do not run it nightly!)
-- -- Table UTL_VOL_FACTS
CREATE TABLE utl_vol_facts (
table_name VARCHAR2(30),
num_rows NUMBER,
meas_dt DATE )
TABLESPACE platab
STORAGE (
INITIAL 128k
NEXT 128k
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS unlimited
)
/
-- Public Synonym
CREATE PUBLIC SYNONYM utl_vol_facts FOR &OWNER..utl_vol_facts
/
-- Grants for UTL_VOL_FACTS
GRANT SELECT ON utl_vol_facts TO public
/
2. analyze_comp.sql
--
-- analyze_comp.sql
--
BEGIN
sys.dbms_utility.analyze_schema ( ‘&OWNER‘,‘COMPUTE‘);
END ;
/
3. pop_vol.sql
--
-- pop_vol.sql
--
insert into utl_vol_facts
select table_name
, NVL ( num_rows, 0) as num_rows
, trunc ( last_analyzed ) as meas_dt
from all_tables -- or just user_tables
where owner in (‘&OWNER‘) -- or a comma-separated list of owners
/
commit
/
C. 每周處理程序
1. nextext.sql
--
-- nextext.sql
--
-- To find tables that don‘t match the tablespace default for NEXT extent.
-- The implicit rule here is that every table in a given tablespace should
-- use the exact same value for NEXT, which should also be the tablespace‘s
-- default value for NEXT.
--
-- This tells us what the setting for NEXT is for these objects today.
--
-- 11/30/98
SELECT segment_name, segment_type, ds.next_extent as Actual_Next
, dt.tablespace_name, dt.next_extent as Default_Next
FROM dba_tablespaces dt, dba_segments ds
WHERE dt.tablespace_name = ds.tablespace_name
AND dt.next_extent !=ds.next_extent
AND ds.owner = UPPER ( ‘&OWNER‘ )
ORDER BY tablespace_name, segment_type, segment_name
2. existext.sql
--
-- existext.sql
--
-- To check existing extents
--
-- This tells us how many of each object‘s extents differ in size from
-- the tablespace‘s default size. If this report shows a lot of different
-- sized extents, your free space is likely to become fragmented. If so,
-- this tablespace is a candidate for reorganizing.
--
-- 12/15/98
SELECT segment_name, segment_type
, count(*) as nr_exts
, sum ( DECODE ( dx.bytes,dt.next_extent,0,1) ) as nr_illsized_exts
, dt.tablespace_name, dt.next_extent as dflt_ext_size
FROM dba_tablespaces dt, dba_extents dx
WHERE dt.tablespace_name = dx.tablespace_name
AND dx.owner = ‘&OWNER‘
GROUP BY segment_name, segment_type, dt.tablespace_name, dt.next_extent
3. No_pk.sql
--
-- no_pk.sql
--
-- To find tables without PK constraint
--
-- 11/2/98
SELECT table_name
FROM all_tables
WHERE owner = ‘&OWNER‘
MINUS
SELECT table_name
FROM all_constraints
WHERE owner = ‘&&OWNER‘
AND constraint_type = ‘P‘
4. disPK.sql
--
-- disPK.sql
--
-- To find out which primary keys are disabled
--
-- 11/30/98
SELECT owner, constraint_name, table_name, status
FROM all_constraints
WHERE owner = ‘&OWNER‘ AND status = ‘DISABLED‘ AND constraint_type = ‘P‘
5. nonuPK.sql
--
-- nonuPK.sql
--
-- To find tables with nonunique PK indexes. Requires that PK names
-- follow a naming convention. An alternative query follows that
-- does not have this requirement, but runs more slowly.
--
-- 11/2/98
SELECT index_name, table_name, uniqueness
FROM all_indexes
WHERE index_name like ‘&PKNAME%‘
AND owner = ‘&OWNER‘ AND uniqueness = ‘NONUNIQUE‘
SELECT c.constraint_name, i.tablespace_name, i.uniqueness
FROM all_constraints c , all_indexes i
WHERE c.owner = UPPER ( ‘&OWNER‘ ) AND i.uniqueness = ‘NONUNIQUE‘
AND c.constraint_type = ‘P‘ AND i.index_name = c.constraint_name
6. mkrebuild_idx.sql
--
-- mkrebuild_idx.sql
--
-- Rebuild indexes to have correct storage parameters
--
-- 11/2/98
SELECT ‘a(chǎn)lter index ‘ || index_name || ‘ rebuild ‘
, ‘tablespace INDEXES storage ‘
|| ‘ ( initial 256 K next 256 K pctincrease 0 ) ; ‘
FROM all_indexes
WHERE ( tablespace_name != ‘INDEXES‘
OR next_extent != ( 256 * 1024 )
)
AND owner = ‘&OWNER‘
/
7. datatype.sql
--
-- datatype.sql
--
-- To check datatype consistency between two environments
--
-- 11/30/98
SELECT
table_name,
column_name,
data_type,
data_length,
data_precision,
data_scale,
nullable
FROM all_tab_columns -- first environment
WHERE owner = ‘&OWNER‘
MINUS
SELECT
table_name,
column_name,
data_type,
data_length,
data_precision,
data_scale,
nullable
FROM all_tab_columns@&my_db_link -- second environment
WHERE owner = ‘&OWNER2‘
order by table_name, column_name
8. obj_coord.sql
--
-- obj_coord.sql
--
-- To find out any difference in objects between two instances
--
-- 12/08/98
SELECT object_name, object_type
FROM user_objects
MINUS
SELECT object_name, object_type
FROM user_objects@&my_db_link
六 . 參考文獻(xiàn)
1. Loney, Kevin Oracle8 DBA Handbook
2. Cook, David Database Management from Crisis to Confidence
[ http://www./ ]
3. Cox, Thomas B. The Database Administration Maturity Model