DCA笔记
一、安装
安装最节省的步骤
1.temp扩容
2.建dm、dm8
3.挂载安装包镜像
4.建组、建用户（密码根据题目）
5.给用户文件夹的权限
6.执行xhost +，echo display
7.用户登录
8.设置display
9.进入挂载目录安装
10.安装

查看tmp 容量
Df -h

扩temp
Mount -o remount,size=2G /tmp
挂载
Root
mount /opt/dm8_20210818_x86_rh6_64_ent_8.4.2.18_pack14.iso
/mnt/dm
[root@KylinDCA03 opt]# groupadd dinstall
[root@KylinDCA03 opt]# useradd -g dinstall -m -d /home/dmdba -s /bin/bash dmdba
[root@KylinDCA03 opt]# passwd dmdba
赋权限
chown dmdba:dinstall /dm8

xhost +
export DISPLAY=:0.0

二、修改参数
修改编辑器
alter system set ‘参数名’=参数值 both|spfile|memory
both=spfile+memory
alter system set ‘COMPATIBLE_MODE’=2 spfile;
alter system set ‘UNDO_RETENTION’=240 both;
alter session set ‘LIST_TABLE’=1;

三、表空间
查看
select * from v$tablespace;
select * from v$datafile;
create tablespace dmtbs DATAFILE ‘/dm8/data/TBS/DMTBS01.DBF’ size 32;

重做日志
select * from SYS.“V$RLOGFILE”;
alter DATABASE ADD LOGFILE ‘DAMENG03.log’ SIZE 256;

归档：
查看
select * from SYS.“V$ARCH_FILE”;

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 * from DBA_ROLE_PRIVS t where t.GRANTEE =‘DMTEST’;
select * from DBA_TAB_PRIVS t where t.GRANTEE =‘DMTEST’;
select * from DBA_SYS_PRIVS t where t.GRANTEE =‘DMTEST’;
设置指定用户最大并发量
Alter user HRTEST LIMIT SESSION_PER_USER;

select a.USERNAME, b.*
from dba_users a, sysusers b
where a.USER_ID = b.ID;

五、建表
create table dmtest.t_testpid
( pid int PRIMARY key, pname varchar(20) not null) TABLESPACE DMTBS;
通过复制建表：
create table dmtest.t_emp as select * from dmhr.EMPLOYEE;
只复制表结构
create table dmtest.t_emp as select * from dmhr.EMPLOYEE where 1=0;

删除表数据：truncate table dmhr.EMPLOYEE
建约束
主键约束
alter table dmtest.t_testchild ADD CONSTRAINT fk_testchild_pid PRIMARY KEY (pid)
REFERENCES dmtest.t_testpid(pid);
外键约束
alter table dmtest.t_testchild ADD CONSTRAINT fk_testchild_pid FOREIGN KEY (pid) REFERENCES dmtest.t_testpid(pid);
检查约束
alter table dmtest.t_testchild ADD CONSTRAINT ck_testchild_salary CHECK (salary>=2800);

建索引
create index DMTEST.ix_emp_name on DMTEST.T_EMP (employee_name)
TABLESPACE DMTBSIDX;
表导入：
Bin ,disql
SQL> set echo off
SQL> set feedback off
SQL> set timing off
SQL> start /dm8/backup/t_emp.sql

六、建视图
create or replace view dmtest.v_emp as
select a.DEPARTMENT_ID, a.EMPLOYEE_ID, a.EMPLOYEE_NAME, a.EMAIL,
a.SALARY
from dmtest.t_emp a
where a.DEPARTMENT_ID =1005;
create or replace view dmtest.v_empsalary as
select b.DEPARTMENT_NAME, avg(salary) avg_salary
69
from dmhr.employee a, dmhr.department b
where a.department_id = b.department_id
group by b.DEPARTMENT_NAME
having avg(salary) > 5000;

七、备份还原
库备份
完全备份
–默认路径
backup database；
—指定路径
backup database full to DMFULLBAK01 backupset ‘/dm8/backup/full/DMFULLBAK01’;
增量备份：
–不指定搜索目录，就是搜索默认备份目录下的最新的全量备份做为基础备份集
backup database INCREMENT to INCK01 backupset ‘/dm8/backup/incr/INCK01’;
–指定路径搜索，with BACKUPDIR后的路径+默认路径+备份到的目录，这三个目录下搜索最新的别分集
backup database INCREMENT with BACKUPDIR ‘/dm8/backup/full/’
to INCK02 backupset ‘/dm8/backup/incr/INCK02’;
–累计增量备份的基础备份集是最新的全量备份集

backup database INCREMENT CUMULATIVE with BACKUPDIR ‘/dm8/backup/full/’
to INCK03 backupset ‘/dm8/backup/incr/INCK03’;
—表空间备份
backup tablespace DMTBS to DMTBSBAK backupset ‘/dm8/backup/full/DMTBSBAK’;

冷备份：
dm8/bin/DMRMAN
RMAN> backup database ‘/dm8/data/DAMENG/dm.ini’ to DMFULLBAK04
backupset ‘/dm8/backup/full/DMFULLBAK04’;

–还原
库级还原与恢复（还原、恢复、更新数据库魔数）
图形化工具
Console
进入目录bin
还原：脱机
还原:restore database ‘/dm8/data/DAMENG/dm.ini’ from backupset
‘/dm8/backup/full/DMFULLBAK04’;
恢复：基于归档
recover database ‘/dm8/data/DAMENG/dm.ini’ with archivedir ‘/dm8/arch’;
更新魔数
recover database ‘/dm8/data/DAMENG/dm.ini’ update db_magic;

表空间(还原、恢复)

作业：
1、创建代理环境
查询作业
select * from SYSJOB.SYSJOBS;
调用作业：
dbms_job.run(1669366566);
查看调用日志：
select * from SYSJOB.SYSJOBHISTORIES2;

八、ODBC

(1) 解压
[root@KylinDCA04 opt]# tar -zxvf unixODBC-2.3.0.tar.gz
(2)安装 ODBC（三部曲：配置、编译、安装）
解压后的目录
[root@KylinDCA04 unixODBC-2.3.0]# ./configure（默认安装在/usr/local 下）
[root@KylinDCA04 unixODBC-2.3.0]# make
[root@KylinDCA04 unixODBC-2.3.0]# make install
（3）配置数据源
查看安装路径
[root@KylinDCA04 unixODBC-2.3.0]# odbcinst -j
unixODBC 2.3.0
DRIVERS…: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES…: /usr/local/etc/ODBCDataSources
USER DATA SOURCES…: /root/.odbc.ini
SQLULEN Size…: 8
SQLLEN Size…: 8
SQLSETPOSIROW Size.: 8

配置
[root@KylinDCA04 etc]# vim odbcinst.ini
[DM8 ODBC DRIVER] --驱动名称
Description = ODBC DRIVER FOR DM8
Driver = /dm8/bin/libdodbc.so
[root@KylinDCA04 etc]# vim odbc.ini
[DM8] --数据源名称
Description = DM ODBC DSN
Driver = DM8 ODBC DRIVER
SERVER = localhost
UID = SYSDBA
PWD = SYSDBA
TCP_PORT = 5236

查看是否安装完成
Su - dmdba
Dm8/bin目录下执行isql dm8 -v

启动、关闭服务
进入bin 下执行
./DmServiceDMSEVER start/stop