The Big Bang

1
The Big Bang

• A Case Study in Upgrading Oracle Applications
• and Migrating to Linux

Jeff Slavitz Oracle Applications
DBA Jeff_at_OracleAppsPro.Com
2
Overview

• Last September Camelbak upgraded from 11.5.4 to
  11.5.10 and migrated database and application
  from Solaris to Linux.
• This presentation will focus on migration and not
  on the 11.5.10 upgrade (we only have an hour)
• Goal is to give DBAs a road map of the documents
  and steps to migrate database and application
• Discuss migration problems and lessons learned
• IT Manager will review business issues related to
  the Big Bang

3
Camelbak and Oracle Apps

• 100 gig database
• Financial applications (GL, AP, PO, FA)
• Order Entry, Inventory, Shipping, WIP
• Custom materialized views used as data warehouse
• Approximately 300 employees in Petaluma, San
  Diego and the Phillipines

4
Technical Issues

• Running 11.5.4 / 8.1.7 / Solaris since 2002
• Database, application and operating system
  unchanged since then
• Extremely poor performance Nightly materialized
  view refresh takes 8 hours, user complaints
• Difficulty getting patches from Support
• Want to upgrade to 11.5.10 and migrate to Linux
• Customer wants to do a Big Bang, not a big
  explosion

5
Business Issues

• Jeannine Sarragossa, Camelbak IT Manager
• Applications upgrade issues
• Hardware Migration
• Constraints
• Cost
• Licensing impacts
• Big Bang vs. a stepped project for each component

6
Old Hardware

• Database and concurrent manager server
• Sun E3500 running Solaris
• 4 processors
• 4 gig memory
• Forms server
• Sun E3500 running Solaris
• 2 processors
• 2 gig memory

7
New Hardware

• Database server
• Dell PowerEdge 2950 running Red Hat Linux 64 bit
• Two Dual Core 2.33 ghz processors
• 16 gb memory
• Application server
• Dell PowerEdge 2950 running Red Hat Linux 32 bit
• Two Dual Core 2.33 ghz processors
• 16 gb memory
• Note to self
• Buy as much horsepower as you can budget.
• Buy more disk space than you think you will need.

8
The Big Bang Overview

• Process took 8 months from initial planning to Go
  Live
• Four full CRP runs to test the master task list
• Several mini-CRP runs to test which
  upgrade/migration path was fastest
• Experimented with staged APPL_TOP
• Tested upgrade and migration time on different
  machines
• Go Live downtime estimate reduced from 14 to 7
  days
• Normally would have pre-applied as many patches
  to PROD as possible, but didnt due to system age
  and testing requirements

9
The Big Bang Overview

• First step in upgrade/migration was to clone
  PROD to loaner Solaris system. This machine was
  faster, cloned from two tier to one. This
  allowed original PROD to be used for fallback
  system.
• Set all PROD tablespaces to read-only so company
  could run reports during upgrade downtime.
• While on 11.5.4 upgraded database from 8.1.7 to
  10.1. Skipped 9i 10.2 not supported with
  11.5.4.
• Upgrade application from 11.5.4 to 11.5.10 on
  Solaris 11.5.4 not supported on Linux.
• Upgrade database from 10.1 to 10.2 on Solaris.
  Datapump faster in 10.2
• Migrate database and application to Linux

10
Database Migration Requirements

• On OTN refer to installation guide for your
  platform, e.g. 10GR2 Linux x86-64 Install Guide
• VERY important to install all operating system
  patches and packages. You may need many RPMs to
  get all of the required kernel packages and
  versions.
• Need to set kernel parameters at least as high as
  indicated in installation guide

11
App Migration Requirements

• Read note 316806.1 , Oracle Applications
  Installation Update Notes Release 11i (11.5.10.2)
  for Linux x86 (or appropriate for your operating
  system)
• Apply all RPMs indicated to get proper packages
• Need gcc and g version 3.2.3. To find your
  version type gcc v (your version likely much
  higher). For Red Hat apply patch 4198954 to get
  the RPMs for the older version of gcc and g.
• You will also need
• JDK 1.3.1 or 1.4.2
• AD.I.5
• Autoconfig enabled

12
Migration Thoughts

• Consider upgrading source to highest RDBMS
  supported BEFORE migration to get improved
  DataPump performance and cool new features, e.g.
  attach and detach from running jobs
• 9i export/import slow
• 10GR1 DataPump 2 x faster than 9i
• 10GR2 DataPump 3 x faster than 9i
• Need to trade off expdp/impdp performance over
  time to upgrade if source machine is slower than
  target
• 10GR2 100gb export on Solaris 7 hours
• 10GR2 100gb import on Linux 5 hours
• Your mpg will vary based on your hardware
  configuration

13
Migration Thoughts

• If using ssh setup trusted FTP access between all
  computers involved in the upgrade
• Run "ssh-keygen -t rsa1" on source
• Copy source HOME/.ssh/identity.pub to target
  HOME/.ssh/authorized_keys
• Run "ssh-keygen -t dsa" on source
• Concatenate source HOME/.ssh/id_dsa.pub to
  target HOME/.ssh/authorized_keys

14
Database Migration

• Many documents - 3 binders, 25 page master task
  list and notes - organization is important
• Main document - Note 362205.1 Export/Import
  Process for 11i Using 10GR2. The major steps in
  the process are
• Clear existing network topology (applications
  understanding of which nodes belong to
  application)
• Deregister source database server as shown in
  note 362203.1 steps 1-4.
• Easier way is to clear entire network topology
  using FND_NET_SERVICES.remove_server as shown in
  note 218089.1, Autoconfig FAQ
• Create new topology by running autoconfig first
  on RDBMS and then on application.

15
Database Migration

• Apply patches to the source system.
• Minimize invalid objects. Run ORACLE_HOME/rdbms/
  admin/utlirp to invalidate all database objects.
  Then run utlrp to compile all database objects.
• Run scripts to extract settings, mainly advanced
  queue settings which arent recreated by the
  import process.

16
Database Migration

• Use expdp to export source. Export log may show
  warning messages for export of application
  triggers. Apply database patch 5459871 on source
  and target to prevent this problem.
• Time saver - Prepare target while export is
  running. Install 10GR2 as shown in note 362203.1
  steps 6-12. Create target database and schemas
  and prepare for import.
• Time saver - Backup target before import!

17
Database Migration

• Use impdp to import export file into target.
  Many messages in logfile.
• Perform post-import updates to setup advance
  queues, create context and spatial objects, and
  setup database context file.

18
Split Configuration

• During test upgrades can bring up the application
  at this point (application on old hardware,
  database on new hardware) to confirm database
  migration was successful.
• In application context file change s_dbhost,
  s_dbdomain and s_dbport to reflect new database
  configuration.
• Remove old database tier or clear network
  topology if you havent already
• Run autoconfig and start application.
• See note 369693.1 for more details.

19
Migrating Application

• Much easier than database migration
• Main document - Note 238276.1 Migrating to
  Linux with Oracle Applications Release 11. The
  major steps in the process are
• Enable autoconfig in source if it isnt already
• Copy APPL_TOP, OA_HTML, OA_JAVA, COMMON_TOP/util,
  COMMON_TOP/pages to target
• Run adgenpsf.pl to generate manifest of customer
  specific files. Upload file to Oracle
• Within 30 minutes a download patch file is ready.
  This patch contains Linux library files.

20
Migrating Application

• Common problem with manifest upload - bad header
• Release 11.5.10
• .2
• Correct it to read Release 11.5.10.2
• Run clone context tool, adclonectx.pl, to
  generate application context file on target.
• Run rapidinstall techstack to install iAS
  technology stack.
• Apply interoperability patches
• Run autoconfig
• Apply customer specific patch downloaded above
• Regenerate forms, reports, etc.
• Start the application

21
Post-Migration IssueRecompile All Database
Objects

• Post-Go-Live-P1 When querying orders users
  received this error
• ORA-4068 existing state of packages () has been
  discarded
• ORA-4065 not executed, altered or dropped stored
  procedure
• APPS.OE_HEAEDER_ADJ_UTIL
• ORA-6508 PL/SQL could not find program unit
  being called
• APPS.OE_HEAEDER_ADJ_UTIL
• ORA-6512 at APPS.OE_OE_TOTALS_SUMMARY, line 18
• ORA-6512 at APPS.OE_OE_TOTALS_SUMMARY, line 486
• To avoid this, after upgrade and migration
  complete, run ORACLE_HOME/rdbms/admin/utlirp to
  invalidate all database objects then utlrp to
  re-compile all objects.

22
Post-Migration IssueConfirm Concurrent Mgr Nodes

• After application migration concurrent manager
  administration screen may show nodes from source
  system. This causes concurrent manager to error
  on startup.

23
Confirm Concurrent Mgr Nodes
24
Confirm Concurrent Mgr Nodes

• To change nodes to point to target system
• As apps, exec fnd_conc_clone.setup_clean
• Run autoconfig on database then application node

25
Post Migration IssueRMAN Bug

• Bug 5247609 - With Oracle 10G RMAN has severe
  performance degradation when using cost based
  optimizer.
• Switch to rule based optimization at the start of
  all RMAN scripts
• sql alter session set optimizer_modeRULE

26
RMAN Bug

• Set to RBO before registering database in RMAN or
  you will get errors like this
• Starting full resync of recovery catalog
• RMAN-03014 implicit resync of recovery catalog
  failed
• RMAN-03009 failure of full resync
• ORA-01652 unable to extend temp segment by in
  tablespace
• Causes temp segment in SYSTEM tablespace to grow
  to 50 gb
• If your database is already registered in RMAN
  you will need to unregister and then re-register
  database

27
Post-Migration IdeaUse an SPFILE

• Use spfile instead of init.ora to start database
• Allows you to maintain database initialization
  parameters across instance shutdown and startup
• Allows you to dynamically change system
  parameters, e.g. alter system set xxx
  scopespfile,memory,both
• To create initial spfile, export init.ora to
  spfile
• cd ORACLE_HOME/dbs
• create spfile'spfilePROD.ora' from
  pfile'initPROD.ora'
• Restart database spfile will be used
  automatically
• UNLESS

28
Using an SPFILE with Oracle Apps

• The autoconfig generated addbctl.sh used to start
  the database is hardcoded to startup using an
  init.ora in adstrtdb.sql. To change to spfile
• Change autoconfig template ORACLE_HOME/appsutil/
  template/adstrtdb.sql
• OR, since autoconfig creates init.ora but never
  overwrites it change your init.ora to
• spfileORACLE_HOME/dbs/spfilePROD.ora
• Periodically copy spfile to init.ora as a backup.
• create pfile'initDB1test.ora' from
  spfile'spfileDB1.ora'

29
Using an SPFILE with Oracle Apps

• If you want to recreate SPFILE as part of running
  autoconfig
• Delete init.ora
• Run autoconfig to create new init.ora
• Shutdown database and restart using new init.ora
• Create new spfile from new init.ora
• Shutdown database
• Replace init.ora with custom init.ora that
  specifies spfile (or change adstrtdb.sql
  template)
• Restart database using new spfile
• Note 249664.1 - Pfile vs SPfile

30
Performance

• Before

• After

• Database server frequently running at 100
  capacity during business hours
• Users frequently complain about slowness
• Custom materialized view refresh took 8 hours to
  complete

• Database server load rarely above 25 even at
  peak times
• Users never complain about performance
• Custom materialized view refresh takes 5 minutes
  to complete

31
Lessons Learned

• Required a great deal of organization with
  documents
• Buy more CPUs and memory than you think you need
• Buy more disk space than you think you can use
• Disk drive failed during migration have spare
  hardware on-site
• Much of the work was done remotely. At one point
  my PC at work was unplugged. Consider using
  console with remote console software
• Use more than one DBA. Have all DBAs practice
  entire master task list so they can be
  interchangeable.

32
Lessons Learned

• As a DBA I was concerned about the Big Bang
• The business dictates the timing of the project.
• Alternatives would have been to upgrade
  now/migrate later, migrate database
  now/application later
• The cost to the business of not doing the Big
  Bang was more downtime, more user testing of each
  stage and delay of final implementation.
• The more you are going to do at one time the more
  you need a strong technical team.

33

• Questions?
• Jeff Slavitz
• Jeff_at_OracleAppsPro.com
• (415) 388 - 3003