【达梦】达梦数据库增量备份恢复-多次增量

戒掉贪嗔痴(薛双奇)

于 2024-07-25 18:37:38 发布

阅读量74

点赞数

分类专栏：国产数据库-达梦文章标签：数据库服务器运维

本文链接：https://blog.csdn.net/weixin_43346403/article/details/140697140

版权

国产数据库-达梦专栏收录该内容

13 篇文章 0 订阅

订阅专栏

1.备份

[dmdba@dm1 dm_fullback]$ disql sysdba/dameng123

服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 6.039(ms)
disql V8
SQL> backup database full to "dbfull" backupset '/dm/dmback/dm_fullback';
操作已执行
已用时间: 00:00:03.073. 执行号:600.


[dmdba@dm1 dmback]$ cd dm_fullback/
[dmdba@dm1 dm_fullback]$ ll
总用量 27892
-rw-r--r--. 1 dmdba dinstall   182784 7月  25 18:13 dm_fullback_1.bak
-rw-r--r--. 1 dmdba dinstall 28270080 7月  25 18:13 dm_fullback.bak
-rw-r--r--. 1 dmdba dinstall   102912 7月  25 18:13 dm_fullback.meta

2.做第一次增量数据

conn sjzt/"sjzt#1234"
create table sjzt_tab2(id int,name varchar(20));
begin 
for i in 1..100 loop 
insert into sjzt_tab2 values(i,i||'xsq1');
end loop; 
commit; 
end; 
/

--数据检查 
SQL> select count(1) from sjzt_tab1;

行号     COUNT(1)            
---------- --------------------
1          100

已用时间: 0.927(毫秒). 执行号:702.
SQL> select count(1) from sjzt_tab2;

行号     COUNT(1)            
---------- --------------------
1          100

已用时间: 0.922(毫秒). 执行号:703.

--第一次增量备份 
SQL> backup database increment base on backupset '/dm/dmback/dm_fullback' to "db_incr1" backupset '/dm/dmback/dm_fullback_incr1';
操作已执行
已用时间: 00:00:06.744. 执行号:800.

[dmdba@dm1 dmback]$ cd dm_fullback_incr1
[dmdba@dm1 dm_fullback_incr1]$ ll
总用量 8808
-rw-r--r--. 1 dmdba dinstall    5632 7月  25 18:17 dm_fullback_incr1_1.bak
-rw-r--r--. 1 dmdba dinstall 8904192 7月  25 18:17 dm_fullback_incr1.bak
-rw-r--r--. 1 dmdba dinstall  102912 7月  25 18:17 dm_fullback_incr1.meta

4.第二次增量

conn sjzt/"sjzt#1234"
create table sjzt_tab3(id int,name varchar(20));
begin 
for i in 1..100 loop 
insert into sjzt_tab3 values(i,i||'xsq1');
end loop; 
commit; 
end; 
/

select count(1) from sjzt_tab1 
union all 
select count(1) from sjzt_tab2 
union all 
select count(1) from sjzt_tab3 ;
行号     COUNT(1)            
---------- --------------------
1          100
2          100
3          100

已用时间: 1.418(毫秒). 执行号:902.

--第二次增量备份 
--基于第一个增量生成第二个增量
SQL> backup database increment base on backupset '/dm/dmback/dm_fullback_incr1' to "db_incr2" backupset '/dm/dmback/dm_fullback_incr2';
操作已执行
已用时间: 00:00:02.918. 执行号:1000.
[dmdba@dm1 dmback]$ cd dm_fullback_incr2
[dmdba@dm1 dm_fullback_incr2]$ ll
总用量 596
-rw-r--r--. 1 dmdba dinstall  67072 7月  25 18:20 dm_fullback_incr2_1.bak
-rw-r--r--. 1 dmdba dinstall 433664 7月  25 18:20 dm_fullback_incr2.bak
-rw-r--r--. 1 dmdba dinstall 102912 7月  25 18:20 dm_fullback_incr2.meta

5.第三次增量

conn sjzt/"sjzt#1234"
create table sjzt_tab4(id int,name varchar(20));
begin 
for i in 1..100 loop 
insert into sjzt_tab4 values(i,i||'xsq1');
end loop; 
commit; 
end; 
/

select count(1) from sjzt_tab1 
union all 
select count(1) from sjzt_tab2 
union all 
select count(1) from sjzt_tab3 
union all 
select count(1) from sjzt_tab4;
行号     COUNT(1)            
---------- --------------------
1          100
2          100
3          100
4          100

已用时间: 1.166(毫秒). 执行号:1102.

--基于第二次增量做第三次增量备份。
SQL> backup database increment base on backupset '/dm/dmback/dm_fullback_incr2' to "db_incr3" backupset '/dm/dmback/dm_fullback_incr3';
操作已执行
已用时间: 00:00:06.721. 执行号:1200.
[dmdba@dm1 dmback]$ cd dm_fullback_incr3
[dmdba@dm1 dm_fullback_incr3]$ ll
总用量 600
-rw-r--r--. 1 dmdba dinstall   5632 7月  25 18:23 dm_fullback_incr3_1.bak
-rw-r--r--. 1 dmdba dinstall 499200 7月  25 18:23 dm_fullback_incr3.bak
-rw-r--r--. 1 dmdba dinstall 102912 7月  25 18:23 dm_fullback_incr3.meta

6.删除数据

删除数据并恢复到第三次增量后的数据，即有四个表，每个表100条数据。

[dmdba@dm1 DAMENG1]$ rm -rf tps_sjzt.DBF 
[dmdba@dm1 DAMENG1]$ rm -rf SYSTEM.DBF

7.恢复数据

--停止数据库
[dmdba@dm1 DAMENG1]$ DmServiceDAMENG1 stop
Stopping DmServiceDAMENG1:                                 [ OK ]

--恢复到第三次增量后。

dmrman
restore database '/dm/mdbms/data/DAMENG1/dm.ini' from backupset '/dm/dmback/dm_fullback';
restore database '/dm/mdbms/data/DAMENG1/dm.ini' from backupset '/dm/dmback/dm_fullback_incr1';
restore database '/dm/mdbms/data/DAMENG1/dm.ini' from backupset '/dm/dmback/dm_fullback_incr2';
restore database '/dm/mdbms/data/DAMENG1/dm.ini' from backupset '/dm/dmback/dm_fullback_incr3';
recover database '/dm/mdbms/data/DAMENG1/dm.ini' from backupset '/dm/dmback/dm_fullback';
recover database '/dm/mdbms/data/DAMENG1/dm.ini' from backupset '/dm/dmback/dm_fullback_incr1';
recover database '/dm/mdbms/data/DAMENG1/dm.ini' from backupset '/dm/dmback/dm_fullback_incr2';
recover database '/dm/mdbms/data/DAMENG1/dm.ini' from backupset '/dm/dmback/dm_fullback_incr3';
recover database '/dm/mdbms/data/DAMENG1/dm.ini' update db_magic ;
exit;

--还原 
[dmdba@dm1 DAMENG1]$ dmrman
dmrman V8
RMAN> restore database '/dm/mdbms/data/DAMENG1/dm.ini' from backupset '/dm/dmback/dm_fullback';
restore database '/dm/mdbms/data/DAMENG1/dm.ini' from backupset '/dm/dmback/dm_fullback';
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.485
RMAN> restore database '/dm/mdbms/data/DAMENG1/dm.ini' from backupset '/dm/dmback/dm_fullback_incr1';
restore database '/dm/mdbms/data/DAMENG1/dm.ini' from backupset '/dm/dmback/dm_fullback_incr1';
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.495
RMAN> restore database '/dm/mdbms/data/DAMENG1/dm.ini' from backupset '/dm/dmback/dm_fullback_incr2';
restore database '/dm/mdbms/data/DAMENG1/dm.ini' from backupset '/dm/dmback/dm_fullback_incr2';
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.513
RMAN> restore database '/dm/mdbms/data/DAMENG1/dm.ini' from backupset '/dm/dmback/dm_fullback_incr3';
restore database '/dm/mdbms/data/DAMENG1/dm.ini' from backupset '/dm/dmback/dm_fullback_incr3';
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.528

--恢复 
RMAN> recover database '/dm/mdbms/data/DAMENG1/dm.ini' from backupset '/dm/dmback/dm_fullback';
recover database '/dm/mdbms/data/DAMENG1/dm.ini' from backupset '/dm/dmback/dm_fullback';
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[127623], file_lsn[127623]
[-8262]:恢复备份集[dbfull] N_MAGIC=637F35A0 与还原备份集 N_MAGIC=362503C0 不相同
RMAN> recover database '/dm/mdbms/data/DAMENG1/dm.ini' from backupset '/dm/dmback/dm_fullback_incr1';
recover database '/dm/mdbms/data/DAMENG1/dm.ini' from backupset '/dm/dmback/dm_fullback_incr1';
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[127623], file_lsn[127623]
[-8262]:恢复备份集[db_incr1] N_MAGIC=42EE960 与还原备份集 N_MAGIC=362503C0 不相同
RMAN> recover database '/dm/mdbms/data/DAMENG1/dm.ini' from backupset '/dm/dmback/dm_fullback_incr2';
recover database '/dm/mdbms/data/DAMENG1/dm.ini' from backupset '/dm/dmback/dm_fullback_incr2';
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[127623], file_lsn[127623]
[-8262]:恢复备份集[db_incr2] N_MAGIC=67CDDA0 与还原备份集 N_MAGIC=362503C0 不相同
RMAN> recover database '/dm/mdbms/data/DAMENG1/dm.ini' from backupset '/dm/dmback/dm_fullback_incr3';
recover database '/dm/mdbms/data/DAMENG1/dm.ini' from backupset '/dm/dmback/dm_fullback_incr3';
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[127623], file_lsn[127623]
[Percent:100.00%][Speed:0.00PKG/s][Cost:00:00:00][Remaining:00:00:00]                               
recover successfully!
time used: 00:00:02.422
RMAN> recover database '/dm/mdbms/data/DAMENG1/dm.ini' update db_magic ;
recover database '/dm/mdbms/data/DAMENG1/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[127624], file_lsn[127624]
recover successfully!
time used: 00:00:01.012

--前三次均报:恢复备份集 与还原备份集 N_MAGIC不相同
--最后一个增量加入后成功。

8.启动数据库并验证

[dmdba@dm1 DAMENG1]$ DmServiceDAMENG1 start
Starting DmServiceDAMENG1:                                 [ OK ]
[dmdba@dm1 DAMENG1]$ 

SQL> conn sjzt/"sjzt#1234"

服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 4.696(ms)
SQL> select count(1) from sjzt_tab1 
union all 
select count(1) from sjzt_tab2 
union all 
select count(1) from sjzt_tab3 
union all 
select count(1) from sjzt_tab4;2   3   4   5   6   7   

行号     COUNT(1)            
---------- --------------------
1          100
2          100
3          100
4          100
已用时间: 3.838(毫秒). 执行号:500.

--由此可见我们已经恢复到最后一次正能备份的时间点。