Oracle DBA的日常工作(zt)

作者： Thomas B. Cox, with Christine Choi
目的： 這篇文件有很詳細的資料記錄著對一個甚至更多的ORACLE資料庫
每天的，每月的，每年的執行的狀態的結果及檢查的結果，在文件的附錄中你將會看到所有檢查，修改的SQL和PL/SQL程式碼。
修改筆記：1.1在Steve DeNunzio 的'existext.sql'中所識別的型別
1.2固定的型別
1.3 增加的Gnu Public License ；在重建index中增加pctincr 0
1.4
加了一個最新的觀點，是從GEOCITIES的主頁上得到的。

目錄
1， 日常維護程式
A． 檢查已起的所有例項
B． 查詢一些新的警告日誌
C． 檢查DBSNMP是否在執行
D． 檢查資料庫備份是否正確
E． 檢查備份到磁帶中的檔案是否正確
F． 檢查資料庫的效能是否正常合理，是否有足夠的空間和資源
G． 將文件日誌複製到備份的資料庫中
H． 要常看DBA使用者手冊
2、晚間維護程式
A．收集 VOLUMETRIC 的資料
3、每週維護工作
A． 查詢那些破壞規則的OBJECT
B． 查詢是否有違反安全策略的問題
C． 檢視錯誤地方的SQL*NET日誌
D． 將所有的警告日誌存檔
E． 經常訪問供應商的主頁
4．月維護程式
A． 檢視對資料庫會產生危害的增長速度
B． 回顧以前資料庫最佳化效能的調整
C． 檢視I/O的屏頸問題
D． 回顧 FRAGMENTATION
E． 將來的執行計劃
F． 檢視調整點和維護
5 附錄
A． 月維護過程
B． 晚間維護過程
C． 周維護過程
6 參考文獻

一．日維護過程
A．檢視所有的例項是否已起
確定資料庫是可用的，把每個例項寫入日誌並且執行日報告或是執行測試檔案。當然有一些操作我們是希望它能自動執行的。
可選擇執行：用ORACLE管理器中的'PROBE'事件來檢視

B。查詢新的警告日誌檔案
1. 聯接每一個操作管理系統
2. 使用'TELNET'或是可比較程式
3. 對每一個管理例項，經常的執行$ORACLE_BASE//bdump操作，並使其能回退到控制資料庫的SID。
4.
在提示下，使用UNIX中的'TAIL'命令檢視alert_.log，或是用其他方式檢查檔案中最近時期的警告日誌
5.
如果以前出現過的一些ORA_ERRORS又出現，將它記錄到資料庫恢復日誌中並且仔細的研究它們，這個資料庫恢復日誌在〈FILE〉中

C．檢視DBSNMP的執行情況
1． 檢查每個被管理機器的'DBSNMP'程式並將它們記錄到日誌中。
在UNIX中，在命令列中，鍵入 ps -ef | grep
dbsnmp,將回看到2個DBSNMP程式在執行。如果沒有，重啟DBSNMP。
D．查資料庫備份是否成功

E．檢查備份的磁帶文件是否成功

F．檢查對合理的效能來說是否有足夠的資源
1. 檢查在表空間中有沒有剩餘空間。
對每一個例項來說，檢查在表空間中是否存在有剩餘空間來滿足當天的預期的需要。當資料庫中已有的資料是穩定的，資料日增長的平均數也是可以計算出來，最小的剩餘空間至少要能滿足每天資料的增長。

A） 執行'FREE.SQL'來檢查表空間的剩餘空間。
B） 執行'SPACE.SQL'來檢查表空間中的剩餘空間百分率

2.檢查回滾段
回滾段的狀態一般是線上的，除了一些為複雜工作準備的專用段，它一般狀態是離線的.
a) 每個資料庫都有一個回滾段名字的列表。
b) 你可以用V$ROLLSTAT來查詢線上或是離線的回滾段的現在狀態.
c) 對於所有回滾段的儲存引數及名字，可用DBA_ROLLBACK_SEGS來查詢。但是它不如V$ROLLSTAT準確。

3.識別出一些過分的增長
檢視資料庫中超出資源或是增長速度過大的段，這些段的儲存引數需要調整
a） 收集日資料大小的資訊，可以用'ANALYZE5PCT.SQL'。如果你收集的是每晚的資訊，則可跳過這一步。
b） 檢查當前的範圍，可用'NR.EXTENTS.SQL'。
c） 查詢當前表的大小資訊。
d） 查詢當前索引大小的資訊。
e） 查詢增長趨勢。

4.確定空間的範圍。
如果範圍空間物件的NEXT_EXTENT比表空間所能提供的最大範圍還要大，那麼這將影響資料庫的執行。如果我們找到了這個目標，可以用'ALTER
TABLESPACE COALESCE'調查它的位置，或加另外的資料檔案。
A）執行'SPACEBOUND.SQL'。如果都是正常的，將不返回任何行。
5. 回顧CPU，記憶體，網路，硬體資源論點的過程
A）檢查CPU的利用情況，進到x:webphase2default.htm =>system
metrics=>CPU利用頁，CPU的最大限度為400，當CPU的佔用保持在350以上有一段時間的話，我們就需要檢視及研究出現的問題。

G．將存檔日誌複製到備用資料庫中
如果有一個備用資料庫，將適當的存檔日誌複製到備用資料庫的期望位置，備用資料庫中儲存最近期的資料。

F. 經常查閱DBA使用者手冊
如果有可能的話，要廣泛的閱讀，包括DBA手冊，行業雜誌，新聞組或是郵件列表。

二．晚間維護過程
大部分的資料庫產品將受益於每晚確定的檢查程式的執行。
A。收集VOLUMETRIC資料
1. 分析計劃和收集資料
更準確的分析計算並儲存結果，
a） 如果你現在沒有作這些的話，用'MK VOLFACT.SQL'來建立測定體積的表。
b） 收集晚間資料大小的資訊，用'ANALYZE COMP.SQL'。
c） 收集統計結果，用'POP VOL.SQL'。
d） 在空閒的時候檢查資料，可能的話，每週或每個月進行。
我是用MS EXCEL和ODBC 的聯接來檢查資料和圖表的增長

三．每週維護過程
A． 查詢被破壞的目標

1.
對於每個給定表空間的物件來說，NEXT_EXTENT的大小是相同的，如12/14/98，預設的NEXT_EXTENT的DATAHI為1G，DATALO為500MB，INDEXES為256MB。

A） 檢查NEXT_EXTENT的設定，可用'NEXTEXT。SQL'。
B） 檢查已有的EXTENTS，可用'EXISTEXT。SQL'。
2. 所有的表都應該有唯一的主鍵
a） 檢視那些表沒有主鍵，可用'NO_PK.SQL'。
b） 查詢那些主鍵是沒有發揮作用的，可用'DIS_PK.SQL'。
c） 所有作索引的主鍵都要是唯一的，可用'NONUPK。SQL'來檢查。
3所有的索引都要放到索引表空間中。執行'MKREBUILD_IDX。SQL'
4 不同的環境之間的計劃應該是同樣的, 特別是測試環境和成品環境之間的計劃應該相同。
d） 檢查不同的2個執行環境中的資料型別是否一致，可用'DATATYPE.SQL'。
e） 在2個不同的例項中尋找物件的不同點，可用'OBJ_COORD.SQL'.
f） 更好的做法是，使用一種工具，象尋求軟體的計劃管理器那樣的工具。

B． 檢視是否有危害到安全策略的問題。

C． 檢視報錯的SQL*NET日誌。
1. 客戶端的日誌。
2. 伺服器端的日誌。

D．.將所有的警告日誌存檔

E．.供應商的主頁
1. ORACLE供應商

2. Quest Software

3. Sun Microsystems

四．月維護過程
A．檢視對資料庫會產生危害的增長速度
1. 從以前的記錄或報告中回顧段增長的變化以此來確定段增長帶來危害

B． 回顧以前資料庫最佳化效能的調整
1. 回顧一般ORACLE資料庫的調整點，比較以前的報告來確定有害的發展趨勢。

C． 檢視I/O的屏頸問題
1. 檢視前期資料庫檔案的活動性，比較以前的輸出來判斷有可能導致屏頸問題的趨勢。

D． 回顧 FRAGMENTATION

E． 計劃資料庫將來的效能
1. 比較ORACLE和作業系統的CPU，記憶體，網路，及硬碟的利用率以此來確定在近期將會有的一些資源爭奪的趨勢
2. 當系統將超出範圍時要把效能趨勢當作服務水平的協議來看

F． 完成調整和維護工作
1.使修改滿足避免系統資源的爭奪的需要，這裡麵包括增加新資源或使預期的停工。

五、附錄
A、 日常 程式
--
-- free.sql
--
-- To verify free space in tablespaces
-- Minimum amount of free space
-- document your thresholds:
-- = 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
-- 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 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 'alter 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 -- 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

六、參考文獻
1. Loney, Kevin Oracle8 DBA Handbook
2. Cook, David Database Management from Crisis to Confidence
[]
3. Cox, Thomas B. The Database Administration Maturity Model