When speaking of moving/copying database between dataservers, database “dump and load” strategy usually come up in mind. Thanks to mount, unmount and quiesce database commands, you can sometimes replace advantageously “dump/load” operations.
1°) Moving a database:
I assume here we want to move completely the database DBLA01 from source dataserver SYBPARFRDEV01_DS to destination dataserver SYBPARFRDEV02_DS. Both dataservers are on the same machine. Database DBLAB01 has it’s own devices.
Let’s connect to the source dataserver SYBPARFRDEV01_DS, and unmount the database DBLAB01:
select @@servername go unmount database DBLAB01 to '/home/sybase/tmp/DBLAB01.manifest' go
The file /home/sybase/tmp/DBLAB01.manifest is called a manifest file. It is a file that contains data informations about devices (name, phyname, size etc…) and database (dbid, dbname etc…). The manifest file does NOT contain any databases or devices.
Once the command is completed, the devices files are not used anymore by ASE. The database and it’s devices have been removed from systems tables (sysdatabases, sysdevices and sysusages).
Now, stay connected to source dataserver SYBPARFRDEV01_DS and get the devices files list stored in the manifest file before to move them to the SYBPARFRDEV02_DS devices location:
select @@servername go mount database all from '/home/sybase/tmp/DBLAB01.manifest' with listonly go
It shows:
'/srv/sybase/SYBPARFRDEV01/dbf/dat/DBLAB01_DAT_01.dat'='DBLAB01_DAT_01'
'/srv/sybase/SYBPARFRDEV01/dbf/log/DBLAB01_LOG_01.dat'='DBLAB01_LOG_01'
As we can see, the manifest file contains informations about the original devices names and location. But destination dataserver SYBPARFRDEV02_DS has a different device location:
/srv/sybase/SYBPARFRDEV02/dbf/dat/
Obviously we will need to change the devices location to fit the destination dataserver installation.
But for now we can move the database DBLAB01 devices to their new location:
cd /srv/sybase/SYBPARFRDEV01/dbf/dat/ mv DBLAB01_DAT_01.dat /srv/sybase/SYBPARFRDEV02/dbf/dat/ cd /srv/sybase/SYBPARFRDEV01/dbf/log/ mv DBLAB01_LOG_01.dat /srv/sybase/SYBPARFRDEV02/dbf/log/
We can reallocate devices with the “using” clause while mounting the DBLAB01 database. We can even rename the physical names of devices, but logical names of devices cannot be changed.
Now let’s connect to the destination dataserver SYBPARFRDEV02_DS, and reallocate the devices while mounting database LABDB01 (we replace SYBPARFRDEV01 with SYBPARFRDEV02 in the devices physical names):
select @@servername go mount database all from '/home/sybase/tmp/DBLAB01.manifest' using '/srv/sybase/SYBPARFRDEV02/dbf/dat/DBLAB01_DAT_01.dat'='DBLAB01_DAT_01', '/srv/sybase/SYBPARFRDEV02/dbf/log/DBLAB01_LOG_01.dat'='DBLAB01_LOG_01' go
ASE will produce an output similar to “load database” while mounting the database:
Started estimating recovery log boundaries for database 'DBLAB01'.
Database 'DBLAB01',checkpoint=(6408, 13),first=(6408, 13),last=(6409, 1).
Completed estimating recovery log boundaries for database 'DBLAB01'.
Started ANALYSIS pass for database 'DBLAB01'.
Completed ANALYSIS pass for database 'DBLAB01'.
Started REDO pass for database 'DBLAB01'. The total number of log records to process is 78.
Redo pass of recovery has processed 6 committed and 1 aborted transactions.
Completed REDO pass for database 'DBLAB01'.
Recovery of database 'DBLAB01' will undo incomplete nested top actions.
Started recovery checkpoint for database 'DBLAB01'.
Completed recovery checkpoint for database 'DBLAB01'.
Started filling free space info for database 'DBLAB01'.
Completed filling free space info for database 'DBLAB01'.
Started cleaning up the default data cache for database 'DBLAB01'.
Completed cleaning up the default data cache for database 'DBLAB01'.
MOUNT DATABASE: Completed recovery of mounted database 'DBLAB01'.
Once the database is mounted with no errors, you want to bring it online:
online database DBLAB01 go
It shows:
Database 'DBLAB01' is now online.
2°) “Cloning” or copying a database:
I assume here we want to copy (clone) completely the database DBLA01 from source dataserver SYBPARFRDEV01_DS to destination dataserver SYBPARFRDEV02_DS.
Both dataservers are on the same machine. Database DBLAB01 has it’s own devices.
Let’s connect to the source dataserver SYBPARFRDEV01_DS, and quiesce the database DBLAB01:
quiesce database dblab_tag hold DBLAB01for external dump to '/home/sybase/tmp/DBLAB01.manifest' go
Now, stay connected to the source dataserver SYBPARFRDEV01_DS and get the devices files list stored in the manifest file before to copy them to the SYBPARFRDEV02_DS devices location:
select @@servername go mount database all from '/home/sybase/tmp/DBLAB01.manifest' with listonly go
It shows:
'/srv/sybase/SYBPARFRDEV01/dbf/dat/DBLAB01_DAT_01.dat'='DBLAB01_DAT_01'
'/srv/sybase/SYBPARFRDEV01/dbf/log/DBLAB01_LOG_01.dat'='DBLAB01_LOG_01'
Here quiesce database used with the hold keyword suspends all updates to the specified database. Transactions cannot update data in suspended databases, and background tasks such as the checkpoint process and housekeeper process skip all databases that are in the suspended state. So we can safely copy the database devices to their new location:
cd /srv/sybase/SYBPARFRDEV01/dbf/dat/ cp -p DBLAB01_DAT_01.dat /srv/sybase/SYBPARFRDEV02/dbf/dat/ cd /srv/sybase/SYBPARFRDEV01/dbf/log/ cp -p DBLAB01_LOG_01.dat /srv/sybase/SYBPARFRDEV02/dbf/log/
When copy is done, connect to the source dataserver SYBPARFRDEV01_DS and release the database DBLAB01 to resume updates:
quiesce database dblab_tag release
Now connect to the destination dataserver SYBPARFRDEV02_DS and reallocate the devices while mounting database LABDB01 (we replace SYBPARFRDEV01 with SYBPARFRDEV02 in the devices physical names):
select @@servername go mount database all from '/home/sybase/tmp/DBLAB01.manifest' using '/srv/sybase/SYBPARFRDEV02/dbf/dat/DBLAB01_DAT_01.dat'='DBLAB01_DAT_01', '/srv/sybase/SYBPARFRDEV02/dbf/log/DBLAB01_LOG_01.dat'='DBLAB01_LOG_01' go
Then bring the database DBLAB01 online:
online database DBLAB01 go
It shows:
Database 'DBLAB01' is now online.
3°) Limitations and Restrictions:
- the page size in the source and the destination dataserver must be the same.
- destination and source dataservers must use the same version as the manifest file.
- NOT suitable for cross-plateform migration !
- dbid must be the same on the source and the destination dataserver.
- you can’t unmount system databases (except sybsystemprocs).
- you can’t unmount proxy databases or user-created temporary databases.
- use of the mount and unmount commands within a transaction is NOT possible.
- you can’t mount a database on a dataserver with high availability (HA) setup.
- you can’t have more than 8 databases in a mount or unmount command.
- you can’t have databases and devices with the same names on the destination dataserver.
- a database with a different character set can be mounted only if the sort order is binary.
- only all databases and their devices in the manifest file must be mounted together.
- databases can’t contain any cross database references/constraints.
- have enough devices configured at the destination dataserver to mount databases.
