一、VNC环境
测试机IP:119.96.110.196:5902
VNC密码:dameng123
root账号密码:dameng123
二、中标麒麟操作系统环境配置
2.1 上传达梦数据库安装介质
sftp> cd /software
sftp> pwd
/software
sftp> put -r "D:\BaiduNetdiskDownload\dm8_setup_rh7_64_ent_8.1.1.88_2029.iso"
sftp> put -r "D:\BaiduNetdiskDownload\dm8_setup_rh7_64_ent_8.1.1.88_2029_ISO_SHA256.txt"
2.2 校验达梦数据库安装介质有效性
[root@localhost software]# sha256sum dm8_setup_rh7_64_ent_8.1.1.88_2029.iso
9c3b14a82a809aea86334b49d8439b36ab558d4ee7e7a27dbda609a27352a64b dm8_setup_rh7_64_ent_8.1.1.88_2029.iso
[root@localhost software]# cat dm8_setup_rh7_64_ent_8.1.1.88_2029_ISO_SHA256.txt
9c3b14a82a809aea86334b49d8439b36ab558d4ee7e7a27dbda609a27352a64b
2.3 安装版本管理工具
[root@bogon ~]# yum install neokylin-lsb.x86_6*
[root@bogon ~]# lsb_release -a
[root@bogon ~]# cat /etc/redhat-release
2.4 安装系统包
[root@bogon ~]# yum install compat-glibc*
[root@bogon ~]# rpm -qa|grep glibc
2.5 配置达梦数据库用户
[root@bogon ~]# groupadd dinstall
[root@bogon ~]# useradd -g dinstall dmdba
[root@bogon ~]# echo "dmdba123"|passwd --stdin dmdba
[root@bogon ~]# id dmdba
uid=1001(dmdba) gid=1002(dinstall) 组=1002(dinstall)
[root@bogon ~]# mkdir /opt/dm8
[root@bogon ~]# chown dmdba:dinstall /opt/dm8
[root@bogon ~]# ls -ld /opt/dm8
drwxr-xr-x 2 dmdba dinstall 6 11月 23 11:42 /opt/dm8
2.6 配置中标麒麟操作系统参数
[root@bogon ~]# cat >> /etc/security/limits.conf << EOF
> dmdba soft nofile 65536
> dmdba hard nofile 65536
> EOF
[root@bogon ~]# cat >> /etc/profile << EOF
> ulimit -n 65536
> EOF
ulimit -a
三、安装达梦数据库
3.1 安装数据库软件(图形界面安装,不建库)
[root@bogon software]# chown dmdba:dinstall dm8_setup_rh7_64_ent_8.1.1.88_2029.iso
[root@bogon software]# ls -l
总用量 660228
-rw-r--r-- 1 dmdba dinstall 676069376 10月 31 17:54 dm8_setup_rh7_64_ent_8.1.1.88_2029.iso
-rw-r--r-- 1 root root64 10月 31 17:54 dm8_setup_rh7_64_ent_8.1.1.88_2029_ISO_SHA256.txt
[root@bogon software]# mount -o loop dm8_setup_rh7_64_ent_8.1.1.88_2029.iso /mnt/
mount: /dev/loop0 写保护,将以只读方式挂载
[root@bogon mnt]# xhost +
[root@bogon mnt]# su – dmdba
[dmdba@bogon mnt]$export DISPLAY=127.0.0.1:0.0
[dmdba@bogon mnt]$cd /mnt
[dmdba@bogon mnt]$./DMInstall.bin
3.2 安装数据库软件(静默安装,不建库)
[dmdba@bogon mnt]$ ./DMInstall.bin -i
[dmdba@bogon mnt]$ /opt/dm8/script/root/root_installer.sh
3.3 创建数据库(图形界面安装)
su – dmdba
vi .bash_profile
export LD_LIBRARY_PATH="$LD_LIBRARY_PATH:/opt/dm8/bin"
export DM_HOME="/opt/dm8"
export PATH=$DM_HOME/bin:$DM_HOME/tool:$PATH
source .bash_profile
[root@bogon mnt]# xhost +
[root@bogon mnt]# su – dmdba
[dmdba@bogon mnt]$export DISPLAY=127.0.0.1:0.0
[dmdba@bogon mnt]dbca.sh
[root@bogon software]#mv /opt/dm8/bin/DMServiceDMSERVER.service /usr/lib/systemd/system/DmServiceDMSERVER.service
[root@bogon software]#systemctl enable DmServiceDMSERVER.service
[root@bogon software]#systemctl start DmServiceDMSERVER.service
[root@bogon software]#systemctl status DmServiceDMSERVER.service
[root@bogon software]#systemctl is-enabled DmServiceDMSERVER.service
[root@bogon software]#systemctl list-unit-files DmServiceDMSERVER.service
[dmdba@bogon mnt]$disql sysdba/damengdba123;
[dmdba@bogon mnt]$disql sysdba/damengdba123@127.0.0.1:5236
3.4 创建数据库(静默安装)
----创建数据库---
su – dmdba
vi .bash_profile
export LD_LIBRARY_PATH="$LD_LIBRARY_PATH:/opt/dm8/bin"
export DM_HOME="/opt/dm8"
export PATH=$DM_HOME/bin:$DM_HOME/tool:$PATH
source .bash_profile
[dmdba@bogon bin]$ ./dminit path='/opt/dm8/data/' page_size=16 extent_size=32 case_sensitive=Y db_name=PROED instance_name=ENMO port_num=5252 sysdba_pwd=EnmoEdu123 sysauditor_pwd=EnmoEdu123
---注册数据库服务---
[root@bogon ~]# cd /opt/dm8/script/root/
[root@bogon root]# ./dm_service_installer.sh -t dmserver -p PROED -dm_ini /opt/dm8/data/PROED/dm.ini
[root@bogon root]# systemctl start DmServicePROED
[root@bogon root]# systemctl status DmServicePROED
四、数据库后续配置任务
4.1 创建样例用户
SQL> `/opt/dm8/samples/instance_script/bookshop/CREATESCHEMA.sql
SQL> `/opt/dm8/samples/instance_script/bookshop/CREATETABLE.sql
SQL> `/opt/dm8/samples/instance_script/bookshop/INSERTSQL.sql
4.2 添加重做日志
SQL> alter database add logfile '/opt/dm8/data/PROED/PROED03.log' size 256;
SQL> alter database add logfile '/opt/dm8/data/PROED/PROED04.log' size 256;
4.3 兼容mysql
SQL> alter system set 'COMPATIBLE_MODE'=4 spfile;
SQL> select * from v$parameter where name like 'COMPA%';
4.4 创建表空间
create tablespace "PROD_TBS" datafile '/opt/dm8/data/PROED/PROD_TBS01.DBF' size 64 autoextend on next 1 maxsize 1024, '/opt/dm8/data/PROED/PROD_TBS02.DBF' size 64 autoextend on next 1 maxsize 1024 ;
4.5 创建用户
create user app identified by "App123456" default tablespace prod_tbs;
grant resource to app;
4.6 创建表
以sysdba用户登录
create table app.emp as select * from resources.employee where 1=2;
create table app.dept as select * from resources.department where 1=2;
GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON APP.EMP TO HR;
GRANT ALL PRIVILEGES ON APP.EMP TO HR;
4.7 赋予修改特定列数据权限
以sysdba用户登录
create user hr identified by "dameng123";
grant resource to hr;
grant select(employeeid,nationalno,loginid),update(employeeid,nationalno,loginid),insert(employeeid,nationalno,loginid) on app.emp to hr;
4.8 导入数据
建立insert.sql文件,赋予dmdba:dinstall权限。
SQL> host more /opt/dm8/samples/insert.sql
insert into app.dept values (1,'xiaoshou');
SQL> start /opt/dm8/samples/insert.sql
SQL> insert into app.dept values (1,'xiaoshou');
影响行数 1
已用时间: 0.486(毫秒). 执行号:814.
SQL> commit;
操作已执行
已用时间: 0.316(毫秒). 执行号:815.
4.9 修改数据
以hr用户登录
update app.emp set PERSONID=1;
commit;
4.10 配置ODBC
tar xzvf unixODBC-2.3.2.tar.gz
cd unixODBC-2.3.2/
./configure --enable-gui=no
make
make install
odbc_config --odbcini
[root@DM etc]# cat odbc.ini
[dm8]
driver=dm8 odbc
server=192.168.0.111
uid=sysdba
pwd=damengdba123
tcp_port=5236
[root@DM etc]# cat odbcinst.ini
[dm8 odbc]
driver=/opt/dm8/drivers/odbc/libdodbc.so
[root@DM software]# odbc_config --version
[root@DM software]# odbc_config --odbcini
[root@DM software]# odbcinst –j
--测试ODBC是否连通
[dmdba@bogon bin]$isql dm8
4.11 备份与恢复
---做一个完整备份,使能够恢复到任意一个时刻。
su – dmdba
mkdir /opt/dm8/bakcup
---将备份存放到/opt/dm8/backup 目录
alter database mount;
alter database archivelog;
---备注:需要注意归档目录属性。
su – dmdba
mkdir /opt/dm8/arch
alter database add archivelog 'DEST=/opt/dm8/arch, TYPE=LOCAL, FILE_SIZE=200,SPACE_LIMIT=1024';
alter database open;
select * from v$dm_arch_ini;
backup database backupset '/opt/dm8/backup/db_full_bak_01';
---将main.dbf 文件删除,利用以上的备份恢复。
SQL> backup database backupset '/opt/dm8/backup/db_full_bak_01';
SQL> host ls -l /opt/dm8/backup/db_full_bak_01
[dmdba@enmoedu1 backup]$ cd /opt/dm8/data/DAMENG/
[dmdba@enmoedu1 DAMENG]$ rm MAIN.DBF
[dmdba@enmoedu1 DAMENG]$ DmServiceDMSERVER stop
[dmdba@enmoedu1 DAMENG]$ ls |grep MAIN.DBF
[dmdba@enmoedu1 DAMENG]$ dmrman
dmrman V8
RMAN>restore database '/opt/dm8/data/DAMENG/dm.ini' tablespace MAIN from backupset '/opt/dm8/backup/db_full_bak_01';
RMAN>restore database '/opt/dm8/data/DAMENG/dm.ini' tablespace MAIN from backupset '/opt/dm8/backup/db_full_bak_01';
RMAN>recover database '/opt/dm8/data/DAMENG/dm.ini' tablespace MAIN;
[dmdba@enmoedu1 DAMENG]$ ls |grep MAIN.DBF
MAIN.DBF
[dmdba@enmoedu1 DAMENG]$ DmServiceDMSERVER start
Starting DmServiceDMSERVER: [ OK ]
[dmdba@enmoedu1 DAMENG]$ disql sysdba/dameng123@localhost:5252
SQL> select file_name,tablespace_name,status from dba_data_files;
SQL> select status$ from v$instance;
---备份定时任务
---创建两个job,一个full_job 每周六,日晚上23:00 做全备,另一个inc_job 每周三,五做增量备份。
---先创建代理环境:
---备注:配置增量备份时要选择“基备份目录”。