#redwaves
请教OFFLINE数据库如何删除?
在作数据库异机恢复时，先将数据库以单用户模式启动后成功恢复了master数据库.
重启数据库后通过sp_helpdb可以看到要恢复的数据库xhb是offline状态
通过drop database xhb，提示:
1> drop database xhb
2> go
Msg 840, Level 17, State 2:
Server 'AS5XHB', Line 1:
Device 'xhb_dev' (with physical name '/opt/sybase1254/data/xhb_dev.dat', and
virtual device number 3) has not been correctly activated at startup time.
Please contact a user with System Administrator (SA) role.
1>
请问如何删除这个库和设备文件重建以便作xhb这个数据库的恢复?
#andkylee
两种方法：
第一种：
use master
go
dbcc traceon(3604)
go
dbcc dbrepair('xhb',dropdb)
go
第二种：
use master
go
delete from sysusages where dbid = db_id('xhb')
go
delete from sysdatabases where name='xhb'
go
然后，手动删除xhb所使用的设备。 delete from sysdevices where name ='设备逻辑名字'
最后重启sybase数据库，即可！（利用sysusages表中的vdevno或者low vstart对应关系找到设备名称，稍微有点麻烦。）
ps：请慎用第二种方法。
#redwaves
回4楼
按照您的指导作了还是不成功
1> use master
2> go
1> dbcc traceon(3604)
2> go
00:00000:00012:2010/07/14 17:15:35.75 server DBCC TRACEON 3604, SPID 12
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
1> dbcc dbrepair('xhb',dropdb)
2> go
Msg 2573, Level 16, State 1:
Server 'AS5XHB', Line 1:
Database 'xhb' is not marked suspect. You cannot drop it with DBCC.
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
1>
按提示需要将数据库xhb标记为suspect
查过手册提示:
After You Execute sp_marksuspect
Once the procedure is created successfully, updates to the system catalog should be immediately disabled as follows:
1> sp_configure "allow updates", 0
2> go
Syntax
sp_marksuspect database_name
Example
1> sp_marksuspect PRODUCTION
2> go
通过sp_help没有找到sp_marksuspect命令,版本12.5.4
还有别的办法吗？
#andkylee
sp_listsuspect_db 看看有没有数据库处于可疑状态？
只有sp_makesuspect_obj ，而没有让数据库直疑的过程。
#redwaves
先要将数据库置于suspects状态
#isql -U sa -P 123456 -SAS5XHB
1.打开更新状态
1>sp_configure “allow updates”,1
2>go
2.设置数据库状态为256
1> use master
2>go
1> update sysdatabases set status=256 where name='xhb'
2> go
(1 row affected)
设置完后数据库由offline变为not recovered, offline:
xhb 4096.0 MB sa 4
Jul 14, 2010
not recovered, offline
3.关闭更新
1>sp_configure “allow updates”,0
2>go
4.重起数据库
1>shutdown with nowait
2>go
5.删除数据库
1>dbcc dbrepair(xhb.dropdb)
2>go
完成如下:
1> sp_helpdb
2> go
name db_size owner dbid
created
status
------------------------ ------------- ------------------------ ------
------------------
------------------------------------------------------------------------------------------------------
master 6.0 MB sa 1
Jul 13, 2010
mixed log and data
model 2.0 MB sa 3
Jul 13, 2010
mixed log and data
sybsystemdb 3.0 MB sa 31513
Jul 13, 2010
mixed log and data
sybsystemprocs 132.0 MB sa 31514
Jul 13, 2010
trunc log on chkpt, mixed log and data
tempdb 3.0 MB sa 2
Jul 14, 2010
select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data
xhb 4096.0 MB sa 4
Jul 14, 2010
not recovered, offline
(1 row affected)
(return status = 0)
1> dbcc dbrepair(xhb,dropdb)
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
1> sp_helpdb
2> go
name db_size owner dbid
created
status
------------------------ ------------- ------------------------ ------
------------------
------------------------------------------------------------------------------------------------------
master 6.0 MB sa 1
Jul 13, 2010
mixed log and data
model 2.0 MB sa 3
Jul 13, 2010
mixed log and data
sybsystemdb 3.0 MB sa 31513
Jul 13, 2010
mixed log and data
sybsystemprocs 132.0 MB sa 31514
Jul 13, 2010
trunc log on chkpt, mixed log and data
tempdb 3.0 MB sa 2
Jul 14, 2010
select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data
(1 row affected)
(return status = 0)
删除日志设备
1> sp_dropdevice xhb_log_dev
2> go
00:00000:00012:2010/07/14 18:26:22.95 kernel uddeactivate: device for vdn 4 already inactive.
Device dropped.
(return status = 0)
1> sp_helpdevice
2> go
device_name physical_name
description
status cntrltype device_number
low
high
------------------------------ ----------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------ --------- -------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
master /opt/sybase1254/data/master.dat
special, dsync on, default disk, physical disk, 30.00 MB
3 0 0
0
15359
sysprocsdev /opt/sybase1254/data/sysprocs.dat
special, dsync on, physical disk, 132.00 MB
16386 0 1
16777216
16844799
systemdbdev /opt/sybase1254/data/sybsysdb.dat
special, dsync on, physical disk, 1.00 MB
16386 0 2
33554432
33554943
tapedump1 /dev/nst0
disk, dump device
16 2 0
0
20000
tapedump2 /dev/nst1
tape, 625 MB, dump device
16 3 0
0
20000
xhb_dev /opt/sybase1254/data/xhb_dev.dat
special, dsync on, physical disk, 2048.00 MB
16386 0 3
50331648
51380223
(6 rows affected)
(return status = 0)
1>
删除数据设备
1> sp_dropdevice xhb_dev
2> go
00:00000:00012:2010/07/14 18:28:34.13 kernel uddeactivate: device for vdn 3 already inactive.
Device dropped.
(return status = 0)
1> sp_helpdevice
2> go
device_name physical_name
description
status cntrltype device_number
low
high
------------------------------ ----------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------ --------- -------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
master /opt/sybase1254/data/master.dat
special, dsync on, default disk, physical disk, 30.00 MB
3 0 0
0
15359
sysprocsdev /opt/sybase1254/data/sysprocs.dat
special, dsync on, physical disk, 132.00 MB
16386 0 1
16777216
16844799
systemdbdev /opt/sybase1254/data/sybsysdb.dat
special, dsync on, physical disk, 1.00 MB
16386 0 2
33554432
33554943
tapedump1 /dev/nst0
disk, dump device
16 2 0
0
20000
tapedump2 /dev/nst1
tape, 625 MB, dump device
16 3 0
0
20000
(5 rows affected)
(return status = 0)
1>
终于搞定，感谢大家支持
#andkylee
告诉你的第二种方法是最简单的了。 将sysdatabases sysuages sysdevices中关于xhb的信息都删除掉。 然后重启ase。 最后把dat删除就全部搞定了。
转自：http://bbs.chinaunix.net/thread-1748185-1-1.html
1756
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
