Solved

Oracle Apps Support

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

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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
one-way data "masking" MD5 sql 26 147
Oracle Database Upgrade 13 62
PL/SQL - Leading zeros 7 60
oracle 11g 23 51
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…
In our personal lives, we have well-designed consumer apps to delight us and make even the most complex transactions simple. Many enterprise applications, however, are a bit behind the times. For an enterprise app to be successful in today's tech wo…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

863 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

27 Experts available now in Live!

Get 1:1 Help Now