Standby Database

[jdempsey]

I want to be able to create a standby database on another HP-UX system, which is continually updated from the master, although not synchronously, eg. a time delay. Although it is not clear from the documentation how to achieve this. Another requirement is to be able to have read-only access to this standby database, and still be able to continue to receive updates from the master.

If someone could enlighten me, or even a howto doc, I would be grateful.

FYI: new Progress DBA, formerly Oracle DBA, or anything else they throw at me.

 

[CtL]

Welcome to the world of Progress database administration!

Good news: Yes, building a "warm spare" db is not that tough (and it's fun!).
Bad news: You will not be able to log into the warm spare, even in read-only mode, while you are keeping it current.

It it is possible, and not that difficult, to keep a non-synchronous copy of your live db using After Imaging (AI). AI is a binary log of all transactions that are made to the database. If you develop a good after imaging strategy you should be able to keep them within ten minutes of each other, although it is a bit safer to go for half an hour (more on that later).

The basic principle is:

a) Add AI extents to your database (IMHO a lot of small ones is better than a few large ones).
b) Develop a method to identify and copy closed/filled extents. You may also want to deliberately close AIs before they are full in order to keep everything on a timely basis.
c) Modify your nightly backup to accomodate AI activity.
d) Make a copy of your db to a new location.
e) Using the process developed in step b), take your copied AI files and apply them against your new one (using rfutil).

This may sound complex, but a lot of companies do this routinely and with great success.

Progress has done an excellent job of describing most of this in their documentation on AI. It used to be that their documentation was good only for looking up details, but I think they are a lot better on this now.

Notes:
1) What version of Progress are you on?
2) There is a very rare bug that can cause errors when closing an open AI extent. I have a customer who was using a "force a partially full AI closed" method, and about once every 4-5 months they would get an error. There are pros and cons to this method.
3) There are a lot of general discussions on this in a lot of forums, check archives for more info.
4) Once you "touch" the copied database by bringing it up you will not be able to apply the AIs to it. If you absolutely have to do this then consider a thrid copy which is only current up until the point where you started/accessed the db. Ugly, but until we get a read-only/rolling forward db method it is what we are stuck with.

Links:
Progress Knowledgebase - 17645: Truncation of the ai file and changing its blocksize
Progress Database Adminstration Guide and Reference (v9) After-Imaging
http://www.peg.com/techpapers/2001C...tems Management/D12_D13_Non_Stop_Progress.ppt
(I'm surprised I couldn't find more on this easily)

 

[toby.Harman]

If the requirement for the warm standby includes reporting on the standby there are a couple of options open.

Progress recently bought a company that has a product called PeerDirect. This would allow asynchronous replication of the database to another database while allowing reporting to happen from the standby.

Alternatively you could "roll your own" replication. I have done this and it is NOT trivial.

AI is (as CtL states) very much in use by most companies and should be investigated for benefits other than warm standby databases. Most of the DBA's I have met have had some reason to use AI and I can recommend it.

To give you an idea of what you can do with AI, there is a company I am aware of which switches its AI extents every 5 minutes or so.

 

[richard_thorp]

Hi

Progress have just released a product called Fathom for Replication. This will work synchronously. In addition, depending on the version purchased, you can get read-only access to the hot spare and still keep in sync.

Funky-doo.

Richard

 

[toby.Harman]

Fathom is a neat solution if you require the full replication thingy and you have deep pockets. To be honest it is reasonably simple to roll your own replication (and yes - I have done it in production) using the replication triggers either asynchronously or synchronously. That takes care of needing to access the warm/hot standby .

If you do not need to access the standby then the easiest implementation would have to be using AI.

Fathom is a great product, but a touch late to market! Most people have already developed their own replication strategies if they need them, and management of the database through scripts is a common topic both here and on the PEG.

Toby