热门搜索
分布式
数据库一体机
云数据库
建设银行
保险核心
密集交易
登录
注册
DM删除用户后的不完全恢复测试
培训园地/
文章详情
/
DM删除用户后的不完全恢复测试
codePanda
2026/04/28
471
0
0
本实验再单机DM8.4测试，需要数据库开启归档和备份策略。
一、备份与数据准备
1、全备前数据（FULLBAK_TAB）
第一次模拟全备数据 前dbmt.fullbak_tab 表插入 10000 行模拟数据 （全备前执行），作为数据库最近一次的全量备份。
CREATE TABLE dbmt.fullbak_tab (
id number(10),
name VARCHAR2(100), -- 姓名
sfz VARCHAR2(20) -- 身份证号
);
-- 插入 10000 行模拟数据
INSERT INTO dbmt.fullbak_tab (id,name, sfz)
SELECT rownum,
-- 随机生成中文姓名
SUBSTR(base.surnames, FLOOR(DBMS_RANDOM.VALUE(1, 61)), 1)
|| SUBSTR(base.first_names, FLOOR(DBMS_RANDOM.VALUE(1, 81)), 1)
|| CASE
WHEN DBMS_RANDOM.VALUE(0, 1) > 0.5
THEN SUBSTR(base.second_names, FLOOR(DBMS_RANDOM.VALUE(1, 41)), 1)
ELSE ''
END AS name,
-- 生成身份证号（前6位固定为421125）
'421125'
|| TO_CHAR(
base.start_date + FLOOR(DBMS_RANDOM.VALUE(0, (base.end_date - base.start_date) + 1)),
'YYYYMMDD'
)
|| LPAD(FLOOR(DBMS_RANDOM.VALUE(0, 10000)), 4, '0') AS sfz
FROM (SELECT LEVEL AS n
FROM dual
CONNECT BY LEVEL <= 10000) num_seq, -- 10000
(SELECT
'赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳' AS surnames,
'伟芳娜秀英敏静丽娟英华慧巧美静文萍玲芳燕敏玲晓锋刚勇毅俊峰强军平保东文辉力明永健世广志义兴良海山仁波宁贵福生龙元' AS first_names,
'伟芳娜秀英敏静丽娟英华慧巧美静文萍玲芳燕敏玲晓锋刚勇毅俊峰强军平保东文辉力明' AS second_names,
TO_DATE('1900-01-01', 'YYYY-MM-DD') AS start_date,
TO_DATE('2025-12-31', 'YYYY-MM-DD') AS end_date
FROM dual) base;
commit;
-- 全备数据库
BACKUP DATABASE BACKUPSET '/u01/dmbak/DAMENG/bak/backup_set_0_full_20260204' TASK THREAD 4 parallel 8;
2、增量备份前数据（INCBAK_TAB）
第二次次模拟增量数据 dbmt.incbak_tab 插入 10000 行模拟数据 （增备份前执行），插入 10000 行数据同上（略），做为数据库全被之后一天后的增量备份。
执行增量备份：
backup database increment base on backupset
'/u01/dmbak/DAMENG/bak/backup_set_0_full_20260204'
to "db_incr_01"
backupset '/u01/dmbak/DAMENG/bak/backup_set_1_inc_20260204'
TASK THREAD 4 parallel 8;
3、归档日志数据（ARCHLOG_TAB）
第三次模拟增量数据 dbmt.archlog_tab 插入 10000 行模拟数据（增备后删用户前执行）
checkpoint(100);
alter system switch logfile;
二、删除用户
删除用户DBMT，此时最近的一次全备
drop user dbmt cascade;
三、确定用户删除时间
select *
from V$DMSQL_DDL_HISTORY
where lower(SQL_TEXT) like lower('%drop user%')
or lower(SQL_TEXT) like lower('%drop schema%');
SEQNO SESS_ID SESS_SEQ TRX_ID EXEC_ID VM_ID STKFRM_LEVEL DDL_FROM SQL_TEXT DDL_TIME MAL_ID MPP_EXEC_ID
----------- -------------------- ----------- -------------------- -------------------- ----------- ------------ -------- -------------------------- -------------------------- -------------------- --------------------
8365 140390469806424 381 1384059 44141 425 1 EXEC DROP SCHEMA "DBMT" CASCADE 2026-02-04 20:26:50.000000 0 4294967295
8083 140389801864824 391 1383054 45300 438 1 EXEC DROP SCHEMA "U2" CASCADE 2026-02-04 19:39:58.000000 0 4294967295
3630 140389808418168 16 1372933 1534 18 1 EXEC DROP SCHEMA "HOJ" CASCADE 2026-02-02 21:40:13.000000 0 4294967295
结果：2026-02-04 20:26:50
四、恢复方案一：新实例恢复
查看目的数据库的参数信息
-- 数据库授权信息查询
select id_code as "版本号"
,CASE SERVER_TYPE WHEN '1' THEN '正式版' WHEN '2' THEN '测试版' WHEN '3' THEN '试用版' WHEN '4' THEN '其他' END AS "产品类型"
,TO_CHAR(EXPIRED_DATE) AS "有效日期"
--,OS_TYPE AS "授权系统"
,NVL(TO_CHAR(CONCURRENCY_USER_NUMBER),'') AS "并发用户数"
,EXPIRED_DATE as "有效期"
,CLUSTER_TYPE AS "授权集群"
,SERIES_NO
,cast(PAGE()/1024 as varchar) as "页大小"
, cast(SF_GET_EXTENT_SIZE() as varchar) as "簇大小"
,cast(case SF_GET_CASE_SENSITIVE_FLAG()when '1' then '是' when '0' then '否' end as varchar) as "大小写敏感"
,CASE SF_GET_UNICODE_FLAG() WHEN '0' THEN 'GBK18030' WHEN '1' then 'UTF-8' when '2' then 'EUC-KR' end as "字符集"
,cast(case (select BLANK_PAD_MODE()) when '1' then '是' when '0' then '否' end as varchar) as "空格填充模式"
,cast(case SF_GET_LENGTH_IN_CHAR()when '1' then '是' when '0' then '否' end as varchar) AS "以字符为单位"
,(SELECT COUNT(*) FROM v$rlogfile) AS "日志文件个数"
,(SELECT CAST(RLOG_SIZE/1024/1024 AS VARCHAR)FROM v$rlogfile WHERE ROWNUM = 1) AS "日志文件大小(MB)"
from V$LICENSE;
1、创建恢复实例
假设待恢复数据库页大小32KB，簇大小32页，大小写敏感，字符集UTF-8，可以在本地另起一个端口建实例恢复，节省备份和归档的传输时间（内存、空间不够或者NAS盘除外）；
./dminit path=/u01/dmdata \ DB_NAME=DMINRECY \ INSTANCE_NAME=DMINRECY \ page_size=32 extent_size=32 \ PORT_NUM=5238 \ CASE_SENSITIVE=1 CHARSET=1 \ log_size=4096 \ SYSDBA_PWD=xxxxxx SYSAUDITOR_PWD=xxxxxx
2、恢复备份
restore database '/u01/dmdata/DMINRECY/dm.ini'
from backupset '/u01/dmbak/DAMENG/bak/backup_set_1_inc_20260204'
WITH BACKUPDIR '/u01/dmbak/DAMENG/bak'
TASK THREAD 8 parallel 8;
3、不完全恢复（按时间点）
RECOVER DATABASE '/u01/dmdata/DMINRECY/dm.ini'
WITH ARCHIVEDIR '/u01/dmarch/DAMENG'
UNTIL TIME '2026-02-04 20:26:50';
4、更新 DB_MAGIC
recover database '/u01/dmdata/DMINRECY/dm.ini' update db_magic;
5、启动数据库
./dmserver /u01/dmdata/DMINRECY/dm.ini disql -L /:5238 as sysdba
五、恢复结果验证
1、用户
被删除的用户和权限已经恢复了
@us dbmt
Show database usernames from dba_users matching %dbmt%
USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE TEMPORARY_TABLESPACE USER_ID CREATED PROFILE
-------- -------------- ------------------ -------------------- ----------- -------------------------- -------
DBMT OPEN MAIN TEMP 50331748 2025-09-23 14:23:58.894120 NULL
DBMT2 OPEN MAIN TEMP 50331771 2026-01-21 17:21:46.775722 NULL
@privs dbmt
GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE
------- ------------ ------------ ------------
DBMT DBA N NULL
DBMT PUBLIC N NULL
DBMT2 PUBLIC N NULL
已用时间: 5.984(毫秒). 执行号:1111.
GRANTEE PRIVILEGE ADMIN_OPTION
------- -------------- ------------
DBMT CREATE SESSION NO
DBMT2 CREATE SESSION NO
已用时间: 2.571(毫秒). 执行号:1112.
2、表
FULLBAK_TAB、INCBAK_TAB、ARCHLOG_TAB建立的三个表和数据已经恢复出来了
@tab dbmt.%
OWNER TABLE_NAME TYPE NUM_ROWS BLOCKS EMPTY AVGSPC ROWLEN LAST_ANALYZED DEGREE COMPRESSION
----- ----------- ---- -------- ------ ----- ------ ------ ------------- ------ -----------
DBMT TEST_LIKE IOT 1000 32 1 NULL NULL 2026-01-21 1 DISABLED
DBMT DEMO01 IOT 1000 32 1 NULL NULL 2026-01-21 1 DISABLED
DBMT CALLINFO PIOT 2 5 0 NULL NULL 2026-01-21 1 DISABLED
DBMT FULLBAK_TAB IOT NULL NULL NULL NULL NULL NULL 1 DISABLED
DBMT INCBAK_TAB IOT NULL NULL NULL NULL NULL NULL 1 DISABLED
DBMT ARCHLOG_TAB IOT NULL NULL NULL NULL NULL NULL 1 DISABLED
6 rows got
dmdba@DAMENG:5238 SQL> select COUNT(*) from dbmt.FULLBAK_TAB;
COUNT(*)
--------------------
10000
已用时间: 3.510(毫秒). 执行号:1107.
dmdba@DAMENG:5238 SQL> select COUNT(*) from dbmt.INCBAK_TAB;
COUNT(*)
--------------------
10000
已用时间: 2.080(毫秒). 执行号:1108.
dmdba@DAMENG:5238 SQL> select COUNT(*) from dbmt.ARCHLOG_TAB;
COUNT(*)
--------------------
10000
六、恢复方案二：原库恢复
shutdown immediate;
restore database '/u01/dmdata/DAMENG/dm.ini' from backupset '/u01/dmbak/DAMENG/bak/backup_set_1_inc_20260204' WITH BACKUPDIR '/u01/dmbak/DAMENG/bak' TASK THREAD 8 parallel 8;
RECOVER DATABASE '/u01/dmdata/DAMENG/dm.ini' WITH ARCHIVEDIR '/u01/dmarch/DAMENG' UNTIL TIME'2026-02-04 20:26:50';
recover database '/u01/dmdata/DAMENG/dm.ini' update db_magic;
执行过程
RMAN> restore database '/u01/dmdata/DAMENG/dm.ini' from backupset '/u01/dmbak/DAMENG/bak/backup_set_1_inc_20260204' WITH BACKUPDIR '/u01/dmbak/DAMENG/bak' TASK THREAD 8 parallel 8;
restore database '/u01/dmdata/DAMENG/dm.ini' from backupset '/u01/dmbak/DAMENG/bak/backup_set_1_inc_20260204' WITH BACKUPDIR '/u01/dmbak/DAMENG/bak' TASK THREAD 8 parallel 8;
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:09][Remaining:00:00:00]
restore successfully.
time used: 00:00:09.317
RMAN> RMAN> RECOVER DATABASE '/u01/dmdata/DAMENG/dm.ini' WITH ARCHIVEDIR '/u01/dmarch/DAMENG' UNTIL TIME'2026-02-04 20:26:50';
RECOVER DATABASE '/u01/dmdata/DAMENG/dm.ini' WITH ARCHIVEDIR '/u01/dmarch/DAMENG' UNTIL TIME '2026-02-04 20:26:50';
[Percent:2.89%][Speed:0.00PKG/s][Cost:00:00:00][Remaining:00:00:00]
recover successfully!
time used: 00:00:01.240
RMAN> RMAN> recover database '/u01/dmdata/DAMENG/dm.ini' update db_magic;
recover database '/u01/dmdata/DAMENG/dm.ini' update db_magic;
recover successfully!
time used: 00:00:01.238
恢复结果验证
dmdba@DAMENG:5236 SQL> @tab dbmt.%
OWNER TABLE_NAME TYPE NUM_ROWS BLOCKS EMPTY AVGSPC ROWLEN LAST_ANALYZED DEGREE COMPRESSION
----- ----------- ---- -------- ------ ----- ------ ------ ------------- ------ -----------
DBMT TEST_LIKE IOT 1000 32 1 NULL NULL 2026-01-21 1 DISABLED
DBMT DEMO01 IOT 1000 32 1 NULL NULL 2026-01-21 1 DISABLED
DBMT CALLINFO PIOT 2 5 0 NULL NULL 2026-01-21 1 DISABLED
DBMT FULLBAK_TAB IOT NULL NULL NULL NULL NULL NULL 1 DISABLED
DBMT INCBAK_TAB IOT NULL NULL NULL NULL NULL NULL 1 DISABLED
DBMT ARCHLOG_TAB IOT NULL NULL NULL NULL NULL NULL 1 DISABLED
6 rows got
dmdba@DAMENG:5236 SQL> @privs dbmt
GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE
------- ------------ ------------ ------------
DBMT DBA N NULL
DBMT PUBLIC N NULL
DBMT2 PUBLIC N NULL
已用时间: 3.843(毫秒). 执行号:612.
GRANTEE PRIVILEGE ADMIN_OPTION
------- -------------- ------------
DBMT CREATE SESSION NO
DBMT2 CREATE SESSION NO
已用时间: 3.462(毫秒). 执行号:613.
未选定行
已用时间: 67.655(毫秒). 执行号:610.
dmdba@DAMENG:5236 SQL> @o dbmt.%
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE STATUS OID D_OID CREATED LAST_DDL_TIME
----- ---------------------- -------------- --------------- ------ --------- ----- -------------------------- --------------------------
DBMT ARCHLOG_TAB NULL TABLE VALID 31425 NULL 2026-02-04 20:20:53.506000 2026-02-04 20:20:53.000000
DBMT CALLINFO PMAX TABLE PARTITION VALID 1495 NULL 2025-10-31 09:22:23.746112 2025-10-31 09:22:23.000000
DBMT CALLINFO P5 TABLE PARTITION VALID 1494 NULL 2025-10-31 09:22:23.746016 2025-10-31 09:22:23.000000
DBMT CALLINFO P4 TABLE PARTITION VALID 1493 NULL 2025-10-31 09:22:23.745920 2025-10-31 09:22:23.000000
DBMT CALLINFO P3 TABLE PARTITION VALID 1492 NULL 2025-10-31 09:22:23.745824 2025-10-31 09:22:23.000000
DBMT CALLINFO P1_2 TABLE PARTITION VALID 1491 NULL 2025-10-31 09:22:23.745711 2025-10-31 09:22:23.000000
DBMT CALLINFO NULL TABLE VALID 1490 NULL 2025-10-31 09:22:23.739000 2025-10-31 09:22:23.000000
DBMT DBMT NULL SCH VALID 150995945 NULL 2025-09-23 14:23:58.891034 NULL
DBMT DEMO01 NULL TABLE VALID 1034 NULL 2025-10-13 09:38:32.373000 2025-10-13 09:38:32.000000
DBMT FULLBAK_TAB NULL TABLE VALID 31423 NULL 2026-02-04 20:05:10.817000 2026-02-04 20:05:10.000000
DBMT INCBAK_TAB NULL TABLE VALID 31424 NULL 2026-02-04 20:12:45.213000 2026-02-04 20:12:45.000000
DBMT INDEX33555487 NULL INDEX VALID 33555487 NULL 2025-09-23 14:29:18.887228 NULL
DBMT INDEX33555491 NULL INDEX VALID 33555491 NULL 2025-10-13 09:38:32.375760 NULL
DBMT INDEX33556158 NULL INDEX VALID 33556158 NULL 2025-10-31 09:22:23.745522 NULL
DBMT INDEX33556159_33556158 NULL INDEX VALID 33556159 NULL 2025-10-31 09:22:23.745753 NULL
DBMT INDEX33556160_33556158 NULL INDEX VALID 33556160 NULL 2025-10-31 09:22:23.745859 NULL
DBMT INDEX33556161_33556158 NULL INDEX VALID 33556161 NULL 2025-10-31 09:22:23.745956 NULL
DBMT INDEX33556162_33556158 NULL INDEX VALID 33556162 NULL 2025-10-31 09:22:23.746051 NULL
DBMT INDEX33556163_33556158 NULL INDEX VALID 33556163 NULL 2025-10-31 09:22:23.746203 NULL
DBMT INDEX33610599 NULL INDEX VALID 33610599 NULL 2026-02-04 20:05:10.819892 NULL
DBMT INDEX33610600 NULL INDEX VALID 33610600 NULL 2026-02-04 20:12:45.216004 NULL
DBMT INDEX33610601 NULL INDEX VALID 33610601 NULL 2026-02-04 20:20:53.509165 NULL
DBMT TEST_LIKE NULL TABLE VALID 1030 NULL 2025-09-23 14:29:18.885000 2025-09-23 14:29:18.000000
23 rows got
已用时间: 65.355(毫秒). 执行号:611.
dmdba@DAMENG:5236 SQL> select COUNT(*) from dbmt.FULLBAK_TAB;
COUNT(*)
--------------------
10000
已用时间: 4.058(毫秒). 执行号:605.
dmdba@DAMENG:5236 SQL> select COUNT(*) from dbmt.INCBAK_TAB;
COUNT(*)
--------------------
10000
已用时间: 4.565(毫秒). 执行号:606.
dmdba@DAMENG:5236 SQL> select COUNT(*) from dbmt.INCBAK_TAB;
COUNT(*)
--------------------
10000
已用时间: 0.257(毫秒). 执行号:607.
数据正常恢复。
七、报错处理
报错：磁盘空间不足
[Percent:0.00%][Speed:0.00M/s][Cost:00:00:02][Remaining:00:00:00]
==============================Error Stack==============================
DM:restore create file(file_name: /u01/dmdata/DMINRECY/ROLL.DBF, file_len: 4362076160, group_id: 1, file_id: 0) error
DM[-523]:磁盘空间不足
=======================================================================
RMAN> RMAN>
排查
[dmdba@panda101:/u01]$ du -sm *|sort -n
du: 无法读取目录"mysql/mysql-8.0.11-linux-glibc2.12-x86_64/data": 权限不够
1 testdd
128 test
1112 dmdrs
1405 soft
1783 dm
2359 mysql
5636 dmbak
21780 dmdata
40195 dmarch
归档目录/u01/dmarch占用较大，清理归档日志
find /u01/dmarch/DAMENG -name "ARCHIVE_LOCAL*.log" -mtime +7 -exec rm {} \;
八、关键点总结
- 必须开启归档有完整备份链（全备 + 增量）
- 恢复时间点必须在删除前，避免重新执行了删除用户这一步操作
- 推荐新实例恢复，避免污染原库
- 磁盘空间必须提前检查
评论
后发表回复
作者
文章
阅读量
获赞
扫一扫
联系客服
联系客服
鄂Copyright © 达梦国产化应用创新实验室
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1