僅變更來源表格中相符欄位的紀錄：UPDATE VS. MERGE INTO

從 B Table 將資料更新到 A Table的方式，大都會這樣寫：

UPDATE A 
   SET A.CODE = (SELECT DISTINCT (CODE)
                     FROM B
                    WHERE A.CODE_1 = B.CODE_1
                      AND A.CODE_2 = B.B_CODE_2)

但有個缺點是如果B與A若有沒有相符的紀錄，即在A表格中不符合 A.A_CODE = B.A_CODE & A.B_CODE = B.B_CODE 條件的紀錄，就會被更新成NULL；為了避免這種情況，我們可以加上NVL()函式跳過不相符的紀錄被更新：

UPDATE A 

   SET A.CODE = NVL((SELECT DISTINCT (CODE)

                     FROM B

                    WHERE A.CODE_1 = B.CODE_1

                      AND A.CODE_2 = B.CODE_2), A.CODE)

Oracle 在 9i 之後的版本新增一個 MERGE 的功能，在語意以及速度方面上更勝一籌：

  MERGE INTO A

  USING  B

  ON (A.CODE_1 = B.CODE_1 AND A.CODE_2 = B.CODE_2)

  WHEN MATCHED THEN

    UPDATE SET A.CODE = B.CODE;

語意上就更接近口語化，程式碼也容易變得簡潔。且不止於此，既然有 MATCHED，也就會有 NOT MATCHED，多增加了分支判斷的功能。假設我們想把不符條件的資料記錄到另外一個Table中，就可這樣寫：

MERGE INTO A

  USING  B

  ON (A.CODE_1 = B.CODE_1 AND A.CODE_2 = B.CODE_2)

  WHEN MATCHED THEN

    UPDATE SET A.CODE = B.CODE;

  WHEN NOT MATCHED THEN

INSERT INTO FAIL_REC VALUES (A.CODE_1, A,CODE_2);

分享囉~

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住的問題，這方法提供參考囉。

Workaround for hanging SQL Statement "DELETE Article" in replication

在處理Oracle複寫到SQL Server的問題之中，最令人難解跟抓狂的，莫過於LogReader在Oracle端執行類似以下的語法時，卻莫名的耗費大量時間，導致複寫機制整個停住：



DELETE FROM HREPL_ARTICLE326LOG_1 l
 WHERE EXISTS (SELECT p.POLL_POLLID
          FROM HREPL_POLL p
         WHERE CHARTOROWID(l.ROWID) = p.Poll_ROWID
           AND p.Poll_PollID = :Pollid)


這個問題目前尚未有答案，只知道一旦這個罕見的情況發生，整個複寫機制形同宣告死亡；如果這種情況在六日發生，禮拜一你來公司才發現的話，最好也檢查一下Oracle的UNDO Tablespace，因為它將產生大量的UNDO資料，很有機會將UNDO Tablespace搞爆；前兩次發生時我只能癡癡的拆掉重建 replication的機制(觀念沿襲於windows不穩定就FORMAT掉 XD)，但今天總算試出一個workaround，解法如下：

1. Kill掉Oracle上的Logread.exe session
2. 找出所有Oracle端的article
3. 刪除所有article的資料(強烈建議用truncate table)
4. 打開複寫監控器觀察

過個一兩分鐘，複寫機制應該就可以復活了。


後記：

好事果然不能說出來，快樂指持續了一個下午就結束，隔幾天的DELETE ARTICLE還是會死掉。想破頭來探究原因，同事提出是否有可能統計值已經失真的問題，並且利用anaylze的方式證明似乎可以讓DELETE ARTICLE變快；那好，死馬當活馬醫，排程所有ARTICLE以及 HREPL_POOL都在適當時間做統計值分析後(改用DBMS_STATS.GATHER_TABLE_STATS處理才不會lock table，而參數ESTIMATE_PERCENT需設定成100強迫上工)，的確可以明顯改善這問題。看來這種大量刪除跟新增的複寫機制，Oracle可要另外加工才能處理得好啊。@@....

Oracle複寫到SQL Server的概略圖

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

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

錯誤：18456，SQL 2005 "myUserAccount" 登入失敗

早上一來同事就大喊無法登入SQL Server測試機，快速檢查了一下，原來是登入機制有設定「強制設定密碼原則」，但因為測試機的關係隨手就把它關掉。再次嘗試登入後還是出現錯誤，這次的錯誤代碼為「18456」。Google了一下，發現 這裡 有解法，但它更具價值的地方在於文章下方的表格，已列出錯誤狀態碼各自代表的錯誤描述，好讓你更快速的判斷造成「18456」的根本錯誤原因。最後把幾個instance的登入者密碼改改就好啦，分享囉~



後記：


改了登入者密碼後，同事過沒多久又開始鬼叫無法alter procedure。找了半天才發現，原來是sp裡面有使用server link，連結到已經有修改過密碼的target。這只要再修改「遠端伺服器\屬性\安全性\」中的遠端登入密碼即可。

Workaround for failure of Orakill

還記得 Orakill也有失靈的時候 提到的，當你用Alter system kill session或者Orakill這兩個大絕都砍不掉死在裡面的session的時候，通常建議只有兩種，不是要你下定決心上patch外，就是要你直接閉著眼睛restart instance，雖然可以解決砍不掉的問題，但downtime時間令人難以忍受。今天恰巧又撞上這個難解之題，但這次手上有了Process Explore，對於Windows平台的Oracle來講可說是一大福音。Process Explore可以找出每個Process含有的Thread清單，這對於我們找出需要砍掉的session是很有幫助的。做法很簡單，首先利用下面SQL找出session的spid(thread handle)：

select spid, osuser, s.program
  from v$process p, v$session s
where p.addr=s.paddr   and s.sid=785    /*785是你session的session id */

然後打開Process Explore，將Process以Tree的方式展開，找到Oracke.exe後點選它，你就可以在下方看見Thread List；每個Thread後面都會有一個數字，那個就是Thread Handle ID，請確實的對它按右鍵(因為它會亂飄，挺好動的)，選擇「Close Handle」，即可大功告成。一下子你就可以看到Oracle Database Control裡的Top Activities清爽多了。

升級你的RMAN CATALOG

今天撞上一個挺有趣的情況。日前我升級了RMAN 專屬的DataBase，但沒有跟著login到RMAN Catalog進行升級。今天一登入後就出現了下列的訊息：

圖1、未升級RMAN CATALOG前，登入RMAN的提示訊息

google了一下才知道原來rman catalog也要在Database升級後跟進才可正確的使用。升級方式很簡單，先以catalog owner的身分登入RMAN後，連打兩次 upgrade catalog即可，畫面參考如下：

圖2、升級RMAN CATALOG

第一次打upgrade catalog 會確認你是CATALOG的OWNER，之後再打一次upgrade catalog即可升級成功。