tcoug upgrade

8/4/2019 TCOUG Upgrade

1/58

Best Practices for Upgrading to Oracle Database10g Release 2

Carol TagliaferriRavi Pattabhi


2/58

The following is intended to outline our generalproduct direction. It is intended for informationpurposes only, and may not be incorporated into anycontract. It is not a commitment to deliver any

material, code, or functionality, and should not berelied upon in making purchasing decisions.The development, release, and timing of anyfeatures or functionality described for Oracles

products remain at the sole discretion of Oracle.


3/58

Program Agenda

10g Release 2 Upgrade Enhancements

Upgrade Process

Manual Upgrade Steps Database Upgrade Assistant

Best Practices

Questions


4/58

Oracle Database 10g Release 2Upgrade Enhancements

Pre-Upgrade Information Tool

Simplified Upgrade

Upgrade performance enhancement

Post-Upgrade Status Tool


5/58

Pre-Upgrade Information Tool

SQL script, utlu102i.sql, analyzes the database to beupgraded

Checks for init.ora settings that may cause upgrade to failand 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/58

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/58

Simplified Upgrade

Upgrade driven from the contents of the componentregistry (DBA_REGISTRY view)

Single top-level script, catupgrd.sql, upgrades allcomponents in the database using the information inthe DBA_REGISTRY view

Supports re-run of catupgrd.sql, if necessary


8/58

Upgrade Performance Enhancement

Parallel recompilation of invalid PL/SQL databaseobjects 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/58

Simplified Documentation

One measure of simplicity: Database Upgrade Guidedocumentation 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/58

Post-Upgrade Status Tool

Run utlu102s.sql to display the results of the upgrade

Reviews the status of each component and lists the elapsedtime

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/58

Upgrade Process


12/58

Prepare to Upgrade

1. Become familiar with the features of the NewOracle Database 10g Release 2

2. Determine the upgrade path

3. Choose an upgrade method

4. Choose an OFA compliant Oracle Home directory

5. Prepare a backup strategy

6. Prepare a recovery strategy

7. Develop a test plan to test your database,applications, and reports


13/58

Oracle Database 10g Release 2Upgrade 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 toperform a double-hop upgrade

e.g. 8.1.5 -> 8.1.7 -> 10.2

7.3.4 -> 8.1.7 -> 10.2


14/58

Choose an Upgrade Method

Database Upgrade Assistant (DBUA) Automated GUI tool that interactively steps the user throughthe upgrade process and configures the database to run withOracle Database 10g Release 2

Manual Upgrade

Use SQL*Plus to perform any necessary actions to preparefor the upgrade, run the upgrade scripts and analyze theupgrade results

Export-Import

Use Data Pump or original Export/Import


15/58

Database Upgrade AssistantAdvantages 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/58

Manual Upgrade Advantages andDisadvantages

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/58

Sample Test Plan

Make a clone of your production system usingEnterprise 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/58

Manual Upgrade


19/58

Performing a Manual Upgrade - 1

1. Install Oracle Database 10g Release 2 in newORACLE_HOME

2. Analyze the existing database

Use rdbms/admin/utlu102i.sql with existing serverSQL> spool pre_upgrade.log

SQL> @utlu102i

3. Adjust REDO logs and tablespace sizes if necessary

4. Copy existing initialization files to new ORACLE_HOMEand make adjustments as recommended


20/58


5. Shutdown immediate (and backup), then switch to the newORACLE_HOME

6. Startup using the Oracle Database 10g Release 2 server:

SQL> startup upgrade

7. Create the SYSAUX tablespace (8.1.7, 9.0.1, 9.2.0)

SQL> create tablespace SYSAUX datafile'e:\oracle\oradata\empdb\sysaux01.dbf'

size 500M

auto extend on next 50M reuse

extent management local

segment space management autoonline;


21/58


8. Run the upgrade

SQL> spool upgrade.logSQL> @catupgrd.sql

SQL> spool off (review spool file for errors)

9. Shutdown and restart

SQL> shutdown immediate

SQL> startup

10. Recompile and revalidate any remaining applicationobjects

SQL> @utlrp (will do parallel compile on multi-processorsystem)


22/58

Startup Upgrade

STARTUP UPGRADE mode will suppress normalupgrade errors

Previously, STARTUP MIGRATE in Oracle Database 9i R2

Only real errors are spooled

Automatically handles setting system parameters that canotherwise cause problems during upgrade

Turns off job queues

Disables system triggers

Allows AS SYSDBA connections only


23/58

Now you are ready to use OracleDatabase 10g Release 2!

Perform any required post-upgrade steps

Make additional post-upgrade adjustments toinitialization parameters

Test your applications and tune performance

Finally, set initialization parameter COMPATIBLE to10.2.0 to make full use of Oracle Database 10gRelease 2 features

9.2 is the minimum compatibility required for 10.2


24/58

Database UpgradeAssistant


25/58

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/58

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 SYSTEMtablespace size, rollback segments size, redo log size

Checks disk space for auto extended datafiles

Creates mandatory SYSAUX tablespace


27/58


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 betaken

Wizard warns user about any issues found

Provides space analysis information for backup

Applies required changes to network configuration files


28/58

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 makesnecessary configuration changes

Logging and Tracing Writes detailed trace and logging files

(ORACLE_HOME/cfgtoollogs/dbua//upgradeNN)


29/58


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/58

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

dbuainitParam param1=value1,param2=value2

Silent mode provides single command upgradedbuasilentsid ora9idb

[-backup ]


31/58

Upgrade Results

HTML Report

Displays results of upgrade process

Provides Post-Upgrade database information such as Versionand 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/58

Proposed New Features for 10g R2

XE Upgrades

Customers will be able to do upgrades from Oracle ExpressEdition to Standard / Enterprise Edition databases using afuture 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 anew 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 AutomaticStorage Management Disk Groups


33/58

Command Line Syntax

Silent mode run

dbuasilentdbName Backup location

backupLocation

Custom scripts

-postUpgradeScripts Initialization parameters

initParam

Help

-help EM configuration

emConfiguration


34/58

WelcomeScreen


35/58

Select the database to upgrade


36/58

Tablespace


37/58

Log Files


38/58

Create SYSAUX Tablespace


39/58

Parallel Recompilation


40/58

Database Backup


41/58

Management Options


42/58

Recovery Configuration


43/58

Database Credentials


44/58

Pre-Upgrade Summary Page - 1


45/58

Pre-Upgrade Summary Page - 2


46/58

Upgrade Progress


47/58

Upgrade Results


48/58

Upgrade Results


49/58

Best Practices

B P i


50/58

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

B P i 2


51/58

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 andfunctional goals have been met

Pre-Upgrade Analysis

You can run DBUA without clicking finish to get a pre-upgradeanalysis or utlu102i.sql

Read general and platform specific release notes to catchspecial cases

B t P ti 3


52/58

Best Practices - 3

Automate your upgrade

Use DBUA in command line mode for automating yourupgrade

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 databasesoftware

Exception: 10g R1 to 10g R2, run utlip.sql as last stepprior to upgrading to 10gR2

Check for sufficient space in SYSTEM, UNDO,TEMP and REDO logs

B t P ti 4


53/58

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 andOPTIMIZER_FEATURES_ENABLE parameters to enable

new optimizer features

B t P ti 5


54/58

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 plansgenerated by CBO

Check for invalid objects in the database beforeupgrading

SQL> select owner, object_name, object_type, statusfrom dba_objects where statusVALID;

B t P ti 6


55/58

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

F i f


56/58

For more info . . .

Read Metalink Note 316889.1Complete checklist

for manual upgrades to 10gR2

Read the Oracle Database Upgrade Guide 10gRelease 2 (10.2) documentation

And Please Read the Release Notes


57/58

Questions?


58/58

tcoug upgrade

Documents