达梦数据库timesteamp数字达梦数据库8

转载

墨守成规de网工 2024-03-18 23:18:30

文章标签 达梦数据库timesteamp数字服务器运维数据库架构 SQL 文章分类 架构后端开发

一、安装前准备

按照单机数据库安装分别在两台服务器装好数据库

二、数据库软件安装

数据库安装和初始化实例

	主库	备库
业务IP	192.168.124.146	192.168.124.147
端口号	5236	5236
实例名	DW1	DW2
安装目录	/opt/dmdbms
实例目录	/opt/dmdbms/data/

三、集群配置

1、主库

1.1开启归档

[dmdba@~]# /opt/dmdbms/bin/disql SYSDBA/dmsysdba@192.168.124.146:5236
SQL> ALTER DATABASE MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE ADD ARCHIVELOG 'DEST=/opt/dmdbms/data/DAMENG/arch, TYPE=LOCAL, FILE_SIZE=2048, SPACE_LIMIT=51200';
SQL> ALTER DATABASE OPEN;

1.2备份数据(脱机备份)

关闭数据库

cd /opt/dmdbms/bin

./DmServiceDW1_01 stop

拷贝备份文件到备库所在机器

scp -r /opt/dmdbms/data/DAMENG/bak/BACKUP_FILE dmdba@192.168.124.147:/opt/dmdbms/data/bak

脱机备份

cd /opt/dmdbms/bin

./dmrman CTLSTMT="BACKUP DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' FULL TO BACKUP_FILE1 BACKUPSET '/opt/dmdbms/data/DAMENG/bak/BACKUP_FILE'"

1.3修改dm.ini参数

SQL> SP_SET_PARA_VALUE (2,'PORT_NUM',5236);
SQL> SP_SET_PARA_VALUE (2,'DW_INACTIVE_INTERVAL',60);
SQL> SP_SET_PARA_VALUE (2,'ALTER_MODE_STATUS',0);
SQL> SP_SET_PARA_VALUE (2,'ENABLE_OFFLINE_TS',2);
SQL> SP_SET_PARA_VALUE (2,'MAL_INI',1);
SQL> SP_SET_PARA_VALUE (2,'RLOG_SEND_APPLY_MON',64);

1.4配置 dm.ini

[dmdba@~]# vi /opt/dmdbms/data/DAMENG/dm.ini

INSTANCE_NAME = DW1_01 #数据库实例名

1.5配置 dmarch.ini

[dmdba@~]# vi /opt/dmdbms/data/DAMENG/dmarch.ini
[ARCHIVE_REALTIME]
  ARCH_TYPE                = REALTIME  #实时归档类型
  ARCH_DEST                = DW1_01B  #实时归档目标实例名
[ARCHIVE_LOCAL1]
  ARCH_TYPE                = LOCAL  #本地归档类型
  ARCH_DEST                = /opt/dmdbms/data/DAMENG/arch/  #本地归档存放路径
  ARCH_FILE_SIZE           = 2048  #单位MB，本地单个归档最大值
  ARCH_SPACE_LIMIT         = 51200  #单位MB，0表示无限制

1.6配置 dmmal.ini

[dmdba@~]# vi /opt/dmdbms/data/DAMENG/dmmal.ini
MAL_CHECK_INTERVAL         = 30  #MAL链路检测时间间隔
MAL_CONN_FAIL_INTERVAL     = 60  #判定MAL链路断开的时间
MAL_TEMP_PATH              = /opt/dmdbms/data/malpath/  #临时文件目录
MAL_BUF_SIZE               = 512  #单个MAL缓存大小限制，单位MB
MAL_SYS_BUF_SIZE           = 2048  #MAL系统总内存大小限制，单位MB
MAL_COMPRESS_LEVEL         = 0  #MAL消息压缩等级，0表示不压缩
[MAL_INST1]
  MAL_INST_NAME            = DW1_01  #实例名，和 dm.ini的INSTANCE_NAME一致
  MAL_HOST                 = 192.168.124.146  #MAL系统监听TCP连接的IP地址
  MAL_PORT                 = 5336  #MAL系统监听TCP连接的端口
  MAL_INST_HOST            = 192.168.124.146  #实例的对外服务IP地址
  MAL_INST_PORT            = 5236  #实例对外服务端口，和dm.ini的PORT_NUM一致
  MAL_DW_PORT              = 5436  #实例对应的守护进程监听TCP连接的端口
  MAL_INST_DW_PORT         = 5536  #实例监听守护进程TCP连接的端口
[MAL_INST2]
  MAL_INST_NAME            = DW1_01B
  MAL_HOST                 = 192.168.124.147
  MAL_PORT                 = 5336
  MAL_INST_HOST            = 172.168.124.147
  MAL_INST_PORT            = 5236
  MAL_DW_PORT              = 5436
  MAL_INST_DW_PORT         = 5536

1.7配置 dmwatcher.ini

[dmdba@~]# vi /opt/dmdbms/data/DAMENG/dmwatcher.ini
[GDW1]
  DW_TYPE                  = GLOBAL  #全局守护类型
  DW_MODE                  = MANUAL  #故障手动切换模式
  DW_ERROR_TIME            = 30  #远程守护进程故障认定时间
  INST_ERROR_TIME          = 30  #本地实例故障认定时间
  INST_RECOVER_TIME        = 60  #主库守护进程启动恢复的间隔时间
  INST_OGUID               = 453331  #守护系统唯一OGUID值
  INST_INI                 = /opt/dmdbms/data/DAMENG/dm.ini  #dm.ini文件路径
  INST_AUTO_RESTART        = 1  #打开实例的自动启动功能
  INST_STARTUP_CMD         = /opt/dmdbms/bin/dmserver  #命令行方式启动
  RLOG_SEND_THRESHOLD      = 0  #指定主库发送日志到备库的时间阀值，默认关闭
  RLOG_APPLY_THRESHOLD     = 0  #指定备库重演日志的时间阀值，默认关闭

1.8拷贝实例

[dmdba@~]# scp -r /opt/dmdbms/data/DAMENG dmdba@192.168.124.147:/opt/dmdbms/data/

1.9注册服务

[root@~]# /opt/dmdbms/script/root/dm_service_installer.sh -t dmserver -p DW1_01 -dm_ini /opt/dmdbms/data/DAMENG/dm.ini -m mount

[root@~]# /opt/dmdbms/script/root/dm_service_installer.sh -t dmwatcher -p Watcher -watcher_ini /opt/dmdbms/data/DAMENG/dmwatcher.ini

备注：删除自启

[root@~]# /opt/dmdbms/script/root/dm_service_uninstaller.sh -n DmServiceDW1_01

[root@~]# /opt/dmdbms/script/root/dm_service_uninstaller.sh -n DmWatcherServiceWatcher

2、备库

2.1配置 dm.ini

[dmdba@~]# vi /opt/dmdbms/data/DAMENG/dm.ini

INSTANCE_NAME = DW1_01B #数据库实例名

2.2配置 dmarch.ini

[dmdba@~]# vi /opt/dmdbms/data/DAMENG/dmarch.ini
[ARCHIVE_REALTIME]
  ARCH_TYPE                = REALTIME  #实时归档类型
  ARCH_DEST                = DW1_01  #实时归档目标实例名
[ARCHIVE_LOCAL1]
  ARCH_TYPE                = LOCAL  #本地归档类型
  ARCH_DEST                = /opt/dmdbms/data/DAMENG/arch/  #本地归档存放路径
  ARCH_FILE_SIZE           = 2048  #单位MB，本地单个归档最大值
  ARCH_SPACE_LIMIT         = 51200  #单位MB，0表示无限制

2.3相同配置项

与A机器DW1_01的dmmal.ini、dmwatcher.ini相同

2.4注册服务

[root@~]# /opt/dmdbms/script/root/dm_service_installer.sh -t dmserver -p DW1_01B -dm_ini /opt/dmdbms/data/DAMENG/dm.ini -m mount

[root@~]# /opt/dmdbms/script/root/dm_service_installer.sh -t dmwatcher -p Watcher -watcher_ini /opt/dmdbms/data/DAMENG/dmwatcher.ini

关闭数据库

cd /opt/dmdbms/bin

./DmServiceDW1_01 stop

2.5恢复数据

[dmdba@~]# /opt/dmdbms/bin/dmrman CTLSTMT="RESTORE DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' FROM BACKUPSET '/opt/dmdbms/data/DAMENG/bak/BACKUP_FILE'"

[dmdba@~]# /opt/dmdbms/bin/dmrman CTLSTMT="RECOVER DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' FROM BACKUPSET '/opt/dmdbms/data/DAMENG/bak/BACKUP_FILE'"

[dmdba@~]# /opt/dmdbms/bin/dmrman CTLSTMT="RECOVER DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' UPDATE DB_MAGIC"

主库

以Mount方式启动主库

./dmserver /opt/dmdbms/data/DAMENG/dm.ini mount

设置OGUID

启动命令行工具DIsql，登录主库设置OGUID值。

达梦数据库timesteamp数字达梦数据库8_数据库架构

SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);

SQL>sp_set_oguid(453331);

SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0);

修改数据库模式

SQL>alter database primary;

备库

以Mount方式启动主库

./dmserver /opt/dmdbms/data/DAMENG/dm.ini mount

修改数据库模式

SQL>alter database standby;

3. 配置监视器（主备都要配置）

[dmdba@~]# vi /opt/dmdbms/bin/dmmonitor.ini
MON_DW_CONFIRM             = 1  #0为非确认，1为确认
MON_LOG_PATH               = ../log  #监视器日志文件存放路径
MON_LOG_INTERVAL           = 60  #每隔 60s 定时记录系统信息到日志文件
MON_LOG_FILE_SIZE          = 512  #单位MB，范围1~2048MB
MON_LOG_SPACE_LIMIT        = 2048  #日志文件总占用2048MB
[GDW1]
  MON_INST_OGUID           = 453331  #组GDW1的唯一OGUID 值
  MON_DW_IP                = 192.168.124.146:5436  #IP对应MAL_HOST，PORT对应MAL_DW_PORT
  MON_DW_IP                = 192.168.124.147:5436