How to maintain a standby database using log shipping
ranjit
I am working on providing a standby DB2 database copy of a live
database for a client. But client does not want to use HADR (because
of cost, resource and time constraints). I have come up with the
following:
INITIAL SYNCRONIZATION
That is, make a full database copy of the production database to a
secondary server using the following steps.
1. Put prod database in circular logging mode to take an offline
backup
2. Take an offline backup
3. Put DB in archive log mode
4. Get backup timestamp from database.
5. Ship backup from primary to DR server
6. Restore database at DR with backup timestamp
CONTINUOUS SYNC
That is, incremental archive replication/recovery with the following
steps.
1. Copy (using scp) archive logs from primary to DR
2. Issue roll forward command at DR
PRIMARY DOWN SCENARIO
That is, unplanned failover that may have some data loss. Do the
following:
1. Recover DR database to online active mode
2. Restart the DR DB
The questions I have are:
1. Is the above plan feasible and complete?
2. Can DR database be maintained in continuous close
synchronization with primary, using above setup?
3. Let me know if you anticipate any problems or anything I may
have missed.
Regards,
Ranjit
Ian
> Hi,
> I am working on providing a standby DB2 database copy of a live
> database for a client. But client does not want to use HADR (because
> of cost, resource and time constraints). I have come up with the
> following:
>
> INITIAL SYNCRONIZATION
> That is, make a full database copy of the production database to a
> secondary server using the following steps.
> 1. Put prod database in circular logging mode to take an offline
> backup
Wrong. The database must be in archive log mode in order to perform
rollforward recovery.
> 2. Take an offline backup
> 3. Put DB in archive log mode
Not necessary
> 4. Get backup timestamp from database.
> 5. Ship backup from primary to DR server
> 6. Restore database at DR with backup timestamp
> CONTINUOUS SYNC
> That is, incremental archive replication/recovery with the following
> steps.
> 1. Copy (using scp) archive logs from primary to DR
> 2. Issue roll forward command at DR
>
> PRIMARY DOWN SCENARIO
> That is, unplanned failover that may have some data loss. Do the
> following:
> 1. Recover DR database to online active mode
> 2. Restart the DR DB
Step 2 is not necessary, when you bring the database out of rollforward
pending it will automatically perform crash recovery.
You also need to make sure that the primary is completely down -- you
don't want clients accidentally connect to a rebooted primary and start
doing work -- then you end up with a "split brain".
> The questions I have are:
> 1. Is the above plan feasible and complete?
From a database perspective, with the above corrections, yes.
However, you still need to consider how clients are going to know
when and how to modify the configuration connect to the secondary
database instead of the primary.
> 2. Can DR database be maintained in continuous close
> synchronization with primary, using above setup?
It depends on your definition of "close". Most people would not
consider log shipping as "continuous" or "close" because it's a
batch process that has a built in delay. The delay is a function
of 2 variables:
1) How frequently log files are archived on the primary
2) How frequently you copy and apply archive logs on standby
#1 is affected by LOGFILSIZ and the volume of transactions.
hsn_
target) anyway if your db2 instance is started. From license point, it
is no difference between HADR and log shiping target if you have right
to use HADR feature. HADR feature is now FREE part of all DB2 Editions
except DB2 Express when Express is licensed by CPU or number of users.
Consult this article, you will most likely to buy 100 PVU for warm
standby/hadr machine.
http://www.ibm.com/developerworks/data/library/techarticle/dm-0909db2halicensing/index.html?S_TACT=105AGX11&S_CMP=FP
In short, dont waste time with custom solution, use hadr, it will save
you time and you will get more reliable solution.
If you want to save money (100PVU) you need to keep your second db2
instance down and start it and rollforward in case of primary failure.
Rollforward can take some time, depends how often you are doing
fullbackups on primary.
ranjit
Ian,
I was thinking circular logging is needed for an offline backup. But I
will try all your recommendations. Primary has to be completely down.
So I will have a script periodically check that it stays down and
raise an alarm if it is found to be up.
The frequency of log dumps would be 20 minutes but as you said it will
depend onsome parameter settings and the trans activity.
Hsn,
License is not a problem as client is able to get more. I did not know
about HADR being free. I will check your link.
I intend to do Rollforward in a continuous, 21 minute cycle so that in
the event of primary failure only a few logs will require to be
applied at DR.
Thanks all for the help. I might come up with more questions :-)
Regards,
Ranjit
Mark A
news:354f8167-55a9-4528...@f20g2000prn.googlegroups.com...
The dbm does not have to be down, and you can take an offline backup on a
database with archive logging. But in order to take an offline backup, there
must not be any connections to the database. That includes connections as
the result of an active HADR primary or standby database.
Whether or not HADR is free depends on the release. I may not be included in
all editions prior to 9.7 or 9.5 (not sure).