达梦数据库物理备份根据日志归档文件指定时间点恢复数据

联机备份前开始本地归档，或远程归档。

SQL> alter database mount;
executed successfully
used time: 375.687(ms). Execute id is 0.
SQL> alter database add archivelog 'dest=/home/dmdba/dmdbms/arch,type=local,file_size=128,space_limit=1024';
executed successfully
used time: 1.488(ms). Execute id is 0.
SQL> alter database archivelog;
executed successfully
used time: 5.601(ms). Execute id is 0.
SQL> alter database open;
executed successfully
used time: 363.241(ms). Execute id is 0.

SQL> backup database backupset '/home/dmdba/dmdbms/bak/';
executed successfully
used time: 00:00:02.324. Execute id is 53400.

创建表test，并插入一条数据后，删除该表，记录删除该表的时间。

SQL> create table test(id int);
executed successfully
used time: 13.468(ms). Execute id is 53401.
SQL> insert into test values(1);
affect rows 1

used time: 1.989(ms). Execute id is 53402.
SQL> commit;
executed successfully
used time: 1.411(ms). Execute id is 53403.
SQL> 
SQL> drop table test;
executed successfully
used time: 63.507(ms). Execute id is 53404.
SQL> select sysdate;
LINEID     SYSDATE            
---------- -------------------
1          2021-10-21 15:01:10

used time: 5.402(ms). Execute id is 53405.

数据还原三部曲：

1. 根据备份文件进行还原。
2. 根据本地归档日志，将数据库恢复至截止的时间点"2021-10-21 15:01:00"。
3. 修改db_magic。

[dmdba@localhost bin]$ ./dmrman 
dmrman V8
RMAN> restore database '/home/dmdba/dmdbms/data/DAMENG/dm.ini' from backupset '/home/dmdba/dmdbms/bak/'
restore database '/home/dmdba/dmdbms/data/DAMENG/dm.ini' from backupset '/home/dmdba/dmdbms/bak/'
file dm.key not found, use default license!
[Percent:0.00%][Speed:0.00M/s][Cost:00:00:01][Remaining:00:00:00]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:02.393
RMAN> #预估下删除前的时间
RMAN> recover database '/home/dmdba/dmdbms/data/DAMENG/dm.ini' with archivedir '/home/dmdba/dmdbms/arch' until time '2021-10-21 15:01:00' 
recover database '/home/dmdba/dmdbms/data/DAMENG/dm.ini' with archivedir '/home/dmdba/dmdbms/arch' until time '2021-10-21 15:01:00'
Database mode = 0, oguid = 0
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
EP[0]'s cur_lsn[25023], file_lsn[25023]
EP:0 total 0 pkgs applied, percent: 0%
EP:0 total 2 pkgs applied, percent: 10%
EP:0 total 4 pkgs applied, percent: 21%
EP:0 total 6 pkgs applied, percent: 31%
EP:0 total 8 pkgs applied, percent: 42%
EP:0 total 10 pkgs applied, percent: 52%
EP:0 total 12 pkgs applied, percent: 63%
EP:0 total 14 pkgs applied, percent: 73%
EP:0 total 16 pkgs applied, percent: 84%
EP:0 total 18 pkgs applied, percent: 94%
EP:0 total 19 pkgs applied, percent: 100%
recover successfully!
time used: 328.915(ms)
RMAN> recover database '/home/dmdba/dmdbms/data/DAMENG/dm.ini' update db_magic
recover database '/home/dmdba/dmdbms/data/DAMENG/dm.ini' update db_magic
Database mode = 0, oguid = 0
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
EP[0]'s cur_lsn[26408], file_lsn[26408]
recover successfully!
time used: 00:00:01.017

恢复后，启动数据库实例，通过disql登陆查询表test。

[dmdba@localhost bin]$ ./disql SYSDBA/SYSDBA:5236

Server[LOCALHOST:5236]:mode is normal, state is open
login used time : 2.428(ms)
disql V8
SQL> select * from test;

LINEID     ID         
---------- -----------
1          1

used time: 3.691(ms). Execute id is 400.

[dmdba@localhost bin]$ ./disql SYSDBA/SYSDBA:5236

Server[LOCALHOST:5236]:mode is normal, state is open
login used time : 3.634(ms)
disql V8
SQL> select file_lsn from v$rlog;

LINEID     FILE_LSN            
---------- --------------------
1          29276

used time: 30.352(ms). Execute id is 400.
SQL> 

查看备份文件中日志备份的lsn

RMAN> show backupset '/home/dmdba/dmdbms/bak/'

指定lsn恢复，会将数据恢复至lsn(29275)前。

recover database '/home/dmdba/dmdbms/data/DAMENG/dm.ini' with archivedir '/home/dmdba/dmdbms/arch' until lsn 29275

其余步骤同上。

[-718]: Archive log collected not consecusive
[-718]：收集的存档日志不连续

当恢复数据时遇到以上问题，可以通过DMRMAN对归档日志进行修复。

RMAN> REPAIR ARCHIVELOG DATABASE '/home/dmdba/dmdbms/data/DAMENG/dm.ini'
REPAIR ARCHIVELOG DATABASE '/home/dmdba/dmdbms/data/DAMENG/dm.ini'
Database mode = 0, oguid = 0
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
EP[0]'s cur_lsn[35285], file_lsn[35285]
repair archive log successfully.
repair time used: 169.456(ms)
time used: 170.227(ms)

想更多了解达梦数据库，请关注达梦云适配中心。
https://eco.dameng.com/