Restore database in Disaster Recovery site

Question

Our DR sybase solution is based on restoration of a full backup and then the dump of the transaction log from the main site. The sybase is an old version: ASE15.0 However, the solution is not working. After having restore the full backup (without error), the restore of transaction log fails (saying an acitivity on the database was performed). I think because after the restore of the full backup the sequence is broken:

Database 'db_warehouse', checkpoint=(2511803, 20), first=(2511803, 20), last=(2511900, 1).

Can anyone help me to understand why I have this behaviour?

The command used to restore the db is the following:

load database db_warehouse from "/dumps/sybase/db_warehouse_20191215010001.dmp

Thanks in advance P

hard to say what the issue is without more details; would help if you update your question with **all** of the commands you issued (`load database`, `load transaction`, `online database`, etc), in the same exact order, and the full text of all error messages — markp-fuso, Dec 17 '19 at 01:04

score 0 · Answer 1 · answered Dec 17 '19 at 16:40

I did some analysis on my side, and I debug the script used to restore the database and the transaction log.

The first step is to restore the dump of the database using the load command. Below the output of the load command after having received the 100%

Backup Server: 3.42.1.1: LOAD is complete (database db_warehouse).
Started estimating recovery log boundaries for database 'db_warehouse'.
Database 'db_warehouse', checkpoint=(2511803, 20), first=(2511803, 20), last=(2511900, 1).
Completed estimating recovery log boundaries for database 'db_warehouse'.
Started ANALYSIS pass for database 'db_warehouse'.
Completed ANALYSIS pass for database 'db_warehouse'.
Started REDO pass for database 'db_warehouse'. The total number of log records to process is 1315.

Then there are a lot roll foward and the end of the execution, I have:

Redo pass of recovery has processed 145 committed and 0 aborted transactions.
Completed REDO pass for database 'db_warehouse'.
Use the ONLINE DATABASE command to bring this database online; ASE will not bring it online automatically.

So everything looks good, and the database remains offline as exepected:

name                           dbid   suid        status version logptr      crdate                  dumptrdate              status2 audflags    deftabaud   defvwaud    defpraud    def_remote_type def_remote_loc                                                                                                                                                                                                                                                                                                                                                status3     status4     audflags2                         
 ------------------------------ ------ ----------- ------ ------- ----------- ----------------------- ----------------------- ------- ----------- ----------- ----------- ----------- --------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------------------------------
 db_warehouse                              8           1     12       1     2510827 2019-12-16 22:28:44.006 2019-12-14 23:50:00.866      16           0           0           0           0            NULL NULL                                                                                                                                                                                                                                                                                                                                                               131072           0 NULL

Then, the same script is used to load the transaction log and the first thing is to check if the database is offline. During this check, the following query is performed:

SELECT CONVERT(int, lct_admin('logsegment_freepages', db_id('db_warehouse')))

This query returns the following error:

Msg 921, Level 14, State 2
Server 'SYBASE_WAREHOUSE', Line 1
Database 'db_warehouse' has not been recovered yet - please wait and try again.

 -----------
           0

Do you why this query returns this error? The only solution is to put online the db but then the transaction log restore fails (there was an activity in the db and then the sequence is not matching anymore).

Thanks P

the `select/lct_admin()` query obviously needs access to the (currently offline) database; what is your script going to do with the results of the query? I'd suggest removing (or commenting out) the `select` as it (and its result) is not needed to perform the `load database` and `load transaction` commands — markp-fuso, Dec 17 '19 at 21:25
It is strange because if I put the database online and then offline again (changing the status2 of the database), the same query is working well. So, I don’t think it is a problem on offline status. Could it be possible that the logsegment is full?! — user3303852, Dec 17 '19 at 22:47
`has not been recovered yet` == `database is offline` ; by flipping the status2 column you're just circumventing normal recovery operations; the `select/lct_admin()` query has nothing to do with the recovery process so just remove the query ... no more Msg 921 ... and see if there are any other problems ... — markp-fuso, Dec 17 '19 at 23:57

Restore database in Disaster Recovery site

1 Answers1