Archive for November, 2009

Transportable Databases

November 8th, 2009 No comments

Very Useful feature to migrate the database between two Production Server, for creating a HA  Server or DR Server.

It can migrate the database between different version where in place upgrade is not possible.

Concept :

The current version of ASE 12.5.4 or possibly earlier on ones allow us to use a Sybase utility called transportable databases to move database at device level between servers. In this scenario, databases technically freeze(by quieseing), the underlying device become stagnant and copies of devices can be made.

In the heart of this operation lies a Sybase utility that allow us to create a binary file called as menifest file that has all the details of devices, the databases  and their layout.

Testing :

Suppose we have two devices on file system(as opposed to raw partitions).  These devices were created in Sybase on ASE 12.5.4 on server called SYB_DS1. They were called SYB_DS1_data01.dat and SYB_DS1_log01.dat respectively.
On these two devices I have created two databases transportable1 and transportable2.

Now I logged in SYB_DS1 server and quiesced these two databases and created a menifest file.
1> QUIESCE DATABASE transport HOLD transportable1,transportable2 FOR EXTERNAL DUMP TO  ’/var/tmp/menifest_file’  WITH OVERRIDE

Note : The manifest file is called as /var/tmp/manifest_file.

Now copy above mentioned these two devices to new location with new name for the other server SYB_DS2 on the same host with new name SYB_DS2_data01.dat and SYB_DS2_log01.dat.

Now  Mount these database from two devices on other Sybase Server SYB_DS2. This SYB_DS2 can be ASE 15.0.

Logged in SYB_DS2, First get the information out from menifest file.

1> MOUNT DATABASE ALL FROM ‘/var/tmp/menifest_file’ WITH LISTONLY

Now mount the new copied devices with menifest file in SYB_DS2.

1> MOUNT DATABASE ALL FROM ‘/var/tmp/menifest_file’ using

It will give long output with successful message as

MOUNT DATABASE: Completed reovery of mounted datbase ‘transportable1′.
MOUNT DATABASE: Completed reovery of mounted datbase ‘transportable2′.

Both the databases would be in offline in SYB_DS2. You need to bring them online by online database comand.
If the SYB_DS2 is ASE15 server, it will upgrade the database.

Happy  Learning!

Syncing the Replicated DB with Primary DB in warm standby setup!

November 7th, 2009 No comments
Following steps  we can use for syncing the replicated database  from the primary database in warm standby setup.
PDS – Primary Dataserver
PDB – Primary Database
RDS – Replicated Dataserver
RDB – Replicated Database in RDS
Step #1
Put database  PDB in dbo use only
Clean up all logins besides systems, ( terminate all user processes )
Step #2
Quiese the replication server.
isql -Usa -Ppasswd -SRS
1> admin quiesce_check
2> go
Replication Server RS is Quiesced
Step #3
Stop the rep agent thread of Primary Database.
isql -Usa -Ppasswd -SPDS
use PDB
sp_stop_rep_agent PDB
Step #4
Suspend the connection to Primary database. Login into the Replication Server and issue the following command.
isql -Usa -Ppasswd -SRS
suspend connection to PDS.PDB
Step #5
Ignore the secondary transaction point in Primary Database.
isql -Usa -Ppasswd -SPDS
use PDB
dbcc settrunc(‘ltm’, ‘ignore’)
Step #6
Truncate the tran log and reset the locator value  to zero. Please only execute if the above step was successful.
isql -Usa -Ppasswd -SPDS
dump tran PDB with truncate_only
Issue this command in RSSD database.
rs_zeroltm PDS, PDB
Step #7
Dump the primary database.
isql -Usa -Ppasswd -SPDS
dump database PDB to ‘/dumpfs/PDB.dmp’
Step #8
Transfer the dump to RDS and load the RDB.
isql -Usa -Ppasswd -SRDS
load database RDB from ‘/sybase/RDS/dumpfs/PDB.dmp’
Step #9
Valid the secondary tran point  in PDS.
isql -Usa -Ppasswd -SPDS
use PDB
dbcc settrunc(‘ltm’, ‘valid’)
Step #10
Start the repagent for PDB in PDS.
isql -Usa -Ppasswd -SPDS
use PDB
sp_start_rep_agent PDB
Step #11
Resume the connection from the PDS.PDB in Replication Server.
isql -Usa -Ppasswd -SRS
resume connection to PDS.PDB
Step #12
Remove DBO use only from db.