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是否有現成的函式,結果是沒有,只好土法煉鋼了

Posted in: Oracle ADG APPLY LAG