Use log mining to restore accidentally deleted tables, data, and views from incomplete Oracle recovery
- /*--------------------------
- Prerequisites:
- 1. DB works in archive mode;
- 2. Prepare cold data files;
- ---------------------------*/
- Col scriptForA80
- Col SQL _REDOForA80
- -- Concatenate the statement where to dig
- Select 'Exec dbms_logmnr.add_logfile ('''| Member |''')' AsScriptFromV $ logfile;
- -- Where to dig
- ExecDbms_logmnr.add_logfile ('/Oracle/oradata/orcl_new/redo03.log');
- ExecDbms_logmnr.add_logfile ('/Oracle/oradata/orcl_new/redo02.log');
- ExecDbms_logmnr.add_logfile ('/Oracle/oradata/orcl_new/redo01.log');
- -- Start digging
- ExecDbms_logmnr.start_logmnr;
- -- Find the result --> the timestamp of the statement to be restored;
- SelectScn, SQL _redoFromV $ logmnr_contentsWhereSQL _redoLike 'Drop table emp %';
- SCN SQL _REDO
- ------------------------------------------------------------------------------------------
- 1036547Drop TableT1 purge;
- 1037333Drop TableScott. emp purge;
- -- 1037333 --> timestamp to be restored
- -- Disable DB
- Shutdown abort
- -- 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;
- [Oracle @ OracleWeblogic orcl_new_bk] $ cp *. dbf ../orcl_new/
- -- Start to mount;
- Startup mount
- -- Restore to the time point of 1037333 drop table scott. emp purge;
- -- Requirement: trc
- RecoverDatabaseUntil change 1035742;
- -- Start DB in resetlogs;
- Alter Database OpenResetlogs;
- Principle
- 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;
- 2. Use the log mining method to find the scn for table deletion in the log;
- 3. In the database shutdown state, use the cold backup data file *. dbf to overwrite the current *. dbf file;-- Ensure data file integrity
- 4. Start DB to mount and restore to the time point of log mining (redo the data file once using logs)
- 5. resetlogOpen --- The previous logs are overwritten;
- PS: the database has four statuses: shutdown, nomount, mount,Open
- Shutdown refers to the shutdown status.
- Nomount indicates that the Root parameter file has constructed the instance status.
- Mount refers to opening the control file for the instance and reading the relevant information in the control file, but not verifying
- OpenIt is used to verify the relevant files. If there is no problem, open the file so that users can access it.