达梦数据库的表的联机备份还原
联机备份还原表
⼀、创建待备份的表
CREATE TABLE TAB_FOR_RES_01(C1 INT);
⼆、表数据初始化
insert into TAB_FOR_RES_01 values(13);
insert into TAB_FOR_RES_01 values(181);
commit;
三、备份表数据
BACKUP TABLE TAB_FOR_RES_01 BACKUPSET '/dm/dmdbms/data/DAMENG/bak/tab_bak_for_res_01';
四、增加表数据
insert into TAB_FOR_RES_01 values(13);
insert into TAB_FOR_RES_01 values(16);
insert into TAB_FOR_RES_01 values(5);
insert into TAB_FOR_RES_01 values(61);
insert into TAB_FOR_RES_01 values(1);
insert into TAB_FOR_RES_01 values(41);
insert into TAB_FOR_RES_01 values(6);
insert into TAB_FOR_RES_01 values(14);
insert into TAB_FOR_RES_01 values(31);
insert into TAB_FOR_RES_01 values(11);
insert into TAB_FOR_RES_01 values(13);
insert into TAB_FOR_RES_01 values(8);
insert into TAB_FOR_RES_01 values(1);
insert into TAB_FOR_RES_01 values(1);
insert into TAB_FOR_RES_01 values(16);
insert into TAB_FOR_RES_01 values(1);
insert into TAB_FOR_RES_01 values(9);
insert into TAB_FOR_RES_01 values(1);
insert into TAB_FOR_RES_01 values(11);
insert into TAB_FOR_RES_01 values(13);
insert into TAB_FOR_RES_01 values(19);
insert into TAB_FOR_RES_01 values(1);
insert into TAB_FOR_RES_01 values(181);
insert into TAB_FOR_RES_01 values(1);
commit;
查看表记录数：
SQL> select count(*) from TAB_FOR_RES_01;
⾏号    COUNT(*)
---------- --------------------
1          26
五、备份表数据
BACKUP TABLE TAB_FOR_RES_01 BACKUPSET '/dm/dmdbms/data/DAMENG/bak/tab_bak_for_res_01_1';
六、场景模拟
（⼀）误删数据
truncate table TAB_FOR_RES_01;
备份表数据
BACKUP TABLE TAB_FOR_RES_01 BACKUPSET '/dm/dmdbms/data/DAMENG/bak/tab_bak_for_res_01_2'；发现数据丢失
SQL> select * from TAB_FOR_RES_01;
未选定⾏
已⽤时间: 0.880(毫秒). 执⾏号:50642.
恢复表数据
恢复表数据到第⼆次批量插⼊数据完成之后：
SQL> restore table TAB_FOR_RES_01 from backupset '/dm/dmdbms/data/DAMENG/bak/tab_bak_for_res_01_1';
操作已执⾏
已⽤时间: 21.020(毫秒). 执⾏号:50643.
SQL> select * from TAB_FOR_RES_01;
⾏号    C1
---------- -----------
1          13
2          181
3          13
4          16
5          5
.
.
.
26 rows got
（⼆）误删表
SQL> drop table TAB_FOR_RES_01 purge;
操作已执⾏
已⽤时间: 43.518(毫秒). 执⾏号:50646.
SQL> select * from TAB_FOR_RES_01;
select * from TAB_FOR_RES_01;
第1 ⾏附近出现错误[-2106]:⽆效的表或视图名[TAB_FOR_RES_01].
已⽤时间: 0.347(毫秒). 执⾏号:0.
恢复表数据
SQL> restore table struct from BACKUPSET '/dm/dmdbms/data/DAMENG/bak/tab_bak_for_res_01_1'；操作已执⾏
已⽤时间: 13.229(毫秒). 执⾏号:50648.
SQL> restore table from BACKUPSET '/dm/dmdbms/data/DAMENG/bak/tab_bak_for_res_01_1';
操作已执⾏
已⽤时间: 17.530(毫秒). 执⾏号:50649.
SQL> select * from TAB_FOR_RES_01;
⾏号    C1
---------- -----------
1          13
2          181
3          13
4          16
5          5
.
.
.
26 rows got
（三）表结构误更改
SQL> alter table TAB_FOR_RES_01 drop column C1;
操作已执⾏
已⽤时间: 13.968(毫秒). 执⾏号:50653.
SQL> alter table TAB_FOR_RES_01 add column(NAME varchar2(20));
操作已执⾏
已⽤时间: 11.701(毫秒). 执⾏号:50651.
插⼊数据报错
SQL> insert into TAB_FOR_RES_01(C1) values(2);
insert into TAB_FOR_RES_01(C1) values(2);
第1 ⾏附近出现错误[-2111]:⽆效的列名[C1].
已⽤时间: 1.475(毫秒). 执⾏号:0.
恢复表数据到初始化表数据之后
⽆法直接恢复表结构
SQL> restore table struct from BACKUPSET '/dm/dmdbms/data/DAMENG/bak/tab_bak_for_res_01'; restore table struct from BACKUPSET '/dm/dmdbms/data/DAMENG/bak/tab_bak_for_res_01';
[-8324]：还原⽬标表定义不匹配.
已⽤时间: 11.239(毫秒). 执⾏号:0.
删除表
SQL> drop table TAB_FOR_RES_01 purge;
操作已执⾏
已⽤时间: 26.787(毫秒). 执⾏号:50655.
恢复表数据
SQL> restore table struct from BACKUPSET '/dm/dmdbms/data/DAMENG/bak/tab_bak_for_res_01';
操作已执⾏
已⽤时间: 16.622(毫秒). 执⾏号:50656.
SQL> restore table from BACKUPSET '/dm/dmdbms/data/DAMENG/bak/tab_bak_for_res_01';
操作已执⾏
已⽤时间: 16.985(毫秒). 执⾏号:50657.
SQL> desc TAB_FOR_RES_01;
⾏号    NAME TYPE$  NULLABLE
---------- ---- ------- --------
1          C1  INTEGER Y
已⽤时间: 1.223(毫秒). 执⾏号:50658.
SQL> select * from TAB_FOR_RES_01;
⾏号    C1
---------- -----------
1          13
2          181
已⽤时间: 0.613(毫秒). 执⾏号:50659.
（四）备份表中包含索引
初始化数据
alter table TAB_FOR_RES_01 add column(id number(10));
create unique index t1 on TAB_FOR_RES_01(id);
insert into TAB_FOR_RES_01(id) values(1);
truncate的数据如何恢复insert into TAB_FOR_RES_01(id) values(2);
commit;
备份表数据
BACKUP TABLE TAB_FOR_RES_01 BACKUPSET '/dm/dmdbms/data/DAMENG/bak/tab_bak_for_res_01_3';
删除表数据
truncate table TAB_FOR_RES_01;
恢复表数据
⽆法直接恢复数据
SQL> restore table from BACKUPSET '/dm/dmdbms/data/DAMENG/bak/tab_bak_for_res_01_3';
restore table from BACKUPSET '/dm/dmdbms/data/DAMENG/bak/tab_bak_for_res_01_3';
[-8327]：还原表中存在⼆级索引或冗余约束.
已⽤时间: 6.962(毫秒). 执⾏号:0.
恢复表结构
SQL> restore table TAB_FOR_RES_01 struct from BACKUPSET '/dm/dmdbms/data/DAMENG/bak/tab_bak_for_res_01_3'；操作已执⾏
已⽤时间: 28.015(毫秒). 执⾏号:50668.
恢复表数据
SQL> restore table TAB_FOR_RES_01 from BACKUPSET '/dm/dmdbms/data/DAMENG/bak/tab_bak_for_res_01_3';
操作已执⾏
已⽤时间: 19.887(毫秒). 执⾏号:50669.
SQL> select * from TAB_FOR_RES_01;
⾏号    C1          ID
---------- ----------- ----
1          13          NULL
2          181        NULL
3          NULL        1
4          NULL        2
已⽤时间: 0.938(毫秒). 执⾏号:50670.
想要获取达梦数据库更多的学习资料，可登陆达梦数据云适配中⼼（eco.dameng/）获取。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。