How to flip Databases ? Suggestions

How to flip Databases ? Suggestions

Post by M A Sriniv » Tue, 29 Oct 2002 00:03:26


Using SQL 2000 with SP2

I have the following scenario

1. Server should be available 24 X 7. No down time

2. Two types of Data. One Set of Data is Read/Write.
   Another set of Data is read only. This data is used mainly for
report queries.

3. Every day read only data is overwritten by new sets of data through
an extract.

I am thinking of setting up the server and database as follows

1. Read/Write database (set of tables) in One Server. Say A

2. Two Read only Databases (say B and C) in another server. Another
server is required because of  the time taken while converting extract
(about 300 Gig) to read only data is high.

3. Data will be extracted to B and C every alternate day. Since Users
will be on the site 24 X 7    It is not possible to extract to the
same Read Only Database.
So two databases B and C are needed .

4. Once extraction is over, I need to flip the Read only Databases
(between B and C) so that latest data should be available to new users
who log in. Users who are already logged in will continue to work in
the old Read only Database.

For example: Let us say users U1, U2 are already on the site and using
read only Database B, and new data is extracted to Read Only Database
C, and a new user U3 logs in after extraction to C is complete, he
should be connected to C, while users U1 and U2 can continue to work
in B until their session terminates.


1. How to set up the servers/databases for this scenario?
2. How to flip databases (DSN, application connection) to new users,
while old users continue to work with
   Old database. How this can be achieved transparent to the users


M A Srinivas


How to flip Databases ? Suggestions

Post by Joe Celk » Tue, 29 Oct 2002 02:29:09

Quote:>> 1. Server should be available 24 X 7. No down time <<

Since that is the #1 priority, you might want to look at non-stop
computers, like Tandem.  A hardware solution is the safest way to go.  

 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex ***
Don't just participate in USENET...get rewarded for it!


1. Seeking suggestion and advice: how to propagate change from one database to another database


I am looking for suggestions and advice on how to move data from one
oracle database to another on daily basis.

We have a production oracle db (let's call it "prod") on a unix box
that runs 7x24. It supports a web front end application for shopping.
Most of the data in the database will be loaded into memory of an
application server when the application starts.

We also need to provide the capability for people in the shop to change
data in the database. They do not want to change the data in "prod"
instance directly. They want to set up a separate unix box and have a
copy of "prod" running (let's call it "prod_mirror"). They would like
to make changes on "prod_mirror", QA the change and then propagate all
the changes in "prod_mirror" to the "prod" instance. The changes
involve about 10 tables (product, price, catalog, etc). Now I am trying
to find a "good" way to accmpolish this goal. I could think of two
options now:

Option A:
1. Add a flag column in these 10 tables, (possible value: modified,
inserted, etc) to indicate that record either has been updated or
2. Create a db link from "prod_mirror" to "prod"
3. Write an oracle package and move the flagged record in these 10
tables from "prod_mirror" to "prod" when propagating all the changes.
4. Clean up the flag in these 10 tables afterwards.

Option B:
1. Do not change the current table structure. But create a new table
create table DataChangeStamp(
   TableName       varchar2(30),
   ID                       Number,
   ChangeType    varchar2(10),     -- possible value: modified,
inserted, etc
   ChangedBy        varchar2(30),
   ChangeDate     Date)
2. Whenever there is a change in data in "prod_mirror", there will be a
new record created in DataChangeStamp table.
3. Create a db link from "prod_mirror" to "prod"
4. Write an oracle package and move the changed records in these 10
tables from "prod_mirror" to "prod" using the info from DataChangeStamp
when one propagates all the changes.
5. Clean up DataChangeStamp afterwards.

I am leaning toward Option B. What do you think? Any other options?



Sent via
Before you buy.

2. SQL query puzzler

3. oracle disaster recovery

4. Help with flipping columns into rows


6. PDOX7: flipping between forms in-sync

7. Iseries <tsx:getProperty Conditional Statements..

8. Flipping enabled property on tab form

9. How to flip a BIT datatype?

10. VB6 + Access 2000 = Flipped Dates

11. Building FLIP tables

12. Flipping bits in sysindexes