达梦timestamp和datetime

文章目录

• 安装前信息

• 查看信息
• 安装前设置
• 关闭防火墙
• 查看时区

• 系统设置

• 创建用户
• 路径规划
• 磁盘测速(示例是1.5MB/s)
• 修改参数

• 修改资源限制参数

• 查看安装包的完整性
• 安装包的准备
• 用dmdba用户执行安装
• 检查dmap进程是否启动，没启动要手动启动
• 初始化实例
• 注册服务
• 系统用户的命令不齐问题
• 添加变量
• 启动服务
• 登陆数据库，可以根据实际情况修改密码
• 开归档:路径要有权限，根据实际情况修改路径
• 优化参数
• 慢SQL日志
• 刷新慢sql日志配置文件，以下查询的名字要与上面配置的一至
• 慢日志产生的路径

• 查看版本

• 重启一下服务

• 生产环境一定要做备份

• 备份创建
• 备份报错：
• 收集统计信息（不建议）
• 备份集的检验
• 作业检查

安装前信息

查看信息

--CPU型号
[root@dmdb116 ~]# cat /proc/cpuinfo | grep name | sort | uniq
model name      : Intel(R) Xeon(R) CPU E5-2696 v2 @ 2.50GHz
--物理CPU个数
[root@dmdb116 ~]# cat /proc/cpuinfo | grep "physical id" | sort | uniq | wc -l
1
--核心数
[root@dmdb116 ~]# cat /proc/cpuinfo | grep "core id" | sort | uniq | wc -l
2
[root@dmdb116 ~]# cat /proc/cpuinfo | grep "processor" | sort | uniq | wc -l
2
--网速
[dmdba@dmdb111 bin]$ ethtool ens32
        Speed: 1000Mb/s

安装前设置

##关闭Selinux
# tail -2 /etc/selinux/config 
SELINUX=disabled
SELINUXTYPE=targeted
##生效
setenforce 0

关闭防火墙

– linux7 防火墙

# systemctl stop firewalld.service 
# systemctl disable firewalld.service

• linux6 防火墙

##临时关闭
service iptables stop
service ip6tables stop
##开机时也禁用
chkconfig iptables off
chkconfig ip6tables off
##刷新防火墙
chkconfig --level 35 iptables off

查看时区

##时区及NTP设置
[root@dmdb116 ~]# timedatectl | grep "Time zone"
Time zone: Asia/Shanghai (CST, +0800)
# timedatectl set-timezone Asia/Shanghai

系统设置

创建用户

# groupadd dinstall -g 2001
# useradd  -G dinstall -m -d /home/dmdba -s /bin/bash -u 2001 dmdba 
# passwd dmdba
dmdba

路径规划

mkdir /dmdata
chown -R dmdba:dinstall /dmdata
chmod -R 775 /dmdata

磁盘测速(示例是1.5MB/s)

···
cd /dmdata
dd if=/dev/zero of=test bs=8k count=64k oflag=dsync
···

修改参数

# sysctl -p
# vi /etc/sysctl.conf
kernel.shmall = 4294967296
fs.file-max = 6815744
fs.aio-max-nr = 1048576
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
vm.dirty_ratio = 80
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 100
##kernel.core_pattern = /dmdata/core.%p
vm.swappiness=10
vm.dirty_background_ratio = 0
vm.min_free_kbytes = 2097152

修改资源限制参数

# ulimit -a
# vim /etc/security/limits.conf
dmdba  soft      nice       0
dmdba  hard      nice       0
dmdba  soft      as         unlimited
dmdba  hard      as         unlimited
dmdba  soft      fsize      unlimited
dmdba  hard      fsize      unlimited
dmdba  soft      nproc      65536
dmdba  hard      nproc      65536
dmdba  soft      nofile     65536
dmdba  hard      nofile     65536
dmdba  soft      core       unlimited
dmdba  hard      core       unlimited
dmdba  soft      data       unlimited
dmdba  hard      data       unlimited
dmdba  soft      stack      65536
dmdba  hard      stack      65536

查看安装包的完整性

sha256sum dm8_20220314_FTarm_kylin10_sp1_64.iso

安装包的准备

# mkdir -p /dmiso
# mount -o loop dm8_20200930_x86_rh6_64_ent_8.1.1.134.iso /dmiso
# cp /dmiso/DMInstall.bin /home/dmdba
# chown -R dmdba:dinstall /home/dmdba/DMInstall.bin
# chmod -R 775 /home/dmdba/DMInstall.bin

用dmdba用户执行安装

$ ./DMInstall.bin -i
# /home/dmdba/dmdbms/script/root/root_installer.sh

检查dmap进程是否启动，没启动要手动启动

ps -ef | grep dmap

初始化实例

$ cd /home/dmdba/dmdbms/bin
--oracle
$ ./dminit path=/dmdata page_size=32 extent_size=32 case_sensitive=1 charset=1 log_size=1024 db_name=dmdb instance_name=dmdb BLANK_PAD_MODE=1 LENGTH_IN_CHAR=0
--mysql
$ ./dminit path=/dmdata page_size=32 extent_size=32 case_sensitive=0 charset=1 log_size=1024 db_name=dmdb instance_name=dmdb  LENGTH_IN_CHAR=1

注册服务

# cd /home/dmdba/dmdbms/script/root/
# ./dm_service_installer.sh -t dmserver -p dmdb -dm_ini /dmdata/dmdb/dm.ini

数据库服务启动脚本里面，都加这个：export MALLOC_ARENA_MAX=1
94版本之后的都先搜下，是否存在了，但是它的值是4

系统用户的命令不齐问题

usermod -s /bin/bash name
[root@dl2 home]$ cp /etc/skel/.bash_logout  /home/hx/  
[root@dl2 home]$ cp /etc/skel/.bash_profile /home/hx
[root@dl2 home]$ cp /etc/skel/.bashrc  /home/hx

添加变量

$ vim ~/.bash_profile 
##DM8
export DM_HOME=/home/dmdba/dmdbms
export PATH=$PATH:$DM_HOME/bin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$DM_HOME/bin
$ source ~/.bash_profile

启动服务

$ DmServicedmdb start
Starting DmServicedmdb:    [ OK ]

登陆数据库，可以根据实际情况修改密码

[dmdba@mdb165 dmdata]$ cd /home/dmdba/dmdbms/bin
[dmdba@mdb165 bin]$ ./disql SYSDBA/SYSDBA
服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 2.572(ms)
disql V8
SQL> alter user SYSDBA identified by "Hn@dameng123";

开归档:路径要有权限，根据实际情况修改路径

alter database mount;
alter database add archivelog 'dest=/dmdata/dmarch,TYPE=local,FILE_SIZE=1024,SPACE_LIMIT=102400';
alter database archivelog;
alter database open;

优化参数

start /home/dmdba/AutoParaAdj2.6.sql
alter system set 'SORT_FLAG'=0 both;
alter system set 'ENABLE_ENCRYPT' = 0 spfile;
alter system set 'MAX_SESSIONS'=5000 spfile;
alter tablespace "MAIN" datafile 'MAIN.DBF' autoextend on maxsize 10240;
alter tablespace "MAIN" datafile "MAIN.DBF" autoextend on next 128 maxsize 10240;
alter tablespace "ROLL" datafile 'ROLL.DBF' autoextend on maxsize 512000;
select * from v$parameter where name = 'ENABLE_ENCRYPT';

• ORACLE兼容参数

select * from v$parameter where name in ('ORDER_BY_NULLS_FLAG','USE_PLN_POOL','CALC_AS_DECIMAL','COMPATIBLE_MODE','NUMBER_MODE');
alter system set 'USE_PLN_POOL'=1 spfile;
alter system set 'CALC_AS_DECIMAL'=1 spfile;
alter system set 'ORDER_BY_NULLS_FLAG'=1 both;
alter system set 'COMPATIBLE_MODE' = 2 spfile;
alter system set 'NUMBER_MODE'=1 spfile;--解决float聚合SUM函数小数太长问题

• MYSQL兼容参数

SP_SET_PARA_VALUE(1,'ORDER_BY_NULLS_FLAG',2);
SP_SET_PARA_VALUE(2,'MY_STRICT_TABLES',1);
sp_set_para_value(2,'COMPATIBLE_MODE',4) ;

慢SQL日志

[dmdba@dmdb116 DAMENG]$ cat sqllog.ini 
BUF_TOTAL_SIZE          = 10240         #SQLs Log Buffer Total Size(K)(1024~1024000)
BUF_SIZE                = 1024          #SQLs Log Buffer Size(K)(50~409600)
BUF_KEEP_CNT            = 6             #SQLs Log buffer keeped count(1~100)
[SLOG_ALL]
    FILE_PATH    = ../log
    PART_STOR    = 1
    SWITCH_MODE  = 2
    SWITCH_LIMIT   = 512
    ASYNC_FLUSH   = 1
    FILE_NUM = 5
    ITEMS    = 0 
    SQL_TRACE_MASK  = 2:3:25
    MIN_EXEC_TIME = 1500
    USER_MODE   = 0 
    USERS =

刷新慢sql日志配置文件，以下查询的名字要与上面配置的一至

alter system set 'SVR_LOG'=1 both;
SP_REFRESH_SVR_LOG_CONFIG();
select VALUE,SYS_VALUE,FILE_VALUE from v$parameter where name in('SVR_LOG_NAME');

慢日志产生的路径

[dmdba@dmdb116 log]$ pwd
/home/dmdba/dmdbms/log
[dmdba@dmdb116 log]$ ls dmsql*
dmsql_DMSERVER_20220715_123031.log  dmsql_DMSERVER_uSYSDBA_20220715_123307.log
dmsql_DMSERVER_20220715_123252.log

查看版本

select id_code;
-- 新的版本用这个查
select * from v$version;
select BUILD_VERSION from v$instance;
select expired_date,max_cpu_num,cluster_type,PROJECT_NAME from v$license;

重启一下服务

$ ./DmServicedmdb restart
Stopping DmServicedmdb:     [ OK ]
Starting DmServicedmdb:     [ OK ]

生产环境一定要做备份

备份创建

##路径：注意权限、要开归档才能做备份
[dmdba@dmdb116 dmdata]$ ll /dmdata/dmbak -d 
drwxrwxr-x. 2 dmdba dmdba 4096 7月  15 12:37 /dmdata/dmbak

##做一次全备，确保全备成功后才能做增备
sp_init_job_sys(1);
call sp_create_job('bakfull',1,0,'',0,0,'',0,'');
call sp_job_config_start('bakfull');
call SP_ADD_JOB_STEP('bakfull', 'bak1', 6, '01020000/dmdata/dmbak', 3, 3, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('bakfull', 'std1', 1, 2, 1, 64, 0, '23:59:00', NULL, sysdate, NULL, '');
call SP_JOB_CONFIG_COMMIT('bakfull');
select id,name from sysjob.sysjobs;
call SP_DBMS_JOB_RUN(1659688280);
[dmdba@dmdb116 dmbak]$ pwd
/dmdata/dmbak
[dmdba@dmdb116 dmbak]$ ls
DB_DAMENG_FULL_2022_07_15_12_45_40

##做增备，确保增备也能生成
call SP_CREATE_JOB('bakincr',1,0,'',0,0,'',0,'');
call SP_JOB_CONFIG_START('bakincr');
call SP_ADD_JOB_STEP('bakincr', 'bak2', 6, '41010000/dmdata/dmbak|/dmdata/dmbak', 3, 3, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('bakincr', 'std2', 1, 2, 1, 63, 0, '23:59:00', NULL, sysdate, NULL, '');
call SP_JOB_CONFIG_COMMIT('bakincr');
select id,name from sysjob.sysjobs;
call SP_DBMS_JOB_RUN(1659688281);
[dmdba@dmdb116 dmbak]$ ls
DB_DAMENG_FULL_2022_07_15_12_45_40  DB_DAMENG_INCREMENT_CUMULATIVE_2022_07_15_12_47_55

##定期删除备份
call SP_CREATE_JOB('delbak',1,0,'',0,0,'',0,'');
call SP_JOB_CONFIG_START('delbak');
--以下两行是一个语句
call SP_ADD_JOB_STEP('delbak', 'bak1', 0, 'sf_bakset_backup_dir_add(''disk'',''/dmdata/dmbak'');
call sp_db_bakset_remove_batch(''disk'',now()-14);', 3, 3, 0, 0, NULL, 0);

call SP_ADD_JOB_SCHEDULE('delbak', 'del01', 1, 1, 1, 0, 0, '23:59:00', NULL, sysdate, NULL, '');
call SP_JOB_CONFIG_COMMIT('delbak');

备份报错：

-8003缺少本地或者远程归档：开归档
-718收集到的归档日志不连续
[dmdba@dmdb116 DAMENG]$ DmServicedmdb stop
Stopping DmServicedmdb:        [ OK ]
[dmdba@dmdb116 DAMENG]$ dmrman
RMAN> repair archivelog database '/dmdata/dmdb/dm.ini' ;

收集统计信息（不建议）

call SP_CREATE_JOB('statjob',1,0,'',0,0,'',0,'');
call SP_JOB_CONFIG_START('statjob');
call SP_ADD_JOB_STEP('statjob', 'statjob', 3, '', 3, 3, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('statjob', 'test', 1, 2, 1, 64, 0, '23:59:00', NULL,sysdate, NULL, '');
call SP_JOB_CONFIG_COMMIT('statjob');

备份集的检验

RMAN> CHECK BACKUPSET '/dmdata/dmbak/bakfull' DATABASE '/dmdata/dmdb/dm.ini';
SQL> SELECT SF_BAKSET_CHECK('DISK','/dmdata/dmbak/bakfull');

作业检查

SELECT * FROM (
SELECT EXEC_ID, NAME,STEPNAME,START_TIME,END_TIME,ERRTYPE,ERRCODE,ERRINFO
FROM (SELECT EXEC_ID, NAME,STEPNAME,START_TIME,END_TIME,ERRTYPE,ERRCODE,ERRINFO,
ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY EXEC_ID DESC) RN 
FROM SYSJOB.SYSSTEPHISTORIES2) WHERE RN<=10);

select * from SYSJOB.SYSSTEPHISTORIES2 ;
select * from "SYSJOB"."SYSJOBHISTORIES2";

select * from "SYSJOB"."SYSOPERATORS";
select * from "SYSJOB"."SYSMAILINFO";
select * from "SYSJOB"."SYSJOBSTEPS";
select * from "SYSJOB"."SYSJOBSCHEDULES";
select * from "SYSJOB"."SYSJOBS";
select * from "SYSJOB"."SYSJOBHISTORIES2";
select * from "SYSJOB"."SYSJOBHISTORIES";
select * from "SYSJOB"."SYSALERTS";
select * from "SYSJOB"."SYSALERTNOTIFICATIONS";
select * from "SYSJOB"."SYSALERTHISTORIES";