Mohammed Mukarram Mohammed Mukarram Mohiuddin
Program Agenda • About ADP • R11i to R12 Upgrade Overview (ADP) • Business Challenges • Upgrade Challenges • Upgrade Best Practices • Downtime Reduction and Measures • Lessons Learnt • ADP Upgrade Snapshot • References • Q&A
About ADP • Automatic Data Processing, Inc. (NASDAQ: ADP), with about $12 billion in revenues and approximately 650,000 clients, is one of the world's largest providers of Human Capital Management. • Leveraging over 60 years of experience, ADP offers a wide range of human resource, payroll, tax and benefits administration solutions from a single source.
11i Instance Overview (ADP) • Upgrading from R11.5.10.2 to R12.1.3 • Database is already upgraded to 11.2.0.2.7 (more than a year back) • Non- RAC environment • Database size 14TB • Over 19 Years of SLA data to be upgraded • Have not evaluated, Staged appl_top, Shared appl_top • System is up to date on AD/ATG RUPs on 11i
ADP Production Architecture
Business Challenges • Production Downtime • Database Size 14TB • Large number of ADP customizations • Tightly Integrated with other ADP legacy systems • All the key business units within ADP uses Oracle E-Business Suite (Majors, Nationals, Small Business, Canada, Total Source, ADP India) • All ADP organizations uses E-Business Suite (Sales, Service, Finance, Accounting, and Marketing) • ADP’s Oracle E-Business Suite is mission critical system used globally by over 13K users
Upgrade Challenges • 1st upgrade iteration took 22 days • Major issues with upgrade performance for long running .sql’s • Series of failures during the 12.1.1 driver due to • Custom indexes, triggers on standard tables, locked statistics, tablespace quota and other known issues • Many R12 forms not working as it hangs or has navigation issues • Applied various forms patches from Oracle SRs to resolve forms related issues • Development identified many issues with various modules • AR, AP, IC, OM • Migration of custom_top to R12 was time consuming
Upgrade Best Practices • Review • Key documents (806593.1, 1080973.1, 761570.1, 399362.1, 954704.1) • Blogs to determine issues and additional patches Steven Chan • “Known-issues” sections from key “living” documents (Metalink Id: 1127593.1, 1448102.1) • Identify and execute tasks that could be completed in a separatedowntime, prior to the production upgrade • Split downtime into 2 weekends, if possible
Upgrade Best Practices (Contd..) • Pre-upgrade planning (week before go-live) • Installation of R12.1.1 • Pre-R12 patches • Database compatible patches with R12 • Run maintain snapshot on all the servers • Add sufficient space to System, Data and Index tablespaces • Run Gather Stats with 30% • Plan for additional storage for stage patches, pre-built code • Merging of patches • Creation of defaults.txt
Upgrade Best Practices (Contd..) • Pre-UpgradeActivities (Go-Live weekend) • Modify the init.ora parameters for the duration of the upgrade • H/W Configuration of the DB Server: • CPU Cores: 78, Memory: 680G
Upgrade Best Practices (Contd..) • Pre-Upgrade Activities (Go-Live weekend) • Apply Required Patches (CUP, AD..) • Fix the critical upgrade failures for known issues • Merge & Apply EBS pre-install patches to fix the upgrade failures (around 206 patches applied) • Drop the custom indexes, if any, on the standard tables • Pre-create large indexes which are time consuming (ZX_LINES_DET_FACTORS_N1)
Upgrade Best Practices (Contd..) • Create temporary indexes to improve the performance of the long running .sql’s (ar120trxl.sql, zxmigupgdb.sql) Create index XLA.XLA_EVENTS_CUST1 ON xla_events(ENTITY_ID,REFERENCE_NUM_1,EVENT_DATE) parallel nologging; Create index XLA.XLA_EVENTS_UPG1 on xla.xla_events(entity_id, event_date, event_status_code, process_status_code) parallel nologging; Create index AR.RA_CUST_TRX_LINE_GL_DIST_UPG1 on ar.ra_cust_trx_line_gl_dist_all(customer_trx_id, Trunc(gl_date), account_set_flag) parallel nologging; Create index AR.AR_RVC_APPLICATIONS_ALL_CUS1 on ar.ar_receivable_applications_all(customer_trx_id, trunc(gl_date), posting_control_id, receivable_application_id) parallel nologging;
Upgrade Best Practices (Contd..) • Create sql profiles to improve the performance of the .sql’s (ar120recpl.sql, appdstln.sql) which were Identified during test run • Setup the high JVM values for these parameters (environment variables) • ADJREOPTS=2048M • ADJRIOPTS=2048M • AFJSMARG=1024M
Upgrade Best Practices – Running Upgrade • Running Upgrade drivers 12.1.1/12.1.3 • Shutdown of 2nd Apps Server during the whole upgrade cycle saved significant time • Pull out the long running and standalone .sql’s from the 12.1.1 driver (zxartrxmigupg.sql) - (Suggested by Oracle ACS) • Adequate number of workers(60) • Higher batch size(10000) • Used various ADPATCH options to reduce the patching downtime phtofile,nocopyportion,nogenerateportion,nocompiledb
Upgrade Best Practices (Contd..) • Monitor the invalid objects during the driver runs • Continuous run of utlrp utility from the database techstack during the upgrade • Monitor upgrade using Enterprise Manager GRID and Identify the improvements using advisory control • Monitor space in tablespaces, session waits, alert.log • Run Gather Stats at intervals on schema & table level • AR, AP, ZX, XLA • Complete the 12.1.1 and 12.1.3 drivers and its post steps
Upgrade Best Practices (Contd..) • Post Upgrade Activities • Merging & Application of post upgrade patches at various stages (421 Patches) • ADP Customizations using Autoconfig templates • Migration of custom_top to R12 using automation • Porting custom modules into R12 using automation • R12 Code Migrations using QUEST STAT (3600 objects migrated) • Parallel run of long running .sql’s pulled from 12.1.1 driver (zxartrxmigupg.sql) • Reset init.ora parameters to original pre-upgrade values • Drop temporary indexes and profiles created prior to upgrade
Upgrade Best Practices (Contd..) • Clone of 2nd apps server from 1stserver • 1st apps server: Tar the main directory where all the TOP’s exists and copy to 2nd apps server and untar it • Copy the $CONTEXT_FILE from 1st apps server to 2nd apps server and rename it to point to 2nd apps server • Using the below PERL utility replace the 1st apps sever entries with the 2nd apps server • $ perl -pi -e 's| apsrv1 | apsrv2 |g' $CONTEXT_FILE • Now run the autoconfigon the 2nd apps server
Downtime Reduction and Measures (Summary) • Pre Upgrade activities in a different downtime window • Creation of temporary indexes • Fix the critical upgrade failures for known issues • Pre-create large indexes identified during the test runs • Shutdown of 2nd apps server • Parallelized pre and post-upgrade technical activities and automated them • Clone of 2nd apps server from 1st server
Lessons Learnt • Properly size RDBMS parameters for upgrade • DB Cache • SGA • PGA Aggregate Target • Parallel_max_servers • Job_queue_processes • Log_buffer • Plan for additional storage for staging patches, pre-built code • Optimize CBO stats • Use higher batch size (10000) • Choose adequate numbers of workers (60)
Lessons Learnt (Contd..) • Used various ADPATCH options • Continuous run of utlrp utility from the database techstack • Run standalone .sql’s pulled from 12.1.1 driver parallel with post upgrade steps • Monitor upgrade using Enterprise Manager GRID • Repeat the upgrade until all issues are fixed and downtime is consistent • Ensure new customizations comply to new standard
ADP R12.1.3 Upgrade Snapshot • Release: 11.5.10.2 to R12.1.3 on IBM AIX 5L • DB size: 15 TB • #Workers and batch size: 60, 10000 • #CPUs on DB server: 40, Memory: 680G • # hrs for 12.1.1 driver: 50Hrs • # hrs for 12.1.3 driver: 11Hrs • Total end-to-end downtime: 96Hrs
References • R12.1 documentation roadmap (790942.1) • Oracle E-Business Suite Release 12.1 Info center (806593.1) • Oracle E-Business Suite Release 12.1.3 Readme (1080973.1) • Database preparation guidelines for R12.1 upgrade (761570.1) • Recommended Performance Fixes (244040.1) • R12 Upgrade Sizing & Best Practices (399362.1) • R12.1 EBS pre-install patches Report (1448102.1)
Questions
Thank You!
