使用dmrestore执行指定时间点或LSN还原
达梦系统使用归档日志将数据库还原到最新的状态，这一节讨论与归档日志相关的另一个功能：还原到指定的时间点。根据用户需求，可以将数据库还原到指定的时间点。还原到时间点的功能依赖于归档日志，在备份完成时，系统会记录一个备份时间，因此要还原的时间点一定在备份完成时间之后，否则系统会提示报错信息。借助备份文件完成还原后，开始重做归档日志，区别于完全还原将所有可用的归档日志全部重做，若指定还原到时间点，则只重做早于时间点的日志，从而达到将数据库还原到指定时间的状态的目的。需要注意的是，由于表空间只是数据库的一部分，为保证还原后，数据库中的所有数据处于最新状态，还原表空间会重做该表空间所有可用的归档日志，因此还原表空间不支持还原到时间点功能。
比如用户在2020-7-21 19:40:20时对数据库jydm做了一个备份，在2020-7-21 20:34:20想将数据库jydm还原到时间点2020-07-21 20:00:03，若用户保证从备份完成的时间点到指定时间点这段时间的归档日志都完好，则通过如下还原命令就能达到预期效果。
下面举例说明
1.在2020-7-21 19:40:20时对数据库jydm进行备份
SQL> backup database full to full_bak_for_arch_restor bakfile '/dm_home/dmdba/dmdbms/backup/full_bak_for_arch_restor.bak'; executed successfully used time: 00:00:07.697. Execute id is 43. SQL> select * from t1; LINEID C1 C2 ---------- ----------- ----------- 1 1 1 2 2 2 3 3 3 4 4 4 5 5 5 6 6 6 6 rows got used time: 0.454(ms). Execute id is 97. SQL> select sysdate from dual; LINEID SYSDATE ---------- --------------------------- 1 2020-07-21 20:00:03.165365 used time: 0.994(ms). Execute id is 337. 使用select file_lsn from v$rlog命令查询此时的LSN为:30043605 SQL> select file_lsn from v$rlog; LINEID FILE_LSN ---------- -------------------- 1 30043605 used time: 1.344(ms). Execute id is 654.
2.删除表t1中的数据
SQL> delete from t1; affect rows 6 used time: 1.309(ms). Execute id is 681. SQL> commit; executed successfully used time: 33.649(ms). Execute id is 682. SQL> select * from t1; no rows used time: 18.944(ms). Execute id is 696.
3.将数据库还原到指定时间2020-07-21 20:00:03.165365
[root@shard1 oracle]# service DmServicejydm stop Redirecting to /bin/systemctl stop DmServicejydm.service [dmdba@shard1 backup]$ dmrestore ini_path=/dm_home/dmdba/dmdbms/data/jydm/dm.ini file=/dm_home/dmdba/dmdbms/backup/full_bak_for_arch_restor.bak archive_dir=/dm_home/dmdba/dmdbms/data/arch time="2020-07-21 20:00:03.165365"; restore V7.1.6.46-Build(2018.02.08-89107)ENT file dm.key not found, use default license!backup sig: BA backup tool version: 12595 backup db name: jydm backup db magic: 1371967312 backup pemnt magic: 1250320462 backup name: FULL_BAK_FOR_ARCH_RESTOR backup type: full backup level: online backup range: database backup description: compressed level: 0 encrypt_type: 0 encrypt_id: 2052 rac node: 0 page check: 0 rlog encrypt: 0 external cipher id: 0 external hash id: 0 length in char: 0 use new hash: 1 backup time: 2020-07-21 19:52:09 page size: 8 KB extent size: 16 case sensitive: 1 log page size: 512 B charset: 0 data version: 0x7000A sys version: V7.1.6.46-Build(2018.02.08-89107)ENT enable policy: 0 mpp_timestamp: 1595332328 crc_check: TRUE parallel type: 0 parallel info len: 0 backup db fil num: 9 archive flag: 1 backup with log: Yes before backup LSN: 30040400 after backup LSN: 30040406 $bak_seq |$file_path |$size(K) |$used(K) 1|/dm_home/dmdba/dmdbms/backup/full_bak_for_arch_restor.bak| 393216.00| 362036.00 $file_seq |$type|$ts_name |$state |$size(K) |$seq_in_bak |$off_in_bak(B) |$len_in_bak(B) |$file_path |$mirror_path 1|DBF |SYSTEM |ONLINE | 23552| 1| 43008| 6766592|/dm_home/dmdba/dmdbms/data/jydm/SYSTEM.DBF | 2|DBF |ROLL |ONLINE | 229376| 1| 6809600| 10887168|/dm_home/dmdba/dmdbms/data/jydm/ROLL.DBF | 3|DBF |MAIN |ONLINE | 276480| 1| 17696768| 240287744|/dm_home/dmdba/dmdbms/data/jydm/MAIN.DBF | 4|DBF |BOOKSHOP |ONLINE | 153600| 1| 257984512| 933888|/dm_home/dmdba/dmdbms/data/jydm/BOOKSHOP.DBF | 5|DBF |DMHR |ONLINE | 131072| 1| 258918400| 425984|/dm_home/dmdba/dmdbms/data/jydm/DMHR.DBF | 6|DBF |USERS |ONLINE | 51200| 1| 259344384| 1105920|/dm_home/dmdba/dmdbms/data/jydm/users01.dbf | 7|DBF |SYSAUX |ONLINE | 179200| 1| 260450304| 110256128|/dm_home/dmdba/dmdbms/data/jydm/SYSAWR.DBF | 8|DBF |FG_PERSON |ONLINE | 131072| 1| 370706432| 16384|/dm_home/dmdba/dmdbms/data/jydm/FG_PERSON.DBF | 9|LOG |ARCHLOG |ONLINE | 0| 1| 370722816| 2048| | Continue?[Y/N]:Y can't find useable archive file when search assigned archive directory restore bak_file: /dm_home/dmdba/dmdbms/backup/full_bak_for_arch_restor.bak ... start restore database... start restore file: /dm_home/dmdba/dmdbms/data/jydm/SYSTEM.DBF(SYSTEM), pages: 826 end restore file: /dm_home/dmdba/dmdbms/data/jydm/SYSTEM.DBF(SYSTEM) start restore file: /dm_home/dmdba/dmdbms/data/jydm/ROLL.DBF(ROLL), pages: 1329 end restore file: /dm_home/dmdba/dmdbms/data/jydm/ROLL.DBF(ROLL) start restore file: /dm_home/dmdba/dmdbms/data/jydm/MAIN.DBF(MAIN), pages: 29332 end restore file: /dm_home/dmdba/dmdbms/data/jydm/MAIN.DBF(MAIN) start restore file: /dm_home/dmdba/dmdbms/data/jydm/BOOKSHOP.DBF(BOOKSHOP), pages: 114 end restore file: /dm_home/dmdba/dmdbms/data/jydm/BOOKSHOP.DBF(BOOKSHOP) start restore file: /dm_home/dmdba/dmdbms/data/jydm/DMHR.DBF(DMHR), pages: 52 end restore file: /dm_home/dmdba/dmdbms/data/jydm/DMHR.DBF(DMHR) start restore file: /dm_home/dmdba/dmdbms/data/jydm/users01.dbf(USERS), pages: 135 end restore file: /dm_home/dmdba/dmdbms/data/jydm/users01.dbf(USERS) start restore file: /dm_home/dmdba/dmdbms/data/jydm/SYSAWR.DBF(SYSAUX), pages: 13459 end restore file: /dm_home/dmdba/dmdbms/data/jydm/SYSAWR.DBF(SYSAUX) start restore file: /dm_home/dmdba/dmdbms/data/jydm/FG_PERSON.DBF(FG_PERSON), pages: 2 end restore file: /dm_home/dmdba/dmdbms/data/jydm/FG_PERSON.DBF(FG_PERSON) redo tmp arch file: /dm_home/dmdba/dmdbms/backup/RES_TMP_ARCH_20200721200858135_0.log, rpages: 16 end restore database data files. Apply archive log LSN from 30040407 to 30040406, time used:0.000s. restore finished, code = 0! restore successfully! restore time used: 7652.178(ms)
4.检查表t1的数据是否已经恢复回来了
[root@shard1 oracle]# service DmServicejydm start Redirecting to /bin/systemctl start DmServicejydm.service SQL> select * from t1; LINEID C1 C2 ---------- ----------- ----------- 1 1 1 2 2 2 3 3 3 4 4 4 5 5 5 6 6 6 6 rows got used time: 0.440(ms). Execute id is 86.
达梦系统使用归档还原，还可以指定还原特定的END_LSN，备份文件中会记录一个备份结束的LSN，如果指定END_LSN，则必须保证该END_LSN大于备份文件中的记录的最后一个LSN，否则会无视该END_LSN，而还原到最新状态，如果同时指定了TIME则会以最早的为标准。
5.上面还原数据库jydm时除了可以指定时间外也可以指定LSN：30043605
[root@shard1 oracle]# service DmServicejydm stop Redirecting to /bin/systemctl stop DmServicejydm.service [dmdba@shard1 backup]$ dmrestore ini_path=/dm_home/dmdba/dmdbms/data/jydm/dm.ini file=/dm_home/dmdba/dmdbms/backup/full_bak_for_arch_restor.bak archive_dir=/dm_home/dmdba/dmdbms/data/arch end_lsn=30043605 restore V7.1.6.46-Build(2018.02.08-89107)ENT file dm.key not found, use default license!backup sig: BA backup tool version: 12595 backup db name: jydm backup db magic: 1371967312 backup pemnt magic: 1250320462 backup name: FULL_BAK_FOR_ARCH_RESTOR backup type: full backup level: online backup range: database backup description: compressed level: 0 encrypt_type: 0 encrypt_id: 2052 rac node: 0 page check: 0 rlog encrypt: 0 external cipher id: 0 external hash id: 0 length in char: 0 use new hash: 1 backup time: 2020-07-21 19:52:09 page size: 8 KB extent size: 16 case sensitive: 1 log page size: 512 B charset: 0 data version: 0x7000A sys version: V7.1.6.46-Build(2018.02.08-89107)ENT enable policy: 0 mpp_timestamp: 1595332328 crc_check: TRUE parallel type: 0 parallel info len: 0 backup db fil num: 9 archive flag: 1 backup with log: Yes before backup LSN: 30040400 after backup LSN: 30040406 $bak_seq |$file_path |$size(K) |$used(K) 1|/dm_home/dmdba/dmdbms/backup/full_bak_for_arch_restor.bak| 393216.00| 362036.00 $file_seq |$type|$ts_name |$state |$size(K) |$seq_in_bak |$off_in_bak(B) |$len_in_bak(B) |$file_path |$mirror_path 1|DBF |SYSTEM |ONLINE | 23552| 1| 43008| 6766592|/dm_home/dmdba/dmdbms/data/jydm/SYSTEM.DBF | 2|DBF |ROLL |ONLINE | 229376| 1| 6809600| 10887168|/dm_home/dmdba/dmdbms/data/jydm/ROLL.DBF | 3|DBF |MAIN |ONLINE | 276480| 1| 17696768| 240287744|/dm_home/dmdba/dmdbms/data/jydm/MAIN.DBF | 4|DBF |BOOKSHOP |ONLINE | 153600| 1| 257984512| 933888|/dm_home/dmdba/dmdbms/data/jydm/BOOKSHOP.DBF | 5|DBF |DMHR |ONLINE | 131072| 1| 258918400| 425984|/dm_home/dmdba/dmdbms/data/jydm/DMHR.DBF | 6|DBF |USERS |ONLINE | 51200| 1| 259344384| 1105920|/dm_home/dmdba/dmdbms/data/jydm/users01.dbf | 7|DBF |SYSAUX |ONLINE | 179200| 1| 260450304| 110256128|/dm_home/dmdba/dmdbms/data/jydm/SYSAWR.DBF | 8|DBF |FG_PERSON |ONLINE | 131072| 1| 370706432| 16384|/dm_home/dmdba/dmdbms/data/jydm/FG_PERSON.DBF | 9|LOG |ARCHLOG |ONLINE | 0| 1| 370722816| 2048| | Continue?[Y/N]:Y can't find useable archive file when search assigned archive directory restore bak_file: /dm_home/dmdba/dmdbms/backup/full_bak_for_arch_restor.bak ... start restore database... start restore file: /dm_home/dmdba/dmdbms/data/jydm/SYSTEM.DBF(SYSTEM), pages: 826 end restore file: /dm_home/dmdba/dmdbms/data/jydm/SYSTEM.DBF(SYSTEM) start restore file: /dm_home/dmdba/dmdbms/data/jydm/ROLL.DBF(ROLL), pages: 1329 end restore file: /dm_home/dmdba/dmdbms/data/jydm/ROLL.DBF(ROLL) start restore file: /dm_home/dmdba/dmdbms/data/jydm/MAIN.DBF(MAIN), pages: 29332 end restore file: /dm_home/dmdba/dmdbms/data/jydm/MAIN.DBF(MAIN) start restore file: /dm_home/dmdba/dmdbms/data/jydm/BOOKSHOP.DBF(BOOKSHOP), pages: 114 end restore file: /dm_home/dmdba/dmdbms/data/jydm/BOOKSHOP.DBF(BOOKSHOP) start restore file: /dm_home/dmdba/dmdbms/data/jydm/DMHR.DBF(DMHR), pages: 52 end restore file: /dm_home/dmdba/dmdbms/data/jydm/DMHR.DBF(DMHR) start restore file: /dm_home/dmdba/dmdbms/data/jydm/users01.dbf(USERS), pages: 135 end restore file: /dm_home/dmdba/dmdbms/data/jydm/users01.dbf(USERS) start restore file: /dm_home/dmdba/dmdbms/data/jydm/SYSAWR.DBF(SYSAUX), pages: 13459 end restore file: /dm_home/dmdba/dmdbms/data/jydm/SYSAWR.DBF(SYSAUX) start restore file: /dm_home/dmdba/dmdbms/data/jydm/FG_PERSON.DBF(FG_PERSON), pages: 2 end restore file: /dm_home/dmdba/dmdbms/data/jydm/FG_PERSON.DBF(FG_PERSON) redo tmp arch file: /dm_home/dmdba/dmdbms/backup/RES_TMP_ARCH_20200721201304925_0.log, rpages: 16 end restore database data files. Apply archive log LSN from 30040407 to 30040406, time used:0.000s. restore finished, code = 0! restore successfully! restore time used: 7764.389(ms)
6.检查表t1的数据是否已经恢复回来了
[root@shard1 oracle]# service DmServicejydm start Redirecting to /bin/systemctl start DmServicejydm.service SQL> select * from t1; LINEID C1 C2 ---------- ----------- ----------- 1 1 1 2 2 2 3 3 3 4 4 4 5 5 5 6 6 6 6 rows got used time: 0.445(ms). Execute id is 47.
可以看到指定时间或LSN可以达到同样的效果。