[轉]ORACLE資料庫管理員應做的事

ORACLE資料庫管理員應按如下方式對ORACLE資料庫系統做定期監控：

(1). 每天對ORACLE資料庫的執行狀態,日誌檔案,備份情況,資料庫的空間使用情況,系統資源的使用情況進行檢查,發現並解決問題。
(2). 每週對資料庫物件的空間擴充套件情況,資料的增長情況進行監控,對資料庫做健康檢查,對資料庫物件的狀態做檢查。
(3). 每月對錶和索引等進行Analyze,檢查表空間碎片,尋找資料庫效能調整的機會,進行資料庫效能調整,提出下一步空間管理計劃。對ORACLE資料庫狀態進行一次全面檢查。

====================
★每天的工作★
====================
(1).確認所有的INSTANCE狀態正常.登陸到所有資料庫或例程,檢測ORACLE後臺程式:
$ps –-ef|grep ora

(2). 檢查檔案系統的使用（剩餘空間）。如果檔案系統的剩餘空間小於20%，需刪除不用的檔案以釋放空間。
$df -k

(3). 檢查日誌檔案和trace檔案記錄alert和trace檔案中的錯誤.連線到每個需管理的系統
※ 使用’telnet’
※ 對每個資料庫,cd 到bdump目錄,通常是$ORACLE_BASE//bdump
※ 使用 Unix ‘tail’命令來檢視alert_.log檔案
※ 如果發現任何新的ORA- 錯誤,記錄並解決

(4). 檢查資料庫當日備份的有效性。
※對RMAN備份方式:
檢查第三方備份工具的備份日誌以確定備份是否成功
※對EXPORT備份方式:
檢查exp日誌檔案以確定備份是否成功
※對其他備份方式:
檢查相應的日誌檔案

(5). 檢查資料檔案的狀態記錄狀態不是“online”的資料檔案，並做恢復。
Select file_name from dba_data_files where status=’OFFLINE’

(6). 檢查表空間的使用情況
SELECT tablespace_name, max_m, count_blocks free_blk_cnt, sum_free_m,
TO_CHAR (100 * sum_free_m / sum_m, '99.99') || '%' AS pct_free
FROM (SELECT tablespace_name, SUM (BYTES) / 1024 / 1024 AS sum_m
FROM dba_data_files
GROUP BY tablespace_name),
(SELECT tablespace_name AS fs_ts_name,
MAX (BYTES) / 1024 / 1024 AS max_m,
COUNT (blocks) AS count_blocks,
SUM (BYTES / 1024 / 1024) AS sum_free_m
FROM dba_free_space
GROUP BY tablespace_name)
WHERE tablespace_name = fs_ts_name ;

(7). 檢查剩餘表空間
/* Formatted on 2007/03/01 15:56 (Formatter Plus v4.8.7) */
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;

(8). 監控資料庫效能執行bstat/estat生成系統報告或者使用statspack收集統計資料
※注意:TOP-5事件

(9). 檢查資料庫效能，記錄資料庫的cpu使用、IO、buffer命中率等等
使用vmstat,iostat,glance,top等命令

(10). 日常出現問題的處理。

====================
★每週的工作★
====================
(1). 控資料庫物件的空間擴充套件情況
根據本週每天的檢查情況找到空間擴充套件很快的資料庫物件,並採取相應的措施
-- 刪除歷史資料
--- 擴表空間
alter tablespace add datafile ‘’ size ;
--- 調整資料物件的儲存引數
next extent
pct_increase

(2). 監控資料量的增長情況
根據本週每天的檢查情況找到記錄數量增長很快的資料庫物件,並採取相應的措施
-- 刪除歷史資料
--- 擴表空間
alter tablespace add datafile ‘’ size ;

(3). 系統健康檢查
檢查以下內容:
init.ora
controlfile
redo log file
archiving
sort area size
tablespace(system,temporary,tablespace fragment)
datafiles(autoextend,location)
object(number of extent,next extent,index)
rollback segment
logging &tracing(alert.log,max_dump_file_size,sqlnet)

(4). 檢查無效的資料庫物件
/* Formatted on 2007/03/01 16:04 (Formatter Plus v4.8.7) */
--look over invalid objects
SELECT owner, object_name, object_type
FROM dba_objects
WHERE status = 'invalid';

(5). 檢查不起作用的約束
/* Formatted on 2007/03/01 16:07 (Formatter Plus v4.8.7) */
SELECT owner, constraint_name, table_name, constraint_type, status
FROM dba_constraints
WHERE status = 'DISABLED' AND constraint_type = 'P' ;

(6). 檢查無效的trigger
/* Formatted on 2007/03/01 16:08 (Formatter Plus v4.8.7) */
--look over triggers state
SELECT owner, trigger_name, table_name, status
FROM dba_triggers
WHERE status = 'DISABLED';

====================
★每月的工作★
====================
(1). Analyze Tables/Indexes/Cluster
analyze table estimate statistics sample 50 percent;

(2). 檢查表空間碎片
根據本月每週的檢查分析資料庫碎片情況,找到相應的解決方法

(3). 尋找資料庫效能調整的機會
比較每天對資料庫效能的監控報告,確定是否有必要對資料庫效能進 行調整

(4). 資料庫效能調整
如有必要,進行效能調整

(5). 提出下一步空間管理計劃
根據每週的監控,提出空間管理的改進方法

======================================
======================================
|| ★★★Oracle DBA 日常管理★★★ ||
======================================
======================================
目的：這篇文件有很詳細的資料記錄著對一個甚至更多的ORACLE 資料庫每天的，每月的，每年的執行的狀態的結果及檢查的結果，在文件的附錄中你將會看到所有檢查，修改的SQL和PL/SQL 程式碼。
-------------
★目錄 ★
-------------
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 的執行情況
檢查每個被管理機器的‘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:.htm =>system
metrics=>CPU 利用頁，CPU 的最大限度為400，當CPU 的佔用保持在350 以上有一段時間的話，我們就需要檢視及研究出現的問題。

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

H. 經常查閱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. 不同的環境之間的計劃應該是同樣的，特別是測試環境和成品環境之間的 計劃應該相同。
a） 檢查不同的2 個執行環境中的資料型別是否一致，可用‘DATATYPE.SQL’。
b） 在2 個不同的例項中尋找物件的不同點， 可用‘OBJ_COORD.SQL’。
c） 更好的做法是，使用一種工具，象尋求軟體的計劃管理器那樣的 工具。

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

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

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

★E．供應商的主頁
====================
1. ORACLE 供應商
2. Quest Software
3. Sun Microsystems
=====================
----------------------------------------------------------------
==================
★四．月維護過程★
==================
A．檢視對資料庫會產生危害的增長速度
>從以前的記錄或報告中回顧段增長的變化以此來確定段增長帶來危害
B． 回顧以前資料庫最佳化效能的調整
>回顧一般ORACLE 資料庫的調整點，比較以前的報告來確定有害的發展趨勢。
C． 檢視I/O 的屏頸問題
1. 檢視前期資料庫檔案的活動性，比較以前的輸出來判斷有可能導致屏頸問題的趨勢。
D． 回顧FRAGMENTATION
E． 計劃資料庫將來的效能
>比較ORACLE 和作業系統的CPU，記憶體，網路，及硬碟的利用率以此來確定在近期將會有的一些資源爭奪的趨勢
>當系統將超出範圍時要把效能趨勢當作服務水平的協議來看
F． 完成調整和維護工作
>使修改滿足避免系統資源的爭奪的需要，這裡麵包括增加新資源或使預期的停工。

----------------------------------------------------------------
==============
★五．附錄★
==============

===============
★A. 日常程式★
===============
※做好每天的記錄,總結分析表空間的增長幅度※
-★- Free.sql
/* Formatted on 2007/03/06 16:54 (Formatter Plus v4.8.7) */
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

-★- Space.sql
-- To check free, pct_free, and allocated space within a tablespace
/* Formatted on 2007/03/06 16:58 (Formatter Plus v4.8.7) */
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

-★- 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)

BEGIN
dbms_utility.analyze_schema ( '&OWNER', 'ESTIMATE', NULL, 5 ) ;
END ;
/

-★- 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)

/* Formatted on 2007/03/06 17:25 (Formatter Plus v4.8.7) */
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

-★- 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.
/* Formatted on 2007/03/06 17:26 (Formatter Plus v4.8.7) */
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. 每晚處理程式★
===================
-★- 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
/

-★-analyze_comp.sql

BEGIN
sys.dbms_utility.analyze_schema ( '&OWNER','COMPUTE');
END ;
/

-★-pop_vol.sql
/* Formatted on 2007/03/06 17:28 (Formatter Plus v4.8.7) */
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
-- 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.
/* Formatted on 2007/03/06 17:30 (Formatter Plus v4.8.7) */
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
-- 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.
/* Formatted on 2007/03/06 17:31 (Formatter Plus v4.8.7) */
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
-- To find tables without PK constraint
/* Formatted on 2007/03/06 17:31 (Formatter Plus v4.8.7) */
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
-- To find out which primary keys are disabled
/* Formatted on 2007/03/06 17:32 (Formatter Plus v4.8.7) */
SELECT owner, constraint_name, table_name, status
FROM all_constraints
WHERE owner = '&OWNER' AND status = 'DISABLED' AND constraint_type = 'P'

5. 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.
/* Formatted on 2007/03/06 17:33 (Formatter Plus v4.8.7) */
SELECT index_name, table_name, uniqueness
FROM all_indexes
WHERE index_name LIKE '&KNAME%'
AND owner = '&OWNER'
AND uniqueness = 'NONUNIQUE'
UNION
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
-- Rebuild indexes to have correct storage parameters
/* Formatted on 2007/03/06 17:33 (Formatter Plus v4.8.7) */
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
-- To check datatype consistency between two environments
/* Formatted on 2007/03/06 17:34 (Formatter Plus v4.8.7) */
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
-- To find out any difference in objects between two instances
/* Formatted on 2007/03/06 17:35 (Formatter Plus v4.8.7) */
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