达梦数据库基础命令

1、安装数据库
--创建用户
groupadd dinstall
useradd -g dinstall -m -d /home/dmdba -s /bin/bash dmdba

id dmdba
passwd dmdba

--创建目录
mkidr /dm8
chown -R dmdba:dinstall /dm8
chmod -R 755 /dm8
--挂载iso文件
mount /opt/dm8_20220304_x86_rh6_64.iso /mnt/dm

--安装软件
su - dmdba
cd /mnt/dm
./DMinstall.bin -i

--执行脚本
/dm8/script/root/root_installer.sh

--安装数据库
cd /dm8/bin
./dminit path=/dm8/data db_name=DMTEST instance_name=DMTEST port_num=5236 page_size=16 SYSDBA_PWD=dmdba123456

--注册服务与服务启停
cd /dm8/script/root
./dm_service_installer.sh -t dmserver -p DMTEST -dm_ini /dm8/data/DMTEST/dm.ini
systemctl enable DmServiceDMTEST.service
systemctl start  DmServiceDMTEST.service
systemctl status DmServiceDMSERVER.service

--连接数据库

/dm8/bin/disql sysdba/dmdba123456:5236

--DM管理工具图形化界面
/dm8/tools/manager

--nca管理工具
/dm8/tools/nca.sh
cat /etc/dm_svc.conf

--命令行方式启动服务
/dm8/bin/dmserver /dm8/data/DMTEST/dm.ini
/dm8/bin/dmserver /dm8/data/DMTEST/dm.ini mount 
--数据库状态切换
select status$ from v$instance;
alter database open;
alter database mount;
alter database suspend;

--console工具
/dm8/tool/console

--dts迁移工具
/dm8/tool/dts

--参数修改

alter system set "参数名"=参数值 both|spfile|memory;
alter system set 'COMPATIBLE_MODE' =2 spfile;

--创建表空间
create tablespace user_tbs datafile 'user_tbs.dbf' size 60 autoextend on next 32;
或者
create tablespace user_tbs1 datafile '/dm8/data/dmtest/user_tbs1.dbf' size 64 autoextend on next 32;

--更改数据文件大小
alter tablespace user_tbs1 resize datafile 'user_tbs1.dbf' to 96; 

--表空间重命名
alter TABLESPACE user_tbs1 RENAME TO user_tbs01;

--数据文件迁移
alter tablespace  user_tbs01 offline;
alter tablespace user_tbs01 RENAME DATAFILE 'user_tbs1.dbf' to 'user_tbs01.dbf';
alter tablespace  user_tbs01 online;

--更改临时表空间大小
alter system set 'TEMP_SIZE'=64 spfile;
SP_TRUNC_TS_FILE(3, 0, 100);

--添加联机日志
alter database add logfile 'dmtest03.log' size 128;
--修改联机日志大小：
alter database RESIZE logfile '/dm8/data/dmtest/dmtest01.log' to 256;
--迁移联机日志文件：
alter database mount;
alter database rename logfile '/dm8/data/dmtest/dmtest03.log' to '/dm8/data/dmtest/dmtest04.log';
alter database open;

--开启归档
alter database mount;
alter database ARCHIVELOG;
alter database add archivelog 'type=local,dest=/dm8/arch,file_size=64,space_limit=10240';
alter database open;

select arch_mode from V$database;

--归档切换

alter DATABASE ARCHIVELOG CURRENT;
alter SYSTEM ARCHIVE LOG CURRENT;
alter system SWITCH LOGFILE;

--归档的删除
select * from v$ifun t where name like 'SF%ARCHIVE%';
select SF_ARCHIVELOG_DELETE_BEFORE_LSN(1489476);
select SF_ARCHIVELOG_DELETE_BEFORE_TIME(sysdate -10);

--DMRMAN脱机备份
systemctl stop DmServicedmtest.service
/dm8/bin/dmrman
backup database '/dm8/data/dmtest/dm.ini' to fullbak0419 backupset '/dm8/backup/full/fullbak0419';

--DMRMAN还原
/dm8/bin/dmrman
restore database '/dm8/data/dmtest/dm.ini'  from backupset '/dm8/backup/full/fullbak0419';
recover database '/dm8/data/dmtest/dm.ini' update db_magic;
systemctl start DmServicedmtest.service

--联机备份
backup database;
backup database full to fullbak01 backupset '/dm8/backup/full/fullbak01';
backup database increment to incrbak01 backupset '/dm8/backup/incr/incrbak01';
backup database increment with BACKUPDIR '/dm8/backup/full' to incrbak02 backupset '/dm8/backup/incr/incrbak02';
backup database increment CUMULATIVE with BACKUPDIR '/dm8/backup/full' to incrbak03 backupset '/dm8/backup/incr/incrbak03';

--逻辑导出

/dm8/bin/dexp userid=sysdba/dmdba123456 directory=/dm8/backup/dexp file=full.dmp log=full.log full=y