达梦做时间点的不完全恢复
环境:
OS:Centos 7
DB:DM8
1.模拟数据写入
[dmdba@host01 ~]$ disql hxl/dameng123
Server[LOCALHOST:5236]:mode is normal, state is open
login used time : 3.718(ms)
disql V8
insert into tb_test values(3,'name3');
insert into tb_test values(4,'name4');
insert into tb_test values(5,'name5');
insert into tb_test values(6,'name6');
insert into tb_test values(7,'name7');
insert into tb_test values(8,'name8');
insert into tb_test values(9,'name9');
insert into tb_test values(10,'name10');
SQL> select * from tb_test;
LINEID id name
---------- -- ------
1 1 name1
2 2 name2
3 3 name3
4 4 name4
5 5 name5
6 6 name6
7 7 name7
8 8 name8
9 9 name9
10 10 name10
10 rows got
used time: 0.474(ms). Execute id is 610.
记录下当前的时间,等会我们就恢复到这个时间点
SQL> select now();
LINEID now()
---------- --------------------------
1 2024-03-21 09:48:35.553247
used time: 0.491(ms). Execute id is 611.
或是记录当前的LSN,还可以通过LSN进行不完全恢复
SELECT FILE_LSN FROM V$RLOG
2.备份数据库和归档日志
backup database full backupset '/dmdbms/bak/fullbak_20240321';
backup archivelog all delete input to "archbak_20240321" backupset '/dmdbms/bak/archbak_20240321';
3.删除表
SQL> drop table tb_test;
executed successfully
used time: 54.792(ms). Execute id is 615.
SQL> select now();
LINEID now()
---------- --------------------------
1 2024-03-21 10:01:29.078014
used time: 0.632(ms). Execute id is 616.
##############################时间点恢复############################
1.停掉当前实例
systemctl stop DmServiceDMSERVER.service
2.删除之前实例的目录
[dmdba@host01 data]$ cd /dmdbms/data
[dmdba@host01 data]$ rm -rf DAMENG/
3.初始化一个实例,实例名与之前一致
/dmdbms/product/bin/dminit path=/dmdbms/data charset=1 case_sensitive=0 length_in_char=0 db_name=DAMENG instance_name=DAMENG SYSDBA_PWD="dameng100" SYSAUDITOR_PWD="dameng100"
4.使用归档日志和全备进行还原
[dmdba@localhost ~]$ dmrman
RMAN>restore archive log from backupset '/dmdbms/bak/archbak_20240321' TO ARCHIVEDIR'/dmdbms/arch' overwrite 2;
RMAN>restore database '/dmdbms/data/DAMENG/dm.ini' from backupset '/dmdbms/bak/fullbak_20240321'; ##还原备份集
RMAN>recover database '/dmdbms/data/DAMENG/dm.ini' with archivedir '/dmdbms/arch' until time '2024-03-21 09:48:35'; ##通过归档日志完全恢复
RMAN>recover database '/dmdbms/data/DAMENG/dm.ini' update db_magic;
说明:
1.若不带until time 就是完全恢复.
2.不完全恢复包括until time和until lsn,使用 SELECT FILE_LSN FROM V$RLOG查LSN
5.启动数据库
systemctl start DmServiceDMSERVER.service
6.验证
[dmdba@host01 ~]$ disql hxl/dameng123
Server[LOCALHOST:5236]:mode is normal, state is open
login used time : 4.821(ms)
disql V8
SQL> select * from tb_test;
LINEID id name
---------- -- ------
1 1 name1
2 2 name2
3 3 name3
4 4 name4
5 5 name5
6 6 name6
7 7 name7
8 8 name8
9 9 name9
10 10 name10
10 rows got
used time: 1.175(ms). Execute id is 501.
可以看到数据已经恢复了.
