migrating to oracle 8i in the real world: a senior dba’s e-business experience roger schrag...
TRANSCRIPT
![Page 1: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/1.jpg)
Migrating to Oracle 8i in the Real World:
A Senior DBA’s E-business Experience
Roger Schrag
Database Specialists, Inc.
www.dbspecialists.com
![Page 2: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/2.jpg)
Today’s Session
• Introduction
• Migration methods
• Elements of a migration plan
• Things to watch out for
![Page 3: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/3.jpg)
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
![Page 4: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/4.jpg)
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
![Page 5: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/5.jpg)
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
![Page 6: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/6.jpg)
Migration Methods
• Export and import
• Database links
• Command-line migration utility
• Data Migration Assistant
![Page 7: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/7.jpg)
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
![Page 8: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/8.jpg)
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
![Page 9: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/9.jpg)
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
![Page 10: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/10.jpg)
The Data Migration Assistant
• Java application with graphical user interface
• Functionality is similar to command-line utility except that DBA gives up all control
![Page 11: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/11.jpg)
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
![Page 12: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/12.jpg)
Initial Fact Finding For a Migration
• 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?
Collect the information you need to make intelligent decisions. Conduct tests to get information you can’t find in the documentation.
![Page 13: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/13.jpg)
• 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
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.
![Page 14: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/14.jpg)
Choosing a Migration Method
• 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.
Consider each database individually. You do not need to use the same migration method for all of your databases.
![Page 15: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/15.jpg)
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?
![Page 16: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/16.jpg)
Testing the Migration Process
• Reduces the chance of unexpected surprises
• Reduces the chance of human error or oversight
• Reduces production downtime
Document the migration steps and validate the plan by applying it against a test environment.
![Page 17: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/17.jpg)
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?
Make sure the migration will not break your application.
![Page 18: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/18.jpg)
Contingency Plans
• Export/import and database link migration methods involve copying the database. You can fall back to the original.
How do you bail out if the migration fails or breaks your application?
![Page 19: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/19.jpg)
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
![Page 20: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/20.jpg)
Interoperability Issues
• 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.)
You’ll need different versions of Oracle to work together unless you migrate the entire enterprise at once.
![Page 21: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/21.jpg)
Timeline
• The ASAP approach• The “let’s do one database at a time to
minimize exposure” approach
• Staffing issues
• Down time opportunities
Develop a timeline for the entire project.
![Page 22: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/22.jpg)
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
![Page 23: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/23.jpg)
Migration Landmines
Real-life problems I’ve encountered when migrating Oracle 7 databases to Oracle 8i.
![Page 24: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/24.jpg)
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...
![Page 25: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/25.jpg)
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);
![Page 26: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/26.jpg)
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?
![Page 27: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/27.jpg)
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.
![Page 28: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/28.jpg)
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
![Page 29: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/29.jpg)
National Language Support Issues
• 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
Oracle 8i client-side NLS files are incompatible with Oracle 7 libraries.
![Page 30: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/30.jpg)
Query Performance
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
Oracle 8i performance improvements and new features can make queries run faster.
![Page 31: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/31.jpg)
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
![Page 32: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/32.jpg)
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
![Page 33: Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience Roger Schrag Database Specialists, Inc](https://reader034.vdocuments.us/reader034/viewer/2022042821/56649daf5503460f94a9d089/html5/thumbnails/33.jpg)
Contact Information
Roger Schrag
http://www.dbspecialists.com
Database Specialists, Inc.
388 Market Street, Suite 400
San Francisco, CA 94111
415-344-0500