HA for ddl
Hi all, I have to fragment a table that in about 6 months will run out of pages, but its reload takes quite long (create the 28 foreign keys alone takes about 7 hours!), and this is a very critical production environment, almost no downtime allowed. I was wondering if it is possible to take advantage of HDR, CLR (Continuous Log restore) or ER for doing the fragmentation with downtime reduced to a few restarts. I don't see how to do that with HDR because you can't issue DDL statements on the secondary, even if it's updatable. I've tried CLR, but that doesn't seem to work either. You have to keep the secondary instance in Fast Recovery state in order for the logical logs to be suitable for the second instance, right? Maybe it'll work with ER, but I'm not sure yet. I have to investigate this a bit more. It would be really great if something like this would be possible: 1) clone an instance (I1 -> I2): customer applications keep on connecting to I1 and I2 is ready to be modified 2) Apply changes (fragment table(s), reload huge tables, etc) on instance I2 3) Apply data changes that took place on I1 during the time that takes point 2) to complete, on instance I2 (e.g. applying logical logs from I1 on I2, which I guess is impossible...) 4) Stop I1 and applications now are redirected to I2 perform a level 0 copy of I2 5) perform a level 0 copy of I2 and restore the copy on I1 6) if necessary, switch production to I1 again Is there a way to carry out a similar strategy? Any ideas? Anyone experienced similar needs and solved the problem in a different way? Thanks in advance. Cheers!
Yes. Step 3 would be accomplished by using ER replication (before step 2) to maintain all of the tables in l2 from l1, except the table that needs to be fragmented. Once the fragmentation is complete, add that table as a replicant and sync it with the copy on l1 using "cdr sync ...". Once everything is in sync, move users to l2, complete the queues of any transactions on l1 that are waiting to be applied to l2, break replication, and pick up your list at #4. Between 5 & 6 restore HDR and reverse roles once the servers are in sync. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Sep 16, 2011 at 8:08 AM, GERARDO PADIERNA <g.padierna@gmail.com>wrote: > Hi all, > I have to fragment a table that in about 6 months will run out of pages, > but > its reload takes quite long (create the 28 foreign keys alone takes about 7 > hours!), and this is a very critical production environment, almost no > downtime allowed. > I was wondering if it is possible to take advantage of HDR, CLR (Continuous > Log restore) or ER for doing the fragmentation with downtime reduced to a > few > restarts. > > I don't see how to do that with HDR because you can't issue DDL statements > on > the secondary, even if it's updatable. > I've tried CLR, but that doesn't seem to work either. You have to keep the > secondary instance in Fast Recovery state in order for the logical logs to > be > suitable for the second instance, right? > Maybe it'll work with ER, but I'm not sure yet. I have to investigate this > a > bit more. > > It would be really great if something like this would be possible: > 1) clone an instance (I1 -> I2): customer applications keep on connecting > to > I1 and I2 is ready to be modified > 2) Apply changes (fragment table(s), reload huge tables, etc) on instance > I2 > 3) Apply data changes that took place on I1 during the time that takes > point > 2) to complete, on instance I2 (e.g. applying logical logs from I1 on I2, > which I guess is impossible...) > 4) Stop I1 and applications now are redirected to I2 perform a level 0 copy > of > I2 > 5) perform a level 0 copy of I2 and restore the copy on I1 > 6) if necessary, switch production to I1 again > > Is there a way to carry out a similar strategy? Any ideas? Anyone > experienced > similar needs and solved the problem in a different way? Thanks in advance. > > Cheers! > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf303f6a5aa637c204ad0ea5a7