1. Database Upgrade Guide
  2. Preparing to Upgrade Oracle Database
  3. Tasks to Prepare for Oracle Database Upgrades
  4. Develop a Test Plan for Upgrading Oracle Database

Develop a Test Plan for Upgrading Oracle Database

Review these topics to understand how to create a series of carefully designed tests to validate all stages of the upgrade process.

Oracle recommends that you perform rigorous tests of your database and applications. When you run and complete tests successfully, you help to ensure that you understand the process of upgrading the production database, so that the upgrade process is predictable and successful. Oracle strongly recommends that you perform as much testing as possible before upgrading a production database. Do not underestimate the importance of a complete and repeatable testing process.

You can choose to perform tests manually, or you can use utilities to assist your tests, such as Oracle Real Application Testing features like Database Replay or SQL Performance Analyzer. In either case, the types of tests that you perform are the same.

Your test plan must include these types of tests:

Parent topic: Tasks to Prepare for Oracle Database Upgrades

Upgrade Testing

Create, test, and validate an upgrade plan.

Upgrade testing for Oracle Database entails planning and testing the upgrade path from your current software to Oracle Database 12c, whether you use Oracle Database Upgrade Assistant (DBUA), perform a manual upgrade, or use Export/Import or other data-copying methods. Regardless of the upgrade method you choose, you must establish, test, and validate an upgrade plan.

Parent topic: Develop a Test Plan for Upgrading Oracle Database

Minimal Testing

Minimal testing can find application startup or invocation problems.

Minimal testing for Oracle Database entails moving all or part of an application from the current database to the new database and running the application without enabling any new database features. Minimal testing might not reveal problems that would appear in an actual production environment. However, minimal testing immediately reveals any application startup or invocation problems.

Parent topic: Develop a Test Plan for Upgrading Oracle Database

Functional Testing After Upgrades

Perform functional testing of the upgraded Oracle Database after the upgrade is complete.

Functional testing for Oracle Database is a set of tests in which new and existing features and functions of the system are tested after the upgrade. Functional testing includes all database, networking, and application components. The objective of functional testing is to verify that each component of the system functions as it did before upgrading and to verify that new functions are working properly.

Parent topic: Develop a Test Plan for Upgrading Oracle Database

High Availability Testing

Plan to perform High Availability testing on your upgraded system.

High Availability testing for Oracle Database ensures that the upgraded database system meets these recovery business requirements:

  • Recovery Time Objective (RTO)

  • Recovery Point Objective (RPO)

Oracle recommends the following test procedures for high availability testing:

  • Create node or instance failures during stress testing. Node or instance failures help to evaluate the Oracle RAC recovery capability.

  • Test fallback plans and procedures to ensure that you can minimize downtime on upgraded databases.

  • Check database performance and stability, and resolve performance problems. Resolving performance problems helps to ensure that the upgrade process runs within the time that you have allocated.

Parent topic: Develop a Test Plan for Upgrading Oracle Database

Integration Testing to Ensure Applications are Compatible

Integration testing for Oracle Database examines the interactions among components of the system.

Oracle recommends that you carry out the following tests as part of your integration testing:

  • Test Pro*C/C++ application clients of Oracle Database 12c instances to ensure that these applications are compatible with the upgraded database.

  • Test graphical user interfaces.

  • Test all applications that interact directly or indirectly with the database. Subtle changes in Oracle Database 12c, such as data types, data in the data dictionary (additional rows in the data dictionary, object type changes, and so on) can affect front-end applications, even if those applications are not directly connected to a new Oracle Database 12c instance.

  • Test and stress-test any Oracle Net or Oracle Net Services connections between components.

Note:

See Pro*C/C++ Programmer's Guide for more information about Pro*C/C++ applications.

See Oracle Database Net Services Reference for more information about upgrade recommendations for Oracle Net Services.

Parent topic: Develop a Test Plan for Upgrading Oracle Database

Performance Testing an Upgraded Oracle Database

Plan performance testing comparisons between your earlier release and new release Oracle Database.

Performance testing of the new Oracle Database compares the performance of various SQL statements in the new database with the performance of those same statements in the current database. Before upgrading, Analyze the performance profile of applications under the current database. Specifically, analyze and understand the calls that applications make to the database server.

  • Database Replay and Performance Testing
    Use the Database Replay feature to perform real-world testing of a database upgrade on your production workload before actually upgrading the production database.
  • SQL Performance Analyzer
    Use the SQL Performance Analyzer to forecast the impact of system changes on a SQL workload.
  • SQL Plan Management
    Review this topic to understand how to carry out SQL plan managements after upgrades to avoid performance regressions.

Parent topic: Develop a Test Plan for Upgrading Oracle Database

Database Replay and Performance Testing

Use the Database Replay feature to perform real-world testing of a database upgrade on your production workload before actually upgrading the production database.

The Database Replay feature captures the actual database workload on the production system, and replays it on the test system. Database Replay also provides analysis and reporting to highlight potential problems; for example, errors encountered, divergence in performance, and so forth. In addition, all the regular Enterprise Manager performance monitoring and reporting tools such as Automatic Database Diagnostic Monitor, Automatic Workload Repository (AWR), and Active Session History are available to address any problems.

Note:

You can change the stored procedure logic in the database. However, the stored PL/SQL procedures that implement the application logic must maintain the same interfaces as before the upgrade. If an upgrade affects the stored procedures of an application, replaying the workload may not be possible. Using Database Replay tool with the same interfaces provides you with good diagnostics to see if the new application logic in the server is performing as expected after the upgrade.

See Also:

Parent topic: Performance Testing an Upgraded Oracle Database

SQL Performance Analyzer

Use the SQL Performance Analyzer to forecast the impact of system changes on a SQL workload.

SQL Performance Analyzer enables you to evaluate the effect of an upgrade on your SQL workloads. SQL Performance Analyzer finds possible issues by identifying the SQL statements affected by the upgrade. It then measures the performance divergence of SQL workloads before the upgrade, and after the upgrade. The analysis enables you to assess the overall effect of the upgrade on SQL performance. You can then take measures to avoid any negative outcome from SQL workload changes before they can affect users.

See Also:

Oracle Database Testing Guide for further information, and examples of using the SQL Performance Analyzer to perform analysis on potential database changes

Parent topic: Performance Testing an Upgraded Oracle Database

SQL Plan Management

Review this topic to understand how to carry out SQL plan managements after upgrades to avoid performance regressions.

A database upgrade that installs a new optimizer version usually results in plan changes for a small percentage of SQL statements. Most plan changes result in no performance change or improvement. However, certain plan changes may cause performance regressions. SQL plan management prevents performance regressions resulting from sudden changes to the execution plan of a SQL statement by providing components for capturing, selecting, and evolving SQL plan information. SQL plan management is a preventative mechanism that records and evaluates the execution plans of SQL statements over time, and builds SQL plan baselines composed of a set of existing plans that are proven efficient after repeated use. SQL plan management uses the SQL plan baselines to preserve the performance of corresponding SQL statements, regardless of changes occurring in the system.

With SQL plan management, the optimizer automatically manages execution plans and ensures that only known or verified plans are used. When SQL Plan management finds a new plan for a SQL statement, it does not use this plan until the database verifies that the new plan has comparable or better performance than the current plan. If you seed SQL plan management with your current execution plans, then those plans becomes the SQL plan baseline for each statement. The optimizer uses these plans after the upgrade. If the Oracle Database 12c optimizer determines that a different plan can result in better performance, then the new plan is queued for verification and is not used until it has been confirmed to have comparable or better performance than the current plan.

There are several ways to seed or populate a SQL Management Base (SMB) with execution plans:

Bulk Load a SQL Management Base from the Cursor Cache

Bulk loading of execution plans or SQL plan baselines from the cursor cache is useful when upgrading a database from Oracle Database 11g to the latest release of Oracle Database. The cursor cache is a shared SQL area, and SQL plans that are bulk loaded are automatically accepted and added to existing or new plan histories as SQL plan baselines.

  1. In the source release of Oracle Database, use the DBMS_SPM.LOAD_PLAN_FROM_CURSOR_CACHE procedure or Oracle Enterprise Manager to load all of the execution plans in the cursor cache into the SQL Management Base.

  2. Upgrade the database.

See Also:

Oracle Database SQL Tuning Guide for more information on how to load plans from the shared SQL area using PL/SQL or Oracle Enterprise Manager

Bulk Load a SQL Management Base with a SQL Tuning Set (STS)

Bulk loading of execution plans or SQL plan baselines may be done with a SQL Tuning Set. This is useful when upgrading from Oracle Database 10g, where no SQL Management Base (SMB) exists to directly load from the cursor cache, or to load historic plans from the Automatic Workload Repository.

  1. In the source release of Oracle Database, create an STS that includes the execution plan for each of the SQL statements.

  2. Load the STS into a staging table and export the staging table into a dump file.

  3. Import the staging table from a dump file into the new release of Oracle and unload the STS.

  4. Use Oracle Enterprise Manager or DBMS_SPM.LOAD_PLANS_FROM_SQLSET to load the execution plans into the SQL Management Base.

See Also:

Oracle Database SQL Tuning Guide for the complete procedure for bulk loading execution plans or SQL plan baselines

Unpack Existing SQL Plan Baselines from a Staging Table

You can test and tune all of your critical SQL queries on an Oracle Database test environment and then move those SQL execution plans to your Oracle Database production environment. Alternatively, you can take plans for SQL queries from your pre-upgrade Oracle Database production environment and move them to your post-upgrade production environment.

  1. On the Oracle Database 12c test system, after completing all testing and tuning, use the DBMS_SPM.LOAD_PLAN_FROM_CURSOR_CACHE procedure or Enterprise Manager to load all of the execution plans in the cursor cache into the SQL Management Base.

  2. Create a staging table using the DBMS_SPM.CREATE_STGTAB_BASELINE procedure.

  3. Pack the SQL plan baselines you created in step 1 into the staging table using the DBMS_SPM.PACK_STGTAB_BASELINE function.

  4. Export the staging table into a flat file using Data Pump.

  5. Transfer this flat file to the target system.

  6. Import the staging table from the flat file using Data Pump.

  7. Unpack the SQL plan baselines from the staging table into the SQL Management Base on the target system using the DBMS_SPM.UNPACK_STGTAB_BASELINE function.

See Also:

Parent topic: Performance Testing an Upgraded Oracle Database

Volume and Load Stress Testing for Oracle Database Upgrades

Use Database Replay to perform volume and load stress testing of the entire upgraded Oracle database under high volume and loads.

Volume describes the amount of data being manipulated. Load describes the level of concurrent demand on the system. Volume and load testing can emulate how a production system behaves under various volumes and loads.

Volume and load stress testing is crucial. However, it is commonly overlooked. Oracle has found that customers often do not conduct any kind of volume or load stress testing. Instead, customers often rely on benchmarks that do not characterize business applications. Oracle recommends that you conduct benchmarks of your applications. Benchmarking can help you to uncover problems relating to functions, performance, and integration. However, they cannot replace volume and load stress testing.

Load testing involves running an application load against the new Oracle Database release. The load test ensures that applications do not encounter problems, such as new errors, or performance issues under the load conditions that you think are likely found in production. Many times, problems manifest only under certain load conditions, and are normally not seen in functional testing. The Database Replay feature is ideal for such load testing. Database Replay enables you to capture the system workload from a production environment, and replay it in identical fashion on the test system.

See Also:

Oracle Database Testing Guide for information about using Database Replay for stress testing

Parent topic: Develop a Test Plan for Upgrading Oracle Database

Test Plan Guidelines for Oracle Database Upgrade Planning

Perform planned tests on your current database and on the test database that you upgraded to the new Oracle Database release.

  • Compare the test results, noting anomalies.

  • Repeat the test upgrade as many times as necessary until issues are resolved.

Test the newly upgraded test database with existing applications to verify that they operate properly with a new Oracle database.

  • Test enhanced functions and new capabilities by adding available Oracle Database features.

  • Ensure that the applications operate in the same manner as they did in the current database.

  • Recompile invalid objects (for example, using utlrp.sql) to determine the time required.

See Also:

Oracle Database Testing Guide for information on testing a database upgrade

Parent topic: Develop a Test Plan for Upgrading Oracle Database