Troubleshooting suggestions for failed cross-platform load?

[unknown]

Hi Folks,

We've done a test of a cross-platform load for a client from
a 64-bit Sun to a 32-bit RH Linux and it worked fine. But
now, when the time comes to do the live switch, it fails
just as it gets to the transaction log:

... /* so far so good */
Cross-platform conversion for database client_db: 15805282
pages completed.
Completed cross-platform conversion for user objects.
Started cross-platform conversion for log records.
Adaptive Server cannot load this database because the
database that was dumped was not quiescent when the dump was
performed. Run sp_flushstats before DUMP DATABASE and ensure
that the database is not updated during the dump.

Well, I did do sp_flushstats and checkpoint and the DB was
absolutely not updated during the dump. (We've turned the
crank on this multiple times trying to ensure that all the
steps happened. Same result.) I've about concluded that
something is wrong with the source DB and the client did
have a problem with the log filling up (despite a scheduled
task that dumps it regularly and thresholds and the whole
bit).

Perhaps something is subtly corrupted in the transaction log
(although the DB's been in use for many weeks since that
happened)?

The instructions say you should wait for a period of time
after running sp_flushstats--is using WAITFOR DELAY going to
cause a problem? Do I need to wait a long time (given that
this is a very large DB)?

Would setting the log to truncate on checkpoint possibly
help?

Apparently there's been a problem and the regular DBCC's
that are supposed to have been happening haven't been. The
syslogs table has no problems show up with DBCC CHECKTABLE
or TABLEALLOC. How important is it to run the DBCC checks
before attempting to do a cross-platform dump (since we
assumed the regular checks had been running, we'd skipped
that step)?

Any ideas you can suggest would be most gratefully accepted.

Here's the info on the two systems:
Source:
Sun Solaris
Adaptive Server Enterprise/12.0.0.4/P/SWR 9973 ESD
1/Sun_svr4/OS 5.7/1784/64bit/FBO/Sat Dec 8 10:03:51 2001

Target:
Linux RH EL 3.0 w/ latest kernel: 2.4.21-32.0.1.ELsmp
Adaptive Server Enterprise/12.5.3/EBF 12151/P/Linux
Intel/Enterprise Linux/ase125x/1883/32-bit/OPT/Thu Nov 11
17:33:16 2004
(That's 12.5.3 ESD # 1, I believe; I haven't tried ESD #2
yet--I'm about to do that.)

Thanks for anything you can suggest.

John
Alpha-G Consulting, LLC
www.alphagconsulting.com

[Mark A. Parsons]

As the message says, make sure no one is making mods to the database
when it's dumped. Are you 110% sure no one is making *any* changes to
the database during the dump?

How about quiescing the database just before the dump? This will insure
*NO* changes are allowed to the database during the dump. After the
dump has completed you can then release the quiesce 'hold' on the database.

Post by unknown
Hi Folks,
We've done a test of a cross-platform load for a client from
a 64-bit Sun to a 32-bit RH Linux and it worked fine. But
now, when the time comes to do the live switch, it fails
... /* so far so good */
Cross-platform conversion for database client_db: 15805282
pages completed.
Completed cross-platform conversion for user objects.
Started cross-platform conversion for log records.
Adaptive Server cannot load this database because the
database that was dumped was not quiescent when the dump was
performed. Run sp_flushstats before DUMP DATABASE and ensure
that the database is not updated during the dump.
Well, I did do sp_flushstats and checkpoint and the DB was
absolutely not updated during the dump. (We've turned the
crank on this multiple times trying to ensure that all the
steps happened. Same result.) I've about concluded that
something is wrong with the source DB and the client did
have a problem with the log filling up (despite a scheduled
task that dumps it regularly and thresholds and the whole
bit).
Perhaps something is subtly corrupted in the transaction log
(although the DB's been in use for many weeks since that
happened)?
The instructions say you should wait for a period of time
after running sp_flushstats--is using WAITFOR DELAY going to
cause a problem? Do I need to wait a long time (given that
this is a very large DB)?
Would setting the log to truncate on checkpoint possibly
help?
Apparently there's been a problem and the regular DBCC's
that are supposed to have been happening haven't been. The
syslogs table has no problems show up with DBCC CHECKTABLE
or TABLEALLOC. How important is it to run the DBCC checks
before attempting to do a cross-platform dump (since we
assumed the regular checks had been running, we'd skipped
that step)?
Any ideas you can suggest would be most gratefully accepted.
Sun Solaris
Adaptive Server Enterprise/12.0.0.4/P/SWR 9973 ESD
1/Sun_svr4/OS 5.7/1784/64bit/FBO/Sat Dec 8 10:03:51 2001
Linux RH EL 3.0 w/ latest kernel: 2.4.21-32.0.1.ELsmp
Adaptive Server Enterprise/12.5.3/EBF 12151/P/Linux
Intel/Enterprise Linux/ase125x/1883/32-bit/OPT/Thu Nov 11
17:33:16 2004
(That's 12.5.3 ESD # 1, I believe; I haven't tried ESD #2
yet--I'm about to do that.)
Thanks for anything you can suggest.
John
Alpha-G Consulting, LLC
www.alphagconsulting.com

[Mark A. Parsons]

A few items to take into consideration when dealing with the quiesce
command ... depends on your version of ASE:

[NOTE: These notes are a few months old.]

case/CR description & comments
----------------------------------------------------------
case 10619022 dump tran no_log/truncate_only followed
by quiesce/hold; quiesce fails because
non-logged operations not allowed prior
to quiesce

ASE 12.0.0.1
fixed with ASE 12.0.0.1 ESD #1

----------------------------------------------------------
case 10999113 quiesce/hold + sp_configure +
CR 306566 quiesce/release; quiesce will fail/hang;
must reboot ASE to have db's released
from quiesce state

ASE 12.x
fixed with ASE 12.5.2 IR

workaround: don't issue sp_configure during
quiesce operations

----------------------------------------------------------
case 10758606 quiesce/hold fails if auditing enabled for
CR 255159 db; must disable auditing for db prior to
issuing quiesce/hold

ASE 12.0.0.1
fixed in 12.0.0.1 and 12.5.0.2

----------------------------------------------------------
case 20318430 quiesce/hold + dump database/transaction
CR 316109 + attempted logins; logins fail due to
CR 340771 chain of lock requests/blocks on master..
sysdatabases

ASE 12.0.0.7
to be fixed in 12.5.2 IR

----------------------------------------------------------
case 10898832 "quiesce...to <manifest_file>" fails if
any underlying devices have Sybase mirroring
enabled; disable mirroring on underlying
devices of a database to be quiesced

manifest files are utilized in conjunction
with the '(un)mount' commands used for moving
and/or copying a db to another dataserver.

ASE 12.x
may be fixed in ASE 15

----------------------------------------------------------
CR 353699 All future logins are blocked if 'max
failed logins' is set, the master db is
quiesced, and a login attempt is made
with an invalid password.

Work around:

a - set 'max failed logins' to 0 before
issuing the quiesce command

b - leave a connection open to insure
a means of issuing the quiesce/release

c - reboot server if 'a' and 'b' not
implemented and no one able to log in

ASE 12.5.1
no notes on when to be fixed

----------------------------------------------------------
CR 304071 Issue with lengthy suspend times waiting
for quiesce/hold to take effect.

Recommendation:

issue manual 'checkpoint' prior to
the quiesce/hold command

ASE 12.5.0.3
no notes on a 'fix'

Not much to go on here. Obviously the
quiesce/hold needs to flush all db changes
to disk before the disks can be copied.
In the case where there has been a lot
of activity before the quiesce/hold, it
could take awhile for quiesce/hold to
checkpoint the database ... thus leading
to lengthy times for the quiesce/hold
to complete successfully.

----------------------------------------------------------
tech notes How to determine if a database is in
a quiesced state:

a - select name from master..sysdatabases
where status3 & 128 = 128

128 => writes to database are blocked
by the quiesce command

b - there should be an entry in the
ASE errorlog for each quiesce/hold
command; entry includes tag name

----------------------------------------------------------

There are also a couple cases that state you may have problems with
replication components ... either while the RSSD is being quiesced ...
or the RepAgent failing to start for a database that is currently in a
quiesced state.

'fix' is to make sure you check, and restart if required, any
replication components after the quiesce has been released.

Post by Mark A. Parsons
As the message says, make sure no one is making mods to the database
when it's dumped. Are you 110% sure no one is making *any* changes to
the database during the dump?
How about quiescing the database just before the dump? This will insure
*NO* changes are allowed to the database during the dump. After the
dump has completed you can then release the quiesce 'hold' on the database.

Post by unknown
Hi Folks,
We've done a test of a cross-platform load for a client from
a 64-bit Sun to a 32-bit RH Linux and it worked fine. But
now, when the time comes to do the live switch, it fails
... /* so far so good */
Cross-platform conversion for database client_db: 15805282
pages completed.
Completed cross-platform conversion for user objects.
Started cross-platform conversion for log records.
Adaptive Server cannot load this database because the
database that was dumped was not quiescent when the dump was
performed. Run sp_flushstats before DUMP DATABASE and ensure
that the database is not updated during the dump.
Well, I did do sp_flushstats and checkpoint and the DB was
absolutely not updated during the dump. (We've turned the
crank on this multiple times trying to ensure that all the
steps happened. Same result.) I've about concluded that
something is wrong with the source DB and the client did
have a problem with the log filling up (despite a scheduled
task that dumps it regularly and thresholds and the whole
bit).
Perhaps something is subtly corrupted in the transaction log
(although the DB's been in use for many weeks since that
happened)?
The instructions say you should wait for a period of time
after running sp_flushstats--is using WAITFOR DELAY going to
cause a problem? Do I need to wait a long time (given that
this is a very large DB)?
Would setting the log to truncate on checkpoint possibly
help?
Apparently there's been a problem and the regular DBCC's
that are supposed to have been happening haven't been. The
syslogs table has no problems show up with DBCC CHECKTABLE
or TABLEALLOC. How important is it to run the DBCC checks
before attempting to do a cross-platform dump (since we
assumed the regular checks had been running, we'd skipped
that step)?
Any ideas you can suggest would be most gratefully accepted.
Sun Solaris
Adaptive Server Enterprise/12.0.0.4/P/SWR 9973 ESD
1/Sun_svr4/OS 5.7/1784/64bit/FBO/Sat Dec 8 10:03:51 2001
Linux RH EL 3.0 w/ latest kernel: 2.4.21-32.0.1.ELsmp
Adaptive Server Enterprise/12.5.3/EBF 12151/P/Linux
Intel/Enterprise Linux/ase125x/1883/32-bit/OPT/Thu Nov 11
17:33:16 2004
(That's 12.5.3 ESD # 1, I believe; I haven't tried ESD #2
yet--I'm about to do that.)
Thanks for anything you can suggest.
John
Alpha-G Consulting, LLC
www.alphagconsulting.com

[unknown]

Mark,

Thanks for the comprehensive reply!

As to whether I'm sure no changes were being made during the
dump, I'm as sure as I can be. The DB was in dbo-only and
single-user mode. Since I issued the sp_flushstats,
checkpoint, and dump database commands in that mode, there's
just no way any other user could have made changes during
the DUMP. Now, could some ASE internal process have recorded
some changes? That I don't know.

I'll certainly try using quiesce database, but at what point
would you do that? After the sp_flushstats and the
checkpoint, I guess (since the checkpoint needs to be in the
log).

If that works, I'll be most disappointed that it wasn't in
the directions from Sybase :~\

Thanks!

John
Alpha-G Consulting, LLC
www.alphagconsulting.com

[unknown]

What finally worked was doing a DUMP TRANSACTION WITH
TRUNCATE_ONLY immediately before the DUMP DATABASE.

It's possible that something else might have worked, but
this did the trick: the final record in the transaction log
was a checkpoint so there wasn't any question.

Hope that helps someone else.

John

[unknown]

Attempting to use QUIESCE DATABASE and then DUMP DATABASE
results in absolutely nothing happening. You cannot dump a
quiesced database.

John

[Mark A. Parsons]

You're right. That's what happens when Mark's typing is out of sync
with what's bouncing around his few neurons (quiesce database vs
mount/unmount vs 'for external dump' vs 'standby_access').

'quiesce database' assumes you're going to make an OS copy of the
underlying devices, hence the reason to disable *ALL* activity on said
devices via the 'quiesce database' command.

--------------

What I meant to type/post ...

There's an option for the 'dump tran' command that'll dump up to a point
where there are no open transactions:

dump tran <db_name> to '....' with standby_access

(I believe this is available in ASE 12.0.x)

So what you'd want to look at doing is 'dump database', verify no
open/long running transactions in the source database (check the results
of 'sp_transactions'), then 'dump tran ... with standby_access'. This
should insure that no last-second open transactions end up in your tran
log dump.

Now go over to the target and 'load database', 'load transaction',
'online database'. There is a 'for standby_access' option to the
'online database' command but that's primarily to allow additional logs
to be loaded into the database (ie, 'for standby_access' will leave
database in a read-only state and no checkpoints will be issued, thus
allowing for follow-on logs to be loaded from another database),
probably not something you need to worry about.

--------------

The 'dump/load tran ... standby_acess' can also be used in conjunction
with a 'quiesce database'/OS_copy_devices procedure ... and that's where
my previous typing went awry. (The neurons were thinking 'OS copy of
device' while my fingers were saying 'dump database'.)

Post by unknown
Attempting to use QUIESCE DATABASE and then DUMP DATABASE
results in absolutely nothing happening. You cannot dump a
quiesced database.
John

[unknown]

Mark suggested using DUMP TRAN with the for-standby-access
option. I'm not sure how this fits in with the need to have
a DB dump that has a clean transaction log (so that the
cross-platform conversion doesn't run into problems).

But, here's what has worked for me: DUMP TRAN WITH
TRUNCATE_ONLY before setting the DB into single-user mode
and using sp_flushstats. This is a rather ham-fisted way to
deal with a problem log (to eliminate it), but since the
full migration is about to take place, it doesn't really
discard anything you need.

With the log truncated, after the cross-platform load,
ONLINE DATABASE doesn't seem to have any problems dealing
with the log (which just has the final checkpoint and
nothing else that represents modifications to the database
at all).

I hope this helps someone avoid the delays I've experienced
in what could be a simple process--but wasn't!

John
Alpha-G Consulting, LLC