Sybase数据库技术，数据库恢复专家

本文转自：http://www.fwolf.com/blog/post/357 作者:Fwolf

开始之前

先把ubuntu装上，用7.04的server安装盘默认安装即可，先装i386的，如果sybase安装顺利，再试试64位的sybase是否好用。

从sybase网站上注册免费用户之后，可以下载免费的ASE Express Edition（下载页面），目前的版本是15.0.2，限制只能使用1个cpu，2G内存和管理5G数据，更详细的资料在快速安装手册中。

如果您也想自己装一个试试，建议您先跳过下面这一段。

安装

下载后的ase1502_xe_linux.tgz，直接解压到一个目录，比如ase1502中，然后运行./setup就可以开始安装了。默认的安装是java图形界面的，而我是远程ssh上去装的，所以加上-console参数运行setup。首先是欢迎信息：

Welcome to the InstallShield Wizard for Sybase Adaptive Server Enterprise Suite
version 15.0.2 GA

The InstallShield Wizard will install Sybase Adaptive Server Enterprise Suite
on your computer. Shut down all Sybase products, then click Next to begin
installation.

Press 1 for Next, 3 to Cancel or 4 to Redisplay [1]

按1继续，接着是选择用户协议类型：

What would you like to install?

Product License

   1. Adaptive Server Enterprise for evaluation
   2. Adaptive Server Enterprise (Developer Edition)
   3. Adaptive Server Enterprise (Express Edition)

Enter one of the options above : [1] 3

1是体验版，应该是有时间限制，2是开发者版，有连接数限制，所以选3继续：

 1)  All regions
Please enter the number of the location you are installing. (1-1) [1]

选择自己所在的地区，只有1可选，应该没什么用处。但如果你仔细看了用户协议，会看到被美国实行贸易禁运的国家比如伊朗、伊拉克等等是禁止下载使用的。选择后会出现完整的用户协议，可以按回车一屏一屏的读完，或者用q退出，然后记得按y同意此用户协议，

Please enter the directory where you would like to install the products or
press ENTER to accept the default.

   Destination Directory [/opt/sybase]

选择安装目录，我觉得用默认的就可以，如果目录不存在，还得确认创建新目录；如果目录已经存在，也会让你确认，不过是确认删除其中的所有文件：

      1. Yes
      2. No

   The directory does not exist.  Do you want to create it? [2] 1

然后就是选择安装哪些内容了：

Choose the setup type that best suits your needs.

[X] 1 - Typical
        The program will be installed with the suggested configuration.
        Recommended for most users.

[ ] 2 - Full
        The program will be installed with all the products and features.

[ ] 3 - Custom
        The program will be installed with the features you choose.
        Recommended for advanced users.

To select an item enter its number, or 0 when you are finished: [0]

如果没有特殊要求，Typical就够用了，如果想自行选择安装内容，可以选3 – Custom：

Select the features for "Sybase Adaptive Server Enterprise Suite" you would
like to install:

   Sybase Adaptive Server Enterprise Suite

   To select/deselect a feature or to view its children, type its number:

     1. +[x] Sybase Servers
     2. +[x] Connectivity
     3. +[x] ASE Data Providers
     4. +[x] Language Modules
     5. +[x] jConnect for JDBC
     6.  [ ] Shared
     7. +[x] Sybase Software Asset Management
     8. +[x] Sybase Unified Agent
     9. +[x] ASE Administration Tools

   Other options:

     0. Continue installing

   Enter command [0]

默认就是除了6 Shared，其他的都安装。最后，安装程序会把所有选项列出来供检查：

Sybase Adaptive Server Enterprise Suite will be installed in the following
location:

/opt/sybase

with the following features:

Sybase Servers
   Adaptive Server Enterprise
      ASE Agent Plugin
   Replicator
Connectivity
   Open Client
      Common ctlib files
      Common dblib files
   Common Connectivity Components
ASE Data Providers
   ASE ODBC Driver
      Unicode/MultiByte Conversion Support
      Sample Programs
Language Modules
   English Language Module
jConnect for JDBC
   JDBC Driver
Sybase Software Asset Management
   SySAM License Utilities
Sybase Unified Agent
   Unified Agent Server
   Agent Management Console
ASE Administration Tools
   Interactive SQL
   Sybase Central
      Adaptive Server Plugin
      SySAM Plugin

for a total size:

 410.8 MB

Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1]

然后终于开始安装了，进度条作得蛮可爱的：

Installing Sybase Adaptive Server Enterprise Suite. Please wait...

|-----------|-----------|-----------|------------|
0%         25%         50%         75%        100%
||||||||||||||||||||||||||||||||||||||||||||||||||

Creating uninstaller...

-------------------------------------------------------------------------------
The InstallShield Wizard has successfully installed Sybase Adaptive Server
Enterprise Suite. Choose Next to continue the wizard.

Press 1 for Next, 3 to Cancel or 4 to Redisplay [1]

接下来是配置向导，首先是email alert，用于接收数字资产变化的更新通知，显然对个人用户来讲没什么用处：

Please configure the Sybase Software Asset Management (SySAM) email alert
mechanism. When configured, specified recipients will receive email
notifications about SySAM events that may need administrator attention.

Do you want to configure email alerts?

   1. Yes
   2. No

Enter one of the options above : [1]
SMTP server host name [smtp] smtp.gmail.com
SMTP server port number [25] 465
Sender email [root] sybase@svr6
Recipient emails [root] svr6@gmail.com
Message Severity for email alerts（信息发送级别，达到级别的才会发送邮件，1为最低/敏感的级别，3为最高/不敏感的级别）

   1. INFORMATIONAL
   2. WARNING
   3. ERROR

Enter one of the options above : [2]

确认后就进入创建服务器的向导了，天知道上面的邮件设置能否正常发信：

The Adaptive Server Enterprise suite contains a number of programs in addition
to the Adaptive Server Enterprise database server. At this point in the
installation you can choose to configure various products. If you select the
options on this screen, you will be given the option of specifying
configuration options for each product or to allow the installer to set default
values for these on the next screen. If you do not choose to configure the
products at this time, you can configure them using the ASE configuration
utility at a later time.

Please deselect any products that you do not want to configure now :

   [X]  1  -  Configure new Adaptive Server
   [X]  2  -  Configure new Backup Server
   [X]  3  -  Configure new Monitor Server
   [X]  4  -  Configure new XP Server
   [X]  5  -  Configure Unified Agent

      To select an item enter its number, or 0 when you are finished: [0]

直接确认，安装全部服务器：

You have chosen to configure the products listed below. You can accept default
configuration values or provide customized values for the configuration options
required by each product. If you do not select custom configuration below, this
installer will configure the products you have selected with default values. If
you accept default values at this time you can change the configuration values
for a product at a later time.

Please select the products that you want to custom configure.

   [ ]  1  -  Custom configure new Adaptive Server
   [ ]  2  -  Custom configure new Backup Server
   [ ]  3  -  Custom configure new Monitor Server
   [ ]  4  -  Custom configure new XP Server
   [ ]  5  -  Custom configure Unified Agent

      To select an item enter its number, or 0 when you are finished: [0]

看上去和刚才的选择类似，不过这里是选择哪些服务器要用自定义设置，否则就采用默认设置快速创建了。我全部采用自定义设置（[]里是默认设置，什么不输入就使用这个值了）：

Please enter custom configuration values for the new Adaptive Server

   Adaptive Server Name [SVR6]
   Port Number [5000]
   Error Log [/opt/sybase/ASE-15_0/install/SVR6.log] /home/sybase/svr6.log
   Page Size

      1. 2k
      2. 4k
      3. 8k
      4. 16k

   Select a page size :  [1]
   Master Device [/opt/sybase/data/master.dat] /home/sybase/master.dat
   Master Device Size (MB) [30] 60
   Master Database Size (MB) [13] 30
   System Procedure Device [/opt/sybase/data/sysprocs.dat] /home/sybase/sysprocs.dat
   System Procedure Device Size (MB) [132] 150  （系统存储过程库比以前的11.9.2版本大了很多）
   System Procedure Database Size (MB) [132] 150
   System Device [/opt/sybase/data/sybsysdb.dat] /home/sybase/sybsysdb.dat
   System Device Size (MB) [1] 20
   System Database Size (MB) [1] 10

   Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1]

然后是backup server的设置：

Please enter custom configuration values for the new Backup Server

   Backup Server Name [SVR6_BS]
   Port Number [5001]
   Error Log [/opt/sybase/ASE-15_0/install/SVR6_BS.log] /home/sybase/svr6_bs.log

   Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1]

Monitor Server：

Please enter custom configuration values for the new Monitor Server

   Monitor Server Name [SVR6_MS]
   Port Number [5002]
   Error Log [/opt/sybase/ASE-15_0/install/SVR6_MS.log] /home/sybase/svr6_ms.log

   Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1]

XP Server：

Please enter custom configuration values for the new XP Server

   Port Number [5003]
   Error Log [/opt/sybase/ASE-15_0/install/SVR6_XP.log] /home/sybase/svr6_xp.log

   Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1]

然后是选择发现sybase服务的方法（这样客户端就不需要interface文件了），JINI需要架设单独的服务器，个人测试用UDP方式就够了，不过只能发现同一子网中的服务：

Discovery Services provide client applications the ability to find Sybase
resources within an enterprise environment without the need for local interface
file definitions. UA currently supports two discovery methods: JINI and UDP.

JINI requires a dedicated JINI server to provide discovery services and can
provide these services throughout the enterprise. UDP does not require a
dedicated server but is limited to discovery within the local subnet.

   Adaptor

      1. JINI
      2. UDP

   Select the adaptor for the Self Discovery Service. [2]

然后是选择用户统一认证方式，我觉得没有特殊理由，就用默认的好了，和11.9.2的使用习惯应该是相同的：

Please select and order the security login modules for the Unified Agent.

Simple Login: authenticates user ID/password against entries within a UA
configuration file.

ASE Login Module: delegates authenticationto an appropriate role (e.g.,
sa_role, sso_role, etc.) within the ASE.

NT/Unix Proxy login Module: delegates authentication to a user group in the
underlying operating system (e.g., Administrators, root, etc.).

      1.  Simple Login Module      true
      2.  ASE Login Module         true
      3.  Unix Proxy Login Module  false

   Select a login module to modify.  Enter "0" when you are done: [0]

最后，依然是把所有设置print出来检查，其中Unified Agent部分的有些内容应该以后会用到：

   Unified Agent - Security Login Modules

      CSI.loginModule.1.provider             com.sybase.ua.services.security.simple.SimpleLoginModule
      CSI.loginModule.1.controlFlag          sufficient
      CSI.loginModule.1.options.moduleName   Simple Login Module
      CSI.loginModule.1.options.username     uafadmin
      CSI.loginModule.1.options.password     ******
      CSI.loginModule.1.options.roles        uaAgentAdmin,uaPluginAdmin
      CSI.loginModule.1.options.encrypted    false

      CSI.loginModule.2.provider             com.sybase.ua.services.security.ase.ASELoginModule
      CSI.loginModule.2.controlFlag          sufficient
      CSI.loginModule.2.options.moduleName   ASE Login Module

终于又开始创建服务器了，不幸又遇到了错误：

Building Adaptive Server 'SVR6':
Writing entry into directory services...
Directory services entry complete.
Building master device...
Task failed
Server 'SVR6' was not created.
/opt/sybase/ASE-15_0/bin/dataserver: error while loading shared libraries:
libstdc++.so.5: cannot open shared object file: No such file or directory

其它服务器创建倒是很正常，除了连不上主服务器：

Building Backup Server 'SVR6_BS':
Writing entry into directory services...
Directory services entry complete.
Writing RUN_SVR6_BS file...
RUN_SVR6_BS file complete.
Starting server...
Server started.
Server 'SVR6_BS' was successfully created.

Building Monitor Server 'SVR6_MS':
Writing entry into directory services...
Directory services entry complete.
Writing RUN_SVR6_MS file...
RUN_SVR6_MS file complete.
Unable to log in to Adaptive Server 'SVR6'.
Unable to boot 'SVR6_MS' because Monitor Server must be able to log in to
Adaptive Server.
Server 'SVR6_MS' was not created.

Building XP Server 'SVR6_XP':
Writing entry into directory services...
Directory services entry complete.
Server 'SVR6_XP' was successfully created.
'SVR6_XP' will be started when needed by the Adaptive Server.

主服务器可能是缺少库文件，安装上libstdc++5包（会自动安装gcc-3.3-base包），用sybase用户进入，设定好环境变量，在.bashrc里添加上两行：

. ~/SYBASE.sh
. ~/ASE150.sh

再用rs文件创建服务器（记得先关掉已经启动的backup服务器，删掉/opt/sybase目录）。但，依然是错误：

   Building Adaptive Server 'SVR6':
   Writing entry into directory services...
   Directory services entry complete.
   Building master device...
   Task failed
   dataserver: master device size for this server: 60.0 Mb
   Server 'SVR6' was not created.
   dataserver: master database size for this server: 13.0 Mb
   dataserver: model database size for this server: 3.0 Mb

log的最后部分是：

   00:00000:00000:2007/09/17 02:31:14.28 kernel Encryption provider
   initialization succeeded on engine 0.
   00:00000:00000:2007/09/17 02:31:14.28 kernel  engine 0, os pid 28397  online
   Segmentation fault

上网查资料，又加上了LD_POINTER_GUARD=0参数（这个是0或者1得来回试试，看哪个行），终于行了：

Building Adaptive Server 'SVR6':
Writing entry into directory services...
Directory services entry complete.
Building master device...
Master device complete.
Writing RUN_SVR6 file...
RUN_SVR6 file complete.
Starting server...
Server started.
Building sysprocs device and sybsystemprocs database...
sysprocs device and sybsystemprocs database created.
Running installmaster script to install system stored procedures...
installmaster: 10% complete.
installmaster: 20% complete.
installmaster: 30% complete.
installmaster: 40% complete.
installmaster: 50% complete.
installmaster: 60% complete.
installmaster: 70% complete.
installmaster: 80% complete.
installmaster: 90% complete.
installmaster: 100% complete.
installmaster script complete.
Creating two-phase commit database...
Two phase commit database complete.
Installing common character sets (Code Page 437, Code Page 850, ISO Latin-1,
Macintosh and HP Roman-8)...
Character sets installed.
Setting server name in Adaptive Server...
Server name added.
Server 'SVR6' was successfully created.

安装小结

Sybase对服务器环境比较挑剔，我的服务器操作系统是Ubuntu 7.04：

• 首先libaio是需要的，apt安装即可，表现为libaio-dev和libaio1两个包。
• 设置shmmax，在/etc/sysctl.conf中加入kernel.shmmax=2147483648（2G共享内存），重启，或者直接echo 2147483648 > /proc/sys/kernel/shmmax，或者用sysctl -p直接加载。查询目前的共享内存设置用sysctl kernel.shmmax，单位为字节，sybase应该是需要至少64M，建议根据机器配置情况，设置为总内存的一半，或者256M以上。
• 安装上libstdc++5包，会自动安装gcc-3.3-base包。
• 手工创建sybase用户，把/home/sybase的权限设给它，并设置$HOME为/opt/sybase，当然，这一步也可以在安装完sybase、创建服务器之前作。
• 在sybase用户的bashrc中添加LD_POINTER_GUARD=0（注：我把sybase的默认shell从sh改成bash了，好像RHEL中需要指定这个环境变量为1）,在bashrc增加两个sh脚本的调用（. ~/SYBASE.sh、. ~/ASE150.sh）。
• 文档中建议使用sybase用户来进行安装，不过我是用root用户安装的，好像也没有遇到太多问题，安装完成后把/opt/sybase整体chown一下。

服务器设置

更改sa密码

格式为sp_password oldpassword,newpassword，比如：

>sp_password null,"new_password"
>go

服务器的字符集

默认字符集是“iso_1 (ISO 8859-1), a binary sort order, and English as the default language.”，我们一般使用的字符集是cp936或者utf8，下面就把服务器字符集调整为utf8。

没有用sp_configure来配置服务器字符集，而是采用sqlloc.rs方式，rs文件内容如下：

sybinit.release_directory: /opt/sybase
sqlsrv.server_name: SVR6
sqlsrv.sa_login: sa
sqlsrv.sa_password:
sqlsrv.default_language: us_english
sqlsrv.language_install_list: USE_DEFAULT
sqlsrv.language_remove_list: USE_DEFAULT
sqlsrv.default_characterset: utf8
sqlsrv.characterset_install_list: USE_DEFAULT
sqlsrv.characterset_remove_list: USE_DEFAULT
sqlsrv.sort_order: binary

运行之：

sybase@svr6:~$ sqllocres -r sqlloc.rs
Installing sort order 'Binary ordering, for the ISO 10646-1, UTF-8 multibyte
encodin...' in the Adaptive Server...
Sort Order 'Binary ordering, for the ISO 10646-1, UTF-8 multibyte encodin...'
was successfully installed in the Adaptive Server.
Making 'UTF-8' the Adaptive Server's default character set...
Making 'Binary ordering, for the ISO 10646-1, UTF-8 multibyte encodin...' the
Adaptive Server's default sort order...
The Adaptive Server's default character set is now 'UTF-8'.
The Adaptive Server's default sort order is now 'Binary ordering, for the ISO
10646-1, UTF-8 multibyte encodin...'.
Done

在isql中检查转换结果：

1> sp_helpsort
2> go
...
 Character Set = 190, utf8
     Unicode 3.1 UTF-8 Character Set
     Class 2 Character Set
 Sort Order = 50, bin_utf8
     Binary sort order for the ISO 10646-1, UTF-8 multibyte encodin
     g character set (utf8).
(return status = 0)
1> select @@client_csname
2> go

 ------------------------------
 utf8

(1 row affected)

简单试了一下，客户端是cp936字符集的时候，服务器能够正常的转换编码。

还有一个问题需要在数据转移时考虑，原来的服务器字符集是cp936，在转移数据的时候应该会遇到字段长度不够的情况，需要把字段长度增加为现在的3/2倍。

Adaptive Server Enterprise 15.0 – Datatypes

客户端的字符集设置需要修改$SYBASE/locales/locales.dat，在指定的操作系统如[win32]段，添加：

locale = default, us_english, cp936

就可以了，在数据传输过程中sybase会自动进行cp936和utf8之间的转换。一点小问题：我在linux下用sqsh连接服务器，不管-J参数怎么设置，select @@client_csname都是NULL，不过中文好像也能够正常使用（linux为Ubuntu，字符集为en_US.utf8）

其它关于字符集的参考资料：

• Sybase ASE的字符集
• 解决Sybase中文问题

自定义cfg文件

内存

max memory = 1048576

2k的页大小，设置2G的内存，XE版中设置超过这个值的话，反而会被ASE拒绝，使用默认值49152。

cpu

max online engines = 1

其实这一项使用DEFAULT即可，因为XE的限制，设置多了也不会起作用。

其他参数

number of user connections = 300
number of locks = 10000 （ASE 15默认值就是这个）
lock scheme = datarows

identity burning set factor

这个是用来自动分配identity列的值的，sybase采用预分配区间的方式，所以重启或rollback事务会产生断带，这个参数就是用来控制断带的大小的，公式为：

identity burning set factor(F) = N * power(10, 7 - M)

其中N是每次分配的种子数量，M为identity列的长度。反过来，想知道每次会分配多少种子数，公式就是：

N = F * power(10, M - 7)

在我这里，identity列长度为8/numeric(8,0)，identity burning set factor设置为10，每次的种子分配就是100个，最大的断带也就是100。

其它文档中提到，但是不需要运行的

• installmaster
• installcommit 两阶段提交或分布式事务（two-phase commit or distributed transactions）
• installsecurity （Sybase auditing feature）
• instmsgs.ebf （brings your Adaptive Server messages up to the level of this EBF）
• installjsdb （Job Scheduler feature enabled）and（upgrade the Job Scheduler Templates）
• installmontables （update the Monitoring Tables’ definitions）

文档中都有详细说明，用isql执行相应sql文件即可，我没有装。

创建init.d脚本

我的安装并没有自动在/etc/init.d/下创建控制服务的脚本，所以只能自己按照SQL Relay上的例子创建一个了（有一点点改动）：

#!/bin/sh

export SYBASE=/opt/sybase
export PATH=$PATH:$SYBASE/ASE-15_0/bin:$SYBASE/ASE-15_0/install
export LD_POINTER_GUARD=0
#echo 268435456 > /proc/sys/kernel/shmmax
case "$1" in
        start)
                for i in `ls $SYBASE/ASE-15_0/install/RUN_*`
                do
                        su -c "$SYBASE/ASE-15_0/install/startserver -f $i" > /dev/null 2>&1;
                        #su -c "$SYBASE/ASE-15_0/install/startserver -f $i"
                done
                ;;
        stop)
                kill `ps -efa | grep sybase | grep -v grep | awk '{print $2}'`
                ;;
        *)
                echo $"Usage: $0 {start|stop}"
                exit 1
esac

exit 0

这里还有一个更好的脚本，主要是关闭使用的不是kill，而是数据库自身的shutdown功能），针对我的平台，我有略微改动，建议大家使用这个或参照修改：

#!/bin/bash
# Author: MichaelBibby
# Date: 2007.06.19
# description: Normal RC script for SYBASE.

SYBASE_USERNAME="sa"
SYBASE_PASSWORD=""

export LD_POINTER_GUARD=0
export SYBASE=/opt/sybase
export SYBASE_HOME='/opt/sybase'
export SYBASE_OCS='OCS-15_0'
export SYBASE_ASE='ASE-15_0'
# ************************************************
# Warnning: Modify the RUN_xxx to fit your need.
# Your startup script will be such as:
#   ${SYBASE_HOME}/${SYBASE_ASE}/install/RUN_${SERVER}
#   ${SYBASE_HOME}/${SYBASE_ASE}/install/RUN_${SERVER_BACKUP}
export SERVER='SVR6'
export SERVER_BACKUP='SVR6_BS'
# For multi-server
# export SERVER='server1 server2'
# export SERVER_BACKUP='server1_backup server2_backup'
# ************************************************

export LIB="${SYBASE_HOME}/${SYBASE_OCS}/lib"
export PATH=$PATH:"${SYBASE_HOME}/${SYBASE_ASE}/bin:${SYBASE_HOME}/${SYBASE_ASE}/install:${SYBASE_HOME}/${SYBASE_OCS}/bin"

start_db()
{
    su -p sybase -c "${SYBASE_HOME}/${SYBASE_ASE}/install/startserver \
    -f ${SYBASE_HOME}/${SYBASE_ASE}/install/RUN_${SERVER}"

    su -p sybase -c "${SYBASE_HOME}/${SYBASE_ASE}/install/startserver \
    -f ${SYBASE_HOME}/${SYBASE_ASE}/install/RUN_${SERVER_BACKUP}"
}

stop_db()
{
# Error: Site 'SVR6_BS' not found in sysservers/ User default back_srv name.
    ${SYBASE_HOME}/${SYBASE_OCS}/bin/isql \
    -U${SYBASE_USERNAME} \
    -P${SYBASE_PASSWORD} \
    -S${SERVER} <<EOF
shutdown SYB_BACKUP
go
exitEOF

    ${SYBASE_HOME}/${SYBASE_OCS}/bin/isql \
    -U${SYBASE_USERNAME} \
    -P${SYBASE_PASSWORD} \
    -S${SERVER} <<EOF
use master
go
checkpoint
go
shutdown
go
exit
EOF

if [ X"$?" == X"0" ]; then
    exit 0
else
    exit 1
fi
}

status_db()
{
    chk_sybase=$(ps axw --cols 32768 | grep dataserver | grep -v grep | wc -l)
    if [ X"$chk_sybase" = X"0" ]
    then
        echo -n "Checking Master Server: ${SERVER}"
        echo " ... Failed."
        echo
    else
        echo -n "Checking Master Server: ${SERVER_BACKUP}" && echo " ... Success."
    fi

    chk_sybase_bs=$(ps axw --cols 32768 | grep backupserver | grep -v grep | wc -l)
    if [ X"$chk_sybase_bs" = X"0" ];then
        echo -n "Checking Backup Server: ${SERVER_BACKUP}"
        echo " ... Failed."
        echo
    else
        echo -n "Checking Backup Server: ${SERVER_BACKUP}" && echo " ... Success."
    fi

    exit 0
}   

case $1 in
    restart) stop_db ; start_db ;;
    start) start_db ;;    stop)
        ps aux | grep -i dataserver | grep -v grep >/dev/null
        if [ X"$?" == X"0" ]; then
            stop_db
        else
            exit 0
        fi
        ;;
    status) status_db ;;
    *) echo "USAGE: $0 [start|stop|status|restart]" ;;
esac

把这个脚本ln为/etc/init.d/sybase就可以用来控制服务了。

开机后自动启动

把刚才创建的/etc/init.d/sybase这个文件ln到各个rc目录下就可以了：

cd /etc/rc0.d/
ln -s ../init.d/sybase K90sybase
cd /etc/rc1.d/
ln -s ../init.d/sybase K90sybase
cd /etc/rc2.d/
ln -s ../init.d/sybase S90sybase
cd /etc/rc3.d/
ln -s ../init.d/sybase K90sybase
cd /etc/rc4.d/
ln -s ../init.d/sybase K90sybase
cd /etc/rc5.d/
ln -s ../init.d/sybase K90sybase
cd /etc/rc6.d/
ln -s ../init.d/sybase K90sybase
cd /etc/rcS.d/
ln -s ../init.d/sybase S90sybase

绑定两个ip地址

默认情况下，sybase只在一个ip地址上监听，如果在interfaces文件中强行添加其他的ip，或者是其他的端口，比如这样：

SVR6
        master tcp ether svr6 5000
        query tcp ether svr6 5000
        master tcp ether svr6 4000
        query tcp ether svr6 4000
        master tcp ether 192.168.0.106 5008
        query tcp ether 192.168.0.106 5008

都用svr6这个名字，5000和4000端口都可以监听成功，但第二个106的地址监听不成功：

00:00000:00008:2007/09/18 13:43:49.84 kernel  network name svr6, interface IPv4, address 0.0.0.0, type tcp, port 5000, filter NONE
00:00000:00008:2007/09/18 13:43:49.84 kernel  network name svr6, interface IPv4, address 0.0.0.0, type tcp, port 4000, filter NONE
00:00000:00008:2007/09/18 13:43:55.17 kernel  ninit: cannot find host 192.168.0.106
00:00000:00008:2007/09/18 13:43:55.17 kernel  Cannot allocate resources for listener with protocol tcp, host 192.168.0.106, port 5008, engine 0.

所以变通的办法无外乎两种，一种是用防火墙把另外一个ip地址的端口作dnat转发过来，另外一种就是修改hosts文件，把svr6的地址指定为0.0.0.0，个人认为第二种方式较好一些，并且试验成功。

创建数据库

设定以后的用户数据库不要默认创建在master设备上

1> sp_diskdefault master, defaultoff
2> go

更改tempdb的大小（建议使用下面ram设备的方式）

先查看tempdb的状态：sp_helpdb tempdb，默认是创建在master设备上，大小为4M，而将临时数据库放在单独的设备上更好，现在就来调整：

> disk init     # 初始化设备
> name="tempdb_dev",    # 设备名称，遵守sybase标识符规则
> physname="/opt/sybase/tempdb.dat",    #设备物理地址，最好用绝对地址
> vdevno=13,        # 设备编号（需要唯一，我刚装上的系统，master是0，sysprocsdev是1，systemdbdev是2，再新建就该用3了）
> dsync=false,      # 关闭I/O缓存，提升性能
> size=15360        # 设备大小，默认是以块为单位（一般512字节或者2k），不过建议使用尺寸，比如"200M"或者"1g"，这样更容易掌握一些）
> go

将临时数据库扩展到该一个设备上，注意这里的单位是M

> alter database tempdb on tempdb_dev=30
> go

打开tempdb数据库，从段上删除master设备

> use tempdb
> go
> sp_dropsegment default, tempdb, master
> go
> sp_dropsegment logsegment, tempdb, master
> go
> sp_dropsegment system, tempdb, master
> go

设置tempdb为第一个recover的库（好像没必要）

-- make the second tempdb the first user db to be recovered.
> sp_dbrecovery_order tempdb_report, 1
> go

参考：disk init手册

把tempdb创建在ram设备上

首先创建虚拟的ram设备，挂载上（默认的大小单位好像是1k，但建议适当多划分一点空间留给文件系统和容纳计算误差，比如我想用30M的tempdb，就划了33000），创建设备文件，并更改文件权限：

mkdir /home/sybase/tempdb
mke2fs -q -m0 /dev/ram0 33000
mount -t ext2 /dev/ram0 /home/sybase/tempdb
?? touch /home/sybase/tempdb/tempdb.dat (第一次创建的时候不建文件，加在启动脚本里再用touch？)
chown -R sybase:sybase /home/sybase/tempdb

然后在这个设备文件上创建sybase数据库设备，并把tempdb转移过来，首先创建设备：

> disk init name="tempdb_dev", phyname="/home/sybase/tempdb/tempdb.dat", vdevno=3,dsync=false,size="30M"
> go

在新设备上分配空间：

> use master
> alter database tempdb on tempdb_dev=30
> go
Extending database by 15360 pages (30.0 megabytes) on disk tempdb_dev

从旧设备（master）上删除段：

> use tempdb
> go
> sp_dropsegment "default", tempdb, master
> go
> sp_dropsegment logsegment, tempdb, master
> go
> sp_dropsegment system, tempdb, master
> go

回收旧设备上的空间，还是直接修改系统表最简单，不过最好先查询一下，对照sysusages表的内容进行适应性调整：

> sp_configure "allow update", 1
> go
> begin tran
> delete from sysusages where dbid=2 and segmap=0
> go
> update sysusages set lstart=0 where dbid=2
> go
> commit
> go
> sp_configure "allow update", 0
> go

还有一种用临时表，写日志然后截断日志的方式，可以参考同一设备数据库如何分离日志与数据，不过我作的时候没有成功，还因为日志堵塞动弹不得（我循环了50万次都没事，然后全部delete from就出事了 ；另外一次是在执行sql insert into t select id from t时，第一次执行没事，5万条数据出来了，第二次再执行。。。死掉了）。可以用sp_helpdb tempdb查设备和段分配情况，

为了在重启的时候也能正常创建ram盘，还要在上面说过的服务控制脚本中添加创建并使用ram盘的内容，即在start_db的开始部分加上：

start_db()
{
    echo "Mounting ramdisk for tempdb ..."
    mke2fs -q -m0 /dev/ram0 33000
    mount -t ext2 /dev/ram0 /home/sybase/tempdb
    touch /home/sybase/tempdb/tempdb.dat
    chown -R sybase:sybase /home/sybase/tempdb
    ......

在stop_db()的结尾部分加上：

    ......
    echo "Dismounting ramdisk of tempdb ..."
    umount /home/sybase/tempdb

    if [ X"$?" == X"0" ]; then
        exit 0
    else
        exit 1
    fi
} // end of func stop_db

两个疑惑的地方及我看到的结果：

• mke2fs创建的ram盘占用实际的内存么？好像不占，分配到文件上才占。
• sybase重启后，tempdb会重用大小和以前不同的同名设备文件么？会的，tempdb的机制和其他用户库不一样。

参考：

• Configuring tempdb for Performance
• Sybase ASE 常见问与答

搞定收工，可以正式建库配置应用数据库了，再推荐几个sybase的资料：

• Getting Started With Sybase
• Sybase on linux-distro matrix
• 最近一段时间都在弄sybase和ms-sqlserver，写了一份学习笔记
• ASE on Linux FAQ