Chapter 1: Oracle and Availability: Illustrated Downtime Scenarios | Oracle Database 10g. High Availablity with RAC Flashback & Data Guard

< Day Day Up >

Overview

As you have already discovered, or perhaps are about to discover, maintaining the high-availability database is a tricky prospect. There are hundreds of possible sources of downtime hiding in the crooks and crannies of your enterprise. Many outage situations cascade into each other, where an attempt to resolve a problem creates another outage and quickly spirals further and further out of control. But almost all downtime scenarios can be prevented with careful planning or swift corrective action.

Throughout this book, we discuss numerous technologies that prevent or drastically reduce different database outages. But what are these downtime scenarios? Which possible outages can you protect yourself against? In order to illustrate the various types of situations that threaten the availability of your database, we will illustrate various database problems that can be prevented using the technologies outlined in this book. We have typically provided a worst-case scenario, but we wanted to place the high-availability technologies in Chapters 2 through 11 in a real-world context prior to exploring the configuration and administration of these technologies.

After each downtime scenario, a text box will describe which piece of technology could be employed to prevent the outage, or to recover from it quickly. If you are not interested in reading through the situations, you can skim through this chapter and just look for the boxes. The boxes provide a road map to the available technologies that are discussed in the rest of this book.

Horatio's Woodscrews

For the situations in this chapter, and for the workshops and examples throughout the book, we will use the database from the company Horatio's Woodscrews, Inc. This is a fictitious company that sells…well…woodscrews. This company has a primary database that holds millions of records concerning woodscrews, woodscrew inventory, and woodscrew orders. The following is the code to create the application owner in the oracle database, along with the three primary tables: Woodscrew, woodscrew_inventory, and woodscrew_orders. These tables will be used throughout the book for all examples, so you might want to make note of this information. After building the tables, a few rows are added so we can manipulate them for labs, like this:

create tablespace ws_app_data datafile '/u01/product/oracle/oradata/orcl/ws_app_data01.dbf' size 100m; create tablespace ws_app_idx datafile '/u01/product/oracle/oradata/orcl/ws_app_idx01.dbf' size 100m; create user ws_app identified by ws_app default tablespace ws_app_data temporary tablespace temp; grant connect, resource to ws_app; connect ws_app/ws_app;  create table woodscrew (  scr_id           number not null,  manufactr_id     varchar2(20) not null,  scr_type         varchar2(20),  thread_cnt       number,  length           number,  head_config      varchar2(20),  constraint pk_woodscrew primary key (scr_id, manufactr_id)  using index tablespace ws_app_idx);  create index woodscrew_identity     on woodscrew(scr_type, thread_cnt, length, head_config)  tablespace ws_app_idx;  create table woodscrew_inventory (  scr_id           number not null,  manufactr_id     varchar2(20) not null,  warehouse_id     number not null,  region           varchar2(20),  count            number,  lot_price    number); create table woodscrew_orders ( ord_id            number not null, ord_date          date, cust_id           number not null, scr_id            number not null, ord_cnt           number, warehouse_id      number not null, region            varchar2(20), constraint pk_wdscr_orders primary key (ord_id, ord_date) using index tablespace ws_app_idx); ---- Now, add rows to the tables. insert into woodscrew values ( 1000, 'Tommy Hardware', 'Finish', 30, 1.5, 'Phillips'); insert into woodscrew values ( 1000, 'Balaji Parts, Inc.', 'Finish', 30, 1.5, 'Phillips'); insert into woodscrew values ( 1001, 'Tommy Hardware', 'Finish', 30, 1, 'Phillips'); insert into woodscrew values ( 1001, 'Balaji Parts, Inc.', 'Finish', 30, 1, 'Phillips'); insert into woodscrew values ( 1002, 'Tommy Hardware', 'Finish', 20, 1.5, 'Phillips'); insert into woodscrew values ( 1002, 'Balaji Parts, Inc.', 'Finish', 20, 1.5, 'Phillips'); insert into woodscrew values ( 1003, 'Tommy Hardware', 'Finish', 20, 1, 'Phillips'); insert into woodscrew values ( 1003, 'Balaji Parts, Inc.', 'Finish', 20, 1, 'Phillips'); insert into woodscrew values ( 1004, 'Tommy Hardware', 'Finish', 30, 2, 'Phillips'); insert into woodscrew values ( 1004, 'Balaji Parts, Inc.', 'Finish', 30, 2, 'Phillips'); insert into woodscrew values ( 1005, 'Tommy Hardware', 'Finish', 20, 2, 'Phillips'); insert into woodscrew values ( 1005, 'Balaji Parts, Inc.', 'Finish', 20, 2, 'Phillips'); insert into woodscrew_inventory values ( 1000, 'Tommy Hardware', 200, 'NORTHEAST', 3000000, .01); insert into woodscrew_inventory values ( 1000, 'Tommy Hardware', 350, 'SOUTHWEST', 1000000, .01); insert into woodscrew_inventory values ( 1000, 'Balaji Parts, Inc.', 450, 'NORTHWEST', 1500000, .015); insert into woodscrew_inventory values ( 1005, 'Balaji Parts, Inc.', 450, 'NORTHWEST', 1700000, .017); insert into woodscrew_orders values ( 20202, '2003-09-22 00:02:02', 2001, 1000, 20000, 64114, 'NORTHEAST'); insert into woodscrew_orders values ( 20203, '2003-09-22 00:02:04', 2001, 1001, 10000, 64114, 'NORTHEAST'); insert into woodscrew_orders values ( 20204, '2003-09-22 00:02:06', 2002, 1002, 10000, 64114, 'NORTHWEST'); insert into woodscrew_orders values ( 20205, '2003-09-22 00:02:08', 2002, 1003, 30000, 64114, 'NORTHWEST'); insert into woodscrew_orders values ( 20206, '2003-10-04 00:02:12', 2002, 1004, 10000, 80903, 'SOUTHWEST'); insert into woodscrew_orders values ( 20207, '2003-10-04 00:02:14', 2001, 1003, 20000, 80903, 'SOUTHWEST'); insert into woodscrew_orders values ( 20208, '2003-10-04 00:02:16', 2002, 1002, 30000, 64114, 'SOUTHWEST'); insert into woodscrew_orders values ( 20209, '2003-10-04 00:02:18', 2003, 1001, 40000, 90210, 'NORTHWEST'); insert into woodscrew_orders values ( 20210, '2003-11-04 00:02:20', 2005, 1000, 10000, 83401, 'SOUTHEAST'); insert into woodscrew_orders values ( 20211, '2003-11-04 00:02:22', 2002, 1005, 10000, 83401, 'SOUTHEAST'); insert into woodscrew_orders values ( 20212, '2003-11-04 00:02:24', 2001, 1004, 10000, 64114, 'NORTHEAST'); insert into woodscrew_orders values ( 20213, '2003-11-04 00:02:26', 2003, 1003, 10000, 64114, 'NORTHEAST'); insert into woodscrew_orders values ( 20214, '2003-12-04 00:02:28', 2002, 1001, 20000, 64114, 'SOUTHEAST'); insert into woodscrew_orders values ( 20215, '2003-12-04 00:02:30', 2001, 1000, 10000, 80903, 'NORTHWEST'); insert into woodscrew_orders values ( 20216, '2003-12-04 00:02:32', 2005, 1001, 50000, 80903, 'SOUTHWEST'); insert into woodscrew_orders values ( 20217, '2003-12-04 00:02:34', 2003, 1003, 70000, 90210, 'SOUTHWEST'); commit;

< Day Day Up >