Excel 自動報表遇 ora-12516 錯誤排除

在更新公司的營運報表(Excel直接讀取資料庫)時會出現 ORA-12516 TNS:listener could not find available handler with matching protocol stack 的錯誤,但每一個 SQL 指令去分別執行時都很正常,詢問過資料庫維護廠商後給我們的答案是 Oracle Client Driver 異常,這點我覺得不太可能是,應該是出在我在短時間對幾個不同的 DB 下查詢指令,而這些資料庫都是透過 Oracle DB 做 DBLink 連結到大陸的子公司去,造成極短時間負荷過大 (Processes 及 Sessions 被吃光),所以調整一下 Excel 的查詢規則

如果也遇到大量查詢時出現上述的錯誤,可以試著把 “啟用幕後執行更改作業” 的勾取消,這樣不免的會導致 Excel 查詢這些SQL指令同時間只會執行一個,但是可以降低系統的負荷就不會產生這個錯誤.

簡易 Oracle 資料同步問題, 使用 Materialized View

環境

公司使用的 ERP 系統是屬於單一家公司單一個 Schema,在大陸工廠因速度的問題所以把資料庫都建置在當地,所以就變成台灣一個資料庫,大陸兩個工廠各有一個資料庫。

 

需求

部門在前兩年開始自行開發電子簽核系統,初期只針對台灣公司導入及開發,今年度開始嘗試把大陸兩廠納入到支援的範圍,當然程式必須要針對新的公司別(主要是資料庫的緣故)做不同幅度的調整,但是最令人頭痛的是調整後的執行速度。

影響速度的因素有三點,第一是兩岸間的網路速度,當時我們僅利用 Untangle 的 OpenVPN 把兩邊串起來,速度當就沒有辦法保障,第二點是我們為了開發程式(當然還有其他用途)方便,跟大陸資料庫的連結是使用 DB Link 的方式進行,轉一手的速度自然比較慢,最後一點是Web Server 是放置在台灣,所以只要是由大陸連結都要看到由台灣傳過去的畫面,再加上資料的往返,這樣自然快不起來。

這樣導致簽核速度的低落,更慘的是簽核最後一關通常都是高階長官,而簽核的內容都必須回填 ERP 系統上某些欄位,執行速度上最差可以到30-45秒,開始測試時就被罵得半死,在不影響整個程式的邏輯上 (因為整個翻寫成本太大),只能靠資料的功能來加速整個過程。

Continue reading “簡易 Oracle 資料同步問題, 使用 Materialized View”

調整 Oracle Home 順序

因近日公司內自行開發軟體的主機需要軟體升級, 目標是把 Oracle Client 端由 10g 升級到 11g, 同時開發套件也把 Framework 由 2.0 升級到 4.0, 透過 VMWare 的 Clone 機制新產生一個測試平台, 透過軟體升級後偶而出現 Oracle connection 的錯誤訊息, 重新開機或重新啟動 IIS 都可能會恢復正常, 但一下子這個錯誤訊息就會跑出來.

網路上找得到 IIS 500.19 的錯誤訊息都跟這個錯誤無關, 最後只好由最基本的除錯開始研究, 首先利用 Oracle 的 Net Manager 把所有的連線刪除掉重新連結並確認連線正常, 重啟 IIS 執行軟體, 還是遇到同樣的錯誤.

腦筋思考了一下或許是因為在同一台機器上安裝了 10g, 11g, 及 11g ODAC (Oracle Data Access Components) 等三個 Oracle Home 讓系統搞不清楚, 更重要的是 ODCA 最後裝, 但沒有地方可以設定 TNS, 或許問題就出在找不到 TNS 上面吧.
Continue reading “調整 Oracle Home 順序”

測試 Oracle On NetApp (III)

然後我們到 O: 去把安裝資料庫的路徑改名稱 Oratest -> Oratest1 破壞原有的設定, 再把 U:Oratest (由 SnapShot 取出備份) 複製到 O:oratest 去. 這樣做的目的可以把剛剛做的 snapshot 的資料回覆回來, 並且確認資料庫是可以正常的開啟.

開啟時有時候會產生錯誤, 只要執行 Recover Database; 然後把資料庫的開啟狀況 alter database open; 開啟資料庫至可以存取的狀態. 檢查 snapshot 之後建立的 table 存不存在, select * from tabletest; 系統應該是會回覆不存在這個 table.

建議再資料複製完畢後, 立即把 U: (由 SnapShot 製作出來的 Disk 移除連結 (disconnect), 以防止該 snapshot 被佔用掉導致下次的 snapshot 不成功.
Test NetApp Picture

接下來再測試一下輸入剛剛的 Create table tabletest as (select * from all_tables); 建立一個測試的 table, 重複上列測試動作, 確認 snapshot 運作正常且 Oracle 可以正確的回覆, 資料不會丟失.

以上驗證看起來 SnapShot 是可以利用 Script 的設定達到備份的目的, 且只要安裝資料庫的主機是把必要的檔案如 Password file/UDump/DDump 等等的位置弄對, 可以很容易的再遠端把資料回覆.

接下來就是重頭戲, 測試 SnapMirror (複製異動資料到遠端的 NAS 上面去).
第一步就是在目的端設定一個 LUN 大小最少要跟 Source 端一樣大小, 設定部分請參考前述文章.

執行 SnapMirror 來設定兩端備援機制, 記得要連線到目的端主機做設定喔.
Test NetApp Picture

首先先指定 Destination 的 Vol & Qtree (path), 指定 Source 端的 Filer (就是來源端主機), Location 其 LUN 的路徑, Maximum Transfer Rate 可以指定最大可用頻寬, 來限制 NetApp 最大可使用同步的頻寬, 並設定每小時同步一次, 其實這邊在實務上應該設定不自動同步, 因為我們會利用 Script 來驅動 Oracle 做Begin / End Backup 的動作, 由 NetApp 主動去製作同步也沒有什麼意義, 因為有部分的資料還是存放在 Server 的 Memory 內, 沒有 Flush 出來到檔案還是會造成資料的部份 Lost.
Test NetApp Picture

最後一步就是在原有的 Script 加上強制同步的功能 (先在 LUN 上面做 SnapShot, 之後立即把資料同步到另外一台 NetApp主機上), 在 Backup.bat 加入下面這一行:

rsh %rm_ip% -l root:rootpassword snapmirror update -S %na_ip%:vol_Oracle %rm_name%:vol_oracle_DR

這樣在執行 Oracle 的線上備份程序後就立即的觸發 SnapMirror 的動作, 把剛剛備份下來熱騰騰的資料傳到另一台主機.

如果要由遠端主機做資料的回覆, 同樣的需要利用 iSCSI 連接到遠端的 LUN, 接下來同樣的可以選擇 Mount 最後備份的資料或是其中的一份 SnapShot 成為另外一個磁碟機, 把檔案複製到原始的 Oracle Data 路徑, 然後重啟 Oracle Database, 做必要的回覆就可以把資料救回來.

備註: 如果 Datafile 無法救到最新的這一份, 而 Archive Log 還保有比 Datafile 更新的資料, 可以由以下的兩個指令來 Apply archive log file 讓資料回覆到系統資料保存最後的一筆資料.

recover database using backup controlfile until cancel;

alter database open resetlogs;

測試 Oracle On NetApp (II)

完成後顯示下列的總表
Test NetApp Picture

系統立即多出來個 50G 空間.
Test NetApp Picture

接下來應該在使用 Oracle dbca (Database Create assist) 建立一個測試的資料庫, 可以開立一個 DOS box 鍵入 dbca, 依照畫面的輸入,把資料庫建立起來, 安裝完成後進入資料庫確認執行正常喔.

註: 我原先已經在 O: 建立好資料庫了, 所以我把 O: 及 R: 對調並且把建立好的資料庫再複製回到 O: 上面, 所以現在開始資料庫是在放 O:, 且 O: 是存在於剛剛建立好的 iSCSI 上面.

由於是透過 LUN 來做的 iSCSI, NetApp無法提供定期性做 SnapShot (有的話也只是對著那個很大的檔案), 且自動 SnapShot 時也無法保證 Oracle DB 會把所有暫存在記憶體內的資料 Flush 到硬碟內, 所以必須透過幾個 Script file 去驅動 Oracle DB 執行 Log Switch 及 Begin Backup 指令, 把該寫的資料寫出到硬碟, 這樣做 SnapShot 才有意義.

接下來測試由廠商提供的 Scripts (加上自己環境的加工), 在 C: 建立一個 Script 的路徑, 把以下的內容 Key 進去.
Backup.bat
Set ORACLE_SID=ORATEST
SET NLS_LANG=AMERICAN_AMERICA.ZHT16MSWIN950
set na_ip=10.5.1.111
set rm_ip=10.5.1.111
set rm_name=FAS2020

rsh %na_ip% -l root:root123 snap delete -V vol1 old3
rsh %na_ip% -l root:root123 snap rename -V vol1 old2 old3
rsh %na_ip% -l root:root123 snap rename -V vol1 old1 old2
rsh %na_ip% -l root:root123 snap rename -V vol1 new old1
sqlplus /nolog @”C:/scripts/dobegin.sql”
rsh %na_ip% -l root:root123 snap create -V vol1 new
sqlplus /nolog @”C:/scripts/doend.sql”

dobegin.sql
connect sys/orc1@oratest as sysdba
SET FEEDBACK off
SET PAGESIZE 0
SPOOL C:/scripts/begin.sql
SELECT
‘ALTER TABLESPACE ‘ || tablespace_name || ‘ BEGIN BACKUP;’
FROM
dba_tablespaces where contents != ‘TEMPORARY’;
SPOOL off
alter system archive log current;
@C:/scripts/begin.sql
select status from vinstance;
EXIT

DOEND.SQL
connect sys/orc1@oratest as sysdba
SET FEEDBACK off
SET PAGESIZE 0
SPOOL C:/scripts/end.sql
SELECT
‘ALTER TABLESPACE ‘ || tablespace_name || ‘ END BACKUP;’
FROM
dba_tablespaces where contents != ‘TEMPORARY’;
SPOOL off
@C:/scripts/end.sql
EXIT

執行完後就可以在 SnapDrive 上面看到一個新的 SnapShot 叫 New, 每執行一次, 就會把 New 更名成 Old1, Old2, Old3 等等, 看看想要保留最後的幾份, 修改 Backup.bat 就可以了
Test NetApp Picture

要測試 SnapShot 是否完成, 新增幾筆資料到資料庫內, 我利用 Create table test as (select * from all_tables); 新增一個table, 確認資料該 Table 實際有資料後, 關閉資料庫, 把 New 這個 SnapShot 再 Connect 成另外一個 Disk.
選擇 SnapDrive 點選Disk, 移到右方選 Connect Disk
Test NetApp Picture

然後 Wizard 又跑出來, 選擇好 FAS2020主機, 選擇 Vol1 -> .snapshot -> New -> oratest, 可千萬不要選錯了喔..
Test NetApp Picture

注意要看到是 Disk snapshot 才是對的喔.. 點選 Next
Test NetApp Picture

給她一個磁碟代號, Next, 接下的動作跟建立一個新的 Disk 一樣, 一直 Next 到底就好.
Test NetApp Picture

最後還是出現一個彙總的資訊畫面, Next 就完成了.
Test NetApp Picture

電腦就多產生了一個 U: 出來, 其大小就是跟 O: 一樣的, 只差在資料.
Test NetApp Picture