Home arrow Oracle arrow Page 2 - Oracle and Availability: Illustrated Downtime Scenarios

Horatio's Woodscrews - 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.)

TABLE OF CONTENTS:
  1. Oracle and Availability: Illustrated Downtime Scenarios
  2. Horatio's Woodscrews
  3. User-Defined Availability
  4. Cyclical Database Resource Requirements
  5. Out of Space in the Woodscrew Tablespace
  6. Restarting Long-Running Transactions
  7. Waiting for the File to Restore from Tape
  8. The Dropped Table
  9. Complete and Total Disaster
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 6
July 20, 2004

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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;

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.



 
 
>>> More Oracle Articles          >>> More By McGraw-Hill/Osborne
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: