Skip to Content
0

Can I use 'until_time' with 'load database' for point in time recovery?

Dec 08, 2016 at 03:23 PM

163

avatar image

Hi

Is there a simple way to identify which trans log dumps are required in order to restore to a specific point of time (Sybase ASE 16), or run just one or two load commands to bring back all the necessary logs?

For example, there may have been 50 trans log dumps since the last backup. A user makes a critical error and we need to restore to before that time. We can run the following command to show all trans logs needed up to the current time:

load database <SID> with listonly = 'LOAD_SQL'

But that shows us the last backup plus ALL the trans logs since - we might only need, say, 48 of the trans log dumps. We can use the 'load transaction from' and 'with until_time' command on trans log backup number 48, but how do we determine that it's that particular trans log that contains transactions within that time frame?

Sure, we could do a 'load trans' 'with headeronly' to read the header of a log to check it's time frame but we need to estimate which trans log file is correct (i.e. guess with 46, with 47 etc) and we still end up with a lot of load commands (48 of them!).

Is there not some way to use 'until_time' with the load database command, for example:

load database <SID> with until_time = "<time>"

That doesn't work... even if we try to put in a 'from' with the location of a valid database dump file on disk, it still doesn't like it. The documentation seems to suggest that 'until_time' can be used with 'load database' (as opposed to 'load transaction') but I can't find any examples and cannot get it to accept the command.

Has anyone been able to successfully use 'until_time' with 'load database'?

Alternatively, we could manually restore the database with the 'load database'; is it then possible to run ONE 'load tran' command pointing at the trans log backup area and using the 'until_time' command, i.e.

load tran <SID> from '<path to all trans logs>' with until_time = "<time>"

i.e. rather than specifiy each and every dump file up until the last. Is it possible to just point at the path where all the log files are? Doesn't seem to allow it.

Just trying to simplify a point in time restore should we have many transaction logs to restore...

Thanks
Ross

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Bret Halford
Dec 08, 2016 at 04:12 PM
0

Hi Ross,

"until_time" cannot be used with LOAD DATABASE.

It is fairly common practice to incorporate date and time information into the name of dumps.
If you do that, you can usually tell which was the first tran dump to be done after an arbitrary point in time, or at least narrow it down to 2 possibilities.

The new dump history file feature can also be used to determine which was the first tran dump done after a point in time.

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc00641.1572/html/new_features/CJAJHJBB.htm

Cheers,
-bret

Show 3 Share
10 |10000 characters needed characters left characters exceeded

Hi Bret

Thanks for the response... I'm assuming the history is enabled as running the "load database <SID> with listonly = 'LOAD_SQL'" commands brings back the complete list/history of backups to restore. Although I can't see the -m startup parameter nor a dumphist file in $SYBASE so not entirely sure...

But yes I suppose it shouldn't be too hard to work out up until which file we would need; just though there might be a less manual way of doing it / that ASE having the history, would be able to work it out itself. Perhaps not then!

Thanks
Ross

0

Hi Ross,

Turns out that the "until_time" syntax for LOAD DATABASE works, but in a different way than the option for LOAD TRAN. It isn't used to directly cause the database to be loaded and recovered to a point in time. For LOAD DATABASE, it is a sub-option for the "with listonly = 'LOAD_SQL'" option to generate the appropriate sequence of commands to recover the database to a point in time: Example:

1> load database test with listonly = 'LOAD_SQL', until_time = "Dec 9, 2016 6:35:00:000am"
2> go
LOAD DATABASE test FROM 'test.dmp'
go
LOAD TRAN test FROM 'test.trn1'
go
LOAD TRAN test FROM 'test.trn2'
go
LOAD TRAN test FROM 'test.trn1'
WITH UNTIL_TIME = 'Dec 9 2016 6:35:00:000AM'
go

If you try to specify a time that precedes the database dump, you get warning like this:

1> load database test with listonly = 'LOAD_SQL', until_time = "Dec 8, 2016 6:35:00:000am"
2> go
LOAD DATABASE test FROM 'test.dmp'
go
Warning. Unable to generate the load sequence until 'Dec 8 2016 6:35:00:000AM'
because the database dump that meets the search criteria also contains
transactions from 'Dec 8 2016 6:35:00:000AM' until 'Dec 9 2016
6:22:45:180AM'.

Cheers,

Bret Halford
SAP Product Support

2

That's *fricken* neat!

'course, would've helped had I paid closer attention to the documentation for load database:

until_time = <datetime>
      generates a load sequence to load (to a specified point in time).

With emphasis on the "generates a load sequence".

1
Mark A Parsons Dec 08, 2016 at 04:42 PM
0

NOTE: Tests run against ASE 15.7 SP138 and ASE 16.0 SP02 PL04

load database/until_time errors out for me in both test dataservers, so obviously a documentation error. Someone from SAP would need to figure out if this was just a typo or perhaps a feature that was planned but never implemented.

---------------

It'd be nice if you could just supply the until_time clause for a series of load transaction commands and have ASE load all log files (in correct load sequence, of course) up until the desired time, but at the moment this generates errors if the log file doesn't encompass the time stamp provided by the until_time clause. Net result being the operator has to know which log file can have the until_time clause applied to it.

Might be a good idea for future releases?

---------------

If you've enabled the dump history file feature you could use load database/with listonly=load_sql to generate the set of load database/transaction commands, then edit said command list to load your database up to the desired point in time.

Otherwise I'm not aware of any method of automatically loading a set of log dump files from a directory/path

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Mark

Yes looking like a documentation error/something that wasn't ever implemented perhaps... yes perhaps newer releases will incorporate this

Seems we can get the details using listonly and work it out without too much trouble, just surprised that there's not a more automated way.

Cheers

Ross

0
Ross Armstrong Dec 13, 2016 at 10:29 AM
0

Thanks guys - tried this myself this morning - seemed to work well!

Made full set of backups:

/usr/sap/archlog/PROD/SDP/master.DB.20161213.090344.000.
/usr/sap/archlog/PROD/SDP/SDP.DB.20161213.090611.000.
/usr/sap/archlog/PROD/SDP/SDP.TRAN.20161213.090936.000.
Dec 13 2016 9:10:33:150AM

Waited a while and made another trans dump:
/usr/sap/archlog/PROD/SDP/SDP.TRAN.20161213.092135.000.
Dec 13 2016 9:21:46:846AM

'Incorrect change' made; carried out a trans dump after:
/usr/sap/archlog/PROD/SDP/SDP.TRAN.20161213.092322.000.
Dec 13 2016 9:23:41:220AM

Opted to restore a minute before the 2nd trans dump (a couple of minutes before the 'mistake'); ran the desired 'listonly' and 'until_time' command as Bret specified:

load database SDP with listonly = 'LOAD_SQL', until_time = "Dec 13 2016 9:20:46:846AM"
go

The output is:

LOAD DATABASE SDP FROM '/usr/sap/archlog/PROD/SDP/SDP.DB.20161213.090611.000'
go
LOAD TRAN SDP FROM '/usr/sap/archlog/PROD/SDP/SDP.TRAN.20161213.090936.000'
go
LOAD TRAN SDP FROM '/usr/sap/archlog/PROD/SDP/SDP.TRAN.20161213.092135.000' WITH UNTIL_TIME = 'Dec 13 2016 9:20:46:846AM'
go

This looks to be correct!

Ran successfully too!

We're running:
SP2 patch level 4 Hotfix 1

Cheers
Ross

Share
10 |10000 characters needed characters left characters exceeded