October 2006

The Specialist
Database News You Can Use
A monthly newsletter for Oracle users

October 2006
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500

Ask DB Specialists: Upgrading Oracle without Downtime

You’re invited to submit your Oracle-related questions to us at askdbspecialists10@dbspecialists.com. Please include your name and telephone number in your email.

This month’s question comes to us from Susan in Chicago: Will RAC, a physical standby database, or a logical standby database allow me to upgrade my Oracle database in a rolling manner to get near-zero downtime?

Ian Jones of the Database Specialists team responds: Let’s look at each of these configurations in turn:

Real Application Clusters (RAC)
It is possible to apply certain patches to a RAC environment in a rolling manner. Depending upon the patch, it may be possible to apply the patch to each RAC instance in turn and achieve near-zero downtime:

  1. Close down instance A on node 1.
  2. Apply patch to instance A on node 1.
  3. Start instance A on node 1.
  4. Close down instance B on node 2.
  5. Apply patch to instance B on node 2.
  6. Start instance B on node 2.
  7. Repeat for all additional instances.

This approach can only be used if Oracle Corporation has designated the patch as a “rolling updatable patch.” In order for a patch to be designated as rolling updatable it must not affect the contents of the database, the RAC inter-node communication infrastructure, or certain shared database resources such as control files or datafile headers. For example, a patch that affects the SQL*Plus executable would likely be rolling updatable but a patch affecting the database shared pool would not. Only one-off patches can be rolling updatable (patchsets cannot). For example, the July 2006 Security Patch is not rolling updatable. Obviously, with these restrictions, it is not possible to upgrade a RAC database in a rolling manner with near-zero downtime.

Physical Standby Database
Physical standby databases do not include any additional functionality to support rolling upgrades. Since a physical standby database is kept in sync with its primary database by sharing the same redo stream, it is generally not possible to minimize downtime during upgrades. If a database was genuinely read-only—just used for reporting during a certain period of the day—then a physical standby could be used to service read-only requests while the primary was undergoing an upgrade. However, this is a very specific case.

Logical Standby Database
Logical standby databases provide the best option for near-zero downtime rolling upgrades. They offer this potential because the logical standby database can apply SQL statements that were executed at the primary database as well as also execute other statements independently of the primary. This facility allows the upgrade of the logical standby and then the subsequent application of redo that was generated at the primary while the standby was getting upgraded. Here is a summary of the steps involved:

  1. Create a logical standby database.
  2. Stop SQL apply between the primary database and logical standby database.
  3. Upgrade the logical standby database; at this point the primary and standby are at different Oracle versions.
  4. Restart SQL apply and wait until all accumulated redo has been applied to the standby.
  5. Test to ensure no unsupported objects have been modified on the primary database.
  6. Perform a switchover to make the logical standby the new primary database.
  7. If desired, create a new standby database and perform another switchover to get the primary database back onto the original primary server.

Unfortunately, there are some restrictions to rolling upgrades using logical standby databases. Firstly, this technique is only supported if the primary and logical standby databases are both at version 10.1.0.3 or later. Therefore this approach cannot be used to upgrade Oracle 9i databases to Oracle 10g. Secondly, logical standbys have various restrictions upon what datatypes are supported. The dba_logstdby_unsupported view lists any unsupported columns and datatypes in your primary database. Thirdly, Oracle internal schemas are typically omitted; the dba_logstdby_skip view contains the skipped internal schemas.

In summary, for Oracle databases 10.1.0.3 and above, it may be possible to reduce downtime during an upgrade by using a logical standby database and upgrading in a rolling manner. Note that the logical standby is relatively new functionality and so careful testing on your particular version, platform, and database is critical to a successful upgrade.

Installing Oracle over a Slow Network

Installing Oracle software or creating a database can be a slow proposition when the network bandwidth between your desktop and the database server is limited. Oracle’s Universal Installer and Database Configuration Assistant use X Windows on Unix platforms for a graphical user interface. Unfortunately, X can be painfully slow over a slow network.

Fortunately, VNC seems to handle slow networks much better than X when it comes to Oracle’s GUI tools. By running a VNC server on your database server and a VNC viewer on your desktop, you can use Oracle’s installer and assistants over slow networks without excruciating latency.

Red Hat Enterprise Linux includes a VNC server and viewer in the /usr/bin directory. If you use Solaris, HP-UX, or Windows, you can download precompiled VNC binaries for free from http://www.realvnc.com. The free edition provides everything you need to run a VNC server on your database server and a VNC viewer on your desktop. You do not need to know the root password on your database server to set up the VNC server.

Starting a VNC server on your database server is as easy as:

  • Uncompress and untar the download file on the database server.
  • Add the X binaries directory (such as /usr/X/bin on Solaris) to your PATH.
  • Add the directory where you untarred the download file to your PATH.
  • Type “vncserver” to start the VNC server. The first time you run this command, you will be asked to enter a password.
  • Type “vncserver -kill :1” to shut down the VNC server.

Running the Oracle Universal Installer or an assistant remotely from your desktop is as easy as:

  • Start the VNC viewer on your desktop.
  • When prompted for the VNC server address, enter the name or IP address of your database server, followed by “:1”.
  • You will be prompted for a password. Enter the password you selected when you started the VNC server.
  • On your desktop you will now see a representation of the desktop on the database server. You may now launch the Oracle Universal Installer, an assistant, or any X application.

VNC offers a free and simple way to run X applications on your database server from your desktop. You don’t need to install an X emulator on your desktop, and you will probably find that Oracle’s Universal Installer and Database Configuration Assistant run faster over VNC than X emulation if network bandwidth is limited.

The Oracle Sponge for Data Warehousing

The Oracle Sponge is the weblog of David Aldridge. He is an independent consultant who specializes in Oracle and data warehousing. Through the history of his blog, you can read up on popular topics related to data warehousing. Take a look at his Best of the Oracle Sponge for entries such as:

  • The Three Pillars of Oracle Data Warehousing
  • My Dishwasher is List Partitioned
  • Predicate Pushing And Analytic Functions

Scroll down the left side of the page, and you’ll see that Aldrige also has a “Categories” section on materialized views, Oracle partitioning, Oracle parallelism, and more.RAC, RAC setup steps, material about RAC concepts and design, and much more.

Visit Database Specialists on November 2

Mark your calendar! Database Specialists will have a booth at the next Northern California Oracle Users Group (NoCOUG) conference in San Francisco on Thursday, November 2. So, stop by and learn more about our team and the services we offer such as:

  • Performance tuning
  • Upgrades and migrations
  • Backup and recovery audits and implementation
  • Remote DBA services and 24/7 monitoring and support

We’d love to meet you in person, but if you can’t make it to the conference, you can learn more about Database Specialists at http://dbspecialists.wpengine.com.

For more information on the NoCOUG conference—including a keynote by popular author Cary Milsap—check out http://www.nocoug.org/next.html.

Leave a Reply

Your email address will not be published. Required fields are marked *