f 七月 2019 ~ 迪貝之家

Microsoft SQL Server Log Shipping

SQL 7就有的技術,簡單的說就是資料庫複本資料同步的技術,而這份複本可以開放讀取的功能以分散營業資料庫的負載,也可純粹當成備援資料庫,不管是在本地還是異地。怎麼達成? 透過SQL Agent自動化排程進行備份、傳檔及復原等作業。

Oracle GolGateden

機房搬遷跨Site能即時同步資料庫利器

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.

sp_whoisactive 版權宣告修改了

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

 https://github.com/amachanic/sp_whoisactive

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

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

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

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去看資料
1F4D6


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


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

獻 與 献 兩個字在 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 資料庫查詢的結果
Big5 vs GB0


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

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

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











接下來改Replicat的密碼





















最後從Replicat Monitory 重建設定

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看的啦!!

用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;}
}
}
}
}