达梦V8用户表的备份与恢复

创建表，并插入3条数据

SQL> create table testu.bookinfo(bookid int,bookcontent varchar2(50),bookcover varchar2(50)) tablespace tbuser;
executed successfully
used time: 9.019(ms). Execute id is 1590.
SQL> create index idx_bookinfo_id on testu.bookinfo(bookid);
executed successfully
used time: 11.924(ms). Execute id is 1591.
SQL> 

SQL> insert into  testu.bookinfo values(1,'bookcontent1','bookcover1');
affect rows 1

used time: 0.780(ms). Execute id is 1592.
SQL> insert into  testu.bookinfo values(2,'bookcontent2','bookcover2');
affect rows 1

used time: 0.567(ms). Execute id is 1593.
SQL> insert into  testu.bookinfo values(3,'bookcontent3','bookcover3');
affect rows 1

used time: 0.472(ms). Execute id is 1594.
SQL> commit;
executed successfully
used time: 0.713(ms). Execute id is 1595.
SQL> 

备份前数据

SQL>  select OWNER,TABLE_NAME,INDEX_NAME from dba_indexes where TABLE_OWNER='TESTU' and TABLE_NAME='BOOKINFO';

LINEID     OWNER TABLE_NAME INDEX_NAME     
---------- ----- ---------- ---------------
1          TESTU BOOKINFO   IDX_BOOKINFO_ID
2          TESTU BOOKINFO   INDEX33555495

used time: 35.363(ms). Execute id is 1598.
SQL> 
SQL> 
SQL> select count(*) from TESTU.BOOKINFO;

LINEID     COUNT(*)            
---------- --------------------
1          3

used time: 0.810(ms). Execute id is 1599.
SQL> 

执行表备份

SQL> SELECT SF_BAKSET_BACKUP_DIR_ADD('DISK','/dm8/backup/');

LINEID     SF_BAKSET_BACKUP_DIR_ADD('DISK','/dm8/backup/')
---------- -----------------------------------------------
1          1

used time: 0.868(ms). Execute id is 1600.
SQL> 
SQL> 
SQL> 
SQL> SP_TAB_BAKSET_REMOVE_BATCH('DISK',NOW(),'TESTU',null);
DMSQL executed successfully
used time: 68.353(ms). Execute id is 1601.
SQL> 
SQL> 
SQL> 
SQL> BACKUP TABLE testu.bookinfo  BACKUPSET '/dm8/backup/bookinfo_bak_01' compressed level 3 ;
executed successfully
used time: 00:00:01.011. Execute id is 1602.
SQL> 

备份后，又插入1条数据

SQL> 
SQL> insert into  testu.bookinfo values(4,'bookcontent4','bookcover4');
affect rows 1

used time: 11.947(ms). Execute id is 500.
SQL> commit;
executed successfully
used time: 1.020(ms). Execute id is 501.
SQL> 

SQL> select * from testu.bookinfo;

LINEID     BOOKID      BOOKCONTENT  BOOKCOVER 
---------- ----------- ------------ ----------
1          1           bookcontent1 bookcover1
2          2           bookcontent2 bookcover2
3          3           bookcontent3 bookcover3
4          4           bookcontent4 bookcover4

used time: 0.942(ms). Execute id is 502.
SQL> 

模拟bookinfo表被意外drop

SQL> 
SQL> 
SQL> 
SQL> drop table  testu.bookinfo purge;
executed successfully
used time: 80.868(ms). Execute id is 503.
SQL> 
SQL> select * from testu.bookinfo;
select * from testu.bookinfo;
[-2106]:Error in line: 1
Invalid table or view name [BOOKINFO].
used time: 0.372(ms). Execute id is 0.
SQL> 
SQL> 

还原bookinfo表结构和索引、约束等

因为没有数据，索引是看不到的。

SQL> RESTORE TABLE STRUCT FROM BACKUPSET '/dm8/backup/bookinfo_bak_01';
executed successfully
used time: 14.885(ms). Execute id is 600.
SQL> 
SQL> select OWNER,TABLE_NAME,INDEX_NAME from dba_indexes where TABLE_OWNER='TESTU'  and TABLE_NAME='BOOKINFO';

LINEID     OWNER TABLE_NAME INDEX_NAME   
---------- ----- ---------- -------------
1          TESTU BOOKINFO   INDEX33555497

used time: 87.053(ms). Execute id is 601.
SQL> 
SQL> 

还原数据

1、有数据，2、有索引，3、只有备份前的数据；4、备份后新增的bookid4无法恢复

SQL> 
SQL> RESTORE TABLE  FROM BACKUPSET '/dm8/backup/bookinfo_bak_01';
executed successfully
used time: 249.229(ms). Execute id is 602.
SQL> 
SQL>  select * from testu.bookinfo;

LINEID     BOOKID      BOOKCONTENT  BOOKCOVER 
---------- ----------- ------------ ----------
1          1           bookcontent1 bookcover1
2          2           bookcontent2 bookcover2
3          3           bookcontent3 bookcover3

used time: 0.551(ms). Execute id is 605.
SQL> 
SQL> 
SQL> select OWNER,TABLE_NAME,INDEX_NAME from dba_indexes where TABLE_OWNER='TESTU'  and TABLE_NAME='BOOKINFO';

LINEID     OWNER TABLE_NAME INDEX_NAME     
---------- ----- ---------- ---------------
1          TESTU BOOKINFO   IDX_BOOKINFO_ID
2          TESTU BOOKINFO   INDEX33555497

used time: 110.117(ms). Execute id is 604.
SQL> 

更多资讯 达梦社区 https://eco.dameng.com