applying transactions logs

applying transactions logs

Post by Ron D Walku » Fri, 11 Jul 2003 04:20:43



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: 6.28.1.1: 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,

Ron

and

 
 
 

applying transactions logs

Post by tezza_che » Fri, 11 Jul 2003 11:37:30


There has been a huge amount of changes from 11.5 to 12.0 in the area of
recovery module - how ASE do recovery as a result of the introduction of
CLR. You may want to read up on these changes/features in the manual.

The 4305 error your're getting is expected and this is due to the new
recovery technique. In short, this problem happens because after
loading the db to the secondary, you issue the online db command. This
will change the timestamp of db QIP on the secondary server. Hence,
the next load tran will fail with the error saying the timestamp is
out of sequence.
I believe the details of why and how are explained in the manual.
What you should be doing is "standby_access" dump and load procedure.
Again, you can find explanation in the manual.

-Tezza.

--
Posted via http://dbforums.com

 
 
 

applying transactions logs

Post by Anthony Mandi » Fri, 11 Jul 2003 13:57:23



> 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.

        It might be simpler to set up replication. ASE 12.5 has a "lite"
        replication version that comes with it.

Quote:> However, we are upgrading to sybase  12.0 on a solaris 2.8 server and this
> procedure no longer works.

        Personally, I would recommend ASE 12.5.

Quote:> When I attempt this procedure by hand on the command line,
> I get the following errors:
...
> 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

        You shouldn't online it. Also look at the "with standby_access"
        option.

Quote:> 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"
...
> Msg 4305, Level 16, State 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?

        The onlining affects the log's timestamp.

Quote:> 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.

        OK, that's going to be a problem. You can't access the data until
        you online the database but that means you can't load sebsequent
        transaction log dumps. If you need the data to be up to date, you'll
        need to replicate.

-am     ? 2003

 
 
 

applying transactions logs

Post by Bret Halfo » Fri, 11 Jul 2003 14:25:43



Quote:> 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: 6.28.1.1: 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,

> Ron

> and

Hi Ron,

The procedure isn't working anymore due to changes made in the way ASE
handles
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
database called
"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
tran would).

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.

-bret

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

 
 
 

1. Applying Transaction Log Backups !!!

Hi,

I have applying transaction log backups in a database with File=x and
Standby options.
Example:

   RESTORE LOG COMERCIAL
    FROM  DMPComercialLog991115
    WITH FILE=1,
    STANDBY = 'E:\DAT\COMERCIALLOG_UNDO.LDF'

   where:
              FILE=1   - Is the sequence from backup logs named
DMPComercialLog991115.

              STANDBY = 'E:\DAT\COMERCIALLOG_UNDO.LDF'  is the UNDO file

  When i am aplying transaction logs and for mistake  reaplying the same
transaction log backup, not message return with error.  the question is:  Is
the database in  consistent state after this?

Thank you in Advance
Alonso Duarte

2. One solution to HYT00 Timeout Expired error

3. Applying transaction logs on SS7.0

4. Averaging via a Dictionary

5. Restoring a databaase and applying transaction log

6. ADO stress tests

7. Applying Transaction Log Backups, Help please !!

8. Problem with a linked table

9. Applying Transaction Logs Via Query

10. SQL Server 7.0, Cold Backup and Applying Transaction Logs

11. How do I apply transaction log to a restored database

12. applying transaction logs

13. Applying transaction logs that are on disk