Upgrading to R12 and Migrating the OraApps DB to Linux

1. Upgrading to R12 and Migrating the OraApps DB to Linux John PetersJRPJR, Inc. john.peters@jrpjr.com John Peters, JRPJR, Inc.

2. Before We Start A Quick Audience Survey • How many of you have are on 11.0, 11i, 12? • How many of you have started an R12 upgrade project? • How many of you plan to upgrade to R12 in the next 18 months? • How many of you have already migrated your DB to Linux? John Peters, JRPJR, Inc.

3. Oracle is already talking about the Fusion Migration http://blogs.oracle.com/schan/2008/01/09#a2349 John Peters, JRPJR, Inc.

4. What I am going to cover • Pre-Upgrade tasks for R12 • Oracle’s ‘best practices’ for the R12 Upgrade • Some of your options • Where we are at in the process • This will be a technical presentation with scripts, ML Note references, and patch discussions John Peters, JRPJR, Inc.

5. Current Client Information • DB Tier on HP/UX, 10.1.0.4, ~190GB(upgraded several times from 8.0 install) • Apps Tier on Linux, 11.5.10.2(upgraded several times from an original 11.0 install, 1997) • Modules:Fin - GL, AP, AR, FAOps – OM, INV, MRP, WIP, BOM, CSTCRM – iStore, Field Service, Contracts, QuotingHR • Two primary OU’s: US, CA • Intercompany Shipments and Invoicing between OU’s • Other World Wide Ops not on OraApps, yet John Peters, JRPJR, Inc.

6. Client’s Business Objectives Upgrade to R12 before additional OU’s are rolled out World Wide John Peters, JRPJR, Inc.

7. Additional Technical Goals • Purge Junk Out of the DB • Get Current on ATG-H, RUP6 (currently RUP4)(R12 pre-req, Section 2, Alert, Step 1) • Eliminate JInitiator • iSetup • OATM • DB Character Set From WE8ISO8859P1 To AL32UTF8 • Migrate DB Tier to Linux • We are already Multi-Org which is required for R12 John Peters, JRPJR, Inc.

8. Metalink Upgrade Docs John Peters, JRPJR, Inc.

9. Metalink Upgrade Docs (cont) John Peters, JRPJR, Inc.

10. Metalink Upgrade Docs (cont) John Peters, JRPJR, Inc.

11. Upgrade Best Practices http://blogs.oracle.com/schan/2007/11/29#a2268 John Peters, JRPJR, Inc.

12. Oracle’s Upgrade Best PracticesPS_S290708_290708_176-1_FIN_v1.pdf • Eugene Weinstein presented here today session 4.01 • Convert to Multi Org • Convert to OATM • Upgrade database to 10.2.0.2 (rapid install) • ‘Upgrade by Request’, Historical data can be upgraded after down time window • Financials and Procurement • Projects • Supply Chain Management • CRM (run scripts manually) John Peters, JRPJR, Inc.

13. Purge Junk Out of the DB • Take a look at what is using the majority of the space in your DB select OWNER, SEGMENT_NAME, sum(BYTES) obj_size from DBA_SEGMENTS group by OWNER, SEGMENT_NAME order by OBJ_SIZE desc; • This query does not take into account index space usage for an object John Peters, JRPJR, Inc.

14. What you get… John Peters, JRPJR, Inc.

15. Some known OraApps space hogs • FND_LOG_MESSAGES, Note:332103.1 • SO_EXCEPTIONS, ML Note:248186.1 • CST_EXPLOSION_TEMP, Note:402504.1 • MRP_ATP_SCHEDULE_TEMP, 283875.1 and 188803.1 and 427430.1 John Peters, JRPJR, Inc.

16. Workflow space hogs WF_ITEM_ATTRIBUTE_VALUES • This table stores attribute (variable) values for run time processes. Good indication of purging issues. select ITEM_TYPE, COUNT(*) cnt from WF_ITEM_ATTRIBUTE_VALUES group by ITEM_TYPE order by CNT desc John Peters, JRPJR, Inc.

17. What you get … Lets look a little deeper select TO_CHAR(begin_date,'YYYY-MM'), COUNT(*) from WF_ITEMS wi where item_type = 'POWFPOAG' group by TO_CHAR(begin_date,'YYYY-MM‘); John Peters, JRPJR, Inc.

18. What you get … Account Generators had errored out long ago and prevented processes from being purged. This was fixed around 2006-03.Oracle Support provided a script to close these processes out so they could be purged. John Peters, JRPJR, Inc.

19. Customization space hogs • We dug through some of the custom objects and found error and audit tables that were not being purged. XXCUST_F_CONCURRENT_REQUESTS • This one is a history table of all concurrent requests run, (maintained through on delete triggers on concurrent request tables). We summarized the historical data to save space. John Peters, JRPJR, Inc.

20. Purge Summary • Now is the time to start reviewing your DB to purge out the junk. • We removed about 30GB of junk. We could not fully recover the space until OATM had been implemented. • Take a look at Lynne Paulus’s presentation that occurred today in session 4.14. John Peters, JRPJR, Inc.

21. Get current on Tech Stack • You should get the most recent AD pack for patching. • R12 does not support JInitator, so you should start your migration now to the Native SUN JRE. Your users will probably be accessing your existing instances and R12 from the same desktops. John Peters, JRPJR, Inc.

22. iSetup • This tool is a hidden gem in the OraApps. • Not the old iSetup, completely rewritten. • No additional license required. • Allows you to migrate setups and data between OraApps Instances and Operating Units. • Allows you to create a snapshot of setups then run difference reports to see “What has changed all of the sudden…”. • Mohan Iyer is giving a presentation on this topic today in session 5.06. John Peters, JRPJR, Inc.

23. OATM - Oracle Applications Tablespace Model • How many of you have already migrated to OATM? • OATM FAQs, ML Note: 269293.1 • How to run OATM migration utility, Note: 404954.1 • OATM Release 11i – Tablespace Migration Utility, Note: 248857.1 John Peters, JRPJR, Inc.

24. OATM Benefits • Reduces the total number of tablespaces (and indirectly data files) to 12, classified by data being stored. • By consolidating the objects, space reorg’s of tables/indexes are now possible. • Reduces the total number of data files that a checkpoint process needs to update. • Currently optional, not mandatory at this time. However, Oracle assumes OATM for new module tablespaces. John Peters, JRPJR, Inc.

25. OATM Migration Utility How Does It Work • Generates scripts to create new tablespaces, migrate actions, shrink source tablespaces, purge empty tablespace. • Migration can be done incrementally (tablespace by tablespace) – not recommended. Or the whole system at one time – recommended. • You will need twice the disk space as your existing instance. John Peters, JRPJR, Inc.

26. OATM Uniform Extent Size • OATM creates the tablespaces with a Uniform Extent Size. • Several notes on ML recommend an extent size of 1MB. • Tables without any rows, will occupy the minimum 1MB allocation for the first extent. • In our instance this amounted to about a 20GB overhead. John Peters, JRPJR, Inc.

27. OATM Issues • Apply patch 5467526, Note:389472.1, and BUG 6454441, or ATG-H, RUP6. • If the source tablespaces are locally managed they will have space header data that will prevent the files from being shrunk completely Note:271866.1 • Shrink script have bugs and attempt to shrink too fall and you will get:ORA-03297: file contains used data beyond requested RESIZE value • When completed you will probably need to copy your datafiles back to the original disks. John Peters, JRPJR, Inc.

28. OATM Issues (cont) • The Tablespace Migration Utility left some LOB Segments in the Source Tablespaces and did not report errors. select tablespace_name, segment_type, COUNT(*) from dba_segments where tablespace_name not in ('APPS_TS_ARCHIVE', 'APPS_TS_INTERFACE', 'APPS_TS_MEDIA', 'APPS_TS_NOLOGGING', 'APPS_TS_QUEUES', 'APPS_TS_SEED', 'APPS_TS_SUMMARY', 'APPS_TS_TOOLS', 'APPS_TS_TX_DATA', 'APPS_TS_TX_IDX', 'SYSTEM') and segment_type != 'SPACE HEADER' -- Note:271866.1 group by tablespace_name, segment_type; John Peters, JRPJR, Inc.

29. OATM Issues (cont) • You can find the object migration scripts with the following query: select * from FND_TS_MIG_CMDS where OBJECT_NAME = ‘xxxx‘; • The fix we received from Oracle Support was some manual edits to the created scripts, then rerun them manually. John Peters, JRPJR, Inc.

30. OATM Issues (cont) • Double check the tablespaces are really empty before dropping them. Oracle uses the following syntax:drop tablespace AK including contents and datafiles;This forces the tablespace to be dropped even if there is something in it. select * from DBA_TABLESPACES dt where not exists (select 'Y' from DBA_SEGMENTS ds where ds.TABLESPACE_NAME = dt.TABLESPACE_NAME and ds.SEGMENT_TYPE != 'SPACE HEADER' -- Note:271866.1 ) order by dt.TABLESPACE_NAME John Peters, JRPJR, Inc.

31. OATM Issues (cont) • Double check the tablespaces are really empty before dropping them. Oracle uses the following syntax:drop tablespace AK including contents and datafiles;This forces the tablespace to be dropped even if there is something in it. select * from DBA_TABLESPACES dt where not exists (select 'Y' from DBA_SEGMENTS ds where ds.TABLESPACE_NAME = dt.TABLESPACE_NAME and ds.SEGMENT_TYPE != 'SPACE HEADER' -- Note:271866.1 ) order by dt.TABLESPACE_NAME; John Peters, JRPJR, Inc.

32. OATM Issues (cont) • OATM only handles database objects that have space allocated in DBA_SEGMENTS. • GLOBAL TEMPORARY tables point to a tablespace at time of creation. • We have 70 of these that are pointing to the original schema data tablespace, which has now been dropped. • The only way to find these was to write a script to do a select count(*) from the object, which will throw:ORA-00959: tablespace 'QPD' does not exist • From within the OraApps this throws no error message. You get a blank message window, or no indication at all. • Others have reported this issue on ML. John Peters, JRPJR, Inc.

33. OATM Issues (cont) Two work arounds exist: • Recreate the missing tablespaces the objects point to • Run the following scripts to retrieve the DDL for the GLOBAL TEMPORARY tables, drop them and recreate them. set LONG 50000 select dbms_metadata.get_ddl(UPPER('TABLE') , UPPER('AD_PTCH_HST_EXE_COP_TMP'),UPPER('APPLSYS') ) from dual; John Peters, JRPJR, Inc.

34. OATM Summary The OATM process took about 24 hours of down time to complete. We are still working the GLOBAL TEMPORARY TABLE issue with Oracle Support. John Peters, JRPJR, Inc.

35. Linux DB Migration • Migrate from HP/UX 64bit, 10.1.0.4 to Red Hat Linux 4 64bit, 10.2.0.3 • Change database character set From WE8ISO8859P1 To AL32UTF8 John Peters, JRPJR, Inc.

36. DB Migration Choices 1. Export/Import • Export DB on HP/UX, Import DB on Linux • Use data pump (and legacy exp/imp) • Well documented: • 10g Export/Import Process for Oracle Applications Release 11i, ML Note: 331221.1 • 10g Release 2 Export/Import Process for Oracle Applications Release 11i, ML Note: 362205.1 • Use R12 Rapid Install to create target 10.2.0.2 ORACLE_HOME, patch up to 10.2.0.3 John Peters, JRPJR, Inc.

37. DB Migration Choices (cont) 2. Transportable Tables Spaces • Copy datafiles from HP/UX • Run through RMAN to convert: • Endian format of data (byte ordering) • Character set • Create Linux DB without OATM tablespaces • “Attach” datafiles to Linux DB, (there are really about 9 steps to complete this process) • Limited Oracle Documentation, none around OraApps • Roger Schrag, Database Specialists, Inc. has a great presentation on this topic: http://www.dbspecialists.com/presentations.html#changing_platforms John Peters, JRPJR, Inc.

38. What did we choose… • We spoke with Stephen Chan about being a Beta customer for the Transportable Tablespaces migration. But this process looked like it would be a fairly major investment of time to develop and work out the kinks. • We chose to follow the documented and proven Import/Export method. John Peters, JRPJR, Inc.

39. Boy were we wrong… • Evidently, the export/import processing with OraApps databases is not as simple as following the documented steps and using the supplied parameter files. • We still have not succeeded at this and are currently working issues with Oracle Support. John Peters, JRPJR, Inc.

40. Patches, Patches, Patches … • 4872830, Per Note 331221.1, Section 1, Step 2 • 5873146, Per Note 331221.1, Section 1, Step 3 • 5753621, Per ML 362203.1 • 5064356, Note:5064356.8 – patch was unavailable so we ended up working around this by changing the NFS mounts parameters • 3897122 and 4352110, on the Source DB • Update system data in SYSTEM_PRIVILEGE_MAP using an SQL statement per Oracle Support John Peters, JRPJR, Inc.

41. Data Pump Issue 1 • If a database user is assigned a custom user profile that uses a custom function it will error on the creation of the user profile . • Workaround, manually create the function and user profile in the database prior to importing John Peters, JRPJR, Inc.

42. Data Pump Issue 2 • Data Pump can not handle XML Types. These have to be migrated using the legacy exp/imp process. • Why not just use the legacy exp/imp process, it can not handle all of the data types as well. • Workaround, run the import, find the errors and then run the legacy exp/imp process for those objects. John Peters, JRPJR, Inc.

43. Data Pump Issue (cont) • We are still working a variety of other issues related to: • Data types not convertedPlease pre-create the object type in the target database and then retry the datapump import with following parameter TRANSFORM=oid:n • Queue objects not converted and expected before tables can be converted • ………. And we are trying to dig through the thousands of errors to determine the real precedence/root cause issues. John Peters, JRPJR, Inc.

44. So where are we now… • For now we are upgrading the database to 10.2.0.3 on HP/UX and leaving the character set as is. • And I thought we were taking the more conservative approach….. • So my advice to everyone is to get these tasks out of the way well in advance of your R12 upgrade, so you will have time to work the issues around these tasks. John Peters, JRPJR, Inc.

45. HP/UX 10.2.0.3 Upgrade Warning ML Note: 362203.1,Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0) There is an error in Section 1, Step 12. The three patches 5892355, 5871314, 5755471 must be applied for any 64bit OS. The patches correct issues introduced on earlier 32bit to 64bit upgrades. John Peters, JRPJR, Inc.

46. R12 Upgrade What we all want to know more about…. John Peters, JRPJR, Inc.

47. R12 Upgrade Where are We • We are about a day from starting the actual upgrade processes running under adpatch. John Peters, JRPJR, Inc.

48. R12 Upgrade Steps Same basic procedure as in the past • Section 2, Preparing for Upgrade(~33 steps) • Section 3, Upgrading to R12(~16 steps) • Section 4, Post Upgrade(~54 steps) • TUMS report is available to assist in identifying steps that can be skipped. John Peters, JRPJR, Inc.

49. R12 Upgrade Size • Oracle says to expect the following size increases: • 3% for 3 years of historical conversion • 8.3% for 12 years of historical conversion • I am going to allow the database files to autoextend up to a maxsize that will allow for an approximate 10% increase John Peters, JRPJR, Inc.

50. R12 Upgrade Differences • No upgrade assistant spreadsheet:upgasst.xls • This has been replaced by the ‘Maintenance Wizard’. See ML Note 215527.1. This is installed in a new RDBMS 10g with a 10g iAS ORACLE_HOME on a UNIX or Linux machine. • We are not using this at this time. John Peters, JRPJR, Inc.