Database Upgrade Log Files

[former_SQLA_member1694934]

We upgraded a large database from 12 to 16 using the dbugrade process (i.e. did not unload and reload). Part of our backup process was to apply the log files from this database to a backup of this database located at a remote off-site location.

My question is what should be done once the live database is upgraded? Will applying the log file upgrade the backup database when applied? Do I need to run the dbupgrade process on the backup database exactly as ran on the live and then apply the subsequent log files? Am I out of luck and need to bring down a new backup of the 700 GB database to the remote site?

By the way the database is a consolidated to > 150 remotes.

Thanks in advance!

Jim

[VolkerBarth]

Interesting question:)

AFAIK, the DBUPGRAD tool and the ALTER DATABASE UPGRADE statement are not contained in the transaction log - at least I don't find fitting entries in the scripts you get by translating the transaction logs, neither in the automatically renamed log nor the newly created one.

That seems to fit to the documentation that tells that upgrading (like unloading/reloading) has to be done for each database individually.

Therefore, I guess the answer to that question:

Do I need to run the dbupgrade process on the backup database exactly as ran on the live and then apply the subsequent log files?

is: "No, but you need to replace the backup up with a new backup of the upgraded main database."

For the database mirroring feature, you certainly need to stop the mirroring system, upgrade (or rebuild) the primary database and then copy that over to the secondary server and then re-start the mirroring.

So I conclude it's the same for a live backup like you seem to use, i.e. you need to start with a full backup of the upgraded database and then re-start the live -backup...

Note: I provide that as a comment because I'm just testing and wild guessing...

[Breck_Carter]

I wanted Volker to be wrong, because what he reported goes against The Watcom Rule that states "Watcom does things the way they should be done".

But (sigh) Volker is correct.

It is possible to capture all the transaction log files before and during the upgrade process (either dbupgrad.exe or ALTER DATABASE UPGRADE), and successfully apply them to the backup database using dbsrv12 -ad (before the upgrade) and dbsrv16 -ad (after the upgrade), and all the data changes you make along the way are successfully applied to the backup database.

So far, so good, The Watcom Rule is in effect... no error messages, no lost data.

But... and it is a huge but... none of the upgrade changes are included in those transaction log files, not even the ALTER DATABASE statement itself, not any of the CREATE PROCEDURE operations it performs, nothing.

Volker talks about nothing showing up in dbtran... nothing shows up in a hex editor either... just 320K of binary zeros, whether you use dbupgrad.exe or ALTER DATABASE.

Here's more proof: The final "upgraded" backup database doesn't contain the new SQL Anywhere 16 sa_certificate_info procedure, nor does it show an 'UPGRADE' row in SYSHISTORY:

SELECT * FROM sa_certificate_info ( 'whatever' );

Could not execute statement.
Procedure 'sa_certificate_info' not found
SQLCODE=-265, ODBC 3 State="42S02"
Line 1, column 1
(Continuing after error)

SELECT operation, version, first_time, last_time
  FROM SYS.SYSHISTORY
 ORDER BY first_time;

operation,version,first_time,last_time
'INIT','12.0.1.4231','2018-09-03 15:21:03.000','2018-09-03 15:21:03.000'
'START','12.0.1.4231','2018-09-03 15:21:04.000','2018-09-03 16:06:02.000'
'LAST_BACKUP','12.0.1.4231','2018-09-03 15:21:58.000','2018-09-03 15:21:58.000'
'START','16.0.0.2512','2018-09-03 16:06:02.000','2018-09-03 16:06:02.000'
'LAST_START','16.0.0.2512','2018-09-03 16:06:02.000','2018-09-03 16:06:11.000'

Meanwhile, the main database that was upgraded does have both those things:

SELECT * FROM sa_certificate_info ( 'whatever' );
name,value
[empty]

SELECT operation, version, first_time, last_time
  FROM SYS.SYSHISTORY
 ORDER BY first_time;
operation,version,first_time,last_time
'INIT','12.0.1.4231','2018-09-03 15:21:03.000','2018-09-03 15:21:03.000'
'START','12.0.1.4231','2018-09-03 15:21:04.000','2018-09-03 15:26:20.000'
'LAST_BACKUP','12.0.1.4231','2018-09-03 15:21:58.000','2018-09-03 15:24:23.000'
'START','16.0.0.2512','2018-09-03 15:26:20.000','2018-09-03 15:26:20.000'
'LAST_START','16.0.0.2512','2018-09-03 15:26:20.000','2018-09-03 15:26:20.000'
'UPGRADE','16.0.0.2512','2018-09-03 15:30:49.000','2018-09-03 15:30:49.000'

But... maybe you don't care. The upgrade process doesn't really do much, maybe you won't miss it. Note that you DO get everything built in to dbsrv16.exe like BINTOHEX().

Maybe when it comes time to restore the backup, you can (a) proceed to use it without the upgrade, or (b) run the ALTER DATABASE UPGRADE at that point.

Or... maybe... just forget about the upgrade process altogether, and run your original Version 12 database file in a Version 16 server.

Personally, that's what I recommend. So few people run the upgrade process that I do ... not ... trust ... it, and I would never, ever use it on one of the databases I administer.

Ever...

...except for you, this one time 🙂

PS Foxhound doesn't recognize an upgraded Version 12 database file as anything other than a Version 12 file which it really is 🙂

Version... 
Software: 16.0.0.2512 
DB File:  12.0.1.4231

          
                           P U L P   F I C T I O N
                                                        May 1993
                                                        last draft
                                  THE WOLF
                            (into phone)
                       Nobody who'll be missed.

[VolkerBarth]

So I guess there are two recommended options when using a live backup:

• If you want to use a "real v16 database", do not make an upgrade but a full rebuild - because you will have to copy the full main database to the backup site anyway...
• Leave the v12 database as is.

[Breck_Carter]

Initially, I too thought JJ Diaz was talking about "live backup" but that is probably not the case. In a live backup situation, dbbackup -l runs continuously to build one giant backup transaction log file at the remote site, and that transaction log is not applied to the original backup database file until a failover is required.

In particular, JJ Diaz asks about "applying the log file" which does not happen while a live backup process is running.

JJ Diaz's term "live database" probably means "the main production database as opposed to the backup database".

JJ Diaz is probably talking about a "regular backup" situation where periodic separate backup transaction log files can be applied to the backup backup database file via dbsrv12 -ad which rolls the database file forward but does not start it.

The Help for live backup contains a very brief and glib discussion about what happens when a regular backup happens while live backup is in progress... it confuses me every time I read it, until I remember why people (i.e., me 🙂 have so much trouble administering live backup.

I didn't finish my live backup testing with the upgrade process, but it is highly likely that dbbackup -l could be set up properly to continue restarting (which is what folks do with dbbackup -l after a normal shutdown / restart) after dbupgrad.exe runs... BUT as you have described it, the log data sent to dbbackup -l would not contain the upgrade actions.

So yes, your "recommended options when using a live backup" are probably correct.

[VolkerBarth]

Well, I guess I also thought of a "database backup with regularly applied logs" situation:)

My point was that JJ Diaz probably

• either wants to avoid the need to copy a huge full database backup (and then a dbupgrade does not really fit as tested)
• or has to accept the need to copy a huge full database backup (and then you might be better off with a real database rebuild).

The documentation of live backups is, err, short and leaves me puzzled, too...

[former_SQLA_member1694934]

Volker, Breck thanks for the discussion. Sorry for the confusion you are correct I was talking about applying the log files created during the normal incremental and full backup process to an off-site copy of the database.

Based on your feedback I will continue the process against a 12 database until I can bring down a new copy of the upgraded database.

We have completed the 16 upgrade process on all production databases but it sounds like that might not have been necessary.

I'll be doing some more work/thinking about this next week and will post again.

Thanks

Jim Diaz