Data recovery using stored procedure

[mohuza zack]

Hello, does anyone knows about how to do data recovery using stored procedure?

It is not the database recovery or the whole recovery but it is data recovery. For example, if A inserted the value 50, and B updated the value to 60, how do we go back to the value 50?

I try to surf the Internet but I am not able to find it. I also tried search for it in oracle forums.

If anyone can help me by giving the useful links, articles, journals, etc on the data recovery using stored procedure, I appreciate it so much.

By recover the data using the stored procedure, I assume that there is an audit trail. The audit trail will keep track the changes of data. And my job is how to go back to the previous state of data using stored procedure based on what I have in the audit trail.

By the way, I will use Oracle 10g or 11g.

Thank you in advance.

[Martin Vashko]

You're probably looking for flashback queries, which are queries that return data from the database as they existed some time in the past. You don't even need a stored procedure, this is just a special syntax of select queries, or an entire session can be brought "back in time" using DBMS_FLASHBACK package.

Generally, there are two flavors of this mechanism:

1) Your database has a Flashback Data Archive (sometimes called Total Recall) set up and running. You can query as back in the past as your archive is configured to keep data for, and generally this mechanism is fast.

2) Your database does not have the above mentioned feature set up. Flashback queries do still work, but they bring up the past data from undo tablespace. The history is available only as long as it is kept in the undo, which depends on total undo size, the database activity and some initialization parameter(s). At most five days or so of history can be hold in undo tablespace in this mode, and the history gets lost when the database is restarted. Also, retrieving past data on table that gets heavily modified can take a long time, roughly proportional to the age of that data.

I don't think standard Oracle audit trail can be used for this (I think the trail does not cover history of the data), but I'm not sure, as I've never used it. Consult Oracle documentation on auditing if you're interested in it, though I'm convinced that Oracle flashback is what you want to use.

Here is a link to Oracle documentation covering flashback, I'd suggest to start there.

Note: data recovery is a term that covers the problematic of bringing the database up from a backup, possibly to a certain point in time somewhere in the past. While the Point-in-time recovery is loosely related to what you need to do, this is not possible to do from an application, it is a database administration related task.

[mohuza zack]

Thank You Martin Vajsar for your very brief explaination..
Before this I have found about the flashback memories..
But like you said, the history will not remain long..

So currently, I'm trying to find how to do the data recovery using stored procedure because it was proposed by my lecturer..
Right now, there is already a trigger that may fired when the user key in data into the database through the application or in the DBMS itself..
So the trigger is used to create audit trail that may keep track the activities such as editing the column,adding the table, and so on..
If a person manipulate the data in the table without telling anyone, database administrator still may know who do that and turn the data to the original state..
Still finding on the stored procedure used to recover data in database..

Thank you in advance..

[Martin Vashko]

I had written a longer reply regarding flashback functionality, unfortunately I've lost it when I've accidentally hit the Back button. Yuck.

I'll try to summarize: I'd suggest to reconsider using Flashback Data Archive/Total Recall. Your requirements are immensely broad and would require triggers of almost every kind that exists in Oracle. You'd need extensive knowledge of Oracle inner mechanics to get them right. Re-read carefully the chapter I've posted you last time, you'll find it covers every requirement you've posted so far.

As your proposed solution would be custom built from the scratch, there is probably no stored procedure in Oracle or on the internet that you could use as it is. There might be some products or packages that would provide this functionality, but given that Oracle's Flashback archive is so much better, well supported and for many corporate users for free (its part of Enterprise Edition right out of the box), I don't think there would be many such products still actively maintained. You might try to look for these products again, but don't use the term "data recovery" - it's meaning is completely different. Good keywords probably are "data archive", "history tables", "data auditing" and the like.

Yet again - given how extensive your requirements are (generally, a DBA should be able to undo any change to your database?), the task is immense. Any good DBA should already know the flashback features and use them as a matter of fact - I'd be surprised if a good DBA trusted a custom, trigger-based solution more than Oracle provided, Oracle supported solution for critical, one-time operations you've described in your post.

[mohuza zack]

Martin Vajsar, I am looking forward to your suggestion. I have go through to the web page you suggested and that is quite interesting. I don't know that Oracle provide such an interesting technique for recovery since I am only student. You have give me a very good view of perspective.

I have a question. Let's say in an organisation, we have 2 person that have an access to the Oracle database. First one is AAA and second is BBB.
AAA insert a data, and BBB overwrite it on purpose or accidentally. We might say this is the 'back door' entry which in the future may lead to the fight or an argue between themselves.
Can I have your opinion and how to solve this problem?

Thank you so much...

[Martin Vashko]

I can answer your question only generally, since I didn't use all of the Oracle tools that come to play here. I'd say the situation you described would touches three main topics:

Security: users should be given only those privileges they need for their work. That way the possibility of destroying important data might be mitigated right from the start. You might grant permission only on tables that are really needed to a user (or role), or create views that would provide only subset of columns from a table to a user (and grant select/update on them), or use fine grained access control (FGAC), which allows to define rows that can be accessed by individual users. Another possibility is to create stored procedures (or better, packages) in the database and allow any modification to happen using these procedures only. You'll be able to put any authorization logic you may ever need. The keyword here is security, search Oracle documentation for that.

Accountability: you want to be able to track changes made to tables or schema object. The damage will be allowed to happen, but you'll be able to find out the culprits. The Oracle database again provides tools for this, but some solution using triggers (or better, stored procedures dedicated to changing data) is also imaginable, auditing requirements may vary considerably. If individual transactions consist of changes to many rows or many tables, you might want to track these actions on a grander scale, not from individual row changes. Tracing schema changes (table drops, for example) will on the other hand be best done using built in tools. The keyword here to search in the documentation is auditing.

Recoverability: you need to be able to undo a bad transaction, or to query the data as they existed some time in the past. Depending on your exact needs, a good backup strategy (and a test that your backup is actually usable!) might be enough. If not, the flashback functionalities we've talked about before is what you need. The keywords here are probably backup or flashback.

Depending on your needs, you'll might employ any combination of these approaches in your application (social networking site can probably do with the security approach alone - plus the most basic backup of course, while back-office of a bank definitely needs all three of these topics covered deep and wide).

Bear in mind this is only the most basic description. You'll probably want to study more of Oracle database. I strongly suggest to start with Database Concepts Guide, though it is a long read, it will give you the broad picture of what Oracle is and what can it do. Chapter 20 of this guide covers the Security and Auditing, by the way. Only basics are covered there, specific topics have dedicated guides of their own. The whole documentation is online for free, you should be able to find what you need there once you get used to its structure a bit.

Another valuable resource is the AskTom site. Although the site is open for new question only sporadically (the site is maintained by a single person, Thomas Kyte, who is one of the most respected Oracle specialists out there, and answers are limited by his spare time), almost any imaginable Oracle topics has already been thoroughly discussed here. Do read the old discussion threads. I'd add just a small advice: I have better luck searching the site using Google (using the site: keyword) than via the search engine provided on its pages.

[mohuza zack]

Thank you so much Martin..
A lot of information you gave me..

By the way, I am using Oracle 11g..
I have found case studies about the company who use flashback and looks like the flashback technologies give a lot of benefits for them..

Is it has being used widely nowadays?
Maybe I should ask any DBA here if they know about the flashback query...
I also found a lot of techniques in database recovery for example, mirroring and so on...
But like I said before, it is a whole database recovery and I just need to recover the data for example the user delete the data on purpose...
I like to know of there are any other solutions than the flashback and their impacts?

Thank you...

[Martin Vashko]

Regarding flashback: I've no idea how widespread it's usage is, neither how to find that out. In my opinion, good DBAs should be aware at least of its existence, as it can be immensely useful in solving certain kinds of emergency situations.

Flashback data archive has very low impact on the database. Data for the archive are generated from UNDO tablespace, that is not as part of the transaction that modified the data. (This means the transaction generally proceeds as fast as if there is no data archive for the table -- now compare that to the trigger-based solution...) Unless some database resource is fully utilized, there should be virtually no impact at all in using the flashback data archive.

I'd say that your requirement (that is, recovering mistakenly deleted data) has only two solutions - the flashback functionality, or a backup strategy that would keep data for a defined period (eg. last 90 days) and allow to recover database or tablespace to any time in the protected period. (This is a troublesome process that might require some downtime or another database machine to restore the backups to, and a competent DBA, so flashback archive is much, much more flexible. But such solution is probably in place anyway to protect against hardware failures, so you don't incur any additional overhead - such as disc space - at all.) There might be some other solutions I don't know about, I'm just a developer after all

The other solution you mentioned - mirroring - does not generally protect against these kind of accidents, as the mirrored databases are generally kept up to date with the master one. Any data changes - even the bad ones - are immediately distributed to mirrors, so you cannot use the mirrors to get the data before they were overwritten or deleted.

I have just one more thought on "users deleting data on purpose": this is a troublesome scenario. Generally, you should only let trusted users to your system. The less you can trust a user, the more you need to restrict his privileges. You also need to keep in mind that hostile operations do not include deletions or overwrites only, also insert can introduce "bad data" (think about user entering a fake invoice into an accounting system).

Anyway, if you want to be able to repair bad data, you also need to somehow detect them. You might have a review system that would only allow verified data in (the reviewer might be a person, or an automated process, of course). In this case you might design the system in such a way that data pending review would be kept in some sort of queues and would get inserted into live tables only after the review was done, efficiently removing the need to recover mistakenly modified data. Of course, solution like these would probably be quite expensive.

On the other hand, if such a review process is not in place (or if it fails in some instances), the errors introduced into your database will probably be discovered at random, when someone gets to recheck the data after obtaining suspicious results of some kind. Now, just repairing the data might not be enough. There could be reports already generated from the wrong data, and important decisions taken based on these misleading reports. What to do now? In my opinion, just putting a tool to repair the bad data, without thinking out profoundly the consequences of such situations is not enough.

[mohuza zack]

Thank you Martin..

Actually I am doing a final project.. Flashback may be a new knowledge for me and it is very good to know it.. But it will not be a main topic in my project since it is a built in feature in the Oracle itself..

Currently, my client already knows that there is a flashback technology.. But it is not sufficient for their situation..
My client has create a trigger to do an audit trail.. It will duplicate the table in the database and it will record the transaction such as update a table and so on.. It can be based on the data update, or column update..
So, they want me to enhance that feature so that it can be used as recovery (or maybe other terms which is more suitable, but still don't have the idea what is the exact term).. And in the same time, we may customize when we are going to do a rollback.. Actually, we can use the authentication, yes.. But there is also a case where there is a irresponsible who use the username and password of the authorized user to log in to the database and make a invalid transaction or change something in the database..
my main objective now is how to tackle the problem of recover the unauthorized transaction being done by fake authorized user..
my lecturer ask to use the stored procedure.. since it may be not existed yet (maybe), then it may be my project..

Thank you..