OEM Database Control-設定主機證明資料

當透過OEM Database Control設定備份值等設定時，都會要求輸入「主機證明資料」，如下圖所示：







所謂的主機證明資料就如字面上所解釋，你必須提供Server上登入的帳號密碼，而這帳號可以是網域帳號或者該主機作業系統上建立的帳號；但一開始大家都應該會遇到跟我相同的情況，即便你多努力的敲入正確的帳號密碼，OEM都會告訴你「密碼錯誤」之類的訊息。要解決這個問題，你得必須手動將帳號加入「以批次工作登入」的權限才行：

1. 打開 「控制台 --> 系統管理工具--> 本機安全性原則」
2. 點選左方「本機原則 / 使用者權限指派」，找到右方的「以批次工作登入」，double-click它進行設定
3. 在「本機安全性原則設定」標籤中，按下「新增使用者或群組」按紐，加入欲設定為主機證明資料的網域帳號或者Oracle所在主機的作業系統帳號。
4. 加入完成後，一路按下「確定」關閉剛剛的設定。


















再回到OEM試一次，就可以正確的辨別你輸入的帳號密碼了。

找出 Oracle Compiling Procedure會Hang住的原因

同事剛遇上一個挺有趣的 Oracle procedure Compile問題。當她以pl/sql develpoer 異動完某個procedure並按下F8執行compiling後，pl/sql develper僅僅顯示compiling訊息之外就沒再動靜， 應用程式就直接hang 在那裡，不知道到底出了什麼事情。這件事讓他困擾了一整個早上，最後只好把這個「磨練」的機會給了我。根據過往的經驗，這種情況有點類似A君開發中的時正在Edit Q Table，但剛好屁股痛去了洗手間，也忘了按Commit，而當B君也要去Edit Q Table，異動完成後按下Commit時卻怎樣也寫不進去，硬是hang在那裡。因此我查了一下解法，答案跟我想的差不多，重點只有一個： 找出誰在使用這個procefure，請它關閉或直接踢掉它就好了。

查詢語法如下：

SELECT B.OSUSER, A.*
FROM V$ACCESS A, V$SESSION B
WHERE A.OBJECT = 'MY_PROC'
AND A.SID = B.SID

結果就會告訴你：哪個session正在使用這物件，所以你無法異動該procedure。譬如說：

OSUSER    SID    OWNER            OBJECT      TYPE
--------- ----   --------------  ----------- ----------------------
murderer  519    SCHEMA_OWNER     MY_PROC     PROCEDURE 

表示使用者murderer (sessoon id = 519)這個傢伙正在使用My_PROC這個procedure，並且尚未釋放掉。大部分的情況下就是他正在執行testing 動作，但是因為程式迴圈很大或者他也去上廁所，因此呼叫的程序仍在執行中或暫停著，被呼叫的MY_PROC也就因此跟著被咬住了。Oracle此時就會禁止其他使用者異動該procedure，直到那位仁兄上廁所回來並結束那個testing的程序。

就這樣，以後找兇手簡單多了。

SQL 2005 Express安裝後的第一件事

VS2005附贈的SQL 2005 Express的安裝並不是難事，大約10來分鐘希哩呼嚕就可以裝完完畢。但惹人嫌是，工作列你看不到任何常駐的Instance圖示，得打開「控制台/系統管理工具/服務」後才能看到SQL Server(SQLExpress)已經自動執行中。但，當你想要用windows認證登入時，SQL Express會告知你不支援以遠端的方式登入使用。根據以往經驗，"遠端"登入這件事，通訊協定就是關鍵角色。所以解法如下：

1. 打開「SQL Server 2005/組態工具/SQL Services Configuration Manager」；
2. 找到「SQL Server 2005網路組態/SQLExpress的通訊協定」；
3. 找到右邊通訊協定清單中的TCP/IP協定，按右鍵啟用它(下圖)。









4. 重新啟動 SQL Server(SQLExpress)服務

接著你就可以用windows認證的方式登入了。

DatabaseMail的郵件報表功能設定

SQL2005提供郵件通知的服務，並透過profile設定的方式，系統也可發送工作完成後的郵件報表。但很不幸的，從郵件服務設定到產生工作的郵件報表的過程有點類似樂高積木，中間眉眉角角的設定一不小心可能就會漏掉，搞得DBA只好"再度"捲起袖子巡田了。最近遇到的問題是工作完成後無法產生郵件報表，SQL Server給的錯誤訊息如下：

無法產生郵件報表。執行 Transact-SQL 陳述式或批次時發生例外狀況。未設定全域設定檔。請在 @profile_name 參數中指定設定檔名稱。

開始對這訊息是一頭務水，Mail發送測試沒問題，SQL Agent Service也重開過，怎麼還會給這莫名奇妙的訊息？查了一下才知道原來是郵件服務找不到預設的 profile 檔，所以郵件報表的也就沒辦法產生了。解法很簡單：

1. Database Mail按右鍵，點選「設定Database Mail」，會出現Database Mail組態精靈
2. 選擇「管理設定檔安全性」













3. 在「公用設定檔」頁籤下方選擇一個已建立好的Profile，並在「預設設定檔」欄位選擇「是」，這樣就成啦。













說穿了也沒什麼，但沒做這一步郵件報表就是給它看得到吃不到。給大家參看囉。

10g2 無解的排程工作建立問題

晚上在測試區根據 segment advisor 建立排程工作，希望將佔著茅坑不拉屎的多餘空間給釋放出來。但EM這傢伙建議了一堆(132個table)可以釋放空間的table，在我想當然爾全選後，卻在建立工作排程時出現了 Failed to commit: ORA-16612: 屬性 "job_action" 的字串值太長...的錯誤。心中頓時一陣無力，大佬啊~，晚上八點了ㄌㄟ，你還這樣搞我有沒有人性哪？哀，上 metalink找了一下，Oracle說(779137.1, Issues Running EM Advisors Recommendations)此題無解，請等到11g時再幫我解。其主要原因是儲存執行工作所需的SQL語句變數，其型態為VARCHAR2，所以最大只能容納4,000bytes，超過這個大小的字串就死給你看。

哀，大佬啊~八點半了ㄌㄟ

看了workaround建議的分批建立工作，只好這樣啦。

手癢的後果-- MAX_SGA_SIZE 的惡夢

今日根據OEM的建議結果，調整DB速度瓶頸之一的方式就是增加 SGA_TARGET 的 Size。SGA_TARGET 建議值為2G多的大小，好大喜功的我心癢手也癢，很自然的就給他照建議調下去了。但Oracle預設的MAX_SGA_SIZE只有1G，所以得再回頭調整 MAX_SGA_SIZE 之後才能再繼續調整SGA_TARGET的大小。

手癢繼續驅動著我，懷疑精神絲毫沒在我腦袋裡閃過，於是我又很自然的透過OEM先將MAX_SGA_SIZE改成2G，然後喜孜孜的重新啟動Oracle後，期待接下來的調整步驟。但是，惡運總是來得比想像中的快，Oracle冷靜的回覆我：

ORA-27100: shared memory realm already exits

果然，手癢沒好下場。

好吧，Google了一下解決方案，作法比想像中的簡單，幾個步驟就行：

1. 找到spfile的位置，將spfile改名
2. 登入成sysdba，以預設的Oracle ini檔啟動DB，指令可能長得如下：
   
   startup pfile='D:\oracle\product\10.2.0\admin\myDB\pfile\init.ora.226200920330'
   

資料庫就呼嚕呼嚕的起來了。這件事告訴我們，沒把握的事盡量在一個夜深人靜的時候做比較好，否則一堆眼睛可憐兮兮的看著你的時候，壓力可真的不是開玩笑的。 ><"

設定SQL 2005 Net Send 服務

說穿了 SQL 2005的Net Send就是執行 Windows 的 Messenger 服務，要啟動它簡單幾個步驟就搞定：

1. 啟動SQL Server主機 的Messenger 服務
2. 新增操作員
3. 設定操作員的Net Send的主機位址(IP也可)
4. 開啟操作員登入的Client端主機的Messenger 服務

四個步驟就可以搞定通知服務了。

Transaction log 吃掉你的硬碟時怎麼辦?

前幾天剛好遇到交易紀錄檔長滿了(700多GB)，搞到硬碟空間一滴不剩，相依的應用系統陸續掛點。最後判斷log不重要後，解決的方式就採破釜沉舟的方式進行：

1. 對交易紀錄檔作截斷處理(這動作還不會釋放空間)

backup log mydb with truncate_only

參考：http://technet.microsoft.com/zh-tw/library/ms189085.aspx

2. 壓縮交易紀錄檔空間(這個才會釋放空間)

dbcc shrinkfile(‘mydb_log’,2)　//給我縮到2mb!

參考：http://technet.microsoft.com/zh-tw/library/ms189493.aspx

檢閱 Oracle Enterprise Manager Data Control的port number

由於我常記不住這麼簡單的東西，只好再拿來充數了。

關於Oracel Enterprise Manager Data Control的portal number，可以打開$ORACLE_HOME/install/portlist.ini檔的HTTP連接埠設定：

iSQL*Plus HTTP 連接埠號碼 =5560
Enterprise Manager 主控台 HTTP 連接埠 (MyOracleDB) = 5500　//就是這個光～
Enterprise Manager 代理程式連接埠 (MyOracleDB) = 3938

連接到Data Control時就可打上 http://myDBHostName:5500/em 就可連上了~

參考：Accessing the Oracle Enterprise Manager Data Control

如何解決Data Pump匯入中途因空間不足而暫停的問題

今天凌晨六點爬起來看Import的結果，結果我一個粗心大意，竟然沒發現Data File空間不足，導致工作Hang在那邊，一副事不關己的等我解決。錯誤的代碼分別ORA-39171為提示你說工作已經暫停（廢話，我用眼睛看不來嗎？），另一個是ORA-01653告訴你空間已經不足。解決的方式很簡單，只要另外再加一個Data File就可。

不過，有趣的是Data File本身的設定引起我的興趣。Data File本身的大小限制乃是受限於DB_BLOCK_SIZE(metalink:Doc ID: 468096.1)，每個版本的Oracle各不同的設定：

• 11.1 Online Reference for DB_BLOCK_SIZE
• 10.2 Online Reference for DB_BLOCK_SIZE
• 10.1 Online Reference for DB_BLOCK_SIZE
• 9.2 Online Reference for DB_BLOCK_SIZE
• 9.0 Online Reference for DB_BLOCK_SIZE

以10.2版來說，DB_BLOCK_SIZE就是4096~8192 bytes間的值。而每個Data File的檔案大小以 Maximum Blocks x Maximum DB_BLOCK_SIZE計算。所以10.2版Data File的最大Size = (4194303 x 8192) /1024 /1024 /1024 = 32GB。因此在新增Data File的時候，就要注意到Data File的最大上限為何，否則就會收到 ORA-01144的錯誤（換句話說，並沒有所謂的檔案大小無上限，且其所在的作業系統也會有對檔案的物理限制）。

讓你的 SQL 指令斷行吧

常會為了輸出某些管理指令而使用spool 指令，但困擾的是如果需要同一個SQL內產出兩行不同指令 ，那麼分行就會是個問題。例如像是以下產出的指令就會連成一行：

SELECT 'TRUNCATE TABLE ' || table_name || ' drop usage; ' ||
'UPDATE myTableList SET status = ''F'' WHERE table_name = ''' || table_name || ''';'
FROM dba_tables
WHERE table_name like 'MGM%';

產出結果：

TRUNCATE TABLE MGM01 drop usage; UPDATE myTableList SET status = 'F' WHERE table_name = 'MGM01';

除了醜之外，command mode執行時還會因為兩個指令中間的分號出現 '字元錯誤'的錯誤提示。如果硬是拆分成兩個SQL指令分別產出 truncate table 跟 update 指令，想想就覺得很討厭。所以，最好的方式就是將兩個SQL指令字串斷行即可。

方法很簡單，加上 chr(13)就可了。改變如下：

SELECT 'TRUNCATE TABLE ' || table_name || ' drop usage; ' || chr(13)
'UPDATE myTableList SET status = ''F'' WHERE table_name = ''' || table_name || ''';'
FROM dba_tables
WHERE table_name like 'MGM%';

結案~

Update 多欄位時需注意Subquery資料需與目標Table一對一對映

Oracle更新多欄位的方式通常會以SET (column_name, column_name, ...) = (subquery4) 的形式執行更新。不過需要注意的是，必須確保目標Table的資料列能與Subquery一對一對的對應映。

以例子中Multi-Column 的Update來看，如果將 t1 建立時的rownum改成 12 更新就會失敗；這時候就只好屈就於cursor來解了：

cursor cur_t2 is
select table_name, tablespace_name
from t2;

for cur_rec in cur_t2 loop
update t1
set t1.tablespace_name = t2.tablespace_name
where t1.table_name = cur_rec.table_name;
end loop;

透過TNS_ADMIN部署TNSNAMES.ORA

前幾天遇到一個如何統一管理tnsnames.ora的問題，對於散落在user端的tnsnames.ora設定通常是一個小麻煩。基本上，只要透過logon script就可以輕鬆將統一的設定好的tnsnames.ora複製到user端，但手法卻醜陋了一點，更別提得要先登入系統時才會更新一次的問題。因此如果能讓user端讀取指定共享目錄下的tnsnames.ora，不是更輕鬆些？以後管理者只要維護該目錄下的tnsnames.ora，user端就能隨時讀取到最新的設定，皆大歡喜。

設定的方式相單簡單，主要準備幾個材料即可；

1. 首先，準備好server端的分享目錄，假設為\\myServer\myShareFolder，將設定完整的tnsnames.ora放進去；
2. 接下來找出user端的的oracle 的registry設定後，加入TNS_ADMIN。以9i為例，找到Oracle在C:\ORACLE\ora92\bin\oracle.key檔中記錄的Registry設定位址 Software\ORACLE\HOME0，接著打開 HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0，並加入字串「TNS_ADMIN」，設定其值為「\\myServer\myShareFolder」；　
3. 將HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0的機碼匯出成ora9i_tnsnames.reg；
4. 將上述準備好的reg檔以logon script的方式，以 regedit ora9i_tnsnames.reg /s 的方式部署到user端。
   

當下次user登入時便會在其oracle home registry中，匯入TNS_ADMIN的設定值，轉而讀取server上的設定而略過本機的tnsnames.ora設定。大功告成！logon script只要確定好部署完畢即可移除，但若有其他管理性目的則不在此述。以後管理者只要維護一份tnsnames.ora即可，user端也隨時可讀取到最新的設定，樂哉～