DM7使用DMRMAN恢复数据库到指定时间点/LSN

使用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.

发表评论

电子邮件地址不会被公开。