sybase数据库扩设备

sybase数据库扩设备

1 背景

  近几个月发生两次因为某个专业网管问题,在短时间内涌现大量告警,因此造成tacfm库空间迅速占满的

  故障。

  sp_helpdb tacfm

  name                     db_size       owner                    dbid        created        status                                                                                                

----                     -------       -----                    ----------- -------        ------                                                                                                

tacfm                        5120.0 MB tacfm                              6 Nov 15, 2005   select into/bulkcopy/pllsort, trunc log on chkpt, allow nulls by default                              

device_fragments               size          usage                created             free kbytes     

----------------               ----          -----                -------             -----------     

data2                              4096.0 MB data only            Nov 15 2005  4:31PM           503896

log2                               1024.0 MB log only             Nov 15 2005  4:31PM not applicable  

-                                                                                               

log only free kbytes = 1044408                                                                  

device                         segment                                                                                                                                                                                                                                                        

------                         -------                                                                                                                                                                                                                                                        

data2                          default                                                                                                                                                                                                                                                        

data2                          system                                                                                                                                                                                                                                                         

log2                           logsegment                                                                                                                                                                                                                                                     

由此可见tacfm一共使用了data2设备上的4G空间。

sp_helpdevice

device_name physical_name description status cntrltype device_number low high

data1 /dev/vg00/rlvdata1                             special, dsync off, physical disk, 10240.00 MB 2 0 3 50331648 55574527

data2 /dev/vg00/rlvdata2                             special, dsync off, physical disk, 10240.00 MB 2 0 4 67108864 72351743

data3 /dev/vg00/rlvdata3                             special, dsync off, physical disk, 5000.00 MB 2 0 7 117440512 120000511

log1 /dev/vg00/rlvlog1                             special, dsync off, physical disk, 3072.00 MB 2 0 5 83886080 85458943

log2 /dev/vg00/rlvlog2                             special, dsync off, physical disk, 3072.00 MB 2 0 6 100663296 102236159

master /dev/vg00/rlvmaster                           special, dsync on, default disk, physical disk, 144.00 MB 3 0 0 0 73727

sysprocsdev /dev/vg00/rlvtemproc                           special, dsync on, physical disk, 300.00 MB 16386 0 1 16777216 16930815

tapedump1 /dev/rmt/0m                                   disk, dump device 16 2 0 0 20000

tapedump2 /dev/rct/0                                     tape,        625 MB, dump device 16 3 0 0 20000

tempdb /dev/vg00/rlvtempdb                           special, dsync on, physical disk, 5000.00 MB 16386 0 2 33554432 36114431

sp_helpdevice data2 该设备空间大小为10G.

device_name                    physical_name                                  description                                                                                                                                                                                                                                                     status      cntrltype   device_number low                                                                             high                                                                           

-----------                    -------------                                  -----------                                                                                                                                                                                                                                                     ----------- ----------- ------------- ------------------------------------------------------------------------------- -------------------------------------------------------------------------------

data2                          /dev/vg00/rlvdata2                             special, dsync off, physical disk, 10240.00 MB  

-------------------------------------------------------------------

select distinct su.dbid

from master..sysusages su,master..sysdevices sd

where su.vstart between sd.low and sd.high

and sd.name='data2'

看哪些数据库使用了data2 设备。

-------------------------------------------------------------------

sp_helpdb tacpm

name                     db_size       owner                    dbid        created        status                                                                                                

----                     -------       -----                    ----------- -------        ------                                                                                                

tacpm                        7168.0 MB tacpm                              8 May 08, 2006   select into/bulkcopy/pllsort, trunc log on chkpt, allow nulls by default                              

device_fragments               size          usage                created             free kbytes     

----------------               ----          -----                -------             -----------     

data2                              4096.0 MB data only            May  8 2006  4:16PM                0

log2                               1024.0 MB log only             May  8 2006  4:16PM not applicable  

data2                              2048.0 MB data only            Nov 28 2006 11:39AM                0

tacpm使用了6G data2设备空间。因此只能考虑新增数据库设备

沿用上述方法,可以看到 tacfm tacpm各使用了

log2                           /dev/vg00/rlvlog2                              special, dsync off, physical disk, 3072.00 MB

中的1024MB,还有剩余1024M,可以分配给新增设备后的tacfm使用的日志空间。

2 vgdisplay -v vg00

--- Volume groups ---

VG Name                     /dev/vg00

VG Write Access             read/write    

VG Status                   available                

Max LV                      255   

Cur LV                      18    

Open LV                     18    

Max PV                      16    

Cur PV                      2     

Act PV                      2     

Max PE per PV               4384        

VGDA                        4  

PE Size (Mbytes)            16             

Total PE                    8748   

Alloc PE                    7618   

Free PE                     1130   

Total PVG                   0       

Total Spare PVs             0             

Total Spare PVs in use      0                    

   --- Logical volumes ---

   LV Name                     /dev/vg00/lvol1

   LV Status                   available/syncd          

   LV Size (Mbytes)            304            

   Current LE                  19       

   Allocated PE                38         

   Used PV                     2      

   LV Name                     /dev/vg00/lvol2

   LV Status                   available/syncd          

   LV Size (Mbytes)            4096           

   Current LE                  256      

   Allocated PE                512        

   Used PV                     2      

   LV Name                     /dev/vg00/lvol3

   LV Status                   available/syncd          

   LV Size (Mbytes)            208            

   Current LE                  13       

   Allocated PE                26         

   Used PV                     2      

   LV Name                     /dev/vg00/lvol4

   LV Status                   available/syncd          

   LV Size (Mbytes)            208            

   Current LE                  13       

   Allocated PE                26         

   Used PV                     2      

   LV Name                     /dev/vg00/lvol5

   LV Status                   available/syncd          

   LV Size (Mbytes)            32             

   Current LE                  2        

   Allocated PE                4          

   Used PV                     2      

   LV Name                     /dev/vg00/lvol6

   LV Status                   available/syncd          

   LV Size (Mbytes)            2672           

   Current LE                  167      

   Allocated PE                334        

   Used PV                     2      

   LV Name                     /dev/vg00/lvol7

   LV Status                   available/syncd          

   LV Size (Mbytes)            2208           

   Current LE                  138      

   Allocated PE                276        

   Used PV                     2      

   LV Name                     /dev/vg00/lvol8

   LV Status                   available/syncd          

   LV Size (Mbytes)            4608           

   Current LE                  288      

   Allocated PE                576        

   Used PV                     2      

   LV Name                     /dev/vg00/lvsybase

   LV Status                   available/syncd          

   LV Size (Mbytes)            10240          

   Current LE                  640      

   Allocated PE                1280       

   Used PV                     2      

   LV Name                     /dev/vg00/lvmaster

   LV Status                   available/syncd          

   LV Size (Mbytes)            160            

   Current LE                  10       

   Allocated PE                20         

   Used PV                     2      

   LV Name                     /dev/vg00/lvtemproc

   LV Status                   available/syncd          

   LV Size (Mbytes)            304            

   Current LE                  19       

   Allocated PE                38         

   Used PV                     2      

   LV Name                     /dev/vg00/lvtempdb

   LV Status                   available/syncd          

   LV Size (Mbytes)            5120           

   Current LE                  320      

   Allocated PE                640        

   Used PV                     2      

   LV Name                     /dev/vg00/lvdata1

   LV Status                   available/syncd          

   LV Size (Mbytes)            10240          

   Current LE                  640      

   Allocated PE                1280       

   Used PV                     2      

   LV Name                     /dev/vg00/lvdata2

   LV Status                   available/syncd          

   LV Size (Mbytes)            10240          

   Current LE                  640      

   Allocated PE                1280       

   Used PV                     2      

   LV Name                     /dev/vg00/lvlog1

   LV Status                   available/syncd          

   LV Size (Mbytes)            3072           

   Current LE                  192      

   Allocated PE                384        

   Used PV                     2      

   LV Name                     /dev/vg00/lvlog2

   LV Status                   available/syncd          

   LV Size (Mbytes)            3072           

   Current LE                  192      

   Allocated PE                384        

   Used PV                     2      

   LV Name                     /dev/vg00/nsmfs

   LV Status                   available/syncd          

   LV Size (Mbytes)            3200           

   Current LE                  200      

   Allocated PE                200        

   Used PV                     1      

   LV Name                     /dev/vg00/lvdata3

   LV Status                   available/syncd          

   LV Size (Mbytes)            5120           

   Current LE                  320      

   Allocated PE                320        

   Used PV                     1      

   --- Physical volumes ---

   PV Name                     /dev/dsk/c2t0d0

   PV Status                   available               

   Total PE                    4374   

   Free PE                     305    

   Autoswitch                  On       

   PV Name                     /dev/dsk/c2t1d0

   PV Status                   available               

   Total PE                    4374   

   Free PE                     825    

   Autoswitch                  On       

   空闲的PE是1130个 大小为1130×16=18080MB

   如果扩充tacfm的空间为4096MB的话,应该使用256个PE。 亦即新建一个LV /dev/vg00/lvdata4,大小为4096MB,用来分配给tacfm。

3 创建LV

  # lvcreate -l 256 -n lvdata4 vg00

4 增加数据库设备

  在sa下

  disk init name="data4",physname="/dev/vg00/rlvdata4",vdevno=8,size=2097152

  将tacfm扩展到data4设备上

  alter database tacfm on data4=4096   

  扩展tacfm的日志空间

  alter database tacfm log on log2=1024

5 执行

# lvcreate -l 256 -n lvdata4 vg00

Logical volume "/dev/vg00/lvdata4" has been successfully created with

character device "/dev/vg00/rlvdata4".

Logical volume "/dev/vg00/lvdata4" has been successfully extended.

Volume Group configuration for /dev/vg00 has been saved in /etc/lvmconf/vg00.conf

# chown sybase rlvdata4     -------------这里要写绝对路径

# chgrp sybase rlvdata4      -------------这里要写绝对路径

su - sybase

$isql -Usa -P12txwgc -Sntalarm

1> disk init name="data4",physname="/dev/vg00/rlvdata4",vdevno=13,size=2097152

2> go

1> alter database tacfm on data4=4096

2> go

Extending database by 524288 pages (4096.0 megabytes) on disk data4

1> alter database tacfm log on log2=1024

2> go

Extending database by 131072 pages (1024.0 megabytes) on disk log2

Warning: Using ALTER DATABASE to extend the log segment will cause user

thresholds on the log segment within 128 pages of the last chance threshold to

be disabled.

1> sp_helpdb tacfm

2> go

 name                     db_size       owner                    dbid 

         created      

         status                                                                                                

 ------------------------ ------------- ------------------------ ------

         --------------

         ------------------------------------------------------------------------------------------------------

 tacfm                       10240.0 MB tacfm                         6

         Nov 15, 2005 

         select into/bulkcopy/pllsort, trunc log on chkpt, allow nulls by defaul

         t                              

(1 row affected)

 device_fragments               size          usage              

         created             free kbytes     

 ------------------------------ ------------- --------------------

         ------------------- ----------------

 data2                              4096.0 MB data only          

         Nov 15 2005  4:31PM          1285944

 log2                               1024.0 MB log only           

         Nov 15 2005  4:31PM not applicable  

 data4                              4096.0 MB data only          

         Sep 18 2007 10:33AM          4177920

 log2                               1024.0 MB log only           

         Sep 18 2007 10:38AM not applicable  

 ------------------------------------------------------------------------------------------------

 log only free kbytes = 2088928              

 

本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/croco1981/archive/2009/05/22/4205729.aspx

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值