5. 灾难恢复篇
5.1 数据库服务不能启动
5.1.1 如何查找数据库启动失败原因
在实际环境中，数据库Server无法启动的原因很多，本文仅列出了几种常见的情况，供您参考。
首先，应检查Server的日志文件。不同版本缺省的日志文件如下（其中<server_name>为Sybase Server的名称：
UNIX:
11.0*: $SYBASE/install/errorlog
11.5* 或 11.9*: $SYBASE/install/<server_name>.log
12.0*:$SYBASE/ $SYBASE_ASE/install/<server_name>.log
NT:
11.0* 11.5* 或 11.9*: %SYBASE%\install\errorlog
12.0*: %SYBASE%\ASE-12_0\install\errorlog
? CASE 1:
basis_dlock: file '/sybase/master.dat' already in use by a SQL Server
kernel kdconfig: unable to read primary master device
kernel kiconfig: read of config block failed
检查server是否已经启动( showserver, ps -u sybase 或NT service )
? CASE 2:
dopen: open '/sybase/master.dat' failed, permission denied
kernel kdconfig: unable to read primary master device
kernel kiconfig: read of config block failed
检查master设备文件的所有者及权限
? CASE 3:
ninit: All master network listeners have failed. Shutting down
检查network ip 及 port配置 ( netstat -a )
? CASE 4:
kernel:kscsinit: connectivity library error. Operation: cs_ctx_alloc().
o 检查操作系统参数是否已经修改并重新启动操作系统 (参见安装手册)
若操作系统异常宕机，ASE未启动而$SYBASE/<server_name>.krg已经存在, 删除该文件
5.1.2 简单故障及排除方法
? 服务器启动失败，报告不能创建共享内存（UNIX平台）：
解决方法：
检查Sybase主目录下的文件，将所有文件的所有者改为sybase帐户。
编辑sybase主目录下的名为ServerName.cfg文件，找到total memory的一行，检查其配置值是否过大。
重新启动数据库服务器。
? 服务器启动失败，报告不能建立网络监听（UNIX平台）：
解决方法：
编辑Sybase主目录下的名为interfaces文件，检查相应服务器的网络端口是否和其它的服务器重复或使用的是系统已经使用的端口；
检查/etc/hosts的主机名和IP地址，对照interfaces中的主机名，是否存在冲突。
重新启动数据库服务器。
? 服务器启动失败，报告不能打开master设备文件（UNIX平台）：
解决方法：
检查$SYBASE/$SYBASE_ASE/RUN_ServerName中的master设备文件的位置；
检查master设备文件路径及访问权限；
重新启动数据库服务器。
5.1.3 master数据库日志满了,server无法启动,怎么办 (error 1105)
[ERROR]
Can't allocate space for object 'syslogs' in database 'master' because the 'system' segment is full.
If you ran out of space in syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment
to increase the size of the segment.
[WORKAROUND]
1.在RUN_servername的文件中添加标识 : -T3607
(编辑RUN_servername文件,在文件末尾添加以上标识)
2.启动ASE
(要直接运行文件RUN_servername,且该运行窗口在执行以下操作时不能关闭)
3.截断日志
1>dump tran master with no_log
2>go
4.停止ASE服务
1>shutdown
2>go
5.删除RUN_servername文件末尾的标识 : -T3607
6.重新启动ASE
5.1.4 SQL Server 10.x配置内存过大、Server不能启动时怎么办？
SQL Server使用的内存与机器的内存总数有一定的比例关系。如果Server使用内存太小，影响到SQL Server的性能，但内存配置过大超过一定比例时，导致SQL Server不能启动。
在ISQL中，用sp_configure “memory”可以看到你的Server现在使用的内存大小。这个数字单位为Page，每一个Page为2K。参数memory在启动Server时被读入内存。所以memory被修改之后，必须shutdown Server，再重新启动Server，新的参数才生效。
1>sp_configure "memory",number_of_memory
2>go
1>reconfigure with override
2>go
1>shutdown with nowait
2>go
如果新的内存配置太大，SQL Server不能启动，那么必须修改Master设备上的内存配置参数。
在$SYBASE/install目录下，使用buildmaster命令
$buildmaster -d/path/master.dat -y cmemsize = XXX
其中XXX为内存页数。如果新的内存大小合适，SQL Server可以启动。
5.2 数据库被挂起
5.2.1 如何解决数据库被挂起的问题(error 926)
现象：Error 926
Severity Level 14
Error Message Text
Database 'xx' cannot be opened - it has been marked SUSPECT by recover Explanation
(1) 当你使用Transact_SQL命令操作这个数据库的数据时, 出现这个信息, 这是一个严重的错误, 如果你要使用这个数据库的数据, 必须改正这个错误.
(2) 启动Backup Server, 后备master数据库
1>dump database master to "/usr/sybase/master.dup"
2>go
(3) 用isql登录到ASE, 须用sa帐号 (本文以pubs2数据库为例)
1>sp_configure "allow updates", 1
2>go
1>begin tran
2>go
1>use master
2>go
1>update sysdatabases
2>set status = -32768
3>Where name="pubs2"
4>go
如果得到(1 row affected),则
1>commit
2>go
否则
1>rollback
2>go
(4)重新启动ASE.
注：ASE重新启动之后，当发现数据库本身存在不可恢复的问题时，如数据页损坏等，且没有完好的数据库备份，一定要用bcp...out备份用户数据库数据。此时，以下步骤省略，并按照“如何删除坏的用户数据库”文章删除此数据库。之后重建此数据库，恢复备份。
否则，按以下步骤继续操作：
用sa帐号注册到ASE.
1>begin tran
2>go
1>use master
2>go
1>update sysdatabases
2>set status=0
3>Where name="pubs2"
4>go
如果得到(1 row affected),则
1>commit
2>go
否则
1>rollback
2>go
1>sp_configure "allow updates" ,0
2>go
(5)重新启动ASE.
(6) 如果你的数据库原来有dboption(例如"select into","trunc log on chkpt"等), 你需要重新设置这些option.
(7) 当数据库已经恢复可使用状态后，运行dbcc命令检查数据库的一致性(参照“如何检查数据库中数据一致性”文章)
(8) 备份用户数据库
例如：
1>dump database pubs2 to "/usr/sybase/pubs2.dup"
2>go
5.2.2 如何做 rebuild log
注意：这个过程可能会引起数据的不一致性。
(1)赋予sa用户sybase_ts_role的角色
isql -Usa -P
1>sp_role "grant","sybase_ts_role",sa
2>go
1>quit
(2)将数据库置为"bypass recovery"状态
isql -Usa -P
1>sp_configure "allow updates",1
2>go
1>use master
2>go
1>update sysdatabases set status=-32768
2>where name="database_name"
3>go
1>shutdown with nowait
2>go
(3)rebuild数据库日志
isql -Usa -P
1>use master
2>go
1>dbcc rebuild_log(database_name,1,1)
2>go
1>shutdown with nowait
2>go
(4)重启ASE
1>use master
2>go
1>update sysdatabases set status=0 where name="database_name"
2>go
1>sp_configure "allow updates",0
2>go
1>shutdown with nowait
2>go
(5)在重启ASE之后,如果数据库恢复正常，rebuild log工作将会成功完成，否则要恢复数据库备份，使用dump database或bcp命令。
5.3 数据库恢复
5.3.1 如何删除坏的用户数据库？(以pubs2为例)
当使用drop database无法删除数据库时，使用本文所示方法可以删除。
(1)使用isql以sa注册ASE
(2)设置允许修改系统表
1>sp_configure "allow updates",1
2>go
(3)把 要删除的用户数据库置为"suspect"状态
1>use master
2>go
1>begin tran
2>go
1>update sysdatabases set status=256
2>where name="pubs2"
3>go
如果得到(1 row affected),则
1>commit
2>go
否则
1>rollback
2>go
(4)重启server,并用isql以sa注册。
(5)删除数据库
1>dbcc dbrepair(pubs2,dropdb)
2>go
(6)恢复允许修改系统表
1>sp_configure "allow updates",0
2>go
(7)结束
5.3.2 如何做Rebuild Master
提示1：
如果有可能，在执行这个任务之前，请先做操作系统级SYBASE DEVICES的后备。UNIX操作系统可使用命令"dd"。因为如果disk reinit使用了错误的信息，那么，在执行了disk refit之后就会产生无法弥补的错误。倘若，存在一个SYBASE DEVICES的后备文件，将会给我们一个弥补的机会。例如：当disk reinit 使用了过小的size值，我们还可以重新恢复SYBASE DEVICES文件，重新做disk reint、disk refit。
提示2：
在使用disk reinit命令时，将覆盖SYBASE DEVICE（请参照以下语法），安全的做法是size值使用裸分区或系统文件的大小的最大值。如果使用的是UNIX裸分区，即使你不能确认SYBASE DEVICE最初大小是不是最大值，都要使用裸分区大小的最大值。
步骤：
? 获得将要被恢复的SYBASE DEVICE的信息。
这些信息被用来重建sysdevices,sysusages以及sysdatabases。
? 从error log的server启动信息中获得SYBASE DEVICE的设备名、指定路径。
? 使用裸分区或系统文件的大小的最大值作为SYBASE DEVICE的
大小。
? 以上信息也可以通过最近的sysdevices系统表的内容来获得。如果
对此信息怀有疑问，还是使用以上的方法比较稳妥。
? 做操作系统级的SYBASE DEVICE后备。UNIX操作系统，使用"dd"命令实现。
? 配置一个新的ASE Server。在以后的步骤中会用到这个新的master。
? 如果需要，请再配置一个Backup Server。
? 用单用户模式启动Server。
? 运行disk reinit，用来重建sysdevices系统表，而没有重新初始化SYBASE DEVICE。
语法如下：
disk reinit
name="device_name",
physname="physical_name",
vdevno=virtual_device_number,
size=number_of_blocks
完成后，请查看error log。
? 确认重建的sysdevices系统表中信息正确：
——select * from sysdevices
比较表中的信息是否与error log或者保留的sysdevices中的信息相同。
? 运行disk refit,用来重建sysdevices以及sysdatabases。
用法如下：
use master
go
disk refit
go
查看error log中是否有错误提示。
? 当disk refit完成后，会自动shut down ASE Server。
? 确认重建的系统表的信息是否正确：
——单用户模式启动ASE Server
——select * from sysusages、select * from sysdatabases
——sysusages系统表看起来是否正确？可以和以前保留的的信息进行比较。如果没有这种可能，那么应该保证不出现显而易见的错误。例如：是否缺少dbid；是否缺行；是否对于一个数据库来说只有segmap=4(表示为日志行)的行等等。
——sysdatabases系统表看起来是否正确？是不是没有显而易见的错误？
11、 启动ASE Server，查看是否所有的数据库都已经正常恢复。
? 对所有的库做dbcc检查。
? 对所有库做后备。
请参考《Trouble Shooting Guide》
5.3.3 如何恢复master数据库
ASE can't setup and has no valid dump of master
1、编辑RUN_servername
在命令行最后加入：-T3607
2、单用户模式启动ASE
$cd install
$startserver -f RUN_servername -m
3、bcp out系统表
$bcp master..sysdevices out /directory.spec/devs -Usa -P -c
$bcp master..sysdatabases out /directory.spec/dbs -Usa -P -c
$bcp master..sysusages out /directory.spec/usages -Usa -P -c
$bcp master..syslogins out /directory.spec/logins -Usa -P -c
$bcp master..sysconfigures out /directory.spec/configures -Usa -P -c
$bcp master..syscharsets out /directory.spec/charsets -Usa -P -c
4、shutdownASE
5、创建新master设备
$buildmaster -d<path_to_new_master_device> -s<new_master_device_size>
(new_master_device_size以2K为单位)
6、编辑RUN_servername
将指定master设备指定为新创建的master设备，并删除在第1步中增加的参数。
7、删除/directory.spec/dbs、/directory.spec/usages文件中有关master、tempdb、model的内容。
8、单用户模式启动ASE
$cd install
$startserver -f RUN_servername -m
9、 bcp in系统表
$ bcp master..sysdevices in /directory.spec/devs -Usa -P -b 1 -c
$bcp master..sysdatabases in /directory.spec/dbs -Usa -P -b 1 -c
$bcp master..sysusages in /directory.spec/usages -Usa -P -b 1 -c
$bcp master..syslogins in /directory.spec/logins -Usa -P -b 1 -c
$bcp master..sysconfigures in /directory.spec/configures -Usa -P -b 1 -c
$bcp master..syscharsets in /directory.spec/charsets -Usa -P -b 1 -c
10、shudown ASE
11、执行installmaster脚本
$isql -Usa -P < $SYBASE/scripts/installmaster
12、启动ASE
5.3.4 如何移植master设备
以下步骤说明了如何将master设备移植到不同的磁盘上。在执行此项任务的同时，请参看SYBASE的相关文档（Technical Documents#1324 entitled "Segment Remapping with Load database When Moving a Database"）。请注意，在执行buildmaster之前，要停止ASE的应用。
成功的关键在于，新建的sysusages系统表中每一行内容与旧的sysusages系统表内容相符。
? 对master库做dbcc检查，并后备master库。
? 执行select * from table_name命令，并保留其输出内容。其中，table_name包括：sysdevices、sysusages、sysdatabases。同样，可以使用bcp命令来实现。
? 执行sp_configure命令，并保留其输出内容。
? 拷贝$SYBASE/server_name.cfg文件，以做保留。
? Shut down SQL/ASE Server。
? 执行以下命令，创建一个新的master设备：
UNIX：buildmaster -d<master_device> -ssize
VMS：buildmaster /disk=<master_device> /size=size
(size以页为单位，1页=2K)
? 编辑RUN_server_name文件,-d(UNIX)或/device(VMS)参数指向新建的设
备名。
? 单用户模式启动SQL/ASE Server：
UNIX：startserver -f RUN_sever_name -m
VMS：startserver /server=server_name /masterrecover
? 执行select * from sysdevices命令，并保留其输出内容。
? 确认新建的sysusages系统表中每一行内容与旧的sysusages系统表内容相符，而且在配置好Server之后，没有做过alter database，那么系统表sysusages的内容是正确的。如果做过alter database,则要按原来的顺序执行这些脚本，如果没有脚本，就要到保存的sysusages系统表的信息中找到alter database的参数。
? Shutdown SQL/ASE Server，并用单用户模式启动Server，查看sysusages系统表内容是否正确。
? 如果所配置的Backup Server名称不是SYB_BACKUP,则要执行：
1> sp_configure "allow updates",1
2> go
1> update sysservers set srvnetname="name in interfaces file"
where srvname="SYB_BACKUP"
2> go
? 装载master库。如果新master设备的大小与旧设备大小不同，则Server会Shut down。请注意新的系统表将会被重写，而且你需要调整Sysdevices系统表中master设备的大小。请执行以下两步：
? 用单用户模式启动Server.
? 如果新设备大小与旧设备大小不同，请执行：
1> sp_configure "allow updates",1
2> go
1> update sysdevices set high=nnnn where name="master"
2> go
其中nnnn是以页（2K）为单位的master设备的大小，此值可以从保留的sysdevices系统表的信息中找到。如果所建的设备比旧设备大，请执行：1）创建一个与master设备差不多大的数据库，这样做的目的是重新初始化分配页，使得整个master设备可用。2）删除这个数据库。
? 重启Server。
? 后备master数据库。
注意：
? 在装载master库之前要确定ASE此时的字符集和语言模块与后备master库时的字符集和语言模块相同。
? 在执行此任务之前和完成以后使用sp_helpsort查看字符集与语言模块是否相同。
5.3.5 如何重建sybsystemprocs系统数据库
依照以下步骤可以实现移动sybsystemprocs系统数据库以及设备的任务。同时这个过程也可以用来扩建sybsystemprocs系统数据库。
以下过程中所提到的语法结构，可以参看SYBASE相关资料。
SYBASE提醒您，在修改系统表时，ASE要以单用户模式运行，同时要以"sa"用户登录。
? 保留驻留在sybsystemprocs系统数据库中自定义的存储过程脚本。
? 单用户模式启动ASE，执行：
1> sp_configure "allow updates",1
2> go
1> reconfigure with override(10.0版本以上，省略此步)
2> go
? 删除sybsystemprocs系统数据库:
1> use master
2> go
1> drop database sybsystemprocs
2> go
在重建sybsystemprocs系统数据库之前，不要创建任何其他数据库。
? 删除sysdevices系统表中有关sybsystemprocs系统数据库的信息：
1> begin tran
2> delete from sysdevices where name="sysprocsdev"
我们假定sysprocsdev是默认的sybsystemprocs系统数据库设备名。
3> select * from sysdevices
确定删除是否正确有效，如果正确，执行：
4> commit tran
否则，执行：
4>rollback
? 重启ASE.
? 创建sybsystemprocs系统数据库设备：
1> disk init name="sysprocsdev",physname="physical_path",vdevno=4,size=25600
2> go
其中size是以页为单位（512页=1M）。
sybsystemprocs系统数据库设备的vdevno应该是4，ASE在单用户模式下4不能被重新利用，所以如果以上语句执行时出现问题，请重启ASE。
? sybsystemprocs系统数据库：
1> create database sybsystemprocs on sysorocsdev=50
2> go
在sysdatabases系统表中，sybsystemprocs系统数据库的dbid=4，如果在重建
sybsystemprocs系统数据库之前，没有创建任何其他数据库。
? 以多用户模式启动ASE,并执行：
1> sp_configure "allow updates",0
2> go
3> reconfigure with override(10.0版本以上，省略此步)
4> go
? 运行installmaster脚本：
%isql -Usa -Psa_password -Sserver_name -n -iinstallmaster -o< output_file
installmaster脚本在$SYBASE/scripts路径下。
? 重建sybsystemprocs系统数据库中的用户自定义存储过程 。
5.3.6 如何挽救 corrupt table 中的数据
[此文仅供参考]
*/设置数据库隔离级别：
sp_setsuspect_granularity [dbname[,{"database"|"page"}[,"read_only"]]]
强制脱机数据库/数据页联机：
sp_forceonline_db dbname,{"sa_on"|"sa_off"|"all_users"}
sp_forceonline_page dbname,pgid,{"sa_on"|"sa_off"|"all_users"}
获取有关脱机数据库/页的信息：
sp_listsuspect_db
sp_listsuspect_page [dbname]
How to patch a corrupt table and save its data
platform: general product: ASE
written by: Hunter Liu
last update time : Dec.17,1998
(1) isql -Usa -P to log into sql server and enter following commands
1> sp_role "grant","sybase_ts_role",sa
2> go
1>quit
2>go
(2) relogin into sql server with sa
(3) enter these commands in isql:
1> use your_database
2 > go
1> select first ,root ,indid from sysindexes
2> where id=object_id("table_name")
3>go
choose a line from the output of which the indid is neither 0 nor 1,
and pick up the value of its root. this is the root page number
of this index btree,next we will use root_page_number
to represent it and indid for its index id.
(4) find a page number which is on the data pages chain
1>dbcc prtipage(database_name,table_name, indid,root_page_number)
2>go
(4.1) read the last line of this dbcc output ,it is like:
index row at offset XX points to page XXXX.
take the page number xxxx and issue above command again :
1>dbcc prtipage(database_name,table_name,indid,XXXX)
2>go
repeat this step until the dbcc output looks like this:
leaf row at offset xxxxx points to data page nnnnn,row number xxxx.
this meens that we reach the leaf-level pages of this index btree,
so you choose a line from its output and read out the
page number, which is a data page of this table.
(5) from the data page (nnnn), you walk along the data page chain of this table
until we get to the begin, the first page of its database chain.
1>dbcc pglinkage(supportdb,nnnn,0,2,0,0)
2>go
the last number of this page number list is the first
page of this table. make sure the object id displayed is correct.
(6) update the first column of this table in sysindexes
1>update sysindexes set first=new_first_page where id=object_id('table_name')
2> and indid=0
3>go
(7) use select count(*) from table_name to test our work.
(8) select * into new_table from table_name.
(9) drop the old corrupt table.
5.3.7 Recovering the master Database or Master Device under ASE 12.5
This TechNote describes the new procedure you need to use to load the master database, or recover from master database or device corruption in Adaptive Server Enterprise 12.5. The disaster recovery procedures differ from pre-12.5 servers.
Contents
1. Background
2. Before You Begin
3. Loading an Older Copy of master Database
4. Recreating the master Database
5. Recreating the Master Device
6. Manually Setting the Backup Server Name
Section 1. Background
Starting with Adaptive Server version 12.5, there is no buildmaster program to build the master device. Rather, this functionality has been incorporated into the dataserver (unix) and sqlsrvr (Windows) programs. The server now allows you to create master devices and databases with 2K, 4K, 8K or 16K logical page sizes. Due to these and other changes, you cannot use the instructions provided in the ASE Troubleshooting and Error Messages Guide (EMTSG) for disaster recovery tasks like recovering the master database or device.
This TechNote explains how to perform disaster recovery in the 12.5.x server. It is applicable to ASE 12.5.0.1 IR and higher versions.
Note:
The EMTSG instructions still apply to the pre-12.5 servers.
Section 2. Before You Begin
This document describes three key maintenance and disaster recovery tasks:
? Loading an older copy of your master database. This assumes that both the master device and master database are intact and free from corruption.
? Recovering from a corrupted master database. This assumes that the master device is intact.
? Recreating a master device and all its databases.
Some points to note before using this information:
? This material applies only to ASE 12.5.x and higher. It has been verified with ASE 12.5.0.1 Interim Release (IR), and it is recommended that you use the procedures with this release (or later). For 12.0 and prior versions, use the instructions provided in the ASE Troubleshooting and Error Messages Guide, "System Database Recovery."
? This material assumes that your Adaptive Server was installed with your platform's default sort order. If you have installed a non-default sort order, you must ensure that your (restored) server uses the correct sort order and character set to reflect that in the dump. Refer to the ASE Troubleshooting and Error Messages Guide, "System Database Recovery," section titled "Valid Dump with Non-Default Sort Order" for details; and note that in Step 3 of that section, the sybinit utility has been replaced by the dsedit/dscp utilities.
? All SQL command examples in this document use Transact-SQL syntax. All command-line examples are unix commands; Windows users can find the equivalent NT syntax in ASE Utility Programs for Windows and Windows NT.
? Starting with 12.5, the dataserver command allows a space between option and parameter.
? The examples in this document assume that
- the dataserver binary is located in $SYBASE/bin/dataserver
- the master device is $SYBASE/d_master.dat
Replace this location and device name with those appropriate for your site.
? The server must be in single-user mode (that is, started with the -m flag) to load the master database. In this mode only the master database can be loaded.
? You can only load a dump of master that matches your server level. Loading an older version dump to a newer server is not permitted.
? You can use these procedures regardless of which version of the server you were using when you created your old master device. The server will find and correct any placement differences between the old and recreated databases.
? After the load completes but before shutting down, the server does some post-processing to reconcile the newly loaded sysdatabases and sysusages tables against the information in the master device. At this time the server may print a variety of error messages regarding failures to use or find the master database, and/or attempts to insert duplicate keys or duplicate rows to sysusages. Ignore these messages; they occur only during the reconciliation phase, and will not affect the server's operation after it shuts down and is restarted.
Section 3. Loading an older copy of master database
Use the following steps if your master database and the master device are intact, and you simply wish to load an older dump of your master database.
Note:
Be sure to read Section 2, Before You Begin.
Step 1: Put the Server in Single-user Mode
Shut down and restart the server with the -m flag, which places the server in single-user mode and sets up to load the master database:
% $SYBASE/bin/dataserver -d $SYBASE/d_master.dat -m
Step 2: Establish the Backup Server Name
This step is necessary to ensure that Adaptive Server has access to its backup server for dumps and loads. Follow the instructions detailed in Section 6, Manually Setting the Backup Server Name.
Step 3. Load the master Database
Issue the following isql command:
1> load database master from "master_db_dump"
2> go
Adaptive Server shuts itself down after the load is complete.
Section 4. Recreating the master Database
Use this procedure when the current master device is usable, but you are unable to use the server because of master database corruption. These steps enable you to create a new master database and reload it from backup.
Step 1. Create a New master Database
The approach to creating the new master database depends on the extent and nature of the corruption. Three different scenarios are possible:
? Basic recreation, which is sufficient if only the data in master was affected. The server reads the master device to determine page and device sizes.
? Recreating when the device's configuration area is corrupted. You will need to provide page and device sizing information.
? Recreating when the master database allocation pages are also corrupted. All corrupt or unallocated extents on the device are allocated to master.
Basic Recreation of master Database
This command instructs the server to read the device's configuration area to obtain page size and device size and determine where to place the master database:
% $SYBASE/bin/dataserver -d $SYBASE/d_master.dat -w master
The server creates a master of the same size, and in the same locations on disk, as the database it is replacing. It will NOT have the old database's data! Instead, it contains a default set of data that you will replace later via load database. The default data includes information about any databases existing on the master device (but no other devices). It also has minimal system information, including a login for sa with a null password.
This process produces a large number of "upgrade" messages tracking the progress of database creation which are helpful in troubleshooting any problems. They are "upgrade" messages because the server creates a new master database by "upgrading" the device.
Note:
If the configuration area is corrupt or unavailable, this command returns the message: "The configuration area in device 'xxx' appears to be corrupt. The server needs this data to boot, and so cannot continue." If this occurs, continue with the instructions below.
Recreation with a corrupt configuration area
The "Basic Recreation" process above may fail if the device's configuration area has become corrupt. If so, you must supply sizing information. You will need two parameters: the page size (you need to know what this was), and the device size, which you can determine directly from the device:
% ls -l $SYBASE/d_master.dat
Divide the size shown by the page size (2048, say) to obtain the number of server pages, by 1024 to obtain KB, or by 1048576 to obtain MB.
Provide this information on the command line as follows:
% $SYBASE/bin/dataserver -d $SYBASE/d_master.dat -w master
-z page_size -b device_size
For example, if your page size is 2K and the device size is 51204 server pages (100 MB, plus 8K space for the configuration area), the command looks like this:
% $SYBASE/bin/dataserver -d $SYBASE/d_master.dat -w master -z 2k -b 51204
You may also specify the device size as Kb, Mb, or Gb; for example, "-b 100M".
Recreation when master database allocation pages are corrupted
If the above procedures for recreating the master database fail, the database's allocation pages are corrupt. (This may happen, for instance, if the database device was inadvertently written over by a completely different file.)
In this case, you can force the server to allocate all corrupted or unallocated extents to the master database:
% $SYBASE/bin/dataserver -d $SYBASE/d_master.dat -w master -f
This allocates ALL corrupted or otherwise unrecognizable extents to the master database. Depending on the extent of your master device corruption, and how much free space it originally had, this will probably leave master much larger than it needs to be, causing it to occupy space that used to belong to other databases like model, tempdb, and sybsystemdb. We will consider recovering from that situation later.
Note:
You may combine the -f, -b, and -z options as necessary.
Step 2. Restart the Server in Single-user Mode
The server shuts down after recreating the master database. Restart it with the -m flag, which places the server in single-user mode and sets up to load the master database:
% $SYBASE/bin/dataserver -d $SYBASE/d_master.dat -m
Step 3: Account for Missing Databases (if you used the -f option)
Note:
You only need this step if you used the -f option in Step 1 to recreate the master database due to allocation page corruption. If you did not use -f, proceed to Step 4.
Recall that the -f command line option could make the new master larger than needed at the expense of other required databases on the master device. You will need to check for these databases before proceeding. This step has many possible permutations, so you must know what databases should be on the master device to perform this step. For example, if you had moved tempdb to a different device, you will not need tempdb on the master device. If upgrading, you may well have created sybsystemdb on a device other than master; if so, you will not need to account for sybsystemdb.
Log in as sa, and check the databases currently on the master device:
1> select name from sysdatabases
2> go
Do you see all the databases that should be on the master device? If so, skip the rest of this step. Otherwise, you will need to determine which databases are missing and how big they should be, then obtain the free space needed to recreate these databases.
The following isql script obtains the required space by removing it from the end of the master database. In order, it
? establishes how many logical pages the missing databases need
? subtracts that number from the pages that master occupies
? removes disk usage entries for parts of master above that limit
? restricts the highest logical chunk of master such that its total size leaves the required number of pages free.
You will need to provide the required space value, denoted as "@needed_mb".
Note:
This sample script is provided to assist you with the disaster recovery task. It is not officially supported by Sybase.
1> declare @needed_mb int, @needed_pages int, @master_end int,
2> @pgspermb int
3> select @pgspermb = (1048576 / @@maxpagesize)
4> select @needed_mb = 12 -- replace '12' with required space value
5> select @needed_pages = @needed_mb * @pgspermb
6> select @master_end = sum(size) - @needed_pages
7> from master.dbo.sysusages
8> where dbid = 1
9> if (@master_end > (6 * @pgspermb))
10> begin
11> delete master.dbo.sysusages
12> where lstart > @master_end
13> update master.dbo.sysusages
14> set size = @master_end - lstart
15> where dbid = 1
16> and lstart = (select max(lstart) from master.dbo.sysusages
17> where dbid = 1)
18> end
19> else
20> begin
21> print "Can't take enough space from the master database!"
22> print "Need to find %1! pages", @needed_pages
23> print "That would leave master with %1! pages", @master_end
24> print "Cannot continue."
25> end
26> go
Note:
If the procedure fails, your master device is not big enough to hold all the databases you are trying to create. Check the required MBs of space that you specified. If it is correct, it may be necessary to create a new master device using the instructions in Section 5, Recreating the Master Device.
You now have enough space to recreate your required databases. Create them one at a time. For example:
1> create database model on default = 3
2> go
Repeat for each database. Then shut down the server, and restart it in single-user mode using step 2 above.
Step 4. Establish the Backup Server Name
This step is necessary to ensure that Adaptive Server has access to its backup server for dumps and loads. Follow the instructions detailed in Section 6, Manually Setting the Backup Server Name.
Step 5. Load the master Database
Issue the following isql command:
1> load database master from "master_db_dump"
2> go
Unlike during a normal database load, the server may need to perform a lot of extra work, because master contains information about the identity and location of your other databases; that information may have changed for this master device, and the server must check and update it as necessary.
At the end of this step, the server contains correct disk usage information about the master device. This may differ from the information in sysusages in the dump, so the server will find and correct both the size and location information for the databases. Any entries for parts of databases that don't actually exist on master will be removed.
During post-processing from the load, you may see one or more errors from the server. Read Section 1, Before You Begin, for more information about these errors.
After checks and validations are complete, the server will shut down. You may now restart it normally.
Step 6. Did you recreate any databases in step 3 above?
If you recreated databases using the procedure in step 3 above, load those databases. You must restart the server without the -m flag in order to accomplish this.
Section 5. Recreating the Master Device
Use these steps when the disk that used to contain your master device is not accessible, and you need to start over with a new device.
This situation is somewhat similar to the scenario in Section 4 above in which the master device becomes so corrupt that you need to use the -f option, because you will need to know what databases used to be on your master device and how big they were so you can validate and recreate them as necessary.
Step 1. Create your new master device
When creating the new master device, make sure you use the same page size as your old master device and make the new device at least as large as the old one. The following example creates a device with a 2048-byte logical page size, and total size 100 Megabytes + 8 Kilobytes (the 8 KB is extra space for the configuration area.)
% $SYBASE/bin/dataserver -d $SYBASE/d_master.dat -z 2k -b 51204
Use the -s option with this command to specify the server name. You can also specify the "-b" size in Kb, Mb, or Gb. In the above example you would use "-b 100.00782M". Without one of the K, M, or G modifiers, the default device size is expressed in server virtual pages, 2048 bytes each.
At device creation, the server issues large numbers of "upgrade" messages tracking its progress; these messages help troubleshoot any problems. They are upgrade messages because the server creates a new installation by doing an "upgrade" of a device that it has just created.
When finished, the server shuts down. You now have a master database containing minimal system information, including an sa login whose password is null, and minimally sized master, model, tempdb, and sybsystemdb databases.
Step 2. Put the Server in Single-user Mode
Shut down and restart the server with the -m flag, which places the server in single-user mode and sets up to load the master database:
% $SYBASE/bin/dataserver -d $SYBASE/d_master.dat -m
Step 3. Establish the Backup Server Name
This step is necessary to ensure that Adaptive Server has access to its backup server for dumps and loads. The new master database contains a default Backup Server entry of SYB_BACKUP for srvnetname, which is probably wrong. Since the sp_addserver procedure is not available at this time, log in to the server as sa and update sysservers directly:
1> update master.dbo.sysservers
2> set srvnetname = "backup_server_name"
3> where srvname = "SYB_BACKUP"
4> go
(1 row affected)
Step 4. Load the master Database
Issue the following isql command:
1> load database master from "master_db_dump"
2> go
The server inspects the master device and makes any corrections needed in the newly loaded sysdatabases and sysusages. These corrections affect only the master device, since that is the only device that changed -- the server assumes that all your other devices are undamaged and need not be inspected.
After this step, it is possible that your new master device contains database entries for databases that also exist on other devices in your system. This may happen if you moved tempdb to a different device, or created sybsystemdb on a different device. The server recognizes and handles this situation: if it finds pre-existing entries for those databases on other devices, it presumes that the existing entries are correct and does not change them.
During post-processing from the load, you may see one or more errors from the server. Please read Section 2, Before You Begin, for more information about these errors.
After the checks and validations are complete the server shuts down. You may now restart it normally.
Step 5. Check that the Databases on Master Device Are Correct
When you created a new master device in step 1 above, the server created only its default set of databases, with minimal data. You will almost certainly need to load dumps of the databases (notably model) that used to be there.
Are the databases on your new master device large enough to hold the dumps you will be loading into them? Are all the necessary databases present? Is there any obsolete data that you need to clean up?
Log in as sa and inspect the databases on your system:
1> declare @pgspermb int
2> select @pgspermb = 1048576 / @@maxpagesize
3> select "db name"=db_name(dbid), dbid, "size"=sum(size) / @pgspermb
4> from master.dbo.sysusages
5> group by dbid
6> go
This command shows you all the databases present on your system, and their total size. Note that the size column in the output is expressed in Megabytes.
Does this list contain any entries where database name is null? These sysusages entries don't have any matching entries in sysdatabases; they are unnecessary and should be deleted. (You may be specially susceptible to this if you upgraded from pre-12.0 versions, and created sybsystemdb on the older version; sybsystemdb will have a different dbid than the default dbid.) To remove these entries, use a script like the following:
1> exec sp_configure "allow updates", 1
2> go
1> delete sysusages
2> where db_name(dbid) is null
3> go
1> exec sp_configure "allow updates", 0
2> go
Are any databases missing? Create those databases. Are the databases large enough? If not, alter them to be at least large enough to hold the dumps. (It is okay if they are too large; the server simply clears the excess space.)
Section 6. Manually Setting the Backup Server Name
This procedure updates the sysservers table and is needed to ensure that Adaptive Server can access the correct backup server to carry out dumps and loads. Use it with the instructions for Sections 3 and 4.
Execute the following isql commands in Adaptive Server:
1> use master
2> go
1> select srvname, srvnetname from sysservers
2> where srvname = "SYB_BACKUP"
3> go
There are three possible outcomes to this query. Take the appropriate action below depending on the outcome:
Outcome Action
ASE returns a single row and
srvnetname contains the correct
Backup Server name No action is needed.
ASE returns a single row
but the srvnetname is not the
correct Backup Server name Issue the following isql command:
1> update sysservers
2> set srvnetname = "backup_server_name"
3> where srvname = "SYB_BACKUP"
4> go
where backup_server_name is the name of the Backup Server
as it appears in the interfaces file.
ASE returns 0 rows Issue the following isql command:
1> sp_addserver SYB_BACKUP, null,
2> backup_server_name
3> go
5.3.8 如何只用数据库设备文件生成新的数据库服务器
本例的环境：
OS: Win2000 professional (中文)
ASE: Adaptive Server Enterprise 12.0 for NT(中文)
原有数据库服务器： PDS_CJ
数据设备文件：c:\sybase\data\master_RDS_CJ.dat 30M
c:\sybase\data\sybprocs_RDS_CJ.dat 80M
目的：
将通过现有的2个数据库设备在相同的平台下的其他的机器上创建数据库服务器，而不用syconfig.exe(服务器配置)程序来创建服务器。
新建服务器名称：RDS_CJ
步骤：
一、配置新的数据库服务器：
1。拷贝原有数据库设备文件到新的机器上：
copy master_PDS_CJ.dat 为 master_RDS_CJ.dat
copy master_PDS_CJ.dat 为 master_RDS_CJ.dat
2。拷贝或新建RUN文件：
copy RUN_PDS_CJ.bat > RUN_RDS_CJ.bat
编辑 RUN_RDS_CJ.bat 将所有 PDS_CJ 替换为 RDS_CJ（指定MASTER设备和SERVER名）
RUN文件样本：
rem
rem Adaptive Server Information:
rem name: PDS_CJ
rem master device: c:\sybase\data\master_PDS_CJ.dat
rem master device size: 15360
rem errorlog: c:\sybase\ASE-12_0\install\errorlog_PDS_CJ
rem interfaces: c:\sybase\ini
rem
c:\sybase\ASE-12_0\bin\sqlsrvr.exe
-dc:\sybase\data\master_PDS_CJ.dat
-sPDS_CJ
-ec:\sybase\ASE-12_0\install\errorlog_PDS_CJ
-ic:\sybase\ini -Mc:\sybase\ASE-12_0
3。添加ASE的服务器明：
用dsedit工具添加接口（interfaces）文件的条目
4。启动新配的服务器：（此时系统存储过程库仍无法使用）
通过执行RUN文件的方式启动服务器。(-T3604)
5。修改数据库中的信息：
1> select * from sysdevices where name="sysprocsdev"
2> go
low high status cntrltype name phyname mirrorname
-----------------------------------------------------------------------------------------
16777216 16818175 16386 0 sysprocsdev c:\sybase\data\sybprocs_PDS_CJ.dat NULL
1> sp_configure "allow update",1
2> go
1> update sysdevices set phyname="c:\sybase\data\sybprocs_RDS_CJ.dat" where name="sysprocsdev"
2> go
(1 row affected)
1> update sysservers set srvname="RDS_CJ" where srvname="PDS_CJ"
2> go
1> shutdown
2> go
6． 启动服务器
通过执行RUN文件的方式启动服务器(不加 -T3604了)，新服务器配置完成。
二、将数据库服务器注册到注册表中：
1。编辑Server(PDS_CJ).reg文件，将所有的PDS_CJ替换成RDS_CJ
执行即在 HKEY_LOCAL_MACHINE\SOFTWARE\SYBASE\Server中注册了名为RDS_CJ的服务器；
2。编辑Serv(PDS_CJ).reg文件，将所有的PDS_CJ替换成RDS_CJ
执行即在 HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services中注册了名为SYBSQL_RDS_CJ的服务；
3。编辑Serv(PDS_CJ).reg文件，将所有的PDS_CJ替换成RDS_CJ
执行即在 HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application中注册了名为RDS_CJ的项目，
其作用是将SYBSQL_RDS_CJ 服务的事件信息和错误记录在Win2000的事件查看器的应用日志中，
前两步是必需的，第3步可以不添加。
以上的注册表文件修改并执行后，不要马上重新启动Win2000。
请运行regedit
将HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SYBSQL_RDS_CJ中ImagePath 的数值数据，
由c:\sybase\ASE-12_0\bin\sqlsrvr.exe -sPDS_CJ -C
该为：
c:\sybase\ASE-12_0\bin\sqlsrvr.exe -sRDS_CJ -C
4。重新启动Win2000，让注册表中新添加的项目生效。
仅供参考：
WIN NT 4.0 和 WIN2000的注册表的格式不同，因此不能够将这2个平台的注册表文件相互迁移。
5.3.9如何单独在备份机上启动Sybase ASE12.5 (ASE HA)
一． 如何单独在备份机上启动Sybase ASE12.5
1. 1. 停止HACMP的服务进程
1. 2. 在备机上将卷组激活
# varyonvg datavg
1. 3. 修改standby地址为主机的service地址
# smitty tcpip à en2
修改10.61.2.102à10.61.1.100
1. 4. 登录到sybase用户
# su - Sybase
$
1. 5. 编辑interfaces文件
将sdcp_bak_srv 改为sdcp_pri_srv
1. 6. 启动ASE
$ cd /sybase/ASE-12_5/install
$ startserver –f RUN_sybase_bak
1. 7. 执行如下命令
$isql –Usa –P –Ssybase_bak
1> dbcc takeover
2> go
二． 恢复到正常状态
2． 1． 行如下命令
$isql –Usa –P –Ssybase_bak
1> dbcc ha_admin(‘’,rollback_failover)
2> 2> go
2． 2． 停止sybase_bak
$isql –Usa –P –Ssybase_bak
1> 1> shutdown
2> 2> go
3．将interfaces文件中的地址改回为原来的sdcp_bak_srv
4．退回到root用户
5．将地址10.61.1.100修改回10.61.2.102
6．中断对卷组datavg的激活
# varyoffvg datavg
7．启动HACMP
5.4 数据库恢复以后的工作
5.4.1 如何检查数据库中数据的一致性
数据库一致性检查(dbcc)提供了一些命令用于检查数据库的逻辑和物理一致性。Dbcc主要有两个功能：
? 使用checkstorage 或 checktable 及 checkdb 在页一级和行一级检查页链及数据指针。
? 使用checkstorage, checkalloc, 或 checkverify, tablealloc, 及indexalloc 检查页分配。
在下列情况中需要使用 dbcc 命令：
? 作为数据库日常维护工作的一部分, 数据库内部结构的完整性决定于sa 或dbo 定期地运行 dbcc 检查。
? 在系统报错以后, 确定数据库是否有损坏。
? 在备份数据库之前, 确保备份的完整性。
? 如果怀疑数据库有损坏时, 例如, 使用某个表时报出表损坏的信息, 可以使dbcc 确定数据库中其他表是否也有损坏。
下面是dbcc的简单用法：
? dbcc checktable (table_name)
检查指定的表, 检查索引和数据页是否正确链接, 索引是否正确排序, 所有指针是否一致, 每页的数据信息是否合理, 页偏移是否合理。
? dbcc checkdb (database_name)
对指定数据库的所有表做和checktable 一样的检查。
? dbcc checkalloc (database_name,fix|nofix)
检查指定数据库, 是否所有页面被正确分配, 是否被分配的页面没被使用。当使用"fix"选项时，在检查数据库的同时会自动修复有问题的页面。（若数据库数据量很大，则该过程会持续很长时间。）
? dbcc tablealloc (table_name,fix|nofix)
检查指定的表, 是否所有页面被正确分配, 是否被分配的页面没被使用。是 checkalloc 的缩小版本, 对指定的表做完整性检查。当使用"fix"选项时，在检查数据表的同时会自动修复数据表中有问题的页面。
关于上述命令的其它选项及详细使用方法和checkstorage, checkverify, indexalloc 的详细使用方法, 请参阅3.11 DBCC或Sybase命令手册。
举例1： Unix平台检查pubs2数据库的一致性
? 单用户模式启动Server：
$SYBASE/install startserver -f RUN_server_name -m
? vi dbcc_db.sql
use master
go
sp_dboption pubs2,"single user",true
go
use pubs2
go
checkpoint
go
dbcc checkdb(pubs2)
go
dbcc checkalloc(pubs2,fix)
go
dbcc checkcatalog(pubs2)
go
use master
go
sp_dboption pubs2,"single user",false
go
use pubs2
go
checkpoint
go
quit
go
? isql -Usa -Pxxxxxx -SSYBASE <dbcc_db.sql >dbcc_db.out
? grep Msg dbcc_db.out
举例2： Unix平台检查pubs2数据库中titles表的一致性
? vi dbcc_table.sql
use pubs2
go
dbcc checktable(titles)
go
dbcc tablealloc(titles)
go
? isql -Usa -Pxxxxxx -SSYBASE < dbcc_table.sql > dbcc_table.out
grep Msg dbcc_table.out
换一张