More about Dataguard and how to check it

Posted on February 6, 2009 by Ludovico

After my post Quick Oracle Dataguard check script I have some considerations to add:
to check the gap of applied log stream by MRP0 process it’s sufficient to replace this query in the perl script I posted:

 select SEQUENCE#, BLOCK# from v\$managed_standby
        where process='RFS' and client_process='LGWR'

1 2	select SEQUENCE#, BLOCK# from v\$managed_standby where process='RFS' and client_process='LGWR'

with this new one:

 select SEQUENCE#, BLOCK# from v\$managed_standby
        where process='MRP0'

1 2	select SEQUENCE#, BLOCK# from v\$managed_standby where process='MRP0'

To check this you have to meet the following condition: You should have real-time apply enabled (and possibly NODELAY clause specified in your recover statement). Check it with this query:

SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;

1	SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;

It should be “MANAGED REAL TIME APPLY”.
If not using realtime apply your MRP0 process will wait until you have a new archive, so even if you have redo transport mode set to LGWR you’ll wait for standby log completion. Your gap of applied redo stream will be at least one sequence#.

With transport mode set to LGWR and real-time apply the output of the perl script is similar to this one:

# ./checkDataGuard.sh
PROD   :       1230      20631
STANDBY:       1230      20613
18         blocks gap

# ./checkDataGuard.sh

PROD : 1230 20631

STANDBY: 1230 20613

18 blocks gap

The whole gap between your primary and standby database should be LOW.

Clustering the RMAN catalog on a RAC environment

Posted on January 13, 2009 by Ludovico

You have your brand new RAC deployed on a cluster and you want to manage your backups through a recovery catalog.
Suppose you don’t have a dedicate server to host your catalog, perhaps you wouldn’t configure your catalog as a RAC database: so why don’t you use Clusterware to configure your catalog as a single instance in cold failover?

OTN has a very nice whitepaper describing how to protect a single instance database. This can be nicely applied on 10g, 10gR2 or 11g: Using Oracle Clusterware to Protect A Single Instance Oracle Database 11g.

Clusterware is appealing also for traditional cold failover clusters. Licensing allows you to use Clusterware as far as you protect Oracle software or 3rd party software that use Oracle as database backend.

Quick Oracle Dataguard check script

Posted on January 5, 2009 by Ludovico

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:

#!/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

#!/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

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):

#!/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>

#!/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!

Oracle RAC Standard Edition to achieve low cost and high performance

Posted on November 28, 2008 by Ludovico

I finished today to create a new production environment based on 2 Linux serverX86_64 and running Oracle RAC 10gR2. (I know, there is 11g right now, but I’m a conservative!)
Wheeew, I just spent a couple of hours applying all the recommended patches!
We choosed 2 nodes with a maximum of 2 multi-core processors each one so we can license Standard Edition instead of Enterprise Edition. 64bits addressing allow us to allocate many gigabytes of SGA. I’m starting with 5Gb but I think we’ll need more. And a set of 6x300Gb 15krpms disks (it can be expanded with more disks and more shelves).
This configuration keeps low the total cost of ownership but achieves best performance.
Due to disks layout, costs and needed usable storage, we had to configure one huge RAID5 on the SAN with multi-path. I decided anyway to create 2 ASM disk groups (ASM is mandatory for Standard Edition RAC), one for the DB, the second one for the recovery area. With spare disks we should have enough availability and even if it’s a RAID5 I saw good write performances (>150M/s).

Welcome new RAC, I hope we’ll feel good together!

DBA survival BLOG

DBA stuff and Oracle Data Guard

Category Archives: Maximum Availability Architecture

More about Dataguard and how to check it

Clustering the RMAN catalog on a RAC environment

Quick Oracle Dataguard check script

Oracle RAC Standard Edition to achieve low cost and high performance