DmCA 快速进攻 Kylin V10 dm8

安装

比较简单

systemctl stop    firewalld.service
systemctl disable firewalld.service

cat >> /etc/security/limits.conf <<!
dmdba hard nofile 65536
dmdba soft nofile 65536
!

ulimit -n 65536
ulimit -a

getenforce
setenforce 0
vi /etc/sysconfig/selinux
SELINUX=disabled

groupadd dinstall
useradd -g dinstall dmdba
id dmdba
passwd dmdba

mkdir /dm
chown dmdba:dinstall /dm

mkdir /data
chown dmdba:dinstall /data

mount -r dm8_20220902_x86_rh6_64_ent_8.1.2.138.iso /mnt

su - dmdba
/mnt/DMInstall.bin -i
或
su - dmdba
export  DISPLAY=:1  #上面root的DISPLAY值
cd /mnt
./DMInstall.bin

root 执行 /dm/script/root/root_installer.sh

cat>> ~/.bash_profile<<eof

export PATH=\$DM_HOME/bin:\$DM_HOME/tool:\$PATH
alias dm='disql sysdba/Dameng123@localhost:5237'
eof

. ~/.bash_profile

替换
sed -i 's#XXX#bbb#' dm.ini

建库

更加容易

su - dmdba
mkdir -p /data/PROD/
dminit INI_FILE=/data/PROD/dm.ini path=/data LOG_PATH=/data/PROD/PROD01.log 
LOG_PATH=/data/PROD/PROD02.log LOG_PATH=/data/PROD/PROD03.log 
EXTENT_SIZE=16 PAGE_SIZE=8 LOG_SIZE=300 CHARSET=0 
SYSDBA_PWD=Dameng123 DB_NAME=PROD INSTANCE_NAME=DBSERVER PORT_NUM=5239

字符集 
select decode(unicode(),0,'GB18030',1,'UTF-8',2,'EUC-KR') charset from dual;

redo文件调整
select file_id,path,rlog_size/1024/1024 size_m from v$rlogfile;
alter database add logfile '/data/PROD/PROD04.log' size 300;  #增加，以 M 为单位
alter database resize logfile 'PROD04.log' to 302;            #调大，不支持调小
不支持删除命令

su -
/dm/script/root/dm_service_installer.sh -t dmserver -dm_ini /data/PROD/dm.ini -p DBSERVER # -p 实例名

su - dmdba
DmServiceDBSERVER start
disql sysdba/Dameng123@localhost:5237
select name,STATUS$ from v$instance;


改口令策略
SP_SET_PARA_VALUE(1,'PWD_POLICY',7);

0 无策略
1 禁止与用户名相同
2 口令长度不小于 9
4 至少包含一个大写字母（A-Z）
8 至少包含一个数字（0-9）
16 至少包含一个标点符号（英文输入法状态下， 除“和空格外的所有符号）

修改参数

alter system set 'PWD_POLICY'=6 both;
alter system set 'COMPATIBLE_MODE'=2 spfile;

0:none
1:SQL92
2:Oracle
3:MS SQL Server
4:MySQL
5:DM6
6:Teradata

DmServiceDBSERVER restart

select name,VALUE from v$parameter where name in ('PWD_POLICY','COMPATIBLE_MODE');

创建表空间

以M为单位

select tablespace_name,file_name,bytes/1024/1024 bytes_m from dba_data_files order by 1,2;
create tablespace USERS datafile '/data/NEW/USERS01.dbf' size 50 autoextend on next 1 maxsize 10240, 
'/data/NEW/USERS02.dbf' size 50 autoextend on next 1 maxsize 10240;

创建用户

create user TEST identified by Dameng123 limit failed_login_attemps 3, password_lock_time 1, password_grace_time 10 default tablespace USERS default index tablespace USERS;
grant resource to TEST;

alter user TEST limit password_life_time 90;
select * from dba_users where username='TEST';

alter user TEST limit password_life_time unlimited;

创建对象

create table DEPARTMENTS
(
DEPTNO DEC(2) NOT NULL,
DNAME VARCHAR(14) NOT NULL,
LOC VARCHAR(13),
primary key(DEPTNO)
) storage(on USERS);


create table EMPLOYEES
(
EMPNO DEC(4) NOT NULL,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR DEC(4),
SAL DEC(7,2),
COMM DEC(7,2) DEFAULT 0,
DEPTNO DEC(2) NOT NULL,
primary key(EMPNO)
) storage(on USERS);

ALTER TABLE EMPLOYEES ADD CHECK (sal > 0);
alter table EMPLOYEES add constraint FK_DEPARTMENT_ID foreign key(DEPTNO) references DEPARTMENTS(DEPTNO);

create user hr identified by Dameng123;
grant resource to hr;
grant update(EMPLOYEE_ID,EMPLOYEE_NAME,SALARY),select(EMPLOYEE_ID,EMPLOYEE_NAME,SALARY) on TEST.EMPLOYEES to HR;

执行脚本
start /opt/script1.sql

备份

chown dmdba:dinstall /backup

ps -ef|grep dms|grep -v grep|cut -d= -f2|cut -d- -f1  #拙劣

设置归档模式
mkdir /data/PROD/arc

select name,arch_mode from v$database;
alter database mount;
alter database add archivelog 'DEST=/data/PROD/arc, TYPE=LOCAL, FILE_SIZE=64, SPACE_LIMIT=10240';
alter database archivelog;
alter database open;

sed -i 's#/data/PROD/bak#/backup#' dm.ini
DmServiceDBSERVER restart

SELECT SF_GET_PARA_STRING_VALUE(1,'BAK_PATH') Path;

BACKUP DATABASE TO DB_BAK3 backupset 'db_bak3';
select backup_name,backup_path from v$backupset;

逻辑导出

dexp test/Dameng123@localhost:5237 file=db_test.dmp log=db_test.log DIRECTORY=/data/backup OWNER=test

create user hr identified by Dameng123;
grant resource to hr;
grant update(EMPLOYEE_ID,EMPLOYEE_NAME,SALARY),select(EMPLOYEE_ID,EMPLOYEE_NAME,SALARY) on TEST.EMPLOYEES to HR;

dimp hr/Dameng123@localhost:5237 file=/data/backup/db_test.dmp log=/dm/imp_hr.log REMAP_SCHEMA=test:hr

恢复数据库（小坑）

dmrman
check backupset '/dm8/backup/20210616_bak';
RESTORE DATABASE '/dm8/data/PROD/dm.ini' tablespace "TBS" FROM BACKUPSET '/dm8/backup/20210616_bak' DEVICE TYPE disk;
RECOVER DATABASE '/dm8/data/PROD/dm.ini' TABLESPACE TBS WITH ARCHIVEDIR '/dm8/arch';
exit

ll /dm8/data/PROD/TBS*
DmServiceTEST start
disql sysdba/Dameng123@localhost:5237
select status$ from v$instance;
select FILE_NAME,TABLESPACE_NAME,STATUS,ONLINE_STATUS from dba_data_files;

移动数据文件

select tablespace_name,FILE_NAME,STATUS,ONLINE_STATUS from dba_data_files order by 1;
alter tablespace tbs offline;
alter tablespace tbs rename datafile '/db/data/DAMENG/TBS02.DBF' to '/dm/data/TBS02.DBF';
alter tablespace tbs online;

创建视图

create view v_sal as select * from departments where deptno in (
select d.deptno from departments d inner join employees e on
 d.deptno=e.deptno group by d.deptno having sum(e.sal)>9000);

安装odbc驱动

cd /opt
tar xzf unixODBC-2.3.11.tar.gz
cd unixODBC-2.3.11
./configure --enable-gui=no
make && make install
odbc_config --version
odbcinst -j

cat >>/usr/local/etc/odbc.ini<<eof
[dm8]
driver=DM8 ODBC DRIVER
server=localhost
uid=SYSDBA
pwd=Dameng123
tcp_port=5237
eof

cat >> /usr/local/etc/odbcinst.ini<<eof
[DM8 ODBC DRIVER]
DRIVER=/dm/bin/libdodbc.so
eof

su - dmdba
isql dm8
quit