达梦8数据库安装配置

达梦8安装过程

一、安装前确认软硬件信息
检查系统版本
uname -a
cat  /etc/redhat-release
--6.9
用户
cat /etc/passwd
cat /etc/group
服务
service --status-all
chkconfig --list
systemctl 
内存
free
--64G
磁盘空间 --500G
df -h
cpu
cat /proc/cpuinfo

二、关掉不必要的用户、服务、防火墙、selinux
groupdel groupid
userdel userid
service service_name stop
vim /etc/security/limits.conf
dmdba soft nofile 4096
dmdba hard nofile 65536

修改/etc/sysctl.conf添加或修改如下参数
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 4294967295
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
执行sysctl –p使参数生效
关闭不需要的服务
for service in NetworkManager abrt-ccpp abrtd acpid atd cups cpuspeed firstboot netfs nfslock ntpd rpcgssd smartd ip6tables iptables kdump libvirt-guests postfix rhnsd rhsmcertd; do chkconfig $service off; done;
删除不必要的用户
for user in adm lp sync shutdown halt uucp operator gopher games; do userdel $user; done;
查看防火墙状态。
systemctl status firewalld
永久关闭防火墙命令。重启后，防火墙不会自动启动。
systemctl disable firewalld

三、准备安装
建组、用户、上传安装包
groupadd dinstall
useradd -g dinstall dmdba
passwd dmdba
ftp上传安装包、挂载
mount -o loop /home/dmdba/dm8_20210818_x86_rh6_64_ent_8.4.2.18_pack14.iso /mnt
规划安装目录：/home/dmdba/dm8

创建指定大小磁盘空间的逻辑卷，比如 10G
lvcreate -L100G vg00
创建使用所有剩余磁盘空间的逻辑卷
lvcreate –l 100%FREE vg00
创建文件系统 
mkfs -t ext4 /dev/vg00/lvol0
3.1.3.3 挂载文件系统 
mkdir /home/dmdba/dm8
mount /dev/vg00/lvol0 /home/dmdba/dm8
vi /etc/fstab
/dev/vg00/lvol0 /home/dmdba/dm8 ext4 defaults 1 2
chown dmdba:dinstall /home/dmdba/dm8

su - dmdba
vi /home/dmdba/.bash_profile
加：
export DM_HOME=/home/dmdba/dm8
export PATH=$DM_HOME/bin:$PATH
source /home/dmdba/.bash_profile

四、安装
cd /mnt
./DMInstall.bin -i
root用户执行/home/dmdba/dm8/script/root/root_installer.sh
cp dm.key /home/dmdba/dm8/bin
重起数据库实例

五、创建数据库
命令 dminit
[dmdba@aaaa1 bin]$ ./dminit path=/home/dmdba/dm8/data db_name=DAMENG instance_name=DMSERVER port_num=5236
注册服务（root用户）
cd /home/dmdba/dm8/script/root
[root@aaaa1 root]# ./dm_service_installer.sh -t dmserver -dm_ini /home/dmdba/dm8/data/DAMENG/dm.ini -p DMSERVER
启动达梦数据库服务
service DmServiceDMSERVER start
service DmServiceDMSERVER status

客户端连接
dm_svc.conf
ABC=(192.168.20.61:5236)
配置文件在 Windows c:\windows\system32
Linux 在/etc
disql可以登录数据库

数据文件
select * from v$datafile;
重做日志
select * from v$rlogfile;
缺省2个文件，扩大到500m
alter database resize logfile '/home/dmdba/dm8/data/DAMENG/DAMENG01.log' to 500;
alter database resize logfile '/home/dmdba/dm8/data/DAMENG/DAMENG02.log' to 500;
增加3个
alter database add logfile '/home/dmdba/dm8/data/DAMENG/DAMENG03.log' size 500;
alter database add logfile '/home/dmdba/dm8/data/DAMENG/DAMENG04.log' size 500;
alter database add logfile '/home/dmdba/dm8/data/DAMENG/DAMENG05.log' size 500;

select * from v$instance;
修改oguid
sp_set_oguid(123);

达梦可以直接切换数据库状态，没有操作时
alter database open；
--mount -> open
alter database mount;
--open -> mount

控制文件
select *  from v$dm_ini where para_name like 'CTL%';
备份控制文件
dmctlcvt help
dmctlcvt type=1 src=/home/dmdba/dm8/data/DAMENG/dm.ctl dest=/tmp/dm.ctl.txt
more /tmp/dm.ctl.txt

参数
dm.ini 初始文件以下参数小，扩大
                MEMORY_POOL                     = 2000                   #Memory Pool Size In Megabyte
                MEMORY_N_POOLS                  = 10                     #Number of Memory Pool
                MEMORY_TARGET                   = 20000                     #Memory Share Pool Target Size In Megabyte
                MEMORY_EXTENT_SIZE              = 10                     #Memory Extent Size In Megabyte
                BUFFER                          = 10000                   #Initial System Buffer Size In Megabytes

停止数据库，备份参数文件，修改，启动数据库
创建应用表空间
SQL> create tablespace tbs_future datafile '/home/dmdba/dm8/data/DAMENG/tbs_future01.dbf' size 100 autoextend on next 1 maxsize 8000,
2   '/home/dmdba/dm8/data/DAMENG/tbs_future02.dbf' size 100 autoextend on  maxsize 8000;
 
  
select * from v$dm_ini where para_name like 'PWD%';
select * from v$dm_ini where para_name like 'COMP%';

创建应用用户
create user future identified by future123 default tablespace tbs_future 
alter user  future 
diskspace no limit ---磁盘空间限制
limit SESSION_PER_USER 200
CONNECT_IDLE_TIME 20 ---空闲时间
CONNECT_TIME 4 ---连接时间
CPU_PER_CALL 100 --请求 CPU 时间
CPU_PER_SESSION 200 --会话使用 CPU 的时间
MEM_SPACE 300 ---内存
READ_PER_SESSION 400 ---每个会话读多少页
FAILED_LOGIN_ATTEMPS 8 --尝试登陆次数
PASSWORD_LIFE_TIME 120 ---有效时间
PASSWORD_LOCK_TIME 5 --锁定时间
PASSWORD_GRACE_TIME 10

grant resource to future;
grant drop any table to future;

-- Create table
create table T_TRADINGCODE
(
  brokerid       CHAR(10) not null,
  investorid     CHAR(12) not null,
  exchangeid     CHAR(8) not null,
  clientid       CHAR(10) not null,
  isactive       NUMBER(1) not null,
  opendate       CHAR(8),
  cancledate     CHAR(8),
  opencardtype   CHAR(1),
  opencardno     CHAR(50),
  address        CHAR(100),
  codesourcetype CHAR(1),
  freezestatus   CHAR(1) not null
);

-- Create/Recreate indexes 
create unique index IDX_TRADINGCODE1 on T_TRADINGCODE (CLIENTID, EXCHANGEID, BROKERID, INVESTORID)
  tablespace TBS_FUTURE;

-- Create/Recreate primary, unique and foreign key constraints 
alter table T_TRADINGCODE
  add constraint PK_TRADINGCODE primary key (BROKERID, INVESTORID, EXCHANGEID, CLIENTID)
  using index 
  tablespace TBS_FUTURE ;

逻辑备份恢复
insert 数据 
 dexp sysdba/SYSDBA file=/home/dmdba/dm8/backup/full.dmp full=y log=/home/dmdba/dm8/backup/full.log

  disql
  drop table future.t_tradingcode;
  
  dimp sysdba/SYSDBA file=/home/dmdba/dm8/backup/full.dmp tables=future.t_tradingcode log=/home/dmdba/dm8/backup/imptc.log
  disql
  select * from future.t_tradingcode;
  
物理备份
冷备份
service DmServiceDMSERVER stop
dmrman
RMAN> backup database '/home/dmdba/dm8/data/DAMENG/dm.ini' backupset '/home/dmdba/dm8/backup/20220410'
 
恢复
dmrman
RMAN>  restore database '/home/dmdba/dm8/data/DAMENG/dm.ini' from backupset '/home/dmdba/dm8/backup/20220410'
RMAN>  recover database '/home/dmdba/dm8/data/DAMENG/dm.ini' from backupset '/home/dmdba/dm8/backup/20220410' 
RMAN>  restore database '/home/dmdba/dm8/data/DAMENG/dm.ini' update db_magic

热备份
service DmServiceDMSERVER start
SQL>select arch_mode from v$database;
SQL> alter database mount;
SQL> alter database add archivelog 'type=local,dest=/home/dmdba/dm8/arch,file_size=64,space_limit=0'; 
SQL> alter database archivelog;
SQL> alter database open;

SQL> backup database;
executed successfully
used time: 00:00:01.435. Execute id is 302.
SQL> backup database backupset '/home/dmdba/dm8/backup/20220410_full';
executed successfully
used time: 00:00:01.414. Execute id is 303.
SQL> backup tablespace future backupset '/home/dmdba/dm8/backup/tbs_future';
backup tablespace future backupset '/home/dmdba/dm8/backup/tbs_future';
[-3414]:Error in line: 1
invalid tablespace name [FUTURE].
used time: 0.293(ms). Execute id is 0.
SQL> backup tablespace tbs_future backupset '/home/dmdba/dm8/backup/tbs_future';
executed successfully
used time: 00:00:01.154. Execute id is 304.
SQL> backup table future.t_tradingcode backupset '/home/dmdba/dm8/backup/tb_tradcode';
executed successfully
used time: 00:00:01.175. Execute id is 305.
SQL> backup archivelog backupset '/home/dmdba/dm8/backup/archivelog';
executed successfully
used time: 00:00:01.248. Execute id is 306.

[root@aaaa1 dm8]# ll backup/
total 32
drwxr-xr-x. 2 dmdba dinstall 4096 Apr 10 12:03 20220410
drwxr-xr-x. 2 dmdba dinstall 4096 Apr 10 14:07 20220410_full
drwxr-xr-x. 2 dmdba dinstall 4096 Apr 10 14:13 archivelog
-rw-r--r--. 1 dmdba dinstall 6259 Apr 10 11:38 full.dmp
-rw-r--r--. 1 dmdba dinstall  442 Apr 10 11:45 imptc.log
drwxr-xr-x. 2 dmdba dinstall 4096 Apr 10 14:10 tbs_future
drwxr-xr-x. 2 dmdba dinstall 4096 Apr 10 14:12 tb_tradcode
[root@aaaa1 dm8]# ll backup/archivelog/
total 276
-rw-r--r--. 1 dmdba dinstall 210432 Apr 10 14:13 archivelog.bak
-rw-r--r--. 1 dmdba dinstall  66048 Apr 10 14:13 archivelog.meta

恢复表空间
RMAN> restore database '/home/dmdba/dm8/data/DAMENG/dm.ini' tablespace tbs_future from backupset '/home/dmdba/dm8/backup/tbs_future'
restore database '/home/dmdba/dm8/data/DAMENG/dm.ini' tablespace tbs_future from backupset '/home/dmdba/dm8/backup/tbs_future'
file dm.key not found, use default license!
Database mode = 0, oguid = 123
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
EP[0]'s cur_lsn[30907], file_lsn[30907]
restore successfully.
time used: 00:00:02.935
RMAN> recover database '/home/dmdba/dm8/data/DAMENG/dm.ini' tablespace tbs_future 
recover database '/home/dmdba/dm8/data/DAMENG/dm.ini' tablespace tbs_future
Database mode = 0, oguid = 123
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
EP[0]'s cur_lsn[30907], file_lsn[30907]
EP:0 total 0 pkgs applied, percent: 0%
EP:0 total 5 pkgs applied, percent: 12%
EP:0 total 10 pkgs applied, percent: 25%
EP:0 total 15 pkgs applied, percent: 37%
EP:0 total 20 pkgs applied, percent: 50%
EP:0 total 25 pkgs applied, percent: 62%
EP:0 total 30 pkgs applied, percent: 75%
EP:0 total 35 pkgs applied, percent: 87%
EP:0 total 40 pkgs applied, percent: 100%
recover successfully.
time used: 581.115(ms)
RMAN> recover database '/home/dmdba/dm8/data/DAMENG/dm.ini' tablespace tbs_future with archivedir '/home/dmdba/dm8/backup/archivelog'
recover database '/home/dmdba/dm8/data/DAMENG/dm.ini' tablespace tbs_future with archivedir '/home/dmdba/dm8/backup/archivelog'
Database mode = 0, oguid = 123
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
EP[0]'s cur_lsn[30907], file_lsn[30907]
[-8365]:tablespace has not been restored