Is it possible to recover log files to a rebuilt database?

[JimDiaz]

I have a remote SQLA 16 database involved in SQL Remote replication that is reaching the maximum 1TB size (4K Page Size, single DB Space) and want to unload and reload to alter the page size and number of DB Spaces. I've tested this and it all works fine, after setting the offsets I was able to receive and send a series of test replication messages.

The issue is it takes two to three days to perform the unload and reload on the hardware I'm working on and I don't want to disable a production system for that long.

I could re-extract but that would interfere with replication for the period of extraction, by procedure we shutdown all external activity on the consolidated when extracting.

Then I had this great idea! I'll unload and reload a backup then apply the transaction logs from the online database, switch the databases and I'm done. Problem is the logs don't apply to the rebuilt database.

Is there something I'm missing, should this work, is there a hidden switch I could use?

[regdomaratzki]

How did you take the backup?

How did you unload/reload the backup copy?

What errors did you get trying to apply the log?

Reg

[JimDiaz]

Reg, thanks for the help

The backup was a full offline backup and when performed the log file was renamed to force the next start to create a new log file.

The unload and reload was done manually IAW the manually starting with "C:\\Program Files\\SQL Anywhere 16\\Bin64\\dbunload.exe" -o "F:\\Unload\\Unload.log" -v -c "UID=;PWD=;Server=;DBN=;ASTART=No" -r "F:\\Unload\\Reload.sql" -ii "F:\\Unload\\Data"

I then modified the Reload.sql to create my db spaces and locate the various tables. Created the database in SQLA 17 opened an ISQL instance and issued a READ F:\\Unload\\Reload.sql

When complete I executed with -x 0 -z ending offset of last log -ir -is

Received no errors applying the logs log file stated
I. 02/09 13:25:11. Database server shutdown automatically after log applied
I. 02/09 13:25:13. Completing server shutdown
I. 02/09 13:25:13. Database server stopped at Tue Feb 09 2021 13:2

but no logs actually applied

[regdomaratzki]

I'm surprised you received no errors applying the transaction log from the original database to the rebuilt backup database. I would have expected you to see something along the lines of :

I. 02/11 09:27:33. Starting database "sqla" (C:\\recovery\\sqla.db) at Thu Feb 11 2021 09:27
I. 02/11 09:27:33. Database recovery in progress
I. 02/11 09:27:33.     Last checkpoint at Thu Feb 11 2021 09:06
I. 02/11 09:27:33.     Transaction log: sqla.log...
E. 02/11 09:27:33. Error: Cannot open transaction log file -- sqla.log belongs to a different database
E. 02/11 09:27:33. Cannot open transaction log file -- sqla.log belongs to a different database
I. 02/11 09:27:36. Database server shutdown due to startup error

Reg

[JimDiaz]

I was surprised as well in fact at first I thought I pointed to the wrong log file directory. Curious...

[regdomaratzki]

In v16, your only option is to apply transactions to a backed up and rebuilt copy of a database is to translate log files to SQL and apply them to the rebuilt database. This will almost certainly change the log offsets where operations were applied and the ending log offset of the transaction log, so it's nor replication/synchronization friendly.

Imagine the process below, which I think (i.e. I haven't actually tested this) will allow you to rebuild a backup of a v16 consolidated database, and while the backup is being rebuilt, you can still make changes to the consolidated database AND allow dbremote to run in RECEIVE ONLY mode, and SQL Remote will continue on it's merry way after the rebuild. It involves doing some unsupported magic that I will NOT explain on the forum, but I think this might work. I've simplified the process by assuming the database contains only cons.db and cons.log in the same directory, but it should easily extrapolate to your environment.

Jim/Volker/Breck : Can you see any issues with this process? The key takeaway here is that by running dbremote in receive only mode during the rebuild, the only updates to the SYSREMOTEUSER table will involve log offsets from the remote databases, not log offsets from the consolidated database.

1. Stop dbremote on prod\\cons.db
2. Stop engine hosting prod\\cons.db
3. copy prod\\cons.db to backup\\cons.db
4. rename prod\\cons.log prod\\cons.olg
5. dbtran prod\\cons.olg ---> capture ending log offset of cons.log as ####
6. start engine hosting prod\\cons.db
7. start dbremote -r on prod\\cons.db ---> DO NOT SEND MESSAGES. RECEIVE MODE ONLY!
8. rebuild backup\\cons.db
9. dberase backup\\cons.log
10. dblog -x 0 -z #### -ir -is backup\\cons.db
11. Stop engine hosting prod\\cons.db
12. dbtran -n apply.sql prod\\cons.log
13. move prod\\*.* justincase\\*.* ---> Paranoid people are more likely to keep their jobs
14. move backup\\cons.db prod\\cons.db
15. dbisql -c dbf\\cons.db read apply.sql
16. *** perform magic on prod\\cons.db so SYSREMOTEUSER matches SYSREMOTEUSER on justincase\\cons.db
17. start engine hosting prod\\cons.db
18. start dbremote on prod\\cons.db ---> Can send + receive now

Reg

[regdomaratzki]

Not mentioned, but should be obvious :

1. During the rebuild, don't do any administration of remote users. No new remote users, no new subscriptions, no remote resets. In a perfect world, there is nothing but DML being applied to the database while the rebuild is occurring.
2. You should do this rebuild to a v17 database, so you can take advantage of dbunload -ao the next time.

[VolkerBarth]

Jim/Volker/Breck : Can you see any issues with this process?

Well, IMVHO, it seems reasonable... Of course, complex systems like SQL Remote setups have tought me to really test things besides assuming they will work 🙂

[regdomaratzki]

Edited my steps. The original steps would have left a log offset gap because I was executing dblog -x -z using the wrong offsets at the wrong time of the rebuild.

[JimDiaz]

No don't think this will work I would think

8. rebuild backup\\cons.db
9. Stop engine hosting prod\\cons.db
10. move prod\\*.* justincase\\*.* ---> Paranoid people are more likely to keep their jobs
11. dbtran -n apply.sql prod\\cons.log
12. dbisql -c dbf\\cons.db read apply.sql
13. erase backup\\cons.log
14. dblog -x 0 -z #### -ir -is backup\\cons.db. ** Last active prod\\cons.log
15. move backup\\cons.db prod\\cons.db
16. *** perform magic on prod\\cons.db so SYSREMOTEUSER matches SYSREMOTEUSER on justincase\\cons.db
17. start engine hosting prod\\cons.db

[regdomaratzki]

Did you comment based on my pre-edited version?

Running dblog -x -z after applying apply.sql was incorrect and would leave a log offset gap, but I think I addressed that in my edit.

[JimDiaz]

Yes sorry I must have

[VolkerBarth]

In my understanding you cannot apply logs from "different databases", and the rebuilt database is different from the original one.

Nevetheless, you might be able to translate the log of the original database to a SQL file and feed that (resp. the statetements done after the rebuild) to the rebuilt database. That should be quite easily doable as long as you prevent any SQL Remote activity after the backup copy is made. (So it would allow to work on with the old production database, which seems to be what you try to achieve.)

I do not know whether there are ways to allow SQL Remote activity in the "old database" after the backup copy is made, but certainly Reg will know.

[regdomaratzki]

Volker is correct, you cannot apply a transaction log from a different database to another database.

We solved this problem in v17 with dbunload -ao, which allows you to automate the process of rebuilding an online database against a backup copy of the database, and the applying the logs from the production database to the rebuilt backup, ignoring the "different database" problem, since we're in control of rebuild + applying of logs. This doesn't help you today at v16 though, because the initial database in this process must be v17. It does mean you'll only ever need to do this process once though, since once your DB is upgraded to v17, dbunload -ao will be a great feature for rebuilding large databases.

Reg

[VolkerBarth]

Ah, wasn't aware of that big improvement... (yes, sometimes I'm missing those old SA blogs...)