[达梦数据库]备份恢复至特定时间

1 创建测试表

SQL> create table utest(uid varchar(10),uname varchar(10));
操作已执行
已用时间: 20.926(毫秒). 执行号:401.
SQL> insert into utest values(2,'HR'); 
影响行数 1

已用时间: 0.680(毫秒). 执行号:406.
SQL> insert into utest values(1,'SCOTT');
影响行数 1

已用时间: 1.179(毫秒). 执行号:405.
SQL> commit;
操作已执行
已用时间: 1.419(毫秒). 执行号:407.
SQL> select * from utest;

行号     UID UNAME
---------- --- -----
1          2   HR
2          1   SCOTT

已用时间: 0.620(毫秒). 执行号:427.

2 备份数据库

SQL> backup database backupset '/dm/dmbak/DB_FULL_LSN';
操作已执行
已用时间: 00:00:03.863. 执行号:428.

3 修改数据

SQL> insert into utest values(3,'PETER');
影响行数 1

已用时间: 1.139(毫秒). 执行号:429.
SQL> commit;
操作已执行
已用时间: 1.036(毫秒). 执行号:430.
SQL> alter system switch logfile;
操作已执行
已用时间: 13.530(毫秒). 执行号:0.

4 查询时间

SQL> select sysdate;

行号     SYSDATE            
---------- -------------------
1          2021-01-25 10:49:29

已用时间: 1.431(毫秒). 执行号:431.
SQL> select file_lsn from v$rlog;

行号     FILE_LSN            
---------- --------------------
1          52805

已用时间: 0.806(毫秒). 执行号:432.

5 模拟误操作

SQL> truncate table utest;
操作已执行
已用时间: 5.708(毫秒). 执行号:433.
SQL> select * from utest;
未选定行

已用时间: 0.794(毫秒). 执行号:434.
SQL> commit;
操作已执行
已用时间: 0.603(毫秒). 执行号:435.

6 关闭数据库

[root@DM8 ~]# service DmServicetest stop
Stopping DmServicetest:                                    [ OK ]

7 还原

[dmdba@DM8 ~]$ dmrman 
dmrman V8

RMAN> restore database '/dbdata/DAMENG/dm.ini' from backupset '/dm/dmbak/DB_FULL_LSN';
restore database '/dbdata/DAMENG/dm.ini' from backupset '/dm/dmbak/DB_FULL_LSN';
file dm.key not found, use default license!
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:00][Remaining:00:00:00]                                 
restore successfully.
time used: 659.942(ms)

RMAN> recover database '/dbdata/DAMENG/dm.ini' with archivedir '/arch' until time '2021-01-25 10:49:29';
recover database '/dbdata/DAMENG/dm.ini' with archivedir '/arch' until time '2021-01-25 10:49:29';
Database mode = 0, oguid = 0
EP[0]'s cur_lsn[52778]
EP:0 total 2 pkgs applied, percent: 10%
EP:0 total 4 pkgs applied, percent: 21%
EP:0 total 6 pkgs applied, percent: 31%
EP:0 total 8 pkgs applied, percent: 42%
EP:0 total 10 pkgs applied, percent: 52%
EP:0 total 12 pkgs applied, percent: 63%
EP:0 total 14 pkgs applied, percent: 73%
EP:0 total 16 pkgs applied, percent: 84%
recover successfully!
time used: 300.659(ms)
#也可以使用UNTIL LSN：
recover database '/dbdata/DAMENG/dm.ini' with archivedir '/arch' UNTIL LSN 52805;

RMAN> recover database '/dbdata/DAMENG/dm.ini' update db_magic;
recover database '/dbdata/DAMENG/dm.ini' update db_magic;
Database mode = 0, oguid = 0
EP[0]'s cur_lsn[52811]
recover successfully!
time used: 00:00:01.028

8 测试

SQL> select * from utest;

行号     UID UNAME
---------- --- -----
1          2   HR
2          1   SCOTT
3          3   PETER

已用时间: 4.279(毫秒). 执行号:400.