达梦数据库的表的联机备份还原
联机备份还原表
一、创建待备份的表
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);
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.
想要获取达梦数据库更多的学习资料，可登陆达梦数据云适配中心（https://eco.dameng.com/）获取。