How to maintain a standby database using log shipping
in [DB2]
|
Prev: DB2 transaction problem
Next: Yellowfin launches its Business Intelligence application for the iPhone
From: ranjit on 4 Dec 2009 08:01 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 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
From: Ian on 4 Dec 2009 12:26 On 12/4/09 6:01 AM, ranjit wrote: > 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.
From: hsn_ on 4 Dec 2009 16:11 You still need DB2 license for Warm standby (used for log shiping 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.
From: ranjit on 6 Dec 2009 23:47 Wow! Two really useful responses. Thanks. 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
From: Mark A on 7 Dec 2009 01:56
"ranjit" <ranjit(a)sanovi.com> wrote in message news:354f8167-55a9-4528-b4b0-7285256c31fb(a)f20g2000prn.googlegroups.com... > Wow! Two really useful responses. Thanks. > > 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 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). |