Split mirror of HADR Standby Database

[Mark A]

I would like to make a split mirror copy of a HADR standby database. Please
note that I do not want to create a standby with a split mirror of the
primary, but I want to make a split mirror of the standby (for a reporting
database).

I know that I cannot do a write suspend on the standby because it does not
allow any connections. But what if I just did a split mirror of the standby,
do a db2inidb, then ship the logs over to the split mirror version and do a
crash recovery (or maybe rollforward to end of logs). Will DB2 come up in a
consistent state (no corrupted pages)? I don't care if any UOW's get backed
out to a point before the split mirror.

[Steve Pearson (news only)]

In order to get a clean split, you'd want to deactivate the standby
first. With that minor tweak, it should work, as long as you are
careful to split/copy absolutely all storage related to the database
(tablespaces, db dir, logs, procedure libraries, etc and so forth).

Regards,
- Steve P.
--
Steve Pearson, DB2 for Linux, UNIX, and Windows, IBM Software Group
"Portland" Development Team, IBM Beaverton Lab, Beaverton, OR, USA

[Mark A]

Post by Steve Pearson (news only)
In order to get a clean split, you'd want to deactivate the standby
first. With that minor tweak, it should work, as long as you are
careful to split/copy absolutely all storage related to the database
(tablespaces, db dir, logs, procedure libraries, etc and so forth).
Regards,
- Steve P.
--
Steve Pearson, DB2 for Linux, UNIX, and Windows, IBM Software Group
"Portland" Development Team, IBM Beaverton Lab, Beaverton, OR, USA

Thanks Steve.

Do I have to do an initdb on the new copy? If so, what if I don't include
the logs in the split mirror and then just rsynch (copy) the logs over to
the new copy before I do the initdb. Also, if I only have V8.2+ SQL stored
procedures, do I have to worry about the procedure libs?

I will give this a try as soon as I get my storage people to create the
mirror.

[Steve Pearson (news only)]

Post by Mark A
Do I have to do an initdb on the new copy?

That's correct, something like:
db2inidb <db> as standby

Post by Mark A
If so, what if I don't include
the logs in the split mirror and then just rsynch (copy) the logs over to
the new copy before I do the initdb.

I can't think of any reason that wouldn't work.

Post by Mark A
Also, if I only have V8.2+ SQL stored
procedures, do I have to worry about the procedure libs?

I'm not an expert on stored procs, but as I understand it SQL stored
procedures may not have separate library files to worry about.

Regards,
- Steve P.
--
Steve Pearson, DB2 for Linux, UNIX, and Windows, IBM Software Group
"Portland" Development Team, IBM Beaverton Lab, Beaverton, OR, USA

[Dale McInnis]

Post by Mark A

Post by Steve Pearson (news only)
In order to get a clean split, you'd want to deactivate the standby
first. With that minor tweak, it should work, as long as you are
careful to split/copy absolutely all storage related to the database
(tablespaces, db dir, logs, procedure libraries, etc and so forth).
Regards,
- Steve P.
--
Steve Pearson, DB2 for Linux, UNIX, and Windows, IBM Software Group
"Portland" Development Team, IBM Beaverton Lab, Beaverton, OR, USA

Thanks Steve.
Do I have to do an initdb on the new copy? If so, what if I don't include
the logs in the split mirror and then just rsynch (copy) the logs over to
the new copy before I do the initdb. Also, if I only have V8.2+ SQL stored
procedures, do I have to worry about the procedure libs?
I will give this a try as soon as I get my storage people to create the
mirror.

Mark
check out the following white paper which describes how to make a copy
of the HADR standby
www.netapp.com/library/tr/3494.pdf
Since the set write suspend was not issued on the standby you do not
need to run db2inidb.

Dale McInnis

[Mark A]

Post by Dale McInnis
Mark
check out the following white paper which describes how to make a copy
of the HADR standby
www.netapp.com/library/tr/3494.pdf
Since the set write suspend was not issued on the standby you do not
need to run db2inidb.
Dale McInnis

According to the white paper, they suggest that a write suspend is done on
the primary to make a clone of the standby:

From page 27:

"One it has been confirmed that the HADR pair are in sync then the
transmission of the log files to the standby must be suspended to ensure
there is no physical I/O in progress while the Snapshot[of standby] is being
created."

"The most efficient way to suspend the physical I/O on the standby is to
issue a set write suspend on the primary system,"

The whole point of us cloning the standby is to avoid problems we have had
with write suspend on the primary (it hung with latches in inconsistent
state). I cannot afford any outage on my primary, and also I would rather
have my reporting database on my standby server. So my plan is to deactivate
the standby, do write suspend on standby, split mirror on Hitachi SAN, write
resume, and then activate standby to bring HADR back into synch. I am still
waiting for my storage guys to create a mirror on my standby (probably take
them a few weeks).

[p***@gmail.com]

Dale can correct me if I'm wrong, but although it might be possible to
come up with a solution using HA/DR, this isn't really what HA/DR was
designed for.

GRIDSCALE would probably be a much better fit here. It would allow you
to use your standby database fully active, so you get scale as well as
availability out of your first two database servers. It would allow
you to keep your reporting server up to date in real time with no
additional configuration or scripting.

You can email ***@xkoto.com, or go to www.xkoto.com if you are
interested. Or talk to your IBM sales rep.

Regards,
-Paul

[Mark A]

Post by p***@gmail.com
Dale can correct me if I'm wrong, but although it might be possible to
come up with a solution using HA/DR, this isn't really what HA/DR was
designed for.
GRIDSCALE would probably be a much better fit here. It would allow you
to use your standby database fully active, so you get scale as well as
availability out of your first two database servers. It would allow
you to keep your reporting server up to date in real time with no
additional configuration or scripting.
interested. Or talk to your IBM sales rep.
Regards,
-Paul

Paul, I am very familiar with Xkoto and I met you several times when I
worked at Travelport.

I don't think it is fair to say that HADR is not "designed" to do read-only
on the standby. True, the DB2 implementation does not now allow that, but
the Informix technology from where it came does allow that to the best of my
understanding. IBM has it as an accepted requirement to provide that in DB2
HADR also. Oracle provides that in Data Guard, so I think everyone
understands the requirement.

My guess is that IBM has been delayed because they are trying to develop a
solution that is more than most people need (which is probably just UR
isolation level), instead of providing UR now and then providing the other
isolation levels later.

Regarding Xkoto, I think it works well as a load balancer in an environment
where there are a lot of selects, but I don't think it is quite ready for
prime time as a high volume disaster recovery solution where there are a lot
of inserts and updates in a mission critical application. When I say mission
critical, I don't mean "very important," I mean the whole enchilada (if the
database is down for more than 5 minutes there are severe financial
consequences and if it is down for a day then my company is out of business
permanently).

I am still trying to figure out why there were so many rumors about IBM
buying Xkoto and then apparently the deal fell through. That is not exactly
confidence inspiring IMO.