安装
比较简单
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