Best Practices for Upgrading to Oracle Database 10g Release 2

1
Best Practices for Upgrading to Oracle Database
10g Release 2

• Carol Tagliaferri
• Ravi Pattabhi

2
The following is intended to outline our general
product direction. It is intended for information
purposes only, and may not be incorporated into
any contract. It is not a commitment to deliver
any material, code, or functionality, and should
not be relied upon in making purchasing
decisions.The development, release, and timing
of any features or functionality described for
Oracles products remain at the sole discretion
of Oracle.
3
Program Agenda
ltInsert Picture Heregt

• 10g Release 2 Upgrade Enhancements
• Upgrade Process
• Manual Upgrade Steps
• Database Upgrade Assistant
• Best Practices
• Questions

4
Oracle Database 10g Release 2 Upgrade
Enhancements

• Pre-Upgrade Information Tool
• Simplified Upgrade
• Upgrade performance enhancement
• Post-Upgrade Status Tool

5
Pre-Upgrade Information Tool

• SQL script, utlu102i.sql, analyzes the database
  to be upgraded
• Checks for init.ora settings that may cause
  upgrade to fail and generates warnings
• Utility runs in old server old database
  context
• Provides guidance and warnings based on Oracle
  Database 10g Release 2 upgrade requirements
• Supplies information to the DBUA to automatically
  perform any required actions

6
Pre-Upgrade Analysis

• The Pre-Upgrade Information Tool checks for
• Database version and compatibility
• Redo log size
• Updated initialization parameters (e.g.
  shared_pool_size)
• Deprecated and obsolete initialization parameters
• Components in database (JAVAVM, Spatial, etc.)
• Some components require installing companion CD
• Tablespace estimates
• Increase in total size
• Additional allocation for AUTOEXTEND ON
• SYSAUX tablespace

7
Simplified Upgrade

• Upgrade driven from the contents of the component
  registry (DBA_REGISTRY view)
• Single top-level script, catupgrd.sql, upgrades
  all components in the database using the
  information in the DBA_REGISTRY view
• Supports re-run of catupgrd.sql, if necessary

8
Upgrade Performance Enhancement

• Parallel recompilation of invalid PL/SQL database
  objects on multiprocessor CPUs
• Utlrp.sql can now exploit multiple CPUs to speed
  up the time required to recompile any stored
  PL/SQL and Java code.

9
Simplified Documentation

• One measure of simplicity Database Upgrade Guide
  documentation size is decreasing dramatically
• 8.1.7 - 512 pages
• 9.0.1 - 484 pages 111 steps total for all
  components!
• 9.2.0 - 344 pages
• 10.1.0 - 154 pages
• 10.2.0 - 130 pages - 16 steps for manual upgrade

10
Post-Upgrade Status Tool

• Run utlu102s.sql to display the results of the
  upgrade
• Reviews the status of each component and lists
  the elapsed time
• Provides information about invalid/incorrect
  component upgrades
• DBUA calls this script to display status and then
  takes corrective action
• Automatically called by catupgrd.sql

11
ltInsert Picture Heregt
Upgrade Process
12
Prepare to Upgrade

• Become familiar with the features of the New
  Oracle Database 10g Release 2
• Determine the upgrade path
• Choose an upgrade method
• Choose an OFA compliant Oracle Home directory
• Prepare a backup strategy
• Prepare a recovery strategy
• Develop a test plan to test your database,
  applications, and reports

13
Oracle Database 10g Release 2 Upgrade Paths

• Direct upgrade from 10.1.0, 9.2.0, 9.0.1, and
  8.1.7
• If you are not at one of these versions you need
  to perform a double-hop upgrade
• e.g. 8.1.5 -gt 8.1.7 -gt 10.2
• 7.3.4 -gt 8.1.7 -gt 10.2

14
Choose an Upgrade Method

• Database Upgrade Assistant (DBUA)
• Automated GUI tool that interactively steps the
  user through the upgrade process and configures
  the database to run with Oracle Database 10g
  Release 2
• Manual Upgrade
• Use SQLPlus to perform any necessary actions to
  prepare for the upgrade, run the upgrade scripts
  and analyze the upgrade results
• Export-Import
• Use Data Pump or original Export/Import

15
Database Upgrade Assistant Advantages and
Disadvantages

• Advantages
• Automates all tasks
• Performs both Release and Patch set upgrades
• Supports RAC, Single Instance and ASM
• Informs user and fixes upgrade prerequisites
• Automatically reports errors found in spool logs
• Provides complete HTML report of the upgrade
  process
• Command line interface allows ISVs to automate
• Disadvantages
• Offers less control over individual upgrade steps

16
Manual Upgrade Advantages and Disadvantages

• Advantages
• The DBA controls every step of the upgrade
  process
• Disadvantages
• More work
• Manually check spool logs for errors
• More error prone
• Harder to automate

17
Sample Test Plan

• Make a clone of your production system using
  Enterprise Manager
• Upgrade test database to latest version
• Update COMPATIBLE to latest version
• Run your applications, reports, and legacy
  systems
• Ensure adequate performance by comparing metrics
  gathered before and after upgrade
• Tune queries or problem SQL statements
• Update any necessary database parameters

18
ltInsert Picture Heregt
Manual Upgrade
19
Performing a Manual Upgrade - 1

• Install Oracle Database 10g Release 2 in new
  ORACLE_HOME
• Analyze the existing database
• Use rdbms/admin/utlu102i.sql with existing server
• SQLgt spool pre_upgrade.log
• SQLgt _at_utlu102i
• Adjust REDO logs and tablespace sizes if
  necessary
• Copy existing initialization files to new
  ORACLE_HOME and make adjustments as recommended

20
Performing a Manual Upgrade - 2

• Shutdown immediate (and backup), then switch to
  the new ORACLE_HOME
• Startup using the Oracle Database 10g Release 2
  server
• SQLgt startup upgrade ltpfilegt
• Create the SYSAUX tablespace (8.1.7, 9.0.1,
  9.2.0)
• SQLgt create tablespace SYSAUX datafile
  'e\oracle\oradata\empdb\sysaux01.dbf'
• size 500M
• auto extend on next 50M reuse
• extent management local
• segment space management auto
• online

21
Performing a Manual Upgrade - 3

• Run the upgrade
• SQLgt spool upgrade.log
• SQLgt _at_catupgrd.sql
• SQLgt spool off (review spool file for errors)
• Shutdown and restart
• SQLgt shutdown immediate
• SQLgt startup
• Recompile and revalidate any remaining
  application objects
• SQLgt _at_utlrp (will do parallel compile on
  multi-processor system)

22
Startup Upgrade

• STARTUP UPGRADE mode will suppress normal
  upgrade errors
• Previously, STARTUP MIGRATE in Oracle Database
  9i R2
• Only real errors are spooled
• Automatically handles setting system parameters
  that can otherwise cause problems during upgrade
• Turns off job queues
• Disables system triggers
• Allows AS SYSDBA connections only

23
Now you are ready to use Oracle Database 10g
Release 2!

• Perform any required post-upgrade steps
• Make additional post-upgrade adjustments to
  initialization parameters
• Test your applications and tune performance
• Finally, set initialization parameter COMPATIBLE
  to 10.2.0 to make full use of Oracle Database 10g
  Release 2 features
• 9.2 is the minimum compatibility required for 10.2

24
ltInsert Picture Heregt
Database Upgrade Assistant
25
Database Upgrade Assistant

• DBUA is a GUI and command line tool for
  performing database upgrades
• Uses a Wizard Interface
• Automates the upgrade process
• Simplifies detecting and handling upgrade issues
• Supported Releases
• 8.1.7, 9.0.1, 9.2 and 10.1 to 10.2
• Patchset Upgrades
• Supported 10.2.0.3 onwards
• Support the following database types
• Single instance
• Real Application Clusters
• Automatic Storage Management

26
Key DBUA Features - 1

• Upgrade Scripts
• Runs all necessary scripts to perform the upgrade
• Progress
• Displays upgrade progress at a component level
• Configuration Checks
• Automatically makes appropriate adjustments to
  initialization parameters
• Checks for adequate resources such as SYSTEM
  tablespace size, rollback segments size, redo log
  size
• Checks disk space for auto extended datafiles
• Creates mandatory SYSAUX tablespace

27
Key DBUA Features - 2

• Recoverability
• Performs a BACKUP of the database before upgrade
• If needed can restore the database after upgrade
• Pre-Upgrade Summary
• Prior to upgrade provides summary of all actions
  to be taken
• Wizard warns user about any issues found
• Provides space analysis information for backup
• Applies required changes to network configuration
  files

28
Key DBUA Features - 3

• Configuration files
• Creates init.ora and spfile in new ORACLE_HOME
• Updates network configurations
• Uses OFA compliant locations
• Updates database information on Oracle Internet
  Directory
• Oracle Enterprise Manager
• Allows you to setup and configure EM DB Control
• Allows you to register database with EM Grid
  Control
• If EM is in use upgrades EM repository and makes
  necessary configuration changes
• Logging and Tracing
• Writes detailed trace and logging files
  (ORACLE_HOME/cfgtoollogs/dbua/ltsidgt/upgradeNN)

29
Key DBUA Features - 4

• Real Application Clusters
• All nodes are upgraded
• All configuration files are upgraded
• Minimizing Downtime
• Speeds up upgrade by disabling archiving
• Recompiles packages in parallel
• User interaction is not required after upgrade
  starts
• Security features
• Locks new users in the upgraded database

30
Customizability

• Post-Upgrade Scripts
• ISVs can customize DBUA to run their upgrade
  scripts
• XML Driven
• XML files drive the DBUA engine
• Created by Pre-Upgrade Information Tool
  Post-Upgrade Status Tool
• Initialization Parameter Changes
• Accepts initialization parameters overrides from
  user
• dbua initParam param1value1,param2value2
• Silent mode provides single command upgrade
• dbua silent sid ora9idb
• -backup ltlocationgt

31
Upgrade Results

• HTML Report
• Displays results of upgrade process
• Provides Post-Upgrade database information such
  as Version and ORACLE_HOME
• Component Level Results
• Reports on the success / failure of each
  component
• Provides a report on errors or warnings
  categorized per component
• Allows DBA to unlock new database users
• Allows DBA to restore the original database

32
Proposed New Features for 10g R2

• XE Upgrades
• Customers will be able to do upgrades from Oracle
  Express Edition to Standard / Enterprise Edition
  databases using a future 10g R2 patch
• You can rename your database instance
• Move Database Files During Upgrade
• Allows you to relocate database files as part of
  upgrading to a new release
• Supported for XE, SE, or EE single instance
  databases
• Requires a 10g R2 patch
• Supported through both command line and GUI
• Migrating Database files to ASM
• Database Files may also be optionally moved into
  Automatic Storage Management Disk Groups

33
Command Line Syntax

• Silent mode run
• dbua silent dbName ltOracle databasegt
• Backup location
• backupLocation
• Custom scripts
• -postUpgradeScripts
• Initialization parameters
• initParam
• Help
• -help
• EM configuration
• emConfiguration

34
Welcome Screen
35
Select the database to upgrade
36
Tablespace
37
Log Files
38
Create SYSAUX Tablespace
39
Parallel Recompilation
40
Database Backup
41
Management Options
42
Recovery Configuration
43
Database Credentials
44
Pre-Upgrade Summary Page - 1
45
Pre-Upgrade Summary Page - 2
46
Upgrade Progress
47
Upgrade Results
48
Upgrade Results
49
ltInsert Picture Heregt
Best Practices
50
Best Practices - 1

• The three Ts TEST, TEST, TEST
• Test the upgrade
• Test the application(s)
• Test the recovery strategy
• Functional Testing
• Clone your production database on a machine with
  similar resources
• Use DBUA for your upgrade
• Run your application and tools to ensure they work

51
Best Practices - 2

• Performance Analysis
• Gather performance metrics prior to upgrade
• Gather AWR or Statspack baselines during various
  workloads
• Gather sample performance metrics after upgrade
• Compare metrics before and after upgrade to catch
  issues
• Upgrade production systems only after performance
  and functional goals have been met
• Pre-Upgrade Analysis
• You can run DBUA without clicking finish to get a
  pre-upgrade analysis or utlu102i.sql
• Read general and platform specific release notes
  to catch special cases

52
Best Practices - 3

• Automate your upgrade
• Use DBUA in command line mode for automating your
  upgrade
• Useful for upgrading a large number of databases
• Logging
• For manual upgrade, spool upgrade results and
  check logs for possible issues
• DBUA can also do this for you
• Automatic conversion from 32 bit to 64 bit
  database software
• Exception 10g R1 to 10g R2, run utlip.sql as
  last step prior to upgrading to 10gR2
• Check for sufficient space in SYSTEM, UNDO, TEMP
  and REDO logs

53
Best Practices - 4

• Use Optimal Flexibility Architecture (OFA)
• Offers best practices for locate your database
  files, configuration files and ORACLE_HOME
• Use new features
• Migrate to CBO from RBO
• Automatic management features for SGA, Undo, PGA
  etc.
• Use AWR/ADDM to diagnose performance issues
• Consider using the SQL tuning advisor
• Change COMPATIBLE and OPTIMIZER_FEATURES_ENABLE
  parameters to enable new optimizer features

54
Best Practices - 5

• Use Enterprise Manager Grid Control to manage
  your enterprise
• Use EM to setup new features and try them out
• EM provides complete manageability solution for
  Databases, Applications, Storage, Security,
  Networks
• Collect Object and System Statistics to improve
  plans generated by CBO
• Check for invalid objects in the database before
  upgrading
• SQLgt select owner, object_name, object_type,
  status from dba_objects where
  statusltgtVALID

55
Best Practices- 6

• Avoid upgrading in a crisis
• Keep up with security alerts
• Keep up with critical patches needed for your
  applications
• Keep track of de-support schedules
• Always upgrade to latest supported version of the
  RDBMS
• Make sure patchset is available for all your
  platforms

56
For more info . . .

• Read Metalink Note 316889.1 Complete checklist
  for manual upgrades to 10gR2
• Read the Oracle Database Upgrade Guide 10g
  Release 2 (10.2) documentation
• And Please Read the Release Notes

57
ltInsert Picture Heregt
Questions?
58
(No Transcript)