Problem solve Get help with specific problems with your technologies, process and projects.

Data retention policy for Oracle DBAs: When to "trash" your data

Oracle DBA Donald K. Burleson discusses data archiving, including legal requirements, how to avoid stale data, and why it is important to "trash" data rather than store it forever.

As CEO of a database software company, Larry Ellison knows the value of archiving his corporate data. When disgruntled...

ex-Oracle employee Adelyn Lee concocted a false claim against Larry, Ellison used Oracle's data archives to prove that she had forged her evidence. The charges were dropped and Lee was found guilty of perjury and sentenced to a year in jail and a $100,000 fine. (For the entertaining details, see Lee v. Oracle Corporation, 1999 WL 595455, Cal App 1999.)

As disk prices fall to record lows, many corporations are retaining all of their corporate data, including all corporate correspondence, e-mails and customer queries. But is this a prudent decision? Although it worked for Larry Ellison, some experts suggest that it's a big mistake to archive every detail of your operational business processes, especially if the data have not been carefully reviewed for content. Today, many large companies are requiring complete purges of sensitive data that might be misunderstood, and they are going to great pains to have their Oracle DBAs remove all traces of this information.

During my work in Oracle forensics, I've helped many litigants resurrect evidence that has helped to punish bad guys and vanquish people who have been treated unfairly. During these forensic investigations, unscrupulous shops are shocked to discover that "smoking guns" can be uncovered years after the data has been deleted from Oracle.

But it's not just the bad guys who must trash their Oracle data. In today's litigious world, the conventional wisdom that saving all corporate data can save the day is now being challenged. In case after case, archived Oracle data is being abused by greedy plaintiffs, and the community is starting to realize that a prudent data retention policy must also include specific directions for "trashing" some Oracle data.

While data is a valuable resource, blindly archiving data can have serious financial consequences. Consider these examples:

  •  

  • AAA Corporation had a big problem: a disgruntled ex-employee was suing for sexual harassment. Even though the charges were groundless, their own historical data was being used to hurt them. During the discovery process, opposing counsel subpoenaed all corporate e-mails for the past three years -- over 20 million messages – and found hundreds of examples of messages that could be construed as demeaning to women. Because ABC archived all of its e-mails in their Oracle database, this data was used against them to horrific effect, costing them over a million dollars in damages.

  •  

  • BBB Car Corporation was accused of cutting corners on their vehicle production costs, precipitating the deaths of over a dozen motorists in the past decade. Using Oracle archived redo logs, the plaintiffs found evidence that ZZZ engineers could have made their car safer, yet they chose not to do so because the extra costs would make their cars non-competitive.

  •  

  • CCC Corporation was found guilty of libel after it was discovered that an ex-employee had published defamatory information on the web using the company's computers. The incident occurred four years ago and even though management had no knowledge of the incident, the Oracle log files revealed the exact details, opening up YYY for millions of dollars in damages.

  •  

  • During a lawsuit for age discrimination, DDD Corporation was ordered to produce confidential documents from their Oracle HR module. Even though the data was deleted from the database, the archived redo logs were used to reveal a pattern of age-related discrimination, costing them over ten million dollars in damages.

What do these cases have in common? They were all Oracle shops that made fatal errors in their data retention policy. They all fell for the common misconception that because disks are cheap and Oracle can easily manage all forms of corporate data, their data should be stored forever.

The Oracle DBA as data custodian

While the intentional destruction of evidence ("spoliation") is highly illegal, it is prudent and responsible to purge data that no longer has any value to the company, especially data that might be misconstrued or used in a lawsuit.

Since employers are held responsible for the acts of their employees, management must decide between two unsavory options:

 

  1. Monitor and archive all employee correspondence (web usage, telephone calls, e-mails).
  2. Deliberately throw away all correspondence after 60 days.

The Orwellian tactic of monitoring employees is falling from favor, so many large corporations now require that all corporate correspondence be completely and totally destroyed after a reasonable period of time.

So how does the savvy Oracle DBA manage data retention policies?

As more and more information systems are consolidating all of their operational information into Oracle databases, the Oracle DBA becomes the custodian of a wealth of varied data: everything from confidential e-mails to secret marketing plans. Since many vendor products (e.g., Oracle Collaboration Suite) now incorporate non-traditional data like spreadsheets and correspondence, the DBA must clearly understand what data is to be preserved and what data must be expunged from the archives.

Let's start by looking at the legal requirements for data archiving and understand how to comply with federal laws while eradicating unwanted information.

Legal requirements for data archiving

The Oracle DBA presides over a vast amount of corporate data and he or she must often work with corporate attorneys to ensure that their data retention policies comply with a host of federal data requirements (see Appendix A for a partial list).

These data archiving laws impose huge burdens on Oracle shops, especially laws such as HIPAA that mandate the auditing of anyone who views confidential patient data. These audits can exceed the size of the database every day, and the DBA is further challenged by laws requiring reporting. For example, an Oracle DBA in a hospital only has a few hours to sort through terabytes of HIPAA data to show everyone who has viewed a particular patient's records.

A wide variety of Oracle data must be retained and archived, including these (as specified in this article):

 

Data Law Requiring Retention
Basic data (name, address, birth date) FLSA
Job advertisements ADEA, FLSA and ADA
Employment applications ADA/Title VII ADEA, and OFCCP
Offers and hiring records ADA, Title VII, Vet's Act
Promotions, demotions, and transfers ADA, ADEA, and Title VII

Many of these laws impose criminal sanctions against any DBA who fails to comply, so some DBAs will simply retain everything in order to ensure compliance.

However, that's often a huge mistake. For example, a well-intentioned e-mail that states something like "Joe is in the hospital for VD treatment, in case anyone wants to send flowers" could be used as evidence for a HIPAA lawsuit for disclosing confidential medical information.

Avoiding stale data

All Oracle DBAs must be vigilant to ensure database recoverability while ensuring that sensitive or confidential data is completely obliterated. Most Oracle DBAs develop a sophisticated data retention policy that ensures recoverability, but they fail to develop policies for completely removing "stale" data.

In the article what you must have, should have, and never want to see in your company's records," we see that that all Oracle database information should be cleansed before archiving, removing all traces (including the redo logs) for any "smoking gun" data. The following information could be buried deep inside Oracle Applications or Oracle Collaboration Suite:

 

  • References to personal status - Any Oracle data referencing pre-employment background checks, sexual orientation, disabilities, politics and criminal history should be routinely purged.
  •  

  • Statements admitting wrongdoing by the company. Even simple notations within Oracle Applications comment fields must be carefully examined. For example, a comment within Oracle AP stating "We are postponing payment as long as possible" could be used in a lawsuit.
  •  

  • Subjective remarks - Some employees tend to include subjective comments in Oracle Apps, and these statements that can prove dangerous in litigation.
  •  

  • Inaccurate information - The publication of false and defamatory information could result in a claim for libel or slander.

So how does the DBA manage these conflicting requirements? In order to be effective, the end-user community must be intimately involved in the purging of stale data from the Oracle tables, but it is up to the DBA to ensure that none of this stale data is retained inside export files, audit trails or archived redo log files.

A sample retention policy should also spell-out the specific acts to ensure the through destruction of the data. Remember, audit trails almost always contain confidential data, and the audit trail tapes should be thoroughly incinerated.

In some shops with threats of third party litigation, the corporate attorneys have developed thorough procedures for destroying Oracle data, even going as far as incinerating the archived backup tapes. They have observed that un-cataloging the archived redo log tapes is not sufficient because they could be reconstructed by an Oracle forensics expert, and an archived redo file that is sent to a "safe site" must also be completely destroyed.

Archives kept on disk also require special treatment. The disk files should be physically erased, since it's not enough to just remove the files. Here are some high-level best practices for Oracle data destruction:

 

  • Destroy stale data – Many Oracle Apps shops require their end-users to purge sensitive data periodically, and some shops run keyword searches against all comment columns, seeking inadvertent failures.
  •  

  • Destroy archived redo logs – Archived redo logs can contain unwanted data (especially in the Oracle Collaboration Suite and Oracle eBusiness Suite) and all redo logs must be completely destroyed as soon as they are no longer needed for database recovery.
  •  

  • Destroy audit trails – Many shops use a job scheduler to mark audit trails for destruction as soon as the legal requirements are met.

In sum, the Oracle DBA has become the important custodian of critical corporate data, a job that requires attention to retention as well as destruction.

Appendix A

U.S. statutes mandating data archiving:

 

  • The Health Insurance Portability & Accountability Act (HIPAA)
  • The Sarbanes Oxley Act (SOX)
  • The Graham/Leach/Bliley Act (GLB)
  • The Federal Insurance Contribution Act (FICA)
  • The Federal Unemployment Tax (FUTA), Americans with Disabilities Act (ADA)
  • The Age Discrimination in Employment Act (ADEA)
  • The Equal Pay Act
  • The Family and Medical Leave Act (FMLA)
  • The Fair Labor Standards Act (FLSA)
  • Title VII of the Civil Rights Act of 1964 (Title VII)
  • The Immigration Reform and Control Act (IRCA)
  • The Occupational Safety and Health Act (OSHA)
  • The Employee Retirement Income Security Act (ERISA)

References

About the author

Donald K. Burleson has been a database administrator since the 1980s and manages the USA's largest remote DBA support service. He is also a popular author and serves as series editor for Rampant TechPress, a leading provider of Oracle technical books.

This was last published in January 2008

Dig Deeper on Oracle database backup and recovery

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close