Oracle DBA 生涯日誌: 2013

2013年12月18日星期三

Oracle - 如何查 Table 有沒有浪費空間

管理DB上最令人頭痛的問題是硬碟空間，很好解決也很頭痛的問題。
空間不夠，加硬碟就夠了，但要是沒辦法加硬碟呢

DBA 就要想辦法榨出空間出來

我們都知道 Table 再被刪除資料時他並不會把空間釋放，必須要由DBA 來主動幫 Table來瘦身

首先粗略找出DB內空閑空間最多的 Table (浪費空間比超過45%,且table size大於1GB )

SELECT *
FROM (SELECT OWNER,
TABLE_NAME,
SIZE_MB,
USE_SPACE_MB,
EMPTY_SPACE_MB,
ROUND ( (EMPTY_SPACE_MB / SIZE_MB) * 100, 2) ASTE_PER,
TABLESPACE_NAME
FROM (SELECT OWNER,
table_name,
ROUND ( (blocks * 8) / 1024, 2) "SIZE_MB",
ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2) "USE_SPACE_MB",
ROUND (
(blocks * 8 - (num_rows * avg_row_len / 1024))
/ 1024, 2) "EMPTY_SPACE_MB",
TABLESPACE_NAME
FROM dba_tables))
WHERE EMPTY_SPACE_MB > 0 AND SIZE_MB > 1024 AND WASTE_PER > 45
ORDER BY 6 DESC;

找出之後在使用指令 alter table table_name shrink space;

使用以上語法並不會把 Table 內的欄位含有 long blob 等等大型類型的 Size 計算進去
要更精準的算法, 首先使用 analyze table t compute statistics;

分析完後看一下dba_tables 該 Table 的 empty_blocks 就會出現值了, 再把empty_blocks * 8 (我這邊設定1個block是8K)

就可以知道空閑空間與 Size 的比了

目前只有 analyze 指令能收集已下空間訊息
EMPTY_BLOCKS,
AVG_SPACE,
CHAIN_CNT

2013年12月10日星期二

Oracle - 關於 recover database using backup controlfile

1. recover database using backup controlfile
如果丟失當前控制文件，用冷備份的控製文件恢復的時候，用來告訴oracle，不要以controlfile中的scn作為恢復的終點；

2. recover database until cancel
如果丟失current/active redo的時候，手動指定終點。

3. recover database using backup controlfile until cancel;
如果丟失當前controlfile並且current/active redo都丟失，會先去自動應用歸檔日誌,可以實現最大的恢復；

4. recover database until cancel using backup controlfile;
如果丟失當前controlfile並且current/active redo都丟失，以舊的redo中的scn為恢復終點。因為沒有應用歸檔日誌，所有會丟失數據。

要理解recover database using backup controlfile，先理解recover database，也就是說，不加using backup controlfile的情況。

在普通的recover database 或者recover tablespace, recover datafile時， Oracle會以當前controlfile所紀錄的SCN為準，利用archive log和redo log的redo entry, 把相關的datafile 的block恢復到“當前controlfile所紀錄的SCN ”

而某些情況下，Oracle需要把數據恢復到比當前controlfile所紀錄的SCN還要靠後的位置（比如說，control file是backup controlfile , 或者controlfile是根據trace create的。）,這時候，就需要用using backup controlfile. 恢復就不會受“當前controlfile所記錄的SCN”的限制。這時候的限制就來自於你的語句(until time , until scn)，或者可用的archive log(until cancel) ...

2013年12月9日星期一

Oracle - Controlfile 還原

當 Oracle DBA 最討厭遇到的事情就是檔案毀損、資料不見 , 還記得我的老師這麼說過, 機房可以爆炸但資料不能不見, 逼不得已毀損也只能以最小的損失為原則, 這句話我一直謹記在心。

資料無遺失的還原都是歸功於完好的備分策略
以下模擬示範的是 DB最重要的controlfile 損毀的各種情況

狀況1:發現 DB 狀態是shutdown ,重新open db報ORA-00205錯誤, 檢查alert Log有

ORA-00227 錯誤,估計是Controlfile出現毀損

說明:DB目前在shutdown狀態, rodo log file無損毀,每天晚上有做 controlfile backup

目標:controlfile 從 backup file還原, 資料並完全恢復

1.db 重開時, 報ora-00205錯誤, 查看alert log ,估計是controlfile 損毀

2.利用rman 來還原controlfile, 先取得dbid, 可以通過 autobackup file 檔名來獲得dbid

3.設定dbid , 並恢復 controlfile

4.把db開到mount, 再rman進行recover database

6.DB Open Resetlogs , 還原成功

狀況2:發現DB 狀態是shutdown ,重新open db報ORA-00205錯誤, 檢查alert log有

ORA-00227 錯誤訊息,估計是Controlfile出現毀損

說明:DB 目前在shutdown 狀態,rodo log file也都存在, 不過只有control file txt 備分

目標:controlfile 從 control file backup txt還原, 資料並完全恢復

狀況3:發現DB 狀態是shutdown ,重新open db報ORA-00205錯誤, 檢查alert Log有

ORA-00227 corrupt block detected in control file 錯誤,估計是Controlfile出現損毀,

最後發現inactive redo log file group也損毀

1.在進行線上操作發現DB在shutdown 狀態, 重新open時出現錯誤

2.使用指令( noresetlogs 模式)重新建立controlfile, 發現不能建立,原因是redo02.log也壞了

3.使用指令( resetlogs 模式)重新建立controlfile

4.查看壞掉的redo log file 狀態, 壞掉的redo log file不是current

5.不是current與active狀態,表示資料已經歸檔, 使用指令重置壞掉的 redo log file group

6.嘗試 open resetlogs , 發現還需要 recovery

7.recover database 發現不行, 改用using backup controlfile ,並且指定恢復到shutdown 前最後一個運行的redo log file, redo01.log 並不是最後, 改指定redo03.log

8 指定恢復到 redo03.log, recover 成功後open resetlogs

9.因新建的 controlfile 沒有temp,因此加上temp

Oracle - HWM 高水位

Oracle - HWM - 高水位說明

你可以把 table 想像成一個水庫， HWM是這個 table 使用空間最高的位置(水位線)， Low HWM 則是目前資料的位置(水位線)，HWM會隨著資料的新增一直往上漲。他有個特性就是他只會往上漲，不會因為資料的 delete 而自動跟著下跌。

Oracle 在 Select Full Table Scans 時，他則是掃瞄 HWM 水位線以下的 block，所以一個 table 的HWM有1000實際的資料只有10，Oracle 則是從 HWM 往下掃瞄，所以有人會說我的 table 資料很少，為什麼 select 很慢或許就是HWM的太高。

那有沒有辦法把HWM調降呢?

1.truncate
在刪除整個 table 資料時就會把HWM降到0, 刪除較大的 table 時推薦使用 truncate 指令來刪除。

2.alter table table_name move tablespace xxxx;
使用搬移指令來把 table 搬移到其他 tablespace 來達到重組 table 的效果, 但記得 move 後得要重建索引。

3. alter table table_name shrink space;
就有點類似 windows 裡的硬碟重組工具的效果,把後面的資料盡量移到前面去再降HWM，
這個階段會造成rowid的改變,因此需要enable row movement.

alter table TABLE_NAME enable row movement;

接下來就是來看看哪些 table 需要降 HWM

1.在這之前需要對 table 做 analyze

2.查詢一下目前的高度與空閑 block, 目前高度為 1882, 空閑則有38

3.查詢 table 的佔用的 block 數有多少

要是覺得空閑的block 太多就可以執行語法來降HWM已達到優化效果

以下SQL 也可以查詢

SELECT *
FROM (SELECT a.owner,
a.table_name,
round ((b.BYTES /1024/1024/1024),2) as size_gb,
a.blocks,
a.empty_blocks,
b.blocks total_blocks,
round ((((B.blocks-A.BLOCKS)/B.BLOCKS) * 100),2) WASTE_PER,
A.tablespace_name
FROM (SELECT owner,
table_name,
num_rows,
blocks,
empty_blocks,
tablespace_name
FROM dba_tables) a,
(SELECT owner,
segment_name,
segment_type,
blocks,
bytes
FROM dba_segments) b
WHERE a.owner = b.owner
AND a.table_name = b.segment_name
AND B.segment_type = 'TABLE')
WHERE WASTE_PER IS NOT NULL AND WASTE_PER >30 AND BLOCKS >0 AND size_gb >1
ORDER BY WASTE_PER DESC;