Dear Readers,
This article will see the following Table Restore Depends On Time in Oracle.
We receive requests from the requester stating that they have lost data of some tables and want us to restore it.
As it is a time-consuming method to restore the whole database and then exp/imp requested table, we can perform this within a short time in the same prod database using flashback from undo data.
If undo data persisted in the database so we could recover it from undo.
We need to know when the data was fine from the requester and create a new temp table with the restored data in prod.
Below are the steps to be performed considering the table status was good at 1 AM on 15th May 2019.
1. Find the current count of records in the impacted table and the flashback count of records (till the time provided by the requester) and provide it to the requester to validate.
|
1 2 3 4 5 6 7 8 9 10 |
SQL> select * from DLA_REPORT.TB309_TEC_DAT_USER_COMPANY; SQL> select count(*) from DLA_REPORT.TB309_TEC_DAT_USER_COMPANY; COUNT(*) ---------- 2 SQL> SELECT count(*) FROM DLA_REPORT.TB309_TEC_DAT_USER_COMPANY AS OF TIMESTAMP TO_TIMESTAMP('2019-05-15 01:00:00', 'YYYY-MM-DD HH24:MI:SS'); COUNT(*) ---------- 1011 |
2.On confirmation from requester that count at 1AM is OK to be restored, create a new temp table as stated below:
|
1 2 3 |
SQL> create table DLA_REPORT.TB309_TEMP as select * FROM DLA_REPORT.TB309_TEC_DAT_USER_COMPANY AS OF TIMESTAMP TO_TIMESTAMP('2019-05-15 01:00:00', 'YYYY-MM-DD HH24:MI:SS'); Table created |
3. Provide the count and table name to requester.
|
1 2 3 4 5 6 7 |
SQL> select * from DLA_REPORT.TB309_TEMP; SQL> select count(*) from DLA_REPORT.TB309_TEMP; COUNT(*) ---------- 1011 |
Once restored the data by flashbacking to temp table we can change the application code to point particular table or transfer the data to old table.
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTEXPERTS is always active on below social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Loading...
