we have worked with sqla since 5.5 versions and also used dbremote style replication with very good success.
Now the databases have grown and the extraction process for new remotes takes 1-2 hours, which most of the time can be done during offline hours. But more and more we need to extract new remotes during normal work hours. This of course then blocks the consolidate database and is optimal ;)
We now tested the extraction against a copy of the db, and it works fine. But we did the following:
Of course this gives a problem in step 2-4, since then the consolidate DB is down, which is not ok during work hours.
My question is now, is it possible to use dbbackup to make the backup ? We tryed it 2-3 times, but we then always had message missing errors for the new remote.
But for me it's not clear how we get a copy of the db wihout shutting down the consolidate, or what dbbackup options would be required...
In my experience, doing a backup from the live database and leave it running should work. We have done so with v8.0.3 for several years before switching to a custom extraction process. The latter made the extraction much faster (sometimes factor 100), and we could go back to extract from the live database... (As such, running DBXTRACT -v to check the underlying extraction statements is recommended to find out whether these statements may be supotimal...).
What we did is not using DBBACKUP but the BACKUP DATABASE statement, and with v8, it had way less options than now, so I can't tell which options are required. Immediately after, we made a REMOT RESET for all newly created remotes, and then did a commit.
We made sure that the consolidatdd database would not run SQL Remote meanwhile.
FWIW, here is a SQL script that made just the necessary steps on the live database - I leave the comments in German, as that won't hurt you, I bet:)
The newly created remotes are here filtered from a user-defined table called T_Replikat.
-- Erstellen einer Kopie der konsolidierten Datenbank -- als Grundlage für das Extrahieren von Replikaten, -- um dabei nicht den laufenden Betrieb der Original-Datenbank zu beeinträchtigen -- -- Die Kopie der Datenbank wird mit IsolationLevel 3 (phantom reads) erstellt. set option ISOLATION_LEVEL = 3; checkpoint; backup database directory 'D:\DATA\BACKUP\ZuDbExtract'; -- und dann in der aktuellen DB ein Remote Reset fuer alle neu erstellen/wiederangelegten Replikate -- durchfuehren mit anschliessendem Commit; begin for forNeueReplikate as crsNeueReplikate cursor for select user_name from sysremoteusers SRU inner join T_Replikat R on SRU.user_name = R.ReplikatName where flagIstFreigegeben = 0 and R.datLetzteAenderung > current date for read only do message 'Remote Reset für Replikat "' || user_name || '" ausführen' type info to client; execute immediate 'remote reset "' || user_name || '";'; end for; commit; end; set option ISOLATION_LEVEL = 0; commit; -- Anschliessend darf die aktuelle DB wieder replizieren; die neuen Replikate werden aus der -- Kopie heraus erstellt.