Unmounting and Mounting a SYBASE database

[Former Member]

Hello Everyone,

We have 2 instances with some set of databases in each instances . ( Both are with same version and running on same host 15.7 ) .

We would like to move database named as "QWE" (DBID is 6 ) from instance1 to instance2.

But in instance2 , We have set of  databases and one of DB is assigned with DBID as 6.  Now as i mentioned i would like to move database "QWE" from instance1 to instance2.

I read in manuals as "In destination server DBID slot should be free i.e here DBID 6 ) . But in my case database exists with DBID = 6 in instance2 .

How to UNMOUNT and MOUNT database with new DBID or How to change the DBID of existing database ?.

Please advise on this

[Former Member]

Excellent points Mark. However, "should not exist" is something that I can corroborate.

Testing and feddback from our colleagues, which is sometimes scarce, might confirm wether or not DBCC CHECKALLOC consistently fixes/reconciles the dbid issue.

I would try my approach. Straighforward, and you are in the driver's seat.

Regards,

Jean-Pierre

[Mark_A_Parsons]

Unfortunately Paul has not (yet) provided details on where in the manuals he found the statement: "In destination server DBID slot should be free" (and I'm too lazy to go do a brute force seaerch), so I can't be 100% sure what dbid issue he's referring to; but from my perusal of the 'mount' command I'm about 98% sure he's referring to the issue of the 'wrong' dbid referenced in his allocation pages after mounting to a different dataserver.

Assuming Paul is referring to the allocation pages having the 'wrong' dbid ... your 'approach' has nothing to do with the issue raised by Paul.

--------- db move/copy method

A - Paul is moving a database between dataservers using the unmount/OS-level-copy-of-db-devices/mount method.

B - Paul is *not* using a 'dump/load database' method.

--------- the issue

A - The OS-level copy (cp/scp/rcp/ftp) has no concept of an allocation page let alone how to update the dbid value stored in allocation pages; the OS-level copy is just performing a raw copy of the sybase device(s); net result is that upon attempting to online the database in the new dataserver, an error will occur when the dataserver processes the newly mounted allocation pages and finds said allocation pages reference a dbid that's already assigned to some other database.

B - If he was using a 'dump/load database' method then he would not be running into this particular issue since one of the benefits of the 'load database' command is the fact that the allocation pages in the newly loaded database will be updated to reference the correct dbid value.

--------- the fix

A - Prior to onlining a database we need a means of updating the allocation pages (of the newly mounted, but not yet onlined database) with the correct dbid; from the documentation on the 'mount' command (see my previous post- above) it appears the 'dbcc checkalloc' command is what we're looking for, and this would make sense in that we're trying to fix an allocation page issue; unfortunately the documentation isn't as clear as it could be on this topic (eg, maybe the docs should say something like: "after mounting a database, run dbcc checkalloc (w/ fix?) to ensure allocation pages are updated with the correct dbid, then online the database" ... ?)

B - If using the 'dump/load database' command, this issue shouldn't arise so there should be no need to update the allocation pages with a new dbid.

--------- confirmation

A - I haven't tested this solution (yeah, I'm lazy), but Juergen has already posted that this is exactly what he's had to do in the past (ie, mount, dbcc checkalloc, online); should be easy enough for Paul to run his own test to (in)validate this solution.

B - I don't see anything in your approach that addresses how to update allocation pages with the 'correct' dbid (assuming Paul wishes to continue using the mount/unmount solution); in fact, one part of your approach ... update sysdatabases set dbid = <somebignumber> ... is going to introduce a good bit of corruption into the database if simply because a) there's no corresponding update of the associated rows in master..sysusages and b) there's no indication how to update the associated allocation pages with the new dbid = <somebignumber>.

--------- once Paul's got his db mounted on the new dataserver ...

Paul will *then* need to address any cross database reference issues, eg ...

     sysusers/sysroles <--> master..syssrvroles

     sysusers/sysalternates <--> master..syslogins

     proxies <--> master..sysattributes/sysservers

     syscolumns.encr* <--> key db (column level encryption)

     sysreferences <--> other_db..sysobjects (cross db RI constraints)

     procs/triggers/views/SQL-code that have references to other dbs

... but then these are concerns whenever bringing a database up in a different dataserver regardless of how the database was copied/loaded.

[Former Member]

Mark,

Sorry it took me this long to reply. Excellent analysis.

I understand that Paul is trying to move a database using the relatively new unmount/mount option ASE 12.5? and as far as that is concerned, yes, you and Juerguen are 100% on, provided Paul again confirms that the DBCC CHECKALLOC command reconciles the dbid in the allocation page.

Otherwise, I think my approach, using the well documented and reliable dump and load approach would work, if you update the dbids as I suggested. I would test it, but I don't have an environment yet. Like I said, I am going to build one on the Amazon cloud. 

Finally, I never suggested a hybrid approach... LOL. That would apples and oranges he? However, it was good of you to mention it just to rule it out.

Regards,

Jean-Pierre

P.S. Hopefully, we will get some feedback from Paul!

[Former Member]

Hello All, Thanks for your valuable answers on this topic .

Our application team didn't agree to go with mount and unmount.

During the testing part ,

I have tried to unmount and mount couple of user database and faced couple of issues

1) I tried to unmount and mount on same server , But i moved few database file to different location and while mounting i have used the parameter "using" to point that new location of database file .

This method is NOT SUCCESSFUL. It got failed ( Error message : Device is in use for other device - But the device is not shared across any database /database server)

2) I have this doubt . Consider i have 9 databases. Database 8 and 6 are running with 10 devices each . But one device is shared across these two( 8,6) database    . How mount and unmount will work . I read as while unmounting a database it drops all devices associated for that database  from that DB server and allows to mount on other server . But in this case it's shared across 2 databases . How it will work .

[Former Member]

Greetings,

First of all, I believe that the dbid is only an issue when:

1. You have views, triggers and procedures and perhaps other new objects in other databases, refferencing objects in this database. In other words, for cross database dependencies...

2. As far as the mapping of disk space for a database on a device through sysusuages. e.g. there is a join between sysdatabases, sysusages, and sysdevices...

In any case, In all my years of working with Sybase ASE I have never heard of DBCC CHECKALLOC fixing this kind of issue. It is all a manual process, or, something you can automate in shell and T-SQL scripts.

So, the only way to copy a database from one server to another using backups e.g. dump at the source and load at the target, is to:

1. Create the database on the target server with the same dbid, and the same or very similar

    mapping of sysdatabases, sysdevices, sysusages (the only way to really garantee a succedssful

    load)

OR

2. Perform some manual reconciliation. The following like a clear and simple option:

     In the source server, temporarily update sysdatabases.dbid and set it to the dbid of the

     database you created at the target server. Dump the database, ftp the file, load at the target.

     Voila!

     If this is not possible, and to prevent any dbid clashes, create the database at the target server,

     and bump up its dbid to a very large number not in use at the source or the target. Then bump the

     dbid in similar fashion at the source, dump, ftp, load.

     This will require updating the system tables in master on both servers:

      exec sp_configure "allow udpates", 1

      go

      update master..sysdatabases.dbid = "Some large numberto beequal on both servers"

      go

      exec sp_configure "allow udpates", 0

      go

I hope this helps.

Regards,

Jean-Pierre

[hans-juergen_schwindke]

Hi,

please check whether dbcc checkalloc (dbname) will reconcile the dbids. When I used the unmount & mount functionality checkalloc had to be used because of the dbids. It is executed before you set the database online.

dbcc checkalloc (prod)

go

online database prod

go

Best regards,

Juergen

[hans-juergen_schwindke]

additional comment:

As Mark wrote the dbid is stored on allocation pages (kind of management page for a group of 256 pages). So further unmounts will not work if the dbid has not been reconciled.