migrating to oracle 8i in the real world a senior dba s e business experience l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience PowerPoint Presentation
Download Presentation
Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience

Loading in 2 Seconds...

play fullscreen
1 / 33

Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience - PowerPoint PPT Presentation


  • 197 Views
  • Uploaded on

Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience. Roger Schrag Database Specialists, Inc. www.dbspecialists.com. Today’s Session. Introduction Migration methods Elements of a migration plan Things to watch out for. White Paper.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience' - omer


Download Now 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 oracle 8i in the real world a senior dba s e business experience
Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience

Roger Schrag

Database Specialists, Inc.

www.dbspecialists.com

today s session
Today’s Session
  • Introduction
  • Migration methods
  • Elements of a migration plan
  • Things to watch out for
white paper
White Paper
  • Covers most material in this presentation
  • Includes detailed step-by-step instructions for migrating to Oracle 8i using various methods
  • Available at: www.dbspecialists.com/4dbas/present.html
my background
My Background
  • 11 years experience with Oracle technology
  • Application developer at Oracle Corp.
  • Oracle DBA at Fortune 500s and dot-coms
  • Manage team of internet DBAs
  • Completed an enterprise-wide migration from Oracle 7.3.4 to 8.1.6 at a high-transaction internet company
upgrade vs migration
Upgrade vs. Migration
  • Upgrade:
    • Changes the minor release number
    • Example: 8.0.5 to 8.1.5
    • Usually modest change to data dictionary
    • Reversible
  • Migration:
    • Changes the major release number
    • Example: 7.3.4 to 8.1.6
    • Changes control file and data file headers
    • Cannot be reversed without restoring a backup
migration methods
Migration Methods
  • Export and import
  • Database links
  • Command-line migration utility
  • Data Migration Assistant
using export and import to migrate a database
Using Export and Import to Migrate a Database
  • Defragment and reorganize data
  • Change database name, server, platform, block size, character set
  • Exclude tables no longer needed
  • Easy to fall back to old database if necessary
  • Must create new database manually
  • Time required proportional to data volume
using database links to migrate a database
Using Database Links to Migrate a Database
  • Defragment and reorganize data
  • Change database name, server, platform, block size, character set
  • Exclude tables and specific rows no longer needed
  • Easy to fall back to old database if necessary
  • Must create new database and schema objects manually
  • Time required is proportional to data volume
the command line migration utility mig
The Command-line Migration Utility “mig”
  • Migrates the entire database in place
  • Creates new control files and updates data file headers to change them to Oracle 8i format
  • Must run catalog scripts to recreate dictionary views and PL/SQL built-ins
  • Time required is independent of data volume
  • Gives the DBA supreme control over migration process
the data migration assistant
The Data Migration Assistant
  • Java application with graphical user interface
  • Functionality is similar to command-line utility except that DBA gives up all control
topics to consider in an enterprise wide migration strategy
Topics to Consider in an Enterprise-wide Migration Strategy
  • Initial fact finding
  • Accomplishing other good things while you migrate
  • Choosing the migration method for each database
  • Testing the migration process
  • Testing the application on Oracle 8i
  • Contingency plans and bailout procedures
  • Mid-migration interoperability issues
  • The overall timeline
initial fact finding for a migration
Initial Fact Finding For a Migration

Collect the information you need to make intelligent decisions. Conduct tests to get information you can’t find in the documentation.

  • How long will the various methods take to migrate your databases?
  • How much extra disk storage will you need during the migration and do you have that much available?
  • Which migration methods seem stable in your environment?
other things to accomplish during a migration
Other Things to Accomplish During a Migration

You may wish to use the migration effort as an opportunity to perform database maintenance or server upgrades.

  • Defragment free space
  • Relocate segments to new or more optimal tablespaces
  • Change database name, character set or block size
  • Move database to a new server (possibly on a different platform)
  • Purge unneeded data
choosing a migration method
Choosing a Migration Method

Consider each database individually. You do not need to use the same migration method for all of your databases.

  • Copying data via database links requires the most effort, but lets you subset your data in any way
  • Export and import require modest effort, and still let you defragment data, change block size, etc.
choosing a migration method continued
Choosing a Migration Method(continued)
  • For large databases export/import and database links are not practical
  • When choosing between the command-line utility and Data Migration Assistant, consider:
    • How much control do you like to have?
    • How much do you trust Data Migration Assistant?
    • How stable is Data Migration Assistant in your environment?
testing the migration process
Reduces the chance of unexpected surprises

Reduces the chance of human error or oversight

Reduces production downtime

Testing the Migration Process

Document the migration steps and validate the plan by applying it against a test environment.

testing the application
Does your application rely on Oracle 7 loopholes that might be plugged in Oracle 8i?

Does your application rely on functionality that has changed between Oracle 7 and Oracle 8i?

Do you use third-party tools not certified for use with Oracle 8i?

Does your application stumble upon an Oracle 8i bug?

Testing the Application

Make sure the migration will not break your application.

contingency plans
Contingency Plans

How do you bail out if the migration fails or breaks your application?

  • Export/import and database link migration methods involve copying the database. You can fall back to the original.
contingency plans continued
Contingency Plans(continued)
  • Command-line utility and Data Migration Assistant modify your database in an irreversible way
    • Take a hot backup before you start and keep it online if you have enough storage
    • Consider offlining all tablespaces except system, temp, and rollback before migration
    • Take a cold backup of the online tablespaces immediately before migration
interoperability issues
Interoperability Issues

You’ll need different versions of Oracle to work together unless you migrate the entire enterprise at once.

  • Net8 or SQL*Net connectivity from all client versions to all server versions
  • Database link connectivity
  • Application access
  • Third-party tools
  • Database management tools (startup scripts, backup scripts, etc.)
timeline
Timeline

Develop a timeline for the entire project.

  • The ASAP approach
  • The “let’s do one database at a time to minimize exposure” approach
  • Staffing issues
  • Down time opportunities
parallel server special considerations
Parallel Server Special Considerations
  • You cannot use the Data Migration Assistant in OPS environments
  • You must migrate all instances in an OPS environment at the same time
migration landmines
Migration Landmines

Real-life problems I’ve encountered when migrating Oracle 7 databases to Oracle 8i.

newsflash oracle 8i has bugs
Newsflash: Oracle 8i has bugs!

Your worst nightmare:

A simple INSERT statement that works fine on Oracle 7 tips off a memory leak on Oracle 8i and crashes your Oracle instance...

slide25

Suppose this seemingly innocent statement ran on your database several times every second. Suppose that every time it ran, it allocated a few more bytes from the shared pool in the SGA...

INSERT INTO accounts accounts

(id, email, referral_id, first_name, last_name,

company, address, city, state, zip, country, phone,

notes, join_time, last_updated, last_login)

VALUES

(accounts_s.NEXTVAL, LOWER (p_email), p_referral_id,

p_first_name, p_last_name, p_company, p_address,

p_city, p_state, p_zip, p_country, p_phone,

p_notes, SYSDATE, SYSDATE, SYSDATE);

slide26

Here’s what Oracle Support had to say after six days of evaluating bstat/estat reports and trace files:

  • There is a logged issue with SQL_TRACE being on at system level causing performance degradation at 815.
  • Have you tried flushing the shared pool at regular intervals?
  • Do you need an index on this table with the insert action?
behavioral changes between oracle 7 and oracle 8i
Behavioral Changes between Oracle 7 and Oracle 8i
  • Stricter date format string interpretation
  • Stricter rules on using dates in check constraints

This was much more of a problem between Oracle V6 and Oracle 7, but it is still something to watch for nonetheless.

interoperability issues28
Interoperability Issues
  • 8.1.6 Net8 listener fails to connect a client to an 8.1.5 database on Solaris
  • Oracle 8i dbstart script on Solaris fails to start Oracle 7.3 databases
national language support issues
National Language Support Issues

Oracle 8i client-side NLS files are incompatible with Oracle 7 libraries.

  • Relink all of your executables with Oracle 8i libraries, or
  • Continue to run your executables from an Oracle 7 home, or
  • Set ORA_NLS32 to point to Oracle 7 NLS files
query performance
Query Performance

Oracle 8i performance improvements and new features can make queries run faster.

Caveat #1: Many new features require application changes before you can realize any benefit

Caveat #2: Cost-based optimizer changes can affect execution plans, sometimes for the worse

minor annoyances
Minor Annoyances
  • Call your database anything you want, as long as you like java8.us.oracle.com as a global name
  • Want detailed tracing of the archiver process? You can’t say no with Oracle 8i release 8.1.5
conclusion
Conclusion
  • Planning and testing are key to a successful Oracle 8i migration
  • Choose the best migration method for each database
  • Prepare a complete migration plan and use it
  • Be on the lookout for the unexpected
contact information
Contact Information

Roger Schrag

rschrag@dbspecialists.com

http://www.dbspecialists.com

Database Specialists, Inc.

388 Market Street, Suite 400

San Francisco, CA 94111

415-344-0500