DATABASE ADMINISTRATION

Migrating to Oracle8: QuickStart on New Features

By Erik Peterson

Use the Enterprise QuickStart Package to find out how new features in Oracle8 can benefit your enterprise. Part 2 in a two-part series.

Once you have completed your migration to Oracle8, you can proceed to dig in and take advantage of some of Oracle8's new features. This article focuses on some of the features from which you can gain the greatest benefits with the least change to your implementation, based on the second package of Oracle8 Enterprise QuickStart, the Oracle8 New Features Strategy Package.

What It Is

Oracle Consulting's Oracle8 Enterprise QuickStart is a fixed-price consulting package that provides a thorough upgrade strategy to help customers quickly exploit the new enterprise-level functionality of Oracle8. It is made up of two components: the Oracle8 Database Upgrade Strategy Package and the Oracle8 New Features Strategy Package.

The Oracle8 Database Upgrade Strategy Package is a structured process for upgrading an Oracle7 database to Oracle8. It includes Oracle8 installation and configuration and development of a thorough migration strategy. Larry Miller and I covered this package in detail in part 1 of this article, Migrating to Oracle8: A QuickStart Primer in the January/February issue of Oracle Magazine.

The Oracle8 New Features Strategy Package is designed to help you analyze your client applications and database to determine how your system can best utilize Oracle8's new features. You can use this package to help determine the full impact of Oracle8 on your environment and to identify how your system can benefit from new functionality such as partitioning, index-organized tables, parallel data manipulation language (DML), and server-managed recovery. You can additionally use the package to incorporate the enhanced functionality of Oracle8 in the development of new backup-and-recovery strategies. As part of the package, Oracle consultants utilize tools that assist in identifying potential candidates for partitioning and index-organized tables.

Knowledge Collection ANd Consolidation

The first stage in setting up a strategy for a new-features implementation is to make sure you have a proper understanding of the features themselves, including identifying the features and knowing why they were developed. It is also beneficial to get information from someone who has actually had the experience of applying these new features in the field.

1. Learn about Oracle8's new features. There are many sources of information on Oracle8 and its new features, including Oracle Magazine articles, Oracle Education classes, Oracle Press books, various new-features seminars, and Oracle8 documentation.
        Oracle also has more than 40 technical and business white papers available free either on the Oracle Web site (www.oracle.com) or on a CD-ROM. These in-depth articles are written by developers, product managers, and consultants with Oracle8 field experience. They provide an excellent complement to the official product documentation. You can order the CD-ROM via the Web or check it out at www.oracle.com/st/products/. Some of the available paper topics are "Taking Advantage of Partitioning in Oracle8," "VLDB Design and Migration for Oracle8," and "Overview of New Backup and Recovery Capabilities for Oracle8."

2. Get help from someone who has implemented Oracle8. Take advantage of the knowledge and experience gained by consultants who have done Oracle8 implementations. They can help you take advantage of the generic advantages of Oracle8, make recommendations, sort out quirks, and even make specific suggestions for your organization's system.

3. Review the new features with your team. Although your team members may have taken the Oracle Education classes and read a lot of material, they probably still have unanswered questions and ideas that would benefit from group discussion. Get your team of DBAs, system architects, and application designers together to review Oracle8's features. As part of the Oracle8 Enterprise QuickStart package, an experienced Oracle consultant provides a half-day presentation on Oracle8's new scalability features, followed by a round-table discussion.

Application Analysis for New Features

Once you have developed a working knowledge of Oracle8's new features, it is time to start analyzing your own application to see how it can take advantage of them. Analyze your application by reviewing existing documentation and working with the team members experienced in the application, including DBAs, architects, and application designers.

1. Implement minor changes first.Using some of the new features may require substantial changes to the application. If you concentrate first on changes you can make with minimal modification, you may be able to achieve significant gains with low cost. The remainder of this section focuses on changes that require little application redesign.

2. Define the table-partitioning strategy.In Oracle8, table and index partitions continue to maintain the same logical schemas as nonpartitioned objects, which means that you can implement partitioning without changing the application.
        First, identify the tables that are suited for partitioning, based on scalability, availability, maintenance, or performance reasons. Tables matching any of the following criteria may be candidates for partitioning: very large tables, tables with bulk delete operations, tables with time-based keys, tables with frequent maintenance operations, and tables that are accessed for large subsets of information (all California customers, for example). As part of the Oracle8 QuickStart Toolset, Oracle consultants have a tool to help them identify good potential candidates for partitioning within your system.
        Partitioning can potentially provide many advantages:
   • Increased parallelism: You can now do parallel DML operations with one process per partition.
   • Reduced backup times: You can also separate older data from more recent data and put it into read-only tablespaces. You then need to back up this data only once after partitioning.
   • Increased availability: If a tablespace with one partition becomes unavailable, you can nevertheless access the remainder of the table.
   • Increased manageability: You can manage the table in its entirety or as individual partitions, which means that you can analyze, rebuild, or even back up every partition independently.
   • Reduced data set for queries: Irrelevant partitions will automatically be eliminated, allowing queries access only to the necessary partitions.
   • Faster elimination of old data: If partitioning by time, you can simply truncate older data instead of going through costly deletes.
   • Intrapartition parallelism: You can do queries in parallel within a partition. However, do not partition tables if an update would cause a row to migrate from one partition to another, because Oracle8 does not currently support this.

3. Define an indexing strategy. You will need to revisit your indexing strategy for the partitioned tables, because both the needs and the costs of indexes will change with the implementation of Oracle8. You will additionally need to decide whether to partition the index in the same way the table is partitioned, by a different method, or not at all.
        Select the index type, based on performance and maintenance requirements, from the following options:
   • Local prefixed partitioned index
   • Local nonprefixed partitioned index
   • Global prefixed partitioned index
   • Regular B-tree index
   • Regular bitmapped index

4. Identify the tables you can index-organize. Index-organized tables are easy to implement. They save space and reduce the number of I/Os necessary, because all table data is stored in the index structure. Currently, Oracle8 doesn't let you create additional indexes against an index-organized table. When identifying candidates, look for tables with only one index. Tables that have the most to gain from being index-organized are those where most of the data is already stored in the index (providing maximum space savings), where few changes occur to the table (this would cause index fragmentation), and cases in which the table is almost always accessed through the index. Reference tables often have these characteristics.

Database-Design Analysis for New Features

Analyze your database to determine the changes you need to make to the physical database as a result of the application analysis. This analysis will also be helpful when you examine the applicability of other Oracle8 features to enhance database performance and manageability.

1. Define the new physical layout. The strategy you defined during application analysis will require you to make changes at the physical level to enhance the key factors of performance, availability, and maintenance. The database layout needs to include tablespace design; number, size, and placement of datafiles; and physical characteristics of each partition. Keep the following factors in mind during this process:
   • Physically separate partitions for parallel scans and maintenance operations.
   • Put partitions in different tablespaces from their writable counterparts in order to make them read-only.
   • Design to allow for changes over time.
   • Because partitions are different objects, they can have different physical characteristics (size, number of extents, percentage of free and used space, and so on).

2. Define a partition-maintenance strategy. Determine your system's partition-rotation requirements. Most partitioning schemes are based on time-dependent keys, and you will have to add and drop partitions routinely. The idea is to determine the most efficient way to do rotation.
        First, determine which tables require the partition-maintenance routines. Then define the partition-rotation strategy for the tables that require partitions to be added and dropped on a routine basis. You need to answer the following questions:
   • Should you split a partition or do an add and drop? Adding is preferable but can be done only at the top of the partition list. You would need to split a partition to add a partition in the middle, to divide a partition that is too large, or when there is no upper bound on the table (use MAXVALUE for the partition less-than clause). Use drop to eliminate partitions you no longer need. Dropping or splitting partitions invalidates global indexes, but adding a partition does not.
   • How can you control tablespace design and physical placement when splitting/adding partitions? You can give each partition its own tablespace. Although this may lead to fragmentation of free space across many datafiles, it simplifies making the tablespace read-only when the partition is read-only, and it allows nonfragmented reuse of the space when the partition is dropped.
   • How should you deal with global indexes? Anytime you drop, exchange, split, or perform another similar operation on a partition that massively changes a group of RowIDs, it will leave global indexes invalid. When a global index is invalid, full table scans will occur instead. These operations should be timed to reduce the impact of having to do full table scans while index rebuilds take place.
   • Where and how should you use the parallel operations? Parallel DML can be useful when rebuilding global indexes, merging partitions, or doing deletes without invalidating global indexes. Note: Most parallel DML is limited to one process per partition.
   • Can the tablespaces where the partitions reside be made read-only? Keeping each partition on a separate tablespace allows you to make the tablespace read-only when no more changes will occur to that partition. This reduces backups (see Tip 2 in "Backup-and-Recovery Strategy," below) and lock overhead, resulting in a 5- to 10-percent performance increase over writable tablespaces.

3. Migrate to partitions. The next step is to plan how to move the application/system from nonpartitioned tables to partitioned tables. Three methods of doing this include doing a parallel CREATE TABLE AS SELECT, reloading and/or re-creating the objects, or doing an EXCHANGE PARTITION to convert Oracle7 Release 7.3 partition views to partitions.

Backup-and-Recovery Strategy

Two new Oracle8 features--partitioning and server manager recovery (SMR)--change backup-and-recovery strategies dramatically. Partitioning can split up large tables, which lets you put large portions of the table into read-only tablespaces, meaning you need to back up the data only once. SMR automates the backup-and- recovery process; automatically makes backup streams parallel; skips empty blocks during backups; and does multilevel incremental backups, which means that only those blocks that have changed since the last backup need to be backed up.

1. Review your current backup-and-recovery strategy. By checking over your current strategy, you can determine your needs and how long backups will take. You should review the current backup-and-recovery methods, system-availability requirements, and system-capacity plan to see what will need to change for the future.

2. Make as many tablespaces as possible read-only. Although Oracle8 supports incremental backups, the system still has to do a full scan of all database blocks to find changes. However, you can skip read-only tablespaces, which eliminates this extra work. Read-only tablespaces have the added advantage that you can replace them independently of the rest of the database during restore operations.

3. Define a multitiered backup strategy, using SMR. Large databases that have trouble completing their backups in the time available can take advantage of the incremental backups SMR makes possible. You can plan SMR backups in a multitiered fashion. For example, you can set a full monthly backup in conjunction with weekly and daily incremental backups. In this case there are three tiers: The monthly is level 0 or full, the weekly is level 1, and the daily is level 2. You can define incremental backups to include only changes made since the last incremental backup of the same level (exclusive) or the previous level (inclusive).

4. Make sure at least two independent restoration options exist. Use SMR's reporting capability to verify that all files have at least two (or even three) restoration options. Multiple restoration options are essential to ensure that you can restore a system even if a backup tape becomes corrupted. SMR further helps enforce this by not allowing you to put datafiles and archived logs in the same backup set. You can create three restoration paths by keeping archived logs until they have been backed up twice.

5. Ensure that you can do the restoration in an adequate time period. Keep the number of backups manageable by limiting the number of tiers used as well as making the incremental backups inclusive of all changes since the last backup of the previous level. Doing this will keep you from having to apply too many tapes during the restoration process.

6. Define a plan for testing backup and recovery. Testing is the most important and most often overlooked stage of a backup-and-recovery plan. It is essential that you define a strategy for testing restorations of backups frequently.

By performing the knowledge collection and consolidation, application analysis, database analysis, and reworking of your backup-and-recovery strategy described in this article, you can easily make large gains with Oracle8. The partitioned tables and indexes, index-organized tables, server-managed recovery, and some of the other features discussed in this article are very easy to analyze and implement. You can similarly analyze other features, but they may require more effort to implement.

Erik Peterson ([email protected]) leads the worldwide Oracle8 QuickStart virtual team and is a member of Oracle Consulting's Enterprise Scalable Solutions Center of Excellence (ESSCOE). He has been involved with Oracle8 for more than two years, helping many customers build very large, scalable, and highly available implementations on Oracle8.

Where to Find It You can find country and regional contacts for delivery of the Oracle8 Enterprise QuickStart packages at www.oracle .com/consulting/o8cont.html or by sending an e-mail message to [email protected]. For more information on table and index partitioning, check out Erik Peterson's white paper "Taking Advantage of Oracle8 Partitioning" on Oracle Magazine Interactive at www.oramag.com/archives/18/18partit.htm.

Copyright © 1994, 1995, 1996, 1997 & 1998 Oracle Corporation. All Rights Reserved.