Backup & recovery strategies for Partitioned Database in Sybase ASE

sybasesybase-ase

I need to know about backup & recovery strategies for partitioned Database in Sybase ASE 15.7 . I am not worried about transaction failure & recovery scenarios.

We have already faced this 2 scenarios previously in un-partitioned database & it took more than 6 hrs for us to recover back [Reason : Low level sybase error]

In case of catastrophic failure like:

  • Data corruption (Some low level defect causes a table corruption that went unnoticed for days or weeks)

  • Failure of a device [A storage system gets corrupted]

    1. Is my idea good to partition my database to achieve better resiliency?

    2. In case I am doing so, is it possible to recover only failed partition? Sybase ASE maintains logical partitions, so is it possible to do piecemeal recovery?

    3. In case my primary partition gets corrupted because of sybase low level error / disk corruption, will my replica also gets corrupted? [After all it's a replication :)]

In response to the initial answer by Catarrunas:

  1. I didn't get point 2. Could you help me to explain that?

    So in Sybase ASE, storage structure looks like (Partition –> Segment –> Device –> Extent –> Allocation Unit –> Page). Idea is to store 1 partition per segment. Of course again segment can span multiple devices.

    Is it possible now to recover only failed partition held by 1 segment? Can we perform segment level recovery or still we would require to recover whole db?

  2. So currently we don't have any replication. But it was more like due to some low level error, data got corrupted which went unnoticed for few days. In fact the last 2 dumps were also corrupted & we had to go -3th level dump to recover back.

Best Answer

  1. Yes is a good idea, if the DB is spread across different devices. ( that ideally would be on different volumes ). The percentage of the volume of corruption would be less. However to restore you still have to restore the whole DB, which in separate devices and volumes can be faster. ( I/O is spread, less concurrency )

  2. No. You have to recover the Db(s) that the corrupted device holds. So if you have a DB that has 1 corrupt device and you have those logical connections (good practice), you just need to change on OS, the connection to the new physical device. Then restore DB.

  3. How is you replication ? Repserver or disk mirroring? For repserver, i do not think so, your source ASE would stop functioning, and you would have to stop replication to restore the db. Disk mirroring I don't know.

Response to updated question:

My point 2 was:

  • You will need to recover the whole db, unless you know exactly which objects where present on that device (and segments of it) that got corrupted, if yes then you can rebuild them manually( tables, procs, views... ). If you don't know or the labour to do it manually is too great, complete db recover.

What I mentioned about the logical connections was this, for example:

You have DB called TEST1 on the the following devices:

data01
data04
log02
data03

When you create the device with disk init, you have to give the path. But the path is not directed to the device itself, it goes to a soft link. Your data01 for example:

disk init.... physname='..sybase/data01.dat" ...

On the OS that sybase/data01.dat will be pointing to /dev/data01.dat. This way if you need on the OS to replace a corrupted device, you won't have to rebuild the database, you just create a new raw file, point data01.dat to it, and on DB restore data (LOAD). Faster process, than drop old DB, create new, load.

Well I'm pretty sure your errorlog would have some information about that corrupted device and also about your dumps had issues. If your dumps were successfully dumped you should be able to use them on your restore (good devices). So I don't really get why you had to go back so many days. The thing is for instance if you have a corrupted device, it will manifest whenever some action tries to use it, (I/O). So your dumps would have failed i believe.

Related Question