Jason L. Froebe – Tech tips and How Tos for Fellow Techies

Scenario: Disaster Recovery Test :

1. Unix group restores the filesystems and creates the necessary raw devices for Sybase ASE (12.5.4 esd 5 64bit on Solaris)
2. We manually build the master (dataserver ….) and change the sort order to no_case iso_1 so we can load the master backup
3. We manually update sysdatabases.status for load (32) and sysdatabases.status2 for don’t recover (16) for the databases we wish to recover as we can’t load the database since the databases are marked suspect. restart, etc. Note that this is the only way for us to load the other databases without recreating each database (which can take hours of build time that we don’t have)
4. Dataserver starts properly noting that the databases are marked for load and don’t recover. We are now able to load the database backups.
5. Load database results in a 903 on an index for a sysobjects before the rpc is sent to the backupserver to start the backup – which doesn’t exist because the raw devices haven’t been written to yet. This occurs only in the v12.5x versions and doesn’t appear to occur in the v15 series.

DR Test involves a ‘virgin’ machine and SAN. As this was during a disaster recovery test, no errorlogs are available (no outside access) and the physical machines have been recycled for other DR tests.

Workaround: set sysdatabases.status to 320 and drop the database using “dbcc dbrepair(<dbname>, dropdb)”… then recreating the databases manually. Ed Barlow‘s sp__revdb is a life saver

I’ve opened a case with Sybase Technical Support but I’m not very hopeful they will be able to reproduce the issue.

UPDATE!  SOLUTION:  The status2 column wasn’t being updated in the script file.  Damn.  Looks like a typo in the slightly modified script.
As there is much confusion within TechSupport as to why this method is necessary let me explain:

The time required to perform a “dbcc dbrepair(, dropdb)” and a “create database …. for load” can take hours or even days depending on the size of the databases and the speed of the devices. This is in no way acceptable for a real disaster recovery environment.

One might argue, “Why not just use Repserver?” (or equivalent?) Well, that’s all fine and dandy but what happens if your DR site and the primary are gone (flood, fire, terrorism, etc)?  You have to restore at an unknown DR site (we have dozens of *potential* DR sites) that you normally wouldn’t have access to.

I realize that this scenario is as foreign from what the TSEs are trained for at Sybase TechSupport as you can get. However, the old ways of a single DR site are long gone. Good or bad, depends on your point of view.

Mich Talebzadeh, on the Sybase Product Futures mailing list, wrote up a verbose introduction to his proposed dump database …. COMPRESSION = n ESTIMATE_ONLY feature.  Basically it would give an estimate of time that the backup will take.  In my opinion, it would be a useful feature but I can’t see how it would be implemented as the compressability of the data could radically differ.  The Z Library, like any other compression mechanism, has no concept of an estimated time for compressing the current data chunk let alone for the rest of the data stream.  If the backup server were to give an estimated time, it wouldn’t be even remotely accurate in most cases.  Kudos to Mich for proposing it though. 

As we now have the capability to use archive database utility in ASE
12.5.4, the ability to interrogate and identify performance issues by
loading the problem database to archive DB is well in hand.

The question that comes to mind is the usual frequently asked question
during the middle of the trading day.

The users have identified a problem with trade capture. We need to
backup
the database, load it into a DEV server and quickly identify and fix
the
problem.

Technically nothing special about it. However, from a business point of
view it is highly visible as the business is impacted and few mangers
are involved including the DBA.

The DBA can give a guesstimate of how long is going to take to back up
the database. However, this is usually based on the overnight backups,
when the server is quiet.

Much like ‘kill SPID with statusonly’ that allows us to give an
estimate of how long is going to take for a rollbacked transaction to
complete, we also need the ability to give a fairly reasonable estimate
of how long is going to take to backup a large database and the
projected dump size, taking into account the load on ASE during the
business hours. This in my opinion is a feature that is desirable to
have.

DUMP DATABASE to ‘/dumpdevice/database_name.dmp’ WITH
COMPRESSION = n ESTIMATE_ONLY

This utility can also be used to estimate the timings of backups and
the
dump area size required on occasions when the OS or ASE is upgraded and
databases need to be backed up.