關於log file sync兩三事

今天Top Activity突然發現有趣的wait class：commit。看起來像下面橘色的那一小塊(請點圖放大)：

由於平常沒什麼特別注意，今日特別興起繼續往下追蹤，發現原來是 「log file sync」的wait event：

查了一下文章，原來是app可能大量用了batch transaction的寫法，但是log buffer或者redo log file所在的硬碟效率有待加強的時候，就容易發生。嗯，這個要開始注意了..

取得Oracle 物件的DDL

因工作關係，寫了取得Table、Procedure、Function、Trigger等物件的DDL的函式，並搭配C#使用；我把文章寫在這裡，分享囉。

Oracle複寫到SQL 2005時，卡在DELETE ARTICLE的終極解法

前篇文章提到當利用SQL 2005複寫機制，將Oracle複寫到SQL端時，一段時間後就會卡死在DELTE FROM Articlexxx的SQL Statement，除了速度緩慢之外，也連帶的影響其他工作效能。最後跟同事試了幾種方式，經由同事的實驗確認了以下方式會取得最佳狀態：

• 建立好複寫機制後，替每個Table做好統計值分析；再確認執行計畫是否有使用Index即可(使用Range Scan而非Full Table Access)。這個步驟有點麻煩；我們的問題是當複寫的時候，DELETE FROM Articlexxx 會因為執行計畫不佳而Hang在哪裏。但每個Article都可能會有這樣的問題，所以我們以Oracle端部署時所用的複寫帳號登入後(假設是repadm)，透過以下SQL Statement準備各Article的DELETE Article SQL Statement：
  
   SELECT 'DELETE FROM ' || TABLE_NAME  ||
         ' WHERE EXISTS ' ||
            (SELECT p.POLL_POLLID ' ||
               'FROM HREPL_POLL p ' || 
             'WHERE CHARTOROWID(l.ROWID) = p.Poll_ROWID ' || 
             '       AND p.Poll_PollID = :Pollid)'
     FROM USER_TABLES
   WHERE TABLE_NAME LIKE 'HREPL_ARTICLE%'
  
  之後，我們先塞點假資料給HREPL_POLL以及各個Article以模擬大量資料(剛開始沒資料，執行計畫就會用Full Table Access的方式規劃)，然後分別開始做統計分析；這時候取得統計值就會接近實際狀況。
  
• 準備好統計資訊後，利用 outline 的方式鎖住上述每一個DELETE Article SQL Statement的執行計畫，避免大量DML後Oracle對該DELETE Statement的執行計畫跑掉。
• 以後每建立一個新的發行項目(Article)，就要執行上述步驟(修改一下SQL Statement取得該新發行項目的Delete Articlexxx SQL Statement)以取得並鎖定最佳的執行計畫。

目前跑了一週，已經沒再發生Hang住的問題，這方法提供參考囉。

Oracle複寫到SQL Server的概略圖

下圖為透過SQL Server複寫機制，將Oracle複寫到SQL Server的架構，概略說明日後補上：

相關在Oracle中建立的Table物件請參考 這裡，流程概念請參考 這裡。

遇見ORA-16014：Recovery空間不足的錯誤

早上一進辦公室就被同事追殺，劈頭就問測試區怎麼進不去，前端SQL開發工具又出現啥勞子「未存檔日誌...」之類的鬼訊息，從他們個個「殷切」關心的眼神來看，壓力很快就填滿整個心臟了。

好了，我試著用sqlplus 登入，並查詢目前$instance的狀態，發現是Mounted的狀態，那還不簡單?直接改成Open就好啦。可是直覺告訴這應該只是假象，事情沒那麼單純。等到一執行 alter database open之後，果不其然就出現了同事口中的鬼訊息了：

SQL> alter database open;

alter database open

*

ERROR 在行 1:

ORA-16014: 未存檔日誌 2 序號 171291, 沒有可用的目的地

ORA-00312: 線上日誌 2 繫線 1:

'D:\ORACLE\PRODUCT\10.2.0\ORADATA\myDatabase\REDO02.LOG'

上網快速查了一下，原來是Recovery的空間不夠了，我開了220G的Recovery還不夠，難道是因為每天凌晨排程的Impdp關係造成的嗎？無時間多想，先在sqlplus將Recovery空間加大到250(alter system set db_recovery_file_dest_size = 250G)，再到RMAN下了ARCHIVELOG ALL DELETE INPUT 騰出空間來，耐心等了10分鐘左右，再回到sqlplus再將資料改成Open狀態後，搞定!

至於Impdp為何會產生大量Archive log的問題，腦袋中閃過一些些殘存的影像(全面啟動?)，等我印證後再來報告。

意外解掉Oracle 10g統計值匯入緩慢的問題

還記得之前遇到的 Oracle 10g 統計值匯入緩慢的問題 嗎? 這個情況在我在測試區上了patch後(10.2.0.4 64bit)，情況已經大幅改善。

原本我是在fund 這邊export出一個 10.2.0.1版本的 dump檔，以適用於當時尚未升級的測試區DB。後來加了統計值，並且匯到64bit windows & 64-bit Oracle 10.2.0.1的環境時，整個作業需要12~13小時。但上禮拜我將這個測試區升級到 10.2.0.4後，意外發現整個作業只需要3小時又40分鍾左右，真的有给它驚訝到。

難道是版本搞的鬼???

真是意外的收穫。

Oracle中一次編譯無效物件之方法

最近工作需要，翻出了用來重新編譯無效物件的 DBMS_UTILITY.compile_schema 套件(package)，參數就只有一個schema name，只要指定好後就可以輕鬆寫意的完成無效物件的編譯。相關的文章則可參考 Recompiling Invalid Schema Objects 。但好巧不巧，前幾天在升級完 Oracle 後，意外的發現到用來更新 Dictionary 的指令檔尾註解中(D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catupgrd.sql)，會強烈的建議我們利用 utlrp.sql 指令檔案將無效的物件再recompile 一次，以避免當使用到無效的物件時，Oracle自動先行編譯而產生的不必要的延遲(latency)。

該指令檔 Utlsp.sql 位於 D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlrp.sql，指令只有短短個幾行，主要就是利用 utl_recomp.recomp_parallel(threads) 自動判斷主機上有幾個CPU後，以平行方式進行compile的作業。很簡單也很好用。如果你已經懶到指定schema name的話，那這個就絕對是你的最佳懶人包。不過，使用前請先用sqlplus / as sysdba的方式登入後再執行。

p.s 後話，在Recompiling Invalid Schema Object 文章末端，也提到了utlsp.sql的用法，而現在我才發現..@@ 。這證明我文章沒讀透啊......

找出 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 指令斷行吧

常會為了輸出某些管理指令而使用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%';

結案~

透過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端也隨時可讀取到最新的設定，樂哉～