一、学习内容大纲
1）DM运维规范
2）分区表、索引管理
3）序列、同义词、物化视图
4）安全管理、数据迁移
5）快速数据加载、性能监控
6）DM数据守护集群（主备，读写分离）
7）达梦DBLINK
8）达梦DEM管理

二、考试内容
1）笔试考试 2小时
2）实操机试 4小时

三、重点知识整理
DEM管理（类似OEM）
-达梦配置步骤
1、vim /dm8/DAMENG/dm.ini //编辑dm.ini文件，修改以下4个参数值
19 MEMORY_POOL = 300 (最低200) #Memory Pool Size In Megabyte
30 BUFFER = 2000 （最低1000） #Initial System Buffer Size In Megabytes
34 KEEP = 64 （默认8） #system KEEP buffer size in Megabytes
42 SORT_BUF_SIZE = 50 #maximum sort buffer size in Megabytes
2、登录数据库，设置参数及启动初始dem脚本
[dmdba@KylinV10SP2-DM8 DAMENG]$ disql sysdba/damengdba
服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 25.199(ms)
disql V8
SQL> set define off; //设置
SQL> set char_code utf8; //设置字符集
SQL> start /home/dmdba/dmdbms/web/dem_init.sql //启动dem初始化脚本，脚本位于数据库安装目录的web下面（注意路径后面没有;）
SQL> /======================================================================
File: dem_init.sql
Version: 6.5.7
Summary: DEM的初始化SQL脚本
Tips: 使用disql运行此SQL脚本前,设置"set CHAR_CODE UTF8"
======================================================================*/
--SCHEMA
CREATE SCHEMA "DEM";
操作已执行
已用时间: 18.018(毫秒). 执行号:3200.

3、重启DEM数据库实例
[dmdba@KylinV10SP2-DM8 ~]$ systemctl restart DmServiceDMSERVER.service //重启需要root权限，为什么？
==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ====
重新启动“DmServiceDMSERVER.service”需要认证。
Authenticating as: root
Password:

4、配置tomcat
1)上传tomcat包到/dm8/tomcat/目录下
[dmdba@KylinV10SP2-DM8 tomcat]$ pwd
/dm8/tomcat
[dmdba@KylinV10SP2-DM8 tomcat]$ ll
-rw-r--r-- 1 root root 9852455 12月 16 11:13 apache-tomcat-8.0.20.zip
2）使用root账号需改tomcat包的所属主和组属性为dmdba:dinstall
[root@KylinV10SP2-DM8 tomcat]# chown dmdba:dinstall apache-tomcat-8.0.20.zip
[root@KylinV10SP2-DM8 tomcat]# ll
-rw-r--r-- 1 dmdba dinstall 9852455 12月 16 11:13 apache-tomcat-8.0.20.zip
3)解压tomcat
[dmdba@KylinV10SP2-DM8 tomcat]$ ll
drwxr-xr-x 9 dmdba dinstall 160 2月 15 2015 apache-tomcat-8.0.20
-rw-r--r-- 1 dmdba dinstall 9852455 12月 16 11:13 apache-tomcat-8.0.20.zip
4)为了方便操作，将apache-tomcat-8.0.20改名为tomcat8
[dmdba@KylinV10SP2-DM8 tomcat]$ mv apache-tomcat-8.0.20 tomcat8
[dmdba@KylinV10SP2-DM8 tomcat]$ ll
-rw-r--r-- 1 dmdba dinstall 9852455 12月 16 11:13 apache-tomcat-8.0.20.zip
drwxr-xr-x 9 dmdba dinstall 160 2月 15 2015 tomcat8
5)tomcat解压后目录结构
[dmdba@KylinV10SP2-DM8 conf]$ pwd
/dm8/tomcat/tomcat8/conf
[dmdba@KylinV10SP2-DM8 conf]$ ll
-rw-r--r-- 1 dmdba dinstall 6600 2月 15 2015 server.xml
...略
[dmdba@KylinV10SP2-DM8 bin]$ pwd
/dm8/tomcat/tomcat8/bin
[dmdba@KylinV10SP2-DM8 bin]$ ll
-rw-r--r-- 1 dmdba dinstall 21362 2月 15 2015 catalina.sh
...略

6）修改tomcat配置文件server.xml参数
vim tomcat8/conf/server.xml
<Connector port="8080" protocol="HTTP/1.1"
maxThreads="150"
connectionTimeout="20000"
redirectPort="8443"
maxPostSize="-1" />

7）配置jvm启动参数（可选）
vim /tomcat/bin/catalina.sh
JAVA_OPTS="-server -Xms512m -Xmx1024m -XX:MaxPermSize=512m -Djava.library.path=/home/dmdba/dmdbms/bin" //在首行或末行添加

8)修改/tomcat/tomcat8/bin/下的脚本文件权限为775，使dmdba账号具备脚本执行权限
[dmdba@KylinV10SP2-DM8 bin]$ ll
-rw-r--r-- 1 dmdba dinstall 21465 12月 16 12:35 catalina.sh
...略
[dmdba@KylinV10SP2-DM8 bin]$ chmod 775 -R /dm8/tomcat/tomcat8/bin/
-rwxrwxr-x 1 dmdba dinstall 21465 12月 16 12:35 catalina.sh
...略

9）配置后台数据库的连接信息
-查看java版本信息
[root@KylinV10SP2-DM8 tomcat]# java -version
openjdk version "1.8.0_272"
OpenJDK Runtime Environment Bisheng (build 1.8.0_272-b10)
OpenJDK 64-Bit Server VM Bisheng (build 25.272-b10, mixed mode)
-进入JVM目录
[root@KylinV10SP2-DM8 tomcat]# cd /usr/lib/jvm/
[root@KylinV10SP2-DM8 jvm]# ll
drwxr-xr-x 3 root root 17 12月 13 08:36 java-1.8.0-openjdk-1.8.0.272.b10-7.ky10.x86_64
lrwxrwxrwx 1 root root 50 3月 11 2021 jre-1.8.0-openjdk-1.8.0.272.b10-7.ky10.x86_64 -> java-1.8.0-openjdk-1.8.0.272.b10-7.ky10.x86_64/jre
...略
-进入jre目录
[root@KylinV10SP2-DM8 jvm]# cd java-1.8.0-openjdk-1.8.0.272.b10-7.ky10.x86_64/jre/
[root@KylinV10SP2-DM8 jre]# ll
drwxr-xr-x 2 root root 172 12月 13 08:39 bin
drwxr-xr-x 9 root root 4096 12月 13 08:36 lib
-确认路径
[root@KylinV10SP2-DM8 jre]# pwd
/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.272.b10-7.ky10.x86_64/jre //将此目录作为JAVA_HOME变量，配置到dmdba环境变量中

-配置java环境变量
vim /hom/dmdba/.bash_profile

Source /root/.bashrc if user has one

[ -f ~/.bashrc ] && . ~/.bashrc
export DM_HOME="/home/dmdba/dmdbms"
export JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.272.b10-7.ky10.x86_64/jre //配置java家目录
export PATH=$JAVA_HOME/bin:$DM_HOME/bin:$DM_HOME/tool:$PATH //PATH添加JAVA_HOME

export LD_LIBRARY_PATH="$LD_LIBRARY_PATH:/home/dmdba/dmdbms/bin"
export DM_HOME="/home/dmdba/dmdbms"

-生效配置
[dmdba@KylinV10SP2-DM8 ~]$ source .bash_profile
[dmdba@KylinV10SP2-DM8 ~]$ echo $JAVA_HOME
/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.272.b10-7.ky10.x86_64/jre

-将数据库安装目录下的dem.war包拷贝到tomcat的webapps下
[dmdba@KylinV10SP2-DM8 ~]$ cp /home/dmdba/dmdbms/web/dem.war /dm8/tomcat/tomcat8/webapps/
[dmdba@KylinV10SP2-DM8 ~]$ ll //dm8/tomcat/tomcat8/webapps/
-rwxr-xr-x 1 dmdba dinstall 351953285 12月 16 13:23 dem.war
...

-启动tomcat
[dmdba@KylinV10SP2-DM8 ~]$ cd /dm8/tomcat/tomcat8/bin/
[dmdba@KylinV10SP2-DM8 bin]$ ll
-rwxrwxr-x 1 dmdba dinstall 1904 2月 15 2015 startup.sh
...
[dmdba@KylinV10SP2-DM8 bin]$ ./startup.sh

-启动后自动生成dem目录
[dmdba@KylinV10SP2-DM8 webapps]$ pwd
/dm8/tomcat/tomcat8/webapps
[dmdba@KylinV10SP2-DM8 webapps]$ ll //启动前,没有dem目录
-rwxr-xr-x 1 dmdba dinstall 351953285 12月 16 13:23 dem.war
...
[dmdba@KylinV10SP2-DM8 webapps]$ ll //启动后，自动生成dem目录
drwxr-xr-x 14 dmdba dinstall 268 12月 16 13:29 dem
-rwxr-xr-x 1 dmdba dinstall 351953285 12月 16 13:23 dem.war
...

-修改de.xml配置 (位置：/dm8/tomcat/tomcat8/webapps/dem/WEB-INF/)
[dmdba@KylinV10SP2-DM8 webapps]$ cd /dm8/tomcat/tomcat8/webapps/dem/WEB-INF/
[dmdba@KylinV10SP2-DM8 WEB-INF]$ ll
-rw-r--r-- 1 dmdba dinstall 461 1月 4 2023 db.xml
...

vim db.xml
<?xml version="1.0" encoding="UTF-8"?>
<ConnectPool>
<Server>192.168.1.201</Server> //IP
<Port>5236</Port> //实例端口
<User>SYSDBA</User> //账号
<Password>damengdba</Password> //密码
<InitPoolSize>5</InitPoolSize>
<CorePoolSize>10</CorePoolSize>
<MaxPoolSize>500</MaxPoolSize>
<KeepAliveTime>60</KeepAliveTime>
<DbDriver></DbDriver>
<DbTestStatement>select 1</DbTestStatement>
<SSLDir>../sslDir/client_ssl/SYSDBA</SSLDir>
<SSLPassword></SSLPassword>
</ConnectPool>

[dmdba@KylinV10SP2-DM8 WEB-INF]$ cat -n db.xml
1 <?xml version="1.0" encoding="UTF-8"?>
2 <ConnectPool>
3 <Server>192.168.1.201</Server>
4 <Port>5236</Port>
5 <User>SYSDBA</User>
6 <Password>damengdba</Password>
7 <InitPoolSize>5</InitPoolSize>
8 <CorePoolSize>10</CorePoolSize>
9 <MaxPoolSize>500</MaxPoolSize>
10 <KeepAliveTime>60</KeepAliveTime>
11 <DbDriver></DbDriver>
12 <DbTestStatement>select 1</DbTestStatement>
13 <SSLDir>../sslDir/client_ssl/SYSDBA</SSLDir>
14 <SSLPassword></SSLPassword>
15 </ConnectPool>

-重启tomcat
[dmdba@KylinV10SP2-DM8 bin]$ pwd
/dm8/tomcat/tomcat8/bin
[dmdba@KylinV10SP2-DM8 bin]$ ./shutdown.sh //停止
Using CATALINA_BASE: /dm8/tomcat/tomcat8
Using CATALINA_HOME: /dm8/tomcat/tomcat8
Using CATALINA_TMPDIR: /dm8/tomcat/tomcat8/temp
Using JRE_HOME: /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.272.b10-7.ky10.x86_64/jre
Using CLASSPATH: /dm8/tomcat/tomcat8/bin/bootstrap.jar:/dm8/tomcat/tomcat8/bin/tomcat-juli.jar
OpenJDK 64-Bit Server VM warning: ignoring option MaxPermSize=512m; support was removed in 8.0

[dmdba@KylinV10SP2-DM8 bin]$ ./startup.sh //启动
Using CATALINA_BASE: /dm8/tomcat/tomcat8
Using CATALINA_HOME: /dm8/tomcat/tomcat8
Using CATALINA_TMPDIR: /dm8/tomcat/tomcat8/temp
Using JRE_HOME: /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.272.b10-7.ky10.x86_64/jre
Using CLASSPATH: /dm8/tomcat/tomcat8/bin/bootstrap.jar:/dm8/tomcat/tomcat8/bin/tomcat-juli.jar
Tomcat started.

-IE登录DEM
地址：http://www.192.168.1.201:8080/dem
账号：admin
密码：888888

10、dem界面

11、纳管主机（三台主机）
-KylinV10SP2-DM8（服务器 192.168.201）
-KylinV10SP2-DM01（客户机01 192.168.144）
-KylinV10SP2-DM02（客户机02 192.168.146）
客户机安装代理(代理位置：/home/dmdba/dmdbms/tool/ ）
KylinV10SP2-DM8（DEM服务端）
-agent.ini位置
[dmdba@KylinV10SP2-DM8 bin]$ cd /home/dmdba/dmdbms/tool/dmagent/
[dmdba@KylinV10SP2-DM8 dmagent]$ ll
-rwxr-xr-x 1 dmdba dinstall 27956 12月 13 10:36 agent.ini
...
-配置agent.ini
[dmdba@KylinV10SP2-DM8 dmagent]$ vim agent.ini //#编辑,修改以下四个地方
#general
center_url = http://192.168.1.201:8080/dem
#ap
ap_enable = true
#service
service_enable = true
#gather
gather_enable = true
-安装代理
[dmdba@KylinV10SP2-DM8 dmagent]$ ./service.sh install // #root账号安装
must run the script by root!
[root@KylinV10SP2-DM8 dmagent]# ./service.sh install
input agent home [/home/dmdba/dmdbms/tool/dmagent] :
input agent.ini path [/home/dmdba/dmdbms/tool/dmagent/agent.ini] :
installation the service DmAgentService completed.
-启动服务
[root@KylinV10SP2-DM8 dmagent]# ./service.sh start //启动服务
重定向至 /bin/systemctl start DmAgentService.service //启动后DEM自动接管，《资源监控》-刷新即可看到

DM2
...（同DM1一样配置）
DM3
...(同DM1一样配置)
注意：配置网络：单一网络无需配置，多个网络时必须配置
DEM可以管理数据库、主机、进出等等是一套集中监控管理平台

*配置D-LINN:
-准备两个实例
实例1：//查询命令：SELECT NAME,INSTANCE_NAME,HOST_NAME, SVR_VERSION,DB_VERSION FROM V$INSTANCE;
SQL> SELECT NAME,INSTANCE_NAME,HOST_NAME, SVR_VERSION,DB_VERSION FROM V$INSTANCE;
行号 NAME INSTANCE_NAME HOST_NAME SVR_VERSION DB_VERSION

---

1 DMSERVER01 DMSERVER01 KylinV10SP2-DM01 DM Database Server x64 V8 DB Version: 0x7000c

实例2：//查询命令：SELECT NAME,INSTANCE_NAME,HOST_NAME, SVR_VERSION,DB_VERSION FROM V$INSTANCE;
SQL> SELECT NAME,INSTANCE_NAME,HOST_NAME, SVR_VERSION,DB_VERSION FROM V$INSTANCE;
行号 NAME INSTANCE_NAME HOST_NAME SVR_VERSION DB_VERSION

---

1 DMSERVER02 DMSERVER02 localhost.localdomain DM Database Server x64 V8 DB Version: 0x7000c

-两个实例分别编辑dm.ini,修改MAL_INI=1,即打开MAL系统
[dmdba@localhost ~]$ cd /dm8/DAMENG/
[dmdba@localhost DAMENG]$ vim dm.ini
dm.ini MAL_INI=1

-配置dmmal.ini文件(两个实例一样)

[MAL_INST1]
MAL_INST_NAME =DMSERVER01
MAL_HOST = 192.168.1.144
MAL_PORT = 5144 //MAL_PORT可以随意定义
MAL_INST_HOST = 192.168.1.144
MAL_INST_PORT = 5236 //创建实例是的端口号
[MAL_INST2]
MAL_INST_NAME = DMSERVER02
MAL_HOST = 192.168.1.146
MAL_PORT = 5146
MAL_INST_HOST = 192.168.1.146
MAL_INST_PORT = 5236

-重启实例，两台都要

[dmdba@KylinV10SP2-DM01 DAMENG]$ systemctl restart DmServiceDMSERVER01.service
==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ====
重新启动“DmServiceDMSERVER01.service”需要认证。
Authenticating as: root
Password:
==== AUTHENTICATION COMPLETE ====
[dmdba@KylinV10SP2-DM01 DAMENG]$ netstat -nltp
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp6 0 0 :::5236 ::😗 LISTEN 5424/dmserver
tcp6 0 0 :::5144 ::😗 LISTEN 5424/dmserver
...
[dmdba@localhost DAMENG]$ systemctl restart DmServiceDMSERVER02.service
==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ====
重新启动“DmServiceDMSERVER02.service”需要认证。
Authenticating as: root
Password:
==== AUTHENTICATION COMPLETE ====
[dmdba@localhost DAMENG]$ netstat -nltp
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp6 0 0 :::5236 ::😗 LISTEN 8122/dmserver
tcp6 0 0 :::5146 ::😗 LISTEN 8122/dmserver
....

-配置D-LINK（manager）
打开模拟器
[dmdba@KylinV10SP2-DM01 DAMENG]$ manager
创建外部连接(在实例1创建外部连接连接到实例2)/
create public link "DAMENG_TO_DAMENG" connect 'DAMENG' with "SYSDBA" identified by "damengdba" using '192.168.1.146/5236'; //公共外部连接
在实例2中创建表
在实例1中查询实例2中的表
select * frem table@DAMENG_TO_DAMENG;
在实例1中插入数据
insert into table@DAMENG_TO_DAMENG;
在实例2中查询插入的数据
-配置D-LINK（DPI）//不需要配置MAL，不需要重启实例
异构D-LINK

---

*数据守护（主备）
主：dwp可读写
备：dws只读
监视器:dmw
*常见双击热备
-基于HA软件的方案
-基于REDO日志的方案
*达梦数据守护
*常见术语
-主机和备机
-数据库模式：normal、primary、standby
-数据库状态:open、mount、suspend
-通信系统：MAL
-OGUID:数据守护唯一标识码
数据守护原理：
数据守护配置步骤：
归档：
-本地归档：local
-远程归档：DSC
-实时归档:实时主备使用实时归档
-1、主库在写联机日志之前，将redo日志发送到备库（通过mal系统发送）
-2、备库收到主库的redo日志后，进行日志重演，同时备库相应主库，已收到redo日志（不需要等日志重演完成）
-3、主库收到备库响应后，开始写入联机日志
-即时归档：读写分离（早期使用），（事务一致性模式）
-1、主库写入联机日志，通过mal系统将redo日志发送到备库
-2、备库收到redo日志后，开始进行日志重演，重演完成后，响应主库，收到日志
-3、主库收到备库响应信息后，开始响应用户请求

-异步归档：
-dmarch.ini:arch_wait_apply=0为高性能模式（1为事务一致性模式）

实验环境
主：可读写192.168.1.144
备：只读192.168.1.146
监视器:192.168.1.201
注意事项：可以配值8个监控监视器，一个确认监视器
推出：先推出监视器
考试是要做成服务

规划：
数据库名 实例名 PORT_NUM MAL_INST_DW_PORT MAL_HOST MAL_PORT MAL_DW_PORT
DAMENG DAMENG01 5236 45101 192.168.1.144 55101 65101
DAMENG DAMENG02 5236 45121 192.168.1.146 55121 65121
-关闭主备实例
[dmdba@KylinV10SP2-DM01 DAMENG]$ systemctl stop DmServiceDMSERVER01.service
==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ====
停止“DmServiceDMSERVER01.service”需要认证。
Authenticating as: root
Password:
==== AUTHENTICATION COMPLETE ====

[dmdba@localhost DAMENG]$ systemctl stop DmServiceDMSERVER02.service
==== AUTHENTICATING FOR org.frsktop.systemd1.manage-units ====
停止“DmServiceDMSERVER02.service”需要认证。
Authenticating as: root
Password:
==== AUTHENTICATION COMPLETE ====

-创建主、备库备份存放目录
主
[dmdba@KylinV10SP2-DM01 dm8]$ mkdir backup
[dmdba@KylinV10SP2-DM01 dm8]$ ll
总用量 0
drwxr-xr-x 2 dmdba dinstall 6 10月 22 16:47 backup
drwxr-xr-x 6 dmdba dinstall 320 10月 22 13:00 DAMENG
drwxr-xr-x 2 dmdba dinstall 6 10月 21 21:55 install_tmp
备
[dmdba@localhost dm8]$ mkdir backup
[dmdba@localhost dm8]$ ll
总用量 0
drwxr-xr-x 2 dmdba dinstall 6 10月 22 16:56 backup
drwxr-xr-x 6 dmdba dinstall 320 10月 22 12:56 DAMENG
drwxr-xr-x 2 dmdba dinstall 6 10月 21 22:08 install_tmp

-主库备份
命令：dmrman CTLSTMT="BACKUP DATABASE '/dm8/DAMENG/dm.ini' FULL TO BACKUP_FILE01 BACKUPSET '/dm8/backup/BACKUP_FILE_01'"
[dmdba@KylinV10SP2-DM01 dm8]$ dmrman CTLSTMT="BACKUP DATABASE '/dm8/DAMENG/dm.ini' FULL TO BACKUP_FILE1 BACKUPSET '/dm8/backup/BACKUP_FILE_01'"
dmrman V8
BACKUP DATABASE '/dm8/DAMENG/dm.ini' FULL TO BACKUP_FILE1 BACKUPSET '/dm8/backup/BACKUP_FILE_01'
file dm.key not found, use default license!
Database mode = 0, oguid = 0
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
EP[0]'s cur_lsn[59160], file_lsn[59160]
Processing backupset /dm8/backup/BACKUP_FILE_01
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:02][Remaining:00:00:00]
backup successfully!
time used: 00:00:03.600

[dmdba@KylinV10SP2-DM01 dm8]$ ll backup/
drwxr-xr-x 2 dmdba dinstall 59 10月 22 16:54 BACKUP_FILE_01

[dmdba@KylinV10SP2-DM01 backup]$ cd BACKUP_FILE_01/
[dmdba@KylinV10SP2-DM01 BACKUP_FILE_01]$ ll
-rw-r--r-- 1 dmdba dinstall 10320384 10月 22 16:54 BACKUP_FILE_01.bak
-rw-r--r-- 1 dmdba dinstall 99840 10月 22 16:54 BACKUP_FILE_01.meta

-通过scp ，将主库备份集拷贝到备库
scp . dmdba@192.168.1.146:/dm8/backup
[dmdba@KylinV10SP2-DM01 BACKUP_FILE_01]$ scp . dmdba@192.168.1.146:/dm8/backup
The authenticity of host '192.168.1.146 (192.168.1.146)' can't be established.
ECDSA key fingerprint is SHA256:lHkOjiU7tVV7LPpVZCRhD7rhn/S5GHHB/LSaK7uh+Aw.
Are you sure you want to continue connecting (yes/no/[fingerprint])? y
Please type 'yes', 'no' or the fingerprint: yes
Warning: Permanently added '192.168.1.146' (ECDSA) to the list of known hosts.
dmdba@192.168.1.146's password:
BACKUP_FILE_01.bak 100% 10MB 119.8MB/s 00:00
BACKUP_FILE_01.meta 100% 98KB 31.7MB/s 00:00

-在备库还原
[dmdba@localhost backup]$ ll
-rw-r--r-- 1 dmdba dinstall 10320384 10月 22 17:04 BACKUP_FILE_01.bak
-rw-r--r-- 1 dmdba dinstall 99840 10月 22 17:04 BACKUP_FILE_01.meta
三部曲
第一步：还原
dmrman CTLSTMT="RESTORE DATABASE '/dm8/DAMENG/dm.ini' FROM BACKUPSET '/dm8/backup'"
[dmdba@localhost backup]$ dmrman CTLSTMT="RESTORE DATABASE '/dm8/DAMENG/dm.ini' FROM BACKUPSET '/dm8/backup'"
dmrman V8
RESTORE DATABASE '/dm8/DAMENG/dm.ini' FROM BACKUPSET '/dm8/backup'
file dm.key not found, use default license!
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:02][Remaining:00:00:00]
restore successfully.
time used: 00:00:02.474
第二步：恢复
dmrman CTLSTMT="RECOVER DATABASE '/dm8/DAMENG/dm.ini' FROM BACKUPSET '/dm8/backup'"
[dmdba@localhost backup]$ dmrman CTLSTMT="RECOVER DATABASE '/dm8/DAMENG/dm.ini' FROM BACKUPSET '/dm8/backup'"
dmrman V8
RECOVER DATABASE '/dm8/DAMENG/dm.ini' FROM BACKUPSET '/dm8/backup'
file dm.key not found, use default license!
Database mode = 2, oguid = 0
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
EP[0]'s cur_lsn[59160], file_lsn[59160]
备份集[/dm8/backup]备份过程中未产生日志
recover successfully!
time used: 342.654(ms)
第三步:
dmrman CTLSTMT="RECOVER DATABASE '/dm8/DAMENG/dm.ini' UPDATE DB_MAGIC"
[dmdba@localhost backup]$ dmrman CTLSTMT="RECOVER DATABASE '/dm8/DAMENG/dm.ini' UPDATE DB_MAGIC"
dmrman V8
RECOVER DATABASE '/dm8/DAMENG/dm.ini' UPDATE DB_MAGIC
file dm.key not found, use default license!
Database mode = 2, oguid = 0
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
EP[0]'s cur_lsn[59160], file_lsn[59160]
recover successfully!
time used: 00:00:01.085

-配置主库
dm.ini

INSTANCE_NAME = DMSERVER01
PORT_NUM = 5236
DW_INACTIVE_INTERVAL = 60 #故障检测间隔
ALTER_MODE_STATUS = 0 #是否允许模式更改该
ENABLE_OFFLINE_TS = 2 #备库不允许表空间离线
MAL_INI = 1#打开mal
ARCH_INI = 1#打开归档
RLOG_SEND_APPLY_MON = 64#统计最近64次日志发送日志

dmmal.ini
MAL_CHECK_INTERVAL = 60 #链路检测时间间隔60秒
MAL_CONN_FAIL_INTERVAL = 60 #判定链路断开时间，网络不好可以调大点
[MAL_INST1]
MAL_INST_NAME = DAMENG01 #实例名
MAL_HOST = 192.168.1.144 #主库IP
MAL_PORT = 55101
MAL_INST_HOST = 192.168.1.144
MAL_INST_PORT = 5236
MAL_DW_PORT = 65101 #守护进程监听进程
MAL_INST_DW_PORT = 45101
[MAL_INST2]
MAL_INST_NAME = DAMENG02
MAL_HOST = 192.168.1.146
MAL_PORT = 55121
MAL_INST_HOST = 192.168.1.146
MAL_INST_PORT = 5236
MAL_DW_PORT = 65121
MAL_INST_DW_PORT = 45121

dmarch.ini
[ARCHIVE_REALTIME] #实时归档
ARCH_TYPE = REALTIME
ARCH_DEST = DMSERVER02 #主库写备库，备库写主库
[ARCHIVE_LOCAL1] #本地归档
ARCH_TYPE = LOCAL
ARCH_DEST = /dm8/arch
ARCH_FILE_SIZE = 128
ARCH_SPACE_LIMIT = 0 #0表示不限制

dmwatcher.ini #守护进程配置文件
[GRP1] #主的名称
DW_TYPE = GLOBAL#守护进程类型
DW_MODE = MANUAL#手动切换
DW_ERROR_TIME = 60
INST_RECOVER_TIME = 60 #守护进程启动恢复的间隔时间
INST_ERROR_TIME = 60 #数据库故障时间
INST_OGUID = 453331 #所有OGUID都必须设置成一样
INST_INI = /dm8/DAMENG/dm.ini
INST_AUTO_RESTART = 1
INST_STARTUP_CMD = /home/dmdba/dmdbms/bin/dmserver
RLOG_SEND_THRESHOLD = 0
RLOG_APPLY_THRESHOLD = 0

启动主库
方法：dmserver /dm8/data/DMDW/dm.ini mount
[dmdba@KylinV10SP2-DM01 DAMENG]$ dmserver /dm8/DAMENG/dm.ini mount
file dm.key not found, use default license!
version info: develop
DM Database Server 64 V8 03134283968-20230103-178822-20033 startup...
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
Database mode = 0, oguid = 0
License will expire on 2024-01-03
file lsn: 59160
ndct db load finished
ndct second level fill fast pool finished
ndct third level fill fast pool finished
ndct second level fill fast pool finished
ndct third level fill fast pool finished
ndct fill fast pool finished
nsvr_startup end.
aud sys init success.
aud rt sys init success.
systables desc init success.
ndct_db_load_info success.
SYSTEM IS READY.

-登录数据库修改配置
[dmdba@KylinV10SP2-DM01 ~]$ disql sysdba/damengdba
服务器[LOCALHOST:5236]:处于普通配置状态
登录使用时间 : 3.829(ms)
disql V8
SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);
SQL>sp_set_oguid(453331);
SQL>alter database primary;#设置为主库
操作已执行
已用时间: 4.590(毫秒). 执行号:0.
SQL> SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0);
DMSQL 过程已成功完成
已用时间: 10.572(毫秒). 执行号:1200.
SQL>exit;
-查看已经变为主库配置状态
[dmdba@KylinV10SP2-DM01 ~]$ disql sysdba/damengdba
服务器[LOCALHOST:5236]:处于主库配置状态
登录使用时间 : 6.081(ms)
disql V8

-配置备库
dm.ini

INSTANCE_NAME = DMSERVER02
PORT_NUM = 5236
DW_INACTIVE_INTERVAL = 60 #故障检测间隔
ALTER_MODE_STATUS = 0 #是否允许模式更改该
ENABLE_OFFLINE_TS = 2 #备库不允许表空间离线
MAL_INI = 1#打开mal
ARCH_INI = 1#打开归档
RLOG_SEND_APPLY_MON = 64#统计最近64次日志发送日志

dmmal.ini

MAL_CHECK_INTERVAL = 60
MAL_CONN_FAIL_INTERVAL = 60
[MAL_INST1]
MAL_INST_NAME =DMSERVER01
MAL_HOST = 192.168.1.144
MAL_PORT = 55101
MAL_INST_HOST = 192.168.1.144
MAL_INST_PORT = 5236
MAL_DW_PORT = 65101
MAL_INST_DW_PORT = 45101
[MAL_INST2]
MAL_INST_NAME = DMSERVER02
MAL_HOST = 192.168.1.146
MAL_PORT = 55121
MAL_INST_HOST = 192.168.1.146
MAL_INST_PORT = 5236
MAL_DW_PORT = 65121
MAL_INST_DW_PORT = 45121

dmarch.ini

[ARCHIVE_REALTIME]
ARCH_TYPE = REALTIME
ARCH_DEST = DMSERVER01
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /dm8/arch
ARCH_FILE_SIZE = 128
ARCH_SPACE_LIMIT = 0

dmwatcher.ini

[GRP1]
DW_TYPE = GLOBAL
DW_MODE = MANUAL
DW_ERROR_TIME = 60
INST_RECOVER_TIME = 60
INST_ERROR_TIME = 60
INST_OGUID = 453331
INST_INI = /dm8/DAMENG/dm.ini
INST_AUTO_RESTART = 1
INST_STARTUP_CMD = /home/dmdba/dmdbms/bin/dmserver
RLOG_SEND_THRESHOLD = 0
RLOG_APPLY_THRESHOLD = 0

-启动备库
dmserver /dm8/DAMENG/dm.ini mount #mount状态启动
[dmdba@localhost DAMENG]$ dmserver /dm8/DAMENG/dm.ini mount
file dm.key not found, use default license!
version info: develop
DM Database Server 64 V8 03134283968-20230103-178822-20033 startup...
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
Database mode = 0, oguid = 0
License will expire on 2024-01-03
file lsn: 59160
ndct db load finished
ndct second level fill fast pool finished
ndct third level fill fast pool finished
ndct second level fill fast pool finished
ndct third level fill fast pool finished
ndct fill fast pool finished
nsvr_startup end.
aud sys init success.
aud rt sys init success.
systables desc init success.
ndct_db_load_info success.
SYSTEM IS READY.

-登录备库，修改配置
[dmdba@localhost ~]$ disql sysdba/damengdba
服务器[LOCALHOST:5236]:处于普通配置状态
登录使用时间 : 13.648(ms)
disql V8
SQL>
SQL> SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);
DMSQL 过程已成功完成
已用时间: 42.480(毫秒). 执行号:500.
SQL> sp_set_oguid(453331);
DMSQL 过程已成功完成
已用时间: 6.729(毫秒). 执行号:501.
SQL> alter database standby;
操作已执行
已用时间: 5.217(毫秒). 执行号:0.
SQL> SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0);
DMSQL 过程已成功完成
已用时间: 4.857(毫秒). 执行号:502.
SQL> quit
[dmdba@localhost ~]$ disql sysdba/damengdba
服务器[LOCALHOST:5236]:处于备库配置状态
登录使用时间 : 4.144(ms)
disql V8
SQL>
————————
SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);
SQL>sp_set_oguid(453331);
SQL>alter database standby;#修改为备库
SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0);

-启动主库守护进程
[dmdba@KylinV10SP2-DM01 ~]$ dmwatcher /dm8/DAMENG/dmwatcher.ini
DMWATCHER[4.0] V8
DMWATCHER[4.0] IS READY
-启动备库守护进程
[dmdba@localhost ~]$ dmwatcher /dm8/DAMENG/dmwatcher.ini
DMWATCHER[4.0] V8
DMWATCHER[4.0] IS READY

-登录主库
[dmdba@KylinV10SP2-DM01 ~]$ disql sysdba/damengdba
服务器[LOCALHOST:5236]:处于主库打开状态
登录使用时间 : 4.643(ms)
disql V8
SQL>

-登录备库
[dmdba@localhost ~]$ disql sysdba/damengdba
服务器[LOCALHOST:5236]:处于备库打开状态
登录使用时间 : 18.591(ms)
disql V8
SQL>

-测试验证
主库创建表
[dmdba@KylinV10SP2-DM01 ~]$ disql sysdba/damengdba
服务器[LOCALHOST:5236]:处于主库打开状态
登录使用时间 : 4.643(ms)
disql V8
SQL> create table t1(id int);
操作已执行
已用时间: 13.891(毫秒). 执行号:2500.
SQL> insert into t1 values(1);
影响行数 1
已用时间: 0.613(毫秒). 执行号:2502.

备库查表
[dmdba@localhost ~]$ disql sysdba/damengdba
服务器[LOCALHOST:5236]:处于备库打开状态
登录使用时间 : 18.591(ms)
disql V8
SQL> select * from t1;
未选定行
已用时间: 24.705(毫秒). 执行号:4500.
SQL> select * from t1;
行号 ID

---

1 1
已用时间: 0.709(毫秒). 执行号:4501.

-配置监视器：192.168.201
[dmdba@KylinV10SP2-DM8 dm8]$ vi dmmonitor.ini
dmmonitor.ini
MON_DW_CONFIRM = 1 #1为确认模式，0为监控模式
MON_LOG_PATH = /dm8/log
MON_LOG_INTERVAL = 60
MON_LOG_FILE_SIZE = 32
MON_LOG_SPACE_LIMIT = 0
[GRP1]
MON_INST_OGUID = 453331
MON_DW_IP = 192.168.1.144:65101
MON_DW_IP = 192.168.1.146:65121

-启动监视器
[dmdba@KylinV10SP2-DM8 dm8]$ dmmonitor /dm8/dmmonitor.in
[monitor] 2023-10-22 11:59:15: DMMONITOR[4.0] V8
[monitor] 2023-10-22 11:59:16: DMMONITOR[4.0] IS READY.

[monitor] 2023-10-22 11:59:16: 收到守护进程(DMSERVER01)消息
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2023-10-22 20:12:33 OPEN OK DMSERVER01 OPEN PRIMARY VALID 6 59778 59778

[monitor] 2023-10-22 11:59:16:
#--------------------------------------------------------------------------------#
GET MONITOR CONNECT INFO FROM DMWATCHER(DMSERVER01), THE FIRST LINE IS SELF INFO.

DW_CONN_TIME MON_CONFIRM MID MON_IP MON_VERSION
2023-10-22 20:12:33 TRUE 77767380 ::ffff:192.168.1.201 DMMONITOR[4.0] V8

#--------------------------------------------------------------------------------#

[monitor] 2023-10-22 11:59:16: 收到守护进程(DMSERVER02)消息
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2023-10-22 20:12:33 OPEN OK DMSERVER02 OPEN STANDBY VALID 6 59777 59777

-show查看
输入命令非法，请输入help命令查看帮助信息！

show
2023-10-22 12:00:10
#================================================================================#
GROUP OGUID MON_CONFIRM MODE MPP_FLAG
GRP1 453331 TRUE MANUAL FALSE

<<DATABASE GLOBAL INFO:>>
DW_IP MAL_DW_PORT WTIME WTYPE WCTLSTAT WSTATUS INAME INST_OK N_EP N_OK ISTATUS IMODE DSC_STATUS RTYPE RSTAT
192.168.1.144 65101 2023-10-22 20:13:27 GLOBAL VALID OPEN DMSERVER01 OK 1 1 OPEN PRIMARY DSC_OPEN REALTIME VALID

EP INFO:
INST_IP INST_PORT INST_OK INAME ISTATUS IMODE DSC_SEQNO DSC_CTL_NODE RTYPE RSTAT FSEQ FLSN CSEQ CLSN DW_STAT_FLAG
192.168.1.144 5236 OK DMSERVER01 OPEN PRIMARY 0 0 REALTIME VALID 15970 59795 15971 59796 NONE

<<DATABASE GLOBAL INFO:>>
DW_IP MAL_DW_PORT WTIME WTYPE WCTLSTAT WSTATUS INAME INST_OK N_EP N_OK ISTATUS IMODE DSC_STATUS RTYPE RSTAT
192.168.1.146 65121 2023-10-22 20:13:27 GLOBAL VALID OPEN DMSERVER02 OK 1 1 OPEN STANDBY DSC_OPEN REALTIME VALID

EP INFO:
INST_IP INST_PORT INST_OK INAME ISTATUS IMODE DSC_SEQNO DSC_CTL_NODE RTYPE RSTAT FSEQ FLSN CSEQ CLSN DW_STAT_FLAG
192.168.1.146 5236 OK DMSERVER02 OPEN STANDBY 0 0 REALTIME VALID 15549 59795 15549 59795 NONE

DATABASE(DMSERVER02) APPLY INFO FROM (DMSERVER01), REDOS_PARALLEL_NUM (1), WAIT_APPLY[FALSE]:
DSC_SEQNO[0], (RSEQ, SSEQ, KSEQ)[15970, 15970, 15971], (RLSN, SLSN, KLSN)[59795, 59795, 59796], N_TSK[0], TSK_MEM_USE[512]
REDO_LSN_ARR: (59795)

-切换
登录监视器
输入命令非法，请输入help命令查看帮助信息！
login
用户名:sysdba //数据库账号
密码:damengdba //数据库密码
[monitor] 2023-10-22 12:03:31: 登录监视器成功!

切换主备
swichover
[monitor] 2023-10-22 12:05:11: 开始切换实例DMSERVER02
[monitor] 2023-10-22 12:05:11: 通知守护进程DMSERVER01切换SWITCHOVER状态
[monitor] 2023-10-22 12:05:11: 守护进程(DMSERVER01)状态切换 [OPEN-->SWITCHOVER]
[monitor] 2023-10-22 12:05:12: 切换守护进程DMSERVER01为SWITCHOVER状态成功
[monitor] 2023-10-22 12:05:12: 通知守护进程DMSERVER02切换SWITCHOVER状态
[monitor] 2023-10-22 12:05:13: 守护进程(DMSERVER02)状态切换 [OPEN-->SWITCHOVER]
[monitor] 2023-10-22 12:05:13: 切换守护进程DMSERVER02为SWITCHOVER状态成功
[monitor] 2023-10-22 12:05:13: 实例DMSERVER01开始执行SP_SET_GLOBAL_DW_STATUS(0, 6)语句
[monitor] 2023-10-22 12:05:14: 实例DMSERVER01执行SP_SET_GLOBAL_DW_STATUS(0, 6)语句成功
[monitor] 2023-10-22 12:05:14: 实例DMSERVER02开始执行SP_SET_GLOBAL_DW_STATUS(0, 6)语句
[monitor] 2023-10-22 12:05:14: 实例DMSERVER02执行SP_SET_GLOBAL_DW_STATUS(0, 6)语句成功
[monitor] 2023-10-22 12:05:14: 实例DMSERVER01开始执行ALTER DATABASE MOUNT语句
[monitor] 2023-10-22 12:05:14: 实例DMSERVER01执行ALTER DATABASE MOUNT语句成功
[monitor] 2023-10-22 12:05:14: 实例DMSERVER02开始执行SP_APPLY_KEEP_PKG()语句
[monitor] 2023-10-22 12:05:15: 实例DMSERVER02执行SP_APPLY_KEEP_PKG()语句成功
[monitor] 2023-10-22 12:05:15: 实例DMSERVER02开始执行ALTER DATABASE MOUNT语句
[monitor] 2023-10-22 12:05:15: 实例DMSERVER02执行ALTER DATABASE MOUNT语句成功
[monitor] 2023-10-22 12:05:15: 实例DMSERVER01开始执行ALTER DATABASE STANDBY语句
[monitor] 2023-10-22 12:05:16: 实例DMSERVER01执行ALTER DATABASE STANDBY语句成功
[monitor] 2023-10-22 12:05:16: 实例DMSERVER02开始执行ALTER DATABASE PRIMARY语句
[monitor] 2023-10-22 12:05:17: 实例DMSERVER02执行ALTER DATABASE PRIMARY语句成功
[monitor] 2023-10-22 12:05:17: 通知实例DMSERVER02修改所有归档状态无效
[monitor] 2023-10-22 12:05:17: 修改所有实例归档为无效状态成功
[monitor] 2023-10-22 12:05:17: 实例DMSERVER01开始执行ALTER DATABASE OPEN FORCE语句
[monitor] 2023-10-22 12:05:17: 实例DMSERVER01执行ALTER DATABASE OPEN FORCE语句成功
[monitor] 2023-10-22 12:05:17: 实例DMSERVER02开始执行ALTER DATABASE OPEN FORCE语句
[monitor] 2023-10-22 12:05:19: 实例DMSERVER02执行ALTER DATABASE OPEN FORCE语句成功
[monitor] 2023-10-22 12:05:19: 实例DMSERVER01开始执行SP_SET_GLOBAL_DW_STATUS(6, 0)语句
[monitor] 2023-10-22 12:05:19: 实例DMSERVER01执行SP_SET_GLOBAL_DW_STATUS(6, 0)语句成功
[monitor] 2023-10-22 12:05:19: 实例DMSERVER02开始执行SP_SET_GLOBAL_DW_STATUS(6, 0)语句
[monitor] 2023-10-22 12:05:19: 实例DMSERVER02执行SP_SET_GLOBAL_DW_STATUS(6, 0)语句成功
[monitor] 2023-10-22 12:05:19: 通知守护进程DMSERVER01切换OPEN状态
[monitor] 2023-10-22 12:05:19: 守护进程(DMSERVER01)状态切换 [SWITCHOVER-->OPEN]
[monitor] 2023-10-22 12:05:20: 切换守护进程DMSERVER01为OPEN状态成功
[monitor] 2023-10-22 12:05:20: 通知守护进程DMSERVER02切换OPEN状态
[monitor] 2023-10-22 12:05:20: 守护进程(DMSERVER02)状态切换 [SWITCHOVER-->OPEN]
[monitor] 2023-10-22 12:05:21: 切换守护进程DMSERVER02为OPEN状态成功
[monitor] 2023-10-22 12:05:21: 通知组(GRP1)的守护进程执行清理操作
[monitor] 2023-10-22 12:05:21: 清理守护进程(DMSERVER01)请求成功
[monitor] 2023-10-22 12:05:21: 清理守护进程(DMSERVER02)请求成功
[monitor] 2023-10-22 12:05:21: 实例DMSERVER02切换成功
2023-10-22 12:05:21

-先退出监视器
输入命令非法，请输入help命令查看帮助信息！
exit
[dmdba@KylinV10SP2-DM8 dm8]$

-再退出备机守护进程
[dmdba@localhost ~]$ dmwatcher /dm8/DAMENG/dmwatcher.ini
DMWATCHER[4.0] V8
DMWATCHER[4.0] IS READY
exit
[dmdba@localhost ~]$
-最后退出主机守护进程
[dmdba@KylinV10SP2-DM01 ~]$ dmwatcher /dm8/DAMENG/dmwatcher.ini
DMWATCHER[4.0] V8
DMWATCHER[4.0] IS READY
exit
[dmdba@KylinV10SP2-DM01 ~]$

-关闭备用数据库
checkpoint end, 0 pages flushed, used_space[198144], free_space[536664576].
exit
Server is stopping...
listener closed and all sessions disconnected
adjust undo_retention & wakeup purge thread...full check point starting...
...
shutdown huge buffer and memory pools...OK
close lsnr socket
DM Database Server shutdown successfully.
-关闭主数据库
checkpoint end, 0 pages flushed, used_space[198144], free_space[536664576].
exit
Server is stopping...
listener closed and all sessions disconnected
adjust undo_retention & wakeup purge thread...full check point starting...
...
shutdown huge buffer and memory pools...OK
close lsnr socket
DM Database Server shutdown successfully.
注册服务
./dm_service_installer.sh -h //查看脚本帮助信息
./dm_service_installer.sh -t dmserver -p DMSERVER -m mount -dm_ini /dm8/DAMENG/dm.ini
./dm_service_installer.sh -t dmwatcher -p DMWATCHER -watcher_ini /dm8/DAMENG/dmwatcher.ini
./dm_service_installer.sh -t dmmonitor -p DMMONITOR -monitor_ini /dm8/DAMENG/dmmonitor.ini
实时主备集群关闭有顺序要求：
1、关闭监视器
2、关闭备库守护进程
3、关闭主库守护进程
4、关闭主库实例
5、关闭备库实例

---

*读写分离：
架构
JDBC //DM8程序员手册，查看读写分离的体系架构和原理
-实在主备基础上，再应用程序增加了个驱动程序
使用dem部署
-登录DEM
192.168.1.201:8080/dem
账号：admin
密码：888888

-客户端工具>集群管理>新建集群搭建>集群名称>读写分离>