move to oracle database 11g – the whole story · move to oracle database 11g – the whole story...

<Insert Picture Here>

Move to Oracle Database 11g – The whole StoryMike Dietrich. Carol Tagliaferri, Roy SwongerDatabase Upgrade & UtilitiesORACLE Corporation

Welcome!!!

Preparation

Agenda

Upgrade

Diagnostics & Tuning

Best Practices

News and Task List

Performance Testing

Preparation

Agenda

Support PolicyPatches

CertificationDocumentationUpgrade paths

InstallationEditions

Upgrade


Best Practices

News and Task List

Performance Testing

Lifetime Support Policy

t

2009

2010

today

Sustaining SupportPremier Support

R2 July 2007Extended Support

July 2010

January 2009 January 2012

R2 July 2010 July 2013

August 2012 August 2015

July 2008

July 2011

2004

2005

2006

2007

2002

2003

2008

2011

2012

2013

2014

2016

2015

http://www.oracle.com/support/library/brochure/lifetime-support-technology.pdf

Lifetime Support PolicyFeature Premier

SupportExtended Support

Sustaining Support

Major Product and Technology ReleasesMajor Product and Technology Releases

Technical SupportTechnical Support

Access to Knowledge BaseAccess to Knowledge Base(MetaLink/Customer Connection/SupportWeb)(MetaLink/Customer Connection/SupportWeb)

Updates, Fixes, Security Alerts and Critical Patch Updates, Fixes, Security Alerts and Critical Patch UpdatesUpdates

PrePre--existing Onlyexisting Only

Tax, Legal and Regulatory UpdatesTax, Legal and Regulatory Updates NoNo

Upgrade ScriptsUpgrade Scripts NoNo

Certification with existing Third Party Certification with existing Third Party Products/VersionsProducts/Versions

NoNo

Certification with new Oracle ProductsCertification with new Oracle Products NoNo

Certification with New Third Party Certification with New Third Party Products/VersionsProducts/Versions

NoNo NoNo

Lifetime Support Policy• See:

http://www.oracle.com/support/library/brochure/lifetime-support-technology.pdf

Releases – Support-SRs

9%

Upgrade to Oracle Database 11g

R2R2≥ 7.3.4≥ 7.3.4

≥ 9.2.0.4≥ 9.2.0.4

≥ 8.0.6≥ 8.0.6

≥ 8.1.7.4≥ 8.1.7.4

≥ 9.0.1.4≥ 9.0.1.4

R2R2

Recommended Patches

Important Alerts?• Note 161818.1

Upgrade Information / Alerts• Note:454507.1

Support Status and Alerts for Oracle 11g Release 1 (11.1.0.X)

Upgrade Information / Alerts• Note 738538.1 Known Issues specific to the 11.1.0.7 Patch Set

• Why DST timezone patches? (DST: Daylight Savings Time)

• The date for moving to DST has been changed for 7 different timezones since 2007

• HST - EST - MST - EST5EDT - MST7MDT - CST6CDT - PST8PDT• Current DST definitions are included from within the following

patch set• 10.2.0.4

• Oracle Database 11g requires timezone version V.4• Oracle 9i: Timezone V1• Oracle 10g: Timezone V2

• Without timezone V4 no upgrade to Oracle Database 11g will be possible!!!

Timezone Patches

• Summary – 4 important ML notes:• Note 414590.1: FAQ and Issues• Note 413671.1: Timezone V4 - Checks and Links to the Patches•

• Note 359145.1: Links to download utltzuv2.sql

• Note 396387.1: Explanations• Run script utltzuv2.sql to recognize TIMESTAMP WITH TIMEZONE• Files timezone.dat and timezlrg.dat will be applied

Timezone Patches

Timezone Patches• Timezone How To:

• Download the right patch according to the table in Note 413671.1

• Download the script utltzuv2.sql according to Note 359145.1

• Run the script utltzuv2.sql in your current source database• Apply files timezone.dat and timezlrg.dat to the source

$ORACLE_HOME/oracore/zoneinfo directory with either Opatchor manually and restart the database

Verify Database Creation Wordsize - 10.2.0.3

• Has the database been created originally in a 32-bit environment and is now on a 64-bit platform?•

• Happens in 10.2.0.3 - see Note:412271.1• Apply patch:5871314 and patch:5892355 if:

• KOPM$.METADATA contains 'B023'created in 32-bit env

• Don't apply patch if:• KOPM$.METADATA contains 'B047'

created in 64-bit env• Database version is 10.2.0.4

select decode(instr(metadata,'B023'),0,'64bit Database','32bit Database') "DB Creation"from kopm$;

select decode(instr(metadata,'B023'),0,'64bit Database','32bit Database') "DB Creation"from kopm$;

Related to Database Creation Wordsize - 11gR1

• Database upgrade to 11.1.0.6:• Delete orphan rows from KOTTD$• Apply patch 6770913

• Database upgrade to 11.1.0.7:• Delete orphan rows from KOTTD$• No patches necessary

• If result is not null:

• See Note:579523.1 - bug:6770913

select sys_nc_oid$ from kottd$ where sys_nc_oid$ not in (select oid$ from obj$ where type#=13); select sys_nc_oid$ from kottd$ where sys_nc_oid$ not in (select oid$ from obj$ where type#=13);

delete from kottd$ where sys_nc_oid$ not in (select oid$ from obj$ where type#=13); commit;

delete from kottd$ where sys_nc_oid$ not in (select oid$ from obj$ where type#=13); commit;

Oracle Certification

•

Recommended OS patches• Note: 169706.1

• Note: 401705.1 Linux x86, x86-64, and s390x Requirements Reference List

Upgrade is easier!• The upgrade to Oracle Database 11g is much easier

than any upgrades to earlier Oracle releases

• Size of Upgrade guides:• 8.1.7 - 512 pages• 9.0.1 - 484 pages – 111 steps for an RDBMS with 9 components

• 9.2.0 - 344 pages• 10.1.0 - 170 pages - only 6 stepsl!!!• 10.2.0 - 140 pages • 11.1.0 - 186 pages - now detailed explanations and

information about new options included

Upgrade Length

• How long will the upgrade taketo complete?• Independent of:

• Size of the database• Used datatypes

• Dependent on:• The number of objects needing upgrading

• Thus: the number of installed options/components• Valid and non-stale data dictionary statistics• Number of synonyms – they‘ll get recompiled (upgrade from 9i)• Number of objects in XDB• At a very low rate if COMPATIBLE will be increased:

• Number of datafiles• Size of redo logs

Documentation• Note: 466181.1 Upgrade Companion 10gR2

• Note: 601807.1 Upgrade Companion 11g

Documentation• Note: 601807.1 Upgrade Companion 11g

Documentation• Upgrade Guide

•

http://download.oracle.com/docs/cd/B28359_01/server.111/b28300/toc.htm

• Note:429825.1Complete Checklist for Manual Upgrades to 11gR1

• Note: 421191.1Complete checklist for manual upgrades from X to Y

OTN Upgrade Page• http://www.oracle.com/technology/products/database/oracle11g/upgrade/index.html

OTN Upgrade Forum• http://forums.oracle.com/forums/forum.jspa?forumID=583&start=0

40 External 11g References 1/2

05-May-2009

40 External 11g References 2/2

05-May-2009

Upgrade Paths

OS Change?OS Change?YY NN

UPGRADEUPGRADEExport/ImportExport/Import

CTAS, COPYCTAS, COPY

Transportable TablespacesTransportable Tablespaces

Transportable DatabaseTransportable Database

Oracle StreamsOracle StreamsDBUA DBUA

Downtime >45min?Downtime >45min?

YY

NN

SQL ApplySQL Apply

ORACLE recommended

ORACLE recommended

CLI CLI SQL> @catupgrd

Preparation• Get INVALID Objects:

Recompile invalid objects in SYS and SYSTEM with utlrp.sql• Compare invalid objects from before and after the upgrade

• Beginning with 11.1.0.7 the comparison has been automated• registry$sys_inv_objs, registry$nonsys_inv_objs => utluiobj.sql

• Get accurate performance statistics• Comparison: before ↔ after• Time specific queries and batches

• Test upgrade of the database• Functional tests• Performance tests with real life loads!!!

SQL> SELECT UNIQUE object_name, object_type, ownerFROM dba_objects WHERE status='INVALID';

SQL> SELECT UNIQUE object_name, object_type, ownerFROM dba_objects WHERE status='INVALID';

Preparation• Possibilities for performance analysis:

• STATSPACK – export Perfstat user!!!• Note:394937.1 STATSPACK guide

• AWR - extract the AWR • Do AWR diff reports comparing before/after upgrade

• Real Application Testing• SQL Performance Analyzer • Database Replay

Preparation• If upgrading from 10g or 11g, purge the recyclebin

SQL> purge DBA_RECYCLEBIN;SQL> purge DBA_RECYCLEBIN;

Preparation• Create dictionary statistics prior to the upgrade - otherwise

it will take significantly longer

• Or in / :

SQL> EXECUTE dbms_stats.gather_schema_stats('SYS',options => 'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO',cascade => TRUE);

SQL> EXECUTE dbms_stats.gather_schema_stats('SYS',options => 'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO',cascade => TRUE);

SQL> EXECUTE dbms_stats.gather_dictionary_stats;SQL> EXECUTE dbms_stats.gather_dictionary_stats;

Preparation• Switch off DATABASE VAULT (if used/installed)

• Oracle® Database Vault Administrator's Guide: Appendix B• Unix: (simplified)

• Relinking without Database Vault[$ make -f ins_rdbms.mk dv_off ]

•

• Windows: • Rename oradv10.dll in ORACLE_HOME\bin• Upon restart:

• After upgrading relink with dv_on or rename the DLL and enable Database Vault again:

$ dvca -action disable ...$ dvca -action disable ...

$ dvca -action disable ...$ dvca -action disable ...

$ dvca -action enable ...$ dvca -action enable ...

Preparation• Drop SYS.PLAN_TABLE$ and SYS.PLAN_TABLE

• See Alert-Note:782735.1 and Notes: 605317.1 and 736353.1• Otherwise the component "Oracle Server" can be INVALID after

the upgrade• Introduced with DBMS_SQLPA• Steps to solve this issue if it has happened:

• @catplan.sql -- recreate the plan table @dbmsxpln.sql -- reload dbms_xplan spec @prvtxpln.plb -- reload dbms_xplan implementation @prvtspao.plb -- reload dbms_sqlpa

Installation• Space / resource requirements

• Software installation:• 150-200 MB in /tmp or \TEMP necessary• Between 2.7 GB (Windows), 3.5 GB (Linux) and 6.9 GB (HP-UX)

• Seed database: ≥ 1,6 GB• Seed databases (ORCL) are always prebuilt EE databases with all

options!!!

• RAM: ≥ 1GB

• Swapspace• RAM up to 2048Mb → 1.5x RAM• RAM between 2049Mb and 8192Mb → 1x RAM• RAM more than 8192Mb → 0.75x RAM

Installation• How to access the software?

• Download from OTN:http://otn.oracle.com/software/index.html

• Metalink:

• http://edelivery.oracle.com/

Installation• Download from OTN:

Installation• DVD contents?

• Database• Enterprise Edition• Standard Edition• Personal Edition (Windows)

• Client• Clusterware

• Extra: Examples (formerly known as companion)• Required Products• Oracle Globalization Support Demos• Oracle Multimedia Demos• Oracle Precompiler Demos• Oracle Spatial Demos• Oracle SQLJ Demos• Oracle XML Demos• Oracle JDBC Development Drivers• Oracle Text Knowledge Base

Installation• Pre-installation checks:

• Warning: 500MB swap space needed – also on Windows!!!

• Ask support before installing with:./runInstaller -ignoreSysPrereqs

Installation - Loopback Adapter for DBcontrol

• Microsoft Loopback Adapter is recommended if IP address is distributed via DHCP

• Install the MS Loopback Adapter• Launch the Add Hardware wizard and install a new network

adapter - manufacturer is Microsoft• Adjust this adapter to be the primary network adapter

• My Network Places => Properties => TCP/IP• IP address: 10.10.10.10• Subnet mask: 255.255.255.0

• Edit:• \windows\system32\drivers\etc\hosts

• 10.10.10.10 mycomputer.mydomain.com mycomputer

• http://download.oracle.com/docs/cd/B28359_01/install.111/b32006/reqs.htm#sthref291

Installation - Loopback Adapter for DBcontrol

• Launch the Windows Add Hardware Wizard

• Choose Yes, I've already connected the hardware ...

• Scoll down to Add a new hardware device

• Choose Install the hardware that I manually select ...

• Select Network Adapters:• Click on Manufacturer Microsoft• Install the Loopback Adapter• Make the Loopback Adapter

the FIRST network card in TCP/IP properties!!

Installation•

Installation•

Important:„CUSTOM“ ALWAYS installs

Enterprise Edition

Important:„CUSTOM“ ALWAYS installs

Enterprise Edition

Installation• Custom installation:

• 10gR2 doc installation guide

• 11gR1 doc installation guidehttp://download.oracle.com/docs/cd/B28359_01/install.111/b32002/install_overview.htm#BABEGJFD

Patch Set Installation• Install patch sets and patches before you start the upgrade

Unattended Installation/Configuration• Two options:

• Oracle Universal Installer OUI• ./runInstaller -record -destinationFile ...• ./runInstaller -silent -noconsole -responseFile ...

• But this has to be done for a patch set, too

• Home Cloning (script or Provisioning Pack)• Prepare a fully patched Oracle Home• Create an archive consisting of all files

• Exclude *log, *dbf, tnsnames/listener/sqlnet.ora• ./runInstaller -silent -clone -...

• Listener Configuration• ./netca /silent /responseFile $OH/inventory/response/netca.rsp

Installation• Overview on differences of database editions and available

options by edition• Feature differences:

Note.465465.1Differences Between Enterprise, Standard and Personal Editions on Oracle 10.2

• Oracle® Database Licensing Information11g Release 1 (11.1)Part Number B28287-01http://download.oracle.com/docs/cd/B28359_01/license.111/b28287/editions.htm

Installation• Windows OS - Home Selector :

Listener Configuration• Best Practices:

Create a new listener environment with NETCA:

Preparation

Agenda

Upgrade


Best Practice

News and Task List

Performance Testing

Database Upgrade AssistantCommand Line Upgrade

Post UpgradeAlternatives

CRS UpgradeASM Upgrade

Migration

Database Upgrade Assistant (GUI)• Features:

• Graphically led upgrade• Lots of important checks• RAC aware - inclusion of all nodes

• for RAC (almost) a must !!!• Offline Backup and Restore possible• ASM upgrade• Oracle XE upgrade• Patch upgrades

• Logs:• $ORACLE_HOME/cfgtoollogs/dbua

• Documentation:• Oracle® Database Upgrade Guide

http://download.oracle.com/docs/cd/B28359_01/server.111/b28300/toc.htm

Database Upgrade Assistant (GUI)• Silent mode:

• $ dbua –help shows all valid options• See doc:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28300/upgrade.htm

Database Upgrade Assistant (GUI)

• dbua [ -silent ] [ -sid SID ] [-oracleHome home_name][-oracleBase base_name] [-diagnosticDest diagnostic_destination][-sysDBAUserName SYSDBA_user] [-sysDBAPassword SYSDBA_pwd] [-upgradeASM] [-autoextendFiles] [-newGlobalDbName db_name][-newSid new_SID] [-generateMapFile] [-useASM][-commonFileLocation common_files] [-omfLocation omf_area] [-databaseMapFile map_file_name] [-newRecoveryArea recover_area][-newRecoveryAreaSize recover_size] [-apexAdminPassword apex_pwd][-disableUpgradeScriptLogging ] [-backupLocation directory][-sysauxTablespace -datafileName name -datafileSize size

-datafileSizeNext size -datafileSizeMax size][-postUpgradeScripts script [, script ] ... ] [-initParam parameter=value [, parameter=value ] ... ] [-disableArchiveLogMode] [-recompile_invalid_objects true | false][-degree_of_parallelism cpu_number] [-emConfiguration {CENTRAL|LOCAL|ALL|NOBACKUP|NOEMAIL|NONE}

-dbsnmpPassword password -sysmanPassword password -asmPassword password -hostUserName hostname-hostUserPassword password -backupSchedule hh:mm [-smtpServer server_name -emailAddress address][-centralAgent location] [-localRacSid SID]]

[-recoveryAreaDestination directory][-h|-help]


• Example:

dbua -silent -sid dwh-oracleHome /opt/oracle/product/RDBMS10g-diagnosticDest /opt/oracle/diag-sysDBAUserName sys-sysDBAPassword manager

-recompile_invalid_objects true-degree_of_parallelism 4

-emConfiguration LOCAL-dbsnmpPassword manager-sysmanPassword manager

Database Upgrade Assistant (GUI)• Before you start DBUA:

• Run $OH_11g/rdbms/admin/utlu111i.sql in your current environment

• Check especially the components status in DBA_REGISTRY• To remove (or reinstall) components manually:

Note:472937.1 Information On Installed Database ComponentsNote:753041.1 How to diagnose Components with NON VALID status


From 11g on alwaysSECURE

From 11g on alwaysSECURE

Preparation

Agenda

Upgrade


Best Practice

News and Task List

Performance Testing




Migration

Command Line Upgrade• Typical scenario: e.g. changing to a new server

Server(NEW)Server(OLD)

1) Install Oracle11g software2) Copy utlu111i.sql

6) Apply suggested changes7) Start the upgrade process

3) Run utlu111i.sql4) Shutdown database5) Copy all relevant files

(dbf, ctl, log, spfile, orapw)

Command Line Upgrade• Step-by-step:

1. Complete online backup of the database2. Install 11g Oracle software and apply patch set 11.1.0.73. Analyze the DB using utlu111i.sql and follow all

requirements given by the script 4. Create a new 11g listener with NETCA5. Switch to the new environment, startup the DB (startup

upgrade) and create the SYSAUX tablespace (if source db is an Oracle 9i db)

6. Run upgrade script catupgrd.sql7. Recompile with utlrp.sql - compare with utluiobj.sql8. Run catuppst.sql if you are upgrading from ≥10g9. Check the post upgrade status: utlu111s.sql

Command Line Upgrade• Upgrade information script: utlu111i.sql

• Run in the environment of the source database• Checks all init parameters and displays warnings for obsolete and

deprecated parameters• Checks

• Components• Tablespace SYSAUX• National Characterset• Timezone file version check• Cluster check

Info

Command Line Upgrade• utlu111i.sql: DB info and time zone check? (see also PREPARATION)

• DST (Daylight Savings Time) patches for Version V4 have to beapplied as a requirement for the upgrade to Oracle Database 11g• REGISTRY$DATABASE ==> TZ_VERSION=4

Command Line Upgrade•utlu111i.sql: Tablespaces adequate size?

Command Line Upgrade•utlu111i.sql: Init parameter changes?

Command Line Upgrade• utlu111i.sql: Components and options?

• Annotation:You'll have to install all options installed for the release you are upgrading from – otherwise some components can’t be upgraded

• To remove (or reinstall) components manually:Note:472937.1 Information On Installed Database ComponentsNote:753041.1 How to diagnose Components with NON VALID status

Command Line Upgrade - 10gR2

•utlu102i.sql: Typical pitfalls

• Ideally includethe Companion-CD's"Oracle Database Products"in all installations:

Command Line Upgrade

• Create Dictionary statistics• Shutdown the database (IMMEDIATE/NORMAL) • Adjust init parameters:

• COMPATIBLE ≥ 10.1.0• SGA_TARGET ≥ 340MB (32-bit) ... ≥ 640MB (64-bit)• PGA_AGGREGATE_TARGET ≥ 25MB• LOG_ARCHIVE_FORMAT must contain %s, %t and %r

• Move init.ora/SPFILE and PWDsid.ora to their new location• Create a new 11g-Listener (use the NETCA)

• Change environment to point to the new $ORACLE_HOME

Command Line Upgrade• Windows only:

• Delete the old Service:> oradim -DELETE -SID ORCL

• Create a new Service:> oradim -NEW-SID ORCL-SYSPWD passwrd-STARTMODE a-PFILE initfile

ORADIM creates a logfile in %ORACLE_HOME%\database


SQL> STARTUP UPGRADE;SQL> STARTUP UPGRADE;

ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY;Autotune of undo retention is turned off. ALTER SYSTEM SET _undo_autotune=FALSE SCOPE=MEMORY;ALTER SYSTEM SET undo_retention=900 SCOPE=MEMORY;ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY;ALTER SYSTEM SET enable_ddl_logging=FALSE SCOPE=MEMORY;Resource Manager disabled during database migration: plan '' not setALTER SYSTEM SET resource_manager_plan='' SCOPE=MEMORY;Resource Manager disabled during database migration

Taken from an example alert.log

• Supresses unnecessary error messages likeORA-00942: table or view does not exist -thus logfiles will be easier to read and check


• Create tablespace SYSAUX (only if source is a 9i db) :

SQL> CREATE TABLESPACE sysauxDATAFILE 'file' SIZE 500MEXTENT MANAGEMENT LOCALSEGMENT SPACE MANAGEMENT AUTOONLINE;

SQL> CREATE TABLESPACE sysauxDATAFILE 'file' SIZE 500MEXTENT MANAGEMENT LOCALSEGMENT SPACE MANAGEMENT AUTOONLINE;

Command Line Upgrade• One upgrade scripts for all releases and all components:

• Useful:

SQL> @catupgrd.sqlSQL> @catupgrd.sql

SQL> SPOOL c:\temp\upgrade.logSQL> SPOOL c:\temp\upgrade.log

Command Line Upgrade• Recompilation:

• utlrp.sql• Calls utlprp.sql with CPU_COUNT-1 • Determines automatically if serial or parallel recompilation

with multiple threads can be done• Recompiles all INVALID objects • Utilizes package utl_recomp

• Re-enables functional indexes automatically• utlprp.sql can be called directly like:

• SQL> @utlprp 7

• This can be useful to minimize CPU usage

• Progress during recompilation :


1. Query returning the number of invalid objects remaining.This number should decrease with time.SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

2. Query returning the number of objects compiled so far.This number should increase with time.SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;

3. Query showing jobs created by UTL_RECOMP.SELECT job_name FROM dba_scheduler_jobsWHERE job_name like 'UTL_RECOMP_SLAVE_%';

4. Query showing UTL_RECOMP jobs that are running.SELECT job_name FROM dba_scheduler_running_jobsWHERE job_name like 'UTL_RECOMP_SLAVE_%';

1. Query returning the number of invalid objects remaining.This number should decrease with time.SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

2. Query returning the number of objects compiled so far.This number should increase with time.SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;

3. Query showing jobs created by UTL_RECOMP.SELECT job_name FROM dba_scheduler_jobsWHERE job_name like 'UTL_RECOMP_SLAVE_%';

4. Query showing UTL_RECOMP jobs that are running.SELECT job_name FROM dba_scheduler_running_jobsWHERE job_name like 'UTL_RECOMP_SLAVE_%';

Command Line Upgrade• Post upgrade script: catuppst.sql

• Only necessary when upgrading from ≥10.1• Located in ?/rdbms/admin• Database is not in upgrade mode anymore• Can be run concurrently with utlrp.sql

• Upgrade Automatic Workload repository (AWR) baseline information• Upgrade ADDM task metadata• Update Oracle Label security (OLS) policies

• Post upgrade script: utlu111s.sql• Run against new database in 11g environment• Checks the upgrade results according to DBA_REGISTRY• Displays duration of the upgrade per component and in total

Command Line UpgradeStatus

Command Line Upgrade• Post upgrade script: utlu111s.sql

SQL> @?/rdbms/admin/utlu111s.sql

Oracle Database 11.1 Post-Upgrade Status Tool 05-06-2009 11:23:33

Component Status Version HH:MM:SS Oracle Server VALID 11.1.0.7.0 00:16:17JServer JAVA Virtual Machine VALID 11.1.0.7.0 00:05:19 Oracle Workspace Manager VALID 11.1.0.7.0 00:01:01 Oracle Enterprise Manager VALID 11.1.0.7.0 00:10:13 Oracle XDK VALID 11.1.0.7.0 00:00:48 Oracle Text VALID 11.1.0.7.0 00:00:58 Oracle XML Database VALID 11.1.0.7.0 00:04:09 Oracle Database Java Packages VALID 11.1.0.7.0 00:00:33 Oracle Multimedia VALID 11.1.0.7.0 00:07:43 Oracle Expression Filter VALID 11.1.0.7.0 00:00:18 Oracle Rule Manager VALID 11.1.0.7.0 00:00:12 Gathering Statistics 00:04:53

Total Upgrade Time: 00:52:31

SQL> @?/rdbms/admin/utlu111s.sql

Oracle Database 11.1 Post-Upgrade Status Tool 05-06-2009 11:23:33

Component Status Version HH:MM:SS Oracle Server VALID 11.1.0.7.0 00:16:17JServer JAVA Virtual Machine VALID 11.1.0.7.0 00:05:19 Oracle Workspace Manager VALID 11.1.0.7.0 00:01:01 Oracle Enterprise Manager VALID 11.1.0.7.0 00:10:13 Oracle XDK VALID 11.1.0.7.0 00:00:48 Oracle Text VALID 11.1.0.7.0 00:00:58 Oracle XML Database VALID 11.1.0.7.0 00:04:09 Oracle Database Java Packages VALID 11.1.0.7.0 00:00:33 Oracle Multimedia VALID 11.1.0.7.0 00:07:43 Oracle Expression Filter VALID 11.1.0.7.0 00:00:18 Oracle Rule Manager VALID 11.1.0.7.0 00:00:12 Gathering Statistics 00:04:53

Total Upgrade Time: 00:52:31

Preparation

Agenda

Upgrade


Best Practice

News and Task List

Performance Testing




Migration

Post Upgrade - SPFILE• Always create an editable init.ora from the current SPFILE

after the upgrade has been finished• SPFILE is:

• It’s a binary file!!!• Default since Oracle 9.0• It simply exists after using DBUA or DBCA• Parameter can be changed by:

or:SQL> create pfile from spfile; Now edit init.ora with an editor:SQL> startup force pfile=initDB.oraSQL> create spfile from pfile;

SQL> create pfile from spfile; Now edit init.ora with an editor:SQL> startup force pfile=initDB.oraSQL> create spfile from pfile;

SQL> alter system set PARAMETER=VALUE scope=both;SQL> alter system set PARAMETER=VALUE scope=both;

Post Upgrade• Create system statistics during a regular workload period -

otherwise non-appropriate values for the CBO will be used:

SQL> select pname NAME, pval1 VALUE, pval2 INFOfrom aux_stats$;

NAME VALUE INFO-------------------- ---------- ------------------------------STATUS COMPLETEDDSTART 04-03-2008 12:30DSTOP 05-03-2008 12:30FLAGS 1CPUSPEEDNW 1392,39IOSEEKTIM 11,405IOTFRSPEED 25595,605...

SQL> select pname NAME, pval1 VALUE, pval2 INFOfrom aux_stats$;

NAME VALUE INFO-------------------- ---------- ------------------------------STATUS COMPLETEDDSTART 04-03-2008 12:30DSTOP 05-03-2008 12:30FLAGS 1CPUSPEEDNW 1392,39IOSEEKTIM 11,405IOTFRSPEED 25595,605...

SQL> EXECUTE dbms_stats.gather_system_stats('start');...SQL> EXECUTE dbms_stats.gather_system_stats('stop');

SQL> EXECUTE dbms_stats.gather_system_stats('start');...SQL> EXECUTE dbms_stats.gather_system_stats('stop');

Post Upgrade• Create fixed table statistics soon after the upgrade:

SQL> executedbms_stats.gather_fixed_objects_stats;

SQL> executedbms_stats.gather_fixed_objects_stats;

Preparation

Agenda

Upgrade


Best Practice

News and Task List

Performance Testing




Migration

Upgrade Alternatives• Export/Import• DataPump• Transportable Tablespaces• Streams• Logical Standby

Upgrade Alternatives• Export/Import

• All-purpose• Import possible for ≥ Oracle V5 • Export is not supported anymore in Oracle 11g• Time and resource consuming• Necessary or useful for:

• Changing the character set• Changing the OS platform before Oracle 10gR2• Consolidating schemas• All non-supported upgrade releases (like 8.1.6)

Upgrade Alternatives• Export/Import

• Changing the character set• Changing the Database Character Set

Note: 225912.1• Use Scanner Utility CSscan before altering the DB Character set

Note: 123670.1• Unicode

• Problem can be:Umlaut Ä in WE8ISO... = 1 ByteUmlaut Ä in UTF8 = 2 Byte

• UTF8 Database Character Set ImplicationsNote: 119119.1

• WE8ISO8859P1 ==> P15: Note 257722.1• Character Set Migration - White Paper:

• http://www.oracle.com/technology/tech/globalization/pdf/mwp.pdf

Upgrade Alternatives• Hints and tricks

• Transfer dump files always in BINARY mode• Do full database export always as user SYSTEM

• GRANTs on SYS's objects have to be exported seperately• Import takes approximately 3x times as long as Export• Export always with the lowest involved database version• Import always with imp of target database

• See also: Note:286775.1

• Export performance• DIRECT=Y ... bypasses SQL-Layer, but no conversions!• Parallelize export by dividing into logical independent chunks of data

• Import performance• Increase BUFFER• INDEXES=N ... build indexes later ... INDEXFILE=...• Parameter COMMIT_WRITE=NOWAIT (10g) or COMMIT_WAIT=NOWAIT (11g) during import

Upgrade Alternatives• Data Pump

• Goal:• Much faster and more flexible than exp/imp

• Concept:• Use impdp through a database link to transport the data into a new

database without dumping the data to disk

• Pros & Cons:+ All advantages of the datapump concept+ No dumpfile will be created (so no additional IO)+ Very simple to realize- Works only with 10.1.0.2 and above

• For documentation see:Oracle® Database Utilities

• Further information and examples: Note:553337.1

IMPDP usr1/password NETWORK_LINK=db_link1 FULL=Y IMPDP usr1/password NETWORK_LINK=db_link1 FULL=Y

Upgrade Alternatives• Transportable Tablespaces

• Goal:• Minimal downtime

• Concept:• Tablespaces and metadata will be just transported from one

database to another and plugged in

• Pros&Cons:+ "Can be" extremely fast- SYSTEM+SYSAUX tablespaces won't get transported - so additional

tasks are required to get views, synonyms etc. moved into the new database

- Full backup necessary afterwards

• Cases and White Papers on OTN:• Amadeus Customer Case

http://www.oracle.com/technology/deploy/availability/pdf/AmadeusProfile_TTS.pdf

• For TTS White Papers see the MAA webpage: http://www.oracle.com/technology/deploy/availability/htdocs/maa.htm

Upgrade Alternatives• General Transportable Tablespaces feature

• Available since Oracle 8i

Target DBSource DB

imp meta data

Copy datafiles

exp meta data

Read Only

Read Write

Upgrade Alternatives

• TTS x-platform (v$transportable_platform):

FILE

COPY

FILE

COPY

Big EndianLittle Endian RMANconvert

HP Open VMSHP Tru64 UNIX

Linux IA (32-bit)Linux IA (64-bit)Linux 64-bit for AMD

Microsoft Windows IA (64-bit)Microsoft Windows 64-bit for AMDMicrosoft Windows IA (32-bit)

Solaris Operating System (x86)

Apple Mac OS

HP-UX (64-bit)HP-UX IA (64-bit)

AIX-Based Systems (64-bit)IBM zSeries Based LinuxIBM Power Based Linux

Solaris[tm] OE (32-bit)Solaris[tm] OE (64-bit)


Source DB

Import non-segmentuser data (views etc.)

10.2.0.4 11.1.0.7

Target DB

Create new

Transport

Upgrade Alternatives• Oracle Streams

• Goal:• Least downtime

• Concept:• Build up a second database, run it synchronized with Streams and

switch over to the new db at any time

• Pros&Cons:+ Operation in parallel+ Possibility of changing the platform environment- Logminer has some datatype restrictions- Effort is relatively high and source db must be at least 9.2

• For a detailed documentation see:Oracle® Streams Concepts and Administration: Appendix Dhttp://download.oracle.com/docs/cd/B28359_01/server.111/b28321/ap_strup.htm#i642623

Upgrade Alternatives• Logical Standby (Data Guard)

• Goal:• Minimal downtime

• Concept:• Upgrade will be done on the standby first• Afterwards: Switchover - standby will be now prod system• Now upgrade on ex-prod - switchover back to start layout

• Pros&Cons:+ Downtime ~2min for both switchovers- No changes of the OS platform possible- Logminer has some datatype restrictions- Some effort to setup and prod must be at least 10.1.0.3

+ But beginning with 11g:Transient Logical Standby utilizes an existing Physical Standby database for the rolling upgrade process


Logical Standby configuration

Clients Redo

10.1.0.3 10.1.0.3

1

LP

Switchover (role change),then upgrade on L (ex-P)

Redo

4

Upgrade

11.1.0.711.1.0.7

PL

"Mixed setup" for testing

Redo

3

11.1.0.710.1.0.3

P L

Upgrade node L to 11.1

Upgrade

LogARCH

10.1.0.3

2

11.1.0.7

P L


• How to:• Patches, Links and Restrictions: Note: 300479.1• Please see the documentation!!!

http://download.oracle.com/docs/cd/B28359_01/server.111/b28294/create_ls.htm#g105412http://download.oracle.com/docs/cd/B28359_01/server.111/b28294/rollup.htm#BABJIDHI

• Note:748595.1 (how to create a logical standby from a physical)

PROD PSTBYGuaranteedRestore Point Guaranteed

Restore Point

LogminerBuild

LogminerBuild LSTBY

Upgrade with DBUA Upgrade with DBUA

KEEP IDENTITY

PRODSWITCHOVER

GuaranteedRestore Point Guaranteed

Restore Point

FLASHBACKDATABASE TO:

PROD

New $OHNew $OH

SWITCHOVER

t t

LSTBY

PSTBY

PSTBY

synchronize

synchronize

synchronize

Upgrade Alternatives – Transient Standby

Upgrade viaLogshipping:

Preparation

Agenda

Upgrade


Best Practices

News and Task List

Performance Testing




Migration

CRS Upgrade• Always install into the existing CRS Home

CRS Upgrade

CRS Upgrade• Documentation:

• Oracle Clusterware Administration and Deployment Guide 11ghttp://download.oracle.com/docs/cd/B28359_01/rac.111/b28255/toc.htm

• CRS Rolling Upgrade:• Note: 338706.1: Oracle Clusterware Rolling Upgrades

Preparation

Agenda

Upgrade


Best Practices

News and Task List

Performance Testing




Migration

ASM Upgrade• Upgrade an ASM instance with DBUA:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28300/upgrade.htm#BABGEDJI

• Upgrade an ASM instance manually:• http://download.oracle.com/docs/cd/B28359_01/server.111/b28300/upgrade.htm#BABHJIFJ

• ASM Rolling Upgrade (only in clustered environments):http://download.oracle.com/docs/cd/B28359_01/server.111/b31107/asminst.htm#OSTMG02400

ASM Upgrade using DBUA

CRS - ASM - DB Compatibility

Note:337737.1

!

CRS - ASM - DB Upgrade

• Reconfigure Oracle Cluster Synchronization Services• Upgrading a single-instance ASM requires that Oracle Cluster

Synchronization Services (CSS) first be configured to run from the new Oracle Database 11g Release 1 (11.1) home. To do this you need to run the following command before upgrading ASM:• localconfig reset [destination_Oracle_home]

• Prepare the new ASM Home• Copy config files ... $OH/dbs/...• Copy password file• Adjust init.ora

Preparation

Agenda

Upgrade


Best Practices

News and Task List

Performance Testing




Migration

Platform Migration

• Data Guard Physical Standby• Goal:

• Platform migration between with a Physical Standby• Concept:

• Build a physical standby database and do a switchover

• Pros&Cons:+ Only 1 minute downtime for a complete platform migration+ Simple to setup+ For Oracle 9.2.0.7+8 and 10.2.0.2+3+4:

HP-PA 11.23 HP-IA64 + Since Oracle 11g: Win Linux

• For supported combinations see: Note:413484.1

OS change on HP: PA RISC IA64• Migration from HP PA-RISC (64bit) to HP Itanium ia64

Note:266220.11) Complete valid backup2) Trace the controlfile

• SQL> alter database backup controlfile to trace;

3) Shutdown immediate (or normal)4) Copy all datafiles5) Startup nomount6) Create a new controlfile7) Possibly rebuild the JVM

• SQL> create or replace java system;

ASM Migration

• Migration to ASM• Option 1: RMAN• Option 2: Physical standby

• Simple switchover will do the migration to ASM• Just 1 minute downtime

• See:

http://www.oracle.com/technology/deploy/availability/pdf/maa_wp_10gr2_asmmigrationwithdg.pdf

RAC Migration

• Migration from Single Instance to RAC:• Option 1: rconfig utility

http://download.oracle.com/docs/cd/B28359_01/install.111/b28264/cvrt2rac.htm#BABGGEGJ

• Modify $ORACLE_HOME/assistants/rconfig/sampleXMLs/ConvertToRAC.xml • Run $ORACLE_HOME/bin/rconfig myconvert.xml

• Option 2: Enterprise Manager Grid Controlhttp://download.oracle.com/docs/cd/B28359_01/install.111/b28264/cvrt2rac.htm#sthref466

• Option 3: Manual• Install Cluster Ready Services (CRS) software• Install Oracle RAC software (or link with RAC option)• Reconfigure database to accommodate RAC settings

• @$ORACLE_HOME/rdbms/admin/catclust.sql

• init.ora• Register instances with srvctl

• See an example:• http://www.oracle.com/technology/pub/articles/chan_sing2rac_install.html

move to oracle database 11g – the whole story · move to oracle database 11g – the whole story...

Documents