使用DMRMAN恢复数据库到指定时间点/LSN
恢复数据库到指定时间点/LSN是从归档恢复的一种方式，也称为不完全恢复。从归档恢复允许恢复到指定的时间点及指定的LSN值。若同时指定了时间点和LSN，则以较早的为结束点。用户可以通过指定一个时间点/LSN，使数据库恢复到这个指定的时间点/LSN。
例如，用户在下午5点做了一个误操作，删除了某些重要数据；我们可以指定恢复时间点到下午4:59分，恢复被误删除的数据。
下面以联机数据库备份为例说明如何恢复数据库到指定的时间点/LSN。
1) 准备数据。
SQL> create table tab_for_recover_01(c1 int); executed successfully used time: 62.140(ms). Execute id is 29. SQL> insert into tab_for_recover_01 values(1); affect rows 1 used time: 1.765(ms). Execute id is 30. SQL> commit; executed successfully used time: 1.582(ms). Execute id is 31.
2) 备份数据库。
SQL> backup database full to db_rac_full_bak_for_time_lsn backupset '/dm7/backup/dm_rac_full_bak_for_time_lsn'; executed successfully used time: 00:00:01.687. Execute id is 32.
3) 正确操作数据库，产生一些归档。
SQL> create table tab_for_recover_02(c1 int); executed successfully used time: 12.213(ms). Execute id is 33. SQL> insert into tab_for_recover_02 values(1); affect rows 1 used time: 1.654(ms). Execute id is 34. SQL> commit; executed successfully used time: 1.331(ms). Execute id is 35.
使用select sysdate命令查询此时的时间为：2020-06-19 15:23:17.809757
SQL> select sysdate; LINEID SYSDATE ---------- --------------------------- 1 2020-06-19 15:23:17.809757 used time: 1.571(ms). Execute id is 36.
使用select file_lsn from v$rlog命令查询此时的LSN为:80126
SQL> select file_lsn from v$rlog; LINEID FILE_LSN ---------- -------------------- 1 80126 used time: 1.490(ms). Execute id is 37.
4) 误操作数据库。此步骤误删除了表tab_for_recover_01中数据。
SQL> delete from tab_for_recover_01; affect rows 1 used time: 1.798(ms). Execute id is 38. SQL> commit; executed successfully used time: 1.435(ms). Execute id is 39.
使用select sysdate命令查询此时的时间为：2020-06-19 15:25:56.982456
SQL> select sysdate; LINEID SYSDATE ---------- --------------------------- 1 2020-06-19 15:25:56.982456 used time: 0.898(ms). Execute id is 40.
使用select file_lsn from v$rlog命令查询此时的LSN为:80131
SQL> select file_lsn from v$rlog; LINEID FILE_LSN ---------- -------------------- 1 80131 used time: 0.536(ms). Execute id is 41.
5)将备份文件与归档日志传输到目标库(这里不在源库进行恢复)
[dmdba@dmrac1 backup]$ scp -r dm_rac_full_bak_for_time_lsn/ dmdba@10.138.130.187:/dm_home/dmdbms/backup/ dmdba@10.138.130.187's password: dm_rac_full_bak_for_time_lsn.bak 100% 25MB 25.5MB/s 00:00 dm_rac_full_bak_for_time_lsn_1.bak 100% 20KB 20.0KB/s 00:00 dm_rac_full_bak_for_time_lsn.meta 100% 81KB 80.5KB/s 00:00 [dmdba@dmrac1 backup]$
这里是RAC环境，需要将两个实例的归档都要传输到目标库
[dmdba@dmrac1 data]$ scp -r rac0_arch dmdba@10.18.10.187:/dm_home/dmdbms/backup/ dmdba@10.18.10.187's password: ARCHIVE_LOCAL1_20200617163125381_0.log 100% 256MB 85.3MB/s 00:03 ARCHIVE_LOCAL1_20200617154121539_0.log 100% 8704 8.5KB/s 00:00 ARCHIVE_LOCAL1_20200615164953273_0.log 100% 160KB 160.0KB/s 00:00 ARCHIVE_LOCAL1_20200615165648166_0.log 100% 157KB 157.0KB/s 00:00 ARCHIVE_LOCAL1_20200528202150715_0.log 100% 277KB 277.0KB/s 00:00 ARCHIVE_LOCAL1_20200608151836879_0.log 100% 158KB 157.5KB/s 00:00 ARCHIVE_LOCAL1_20200609150852829_0.log 100% 157KB 157.0KB/s 00:00 ARCHIVE_LOCAL1_20200608152638617_0.log 100% 159KB 158.5KB/s 00:00 ARCHIVE_LOCAL1_20200609170732487_0.log 100% 161KB 160.5KB/s 00:00 ARCHIVE_LOCAL1_20200615172117341_0.log 100% 159KB 158.5KB/s 00:00 ARCHIVE_LOCAL1_20200615171042444_0.log 100% 159KB 159.0KB/s 00:00 ARCHIVE_LOCAL1_20200605154214367_0.log 100% 157KB 157.0KB/s 00:00 ARCHIVE_LOCAL1_20200616164818700_0.log 100% 160KB 160.0KB/s 00:00 [dmdba@dmrac1 data]$ scp -r rac1_arch dmdba@10.18.10.187:/dm_home/dmdbms/backup/ dmdba@10.18.10.187's password: ARCH_REMOTE1_20200608152641970_1.log 100% 158KB 158.0KB/s 00:00 ARCH_REMOTE1_20200609170737070_1.log 100% 159KB 159.0KB/s 00:00 ARCH_REMOTE1_20200609150857056_1.log 100% 158KB 158.0KB/s 00:00 ARCH_REMOTE1_20200616164822181_1.log 100% 256MB 85.3MB/s 00:03 ARCH_REMOTE1_20200615172121038_1.log 100% 158KB 158.0KB/s 00:00 ARCH_REMOTE1_20200608151838201_1.log 100% 5632 5.5KB/s 00:00 ARCH_REMOTE1_20200528202146001_1.log 100% 158KB 158.0KB/s 00:00 ARCH_REMOTE1_20200615164957743_1.log 100% 158KB 158.0KB/s 00:00 ARCH_REMOTE1_20200605154211189_1.log 100% 159KB 159.0KB/s 00:00 ARCH_REMOTE1_20200615165652504_1.log 100% 158KB 158.0KB/s 00:00 ARCH_REMOTE1_20200615171046600_1.log 100% 158KB 158.0KB/s 00:00 [dmdba@dmrac1 data]$
6) 还原数据库。步骤4为误操作，因此我们需要将数据库恢复到步骤3的状态。首先需要还原数据库:
RMAN> restore database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' from backupset '/dm_home/dmdbms/backup/dm_rac_full_bak_for_time_lsn'; restore database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' from backupset '/dm_home/dmdbms/backup/dm_rac_full_bak_for_time_lsn'; file dm.key not found, use default license! checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[4]. checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[3]. checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[2]. checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[1]. checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[0]. checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running, write dmrman info. RESTORE DATABASE CHECK...... RESTORE DATABASE , dbf collect...... RESTORE DATABASE , dbf refresh ...... RESTORE BACKUPSET [/dm_home/dmdbms/backup/dm_rac_full_bak_for_time_lsn] START...... total 5 packages processed... RESTORE DATABASE , UPDATE ctl file...... RESTORE DATABASE , REBUILD key file...... RESTORE DATABASE , CHECK db info...... RESTORE DATABASE , UPDATE db info...... total 6 packages processed! CMD END.CODE:[0] restore successfully. time used: 16581.139(ms)
7) 恢复数据库到指定时间点/LSN。还原后数据库的数据与备份时一致，如果要恢复数据库至步骤3的状态可以指定UNTIL TIME或UNTIL LSN参
数重做部分归档。
使用RECOVER DATABASE…UNTIL TIME命令恢复到指定的时间：
RMAN> recover database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' with archivedir '/dm_home/dmdbms/backup/rac0_arch' , '/dm_home/dmdbms/backup/rac1_arch' until time '2020-06-19 15:23:17.809757'; recover database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' with archivedir '/dm_home/dmdbms/backup/rac0_arch' , '/dm_home/dmdbms/backup/rac1_arch' until time '2020-06-19 15:23:17.809757'; checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[4]. checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[3]. checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[2]. checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[1]. checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[0]. checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running, write dmrman info. EP[0] max_lsn: 80023 EP 0's ckpt_lsn = 80054 min_ckpt_lsn = 80054 The RAC redo archive log 1 ptx The RAC redo archive log 2 ptx The RAC redo archive log 3 ptx The RAC redo archive log 4 ptx The RAC redo archive log 5 ptx The RAC redo archive log 6 ptx The RAC redo archive log 7 ptx The RAC redo archive log 8 ptx The RAC redo archive log 9 ptx The RAC redo archive log 10 ptx The RAC redo archive log 11 ptx The RAC redo archive log 12 ptx The RAC redo archive log 13 ptx The RAC redo archive log 14 ptx The RAC redo archive log 15 ptx The RAC redo archive log 16 ptx The RAC redo archive log 17 ptx The RAC redo archive log 18 ptx The RAC redo archive log 19 ptx The RAC redo archive log 20 ptx The RAC redo archive log 21 ptx The RAC redo archive log 22 ptx The RAC redo archive log 23 ptx The RAC redo archive log 24 ptx The RAC redo archive log 25 ptx The RAC redo archive log 26 ptx The RAC redo archive log 27 ptx The RAC redo archive log 28 ptx The RAC redo archive log 29 ptx The RAC redo archive log 30 ptx The RAC redo archive log 31 ptx The RAC redo archive log 32 ptx The RAC redo archive log 33 ptx The RAC redo archive log 34 ptx The RAC redo archive log 35 ptx The RAC redo archive log 36 ptx The RAC redo archive log 37 ptx The RAC redo archive log 38 ptx The RAC redo archive log 39 ptx The RAC redo archive log 40 ptx The RAC redo archive log 41 ptx The RAC redo archive log 42 ptx The RAC redo archive log 43 ptx The RAC redo archive log 44 ptx The RAC redo archive log 45 ptx The RAC redo archive log 46 ptx The RAC redo archive log 47 ptx The RAC redo archive log 48 ptx The RAC redo archive log 49 ptx The RAC redo archive log 50 ptx The RAC redo archive log 51 ptx The RAC redo archive log 52 ptx The RAC redo archive log 53 ptx The RAC redo archive log 54 ptx The RAC redo archive log 55 ptx The RAC redo archive log 56 ptx The RAC redo archive log 57 ptx The RAC redo archive log 58 ptx The RAC redo archive log 59 ptx The RAC redo archive log 60 ptx The RAC redo archive log 61 ptx The RAC redo archive log 62 ptx The RAC redo archive log 63 ptx The RAC redo archive log 64 ptx The RAC redo archive log 65 ptx The RAC redo archive log 66 ptx The RAC redo archive log 67 ptx The RAC redo archive log 68 ptx The RAC redo archive log 69 ptx The RAC redo archive log 70 ptx The RAC redo archive log 71 ptx The RAC redo archive log 72 ptx The RAC redo archive log 73 ptx The RAC redo archive log 74 ptx The RAC redo archive log 75 ptx The RAC redo archive log 76 ptx The RAC redo archive log 77 ptx The RAC redo archive log 78 ptx The RAC redo archive log 79 ptx The RAC redo archive log 80 ptx The RAC redo archive log 81 ptx The RAC redo archive log 82 ptx The RAC redo archive log 83 ptx The RAC redo archive log 84 ptx The RAC redo archive log 85 ptx The RAC redo archive log 86 ptx The RAC redo archive log 87 ptx The RAC redo archive log 88 ptx The RAC redo archive log 89 ptx The RAC redo archive log 90 ptx The RAC redo archive log 91 ptx The RAC redo archive log 92 ptx The RAC redo archive log 93 ptx The RAC redo archive log 94 ptx The RAC redo archive log 95 ptx The RAC redo archive log 96 ptx The RAC redo archive log 97 ptx The RAC redo archive log 98 ptx The RAC redo archive log 99 ptx The RAC redo archive log 100 ptx The RAC redo archive log 101 ptx The RAC redo archive log 102 ptx The RAC redo archive log 103 ptx The RAC redo archive log 104 ptx The RAC redo archive log 105 ptx The RAC redo archive log 106 ptx The RAC redo archive log 107 ptx The RAC redo archive log 108 ptx The RAC redo archive log 109 ptx The RAC recover total redo 109 ptx EP(0) slot ctl page(1, 0, 16) trxid = 10105 EP(1) slot ctl page(1, 0, 17) trxid = 8457 EP[0] Recover LSN from 80054 to 80130. EP[1] Recover LSN from 80024 to 80023. Recover from archive log finished, time used:0.147s. recover successfully! time used: 7027.267(ms)
或者使用RECOVER DATABASE…UNTIL TIME命令恢复到指定的LSN：
RMAN> recover database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' with archivedir '/dm_home/dmdbms/backup/rac0_arch' , '/dm_home/dmdbms/backup/rac1_arch' until lsn 80126; recover database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' with archivedir '/dm_home/dmdbms/backup/rac0_arch' , '/dm_home/dmdbms/backup/rac1_arch' until lsn 80126; checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[4]. checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[3]. checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[2]. checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[1]. checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[0]. checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running, write dmrman info. EP[0] max_lsn: 80023 EP 0's ckpt_lsn = 80054 min_ckpt_lsn = 80054 The RAC redo archive log 1 ptx The RAC redo archive log 2 ptx The RAC redo archive log 3 ptx The RAC redo archive log 4 ptx The RAC redo archive log 5 ptx The RAC redo archive log 6 ptx The RAC redo archive log 7 ptx The RAC redo archive log 8 ptx The RAC redo archive log 9 ptx The RAC redo archive log 10 ptx The RAC redo archive log 11 ptx The RAC redo archive log 12 ptx The RAC redo archive log 13 ptx The RAC redo archive log 14 ptx The RAC redo archive log 15 ptx The RAC redo archive log 16 ptx The RAC redo archive log 17 ptx The RAC redo archive log 18 ptx The RAC redo archive log 19 ptx The RAC redo archive log 20 ptx The RAC redo archive log 21 ptx The RAC redo archive log 22 ptx The RAC redo archive log 23 ptx The RAC redo archive log 24 ptx The RAC redo archive log 25 ptx The RAC redo archive log 26 ptx The RAC redo archive log 27 ptx The RAC redo archive log 28 ptx The RAC redo archive log 29 ptx The RAC redo archive log 30 ptx The RAC redo archive log 31 ptx The RAC redo archive log 32 ptx The RAC redo archive log 33 ptx The RAC redo archive log 34 ptx The RAC redo archive log 35 ptx The RAC redo archive log 36 ptx The RAC redo archive log 37 ptx The RAC redo archive log 38 ptx The RAC redo archive log 39 ptx The RAC redo archive log 40 ptx The RAC redo archive log 41 ptx The RAC redo archive log 42 ptx The RAC redo archive log 43 ptx The RAC redo archive log 44 ptx The RAC redo archive log 45 ptx The RAC redo archive log 46 ptx The RAC redo archive log 47 ptx The RAC redo archive log 48 ptx The RAC redo archive log 49 ptx The RAC redo archive log 50 ptx The RAC redo archive log 51 ptx The RAC redo archive log 52 ptx The RAC redo archive log 53 ptx The RAC redo archive log 54 ptx The RAC redo archive log 55 ptx The RAC redo archive log 56 ptx The RAC redo archive log 57 ptx The RAC redo archive log 58 ptx The RAC redo archive log 59 ptx The RAC redo archive log 60 ptx The RAC redo archive log 61 ptx The RAC redo archive log 62 ptx The RAC redo archive log 63 ptx The RAC redo archive log 64 ptx The RAC redo archive log 65 ptx The RAC redo archive log 66 ptx The RAC redo archive log 67 ptx The RAC redo archive log 68 ptx The RAC redo archive log 69 ptx The RAC redo archive log 70 ptx The RAC redo archive log 71 ptx The RAC redo archive log 72 ptx The RAC redo archive log 73 ptx The RAC redo archive log 74 ptx The RAC redo archive log 75 ptx The RAC redo archive log 76 ptx The RAC redo archive log 77 ptx The RAC redo archive log 78 ptx The RAC redo archive log 79 ptx The RAC recover total redo 79 ptx EP(0) slot ctl page(1, 0, 16) trxid = 10104 EP(1) slot ctl page(1, 0, 17) trxid = 8457 EP[0] Recover LSN from 80054 to 80126. EP[1] Recover LSN from 80024 to 80023. Recover from archive log finished, time used:0.145s. recover successfully! time used: 7026.956(ms)
8)在目标库验证表tab_for_recover_01中的数据
[root@dmks init.d]# service DmServicedmrc start Starting DmServicedmrc: [ OK ] [dmdba@dmks ~]$ disql SYSDBA/SYSDBA@localhost:5336 Server[localhost:5336]:mode is normal, state is open login used time: 10.592(ms) disql V7.1.6.46-Build(2018.02.08-89107)ENT Connected to: DM 7.1.6.46 SQL> select * from tab_for_recover_01; LINEID C1 ---------- ----------- 1 1 used time: 2.879(ms). Execute id is 3.