Question Re: Re-build database in Technology Q&A

Re-build database

Former Member — Mon, 05 Nov 2018 04:20:05 GMT

We have an issue with our main consolidated database which means we need to re-build it from the remote sites data and then re-extract the remote sites.

I plan to copy over the remote site db's to our consolidated server. Then unload and reload them into the new copy of the consolidated db . Mu question is can this be done in one operation ? Or can I run a command to unload the remote DB data and then load it into to new copy of the consolidated DB ?

Also will I need to copy the log files from the remote site to complete this operation ?

Many thanks from a stressed guy !

Mark

Re: Re-build database

VolkerBarth — Mon, 05 Nov 2018 04:34:05 GMT

We have an issue with our main consolidated database which means we need to re-build it

Just to clarify: You cannot restore from a backup of the consolidated and then "just re-apply" missing changes/messages from remotes that have sync'ed/replicated in between?

Is that a SQL Remote or MobiLink setup?

Note, I have no "quick" answer to your rebuilt plans...

Re: Re-build database

Former Member — Mon, 05 Nov 2018 05:30:40 GMT

Yes, that's correct. I have to rebuild consolidated from remote site DB's (we have SQL Remote)

My plan was to stop remote site DB, copy remote site DB over to CONSOL server. Then unload remote site DB into data file. Load this datafile into CONSOL DB. Extract remote site USER DB, copy over to remote site then start. This should then begin replicating.

I wanted to know if there was a way of unloading remote site DB and then load data into CONSOL in one hit ?

Thanks in advance

Mark (still stressed !)

Re: Re-build database

VolkerBarth — Mon, 05 Nov 2018 05:56:46 GMT

Well, usually DBUNLOAD -ac -d can be used to unload into an existing database with identical schema in one go, however when building a new consolidated from a bunch of SQL Remotes I see several issues:

I don't know how SQL Remote-specific values (i.e. the contents of the according system tables or the "max_identity" values for GLOBAL AUTOINCREMENTS) are handled here.
Unless your remotes do contain fully different data sets each, you will have problems with duplicate data when simply reloading from all remotes: If a row has been shared between more than one remote, the unload from the second remote and the underlying LOAD TABLE will fail with a duplicate key violation.
Statistics etc. may be wrong.

Just a few thoughts. Sorry, I do assume you are not only stressed but somewhat running out of time, so I hope the real SQL Remote experts like Reg have time to help you further.

Re: Re-build database

Former Member — Mon, 05 Nov 2018 06:06:59 GMT

Each remote DB has unique data and key's so shouldn't be too much of a problem.

I presume any record that has a autoincrement value will keep the value from the original store data ?

Mark

Re: Re-build database

Former Member — Mon, 05 Nov 2018 06:09:59 GMT

Also, do I need to bring the LOG files across from the remote sites ? (some of them are very large and take time to copy) - can I just run the DB on it's own with no log file while I unload/reload ?

Thanks

Mark

Re: Re-build database

VolkerBarth — Mon, 05 Nov 2018 06:27:28 GMT

When you are sure that these remotes must be re-extracted, you can cleanly shut them down and just copy and use the .db files to rebuild your consolidated.

Re: Re-build database

VolkerBarth — Mon, 05 Nov 2018 06:33:25 GMT

I presume any record that has a autoincrement value will keep the value from the original store data ?

Yes, as these rows have already got their autoincrement value, that value will be kept. (Aside: DBUNLOAD uses LOAD TABLE with the default clause DEFAULTS OFF, so a new default value would not be set even if the according column would be missing within the optional LOAD TABLE column list.)

Re: Re-build database

Former Member — Mon, 05 Nov 2018 06:51:50 GMT

Okay, I'm just about to run some tests on some copies to see if it works ok.

Thanks Mark (little less stressed 90% !)

Re: Re-build database

Former Member — Mon, 05 Nov 2018 07:04:30 GMT

I presume I can run the new version on the consolidated with DBEng so no one can connect to it while we re-build ?

Can i just delete the old log files before we start re-build ?

Mark

Re: Re-build database

VolkerBarth — Mon, 05 Nov 2018 07:42:36 GMT

so no one can connect to it

No one except from the same machine...

Can i just delete the old log files before we start re-build ?

What files do you mean?

Re: Re-build database

Former Member — Mon, 05 Nov 2018 08:17:34 GMT

The old consolidated .log files

Re: Re-build database

VolkerBarth — Tue, 06 Nov 2018 03:23:46 GMT

Well, if you build a new consolidated database, it will have its own series of transaction logs and won't be able to use older logs, so for replication purposes they are no longer needed.

However, in case your consolidated is not just a collector of remote data but does also have "its own data", you might check whether the old logs contain data that is not yet part of your new consolidated. For that purpose you might translate the old logs (possibly with the DBTRAN "-is Local" option) to check whether there are usable DML statements to run against the new database.

That being said, I would generally not immediately delete old logs but move them to a secure place, just in case...

Just my two cents, anyway.

Re: Re-build database

Former Member — Tue, 06 Nov 2018 08:29:57 GMT

Hi Volker

I have extracted first remote site and at remote site when I try to connect it comes up with message below ? We extracted the DB on a 17.0.7.3390 build - the remote site is 17.0.9.4803 ?

Thanks

[SAP][ODBC Driver][SQL Anywhere]Unable to start specified database: Server must be upgraded to start database C:\\sdata\\store08.db SQLCODE=-934 SQLSTATE=08001

Re: Re-build database

VolkerBarth — Tue, 06 Nov 2018 08:54:35 GMT

Hm, I would ask that as a new question...

FWIW, I would expect that error message under switched conditions, i.e. when the database file version is newer than the database server version.

You may have a look at the SYSHISTORY entries for that database (checked on a database server that can run it, apparently:)...).

Re: Re-build database

Former Member — Tue, 06 Nov 2018 10:05:13 GMT

Okay, solved this, it was a PATH issue - we upgraded to 64 bit version from .0.7 -> .0.9. and it was still looking at the old (x86) install.

Re: Re-build database

Former Member — Wed, 07 Nov 2018 06:57:54 GMT

If I'm running dbeng and no one is connected can I run extract users at a level lower than 3 ?

Re: Re-build database

VolkerBarth — Wed, 07 Nov 2018 08:03:54 GMT

Yes, isolation level 0 is sufficient if there are no connections that modify the contents of the published data during the extraction.

Re: Re-build database

Former Member — Sat, 10 Nov 2018 03:46:40 GMT

Hi Volker

We have now extracted all users (apart for some less urgent ones) However when I fire up the SQL Remote service on the consolidated I get an error.

Not sure what to do here ? All the reloads and extractions seem to work fine and the remote sites are all working (including their SQL Remote services)

HELP

Mark

I. 2018-11-10 08:36:44. 
I. 2018-11-10 08:36:45. Scanning logs starting at offset 01178032419905
I. 2018-11-10 08:36:45. Transaction log "c:/sdata_log/consol.log" starts at offset 01178199173531
I. 2018-11-10 08:36:45. Processing transaction logs from directory "c:\\sdata_log"
I. 2018-11-10 08:36:45. Retrieving offline transaction logs through the engine from "c:/sdata_log/"
E. 2018-11-10 08:36:45. No off-line transaction log file found and on-line transaction log starts at offset 01178199173531.
I. 2018-11-10 08:36:45. We are looking for a transaction log file starting at offset 01178032419905.
I. 2018-11-10 08:36:46. Execution completed

Re: Re-build database

Former Member — Sun, 11 Nov 2018 06:03:35 GMT

I think I have made a mistake (see post a bit further on) - I cleared the old consol db of all data, did a dbreload and then deleted the old log files by mistake. I then continued reloading data into the consolidated, then extracted all users.

However when I now try and start dbremote it is giving me an error, I guess it's still looking for the old files. Is there any way I can stop it looking for the older log files and just use the current one ?

Otherwise I might have to start all over again ?

I. 2018-11-10 08:36:44. 
I. 2018-11-10 08:36:45. Scanning logs starting at offset 01178032419905
I. 2018-11-10 08:36:45. Transaction log "c:/sdata_log/consol.log" starts at offset 01178199173531
I. 2018-11-10 08:36:45. Processing transaction logs from directory "c:\\sdata_log"
I. 2018-11-10 08:36:45. Retrieving offline transaction logs through the engine from "c:/sdata_log/"
E. 2018-11-10 08:36:45. No off-line transaction log file found and on-line transaction log starts at offset 01178199173531.
I. 2018-11-10 08:36:45. We are looking for a transaction log file starting at offset 01178032419905.
I. 2018-11-10 08:36:46. Execution completed