Sybase数据库安装及配置
Case库sybase数据库安装安装环境
系统:Red Hat Enterprise Linux AS release 4 (Nahant Update 5)
主机名:db
IP地址:172.16.9.86
内核:2.6.9-55.Elsmp
内存:4G
交换分区:4G
CPU:Intel(R) Xeon(R) 2GHz (2颗,32bits)
安装前准备
1、新建安装用户sybase
[root@db etc]# useradd –d /opt/sybase –m sybase
[root@db etc]# id sybase
uid=505(sybase) gid=505(sybase) groups=505(sybase)
2、修改内核参数
在/etc/sysctl.conf文件中加入如下三行:
kernel.shmmax=1000000000
kernel.exec-shield=0
kernel.exec-shield-randomize=0
然后执行如下命令,使修改生效:
[root@db etc]# sysctl –p
检查修改是否生效:
[root@db etc]# sysctl kernel.shmmax
kernel.shmmax = 1000000000
[root@db etc]# sysctl kernel.exec-shield
kernel.exec-shield = 0
[root@db etc]# sysctl kernel.exec-shield-randomize
kernel.exec-shield-randomize = 0
安装
在图形界面下进行安装
安装目录:/opt/sybase
安装版本:Adaptive Server Enterprise 12.5.2 EBF 11949 linux
以sybase用户登陆服务器图形界面,首先解压安装包
[sybase @db inst]$ tar xvfz ase1252_de_linux.tgz
然后执行安装命令:
[sybase @db inst]$./setup
根据提示进行设置,选择“Typical”安装。
安装后,修改sybase用户的.bash_profile文件。在该文件的末尾加入如下内容:. /opt/sybase/SYBASE.sh。修改完成后,注销用户,然后重新以sybase用户登陆系统。
检查服务是否已经起来:
[sybase@db ~]$ /opt/sybase/ASE-12_5/install/showserver
F S UID PID PPID C PRI NI ADDR SZ WCHAN STIME TTY TIME CMD
0 S sybase 13725 13724 0 76 0 - 11689 - May08 ? 00:00:00 /opt/sybase/ASE-12_5/bin/monserver -SDB -Usa -P -l/opt/sybase/ASE-12_5/install/DB_MS.log -L/opt/sybase/ASE-12_5/DB_MS.cfg -m/opt/sybase/ASE-12_5 -MDB_MS
0 S sybase 13597 13596 0 75 0 - 18861 - May08 ? 00:01:36 /opt/sybase/ASE-12_5/bin/dataserver -d/opt/sybase/data/master.dat -e/opt/sybase/ASE-12_5/install/DB.log -c/opt/sybase/ASE-12_5/DB.cfg -M/opt/sybase/ASE-12_5 –sDB
登陆数据库
[sybase@db ~]$ isql -Usa -P -Sdb
CT-LIBRARY error:
ct_connect(): directory service layer: internal directory control layer error: Requested server name not found.
通过google、baidu,以及重装数据库后,该问题一直没有得到解决,在陈川的帮助下解决,问题处在-S的db应该写成大写DB!
[sybase@db ~]$ isql -Usa -SDB
Password:
Msg 2401, Level 11, State 2:
Server 'DB':
Character set conversion is not available between client character set 'utf8'
and server character set 'iso_1'.
No conversions will be done.
1>
关闭数据库
1>shutdown
2>go
启动数据库
[sybase@db ~]$ /opt/sybase/ASE-12_5/install/startserver –f RUN_DB
数据库配置
查看数据库现状
1>
2> sp_helpdb
3> go
name db_size owner dbid created
status
------------------------ ------------- ------------------------ ------
--------------
------------------------------------------------------------------------------------------------------ master 6.0 MB sa 1 May 08, 2008
mixed log and data
model 2.0 MB sa 3 May 08, 2008
mixed log and data
sybsystemdb 2.0 MB sa 31513 May 08, 2008
mixed log and data
sybsystemprocs 120.0 MB sa 31514 May 08, 2008
trunc log on chkpt, mixed log and data
tempdb 3.0 MB sa 2 May 08, 2008
select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data
(1 row affected)
(return status = 0)
创建设备
1> disk init name = casedev01,physname="/oradata/sybase/sicase/casedev01",size="1024m"
2> disk init name = casedev02,physname="/oradata/sybase/sicase/casedev02",size="1024m"
3> disk init name=tempdev01,physname="/oradata/sybase/sicase/tempdev01",size="500m"
4> go
创建数据库
1>
2> create database sicase on casedev01="1000m", casedev02="1000m"
3> go
CREATE DATABASE: allocating 512000 logical pages (1000.0 megabytes) on disk
'casedev01'.
CREATE DATABASE: allocating 512000 logical pages (1000.0 megabytes) on disk
'casedev02'.
扩展临时库
1> alter database tempdb on tempdev01="500m"
2> go
Extending database by 256000 pages (500.0 megabytes) on disk tempdev01
1> sp_helpdb tempdb
2> go
name db_size owner dbid created
status
------------------------ ------------- ------------------------ ------
--------------
------------------------------------------------------------------------------------------------------ tempdb 503.0 MB sa 2 May 08, 2008
select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data
(1 row affected)
device_fragments size usage
created free kbytes
------------------------------ ------------- --------------------
------------------- ----------------
master 3.0 MB data and log
May 8 2008 2:42PM 1802
tempdev01 500.0 MB data and log May 8 2008 3:02PM 509986
(return status = 0)
添加用户
1> sp_addlogin con,"sitechcase",sicase
2> go
Password correctly set.
Account unlocked.
New login created.
(return status = 0)
1> use sicase
2> go
1> sp_adduser con
2> go
New user added.
(return status = 0)
1> sp_role "grant",sa_role ,con
2> go
Authorization updated.
(return status = 0)
1> sp_role "grant",sso_role,con
2> go
Authorization updated.
(return status = 0)
更改数据库属主
1> use sicase
2> sp_changedbowner con sicase
3> go
Msg 102, Level 15, State 1:
Server 'DB', Line 2:
Incorrect syntax near 'sp_changedbowner'.
1> EXEC sp_changedbowner con
2> go
Msg 17362, Level 16, State 1:
Server 'DB', Procedure 'sp_changedbowner', Line 97:
The proposed new db owner already is a user in the database or owns the
database.
(return status = 1)
上面命令没有成功更改数据库属主,问题出在con应经是该数据库的用户了,所以应该首先在该库中删除con用户
1> sp_dropuser con
2> go
User has been dropped from current database.
(return status = 0)
1> sp_changedbowner con
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
Database owner changed.
(return status = 0)
1> sp_helpdb
2> go
name db_size owner dbid created
status
------------------------ ------------- ------------------------ ------
--------------
------------------------------------------------------------------------------------------------------ master 6.0 MB sa 1 May 08, 2008
mixed log and data
model 2.0 MB sa 3 May 08, 2008
mixed log and data
sicase 2000.0 MB con 4 May 08, 2008
select into/bulkcopy/pllsort, mixed log and data
sybsystemdb 2.0 MB sa 31513 May 08, 2008
mixed log and data
sybsystemprocs 120.0 MB sa 31514 May 08, 2008
trunc log on chkpt, mixed log and data
tempdb 503.0 MB sa 2 May 08, 2008
select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data
(1 row affected)
(return status = 0)
解决不能telnet本机5000端口问题
正常运行情况下,应用程序必须能访问5000端口。但是本机的5000端口只有本地才能访问。
[root@db ~]# lsof -i :5000
COMMAND PID USER FD TYPE DEVICE SIZE NODE NAME
dataserve 21221 sybase 13u IPv4 18952868 TCP db:5000 (LISTEN)
dataserve 21221 sybase 14u IPv4 18998135 TCP db:5000->db:45610 (ESTABLISHED)
isql 29121 sybase 6u IPv4 18998134 TCP db:45610->db:5000 (ESTABLISHED)
该命令显示5000端口已经打开,并且已有本地的访问连接,说明数据库的功能没有问题。
[sybase@db sybase]$ netstat -anv | grep 500*
tcp 0 0 127.0.0.1:5000 0.0.0.0:* LISTEN tcp 0 0 127.0.0.1:5001 0.0.0.0:* LISTEN tcp 0 0 127.0.0.1:5002 0.0.0.0:* LISTEN tcp 0 0 0.0.0.0:50008 0.0.0.0:* LISTEN 请注意上面标红的IP地址,表示服务器只监听127.0.0.1的5000端口,显然不对。
检查/etc/hosts表
root@db etc]# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 db localhost.localdomain localhost
172.16.9.85 app
172.16.9.86 db
172.16.9.245 app-ilo
172.16.9.84 app-srv
172.16.9.246 db-ilo
172.16.9.88 db-srv
# Interconnect RAC (Private Network)
172.16.9.95 app_rac
172.16.9.96 db_rac
172.16.9.18 https://www.360docs.net/doc/a116859841.html,
将上面标红的行中的db去掉,然后重启sybase数据库。
[root@db etc]# netstat -anv | grep 500*
tcp 0 0 172.16.9.86:50000.0.0.0:* LISTEN tcp 0 0 172.16.9.86:5002 0.0.0.0:* LISTEN