Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: providing 24*7 database ---

RE: providing 24*7 database ---

From: Jim Conboy <Jim.Conboy_at_trw.com>
Date: Wed, 24 Oct 2001 08:37:51 -0700
Message-ID: <F001.003B33A2.20011024083524@fatcity.com>

Seemed fine when I tried it (and thanks for the idea!).  The trick with the indexes is that the ones on the partitioned table have to be local, which was in the scripts provided, and the 'exchange partition' had to say 'including indexes', which was not.  Adding 'including indexes' made this work like a charm for me.
 
Jim>>> AponteT_at_hsn.net 10/24/01 11:25AM
>>>

I
couldn't verify that the non-partitioned indexes become unusable after exchanging the partition for the normal table.  In the sample I posted I snipped the output of the queries on USER_INDEXES and USER_PART_INDEXES, but my tests showed that they remain valid.  I'm curious to see why your results are different.  Can you post the spooled output of your test?
<SPAN
class=638130914-24102001> 
<SPAN
class=638130914-24102001>Thanks.
Tony
Aponte

  <FONT face="Times New Roman"
  size=2>-----Original Message-----From: Narender Akula   [mailto:narender.akula_at_terralink.co.nz]Sent: Tuesday, October 23,   2001 10:30 PMTo: Multiple recipients of list   ORACLE-LSubject: RE: providing 24*7 database   ---
  <SPAN
  class=152063301-24102001>Thanks all for the input.   hi
  tony ,
  <SPAN
  class=152063301-24102001>Quick question ... when you exchange partititons with   non partitioned table data , all indexes on non partitioned tables become   unusable status right.
  do
  have to rebuild them after every exchnage...   <SPAN
  class=152063301-24102001> 
  <SPAN
  class=152063301-24102001>naren   

    <FONT face=Tahoma
    size=2>-----Original Message-----From: Aponte, Tony     [mailto:AponteT_at_hsn.net]Sent: Tuesday, 23 October 2001     05:06To: Multiple recipients of list ORACLE-LSubject:     RE: providing 24*7 database ---
    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

    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) <FONT     size=2>  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 <FONT

    size=2>                                                                                
    

    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----- From:
    Narender Akula [<A
    href="">mailto:narender.akula_at_terralink.co.nz]     Sent: Thursday, October 18, 2001 5:30 PM <FONT     size=2>To: Multiple recipients of list ORACLE-L <FONT     size=2>Subject: providing 24*7 database ---     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 - <FONT
    size=2>*       not possible until Oracle 9i     spatial  (because of the restriction on <FONT     size=2>replicating objects).  <FONT     size=2>*       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 <FONT     size=2>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 Received on Wed Oct 24 2001 - 10:37:51 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US