?
Solved

sybase 12.5 hpux increase device size

Posted on 2011-10-10
7
Medium Priority
?
708 Views
Last Modified: 2012-05-12
experts,

I've ran out of devices on my Sybase 12.5 install, and need to increase the sizse of them. I thought the following would work:
delete devices
drop database
drop lvms
recreate lvms of bigger size
recreate devices
recreate database for load
load database back

is this the correct procedure? I had to re-map my segments which was a little tricky (using dbartisan GUI), but running DBCC checktable has returned some unexpected results. it appears that i've lost a lot of data.

Can anyone tell me what the correct way perform this maintenance?

thanks in advance.
0
Comment
Question by:telliot79
  • 3
  • 2
  • 2
7 Comments
 
LVL 24

Assisted Solution

by:Joe Woodhouse
Joe Woodhouse earned 1400 total points
ID: 36948101
Broadly speaking that looks to me to be correct.

You should take care to recreate the database in exactly the same way - ie same segments on same fragments of the same size in the same order.

eg. if your original database was created as:

   create database db
   on data1 = 2000, data2 = 2000
   log on log1 = 1000
   go

   alter database db
   on data3 = 2000
   go

then that is exactly how you should recreate it, and not (for example):

   create database db
   on data1 = 2000, data2 = 2000, data3 = 2000
   log on log1 = 1000
   go

But getting that wrong won't lose you data, it would just at worst cause data & log to be mixed. If you're getting dbcc checktable errors and data loss, something else has happened.

Try testing the database dump by loading it somewhere else? Maybe the dump is bad. Maybe you already had data corruption... dump & load tends to make it worse.

Don't forget to make the sybase UNIX user the owner of the LVMs and of /dev/async. You should also check the minor number of /dev/async - if you aren't using mirroring, you probably want minor number 0 or 2, and if you are, 4 or 6.

More details available at:

http://hpux-tips.blogspot.com/2006/01/how-to-enable-asynchronous-io-disk-hp.html 
0
 

Author Comment

by:telliot79
ID: 36949121
okay, so maybe that's my problem - I've decreased my segments form 18 per database, to 6 per database, and trippled there size. Where originally, the create database would have been:

 create database db
   on data1 = 2000, data2 = 2000
   log on log1 = 1000
   go


it is now:

 create database db
   on data1 = 6000, data2 = 6000
   log on log1 = 3000
   go

thus using 1 third of the devices.

i i used the same amount of devices, but increased the sizes of them all, and then altered the database as needed, would this work do you think?

Also, I did notice the data and logs were mixed. Using DBArtisan I dropped and extended them where needed and this seemed to resolve the problem - although it took a little tinkering.
0
 
LVL 13

Assisted Solution

by:alpmoon
alpmoon earned 600 total points
ID: 36951173
What you did definitely create a data and log mixed database. You need to stick with the current segment map. However, you don't have to use same devices as they are used now. For example you can create your database in this way:

create database db
   on data1 =4000
   log on log1 = 1000
   go

Then

alter database db
   on data1 = 2000, data2 = 6000
   log on log1 = 2000
   go

In this way you keep the first two data segments in data1 and create a third data segment on data1 after log segment.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:telliot79
ID: 36951668
okay, the problem is though I'm out of devices. in 12.5 there's a 250 device limit.... and believe it or not, we're approaching that. from what I'm reading, creating the devices larger, should work. i'll just re-run my create database scripts, but should then have extra space on the devices. What are your thoughts?

Thanks again for all the help.
0
 
LVL 13

Expert Comment

by:alpmoon
ID: 36952138
You can use the remaining extra space for another database or for that database in the future. It will not be waste. It will be available anyway
0
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 1400 total points
ID: 36953241
You don't need to match the segments & fragments exactly. As alpmoon says, you can collapse contiguous allocations of the same segment. So if you'd extended the database onto data devices five times in a row, you can totally make that a single fragment.

However you don't actually need to reduce the number of fragments in order to reduce the number of devices.

If you originally had:

    create database db
    on data1=2000, data2=2000, data3=2000, data4=2000
    log on log1=2000

where each device was only 2000Mb in size, you could totally recreate your devices to be larger (and thus have fewer of them), but still maintain exactly the same fragment & segment mappings:

    create database db
    on data1=2000, data1=2000, data1=2000, data1=2000
    log on log1=2000

ie. the "create database" and "alter database" commands only care that there's enough space available on the devices, but they don't care how large they actually are.

So you can recreate your LVMs and devices to be considerably larger and still recreate your databases in the same way they are today.

That said, there is still an advantage to reducing the number of fragments as well, because you have a limit of how many fragments a given database can use. In my example above this could totally have become:

    create database db
    on data1=8000
    log on log1=2000

but that's only because there were four data allocations in a row. You can only collapse contiguous allocations of the same segment type. A more detailed (and abbreviated) example would be if your original allocations were:

    data1=2000, data2=2000
    log1=1000
    data3=2000
    log2=500
    data4=2000, data5=500, data5=500, data5=1000
    log3=1000
    data6=2000, data7=2000

you could collapse that into:

    data1=4000
    log1=1000
    data2=2000
    log1=500
    data2=4000
    log1=1000
    data1=4000

Again, create/alter database doesn't care about device names or sizes, just that there is enough free space on those devices.

Summary: If you have 200x2Gb LVMs & devices, you can totally recreate those as 40x8Gb LVMs & devices without having to change a thing in how your databases are created... but it is also an opportunity to collapse contiguous allocations per above. In fact sometimes ASE will do that for you during a dump & load if it sees that it can (depending on how source & target have been recreated).

Important note: none of these issues explain dbcc errors and data loss. Even if you'd totally messed up the fragments you shouldn't have had those problems, so there is a separate issue also happening here.
0
 

Author Closing Comment

by:telliot79
ID: 36958852
thanks fellas. i've not had time to implement yet, but this has been very helpful.

much appreciated.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Installing FreeBSD… FreeBSD is a darling of an operating system. The stability and usability make it a clear choice for servers and desktops (for the cunning). Savvy?  The Ports collection makes available every popular FOSS application and packag…
Every server (virtual or physical) needs a console: and the console can be provided through hardware directly connected, software for remote connections, local connections, through a KVM, etc. This document explains the different types of consol…
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question