Oracle基于数据挖掘的不完全恢复

1. SQL> select scn,to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') TIMESTAMP,SQL_REDO,SQL_UNDO from v$logmnr_contents where table_name = 'EMP';
2. SCN TIMESTAMP        SQL_REDO         SQL_UNDO
3. ---------- -------------------- -------------------- --------------------
4. 2507301 2017-04-10 22:54:49  drop table emp purge

复制代码

到此为止，我们已经挖掘成功，找到scn和时间点
注意：如果用时间点恢复的时候要减1秒
最后关闭数据挖掘
SQL> exec dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed.

4. 不完全恢复

1. RMAN> shutdown immediate;
2. using target database control file instead of recovery catalog
3. database closed
4. database dismounted
5. Oracle instance shut down
6. #先关闭数据库
7. RMAN> startup mount;
8. connected to target database (not started)
9. Oracle instance started
10. database mounted
11. Total System Global Area     584568832 bytes
12. Fixed Size                     2230552 bytes
13. Variable Size                444597992 bytes
14. Database Buffers             130023424 bytes
15. Redo Buffers                   7716864 bytes
16. #启动倒mount状态下
17. RMAN> run{
18. 2> set until scn 2507301;
19. 3> restore database;
20. 4> recover database;
21. 5> alter database open resetlogs;
22. 6> }
23. executing command: SET until clause
24. Starting restore at 10-APR-17
25. allocated channel: ORA_DISK_1
26. channel ORA_DISK_1: SID=137 device type=DISK
27. channel ORA_DISK_1: starting datafile backup set restore
28. channel ORA_DISK_1: specifying datafile(s) to restore from backup set
29. channel ORA_DISK_1: restoring datafile 00001 to /u01/oracle/oradata/orcl/system01.dbf
30. channel ORA_DISK_1: restoring datafile 00002 to /u01/oracle/oradata/orcl/sysaux01.dbf
31. channel ORA_DISK_1: restoring datafile 00004 to /u01/oracle/oradata/orcl/users01.dbf
32. channel ORA_DISK_1: restoring datafile 00005 to /u01/oracle/oradata/orcl/user02.dbf
33. channel ORA_DISK_1: restoring datafile 00006 to /u01/oracle/oradata/orcl/tmpspace0327
34. channel ORA_DISK_1: restoring datafile 00007 to /u01/oracle/oradata/orcl/undotbs02.dbf
35. channel ORA_DISK_1: restoring datafile 00008 to /u01/oracle/oradata/orcl/example.dbf
36. channel ORA_DISK_1: reading from backup piece /tmp/0us1cc3q_1_1
37. channel ORA_DISK_1: piece handle=/tmp/0us1cc3q_1_1 tag=TAG20170410T225138
38. channel ORA_DISK_1: restored backup piece 1
39. channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
40. Finished restore at 10-APR-17
41. Starting recover at 10-APR-17
42. using channel ORA_DISK_1
43. starting media recovery
44. media recovery complete, elapsed time: 00:00:01
45. Finished recover at 10-APR-17
46. database opened
47. # 用上面run{}脚本进行恢复，restore，recover,open resetlogs;
48. # 成功

复制代码

5. 验证

1. <blockquote>SQL> desc emp;
2. Name                      Null?    Type
3. ----------------------------------------- -------- ----------------------------
4. EMPNO                     NOT NULL NUMBER(4)
5. ENAME                          VARCHAR2(10)
6. JOB                            VARCHAR2(9)
7. MGR                            NUMBER(4)
8. HIREDATE                       DATE
9. SAL                            NUMBER(7,2)
10. COMM                           NUMBER(7,2)
11. DEPTNO                         NUMBER(2)
12. SQL> select count(*) from emp;
13. COUNT(*)
14. ----------
15. # 被删除的emp表恢复成功

复制代码

如果有对软件测试感兴趣的小伙伴可以了解更多：点击进群https://jq.qq.com/?_wv=1027&k=9MuyI1n