Sybase数据库维护篇--高级

1、SYBASE培训教材0培训计划 培训内容 培训目标 培训对象SYBASE培训教材1培训内容SybaseSybase数据库简介数据库简介SybaseSybase产品（产品（ASAASA，ASEASE，IQIQ）ASEASE它是基于客户它是基于客户/ /服务器体系结构的数据库服务器体系结构的数据库 OLTPOLTP 1:Oracle Server是一个真正的多进程数据库引擎，Sybase数据库管理系统采用的是单进程多线程的引擎结构。 2:Sybase的数据库引擎还代替操作系统来管理一部分硬件资源，如端口、内存、硬盘，绕过了操作系统这一环节，提高了性能。SYBASE培训教材2数据库(raid0,1,

2、5)SYBASE培训教材3数据库设备分配结构SYBASE培训教材4数据库page分配结构SYBASE培训教材5Sybase的多库结构安装Sybase数据库时自动生成的下列系统数据库：主数据库master: 数据库全面控制和管理用户数据库以及SQLSERVER上的一切操作。模型数据库model: 为新建的用户数据库提供一个原形系统过程数据库sybsystemprocs: SYBASE的系统存储过程被储存在这个数据库中临时数据库tempdb: 作为临时表和其他的临时工作空间提供一个存储区域。TEMPDB的空间为全体数据库用户共享也可选择下列数据库：安全审核数据库sybsecurity: 包含SQL

3、SERVER的审计系统示例数据库pubs2: 这是一个样本库 命令语法数据库sybsyntax: 为用户提供联机帮助用户数据库 用户数据库是指用Create database 命令创建的数据库。 使用”use 数据库名”来切换不同的数据库 执行sp_changedbowner 改变数据库的属主关系 建议事务日志放在单独的设备上(log)SYBASE培训教材6master库它是管理和控制用户数据库以及维护服务器正常运行的核心数据库，它包含许多系统表和系统过程，从总体上控制用户数据库和SQL Server的操作，构成了SYBASE系统的数据字典，它保存了大量的系统信息，如服务器配置、用户、设备等。

4、在master数据库中不允许普通用户在其中创建数据库对象，否则会使得master数据库的事务日志很快变满。如果事务日志用尽，就无法使用dump transaction命令释放master数据库中的空间。master数据库主要记录信息为： 登录帐号（SQL服务器用户名）；syslogins,sysremolelogins 正在执行的过程；sysprocesses 可修改的环境变量；sysconfigures 系统错误信息；sysmessages SQL服务器上每个数据库的信息；sysdatabases 每个数据库占用的存储空间；sysusages 系统上安装的设备及磁带信息；sysdevices

5、 活动锁信息；syslocksSYBASE培训教材7TEMPDB库 它是个临时数据库，为服务器运行与处理提供一个共享的存储区域，如group by和order by的中间结果就存放在这里。Tempdb的空间为服务器中所有数据库的所有用户所共享。 每次重启SQL Server，服务器的一个自动进程都拷贝model数据库到tempdb数据库，并清除tempdb中原来的内容。因此tempdb中的用户表都是临时的。临时表分为两类：可共享的和不可共享的。不可共享的临时表在由create table中将符号#置于表名之前创立；可共享的临时表通过create table中指定表名前缀tempdb.而创立。不

6、可共享的临时表SQL Server自动为其添加数字后缀名，且它只存在于当前会话中SYBASE培训教材8数据库有关概念（segment/device)数据库设备从逻辑上被划分为数据库段以允许将某一特定的对象放置在指定的段上（创建对象时指定），一数据库设备可拥有多达192个段，一段可使用255个逻辑设备上的存储空间。当用户创建一个数据库时，SQL SERVER 会自动在该数据库中创建三个段： SYSTEM、LOGSEGMENT 、DEFAULT，这三个段分别用来存储数据库的系统表、事务日志和其他数据库对象提高性能：处于不同磁盘设备上的段可以并行地读写；处理大表：利用段，可以将一个大表分段放在独立的

7、物理设备上。所谓数据库设备，不是指一个可识别的物理设备，而是指用于存储数据库和数据库对象的磁盘原始分区或操作系统文件。初始化数据库设备使用DISK INIT命令： DISK INITNAME=设备名，PHYSNAME=物理设备名，VDEVNO=虚拟设备号，SIZE=块数SYBASE培训教材9数据库有关概念（segment/device)Sybase segments Table创建于segment上 每个db最多可有32个segments创建段：sp_addsegment seg, db_name, device_name注：创建表需要指定索引和数据的存放段名称Device 和 segment

8、 关系：单个device上可以创建多个segment，单个segment可以跨越多个deviceTable和segment关系： 单个segment上创建多个table，单个table可以跨越多个segment（分区表）。SYBASE培训教材10数据库对象(1) 表(Tables) (2) 视图(Views) : 视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。对其中所引用的基础表来说，视图的作用类似于筛选。 作用: 安全 ; 可使复杂的

9、查询易于理解和使用 SYBASE培训教材11Index与性能索引(index): 索引就是加快检索表中数据的方法。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必Table scan 。 1. 索引可以加快数据库的检索速度2. 索引降低了数据库插入、修改、删除等维护任务的速度3. 索引创建在表上，不能创建在视图上 4. 索引既可以直接创建(create index) ，也可以间接创建 ( 主键约束或者唯一性键约束时)Sybase支持三种不同类型的索引，一是复合索引，即包含几个列的索引；二是唯一性索引，即任意两行中的索引列不能有相同的取值；三是簇类索引（Clustered index

10、es）和非簇类索引(Nonclustered indexs)，SYBASE培训教材12Index与性能-2a) 普通索引(create index) 和唯一性索引(create UNIQUE index)b) 单个索引和组合索引( 最多16 个字段 组成)c) 聚簇索引CLUSTERED 和非聚簇索引UNCLUSTERED :聚簇索引：即物理索引，与基表的物理顺序相同(不断重排表行的物理顺序以使其与逻辑索引顺序相一致)，数据值的顺序总是按照顺序排列. 即索引树的叶级页包含实际的数据 . 非聚簇索引: 索引顺序与数据物理排列顺序无关, 即叶级页指向表中的记录. , 一个表最多可建249 个非聚簇

11、索引. 可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块. 每个表最多只能建立一个簇类索引，非簇类索引则可建立多个。与非簇类索引相比，簇类索引的检索速度要快。应当在建立任何非簇类索引以前建立簇类索引。SYBASE培训教材13Index与性能-31 1：选择选择ClusteredClustered索引索引的建议：的建议：主键时常作为主键时常作为wherewhere子句的条件子句的条件 某一列经常以这样的格式出现在某一列经常以这样的格式出现在wherewhere表达式中表达式中(x=column =y) (x=column use

12、 master 2go 1sp_dboption database_name,trunc,true 2go 1use database_name 2go 1checkpoint 2go Bcp. -b 100 (on unix) -一个批执行100行拷贝SYBASE培训教材22Sybase事务日志-5增加阈值（增加阈值（sp_addthreshold）例如：sp_addthreshold pubs2, logsegment, 200, dump_transaction 其中，存储过程定义为： create procedure dump_transaction dbname varchar (3

13、0), segmentname varchar (30), space_left int, status int as dump transaction dbname to /dev/rmtx那么，当日志段上可用空间小于200页时，SQL Server运行存储过程dump_transaction,将pubs2 数据库的日志转储到另一台设置上。SYBASE培训教材23数据库锁数据库行数据库行/ /页页/ /表锁表锁为了在使用的一个表中改变加锁类型，可以采用如下的句法格式：alter table lock allpages | datapages | datarows在索引页面中不会破坏事务加锁。

14、相反，而是采用了一种称之为锁存的机制。锁存是一种类似于旋转锁（spinlocks）的同步方法sp_configure number of pre-allocated extent设置为设置为16也将对系统性能也将对系统性能有明显的积极的效果有明显的积极的效果，减小锁资源的竞争。，减小锁资源的竞争。共享锁、排他锁，死锁共享锁、排他锁，死锁数据库的锁存技术将那些在索引页中发生死锁的情形尽量排除掉。减小死锁的方法：1：尽量对数据多次并发访问2：配置“带有加锁方式的读提交”（read committed with lock”），并可用如下的命令加以配置：sp_configure read commit

15、ted with lock,0|1SYBASE培训教材24数据库死锁可能发生的死锁可能发生的死锁的原因：的原因：1：插入与非群聚性的索引访问2：数据更新的范围当第一个用户到达所确定的页面边缘，即该范围即将终止，下一批所组块并妨碍的页面范围即将开始之时，就可能发生死锁。3：按照不同顺序访问表即使访问行是不同的，对表按照任何不一致的顺序进行访问，也会意味着可能发生死锁。4：如何查锁阻塞Sp_who sp_lock , select * from sysprocesses where blocked 05: 如何查锁的表名 sp_lock-查其中查其中table_id=XXXXXXX对应的表对应的表

16、select * from sysobjects where id = XXXXXXXSYBASE培训教材25数据库的备份和恢复数据库的备份：必须启动backup server由于文件的大小超出了操作系统的用户最大文件限制。而有些操作系统不支持大于2GB的文件，这时可以使用Backup Server将一个数据库后备到多个文件中。dump database pubs2 to /usr/sybase/pubs2_dump.1stripe on /usr/sybase/pubs2_dump.2stripe on /usr/sybase/pubs2_dump.3go 这种方法还可以提高后备及恢复的速度

17、，但注意恢复也必须用相应多的设备。例如：1load database pubs2 from /usr/sybase/pubs2_dump.12stripe on /usr/sybase/pubs2_dump.23stripe on /usr/sybase/pubs2_dump.34go SYBASE培训教材26如何生成bcp命令文件以pubs2为例编辑一个文本文件select.sql，内容如下： set nocount on use pubs2 go select bcp pubs2. + name + out + name + .bcp -Usa -P -c from sysobjects

18、where type=U goSYBASE培训教材27检查修改服务器基本配置参数检查修改服务器基本配置参数内存配置：内存配置：sp_configure “total memory”, new_value (修改内存配置，new_value的单位为2K)CPU数：数：sp_configure “max online engines”, cpu_number(修改CPU配置，cpu_number小于等于实际cpu数)并发用户连接数：并发用户连接数：sp_configure “number of user connections” (检查当前的并发用户连接数)sp_configure number

19、of locksp_configure procedure cache sizesp_configure number of usersp_configure number of open databasessp_configure number of open objects,150000sp_configure number of open indexes,150000SYBASE培训教材28如何解决数据库被挂起的问题1现象：现象：Error 926 Severity Level 14 Error Message Text Database xx cannot be opened - it

20、 has been marked SUSPECT by recover Explanation (一一)：你使用你使用Transact_SQL命令操作这个数据库的数据时命令操作这个数据库的数据时, 出现这个信息出现这个信息, 这是一个这是一个严重的错误严重的错误, 如果你要使用这个数据库的数据如果你要使用这个数据库的数据, 必须改正这个错误必须改正这个错误. (二二) ：启动启动Backup Server, 后备后备master数据库数据库1dump database master to /usr/sybase/master.dup2go（三（三) 用用isql登录到登录到SQL Server

21、, 须用须用sa帐号帐号 (本文以本文以pubs2数据库为例数据库为例)1sp_configure “allow updates”, 1 -允许修改允许修改mask库库2go 1begin tran 2go 1use master2go1update sysdatabases 2set status = -32768 3Where name=pubs2 4goSYBASE培训教材29如何解决数据库被挂起的问题2如果得到如果得到(1 row affected),则则1commit2go否则否则1rollback2go（四（四)重新启动重新启动SQL Server. 注：注：SQL Server重

22、新启动之后，当发现数据库本身存在重新启动之后，当发现数据库本身存在不可恢复的问题不可恢复的问题时，如数时，如数据页损坏等，且没有完好的数据库备份，一定要用据页损坏等，且没有完好的数据库备份，一定要用bcp.out备份用户数据库数据备份用户数据库数据。此时，以下步骤省略，并按照。此时，以下步骤省略，并按照“如何删除坏的用户数据库如何删除坏的用户数据库”文章删除此数据库。文章删除此数据库。之后重建此数据库，恢复备份。之后重建此数据库，恢复备份。否则，按以下步骤继续操作：否则，按以下步骤继续操作：用用sa帐号注册到帐号注册到SQL Server. 1begin tran 2go 1use maste

23、r2goSYBASE培训教材30如何解决数据库被挂起的问题31update sysdatabases 2set status=0 3Where name=pubs2 4go 如果得到如果得到(1 row affected),则则1commit 1commit 2go2go否则否则 1rollback 2go2go1sp_configure “allow updates” ,0-不允许修改不允许修改maskmask库库2go (五五)重新启动重新启动SQL Server.(六六) 如果你的数据库原来有如果你的数据库原来有dboption(例如例如select into,trunc log on

24、chkpt等等), 你你需要重新设置这些需要重新设置这些option.(七七) 数据库已经恢复可使用状态后，运行数据库已经恢复可使用状态后，运行dbcc命令检查数据库的一致性命令检查数据库的一致性(八八) 备份用户数据库：备份用户数据库：1dump database pubs2 to /usr/sybase/pubs2.dup2goSYBASE培训教材31 如何终止数据库恢复过程1当某一正常运行的大事务（例如：update、delete操作）被终止，且重新启动server后，运行该事务的数据库处于恢复状态，通常这种状态会持续很长时间，当在此恢复过程中没有出现任何异常时，建议耐心等待恢复过程完成

25、。同时提供以下方法来终止此恢复过程，但请注意这些操作将带来数据不一致性。必要时，希望用户用完整、可靠的数据库备份恢复此数据库。 (1) 启动Backup Server后，备份master数据库（2）用isql登录到SQL Server, 须用sa帐号 (本文以pubs2数据库为例)1sp_configure allow updates, 1 2go 1begin tran 2go 1 use master2 go1update sysdatabases 2set status = -32768 3Where name=pubs2 4go如果得到(1 row affected),则commit提

26、交，否则rollbackSYBASE培训教材32 如何终止数据库恢复过程2（3）这时重新启动这时重新启动SQL Server, 再用再用sa帐号登录到帐号登录到SQL Server. 1dump tran pubs2 with no_log2go1begin tran 2go 1 use master2 go1update sysdatabases 2set status=0 3Where name=pubs2 4go 如果得到(1 row affected),则commit提交，否则rollback(4) 如果你的数据库原来有如果你的数据库原来有dboption(例如例如select int

27、o,trunc log on chkpt等等), 你需要重新设置这些你需要重新设置这些option. (5) 当数据库已经恢复可使用状态后，运行当数据库已经恢复可使用状态后，运行dbcc命令检查数据库的一致性命令检查数据库的一致性(参照参照如何检查数据库中数据一致性如何检查数据库中数据一致性文章文章)(6) 备份备份用户数据库用户数据库SYBASE培训教材33如何删除坏的用户数据库当使用当使用drop database无法删除数据库时，使用本文所示方法可以删除。无法删除数据库时，使用本文所示方法可以删除。(1)设置允许修改系统表设置允许修改系统表1sp_configure allow upda

28、tes,12go(2)把把 要删除的用户数据库置为要删除的用户数据库置为suspect状态状态1use master2go1begin tran2go1update sysdatabases set status=2562where name=pubs23go如果得到如果得到(1 row affected),则则则commit提交，否则rollback(3)重启重启server,并用并用isql以以sa注册。注册。(4)删除数据库删除数据库1dbcc dbrepair(pubs2,dropdb)2go (5)恢复允许修改系统表恢复允许修改系统表1sp_configure allow updat

29、es,02goSYBASE培训教材34数据一致性检查1数据库一致性检查(dbcc)提供了一些命令用于检查数据库的逻辑和物理一致性。Dbcc主要有两个功能：使用checkstorage 或 checktable 及 checkdb 在页一级和行一级检查页链及数据指针。 使用checkstorage, checkalloc, 或 checkverify, tablealloc, 及indexalloc 检查页分配。 在下列情况中需要使用 dbcc 命令：作为数据库日常维护工作的一部分, 数据库内部结构的完整性决定于sa 或dbo 定期地运行 dbcc 检查。 在系统报错以后, 确定数据库是否有损坏。 在备份数据库之前, 确保备份的完整性。 如果怀疑数据库有损坏时, 例如, 使用某个表时报出表损坏的信息, 可以使用 dbcc 确定数据库中其他表是否也有损坏。 SYBASE培训教材35数据一致性检查2下面是dbcc的简单用法：dbcc checktable (table_name)检查指定的表, 索引和数据页是否正确链接, 索引是否正确排序, 所有指针是否一致, 每页的数据信息是否合理, 页偏移是否合