Use log mining to restore accidentally deleted tables, data, and views from incomplete Oracle recovery

Use log mining to restore accidentally deleted tables, data, and views from incomplete Oracle recovery

1. /*--------------------------
2. Prerequisites:
3. 1. DB works in archive mode;
4. 2. Prepare cold data files;
5. ---------------------------*/

 

1. Col scriptForA80
2. Col SQL _REDOForA80
4. -- Concatenate the statement where to dig
5. Select 'Exec dbms_logmnr.add_logfile ('''| Member |''')' AsScriptFromV $ logfile;
7. -- Where to dig
8. ExecDbms_logmnr.add_logfile ('/Oracle/oradata/orcl_new/redo03.log');
9. ExecDbms_logmnr.add_logfile ('/Oracle/oradata/orcl_new/redo02.log');
10. ExecDbms_logmnr.add_logfile ('/Oracle/oradata/orcl_new/redo01.log');
13. -- Start digging
14. ExecDbms_logmnr.start_logmnr;
16. -- Find the result --> the timestamp of the statement to be restored;
17. SelectScn, SQL _redoFromV $ logmnr_contentsWhereSQL _redoLike 'Drop table emp %';
19. SCN SQL _REDO
20. ------------------------------------------------------------------------------------------
21. 1036547Drop TableT1 purge;
22. 1037333Drop TableScott. emp purge;
23. -- 1037333 --> timestamp to be restored
25. -- Disable DB
26. Shutdown abort
28. -- Copy the cold backup file-*. dbf to the data file directory and overwrite it; because the data in the table space such as sys and user should be redone once using logs;
29. [Oracle @ OracleWeblogic orcl_new_bk] $ cp *. dbf ../orcl_new/
31. -- Start to mount;
32. Startup mount
34. -- Restore to the time point of 1037333 drop table scott. emp purge;
35. -- Requirement: trc
36. RecoverDatabaseUntil change 1035742;
38. -- Start DB in resetlogs;
39. Alter Database OpenResetlogs;
42. Principle
43. 1. The cold backup data file *. dbf is older than the current one, so the scn of the data file must be smaller than the current one;
44. 2. Use the log mining method to find the scn for table deletion in the log;
45. 3. In the database shutdown state, use the cold backup data file *. dbf to overwrite the current *. dbf file;-- Ensure data file integrity
46. 4. Start DB to mount and restore to the time point of log mining (redo the data file once using logs)
47. 5. resetlogOpen --- The previous logs are overwritten;
49. PS: the database has four statuses: shutdown, nomount, mount,Open
50. Shutdown refers to the shutdown status.
51. Nomount indicates that the Root parameter file has constructed the instance status.
52. Mount refers to opening the control file for the instance and reading the relevant information in the control file, but not verifying
53. OpenIt is used to verify the relevant files. If there is no problem, open the file so that users can access it.