Case Study 3:
Archive Log Generation Rate
1.Case Summary
1.1 Customer Profile
Customer Name: Large Australian Financial Institution
System: Oracle E-Business Suite
System Description:
A large Australian financial institution uses Oracle E-Business to manage the following operations:
-
General Ledger
-
Accounts Payable
-
Human Resources\Payroll
​
1.2 Case Study Explanation
The application team responsible for supporting the Oracle E-Business finance system is preparing for an application code release. As part of the code release there will be a fair amount of data modification. The application team has requested that a restore point be created prior to the application release. This restore point needs to stay in place for a maximum of 3 days.
The database administrator needs to guarantee that the database can roll back to the restore point in the event the application release needs to be backed out.
​
1.3 Required Actions\Deliverables
-
Conduct the database capacity review to determine the amount of archive log generation over a period of 3 days.
-
The scope of the capacity review is just to determine the volume of archive log generation. This value will be provided to platform managers who will ensure adequate disk space for the duration of the active restore point(3 days).
​
​
2.Review the Archive Log Generation
First of all let’s look at some important information on the BO_MESSAGE table which could impact query performance.
​
Step 1. Enter the login credentials to the database
Step 2. Select “Recovery Usage” from the Storage pull down menu
Step 3. Click on the “Retrieve” button
Here we can see that the daily archive log generation can reach up to 1,000,000,000 blocks per day(or just a little over). With each block being 512bytes the daily archive log generation is 512GBytes. Therefore worst case scenario is the database will generate 1.5 Terabytes of archive logs over a 3 day period whilst the restore point is active. In order to guarantee any sort of rollback we need to provision for 3 days’ worth of logs which is 1.5TB of disk space.
3. Speedway v’s OEM
Various pieces of Speedway functionality used in this case study are not available within standard products such as OEM. The features shown in this document provide the database adminstrator with further insight into capacity information by providing richer functionality.
Features List:
-
Information on how the database recovery area is consuming space
-
Information on what space can be reclaimed from the database recovery area
-
Information on volume of archive log generation