move to oracle database 11g – the whole...
TRANSCRIPT
<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
Diagnostics & Tuning
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
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• 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•
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
Diagnostics & Tuning
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]
Database Upgrade Assistant (GUI)
• 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
Database Upgrade Assistant (GUI)
Database Upgrade Assistant (GUI)
Database Upgrade Assistant (GUI)
Database Upgrade Assistant (GUI)
Database Upgrade Assistant (GUI)
Database Upgrade Assistant (GUI)
Database Upgrade Assistant (GUI)
Database Upgrade Assistant (GUI)
Database Upgrade Assistant (GUI)
Database Upgrade Assistant (GUI)
Database Upgrade Assistant (GUI)
From 11g on alwaysSECURE
From 11g on alwaysSECURE
Database Upgrade Assistant (GUI)
Database Upgrade Assistant (GUI)
Database Upgrade Assistant (GUI)
Database Upgrade Assistant (GUI)
Database Upgrade Assistant (GUI)
Database Upgrade Assistant (GUI)
Database Upgrade Assistant (GUI)
Database Upgrade Assistant (GUI)
Database Upgrade Assistant (GUI)
Preparation
Agenda
Upgrade
Diagnostics & Tuning
Best Practice
News and Task List
Performance Testing
Database Upgrade AssistantCommand Line Upgrade
Post UpgradeAlternatives
CRS UpgradeASM Upgrade
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
Command Line Upgrade
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
Command Line Upgrade
• 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 :
Command Line Upgrade
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
Diagnostics & Tuning
Best Practice
News and Task List
Performance Testing
Database Upgrade AssistantCommand Line Upgrade
Post UpgradeAlternatives
CRS UpgradeASM Upgrade
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
Diagnostics & Tuning
Best Practice
News and Task List
Performance Testing
Database Upgrade AssistantCommand Line Upgrade
Post UpgradeAlternatives
CRS UpgradeASM Upgrade
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)
Upgrade Alternatives
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
Upgrade Alternatives
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
Upgrade Alternatives
• 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
Diagnostics & Tuning
Best Practices
News and Task List
Performance Testing
Database Upgrade AssistantCommand Line Upgrade
Post UpgradeAlternatives
CRS UpgradeASM Upgrade
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
Diagnostics & Tuning
Best Practices
News and Task List
Performance Testing
Database Upgrade AssistantCommand Line Upgrade
Post UpgradeAlternatives
CRS UpgradeASM Upgrade
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
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
Diagnostics & Tuning
Best Practices
News and Task List
Performance Testing
Database Upgrade AssistantCommand Line Upgrade
Post UpgradeAlternatives
CRS UpgradeASM Upgrade
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