RE: Partitions restore

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Thu, 2 Nov 2006 15:01:06 -0500
Message-ID: <F4C27E77F7A33E4CA98C19A9DC6722A201612248@EXCHANGE.corp.perceptron.com>

Yechiel,

We implemented somewhat different approach in order to archive (remove from "active" database) old partitions which are not needed for "everyday" activities, and de-archive them whenever user needs to see information stored in "old" partitions (partition key has DATE type).

When archiving:
1. create "archive" tablespace.
2. in the "archive" tablespace create "archive" (not partitioned) table with the same structure as "original" table 'as select * from <original_table> partiton(<archived_partition>).

3. change "archive" tablespace to "read only".
4. drop "archived" partition from original table.
5. export "archive" tablespace as transportable tablespace.
6. send alert to the front-end application that transportable tablespace

export is ready to be saved somewhere on the network (or on the tape).

When de-archiving:

1. bring back a copy of required exported transportable tablespace.
2. re-connect it to the database, importing transportable tablespace.
3. change imported tablespace to "read write".
4. split "oldest" partition in the "original" partitioned table (based

on the archive, we brought back).
5. alter <original_table> exchange partition <newly_created_partition> with table <table_in_the_imported_tablespace>. 6. when user is done looking at the "old" data (brought from the archive), we drop this partition, change "imported" tablespace to "read only" and drop it.

Both actions (archiving and de-archiving) are done without affecting availability of the "original" table.

Igor

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Yechiel Adar Sent: Thursday, November 02, 2006 2:11 PM To: ORACLE-L_at_freelists.org
Subject: Partitions restore

Oracle 9206 on windows RAC.
We have am imaging system for checks.
Each morning each employee in the bank retrieve the images of the checks, that belong to the account he is responsible for, and decide if to honor them or bounce them. Grey images(each about 30KB) are kept for 90 days and black and white are kept for 1 year. After the first scan the images are not used again, unless there is something to check.
By the nature of the application the users use mostly the last partition.

In a meeting today someone suggest the following:

1) Put each partition (or several partitions) in a separate tablespace.
2) Backup the whole database.
3) In case of needing to restore, you can restore the system tablespace

and the tablespaces containing the most recent partitions and activate the database.
4) While the users are working you can restore the other tablespace containing the historical images that are not usually used.

This sounds fine but I started to wonder if Oracle will let me work on a table that contain partitions that should be there but does not exist.

--

Adar Yechiel
Rechovot, Israel

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Thu Nov 02 2006 - 14:01:06 CST