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

模拟场景

模拟对用户表空间TBUSER的数据文件进行rm删除后进行恢复

用户表空间TBUSER的文件情况

SQL> select id,NAME from v$tablespace;

LINEID     ID          NAME  
---------- ----------- ------
1          0           SYSTEM
2          1           ROLL
3          3           TEMP
4          4           MAIN
5          6           TBUSER

used time: 0.834(ms). Execute id is 1527.
SQL> select id,PATH from v$datafile where GROUP_ID =6;

LINEID     ID          PATH                           
---------- ----------- -------------------------------
1          0           /dm8/mdbms/data/OA/tbuser01.dbf

used time: 1.492(ms). Execute id is 1546.
SQL> 

用户表空间TBUSER下有那些表

SQL> select OWNER,TABLE_NAME from dba_tables where TABLESPACE_NAME='TBUSER';

LINEID     OWNER  TABLE_NAME  
---------- ------ ------------
1          SYSDBA TB01
2          TESTU  BOOKINFO3
3          TESTU  BOOKINFO_BAK
4          TESTU  BOOKINFO

used time: 39.095(ms). Execute id is 1515.
SQL> 

表 BOOKINFO 里面已经存在的数据

SQL> select bookid from testu.bookinfo;

LINEID     BOOKID     
---------- -----------
1          1
2          2
3          3
4          4

used time: 0.977(ms). Execute id is 1516.
SQL> 

备份用户表空间TBUSER

SQL>BACKUP TABLESPACE TBUSER BACKUPSET '/dm8/backup/tb_tbuser_bak_01';
DMSQL executed successfully
used time: 336.872(ms). Execute id is 1568.
SQL> 

模拟更新bookinfo数据、插入一些其他数据、切换日志

SQL> begin
2   for i in 1..1000 loop
3   insert into tb01 values (i);
4   end loop;
5   end;
6   /
DMSQL executed successfully
used time: 5.888(ms). Execute id is 1570.
SQL> 
SQL> alter system switch logfile;
executed successfully
used time: 3.413(ms). Execute id is 0.
SQL> 

SQL> insert into testu.bookinfo values (5,null,null);
affect rows 1

used time: 1.260(ms). Execute id is 1573.
SQL> commit;
executed successfully
used time: 1.000(ms). Execute id is 1574.
SQL> 

核对bookinfo有5条记录

SQL> select bookid from testu.bookinfo;

LINEID     BOOKID     
---------- -----------
1          1
2          2
3          3
4          4
5          5

used time: 0.326(ms). Execute id is 1577.
SQL> 

备份归档

SQL> BACKUP ARCHIVE LOG ALL delete input BACKUPSET '/dm8/backup/arch_bak_all' ;
executed successfully
used time: 824.260(ms). Execute id is 1578.
SQL>
SQL> BACKUP ARCHIVE LOG ALL delete input BACKUPSET '/dm8/backup/arch_bak_all_2' ;
executed successfully
used time: 00:00:03.739. Execute id is 1580.
SQL> 

模拟故障

[dmdba@dmdb1 arch]$ rm /dm8/mdbms/data/OA/tbuser01.dbf
[dmdba@dmdb1 arch]$ 

强制触发故障

 
SQL> alter tablespace tbuser offline;
alter tablespace tbuser online;
executed successfully
used time: 977.121(ms). Execute id is 1584.
SQL> alter tablespace tbuser online;
[-2405]:File or Directory [/dm8/mdbms/data/OA/tbuser01.dbf] does not exist.
used time: 2.210(ms). Execute id is 0.
SQL> select bookid from testu.bookinfo;
select bookid from testu.bookinfo;
[-3408]:tablespace [TBUSER] is offline.
used time: 0.326(ms). Execute id is 0.
SQL> 

恢复归档

RMAN> restore ARCHIVE LOG FROM BACKUPSET '/dm8/backup/arch_bak_all' to ARCHIVEDIR '/dm8/restore/arch_bak_all';
restore ARCHIVE LOG FROM BACKUPSET '/dm8/backup/arch_bak_all' to ARCHIVEDIR '/dm8/restore/arch_bak_all';

restore successfully.
time used: 37.780(ms)
RMAN> restore ARCHIVE LOG FROM BACKUPSET '/dm8/backup/arch_bak_all_2' to ARCHIVEDIR '/dm8/restore/arch_bak_all_2';
restore ARCHIVE LOG FROM BACKUPSET '/dm8/backup/arch_bak_all_2' to ARCHIVEDIR '/dm8/restore/arch_bak_all_2';

restore successfully.
time used: 00:00:03.736
RMAN> 
RMAN> 

RESTORE和RECOVER用户表空间TBUSER

RMAN>  RESTORE DATABASE '/dm8/mdbms/data/OA/dm.ini' TABLESPACE TBUSER  DATAFILE '/dm8/mdbms/data/OA/tbuser01.dbf'  FROM BACKUPSET '/dm8/backup/tb_tbuser_bak_01';
RESTORE DATABASE '/dm8/mdbms/data/OA/dm.ini' TABLESPACE TBUSER DATAFILE '/dm8/mdbms/data/OA/tbuser01.dbf' FROM BACKUPSET '/dm8/backup/tb_tbuser_bak_01';
Database mode = 0, oguid = 0
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
begin redo pwr log collect, last ckpt lsn: 25556728 ...
redo pwr log collect finished
EP[0]'s cur_lsn[25556739], file_lsn[25556739]
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:34][Remaining:00:00:00]                                 
restore successfully.
time used: 00:00:34.663
RMAN> 

RMAN> RMAN>  recover DATABASE '/dm8/mdbms/data/OA/dm.ini' TABLESPACE TBUSER WITH ARCHIVEDIR '/dm8/restore/arch_bak_all','/dm8/restore/arch_bak_all_2';
recover DATABASE '/dm8/mdbms/data/OA/dm.ini' TABLESPACE TBUSER WITH ARCHIVEDIR '/dm8/restore/arch_bak_all' , '/dm8/restore/arch_bak_all_2';
Database mode = 0, oguid = 0
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
begin redo pwr log collect, last ckpt lsn: 25556737 ...
redo pwr log collect finished
EP[0]'s cur_lsn[25556739], file_lsn[25556739]
[Percent:100.00%][Speed:1630.00PKG/s][Cost:00:00:10][Remaining:00:00:00]                            
recover successfully.
time used: 00:00:10.619
RMAN> 
RMAN> 

online 用户表空间TBUSER和验证数据

SQL> alter tablespace tbuser online;
executed successfully
used time: 7.469(ms). Execute id is 1565.
SQL> select bookid from testu.bookinfo;

LINEID     BOOKID     
---------- -----------
1          1
2          2
3          3
4          4
5          5

used time: 0.420(ms). Execute id is 1588.
SQL> 

达梦在线服务平台：https://eco.dameng.com