Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle Apps Support

Posted on 2011-02-11
13
Medium Priority
?
334 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
11 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 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 74

Assisted Solution

by:sdstuber
sdstuber earned 1000 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 35

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 78

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
 

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 74

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 78

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 74

Expert Comment

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

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Read about achieving the basic levels of HRIS security in the workplace.
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

824 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