DataBase Administration
Hello everybody.
We have an Oracle 8i DataBase of aprox. 30 GigaBytes. There are 3 big tables using about 50 % of that space, and our DBA is planning to move these tables to another database, to make the administration and backup procedure easier... but I'm not sure about that.
Is that a good idea ?
what would you suggest ?
Thanks in advance.
Software/Hardware used:
Software/Hardware used:
ASKED:
January 20, 2006 12:39 PM
UPDATED:
January 23, 2006 4:22 PM
Answer Wiki:
It really depends on how you use the data.
If there are foreign keys to of from these tables to other tables that he is NOT moving - then you're going to lose any hope of maintaining integrity.
If these tables are frequently accessed in the same session, then you'll have to switch to db links with their own set of issues. Performance may suffer, and queries can be tuned to minimize the effect of the split.
As far as admin and backup, I think it's easier to take care of fewer instances not more. Whenever given an opportunity - we consolidate. It makes more sense to move these tables into one or more tablespaces if they aren't already.
The DBA might have other reasons to split the data off, but you haven't really given us any compelling argument for moving the data to another database.
I really hate to second guess someone else without having all the facts, but the only reason I can think of to split the data up - is if these 3 tables are NOT associated with the other 30 gig of data AND the 2 databases have different uptime requirements.
Let's say the set of smaller tables is an entire application that HAS TO BE UP all the time and perform reasonably well. The 3 big guys don't have to be up all the time, and there are expensive queries running against them all the time. THEN it makes sense to split them off, so they don't interfere with the critical application.
I hope this helps.
((MrO))
To see all answers submitted to the Answer Wiki: View Answer History.
Consider table partitioning for the three large tables. That splits a large table into partitions that are easier to back up and manage. For example, if the data can be partitioned by date, then each month would be a partition. “Older” partitions can be made read-only and backed up just once. Only new partitions need be backed up regularly.
I am sure your DBA has valid reasons for the split. That being said, I would think that unless you are doing only cold backups, there should be no problem with that small of a database for backup/recovery or administration.
Having been the DBA for databases ranging in the multiple TeraByte range, I had no problems doing a daily backup (hot backup – archivelog mode) of the databases (over 35 instances of various size from 36 Gbytes on up). The trick is to script your backups so you don’t step all over your I/O toes.
I also used both the BMC SQL BackTrack application and Rman. SQL BackTrack is more expensive (Rman is free), but it also makes the administrative job of the backup/recovery process a lot easier (hope I didn’t open a tangle of worms there)
There are inherent problems when you split databases arpart – I/O contention, additional binary size (if the second is going onto a different server) and administration of the database itself. You may want to suggest partitioning (as stated in an earlier response) or just creating a different schema and backing up one schema at a time or just creating a new tablespace for the larger tables.
Hope I helped some.
I agree with the first respondent MrOracle on this thread entirely. Its not best practice to split off and move tables to another database based purely on a space constraint premise unless the business processes requiring this data is not relying on it for fast access or some other BUSINESS compelling reason. This proposed strategy by your DBA warrants further review and perhaps as the third respondent suggested, other backup scripts and methodologies /strategies should be reviewed first before attempting to move off tables and data files. Your DBAs proposed strategy will simply add to the maintenance workload particularly in the future if your organization plans on upgrading, adding, doing other things with your applications.