存档

2010年9月 的存档,文章数:26

安装中文语言支持、配置字符集

         windows平台上,Sybase ASE15.0.3安装完成后默认语言是:英语(us_english),默认字符集为:cp850cp850是一个西欧字符集,虽然也能使用cp850字符集保存汉字,但是不推荐在生产环境中使用该字符集。

         如果生产系统中Sybase ASE数据库仅考虑支持简体中文的话,则可以使用cp936或者utf8这两种字符集。如果需要国际化支持,那么需选择utf8字符集。

         在生产环境中,需要将默认字符集由cp850调整为utf8(或者调整成支持简体中文的cp936也可以,根据项目情况而定!),将默认语言调整为中文。

         备注:如果想设定ASE的默认语言为中文,必须先将默认字符集设定为utf8,才能设置默认语言为中文。否则会报错。

1查看当前数据库的默认字符集:

执行:

sp_configure "default character set id"

go

查看到Run Value为:2,执行:

select id,name from master..syscharsets where id=2

go

查看到当前字符集为:cp850

以下的脚本能够自动创建数据库、登录、用户并能给用户分配命令权限。只需要指定用户数据库的名称,数据设备、日志设备的大小,登录名称以及登录密码即可。

局限性:

没有判断即将要创建的设备是否存在?

将数据库设备创建在和master设备同一目录下,不能提高物理IO的性能。

脚本文件下载:sybase数据库自动创建脚本.sql

-----------------------------------------------------------------------------------------------------------------------------

use master
go
declare @dbname varchar(30)
declare @MasterDevicePath varchar(255),@LogicalDataDevName varchar(30),@LogicalLogDevName varchar(30)
declare @DATDevicePath varchar(255),@LOGDevicePath varchar(255),@DataDevSize int,@LogDevSize int,@DataDevPageCount  int,@LogDevPageCount int
declare @SQLTEXT varchar(255)
declare @loginname varchar(30),@loginpasswd varchar(30)
set nocount on
begin
-- @dbname 代表将要创建的数据库名称

Adaptive Server version 15.0.2 uses row-level locking on system tables to enhance performance.

Versions of Adaptive Server earlier than 15.0.2 used exclusive table locks on system tables while executing data definition language (DDL) and utility commands. The set of system tables Adaptive Server locked depended on the type of DDL operation you executed. If another DDL running concurrently tried to take a conflicting exclusive table lock on the same system table, this DDL had to wait to acquire the lock on any system catalogs. These DDL operations were executed serially.

This methodology impeded performance in temporary databases, where their DDL activity is very high also, and consequently their catalog contention is very high. This limited the Adaptive Server throughput for applications using temporary tables.
Adaptive Server version 15.0.2 uses row-level locking to resolve these issues:

*

System-table contention, caused a bottleneck for many DDLs and utilities.
*

tempdb contention. Because the system tables are locked at the row level, Adaptive Server 15.0.2 eliminates tempdb contention.
*

Shared or exclusive table-level locks while executing DDLs and utilities. Earlier versions converted most system tables to data-only locking (DOL), but still created shared or exclusive table-level locks while executing DDLs and utilities. Using row-level locks for system tables eliminates this contention.

Adaptive Server sets intent locks on catalogs only, which removes potential contention (An intent lock indicates that page-level or row-level locks are currently held on a table.).
*

DDLs and utilities blocking each other. Adaptive Server 15.0.2 allows DDLs and utilities to run in parallel.

Earlier versions of Adaptive Server used table locks to achieve system catalog synchronization. Adaptive Server 15.0.2 uses intent locks for table-level synchronization and row locks for row-level synchronization. Earlier releases of Adaptive Server locked the entire system catalog while performing operations on the object, so a single lock request was made. However, Adaptive Server version 15.0.2 requests locks for all applicable rows while performing operations on the object if there are multiple rows corresponding to an object in a system catalog.

This change means that Adaptive Server 15.0.2 requests more locks to perform the same operation than earlier releases, and increases the number of lock resources the system needs. Consequently, you may need to change the number of locks configuration option after you upgrade Adaptive Server.

sybase官方文档上关于设备的dsync,directio这两个属性也是语焉不详,可能不同版本不同平台的ASE这两个属性有不同的默认值。
下面是一位Sybase大牛的论述,很清晰明朗。

The use of dsync/directio (typically) means the dataserver has to wait for a write to complete on the physical disk
before the dataserver considers the write to be 'successful' (aka guaranteed write). While this *wait* will extend the
time it takes to complete the associated database action, the guaranteed write to disk is required to insure
recoverability (in the case of a dataserver/machine/disk subsystem failure).

If dsync/directio are disabled then a dataserver write may finish more quickly due to the disk subsystem *caching* the
write IO. This means the associated dataserver action completes more quickly but there's no guarantee the IO made it to
the physical disk. If the dataserver/machine/disk subsystem fails between the successful disk cache write and the
physical disk write, you could end up losing data (ie, the dataserver thinks the data is on disk while the disk has no
record of the data existing).

For important databases (eg, master, RSSDs, user databases), especially in production environments, the recoverability
of the data usually takes precedence over speed. In these scenarios the dataserver must wait for physical disk writes
to complete 'successfully', with the caveat that the associated database action takes longer to complete.

For trivial/development databases where recoverability is not a concern, or for databases that are rebuilt from scratch
at dataserver startup (eg, temporary databases), guaranteed disk writes are not as important. In these scenarios a
successful write to disk cache is sufficient, with the added benefit that the associated database action completes more
quickly.

So, generally speaking:

dsync/directio enabled : guaranteed disk writes, guaranteed recoverability, associated database actions take more time
to complete

dsync/directio disabled : no guarantee of writes to physical disks, no guarantee of recoverability, associated database
actions take less time to complete

------------------

Now-a-days there are lots of ways to configure disk subsystems ... raw disks, cached disks, cached file systems,
journaled file systems, multiple layers of logical disk management, sans (w/ and w/out cache), etc, etc, etc ...

Some of these configurations may support dsync/directio operations by the dataserver while actually performing the write
to cache, ie, the physical disk write takes place at a later time. For some systems this may be ok if the disk
subsystem vendor can guarantee that those cache writes will always make their way to disk.

The issue here is that regardless of which attribute settings (dsync/directio) are used for dataserver devices, it's the
DBA's responsibility to insure the disk subsystem can really guarantee disk writes for those database actions that
require guaranteed recoverability.

总结一下:文件系统将directio属性关闭。文件系统上临时数据库或者不太“重要”的数据库的设备可以将dsync调整成false,其余数据库为了保证恢复将dsync设置成true。

1、编辑/etc/ssh/sshd_config;把 PermitRootLogin no改成 PermitRootLogin yes

2、svcadm restart network/ssh

安装SERVER FOR redhat

步骤一:
设置系统共享内存为服务器物理内存的7/8

以root身份登录

vi /etc/sysctl.conf,在此文件中加入如下2行

kernel.shmmax = 2106408960  #设置为系统共享内存的7/8,可到/proc目录下打开meminfo文件来查看共享内存大小

kernel.shmmni = 4096

修改完成后重启动服务器

步骤二:
创建sybiq用户(也可在图形界面中预先添加iq用户)

以root身份登录创建iq用户:

useradd –m –d /sybiq –s /bin/sh –g staff sybiq

passwd sybiq(并根据提示输入密码两次)

步骤三:
安装SybaseIQ

最好创建独立的文件系统/sybiq来安装SybaseIQ

将SybaseIQ的安装包sybiqserver.tar解压缩到/sybiq:

tar –xvf sybiqserver.tar

cd  /sybiq/asiq125

执行以下命令使.profile配置文件生效:

. .profile

以sybiq用户开始安装运行./sybinstall脚本

1

Welcome to Adaptive Server IQ 12.5

Welcome to Adaptive Server IQ.  This script will install

Adaptive Server IQ and other supporting products on your

system.  You may quit at any time by pressing ^C (Control-C).

Press return to continue:

2

Select the location where you are installing the software

1) Americas (Mid/So.) and Asia Pacific General 17) Malaysia

2) Argentina                                   18) Mexico

3) Australia                                   19) Netherlands

4) Brazil                                      20) Norway

5) Canada                                      21) Philippines

6) China, Peoples Republic of (PRC)            22) Republic of Ireland

7) Europe, Middle East, and Africa - General   23) Singapore

8) Finland                                     24) Spain

9) France                                      25) Sweden

10) Germany                                     26) Switzerland - French

11) Hong Kong                                   27) Switzerland - German

12) India                                       28) Taiwan

13) Indonesia                                   29) Thailand

14) Italy                                       30) United Kingdom

15) Japan                                       31) United States of America

16) Korea

Please enter the number for the location you are installing (1..31, Q):6

3

阅读license(可能是乱码)

I agree with the terms of the license, for the location specified <Y/N>?y

4

Select Products

Destination Directory ($SYBASE)                            Free Space(k)

---------------------------------------------------------- -------------

/sybase/asiq125                                                103900845

#  Product                          Size(k)  Install Directory

-- -------------------------------  -------  ----------------------------

1) Adaptive Server IQ 12.5           190908  $SYBASE/ASIQ-12_5

2) Open Client Developer Kit 12.5    252936  $SYBASE

3) Sybase Central Java Edition 3.2     9240  $SYBASE/sybcentral

4) jConnect 5.5                       15144  $SYBASE/shared

Options

--------------------------------------------

1..4) De/Select Product for Installation

C) Change Target Directory

S) Selection Completed

Q) Quit the Install

Please enter (1..4, C, S or Q ) s

5

Installing

-----------------------------------

Installing Adaptive Server IQ 12.5

开始安装

安装完毕打补丁,还有一个补丁的安装包

将SybaseIQ的补丁EBFQ1532.tar解压缩到/sybiq:

tar –xvf EBFQ1532.tar

解压完毕执行补丁解压成的安装文件./sybinstall进行补丁的安装,安装方法与上述步骤相同。

步骤四:
编辑sybiq用户的环境变量

以sybiq用户登录,在/sybiq/asiq125目录下编辑.profile文件如下:

##copyright (c) 2001 by Sun Microsystems, Inc.

## All rights reserved.

##

## ident        "@(#)local.profile      1.10    01/06/23 SMI"

#SYBPLATFORM=sun_svr464

#export SYBPLATFORM

LANG=zh_CN.GB18030

export LANG

SYBASE=/sybiq/asiq125         #指定安装路径(如路径不存在需新建安装路径mkdir)

export SYBASE

LD_ASSUME_KERNEL=2.4.1    #新加入一行(不同于SUN的solaris)

export LD_ASSUME_KERNEL

ASDIR=$SYBASE/ASIQ-12_5

export ASDIR

SYBASE_OCS=OCS-12_5

export SYBASE_OCS

SYBASE_JER=$SYBASE/shared/jre-1_22

export SYBASE_JRE

PATH=$ASDIR/bin:$SYBASE/$SYBASE_OCS/bin:$PATH:$SYBASE/:$SYBASE/ASIQ-12_5/bin:$SYBASE/ASIQ-12_0/sybiq/asiq125/zcdyiq

export PATH

IQLIB=$ASDIR/usrlib:$ASDIR/lib:$SYBASE/$SYBASE_OCS/lib:$SYBASE/ASIQ-12_5/lib

export IQLIB

LD_LIBRARY_PATH=$IQLIB

export LD_LIBRARY_PATH

步骤五:
修改语言环境

将/sybiq/asiq125/locales/locales.dat文件 [linux]小节中加入一行:
locale = zh, us_english, iso_1

启动iqserver ,进入demo库:

cd /sybiq/asiq125/ASIQ-12_5/demo

start_asiq @asiqdemo.cfg asiqdemo.db

在/sybiq/asiq125目录下创建interfaces文件,添加启动的数据库IP及端口信息:

asiqdemo

master tcp ether 192.168.2.113 2638

query tcp ether 192.168.2.113 2638

此时可以启用isql:

$isql  -Udba –PSQL -Sasiqdemo

1>

创建用户数据库(在磁盘阵列中创建数据库)

步骤一:
启动demo库:

$cd /sybiq/asiq125/ASIQ-12_5/demo

$start_asiq @asiqdemo.cfg asiqdemo.db

步骤二:
创建用户数据库目录

cd /sybiq/asiq125

mkdir zcdyiq

步骤三:
把裸设备设置成可被IQ使用的数据库设备
  1. 使用命令绑定裸设备

将阵列中两个裸设备【执行fdisk将sda分为两个区其中sda1较大,sda2较小,sda1(用于指定iq path)和sda2(用于指定temporary path)】分别绑定到raw:

#raw /dev/raw/raw1 /dev/sda1
#raw /dev/raw/raw2 /dev/sda2

  1. 编辑 /etc/sysconfig/rawdevices文件,用于定义裸设备:

/etc/sysconfig/rawdevices格式如下:
# raw device bindings
# format: rawdev major minor
# rawdev blockdev
# example: /dev/raw/raw1 /dev/sda1
# /dev/raw/raw2 8 5
/dev/raw/raw1 /dev/sda1
/dev/raw/raw2 /dev/sda2

  1. 启动绑定信息/etc/rc.d/init.d/rawdevices

[root@legolas init.d]# sh rawdevices start
执行上面的命令后系统显示如下信息:

Assigning devices:
/dev/raw/raw1 --> /dev/sda1
/dev/raw/raw1: bound to major 3, minor 5
/dev/raw/raw2 --> /dev/sda2
/dev/raw/raw2: bound to major 3, minor 6
done

  1. 执行chkconfig命令确保重新启动时裸设备能被绑定

#/sbin/chkconfig rawdevices on

  1. 修改裸设备的属主和访问权限:

#chown sybiq:sybiq /dev/raw/raw1

#chown sybiq:sybiq /dev/raw/raw2

#chown sybiq:sybiq /dev/sda1

#chown sybiq:sybiq /dev/sda2
#chmod  777  /dev/rawctl

  1. 用raw 命令检查裸设备的配置情况:

$ raw –qa

正常的显示结果:
#/dev/raw/raw1: bound to major 3, minor 5
#/dev/raw/raw2: bound to major 3, minor 6

  1. 建立软连接

在 zcdyiq目录中创建于裸设备进行软连接的文件:

#ln –s /dev/raw/raw1  /sybiq/asiq125/zcdyiq/iqdatadyn(用于指定iq path)

#ln –s /dev/raw/raw2  /sybiq/asiq125/zcdyiq/iqtmpdyn(用于指定temporary path)

执行上面命令后,在/sybiq/asiq125/zcdyiq/目录下生成了iqdatadyn和iqtmpdyn连接符

  1. 修改连接符的属主

#cd /sybiq/asiq125/zcdyiq

#chown sybiq:sybiq iqdatadyn

#chown sybiq:sybiq iqtmpdyn

步骤四:
创建用户数据库zcdynadb.db

isql -Udba -PSQL –Sasiqdemo

1>     drop database '/sybiq/ asiq125/zcdyiq/ zcdynadb.db'

2>     go

3>     create database '/sybiq/ asiq125/ zcdyiq/zcdynadb.db’

4>     iq path '/sybiq/asiq125/zcdyiq/iqdatadyn’           #用于绑定裸设备的连接文件

5>     temporary path '/sybiq/asiq125/zcdyiq/iqtemdyn’     # 用于绑定裸设备的连接文件

6>     go

1>     commit

2>     go

1>     quit

步骤五:
编辑用户数据库启动文件zcdynadb.cfg

cd $SYBASE/zcdyiq

vi zcdynadb.cfg

-n s1_zcdynadb

-c 64m

-gp 4096

-gd all

-gl all

-gm 100

-gc 6000

-gr 6000

-ti 4400

-tl 300

-iqmc 3000               #依据共享内存大小不同进行合理配置

-iqtc 3000              #依据共享内存大小不同进行合理配置

-x tcpip{port=2640}

其中:

-n 服务器名_库名

-iqmc IQ MAIN CACHE大小,单位M

-iqtc IQ TEMP CACHE大小,单位M

用例中给IQ分配了6000M的共享内存,MAIN CACHE和TEMP CACHE各3000M

步骤六:
修改interfaces文件,

加入所建数据库的ip及端口信息

zcdynadb

master tcp ether 192.168.2.113 2640

query tcp ether 192.168.2.113 2640

步骤七:
启动用户数据库

cd $SYBASE/zcdyiq

start_asiq @zcdynadb.cfg zcdynadb.db

步骤八:
添加用户、设置加载内存(单位M)

isql -Udba -PSQL –Szcdynadb

1>     grant connect to zcss7db identified by zcss7db

2>     go

1>     commit

2>     go

1>     set option PUBLIC.LOAD_MEMORY_MB=400

2>     go

1>     commit

2>     go

1>     quit

步骤九:
IQ客户端配置

例如设置ODBC来访问用户数据库zcdynadb

ODBC:data source name 随意输入(例如:zcdyIQ)

LOGIN:userid:dba  passwd:SQL

Database:servername:/sybiq/asiq125/zcdyiq/zcdynadb.cfg中对应的-n后面的信息,这里应该为s1_zcdynadb;

databasename:/sybiq/asiq125/下interfaces文件中可获得,这里为zcdynadb

Network:tcp/ip: host=ip地址:端口号(例如:host=192.168.2.110:2640),端口号也要由数据库对应的.cfg文件中-x tcpip{port=2640}中定义的port来确定。

问题描述:

在UNIX及某些系统内不能启动Sybase Center,那么在这种情况下怎样修改sa密码呢,同时如果我们不需要密码时也可以使用该方法将sa密码置为空。

如何将sa的密码置为空,假设当前sa密码为:123456。

解决方案:
$isql -Usa -P123456 -SSYBASE
1>sp_configure "upgrade version"
2>go
#记录打印的版本号,Run Value表示当前系统版本是:11920 (sybase 11.9.2)

1>sp_configure "upgrade version",492
2>go
#更改版本号为492

1>sp_password   '123456',NULL,sa
2>go
#修改密码将123456密码置为空

1>sp_configure "upgrade version",11920
2>go
#务必更改版本号为原来的版本号

如果将参数upgrade version修改为492后忘记改回原来的值,则在Sybase服务器关闭后再启动的时候报错:

Pre 10.0 database cannot work with this version of the server. Please upgrade the databases to 10.0 or above release and then try to start with this version of the server. Shutting down ***.

这说明master数据库已经损坏了。因此,通过修改参数upgrade version重置sa口令为空时千万要谨慎,最后一定要将upgrade version修改回原值!

修复的方法请参考:

master数据库配置区域(configuration area)损坏的一种修复方法

https://www.dbainfo.net/one-way-to-recover-master-configuration-area.htm

Sybase数据库技术,数据库恢复博客欢迎您的光临!

本博客旨在交流Sybase数据库使用心得、共享Sybase数据库技术知识。

期待与您交流Sybase或者其他数据库技术!

QQ :289965371 (晴空)

MSN: