Migrating to 11g step ordered approach l.jpg
This presentation is the property of its rightful owner.
Sponsored Links
1 / 38

Migrating to 11g – Step Ordered Approach PowerPoint PPT Presentation


  • 332 Views
  • Uploaded on
  • Presentation posted in: General

Migrating to 11g – Step Ordered Approach . April Sims OCP 8i 9i 10g Southern Utah University. Submit an Article or become a Reviewer. April Sims, Editor. Are you a Dinosaur? . Do you still do weekly cold backups? Do you stay in your office, cubicle, work area? 

Download Presentation

Migrating to 11g – Step Ordered Approach

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Migrating to 11g step ordered approach l.jpg

Migrating to 11g – Step Ordered Approach

April Sims OCP 8i 9i 10g

Southern Utah University


Slide2 l.jpg

Submit an Article or become a Reviewer

April Sims, Editor


Are you a dinosaur l.jpg

Are you a Dinosaur?

  • Do you still do weekly cold backups?

  • Do you stay in your office, cubicle, work area? 

  • Rarely go to meetings with other human beings? 

  • Do people wonder what do you do all day?

  • Did you bring any business cards to pass around?

  • Do you only “linger” on technical email discussion lists?

  • Do you have a migration to the next patchset, version, hardware replacement in progress? 


Step ordered approach l.jpg

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.


Compatible components higher version than database l.jpg

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


Oracle application server cross compatibility l.jpg

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://localhost:12507

    Oc4jMount /instance/* ajp13://localhost:12507


Recommended order of implementation l.jpg

Recommended Order of Implementation

  • Listener

  • Rman

  • Rman Catalog

  • GC database

  • Clients

  • ASM

  • Database and Optimizer


Planning for change keeping things clean l.jpg

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


Why clean things up l.jpg

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/upgrades/testing


Multiple oracle homes l.jpg

Multiple $ORACLE_HOMES

Core dumps, fatal tns errors or hanging if exp, imp, sqlplus from one OH to a database in a different OH

Different OH’s different versions or patches. Recommended by Oracle Support & other DBA’s.

Multiple listeners running in different OH’s can also cause problems as well as multiple OMS Intelligent Agents

It is normal to have several OH’s 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)…..who does this? Reality check.


Multiple orainventory s l.jpg

Multiple OraInventory’s

Contents of /etc/oraInst.loc

inst_group=oinstall

#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.


I don t need no stinking patches l.jpg

I don’t 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


Upgrades and patches l.jpg

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 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.


Psu patch set update l.jpg

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


Oracle recommended patches l.jpg

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/10g_Upgrade_Companion.htm

  • Released between quarterly PSU’s


Oracle target configurations l.jpg

Oracle Target Configurations

  • Generic

  • Real Application Clusters and CRS

  • DataGuard (and/or Streams)

  • Exadata

  • Ebusiness Suite Certification


Psus vs cpus information l.jpg

PSUs vs. CPUs Information

  • Apply PSUs from now on (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

  • Don’t revert back to CPU once on PSU

    http://blogs.oracle.com/gridautomation/


Issues with conflicts and rollbacks l.jpg

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!

Patch Plan

Auto checks prerequisites

Checks Conflicts before downloading

Request Merge before downloading

Check Recommendations

Deployment Plan Available

Issues with Conflicts and Rollbacks

With Uploaded Configs

Without Uploaded Configs


Net services listener pre spin l.jpg

Net Services – Listener Pre-Spin

  • Give each database a different listener port, use naming convention

  • I don’t 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


Listener ora l.jpg

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_TEST=ON

LOG_FILE_LISTENER_TEST=listener_test.log


Rotate listener log l.jpg

Rotate Listener Log

export dat="`date '+%y%m%d'`"

lsnrctl << 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 >> listener_test.${dat}


Clients l.jpg

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


Slide23 l.jpg

RMAN

  • Migrating the catalog database

  • Multiple schemas- One for each release and/or database

  • Upgrading the rman catalog

  • Differences in 11g


Rman duplicate upgrading l.jpg

RMAN Duplicate – Upgrading

  • RMAN is configured so that a higher release is able to restore a lower release

    SQL>  alter database open resetlogs upgrade;

    SQL>  alter database open resetlogs downgrade;

    Then run CATUPGRD.SQL or CATDWGRD.SQL

  • Can’t use duplicate command

  • Different OS levels

  • Switch between word sizes (32 vs 64)

  • Use Transportable Tablespace Conversion between different OS’s


Grid control l.jpg

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.


Asm crs rdbms l.jpg

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


11g diagnosability framework l.jpg

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


Surprises l.jpg

Surprises

  • SYSTEM password expires with default profile – 11gR1

  • Case sensitivity issue between primary and physical standby – new security feature, 11gR1

  • MOS NoteID:579523.1 dangling (orphaned) rows in dictionary table SYS.KOTTD$ - affects all upgrades


Characterset selection utf8 l.jpg

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


Database upgrade methods l.jpg

Database Upgrade Methods

  • EXP/IMP or DataPump -

  • Transportable Tablespaces - same or different node

  • DBUA

  • Manual Upgrade

  • Transient Logical Standby

  • Physical Standby

  • Snapshot Standby


Upgrade testing environment l.jpg

Upgrade Testing Environment

  • Data Guard copy of production

  • Break DG Apply process to standby

  • Enable flashback, guaranteed restore point

  • Upgrade to 11g for testing….TEST….TEST

  • Downgrade to 10g

  • ???? Exact steps will post to blog.

  • Restore to guaranteed restore point…repeat

  • Data Guard site is also fallback position during go-live, break connection leave at 10g.


Optimizer upgrade l.jpg

SQL Plan Management=Stored Outlines in 10g

Bulk Load

SQL Tuning Set (STS) – Top 50, 100, etc.

Stored Outlines

Cursor Cache

Use a staging table

Optimizer Upgrade

http://optimizermagic.blogspot.com


Preupgrade optimizer l.jpg

PreUpgrade Optimizer

  • STS – Top 50…etc.

  • Source database – instance-wide stats at peak load, AWR - 7 days

  • Statspack – Level 7 – generates plans

  • OS stats - CPU, memory and IO (such as sar, vmstat, iostat)


Order of sql plan migration l.jpg

In the target 11g database……

OPTIMIZER_USE_SQL_PLAN_BASELINES=TRUE

OPTIMIZER_FEATURES_ENABLE=11g

Turn off Statistics Gathering until stable – 1 week

DBMS_STATS.SET_GLOBAL_PREFS(‘PENDING’,’TRUE’);

TEST…TEST…TEST, Correct Regression

Turn off Pending Statistics

Gather 11g Statistics – SPM in effect

Order of SQL Plan Migration


Backpedal to 10g stats l.jpg

Backpedal to 10g Stats

In the target 11g database……

OPTIMIZER_USE_SQL_PLAN_BASELINES=TRUE

OPTIMIZER_FEATURES_ENABLE=10g

Turn off Statistics Gathering

Re-import 10g Stats from Bulk Load

DBMS_STATS.SET_GLOBAL_PREFS(‘PENDING’,’TRUE’);

TEST…TEST…TEST, Correct Regression

Turn off Pending Statistics

Gather 11g Statistics – SPM in effect


Migration step ordered approach l.jpg

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


Slide38 l.jpg

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

@aprilcsims

BLOG http://aprilcsims.wordpress.com

38


  • Login