> I recently inherited a application server that is running sybase
> 11.5.1 on a solaris 2.6 server. Previously, the former dba
> had set up a failover scenarios of
> dumping the database once a night and then copying that dump to
> a secondary database and loading it. Then every 10 minutes, a script
> would run on the primary that would dump the transaction log and
> copy and apply it to the backup database as well. This seemed to work fine.
> However, we are upgrading to sybase 12.0 on a solaris 2.8 server and this
> procedure no longer
> works. When I attempt this procedure by hand on the command line,
> I get the following errors:
> first, I dump the database on the primary:
> 1> dump database QIP to "/tmp/QIP_dat.20030709.102154"
> 2> go
> then I apply the dump to the secondary database and then bring the
> database online:
> 1> load database QIP from "/tmp/QIP_dat.20030709.102154"
> 2> go
> 1> online database QIP
> 2> go
> Next, I dump the transaction log of the primary database:
> 1> dump tran QIP to "/tmp/QIP_log.20030709.105684"
> 2> go
> Lastly, I try to load the dumped transaction log on the secondary server
> and get the following errors:
> 1> load tran QIP from "/tmp/QIP_log.20030709.105684"
> 2> go
> Backup Server session id is: 26. Use this value when executing the
> 'sp_volchanged' system stored procedure after fulfilling any volume change
> request from the Backup Server.
> Backup Server: 184.108.40.206: Dumpfile name 'QIP0319007557 ' section number 1
> mounted on disk file '/tmp/QIP_log.20030709.102154'
> Msg 4305, Level 16, State 1:
> Line 1:
> Specified file 'dump device' is out of sequence. Current time stamp is Jul 9
> 2003 8:08AM while dump was from Jul 8 2003 4:57PM.
> Does anybody have any ideas why this procedure used to work and fails to
> work now?
> We do all of our reading of data from the secondary server so we need it
> online as soon as loading the dump of the database is complete.
> Anyhelp would be appreciated, as I am very new to sybase.
> Thanks and Regards,
The procedure isn't working anymore due to changes made in the way ASE
logging and rollbacks that were made in the 11.9.x release in order to
support the new row-level-locking features. When you online a
database, ASE now generates new log records, called "compensatory log
records" or CLRs, that handle the rollback of any open transactions.
The generation of these new log records increases the database
timestamp, which breaks the sequence of tran logs, preventing you from
loading subsequent tran dumps.
If you want to use the secondary server for both warm backup and
reporting purposes, there is a new option for dump tran and online
"for standby_access", which you will find described in the SA Guide.
The dump tran for standby_access command scans the log looking for
points in time at which there were no open transactions, and it
includes as much of the log as it
can from the beginning to the most recent of these "quiescent points".
You can then load this tran dump onto the backup server and issue an
"online database for standby_access" and, since there were no open
transactions included in the dump, no clrs will be generated and the
database is put online in a read-only mode for reporting purposes, and
you are allowed to load subsequent tran log dumps.
Some issues to think about include the fact that sometimes the dump
tran for standby_access cannot find any quiesenct point in the log,
which raises an error (and no tran dump is generated). The
standby_access tran dump will also not inlcude any completed
transactions that follow the quiescent point (which a regular dump
A strategy for having it both ways is to start with a database dump,
which you load onto the secondary server (don't bring it online).
Then do a tran dump for standby access, load it onto the secondary
server, and bring it online for standby_access, immediately followed
by a regular dump tran for up-to-the-moment recoverability, which you
do not yet load or put online. When the time comes for the next dump
tran, take a dump tran for standby_access. If this succeeds, load
that regular dump tran, followed by the dump tran for standby_access,
and online for standby_access, then take a regular dump tran (which,
again, you don't load yet). If the dump tran for standby_access
fails, take a regular dump tran and save it, too, leaving the
secondary database online with the older data. Keep saving them until
a dump tran for standby_access succeeds, at which point load them all
in sequence, etc.
load the regular dump tran, then take a dump tran for standby_access,
load it, online for standby_access, and take a regular dump tran
[repeat as necessary]. If a dump tran for standby_access indicates it
cannot find a quiescent poi