Help me clear my doubts in choosing a backup mode.
My database(its going to be a 24 x 7) contains 276 tablespaces. I am interested in having backup of only one of the tablespaces.
Cold backup is not being considered. I wanted to do online backup for the particular tablespace, and also carryout an export at table level for the concerned tables in that tablespace.
My doubt is, what is the performance degradation when i run the database in Archieve mode, just for the sake of one tablespace which forms less than 1% of the total database size?And, if it is considerable, how good is to depend on export/import as a primary way of backup?
Don't rely on export/import for a production system as a method of backup. Period.
The performance degradation of a hot-backup depends on the amount of activity that is going on in the database. If you do your backup when the activity is low, you will see a small performance difference (<10%).
Running in archivelog mode will barely impact performance if you have fast enough devices. If your archives are kept on the same disk as your TEMP/RBS/Redo/DATA, then it will impact performance significantly.
I am assuming you have a reason for only backing up one tablespace... I couldn't justify it to myself, but it sounds like you thought about this...
Most of the tablespaces are for partitioned tables(6 tables , 45 partitions each). Data in these tables will become readonly after 3 days and on 46th day will be deleted totally. The activity in these tables will be very heavy insertions on the first day and done using loader. In case something goes wrong, we have the data and need to reload again.
But for one table this is not so. Its going to be a table with less deletes, but insertions and updates will be there.I am keeping this on a separate tablespace, so that it can be maintained separately.
Thanks for your suggestions, I guess there is really no other way than what you have suggested.