Upgrading with the
DBUA-
The DBUA is essentially unchanged from the Oracle Database 10g release. There
are a couple of important changes which you’ll see when we go through a manual
upgrade process. You’ll have an additional screen during the upgrade process,
which asks you to specify a location for the diagnostic directory. The DBA
automatically starts when you choose to upgrade your database during the
installation of the Oracle Database 11gserver software. Note that when you use
the manual upgrade method, you must upgrade an ASM instance separately, Where as
the DBUA lets you perform the ASM upgrade along with the upgrade of the database
instance.
Scripts to Run for
Upgrading a Database- Upgrade
an Oracle Database 10grelease database to the Oracle Database 11grelease using
the Oracle-supplied scripts for upgrading a database. The following are the
steps you use in upgrading a database to the Oracle Database 11g release:
#
utlu111i.sql - The Pre-Upgrade Information tool
#
catupgd.sql - The script that performs the actual upgrade process
# utlu111s.sql - The Post-Upgrade Status tool
# catuppst.sql - The post-upgrade actions script
# utlrp.sql - The script you run at the end of the upgrade process, to
recompile
all objects that were invalidated during the upgrade.
Real
Application Testing
The
Real Application testing feature, which consists of two separate tools,
Database Replay and the SQL Performance Analyzer, is arguably the most
significant new feature in the Oracle Database 11.1 release. The two new
features address significant unmet needs regarding change management.
Organizations often find that upgrading operating system or database server
software or making major application changes is fraught with considerable risk.
There simply is no way to predict how a production system is going to perform
pursuant to major changes. Real Application Testing addresses this need by
letting you quickly and exhaustively test changes using Oracle’s own tools
instead of your having to resort to third-party tools that may not be able to
capture all the required changes.
The
new Oracle-supplied packages DBMS_WORKLOAD_CAPTURE and DBMS_WORKLOAD_REPLAY
provide the APIs for the Database Replay feature. The DBMS_SQLPA package
supports the SQL performance Analyzer feature. The following sections first
look at the Database Replay feature and then the SQL Performance Analyzer.
Database Replay - System changes such
as a database upgrade require substantial testing and validation before you can
actually migrate the changes to a production system. The trick is to simulate a
real production workload on a test system. The Database Replay feature enables
you to perform real-life testing of major changes by letting you capture the
actual database workload on the production system and replay it on a test
system. Thus, you essentially re-create the production workload effortlessly on
a test system. Database Replay performs a sophisticated replay of the production
workload by adhering to the original concurrency and timing characteristics.
Once you complete the testing, you can analyze and review the reports produced
by Database Replay to see if there was a performance divergence between the two
runs and also if there were any errors. Finally, you can choose to implement
the recommendations made by Database Replay to fix any problems it encountered
during the replay of the production workload.
You
can employ Database Replay to test significant system changes such as the
following:
#Operating
system and database upgrades and migrations.
#
Configuration changes such as moving to an oracle
RAC environment.
#
Storage changes.
Database
Replay doesn’t capture the following types of client requests:
# SQL*Loader direct path load of data
# Oracle Streams
# Data Pump Import and
Export
# Advanced replication
streams
# Non–PL/SQL-based Advanced
Queuing (AQ)
# Flashback Database and
Flashback queries
#
Distributed transactions and remote describe/commit operations
# Shared server
# Non–SQL-based object access
The
following data dictionary views help you manage the Database Replay feature:
# DBA_WORKLOAD_CAPTURES - It shows all workload
captures you performed
in a database.
# DBA_WORKLOAD_FILTERS – It shows all workload filters
you defined in a database.
# DBA_WORKLOAD_REPLAYS – It shows all workload
replays you performed in
a database.
# DBA_WORKLOAD_REPLAY_DIVERGENCE – It helps
monitor workload divergence.
# DBA_WORKLOAD_THREAD – It helps monitor the status of external
replay clients.
# DBA_WORKLOAD_CONNECTION_MAP – It shows all connection
strings used
by workload replays.
SQL
Performance Analyzer
SQL
Performance Analyzer, which, along with the Database Replay constitutes the
Total Replay
feature,
lets you test the impact of potential changes such as a server or database
upgrade on SQL workload response time. The SQL Performance Analyzer focuses on
comparing the performance of a specific SQL workload before and after a major
system change. The analyzer does this by building two versions of the SQL
workload performance, which includes both the SQL execution plans as well as
their execution statistics. After analyzing SQL performance both before and
after you make a major
change,
the SQL Performance Analyzer provides suggestions to prevent potential
performance degradation of SQL statements. This is especially handy when you’re
planning an upgrade of your database to a newer release of the Oracle database.
The SQL Performance Analyzer, by enabling you to compare SQL performance on two
systems running on different versions of the Oracle database, lets you know
ahead of the upgrade which of the SQL statements may show a deterioration in
performance. Thus, you can reengineer those statements prior to the actual
upgrade.
You
can use the SQL Performance Analyzer to predict performance changes resulting
from the following system changes:
# Database and application upgrades
# Hardware upgrades
# Operating system upgrades
#
Initialization parameter changes
# SQL tuning actions such as the creation of SQL
profiles
# Statistics gathering
# Schema changes
