达梦数据库-日常管理

最新推荐文章于 2024-05-17 16:56:30 发布

fangyu_7362

最新推荐文章于 2024-05-17 16:56:30 发布

阅读量2.2k

点赞数

文章标签： dba

本文链接：https://blog.csdn.net/fangyu_7362/article/details/125172119

版权

模式管理

模式

类似MySQL中的database , 用户可以创建多个database, 在DM中表示为模式, 默认情况下当创建用户时, 则默认会创建一个同名的模式

创建模式

CREATE SCHEMA s1 AUTHORIZATION fangyu; 在manager中可以创建臣成功, 但是disql中创建不成功

SQL> drop schema s1 cascade;

操作已执行

已用时间: 22.799(毫秒). 执行号:1000.

SQL> CREATE SCHEMA s1 AUTHORIZATION fangyu;

5 一直处于输入状态

切换模式

SET SCHEMA schema_name;

删除模式

DROP SCHEMA schema_name;

CASCADE 删除时, 如果该模式下存在数据对象则无法删除, 使用该参数则不会报错

RESTRICT 默认选项

表空间管理

创建表空间

CREATE TABLES tablespace_name DATAFILE '/dmdata/itsdata/tbs1.dbf' SIZE 2048;

扩展修改表空间

扩展表空间, 实际就是向表空间中添加数据文件

ALTER TABLESPACE tbs_its ADD DATAFILE '/dmdata/itsdata/its02.dbf' SIZE 1024;

数据文件扩展属性

AUTOEXTEND {ON|OFF} 是否开启自动扩展

NEXT 一次扩展多大

MAXSIZE 最大扩展到多大

修改数据文件路径

ALTER TABLESPACE tbs_its RENAME DATAFILE '/dmdata/itsdata/tbs_its01.dbf' TO '/tmp/tbs_its01.dbf'

修改表空间只能修改表空间名称

ALTER TABLESPACE tbs_name RENAME TO new_its_tbs;

修改表空间状态

对于表空间的状态修改只能除力, 系统表空间、回滚表空间、重做日志和临时表空间外的表空间

ALTER TABLESPACE tbs_name OFFLINE; 将表空间脱机

ALTER TABLESPACE tbs_name ONLINE;

ALTER TABLESPACE tbs_name CORRUPT; 损坏状态

修改表空间缓冲区

ALTER TABLESPACE tbs_its CACHE="KEEP"

删除表空间

删除表空间只能删除未使用过的表空间, 删除表空间会将所有数据文件都删除

DROP TABLESPACE tbs_name;

创建表空间限制

向表空间中添加的数据文件大小必须满足 4096*页大小

向表空间中添加数据文件的个数最多只能添加256个

理论上数据库可以创建65535个表空间, 用户最多只能创建32768个表空间, 剩余的系统保留使用

表空间名称必须唯一

SYSTEM 表空间不允许关闭自动扩展, 且不允许限制空间大小

重命名数据文件, 表空间必须处于OFFLINE状态

表空间相关视图

V$TABLESPACE

DBA_TABLESPACE

表空间文件检测

FIL_CHECK_INTERVAL = 0 检测表空间是否存在 0: 不检查单位是秒

手动检测

SP_FILE_SYS_CHECK()

表空间文件恢复

当表空间中的文件被删除后, 如果文件句柄没有被删除则可以进行恢复

1、通过/proc/dm_pid/fd 目录下找到对应的文件句柄

SQL> drop tablespace tbs_its11111;

2、通过cp 命令复制该句柄号即可恢复

[root@itsdata ~]# ps aux |grep dm

[root@itsdata ~]# cd /proc/1122/fd

[root@itsdata fd]# cp 52 /tmp/its011111.dbf

表空间常用函数

SP_TABLESPACE_PREPARE_RECOVER(); 表空间恢复文件准备

SP_TABLESPACE_RECOVER(); 表空间恢复

管理表

创建表

CREATE TABLE PRODUCTION.PRODUCT_REVIEW(

PRODUCT_REVIEWID INT IDENTITY(1,1),

PRODUCTID INT NOT NULL,

NAME VARCHAR(50) NOT NULL,

REVIEWDATE DATE NOT NULL,

EMAIL VARCHAR(50) NOT NULL,

RATING INT NOT NULL,

COMMENTS TEXT,

PRIMARY KEY(PRODUCT_REVIEWID),

FOREIGN KEY(PRODUCTID) REFERENCES PRODUCTION.PRODUCT(PRODUCTID),

CHECK(RATING IN(1,2,3,4,5))

)STORAGE (

INITIAL 5, 创建表时立即分配多少个簇

MINEXTENTS 5, 最少保留多少个簇

NEXT 2, 当需要扩展表空间时, 一次分配多少个簇

FILLFACTOR 85, 填充因子, 默认是0,表示100%

ON TS_PRODUCTION 指定表空间

);

列的约束

NULL , NOT NULL 是否为空

UNIQUE 唯一性约束

PRIMARY KEY 主键约束

CLUSTER PRIMARY KEY 主键约束

NOT CLUSTER PRIMARY KEY 主键约束, 指明指定列作为基表的非聚集索引主关键字

CLUSTER KEY 聚集索引非唯一性

USING INDEX TABLESPACE 指定索引使用的表空间

REFERENCES 指明列的引用约束

CHECK 检查约束

NOT VISIBLE 不可见约束

FOREIGN KEY 外键约束

STORAGE 选项

NOBRABCH 堆表并发分支

BRANCH 堆表非并发分支

CLUSTERBTR B树表

堆表

堆表的rowid, 采用物理地址进行存储(文件号、页号、页便宜)

创建堆表

1、INI 参数方式

LIST_TABLE = 0 0:表示没有没有显示的指定创建堆表则创建的就不是堆表

2、显示指定方式

在STORAGE中显示指定

BRANCH(n,m) 创建的表为堆表, 并发分指数为n, 非并发的分指数为m

NOBRANCH 创建的表为堆表, 并发分支数为0, 非并发分支数为1

CLUSTERBTR 创建的不是堆表

堆表的限制

1、不能创建聚集索引

2、不支持列存储

创建外部表

所谓外部表, 就是引用外部定义的控制文件和数据文件进行表的创建

CREATE EXXTERNAL TABLE tb_name(

c1 INT,

c2 INT,

c3 INT

) FROM '控制文件路径';

创建临时表

CREATE GLOBAL TABLE t1(

c1 INT ,

c2 VARCHAR(12)

) ON COMMIT DELETE ROWS;

ON COMMIT DELETE ROWS 指定临时表是事务级别, 每次提交或回滚之后, 表中所有数据都被删除

ON COMMIT PRESREVE ROWS 指定临时表是会话级别, 会话结束后数据才会删除

修改表

修改列的属性

CREATE TABLE t1(

id INT,

name VARCHAR(10)

);

ALTER TABLE t1 MODIFY name VARCHAR(32) NOT NULL DEFAULT ''; 修改name字段字节长度32, 不允许为空, 默认值为''

添加字段

ALTER TABLE t1 ADD COLUMN age INT;

删除字段

ALTER TABLE t1 DROP COLUMN age;

删除表

DROP TABLE tb_name;

DROP TABLE IF EXISTS tb_name;

清空表数据

TRUNCATE TABLE

DELETE FROM

查看建表语句

CALL SP_TABLEDEF('用户名','表名');

SELECT DBMS_METADATA.GET_DDL('TABLE','表名','用户或者模式') FROM dual;

分区表

分区表可以再一个表空间中, 也可以在多个表空间中

分区表的好处

1、平均IO

2、管理性好

分区类型

RANGE范围分区

CREATE TABLE t1(

id INT,

name VARCHAR(12),

dtime DATETIME

) PARTITION BY RANGE(dtime)(

PARTITION p1 VALUES LESS THAN '2022-05-01',

PARTITION p2 VALUES LESS THAN '2022-05-02',

PARTITION p3 VALUES LESS THAN '2022-05-03',

PARTITION p4 VALUES MAXVALUE

);

HASH哈希分区

CREATE TABLE t1(

id INT,

name VARCHAR(12),

dtime DATETIME

) PARTITION BY HASH(dtime)(

PARTITION p1,

PARTITION p2,

);

LIST列表分区

CREATE TABLE t1(

id INT,

name VARCHAR(12),

dtime DATETIME

) PARTITION BY LIST(dtime)(

PARTITION p1 VALUES ('2022-05-01','2022-06-01'),

PARTITION p2 VALUES ('2022-07-01','2022-08-01'),

);

多級分区

对子分区再进行分区

CREATE TABLE t1(

id INT,

name VARCHAR(12),

dtime DATETIME

) PARTITION BY RANGE(dtime)(

SUBPARTITION BY RANGE(id)

SUBPARTITION p11 VALUES LESS THAN 100,

);

分区表创建索引

全局索引 GLOBAL

局部索引索引必须是分区键的一部分

添加分区

ALTER TABLE t1 ADD PARTITION p3 VALUES

删除分区

ALTER TABLE t1 DROP PARTITION p1;

交换分区

ALTER TABLE t1 EXCHANGE PARTITION p1 WITH TABLE t1_bak;

将t1表中p1分区中的数据写入t1_bak表中, t1表的数据没有了

合并分区

ALTER TABLE t1 MERGE PARTITION p1,p2 INTO PARTITION p3;

将p1,p2中的数据合并到p3

索引

索引类型

聚集索引每个表只能有一个

唯一索引索引具有唯一性

函数索引函数表达式作为索引

位图索引重复值的列

位图连接索引连接列上建立位图索引

全文索引倒排索引, 主要用于文本内容匹配

在进行数据导入时, 要先将数据导入后, 再创建索引, 这是因为可以尽量避免页合并和页分裂

创建索引

CREATE INDEX idx_name ON tb_name(col_name) STORAGE(... ON tbs_name);

聚集索引建立

CREATE CLUSTER INDEX idx_name ON tb_name(col_name);

唯一索引建立

CREATE UNIQUE INDEX idx_name ON tb_name(col_name);

函数索引建立

CREATE INDEX idx_name ON tb_name(col1 + col2); ()中是表达式

位图索引

CREATE BITMAP INDEX idx_name ON tb_name(col_name);

反向索引

CREATE INDEX idx_name ON tb_anme(col_name) REVERSE;

修改索引

ALTER INDEX idx_anme INVISIBLE; 设置所以不可见, 表示在执行SQL时不会使用该索引

ALTER INDEX idx_anme UNUSABLE; 设置索引无效

重建索引

SP_REBUILD_INDEX('模式','索引id')

ALTER INDEX idx_name REBUILD SHARE;

删除索引

DROP INDEX idx_name;

ALTER TABLE tb_name DROP CONSTRAINT idx_name; 删除唯一索引或者主键

查看索引信息

INDEXDEF(索引id,{0,1})

聚集索引

每个表都有一个聚集索引, 数据根据聚集索引进行排序存储, 如果没有明确指定, 则系统会自动建立一个ROWID字段作为聚集索引

以ROWID为聚集索引不能提高查询速度, 因为很少有人使用rowid进行查询数据

DM 创建聚集索引方式

CLUSTER PRIMARY KEY 创建聚集索引, 并且指定为主键

CLUSTER KEY 创建聚集索引, 但是不唯一

CLUSTER UNIQUE KEY 创建聚集索引, 唯一

PK_WITH_CLUSTER = 0 设置为1: 表示当创建聚集索引时, 则自动转化为聚集主键

填充因子的作用: 在对记录进行更新时, 如果更新的数据比原数据大, 有可能导致页分裂, 填充银子就是防止页分裂的一种手段

序列

创建序列

CREATE SEQUENCE seq_name ...;

CURRVAL 返回当前序列值

NEXTVAL 返回下一个序列值

示例

SQL> create table t2 (c1 int,c2 int);

SQL> create sequence s1 increment by 10; 10: 表示步长

SQL> insert into t2 values(s1.nextval,1);

SQL> select * from t2;

行号 C1 C2

---------- ----------- -----------

1 1 1

2 11 1

3 21 1

4 31 1

5 41 1

SQL> select s1.CURRVAL;

行号 CURRVAL

---------- --------------------

1 41

修改序列

SQL> alter sequence s1 increment by 1;

SQL> select s1.CURRVAL;

行号 CURRVAL

---------- --------------------

1 41

SQL> select s1.NEXTVAL;

行号 NEXTVAL

---------- --------------------

1 42

删除序列

DROP SEQUENCE seq_name;

数据目录

create directory tmp AS '/tmp/'; 创建一个数据目录

select * from SYS.DBA_DIRECTORIES 通过该视图可以查看

当使用dexpdp命令导出数据时,指定目录则默认导出的文件就会放到该目录下

DROP DIRECTORY TMP; 删除数据目录

SELECT 查询语句

DISTINCT 去除重复值

SQL> SELECT * FROM KK;

行号 A

---------- -----------

1 5

2 5

3 6

4 8

SQL> SELECT DISTINCT(A) FROM KK;

行号 A

---------- -----------

1 5

2 6

3 8

MINUS,EXCEPT 查找差集

A MINUS B 找出A表存在的数据, 但B表没有的

SQL> SELECT * FROM TT MINUS SELECT * FROM KK;

行号 A

---------- -----------

1 7

SQL> SELECT * FROM TT EXCEPT SELECT* FROM KK;

行号 A

---------- -----------

1 7

WHERE 语句

用于条件过滤, 主要操作符包含下面这些

算术运算符

+ - * / %

比较运算符

> <= >= <= <> != <=>

IS NULL 判断是否为空

IS NOT NULL 判断是否不为空

LEAST 在多个值中返回最小值

GREATEST 在多个值中返回最大值

BETWEEN AND 判断区间

IN 判断一个值是否存在于列表中

NOT IN 判断一个值是否不存在于列表中

LIKE 模糊匹配 % 匹配多个值 _ 匹配单个值

REGEXP 正则表达式规则匹配

RLIKE 一个值是否符合正则表达式的规则

逻辑运算符

NOT , !

AND , &&

OR , ||

XOR

聚集函数

常用聚集函数

MAX() 求最大值

MIN() 求最小值

SUM() 求总和

AVG() 求平均数

COUNT() 求总行数

SQL> select count(*) from tt;

行号 COUNT(*)

---------- --------------------

1 3

SQL> select min(a) from tt;

行号 MIN(A)

---------- -----------

1 5

SQL> select max(a) from tt;

行号 MAX(A)

---------- -----------

1 7

情况表达式 CASE 多分支判断

1、第一种写法

CASE

WHEN expr THEN ...

ELSE

END

SELECT

emp_no,

first_name,

last_name,

CASE

WHEN emp_no > 2000 THEN '小'

WHEN emp_no > 3000 THEN '中'

WHEN emp_no > 4000 THEN '大'

WHEN emp_no > 5000 THEN '超大'

ELSE 'none'

END AS '判断'

FROM

employees;

2、第二种写法

CASE col

WHEN value THEN ...

ELSE ...

END

/* 这种方式只能判断value的等值操作 */

SELECT

emp_no,

first_name,

last_name,

CASE emp_no

WHEN 2000 THEN '小'

WHEN 3000 THEN '中'

WHEN 4000 THEN '大'

WHEN 5000 THEN '超大'

ELSE 'none'

END AS '判断'

FROM

employees;

连接查询

交叉连接

对两张表做笛卡尔积, 后进行条件过滤

SQL> SELECT * FROM TT CROSS JOIN KK;

行号 A A

---------- ----------- -----------

1 5 5

2 5 5

3 5 6

4 5 8

5 6 5

6 6 5

7 6 6

8 6 8

9 7 5

10 7 5

11 7 6

行号 A A

---------- ----------- -----------

12 7 8

12 rows got

自然连接

自然连接: 根据两张表相同名的字段自动进行等值连接

自然连接写法

SELECT * FROM T1 NATURAL JOIN T2 ;

SELECT * FROM T1 JOIN T2 USING(ID);

SELECT * FROM T1 JOIN T2 ON T1.ID = T2.ID;

自连接

自己和自己进行关联

自连接写法

SELECT * FROM A AS A1,B AS B1 WHERE A1.ID = B1.ID

内连接

根据连接条件, 仅将满足条件的数据显示

SELECT * FROM A INNER JOIN B ON A.ID = B.ID

左外连接

左外连接: 以左表为准, 左表全部显示匹配右表数据, 匹配不到得使用NULL补齐根据ON条件进行等值连接,如果左表有数据,右表没有数据则右表以NULL补齐

SELECT * FROM A LEFT JOIN B ON A.id = B.id

右外连接

右外连接: 以右表为准, 右表全部显示匹配左表数据, 匹配不到得使用NULL补齐根据ON条件进行等值连接,右表没有数据则左表以NULL补齐

SELECT * FROM A RIGHT JOIN B ON A.id = B.id

子查询

标量子查询: 子查询中返回的数据只能有一行

SELECT * FROM A WHERE A.ID = (SELECT ID FROM B WHERE .ID=1);

标量子查询操作符

表查询: 子查询中返回的数据可以有多行

SELECT * FROM A WHERE A.ID IN (SELECT ID FROM B WHERE .ID=1);

多行子查询操作符

ANY

ALL

SOME

派生子查询

派生表查询指的是对应的子查询是一个SELECT语句组成的虚表, 通常出现在FROM 后面

SELECT * FROM A ,(SELECT * FROM B) B1 WHERE A.ID = B1.ID

EXISTS子查询

如果子查询中存在满足条件的数据返回 TRUE 不在子查询中继续查找

如果子查询不存在满足条件的数据返回 FALSE 继续在子查询中查找

EXISTS子查询必须和外表有关联字段

SELECT * FROM employees WHERE emp_no IN (SELECT emp_no FROM dept_emp WHERE dept_no = 'd005');

SELECT * FROM employees WHERE exists(SELECT * FROM dept_emp de WHERE dept_no = 'd005' AND employees.emp_no = de.emp_no)

多列子查询

子查询的返回数据中包含多个字段

DM 只支持IN和NOT IN

SELECT * FROM A WHERE (id,name) IN(SELECET id,name FROM B);

UNION , UNION ALL

将两个表的结果进行合并

两个表对应的列名和类型要保持一致

SELECT * FROM A UNION ALL SELECT * FROM B; UNION ALL 不会进行去重操作

SELECT * FROM A UNION SELECT * FROM B; UNION 会进行去重操作

GROUP BY

根据指定字段, 对数据进行分组显示, 常和聚集函数一起使用

SELECT * FROM B GROUP BY B.ID

SELECT A.ID, COUNT(*) FROM A GROUP BY A.ID

HAVING

对分组后的数据进行过滤

SELECT A.ID, COUNT(*) C FROM A GROUP BY A.ID HAVING C > 100;

ORDER BY

根据指定字段进行排序

SELECT ID,NAME FROM A ORDER BY ID [ASC,DESC]

TOP

显示结果集的前多少行数据

SELECT TOP 10 FROM A; 显示表中前10行数据

LIMIT 限制查询条数

LIMIT 起始位置, 行数

SELECT * FROM A LIMIT 10 取出前10行数据

SELECT * FROM A LIMIT 10,10 从第10行开始取, 共取10行

并行查询

开启参数

MAX_PARALLEL_DEGREE 并行的最大任务数

PARALLEL_POLICY 并行策略 0: 不开启 1: 自动配置并发线程个数 2: 手动指定并发个数

PARALLEL_THRD_NUM 并发的工作线程

SELECT /+ PARALLEL(16) */ * FROM A

DML 数据操作语句

INSERT 插入数据

用法

插入单行或多行数据,该方式需要插入所有列的值: INSERT INTO TB_ANME VALES(),[()]

插入单行或多行数据,该方式可以指定插入那些列: INSERT INTO TB_NAME(COL_NAME1,COL_NAME2....) VALUES(VAL1,VAL2...)

基于某个表创建一个新表: INSERT .... SELECT ...

UPDATE

用法

UPDATE TB_NAME SET 更新字段=更新的值 WHERE ....

DELETE

DELETE FROM TB_NAME WHERE .....

自增列

创建自增列

CREATE TABLE T1(

id INT IDENTITY(100,100), /* IDENTITY(起始,步长) */

name VARCHAR(12)

);

INSERT INTO T1(name) VALUES('AAA');

COMMIT;

SQL> SELECT * FROM T1;

行号 ID NAME

---------- ----------- ----

1 100 AAA

SQL> SELECT IDENT_CURRENT('SYSDBA.T1'); 获取自增列的当前值

行号 IDENT_CURRENT('SYSDBA.T1')

---------- --------------------------

1 200

SQL> SELECT IDENT_SEED('SYSDBA.T1'); 获取自增列的起始值

行号 IDENT_SEED('SYSDBA.T1')

---------- -----------------------

1 100

SQL> SELECT IDENT_INCR('SYSDBA.T1'); 获取自增列的步长

行号 IDENT_INCR('SYSDBA.T1')

---------- -----------------------

1 100

SQL> SELECT TABLE_USED_SPACE('SYSDBA','T1'); 获取当前表占用的页数量

行号 TABLE_USED_SPACE('SYSDBA','T1')

---------- -------------------------------

1 64

SQL> SELECT TABLE_USED_PAGES('SYSDBA','T1'); 表已经使用的页面数

行号 TABLE_USED_PAGES('SYSDBA','T1')

---------- -------------------------------

1 2

视图

视图的概念

1、视图是一个虚表, 不存在真实数据, 只是在读取时根据定义的语句进行查询

2、视图的数据修改,也会影响原表, 原表的修改也会影响视图查询出来的数据

创建视图

CREATE VIEW v_name AS SELECT ...

删除视图

DROP VIEW v_name;

查看视图

DBA_VIEWS

视图编译

当视图无效时, 进行

ALTER VIEW v_name COMPILE;

物化视图

1、物化视图存储的真实的数据

2、基表数据变换, 不影响物化视图

创建物化视图

CREATE MATERIALIZED VIEW MV_NAME REFRESH WITH ROWID START WITH SYSDATE NEXT SYSDATE + 1 AS SELECT ....

更新通过ROWID进行并且根据系统时间每隔一天刷新一次

删除物化视图

DROP MATERIALIZED VIEW MV_NAME

刷新物化视图

REFRESH MATERIALIZED VIEW MV_NAME [FAST , COMPLETE , FORCE]

刷新方法

FAST 根据表上的数据修改进行增量刷新

COMPLETE 通过定义物化视图的语句进行刷新

FORCE 快速刷新

ON DEMAND

ON COMMIT

触发器

创建触发器语法

CREATE [OR REPLACE] TRIGGER 触发器名[WITH ENCRYPTION]

BEFORE|AFTER|INSTEAD OF DELETE|INSERT|UPDATE [OF 列名] ON 表名

[FOR EACH ROW [WHEN 条件]]

BEGIN

DMSQL 程序语句

END;

删除触发器

DROP TRIGGER t_name;

ALTER TRIGGER t_name DISABLE; 将触发器置为失效

ALTER TRIGGER t_name ENABLE; 将触发器置为有效

重新编译

ALTER TRIGGER t_name COMPILE;

表级触发器

对于表级别触发器,只针针对表的DML操作进行触发

事件触发器

触发事件

DDL: CREATE、ALTER、DROP、GRANT、REVOKE 以及 TRUNCATE

系统级别: LOGIN/LOGON、LOGOUT/LOGOFF、AUDIT、NOAUDIT、BACKUP DATABASE、RESTORE DATABASE、TIMER、STARTUP、SHUTDOWN 以及 SERERR

时间触发器

针对某个时间点触发, 支持分钟级别触发

示例: 每个月的第 28 天，从早上 9 点开始到晚上 18 点之间，每隔一分钟就打印一个字符串 Hello World

CREATE

OR REPLACE TRIGGER timer2

AFTER

TIMER ON DATABASE FOR EACH 1 MONTH DAY 28

FROM

TIME '09:00' TO TIME '18:00' FOR EACH 1 MINUTE DECLARE str VARCHAR;

BEGIN PRINT 'HELLO WORLD';

END;

示例

CREATE TRIGGER trg_upd_score

BEFORE UPDATE ON test_trigger_1

FOR EACH ROW

BEGIN

IF NEW.score < 0 THEN

SET NEW.score=0;

ELSEIF NEW.score > 100 THEN

SET NEW.score = 100;

END IF;

END;//

delimiter ;

NEW.score 更新以后该字段的值

OLD.score 更新之前该字段的值

同义词

同义词相当于模式对象的别名,

创建同义词

SQL> CREATE TABLE F1(ID INT);

SQL> INSERT INTO F1 VALUES(1),(2);

SQL> COMMIT;

SQL> SELECT * FROM F1;

行号 ID

---------- -----------

1 1

2 2

SQL> CREATE SYNONYM s1 FOR F1;

SQL> SELECT * FROM S1;

行号 ID

---------- -----------

1 1

2 2

删除同义词

DROP SYNONYM s_name;

安全管理

安全管理功能

用户表示与鉴别通过登录账号区别各个用户, 并通过口令方式防止用户被冒充

自主访问控制通过权限管理, 使用户只能访问自己权限内的数据对象

强制访问控制通过安全标记，使用户只能访问与自己安全级别相符的数据对象

审计审计人员可以查看所有用户的操作记录，为明确事故责任提供证据支持

通信存储加密用户可以自主的将数据以密文的形式存储在数据库中。也可以对在网络上传输的数据进行加密

加密引擎用户可以用自定义的加密算法来加密自己的核心数据

资源限制可以对网络资源和磁盘资源进行配额设置，防止恶意资源抢占

客体重用实现了内存与磁盘空间的释放清理，防止信息数据的泄露

DM 数据库安全产品

标准版只包含基本安全功能

企业版功能最全

安全版增强强制访问控制, 登录限制

军用版面向军队需求的定制版

安全可信版增加环境标记, 多要素访问控制, 多线程加密

商密版本采用商业算法, 通过国密办认证

DM 多权分立

将用户分为多个角色

系统管理员 SYSDBA 身份验证, 资源限制, 数据加密, 访问控制, 客体重用

系统审计员 SYSAUDITOR 审计

系统安全员 SYSSSO 策略和标记管理

对象管理员 SYSDBO 访问对象控制

用户管理

创建用户

CREATE USER username IDENTIFIED BY password;

用户身份验证模式

数据库认证模式

使用创建用户时, 设置的密码登录

基于操作系统认证模式

开启操作系统认证参数

ENABLE_LOCAL_OSAUTH = 1 开启本地操作系统认证

ENABLE_REMOTE_OSAUTH = 1 开启远程操作系统认证

ENABLE_ENCRYPT = 1 表示使用ssl安全加密

上面的参数时静态参数, 需要重启数据库

1、开启操作系统认证需要先将系统用户添加到

useradd fangyu

groupadd dmusers

usermod -G dmusers fangyu

2、数据库中创建一个和系统用户相同名字的用户

CREATE USER fangyu IDENTIFIED BY "fangyu421";

3、登录数据库

disql /localhost:5236 users

修改用户

ALTER USER username .....

示例

限制用户的磁盘使用率

ALTER USER fangyu DISKSPACE LIMIT 128;

删除用户

DROP USER username;

CASCADE 删除用户时, 如果用户存在数据库对象则提示报错, 使用该参数则不会报错

RESTRICT 默认选项

用户资源限制

1、创建用户时指定用户所能使用的磁盘空间

CREATE USER fangyu IDENTIFIED BY fangyu421 DISKSPACE LIMIT 50; 限制用户只能使用50M的磁盘空间

CREATE USER fangyu IDENTIFIED BY fangyu421 DISKSPACE UNLIMITED;

2、限制表的磁盘空间使用率

CREATE TABLE t1(id INT , name VARCHAR(12)) DISKSPACE LIMIT 10;

ALTER TABLE t1 MODIFY DISKSPACE LIMIT 50;

3、查看用户所占用的空间

SQL> SELECT USER_USED_SPACE('fangyu');

4、查看表占用的空间

SELECT TABLE_USED_SPACE('fangyu','t1');

5、查看表使用的页数

SELECT TABLE_USED_PAGES('fangyu','t1');

6、查看索引占用的空间

SELECT INDEX_USED_SPACE(33555463); 填写索引id

7、查看索引占用的页数

SELECT INDEX_USED_PAGES(33555463);

权限管理

常用权限

CREATE SESSION: 连接数据库权限, 默认创建的用户都具有该权限

CREATE TABLE: 创建表

CREATE ANY TABLE: 在任意模式下创建表

ALTER ANY TABLE: 修改任意表

DROP ANY TABLE: 删除任意表

INSERT TABLE: 插入表记录

INSERT ANY TABLE: 向任意表插入记录

UPDATE TABLE: 更新表记录

UPDATE ANY TABLE: 更新任意表的记录

DELETE TABLE: 删除表记录

DELETE ANY TABLE: 删除任意表的记录

SELECT TABLE: 查询表记录

SELECT ANY TABLE: 查询任意表的记录

REFERENCES TABLE: 引用表

REFERENCES ANY TABLE: 引用任意表

DUMP TABLE: 导出表

DUMP ANY TABLE: 导出任意表

GRANT TABLE: 向其他用户进行表上权限的授权

GRANT ANY TABLE: 向其他用户进行任意表上权限的授权

CREATE PROCEDURE: 创建存储程序

CREATE ANY PROCEDURE: 在任意模式下创建存储程序

DROP PROCEDURE: 删除存储程序

DROP ANY PROCEDURE: 删除任意存储程序

EXECUTE PROCEDURE: 执行存储程序

EXECUTE ANY PROCEDURE: 执行任意存储程序

GRANT PROCEDURE: 向其他用户进行存储程序上权限的授权

GRANT ANY PROCEDURE: 向其他用户进行任意存储程序上权限的授权

角色管理

角色: 一组权限的集合

预定角色

管理类

DBA 数据库管理角色, 在该数据库内具备除审计和标记相关权限之外的所有权限

RESOURCE 具备部分创建数据库对象的权限, 且对其自身创建的数据库对象拥有所有的权限, 并能将其转授给其他用户

PUBLIC 只能连接数据库并访问部分数据字典在缺省情况下,

VTI 具有查询动态视图的查询权限

SVI 查看v开头视图的权限

SOI 查看系统表的权限

审计类

DB_AUDIT_ADMIN 数据库审计管理角色, 在该库内具备所有与审计相关的权限, 包括创建审计用户和进行数据库审计

DB_AUDIT_OPER 可连接数据库并进行审计操作

DB_AUDIT_PUBLIC 只能连接数据库并访问部分数据字典缺省情况下, 每个审计用户在被创建时即具有此角色

DB_AUDIT_VTI 具有查询动态视图的查询权限

DB_AUDIT_SOI 查看v开头视图的权限

DB_AUDIT_SVI 查看v开头视图的权限

安全类

DB_POLICY_ADMIN 数据库标记管理角色, 在该库内具备所有与标记相关的权限, 包括创建标记用户和管理数据库标记

DB_POLICY_OPER 可连接数据库并进行标记操作

DB_POLICY_PUBLIC 只能连接数据库并访问部分数据字典缺省情况下, 每个标记用户在被创建时即具有此角色

DB_POLICY_VTI 具有查询动态视图的查询权限

DB_POLICY_SOI 查看系统表的权限

DB_POLICY_SVI 查看v开头视图的权限

创建角色

CREATE ROLE role_name;

删除角色

DROP ROLE role_name;

禁用角色

SP_SET_ROLE('role_name',0)

授权与回收权限

授权

GRANT 权限 TO username [WITH ADMIN OPTION]

回收

REVOKE 权限 FROM username

示例

SQL> CREATE USER FYU IDENTIFIED BY "fangyu421";

SQL> GRANT RESOURCE TO FYU ;

SQL> REVOKE RESOURCE FROM FYU;

权限相关系统表

DBA_SYS_PRIVS 系统权限

DBA_TAB_PRIVS 对象权限

DBA_ROLE_PRIVS 角色

DBA_COL_PRIVS 列权限

审计

开启审计功能

SP_SET_ENABLE_AUDIT(1);

0: 关闭审计

1: 开启普通审计

2: 开启普通审计和实时审计

[dmdba@itsdata ~]$ disql

disql V8

用户名:SYSAUDITOR 开启审计必须使用SYSAUDITOR用户

密码:

SQL> SP_SET_ENABLE_AUDIT (1);

SQL>

SELECT * FROM V$DM_INI WHERE PARA_NAME='ENABLE_AUDIT'; 查看参数配置

审计级别

系统级别审计: 系统的启动和关闭

语句级别审计: 特殊的SQL

对象级别审计: 作用在特殊对象上的语句

审计配置信息存放在: SYSAUDIT系统表

语句级审计选项

配置审计TABLE

SQL> SP_AUDIT_STMT('TABLE','NULL','ALL');

SQL> select * from sysauditor.sysaudit;

行号 LEVEL UID TVPID COLID TYPE WHENEVER

---------- ----------- ----------- ----------- ----------- ----------- -----------

1 1 -1 -1 -1 15 3

/* 具体有那些参数, 参考官方文档 */

查看审计日志

SQL> select * from SYSAUDITOR."V$AUDITRECORDS";

行号 USERID USERNAME ROLEID ROLENAME IP SCHID SCHNAME OBJID OBJNAME OPERATION SUCC_FLAG SQL_TEXT DESCRIBTION OPTIME MAC SEQNO

---------- ----------- ---------- ----------- -------------- --- ----------- ------- ----------- ------- ------------ --------- ------------------------------------ ------------------------------------ -------------------------- ----------------- -----------

9 50331649 SYSDBA 67108864 DBA ::1 -1 -1 CREATE TABLE Y create table ff(id int); 2022-06-01 20:38:06.000000 00:0C:29:43:AB:87 0

对SYSDBA用户进行审计, 审计该用户创建用户的语句

SP_AUDIT_STMT('USER','SYSDBA','SUCCESSFUL');

对FANGYU用户审计, 审计更新表的与

SP_AUDIT_STMT('UPDATE TABLE','FANGYU','ALL')

SP_AUDIT_STMT 参数说明

SP_AUDIT_STMT(TYPE,USERNAME,WHENEVER)

TYPE: 审计什么操作, 具体看官方文档

USERNAME: 对那个用户进行审计, NULL: 所有用户

WHENEVER: 审计的时机, ALL: 所有 SUCCESSFUL: 操作成功时审计 FAIL: 操作失败时审计

取消审计

SP_NOAUDIT_STMT()

参数和配置审计相同

对象级别审计

配置对象审计

SP_AUDIT_OBJECT(TYPE,USERNAME,SCHNAME,TVNAME,COLNAME,WHENEVER)

TYPE 对象审计选项

USERNAME 对那个用户审计

SCHNAME 模式

TVNAME 表

COLNAME 列名

WHENEVER 审计时机

取消对象审计

SP_NOAUDIT_OBJECT() , 参数和配置审计相同

示例

SP_AUDIT_OBJECT('INSERT', 'FANGYU', 'FANGYU', 'T1', 'SUCCESSFUL');

SP_AUDIT_OBJECT('UPDATE', 'FANGYU', 'FANGYU', 'T1', 'SUCCESSFUL');

对FANGYU这个用户下FANGYU这个模式下的T1表进行 INSERT 和 UPDATE的审计, 只要成功才审计

审计文件管理

审计文件配置参数

AUD_FILE = /PATH/TO_FILE

审计日志默认存放在数据库目录下, 通过AUD_FILE可以配置存放路径

[dmdba@itsdata ~]$ cd /dmdata/itsdata/

[dmdba@itsdata itsdata]$ ls -1

AUDIT_ITSDATA_58872B9F58872B9FF52E4FAD7841ABD7_2022-6-1-17-18-19.log

AUDIT_ITSDATA_86B8D88686B8D886ABCA246A7841ABD7_2022-6-1-20-18-36.log

bak

BOOKSHOP.DBF

ctl_bak

dm.ctl

DMHR.DBF

dm.ini

dminit20220521212718.log

dm_service.prikey

HMAIN

itsdata01.log

itsdata02.log

MAIN.DBF

rep_conflict.log

ROLL.DBF

sqllog.ini

SYSTEM.DBF

TEMP.DBF

trace

[dmdba@itsdata itsdata]$

AUDIT_MAX_FILE_SIZE 指定审计日志的单个文件大小, 超过该值则自动切换, 默认100M

AUDIT_FILE_FULL_MODE = 1 删除审计文件策略 1: 删除最老的审计文件, 知道有足够的空间创建新的审计文件 2: 不在记录审计日志

审计文件删除

SP_DROP_AUDIT_FILE('指定时间',{0|1});

0 删除普通审计文件

1 删除实时审计文件

加密审计文件

SP_AUDIT_SET_ENC()

审计信息查看

SYSAUDITOR.V$AUDITRECORDS

审计分析

分析工具

1、图形化审计工具 analyzer

2、

2、命令行审计工具 dmaudtool

[dmdba@itsdata ~]$ dmaudtool USERID=SYSDBA/fangyu421 AFIL_PATH=/dmdata/itsdata OUT_PATH=/dmdata/itsdata/aud_out.txt

dmaudtool V8

Start to audit files in [/dmdata/itsdata]....

aud sys init success.

start file [/dmdata/itsdata/AUDIT_ITSDATA_58872B9F58872B9FF52E4FAD7841ABD7_2022-6-1-17-18-19.log]....

start file [/dmdata/itsdata/AUDIT_ITSDATA_86B8D88686B8D886ABCA246A7841ABD7_2022-6-1-20-18-36.log]....

audit files time used: 1.111(ms)

Analyse the audit files successfully.

[dmdba@itsdata ~]$

DM 配置文件说明

dm.ini 主配置文件

ini 参数分为手动、静态和动态三种类型

READ ONLY 手动参数, 不能被修改

IN FILE 静态参数, 可在 dm.ini 文件和内存同时修改, 修改后即时生效

SESSION 动态参数, 可在 dm.ini 文件和内存同时修改, 修改后即时生效

SYS 动态参数, 只能通过修改 dm.ini 文件进行修改, 修改后重启服务器才能生效

SYS 为系统级参数, 修改后会影响所有的会话

SESSION 为会话级参数, 服务器运行过程中被修改时, 之前创建的会话不受影响, 只有新创建的会话使用新的参数值

配置参数

用于于修改整型静态配置参数和动态配置参数

SP_SET_PARA_VALUE(scope int, paraname varchar(256), value int64)

用于修改浮点型静态配置参数和动态配置参数

SP_SET_PARA_DOUBLE_VALUE(scope int, paraname varchar(8187), value double)

用于修改浮点型静态配置参数和动态配置参数

SP_SET_PARA_STRING_VALUE(scope int, paraname varchar(8187), value double)

参数相关视图

v$dm_ini

v$parameter

dm.ini 参数说明

CTL_PATH = /dmdata/itsdata/dm.ctl 控制文件路径

CTL_BAK_PATH = /dmdata/itsdata/ctl_bak 控制文件备份路径

CTL_BAK_NUM = 10 控制文件备份数量

SYSTEM_PATH = /dmdata/itsdata 数据库安装路径

CONFIG_PATH = /dmdata/itsdata 配置文件路径

TEMP_PATH = /dmdata/itsdata 临时文件路径

BAK_PATH = /dmdata/itsdata/bak 备份目录

DFS_PATH = DFS目录

INSTANCE_NAME = ITSDATA 实例名

BUFFER = 1000 存放数据页, 相当于数据的副本, buffer实际上是一个链表

BUFFER_POOLS = 19 BUFFER的实例个数, 相当于MySQL的innodb_buffer_pool_instnaces 减少内部锁竞争

OPTIMIZER_MODE = 1 优化器探测模式, 1: 采用左深树方式进行探测 0: 采用卡特兰树方式进行探测

CACHE_POOL_SIZE = 100 用于结果集、执行计划、SQL语句的缓存, 相当于MySQL的query_cache 建议1024~2048

ENABLE_MONITOR = 1 数据库系统监控级别

MAX_OS_MEMORY = 100 使用物理内存最大百分比

MEMORY_POOL = 500 运行时内存, 相当于计算时需要消耗的内存

MEMORY_N_POOLS = 1 公共内存区域的实例个数

RECYCLE = 300 用于缓冲临时表空间, 对应的磁盘物理文件就是TEMP.DBF , 当产生的临时数据很多是, RECYCLE满了则需要写入TEMP.DBF 导致TEMP.DBF自动扩展(扩展相当于一个系统中断), 性能会下降

RECYCLE_POOLS = 19 和BUFFER_POOLS原理相同, 建议设置>100

SORT_BUF_SIZE = 20 排序缓冲区

HJ_BUF_GLOBAL_SIZE = 5000 哈希连接使用的内存大小, 整个数据库分配给哈希连接的内存

HJ_BUF_SIZE = 500 单个哈希连接使用的内存大小

HJ_BLK_SIZE = 2 当单个哈希连接使用的内存不够时, 每次申请内存时一次性申请多少

HAGR_BUF_GLOBAL_SIZE = 5000 聚集操作使用的内存大小

HAGR_BUF_SIZE = 500 单个聚集操作使用的内存大小, 例如SUM()操作

DICT_BUF_SIZE = 50 元数据信息缓存区, 可以通过查询v$db_cache视图, 如果LRU_DISCARD 很大则需要调大该值

WORKER_THREADS = 16 工作线程的个数

TASK_THREADS = 16 任务线程个数

MAX_SESSIONS = 10000 用户最大连接数, 一般设置为 2000 即可满足绝大多数应用

OLAP_FLAG = 0 是否开启OLAP环境 OLTP 系统建议设置为 2, OLAP 系统建议设置为 1

TEMP_SPACE_LIMIT = 0 临时表空间大小上限

SVR_LOG_NAME = SLOG_ALL 使用SQLLOG.INI中预设的模式的名称

SVR_LOG = 0 是否打开 SQL 日志功能, 0: 表示关闭；1: 表示打开；2: 按文件中记录数量切换日志文件, 一般生产环境建议设置为 2, 搭配参数 SVR_LOG_FILE_NUM 使用

MAL_INI = 0 是否开启 dmmal.ini

ARCH_INI = 0 是否开启 dmarch.ini

REP_INI = 0 是否开启 dmrep.ini

LLOG_INI = 0 是否开启 dmllog.ini

TIMER_INI = 0 是否开启 dmtimer.ini

MPP_INI = 0 是否开启 dmmpp.ini

DFS_INI = 0 是否开启 dmdfs.ini

MEMORY_TARGET = 15000 数据库主内存池在扩展到此大小以上后, 空闲时间收缩会此指定大小, 以 MB 为单位

MEMORY_EXTENT_SIZE = 32 共享内存池每次扩充的大小单位M

dmmal.ini MAL系统的配置文件, 所有实例需要严格一致

PWD_POLICY 设置系统默认口令策略默认: 2

PWD_MIN_LEN 设置用户口令的最小长度默认: 9 PWD_POLICY&2!=0时才有效

ENABLE_OBJ_REUSE 是否支持客体重用, 0: 不支持；1: 支持,

ENABLE_REMOTE_OSAUTH 否支持远程操作系统认证, 0: 不支持；1: 支持注: 该参数设置仅安全版有效

ENABLE_LOCAL_OSAUTH 是否支持本机操作系统认证, 0: 不支持；1: 支持

ENABLE_STRICT_CHECK 是否检查存储过程中EXECUTE IMMEDIATE语句的权限, 1: 表示检查；0: 不检查

MAC_LABEL_OPTION 用于控制SP_MAC_LABEL_FROM_CHAR过程的使用范围, 0: 只有SSO可以调用；1: 所有用户都可以调用；2: 所有用户可以调用, 但是非SSO用户不会主动创建新的LABEL, 注: 该参数设置仅安全版有效

ENABLE_EXTERNAL_CALL 是否允许创建或执行外部函数, 0: 不允许；1: 允许

ENABLE_DDL_ANY_PRIV 是否可以授予和回收DDL相关的ANY系统权限, 0: 否；1: 是

SEC_PRIV_MODE 表示权限管理模式, 0: 表示传统模式；1: 表示专用机模式；2: 表示EVAL测评模式, 注: 该参数设置仅安全版有效

MAL_CHECK_INTERVAL 检测线程检测间隔, 默认30s, 范围（0-1800）, 如果配置为0, 则表示不进行链路检测

MAL_CONN_FAIL_INTERVAL 检测线程认定链路断开的时间, 默认10s, 范围（2-1800）

MAL_LEAK_CHECK 是否打开MAL内存泄露检查（0: 关闭, 1: 打开）, 默认0

MAL_LOGIN_TIMEOUT MPP/DBLINK等实例间登录时的超时检测间隔（3-1800）, 以秒为单位, 默认15s

MAL_BUF_SIZE 单个MAL缓存大小限制, 以兆为单位, 当此MAL的缓存邮件超过此大小, 则会将邮件存储到文件中, 有效值范围（0~500000）, 默认为100

MAL_SYS_BUF_SIZE MAL 系统总内存大小限制, 单位: M, 有效值范围（0~500000）, 默认为0, 表示MAL系统无总内存限制

MAL_VPOOL_SIZE MAL 系统使用的内存初始化大小, 以兆为单位, 有效值范围（1~500000）, 默认为10, 此值一般要设置的比MAL_BUF_SIZE大一些

MAL_COMPRESS_LEVEL MAL 消息压缩等级, 取值范围0-10, 0代表不进行消息压缩, 为默认值 1 – 9表示采用zip算法, 从1到9表示压缩速度依次递减, 压缩率依次递增, 10表示采用snappy算法, 压缩速度高于zip算法, 压缩率相对低

MAL_TEMP_PATH 指定临时文件的目录, 当邮件使用的内存超过mal_buf_size或者mal_sys_buf_size时, 将新产生的邮件保存到临时文件中, 如果缺省, 则新产生的邮件保存到temp.dbf文件中

MAL_COMBIN_BUF_SIZE 指定MAL消息合并发送的缓冲区大小, 单位KB, MAL发送消息时进行合并, 减少网络发送次数, 有效值范围（0~131072）, 0表示不进行消息合并, 最小缓冲区大小128k, 当设置值大于0小于128时, 自动调整为128

MAL_MESSAGE_CHECK 是否对MAL通信消息启用消息体校验（只有当消息的发送端和接收端都配置为1才启用通信体校验）, 0: 不启用；1: 启用, 默认为1

[MAL_INST1] 实例配置 MAL_INST_NAME 实例名, MAL系统中的各数据库实例不允许同名,

MAL_HOST 实例名称

MAL_PORT MAL监听端口, 用于数据守护、DSC、MPP等环境中各节点实例之间MAL链路配置, 监听端端口配置此参数, 范围1024~65534, 发起连接端的端口在1024~65535之间随机分配

MAL_INST_PORT MAL_INST_NAME实例服务器监听通讯端口号, 服务器配置此参数, 有效值范围（1024~65534）, 发起连接端的端口在1024-65535之间随机分配此参数的配置应与DM.INI中的PORT_NUM保持一致

MAL_INST_HOST 连接数据库服务器使用的IP地址, （在MPP下以及读写分离的即时归档主备系统下一定要配置）

MAL_DW_PORT MAL_INST_NAME实例守护进程的监听端口, 其他守护进程或监视器使用MAL_HOST + MAL_DW_PORT创建与该实例守护进程的TCP连接, 监听端配置此参数, 有效值范围（1024~65534）, 发起连接端的端口在1024-65535之间随机分配

MAL_LINK_MAGIC MAL链路网段标识, 有效值范围（0-65535）, 默认0, 设置此参数时, 同一网段内的节点都设置相同, 不同网段内的节点设置的值必须不同, 搭建跨网段的DBLINK时, 如果内外网不通, 则必须配置此参数；如果内外网互通, 则可以不配

dmarch.ini 用于本地归档和远程归档

归档类型

本地归档在写入REDO日之后, 触发归档, 最多可以设置8个

实时归档在写入本地REDO日志之前, 通过MAL系统将REDO日志发送给远程服务器, 远程服务器收到REDO后, 返回确认信息主库收到确认信息后执行后续操作

即时归档主库将REDO日志写入本地REDO日志后, 通过MAL系统发送给备库

异步归档通过设置发送归档时间, 将REDO发送给备库

远程归档将REDO日志写入远程服务器

全局配置

ARCH_WAIT_APPLY 是否需要等待备库做完日志，1表示需要，0表示不需要, 默认为1

ARCH_RESERVE_TIME 归档日志保留时间，单位分钟，取值范围（0~2147483647）, 只对远程归档和本地归档有效, 服务器每隔5分钟检查是否存在超过保留时间的归档并删除, 默认为0，表示不删除归档

ARCH_LOCAL_SHARE DMDSC 集群本地归档是否共享给远程节点作为远程归档, 0表示不共享，1表示共享，默认值为0, 此参数值在DMDSC集群各个节点的配置文件中需保持一致, 配置为1时，本地归档路径和远程归档路径均需配置在ASM或共享磁盘上，并且DMARCH.INI中ARCH_INCOMING_PATH为对应节点的本地归档路径；配置为0时，REMOTE归档路径与对应节点LOCAL归档路径不能指向共享存储的同一个目录

本地归档配置

[ARCHIVE_LOCAL1]

ARCH_TYPE 归档类型

ARCH_DEST 归档路径

ARCH_FILE_SIZE 单个归档文件大小，单位MB，取值范围（64~2048），默认为1024MB，即1G

ARCH_SPACE_LIMIT 本地归档文件空间限制

ARCH_FLUSH_BUF_SIZE 归档合并刷盘缓存大小，单位MB，取值范围（0-128），默认为0，表示不使用归档合并刷盘

实时归档

[ARCHIVE_REALTIME]

ARCH_TYPE 归档类型

ARCH_DEST 归档目录实例名

异步归档

[ARCHIVE_ASYNC]

ARCH_TYPE 归档类型

ARCH_DEST 归档目录实例名

ARCH_TIMER_NAME 定时器名称

ARCH_SEND_DELAY 指定源库到异步备库的归档延时发送时间，单位为分钟，取值范围（0~1440)，默认为0，表示不启用归档延时发送功能,

即时归档

[ARCHIVE_TIMELY]

ARCH_TYPE 归档类型

ARCH_DEST 归档目录实例名

远程归档

[REMOTE]

ARCH_TYPE 归档类型

ARCH_DEST 归档目录实例名

ARCH_FILE_SIZE 单个归档文件大小，单位MB，取值范围（64~2048），默认为1024MB，即1G

ARCH_SPACE_LIMIT 远程归档文件空间限制, 当同一节点号的远程归档文件达到限制值时，系统自动删除最早生成的远程归档日志文件, 0表示无空间限制，取值范围（1024M~2147483647M），缺省为0

ARCH_INCOMING_PATH 对应远程归档存放在本节点的实际路径

ARCH_FLUSH_BUF_SIZE 归档合并刷盘缓存大小，单位MB，取值范围（0-128），默认为0，表示不使用归档合并刷盘

dm_svc.conf

作用:

各接口和客户端工具所需要配置的一些参数

实现DM各种集群的读写分离和均衡负载

sqllog.ini 用于配置sql日志

作用: 用于sql日志的配置，当且仅当INI参数SVR_LOG=1时有效

物理备份

归档配置

配置本地归档方式

1、SQL语句配置

SQL> ALTER DATABASE MOUNT;

SQL> ALTER DATABASE ADD ARCHIVELOG 'DEST=/dmarch, TYPE=local,FILE_SIZE=128,SPACE_LIMIT=2048';

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;

/* DEST=/dmarch 对应的目录后面不能有'/' 否则报错 */

2、配置文件配置

dm.ini

ARCH_INI = 1 开启归档

dmarch.ini

[ARCH_NAME] REDO日志归档名

ARCH_TYPE REDO日志归档类型, LOCAL: 本地归档, REALTIME: 实时归档

ARCH_DEST REDO日志归档目标, LOCAL: 对应本地目录, 远程归档: 对端实例名

ARCH_FILE_SIZE REDO日志大小, 默认1GB

ARCH_SPACE_LIMIT REDO日志使用的目录大小限制 0: 无限制取值范围(1024 MB~4294967294 MB) 默认: 0

ARCH_INCOMING_PATH 仅远程归档有效, 对应远程归档存放在本节点的实际路径

示例

[ARCHIVE_REALTIME]

ARCH_TYPE = REALTIME

ARCH_DEST = its2

[ARCHIVE_LOCAL1]

ARCH_TYPE = LOCAL

ARCH_DEST = /dmarch

ARCH_FILE_SIZE = 128

ARCH_SPACE_LIMIT = 10240

DMAP 服务

备份集备份还原策略有以下两种

1、DMAP: 辅助进程方式

支持第三方备份

2、无辅助进程方式

由dmserver自身执行备份还原, 不支持第三方备份

BAK_USE_AP = {0|1} 控制上面的策略方式

数据库备份命令

联机备份

全量备份

SQL> BACKUP DATABASE FULL BACKUPSET '/dmbak/full_database';

FULL: 表示全量备份, 也可以不写, 默认就是全量备份

BACKUPSET: 指定备份集路径

TO: 指定备份名称

MAXPIECESIZE: 指定备份片的大小

COMPRESS LEVEL: 备份时压缩

PARALLEL: 并行备份

BACKUPINFO: 备份描述信息

SQL> BACKUP DATABASE TO 'first_full_backup' FULL BACKUPSET '/dmbak/full_database' BACKUPINFO '第一次全量备份' MAXPIECESIZE 100 COMPRESSED LEVEL 6 PARALLEL 8

增量备份

SQL> BACKUP DATABASE INCREMENT WITH BACKUPDIR '/dmbak/full_backup' BACKUPSET '/home/dm_bak/db_increment_bak_02';

INCREMENT WITH BACKUPDIR: 指定全量备份的路径

/* 增量备份, 指定的BACKUPDIR 是指定完全备份的路径, 还是指定备份目录的路径 */

表空间备份

表空间备份限制

1、非归档模式下, 不能备份表空间

2、MOUNT状态下, 不能备份表空间

3、MPP环境下, 不能备份表空间

SQL> BACKUP TABLESPACE TBS_ITS FULL BACKUPSET '/dmbak/tablespace_its';

SQL> BACKUP TABLESPACE TBS_ITS INCREMENT WITH BACKUPDIR '/dmbak'BACKUPSET '/dmbak/tablespace_its_increment_01';

表备份

表备份限制

1、表备份只能联机备份

2、不需要配置归档日志

3、表备份不存在增量备份

SQL> BACKUP TABLE tb_name BACKUPSET '/dmbak/tb_name_bak';

备份集管理

SF_BAKSET_BACKUP_DIR_ADD 添加备份目录

SF_BAKSET_BACKUP_DIR_REMOVE 删除备份目录

SF_BAKSET_BACKUP_DIR_REMOVE_ALL 清理全部备份目录

SF_BAKSET_CHECK 备份集校验

SF_BAKSET_REMOVE 删除指定设备类型和指定备份集目录的备份集

SF_BAKSET_REMOVE_BATCH 批量删除满足指定条件的所有备份集

SP_DB_BAKSET_REMOVE_BATCH 批量删除指定时间之前的数据库备份集

SP_TS_BAKSET_REMOVE_BATCH 批量删除指定表空间对象及指定时间之前的表空间备份集

SP_TAB_BAKSET_REMOVE_BATCH 批量删除指定表对象及指定时间之前的表备份集

SP_ARCH_BAKSET_REMOVE_BATCH 批量删除指定时间之前的归档备份集

dmrman 脱机备份

全量备份: BACKUP DATABASE 'dm.ini' FULL BACKUPSET '/dmbak/full_database';

增量备份: BACKUP DATABASE 'dm.ini' INCREMENT WITH BACKUPDIR '/dmbak' BACKUPSET '/dmbak/increment_bak_01';

校验备份集: BACKUP DATABASE 'dm.ini' FULL BACKUPSET '/dmbak/full_database'; CHECK BACKUPSET '/dmbak/full_database';

删除备份集: BACKUP DATABASE 'dm.ini' BACKUPSET '/dmbak/full_database'; REMOVE BACKUPSET '/dmbak/full_database';

dexp 逻辑备份

dexp 参数

USERID 指定用户名和密码

FILE 指定导出文件的名称, 如果不指定默认: dexp.dmp

DIRECTORY 指定导出文件目录

FULL 全量备份

OWNER 导出指定用户的数据, 格式: (user1,user2,...)

SCHEMAS 导出指定模式的数据, 格式: (schema1,schema2,...)

TABLES 指定导出某个表 , 格式: (table1,table2,...)

FUZZY_MATCH 使用TABLES选项时, 是否对表名进行模糊匹配默认:N

QUERY 导出表数据时, 过滤条件

PARALLEL 并发导出

TABLE_PARALLEL 对于表进行并发导出, MPP模式下会转换成单线程

TABLE_POOL 指定表的缓冲区个数

EXCLUDE 指定导出时排除那些不导出 EXCLUDE=(CONSTRAINTS,INDEXES,ROWS,TRIGGERS,GRANTS) or EXCLUDE=TABLES:table1,table2 or EXCLUDE=SCHEMAS:sch1,sch2

INCLUDE 指定导出的对象 INCLUDE=(CONSTRAINTS,INDEXES,ROWS,TRIGGERS,GRANTS) or INCLUDE=TABLES:table1,table2

CONSTRAINTS 导出约束 (Y)

TABLESPACE 导出对象带有表空间 (N)

GRANTS 导出权限 (Y)

INDEXES 导出索引 (Y)

TRIGGERS 导出触发器 (Y)

ROWS 导出数据行 (Y)

LOG 指定日志文件

NOLOGFILE 不使用日志文件(N)

NOLOG 屏幕上不显示日志信息(N)

LOG_WRITE 日志信息实时写入文件: 是(Y),否(N)

DUMMY 交互信息处理: 打印(P), 所有交互都按YES处理(Y),NO(N)

PARFILE 参数文件名

FEEDBACK 每 x 行显示进度 (0)

COMPRESS 导出数据是否压缩 (N)

ENCRYPT 导出数据是否加密 (N)

ENCRYPT_PASSWORD 导出数据的加密密钥

ENCRYPT_NAME 加密算法的名称

FILESIZE 每个转储文件的最大大小

FILENUM 一个模板可以生成的文件数

DROP 导出后删除原表，但不级联删除 (N)

DESCRIBE 导出数据文件的描述信息，记录在数据文件中

COL_DEFAULT_SEPARATE 是否单独导出列默认值 (Y)

HELP 打印帮助信息

示例

全库导出: dexp USERID=SYSDBA/fangyu421 FULL=y FILE=full.dmp LOG=full.log DIRECTORY=/tmp

导出某个用户的数据: dexp USERID=SYSDBA/fangyu421 OWNER=fangyu FILE=fnagyu.dmp LOG=fangyu.log DIRECTORY=/tmp

导出某个模式的数据: dexp USERID=SYSDBA/fangyu421 SCHEMAS=fangyu FILE=fnagyu.dmp LOG=fangyu.log DIRECTORY=/tmp

导出指定的表数据: dexp USERID=SYSDBA/fangyu421 TABLES=t1,t2 FILE=t1_t2.dmp LOG=t1_t2.log DIRECTORY=/tmp

定时备份

1、创建作业

SP_INIT_JOB(1);

2、定义每周六晚上进行全量备份

call SP_CREATE_JOB('back_full',1,0,'',0,0,'',0,'back_full');

call SP_JOB_CONFIG_START('back_full');

call SP_ADD_JOB_STEP('back_full', 'back_full', 6, '01000000/opt/dmdbms/data/BAK', 1, 2, 0, 0, NULL, 0);

call SP_ADD_JOB_SCHEDULE('back_full', 'back_full', 1, 2, 1, 64, 0, '23:30:00', NULL, '2020-03-27 16:35:56', NULL, '');

call SP_JOB_CONFIG_COMMIT('back_full');

3、每周一、周二、周三、周四、周五、周日晚上 23：30 定时增量备份

call SP_CREATE_JOB('backup_inc',1,0,'',0,0,'',0,'backup_inc');

call SP_JOB_CONFIG_START('backup_inc');

call SP_ADD_JOB_STEP('backup_inc', 'backup_inc', 6, '11000000/opt/dmdbms/data/BAK|/opt/dmdbms/data/BAK', 1, 2, 0, 0, NULL, 0);

call SP_ADD_JOB_SCHEDULE('backup_inc', 'backup_inc', 1, 2, 1, 63, 0, '23:33:00', NULL, '2020-03-27 22:03:58', NULL, '');

call SP_JOB_CONFIG_COMMIT('backup_inc');

4、每天 00：00 删除 15 天以前的数据。删除前请先提前将备份文件转移到单独的备份服务器

call SP_CREATE_JOB('JOB_DEL_BAK_TIMELY',1,0,'',0,0,'',0,'JOB_DEL_BAK_TIMELY');

call SP_JOB_CONFIG_START('JOB_DEL_BAK_TIMELY');

call SP_ADD_JOB_STEP('JOB_DEL_BAK_TIMELY', 'JOB_DEL_BAK_TIMELY', 0, 'call SF_BAKSET_BACKUP_DIR_ADD(''DISK'', ''/data/dmdbms/data/BAK'');

CALL SP_DB_BAKSET_REMOVE_BATCH(''DISK'', NOW()-15);', 1, 2, 0, 0, NULL, 0);

call SP_ADD_JOB_SCHEDULE('JOB_DEL_BAK_TIMELY', 'JOB_DEL_BAK_TIMELY', 1, 1, 1, 0, 0, '00:00:00', NULL, '2020-03-27 22:06:13', NULL, '');

call SP_JOB_CONFIG_COMMIT('JOB_DEL_BAK_TIMELY');

物理还原

联机还原

/* DM 只支持连接还原表 */

还原表的顺序

1、备份表 BACKUP TABLE t1 BACKUPSET '/dmbak/t1';

2、对备份进行校验 SELECT SF_BAKSET_CHECK('DISK','/dmbak/t1');

3、还原表结构 RESTORE TABLE STRUCT FROM BACKUPSET '/dmbak/t1';

4、还原表数据 RESTORE TABLE FROM BACKUPSET '/dmbak/t1'

WITHOUT INDEX 指定还原时, 不重建索引

WITHOUT CONSTRAINT 指定还原表数据时, 不还原约束

脱机还原

整库还原

1、校验备份集: CHECK BACKUPSET '/dmbak/full_database';

2、还原数据库: RESTORE DATABASE 'dm.ini' FROM BACKUPSET

3、恢复数据库: RECOVER DATABASE 'dm.ini' FROM BACKUPSET

4、更新DB_MAGIC: RECOVER DATABASE 'dm.ini' UPDATE DB_MAGIC;

表空间还原

1、校验备份集: CHECK BACKUPSET '/dmbak/full_database';

2、将表空间脱机: ALTER TABLESPACE tbs_name OFFLINE;

3、还原表空间: RESTORE DATABASE 'dm.ini' TABLESPACE tbs_name FROM BACKUPSET '/dmbak/xxx';

4、恢复表空间: RECOVER DATABASE 'dm.ini' TABLESPACE tbs_name FROM BACKUPSET '/dmbak/xxx';

dimp 逻辑备份还原

整库导入

dimp USERID=SYSDBA/fangyu421 FILE=/dmbak/full.dmp LOG=full.log FULL=Y DIRECTORY=/dmbak/dimp/

FILE: 指定要导入的文件

DIRECTORY: 在导入过程中, 生成的日志文件会存放在该目录下

定时备份和删除备份

1、使用manager工具备份

2、使用命令行备份

1、创建作业

SP_INIT_JOB(1);

2、定义每周六晚上进行全量备份

call SP_CREATE_JOB('back_full',1,0,'',0,0,'',0,'back_full');

call SP_JOB_CONFIG_START('back_full');

call SP_ADD_JOB_STEP('back_full', 'back_full', 6, '01000000/opt/dmdbms/data/BAK', 1, 2, 0, 0, NULL, 0);

call SP_ADD_JOB_SCHEDULE('back_full', 'back_full', 1, 2, 1, 64, 0, '23:30:00', NULL, '2020-03-27 16:35:56', NULL, '');

call SP_JOB_CONFIG_COMMIT('back_full');

3、每周一、周二、周三、周四、周五、周日晚上 23：30 定时增量备份

call SP_CREATE_JOB('backup_inc',1,0,'',0,0,'',0,'backup_inc');

call SP_JOB_CONFIG_START('backup_inc');

call SP_ADD_JOB_STEP('backup_inc', 'backup_inc', 6, '11000000/opt/dmdbms/data/BAK|/opt/dmdbms/data/BAK', 1, 2, 0, 0, NULL, 0);

call SP_ADD_JOB_SCHEDULE('backup_inc', 'backup_inc', 1, 2, 1, 63, 0, '23:33:00', NULL, '2020-03-27 22:03:58', NULL, '');

call SP_JOB_CONFIG_COMMIT('backup_inc');

4、每天 00：00 删除 15 天以前的数据。删除前请先提前将备份文件转移到单独的备份服务器

call SP_CREATE_JOB('JOB_DEL_BAK_TIMELY',1,0,'',0,0,'',0,'JOB_DEL_BAK_TIMELY');

call SP_JOB_CONFIG_START('JOB_DEL_BAK_TIMELY');

call SP_ADD_JOB_STEP('JOB_DEL_BAK_TIMELY', 'JOB_DEL_BAK_TIMELY', 0, 'call SF_BAKSET_BACKUP_DIR_ADD(''DISK'', ''/data/dmdbms/data/BAK'');

CALL SP_DB_BAKSET_REMOVE_BATCH(''DISK'', NOW()-15);', 1, 2, 0, 0, NULL, 0);

call SP_ADD_JOB_SCHEDULE('JOB_DEL_BAK_TIMELY', 'JOB_DEL_BAK_TIMELY', 1, 1, 1, 0, 0, '00:00:00', NULL, '2020-03-27 22:06:13', NULL, '');

call SP_JOB_CONFIG_COMMIT('JOB_DEL_BAK_TIMELY');

更多详情请访问达梦在线服务平台达梦数据库 - 新一代大型通用关系型数据库 | 达梦在线服务平台

fangyu_7362

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
达梦数据库-日常管理

类似MySQL中的database , 用户可以创建多个database, 在DM中表示为模式, 默认情况下当创建用户时, 则默认会创建一个同名的模式CREATE SCHEMA s1 AUTHORIZATION fangyu; 在manager中可以创建臣成功, 但是disql中创建不成功SQL> drop schema s1 cascade;操作已执行已用时间: 22.799(毫秒). 执行号:1000.SQL> CREATE SCHEMA s1 AUTHORIZATION fangyu;2 3 4
复制链接

扫一扫