oracle Error Delete Data Table Restore Two Methods of oracle Restore

  • 2021-10-25 00:08:10
  • OfStack

1. If you just deleted it, there are two ways:

First, show parameter undo; Command to view the database parameter undo_retention setting at that time.

Shows as follows:


undo_management   string   AUTO
undo_retention  integer 10800
undo_suppress_errors  boolean  FALSE
undo_tablespace   string   UNDOTBS1

undo_retention (holding force), 10800 units are seconds. That is, three hours.

Modify the default undo_retention parameter settings:


ALTER SYSTEM SET undo_retention=10800 SCOPE=BOTH;

Method 1, through the flashback function provided by oracle:


exec dbms_flashback.enable_at_time ( to_date ( '2007-07-23 10:21:00','yyyy-mm-dd hh24:mi:ss' )); 
set serveroutput on
DECLARE r_temp hr.job_history%ROWTYPE;
CURSOR c_temp IS SELECT * FROM hr.job_history;
BEGIN
OPEN c_temp;
dbms_flashback.disable;
LOOP
FETCH c_temp INTO r_temp;
EXIT WHEN c_temp%NOTFOUND;
insert into hr.job_history ( EMPLOYEE_ID,JOB_ID,START_DATE,END_DATE )  values  ( r_temp.EMPLOYEE_ID,r_temp.JOB_ID,r_temp.START_DATE,r_temp.END_DATE ); 
commit;
END LOOP;
CLOSE c_temp;
END;

Method 2, insert into hr. job_history


select * from hr.job_history as of timestamp to_timestamp ( '2007-07-23 10:20:00', 'yyyy-mm-dd hh24:mi:ss' ); 

This method is simple and easy to master. The function and the above one kind of time are the time before your misoperation, and it is best to be close to the misoperation, because the data time saved in the rollback hold section of oracle has a fixed time limit determined by the parameter value of undo_retention.

2. If it has been deleted for 1 period of time, but you have a relatively new database backup, restore it through backup. Create a new library, restore the backup, export the table data, and then import it into the current library.

3. If it has been deleted for 1 period of time and there is no backup, but the data will be written to other 1 related tables at the same time when it is written to the table, then try to take data from other tables by writing SQL statement and send insert to the deleted table.

4. Restore to backup table


create table tableName_bak
as
select * from tableName as of TIMESTAMP to_timestamp ( '20081126 103435','yyyymmdd hh24miss' ); 


Related articles: