f 八月 2020 ~ 迪貝之家

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.

SQL Server AlwaysOn 出現 "本機複本正在準備轉換成主要角色" 錯誤

 

debug always on 最簡便的方式
就是查看它的extended event
因為直接看系統記錄
實在是有夠亂的

Always On LeaseExpired



再到google查LeaseExpired
你就會找到一篇微軟的文件
Mechanics and guidelines of lease, cluster, and health check timeouts for Always On availability groups
這是造成expire 的主要內容
The lease is primarily a synchronization mechanism between the primary instance and the cluster, but it can also create failure conditions where there was otherwise no need to fail over. For example, high CPU, out-of-memory conditions (low virtual memory, process paging), SQL process not responding while generating a memory dump, system not responding, cluster (WSFC) going offline (e.g due to quorum loss) can prevent lease renewal from the SQL instance and causing a restart or failover.
看到它的描述後
我就去查看whoisactive的歷史資料
00:17 本來就有一個sql agent job在跑
看它的wait info
很多都是CXCONSUMER
那都是parallel 運作
很耗CPU
跑到凌晨3點都還沒結束
在02:54分的時候
又跑了一個掃含有2億8千萬筆資料的分割表
挖哩~~
看到這裡...我就想說難怪了~~
可是因為專業分工
我又看不到作業系統的效能歷史數據
只好Pass給主機單位確認系統效能使用
負責人給了肯定的答覆
好吧~~~ 那就是這樣啦~~
但就系統的記錄來看,如下

本機複本正在準備轉換成主要角色

當系統繁忙到沒有respond給Always On監控機制的狀況時
還不至於會造成要進行AG切換的動作啦~~
因為看起來就是強行把SESSION 砍掉
就地重帶Primary Replica
哪來的切換來切換去

Secondary Replica

用Perl來算每周四的日期

 

MySQL Cookbook Page 223[251]
root@tbtools:~# cat test_date_shift.pl
#!/usr/bin/perl
# connect.pl: connect to the MySQL server

use strict;
use warnings;
use DBI;

my $dsn = "DBI:mysql:host=192.168.56.7;database=cookbook";
#@ _CONNECT_
my $dbh = DBI->connect ($dsn, "cbuser", "cbpass")
            or die "Cannot connect to server\n";
#@ _CONNECT_
print "Connected\n";
my $year = 2020;
print "MM/CCYY 2nd Thursday 4th Thursday\n";
foreach my $month (1..12)
{
my $first = sprintf ("%04d-%02d-01", $year, $month);
my ($thu2, $thu4) = $dbh->selectrow_array (qq{
SELECT
DATE_ADD(
DATE_ADD(?,INTERVAL 5-DAYOFWEEK(?) DAY),
INTERVAL IF(DAYOFWEEK(?) <= 5, 7, 14) DAY),
DATE_ADD(
DATE_ADD(?,INTERVAL 5-DAYOFWEEK(?) DAY),
INTERVAL IF(DAYOFWEEK(?) <= 5, 21, 28) DAY)
}, undef, $first, $first, $first, $first, $first, $first);
printf "%02d/%04d %s %s\n", $month, $year, $thu2, $thu4;
}

$dbh->disconnect ();
print "Disconnected\n";




設定Replication時,出現"無法設定為發行及散發"

 

這個錯誤找到的原因會讓我吐血

SQL Server Replication Distributor













ㄝ...這是sql server 2019 標準版ㄝ.....
後來我更新SSMS到最新版18.6
就解決了
我原先是安裝13.0.16106.4
因為3年前一個SQL Server 2016的升級專案
用了太新的SSMS
AP說他們的SSIS程式跑起來有問題
直接指定這個版
這次升級又是同一個AP單位
好吧~~~
想說....也是一樣的舊版本
都是由2008R2做升級
就用這個版吧~~
現在系統上就安裝了2套SSMS
哈....真是.....

用sql 計算年齡

 

研究MySQL 專書MySQL Cookbook
有一段內容提到了這個問題
我原本想說,這有什麼好談的~~
阿不就兩個時間的相減取出年份而已
其實不然,這是該段討論所描述的
Age determination is a type of date-interval calculation. However, you cannot simply
compute a difference in days and divide by 365 because leap years throw off the calculation.
(It is 365 days from 1995-03-01 to 1996-02-29, but that is not a year in age terms.)
Dividing by 365.25 is slightly more accurate, but still not correct for all dates.
To calculate ages, use the TIMESTAMPDIFF() function. Pass it a birth date, a current date,
and the unit in which you want the age expressed:
TIMESTAMPDIFF(unit,birth,current)
TIMESTAMPDIFF() handles the calculations necessary to adjust for differing month and
year lengths and relative positions of the dates within the calendar year.
得考量到閏年......
我上網查了有關Postgres MSSQL 及Oracle 的相關語法
才發覺......難怪MySQL這麼多人在用
尤其是Internet上的web 網站
因為任何一個帳號都是會員資料
坦白說,科技來自於人性
我以2000-02-29這個日期來列舉這四個資料庫的語法

MySQL :
select TIMESTAMPDIFF(YEAR,'2000-02-29',curdate());

Postgres :
select date_part('year',age(current_date,'2000-02-29'));

MSSQL:
select DATEDIFF(YY,'2000-02-29',getdate()) -
CASE
dateadd(YY,DATEDIFF(YY,'2000-02-29',getdate()),'2000-02-29') > getdate() then 1
Oracle : 我查了半天,甚至到metalink去查過
甲骨文可能認為這是開發人員要自己解決的
SELECT TRUNC(TO_NUMBER( TO_DATE('29-02-2020','DD-MM-YYYY') - TO_DATE('29-02-2000','DD-MM-YYYY')) / 365.25) AS AGE FROM DUAL;

商用資料庫還真沒opensource 資料庫來得好用,就這一點來看的話
但上述語法,準確度如何?? 嘿嘿嘿....I am not sure!!!
我沒實際implement的經驗
所以還是得看各位在business logic 的實際需求
我想老半天,大概就是退休年金相關軟體在年齡計算上有最大的concern
參考資料 :
Posgres Date/Time Functions and Operators
How to Calculate Age in SQL Server
####2020/09/25########
這個問題,今天在看處理validate date的部分時
仔細地想了一下
好像被書的內容搞得有點太複雜了
我個人認為是碰到2/29出生的人
如果非閏年要滿足歲
就是要跳到3/1 才算吧~~
以今年2020來看
是閏年,2/29後就滿足歲
明年2021年就是要3/1 後才滿歲
問題就在於判斷閏年的邏輯
這是書上perl 的邏輯
sub is_leap_year
{
my $year = $_[0];
return ($year % 4 == 0) && ((($year % 100) != 0) || ($year % 400) == 0);
}


sub days_in_month
{
my ($year, $month) = @_;
my @day_tbl = (31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31);
my $days = $day_tbl[$month-1];
# add a day for Feb of leap years
$days++ if $month == 2 && is_leap_year ($year);
return $days;
}

WHY VACUUM WON’T REMOVE DEAD ROWS

 

這是一個找table bloat原因的case
哈哈...如果你想知道來龍去脈
事件的起源如這篇Po文 :
Tuning Example IV: EDB(Postgres) DB 主機 CPU 滿載
不想知道的話
我直接告訴你原因
因為MVCC的設計
有session正參照到已經被delete的資料
因此vacuum不管跑幾次
表格就是一直長大
大到了影響效能
這是Vacuum執行所出現的關鍵字串
DETAIL: 42427 dead row versions cannot be removed yet.
這是我覺得可能造成dead row無法移除的session

dead row not removed
當然也得感謝某人在今天不斷地追問
我也只好鍥而不捨地把真正地原因找出來
不然我也只是看到前人用drop table來解決
而且還得把AP停掉...
前兩天我聽到的時候
還在納悶...為什麼要停AP
哈..........................
參考資料如下 :
THREE REASONS WHY VACUUM WON’T REMOVE DEAD ROWS FROM A TABLE
VACUUM VERBOSE outputs, nonremovable “dead row versions cannot be removed yet”?

Oracle RAC 不是OLTP 的萬靈丹

 

AP說起他們的程式發生timeout
問我能不能看一下資料庫端的狀況
只要你不是一付都是你啦~~ 你資料庫的問題
我當然是責無旁貸會去確認資料庫的狀況
這是一套Oracle RAC資料庫
會跑這東西的,當然都嘛是非同小可
能說不行嗎 ??
首先當是去看了一下ash 報表
因為AP就給了20分鐘的時間區間
(如果你熟Oracle,就會知道為何我先看ASH)

row lock contention

恩.....發生row lock contention
好...我再來看addm報表

ADDM

我再去調1nbv5wx5nx0g5它的執行數據
AWR SQL Report

instance 1 等了 84秒
instance 2 等了 52秒(inst 2的圖我就不貼了)
我初看就覺得納悶,怎麼會insert 造成lock
去metalink找到了Doc ID 873243.1
Troubleshooting 'enq: TX - index contention' Waits
它描述了在RAC環境下,大量insert會有什麼影響
原文如下 :
When running an OLTP systems, it is possible to see high TX enqueue contention on index associated with tables, which
are having high concurrency from the application. This usually happens when the application performs lot of INSERTs and
DELETEs concurrently. For RAC system, the concurrent INSERTs and DELETEs could happen from all the instances .
The reason for this is the index block splits while inserting a new row into the index. The transactions will have to wait for
TX lock in mode 4, until the session that is doing the block splits completes the operations.
A session will initiate a index block split, when it can't find space in an index block where it needs to insert a new row.
Before starting the split, it would clean out all the keys in the block to check whether there is enough sufficient space in
the block.deleted
Splitter has to do the following activities:
o Allocate a new block.
o Copy a percentage of rows to the new buffer.
o Add the new buffer to the index structure and commit the operation.
In RAC environments, this could be an expensive operation, due to the global cache operations included. The impact will
be more if the split is happening at a branch or root block level.
可是我的狀況是row lock contention
但依照這篇文件的指示
我再去看它所要求的AWR報表內
Segments by Row Lock Waits
的數據
我就恍然大悟
就是你了.....
它就是該表的索引
Segments by Row Lock Waits

文件裏頭所提出的最簡單的解法
就是改成reverse index
為了把東西賣出去
原廠都嘛跟你講不用改程式
對阿..用了你就知道.....
對...不用改...那才有鬼
不過...為了使用一個平台
你本來就該follow那個平台的coding規矩
不然夠本事,就不要用阿,自己寫一套囉~~
我最討厭那一種Programmer
因為framework,sql 語句是它自己產生的
104.................
那你用這個framework幹嘛哩~~
####2020/10/08#####
原來split page 的動作這麼大
這是sql server 2012 專書裏頭的描述
After SQL Server finds the new page, the original page must be split; half the rows (the first half based on the slot array on the page) are left on the original page, and the other half are moved to the new page, or as close to a 50/50 split as possible.
這還只是單一page split
當你大量匯入或者異動資料時
就是帶出一串粽子
難怪會有waiting

要怎麼查who and when 去縮小了sql server交易記錄檔

 

系統的default trace就有記錄了
Using the Default Trace in SQL Server
可以看上述善心人士寫的文章
我節錄重點
1.trace file 的位置

SELECT traceid, value FROM [fn_trace_getinfo](NULL)
WHERE [property] = 2;

default trace

2.查看trace file的內容

SELECT *
FROM [fn_trace_gettable]('F:\MSSQL12.MSSQLSERVER\MSSQL\Log\log_230.trc', DEFAULT)
where StartTime between '2020-08-17 04:00:00' and '2020-08-17 06:00:00'
and textdata like '%shrink%'
order by starttime desc ;

default trace




怎麼在windows 平台只安裝psql

 

為什麼會有這個需求
是因為enterprisedb打包出來的安裝
包含了db engine一大堆的元件
我們幹DBA的
哪有一定要圖形化介面
他打了一整包
也是為了把PgAdmin包進去
版本4有夠難用的
一個剛成立的專案
又是用serverless postgres aurora
AP Server 跑在AWS WINOWS EC2上
如果用的是Linux
我就不用這麼麻煩了~~
想說盡量能精簡就精簡
(我只是要喝一杯牛奶
不需要養一頭牛)
就只好上網找看看
果然....
絕對不會是只有我有這種想法
stackoverflow 有善心人士分享了這篇文
How do I install just the client tools for PostgreSQL on Windows?
研究了之後,我講我的作法
1.在我自己的NB WIN 10上下載 postgres client zip檔
注意...不是安裝檔喔
postgresql-binaries
2.解開來之後,到bin下,拷貝下列檔案到另一個目錄
至於msvcr120.dll
我的電腦就有了
搜尋了一下
就在C:\Windows\system32 裏頭
我就從那邊Co過來的
再把整個目錄送上EC2就OK了
如果你不能用
那就再額外安裝Visual C++ Redistributable
因為我事先有通知作業系統負責人安裝它
所以我也不清楚參照的是哪一份library
(下次還有專案,我來驗證看看)
這是我弄好aurora的環境後
截圖給AP的存證

list postgres db schema
其實我還是有UI的需求
因為整理資料真的比較方便
但我又不希望有安裝這個動作
所以建議使用一個Portable 的小軟體
下載解開即可使用
Postbird-0.8.4-win

Postgres rsync 備份失敗的除錯

 

因為這是從8.x的版本升級到9.2
那時候還沒有pg_basebackup
只有用rsync來進行差異備份
所以就沿用到了現在
出現error
只好進行debug囉~~
+ rsync -av --delete --exclude=/scmdb/Initial/pg_xlog --exclude=/scmdb/Initial/dbserver_logs /scmdb/Initial/ /BACKUP/main_sync
+ exitIfFail
+ '[' 24 -ne 0 ']' - 因為shell return code $? 不是0, 所以被認為rsync有錯誤,把備份程式該跑的流程給中斷掉了
+ '[' started ']'
+ psql -ec 'SELECT pg_stop_backup();'
SELECT pg_stop_backup();
pg_stop_backup
----------------
A992/46718F80
(1 row)
+ exit 0 <- 退出shell
去調看rsync的man page 裡頭對exit code 24的描述,底下是相應的man page 擷圖

rsync exit code

再對照rsync 的錯誤記錄檔最後一行的內容
rsync warning: some files vanished before they could be transferred (code 24) at main.c(892) [sender=2.6.8]
表示rsync在進行中,有檔案不見了
再去找記錄檔的內容,發現如下
file has vanished: "/scmdb/Initial/data/pg_xlog/000000010000A99200000026"
file has vanished: "/scmdb/Initial/data/pg_xlog/000000010000A99200000027"
file has vanished: "/scmdb/Initial/data/pg_xlog/000000010000A99200000028"
file has vanished: "/scmdb/Initial/data/pg_xlog/000000010000A99200000029"
file has vanished: "/scmdb/Initial/data/pg_xlog/000000010000A9920000002A"
file has vanished: "/scmdb/Initial/data/pg_xlog/000000010000A9920000002B"
file has vanished: "/scmdb/Initial/data/pg_xlog/000000010000A9920000002C"
file has vanished: "/scmdb/Initial/data/pg_xlog/000000010000A9920000002D"
這些檔案是Postgres的online交易記錄檔
Checkpoint等作業完成後,系統會自行清掉或者recycle使用,用以釋放空間
正常來說, rsync 寫script的時候,應該會把該目錄排除掉
因此對照了這封mail第一行rsync 指令 exclude 的開關
--exclude=/scmdb/Initial/pg_xlog
路徑中少了data
就是這樣

####2020/08/25#############
嘿....又接到備份失敗
查了一下rsync的記錄
file has vanished: "/scmdb/Initial/data/pg_xlog/000000010000AADE000000C7"
file has vanished: "/scmdb/Initial/data/pg_xlog/000000010000AADE000000C8"
file has vanished: "/scmdb/Initial/data/pg_xlog/000000010000AADE000000C9"
又是它 ???
奇怪了~~ 我上次不是修改了exclude的參數嗎 ?
只好再去問谷大哥啦~~
原來rsync --exclude 只認relative path
哈....還真是....
請看....
rsync exclude
這是人家的Po文
還是尊重一下人家敲文的心血
How To Use Rsync To Exclude Files And Directories In Data Transfer

怎麼列出Postgres 10 各Partition 的定義

 

我們碰到的問題
一定有人也碰過
所以找谷大哥問了一下
誰叫他是全世界最大的資料庫
How to identify the ranges over which a postgres table was partitioned?
其實主要癥結點在於使用 pg_get_expr這個function
原來partition的定義寫到metadata裡頭只有Postgres自己看得懂
要撈出來的話,得透過這個function來解譯成當初給的語法
select pt.relname as partition_name,
pg_get_expr(pt.relpartbound, pt.oid, true) as partition_expression
from pg_class base_tb , pg_inherits i ,pg_class pt
where i.inhparent = base_tb.oid
and pt.oid = i.inhrelid
and base_tb.oid = 'mytest.settlement_detail'::regclass;
我跑的結果如下 :
mytestdb=# select pt.relname as partition_name,
mytestdb-# pg_get_expr(pt.relpartbound, pt.oid, true) as partition_expression
mytestdb-# from pg_class base_tb , pg_inherits i ,pg_class pt
mytestdb-# where i.inhparent = base_tb.oid
mytestdb-# and pt.oid = i.inhrelid
mytestdb-# and base_tb.oid = 'mytest.settlement_detail'::regclass;
partition_name | partition_expression
-----------------------------------+----------------------------------------------
settlement_detail_201809 | FOR VALUES FROM ('20180901') TO ('20181001')
settlement_detail_201810 | FOR VALUES FROM ('20181001') TO ('20181101')
settlement_detail_201811 | FOR VALUES FROM ('20181101') TO ('20181201')
settlement_detail_201812 | FOR VALUES FROM ('20181201') TO ('20190101')
settlement_detail_201901 | FOR VALUES FROM ('20190101') TO ('20190201')
settlement_detail_201902 | FOR VALUES FROM ('20190201') TO ('20190301')
settlement_detail_201903 | FOR VALUES FROM ('20190301') TO ('20190401')
settlement_detail_201904 | FOR VALUES FROM ('20190401') TO ('20190501')
settlement_detail_201905 | FOR VALUES FROM ('20190501') TO ('20190601')
settlement_detail_201906 | FOR VALUES FROM ('20190601') TO ('20190701')
settlement_detail_201907 | FOR VALUES FROM ('20190701') TO ('20190801')
settlement_detail_201908 | FOR VALUES FROM ('20190801') TO ('20190901')
settlement_detail_201909 | FOR VALUES FROM ('20190901') TO ('20191001')
settlement_detail_201910 | FOR VALUES FROM ('20191001') TO ('20191101')
settlement_detail_201911 | FOR VALUES FROM ('20191101') TO ('20191201')
settlement_detail_201912 | FOR VALUES FROM ('20191201') TO ('20200101')
settlement_detail_202001 | FOR VALUES FROM ('20200101') TO ('20200201')
settlement_detail_202002 | FOR VALUES FROM ('20200201') TO ('20200301')
settlement_detail_202003 | FOR VALUES FROM ('20200301') TO ('20200401')
settlement_detail_202004 | FOR VALUES FROM ('20200401') TO ('20200501')

SQL Server升級移轉LOGIN帳號


以往資料庫平移(不升級)
我還可以透過restore master資料庫來處理
突然之間跟我說要升級
真是佩服這些人變臉之快速
只好找看看有沒有方式可以在不同版本間移轉
不會掉密碼的
因為透過SSMS反組譯產出的create login sql 語法
所產出的密碼一定在新環境無法登入
Internet上有這麼一篇
How to migrate the logins of a database to a different server
看了看,我大概能用的就是第四種
用我自己的帳號steven簡單地測試了一下
到2019標準版的環境進行測試,是OK的
只是這次的環境是要跨domain
也不知道是否會造成ophan
不過就算發生,也是有相應的解法啦~
轉出的store procedure是微軟寫的程式
source code在底下這篇文章裡
如何在 SQL Server 的執行個體之間傳送登入和密碼

在master 資料庫會建出sp_hexadecimal 和 sp_help_revlogin兩個程序。
執行exec sp_help_revlogin後
會轉出所有login相應的的t-sql 語法
包含系統帳號,你當然得自行判斷,那些要,哪些是不要的
這是我帳號的反組譯的sql 語法
-- Login: steven
CREATE LOGIN [steven] WITH PASSWORD = 0x0100410A50AF92061671CFDA133C1D62234DAC9AFD60A302B18A HASHED, SID = 0x1CDB2D910E20C347A8626179D63A5934, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF


ssh passwordless 在HA切換下要執行的兩行指令

ssh-keygen -f "/var/lib/nagios/.ssh/known_hosts" -R ur_cluster_ip(x.x.x.x)

# Host x.x.x.xfound: line 32
/var/lib/nagios/.ssh/known_hosts updated.
Original contents retained as /var/lib/nagios/.ssh/known_hosts.old
nagios@dbamgr02:~$ ssh-copy-id oracle@x.x.x.x
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/var/lib/nagios/.ssh/id_rsa.pub"
The authenticity of host 'x.x.x.x (x.x.x.x)' can't be established.
ECDSA key fingerprint is SHA256:KfrSx0EeciroLh9z56vOCBEk4xXe+4SCvE9qUO4rzDs.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: WARNING: All keys were skipped because they already exist on the remote system.
(if you think this is a mistake, you may want to use -f option)

Tuning Example V: 又見SQL Server 主機 CPU 滿載.....的案例

AP為了方便傳遞資料給呈現層
超喜歡用Join語法
再加上subquery
那就是雪上加霜
這個例子就是
from 裏頭全都是subqery
你說資料庫optimizer怎麼幫你優化
optimization都是依據數據
沒有實體化,哪來的statistics
這種程式也能上線??
然後再來跟我說
他們也不知道怎麼做調整
媽的...那你不要領薪水阿~~
SA 及 SD都是躺著幹喔......
我看不下去了,就給了我的意見
tuning solution
這是發生問題的SQL 語句
你會讓你的PG寫這種程式嗎?
用紅色框起來的,就是三個子查詢
issued sql statements
我仔細看了當下的執行計畫
索引全跑錯了
再寫subquery來進行join嘛~~