When upgrading Oracle you may also want to consider
upgrading your application server software.
Normally this is based on the certification matrix from your vendor,
and there can be a lag from 6 months to several years before a vendor
application package is certified for the newest release of Oracle.
Remember, it is very risky to implement simultaneous
changes. Even a low level change
like an upgrade to UNIX can cause issues with the end-user application, and
you should always do a bottom-up approach.
For Oracle systems, you start by reviewing the
certification matrix for Oracle 11g and upgrade to the latest stable release
of your operating system. A few
caveats for OS upgrades include getting the getting the 2nd release:
Nobody wants to be the first to encounter bugs, and savvy shops will
always wait for the second major version to upgrade.
Most shops will upgrade from 10g release 2 to Oracle 11g release 2,
and wait until the second major release of the OS environment.
It is a best practice to start with the OS upgrade,
then the database software, then to application server software, followed
last by the application software.
Generally, shops will want for a full business cycle
(weekly or monthly) before moving up to the next layer, upgrading the OS and
running for a month, upgrading Oracle and running for a month, upgrading the
app server and tuning for a month, and finally upgrading the application
software and running for a month.
·
Monolithic
server:
There is a massive trend toward
Oracle instance consolidation as shops abandon the archaic
one-server/one-instance architecture in favor of the new giant
servers that can manage dozens of Oracle instances.
·
More CPU:
Oracle
11g has
improvements to parallel query and many shops are considering adding
processes to their servers to speed-up full-scan operations.
·
Solid-state disks:
Oracle now has the flash cache working for Linux, and with the Sun
acquisition, it’s a sure bet that Oracle will be offering a super fast
solid-state release of Oracle in the near future.
·
Virtualization:
Using VMware for Oracle goes along with server consolidation as noted
in the book
Oracle on
VMware. While
virtualization is a hot topic right now, the very best way to share
computing resources is to do nothing at all, and there are existing methods
for adding CPU
“governors” with CPU caging to keep any one instance from hogging too
much CPU. Of course, the DBA
decides how much shared RAM each instance gets, so this is not an issue.
·
Compression
– Oracle 11g compression is one of the hottest new features because it’s
transparent and effective.
While compression does indeed make tables smaller, the real benefit of
Oracle 11g compression ifs the ability to perform faster range-scan and
full-scan operations because more data is fetched with each I/O operation.
Oracle 11g Software upgrades
Industry trends aside, Oracle has
hundreds of new features to choose from and you need to be able to separate
the wheat from the chaff.
For an excellent treatment of 11g new features from a
functional perspective, I recommend the book
Oracle 11g New Features by John
Garmany, V. J. Jain, with Oracle ACE’s Lutz Hartmann, Brian Karr and Stave
Karam.
Oracle 11g has many new DBA new features to make it
easier to manage complex databases:
·
Enhanced ILM
- Information Lifecycle Management (ILM) has been around for decades, but
Oracle has made a push to codify the approach in 11g.
·
Table-level control of
CBO statistics refresh threshold
- When Oracle automatically enables statistics collection, the default
"staleness" threshold of 10% can now be changed with the
dbms_stats.set_table_prefs procedure:
·
File Group Repository
- Oracle introduced an
exciting new feature in 10gr2 dubbed the Oracle File Group Repository (FGR).
The FGR allows the DBA to define a logically-related group of files and
build a version control infrastructure.
- Interval partitioning for
tables - This is a new 11g partitioning
scheme that automatically creates time-based partitions as new data is
added.
·
Server-side connection
pooling
- In 11g server-side
connection pooling, an additional layer to the shared server, to enable
faster [actually to bypass] session creation.
·
Capture/replay database
workloads
- Sounds appealing. You can capture the workload in prod and apply it in
development. Oracle is moving toward more workload-based optimization,
adjusting SQL execution plans based on existing server-side stress.
·
Data Guard - Standby
snapshot - The
new standby snapshot feature allows you to encapsulate a snapshot for
regression testing. You can
collect a standby snapshot and move it into your QA database, ensuring that
your regression test uses real production data.
·
Quick Fault Resolution
- Automatic capture of diagnostics (dumps) for a fault.
Oracle is big on automation and we see these new 11g
automation features:
·
Automated Storage Load
balancing -
Oracle’s Automatic Storage Management (ASM) now enables a single storage
pool to be shared by multiple databases for optimal load balancing. Shared
disk storage resources can alternatively be assigned to individual databases
and easily moved from one database to another as processing requirements
change.
·
Automatic Diagnostic
Repository -
When critical errors are detected, Oracle automatically creates an
“incident” ticket, notifying the DBA instantly.
·
Automatic Diagnostic
Repository (ADR)
- When critical errors are detected, they automatically create an
“incident”. Information relating to the incident is automatically captured,
the DBA is notified and certain health checks are run automatically.
11g RAC new Features
Oracle continues to enhance Real Application Clusters in
Oracle11g and we see some exciting new features in RAC manageability and
enhanced performance:
·
Oracle 11g RAC parallel
upgrades -
Oracle 11g promises to have a rolling upgrade features whereby RAC database
can be upgraded without any downtime.
·
Oracle RAC load balancing
advisor -
Starting in 10gr2 we see a RAC load balancing advisor utility.
Oracle says that the 11g RAC load balancing advisor is only available
with clients which use .NET, ODBC, or the Oracle Call Interface (OCI).
·
ADDM for RAC
- Oracle will incorporate RAC into the automatic database diagnostic
monitor, for cross-node advisories.
·
ADR command-line tool
- The Oracle Automatic Diagnostic repository (ADR) has a new command-line
interface dubbed ADRCI, the ADR Command Interface.
·
Optimized RAC cache
fusion protocols
– This moves on from the general cache fusion protocols in 10g to deal with
specific scenarios where the protocols could be further optimized.
·
Oracle 11g RAC Grid
provisioning -
The Oracle grid control provisioning pack allows you to "blow-out" a RAC
node without the time-consuming install, using a pre-installed "footprint".
·
RAC Hot patching -
Zero downtime patch application.
Now that we see
the features, let’s review a best practices checklist for migrating to 11g.
Oracle Migration
checklist
Oracle 11g migration due diligence
recognizes that Oracle is the world's most flexible and robust database, and
upgrading can be very tricky.
Prior to putting your Oracle 10g upgrade into
production, it's a best practice to perform complete testing and obtain an
independent Oracle health check to ensure an optimal configuration.
Remember you can use the
v$system_fix_control
view to display specific optimizer features by
release.
One common complaint about a haphazard
migration strategy is varying performance after migration.
New bugs are found and old bugs are fixed and while
overall performance will be faster, you need to ensure proper testing to
avoid surprises.
I recommend checking these items prior to migrating to
production in 11g:
·
Selectively disable
dynamic sampling
- Dynamic sampling is not for every database.
Dynamic sampling default levels change between releases, and you may
want to turn-off dynamic sampling, depending on your database load.
·
Re-set optimizer costing
- Consider unsetting your CPU-based optimizer costing base upon your
workload characteristics. CPU
costing is best of you see CPU in your top-5 timed events in your
STATSPACK/AWR report, and the 11g default of _optimizer_cost_model=cpu will
include CPU costs, sometimes invoking more full scans, especially in
tablespaces with large blocksizes.
·
Verify quality of CBO
statistics -
Oracle does automatic statistics collection and your original customized
dbms_stats job (with your customized parameters) will be overlaid.
You may also see a statistics deficiency (i.e. not enough histograms)
causing performance issues.
Re-analyze object statistics using dbms_stats and make sure that you collect
system statistics.
·
Check optimizer
parameters -
Ensure that you are using the proper optimizer_mode (the default of all_rows
which is not optimal for online transaction processing systems).
·
Check I/O timings
– When using ASM, beware that "db file scattered reads" (full scan I/O) can
become slower than "db file scattered reads" (usually single block gets)
because of non-contiguous data block placement on disk.
In sum, migration to 11g can be seamless
or full of surprises, it’s your choice.
A proper 11g migration requires careful up-front
planning with an eye towards upgrading all of the infrastructure components
that go along with Oracle and choosing those new 11g features that are right
for you!
NOTE: Rampant author Laurent Schneider has some additional insight
into
creating an Oracle Automatic Diagnostic Repository (ADR).
