达梦数据库DM8在WINDOWS上通过命令使用时间点进行不完整恢复

郑全

一、环境

操作系统：win11
数据库版本：dm8

数据库名：DAMENG
数据库目录：c:\dmdbs\data
归档：d:\arch_log
备份目录：d:\backup\dameng_full,D:\backup\arch_bak

二、准备数据
   create table emp(empid number,last_name varchar(20));
   SQL> insert into emp values(100,'abc');
   SQL> insert into emp values(101,'wangjia');
   SQL> insert into emp values(102,'liuzeyu');
   SQL> insert into emp values(103,'zhangxinyue');
   SQL> insert into emp values(104,'yangshuang');
   SQL> insert into emp values(105,'xiaolongqy');
   SQL> insert into emp values(106,'zhangqi');
   SQL> insert into emp values(107,'wangwang');
   SQL> insert into emp values(108,'tongyi');
   SQL> insert into emp values(109,'kangshifu');

   记录下当前的时间,等会我们就恢复到这个时间点

   SQL> select now();

行号       NOW()
---------- --------------------------
1          2025-03-09 14:52:55.455000

已用时间: 0.514(毫秒). 执行号:529.
或是记录当前的LSN,还可以通过LSN进行不完全恢复
SELECT FILE_LSN FROM V$RLOG


三、进行数据库备份

备份数据库和归档日志
SQL> backup database full backupset 'D:\backup\dameng_full\fullbak_20250309';
操作已执行
已用时间: 00:00:05.284. 执行号:808.

SQL> backup archivelog all delete input backupset 'D:\backup\arch_bak\arch_20250309';
操作已执行
已用时间: 00:00:05.071. 执行号:811.

四、删除表
SQL> drop table emp;
操作已执行
已用时间: 56.222(毫秒). 执行号:530.
SQL> select * from emp;
select * from emp;
第1 行附近出现错误[-2106]:无效的表或视图名[EMP].
已用时间: 0.770(毫秒). 执行号:0.
SQL> select now();

行号       NOW()
---------- --------------------------
1          2025-03-09 15:18:56.387000



五、基于时间点恢复

1.停掉当前实例
exit
Server is stopping...

2.删除之前实例的目录
cd C:\dmdbms\data
rmdir DAMENG

3.初始化一个实例,实例名与之前一致
c:\dmdbms\bin>dminit.exe path=c:\dmdbms\data db_name=DAMENG instance_name=DAMENG SYSDBA_PWD='sztech_4U' sysauditor_pwd='szgtech_4U'

initdb V8
db version: 0x7000c
file dm.key not found, use default license!
License will expire on 2025-12-25
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL

log file path: c:\dmdbms\data\DAMENG\DAMENG01.log


log file path: c:\dmdbms\data\DAMENG\DAMENG02.log

write to dir [c:\dmdbms\data\DAMENG].
create dm database success. 2025-03-09 15:22:41

c:\dmdbms\bin>

4.使用归档日志和全备进行还原

使用dmrman 完成

c:\dmdbms\bin>dmrman
dmrman V8

#还原归档日志

RMAN> restore archivelog from backupset 'D:\backup\arch_bak\arch_20250309' to archivedir 'd:\arch_log\' ;   

restore archivelog from backupset 'D:\backup\arch_bak\arch_20250309' to archivedir 'd:\arch_log\';
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:00][Remaining:00:00:00]
restore successfully.
time used: 275.587(ms)

#还原备份集

RMAN> restore database 'C:\dmdbms\data\DAMENG\dm.ini' from backupset 'D:\backup\dameng_full\fullbak_20250309'  

restore database 'C:\dmdbms\data\DAMENG\dm.ini' from backupset 'D:\backup\dameng_full\fullbak_20250309'
file dm.key not found, use default license!
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:02][Remaining:00:00:00]
restore successfully.
time used: 00:00:03.201

#通过归档日志进行不完全恢复

RMAN> recover database 'C:\dmdbms\data\DAMENG\dm.ini' with archivedir 'd:\arch_log' until time '2025-03-09 14:52:55.455000';     

recover database 'C:\dmdbms\data\DAMENG\dm.ini' with archivedir 'd:\arch_log' until time '2025-03-09 14:52:55.455000';
Database mode = 2, oguid = 0
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
EP[0]'s cur_lsn[59163], file_lsn[59163]
[Percent:79.31%][Speed:0.00PKG/s][Cost:00:00:00][Remaining:00:00:00]
recover successfully!
time used: 796.950(ms)

说明:
1.若不带until time 就是完全恢复.
2.不完全恢复包括until time和until lsn,使用 SELECT FILE_LSN FROM V$RLOG查LSN


# 更新 magic

RMAN> recover database 'C:\dmdbms\data\DAMENG\dm.ini' update db_magic;      

recover database 'C:\dmdbms\data\DAMENG\dm.ini' update db_magic;
Database mode = 2, oguid = 0
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
EP[0]'s cur_lsn[59350], file_lsn[59350]
recover successfully!
time used: 00:00:01.342
RMAN>

六、验证
1.启动数据库
c:\dmdbms\bin>dmserver.exe path=c:\dmdbms\data\DAMENG\dm.ini

2.验证
disql /nolog
sql>conn sysdba;


SQL> select now();

行号       NOW()
---------- --------------------------
1          2025-03-09 17:02:26.715000

已用时间: 1.428(毫秒). 执行号:601.
SQL> select * from emp;

行号       EMPID LAST_NAME
---------- ----- -----------
1          100   abc
2          101   wangjia
3          102   liuzeyu
4          103   zhangxinyue
5          104   yangshuang
6          105   xiaolongqy
7          106   zhangqi
8          107   wangwang
9          108   tongyi
10         109   kangshifu

10 rows got

已用时间: 0.525(毫秒). 执行号:602.
SQL>

可以看到数据已经恢复了.