Re: A way to restore a DB

Andrey,

I am not sure I understand "We can now import the large data tables - partition by partition..And I don't care if THIS step will take 2 weeks.". Didn't you mention that you couldn't afford 48 hours of recovery?

Import is extremely slow, and I would not recommend exp/imp for backup recovery.

Or is it that some tables you could wait 2 weeks for recovery and some more urgent tables should be recoved ASAP? Also, you mentioned a lots of partions are dropped annd new ones are created every day. Are all the partitions of the table updated everyday? It seemsto me you are inserting into partition for today and dropping partition for sysdate-90 days. The other 89 partitions seem like they are intact. So why back them up every day? Back them up once and recover them only when time permits.

Here is an example scenario for a table called STOCKS partioned along trading_day column. You have partitions named in format STOCKS_YYMMDD and each one is its own tablespace. At the beginning of 11/25/02, you would drop partition stocks_020827 and add stocks_021125. Assuming that all other partitons are not manipulated anyway, the hot backup will pick up changes to stocks_021125 only. Now you had a crash. You would restore ONLY the datafiles of the tablespace that contained the stocks_021125 partition and recover that. This should be fairly easy. Then you would restore and recover other partitions leisurely.

You also mentioned you could build these tables from some external database. So you may decide to forego backups all together; but that's a business decision, not tehnical one. In one of the datawarehouses (actually a datamart) I manage, lack of tape drives prompted us to stop backups completely. If we have to recover from a disater, we have scripts that build the tablespaces, tables, populate control tables, etc and then refresh it from the regular sources. This works out to be longer but less expensive.

HTH. Arup Nanda
www.proligence.com

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Sunday, November 24, 2002 12:23 PM

> Dear gurus!
> We are evaluating a "strange" way to "recover" a production DB.
> This is a 3.7 TB database, still growing, with LOTS of data inserted every
5
> minutes (and several partitions belonging to several tables get dropped
each
> day - we keep historical data for 90 days back), which we used to backup
> with RMAN to HP Omniback controlled tapes.
> We had a failure, and it took almost 48 hours to restore/recover the DB
(in
> addition to backed DB files, I had to restore thousands of archived logs
> from tapes and apply them to the DB).
> We can not afford to have the DB down for 2 days.
> Now, we decided to give up RMAN hot backup, actually to give up backup (in
> it's classic meaning) at all.
> I'll explain why: It's very important for us to get the DB up & running
ASAP
> after a crash. We want the DB operating much more than we care about
> historical data that reside in our DB. Our goal is to enable the inserts
> (which run every 5 minutes, as I have stated) ASAP.
> So, we plan to do the following backup/recovery procedures:
> 1) We want to generate a script that will build the basics of the
DB -
> create the DB and the instance, build tablespaces, users, grants, roles
> etc...
> 2) We want to create a daily export of small (but the most
important)
> configuration tables, which can be imported very quickly after the crash
and
> rebuild of DB.
> At this point the DB is operational.
> 3) We can now import the large data tables - partition by partition.
> And I don't care if THIS step will take 2 weeks.
> What do you say?
> Any reviews of my steps and the idea in general?
> And, do you have a script that can re-engineer a DB (as in my step 1)?
> Thanks a lot in advance.
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Andrey Bronfin
> INET: andreyb_at_elrontelesoft.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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: Arup Nanda
  INET: arupnanda_at_hotmail.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).