SYBASE数据库的使用及日常问题的解决方法.doc

SYBASE数据库的使用及日常问题的解决方法中兴通讯固网南京用服部 辛国成摘要: 介绍sybase数据库的基础知识和核心系统SQL Server的基本特征以及sybase软件重要组成、系统库和用户数据库主要部分，sybase数据库各种问题的处理。关键词: SQL Server 数据库备份 BCP表操作1 Sybase基础知识1.1 Sybase软件的组成Sybase软件可划分为以下三个部分：（1）进行数据管理与维护的联机关系数据库管理系统Sybase SQL Server；（ 2 ） 支持数据库应用系统的建立和开发的一组前端工具软件Sybase SQL Tools；（3）可把异构环境下其他厂商的应用软件和任何类型的数据连接在一起的接口软件Open Client/Open Server。SQL Server是个可编程的数据库管理系统（DBMS），它是整个Sybase产品的核心软件，起着数据管理、高速缓冲区管理、事务管理的作用。1.2 SQL Server的基本特征SQL Server是个关系数据库管理系统，它具有如下一些基本特征：（1）SQL Server可以放在若干个磁盘设备上，初始安装时所需的磁盘空间根据版本不同需要的空间不同。（2）SQL Server支持多库结构，也就是说Sybase系统中可以有多个数据库。Sybase可以管理多个数据库。（3）SQL Server可以编译和运行T-SQL语句，并可返回客户程序所要求的结果。T-SQL语句是标准SQL的扩充，它除了有数据定义语句、数据操纵语句和数据控制语句之外，主要增加了流程控制语句。（4）SQL Server可以管理多个用户并具有较高的事务吞吐量和较低的事务响应时间。1.3 客户/服务器体系结构Sybase系统是一种建立在客户/服务器体系结构上的数据库管理系统。那么什么是客户/服务器体系结构？从硬件角度看，客户/服务器体系结构是指将某项任务在两台或多台机器之间进行分配，其中客户机（Client）用来运行提供用户接口和前端处理的应用程序，服务器机（Server）提供客户机使用的各种资源和服务。从软件角度看，客户/服务器体系结构是把某项应用或软件系统按逻辑功能划分为客户软件部分和服务器软件部分。客户软件部分一般负责数据的表示和应用，处理用户界面，用以接收用户的数据处理请求并将之转换为对服务器的请求，要求服务器为其提供数据的存储和检索服务；服务器端软件负责接收客户端软件发来的请求并提供相应服务。1.4 SQL Server的配置参数服务器配置是系统管理员的职责，正确的配置对系统性能有重大的影响。有两个系统表存储配置信息：sysconfigures和syscurconfigs；sysconfigures是永久性的，一旦系统运行，sysconfigures的信息就拷贝到syscurconfigs中。显示配置和改变配置使用系统过程sp_configure。有两种配置值：动态的和静态的，动态值一旦改变立即生效，静态值要在系统重启动后才起作用。1.5 访问Sybase服务器的基本过程（1）建立客户与服务器之间的连接，包括网络连接、客户进程与服务器进程之间的连接；（2）客户端通过网络发送SQL语句给服务器，用来查询或操作服务器中的数据或数据库对象；（3）服务器接收到SQL语句后，对其进行语法分析、优化和编译后执行；（4）如果执行的语句产生一个结果集，服务器通过网络把结果集返回给客户；（5）客户端对收到的结果作相应的处理。在客户/服务器体系，有两种数据库引擎结构，即多进程数据库引擎和单进程、多线程引擎结构。多进程结构即多个可执行程序同时运行。每当用户登录到数据库系统时实际上都启动了数据库引擎的一个独立实例。存在起协调作用的进程协调进行之间的通讯以保证当多用户访问相同数据时数据的完整性。多进程数据库引擎一般用在大型机数据库上。单进程多线程数据库引擎在原理上和多进程数据库引擎类似，不同的是多线程数据库引擎自己负责调度各应用程序占用CPU的时间，而不依赖于操作系统。这样，多线程数据库引擎自我保护的能力更强。Oracle Server是一个真正的多进程数据库引擎，Sybase数据库管理系统采用的是单进程多线程的引擎结构。1.6 SQL Server的主要内容Sybase SQL Server是一个多库结构的RDBMS，体系结构大致如下：（1） 数据库。服务器自身所使用的数据库，也可以说是管理服务器和用户数据库的数据库。Sybase在安装时，自动创建了四个系统数据库：master、model、tempdb、sybsystemprocs master数据库它是管理和控制用户数据库以及维护服务器正常运行的核心数据库，它保存了大量的系统信息，如服务器配置、用户、设备等。在master数据库中不允许普通用户在其中创建数据库对象，否则会使得master数据库的事务日志很快变满。如果事务日志用尽，就无法使用dump transaction命令释放master数据库中的空间。 model数据库它是为创建用户数据库而提供的模板。每当创建新的数据库时，SQL Server自动建立model数据库的一份拷贝，并把它扩充到用户所要求的大小，以此作为新用户数据库。Model数据库中包含每个用户数据库所要求的系统表。Model数据库可以被修改以便定制新创建的。 tempdb数据库它是个临时数据库， 为服务器运行与处理提供一个共享的存储区域，如group by和order by的中间结果就存放在这里。Tempdb的空间为服务器中所有数据库的所有用户所共享。每次重启SQL Server，服务器的一个自动进程都拷贝model数据库到tempdb数据库，并清除tempdb中原来的内容。因此tempdb中的用户表都是临时的。临时表分为两类：可共享的和不可共享的。不可共享的临时表在由create table中将符号#置于表名之前创立；可共享的临时表通过create table中指定表名前缀tempdb.而创立。不可共享的临时表SQL Server自动为其添加数字后缀名，且它只存在于当前会话中。 sybsystemprocs数据库master数据库它是专门用来保存系统命令（存储过程）的数据库，如sp_help、sp_configure、sp_helpdevice等。当任一数据库用户运行以sp_开头的存储过程时，SQL Server按照以下顺序查找： 当前数据库、sybsystemprocs数据库、master数据库。（2）用户数据库。用户数据库是我们使用Sybase服务器的真正目的。要管理用户数据，必须在Sybase中创建自己的数据库，它是指用create database命令创建的数据库。不能存取master数据库的用户是无权创建新的数据库的。数据库中的主要内容数据库对象： 表、视图、临时表 索引、主键、外键 缺省值、规则 存储过程、触发器2 Sybase的使用2.1 数据库的字符集问题SYBASE数据库缺省的安装选项是：语言为us_english，字符集为roman8。安装过程中一般要改变成语言为chinese,字符集为eucgb. 如果字符集设置不对，数据库操作过程中就可能产生软码现象。2.2 数据库服务器的系统配置和数据库的选项SYBASE数据库服务器的配置选项很多，常用的选项有：allow sql server async i/o（是否启用磁盘异步IO）number of user connections（最大的用户连接数）number of open databases（同时打开的数据库的最大数目）procedure cache percent（存储过程缓冲占总数据库内存的百分比）number of locks（锁的数目）max online engine（数据库最大引擎数目）number of worker processes（工作引擎数目）total memory（数据库总的物理内存）tcp no delay（控制TCP数据是否延迟发送）数据库安装完成后所有的选项都是缺省配置，必需根据具体的应用修改配置的值。修改方法有两种：（1）修改配置文件。修改$SYBASE用户下的server_name.cfg文本文件。（2）用sp_configure存储过程。进入isql，执行sp_configure option_name,option_valuego数据库常用的选项有：select into/bulk copy（允许大批数据加载到数据库表的选项，如bcp）truncate log on checkpoint（数据库执行checkpoint时自动删除已提交事务）single used（将数据库设置成单用户模式）2.3 服务器端客户程序isqlisql的语法如下：isql -b -e -F -p -n -v -X -Y-a display_charset -A packet_size -c cmdend -D database-E editor -h header -H hostname -i inputfile-I interfaces_file -J client_charset -K keytab_file-l login_timeout -m errorlevel -M labelname labelvalue-o outputfile -P password -R remote_server_principal-s col_separator -S server_name -t timeout -U username-V security_options -w column_width -z localename-Z security_mechanism使用方法是：isql Uusername Ppasswd Sserver_name2.4 数据库的备份SYBASE数据库的备份主要通过dump命令来实现的，备份的介质可以是文件系统、裸设备和磁带设备。（1）备份到文件系统。方法如下：dump database database_name to /home/sybase/zxin.dmp（2）备份到裸设备。在裸设备上，创建备份设备，然后用以下命令备份：dump database database_name to dump_device（3）备份到磁带设备。向在磁带上创建一个磁带备份设备，需要指定capacity和不自动回卷的设备文件。非回滚设备文件AIX上为 /dev/rmt0.1， HPUX上为/dev/rmt/0mn然后用如下命令来进行备份：dump database database_name to dump_device with init2.5 数据库的恢复SYBASE数据库的恢复主要通过load命令来实现的。简单的命令语法如下：load database database_name from device_name2.6 bcp实用程序的使用方法以用户指定的形式将数据库表拷贝到操作系统文件中，或从操作系统文件中拷贝数据到到数据库表。简化后的语法如下：bcp database_name. owner.table_name in | out datafile-U username -P password -Sserver_name-c | -n -t field_terminator -rrow_terminator其中：U username 指定用户名P password 指定用户口令S server_name 指定数据库服务器的名字。该名字在interfaces文件中必需存在。c 以字符方式输出或输入数据库表，用户可以查看文件的内容。n 以内部格式输出或输入数据库表，用户无法查看文件内容。t field_terminator 指定域分隔符。r row_terminator 指定行分隔符。例如， 将数据库z x d b _ 1 中的表s139cardinf表中的内容BCP到文件中的命令为：bcp zxdb_1.dbo.s139cardinf outs139cardinf.txt -Usa -P -Sscp1 -c -t | -r |n各域之间以 | 分隔，行结束符为 |加回车。2.7 给数据库增加空间的方法用sp_helpdb database_name可以查看某个数据库的空间分配情况。如果发现数据或日志空间过小，必需及时增加。增加方法如下。（1）扩数据空间。如给zxin库在设备zxindatadev设备上扩100M数据空间。alter database zxin on zxindatadev=100（2）扩日志空间。如给zxin库在设备zxinlogdev设备上扩100M日志空间。a l t e r d a t a b a s e z x i n l o g o nzxinlogdev=1002.8 数据库操作的注意事项（1）应避免直接用SYBASE的工具对数据库操作，尽量通过“智能网系统管理程序”和“智能网业务管理程序”来修改用户数据。（2）应尽量避免对呼叫中正在使用的记录进行数据量很大的更新（update）操作，如一次更新数万条记录，因为这样会在多页上加锁，甚至上升为表锁，造成锁冲突。如果必须做，请先将业务去激活，或者将SCP暂停再做。从时间上说，因为去激活时要等待所有呼叫释放，所以需要比较长的时间。如果业务很少，暂停SCP反而更快。（3）对于常用的SQL操作，特别是新增加的运行在版本中或业务中的操作，要仔细考虑它的效率，必要时在查询字段上加索引。SYBASE对索引的依赖性很强，虽然索引会影响一点增加/删除记录的速度，但从查询和更新的角度来看，速度一般能提高一个数量级。（4）在进行了大批数据的增加/删除后，请对该表作如下操作（如果是通过“业务管理程序”增加删除卡号的，那么程序中已经做了这个操作）：update ststistics table_name3 SYBASE数据库常见的故障和处理方法3.1 SYBASE磁盘异步IO问题问题：async i/o 始终配不上。前提：/dev/async文件已做，groupid正确，async driver已pend in。原因：sybase的group没有lock share memory的权限。解决办法： 在s b i n / r c 3 . d 下增加文件S996setprivgrp。具体信息如下：# cd sbin/rc3.d# ll S996*lrwxrwxrwx 1 root sys 26 May 30 15:56S996setprivgrp - /sbin/init.d/sybsetprivgrp# cat S996*# Set setprivgrp for sybase account#PATH=/sbin:/usr/sbin:/usr/binexport PATHrval=0case $1 instart_msg)echo Setting setprivgrp for sybase;start)/usr/sbin/setprivgrp sybase MLOCK RTPRIOCHOWN;*)echo usage: $0 startrval=1;esacexit $rval3.2 将SYBASE的数据和日志合一的数据库的日志分开现场经常出现在创建某个数据库时，没有把数据和日志分开建在不同的设备上，即某个设备上既有数据又有日志。出现这种情况对智能网数据库的维护很不利，空间检测程序也没法正常检测数据库的空间。如果出现这种情况，可以通过下面的方法来分离日志到其它的设备上。在i s q l 中使用存储过程： s p _ l o g d e v i c edbname,logdevicedbname是数据库名字，logdevice是把数据库的日志分离到的设备名。注意：在执行之前要先备份一下数据库。3.3 移植数据库到其它机器上，使数据和日志的存放顺序不产生混乱的方法查看原数据库的设备创建顺序以及大小，执行以下命令查看：select * from sysusages from sysusageswhere dbid=db_id(database_name) orderby lstart输出如下所示：其中：segmap表示该设备片的用途，3表示数据，4表示日志。size为该设备片的大小，单位为2K。按照原设备的信息创建数据库，注意连续相同的segmap可以合并，计算总的size值。以上面为例，执行如下的语句：create database database_name on datadev=200 logon logdev=200alter database database_name on datadev=300alter database database_name on logdev=100恢复数据库。3.4 SYBASE12.0 在HPUX11.11上安装时出错安装时，出现如下错误：/usr/lib/dld.sl: Unresolved symbol:_head (data) from /usr/lib/libC.2/usr/lib/dld.sl: Unresolved modulefor symbol: _shlinit (code) from /usr/lib/libCsup.2swlist l product |grep i aC+将该patch删除后即可，方法为：swremove PHSS_xxxxxx。3.5 由于关电或不掉电慎造成数据库无法启动从日志上看一般有两种情况：一是报告active disk失败，二是报告一大堆页的错误、数据段的错误等等。第一种情况通常只影响该设备涉及的数据库，在sybase central里看到的是这些库被suspect，sysdatabases表里状态为328。第二种情况由于这个库难以恢复，会阻塞在那里，造成排在它后面的其他库也无法恢复，在sybase central里看到的是这个库以及其他一些库都尚未恢复完。第一种情况的解决方式是先想办法把失败的数据库设备都激活，然后修改sysdatabases里这些库的状态，改为8，最后重启db server，一般可以解决问题。第二种情况的解决方式比较麻烦。先修改sysdatabases里这个故障库的状态，改为32768，然后重启db server。这时系统的恢复进程会bypass这个库。然后运行grant role sybase_ts_role to sa，即把sybase_ts_role的角色赋予sa，然后退出isql，再重新以sa进入。这时运行dbcc rebuild_log(dbname,1,1)重建日志。成功后，将sysdatabases里这个故障库的状态改为8，然后重启db server。检查一下库的状态，查看是否已经正常。如果正常，运行dbcc checkalloc(dbname)或dbcc checkalloc(dbname,fix)，前者只检查不修复。一般检查一下，如果没问题，问题就算解决了。如果rebuild_log后数据库仍然未恢复，那么就只能用以前的备份还恢复该库了。如果恢复也不行，就drop database dbname或dbcc dbrepair(dbname,dropdb)删除数据库，重建数据库，再用备份恢复。3.6 用户数据库出故障时快速地重启数据库服务器（1）用命令update sysdatabases set status=status+320 where name=将故障数据库手工置为suspect状态。（2）reboot dataserver。此时数据库服务器在恢复数据库时回跳过该数据库。（3）在数据库服务器起动完成后手工用以下命令恢复该数据库或等系统闲时再做其它处理：update sysdatabases set status=status-256 wherename=godbcc traceon(3604)godbcc dbrecover()go注意，需要首先将参数allow updates to systemtable置为1。3.7 sybase数据库suspect状态的修复在sql server 的advantage中如果某个数据库的状态变成suspect状态，此时无法打开该数据库。因此需要对该数据库进行修复。方法如下（修复时需要停业务）：（1）选择 master库执行sp_configure allow updatesto system tables,1（2）执行：update sysdatabases set status32768 where namezxinchargedbcc save_rebuild_log(zxincharge)（3）重新启动mssql server服务。（4）执行：update sysdatabases set status0 where namezxinchargedbcc save_rebuild_log(zxincharge)（5）重新启动mssql server服务。（6）执行：sp_configure allow updates to system tables3.8 当数据库备份的设备文件大于2G时无法进行正常的备份解决方法：增加多个备份的设备，可以解决相应的备份设备只能小于2G的问题。举例：登录isql，对zxincharge库进行备份。将库zxincha rge备份的设备为/home / syba s e /zxinchargebak.dat和/home/sybase/zxinchargebak1.dat，每个备份的设备最大为2G，总共限制为4G。备份方法：dump database zxincharge to /home/sybase/z x i n c h a rg e b a k . d a t s t r i p e o n / h ome / s y b a s e /zxinchargebak1.dat其余依此类推。恢复方法：load database zxincharge from/home/sybase/z x i n c a h rg e b a k . d a t s t r i p e o n / h ome / s y b a s e /zxinchargebak1.dat3.9 删除坏的用户数据库当使用drop database无法删除数据库时，使用本文所示方法可以删除。（1）使用isql以sa注册SQL server。（2）设置允许修改系统表：1sp_configure allow updates,12go（3）把要删除的用户数据库置为suspect状态。1use master2go1begin tran2go 1u p d a t e s y s d a t a b a s e s s e tstatus=2562where name=pubs23go如果得到(1 row affected)，则1commit2go否则1rollback2go（4）重启server，并用isql以sa注册。（5）删除数据库。1dbcc dbrepair(pubs2,dropdb)2go（6）恢复允许修改系统表：1sp_configure allow updates,02go（7）结束。3.10 找回SYBASE的SA密码的方法（1）在sybase目录的install子目录的启动server文件。RUN_server名，先备份该文件，然后编辑该文件，在末尾增加-psa（如果文件最有有&符号，则在&前面增加这一行），保存该文件。（2）如果服务器已经启动，先停止。（3）执行第1步批处理文件以启动server，在启动最后显示信息出现sa的新口令，并记录。（4）切换到sybase central以sa帐号登录，口令为新记录的口令。（5）进入sybase central，选择sa用户，修改其密码。（6） 回到第1 步， 去掉增加的选项-psa，保存退出，重启sybase数据库服务。3.11 Sybase12.5下如何增加虚拟设备数问题描述：某局反映现场数据库设备数超过10个后，无法新建新的数据库设备，提示“已达最大设备数”。解决方法：sybase数据库中，数据库设备数这个约束值是通过配置参数number of device来完成的。只有数据库设备数小于number of device参数的值时才可以新建，否则将提示失败。当数据库设备数达到number of device设置的最大值后，可以通过手工修改number of device 来达到创建新设备的目的。操作方法如下：（1）打开sql advantage客户端；（2）执行sp_configure number of device, value；value为需要设置的设备数。由于number of device 是动态参数，故不需要重启数据库服务即可使其生效。 Sybase SQL Server的日志管理机制介绍中兴通讯固网南京用服部 张晓忠摘 要： 介绍sybase数据库 SQL Server的日志管理机制，数据库日志备份、清空等日常维护操作方法。关键词： SQL Server log1 SYBASESQL Server 如何记录和读取日志信息SYBASESQL Server是先记Log的机制。每当用户执行修改数据库的语句时，SQLServer就会自动地把变化写入日志。一条语句所产生的所有变化都被记录到日志后，它们就被写到数据页在缓冲区的拷贝里。该数据页保存在缓冲区中，直到别的数据页需要该内存时，该数据页才被写到磁盘上。若事务中的某条语句没能完成，SQLServer将回滚事务产生的所有变化。这样就保证了整个数据库系统的一致性和完整性。2 日志设备Log和数据库的Data一样，需要存放在数据库设备上，可以将Log和Data存放在同一设备上，也可以分开存放。一般来说，应该将一个数据库的Data和Log存放在不同的数据库设备上。这样做有如下好处：一是可以单独地备份Backup事务日志；二是防止数据库溢满；三是可以看到Log的空间使用情况。所建Log设备的大小，没有十分精确的方法来确定。一般来说，对于新建的数据库，Log的大小应为数据库大小的30%左右。Log的大小还取决于数据库修改的频繁程度。如果数据库修改频繁，则Log的增长十分迅速。所以说Log空间大小依赖于用户是如何使用数据库的。此外，还有其它因素影响Log大小，我们应该根据实际操作情况估计Log大小，并间隔一段时间就对Log进行备份和清除。3 日志的清除随着数据库的使用，数据库的Log是不断增长的，必须在它占满空间之前将它们清除掉。清除Log有两种方法。（1）自动清除法。开放数据库选项 Trunc Log on Chkpt，使数据库系统每隔一段时间自动清除Log。此方法的优点是无须人工干预，由SQLServer自动执行，并且一般不会出现Log溢满的情况；缺点是只清除Log而不做备份。（2）手动清除法。执行命令“dump transaction”来清除Log。以下两条命令都可以清除日志：dump transaction with truncate_onlydump transaction