Home » RDBMS Server » Backup & Recovery » Restore table data till specified date (Oracle 11g,Linux )
Restore table data till specified date [message #618630] Mon, 14 July 2014 08:06 Go to next message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member

Hi,

There is a situation where i have updated table with data till 14th of June 2014
without any backup and found it to wrong and user want to restore table data to
13th of June 2014.Now the user want to remove the data loaded after 13th of June 2014
i.e remove data of 14th of June.

Could you guide me how to restore the table data till 13th of June 2014.

Regards,
Srini

[Updated on: Mon, 14 July 2014 08:07]

Report message to a moderator

Re: Restore table data till specified date [message #618633 is a reply to message #618630] Mon, 14 July 2014 08:17 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
no backup = no solution
Re: Restore table data till specified date [message #618635 is a reply to message #618630] Mon, 14 July 2014 08:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From what do you want to restore if the information is no where?

Re: Restore table data till specified date [message #618640 is a reply to message #618635] Mon, 14 July 2014 09:42 Go to previous messageGo to next message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member

Hi Blackswan/michel,

Is it not possible to remove one day post data that was wrongly loaded into table without backup ?

Regards,
Srini
Re: Restore table data till specified date [message #618641 is a reply to message #618640] Mon, 14 July 2014 09:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Database admin wrote on Mon, 14 July 2014 07:42
Hi Blackswan/michel,

Is it not possible to remove one day post data that was wrongly loaded into table without backup ?

Regards,
Srini


It depends.

You first post used the word "updated"; which means to me changed data.
If existing data was changed, it is not possible to know the previous old values.

This post uses the word "loaded", which can mean new rows were added.
Can you post SQL SELECT that returns only these "loaded" rows? If so, then you can DELETE these rows.
Re: Restore table data till specified date [message #618645 is a reply to message #618640] Mon, 14 July 2014 10:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You could use Log Miner but you have to find someone to do it for you as it is not an easy way.
In addition, you have to ask what do you with data that have been updated after having been wrongly updated. Are some rows depending on some wrongly updated rows and then what to do with them?
And many many other questions.

Re: Restore table data till specified date [message #618650 is a reply to message #618630] Mon, 14 July 2014 11:07 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Have you looked at Flashback Query?
Re: Restore table data till specified date [message #618651 is a reply to message #618650] Mon, 14 July 2014 11:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
gazzag wrote on Mon, 14 July 2014 09:07
Have you looked at Flashback Query?


Really? for one month old data?????????
Re: Restore table data till specified date [message #618652 is a reply to message #618651] Mon, 14 July 2014 11:19 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Well, obviously I don't know his UNDO_RETENTION settings Razz But you're right, I misread June for July.
Re: Restore table data till specified date [message #618681 is a reply to message #618630] Mon, 14 July 2014 15:19 Go to previous messageGo to next message
tim2boles
Messages: 38
Registered: August 2008
Location: Clarksburg, WV
Member
I am thing perhaps they truly do mean 13th of July instead of 13th of June.

With the information given as it is the answers so far do seem correct. I am wondering though when it is said "without backup" do they truly mean the entire database is not being backed up or that just this table was not being backed up.

If the database is backedup, I could see restoring it to a different machine and doing a point-in-time recovery. There is all kinds of potential there depending on the setup of the system. Export/Import, scripts to do comparisons, etc.

The database itself is not being backed up then why worry about the data in the table at all. If you are not backing up your database then the data is not valuable to begin with.

Regards
Tim
Re: Restore table data till specified date [message #618686 is a reply to message #618681] Mon, 14 July 2014 20:59 Go to previous messageGo to next message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member

Hi all,

Thank you all for sharing your inputs here.

Blackswan,
Quote:
You first post used the word "updated"; which means to me changed data.
If existing data was changed, it is not possible to know the previous old values.

This post uses the word "loaded", which can mean new rows were added.
Can you post SQL SELECT that returns only these "loaded" rows? If so, then you can DELETE these rows.


Sorry to say by mistake i used the word 'updated'.Actually its 'data added into table'.

Hi Tim,
Quote:
I am thing perhaps they truly do mean 13th of July instead of 13th of June.

Yes,you are correct .Its for the month of July and not for June.again mistake Sad
Quote:
The database itself is not being backed up then why worry about the data in the table at all. If you are not backing up your database then the data is not valuable to begin with.

Actualy this issue happend during peak hours in the morning and backup is taken everyday in the night.
So could not use backup to restore the data.

Regards,
Srini
Re: Restore table data till specified date [message #618687 is a reply to message #618686] Mon, 14 July 2014 21:10 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
can you post any actual logfile showing what was really done & when it was done and not just your uncertain recount of what you think you saw?

post CREATE TABLE statement(s) for any/every table that was adversely affected by the incorrect data load
Re: Restore table data till specified date [message #618688 is a reply to message #618652] Mon, 14 July 2014 21:14 Go to previous messageGo to next message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member

Hi Gazzag,
I read flashback query and found it to be interesting.
I read the topic 'flashback Data Archive to Recover Data' and found it suitable for answer i am looking for.
I hope the below quote is similar to the one i am looking for.
Quote:
Scenario: Using Flashback Data Archive to Recover Data
An end user recovers from erroneous transactions that were previously committed in the database. The undo data for the erroneous transactions is no longer available, but because the required historical information is available in the Flashback Data Archive, Flashback Query works seamlessly.

Lisa manages a software development group whose product sales are doing well. On November 3, 2007, she decides to give all her level-three employees who have more than two years of experience a salary increase of 10% and a promotion to level four. Lisa asks her HR representative, Bob, to make the changes.

Using the HR web application, Bob updates the employee table to give Lisa's level-three employees a 10% raise and a promotion to level four. Then Bob finishes his work for the day and leaves for home, unaware that he omitted the requirement of two years of experience in his transaction. A few days later, Lisa checks to see if Bob has done the updates and finds that everyone in the group was given a raise! She calls Bob immediately and asks him to correct the error.

At first, Bob thinks he cannot return the employee table to its prior state without going to the backups. Then he remembers that the employee table has Flashback Data Archive enabled.

First, he verifies that no other transaction modified the employee table after his: The commit time stamp from the transaction query corresponds to Bob's transaction, two days ago.

Next, Bob uses these statements to return the employee table to the way it was before his erroneous change:

DELETE EMPLOYEE WHERE MANAGER = 'LISA JOHNSON';
INSERT INTO EMPLOYEE
SELECT * FROM EMPLOYEE
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' DAY)
WHERE MANAGER = 'LISA JOHNSON';
Bob then reexecutes the update that Lisa had requested.


How to ensure Flashback Data Archive is enable in database and would it have any impact to users and database in enabling this feature ?

Regards,
Srini
Re: Restore table data till specified date [message #618706 is a reply to message #618688] Tue, 15 July 2014 00:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Actualy this issue happend during peak hours in the morning and backup is taken everyday in the night.
So could not use backup to restore the data.


I bet by "backup" you mean export and you now see that export is NOT a backup as you can't use it to restore.

Quote:
How to ensure Flashback Data Archive is enable in database


Maybe, once again, you could search and read the documentation.
Hint: if you don't know what is FDA then you had not enable/configure it.
Note: you can use "flashback query" without FDA but most likely you won't be able to get data from 2 days before, too bad you didn't take care of what you post we could tell you about this yesterday and you might be able to recover your data.

As I said, most likely, your last option is Log Miner... if you are ARCHIVELOG mode, of course.

Re: Restore table data till specified date [message #618751 is a reply to message #618688] Tue, 15 July 2014 03:50 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Note that there is a difference between Flashback Query and Flashback Database. The latter needs a bit more configuration at database level - as Michel states, read the documentation - the former relies on the DB_FLASHBACK_RETENTION_TARGET parameter which can be used to specify the time (in seconds) that Oracle will attempt to maintain any data. In fact, there is an article on OraFaq's here. It's a few years old but may help get you started.

HTH
-g
Re: Restore table data till specified date [message #618754 is a reply to message #618751] Tue, 15 July 2014 04:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
the former relies on the DB_FLASHBACK_RETENTION_TARGET parameter


No, the latter: flashback database.
The former, flashback query, relies, by default, on undo data and so UNDO_RETENTION, and more if you have FDA (Flashback Data Archive) active on your table.

Re: Restore table data till specified date [message #618756 is a reply to message #618754] Tue, 15 July 2014 04:14 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
You are correct, Michel. Thank you.
Re: Restore table data till specified date [message #618773 is a reply to message #618686] Tue, 15 July 2014 06:28 Go to previous message
tim2boles
Messages: 38
Registered: August 2008
Location: Clarksburg, WV
Member
Often when trying to help someone it is like shooting a target in the dark with only a general idea of what the target looks like and the choices you have in items to shoot at the target with. You need to communicate your situation and options more completely.

You now say that

Quote:
Actualy this issue happend during peak hours in the morning and backup is taken everyday in the night.
So could not use backup to restore the data.


Don't just assume you can not use the backup to restore the data. You might be right be there are some fairly cleaver people in this forum they might have some iteas on what you can do. Tell us more about the 'backup' you do. How often do you backup your system up? What commands do you use to backup your system? How long are the backups maintained?

You now say that
Quote:
Sorry to say by mistake i used the word 'updated'.Actually its 'data added into table'.


Well "added" often can be "deleted" if the primary key is known and used to add the data. Or if perhaps there are other "key" fields within the table that will allow you to narrow you delete statement to the appropriate data loaded.

So as Blackswan indicated give us the details...
Quote:
can you post any actual logfile showing what was really done & when it was done and not just your uncertain recount of what you think you saw?

post CREATE TABLE statement(s) for any/every table that was adversely affected by the incorrect data load


If you have the actual DML that inserted the rows, let us know that. If not the actual DML give us details on the process and include any log files that might be helpful.

A solution is only as good as the building blocks you provide. Right now I feel we are stumbling over pebbles when there may be good foundation stones that can be used.
Previous Topic: RMAN Restore from different path
Next Topic: RMAN backups increase in size and don't delete
Goto Forum:
  


Current Time: Thu Mar 28 23:51:01 CDT 2024