External 11g References
25-Jan-2009
Upgrade Paths
UPGRADE
UPGRADE
Export/Import
Export/Import
Y
Y OS Change?
OS Change? N
N
CTAS, COPY
CTAS, COPY
N
N Downtime >45min?
Downtime >45min?
SQL Apply
SQL Apply
Y
Y
Oracle Streams
Oracle Streams
DBUA
DBUA
Transportable Tablespaces
Transportable Tablespaces
OR
ORA
CLI
CLI
ACL
CLEE rrec
ecom SQL> @catupgrd
Transportable Database
Transportable Database omm
men
ende
dedd
Preparation
• Get INVALID Objects:
SQL> SELECT UNIQUE object_name, object_type, owner
SQL> SELECT UNIQUE object_name, object_type, owner
• FROM dba_objects WHERE status='INVALID';
FROM dba_objects WHERE status='INVALID';
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!!!
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
• Create dictionary statistics prior to the upgrade - otherwise
it will take significantly longer
SQL> EXECUTE dbms_stats.gather_schema_stats
SQL> EXECUTE dbms_stats.gather_schema_stats
('SYS',
('SYS',
options
options => 'GATHER',
=> 'GATHER',
estimate_percent
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
=> DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
=> 'FOR ALL COLUMNS SIZE AUTO',
cascade
cascade => TRUE);
=> TRUE);
• Detailed scripts can be found here:
Oracle® Database Upgrade Guide Appendix B
• Or in / :
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 ]
•
$ dvca -action disable ...
$ dvca -action disable ...
• Windows:
• Rename oradv10.dll in ORACLE_HOME\bin
• Upon restart:
$ dvca -action disable ...
$ dvca -action disable ...
• After upgrading relink with dv_on or rename the DLL and enable
Database Vault again:
$ dvca -action enable ...
$ dvca -action enable ...
Preparation
• Drop SYS.PLAN_TABLE$ and SYS.PLAN_TABLE
• See Note: 605317.1 and Note: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
•
Example: Linux
• Seed database: ≥ 1,6 GB
• Seed databases are always prebuilt EE databases with all options!!!
• RAM: ≥ 1GB
• Swapspace
• RAM between 513Mb and 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
• 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:
Important:
„CUSTOM“ ALWAYS installs
„CUSTOM“ ALWAYS installs
Enterprise Edition
Enterprise Edition
Installation
• Custom installation:
• 10gR2 doc installation guide
• 11gR1 doc installation guide
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 ...
Installation
• Overview on differences of database editions and available
options by edition
• Feature differences:
Note.465465.1
Differences Between Enterprise, Standard and Personal Editions on Oracle 10.2
• Oracle® Database Licensing Information
11g Release 1 (11.1)
Part Number B28287-01
http://download.oracle.com/docs/cd/B28359_01/license.111/b28287/editions.htm