达梦数据库安装部署(命令行模式安装)

环境:
OS:Centos 7
DB:dm8

 

1.创建安装用户
groupadd dinstall
useradd -g dinstall dmdba
passwd dmdba

密码设置为dameng

 

2. 修改操作系统限制
vi /etc/security/limits.conf

dmdba hard nofile 65536
dmdba soft nofile 65536
dmdba hard stack 32768
dmdba soft stack 16384

 

然后执行sysctl -p生效

[root@host85 soft]# sysctl -p 

 

4.关闭防火墙
systemctl stop firewalld
systemctl disable firewalld
sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config

 

5.安装依赖包
yum install glibc
yum install libXp
yum install libXt
yum install libXtst

yum install unzip

 

6.解压安装包并挂载镜像文件
[root@localhost soft]# unzip dm8_20220525_x86_rh6_64.zip
[root@localhost soft]# cd dm8_20220525_x86_rh6_64_ent/
[root@localhost dm8_20220525_x86_rh6_64_ent]# mount -o loop dm8_20220525_x86_rh6_64.iso /mnt
##解压后的文件
[root@localhost mnt]# pwd
/mnt
[root@localhost mnt]# ls
DM8 Install.pdf DMInstall.bin

 

7.创建目录
[root@localhost /]#mkdir -p /dmdbms/product --数据库软件的安装目录
[root@localhost /]#mkdir -p /dmdbms/data --数据目录
[root@localhost /]#mkdir -p /dmdbms/arch --归档目录
[root@localhost /]#mkdir -p /dmdbms/backup --备份目录

[root@localhost /]#chown -R dmdba:dinstall /dmdbms --授权dmdbms给dmdba用户

 

8.安装数据库软件

[root@localhost mnt]# su - dmdba
[dmdba@localhost ~]$ cd /mnt
[dmdba@localhost mnt]$./DMInstall.bin -i
Please select the installer's language (E/e:English C/c:Chinese) [E/e]:e
Extract install files......... 
Welcome to DM DBMS Installer

Whether to input the path of Key File? (Y/y:Yes N/n:No) [Y/y]:n
Whether to Set The TimeZone? (Y/y:Yes N/n:No) [Y/y]:y
TimeZone:
[ 1]: GTM-12=West Date Line
[ 2]: GTM-11=Samoa
[ 3]: GTM-10=Hawaii
[ 4]: GTM-09=Alaska
[ 5]: GTM-08=Pacific(America and Canada)
[ 6]: GTM-07=Arizona
[ 7]: GTM-06=Central(America and Canada)
[ 8]: GTM-05=East(America and Canada)
[ 9]: GTM-04=Atlantic(America and Canada)
[10]: GTM-03=Brasilia
[11]: GTM-02=Middle Atlantic
[12]: GTM-01=Azores
[13]: GTM=Greenwich Mean Time
[14]: GTM+01=Sarajevo
[15]: GTM+02=Cairo
[16]: GTM+03=Moscow
[17]: GTM+04=AbuDhabi
[18]: GTM+05=Islamabad
[19]: GTM+06=Dakar
[20]: GTM+07=BangKok,Hanoi
[21]: GTM+08=China
[22]: GTM+09=Seoul
[23]: GTM+10=Guam
[24]: GTM+11=Solomon
[25]: GTM+12=Fiji
[26]: GTM+13=Nukualofa
[27]: GTM+14=Kiribati
Please Select the TimeZone [21]:21

Installation Type:
1 Typical
2 Server
3 Client
4 Custom
Please Input the number of the Installation Type [1 Typical]:1
Require Space: 1585M

Please Input the install path [/home/dmdba/dmdbms]:/dmdbms/product
Available Space:117G
Please Confirm the install path(/dmdbms/product)? (Y/y:Yes N/n:No) [Y/y]:y

Pre-Installation Summary
Installation Location: /dmdbms/product
Require Space: 1585M
Available Space: 117G
Version Information: 
Expire Date: 
Installation Type: Typical
Confirm to Install? (Y/y:Yes N/n:No):y

2022-07-08 14:33:06 
[INFO] Installing DM DBMS...
2022-07-08 14:33:06 
[INFO] Installing BASE Module...
2022-07-08 14:33:29 
[INFO] Installing SERVER Module...
2022-07-08 14:33:33 
[INFO] Installing CLIENT Module...
2022-07-08 14:33:43 
[INFO] Installing DRIVERS Module...
2022-07-08 14:34:04 
[INFO] Installing MANUAL Module...
2022-07-08 14:34:07 
[INFO] Installing SERVICE Module...
2022-07-08 14:34:08 
[INFO] Move log file to log directory.
2022-07-08 14:34:09 
[INFO] Installed DM DBMS completely.

Please execute the commands by root:
/dmdbms/product/script/root/root_installer.sh

End

 

根据提示在root账号下执行脚本
[root@localhost product]# /dmdbms/product/script/root/root_installer.sh
Move /dmdbms/product/bin/dm_svc.conf to /etc
Modify the files' mode of DM Server 
Create the DmAPService service
Created symlink from /etc/systemd/system/multi-user.target.wants/DmAPService.service to /usr/lib/systemd/system/DmAPService.service.
Finished to create the service (DmAPService)
Start the DmAPService service

 

查看服务
[root@localhost product]# systemctl status DmAPService.service
● DmAPService.service - DM Assistant Plug-In Service(DmAPService).
   Loaded: loaded (/usr/lib/systemd/system/DmAPService.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2022-07-08 14:35:24 CST; 19s ago
  Process: 21524 ExecStart=/dmdbms/product/bin/DmAPService start (code=exited, status=0/SUCCESS)
 Main PID: 21550 (dmap)
   CGroup: /system.slice/DmAPService.service
           └─21550 /dmdbms/product/bin/dmap

Jul 08 14:35:09 localhost.localdomain systemd[1]: Starting DM Assistant Plug-In Service(DmAPService)....
Jul 08 14:35:24 localhost.localdomain DmAPService[21524]: [33B blob data]
Jul 08 14:35:24 localhost.localdomain systemd[1]: Started DM Assistant Plug-In Service(DmAPService)..

 

 

10.dmdba账号设置环境变量

 

export LD_LIBRARY_PATH="$LD_LIBRARY_PATH:/dmdbms/product/bin"
export DM_HOME="/dmdbms/product"
export PATH=$DM_HOME/bin:$DM_HOME/tool:$PATH

 

  

9.初始化数据库与注册服务

 

或是一个命令执行

su - dmdba

dminit path=/dmdbms/data charset=1 db_name=slnngk instance_name=slnngk SYSDBA_PWD="dameng123" SYSAUDITOR_PWD="dameng123"

  

 

dminit path=/dmdbms/data charset=1 case_sensitive=0 length_in_char=0 db_name=slnngk instance_name=slnngk SYSDBA_PWD="dameng123" SYSAUDITOR_PWD="dameng123"

 

 说明:

1. 参数和值之间不能有空格

2.交换模式创建数据库或图形界面创建的数据库，系统设置的db_name和instance_name都是DAMENG, 注册服务的-p参数是DMSERVER

 

[root@host02 root]# cd /dmdbms/product/script/root
[root@host02 root]# ./dm_service_installer.sh -t dmserver -dm_ini /dmdbms/data/DAMENG/dm.ini -p DMSERVER
[root@host01 root]# systemctl start DmServiceDMSERVER.service
[root@host01 root]# systemctl status DmServiceDMSERVER.service

  

 

charset:字符集 值:0[GB18030],1[UTF-8],2[EUC-KR]
case_sensitive:大小写是否敏感 1敏感 0不敏感，现有库查看    取值：Y、y、1 表示敏感；N、n、0 表示不敏感。缺省值为 Y

SQL> select PARA_VALUE from v$dm_ini where PARA_NAME like '%CASE_SENSITIVE%';

LINEID     PARA_VALUE
---------- ----------
1          1

LENGTH_IN_CHAR:varchar中定义的数字是字符为单位还是字节为单位 1字符为单位 0字节为单位 默认值0，现有库查看

SQL> select name, value from SYS."V$PARAMETER" t where t.NAME='LENGTH_IN_CHAR';

LINEID     NAME           VALUE
---------- -------------- -----
1          LENGTH_IN_CHAR 0

 

 

su - dmdba
[dmdba@localhost bin]$ cd /dmdbms/product/bin
[dmdba@localhost bin]$ ./dminit
initdb V8
db version: 0x7000c
file dm.key not found, use default license!
License will expire on 2023-05-25
input system dir: /dmdbms/data
input db name: slnngk
input port num: 5236
input page size(4, 8, 16, 32): 8
input extent size(16, 32, 64): 16
input sec priv mode(0, 1, 2): 0
input time zone(-12:59,+14:00): +8
string case sensitive? ([Y]es, [N]o): y
which charset to use? (0[GB18030], 1[UTF-8], 2[EUC-KR]): 1
length in char? ([Y]es, [N]o): n
enable database encrypt? ([Y]es, [N]o): n
input slice size(0, 512, 4096): 4096
page check mode? (0/1/2): 0
input elog path: /tmp/elog
only create huge table with delta? (0/1): 1
rlog generate for huge? (0/1): 0
pseg_mgr_flag (0/1): 0
auto_overwrite mode? (0/1/2): 0
CHARACTER type fixed storage ? ([Y]es/1, [N]o/0): n
SQL log forbid ? ([Y]es/1, [N]o/0): n
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
 log file path: /dmdbms/data/slnngk/slnngk01.log


 log file path: /dmdbms/data/slnngk/slnngk02.log

write to dir [/dmdbms/data/slnngk].
create dm database success. 2022-07-08 15:23:17

 

下面注册服务
切换至注册入口路径，执行命令，注册服务
需要用操作系统root账号登陆注册

[root@localhost root]#cd /dmdbms/product/script/root

[root@localhost root]# ./dm_service_installer.sh -t dmserver -dm_ini /dmdbms/data/slnngk/dm.ini -p slnngk
Created symlink from /etc/systemd/system/multi-user.target.wants/DmServiceslnngk.service to /usr/lib/systemd/system/DmServiceslnngk.service.
Finished to create the service (DmServiceslnngk)

 

启动服务
[root@localhost root]#systemctl start DmServiceslnngk.service
[root@localhost root]# systemctl status DmServiceslnngk.service

 

不需要服务方式启动的话，可以使用如下命令启动

su - dmdba

nohup /dmdbms/product/bin/dmserver path=/dmdbms/data/slnngk/dm.ini & 

  

注册了服务的话，还可以使用如下方式启动

 

su - dmdba
cd /dmdbms/product/bin
[dmdba@host135 bin]$ ./DmServiceslnngk start
./DmServiceslnngk: line 238: ulimit: core file size: cannot modify limit: Operation not permitted
Starting DmServiceslnngk: 
                                                           [ OK ]
解决办法(root账号操作):
vi /etc/security/limits.conf
添加如下两项
dmdba hard core unlimited
dmdba soft core unlimited

   

10.登录
验证是否安装成功，登录可以使用windows客户端，但我没有安装，我们就用服务器上的客户端进行登录.
服务启动成功后，登录达梦数据库客户端进行验证：
用户名:SYSDBA 密码:SYSDBA 端口:5236

[root@localhost dmdb]# su - dmdba
[dmdba@localhost tool]$ /dmdbms/product/tool/disql
disql V8
SQL> connect sysdba
password:

Server[LOCALHOST:5236]:mode is normal, state is open
login used time : 2.886(ms)

SQL> select username from dba_users;

LINEID USERNAME
---------- ----------
1 SYSAUDITOR
2 SYSSSO
3 SYSDBA
4 SYS

sys -----达梦数据库内置管理用户，不能登录数据库，数据库使用的大部分的数据字典和动态性能视图sys
sysdba -----数据库的管理员
sysauditor ---审计用户
syssso ---安全用户

这里可以修改数据库账号密码
alter user sysdba identified by "dameng123";
alter user sys identified by "dameng123";

alter user SYSSSO identified by "dameng123"; ##修改不了,也不能登陆
alter user SYSAUDITOR identified by "dameng123"; ##该账号修改密码需要使用该账号登陆,默认密码是:SYSAUDITOR

 

 

11.客户端工具登陆
下载windos客户端工具,通过该工具进行连接
dm8_20220519_x86_win_64_ent.zip

12.数据库开启归档模式(命令模式修改)
例如开启归档并限制归档空间为 200 GB，如下所示：
disql sysdba/dameng123

alter database mount;
alter database add archivelog 'dest=/dmdbms/arch,TYPE=local,FILE_SIZE=1024';
alter database archivelog;
alter database open;

 

说明:

a.ARCH_FILE_SIZE = 1024 #单个归档文件大小，单位MB，取值范围（64~2048），默认为1024MB，即1G

b.ARCH_SPACE_LIMIT = 4096 #归档文件空间限制，单位MB，取值范围（1024~4294967294），0表示无空间限制

c.设置了归档后系统会自动修改dm.ini配置文件中的参数

 

ARCH_INI                        = 1                     #dmarch.ini

 

 

查看是否配置归档
select name,arch_mode from v$database;

查看是否归档,及归档的空间,路径
select arch_name,arch_type,arch_dest,arch_file_size,arch_space_limit from v$dm_arch_ini;

查看数据库实例名,状态,主机名
select instance_name,status$,host_name from v$instance;

归档切换
alter system archive log current;

 

 

13.配置文件参数vim dmmal.ini(用于后续的主备部署不需要重启主库)

[dmdba@host01 DAMENG]$ more dmmal.ini 
#DaMeng Database Mail Configuration file
#this is comments
MAL_CHECK_INTERVAL     = 5
MAL_COMBIN_BUF_SIZE    = 0
MAL_SEND_THRESHOLD = 2048
MAL_CONN_FAIL_INTERVAL = 5
MAL_LOGIN_TIMEOUT      = 15
MAL_BUF_SIZE           = 100
MAL_SYS_BUF_SIZE       = 0
MAL_VPOOL_SIZE         = 128
MAL_COMPRESS_LEVEL     = 0
MAL_TEMP_PATH          = 

[MAL_INST1]
    MAL_INST_NAME    = SLNNGK
    MAL_HOST         = 192.168.1.101
    MAL_PORT         = 61141
    MAL_INST_HOST    = 192.168.1.101
    MAL_INST_PORT    = 5236
    MAL_DW_PORT      = 52141
    MAL_LINK_MAGIC   = 0
    MAL_INST_DW_PORT = 33141

 

 

注意这里需要将dm.ini文件如下设置打开

MAL_INI                         = 1                     #dmmal.ini 

或是使用系统过程进行打开设置

SP_SET_PARA_VALUE(2, 'MAL_INI', 1);

这个配置需要重启数据库的

systemctl restart DmServiceslnngk.service

 

13.日志目录
日志目录存放在数据库软件的安装目录下
[root@localhost log]# ls -al
total 384
drwxr-xr-x 2 dmdba dinstall 205 Jul 8 15:35 .
drwxr-xr-x 16 dmdba dinstall 251 Jul 8 14:34 ..
-rw-r--r-- 1 dmdba dinstall 0 Jul 8 14:35 DmAPService.log
-rw-r--r-- 1 dmdba dinstall 162 Jul 8 14:35 dm_dmap_202207.log
-rw-rw-r-- 1 dmdba dinstall 26381 Jul 8 15:56 dm_DMSERVER_202207.log
-rw-r--r-- 1 dmdba dinstall 5206 Jul 8 15:56 DmServiceDMSERVER.log
-rw-r--r-- 1 dmdba dinstall 388 Jul 8 15:35 dmsvc_sh.log
-rw-rw-r-- 1 dmdba dinstall 734 Jul 8 15:35 dm_unknown_202207.log
-rw-rw-r-- 1 dmdba dinstall 339650 Jul 8 14:34 install_ant.log
-rwxr-xr-x 1 dmdba dinstall 504 Jul 8 14:34 install.log
[root@localhost log]# pwd
/dmdbms/product/log

 

14.创建schema(与oracle一样)
创建表空间
create tablespace tps_hxl datafile '/dmdbms/data/slnngk/tps_hxl01.DBF' size 1024 autoextend on maxsize 32768; ##这里单位是M,文件目录存储在/dmdata/dmdb/DAMENG,这里设置文件最大值,不设置的话就是16TB
alter tablespace tps_hxl add datafile '/dmdbms/data/slnngk/tps_hxl02.DBF' size 1024 autoextend on;
alter tablespace tps_hxl add datafile '/dmdbms/data/slnngk/tps_hxl03.DBF' size 1024; ##默认就是自动扩展的
drop tablespace tps_hxl; ##删除表空间,数据文件一并删除掉了

create user hxl identified by dameng123 default tablespace tps_hxl default index tablespace tps_hxl;
grant dba to hxl;

 

使用新创建的用户的登录

 

disql hxl/dameng123

   

开发者权限

 

grant resource to hxl;
grant public to hxl;

grant VTI to hxl;

grant SELECT ANY VIEW to hxl; ##查询sys下的对象,比如sys.dba_segments
GRANT soi TO hxl;

–内置角色分类
DB_AUDIT 开头为审计相关角色，默认赋给了 SYSAUDITOR；
DB_POLICY 开头的为安全相关角色，默认赋给 SYSSSO；
其他 DBA、RESOURCE、PULIBC、SOI、VTI 等默认赋给了 SYSDBA。
DBA：系统管理员角色，拥有除审计和强制访问控制之外的几乎所有权限，
RESOURCE：拥有 CREATE 创建表、索引、视图等对象定义的权限和数据操作权限（DML 操作）。
PULIBC：拥有数据操作权限（增删改查操作），没有创建表、视图等对象定义权限。
SOI：具有查询系统表（SYS 开头的）查询权限。
VTI 具有查询动态视图（v$开头的）权限。 

  

 

设置密码永不过期
alter user hxl limit password_life_time unlimited;

查看用户密码是否过期
select u.username,p.FAILED_NUM,p.life_time from SYSUSERS p,dba_users u where p.FAILED_NUM not in ('0') order by 1,2 ;
##life_time为0,表示不限制密码过期天数

15.创建表并写入数据
create table tb_test
(
id number,
name varchar(32)
);

insert into tb_test values(1,'name1');
insert into tb_test values(2,'name2');
insert into tb_test values(3,'name3');
insert into tb_test values(4,'name4');
insert into tb_test values(5,'name5');

获取表的ddl
select dbms_metadata.get_ddl ('TABLE','TB_TEST','HXL') FROM DUAL;
SQL> select dbms_metadata.get_ddl ('TABLE','TB_TEST','HXL') FROM DUAL;

LINEID DBMS_METADATA.GET_DDL('TABLE','TB_TEST','HXL')
---------- ------------------------------------------------------------------------------------------------------
1 CREATE TABLE "HXL"."TB_TEST"
(
"ID" NUMBER,
"NAME" VARCHAR(32)) STORAGE(ON "TPS_HXL", CLUSTERBTR) ;

used time: 77.344(ms). Execute id is 813

获取通过索引名获取索引ddl
SQL> select dbms_metadata.get_ddl ('INDEX','IDX_ID','HXL') FROM DUAL;

LINEID DBMS_METADATA.GET_DDL('INDEX','IDX_ID','HXL')
---------- ---------------------------------------------------------------------------------------
1 CREATE INDEX "IDX_ID" ON "HXL"."TB_TEST"("ID" ASC) STORAGE(ON "TPS_HXL", CLUSTERBTR) ;

used time: 4.024(ms). Execute id is 818.
SQL>

 

create table tb_test_01
(
id bigint identity(1,1) primary key, ##类似mysql的AUTO_INCREMENT
name varchar(32),
createdate datetime default sysdate,
updatetime datetime default sysdate,
);

 

insert into tb_test02(id,name) values(1,'name1');
insert into tb_test02(id,name) values(2,'name2');
insert into tb_test02(id,name) values(3,'name3');
insert into tb_test02(id,name) values(4,'name4');
insert into tb_test02(id,name) values(5,'name5');

16.开发者只需要的权限
revoke dba from hxl;
grant resource to hxl;
grant public to hxl;

17.修改连接数
修改dm.ini文件,找到如下项目进行修改
#database
MAX_SESSIONS = 3000 #Maximum number of concurrent sessions

然后进行重启动
systemctl stop DmServiceDMSERVER.service
systemctl start DmServiceDMSERVER.service

查看
disql sysdba/dameng123
SQL> SELECT SF_GET_PARA_VALUE (1, 'MAX_SESSIONS');

LINEID SF_GET_PARA_VALUE(1,'MAX_SESSIONS')
---------- -----------------------------------
1 3000

used time: 11.475(ms). Execute id is 500.

18.查看数据库字符集
select SF_GET_UNICODE_FLAG();
select UNICODE ();

##0表示 GB18030,1表示 UTF-8,2表示 EUC-KR

 

18.导出查询结果数据(跟oracle一致)
SQL> spool a.txt;
SQL> select * from hxl.tb_test;

LINEID ID NAME
---------- -- -----
1 1 name1
2 2 name2
3 3 name3
4 4 name4
5 5 name5

used time: 0.293(ms). Execute id is 504.
SQL> spool off;

 

19.使用自增列(identity)
create table test(id int identity,name varchar(20));

  

 

常用参数设置:

dm.in

ALTER_TABLE_OPT=3 ##加快带默认值的字段添加

MAX_SESSIONS   = 10000 ##最大连接数

 

COMPATIBLE_MODE = 0 ##兼容模式
Server compatible mode, 0:none, 1:SQL92, 2:Oracle, 3:MS SQL Server, 4:MySQL, 5:DM6, 6:Teradata

 

 连接方式:

 

通过ip和端口连接
disql icstock/icstock123456@192.168.1.136:5236

 

本地服务器的话 通过指定端口连接
disql icstock/icstock123456:5236

 

通过服务名连接

创建服务文件dm_svc.conf

内容如下: 

mydm_dsc=(192.168.58.101:5236,192.168.58.102:5236)
SWITCH_TIME=(10000)
SWITCH_INTERVAL=(1000)

linux环境该文件放置/etc/目录下

windows环境放置在C:\Windows\System32目录下

说明安装了达梦客户端,自动会在C:\Windows\System32目录下创建dm_svc.conf文件，但是该文件编辑保持不了，我是把它拷贝出来修改后再拷贝回去就可以了。

服务名连接方式:

C:\Users\Administrator>disql.exe SYSDBA/SYSDBA@mydm_dsc

 

20.避免忘记sysdba密码无法登录,需要修改如下参数

SQL> sp_set_para_value(2,'ENABLE_LOCAL_OSAUTH',1);

然后重启动数据库

 

增加dmdba组，并添加为dmdba用户的附组

 

[root@DM ~]# groupadd dmdba

 

[root@DM ~]# usermod -G dmdba dmdba

 

[root@DM ~]# id dmdba

 

uid=1001(dmdba) gid=2001(dinstall)组=2001(dinstall),2002(dmdba)

 

以系统认证方式登录

 

[dmdba@DM ~]$ disql / as sysdba

 

 

那么就可以通过如下方式登录了

[dmdba@DM ~]$ disql / as sysdba