Skip to Content
0

Recovering a database that has added segments on 15.7 SP138

Jan 04, 2017 at 10:27 PM

26

avatar image

Sybase documents on the create database page (http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36272.1570/html/commands/X75308.htm) under Using the for load option:

You can use the for load option for recovering from media failure or for moving a database from one machine to another, if you have not added to the database with sp_addsegment.

I’m currently running on Sybase version 15.7 SP138. My question is that for databases that I’ve added segments to using sp_addsegment, how would I go about recovering it? Just from playing around it seems that the load itself works just fine and the segment gets added however the device layout of the database changes slightly depending on if there were fragments on the same device adjacent to either other, where instead of being two separate fragments, the new recovered database combines them to become one fragment.

Example: cmp_page_db is a database that I've added a segment to:

1> select * from master..sysusages where dbid=db_id('cmp_page_db')
2> go
 dbid   segmap      lstart     size       vstart      location unreservedpgs crdate                          vdevno      
 ------ ----------- ---------- ---------- ----------- -------- ------------- ------------------------------- ----------- 
     24           3          0      25600      185856        0         24656             Nov 30 2016  1:27AM          18 
     24           3      25600      12800      237056        0         12750             Nov 30 2016  1:27AM          18 
     24           3      38400      12800      332800        0         12750             Nov 30 2016  1:27AM          19 
     24           4      51200      12800      179200        0         12750             Nov 30 2016  1:27AM          29 
     24           4      64000      25600      204800        0         25500             Nov 30 2016  1:27AM          29 
     24          11      89600      12800      102912        0         12750             Jan  3 2017  4:04PM          30 
     24          11     102400      10240      128512        0         10200             Jan  3 2017  4:04PM          30 
     24           4     112640       5120      154112        0          5100             Jan  3 2017  5:20PM          30 
     24           4     117760       7680      164352        0          7650             Jan  3 2017  5:20PM          30 
     24          11     125440       2560      148992        0          2550             Jan  3 2017  4:04PM          30 
(10 rows affected)

After taken a dump of cmp_page_db, I create the database I want to recover it to called testing where the layout is this before the load:

1> select * from sysusages where dbid=db_id('testing')
2> go
 dbid   segmap      lstart     size       vstart      location unreservedpgs crdate                          vdevno      
 ------ ----------- ---------- ---------- ----------- -------- ------------- ------------------------------- ----------- 
     34           3          0      25600           0        0         24664             Jan  4 2017  1:43PM          42 
     34           3      25600      12800       51200        0         12750             Jan  4 2017  1:43PM          42 
     34           3      38400      12800           0        0         12750             Jan  4 2017  1:43PM          43 
     34           4      51200      12800           0        0         12750             Jan  4 2017  1:43PM          44 
     34           4      64000      25600       25600        0         25500             Jan  4 2017  1:43PM          44 
     34           3      89600      12800           0        0         12750             Jan  4 2017  1:43PM          45 
     34           3     102400      10240       25600        0         10200             Jan  4 2017  1:43PM          45 
     34           4     112640       5120       46080        0          5100             Jan  4 2017  1:43PM          45 
     34           4     117760       7680       56320        0          7650             Jan  4 2017  1:43PM          45 
     34           3     125440       2560       71680        0          2550             Jan  4 2017  1:43PM          45 
(10 rows affected)

After loading from a dump taken from cmp_page_db the layout becomes:

1> select * from sysusages where dbid=db_id('testing')
2> go
 dbid   segmap      lstart     size       vstart      location unreservedpgs crdate                          vdevno      
 ------ ----------- ---------- ---------- ----------- -------- ------------- ------------------------------- ----------- 
     34           3          0      38400           0        0         38250             Jan  4 2017  1:45PM          42 
     34           3      38400      12800           0        0         12750             Jan  4 2017  1:45PM          43 
     34           4      51200      38400           0        0         38250             Jan  4 2017  1:45PM          44 
     34          11      89600      23040           0        0         22950             Jan  4 2017  1:45PM          45 
     34           4     112640      12800       46080        0         12750             Jan  4 2017  1:45PM          45 
     34          11     125440       2560       71680        0          2550             Jan  4 2017  1:45PM          45 
(6 rows affected)

Other then the layout change the recovered database seems to work fine. Is there a way to maintain the same device layout as it was for the original database? The adjacent fragments seem to coalesce otherwise. Considering that the load went through and the database seems to work, I was wondering if anything else breaks when loading a database with custom segments?

Thanks!

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers