[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2773
  • Last Modified:

How to unmix data and log segments on same device SYBASE??

We recently have a disk crash, where we were running our SYBASE ASE 12.5 server, we had the dump files for databases and we could recover in other installed server, the issue is found out that we have the log and data spaces mixed on the same devices and we now have to be able to make dump of the transaction log, so I have to questions:

1. Is it possible to make dumps of the transaction under this database structure?
2. How does this strcuture affects the performance?
3. What detaled procedure should we follow in order to umix the log and data segments on same device?

I paste results for sysusages and helpdb:

dbid   segmap      lstart      size        vstart      pad    unreservedpgs crdate                      
------ ----------- ----------- ----------- ----------- ------ ------------- ---------------------------          
4      3           0           12800       33554432    (null) 0             Apr 7 2009  7:00AM          
4      4           12800       3840        50331648    (null) 3825          Apr 7 2009  7:00AM          
4      3           16640       28160       50362368    (null) 0             Apr 7 2009  7:00AM          
4      3           44800       64000       67108864    (null) 24058         Apr 7 2009  7:00AM          
4      3           108800      6400        570425344   (null) 6375          Apr 7 2009  7:00AM          
4      4           115200      3840        570476544   (null) 3825          Apr 7 2009  7:00AM          
7      3           0           25600       167772160   (null) 0             Apr 7 2009  6:26AM          
7      4           25600       6400        184549376   (null) 6375          Apr 7 2009  6:26AM          
7      3           32000       25600       184600576   (null) 0             Apr 7 2009  6:26AM          
7      3           57600       64000       201326592   (null) 0             Apr 7 2009  6:26AM          
7      3           121600      64000       218103808   (null) 0             Apr 7 2009  6:26AM          
7      3           185600      6400        234881024   (null) 0             Apr 7 2009  6:26AM          
7      4           192000      6400        234932224   (null) 6375          Apr 7 2009  6:26AM          
7      3           198400      51200       234983424   (null) 0             Apr 7 2009  6:26AM          
7      3           249600      12800       251658240   (null) 0             Apr 7 2009  6:26AM          
7      4           262400      25600       251760640   (null) 25500         Apr 7 2009  6:26AM          
7      3           288000      25600       251965440   (null) 0             Apr 7 2009  6:26AM          
7      3           313600      64000       268435456   (null) 0             Apr 7 2009  6:26AM          
7      3           377600      64000       285212672   (null) 0             Apr 7 2009  6:26AM          
7      3           441600      102400      301989888   (null) 40020         Apr 7 2009  6:26AM          
7      3           544000      15872       318767104   (null) 15810         Apr 7 2009  6:26AM

name                     db_size       owner                    dbid   created        status                                                                                                
------------------------ ------------- ------------------------ ------ -------------- ------------------------------------------------------------------------------------------------------
cobis                        1860.0 MB sa                       4      Apr 01, 2009   select into/bulkcopy/pllsort, trunc log on chkpt                                                      
device_fragments               size          usage                created             free kbytes                    
------------------------------ ------------- -------------------- ------------------- ------------------------------
cobis_dat                      200.0 MB      data only            Apr  7 2009  7:00AM              128              
cobis_dat1                     440.0 MB      data only            Apr  7 2009  7:00AM                0              
cobis_dat1                     60.0 MB       log only             Apr  7 2009  7:00AM not applicable                
cobis_dat2                     1000.0 MB     data only            Apr  7 2009  7:00AM           364528              
cobis_log1                     100.0 MB      data only            Apr  7 2009  7:00AM           102000              
cobis_log1                     60.0 MB       log only             Apr  7 2009  7:00AM not applicable                
                                                               
--------------------------------------------------------------
log only free kbytes = 122512    

name                     db_size       owner                    dbid   created        status                                                                                                
------------------------ ------------- ------------------------ ------ -------------- ------------------------------------------------------------------------------------------------------
cob_cartera                  8748.0 MB sa                       7      Apr 01, 2009   select into/bulkcopy/pllsort, trunc log on chkpt                                                      
device_fragments               size          usage                created             free kbytes                    
------------------------------ ------------- -------------------- ------------------- ------------------------------
cob_cartera_dat                400.0 MB      data only            Apr  7 2009  6:26AM                0              
cob_cartera_dat1               400.0 MB      data only            Apr  7 2009  6:26AM                0              
cob_cartera_dat1               100.0 MB      log only             Apr  7 2009  6:26AM not applicable                
cob_cartera_dat2               1000.0 MB     data only            Apr  7 2009  6:26AM                0              
cob_cartera_dat3               1000.0 MB     data only            Apr  7 2009  6:26AM                0              
cob_cartera_dat4               100.0 MB      data only            Apr  7 2009  6:26AM                0              
cob_cartera_dat4               800.0 MB      data only            Apr  7 2009  6:26AM                0              
cob_cartera_dat4               100.0 MB      log only             Apr  7 2009  6:26AM not applicable                
cob_cartera_dat5               200.0 MB      data only            Apr  7 2009  6:26AM                0              
cob_cartera_dat5               400.0 MB      data only            Apr  7 2009  6:26AM                0              
cob_cartera_dat5               400.0 MB      log only             Apr  7 2009  6:26AM not applicable                
cob_cartera_dat6               1000.0 MB     data only            Apr  7 2009  6:26AM                0              
cob_cartera_dat7               1000.0 MB     data only            Apr  7 2009  6:26AM                0              
cob_cartera_dat8               1600.0 MB     data only            Apr  7 2009  6:26AM           527104              
cob_cartera_log                248.0 MB      data only            Apr  7 2009  6:26AM           252960              
                                                               
--------------------------------------------------------------
log only free kbytes = 611968                                  

Any help would be really appreciated
0
Veneno
Asked:
Veneno
  • 2
  • 2
1 Solution
 
Jan FranekCommented:
Ad 1
I'm not 100% sure but I think, that dump transaction shuld be possible

Ad 2  
Log needs mainly sequential writes while data is access more randomly, so mixing them on one device will probably have some negative effect - mainly if you don't have some RAID cache on the way to disks. I have seen situations like this and I didn't see any visible slowdown so I guess that the performace impact will not be too big

Ad 3
To "unmix" data and log you need to 1 dump database, 2 drop database, 3 create database with correct order of data and log segments a then 4 load database. I suppose steps 1,2 and 4 are trivial, only step 3 is little bit tricky.

sp_helpdb output shows, that your cobis database has following structure:
640 MB data
60 MB log
1100 MB data
60 MB log

So you have to create it like this:
create database cobis on DATA = 640 log on LOG = 60
alter database cobis on DATA = 1100
alter database cobis log on LOG = 60

where DATA and LOG has to be replaced by your data and log devices. And if your devices are smaller then needed size, you can create it in more steps -

alter database cobis on DATA = 1100

can be replaced by

alter database cobis on DATA1 = 1000
alter database cobis on DATA2 = 100

0
 
Joe WoodhousePrincipal ConsultantCommented:
You are able to take transaction dumps of this database.

Your data and log are physically mixed, in that data fragments and log fragments appear on the same Sybase devices, but they are not logically mixed, in that no fragment has both data and log on it. (You can see this confirmed in sysusages - you have no fragment with segmap & 7 = 7 , ie. logical OR of bitmap 3 and bitmap 4.)

That's all that's required for dumps of the transaction log (ie incremental backups). If your recovery targets for this database are "recover to last incremental backup" then you already have everything you need.

Physical mixing does cost you the opportunity to perform up to the instant recovery. The idea with this is that so long as your log device(s) are intact, you can survive the loss of one or more data devices. Of course, if you aren't especially protecting your log devices then it's up to pure chance as to whether a disk crash kills data vs log.

To fix your physical mixing is simple but possibly tedious. The easiest (not necessarily fastest) method is to drop & recreate the database correctly - this is why they got mixed in the first place, you didn't create the database correctly when you recreated it.

The idea is when you load a database the pages must be written out in the order they appear in the database (ie in order of sysusages.lstart). Let's look at the rows from sysusages for dbid 4:

4      3           0           12800       33554432    (null) 0             Apr 7 2009  7:00AM          
4      4           12800       3840        50331648    (null) 3825          Apr 7 2009  7:00AM          
4      3           16640       28160       50362368    (null) 0             Apr 7 2009  7:00AM          
4      3           44800       64000       67108864    (null) 24058         Apr 7 2009  7:00AM          
4      3           108800      6400        570425344   (null) 6375          Apr 7 2009  7:00AM          
4      4           115200      3840        570476544   (null) 3825          Apr 7 2009  7:00AM          


We're interested in segmap, lstart and size. Ordering by lstart (which it is) gives us the order the database fragments were originally created. Segmap tells us data or log. Size is in pages, and you seem to be using 16Kb pages so we divide size by 64 to give us a size in Mb.

That tells us dbid 4 was created as:

data = 200Mb
log = 60Mb
data = 440 Mb
data = 1000Mb
data = 100Mb
log = 60Mb

Note this is not the order reported by sp_helpdb! sp_helpdb is useless for this - it sorts the fragments in alphabetical order of device name!! You must go by sysusages ordered by lstart.

So all you have to do is dump your database, drop it, and recreate it to match the above. Remember you can only combine fragments in a single CREATE or ALTER DATABASE statement for consecutive data fragments up to the first log fragment, and then consecutive log fragments up to the next data fragment. Eg

create database cobis
on cobis_dat = 200
log on cobis_log1  = 60
go

alter database cobis
on cobis_dat1 = 440
, cobis_dat2 = 1000
, cobis_dat1=100
log on cobis_log1 = 60
go

The moral of the story is you cannot properly recreate a database unless you know what its original sysusages looks like. But! If you get it wrong (or you simply don't have access to it), you can always reconstruct what it is supposed to look like with a similar method to what I've used above.

This is a common DBA mistake, everyone is always confused about why this happens. Think of a database backup as writing out all the database pages one by one in the order they exist in the original database. When you load the database, Sybase says, ok, I see a data page in the backup so that's what I have to write now. In fact I see 200Mb of data pages, so that's what I'll write. And the next page is... uh oh, a log page. But the database I'm loading into says the next page gets written into a data device! Oh well, it can't be helped, the next page is a log page so that's what I have to write, even though that means I'm putting a log page on a data device.

At least in your case it was able to cleanly divide the fragments so data and log were not mixed on any one fragment.

It's a good idea when you take your database backups to also reverse-engineer the SQL needed to recreate them. That way you never ever run into this mess again. There are any number of scripts to do this, have a look on Sybase CodeXchange (needs registration but is free), or there is the generally good dbschema.pl Perl scripts. (They don't handle everything correctly but they get devices and databases right.)

Good luck!
0
 
Jan FranekCommented:
OK, Joe is right, I'm not. I didn't notice that sysusages shows different order of data and log segments.
0
 
Joe WoodhousePrincipal ConsultantCommented:
Hi Jan,

Yeah, I've been bitten by that before. It's easy to just look at sp_helpdb but it can be very misleading. Always go to the source rows in master..sysusages. :)
0
 
VenenoAuthor Commented:
Thanks for the response I usually tried to be more clean in my process of dump and load of databases so the log and data don't get myxed on same device when you load. But like a year ago I was on vacations and a system crash occured I never get to know what the person who reinstalled did in detail and we have had the mixing since then, now we are trying now to implement an incremental bakcup plan and thought it was just about time to strar learning about this SYBASE intrincate.

TYVM
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now