Trimming Down the Database : Page 2

Posted October 11, 2004

Drew Robb

Drew Robb

(Page 2 of 2)

Continued from Page 1.

While the system can be automated, the company prefers to rely on manual archiving for the moment. Every month, a DBA spends 30 minutes selecting files to archive. The decision is based on locating files in the inventory application database, for example, that are older than 30 months. These are moved from the production system to a less expensive Applimation datastore.

HR data, on the other hand, is only archived once per year. Reason: HR depends on a complete year of information. Any reports they run require a whole year, so HR data is retained a little longer and then a year's worth is retired at once.

"Decide how much data you need to keep in production for each application and business unit," said Cotrone. "Also, some areas such as inventory can have archiving done monthly, whereas HR/Payroll may need to have annual archiving as they typically need to access data for the entire year."

As the Applimation system is set up with the same look as the production system, the administrator just selects the rows that need to be moved, does so, and then deletes them from the production system. Cotrone recommends completing a backup of the archive BEFORE deleting anything from the production database, however.

No Performance Hit

According to Cotrone, archived files can be accessed by the user transparently from the original application.

"If you stick to querying archived data alone, there is no noticeable difference in responsiveness," he said. "But if you run a search across the production system and the archive, it will be a little slower."

Cotrone ran into trouble due to differences between Oracle 8i and 9i. His system runs on 8i while the archive database runs Oracle 9i in a Linux server instance within an IBM mainframe. Each successive evolution of Oracle and its associated applications appears to add more complexity that can potentially damage a project. The Oracle 11i E-Business Suite, for example, adds 200 new modules and 17,500 tables to the application infrastructure. The same holds true for other database vendors,

"We couldn't export files from our 8i production database into the 9i archive, as there are certain tables you can't send across," said Cotrone. "Fortunately, our inventory application doesn't have these tables so we were able to archive it while we complete a migration of everything else to 9i."

Page 2 of 2

Previous Page
1 2

0 Comments (click to add your comment)
Comment and Contribute


(Maximum characters: 1200). You have characters left.