Transcript Upgrading to R12 and Migrating the OraApps DB to Linux
Upgrading to R12 and Migrating the OraApps DB to Linux
John Peters JRPJR, Inc.
01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
1
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?
01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
2
Oracle is already talking about the Fusion Migration
http://blogs.oracle.com/schan/2008/01/09#a2349 01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
3
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 01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
4
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, FA Ops – OM, INV, MRP, WIP, BOM, CST CRM – iStore, Field Service, Contracts, Quoting HR • 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.
01/17/08 NorCal OAUG Training Day, Paper 5.14
5
Client’s Business Objectives
Upgrade to R12 before additional OU’s are rolled out World Wide 01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
6
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 01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
7
Metalink Upgrade Docs
01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
8
Metalink Upgrade Docs (cont)
01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
9
Metalink Upgrade Docs (cont)
01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
10
Upgrade Best Practices
http://blogs.oracle.com/schan/2007/11/29#a2268 01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
11
Oracle’s Upgrade Best Practices
PS_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 – Financials and Procurement – Projects – Supply Chain Management – CRM (run scripts manually) (rapid install) • ‘Upgrade by Request’, Historical data can be upgraded after down time window John Peters, JRPJR, Inc.
12 01/17/08 NorCal OAUG Training Day, Paper 5.14
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 01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
13
OWNER
BOM BOM XXCUST APPLSYS APPLSYS BOM APPLSYS OE BOM INV INV INV INV APPLSYS MRP ONT GL AR APPLSYS INV APPLSYS APPLSYS GL BOM AR AR WSH WSH APPLSYS 01/17/08 NorCal OAUG Training Day, Paper 5.14
What you get…
SEGMENT_NAME
CST_EXPLOSION_TEMP_N2 CST_EXPLOSION_TEMP_N1 XXCUST_F_CONCURRENT_REQUESTS SYS_LOB0000198591C00004$$ FND_LOG_MESSAGES CST_EXPLOSION_TEMP_N3 WF_ITEM_ATTRIBUTE_VALUES_PK SO_EXCEPTIONS CST_EXPLOSION_TEMP MTL_TXN_REQUEST_LINES MTL_MATERIAL_TRANSACTIONS MTL_TRANSACTION_ACCOUNTS MTL_CST_ACTUAL_COST_DETAILS WF_ITEM_ATTRIBUTE_VALUES MRP_ATP_SCHEDULE_TEMP OE_ORDER_LINES_ALL GL_IMPORT_REFERENCES RA_CUST_TRX_LINE_GL_DIST_ALL WF_ITEM_ACTIVITY_STATUSES MTL_CST_ACTUAL_COST_DETAILS_U1 FND_CONC_REQ_STAT WF_ITEM_ACTIVITY_STATUSES_PK GL_JE_LINES BOM_EXPLOSIONS RA_CUSTOMER_TRX_LINES_ALL AR_RECEIPTS_REP_ITF WSH_EXCEPTIONS WSH_DELIVERY_DETAILS WF_ITEM_ACTIVITY_STATUSES_N1
SEGMENT_TYPE
TABLE TABLE TABLE TABLE INDEX TABLE INDEX TABLE TABLE TABLE TABLE TABLE TABLE INDEX INDEX INDEX TABLE LOBSEGMENT TABLE INDEX INDEX TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE
SUM(BYTES)
5,782,052,864 4,544,249,856 4,508,876,800 3,262,504,960 3,087,056,896 2,977,914,880 2,967,519,232 2,667,618,304 2,521,784,320 2,428,002,304 2,270,167,040 2,221,137,920 2,191,212,544 2,179,063,808 1,782,202,368 1,365,032,960 1,340,645,376 1,308,868,608 1,241,538,560 1,141,776,384 905,650,176 828,424,192 806,273,024 792,272,896 771,932,160 745,586,688 714,014,720 711,680,000 709,427,200 John Peters, JRPJR, Inc.
14
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.
15 01/17/08 NorCal OAUG Training Day, Paper 5.14
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
01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
16
What you get …
ITEM_TYPE
POWFPOAG WFERROR APCCARD OEOL SERVEREQ OECOGS POAPPRV XXCUSTOMNO OEOH POXML JTFTASK
COUNT(*)
2663195 1480304 1174750 911634 430941 421434 360450 124026 106318 42950 35289 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‘);
01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
17
What you get …
Date
2000-04 2000-05 2000-06 …………… 2003-09 2003-11 2005-11 2005-12 2006-01 2006-02 2006-03 2007-10 2007-11
Cnt
56 34 …………… 248 16 70 394 2969 3870 2965 2905 1745 513 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.
01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
18
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.
01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
19
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.
01/17/08 NorCal OAUG Training Day, Paper 5.14
20
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.
01/17/08 NorCal OAUG Training Day, Paper 5.14
21
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.
01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
22
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.
01/17/08 NorCal OAUG Training Day, Paper 5.14
23
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.
01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
24
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.
01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
25
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.
01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
26
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.
01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
27
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; 01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
28
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.
29 01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
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 01/17/08 NorCal OAUG Training Day, Paper 5.14
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; 01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
31
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.
01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
32
OATM Issues (cont) Two work arounds exist: 1. Recreate the missing tablespaces the objects point to 2. 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; 01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
33
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.
01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
34
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 35 01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
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
01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
36
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 01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
37
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.
01/17/08 NorCal OAUG Training Day, Paper 5.14
38
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.
01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
39
• • • • • • 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 40 01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
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 41 01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
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.
01/17/08 NorCal OAUG Training Day, Paper 5.14
42
• Data Pump Issue (cont) We are still working a variety of other issues related to: – Data types not converted Please 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.
01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
43
• 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.
01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
44
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.
01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
45
R12 Upgrade What we all want to know more about….
01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
46
R12 Upgrade Where are We • We are about a day from starting the actual upgrade processes running under adpatch.
01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
47
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.
01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
48
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.
49 01/17/08 NorCal OAUG Training Day, Paper 5.14
• 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.
01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
50
R12 Upgrade Differences (cont.) • We don’t run AutoUpgrade. • We now use adpatch, and u*.drv file.
• This is a special version of the AD utilities (R12.AD-A) that must be applied via patch just before you begin the upgrade on the older OraApps version.
01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
51
R12 Upgrade Differences (cont.) Upgrade by request • You can defer historical data conversions to a post upgrade step • Concurrent requests will run in the background to convert historical data • You can decide to perform additional historical data conversions any time in the future • Oracle uses this mechanism to convert some default historical data 01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
52
R12 Upgrade Differences (cont.) Upgrade by request used by: • CRM • Financials • Procurement • Projects • Supply Chain Management 01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
53
R12 Upgrade Differences (cont.) • Oracle recommends a dedicated concurrent manager queue for the Upgrade Concurrent Programs so you can control the background load • ML Note: 399362.1
54 01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
R12 Upgrade Differences (cont.) PL/SQL No Compile • Add PL/SQL no compile option in R12 upgrade driver to save time during upgrade • Add “extension plsql_no compile yes” line in u4440000.drv file to enable PL/SQL no compile option 55 01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
Our R12 Status • I will update this presentation with the R12 Upgrade results once we are complete: – Timing – Size increases – Bugs and Issues 56 01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.
• My contact information: John Peters [email protected] http://www.jrpjr.com
• Additional reference papers can be found at: http://www.norcaloaug.org
http://www.jrpjr.com
57 01/17/08 NorCal OAUG Training Day, Paper 5.14
John Peters, JRPJR, Inc.