| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: providing 24*7 database ---
Tony,
I must say this is a very clever use of partitioning. Thanks for sharing.
Jared
"Aponte, Tony"
<AponteT_at_hsn.n To: Multiple recipients of list ORACLE-L
<ORACLE-L_at_fatcity.com>
et> cc:
Sent by: Subject: RE: providing 24*7 database ---
root_at_fatcity.c
om
10/22/01 09:05
AM
Please respond
to ORACLE-L
We use a modified version of your duplicate schema idea. But we don't have the objects in different schemas. We use partitioned objects so that we can exchange the partitions with the production tables at a scheduled time. The voodoo is that we use a single range partition of MAXVALUE and all indexes are LOCAL PARTITIONED. The partitioning key doesn't really matter in this setup since we aren't using the features for its advantages, just to be able to swap data and index segments on the fly. I've attached a transcript showing the actual sequence but I'll give you a short explanation first:
There are production tables/indexes that are used by the application, whether directly or via synonyms. There is a second set of tables with a _TEMP suffix that have duplicate structural definitions (constraints, column names and data types, etc.) The indexes also end with a _TEMP but are identical to the production ones. The only difference is that they are partitioned tables/indexes. All partitioned objects have a single range partition by a bogus column. The single partition is bounded by the MAXVALUE keyword, so all of the data is contained in one partition.
Now you can manipulate the _TEMP tables at your convenience without interrupting the access tot he "published" objects. Once you have refreshed your _TEMP objects and are ready to publish the new data your would execute a series of ALTER TABLE <tablename>_TEMP EXCHANGE PARTITION TABLE <tablename>. That's it. No re-pointing of synonyms, revalidating of views/stored procs./etc. The application keeps chugging along. The next execution of SQL will use the published tables.
HTH
Tony Aponte
SQL> create table x(x1 number,x2 varchar2(50));
Table created.
SQL> create index xi1 on x(x1);
Index created.
SQL> create table y(x1 number,x2 varchar2(50)) 2 partition by range (x1) (partition y values less than (maxvalue));
Table created.
SQL> create index yi1 on y(x1)
2 local (partition yi1 );
Index created.
SQL> insert into x values (1,'original data from regular table');
1 row created.
SQL> insert into y values (2,'original data from partitioned table');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from x;
X1 X2
1 original data from regular table
SQL> select * from y;
X1 X2
2 original data from partitioned table
SQL> alter table y exchange partition y with table x;
Table altered.
SQL> select * from x;
X1 X2
2 original data from partitioned table
SQL> select * from y;
X1 X2
1 original data from regular table
SQL> select * from user_indexes;
output snipped
SQL> select * from user_part_indexes;
output snipped
SQL> alter table y exchange partition y with table x;
Table altered.
SQL> select * from x;
X1 X2
1 original data from regular table
SQL> select * from y;
X1 X2
2 original data from partitioned table
SQL> select * from user_indexes;
output snipped
SQL> select * from user_part_indexes;
output snipped
SQL> drop table x;
Table dropped.
SQL> drop table y;
Table dropped.
SQL> spool off
-----Original Message-----
Sent: Thursday, October 18, 2001 5:30 PM
To: Multiple recipients of list ORACLE-L
hi gurus,
Our shop ( GIS oracle spatials ) attempting to provide a production
database
(7x 24 hours) , currently we have to offline database for users while
loading of data.
we donot what users to access data while loading.
We are thinking of provide 24* 7 services to customers with out going
offline.
What are the best possible solutions ? I had few but I donot know its
right
direction .................
Possible Solutions
Replication -
* not possible until Oracle 9i spatial (because of the restriction
on
replicating objects).
* Even then expense of additional licensing/machinery may be too
great
Duplicate instances - have 2 instances and users switch from one to other after production load.-- how to implement ?
Duplicate schema within current database. Have 3 schemas inside a single TIPSPROD instance. The schema with the current data remains in production until the second schema is loaded with the new data. This is can only work
if we introduce a third schema that holds the views. At certain time, we redefine all the views in the DATAVIEWS schema. --- its kind of duplication
Can some body direct me where to look or any ideas ?
TIA
> narender.akula
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Narender Akula INET: narender.akula_at_terralink.co.nz Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing ListsReceived on Mon Oct 22 2001 - 12:47:33 CDT
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Jared.Still_at_radisys.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
 |
 |