OraFAQ Forum: Backup & Recovery » RMAN, how to schedule a backup of the huge table?

Home » RDBMS Server » Backup & Recovery » RMAN, how to schedule a backup of the huge table?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

RMAN, how to schedule a backup of the huge table? [message #207138]

Mon, 04 December 2006 04:46

dendroid66
Messages: 4
Registered: September 2006
Location: Belgium

Junior Member

Hello,

I have a huge table (300 Gb) which stands for keeping some statistical data. It is partitioned to keep last 90 days statistic, partitions are ranged by time, so one day - one partition, located in one TABLESPACE. With time being all outdated partitions would be dropped and the new ones would be created.

1. Could you advise which kind of backup can be applied that the daily backup will not last more than two, maximum 3 hours (I have SUN V440).
2. Should I do a refactoring of my data model? (like putting each partition in each own TABLESPACE).
3. Can I use TSPITR without FULL DB BACKUP?
4. Is it possible to backup only a certain number of partitions?

P.S. For instance, I can do daily backup of 3,3Gb which lasts
about 20 min. with RMAN to local disks.

Report message to a moderator

Re: RMAN, how to schedule a backup of the huge table? [message #207164 is a reply to message #207138]

Mon, 04 December 2006 06:31

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

hi,

Is it possible to backup only a certain number of partitions?

Through export utilities you can .

Partition-Level Export
In partition-level Export, you can export one or more specified partitions or subpartitions of a table. You can only perform a partition-level export in Table mode.

partition_name indicates that the export is a partition-level Export. Partition-level Export lets you export one or more specified partitions or subpartitions within a table.
The syntax you use to specify the preceding is in the form:

schemaname.tablename:partition_name
schemaname.tablename:subpartition_name

If you use tablename:partition_name, the specified table must be partitioned, and partition_name must be the name of one of its partitions or subpartitions. If the specified table is not partitioned, the partition_name is ignored and the entire table is exported.

Report message to a moderator

Re: RMAN, how to schedule a backup of the huge table? [message #207177 is a reply to message #207138]

Mon, 04 December 2006 07:32

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Export is not a real backup. I would not consider it for a large database.

With RMAN you can individually backup tablespaces. Since each partition has its own tablespace, you can backup individually.
>>one partition, located in one TABLESPACE.
>>2. Should I do a refactoring of my data model? (like putting each partition in each own TABLESPACE).
I thought you are having each partition in a seperate tablespace.
This is the good way to. Idea is to divide the administrative burden across multiple physical files.

What is your Oracle version?
There is big time difference between 9i RMAN and 10g RMAN.
In 10g, you can enable the change tracking mechanism and do an incremental backup.
I am backing my 700 partitioned database within 1 hour incrementally everday.
IT depends on your strategy between level 0/leve1 backups (in 10g, you can merge backups).
Whatever the versions may be, your configuration / available hardware/ RAID will determine the performance.

Report message to a moderator

Re: RMAN, how to schedule a backup of the huge table? [message #207181 is a reply to message #207177]

Mon, 04 December 2006 08:06

dendroid66
Messages: 4
Registered: September 2006
Location: Belgium

Junior Member

I have Oracle 9i.

Ok, suppose each partition is located in a separate tablespace,
like TS0001,TS0002, ... ,TSXXXX.

CREATE TABLESPACE TS0001
DATAFILE 'ts0001.dbf' SIZE 10M DEFAULT STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0) ONLINE;
CREATE TABLESPACE TS0002 DATAFILE 'ts0002.dbf' SIZE 10M
DEFAULT STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0) ONLINE;
CREATE TABLESPACE TS0003
DATAFILE 'ts0003.dbf' SIZE 10M DEFAULT STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0) ONLINE;

CREATE TABLE tstest
(ri NUMBER(10), updt NUMBER(10)) PARTITION BY RANGE (updt)
(PARTITION P01112006 VALUES LESS THAN (10000) TABLESPACE TS0001 ,
PARTITION P02112006 VALUES LESS THAN (20000) TABLESPACE TS0002 ,
PARTITION P03112006 VALUES LESS THAN (30000) TABLESPACE TS0003 );

The data is not supposed to be modifed (only INSERT and SELECT).
All the partitions prior to the current date are only for SELECT (but actually they are not in the READONLY state).

So I have a couple of questions.

1. What should be done to backup my "tstest" table ?
2. Is it sufficent to do the next as a daily backup procedure:
rman nocatalog
connect target;
run {
allocate channel ch1 type disk format='bu1/%u.%p';
backup tablespace TS0001; #TS0001 is a subject of calculation...
copy
datafile 26 to 'bu1/df_1.f',
current controlfile to 'bu1/cf.f';
}

How to determine the datafile number automatically (26 in this case)?

3. Do I really need my control file being backed up daily (to restore the table structure)?

4. How do I restore the data after erroneously dropped tablespace, table or partition ?

5. Is there a way to do a backup incrementally, so the first time it will do the full table backup, next time - only the difference

6. How to restore with p.5 (if possible) taking into account p.4.

Report message to a moderator

Re: RMAN, how to schedule a backup of the huge table? [message #207200 is a reply to message #207181]

Mon, 04 December 2006 10:14

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

I suggest you start reading documentation.
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96566/toc.htm
>>1. What should be done to backup my "tstest" table ?
Backup the tablespace/database
>>2. ....
>>backup tablespace TS0001; #TS0001 is a subject of calculation...
>>copy datafile 26 to 'bu1/df_1.f',
>>current controlfile to 'bu1/cf.f';
First you are backing the tablespace.
next you are doing an image file copy. both are different.
Image copy is just FULL copy of the datafile.
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96566/rcmquick.htm#440799
I would prefer to autoconfigure controlfile backups.
>>How to determine the datafile number automatically (26 in this case)?
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96566/rcmbackp.htm#422767

>>3. Do I really need my control file being backed up daily (to restore the table structure)?
Yes. But control file has more to do than just the table structure.
READ DOCUMENTATION.
It is very dangerous to get involved in backup/restore activities without proper know-how on
basics.
>>4. How do I restore the data after erroneously dropped tablespace, table or partition ?
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96566/rcmconc2.htm#464575
>>5. Is there a way to do a backup incrementally, so the first time it will do the full table backup, next time - only the difference

http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96566/rcmbackp.htm#447156

>> 6. How to restore with p.5 (if possible) taking into account p.4.
What is p5 / p4?

Report message to a moderator

Re: RMAN, how to schedule a backup of the huge table? [message #290624 is a reply to message #207138]

Sun, 30 December 2007 22:20

rohitpatel9999
Messages: 1
Registered: December 2007

Junior Member

dendroid66

Just need some guidance/info - For RMAN backup of the huge table.
Which backup hardware do you use ?

Thanks
Rohit

Report message to a moderator

Re: RMAN, how to schedule a backup of the huge table? [message #290645 is a reply to message #290624]

Mon, 31 December 2007 00:33

Michel Cadot
Messages: 68651
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

dendroid66 did not connect to the site since more than a year, so I don't think you'll get an answer.

Regards
Michel

Report message to a moderator

Previous Topic:	scripting setnew name for datafile 1 to
Next Topic:	Automate Backup/Restore

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 15 20:48:30 CDT 2024