Re: Backup and partitions

<alex.berindei_at_elsag.it> wrote in message news:96dqjd$pd2$1_at_news.netmar.com...
>
> Hi,
> I'm designing a Oracle 8.0.6 database on NT server, with 2 groups of disks
 on
> RAID5. This is an historical database, with one table having to grow to
 500
> million rows in 2 years, and row size around 200 bytes.
> I'm looking for any advice regarding partitions, indexes and backup.
>
> I would try to explain exactly what I have:
> -table OPERATIONS (1 million rows loaded each night, for 2 years 500 mil
> rows,
> aprox 100Gb)
> -table BOXES (10000 rows loaded each night, is external key for table
> OPERATIONS, can be modified by user)
> -table BIGBOXES (1000 rows inserted by user during the day, is external
 key
> for table BOXES)
>

Let me see if I got this right....

You are proposing to write 1,000,000 rows per night to a database using RADI5? You *have* read all the RAID 5 arguments that gone through this group over the past few months, have you?

If you haven't, the short version goes like this: RAID 5 cripples systems that need to write. Oracle warns you off using them. They are getting better all the time, but nevertheless, there remains a significant write penalty.

> I'm thinking to partition the table OPERATIONS by DATE, as rows inserted
 each
> night have all previous day. Problems here:
> - how big to make the partitions (week: 1Gb, month: 4Gb)?
> - is a good idea to partition also BOXES and, eventually, BIGBOXES tables?
> - there is a numerical column OPERATIONS.DIPENDENCY, range 1 to 1000, used
 in
> most selects from OPERAZIONE. An option could be to partition the table
 using
> two colums (DATE, DIPENDENCY), with larger ranges for DATE: (DATE 3
 mounts,
> DIPENDENCY: 10 ranges 1-100,101-200,...).
> This way I cand create local prefixed indexes in partitions, using colums
> (DATE, DIPENDENCY)
>
> Backup problems (I use ARCHIVELOG mode):
> - which is the best way to backup partitions, so that it wouldn't be any
> problems (or archivelogs/recovery needed).

I'm confused. You say you run in archivelog mode, yet you want a way of backing up that won't need archives for recovery? Contradiction in terms, I'm afraid (unless I've misunderstood you). Archives are the only thing that guarantees total data recovery.

>An option could be to make old
> partitions read only, but I'm not sure in this help since I know SCN are
> written in Control File and datafiles?

Partitions can't be made read only, but different partitions can be in separate tablespaces, and tablespaces can be made read only. And that's perfectly legitimate -yes, the SCN is in the header of the datafiles of that tablespace (and in the Control File), but if the tablespace is flagged as being read only then it is permissible for the SCN of that tablespace to lag behind that of the rest of the database without it provoking Oracle into thinking that recovery is required.

> - a cold backup strategy of whole database each day is too much, but I
 could
> backup with database closed during the night. Would you recomend a cold
> backup or hot (incremantal)?

Hot backups are not necessarily incremental. They are only incremental if you specifically get RMAN (or a similar tool) to make them so. Ordinarily, most people would take complete hot backups, a tablespace at a time -the thing about hot backups is that you can take 1 tablespace on Monday, another on Tuesday, another on Wednesday and so on (maybe that's what you meant by "incremental"). Not a problem -except that you'd best make sure you have plenty of storage for the archives required for this sort of approach. To guarantee total data recovery in such a scenario, you *must* retain all archives since the start of the backup cycle. If it takes you a week to complete your backup, you need to keep 1 week's worth of archives ready for emergencies *as a minimum*. And if speed of recovery is important to you, that entire week's worth of archives needs to be retained on a local disk, as well as backed up to tape.

Cold backups are easy. And, given the presence of archives, you could do those bit by bit over a number of days, too.

The big difference between hot and cold backups when both would take place in the presence of archives is that hot backups will flood your redo subsystem with masses of block-sized redo. Cold backups won't. If your redo system is generously provisioned, maybe the hot stuff won't worry you. If it isn't, then consider cold ones -or use RMAN, since that doesn't generate block-sized redo even when used hot.

The only other thing I'd wonder about is using 8.0.6. If this is being designed ab initio, why not use 8.1.6 or 8.1.7? It doesn't make any difference to this particular discussion, but the later versions have new partitioning features which may be of interest to you (ie, hash partitions and composite partitions).

Regards
HJR
>
> Thanks alot,
> Alex.
>
>
>
>
>
> ----- Posted via NewsOne.Net: Free (anonymous) Usenet News via the
 eb -----
> http://newsone.net/ -- Free reading and anonymous posting to 60,000+
 groups
> NewsOne.Net prohibits users from posting spam. If this or other posts
> made through NewsOne.Net violate posting guidelines, email
abuse_at_newsone.net Received on Wed Feb 14 2001 - 17:39:15 CST