Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2009
    Location
    India
    Posts
    50

    Unanswered: setting up replica of a Sybase Instance

    Hi All,

    I am new to sybase.

    I am having requirement to setup a new Test ASE instance(12.5) from Prod Instance(12.5.2). I have configured new Test instance locally & took dumps of all databases of Prod instance (i.e master, sybsystemprocs, dbccdb, user dbs etc).

    However, i am unable to load system & user databases from the dumps taken from Prod instance. Could you please help me out if there is any procedure to be followed for the above requirement.

  2. #2
    Join Date
    Mar 2008
    Posts
    96
    probably you are trying to load in normal startup mode try to start theASE server in master recover mode and then try to load the system databases. By the way there is no need to restore all the system databases on UAT. only some Tables need to BCP in in the UAT. Like restoring model is not required. Explain the whole scenario may be i can help u better

  3. #3
    Join Date
    Jun 2009
    Location
    India
    Posts
    50
    Hi All - Below is the detailed requirement.

    I am having a production ASE instance remote server(Version : 12.5.2). This does not have development ASE instance. Now, I have a requirement to setup a new development ASE instance, which is replica of production ASE instance, in local.

    Could you please provide me with steps to be performed to meet above requirement?

    PS: This is an urgent requirement, i am a new bee as a DBA
    Last edited by Neevarp; 10-29-09 at 09:18.

  4. #4
    Join Date
    Mar 2008
    Posts
    96
    Hi,
    Follow the below steps.
    1. Install the new ASE on ur dev m/c.
    2. On your production m/c take the dump of the user databases fire the following query in master database
    1> select db_name(dbid),segmap,size*(size of ur server page size)/1024,name from sysusages, sysdevices where dbid > 4 and vstart between low and high order by dbid
    2> go

    this will give you the information for each database. The segmap column will have values 3,4,7 or some higher no.4 means it is a log device,3 means it is a data device, 7 means it is mixed and higher value means it is a user defined segment. The size column will give u size of each fregment. The order of creating the fregment is very importatnt. You should alter your database on UAT in the same order as there is in production. the name column will give you the information about the device(its optional if u want the same device names on the UAT too).

    3. Now on production take the bcp out of the following table
    sysusages,syslogins,sysloginroles,sysdevices,syspr otects,sysdatabases

    4. Now go to your UAT. create the devices as per the information u got from the query in step 1. For example if result of the query shows a database have 5 devices 3 of size 10GB and 2 of size 2 GB. create 5 devices on UAT 3 of 10 GB and 2 of 5 GB.

    5. Now create your database as per the information u get in the query result.
    For example if the query shows that database a is created as below

    dbname segmap size device
    .................................................. .........
    a 3 10GB data1
    a 4 5gb log1
    a 3 5gb data2
    a 4 2GB log2
    a 3 5GB data2
    a 4 3GB log2
    .................................................. .........
    .................................................. ........
    .................................................. .....

    then u should create the database as
    create database a on data1='10G' log on log1='5G' for load
    go
    alter database a on data2 = '5G' log on log2 = '2G'
    go
    alter database a on data2 = '5G' log on log 2 = '3G'
    go

    and so on.

    6. Now load the database dump from prod to UAT.
    7. online the database
    8. bcp in the sylogin and sysloginroles in the UAT.

    now test ur UAT. I hope this will helpful.

    regards
    Ishu Srivastava

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Create a new ASE instance and use the same sizes as the original
    Then in order to restore master you need to start ASE in single user mode
    startserver -f RUN_yoursrv -m

  6. #6
    Join Date
    Jun 2009
    Location
    India
    Posts
    50
    Quote Originally Posted by pdreyer View Post
    Create a new ASE instance and use the same sizes as the original
    Then in order to restore master you need to start ASE in single user mode
    startserver -f RUN_yoursrv -m
    Hi pdreyer,

    I am getting below error, after loading master in single user mode & restarting ASE. Could you please suggest.

    00:00000:00001:2009/11/04 14:13:48.80 server Error: 7114, Severity: 22, State: 1
    00:00000:00001:2009/11/04 14:13:48.80 server Page 0 is not a valid text page.
    00:00000:00001:2009/11/04 14:13:48.80 kernel ************************************
    00:00000:00001:2009/11/04 14:13:48.80 kernel curdb = 1 pstat = 0x0 lasterror = 7114
    00:00000:00001:2009/11/04 14:13:48.80 kernel preverror = 0 transtate = 1
    00:00000:00001:2009/11/04 14:13:48.80 kernel curcmd = 0 program =
    00:00000:00001:2009/11/04 14:13:48.82 kernel pc: 0x00000000806ac4ac pcstkwalk+0x28(0x00000100003c6688, 0x00000100003c5e00, 0x000000000000270f, 0x0000000000000002, 0x0000000000000000)
    00:00000:00001:2009/11/04 14:13:48.82 kernel pc: 0x00000000806ac394 ucstkgentrace+0x1c8(0x000000000000270f, 0x0000000000070007, 0x0000000000000000, 0x00000100013b21c0, 0x0000010000c52208)
    00:00000:00001:2009/11/04 14:13:48.82 kernel pc: 0x000000008066c8d8 ucbacktrace+0xac(0x0000000000000000, 0x0000000000000001, 0x000000000000001e, 0x0000010000c661e4, 0x0000010000000000)
    00:00000:00001:2009/11/04 14:13:48.82 kernel pc: 0x00000000801c5e18 terminate_process+0xf80(0x0000010000c52208, 0xffffffffffffffff, 0x0000010000c59c40, 0x0000000000000000, 0x0000010000c59b38)
    00:00000:00001:2009/11/04 14:13:48.82 kernel pc: 0x000000008020a590 hdl_default+0x58(0x0000000000000047, 0x000000000000000e, 0x0000000000000016, 0x0000000000000001, 0x00000100003c6bf0)
    00:00000:00001:2009/11/04 14:13:48.82 kernel pc: 0x00000000802bcba0 text_handler+0x34(0x0000000000000047, 0x000000000000000e, 0x0000000000000016, 0x0000000000000001, 0x00000100003c6bf0)
    00:00000:00001:2009/11/04 14:13:48.82 kernel pc: 0x0000000080209f0c ex_raise+0x1d8(0x0000000000000016, 0x0000000000000001, 0x0000010000c59c40, 0x0000000000000000, 0x0000010000c59b38)
    00:00000:00001:2009/11/04 14:13:48.82 kernel pc: 0x00000000802b402c gettxtpage+0x310(0x00000000000000a4, 0x0000010000c528c8, 0x0000010002579ec0, 0x00000000802bcb6c, 0x00000000801c6bb4)
    00:00000:00001:2009/11/04 14:13:48.82 kernel [Handler pc: 0x00000000802bcb6c text_handler installed by the following function:-]
    00:00000:00001:2009/11/04 14:13:48.83 kernel pc: 0x00000000802c2abc txt_open+0x248(0x00000100003c7c60, 0x0000000000000000, 0xffffffffffffffff, 0xfffffffffffffff8, 0x0000000000000000)
    00:00000:00001:2009/11/04 14:13:48.83 kernel [Handler pc: 0x000000008020a538 hdl_default installed by the following function:-]
    00:00000:00001:2009/11/04 14:13:48.83 kernel [Handler pc: 0x000000008020a538 hdl_default installed by the following function:-]
    00:00000:00001:2009/11/04 14:13:48.83 kernel pc: 0x00000000801c6bb4 ld_sort_char+0x1f4(0x0000000000000000, 0x000000000002e6da, 0x0000000000000001, 0x0000000000000001, 0x0000000000000001)
    00:00000:00001:2009/11/04 14:13:48.83 kernel pc: 0x00000000801c3370 dsinit+0x360(0x00000000000077f8, 0x0000000000000000, 0x000000008103adc0, 0x0000000000003000, 0x000000000000a400)
    00:00000:00001:2009/11/04 14:13:48.83 kernel pc: 0x00000000806c016c _coldstart(0x0000000000000000, 0x00000000801c3010, 0x0000000000000000, 0x0000000000000000, 0x0000000000000000)
    00:00000:00001:2009/11/04 14:13:48.83 kernel end of stack trace, spid 1, kpid 458759, suid 0

  7. #7
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Is the original master OK i.e. no dbcc errors?

  8. #8
    Join Date
    Jun 2009
    Location
    India
    Posts
    50
    Yes, before taking dumps, i have run dbcc checkstorage. However, i have repeated the same process and tried to load master database again.

    Now I am getting below strange error "823". Also, now I am unable to connect to my ASE, as my master db had got overwritten. However, i reastarted ASE and generated new sa password.

    Just to confim, do we need to load even master db, while creating new ase instance? please confirm.

    1> load database master from "/sybdump/DR_2_SDS/master.db.dmp"
    2> go
    WARNING: In order to LOAD the master database, the SQL Server must run in single-user mode. If the master database dump uses multiple volumes, you must execute sp_volchanged on another SQL Server at LOAD time in order to signal volume changes.
    Backup Server session id is: 30. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
    Backup Server: 6.28.1.1: Dumpfile name 'master0930906D9E ' section number 1 mounted on disk file '/sybdump/DR_2_SDS/master.db.dmp'
    Backup Server: 4.58.1.1: Database master: 13830 kilobytes LOADed.
    Backup Server: 4.58.1.1: Database master: 15366 kilobytes LOADed.
    Backup Server: 4.58.1.1: Database master: 15374 kilobytes LOADed.
    Backup Server: 3.42.1.1: LOAD is complete (database master).
    Started estimating recovery log boundaries for database 'master'.
    Completed estimating recovery log boundaries for database 'master'.
    Started ANALYSIS pass for database 'master'.
    Completed ANALYSIS pass for database 'master'.
    Started REDO pass for database 'master'. The total number of log records to process is 1.
    00:00000:00000:2009/11/05 20:00:07.58 kernel sddone: read error on virtual disk 0 block 15364:
    00:00000:00000:2009/11/05 20:00:07.58 kernel sddone: 2048 bytes passed, 0 returned on read for virt disk 0 block 15364
    Completed REDO pass for database 'master'.
    Started filling free space info for database 'master'.
    Completed filling free space info for database 'master'.
    Started cleaning up the default data cache for database 'master'.
    Completed cleaning up the default data cache for database 'master'.
    00:00000:00008:2009/11/05 20:00:07.58 server Error: 823, Severity: 24, State: 1
    00:00000:00008:2009/11/05 20:00:07.58 server I/O error detected during read for BUF pointer = '0x10002808f38', MASS pointer = '0x10002808f38', (Buf#: '0'), page ptr = '0x10002740800', dbid = '1', Mass virtpage = '15364', Buffer page = '0', Mass status = '0x908', Buffer status = '0x1', size = '2048', cache (id: 0) = 'default data cache', Pinning Xdes = '0x0', spid = '0'.

    00:00000:00008:2009/11/05 20:00:07.58 kernel ************************************
    00:00000:00008:2009/11/05 20:00:07.58 kernel SQL causing error : load database master from "/sybdump/DR_2_SDS/master.db.dmp"

    00:00000:00008:2009/11/05 20:00:07.58 kernel ************************************
    00:00000:00008:2009/11/05 20:00:07.77 server SQL Text: load database master from "/sybdump/DR_2_SDS/master.db.dmp"
    00:00000:00008:2009/11/05 20:00:07.77 kernel curdb = 1 tempdb = 2 pstat = 0x1010000
    00:00000:00008:2009/11/05 20:00:07.77 kernel lasterror = 823 preverror = 2812 transtate = 0
    00:00000:00008:2009/11/05 20:00:07.77 kernel curcmd = 316 program = isql
    00:00000:00008:2009/11/05 20:00:07.80 kernel pc: 0x00000000808c4554 pcstkwalk+0x28(0x000001000046e298, 0x000001000046da10, 0x000000000000270f, 0x0000000000000002, 0x0000000000000000)
    00:00000:00008:2009/11/05 20:00:07.80 kernel pc: 0x00000000808c443c ucstkgentrace+0x1c8(0x000000000000270f, 0x00000000000b000b, 0x0000000000000000, 0x00000100015dd090, 0x0000010000e06e30)
    00:00000:00008:2009/11/05 20:00:08.07 kernel pc: 0x0000000080870a9c ucbacktrace+0xb0(0x0000000000000000, 0x0000000000000001, 0x000000000000001e, 0x0000010000e1af44, 0x0000010000000000)
    00:00000:00008:2009/11/05 20:00:08.08 kernel pc: 0x000000008024b6ec terminate_process+0xfb0(0x00000000810e4400, 0xffffffffffffffff, 0x0000010000e0e9e8, 0x00000000802a4788, 0x0000010000e0e778)
    00:00000:00008:2009/11/05 20:00:08.08 kernel pc: 0x00000000802a479c hdl_default+0x58(0x0000000000000008, 0x0000000000000017, 0x0000000000000018, 0x0000000000000001, 0x000001000046e920)
    00:00000:00008:2009/11/05 20:00:08.39 kernel pc: 0x000000008056e8a8 s_handle+0x11d4(0x0000000000000008, 0x0000000000000018, 0x0000000000000001, 0x0000000000009c00, 0x0000000000001000)
    00:00000:00008:2009/11/05 20:00:08.39 kernel pc: 0x00000000802a4114 ex_raise+0x1e0(0x0000000000000018, 0x0000000000000001, 0x0000000000000000, 0x0000010000e0e8f8, 0x0000010000e0e778)
    00:00000:00008:2009/11/05 20:00:08.39 kernel pc: 0x00000000802197cc bufread+0xa74(0x0000000000000000, 0x0000000000000000, 0x0000000000000000, 0x00000000818da400, 0x0000000081841c00)
    00:00000:00008:2009/11/05 20:00:08.71 kernel [Handler pc: 0x00000000804ea9f8 ut_handle installed by the following function:-]
    00:00000:00008:2009/11/05 20:00:08.71 kernel pc: 0x000000008046cf60 disk_do_refit+0x6c0(0x0000000000000018, 0x0000000000009cb8, 0x0000000001499f5e, 0x000001000046ecb4, 0x0000000000003c04)
    00:00000:00008:2009/11/05 20:00:08.71 kernel [Handler pc: 0x00000000806d06d4 lddb_handle installed by the following function:-]
    00:00000:00008:2009/11/05 20:00:08.71 kernel pc: 0x00000000806cd7d8 lddb_main+0x944(0x000001000046f414, 0x0000000000000000, 0x00000100016523d8, 0x0000000000000001, 0x00000000810e4400)
    00:00000:00008:2009/11/05 20:00:09.03 kernel pc: 0x00000000801c4d14 s_execute+0x3de0(0x0000000080000000, 0x0000000081176188, 0x0000000000000001, 0x0000000000007400, 0x0000010000e0e4fb)
    00:00000:00008:2009/11/05 20:00:09.03 kernel [Handler pc: 0x000000008056d6d4 s_handle installed by the following function:-]
    00:00000:00008:2009/11/05 20:00:09.03 kernel pc: 0x0000000080215b68 sequencer+0x11a8(0x00000000000076b8, 0x0000000000001000, 0x0000010000e0e880, 0x0000010000e0e4e8, 0x0000000000001800)
    00:00000:00008:2009/11/05 20:00:09.03 kernel pc: 0x000000008027e674 tdsrecv_language+0xcc(0x0000000000000000, 0x0000000000000001, 0x0000010000e06e30, 0x0000000000000021, 0x0000010000e10b70)
    00:00000:00008:2009/11/05 20:00:09.35 kernel [Handler pc: 0x00000000802a438c hdl_backout installed by the following function:-]
    00:00000:00008:2009/11/05 20:00:09.35 kernel [Handler pc: 0x00000000804ea9f8 ut_handle installed by the following function:-]
    00:00000:00008:2009/11/05 20:00:09.35 kernel [Handler pc: 0x00000000804ea9f8 ut_handle installed by the following function:-]
    00:00000:00008:2009/11/05 20:00:09.35 kernel pc: 0x00000000801fdb6c conn_hdlr+0x2444(0x00000000818e0c00, 0x0000000000007800, 0x0000010000e10ba4, 0x0000000000007400, 0xffffffffffffffff)
    00:00000:00008:2009/11/05 20:00:09.68 kernel end of stack trace, spid 8, kpid 720907, suid 1
    Msg 823, Level 24, State 1:
    Server 'DR_TEST_SDS', Line 1:
    I/O error detected during read for BUF pointer = '0x10002808f38', MASS pointer = '0x10002808f38', (Buf#: '0'), page ptr = '0x10002740800', dbid = '1', Mass virtpage = '15364', Buffer page = '0', Mass status = '0x908', Buffer status = '0x1', size = '2048', cache (id: 0) = 'default data cache', Pinning Xdes = '0x0', spid = '0'.
    The SQL Server is terminating this process.
    Last edited by Neevarp; 11-05-09 at 10:46.

  9. #9
    Join Date
    Feb 2009
    Location
    Prague
    Posts
    17
    Just to confim, do we need to load even master db, while creating new ase instance? please confirm.


    I don't think we need but it depends on what you want to do. I'm, usually building replica of production ASE for testing and development purposes or because of migration to another box. I do it usually like this

    1) Prepare devices for master, sybsystemprocs and sybsystemdb(if required)
    2) create new ASE with same master and sybsystemprocs databases as the old ase (but it usually does not matter much)
    3) replace default configuration files with those that are on production
    4) create other devices in ASE (if using raw you must have prepared them in Unix)
    5) create user databases same as on the production, alter tempdb database to the size of the production one
    6) bcp out syslogins and sysloginroles tables from production and bcp it in the replica (because of logins synchronisation).
    7) load the user databases
    Last edited by jannovak; 11-06-09 at 10:06.

  10. #10
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    1 Error 823 is a hard error. Could be a Endian problem. Are Prod and Test one the same platform, if you are dump/loading across plat forms, you need a Cross Platform dump, read up on it.

    2 I agree with Jan. You EITHER build a new Test server and load user (not system) dbs as described in post 9 OR you load master, etc. If you load master, then afterwards, you have to correct all the device paths, before you load the user dbs.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  11. #11
    Join Date
    Jun 2009
    Location
    India
    Posts
    50
    Hi All - Thanks a lot for ur help.

    Actually, the dumps that i had taken are SunOS 5.8, Sybase ASE 12.5.2. I am loading these dumps to SunOS 5.10, Sybase ASE 12.5.2.

    I had followed all these above procedures except loading master db ( as i had to create test instance) & issue is now resolved.

    I faced problem while loding the dumps, below is error.

    00:00000:00014:2009/11/05 19:06:58.41 kernel Cannot send, host process disconnected: DR_TEST_SDS spid: 14
    00:00000:00014:2009/11/05 19:06:58.41 server Error: 7215, Severity: 18, State: 1
    00:00000:00014:2009/11/05 19:06:58.41 server Can't send to site 'SYB_BACKUP'.
    00:00000:00014:2009/11/05 19:06:58.45 server Failed to connect to remote server DR_TEST_SDS_BS tcp ether axews89 5001 for site SYB_BACKUP. Trying the next query entry of this server, if any.
    00:00000:00014:2009/11/05 19:06:58.45 server Error: 7235, Severity: 17, State: 0
    00:00000:00014:2009/11/05 19:06:58.45 server No other query entry found for the remote server DR_TEST_SDS_BS - site SYB_BACKUP. Aborting this site handler, as no remote server responded. Please make sure the remote server is accepting connections.


    I have checked th entries of backup server in sysservers table & interfaces file, which were correctly written. However, after replacing the hostname with IP address in interfaces file , site handler took connects & load database was successful. But, as per our discussion in forum http://www.dbforums.com/sybase/16494...ml#post6432202 we need to write machine name instead of IP address. Ho w do resolve this error, without upding interfaces file?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •