达梦数据库备份与还原

目录

前言

一、物理备份还原

1、联机备份

a）全备

b）增备

2、脱机备份

a）全备

b）增备

3、物理还原

a）全库还原（库级还原需要更新数据库魔数）

b）增量还原

c）表空间还原

d）表还原

e）基于归档还原

f）恢复到指定时间点(UNTIL TIME/UNTIL LSN)

二、逻辑备份还原

1.逻辑备份

a）全库备份

b）模式备份

c）用户备份

d）表备份

2.逻辑还原

a）全库还原

b）模式还原

c）用户还原

d）表还原

总结

---

前言

达梦数据库备份与还原。

---

一、物理备份还原

1、联机备份

a）全备

BACKUP DATABASE FULL BACKUPSET '/dmbak/bakfull_0815';

定义备份集名称为BAKFULL

BACKUP DATABASE FULL TO BAKFULL BACKUPSET '/dmbak/bakfull_0815';

创建备份并添加描述信息：

BACKUP DATABASE BACKUPSET '/dmbak/bakfull_0815' BACKUPINFO '全备';

限制备份片大小：

BACKUP DATABASE BACKUPSET '/dmbak/bakfull_0815‘ MAXPIECESIZE 500;

设置压缩级别：

BACKUP DATABASE BACKUPSET '/dmbak/bakfull_0815’ COMPRESSED LEVEL 1;

设置并行数

BACKUP DATABASE BACKUPSET '/dmbak/bakfull_0815‘ PARALLEL 8；

b）增备

增量备份（基于备份集'/dmbak/bakfull_0815’）

BACKUP DATABASE INCREMENT WITH BACKUPDIR '/dmbak/bakfull_0815' BACKUPSET '/dmbak/bakinc_0815';

2、脱机备份

使用dmrman备份工具

./dmrman进入

a）全备

BACKUP DATABASE '/dmdata/DAMENG/dm.ini' FULL BACKUPSET '/dmbak/bakfull_0815';

b）增备

BACKUP DATABASE '/dmdata/DAMENG/dm.ini'  INCREMENT WITH BACKUPDIR '/dmbak' BACKUPSET '/dmbak/bakinc_0815';

3、物理还原

使用dmrman备份工具

./dmrman进入

a）全库还原（库级还原需要更新数据库魔数）

RESTORE DATABASE '/dmdata/DAMENG/dm.ini' FROM  BACKUPSET  '/dmbak/bakfull_0815';
RECOVER DATABASE '/dmdata/DAMENG/dm.ini' FROM  BACKUPSET  '/dmbak/bakfull_0815';
RECOVER DATABASE '/dmdata/DAMENG/dm.ini' UPDATE DB_MAGIC;

b）增量还原

RESTORE DATABASE '/dmdata/DAMENG/dm.ini' FROM  BACKUPSET  '/dmbak/bakfull_0815';
RESTORE DATABASE '/dmdata/DAMENG/dm.ini' FROM  BACKUPSET  '/dmbak/bakinc_0815';
RECOVER DATABASE '/dmdata/DAMENG/dm.ini' FROM  BACKUPSET  '/dmbak/bakfull_0815';
RECOVER DATABASE '/dmdata/DAMENG/dm.ini' FROM  BACKUPSET  '/dmbak/bakinc_0815';
RECOVER DATABASE '/dmdata/DAMENG/dm.ini' UPDATE DB_MAGIC;

c）表空间还原

使用 RESTORE 命令完成表空间的脱机还原，还原的备份集可以是联机或脱机生成的库备份集，也可以是联机生成的表空间备份集。

RESTORE DATABASE '/dmdata/DAMENG/dm.ini' TABLESPACE TEST FROM BACKUPSET '/dmbak/bakfull_0815';
RECOVER DATABASE '/dmdata/DAMENG/dm.ini' TABLESPACE TEST;

d）表还原

表备份：

BACKUP TABLE TEST BACKUPSET '/dmbak/bakfull_0815';

还原表结构：

RESTORE TABLE STRUCT FROM BACKUPSET '/dmbak/bakfull_0815';

还原表数据：

RESTORE TABLE FROM BACKUPSET '/dmbak/bakfull_0815';

e）基于归档还原

RESTORE DATABASE '/dmdata/DAMENG/dm.ini' FROM  BACKUPSET  '/dmbak/bakfull_0815';
RECOVER DATABASE '/dmdata/DAMENG/dm.ini' with archivedir '/dmarch’;
RECOVER DATABASE '/dmdata/DAMENG/dm.ini' UPDATE DB_MAGIC;

f）恢复到指定时间点(UNTIL TIME/UNTIL LSN)

RESTORE DATABASE '/dmdata/DAMENG/dm.ini' FROM  BACKUPSET '/dmbak/bakfull_0815';
RECOVER DATABASE '/dmdata/DAMENG/dm.ini' with archivedir '/dmarch’ UNTIL TIME '2021-08-15 22:05:00';
RECOVER DATABASE '/dmdata/DAMENG/dm.ini' UPDATE DB_MAGIC;

二、逻辑备份还原

1.逻辑备份

a）全库备份，FULL=Y

./dexp USERID=SYSDBA/SYSDBA FILE=db_str.dmp LOG=db_str.log FULL=Y DIRECTORY=/dmbak/dexp

b）模式备份，SCHEMAS=TEST

./dexp USERID=SYSDBA/SYSDBA FILE=db_str.dmp LOG=db_str.log SCHEMAS=TEST  DIRECTORY=/dmbak/dexp

c）用户备份，OWNER=USER1

./dexp USERID=SYSDBA/SYSDBA FILE=db_str.dmp LOG=db_str.log OWNER=USER1 DIRECTORY=/dmbak/dexp

d）表备份，TABLES=table1,table2

./dexp USERID=SYSDBA/SYSDBA FILE=db_str.dmp LOG=db_str.log TABLES=table1,table2 DIRECTORY=/dmbak/dexp

2.逻辑还原

a）全库还原

./dimp USERID=SYSDBA/SYSDBA FILE=/dmbak/dexp/db_str.dmp LOG=db_str.log FULL=Y DIRECTORY=/dmbak/dimp

b）模式还原

./dimp USERID=SYSDBA/SYSDBA FILE=/dmbak/dexp/db_str.dmp LOG=db_str.log SCHEMAS=TEST DIRECTORY=/dmbak/dimp

c）用户还原

./dimp USERID=SYSDBA/SYSDBA FILE=/dmbak/dexp/db_str.dmp LOG=db_str.log OWNER=USER1 DIRECTORY=/dmbak/dimp

d）表还原

./dimp USERID=SYSDBA/SYSDBA FILE=/dmbak/dexp/db_str.dmp LOG=db_str.log TABLES=table1,table2 DIRECTORY=/dmbak/dimp

---

总结

更多资讯请上达梦技术社区了解：https://eco.dameng.com