Oracle Dataguard has his own command-line dgmgrl to check the whole dataguard configuration status.
At least you should check that the show configuration command returns SUCCESS.
This is an hypothetic script:
1 2 3 4 5 6 7 8 9 10 11 |
#!/bin/bash export ORACLE_HOME=/u1/app/oracle/product/10.2.0 export ORACLE_SID=orcldg result=`echo "show configuration;" | \ $ORACLE_HOME/bin/dgmgrl sys/strongpasswd | \ grep -A 1 "Current status for" | grep -v "Current status for"` if [ "$result" = "SUCCESS" ] ; then exit 0 else exit 1 fi |
Another script should check for the gap between production online log and the log stream received by the standby database. This can be accomplished with v$managed_standby view.
The Total Block Gap between production and standby can be calculated this way:
Sum all blocks from v$archived_logs where seq# between Current Standby Seq# and Current Production Seq#. Then add current block# of the production LGWR process and subtract current block# from RFS standby process. This gives you total blocks even if there is a log sequence gap between sites.
This is NOT the gap of online log APPLIED to the standby database. THIS IS THE GAP OF ONLINE LOG TRANSMITTED TO THE STANDBY RFS PROCESS and can be used to monitor your dataguard transmission from production to disaster recovery environment.
This is an excerpt of such script (please take care that it does not check against RFS failures, so it can fails when RFS is not alive):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
#!/u1/app/oracle/product/10.2.0/perl/bin/perl -w use DBI; use DBD::Oracle qw(:ora_session_modes); # DB connection # my $prod = "orclprod"; my $stby = "orcldr"; my $prodh; unless ($prodh = DBI->connect('dbi:Oracle:'.$prod, 'sys', 'strongpassword', {PrintError=>0, AutoCommit => 0, ora_session_mode => ORA_SYSDBA})) { print "Error connecting to DB: $DBI::errstr\n"; exit(1); } $prodh->{RaiseError}=1; my $stbyh; unless ($stbyh = DBI->connect('dbi:Oracle:'.$stby, 'sys', 'strongpassword', {PrintError=>0, AutoCommit => 0, ora_session_mode => ORA_SYSDBA})) { print "Error connecting to DB: $DBI::errstr\n"; $prodh->disconnect; exit(1); } $stbyh->{RaiseError}=1; my $sth; ### query prod $sth = $prodh->prepare( < <eosql ); select SEQUENCE#, BLOCK# from v\$managed_standby where process='LGWR' EOSQL $sth->execute(); my ($psequence, $pblock) = $sth->fetchrow_array(); $sth->finish(); ### query stdby $sth = $stbyh->prepare( < </eosql><eosql ); select SEQUENCE#, BLOCK# from v\$managed_standby where process='RFS' and client_process='LGWR' EOSQL $sth->execute(); my ($ssequence, $sblock) = $sth->fetchrow_array(); $sth->finish(); printf ("PROD : %10d %10d\n", $psequence, $pblock); printf ("STANDBY: %10d %10d\n", $ssequence, $sblock); $sth = $stbyh->prepare( < </eosql><eosql ); select nvl(sum(blocks),0) + $pblock - $sblock as BLOCK_GAP from v\$archived_log where sequence# between $ssequence and $psequence EOSQL $sth->execute(); my ($blockgap) = $sth->fetchrow_array(); $sth->finish(); printf ("%-10d blocks gap\n", $blockgap); $stbyh->disconnect; $prodh->disconnect; </eosql> |
Any comment is appreciated!
Latest posts by Ludovico (see all)
- New views in Oracle Data Guard 23c - January 3, 2024
- New in Data Guard 21c and 23c: Automatic preparation of the primary - December 22, 2023
- Does FLASHBACK QUERY work across incarnations or after a Data Guard failover? - December 13, 2023
Ludovico, can you change your first script from hypothetical to an actual working script. I am getting both values back for the if and the else, and I’m not sure where to turn.
That post is VERY old. I’ll try to come up with something newer soon.
Hi Ludovico,
How to identify the server i am working on is configured with Oracle data guard set up ?
Thanks,
Balaji.C
Pingback: Dataguard check script for Real Application Clusters (MAA) « DBA survival BLOG
Hey Admin,
It worked after the change u suggested.
Thanks,
Dev
you’re right, doing cut&paste of my script it seems that all occurrences of:
$sth = $prodh->prepare( <<EOSQL );
have become:
$sth = $prodh->prepare( < <EOSQL );
there is an extra blank between < operators. try to remove it and let me know!
I get following error when i run ur perl script
Unterminated operator at ./dg line 30.
Pingback: DBA survival BLOG » Blog Archive » More about Dataguard and how to check it
I found the following non-critical warning returned by the broker on the first script:
Warning: ORA-16610: command ‘Broker automatic health check’ in progress
Is it safe to ignore this warning and force a SUCCESS message??
[…]
echo $result | grep “Broker automatic health check” >/dev/null
if [ $? -eq 0 ] ; then
result=”SUCCESS”
fi
[…]