OGG Replicat Big5 轉UTF8 錯誤

AP說他沒方向
要我至少找到一或兩筆資料給他debug
這是OGG的錯誤

透過Logdump找到該筆資料
最下面的紅色框框是該筆rowid

我們來查看該筆資料
又見亂碼

Read More

IMPDP BIG5轉UTF8出現長度不夠錯誤

AP很堅持說它的欄位值只有英數字 所以BIG5轉UTF8的過程 不應該出現長度不夠的錯誤 然後說是不是Oracle Bug DBA其實很歹命 都得想辦法去證明不是資料庫系統出錯

底下是我證明的過程,我只挑TEMP 表格 因為另一個表格有好幾億筆資料 我又不是閒著沒事幹 幫AP洗資料 那是最繁瑣及最吃力不討好的工作

資料是garbabage或亂碼,難不成是我的問題?

Read More

怎麼用Perl DBI

我的目標是open source,是不可能花錢買driver,所以要用Perl DBI,首先就要先安裝FreeTDS 再來就是照著下列步驟安裝Sybase DBD模組 得先安裝dbi套件 apt-get install libdbi-perl $ cd DBD-Sybase-1.16 #這一行很重要  $ export SYBASE=/usr/local/freetds $ perl Makefile.PL $ make $ make install There will be some output about missing libraries after perl Makefile.PL. These are normal. NOTE: here again make test is likely to fail. Writing your own simple test program is highly recommended! Example  Connect to a server with DBD::Sybase #!/usr/local/bin/perl # use DBI; my $dbh = DBI->connect("dbi:Sybase:server=JDBC", 'guest', 'sybase', {PrintError => 0}); die "Unable for connect to server $DBI::errstr" unless $dbh; my $rc; my $sth; $sth = $dbh->prepare("select \@\@servername"); if($sth->execute) { while(@dat = $sth->fetchrow) { print "@dat\n"; } }

Read More

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.

Read More

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封包。 這個機制是作業系統為了防範網路攻擊所衍生出來的行為。" 其實case一開始我就去看了trace file,只是建置之前就找了原廠來consult,說是HAIP 不同網段間不用通,就只能用其他的工具來交叉比對了 [grid@dbha1 ~]$ grep  169\.254 /u01/app/oracle/diag/rdbms/cdb/cdb1/trace/cdb1_lck0_20207.trc 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

Read More

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之後，不需要重開任何的服務。

Read More