f Oracle DR 資料庫同步時間差的運算 ~ 迪貝之家

Pages

Oracle DR 資料庫同步時間差的運算

dg broker命令show database在正常狀況你是會看到下面的畫面,紅色框框的部份就是ADG的資料時間差




那為何還要自己寫程式去算出時間差?
因為曾經發生filesystem 爆掉了,dgmgrl 的數據就失真了
六年多前寫10g DR資料庫的監控程式時,我就是抓兩個資料庫的scn來比對
到了18c,雖然它的建置方式多了很多方便,監控上找了不少文件
最後在一本專書上直接告訴你最可靠的方式,還是抓scn來比對
底下是我用Perl 寫的程式碼

因為我有implement AWR FOR ADG,所以借用了相應的DBLINK

use strict;
use DBI;
use DBD::Oracle ;
use warnings;
use v5.16;
my $threshold = shift;
my $dbi_connect_string = "dbi:Oracle:";
my %connect_properties;
$connect_properties{RaiseError} = 1;
$connect_properties{PrintError} = 0;
$connect_properties{AutoCommit} = 1;
$connect_properties{ora_session_mode} = DBD::Oracle::ORA_SYSDBA;
my $db_user="";
my $db_password="";
my $dbh = DBI->connect($dbi_connect_string, $db_user, $db_password,\%connect_properties);
die "Unable for connect to server $DBI::errstr" unless $dbh;
my $sth_1;
my $SCN;
$sth_1 = $dbh->prepare(" select current_scn from v\$database");
if($sth_1->execute) {
$SCN=$sth_1->fetchrow;
}
my $sth_2;
my $DRSCN;
$sth_2 = $dbh->prepare(" select current_scn from v\$database\@testcdb_teststb");
if($sth_2->execute) {
$DRSCN=$sth_2->fetchrow;
}
my $sth_3;
$sth_3 = $dbh->prepare(" select extract( hour from diff) * 3600 + extract ( minute from diff ) *60 from (select scn_to_timestamp($SCN) - scn_to_timestamp($DRSCN) as diff from DUAL)");
if($sth_3->execute) {
say "ORA-" if ( $sth_3->fetchrow > $threshold )
}
$sth_1->finish();
$sth_2->finish();
$sth_3->finish();
$dbh->disconnect();
紅色字的部份是主要的運算邏輯,下午我花了不少時間找Oracle是否有現成的函式,結果是沒有,只好土法煉鋼了