DM8误删除数据的找回方法演练

1 场景介绍
今天一位客户找到我说他的生产环境中两张表在两天前被误删除了，现在要找回这两张表。其实本来有三种方法：第一种就是用闪回功能，但是dm8最大只能闪回24小时内的数据；第二种就是用生产环境的两天前最新的全备加上这两天以来至今所有的连续的完整归档在测试服务器（一般是虚拟的一台跟生产环境平台一样配置差不多的虚拟机）做一个不完全恢复到表被删除的时间点之前的时间点，再把恢复出来的表用dexp/dimp导出和导入到生产库中，这种方法要求生产库在表删除之前有做全备，删除之后归档要连续。第三种方法就只有看业务那边有没有做另外的记录来找回这两张表了。
以上三种方法，一般我们采用第二种，下面我们就来演示第二种方法。

2 源库数据准备
2.1 打开归档
归档的路径为：/dm/data/dmdata/arch
记住这个路径有用，后面恢复需要

2.2 做一次全备
可以做热备，也可以做冷备，生产环境一般是做的热备，因为我们有自动做备份的JOB。
做完备份就做一次归档切换，模拟生产环境：alter database archivelog current;

备份的目录为：/dm/data/dmdata/DMDB/bak
记住这个有用，后面还原需要

2.3 删除数据
1.先记录此时的时间：select sysdate;
SQL>2021-03-25 21:17:51
2.删除一张表;
drop table “DMDB”.“TABLE_1”;
3.做一次归档切换，模拟生产环境：alter database archivelog current;

3 测试环境准备
3.1 安装数据库
安装的数据库版本最好跟生产环境一样

3.2 初始化测试库
初始化的参数要跟生产环境一样，如下所示：
这是生产环境的参数：

这是测试库的初始化：
[dmdba@dmm bin]$./dminit path=/dm/data/dmdata page_size=16 case_sensitive=1 charset=1 extent_size=16 db_name=DMDB instance_name=DMSERVER

启停测试库看有没有问题，一般都不会有问题

3.3 拷贝备份和归档到测试库
1.在测试库新建备份和归档的路径：
[dmdba@dmm ~]$ mkdir -p /dm/data/dmdata/arch
[dmdba@dmm ~]$ mkdir -p /dm/data/dmdata/DMDB/bak

2.将主库在删除目标表前最近的全备和自全备以来连续的归档文件拷贝到以上两个目录，注意：考过去之后一定要把每一个文件的权限都要授权给dmdba用户！不然还原恢复会报错。

3.4 开始做还原和恢复
恢复：

[dmdba@dmm bin]$ ./dmrman
dmrman V8RMAN> restore database ‘/dm/data/dmdata/DMDB/dm.ini’ from backupset ‘/dm/data/dmdata/DMDB/bak/DB_DMDB_FULL_2021_03_25_21_17_25’;
restore database ‘/dm/data/dmdata/DMDB/dm.ini’ from backupset ‘/dm/data/dmdata/DMDB/bak/DB_DMDB_FULL_2021_03_25_21_17_25’;
RESTORE DATABASE CHECK…
RESTORE DATABASE,data collect…
RESTORE DATABASE,database refresh …
RESTORE BACKUPSET [/dm/data/dmdata/DMDB/bak/DB_DMDB_FULL_2021_03_25_21_17_25] START…
total 6 packages processed…
total 8 packages processed…
RESTORE DATABASE,UPDATE ctl file…
RESTORE DATABASE,REBUILD key file…
RESTORE DATABASE,CHECK db info…
RESTORE DATABASE,UPDATE db info…
total 8 packages processed…
total 8 packages processed!
CMD END.CODE:[0]
restore successfully.
time used: 00:00:01.653

还原：

RMAN>recover database ‘/dm/data/dmdata/DMDB/dm.ini’ with archivedir ‘/dm/data/dmdata/arch’ until time ‘2021-03-25 21:17:51.000000’;
recover database ‘/dm/data/dmdata/DMDB/dm.ini’ with archivedir ‘/dm/data/dmdata/arch’ until time ‘2021-03-25 21:17:51.000000’;
Database mode = 0, oguid = 0
EP[0]'s cur_lsn[269330]
EP:0 total 0 pkgs applied, percent: 0%
Recover from archive log finished, time used:0.040s.
EP[0]'s apply_lsn[269330] >= end_lsn[269298]
recover successfully!
time used: 350.726(ms)

同步魔数：

RMAN> recover database ‘/dm/data/dmdata/DMDB/dm.ini’ update db_magic;
recover database ‘/dm/data/dmdata/DMDB/dm.ini’ update db_magic;
Database mode = 0, oguid = 0
EP[0]'s cur_lsn[269330]
EP[0]'s apply_lsn[269330] >= end_lsn[269298]
recover successfully!
time used: 00:00:01.009

3.5 启动测试数据库
[root@dmm bin]# ./dmserver /dm/data/dmdata/DMDB/dm.ini
file dm.key not found, use default license!
version info: develop
Use normal os_malloc instead of HugeTLB
Use normal os_malloc instead of HugeTLB
DM Database Server x64 V8 1-1-126-20.09.04-126608-ENT startup…
Database mode = 0, oguid = 0
License will expire on 2021-09-04
file lsn: 269330
ndct db load finished
ndct fill fast pool finished
iid page’s trxid[168269]
NEXT TRX ID = 168270
pseg_collect_mgr_items, total collect 1 active_trxs, 0 cmt_trxs, 0 pre_cmt_trxs, 1 active_pages, 0 cmt_pages, 0 pre_cmt_pages, 1 mgr pages, 1 mgr recs!
total 1 active crash trx, pseg_crash_trx_rollback sys_only(0) begin …
trx: 168079 rollbacking…
total 1 page rollbacked, rollback percent: 100%
total 1 trx rollbacked, rollback percent: 100%
trx: 168079 rollback 1 upages, 17 urecs end
pseg_crash_trx_rollback end, total 1 active crash trx, include 0 empty_trxs, 0 empty_pages which only need to delete mgr recs.
pseg_crash_trx_rollback end
pseg recv finished
nsvr_startup end.
aud sys init success.
aud rt sys init success.
systables desc init success.
ndct_db_load_info success.
nsvr_process_before_open begin.
Server DM8_DCT_VERSION mismatch, version of data is 23, server version is 20.
Please use the correct version of server or set the CHECK_SVR_VERSION=0 in dm.ini

启动失败，最后一行报了一个错，Please use the correct version of server or set the CHECK_SVR_VERSION=0 in dm.ini

于是把dm.ini中CHECK_SVR_VERSION设置为0

再次启动：

[root@dmm bin]# ./dmserver /dm/data/dmdata/DMDB/dm.ini
file dm.key not found, use default license!
version info: develop
Use normal os_malloc instead of HugeTLB
Use normal os_malloc instead of HugeTLB
DM Database Server x64 V8 1-1-126-20.09.04-126608-ENT startup…
Database mode = 0, oguid = 0
License will expire on 2021-09-04
begin redo pwr log collect, last ckpt lsn: 269330 …
redo pwr log collect finished
main rfil[/dm/data/dmdata/DMDB/DMDB01.log]'s grp collect 0 valid pwr record, discard 1 invalid pwr record
EP[0]'s cur_lsn[269392]
begin redo log recover, last ckpt lsn: 269330 …
redo log recover finished
ndct db load finished
ndct fill fast pool finished
iid page’s trxid[169271]
NEXT TRX ID = 169272
pseg_collect_mgr_items, total collect 1 active_trxs, 0 cmt_trxs, 0 pre_cmt_trxs, 1 active_pages, 0 cmt_pages, 0 pre_cmt_pages, 1 mgr pages, 1 mgr recs!
total 1 active crash trx, pseg_crash_trx_rollback sys_only(0) begin …
trx: 168079 rollbacking…
total 1 page rollbacked, rollback percent: 100%
total 1 trx rollbacked, rollback percent: 100%
trx: 168079 rollback 1 upages, 0 urecs end
pseg_crash_trx_rollback end, total 1 active crash trx, include 1 empty_trxs, 1 empty_pages which only need to delete mgr recs.
pseg_crash_trx_rollback end
pseg recv finished
nsvr_startup end.
aud sys init success.
aud rt sys init success.
systables desc init success.
ndct_db_load_info success.
nsvr_process_before_open begin.
Server DM8_DCT_VERSION mismatch, version of data is 23, server version is 20.
nsvr_process_before_open success.
total 0 active crash trx, pseg_crash_trx_rollback sys_only(0) begin …
pseg_crash_trx_rollback end, total 0 active crash trx, include 0 empty_trxs, 0 empty_pages which only need to delete mgr recs.
pseg_crash_trx_rollback end
SYSTEM IS READY.

启动成功！

3.6 检查数据
用dm管理器连接数据库，sysdba的密码已经改变了，要用源库的sysdba密码

此时检查2021-03-25 21:17:51前被删除的表回来了。

4 导出和导入数据
前面已经将过方法，至此不再赘述了

5 总结
我觉得最关键的问题就是要保证测试环境的库相关的路径要和环境的库保持一致，另外一个就是备份和归档的文件权限要设为dmdba用户。