热门搜索
分布式
数据库一体机
云数据库
建设银行
保险核心
密集交易
登录
注册
全备、增备及使用dmrman进行数据恢复
全备、增备及使用dmrman进行数据恢复
吉吉
2023/09/06
1685
0
0
摘要
在对数据库进行全备或增备前，需要开启数据库归档，并启动dmap服务
测试环境信息：
源端实例名test1，端口5236
目的端实例名test2，端口5237
一、全量备份
1.在源端创建测试表baktest，并插入两条数据
[dmdba@localhost bak]$ disql SYSDBA/SYSDBA:5236
服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 3.368(ms)
disql V8
SQL> create table baktest (a int);
操作已执行
已用时间: 3.739(毫秒). 执行号:506.
SQL> insert into baktest (a) values (1);
影响行数 1
已用时间: 0.687(毫秒). 执行号:507.
SQL> insert into baktest (a) values (2);
影响行数 1
已用时间: 0.739(毫秒). 执行号:508.
SQL> commit;
操作已执行
已用时间: 1.233(毫秒). 执行号:509.
SQL> select * from baktest;
行号 a
---------- -----------
1 1
2 2
已用时间: 0.711(毫秒). 执行号:510.
2.对源库进行联机全备
关键词 full
SQL> backup database full backupset '/dmdata/bak/bak_full';
操作已执行
已用时间: 00:00:03.083. 执行号:600.
或backup database backupset '/dmdata/bak/bak_full';
SQL> backup database backupset '/dmdata/bak/bak_full';
操作已执行
已用时间: 00:00:03.017. 执行号:601.
由于数据库备份默认为全量备份，所以备份语句也可以写成
backup database backupset '/dmdata/bak/bak_full';
3.对全量备份进行恢复
目标端停库后，使用dmrman工具进行数据恢复
1.校验备份集
RMAN> CHECK BACKUPSET '/dmdata/bak/bak_full';
CHECK BACKUPSET '/dmdata/bak/bak_full';
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:00][Remaining:00:00:00]
check backupset successfully.
time used: 177.758(ms)
2.还原数据库（恢复数据文件）
RMAN> RESTORE DATABASE '/dmdata/test2/dm.ini' FROM BACKUPSET '/dmdata/bak/bak_full';
RESTORE DATABASE '/dmdata/test2/dm.ini' FROM BACKUPSET '/dmdata/bak/bak_full';
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.546
3.恢复数据库（进行数据库一致性修复）
RMAN> RECOVER DATABASE '/dmdata/test2/dm.ini' FROM BACKUPSET '/dmdata/bak/bak_full';
RECOVER DATABASE '/dmdata/test2/dm.ini' FROM BACKUPSET '/dmdata/bak/bak_full';
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[39501], file_lsn[39501]
[Percent:100.00%][Speed:0.00PKG/s][Cost:00:00:00][Remaining:00:00:00]
recover successfully!
time used: 00:00:02.493
4.更新DB_MAGIC
RMAN> RECOVER DATABASE '/dmdata/test2/dm.ini' UPDATE DB_MAGIC;
RECOVER DATABASE '/dmdata/test2/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[41297], file_lsn[41297]
recover successfully!
time used: 00:00:01.001
4.启动目的端，检查测试表baktest
disql SYSDBA/SYSDBA:5237
服务器[LOCALHOST:5237]:处于普通打开状态
登录使用时间 : 5.414(ms)
disql V8
SQL> select * from baktest;
行号 a
---------- -----------
1 1
2 2
已用时间: 2.009(毫秒). 执行号:500.
二、增量备份
增备分为差异增量备份和累计增量备份，进行增备时需要参数with backupdir指定基备份集
差异增量备份是 备份从上一次全备起到现在的所有增量数据
累积增量备份是 备份从任何一次备份（全备或增备）起到现在的所有增量数据
1.差异增量备份
① 向源端表baktest再插入两条数据
disql SYSDBA/SYSDBA:5236
服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 4.337(ms)
disql V8
SQL> insert into baktest (a) values (3);
影响行数 1
已用时间: 0.744(毫秒). 执行号:701.
SQL> insert into baktest (a) values (4);
影响行数 1
已用时间: 0.479(毫秒). 执行号:702.
SQL> commit;
操作已执行
已用时间: 1.133(毫秒). 执行号:703.
SQL> select * from baktest;
行号 a
---------- -----------
1 1
2 2
3 3
4 4
已用时间: 0.490(毫秒). 执行号:704.
② 以第一步的全备作为基备份，进行联机增量备份，增备默认为差异增量备份
关键词 increment
SQL> backup database increment with backupdir '/dmdata/bak/bak_full' backupset '/dmdata/bak/bak_incre';
操作已执行
已用时间: 00:00:03.048. 执行号:707.
③ 对增备数据进行恢复
目标端停库后，使用dmrman工具进行数据恢复
1.校验备份集
RMAN> CHECK BACKUPSET '/dmdata/bak/bak_incre';
CHECK BACKUPSET '/dmdata/bak/bak_incre';
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:00][Remaining:00:00:00]
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:00][Remaining:00:00:00]
check backupset successfully.
time used: 158.445(ms)
2.还原数据库（恢复数据文件）
RMAN> RESTORE DATABASE '/dmdata/test2/dm.ini' FROM BACKUPSET '/dmdata/bak/bak_incre'
RESTORE DATABASE '/dmdata/test2/dm.ini' FROM BACKUPSET '/dmdata/bak/bak_incre'
file dm.key not found, use default license!
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.636
3.恢复数据库（进行数据库一致性修复）
RMAN> RECOVER DATABASE '/dmdata/test2/dm.ini' FROM BACKUPSET '/dmdata/bak/bak_incre';
RECOVER DATABASE '/dmdata/test2/dm.ini' FROM BACKUPSET '/dmdata/bak/bak_incre';
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[41945], file_lsn[41945]
[Percent:100.00%][Speed:0.00PKG/s][Cost:00:00:00][Remaining:00:00:00]
recover successfully!
time used: 00:00:02.406
4.更新DB_MAGIC
RMAN> RECOVER DATABASE '/dmdata/test2/dm.ini' UPDATE DB_MAGIC;
RECOVER DATABASE '/dmdata/test2/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[41995], file_lsn[41995]
recover successfully!
time used: 00:00:01.013
④ 启动目的端，检查测试表baktest
disql SYSDBA/SYSDBA:5237
服务器[LOCALHOST:5237]:处于普通打开状态
登录使用时间 : 3.436(ms)
disql V8
SQL> select * from baktest;
行号 a
---------- -----------
1 1
2 2
3 3
4 4
已用时间: 1.858(毫秒). 执行号:500.
2.累计增量备份
① 源端分次插入两条数据，分别做两次累计增量备份
关键词 cumulative
disql SYSDBA/SYSDBA:5236
服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 4.337(ms)
disql V8
SQL> select * from baktest;
行号 a
---------- -----------
1 1
2 2
3 3
4 4
已用时间: 3.099(毫秒). 执行号:800.
SQL> insert into baktest (a) values (5);
影响行数 1
已用时间: 2.153(毫秒). 执行号:801.
SQL> commit;
操作已执行
已用时间: 1.657(毫秒). 执行号:802.
SQL> select * from baktest;
行号 a
---------- -----------
1 1
2 2
3 3
4 4
5 5
已用时间: 0.628(毫秒). 执行号:803.
使用上一次差异增备作为基备份，进行联机增备
SQL> backup database increment cumulative with backupdir '/dmdata/bak/bak_incre' backupset '/dmdata/bak/bak_incre_cum1';
操作已执行
已用时间: 00:00:03.257. 执行号:805.
SQL> insert into baktest (a) values (6);
影响行数 1
已用时间: 0.819(毫秒). 执行号:806.
SQL> commit;
操作已执行
已用时间: 1.326(毫秒). 执行号:807.
SQL> select * from baktest;
行号 a
---------- -----------
1 1
2 2
3 3
4 4
5 5
6 6
6 rows got
已用时间: 0.430(毫秒). 执行号:808.
使用上一次累积增备作为基备份，进行联机增备
SQL> backup database increment cumulative with backupdir '/dmdata/bak/bak_incre_cum1' backupset '/dmdata/bak/bak_incre_cum2';
操作已执行
已用时间: 00:00:03.043. 执行号:809.
② 使用第二次的累计增量备份集bak_incre_cum2恢复数据
1.校验备份集
RMAN> CHECK BACKUPSET '/dmdata/bak/bak_incre_cum1';
CHECK BACKUPSET '/dmdata/bak/bak_incre_cum1';
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:00][Remaining:00:00:00]
check backupset successfully.
time used: 177.559(ms)
RMAN> CHECK BACKUPSET '/dmdata/bak/bak_incre_cum2';
CHECK BACKUPSET '/dmdata/bak/bak_incre_cum2';
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:00][Remaining:00:00:00]
check backupset successfully.
time used: 153.979(ms)
2.使用累计增量备份集bak_incre_cum2还原数据库（恢复数据文件）
RMAN> RESTORE DATABASE '/dmdata/test2/dm.ini' FROM BACKUPSET '/dmdata/bak/bak_incre_cum2'
RESTORE DATABASE '/dmdata/test2/dm.ini' FROM BACKUPSET '/dmdata/bak/bak_incre_cum2'
file dm.key not found, use default license!
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.696
3. 使用累计增量备份集bak_incre_cum2恢复数据库（进行数据库一致性修复）
RMAN> RECOVER DATABASE '/dmdata/test2/dm.ini' FROM BACKUPSET '/dmdata/bak/bak_incre_cum2';
RECOVER DATABASE '/dmdata/test2/dm.ini' FROM BACKUPSET '/dmdata/bak/bak_incre_cum2';
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[44127], file_lsn[44127]
[Percent:100.00%][Speed:0.00PKG/s][Cost:00:00:00][Remaining:00:00:00]
recover successfully!
time used: 00:00:02.425
4.更新DB_MAGIC
RMAN> RECOVER DATABASE '/dmdata/test2/dm.ini' UPDATE DB_MAGIC;
RECOVER DATABASE '/dmdata/test2/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[44265], file_lsn[44265]
recover successfully!
time used: 00:00:01.016
③ 启动目的端，检查数据
disql SYSDBA/SYSDBA:5237
服务器[LOCALHOST:5237]:处于普通打开状态
登录使用时间 : 3.436(ms)
disql V8
SQL> select * from baktest;
行号 a
---------- -----------
1 1
2 2
3 3
4 4
5 5
6 6
6 rows got
3.删除目的端测试表，直接使用累计增量备份集bak_incre_cum2还原数据库恢复数据
SQL> drop table baktest;
操作已执行
已用时间: 31.287(毫秒). 执行号:502.
SQL> select * from baktest;
select * from baktest;
第1 行附近出现错误[-2106]:无效的表或视图名[baktest].
已用时间: 0.679(毫秒). 执行号:0.
RMAN> RESTORE DATABASE '/dmdata/test2/dm.ini' FROM BACKUPSET '/dmdata/bak/bak_incre_cum2'
RESTORE DATABASE '/dmdata/test2/dm.ini' FROM BACKUPSET '/dmdata/bak/bak_incre_cum2'
file dm.key not found, use default license!
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.636
RMAN> RECOVER DATABASE '/dmdata/test2/dm.ini' FROM BACKUPSET '/dmdata/bak/bak_incre_cum2';
RECOVER DATABASE '/dmdata/test2/dm.ini' FROM BACKUPSET '/dmdata/bak/bak_incre_cum2';
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[44127], file_lsn[44127]
[Percent:100.00%][Speed:0.00PKG/s][Cost:00:00:00][Remaining:00:00:00]
recover successfully!
time used: 00:00:02.413
RMAN> RECOVER DATABASE '/dmdata/test2/dm.ini' UPDATE DB_MAGIC;
RECOVER DATABASE '/dmdata/test2/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[44265], file_lsn[44265]
recover successfully!
time used: 00:00:01.017
检查备库还原结果，同样可以查询到全部数据
disql SYSDBA/SYSDBA:5237
服务器[LOCALHOST:5237]:处于普通打开状态
登录使用时间 : 4.513(ms)
disql V8
SQL> select * from baktest;
行号 a
---------- -----------
1 1
2 2
3 3
4 4
5 5
6 6
6 rows got
已用时间: 2.141(毫秒). 执行号:500.
备注
如果是将主备环境下的主库或备库恢复到单实例库，那么数据恢复后，单实例库会是对应的主库配置状态或备库配置状态，需要依次执行alter database normal;alter database open;，调整为普通打开状态。
评论
后发表回复
作者
文章
阅读量
获赞
扫一扫
联系客服
联系客服
鄂Copyright © 达梦国产化应用创新实验室
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1