DATA RECOVERY from ORACLE DATABASE 

What do you mean by Data, Data is nothing but information what we stored in relational Database. Here we will deal with data error while updating the records.



Case: Let's imaging we have Table EMPLOYEES and DEPARTMENT in schema HR. 

Let Company have decided to update the Salary of few selected EMPLOYEES. So we have different Table TEMP_HIKE where we have two 
columns only SALARY and EMPLOYEE_ID. 

Let's Check the View the Of EMPLOYEES and TEMP_HIKE;

Now check what mistake a developer Done:

UPDATE EMPLOYEES SET SALARY= (SELECT SALARY FROM TEMP_HIKE where EMPLOYEES.EMPLOYEE_ID=TEMP_HIKE.ID);

What happened after this Query:

Now will discuss the Solution:

First run the: To get the time,  Now check when you run the update on table.

SELECT  SYSTIMESTAMP from DUAL;

select * from EMPLOYEES as of timestamp TO_TIMESTAMP('2013-06-14 09:20:00', 'YYYY-MM-DD HH:MI:SS');

Thank GOD we have Data some where, Now we need it back in action.
So as a developer we got confidence, If we have Data we can get it back...... :)



declare
begin
for i in (select employee_id, salary from employees as of timestamp TO_TIMESTAMP('2013-06-14 09:20:00', 'YYYY-MM-DD HH:MI:SS')) loop
update EMPLOYEES set salary=i.salary where EMPLOYEE_ID=i.employee_id;
end loop;
end;

commit;

so now we have our data return.

So,  Now for practice you can correct the Query what cause all this troubles, However what we learn from this is How to recover if mistake happened.

GOOD PRACTICES:

1> RUN SELECT QUERY BEFORE UPDATE COMMAND TO ENSURE ONLY DESIRE ROWS IS IMPACTING.

2> ALWAYS KEEP THE BACKUP BEFORE UPDATE

3> ALWAYS KEEP AUTO COMMIT DISABLE 

Thanks:

Dewendra K Pandey

TA-Oracle
dewendra1@gmail.com

CTECHH-GROUP on FaceBook

      Feel free to contact for any Question/Suggestion.