Microsoft SQL Server Log Shipping
SQL 7就有的技術,簡單的說就是資料庫複本資料同步的技術,而這份複本可以開放讀取的功能以分散營業資料庫的負載,也可純粹當成備援資料庫,不管是在本地還是異地。怎麼達成? 透過SQL Agent自動化排程進行備份、傳檔及復原等作業。
Nagios 資料庫維運自動化
一開始設計這架構時,就排除使用remote agent的想法因為在專業分工的組織下,要求安裝新軟體於既有系統是一件不太可行的方案,既然身為DBA就只能把資料庫instance當作是一個最大的agent 想辦法在資料庫內做到我想做的事情
This is default featured post 4 title
Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.
This is default featured post 5 title
Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.
IMPDP BIG5轉UTF8出現長度不夠錯誤
AP很堅持說它的欄位值只有英數字 所以BIG5轉UTF8的過程 不應該出現長度不夠的錯誤 然後說是不是Oracle Bug DBA其實很歹命 都得想辦法去證明不是資料庫系統出錯 |
底下是我證明的過程,我只挑TEMP 表格 因為另一個表格有好幾億筆資料 我又不是閒著沒事幹 幫AP洗資料 那是最繁瑣及最吃力不討好的工作 |
資料是garbabage或亂碼,難不成是我的問題?
怎麼用Perl DBI
我的目標是open source,是不可能花錢買driver,所以要用Perl DBI,首先就要先安裝FreeTDS 再來就是照著下列步驟安裝Sybase DBD模組
|
Oracle 18c 打 2019 年第三季RU 所碰到的問題
datapatch 打完還會讓PDB鎖死,進入restrict 模式 |
只能允許具dba權限的帳號登入,真的是想把Oracle給掐死,系統是還在建置當中,但如果這是營運系統的話,我大概準備收拾包袱走人了,開了Call給Oracle,答覆如下 |
解法就是修改該段確認物件狀態的語法,重新執行一次datapatch,不過補丁去檢核user物件幹嘛?? |
我去查過打過去年10月份的12.2 GI Patch裡頭,並沒發現datapatch 打完還去檢核user 物件狀態的。坦白說,誰的資料庫真的這麼乾淨,完全沒有invalid 物件,case剛開上去,support 回覆電話就跟我在爭執這一點。講難聽點,是誰允許你Oracle,user 物件狀態為invalid,就能把資料庫給鎖死的,搞清楚那是誰的資產?眼前他們大概也只能修改該sql 做為work around. |
rp_filter 對Oracle RAC Interconnect 的影響
為了redundancy,特意加了一張網卡,結果就發生只能活著一個instance的狀況,我在工作週報上是這麼寫的 | |
"加了網卡之後造成資料庫Instance只能啟動一個的狀況,問題是出在 該網卡的kernel 參數rp_filter未調成2,預設值為1。rp 兩字表示 reverse path,當一張網卡收到封包,作業系統核心的網路模組在1的 模式下會嚴謹地判斷收到的封包是否合理,不合理時,作業系統會把 封包丟掉,因而造成Oracle RAC Instance彼此間要溝通時出現 timeout,導致其中一個instance被踢掉;透過tcpdump查看封 包的來往時,有發現HAIP 不同網段封包的傳遞,原本以為是網路 swtich 上的設定問題,把作業系統相應網路介面的rp_filter值調成2 後,RAC Instance間無法溝通的問題就解決了,第二個instance也就 能順利開啟。2表示loose模式,作業系統核心會去判斷封包來源網段 在系統本身是否存在,1只收自身網卡綁定網段的IP封包。 這個機制是作業系統為了防範網路攻擊所衍生出來的行為。"
IPCLW:[0.3]{-}[WAIT]:UTIL: [1564046613337096] Local Address: 169.254.126.69:33097 Remote Address: 169.254.251.45:49917 [oracle@dbha1 trace]$ grep 169\.254 /u01/app/oracle/diag/rdbms/cdb/cdb1/trace/cdb1_lck0_14232.trc IPCLW:[0.3]{-}[WAIT]:UTIL: [1563946449680871] Local Address: 169.254.187.150:40852 Remote Address: 169.254.119.30:64953 |
Oracle SQLNET.EXPIRE_TIME=10 side effect
一直被complain 連線中斷,也解釋過Oracle 如果沒在user profile設定,是不會主動砍user session,像鴨子聽雷一樣,這個case搞到最後火很大,接下來被要求設上SQLNET.EXPIRE_TIME這個參數,然後斷線的問題就解決了.....那到底會是誰的問題? |
Oracle 官方的解說 : |
A SQLNET.EXPIRE_TIME=10 solution is to make this backend "not so" passive, using the DCD (dead connection detection) to figure out if the communication is still possible. |
Simply, set in the $ORACLE_HOME/network/admin/sqlnet.ora, in the server side SQLNET.EXPIRE_TIME=10 (10 minutes, for example). With this parameter in place, after 10 minutes of inactivity, the server send a small 10 bytes probe packet to the client. If this packet is not acknowledge, the connection will be closed and the associated resources will be released. |
There are two benefits with this DCD |
1. If the SQLNET.EXPIRE_TIME is less than the FW connection idle time-out, then the firewall *may* consider this packet as activity, and the idle time-out (firewall blackout) will never happen until both the client and the server processes are alive. |
2. If the SQLNET.EXPIRE_TIME (let's say a little bit higher) than the FW idle limit, then , as soon as the blackout happens , the RDBMS will know and will close the connection. |
The first case is recommended when the connection comes from another application server , and the second makes sense for client applications. |
DCD works at the application level and also works on top of TCP/IP protocol. If you have set the SQLNET.EXPIRE_TIME=10 then do not expect that the connections will be flagged as dead exactly after 10 minutes of the blackout or network outage. Please see Note:151972.1 "Dead Connection Detection (DCD) Explained" for details on DCD. The TCP timeout and TCP retransmission values also adds to this time. |
PLEASE NOTE: |
DCD was never designed to be used as a "virtual traffic generator" as we are wanting to use it for here. This is merely a useful side-effect of the feature. |
In fact, some later firewalls and updated firewall firmware may not see DCD packets as a valid traffic possibly because the packets that DCD sends are actually empty packets. Therefore, DCD may not work as expected and the firewall / switch may still terminate TCP sockets that are idle for the period monitored, even when DCD is enabled and working. |
In such cases, the firewall timeout should be increased or users should not leave the application idle for longer than the idle time out configured on the firewall. |
今天是2019/12/23,user又跟我反應了相同的狀況,因為昨天凌晨才又從士林搬了一套10g的資料庫到板橋。查了一下10g相關的文件,原來10g就有這個參數了。 |
順便去查了一下Internet其他人的case,如果你的連線是重建的,參數設上sqlnet.ora之後,不需要重開任何的服務。 |