SYBASE数据库系统检查和日常维护命令指南.doc

Sybase数据库系统检查与日常维护手册以下是对sybase数据库系统检查与日常维护的具体方法。并且定义了每天日常检查，以及每月的定期维护命令。进行下列操作前请先使用sybase用户登录主机。命令字体为加粗且倾斜，命令中使用的一些需要检查人员根据各自数据库系统实际情况输入的参数加下划线表示，结果信息中需要特别关注的选项已加粗且倾斜标注。1 SYBASE日常维护1.1 sybase数据库服务器运行状态检查检查方法：使用showserver命令，不带任何参数，观察命令的返回信息。结果判断，详见下表：检查内容正常状态非正常状态和采取措施数据库运行状态返回数据库服务器进程、备份服务器进程信息无进程信息返回措施：代表数据库当前为非联机状态，需要重新启动数据库。命令举例：bash-2.05$ showserver显示如下信息，包含sybaseserver 与bakupserver 两个server信息F S UID PID PPID C PRI NI ADDR SZ WCHAN STIME TTY TIME CMD000 S sybase 1003 1000 0 76 0 - 2253 schedu 09:19 ? 00:00:00 /opt/sybase-12.5/ASE-12_5/bin/backupserver -e/opt/sybase-12.5/ASE-12_5/install/S000 S sybase 1001 996 1 75 0 - 16042 schedu 09:19 ? 00:00:47 /opt/sybase-12.5/ASE-12_5/bin/dataserver -d/opt/sybase-12.5/ASE-12_5/install/mas执行周期：可随时执行。1.2 SYBASE数据库运行状态检查检查方法：isql命令行登录数据库，使用sp_helpdb命令，不带任何参数，观察命令的返回信息。结果判断，详见下表：检查内容正常状态非正常状态和采取措施数据库运行状态返回所有数据库信息数据库有offline标记措施：代表数据库当前为非联机状态，需要确认offline的原因，如有必要，可联系SYBASE支持小组命令举例：bash-2.05$ isql Usa -P保密字:1 sp_helpdb2 goname db_size owner dbid created status - - - - - - archivedb 100.0 MB sa 6 Sep 06, 2006 read only, no free space acctg, archive, compressed ksqhdb 5000.0 MB sa 4 Sep 05, 2006 select into/bulkcopy/pllsort master 50.0 MB sa 1 Sep 05, 2006 mixed log and data model 2.0 MB sa 3 Sep 05, 2006 mixed log and data scratchdb 400.0 MB sa 5 Sep 06, 2006 scratch sybsystemdb 3.0 MB sa 31513 Sep 05, 2006 mixed log and data sybsystemprocs 124.0 MB sa 31514 Sep 05, 2006 trunc log on chkpt, mixed log and data tempdb 3.0 MB sa 2 Sep 07, 2006 select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data (1 row affected) name attribute_class attribute int_value char_value comments - - - - - - archivedb Archive Database Access dump device NULL /tmp/ksqhdb20060801.dmp.gz NULL archivedb Archive Database Access scratch database NULL scratchdb NULL (return status = 0)1.3 Sybase数据库逻辑日志空间检查检查方法：isql命令行登录数据库，use dbname选择需要查看信息的数据库，使用sp_helpsegment logsegment或dbcc checktable(syslogs)命令，观察命令的返回信息。结果判断，详见下表：检查内容正常状态非正常状态和采取措施数据库日志状态Freepages占totalpages总数的50以上日志可用空间在50以下,详细措施后文详述。如有必要联系SYBASE支持小组确认解决方案。命令举例：bash-2.05$ isql Usa -P1 use_ksqhdb2 go1 sp_helpsegment logsegment2 gosegment name status - - - 2 logsegment 0 device size - - ksqhdblog 1000.0MB free_pages - 509925 table_name index_name indid - - - syslogs syslogs 0 total_size total_pages free_pages used_pages reserved_pages - - - - - 1000.0MB 512000 509925 2075 0 1 use use_ksqhdb2 go1 dbcc checktable(syslogs)2 goChecking syslogs: Logical pagesize is 2048 bytes The total number of data pages in this table is 75. * NOTICE: Space used on the log segment is 2075 pages (4.05 MB), 0.41%. Server Message: Number 15000, Severity 10Server SYBASE, Line 1:* NOTICE: Space reserved on the log segment is 0 pages (0.00 MB), 0.00%. * NOTICE: Space free on the log segment is 509925 pages (995.95 MB), 99.59%. Table has 1450 data rows. DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.注：1.dbcc checktable命令只能针对日志段拥有独立数据设备的情况，如果数据段和日志段混合使用数据设备只能使用sp_helpsegment logsegment命令。建议使用 dbcc chektable命令，结果较直观。2.对于日志可用空间低于50%的情况，解决步骤如下：A 使用sp_helpdb dbname命令判断该数据库的日志段拥有的设备是独立设备还是混合设备bash-2.05$isql Usa P1sp_helpdb master2 goname db_size owner dbid created status - - - - - - master 50.0 MB sa 1 Sep 05, 2006 mixed log and data device_fragments size usage created free kbytes - - - - - master 6.0 MB data and log Sep 5 2006 2:42PM 1546 master 44.0 MB data and log Sep 5 2006 2:43PM 44816 device segment - - master default master logsegment master system 出现data and log字样，代表日志段使用混合设备1 sp_helpdb ksqhdb2 goname db_size owner dbid created status - - - - - - ksqhdb 5000.0 MB sa 4 Sep 05, 2006 select into/bulkcopy/pllsort device_fragments size usage created free kbytes - - - - - ksqhdb_part1 1000.0 MB data only Sep 6 2006 9:37AM 705104 ksqhdb_part2 1000.0 MB data only Sep 6 2006 9:37AM 1019490 ksqhdb_part3 1000.0 MB data only Sep 6 2006 9:37AM 1006902 ksqhdb_part4 1000.0 MB data only Sep 6 2006 9:37AM 1020000 ksqhdblog 1000.0 MB log only Sep 6 2006 9:37AM not applicable - log only free kbytes = 1019850 所有设备均显示data only或log only，代表数据段和日志段都使用独立设备B 如果日志段使用独立设备，当可用空间为50%以下时，使用dump tran dbname to dumpfile命令备份并截断非活动日志C 如果日志段使用混合设备或者数据库选项trunc log on chkpt被打开，当可用空间为50%以下时，需确保当前数据库为空闲状态后，运行dump tran dbname with truncate_only命令截断非活动日志。日志被截断后后，请尽快执行dump database dbname命令，确保数据库故障时可以利用备份介质恢复到故障发生点。D 一般情况禁止使用dump tran dbname with no_log截断日志空间，该命令仅用于数据库崩溃后的紧急恢复。执行周期：可随时执行。1.4 SYBASE数据库日志文件检查检查方法：检查SYBASE数据库.log消息日志文件，详见下表：检查内容正常状态非正常状态和采取措施数据库消息日志消息日志中无severity大于18的出错信息， 消息日志中出现severity大于18的信息，代表数据库发生关键错误，联系SYBASE支持小组确认解决方案。命令举例：bash-2.05$ tail -100 /opt/sybase/ASE-12_5/install/SYBASE.log 此处应根据实际情况确定需要查看消息日志文件长度。00:00000:00001:2006/09/07 14:51:03.20 server Started estimating recovery log boundaries for database archivedb.00:00000:00001:2006/09/07 14:51:03.20 server Database archivedb, checkpoint=(2385377, 4), first=(2385377, 4), last=(2385377, 4).00:00000:00001:2006/09/07 14:51:03.20 server Completed estimating recovery log boundaries for database archivedb.00:00000:00001:2006/09/07 14:51:03.20 server Started ANALYSIS pass for database archivedb.00:00000:00001:2006/09/07 14:51:03.20 server Completed ANALYSIS pass for database archivedb.00:00000:00001:2006/09/07 14:51:03.20 server Started REDO pass for database archivedb. The total number of log records to process is 1.00:00000:00001:2006/09/07 14:51:03.23 server Completed REDO pass for database archivedb.00:00000:00001:2006/09/07 14:51:03.23 server Recovery of database archivedb will undo incomplete nested top actions.00:00000:00001:2006/09/07 14:51:03.23 server Started recovery checkpoint for database archivedb.00:00000:00001:2006/09/07 14:51:03.23 server Completed recovery checkpoint for database archivedb.00:00000:00001:2006/09/07 14:51:03.24 server Started filling free space info for database archivedb.00:00000:00001:2006/09/07 14:51:03.24 server Completed filling free space info for database archivedb.00:00000:00001:2006/09/07 14:51:03.24 server Started cleaning up the default data cache for database archivedb.00:00000:00001:2006/09/07 14:51:03.24 server Completed cleaning up the default data cache for database archivedb.00:00000:00001:2006/09/07 14:51:03.24 server The transaction log in the database archivedb will use I/O size of 2 Kb.00:00000:00001:2006/09/07 14:51:03.25 server Database archivedb is now online.00:00000:00001:2006/09/07 14:51:03.31 server Recovery has restored the value of local async prefetch limit for 16K pool in default data cache from 80 to DEFAULT.00:00000:00001:2006/09/07 14:51:03.31 server Recovery has restored the value of local async prefetch limit for 2K pool in default data cache from 80 to DEFAULT.00:00000:00001:2006/09/07 14:51:03.38 server Recovery has restored the original size for 16K pool and 2K pool in default data cache.00:00000:00001:2006/09/07 14:51:03.41 server Recovery complete.00:00000:00001:2006/09/07 14:51:03.41 server SQL Servers default unicode sort order is binary.00:00000:00001:2006/09/07 14:51:03.42 server SQL Servers default sort order is:00:00000:00001:2006/09/07 14:51:03.42 server bin_iso_1 (ID = 50)00:00000:00001:2006/09/07 14:51:03.42 server on top of default character set:00:00000:00001:2006/09/07 14:51:03.42 server iso_1 (ID = 1).00:00000:00001:2006/09/07 14:51:03.42 server Master device size: 60 megabytes, or 30720 virtual pages. (A virtual page is 2048 bytes.)执行周期：可随时执行。2 SYBASE数据库周期维护2.1 说明本章命令不必每天都执行，可以根据数据库使用状况将检查周期定为一周或进行每月进行不定期检查2.2 Sybase数据库锁使用情况检查检查方法：isql命令行登陆数据库，使用sp_monitorconfig number of lock命令，观察命令的返回信息。结果判断，详见下表：检查内容正常状态非正常状态和采取措施数据库锁使用情况当前活动锁百分比（Pct_act）小于80%, Max_Used小于锁总数曾经使用锁个数(Max_Used)接近或超过锁总数，代表曾有事务大量使用锁措施：考虑增加锁个数，如有必要联系SYBASE支持小组确认解决方案。命令举例：bash-2.05$ isql Usa -P1 sp_monitorconfig number of locks2 goUsage information at date and time: Sep 7 2006 3:11PM. Name Num_free Num_active Pct_act Max_Used Num_Reuse - - - - - - number of locks 4915 85 1.70 107执行周期：可随时执行。2.3 SYBASE数据库有效性检查检查方法：isql登陆数据库，使用dbcc checkdb命令，观察命令返回信息。建议检查master库和用户库结果判断：检查内容正常状态非正常状态和采取措施数据库有效性检查无报错信息（正常和Warning信息除外）有报错信息措施：联系Sybase支持小组确认解决方案。命令举例：bash-2.05$ isql Usa -P1 dbcc checkdb (master)2 goChecking spt_mda: Logical pagesize is 2048 bytesThe total number of data pages in this table is 6.Table has 191 data rows.Checking spt_jtext: Logical pagesize is 2048 bytesThe total number of data pages in this table is 1.The total number of TEXT/IMAGE pages in this table is 1.Table has 1 data rows.Checking spt_jdbc_conversion: Logical pagesize is 2048 bytesThe total number of data pages in this table is 1.Table has 20 data rows.Checking jdbc_function_escapes: Logical pagesize is 2048 bytesThe total number of data pages in this table is 2.Table has 89 data rows.Checking t1: Logical pagesize is 2048 bytesThe total number of data pages in this table is 1.Table has 1 data rows.DBCC execution completed. If DBCC printed error messages, contact a user withSystem Administrator (SA) role.执行周期：每隔两周非业务高峰时执行。2.4 sybase数据库数据库空间使用情况检查检查方法：isql命令行登陆数据库，使用sp_spaceused dbname 结果判断，详见下表：检查内容良好状态不良状态和采取措施Database空间使用情况各database剩余空间(reserved/database_size)均在10以上存在database剩余空间不足10措施：为此database增加空间。“database扩容”为重大操作内容，操作前，一定要备份master数据克和用户数据库，完成后务必告知相关部门建议对master库和用户库做定期检查命令举例：bash-2.05$isql Usa -P1 sp_spaceused dbname2 go database_name database_size - - dbname 6