Oracle data missing, over 500GB worth.

15 pts.
Tags:
Oracle 9
Oracle administration
Oracle error messages
Instance of Oracle on HP-UX 11.11, Oracle version 9.2.0.8, has lost hundreds of GB of data. Manual/accidental deletion is not possible as the process would take a long time to execute. Error logs do not show much, AUD$ log doesn't show any user activity during the window of time (roughly 24 hours). an ORA-1650 error exists (ORA-1650: unable to extend rollback segment R01 by 640 in tablespace RBS). What are some causes to lose such high volumes of data? Any pointers on where I can look further for clues/indicators for root cause? Thanks for your expertise, as I am not a DB person.

Software/Hardware used:
HP-UX 11.11, Oracle version 9.2.0.8

Answer Wiki

Thanks. We'll let you know when a new response is added.

what data is missing?

all data from a table?

some data form one table?

some data from multiple tables?

Data can rapidly be removed with a TRUNCATE statement. (think of opening a file as output). Nothing is logged. but that removes all data from a table. without logging the data.

RBS cannot be extended? So something is running that used up all available roll back segments? Is it possible that this job that used all the RBS is holding locks on the missing data?

I think your first task is to find a DB person. they need to address the RBS situation, then determine what happened to the data.

At worst, there is always recovery. followed by a review of your security. because, maybe, someone did remove your data.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Spamtrap
    Thank you Meandyou, Some of what you mentioned has been noted, and discussed. e are looking toward human error, but will not have a "smoking gun" so to speak. I do appreciate your time and effort in helping out!
    15 pointsBadges:
    report
  • Paladine Solamnia
    The way I see it, it could still be a DML delete statement. If so much data is gone, wouldn't it make sense that some jobs are performing a delete operation hence the full consumption of all the UNDO tablespaces ? In any case, AUD$ doesn't show a thing on user activity does not mean users with sysdba roles can't perform delete operations. You probably need to ask some questions. In the first place, are the users specifically audited with the DELETE ANY TABLE whenever successful if given that some of the users are granted with the DELETE system privilege ? Secondly, like I mentioned earlier, if users can login with sysdba role, their actions will only be recorded if your audit_sys_operations=TRUE. If that is the case, do a SQL> show parameter audit to determine the audit destination directory and look through all the ****.aud files in that directory to see any suspicious activities involving delete operations. This would be a better bet if your AUD$ table is not showing anything. Last but not least, do u have full RMAN backups done prior to the loss of data ? If you have, simply do a restore database and recover until the archive log prior to the loss of data. You can probably utilitze the log sequence number or SCN if you know them to recover your database right to the point before disaster occur.
    170 pointsBadges:
    report
  • Kccrosser
    The questions above are still valid: 1. Is the missing data from one table or multiple tables? 2. In the table(s) with missing data, is the table empty (i.e., a TRUNCATE)? If not empty, do the contents look like recent adds (i.e., a TRUNCATE occurred, followed by a bunch of normal insert actions)? 3. Are whole tables missing, or just data from within the tables? (Possible DROP TABLE or RENAME TABLE actions) 4. Over what time did the data disappear? Deleting 500 GB of data might not take as long as you think. Oracle buik collect/delete transactions can delete data pretty fast, and even a decent program loop that deleted blocks of say 500-1000 records at a time (i.e., delete 500 rows, then commit all at once) could remove a lot of data in a few hours.
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following