|
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? |
|
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. 2
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
(11 Feb '21, 10:02)
Reg Domaratzki
Replies hidden
1
Ah, wasn't aware of that big improvement... (yes, sometimes I'm missing those old SA blogs...)
(11 Feb '21, 11:33)
Volker Barth
|
|
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.
Reg Not mentioned, but should be obvious :
(11 Feb '21, 11:39)
Reg Domaratzki
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 :)
(11 Feb '21, 11:59)
Volker Barth
1
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.
(11 Feb '21, 12:16)
Reg Domaratzki
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
(11 Feb '21, 13:41)
J Diaz
Replies hidden
Comment Text Removed
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.
(11 Feb '21, 14:08)
Reg Domaratzki
Yes sorry I must have
(11 Feb '21, 16:29)
J Diaz
|
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
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
but no logs actually applied
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 :
Reg
I was surprised as well in fact at first I thought I pointed to the wrong log file directory. Curious...