AnsweredAssumed Answered

Error during Post Upgrade from 7.5.3 to 8.1

Question asked by sundar on Feb 19, 2010
Latest reply on Feb 19, 2010 by sundar
Hi,  Our development upgrade failed twice during 7.5.3 to 8.1 post upgrade because of the below post upgrade script query takes very long time (80 hours) to run.we are migrating from 7.5.3 to 8.1 and failed.(Budget Migration).CA folks telling that since we used financials intensively it is taking so much time (PAC_FRC_FORECAST_VALUES tables have 9 million records,PAC_FRC_FORECAST_PROPERTIES) have 4 million records and we are also checking with DBA for improving the DB performance. we have also raised a case id with CA and hope they will revert back soon.Can we tune the upgrade sql but it must be validated by CA.

we have an good box with enough memory and our DB also tuned the general settings of DB,but we have not tuned this query as this comes as an part of upgrade.any help is much appreciated.

Have you faced any sort of issues before?.If so any advice.

****The Killer Query****
SELECT count(*) as count from (SELECT ( fdetails.forecast_id
|| fdetails.detail_id
|| NVL(fdetails.detail2_id, 0)
) AS map_id,
fdetails.forecast_id AS forecast_id,
fdetails.detail_id AS detail_id,
fdetails.detail2_id AS detail_2_id,
periods.start_date AS start_dt,
periods.end_date AS end_dt,
fdetails.units AS units,
fvalues.cost AS cost,
fvalues.revenue AS revenue,
fvalues.currency_type AS currency_type,
srmp.is_revenue_plan AS is_revenue,
periods.period_type,
inv.entity_code,
fvalues.actual_benefits AS actual_benefits,
fprop.project_id AS project_id,
fprop.revision AS revision,
fprop.forecast_type AS forecast_type
FROM pac_frc_forecast_details fdetails,
pac_frc_forecast_values fvalues,
pac_frc_forecast_properties fprop,
srm_projects srmp,
inv_investments inv, biz_com_periods periods
WHERE fdetails.ID = fvalues.forecast_details_id
AND fdetails.forecast_id = fprop.ID
AND fprop.project_id = srmp.ID
AND srmp.id=inv.id
AND periods.id = fdetails.period_id
AND periods.entity_id IS NULL )

Error:-

ERROR 2010-02-14 19:16:51,006 [main] upgrade.MigrateFinancialPlans ERROR: [MigrateFinancialPlans.addDepartmentsToInvestments].
2/17/10 11:53 PM (admin) FATAL 2010-02-17 23:53:19,584 [main] union.persistence Failed to determine analytic function support
2/17/10 11:53 PM (admin) java.lang.RuntimeException: Unexpected invocation exception: [CA Clarity][Oracle JDBC Driver]Object has been closed.

cheers,
sundar

Outcomes