提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
文章目录
前言
在达梦数据库中,有两种备份方式:脱机备份跟联机备份,脱机备份使用dmrman工具来实现,联机备份可以使用客户端manager工具或者disql工具实现。本章内容将介绍达梦数据库物理备份跟还原的有关内容,主要针对、库备份还原、表的备份还原、表空间备份还原、归档备份还原、以及增备的内容。针对备份内容有以下几点注意内容
备份注意事项:
- 库备份可以在联机以及脱机状态下执行
- 表空间备份只能在联机状态下执行,必须配置归档
- 表备份只能在联机状态下执行,且不需要配置归档就可以执行,不支持增量表备份
- 归档日志备份可以在联机以及脱机状态下执行,必须配置归档还原注意事项:
还原注意事项:
- 库还原跟表空间还原必须在脱机下执行
- 表还原只能联机下执行
- 还原的目标空间不能是temp空间,其他都可以
使用注意事项:
另外,再使用类似disql工具或者dmrman工具时候,会出现无法正常删除或者返回到开头的情况,这会给本身习惯操作于mysql客户端的人带来不太适应的感觉,例如以下情况
1.发现打错了,想要使用退格键删除时候发现无法正常删除(可以通过ctrl+backspace删除)
2.当你输入完发现开头输错了想要按方向键或者home键返回开头时候,无法移动,只能删除到开头重打
为了解决这个问题,咱们可以安装一个rpm包,名字叫rlwrap,用来解决该问题,rpm包下载链接如下
http://dameng.online:5000/fsdownload/dm8KKbKI4/rlwrap(如果打不开建议开启vpn翻个墙试试)
一、开启归档
该工具用于实现数据库、表空间、归档日志等内容的脱机备份跟还原操作
1.1 配置归档内容
为了方便后面的备份跟还原测试,先开启归档内容,具体开启方法如下
1.设置数据库为mount模式,只有该模式下才支持归档备份
2.配置归档文件以及路径大小等内容
3.启用归档
4.将数据库设置为open模式,用于支持备份还原操作
[dmdba@czk bin]$ rlwrap ./disql SYSDBA/SYSDBA@localhost:5236
服务器[localhost:5236]:处于普通打开状态
登录使用时间 : 1.572(ms)
disql V8
SQL> alter database mount;
操作已执行
已用时间: 38.894(毫秒). 执行号:0.
SQL> alter database add archivelog 'dest=/opt/dmdbms/data/arch1,type=local,file_size=1024,space_limit=2048';
操作已执行
已用时间: 3.080(毫秒). 执行号:0.
SQL> alter database archivelog;
操作已执行
已用时间: 0.279(毫秒). 执行号:0.
SQL> alter database open;
操作已执行
已用时间: 21.579(毫秒). 执行号:0.
1.2 查看归档配置状态跟内容
如果你已经配置好归档,但是想要查看下配置及情况,可以使用dm_arch_ini或者arch_status来查看相应内容
SQL> select * from v$dm_arch_ini;
行号 ARCH_NAME ARCH_TYPE ARCH_DEST ARCH_FILE_SIZE ARCH_SPACE_LIMIT ARCH_HANG_FLAG ARCH_TIMER_NAME
---------- -------------- --------- ------------------------ -------------- ---------------- -------------- ---------------
ARCH_IS_VALID ARCH_WAIT_APPLY ARCH_INCOMING_PATH ARCH_CURR_DEST ARCH_FLUSH_BUF_SIZE ARCH_RESERVE_TIME
------------- --------------- ------------------ ------------------------ ------------------- -----------------
ARCH_LOCAL_SHARE ARCH_LOCAL_SHARE_CHECK ARCH_SEND_DELAY
---------------- ---------------------- ---------------
1 ARCHIVE_LOCAL1 LOCAL /opt/dmdbms/czkdata/arch 1024 2048 1 NULL
Y NULL NULL /opt/dmdbms/czkdata/arch 0 0
0 0 0
行号 ARCH_NAME ARCH_TYPE ARCH_DEST ARCH_FILE_SIZE ARCH_SPACE_LIMIT ARCH_HANG_FLAG ARCH_TIMER_NAME
---------- -------------- --------- ------------------------ -------------- ---------------- -------------- ---------------
ARCH_IS_VALID ARCH_WAIT_APPLY ARCH_INCOMING_PATH ARCH_CURR_DEST ARCH_FLUSH_BUF_SIZE ARCH_RESERVE_TIME
------------- --------------- ------------------ ------------------------ ------------------- -----------------
ARCH_LOCAL_SHARE ARCH_LOCAL_SHARE_CHECK ARCH_SEND_DELAY
---------------- ---------------------- ---------------
2 ARCHIVE_LOCAL2 LOCAL /opt/dmdbms/data/arch 1024 2048 1 NULL
Y NULL NULL /opt/dmdbms/data/arch 0 0
0 0 0
行号 ARCH_NAME ARCH_TYPE ARCH_DEST ARCH_FILE_SIZE ARCH_SPACE_LIMIT ARCH_HANG_FLAG ARCH_TIMER_NAME
---------- -------------- --------- ------------------------ -------------- ---------------- -------------- ---------------
ARCH_IS_VALID ARCH_WAIT_APPLY ARCH_INCOMING_PATH ARCH_CURR_DEST ARCH_FLUSH_BUF_SIZE ARCH_RESERVE_TIME
------------- --------------- ------------------ ------------------------ ------------------- -----------------
ARCH_LOCAL_SHARE ARCH_LOCAL_SHARE_CHECK ARCH_SEND_DELAY
---------------- ---------------------- ---------------
3 ARCHIVE_LOCAL3 LOCAL /opt/dmdbms/data/arch1 1024 2048 1 NULL
Y NULL NULL /opt/dmdbms/data/arch1 0 0
0 0 0
已用时间: 6.555(毫秒). 执行号:4400.
SQL> select * from v$arch_status;
行号 ARCH_TYPE ARCH_DEST ARCH_STATUS ARCH_SRC
---------- --------- ------------------------ ----------- --------
1 LOCAL /opt/dmdbms/czkdata/arch VALID DMSERVER
2 LOCAL /opt/dmdbms/data/arch VALID DMSERVER
3 LOCAL /opt/dmdbms/data/arch1 VALID DMSERVER
已用时间: 0.802(毫秒). 执行号:4401.
二、备份
2.1 表备份
注意,表备份只能联机备份,所以我这里使用的是disql工具
backupset 后面接的是备份集的路径,就是把备份集存到哪里
SQL> backup table C1 backupset '/opt/dmdbms/c1_backup';
操作已执行
已用时间: 00:00:03.433. 执行号:4402.
2.2 表空间备份
SQL> backup tablespace BOOKS backupset '/opt/dmdbms/tablespace_books_backup';
操作已执行
已用时间: 00:00:02.954. 执行号:4403.
2.3 库备份
基于联机的库备份
SQL> backup database backupset '/opt/dmdbms/database_backup1';
操作已执行
已用时间: 00:00:04.041. 执行号:4404.
基于脱机的库备份
full参数代表全备,也可以不写,默认就是全备
[dmdba@czk bin]$ rlwrap ./dmrman
dmrman V8
RMAN> backup database '/opt/dmdbms/data/DAMENG/dm.ini' full backupset '/opt/dmdbms/database_backup2';
backup database '/opt/dmdbms/data/DAMENG/dm.ini' full backupset '/opt/dmdbms/database_backup2';
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[1028634], file_lsn[1028634]
Processing backupset /opt/dmdbms/database_backup2
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:02][Remaining:00:00:00]
backup successfully!
time used: 00:00:03.388
2.4 归档备份
基于联机归档备份
SQL> backup archivelog all backupset '/opt/dmdbms/arch_backup001';
服务器[localhost:5236]:处于普通打开状态
已连接
操作已执行
已用时间: 00:00:03.824. 执行号:900.
基于脱机归档备份
RMAN> backup archivelog all database '/opt/dmdbms/data/DAMENG/dm.ini';
backup archivelog all database '/opt/dmdbms/data/DAMENG/dm.ini';
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[1028634], file_lsn[1028634]
Processing backupset /opt/dmdbms/data/DAMENG/bak/ARCH_LOG_20220414_153230_636293
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:04][Remaining:00:00:00]
backup successfully!
time used: 00:00:05.347
2.5 库的增量备份
基于联机增量备份
with backupdir 用于指定基础备份的搜索目录(增备需要的基备必须是全备),可能存在多个备份搜索目录
SQL> backup database increment with backupdir '/opt/dmdbms/database_backup01' backupset '/opt/dmdbms/increment_backup';
操作已执行
已用时间: 00:00:02.957. 执行号:901.
基于脱机增量备份
RMAN> backup database '/opt/dmdbms/data/DAMENG/dm.ini' increment with backupdir '/opt/dmdbms/database_backup02' backupset '/opt/dmdbms/increment_backup01';
backup database '/opt/dmdbms/data/DAMENG/dm.ini' increment with backupdir '/opt/dmdbms/database_backup02' backupset '/opt/dmdbms/increment_backup01';
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[1030310], file_lsn[1030310]
Processing backupset /opt/dmdbms/increment_backup01
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:02][Remaining:00:00:00]
backup successfully!
time used: 00:00:03.258
2.6 表空间的增量备份
base on backupset 指定基备份集路径
执行第一次增备,指定基备份集为全备
SQL> backup tablespace books increment base on backupset '/opt/dmdbms/tablespace_books_backup' backupset '/opt/dmdbms/tablespace_increment_backup';
操作已执行
已用时间: 00:00:02.988. 执行号:900.
执行第二次增备,指定基备份集为增备1
SQL> backup tablespace books increment base on backupset '/opt/dmdbms/tablespace_increment_backup' backupset '/opt/dmdbms/tablespace_increment_backup02';
操作已执行
已用时间: 00:00:06.782. 执行号:901.
三、还原
提醒一下,还原操作除了做还原操作外,还必须要做恢复操作跟更新db_magic操作,恢复操作我没有全部放进来,参考库还原的恢复操作即可
3.1 表还原
SQL> restore table C1 from backupset '/opt/dmdbms/c1_backup';
操作已执行
已用时间: 51.372(毫秒). 执行号:800.
3.2 表空间还原
RMAN> restore database '/opt/dmdbms/data/DAMENG/dm.ini' tablespace BOOKS from backupset '/opt/dmdbms/tablespace_books_backup';
restore database '/opt/dmdbms/data/DAMENG/dm.ini' tablespace BOOKS from backupset '/opt/dmdbms/tablespace_books_backup';
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[1034369], file_lsn[1034369]
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:02][Remaining:00:00:00]
restore successfully.
time used: 00:00:02.489
3.3 库还原
RMAN> restore database '/opt/dmdbms/data/DAMENG/dm.ini' from backupset '/opt/dmdbms/database_backup1';
restore database '/opt/dmdbms/data/DAMENG/dm.ini' from backupset '/opt/dmdbms/database_backup1';
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.879
库恢复操作
RMAN> recover database '/opt/dmdbms/data/DAMENG/dm.ini' with archivedir '/opt/dmdbms/data/arch1';
recover database '/opt/dmdbms/data/DAMENG/dm.ini' with archivedir '/opt/dmdbms/data/arch1';
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[1028654], file_lsn[1028654]
[Percent:100.00%][Speed:5230.00PKG/s][Cost:00:00:00][Remaining:00:00:00]
recover successfully!
time used: 578.315(ms)
库恢复更新操作
RMAN> recover database '/opt/dmdbms/data/DAMENG/dm.ini' update db_magic;
recover database '/opt/dmdbms/data/DAMENG/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[1036648], file_lsn[1036648]
recover successfully!
time used: 00:00:01.099
3.4 归档还原
设置overwirte 2,如果归档已经存在,会报错。
to archivedir 指定归档配置目录
RMAN> restore archive log from backupset '/opt/dmdbms/arch_backup001' to archivedir '/opt/dmdbms/data/arch1' overwrite 2;
restore archive log from backupset '/opt/dmdbms/arch_backup001' to archivedir '/opt/dmdbms/data/arch1' overwrite 2;
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:00][Remaining:00:00:00]
restore successfully.
time used: 540.007(ms)
3.5 库的增量还原
RMAN> restore database '/opt/dmdbms/data/DAMENG/dm.ini' from backupset '/opt/dmdbms/increment_backup' with backupdir '/opt/dmdbms/database_backup01';
restore database '/opt/dmdbms/data/DAMENG/dm.ini' from backupset '/opt/dmdbms/increment_backup' with backupdir '/opt/dmdbms/database_backup01';
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.987
3.6 表空间的增量还原
RMAN> restore database '/opt/dmdbms/data/DAMENG/dm.ini' tablespace BOOKS from backupset '/opt/dmdbms/tablespace_books_backup1';
restore database '/opt/dmdbms/data/DAMENG/dm.ini' tablespace BOOKS from backupset '/opt/dmdbms/tablespace_books_backup1';
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[1031633], file_lsn[1031633]
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:02][Remaining:00:00:00]
restore successfully.
time used: 00:00:02.460
RMAN> restore database '/opt/dmdbms/data/DAMENG/dm.ini' tablespace BOOKS from backupset '/opt/dmdbms/tablespace_increment_backup1' with backupdir '/opt/dmdbms/tablespace_books_backup1';
restore database '/opt/dmdbms/data/DAMENG/dm.ini' tablespace BOOKS from backupset '/opt/dmdbms/tablespace_increment_backup1' with backupdir '/opt/dmdbms/tablespace_books_backup1';
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[1031633], file_lsn[1031633]
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:02][Remaining:00:00:00]
restore successfully.
time used: 00:00:02.396
RMAN> restore database '/opt/dmdbms/data/DAMENG/dm.ini' tablespace BOOKS from backupset '/opt/dmdbms/tablespace_increment_backup2' with backupdir '/opt/dmdbms/tablespace_increment_backup1';
restore database '/opt/dmdbms/data/DAMENG/dm.ini' tablespace BOOKS from backupset '/opt/dmdbms/tablespace_increment_backup2' with backupdir '/opt/dmdbms/tablespace_increment_backup1';
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[1031633], file_lsn[1031633]
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:02][Remaining:00:00:00]
restore successfully.
time used: 00:00:02.417
RMAN> recover database '/opt/dmdbms/data/DAMENG/dm.ini' tablespace BOOKS with archivedir '/opt/dmdbms/data/arch1' use db_magic 114478272;
recover database '/opt/dmdbms/data/DAMENG/dm.ini' tablespace BOOKS with archivedir '/opt/dmdbms/data/arch1' use db_magic 114478272;
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[1031633], file_lsn[1031633]
[Percent:100.00%][Speed:0.00PKG/s][Cost:00:00:00][Remaining:00:00:00]
recover successfully.
time used: 428.381(ms)
总结
如果还有其他问题,欢迎到达梦社区来提问~
社区地址:https://eco.dameng.com