1 / 33

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. Introduction Migration methods Elements of a migration plan Things to watch out for. White Paper.

omer
Download Presentation

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

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc. www.dbspecialists.com

  2. Today’s Session • Introduction • Migration methods • Elements of a migration plan • Things to watch out for

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

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

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

  6. Migration Methods • Export and import • Database links • Command-line migration utility • Data Migration Assistant

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

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

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

  10. The Data Migration Assistant • Java application with graphical user interface • Functionality is similar to command-line utility except that DBA gives up all control

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  25. 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);

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

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

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

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

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

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

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

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

More Related