Migrating to 11g

1
Migrating to 11g Step Ordered Approach

• April Sims OCP 8i 9i 10g
• Southern Utah University
• The Lone DBA

2
Submit an Article or become a Reviewer
April Sims, Editor
3
Step Ordered Approach

• Narrows the amount/time of outage needed
• Interim steps
• Oracle is backwards compatible generally
• What is compatible?
• Patching, upgrades and migrations Tips and
  Techniques.

4
Compatible Components Higher version than
database

• Oracle Net Services
• Clients
• RMAN binary, Virtual/Catalog and Database
• Grid Control Repository Database
• Grid Control Intelligent Agents
• ASM
• EXPORT/IMPORT and DATAPUMP will have issues

5
Oracle Application Server Cross Compatibility

• Deploy Ear files from 10.1.3.x ? 10.1.2.x
• Utilize latest OC4J enhancements without starting
  another HTTP Server.
• Using static ports Note 184826.1
• Deploy the ear file, default-web-site.html to
  find the port number, add lines to 10.1.2
  mod_oc4j.conf
• Oc4jMount /instance ajp13//localhost12507
• Oc4jMount /instance/ ajp13//localhost12507

6
Recommended Order of Implementation

• Listener
• Rman
• Rman Catalog
• GC database
• Clients
• ASM
• Database and Optimizer

7
Planning for Change Keeping things clean!

• Maximum Availability Architecture OTN
• Multiple Oracle Homes
• Multiple Operating System Accounts
• Environmental Variables SID.cnf for variables
  not in .profile which is executed using oraenv
• Scripting

8
Why Clean Things Up?

• It is enough work to install, maintain and
  upgrade multiple instances with multiple
  ORACLE_HOMES (OH).
• Less need to modify scripts after an upgrade
• Less errors and/or problems when the environment
  becomes cloudy with more than one OH.
• Less core dumps.
• Less off-hours work due to installs/patches/upgrad
  es/testing

9
Multiple ORACLE_HOMES

• Core dumps, fatal tns errors or hanging if exp,
  imp, expdp, impdp, sqlplus from one OH to a
  database in a different OH (on the same node)
• Different OHs different versions or patches.
  Recommended by Oracle Support.
• Multiple listeners running in different OHs can
  also cause problems as well as multiple OMS
  Intelligent Agents
• It is normal to have several OHs at any point in
  time. Now what is the best way to do this safely?
• It can be done by the same oracle unix account
  or by using two different oracle unix accounts
  (multiple is safer) REALITY CHECK!

10
Multiple OraInventorys

• Contents of /etc/oraInst.loc
• inst_groupoinstall
• inventory_loc/u01/10.2/oraInventory
• inventory_loc/u01/11.1/oraInventory
• This is for those who survived an oraInventory
  corruption and lived to tell about it. Comment
  or uncomment as needed for each install. Not
  recommended by Oracle.

11
I dont need no stinking patches

• Patch Set Number of bugs
• 8.1.7.4 1757
• 9.0.1.5 1000
• 9.2.0.7 2000
• 10.1.0.5 2500
• 10.2.0.2 1173
• 10.2.0.3 2007
• 10.2.0.4 4326

12
Upgrades and Patches

• Have at least 2 ORACLE_HOMES one for production,
  one for testing patches ALWAYS!!!!
• Find the most stable, what is the terminal
  release? Put in the latest CPU or PSU patch.
• Bad version that is x.0.0.0 (unpatched) because
  it is rare that you can truly simulate production
  load.Bugs come out under pressure.
• Cancel out of the install when it starts to
  configure Net Manager and the Intelligent Agent
  (configure later).
• Switch to new listener during off peak hours.

13
Tactics in applying Patches

• How often is the OS patched?

• Probably more often than Oracle!!!!

Check out the new Patch Plan functionality in My
Oracle Supportand integration with Grid Control.
14
Oracle Target Configurations

• Generic
• Real Application Clusters and CRS
• DataGuard (and/or Streams)
• Exadata
• Ebusiness Suite Certification

15
Oracle Recommended Patches

• Common issues in targeted configurations.
• Stabilize production environments.
• Save time and cost with known issues.
• Tested as a single combined unit, reducing risk.
• Easier to identify applicable patches.
• https//metalink2.oracle.com/metalink/plsql/docs/1
  0g_Upgrade_Companion.htm

16
PSU Patch Set Update

• 1st Digit - Major release number
• 2nd Digit - Maintenance release
• 3rd Digit - Application server release
• 4th Digit - Release component specific
• 5th Digit - Platform specific release
• First PSU 10.2.0.4.1
• Second PSU 10.2.0.4.2
• MetaLink Doc ID 850471.1
• Includes latest CPU at release time

17
PSUs vs. CPUs Information

• PSUs available since DB Version 10.2.0.4
• Both CPU PSU Released Quarterly
• PSUs include CPUs
• PSUs are a Superset of CPUs
• Might need merge patch if migrating from a CPU
• Dont revert back to CPU once on PSU
• http//blogs.oracle.com/gridautomation/

18
Issues with Conflicts and Rollbacks
My Oracle Support
Without My Oracle Support

• Patch Plan
• Auto checks prerequisites
• Checks Conflicts before downloading
• Request Merge before downloading
• Check Recommendations
• Deployment Plan Available

• Download patch then
• Install.if conflict then
• Check if any key patches rolled back
• Merge request..then
• Wait.might be too late for this CPU, narrow
  window.maybe next quarter!

19
Net Services Listener Pre-Spin

• Give each database a different listener port, use
  naming convention
• I dont use port 1521 for any listener (because
  of autoregister feature)
• Put ORACLE_HOME of database if using Listener in
  a different ORACLE_HOME
• Define local_listener in init.ora
• TCP, BEQ

20
Listener.ora

• LISTENER_TEST (ADDRESS (PROTOCOL TCP)(HOST
  FQ.NODENAME)(PORT 1540))
• SID_LIST_LISTENER_TEST (SID_LIST
• (SID_DESC (SID_NAME TEST)
• (ORACLE_HOME /u01/app/oracle/product/10gR2) )
  )
• ADMIN_RESTRICTIONS_LISTENER_TESTON
• LOG_FILE_LISTENER_TESTlistener_test.log

21
Rotate Listener Log

• export dat"date 'ymd'"
• lsnrctl ltlt EOF
• set current_listener listener_test
• set log_file listener_tmp.log
• exit
• EOF
• mv listener_test.log listener_test.dat
• cat listener_temp.log gtgt listener_test.dat

22
Clients

• ODBC,SQLPLUS, Instant Client
• Database Links
• 32/bit to 64/bit
• JDBC, JDK
• Precompilers
• Features Availability
• Exp/Imp or Datapump
• BEQUEATH not supported b/t diff releases
• Highly recommended, usually minor issues
• Needs testing, maybe application specific
• Definitely has issues

23
RMAN

• Migrating the catalog database
• Multiple schemas- One for each release and/or
  database
• Upgrading the rman catalog
• Differences in 11g

24
RMAN Duplicate Upgrading

• RMAN is configured so that a higher release is
  able to restore a lower release
• SQLgt  alter database open resetlogs upgrade
• SQLgt  alter database open resetlogs downgrade
• Then run CATUPGRD.SQL or CATDWGRD.SQL
• Cant use duplicate command
• Different OS levels
• Switch between word sizes (32 vs 64)
• Use Transportable Tablespace Conversion between
  different OSs

25
Grid Control

• Migrating Repository Database to 11g if
  uncoupled install Use GC 10.2.0.5
• Co-locate RMAN Catalog (s) - much easier to move,
  migrate or drag along with GC.

26
ASM/CRS/RDBMS

• CRS must have its own ORACLE_HOME
• ASM can be separate as of 10gR2
• Push to move ASM, CRS and RDBMS to three
  different operating accounts
• One-off patches - separate ASM and RDBMS
• Rolling Upgrades

27
(No Transcript)
28
11g Diagnosability Framework

• Environmental Variable ORACLE_BASE
• adrci command-line utility
• Scripting changes
• Rotating, purging logs, trace files, core dumps
  and incidents
• Can disable ADR, especially for troubleshooting

29
11gR1 Surprises

• SYSTEM password expires with default profile
• Case sensitivity issue between primary and
  physical standby new security feature

30
Characterset Selection UTF8

• Any implications for migration? options are
  usually some sort of csalter and/or exp/imp,
  datapump.
• Transitional Steps convert to a superset to
  remove some types of lossy data. See blog also
  NLS by Gary Gordhammer articles in IOUG SELECT
• Grid Control and RMAN catalog can be UTF8

31
Database Upgrade Methods

• EXP/IMP or DataPump -
• Transportable Tablespaces - same or different
  node
• DBUA
• Manual Upgrade
• Transient Logical Standby
• Physical Standby
• Snapshot Standby

32
Optimizer Upgrade

• SQL Tuning Set (STS)
• Stored Outlines
• Cursor Cache
• Use a staging table

• SQL Plan Management
• Bulk Load

http//optimizermagic.blogspot.com
33
PreUpgrade Optimizer

• Source database instance-wide stats at peak
  load, 7 days
• Statspack Level 7
• AWR Diagnostics, Tuning Pack
• OS stats - CPU, memory and IO (such as sar,
  vmstat, iostat)

34
Migration Step Ordered Approach

• Narrows the amount/time of outage needed
• Interim steps
• Oracle is backwards compatible generally
• What is compatible?
• Patching, upgrades and migrations

35
Questions?
Leave Business Card put question or interest
on back. http//www.twitter.com/aprilcsims
http//www.twibes.com/novicedba http//www.twibes
.com/lone_dba Twitter hash tags IOUG_SELECT
NOVICEDBA LONE_DBA _at_aprilcsims BLOG
http//aprilcsims.wordpress.com
35