Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2003
    Posts
    10

    Unanswered: Adding more storage to a Sybase database

    Can someone give me a summary of how to do this on Sybase ASE under Solaris or point me to an FAQ?

    (I don't need help with the UNIX aspects...just how to add storage to a particular Sybase database that doesn't have enough space for a new index...)

    Is it as straight forward as adding a device and then using "ALTER DATABASE"?

    Thanks!

  2. #2
    Join Date
    Sep 2002
    Location
    Germany, near Aachen
    Posts
    120

    Re: Adding more storage to a Sybase database

    Is it as straight forward as adding a device and then using "ALTER DATABASE"?
    Yes, thats all you have to do.

  3. #3
    Join Date
    Feb 2003
    Posts
    10

    Re: Adding more storage to a Sybase database

    Originally posted by Bernd Dulfer
    Yes, thats all you have to do.
    Well, I just installed Sybase Central...so this might make life easier.

    Soooo....

    (1) Add Database Device here
    (2) Drop database into single user mode
    (3) Click Database properties
    (4) Add new device under Devices tab
    (5) Apply changes
    (6) Bring database back up into multiuser

    That's really all I have to worry about? I could even get to like Sybase ;-)

    Thanks muchly.

  4. #4
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    I don't think you even need the singleuser mode, although it couldn't hurt to do so.
    Thanks,

    Matt

  5. #5
    Join Date
    Feb 2003
    Posts
    10
    Originally posted by MattR
    I don't think you even need the singleuser mode, although it couldn't hurt to do so.
    My next window for trying this is tonight....so there won't actually be any users on the database.

    There's really no gotchas with this? I've been playing in Sybase Central on my laptop database and it seems straightforward.

    Is there a way to equate the GUI functionality of Sybase Central with commandline?

    Thanks! :-)

  6. #6
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Yes, ASE is completely controllable via stored procedures and custom T-SQL. Sybase Central simply fires off SQL to ASE (well, it probably uses CIS to accomplish the same thing). I don't even have Sybase Central installed -- I can admin all my servers (we don't have *too* many) via SSH.

    The ASE Docs are here.

    Database device init is here.

    Have you previous experience in database administration?
    Thanks,

    Matt

  7. #7
    Join Date
    Feb 2003
    Posts
    10
    Originally posted by MattR
    Have you previous experience in database administration?
    Thanks Matt.

    I'm a UNIX guy....but I have a little bit of experience managing MySQL and Oracle from a systems point of view.

    If I do this from Sybase Central, are there many risks involved? I note the Sybase docs suggest dumping the master AFTER running disk init.

  8. #8
    Join Date
    Aug 2002
    Location
    Madrid, Spain
    Posts
    97

    Re: Adding more storage to a Sybase database

    Originally posted by geekything
    Well, I just installed Sybase Central...so this might make life easier.

    Soooo....

    (1) Add Database Device here
    (2) Drop database into single user mode
    (3) Click Database properties
    (4) Add new device under Devices tab
    (5) Apply changes
    (6) Bring database back up into multiuser
    There's no need to drop and re-load the database.
    Just create the database device and assign it to
    the database. Both actions may take some time.
    Be careful: once the device is added to the database,
    the action cannot be reversed.

    Single user mode is not needed, either.

    You may feel safer taking a database dump before.

    Regards,
    Mariano Corral

  9. #9
    Join Date
    Feb 2003
    Posts
    10

    Re: Adding more storage to a Sybase database

    Originally posted by corral
    There's no need to drop and re-load the database.
    Just create the database device and assign it to
    the database. Both actions may take some time.
    Be careful: once the device is added to the database,
    the action cannot be reversed.

    Single user mode is not needed, either.

    You may feel safer taking a database dump before.
    You're referring to dumping the master after disk init, or dumping the database I'm adding storage to? (I kinda planned on doing both as I'm a bit paranoid).

    Also, I noticed the docs mention putting data and log on different database devices...so I was planning on doing this, even though the two devices would be on one physical drive...

    Thanks to all for the response. Sorry if I seem a bit of a neophyte...this was kind of dumped (no pun intended) on me ;-)

    In real world terms, how risky is adding storage? I understand that docs tend to err on the side of caution (re: dumping master)....but is it really at all likely things will go awry?
    Last edited by geekything; 02-10-03 at 15:13.

  10. #10
    Join Date
    Aug 2002
    Location
    Madrid, Spain
    Posts
    97
    The user database should be dumped *before*
    adding a new device. So, if something goes
    wrong you may drop the database, create
    with its original device layout and load from
    that dump file.

    master should be dumped *after* adding
    a new device, because the device and its
    allocation to the user database are recorded
    in master's sysdevices and sysusages tables.
    If you ever need to restore the master
    database, it's important to use a dump
    containing the most recent changes to
    the devices. So, it's just a matter of keeping
    a up-to-date dump of master. If you dump
    it weekly, you may simply rely on your
    weekly dump, unless you're afraid you could
    need master's dump sooner.

    There's no ASE-specific hazard with adding
    new devices. However, I'm very afraid with
    typos, because the device cannot be changed
    once added to the database.

    About separate data and log, since you already
    have a database, the key point is whether it
    already has separate data and log, or not. If
    they are separate, the new device is added as
    data or log, depending on the alter database syntax:
    alter database MYDB on MYDEVICE=... /* Data */
    alter database MYDB log on MYDEVICE=... /* Log */
    I'm not sure, but I think that, if data and log are
    mixed, the new device is added with data and log
    mixed, too.

    If you have only a device assigned to the database
    (with mixed data and log, by force) and plan to add
    a new device and also to separate data and log, I suggest
    dedicating the new device to log and the old to data.
    Or, if you need to enlarge the data space, add first a
    new device for extra data and then a third device for log.
    Just after adding the device(s), both will have mixed data
    and log, so you'll need to remove logsegment from the
    first, and default and system from the second (or third)
    with three sp_dropsegment commands. sp_dropsegment
    only declares the segment layout for new allocations, but
    it doesn't move pages out of existing devices. No problem:
    your new device(s) is empty and your old device simply
    contains some log pages which ASE will clear as they age
    and the log is truncated.

    Regards,
    Mariano Corral

  11. #11
    Join Date
    Feb 2003
    Posts
    10
    Thanks muchly.

  12. #12
    Join Date
    Feb 2003
    Posts
    10
    So now I'm trying to backup the master....and Backup Server seems to barf with the following:

    1> dump database master to "tapedump3"
    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: 32. Use this value when executing the
    'sp_volchanged' system stored procedure after fulfilling any volume change
    request from the Backup Server.
    Backup Server: 8.5.1.1: Warning, unable to open device configuration file
    /sybase/backup_tape.cfg for reading. Operating system error No such file or
    directory.
    Backup Server: 8.15.1.1: Device /dev/rmt/0hn: not found in tape configuration
    file. Tape configuration will be attempted.
    Backup Server: 8.10.2.1: Device /dev/rmt/0hn: Not found in configuration file
    /sybase/backup_tape.cfg. INIT needs to be specified to configure the device.
    Msg 8009, Level 16, State 1:
    Line 1:
    Error encountered by Backup Server. Please refer to Backup Server messages for
    details.


    I've added the tape device ("tapedump3") within ASE.....what is this "INIT" that is referred to?!

    I'm reading the manuals as I go...

    Any pointers appreciated. Can I dump the master to disk with the following:

    dump database master to /path/to/disk

    And still reliably restore from that? It seems to write a file, but I wondering if I actually need to create a dump device?

    Thanks muchly!

  13. #13
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    I am not familliar with tape devices but disk dumps are quite easy and what we use.
    Thanks,

    Matt

  14. #14
    Join Date
    Feb 2003
    Posts
    10
    Originally posted by geekything
    /sybase/backup_tape.cfg. INIT needs to be specified to configure the device.
    I just discovered the "with init" flags for dump. Scratch that last post

  15. #15
    Join Date
    Feb 2003
    Posts
    10

    Talking

    For what it's worth, I now have an extra 2Gbyte for data and an extra 2Gbyte (probably excessive) for log on this database. (And they're on separate, new, physical devices).

    Thanks again for everyone's help.

Posting Permissions

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