sp_whoisactive 版權宣告修改了

這可是一大福音, Adam的版權宣告改為GPL V3,那就表示你的產品可以把它當成API來呼叫做生意了,你當然也可以修改它的程式碼,但照規矩你改了甚麼就是得open給其他人,這是它在github的source,有興趣的人看一下它的版權宣告吧!!

 https://github.com/amachanic/sp_whoisactive

Adam以前的規則是,只要你的產品用到它的code, 賺了多少錢,就得分Adam 一杯羹。

Read More

怎麼由線性推估出資料庫日成長量

難的是收資料....
所謂的資料科學就是得由歷史資料進行推估及預測
買工具? 哈..........

Read More

Oracle 18c Support Unicode 9

這是開給Oracle的Case,這是這幾年來,我對Oracle Support的回應最滿意的一個案件,故事是這樣的,客戶因為碰到難字問題,所以在資料庫升級時,把big5碼轉成了UTF8,結果他們嘗試去把WORD裡頭的一個OPEN BOOK的字符圖案塞進資料庫,但沒辦法完成,所以就開了case給Oracle,Global Support 的回應如下:

Notes Oracle Support- 03-Jul-2019 10:01 (25 mins ago)

#Note 07/03

Dear Customer

Please let me share the local verify result.

I has tested two characters ：

"1F4D6;OPEN BOOK"

UNICODE 1F4D6

UTF-8 (hex) F0 9F 93 96

UTF-16 (hex) D83D DCD6

"1F352; CHERRIES"

UNICODE 1F4D6

UTF-8 (hex) F0 9F 8D 92

UTF-16 (hex) D83C DF52

Insert OPEN BOOK & CHERRIES into table:

insert into tbl_test values (1,unistr('\D83C\DF52'),unistr('\D83D\DCD6'),sysdate);

我按照他給的語法把資料塞進資料庫後,用sql developer去看資料

所以不要想說, 在WORD上透過COPY Paste就可以把UTF8的字符透過insert 語法就可以塞進Oracle的資料庫,沒那麼簡單啦!!

Oracle 18c 支援 Unicode 9,得花時間去研究喔!!

Read More

獻 與 献 兩個字在 Oracle Big5 資料庫的故事

客戶被門市反映取件人名字與包裹上的名字中間那個字有差異,追問AP,然後AP跑來問我....不過以這個單位的態度,通常都會直接回絕,說這是你們AP要處理的,但對我來說,我要的是累積個人資產,所以有問題,我盡量嘗試解決底下是我個人的測試及回覆,我也不知道準確與否

==================================

        ‘献’ 這個字應該是簡體字

         獻  這是繁體字

         在測試之後

         我覺得可能因為資料庫是big5編碼

         它主動把簡體字轉為繁體字
         (我覺得是driver轉的啦)

         用下列的sql 去測試就知道我為何這麼說

select dump('献',1016) from dual;

select dump('獻',1016) from dual;

        回應的編碼都是C46D

        那就是”獻” BIG5的編碼

        同樣的兩個dump sql 語句

        在UTF8 編碼資料庫的顯示不一樣

Typ=96 Len=3 CharacterSet=AL32UTF8: e7,8c,ae

Typ=96 Len=3 CharacterSet=AL32UTF8: e7,8d,bb

         這是我塞資料進UTF8 資料庫查詢的結果

Read More

SQL Server 2008R2 修改 Transaction Replication 架構下的sa 密碼

因資安需求,得定期修改系統最高權限的密碼,每次改到這種架構,就心驚膽顫,怕影響到服務,只好記錄下來,方便以後參考

首先,照正規方式修改login 的密碼

接下來改Replicat的密碼

最後從Replicat Monitory 重建設定

Read More

Oracle GRID 12.2 驗收文件

延伸自 Oracle Grid 12.2 在RedHat 7.x上的安裝 Infra端負責這案子owner詢問，我們有沒有驗收文件?想說要驗收，不就是測試session failover 是否真的不會中斷，他說那是功能驗證，有沒有類似check list之類的東西，我想了一下，就給他下列的指令跑出來的報表

[grid@dbha1 grid]$ ./runcluvfy.sh stage -post crsinst -n dbha1,dbha2

Verifying Node Connectivity ...

  Verifying Hosts File ...PASSED

  Verifying Check that maximum (MTU) size packet goes through subnet ...PASSED

  Verifying subnet mask consistency for subnet "10.199.4.0" ...PASSED

  Verifying subnet mask consistency for subnet "10.199.5.0" ...PASSED

  Verifying subnet mask consistency for subnet "10.161.72.0" ...PASSED

Verifying Node Connectivity ...PASSED

Verifying Multicast check ...PASSED

Verifying ASM filter driver configuration consistency ...PASSED

Verifying Time zone consistency ...PASSED

Verifying Cluster Manager Integrity ...PASSED

Verifying User Mask ...PASSED

Verifying Cluster Integrity ...PASSED

Verifying OCR Integrity ...PASSED

Verifying CRS Integrity ...

  Verifying Clusterware Version Consistency ...PASSED

Verifying CRS Integrity ...PASSED

Verifying Node Application Existence ...PASSED

Verifying Single Client Access Name (SCAN) ...

  Verifying DNS/NIS name service 'db-scan.gid.set' ...

    Verifying Name Service Switch Configuration File Integrity ...PASSED

  Verifying DNS/NIS name service 'db-scan.gid.set' ...PASSED

Verifying Single Client Access Name (SCAN) ...PASSED

Verifying OLR Integrity ...PASSED

Verifying Voting Disk ...PASSED

Verifying ASM Integrity ...

  Verifying Node Connectivity ...

    Verifying Hosts File ...PASSED

    Verifying Check that maximum (MTU) size packet goes through subnet ...PASSED

    Verifying subnet mask consistency for subnet "10.199.4.0" ...PASSED

    Verifying subnet mask consistency for subnet "10.199.5.0" ...PASSED

    Verifying subnet mask consistency for subnet "10.161.72.0" ...PASSED

  Verifying Node Connectivity ...PASSED

Verifying ASM Integrity ...PASSED

Verifying Device Checks for ASM ...

  Verifying Access Control List check ...PASSED

Verifying Device Checks for ASM ...PASSED

Verifying ASM disk group free space ...PASSED

Verifying I/O scheduler ...

  Verifying Package: cvuqdisk-1.0.10-1 ...PASSED

Verifying I/O scheduler ...PASSED

Verifying User Not In Group "root": grid ...PASSED

Verifying Clock Synchronization ...

CTSS is in Observer state. Switching over to clock synchronization checks using NTP

  Verifying Network Time Protocol (NTP) ...

    Verifying '/etc/ntp.conf' ...PASSED

    Verifying Daemon 'ntpd' ...PASSED

    Verifying NTP daemon or service using UDP port 123 ...PASSED

    Verifying NTP daemon is synchronized with at least one external time source ...PASSED

  Verifying Network Time Protocol (NTP) ...PASSED

Verifying Clock Synchronization ...PASSED

Verifying VIP Subnet configuration check ...PASSED

Verifying Network configuration consistency checks ...PASSED

Verifying File system mount options for path GI_HOME ...PASSED

Post-check for cluster services setup was successful.

CVU operation performed:      stage -post crsinst

Date:                         Jul 1, 2019 4:28:37 PM

CVU home:                     /u01/app/12.2.0/grid/

User:                         grid

今早看到部門大姊發出來的mail，是要拿來請款的啦!!給USER看的啦!!

Read More

用Perl 查核AWS SQL Server RDS 的系統LOG 內容

#!/usr/bin/perl
use strict;
use warnings;
use DateTime;
use DateTime::Format::Strptime;
use DateTime::Duration;
use DBI;
use v5.16;

my $interval = shift;
my $dt_low = DateTime->now(time_zone => 'Asia/Taipei') - DateTime::Duration->new( hours => $interval );
say "low limit time : ", $dt_low->strftime( '%Y-%m-%d %H:%M:%S' );
my $low_epoch= $dt_low -> epoch;

my $dbh = DBI->connect("dbi:Sybase:server=yourserver in freetds.conf", 'userlogin', 'password', {PrintError => 0});
die "Unable for connect to server $DBI::errstr"
unless $dbh;
my @log_file;
my $sth;

$sth = $dbh->prepare(" EXEC master.dbo.sp_enumerrorlogs");
if($sth->execute) {
while(my @dat = $sth->fetchrow) {
push @log_file, $dat[0];
}
}

$sth=undef;
my @sorted_log_file = sort { $a <=> $b } @log_file;
foreach my $i (0 .. $#sorted_log_file) {
my $sql = 'EXEC rdsadmin.dbo.rds_read_error_log '. $i . ',1';
$sth = $dbh->prepare( $sql );
if($sth->execute) {
while(my @dat = $sth->fetchrow) {
if ( $dat[2] =~ m/error|failed|Server is listening on/i && $dat[2] !~ m/Attempting to cycle error log|Logging SQL Server messages in file|The error log has been reinitialized|DBCC CHECKDB/i && $dat[1] !~ m/logon/i ) {
my $strp=DateTime::Format::Strptime->new(pattern =>'%B %d %Y %I:%M:%S:%N%P',time_zone => 'Asia/Taipei',);
my $data_dt=$strp->parse_datetime($dat[0]);
if ($low_epoch < $data_dt->epoch ){say @dat;}
}
}
}
}

Read More