Solved

Oracle Apps Support

Posted on 2011-02-11
13
327 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 77

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 74

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 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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 77

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 77

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Read about achieving the basic levels of HRIS security in the workplace.
"Disruption" is the most feared word for C-level executives these days. They agonize over their industry being disturbed by another player - most likely by startups.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

830 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