Solved

Oracle Apps Support

Posted on 2011-02-11
13
324 Views
Last Modified: 2012-05-11
We are using Oracle Apps 11.5.8 since last five years on Oracle 8i and we have huge amount of data that is hampering the performance of the application. We are planning to upgrade in near future possibly on SAP, however  till we go live we need to support the business on this application . We would like to Archive old data and keep data only for 2 or 3 yrs. This would improve Application & Database Performance. We usually run reports for last 2 yrs but whenever needed we should be able to retrieve data using the same Oracle Standard & customized reports. Please let me know our options.
0
Comment
Question by:Android26
  • 4
  • 3
  • 3
  • +1
13 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 34876493
My 8i experience is very old and my Oracle apps experience is even older but I'm responding to a neglected question alert.

Is it possible to partition your larger tables and indexes?  That might ease the performance issues and keep the older data available with the least impact to the apps.

I know it requires a separate license with newer releases but I can't remember if it did in 8i.
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
ID: 34876702
Try using partition views - it's a little more cumbersome than real partitioning,  but it's free.

The idea is quite simple

Create 2 tables (or more) that are identical in structure and indexes

Add a check constraint to each table such that each table can only have data for their specified dates


then create a view that does a UNION ALL of the tables

To reinforce the metadata (which is good to do since the 8i optimizer was still relatively new),  on each select of the unions, impose the same date range condition


something like

create or replace my_view as
select col1,col2,col3 from my_old_data where col1 < to_date('20090101','yyyymmdd')
union all
select col1,col2,col3 from my_new_data where col1 >=to_date('20090101','yyyymmdd')

since this is an existing system  rename your old table, then create the view with the same the table used to have





0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 34876844
Unfortunately, Oracle does not offer an archiving mechanism for Oracle Apps in any version.  There are a couple of third-party providers that do this (IBM and Informatica) but these solutions are not cheap!  Oracle does offer *some* purging programs for Oracle Apps, but these do *NOT* archive the data before purging it.
0
 

Author Comment

by:Android26
ID: 34958948
Guys,

Since we have to maintain this data for next few years and the hardware that it currently rests on  will not last that long, we are looking at porting this data to some new hardware by way of migration or archival utility. Any leads on that?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34960530
Can you migrate ALL the data to the new hardware?  

It will require downtime to ship the data files over but from the posts above, it appears without a manual data archival of some type, it might be your only option.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:Android26
ID: 34960554
Yes all the data can be migrated to new hardware,  and in fact I want to migrate all the data on the new hardware as the current hardware has become obsolete. Downtime for few hours can be manageable.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34960579
If you go with the partitioned view route, you could precreate all of the tables you need and as you migrate, put the data into whichever table is appropriate.

You could do that by exporting in pieces and importing and renaming the tables
or create db links and simply insert/select
or by exporting everything, importing everything then splitting the data
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34960672
>>Yes all the data can be migrated to new hardware

Are you wanting to move the Oracle instance (Oracle software/server processes/???) to the new server as well?  Basically move it all and shutdown the old server?

If the new hardware will support Oracle 8 (or visa versa), this shouldn't be a problem.
0
 

Author Comment

by:Android26
ID: 35372366
Have decided to keep the status quo till we start the new ERP implementation and with the help of implementation partner will address this concern.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35374541
what was the solution?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35378812
I recommend split on

Http:#34876493
http:#34876702

These address the archiving issue itself.
If the asked has another solution to accept that's ok too but delete seems inappropriate when there are answers available with no counter
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Note: You must have administrative privileges in order to create/edit Sharing Rules. Salesforce.com (http://www.salesforce.com) (SFDC) is a cloud-based customer relationship management (CRM) system. It is a database most commonly used by sales an…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now