HomeOracle Page 3 - Oracle and Availability: Illustrated Downtime Scenarios
User-Defined Availability - Oracle
Ever have to recover your servers from a flooded basement? Move a database to an new platform and need the weekend to test it but users need the database today? We will illustrate various database problems and which piece of technology could be employed to prevent the outage or to recover from it quickly. (From the book Oracle Database 10g High Availability with RAC, Flashback & Data Guard by Matthew Hart and Scott Jesse, ISBN: 0072254289, McGraw-Hill/Osborne, 2004.)
Sometimes database availability is not defined by the database administrator. Even if the DBA can connect to the database and select against tables, users may not be having the same experience from their application.
Take the example of Horatio's Woodscrews. Due to massive transaction processing that occurs over the course of the day against the woodscrew_orders table, thousands of new rows could be added over the course of a day. When the CEO goes to check on his reports which show how many woodscrews are being sold, and to which customers, the report is creating an ad hoc SQL query against the entire table. Because of the amount of data, and the simplistic data design, these queries might not return in what the CEO envisions as a reasonable amount of time. The DBA of the company is inundated with uncomfortable calls from impatient executives wondering why the database is “down.”
The CEO has a button on his desktop application, see, that says nothing more than woodscrew_orders_by_customer. He has no interest in hearing about how long-running queries against massive amounts of data might take a little longer than his expectations. He just wants the report to be available.
Test and Development Availability
Just as with CEOs demanding faster reports, sometimes availability is defined more by a human resource issue than actual data availability. At Horatio's Woodscrews, they run all their production systems on Solaris, but are investigating Linux as an alternative. The first step of the investigation is to set up the test and development shop with Linux servers, and then move a copy of production data to the new Linux servers for application testing.
The Linux servers arrived late in the week, and were configured with the Oracle software image over the weekend. On Monday, the development lead needs to get the data moved from the production Woodscrew database to the Linux servers. So the development lead contacts the senior DBA for the database and asks that they transition the data to the new Linux servers.
The dilemma for the DBA is that getting a read-consistent view of the Woodscrew tables is nearly impossible for long enough to get an export to complete. The only time there is a large enough window for such an operation would be the following weekend. The backups cannot be used, because they are backups of Solaris datafiles. But if they wait for the weekend, the development team will sit idle all week on a project that has imminent deadlines. How can they get the data moved across platforms in an expedient way?
Partitioning, mviews, and Index-Organized-Tables
Sometimes availability has as much to do with user perception as it does with database reality. When upper management complains, even brand new DBAs must take measures to improve performance for them. In this situation, the HA DBA will be best served by looking to reformulate some of the tables used by his boss, or by creating materialized views to subset the data appropriately. This is covered in Chapter 2, in the section “Materialized Views.”
Cross-Platform Transportable Tablespaces
Availability isn't always about the production database, and an outage may simply be defined by “idle developers twiddling their thumbs.” But a move of database information to a new platform has always been a huge undertaking involving the tried and true export and import utilities to logically extract data. However, this always came with huge resource issues at the export database.
Starting in Oracle Database 10g, Oracle has built in a means of using the Transportable Tablespace feature of the database and allowing it to transfer tablespaces across platforms. This is discussed in Chapter 2, in the section “Transportable Tablespaces.”
This chapter is from Oracle Database 10g High Availability with RAC, Flashback & Data Guard, by Hart and Jesse. (McGraw-Hill/Osborne, 2004, ISBN: 0072254289). Check it out at your favorite bookstore today. Buy this book now.