达梦数据库的备份与恢复(物理及逻辑备份)
物理联机备份
联机备份:数据库是启动状态,联机备份要求数据库打开归档。备份时 AP 服务需要为启动状态。
- 数据库开机归档
alter database mount;
alter database ARCHIVELOG;
alter database add ARCHIVELOG 'type=local,dest=/dm8/arch';
alter database add archivelog 'type=local,dest=/dm8/arch,file_size=64,space_limit=10240';
alter database open;
1.1 故障参考
第一次联机备份数据库时,出现归档序号不连续情况执行以下操作处理:
backup database;
checkpoint(100);
- 备份数据库
备份全库
backup DATABASE FULL to FULLBAK001 BACKUPSET '/dm8/data/bak/FULLBAK001';
累计增量备份
backup DATABASE INCREMENT CUMULATIVE with BACKUPDIR '/dm8/data/bak/' to INCR_CUM_BAK001 BACKUPSET '/dm8/data/incr/INCR_CUM_BAK001';
差异增量备份
backup DATABASE INCREMENT BASE ON BACKUPSET '/dm8/data/bak/FULLBAK001' to INCR_BAK001 backupset '/dm8/data/incr/INCR_BAK001';
表空间备份
backup tablespace dmtbs;
backup tablespace dmtbs INCREMENT with BACKUPDIR '/dm8/backup/full/' to DMTBSINCR_01 backupset '/dm8/backup/incr/DMTBSINCR_01';
表备份
backup table liuzitao.abc;
backup table liuzitao.abc to LZTTABABCBAK01 backupset '/dm8/data/bak/LZTTABABCBAK01';
- 还原数据库
库级、表空间的还原和恢复不支持联机恢复,只支持脱机还原恢复。整库恢复还原会更改临时魔数,整库恢复后,之前备份将无法再用于本库恢复;
关闭数据库
shutdown immediate
==========================================================================
表还原
restore TABLE STRUCT FROM BACKUPSET '/dm8/data/bak/LZTABCBAK001';
restore TABLE FROM BACKUPSET '/dm8/data/bak/LZTABCBAK001';
先还原表结构,再还原表数据;
==========================================================================
表空间从全备份还原
restore database '/dm8/data/DAMENG/dm.ini' tablespace lztspc from backupset '/dm8/backup/full/CONSOLEFULLBAK';
表空间恢复
recover database '/dm8/data/DAMENG/dm.ini' tablespace main;
备注:表空间的恢复系统会自动利用归档恢复到最新状态。
表空间从增量备份还原
restore database '/dm8/data/DAMENG/dm.ini' tablespace LZTSPC from backupset '/dm8/data/incr/INCR_BAK001' with backupdir '基础全备目录';
表空间恢复
recover database '/dm8/data/DAMENG/dm.ini' tablespace main;
备注:表空间的恢复系统会自动利用归档恢复到最新状态。
==========================================================================
整库还原
restore database '/dm8/data/DM/dm.ini' from backupset '/dm8/backup/full/ONLINEBAK_02';
整库恢复
recover database '/dm8/data/DM/dm.ini' with archivedir '/dm8/arch';
更新数据库魔数
recover database '/dm8/data/DM/dm.ini' update db_magic;
==========================================================================
表误删除(DROP),可以指定归档恢复到删除的前一刻。
recover database '/dm8/data/DMTEST/dm.ini' with archivedir '/dm8/arch' until lsn XX;
recover database '/dm8/data/DMTEST/dm.ini' with archivedir '/dm8/arch' until time XX;
- 非默认备份路径注册
SF_BAKSET_BACKUP_DIR_ADD('DISK','/dm8/backup/full/');
SF_BAKSET_BACKUP_DIR_ADD('DISK','/dm8/backup/incr/');
- 备份集维护
dmrman
查看备份集
show backupset '/dm8/data/DAMENG/bak_full';
删除备份集
remove backupset '/dm8/data/DAMENG/bak_increment';
检查备份集
check backupset '/dm8/data/DAMENG/bak_full';
==============================================
manager
校验备份集
select SF_BAKSET_CHECK('DISK','/dm8/data/incr/INCR_BAK001');
查看备份集
select * from v$backupset;
查询永久魔数
select permanent_magic;
查询当前魔数
select db_magic from v$rlog;
脱机物理冷备
关闭数据库
shutdown immediate
dmrman
backup database '/dm8/data/DAMENG/dm.ini' full to DM8FULLBAK backupset '/dm8/data/bak/DM8FULLBAK';
逻辑备份
dexp 逻辑导出 | dimp 逻辑导入 四个级别:
全库(full=y)
按用户(owner=XXX)
按模式(schemas=XXX)
按表(tables=XX)
dexp
全库导出
dexp userid=sysdba/Dameng123:5236 directory=/dm8/backup/dexp file=full.dmp log=full.log full=y
按用户导出
dexp userid=sysdba/Dameng123:5236 directory=/dm8/backup/dexp file=HRTEST.dmp log=HRTEST.log owner=HRTEST
按模式导出
dexp userid=sysdba/Dameng123:5236 directory=/dm8/backup/dexp file=DMHR.dmp log=DMHR.log schemas=DMHR
按表导出
dexp userid=sysdba/Dameng123:5236 directory=/dm8/backup/dexp file=EMPLOYEE.dmp log=EMPLOYEE.log tables=DMHR.EMPLOYEE
手写例子
dexp userid=sysdba/SYSDBA directory=/dm8/data/bak/ file=lzt.dmp log=lzt.log owner=liuzitao
dimp
全库导入
dimp userid=sysdba/Dameng123:5238 directory=/dm8/backup/dexp file=full.dmp log=impfull.log full=y
按模式导入(将 A 模式导入到 B 模式,使用 REMAP_SCHEMA 参数):
dimp userid=sysdba/Dameng123:5238 directory=/dm8/backup/dexp file=DMHR.dmp log=impDMHR.log REMAP_SCHEMA=DMHR:DMTEST
表导入测试
dimp userid=sysdba/SYSDBA:5238 directory=/dm8/dexp file=t_dept.dmp log=dept.log tables=SYSDBA.t_dept remap_schema=SYSDBA:DMHR
手写例子
dimp userid=sysdba/SYSDBA directory=/dm8/data/bak/ file=lzt.dmp log=lztimp.log tables=liuzitao.abc
dimp userid=sysdba/SYSDBA directory=/dm8/data/bak/ file=lzt.dmp log=lztimp.log tables=liuzitao.abc table_exists_action=replace
dimp userid=sysdba/SYSDBA directory=/dm8/data/bak/ file=lzt.dmp log=lztimp.log table_exists_action=replace