Re: Data Purging Strategy
Hello Jay How about building an historical DB and keeping the data there. It will not overload the production instance, will be available online if you need something, you will migrate it to new versions of Oracle so compatibility will not be an issue and you can implement table changes on the historical data so the structure will remain the same as in production. We are doing it in ADABAS on the mainframe. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, November 07, 2002 11:24 PM Well, if worst comes to worst we can always install an earlier version on a box and import it there. But the reason we can't get more storage approved still has me shaking my head... -Original Message- Sent: Thursday, November 07, 2002 2:19 PM To: Multiple recipients of list ORACLE-L Jay, just make sure you are not around when, after several Oracle upgrades, and they want to import one of these files back that they discover that the current release of import can no longer read the older version of the .dmp file. now what are these senior damagers going to do? blame the DBA, that's what! duck and cover... duck and cover... Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, November 07, 2002 1:55 PM To: Multiple recipients of list ORACLE-L FWIW, what we just implemented (because senior management refuses to approve additional storage on the grounds that making the database larger will affect performance - aaargh!) is 1) Confirmed with business how long data needs to be online for various tables (they're all partitioned so that makes it a lot easier) 2) Export partitions older than that once/month (this is generated off a table that lists each partitioned table and how long data should be kep) 3) After confirming that all export files are valid we drop the old partitions (this will be done by script but is being done manually for the first few months) 4) Leave dmp files on server for 2 end of months (our end of month backup tapes are stored for 7 years) 5) Maintain a table in database saying what exported partitions are on what date's tapes And I really long for the days in this company when senior management made technical decisions by asking the technical people instead of just making things up... Jay Miller -Original Message- Sent: Wednesday, November 06, 2002 11:54 AM To: Multiple recipients of list ORACLE-L Someone asked about this 3 weeks ago. Here's my take on archiving data. I don't expect everyone to agree with this, but nonetheless, I have an opinion. :) Here's an email from last month. You can undoubtedly find some other ideas on this by searching the archives of this list at fatcity.com Jared == I'm not a proponent of purging data. Unless of course, you expect to never see it again. That word 'archive' rolls of the tongues of managers and consultants pretty easily, but what's behind it? There are a few gotchas with purging and archiving. Let's assume you have some 3 year old data that you need to see again, and it has been purged. Here are some of the possible problems: * Your backup tapes are corrupted * Your new backup hardware can't read the old tapes * Your software no longer understands the format that the data is in. * You have the correct software, but it won't work on the current version of OS on your hardware. * The data format/software/whatever is not well documented * The employees that understood the data 3 years ago have been laid off. * ... lots more stuff Read Bryon Bergeron's Dark Ages II: When the Digital Data Die http://www.powells.com/cgi-bin/biblio?inkey=2-0130661074-0 Perhaps much better than archiving the data, is to stick with the idea of moving it to another database, and using lots of cheap disk storage (NAS) or a heirarchical file system to store it. The point being that if it's online somewhere, it will be maintained. Don't purge it till Finance, HR, the IRS and any other stakeholder says it's ok. Only then purge it and archive it to offline tape with the knowledge that you may never see that data again. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 01:13 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Data Purging Strategy Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we have designed for this. When the retention period is
RE: Data Purging Strategy
Another poor man's solution would be to unload the tables into flat files and attach to them as needed using Oracle's external table feature from 9i. That solution should hold for quite a while into the future since the external table function is very much like SQL*Loader, which is so integral to so many systems that Oracle is not going to think about making it 'go away'. You would still run into problems if there is some substantive change that makes the external tables from 9i invalid, but that still leaves you with flat files that you can load back into the DB with SQL*Loader. Chris Gait On 6 Nov 2002 at 6:43, Conboy, Jim wrote: Date sent: Wed, 06 Nov 2002 06:43:38 -0800 To: Multiple recipients of list ORACLE-L ORACLE- [EMAIL PROTECTED] Send reply to: [EMAIL PROTECTED] Organization: Fat City Network Services, San Diego, California A poor man's solution might be to load the offline database with appropriate data, then do a tablespace export and store the results on CD labelled by date. Restoring needed data would entail a tablespace import of stuff from the appropriate CD into the offline DB. I'm sure here's some gotchas involved but some variation on that theme might work. Jim -Original Message- Sent: Wednesday, November 06, 2002 8:49 AM To: Multiple recipients of list ORACLE-L This is a data-archival requirement, not a data-purge requirement. It only resembles a purge requirement based on the multiple-database-migration strategy you outlined. There are alternatives... Depending on the volume of data in your database and your availability requirements, implementing table- and index-partitioning will likely be crucial. One strategy is to have the most-active tables partitioned by a date column and have different sets of these partitions reside in time-variant tablespaces. With this arrangement, you can archive data to tape by simply setting the archived tablespaces to READ ONLY and then migrating them to tape-based (instead of disk-based) file-systems and bringing them back online. Legato has this file-system technology (recently purchased) and there is a share-ware product called SAMFS which is an HSM (hierarchical storage mgmt) filesystem used by some vendors (i.e. StorageTek, etc). By setting tablespaces to READ ONLY it becomes very easy to move them from disk to tape while retaining them within the same original database, simplifying the task of later retrieval (which is really important). Of course, Oracle's partitioning option is enormously expensive, but in this case it is a matter of the upfront license costs (with reduced downstream implementation costs due to simplicity) versus a large downstream application-development cost. In this situation, I think roughly offsets everything. Since I'm not spending the money, I can afford such a calculation... :-) With the various storage technologies available, a single database can straddle several simultaneously, optimizing performance or cost as needed. Some files might reside on solid-state NVRAM disk, some on SAN-based disk, some on NAS-based storage, and then finally reside in archive media file-systems such as tape or magneto-optical based HSM file-systems. - Original Message - To: Multiple recipients of list ORACLE-L mailto:ORACLE-L;fatcity.com Sent: Wednesday, November 06, 2002 2:13 AM Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we have designed for this. When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database. In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates. i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables. Regards Prem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you
RE: Data Purging Strategy
Jay, Remind the management that in the future there might also ba a change of hardware and then the backups on tape could possible be useless and unreadable by the new tape drives. If possible save the data to a text delimited file and save the file. That wouls insure you that you would always be able to at least read the information if needed. I have a lot of data( from 1993- to - today) that someday will be archived , I hope, and I can remove from the system. I will be saving it in text format in CD's so it can be accessed if needed. We also are changing to a new server and OS format. The old backup tapes are scrap now. Planning on your part could be very helpfull down the road. Ron [EMAIL PROTECTED] 11/07/02 04:24PM Well, if worst comes to worst we can always install an earlier version on a box and import it there. But the reason we can't get more storage approved still has me shaking my head... -Original Message- Sent: Thursday, November 07, 2002 2:19 PM To: Multiple recipients of list ORACLE-L Jay, just make sure you are not around when, after several Oracle upgrades, and they want to import one of these files back that they discover that the current release of import can no longer read the older version of the .dmp file. now what are these senior damagers going to do? blame the DBA, that's what! duck and cover... duck and cover... Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, November 07, 2002 1:55 PM To: Multiple recipients of list ORACLE-L FWIW, what we just implemented (because senior management refuses to approve additional storage on the grounds that making the database larger will affect performance - aaargh!) is 1) Confirmed with business how long data needs to be online for various tables (they're all partitioned so that makes it a lot easier) 2) Export partitions older than that once/month (this is generated off a table that lists each partitioned table and how long data should be kep) 3) After confirming that all export files are valid we drop the old partitions (this will be done by script but is being done manually for the first few months) 4) Leave dmp files on server for 2 end of months (our end of month backup tapes are stored for 7 years) 5) Maintain a table in database saying what exported partitions are on what date's tapes And I really long for the days in this company when senior management made technical decisions by asking the technical people instead of just making things up... Jay Miller -Original Message- Sent: Wednesday, November 06, 2002 11:54 AM To: Multiple recipients of list ORACLE-L Someone asked about this 3 weeks ago. Here's my take on archiving data. I don't expect everyone to agree with this, but nonetheless, I have an opinion. :) Here's an email from last month. You can undoubtedly find some other ideas on this by searching the archives of this list at fatcity.com Jared == I'm not a proponent of purging data. Unless of course, you expect to never see it again. That word 'archive' rolls of the tongues of managers and consultants pretty easily, but what's behind it? There are a few gotchas with purging and archiving. Let's assume you have some 3 year old data that you need to see again, and it has been purged. Here are some of the possible problems: * Your backup tapes are corrupted * Your new backup hardware can't read the old tapes * Your software no longer understands the format that the data is in. * You have the correct software, but it won't work on the current version of OS on your hardware. * The data format/software/whatever is not well documented * The employees that understood the data 3 years ago have been laid off. * ... lots more stuff Read Bryon Bergeron's Dark Ages II: When the Digital Data Die http://www.powells.com/cgi-bin/biblio?inkey=2-0130661074-0 Perhaps much better than archiving the data, is to stick with the idea of moving it to another database, and using lots of cheap disk storage (NAS) or a heirarchical file system to store it. The point being that if it's online somewhere, it will be maintained. Don't purge it till Finance, HR, the IRS and any other stakeholder says it's ok. Only then purge it and archive it to offline tape with the knowledge that you may never see that data again. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 01:13 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Data Purging Strategy Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve
Re: Data Purging Strategy
Ron, Under ideal conditions, that is, controlled temperature, humidity and atmosphere, a CD has a lifespan of 30-200 years. In typical conditions, 5-50 years. CD's stored in a computer room might only last 10 years. In someone's desk, maybe only 5 years. On the visor of your car, probably not that long. ;) Jared On Friday 08 November 2002 04:48, Ron Rogers wrote: Jay, Remind the management that in the future there might also ba a change of hardware and then the backups on tape could possible be useless and unreadable by the new tape drives. If possible save the data to a text delimited file and save the file. That wouls insure you that you would always be able to at least read the information if needed. I have a lot of data( from 1993- to - today) that someday will be archived , I hope, and I can remove from the system. I will be saving it in text format in CD's so it can be accessed if needed. We also are changing to a new server and OS format. The old backup tapes are scrap now. Planning on your part could be very helpfull down the road. Ron [EMAIL PROTECTED] 11/07/02 04:24PM Well, if worst comes to worst we can always install an earlier version on a box and import it there. But the reason we can't get more storage approved still has me shaking my head... -Original Message- Sent: Thursday, November 07, 2002 2:19 PM To: Multiple recipients of list ORACLE-L Jay, just make sure you are not around when, after several Oracle upgrades, and they want to import one of these files back that they discover that the current release of import can no longer read the older version of the .dmp file. now what are these senior damagers going to do? blame the DBA, that's what! duck and cover... duck and cover... Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, November 07, 2002 1:55 PM To: Multiple recipients of list ORACLE-L FWIW, what we just implemented (because senior management refuses to approve additional storage on the grounds that making the database larger will affect performance - aaargh!) is 1) Confirmed with business how long data needs to be online for various tables (they're all partitioned so that makes it a lot easier) 2) Export partitions older than that once/month (this is generated off a table that lists each partitioned table and how long data should be kep) 3) After confirming that all export files are valid we drop the old partitions (this will be done by script but is being done manually for the first few months) 4) Leave dmp files on server for 2 end of months (our end of month backup tapes are stored for 7 years) 5) Maintain a table in database saying what exported partitions are on what date's tapes And I really long for the days in this company when senior management made technical decisions by asking the technical people instead of just making things up... Jay Miller -Original Message- Sent: Wednesday, November 06, 2002 11:54 AM To: Multiple recipients of list ORACLE-L Someone asked about this 3 weeks ago. Here's my take on archiving data. I don't expect everyone to agree with this, but nonetheless, I have an opinion. :) Here's an email from last month. You can undoubtedly find some other ideas on this by searching the archives of this list at fatcity.com Jared == I'm not a proponent of purging data. Unless of course, you expect to never see it again. That word 'archive' rolls of the tongues of managers and consultants pretty easily, but what's behind it? There are a few gotchas with purging and archiving. Let's assume you have some 3 year old data that you need to see again, and it has been purged. Here are some of the possible problems: * Your backup tapes are corrupted * Your new backup hardware can't read the old tapes * Your software no longer understands the format that the data is in. * You have the correct software, but it won't work on the current version of OS on your hardware. * The data format/software/whatever is not well documented * The employees that understood the data 3 years ago have been laid off. * ... lots more stuff Read Bryon Bergeron's Dark Ages II: When the Digital Data Die http://www.powells.com/cgi-bin/biblio?inkey=2-0130661074-0 Perhaps much better than archiving the data, is to stick with the idea of moving it to another database, and using lots of cheap disk storage (NAS) or a heirarchical file system to store it. The point being that if it's online somewhere, it will be maintained. Don't purge it till Finance, HR, the IRS and any other stakeholder says it's ok. Only then purge it and archive it to offline tape with the knowledge that you may never see that data again. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 01:13 AM Please respond to ORACLE-L
RE: Data Purging Strategy
Burnt mud? Isn't that all scotch? Scott Shafer San Antonio, TX 210.581.6217 -Original Message- From: Steve McClure [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, November 06, 2002 4:34 PM To: Multiple recipients of list ORACLE-L Subject: RE: Data Purging Strategy Sherry Finish? I thought you liked scotch that tasted like burnt mud? -Original Message- [EMAIL PROTECTED] Sent: Wednesday, November 06, 2002 10:55 AM To: Multiple recipients of list ORACLE-L That reminds me: Mark, your annual stipend is due. Make it a case of Glenmorangie this time, Sherry finish. :) Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 07:56 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Data Purging Strategy Hey Dennis, Mark Leith is the only person on this list allowed to mention 3rd party products. I am sure he bought the franchise from Jared :) John -Original Message- Sent: 06 November 2002 14:15 To: Multiple recipients of list ORACLE-L Prem - You are receiving some excellent advice from Tom and Tim. I would mention two items in addition: - If you ever hope to re-use the data you archive off-line, you must also archive all the related tables, because after all, this is a RELATIONAL database. - PrincetonSoftech has a product Active Archiving that looks pretty good from the demos I've seen. I haven't used it myself. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 06, 2002 6:54 AM To: Multiple recipients of list ORACLE-L Prem, I would re-visit the requirement. Why do you feel the need to delete the data from the database? What is the purpose for this type of requirement? It would be far easier to modify the requirement than to do what you are thinking of doing. Adding columns to database tables indicating that a record has passed it's retention policy and thus, is not included in queries, would be a much easier solution. Or, simply moving these records to historical tables in the database - and NOT deleting them from the system - is a much better solution. The data is always accessible and not available in the current tables. And you will not be playing the get the data from tape and reload it game with all of it's problems (writing an offload program, table structure changes offload program versions). Try and keep this as simple as possible. Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 06, 2002 4:13 AM To: Multiple recipients of list ORACLE-L Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we have designed for this. When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database. In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates. i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables. Regards Prem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name
RE: Data Purging Strategy
Title: RE: Data Purging Strategy And finding something to play that CD in 50 years, much less understand the data format, will be like finding something to play an old 78 now. 78. How's that for showing my age! I even had a 16 once. And a 4-track (pre 8-track). Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Jared Still [SMTP:[EMAIL PROTECTED]] Ron, Under ideal conditions, that is, controlled temperature, humidity and atmosphere, a CD has a lifespan of 30-200 years. In typical conditions, 5-50 years. CD's stored in a computer room might only last 10 years. In someone's desk, maybe only 5 years. On the visor of your car, probably not that long. ;) Jared
Re: Data Purging Strategy
Hair of the dog! I wonder if that would help my sorry butt. Might have to leave for the airport early and sit at the bar until the stupor clears. Hope I don't miss my plane. D [EMAIL PROTECTED] wrote: Burnt mud? Isn't that all scotch? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Darlene Marley INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Data Purging Strategy
I went to one meeting where someone from another DOE lab said they needed to store some data on media which would last 10,000 years. I suggested chisels and stone tablets :) Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Friday, November 08, 2002 9:14 AM To: Multiple recipients of list ORACLE-L Ron, Under ideal conditions, that is, controlled temperature, humidity and atmosphere, a CD has a lifespan of 30-200 years. In typical conditions, 5-50 years. CD's stored in a computer room might only last 10 years. In someone's desk, maybe only 5 years. On the visor of your car, probably not that long. ;) Jared On Friday 08 November 2002 04:48, Ron Rogers wrote: Jay, Remind the management that in the future there might also ba a change of hardware and then the backups on tape could possible be useless and unreadable by the new tape drives. If possible save the data to a text delimited file and save the file. That wouls insure you that you would always be able to at least read the information if needed. I have a lot of data( from 1993- to - today) that someday will be archived , I hope, and I can remove from the system. I will be saving it in text format in CD's so it can be accessed if needed. We also are changing to a new server and OS format. The old backup tapes are scrap now. Planning on your part could be very helpfull down the road. Ron [EMAIL PROTECTED] 11/07/02 04:24PM Well, if worst comes to worst we can always install an earlier version on a box and import it there. But the reason we can't get more storage approved still has me shaking my head... -Original Message- Sent: Thursday, November 07, 2002 2:19 PM To: Multiple recipients of list ORACLE-L Jay, just make sure you are not around when, after several Oracle upgrades, and they want to import one of these files back that they discover that the current release of import can no longer read the older version of the .dmp file. now what are these senior damagers going to do? blame the DBA, that's what! duck and cover... duck and cover... Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, November 07, 2002 1:55 PM To: Multiple recipients of list ORACLE-L FWIW, what we just implemented (because senior management refuses to approve additional storage on the grounds that making the database larger will affect performance - aaargh!) is 1) Confirmed with business how long data needs to be online for various tables (they're all partitioned so that makes it a lot easier) 2) Export partitions older than that once/month (this is generated off a table that lists each partitioned table and how long data should be kep) 3) After confirming that all export files are valid we drop the old partitions (this will be done by script but is being done manually for the first few months) 4) Leave dmp files on server for 2 end of months (our end of month backup tapes are stored for 7 years) 5) Maintain a table in database saying what exported partitions are on what date's tapes And I really long for the days in this company when senior management made technical decisions by asking the technical people instead of just making things up... Jay Miller -Original Message- Sent: Wednesday, November 06, 2002 11:54 AM To: Multiple recipients of list ORACLE-L Someone asked about this 3 weeks ago. Here's my take on archiving data. I don't expect everyone to agree with this, but nonetheless, I have an opinion. :) Here's an email from last month. You can undoubtedly find some other ideas on this by searching the archives of this list at fatcity.com Jared == I'm not a proponent of purging data. Unless of course, you expect to never see it again. That word 'archive' rolls of the tongues of managers and consultants pretty easily, but what's behind it? There are a few gotchas with purging and archiving. Let's assume you have some 3 year old data that you need to see again, and it has been purged. Here are some of the possible problems: * Your backup tapes are corrupted * Your new backup hardware can't read the old tapes * Your software no longer understands the format that the data is in. * You have the correct software, but it won't work on the current version of OS on your hardware. * The data format/software/whatever is not well documented * The employees that understood the data 3 years ago have been laid off. * ... lots more stuff Read Bryon Bergeron's Dark Ages II: When the Digital Data Die http://www.powells.com/cgi-bin/biblio?inkey=2-0130661074-0 Perhaps much better than archiving the data, is to stick with the idea of moving it to another database, and using lots of cheap disk storage (NAS) or a heirarchical file system to store it. The point
RE: Data Purging Strategy
Scott, You're walking where angels fear to tread. Watch it. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/08/2002 09:25 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Data Purging Strategy Burnt mud? Isn't that all scotch? Scott Shafer San Antonio, TX 210.581.6217 -Original Message- From: Steve McClure [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, November 06, 2002 4:34 PM To:Multiple recipients of list ORACLE-L Subject: RE: Data Purging Strategy Sherry Finish? I thought you liked scotch that tasted like burnt mud? -Original Message- [EMAIL PROTECTED] Sent: Wednesday, November 06, 2002 10:55 AM To: Multiple recipients of list ORACLE-L That reminds me: Mark, your annual stipend is due. Make it a case of Glenmorangie this time, Sherry finish. :) Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 07:56 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Data Purging Strategy Hey Dennis, Mark Leith is the only person on this list allowed to mention 3rd party products. I am sure he bought the franchise from Jared :) John -Original Message- Sent: 06 November 2002 14:15 To: Multiple recipients of list ORACLE-L Prem - You are receiving some excellent advice from Tom and Tim. I would mention two items in addition: - If you ever hope to re-use the data you archive off-line, you must also archive all the related tables, because after all, this is a RELATIONAL database. - PrincetonSoftech has a product Active Archiving that looks pretty good from the demos I've seen. I haven't used it myself. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 06, 2002 6:54 AM To: Multiple recipients of list ORACLE-L Prem, I would re-visit the requirement. Why do you feel the need to delete the data from the database? What is the purpose for this type of requirement? It would be far easier to modify the requirement than to do what you are thinking of doing. Adding columns to database tables indicating that a record has passed it's retention policy and thus, is not included in queries, would be a much easier solution. Or, simply moving these records to historical tables in the database - and NOT deleting them from the system - is a much better solution. The data is always accessible and not available in the current tables. And you will not be playing the get the data from tape and reload it game with all of it's problems (writing an offload program, table structure changes offload program versions). Try and keep this as simple as possible. Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 06, 2002 4:13 AM To: Multiple recipients of list ORACLE-L Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we have designed for this. When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database. In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates. i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables. Regards Prem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San
RE: Data Purging Strategy
water constantly dripping on stone, will wear away the stone. And an earthquake, with debris falling on that stone could shatter it. I don't think even that will work --- MacGregor, Ian A. [EMAIL PROTECTED] wrote: I went to one meeting where someone from another DOE lab said they needed to store some data on media which would last 10,000 years. I suggested chisels and stone tablets :) Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Friday, November 08, 2002 9:14 AM To: Multiple recipients of list ORACLE-L Ron, Under ideal conditions, that is, controlled temperature, humidity and atmosphere, a CD has a lifespan of 30-200 years. In typical conditions, 5-50 years. CD's stored in a computer room might only last 10 years. In someone's desk, maybe only 5 years. On the visor of your car, probably not that long. ;) Jared On Friday 08 November 2002 04:48, Ron Rogers wrote: Jay, Remind the management that in the future there might also ba a change of hardware and then the backups on tape could possible be useless and unreadable by the new tape drives. If possible save the data to a text delimited file and save the file. That wouls insure you that you would always be able to at least read the information if needed. I have a lot of data( from 1993- to - today) that someday will be archived , I hope, and I can remove from the system. I will be saving it in text format in CD's so it can be accessed if needed. We also are changing to a new server and OS format. The old backup tapes are scrap now. Planning on your part could be very helpfull down the road. Ron [EMAIL PROTECTED] 11/07/02 04:24PM Well, if worst comes to worst we can always install an earlier version on a box and import it there. But the reason we can't get more storage approved still has me shaking my head... -Original Message- Sent: Thursday, November 07, 2002 2:19 PM To: Multiple recipients of list ORACLE-L Jay, just make sure you are not around when, after several Oracle upgrades, and they want to import one of these files back that they discover that the current release of import can no longer read the older version of the .dmp file. now what are these senior damagers going to do? blame the DBA, that's what! duck and cover... duck and cover... Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, November 07, 2002 1:55 PM To: Multiple recipients of list ORACLE-L FWIW, what we just implemented (because senior management refuses to approve additional storage on the grounds that making the database larger will affect performance - aaargh!) is 1) Confirmed with business how long data needs to be online for various tables (they're all partitioned so that makes it a lot easier) 2) Export partitions older than that once/month (this is generated off a table that lists each partitioned table and how long data should be kep) 3) After confirming that all export files are valid we drop the old partitions (this will be done by script but is being done manually for the first few months) 4) Leave dmp files on server for 2 end of months (our end of month backup tapes are stored for 7 years) 5) Maintain a table in database saying what exported partitions are on what date's tapes And I really long for the days in this company when senior management made technical decisions by asking the technical people instead of just making things up... Jay Miller -Original Message- Sent: Wednesday, November 06, 2002 11:54 AM To: Multiple recipients of list ORACLE-L Someone asked about this 3 weeks ago. Here's my take on archiving data. I don't expect everyone to agree with this, but nonetheless, I have an opinion. :) Here's an email from last month. You can undoubtedly find some other ideas on this by searching the archives of this list at fatcity.com Jared == I'm not a proponent of purging data. Unless of course, you expect to never see it again. That word 'archive' rolls of the tongues of managers and consultants pretty easily, but what's behind it? There are a few gotchas with purging and archiving. Let's assume you have some 3 year old data that you need to see again, and it has been purged. Here are some of the possible problems: * Your backup tapes are corrupted * Your new backup hardware can't read the old tapes * Your software no longer understands the format that the data is in. * You have the correct software, but it won't work on the current version of OS on your hardware. * The data format/software/whatever is not well documented * The employees that understood the data 3 years ago
RE: Data Purging Strategy
Ian - I have seen some of that in the general media. One problem is that if the information is extremely miniaturized so a lot of information can be stored in a small area, then some who discovers one of your artifacts may not realize something is written on it. The first clay tablets with cuneiform writing were tossed because they were assumed to be decorative tiles. An ingenious solution was to write in a spiral pattern on a disk and make the outer row large enough to be easily readable with the naked eye, then gradually reduce the character size. I thought that was clever. obligatory Oracle reference Of course Larry feels that Oracle will still be available then. / obligatory Oracle reference Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, November 08, 2002 1:15 PM To: Multiple recipients of list ORACLE-L I went to one meeting where someone from another DOE lab said they needed to store some data on media which would last 10,000 years. I suggested chisels and stone tablets :) Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Friday, November 08, 2002 9:14 AM To: Multiple recipients of list ORACLE-L Ron, Under ideal conditions, that is, controlled temperature, humidity and atmosphere, a CD has a lifespan of 30-200 years. In typical conditions, 5-50 years. CD's stored in a computer room might only last 10 years. In someone's desk, maybe only 5 years. On the visor of your car, probably not that long. ;) Jared On Friday 08 November 2002 04:48, Ron Rogers wrote: Jay, Remind the management that in the future there might also ba a change of hardware and then the backups on tape could possible be useless and unreadable by the new tape drives. If possible save the data to a text delimited file and save the file. That wouls insure you that you would always be able to at least read the information if needed. I have a lot of data( from 1993- to - today) that someday will be archived , I hope, and I can remove from the system. I will be saving it in text format in CD's so it can be accessed if needed. We also are changing to a new server and OS format. The old backup tapes are scrap now. Planning on your part could be very helpfull down the road. Ron [EMAIL PROTECTED] 11/07/02 04:24PM Well, if worst comes to worst we can always install an earlier version on a box and import it there. But the reason we can't get more storage approved still has me shaking my head... -Original Message- Sent: Thursday, November 07, 2002 2:19 PM To: Multiple recipients of list ORACLE-L Jay, just make sure you are not around when, after several Oracle upgrades, and they want to import one of these files back that they discover that the current release of import can no longer read the older version of the .dmp file. now what are these senior damagers going to do? blame the DBA, that's what! duck and cover... duck and cover... Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, November 07, 2002 1:55 PM To: Multiple recipients of list ORACLE-L FWIW, what we just implemented (because senior management refuses to approve additional storage on the grounds that making the database larger will affect performance - aaargh!) is 1) Confirmed with business how long data needs to be online for various tables (they're all partitioned so that makes it a lot easier) 2) Export partitions older than that once/month (this is generated off a table that lists each partitioned table and how long data should be kep) 3) After confirming that all export files are valid we drop the old partitions (this will be done by script but is being done manually for the first few months) 4) Leave dmp files on server for 2 end of months (our end of month backup tapes are stored for 7 years) 5) Maintain a table in database saying what exported partitions are on what date's tapes And I really long for the days in this company when senior management made technical decisions by asking the technical people instead of just making things up... Jay Miller -Original Message- Sent: Wednesday, November 06, 2002 11:54 AM To: Multiple recipients of list ORACLE-L Someone asked about this 3 weeks ago. Here's my take on archiving data. I don't expect everyone to agree with this, but nonetheless, I have an opinion. :) Here's an email from last month. You can undoubtedly find some other ideas on this by searching the archives of this list at fatcity.com Jared == I'm not a proponent of purging data. Unless of course, you expect to never see it again. That word 'archive' rolls of the tongues of managers and consultants pretty easily, but what's behind it? There are a few gotchas with purging and archiving. Let's assume you have some
RE: Data Purging Strategy
-Original Message- I went to one meeting where someone from another DOE lab said they needed to store some data on media which would last 10,000 years. I suggested chisels and stone tablets :) That gold platter that NASA sent out into space: How long is it supposed to last? Another option might be a typical fruit cake -- the season for which is almost upon us. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephen Lee INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Data Purging Strategy
Stephen Lee wrote: -Original Message- I went to one meeting where someone from another DOE lab said they needed to store some data on media which would last 10,000 years. I suggested chisels and stone tablets :) That gold platter that NASA sent out into space: How long is it supposed to last? Another option might be a typical fruit cake -- the season for which is almost upon us. While we are on the topic of comestibles, I find the comparison of the lifespan of a CD to the time spent in wooden caskets by the favorite beverages of some people of the list (sorry to disappoint, folks, but I survive on water, tea and coffee) perfectly distressing. What about writing the data on bottle labels ? Would last longer, and would probably be more lovingly cared for. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Data Purging Strategy
Hi Tim We just signed a contract for external storage system from EMC and the configuration is going to be: Regular servers - connect as Nas Database servers - connect as San. If I remember correctly Nas use SCSI connections while San use fiber. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, November 07, 2002 1:43 AM In response to a post on data purging Tim Gorman wrote some on SAN-based disk, some on NAS-based storage. Can someone please explain the differences between these technologies please. My understanding that a SAN is a group of disks which are available on a network and are not 'owned' by a server and have no direct cables into a server. I also understood NAS to be network based disk (duh!) Please correct, clarify, or comment as needed; I don't recall ever having seen a formal definition for either acronym: * SAN (storage area network): storage-arrays connected by dedicated high-speed interconnects (i.e. SCSI, SSA, FC-AL, etc) managed by a dedicated server, including switches and routers to provide storage for one or multiple storage clients (i.e. what we tend to call servers)... * NAS (network-attached storage): storage that is hosted by (i.e. mounted on) a dedicated, special-purpose server and made available to network clients via IP protocols like NFS, Samba, etc across general-purpose IP networks. For NAS, think dedicated NFS server or dedicated file server or the like and you've got the idea... There are so many technologies mixed into SANs that I find it difficult to generalize. It is probably more appropriate to define NAS first and then say SANs are everything else in networked storage, but I thought I'd try it the hard way... Further generalizing: * SANs are capable of faster and more sustainable I/O throughput rates, but more complex and more expensive * NAS are economical, easy to administer, and easy to implement, but provide lower sustained I/O throughput rates For this reason, I don't see the question as an either-or proposition (i.e. either all SAN or all NAS). They are each point-solutions along a continuum, as illustrated in the strategy in my previous reply. Data passes through a life-cycle, just like anything else. Requirements for storage and retrieval can change during that life-cycle... - ... continuum .. there's a high-class word I've been itching to use . has the potential to become as hoity-toity and annoying as paradigm and juxtaposition, though... :-) Thanks John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Tim Gorman INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Yechiel Adar INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Data Purging Strategy
Sounds fair enough to me.. ;) -Original Message- [EMAIL PROTECTED] Sent: 06 November 2002 18:55 To: Multiple recipients of list ORACLE-L That reminds me: Mark, your annual stipend is due. Make it a case of Glenmorangie this time, Sherry finish. :) Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 07:56 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Data Purging Strategy Hey Dennis, Mark Leith is the only person on this list allowed to mention 3rd party products. I am sure he bought the franchise from Jared :) John -Original Message- Sent: 06 November 2002 14:15 To: Multiple recipients of list ORACLE-L Prem - You are receiving some excellent advice from Tom and Tim. I would mention two items in addition: - If you ever hope to re-use the data you archive off-line, you must also archive all the related tables, because after all, this is a RELATIONAL database. - PrincetonSoftech has a product Active Archiving that looks pretty good from the demos I've seen. I haven't used it myself. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 06, 2002 6:54 AM To: Multiple recipients of list ORACLE-L Prem, I would re-visit the requirement. Why do you feel the need to delete the data from the database? What is the purpose for this type of requirement? It would be far easier to modify the requirement than to do what you are thinking of doing. Adding columns to database tables indicating that a record has passed it's retention policy and thus, is not included in queries, would be a much easier solution. Or, simply moving these records to historical tables in the database - and NOT deleting them from the system - is a much better solution. The data is always accessible and not available in the current tables. And you will not be playing the get the data from tape and reload it game with all of it's problems (writing an offload program, table structure changes offload program versions). Try and keep this as simple as possible. Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 06, 2002 4:13 AM To: Multiple recipients of list ORACLE-L Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we have designed for this. When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database. In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates. i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables. Regards Prem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: INET: [EMAIL PROTECTED] 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
RE: Data Purging Strategy
just don't slip in that 32 year old Macallans! that one is MINE :) --- Mark Leith [EMAIL PROTECTED] wrote: Sounds fair enough to me.. ;) -Original Message- [EMAIL PROTECTED] Sent: 06 November 2002 18:55 To: Multiple recipients of list ORACLE-L That reminds me: Mark, your annual stipend is due. Make it a case of Glenmorangie this time, Sherry finish. :) Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 07:56 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Data Purging Strategy Hey Dennis, Mark Leith is the only person on this list allowed to mention 3rd party products. I am sure he bought the franchise from Jared :) John -Original Message- Sent: 06 November 2002 14:15 To: Multiple recipients of list ORACLE-L Prem - You are receiving some excellent advice from Tom and Tim. I would mention two items in addition: - If you ever hope to re-use the data you archive off-line, you must also archive all the related tables, because after all, this is a RELATIONAL database. - PrincetonSoftech has a product Active Archiving that looks pretty good from the demos I've seen. I haven't used it myself. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 06, 2002 6:54 AM To: Multiple recipients of list ORACLE-L Prem, I would re-visit the requirement. Why do you feel the need to delete the data from the database? What is the purpose for this type of requirement? It would be far easier to modify the requirement than to do what you are thinking of doing. Adding columns to database tables indicating that a record has passed it's retention policy and thus, is not included in queries, would be a much easier solution. Or, simply moving these records to historical tables in the database - and NOT deleting them from the system - is a much better solution. The data is always accessible and not available in the current tables. And you will not be playing the get the data from tape and reload it game with all of it's problems (writing an offload program, table structure changes offload program versions). Try and keep this as simple as possible. Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 06, 2002 4:13 AM To: Multiple recipients of list ORACLE-L Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we have designed for this. When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database. In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates. i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables. Regards Prem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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
RE: Data Purging Strategy
Oh no, I wouldn't dream of it! That is being saved for your up and coming UK tour :) It's matured even more now as well.. ;) -Original Message- Carmichael Sent: 07 November 2002 10:59 To: Multiple recipients of list ORACLE-L just don't slip in that 32 year old Macallans! that one is MINE :) --- Mark Leith [EMAIL PROTECTED] wrote: Sounds fair enough to me.. ;) -Original Message- [EMAIL PROTECTED] Sent: 06 November 2002 18:55 To: Multiple recipients of list ORACLE-L That reminds me: Mark, your annual stipend is due. Make it a case of Glenmorangie this time, Sherry finish. :) Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 07:56 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Data Purging Strategy Hey Dennis, Mark Leith is the only person on this list allowed to mention 3rd party products. I am sure he bought the franchise from Jared :) John -Original Message- Sent: 06 November 2002 14:15 To: Multiple recipients of list ORACLE-L Prem - You are receiving some excellent advice from Tom and Tim. I would mention two items in addition: - If you ever hope to re-use the data you archive off-line, you must also archive all the related tables, because after all, this is a RELATIONAL database. - PrincetonSoftech has a product Active Archiving that looks pretty good from the demos I've seen. I haven't used it myself. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 06, 2002 6:54 AM To: Multiple recipients of list ORACLE-L Prem, I would re-visit the requirement. Why do you feel the need to delete the data from the database? What is the purpose for this type of requirement? It would be far easier to modify the requirement than to do what you are thinking of doing. Adding columns to database tables indicating that a record has passed it's retention policy and thus, is not included in queries, would be a much easier solution. Or, simply moving these records to historical tables in the database - and NOT deleting them from the system - is a much better solution. The data is always accessible and not available in the current tables. And you will not be playing the get the data from tape and reload it game with all of it's problems (writing an offload program, table structure changes offload program versions). Try and keep this as simple as possible. Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 06, 2002 4:13 AM To: Multiple recipients of list ORACLE-L Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we have designed for this. When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database. In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates. i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables. Regards Prem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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
RE: Data Purging Strategy
FWIW, what we just implemented (because senior management refuses to approve additional storage on the grounds that making the database larger will affect performance - aaargh!) is 1) Confirmed with business how long data needs to be online for various tables (they're all partitioned so that makes it a lot easier) 2) Export partitions older than that once/month (this is generated off a table that lists each partitioned table and how long data should be kep) 3) After confirming that all export files are valid we drop the old partitions (this will be done by script but is being done manually for the first few months) 4) Leave dmp files on server for 2 end of months (our end of month backup tapes are stored for 7 years) 5) Maintain a table in database saying what exported partitions are on what date's tapes And I really long for the days in this company when senior management made technical decisions by asking the technical people instead of just making things up... Jay Miller -Original Message- Sent: Wednesday, November 06, 2002 11:54 AM To: Multiple recipients of list ORACLE-L Someone asked about this 3 weeks ago. Here's my take on archiving data. I don't expect everyone to agree with this, but nonetheless, I have an opinion. :) Here's an email from last month. You can undoubtedly find some other ideas on this by searching the archives of this list at fatcity.com Jared == I'm not a proponent of purging data. Unless of course, you expect to never see it again. That word 'archive' rolls of the tongues of managers and consultants pretty easily, but what's behind it? There are a few gotchas with purging and archiving. Let's assume you have some 3 year old data that you need to see again, and it has been purged. Here are some of the possible problems: * Your backup tapes are corrupted * Your new backup hardware can't read the old tapes * Your software no longer understands the format that the data is in. * You have the correct software, but it won't work on the current version of OS on your hardware. * The data format/software/whatever is not well documented * The employees that understood the data 3 years ago have been laid off. * ... lots more stuff Read Bryon Bergeron's Dark Ages II: When the Digital Data Die http://www.powells.com/cgi-bin/biblio?inkey=2-0130661074-0 Perhaps much better than archiving the data, is to stick with the idea of moving it to another database, and using lots of cheap disk storage (NAS) or a heirarchical file system to store it. The point being that if it's online somewhere, it will be maintained. Don't purge it till Finance, HR, the IRS and any other stakeholder says it's ok. Only then purge it and archive it to offline tape with the knowledge that you may never see that data again. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 01:13 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Data Purging Strategy Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we have designed for this. When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database. In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates. i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables. Regards Prem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: Data Purging Strategy
Jay, just make sure you are not around when, after several Oracle upgrades, and they want to import one of these files back that they discover that the current release of import can no longer read the older version of the .dmp file. now what are these senior damagers going to do? blame the DBA, that's what! duck and cover... duck and cover... Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, November 07, 2002 1:55 PM To: Multiple recipients of list ORACLE-L FWIW, what we just implemented (because senior management refuses to approve additional storage on the grounds that making the database larger will affect performance - aaargh!) is 1) Confirmed with business how long data needs to be online for various tables (they're all partitioned so that makes it a lot easier) 2) Export partitions older than that once/month (this is generated off a table that lists each partitioned table and how long data should be kep) 3) After confirming that all export files are valid we drop the old partitions (this will be done by script but is being done manually for the first few months) 4) Leave dmp files on server for 2 end of months (our end of month backup tapes are stored for 7 years) 5) Maintain a table in database saying what exported partitions are on what date's tapes And I really long for the days in this company when senior management made technical decisions by asking the technical people instead of just making things up... Jay Miller -Original Message- Sent: Wednesday, November 06, 2002 11:54 AM To: Multiple recipients of list ORACLE-L Someone asked about this 3 weeks ago. Here's my take on archiving data. I don't expect everyone to agree with this, but nonetheless, I have an opinion. :) Here's an email from last month. You can undoubtedly find some other ideas on this by searching the archives of this list at fatcity.com Jared == I'm not a proponent of purging data. Unless of course, you expect to never see it again. That word 'archive' rolls of the tongues of managers and consultants pretty easily, but what's behind it? There are a few gotchas with purging and archiving. Let's assume you have some 3 year old data that you need to see again, and it has been purged. Here are some of the possible problems: * Your backup tapes are corrupted * Your new backup hardware can't read the old tapes * Your software no longer understands the format that the data is in. * You have the correct software, but it won't work on the current version of OS on your hardware. * The data format/software/whatever is not well documented * The employees that understood the data 3 years ago have been laid off. * ... lots more stuff Read Bryon Bergeron's Dark Ages II: When the Digital Data Die http://www.powells.com/cgi-bin/biblio?inkey=2-0130661074-0 Perhaps much better than archiving the data, is to stick with the idea of moving it to another database, and using lots of cheap disk storage (NAS) or a heirarchical file system to store it. The point being that if it's online somewhere, it will be maintained. Don't purge it till Finance, HR, the IRS and any other stakeholder says it's ok. Only then purge it and archive it to offline tape with the knowledge that you may never see that data again. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 01:13 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Data Purging Strategy Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we have designed for this. When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database. In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates. i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables. Regards Prem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (note EXACT spelling of
RE: Data Purging Strategy
Thomas, Jay Here is my thought for your consideration, evisceration. A fundamental principle of backup and recovery is that you can only consider a backup to be good if you've tested a recovery. Why not apply this principle to data archiving? In other words, when you upgrade to a new Oracle version, as part of the testing process, load each of the ancient archives. That may be a good time to re-store the archive again using the new Oracle version. More work, but you've proven that the new Oracle version can successfully read the old archives. If it can't then you deal with that issue right then, perhaps by reading them again with the current Oracle version or something. If you put the old data out on archive tapes and seriously expect to retrieve it, this is the only practice that makes sense to me. On the other hand, if recovery is only a maybe thing, then by all means just toss it out on tape and let the tapes gather cobwebs. I do that at home all the time. I want to toss something, but I'm not sure, so I stick it in the garage in case I change my mind. But if I feel this is something valuable and delicate I certainly wouldn't take for granted recovery from the garage after several years. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, November 07, 2002 1:19 PM To: Multiple recipients of list ORACLE-L Jay, just make sure you are not around when, after several Oracle upgrades, and they want to import one of these files back that they discover that the current release of import can no longer read the older version of the .dmp file. now what are these senior damagers going to do? blame the DBA, that's what! duck and cover... duck and cover... Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, November 07, 2002 1:55 PM To: Multiple recipients of list ORACLE-L FWIW, what we just implemented (because senior management refuses to approve additional storage on the grounds that making the database larger will affect performance - aaargh!) is 1) Confirmed with business how long data needs to be online for various tables (they're all partitioned so that makes it a lot easier) 2) Export partitions older than that once/month (this is generated off a table that lists each partitioned table and how long data should be kep) 3) After confirming that all export files are valid we drop the old partitions (this will be done by script but is being done manually for the first few months) 4) Leave dmp files on server for 2 end of months (our end of month backup tapes are stored for 7 years) 5) Maintain a table in database saying what exported partitions are on what date's tapes And I really long for the days in this company when senior management made technical decisions by asking the technical people instead of just making things up... Jay Miller -Original Message- Sent: Wednesday, November 06, 2002 11:54 AM To: Multiple recipients of list ORACLE-L Someone asked about this 3 weeks ago. Here's my take on archiving data. I don't expect everyone to agree with this, but nonetheless, I have an opinion. :) Here's an email from last month. You can undoubtedly find some other ideas on this by searching the archives of this list at fatcity.com Jared == I'm not a proponent of purging data. Unless of course, you expect to never see it again. That word 'archive' rolls of the tongues of managers and consultants pretty easily, but what's behind it? There are a few gotchas with purging and archiving. Let's assume you have some 3 year old data that you need to see again, and it has been purged. Here are some of the possible problems: * Your backup tapes are corrupted * Your new backup hardware can't read the old tapes * Your software no longer understands the format that the data is in. * You have the correct software, but it won't work on the current version of OS on your hardware. * The data format/software/whatever is not well documented * The employees that understood the data 3 years ago have been laid off. * ... lots more stuff Read Bryon Bergeron's Dark Ages II: When the Digital Data Die http://www.powells.com/cgi-bin/biblio?inkey=2-0130661074-0 Perhaps much better than archiving the data, is to stick with the idea of moving it to another database, and using lots of cheap disk storage (NAS) or a heirarchical file system to store it. The point being that if it's online somewhere, it will be maintained. Don't purge it till Finance, HR, the IRS and any other stakeholder says it's ok. Only then purge it and archive it to offline tape with the knowledge that you may never see that data again. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 01:13 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Data Purging Strategy
RE: Data Purging Strategy
Well, if worst comes to worst we can always install an earlier version on a box and import it there. But the reason we can't get more storage approved still has me shaking my head... -Original Message- Sent: Thursday, November 07, 2002 2:19 PM To: Multiple recipients of list ORACLE-L Jay, just make sure you are not around when, after several Oracle upgrades, and they want to import one of these files back that they discover that the current release of import can no longer read the older version of the .dmp file. now what are these senior damagers going to do? blame the DBA, that's what! duck and cover... duck and cover... Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, November 07, 2002 1:55 PM To: Multiple recipients of list ORACLE-L FWIW, what we just implemented (because senior management refuses to approve additional storage on the grounds that making the database larger will affect performance - aaargh!) is 1) Confirmed with business how long data needs to be online for various tables (they're all partitioned so that makes it a lot easier) 2) Export partitions older than that once/month (this is generated off a table that lists each partitioned table and how long data should be kep) 3) After confirming that all export files are valid we drop the old partitions (this will be done by script but is being done manually for the first few months) 4) Leave dmp files on server for 2 end of months (our end of month backup tapes are stored for 7 years) 5) Maintain a table in database saying what exported partitions are on what date's tapes And I really long for the days in this company when senior management made technical decisions by asking the technical people instead of just making things up... Jay Miller -Original Message- Sent: Wednesday, November 06, 2002 11:54 AM To: Multiple recipients of list ORACLE-L Someone asked about this 3 weeks ago. Here's my take on archiving data. I don't expect everyone to agree with this, but nonetheless, I have an opinion. :) Here's an email from last month. You can undoubtedly find some other ideas on this by searching the archives of this list at fatcity.com Jared == I'm not a proponent of purging data. Unless of course, you expect to never see it again. That word 'archive' rolls of the tongues of managers and consultants pretty easily, but what's behind it? There are a few gotchas with purging and archiving. Let's assume you have some 3 year old data that you need to see again, and it has been purged. Here are some of the possible problems: * Your backup tapes are corrupted * Your new backup hardware can't read the old tapes * Your software no longer understands the format that the data is in. * You have the correct software, but it won't work on the current version of OS on your hardware. * The data format/software/whatever is not well documented * The employees that understood the data 3 years ago have been laid off. * ... lots more stuff Read Bryon Bergeron's Dark Ages II: When the Digital Data Die http://www.powells.com/cgi-bin/biblio?inkey=2-0130661074-0 Perhaps much better than archiving the data, is to stick with the idea of moving it to another database, and using lots of cheap disk storage (NAS) or a heirarchical file system to store it. The point being that if it's online somewhere, it will be maintained. Don't purge it till Finance, HR, the IRS and any other stakeholder says it's ok. Only then purge it and archive it to offline tape with the knowledge that you may never see that data again. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 01:13 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Data Purging Strategy Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we have designed for this. When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database. In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates. i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables. Regards Prem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network
RE: Data Purging Strategy
Prem, I would re-visit the requirement. Why do you feel the need to delete the data from the database? What is the purpose for this type of requirement? It would be far easier to modify the requirement than to do what you are thinking of doing. Adding columns to database tables indicating that a record has passed it's retention policy and thus, is not included in queries, would bea much easier solution. Or, simply moving these records to historical tables in the database - and NOT deleting them from the system - is a much better solution. The data is always accessible and not available in the current tables. And you will not be playing the "get the data from tape and reload it" game with all of it's problems (writing an offload program, table structure changes offload program versions). Try and keep this as simple as possible. Hope this helps Tom Mercadante Oracle Certified Professional -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]Sent: Wednesday, November 06, 2002 4:13 AMTo: Multiple recipients of list ORACLE-LSubject: Data Purging StrategyDear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we have designed for this. When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database. In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates. i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables. Regards Prem
Re: Data Purging Strategy
This is adata-archival requirement, not a data-purge requirement. It only resembles a purge requirement based on the multiple-database-migration strategy you outlined. There are alternatives... Depending on the volume of data in your database and your availability requirements, implementing table- and index-partitioning will likely be crucial. Onestrategy is tohave the most-active tables partitioned by a date column and have different sets of these partitions reside in time-variant tablespaces. With this arrangement, you can archive data to tape by simply setting the archived tablespaces to READ ONLY and then migratingthem to tape-based (instead of disk-based) file-systems and bringing them back online. Legato has this file-system technology (recently purchased)and there is a share-ware product called SAMFS which is an HSM (hierarchical storage mgmt) filesystem used by some vendors (i.e. StorageTek, etc). By setting tablespaces to READ ONLY it becomes very easy to move them from disk to tape while retaining them within the same original database, simplifying the task of later retrieval (which is really important). Of course, Oracle's partitioning option is enormously expensive, but in this case it is a matter of the upfront license costs (withreduced downstream implementation costs due to simplicity)versusa largedownstream application-development cost. In this situation, I think roughly offsets everything. Since I'm not spending the money, I can afford such a calculation... :-) With the various storage technologies available, a single database can straddle several simultaneously, optimizing performance or cost as needed. Somefiles might reside on solid-state NVRAM "disk", some on SAN-based disk, some on NAS-based storage, and then finally reside in archive media file-systems such as tape or magneto-optical based HSM file-systems. - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Wednesday, November 06, 2002 2:13 AM Subject: Data Purging Strategy Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we have designed for this. When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database. In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates. i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables. Regards Prem
RE: Data Purging Strategy
Tim, my problem with moving data to tape is as follows: Your one strategy involved moving read-only tablespaces to tape. what if you upgrade Oracle versions. will these read-only files still be valid? will they still be able to be put back on-line, or will they need to be converted along with the rest of the files to the newer version and then copied back to tape. if this is the case, is there disk space to put all of these back? if there is disk space, then why copy them to tape at all? they could always be available and on-line. Having a plan to save data to tape in hopes of resurrecting it later on has more challenges than anything I've come across lately. It just doesn't seem to make sense to do this anymore. With disk space at an all-time low-cost, why put ourselves thru this? the logistics are just too expensive, as well as the risk of never being able to get the data back because of software compatibility issues. Tom Mercadante Oracle Certified Professional -Original Message-From: Tim Gorman [mailto:[EMAIL PROTECTED]]Sent: Wednesday, November 06, 2002 8:49 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Data Purging Strategy This is adata-archival requirement, not a data-purge requirement. It only resembles a purge requirement based on the multiple-database-migration strategy you outlined. There are alternatives... Depending on the volume of data in your database and your availability requirements, implementing table- and index-partitioning will likely be crucial. Onestrategy is tohave the most-active tables partitioned by a date column and have different sets of these partitions reside in time-variant tablespaces. With this arrangement, you can archive data to tape by simply setting the archived tablespaces to READ ONLY and then migratingthem to tape-based (instead of disk-based) file-systems and bringing them back online. Legato has this file-system technology (recently purchased)and there is a share-ware product called SAMFS which is an HSM (hierarchical storage mgmt) filesystem used by some vendors (i.e. StorageTek, etc). By setting tablespaces to READ ONLY it becomes very easy to move them from disk to tape while retaining them within the same original database, simplifying the task of later retrieval (which is really important). Of course, Oracle's partitioning option is enormously expensive, but in this case it is a matter of the upfront license costs (withreduced downstream implementation costs due to simplicity)versusa largedownstream application-development cost. In this situation, I think roughly offsets everything. Since I'm not spending the money, I can afford such a calculation... :-) With the various storage technologies available, a single database can straddle several simultaneously, optimizing performance or cost as needed. Somefiles might reside on solid-state NVRAM "disk", some on SAN-based disk, some on NAS-based storage, and then finally reside in archive media file-systems such as tape or magneto-optical based HSM file-systems. - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Wednesday, November 06, 2002 2:13 AM Subject: Data Purging Strategy Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we have designed for this. When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database. In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates. i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables. Regards Prem
RE: Data Purging Strategy
A poor man's solution might be to load the offline database with appropriate data, then do a tablespace export and store the results on CD labelled by date. Restoring needed data would entail a tablespace import of stuff from the appropriate CD into the offline DB. I'm sure here's some gotchas involved but some variation on that theme might work. Jim -Original Message-From: Tim Gorman [mailto:[EMAIL PROTECTED]]Sent: Wednesday, November 06, 2002 8:49 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Data Purging Strategy This is adata-archival requirement, not a data-purge requirement. It only resembles a purge requirement based on the multiple-database-migration strategy you outlined. There are alternatives... Depending on the volume of data in your database and your availability requirements, implementing table- and index-partitioning will likely be crucial. Onestrategy is tohave the most-active tables partitioned by a date column and have different sets of these partitions reside in time-variant tablespaces. With this arrangement, you can archive data to tape by simply setting the archived tablespaces to READ ONLY and then migratingthem to tape-based (instead of disk-based) file-systems and bringing them back online. Legato has this file-system technology (recently purchased)and there is a share-ware product called SAMFS which is an HSM (hierarchical storage mgmt) filesystem used by some vendors (i.e. StorageTek, etc). By setting tablespaces to READ ONLY it becomes very easy to move them from disk to tape while retaining them within the same original database, simplifying the task of later retrieval (which is really important). Of course, Oracle's partitioning option is enormously expensive, but in this case it is a matter of the upfront license costs (withreduced downstream implementation costs due to simplicity)versusa largedownstream application-development cost. In this situation, I think roughly offsets everything. Since I'm not spending the money, I can afford such a calculation... :-) With the various storage technologies available, a single database can straddle several simultaneously, optimizing performance or cost as needed. Somefiles might reside on solid-state NVRAM "disk", some on SAN-based disk, some on NAS-based storage, and then finally reside in archive media file-systems such as tape or magneto-optical based HSM file-systems. - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Wednesday, November 06, 2002 2:13 AM Subject: Data Purging Strategy Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we have designed for this. When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database. In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates. i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables. Regards Prem
RE: Data Purging Strategy
Prem - You are receiving some excellent advice from Tom and Tim. I would mention two items in addition: - If you ever hope to re-use the data you archive off-line, you must also archive all the related tables, because after all, this is a RELATIONAL database. - PrincetonSoftech has a product Active Archiving that looks pretty good from the demos I've seen. I haven't used it myself. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 06, 2002 6:54 AM To: Multiple recipients of list ORACLE-L Prem, I would re-visit the requirement. Why do you feel the need to delete the data from the database? What is the purpose for this type of requirement? It would be far easier to modify the requirement than to do what you are thinking of doing. Adding columns to database tables indicating that a record has passed it's retention policy and thus, is not included in queries, would be a much easier solution. Or, simply moving these records to historical tables in the database - and NOT deleting them from the system - is a much better solution. The data is always accessible and not available in the current tables. And you will not be playing the get the data from tape and reload it game with all of it's problems (writing an offload program, table structure changes offload program versions). Try and keep this as simple as possible. Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 06, 2002 4:13 AM To: Multiple recipients of list ORACLE-L Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we have designed for this. When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database. In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates. i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables. Regards Prem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Data Purging Strategy
In response to a post on data purging Tim Gorman wrote some on SAN-based disk, some on NAS-based storage. Can someone please explain the differences between these technologies please. My understanding that a SAN is a group of disks which are available on a network and are not 'owned' by a server and have no direct cables into a server. I also understood NAS to be network based disk (duh!) Thanks John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Data Purging Strategy
Hey Dennis, Mark Leith is the only person on this list allowed to mention 3rd party products. I am sure he bought the franchise from Jared :) John -Original Message- Sent: 06 November 2002 14:15 To: Multiple recipients of list ORACLE-L Prem - You are receiving some excellent advice from Tom and Tim. I would mention two items in addition: - If you ever hope to re-use the data you archive off-line, you must also archive all the related tables, because after all, this is a RELATIONAL database. - PrincetonSoftech has a product Active Archiving that looks pretty good from the demos I've seen. I haven't used it myself. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 06, 2002 6:54 AM To: Multiple recipients of list ORACLE-L Prem, I would re-visit the requirement. Why do you feel the need to delete the data from the database? What is the purpose for this type of requirement? It would be far easier to modify the requirement than to do what you are thinking of doing. Adding columns to database tables indicating that a record has passed it's retention policy and thus, is not included in queries, would be a much easier solution. Or, simply moving these records to historical tables in the database - and NOT deleting them from the system - is a much better solution. The data is always accessible and not available in the current tables. And you will not be playing the get the data from tape and reload it game with all of it's problems (writing an offload program, table structure changes offload program versions). Try and keep this as simple as possible. Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 06, 2002 4:13 AM To: Multiple recipients of list ORACLE-L Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we have designed for this. When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database. In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates. i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables. Regards Prem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Data Purging Strategy
Hey, thanks for mentioning that, I'll have to mind my manners. I really don't have a connection to that vendor, just attended a demo. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 06, 2002 9:56 AM To: Multiple recipients of list ORACLE-L Hey Dennis, Mark Leith is the only person on this list allowed to mention 3rd party products. I am sure he bought the franchise from Jared :) John -Original Message- Sent: 06 November 2002 14:15 To: Multiple recipients of list ORACLE-L Prem - You are receiving some excellent advice from Tom and Tim. I would mention two items in addition: - If you ever hope to re-use the data you archive off-line, you must also archive all the related tables, because after all, this is a RELATIONAL database. - PrincetonSoftech has a product Active Archiving that looks pretty good from the demos I've seen. I haven't used it myself. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 06, 2002 6:54 AM To: Multiple recipients of list ORACLE-L Prem, I would re-visit the requirement. Why do you feel the need to delete the data from the database? What is the purpose for this type of requirement? It would be far easier to modify the requirement than to do what you are thinking of doing. Adding columns to database tables indicating that a record has passed it's retention policy and thus, is not included in queries, would be a much easier solution. Or, simply moving these records to historical tables in the database - and NOT deleting them from the system - is a much better solution. The data is always accessible and not available in the current tables. And you will not be playing the get the data from tape and reload it game with all of it's problems (writing an offload program, table structure changes offload program versions). Try and keep this as simple as possible. Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 06, 2002 4:13 AM To: Multiple recipients of list ORACLE-L Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we have designed for this. When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database. In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates. i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables. Regards Prem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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
Re: Data Purging Strategy
Someone asked about this 3 weeks ago. Here's my take on archiving data. I don't expect everyone to agree with this, but nonetheless, I have an opinion. :) Here's an email from last month. You can undoubtedly find some other ideas on this by searching the archives of this list at fatcity.com Jared == I'm not a proponent of purging data. Unless of course, you expect to never see it again. That word 'archive' rolls of the tongues of managers and consultants pretty easily, but what's behind it? There are a few gotchas with purging and archiving. Let's assume you have some 3 year old data that you need to see again, and it has been purged. Here are some of the possible problems: * Your backup tapes are corrupted * Your new backup hardware can't read the old tapes * Your software no longer understands the format that the data is in. * You have the correct software, but it won't work on the current version of OS on your hardware. * The data format/software/whatever is not well documented * The employees that understood the data 3 years ago have been laid off. * ... lots more stuff Read Bryon Bergeron's Dark Ages II: When the Digital Data Die http://www.powells.com/cgi-bin/biblio?inkey=2-0130661074-0 Perhaps much better than archiving the data, is to stick with the idea of moving it to another database, and using lots of cheap disk storage (NAS) or a heirarchical file system to store it. The point being that if it's online somewhere, it will be maintained. Don't purge it till Finance, HR, the IRS and any other stakeholder says it's ok. Only then purge it and archive it to offline tape with the knowledge that you may never see that data again. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 01:13 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Data Purging Strategy Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we have designed for this. When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database. In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates. i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables. Regards Prem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Data Purging Strategy
LOL! Thanks John! ;) As a matter of fact.. ;P I do actually know of another tool that does just this, it's called Checkmate from a company called BitByBit http://www.bitbybit.co.uk (which I have just checked and it now seems they have been acquired by OuterBay!).. We actually used to promote Checkmate for them, but in all honesty it was a little hard for the every day DBA to use.. When we came across DataBee, which performs the function that most DBA's wanted anyway (subsetting), we decided to go that route instead.. Prepare to have some $$'s if your going to look at Checkmate. Checkmate does take a referentially correct archive, and purges the data after it has been archived (and checked for data integrity etc).. Checkmate also has the ability to archive to an historic database, so that all data is still online, and accessible through database links if need be, whilst keeping the size of the live system down.. This also give the benefit of all historic reporting being run against a separate system as well. This is along the same lines as what Tom is saying really, although you will still get the historical reporting loads against your prod system with his guidelines. And no, I don't get any gains from saying any of this :) Hey, they also have modules for Oracle Apps, and Peopl$lop! Go get 'em Dick! G,DRLH Mark -Original Message- [EMAIL PROTECTED] Sent: 06 November 2002 15:56 To: Multiple recipients of list ORACLE-L Hey Dennis, Mark Leith is the only person on this list allowed to mention 3rd party products. I am sure he bought the franchise from Jared :) John -Original Message- Sent: 06 November 2002 14:15 To: Multiple recipients of list ORACLE-L Prem - You are receiving some excellent advice from Tom and Tim. I would mention two items in addition: - If you ever hope to re-use the data you archive off-line, you must also archive all the related tables, because after all, this is a RELATIONAL database. - PrincetonSoftech has a product Active Archiving that looks pretty good from the demos I've seen. I haven't used it myself. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 06, 2002 6:54 AM To: Multiple recipients of list ORACLE-L Prem, I would re-visit the requirement. Why do you feel the need to delete the data from the database? What is the purpose for this type of requirement? It would be far easier to modify the requirement than to do what you are thinking of doing. Adding columns to database tables indicating that a record has passed it's retention policy and thus, is not included in queries, would be a much easier solution. Or, simply moving these records to historical tables in the database - and NOT deleting them from the system - is a much better solution. The data is always accessible and not available in the current tables. And you will not be playing the get the data from tape and reload it game with all of it's problems (writing an offload program, table structure changes offload program versions). Try and keep this as simple as possible. Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 06, 2002 4:13 AM To: Multiple recipients of list ORACLE-L Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we have designed for this. When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database. In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates. i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables. Regards Prem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: INET: [EMAIL PROTECTED] Fat
RE: Data Purging Strategy
That reminds me: Mark, your annual stipend is due. Make it a case of Glenmorangie this time, Sherry finish. :) Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 07:56 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Data Purging Strategy Hey Dennis, Mark Leith is the only person on this list allowed to mention 3rd party products. I am sure he bought the franchise from Jared :) John -Original Message- Sent: 06 November 2002 14:15 To: Multiple recipients of list ORACLE-L Prem - You are receiving some excellent advice from Tom and Tim. I would mention two items in addition: - If you ever hope to re-use the data you archive off-line, you must also archive all the related tables, because after all, this is a RELATIONAL database. - PrincetonSoftech has a product Active Archiving that looks pretty good from the demos I've seen. I haven't used it myself. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 06, 2002 6:54 AM To: Multiple recipients of list ORACLE-L Prem, I would re-visit the requirement. Why do you feel the need to delete the data from the database? What is the purpose for this type of requirement? It would be far easier to modify the requirement than to do what you are thinking of doing. Adding columns to database tables indicating that a record has passed it's retention policy and thus, is not included in queries, would be a much easier solution. Or, simply moving these records to historical tables in the database - and NOT deleting them from the system - is a much better solution. The data is always accessible and not available in the current tables. And you will not be playing the get the data from tape and reload it game with all of it's problems (writing an offload program, table structure changes offload program versions). Try and keep this as simple as possible. Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 06, 2002 4:13 AM To: Multiple recipients of list ORACLE-L Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we have designed for this. When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database. In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates. i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables. Regards Prem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name
RE: Data Purging Strategy
Port finish is better :) --- [EMAIL PROTECTED] wrote: That reminds me: Mark, your annual stipend is due. Make it a case of Glenmorangie this time, Sherry finish. :) Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 07:56 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Data Purging Strategy Hey Dennis, Mark Leith is the only person on this list allowed to mention 3rd party products. I am sure he bought the franchise from Jared :) John -Original Message- Sent: 06 November 2002 14:15 To: Multiple recipients of list ORACLE-L Prem - You are receiving some excellent advice from Tom and Tim. I would mention two items in addition: - If you ever hope to re-use the data you archive off-line, you must also archive all the related tables, because after all, this is a RELATIONAL database. - PrincetonSoftech has a product Active Archiving that looks pretty good from the demos I've seen. I haven't used it myself. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 06, 2002 6:54 AM To: Multiple recipients of list ORACLE-L Prem, I would re-visit the requirement. Why do you feel the need to delete the data from the database? What is the purpose for this type of requirement? It would be far easier to modify the requirement than to do what you are thinking of doing. Adding columns to database tables indicating that a record has passed it's retention policy and thus, is not included in queries, would be a much easier solution. Or, simply moving these records to historical tables in the database - and NOT deleting them from the system - is a much better solution. The data is always accessible and not available in the current tables. And you will not be playing the get the data from tape and reload it game with all of it's problems (writing an offload program, table structure changes offload program versions). Try and keep this as simple as possible. Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 06, 2002 4:13 AM To: Multiple recipients of list ORACLE-L Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we have designed for this. When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database. In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates. i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables. Regards Prem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: Data Purging Strategy
Sherry Finish? I thought you liked scotch that tasted like burnt mud? -Original Message- [EMAIL PROTECTED] Sent: Wednesday, November 06, 2002 10:55 AM To: Multiple recipients of list ORACLE-L That reminds me: Mark, your annual stipend is due. Make it a case of Glenmorangie this time, Sherry finish. :) Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 07:56 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Data Purging Strategy Hey Dennis, Mark Leith is the only person on this list allowed to mention 3rd party products. I am sure he bought the franchise from Jared :) John -Original Message- Sent: 06 November 2002 14:15 To: Multiple recipients of list ORACLE-L Prem - You are receiving some excellent advice from Tom and Tim. I would mention two items in addition: - If you ever hope to re-use the data you archive off-line, you must also archive all the related tables, because after all, this is a RELATIONAL database. - PrincetonSoftech has a product Active Archiving that looks pretty good from the demos I've seen. I haven't used it myself. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 06, 2002 6:54 AM To: Multiple recipients of list ORACLE-L Prem, I would re-visit the requirement. Why do you feel the need to delete the data from the database? What is the purpose for this type of requirement? It would be far easier to modify the requirement than to do what you are thinking of doing. Adding columns to database tables indicating that a record has passed it's retention policy and thus, is not included in queries, would be a much easier solution. Or, simply moving these records to historical tables in the database - and NOT deleting them from the system - is a much better solution. The data is always accessible and not available in the current tables. And you will not be playing the get the data from tape and reload it game with all of it's problems (writing an offload program, table structure changes offload program versions). Try and keep this as simple as possible. Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 06, 2002 4:13 AM To: Multiple recipients of list ORACLE-L Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we have designed for this. When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database. In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates. i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables. Regards Prem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE
Re: Data Purging Strategy
Agreed. The current Oracle datafile format (7.3, 8.0, 8.1, 9.0, or 9.2) may not survive 10i -- who the heck know? However, the tape-based file-systems (i.e. SAMFS, Legato DiskExtender, etc) can be treated like a file-system in all respects (only slower). In other words, convert the datafiles in place just as you would if they are on disk. After all, they are still active parts of an active Oracle database, even if they are in READ ONLY. The last time such a conversion was necessary was between Oracle7 and Oracle8; I think that it is valid to assume that Oracle will provide a similar migration utility should another such conversion become necessary. Whatever needs to be performed for one tablespace in converting file formats should be done for all, so this is not a problem specific to the strategy I mentioned... - Original Message - From: Mercadante, Thomas F To: Multiple recipients of list ORACLE-L Sent: Wednesday, November 06, 2002 7:18 AM Subject: RE: Data Purging Strategy Tim, my problem with moving data to tape is as follows: Your one strategy involved moving read-only tablespaces to tape. what if you upgrade Oracle versions. will these read-only files still be valid? will they still be able to be put back on-line, or will they need to be converted along with the rest of the files to the newer version and then copied back to tape. if this is the case, is there disk space to put all of these back? if there is disk space, then why copy them to tape at all? they could always be available and on-line. Having a plan to save data to tape in hopes of resurrecting it later on has more challenges than anything I've come across lately. It just doesn't seem to make sense to do this anymore. With disk space at an all-time low-cost, why put ourselves thru this? the logistics are just too expensive, as well as the risk of never being able to get the data back because of software compatibility issues. Tom Mercadante Oracle Certified Professional -Original Message-From: Tim Gorman [mailto:[EMAIL PROTECTED]]Sent: Wednesday, November 06, 2002 8:49 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Data Purging Strategy This is adata-archival requirement, not a data-purge requirement. It only resembles a purge requirement based on the multiple-database-migration strategy you outlined. There are alternatives... Depending on the volume of data in your database and your availability requirements, implementing table- and index-partitioning will likely be crucial. Onestrategy is tohave the most-active tables partitioned by a date column and have different sets of these partitions reside in time-variant tablespaces. With this arrangement, you can archive data to tape by simply setting the archived tablespaces to READ ONLY and then migratingthem to tape-based (instead of disk-based) file-systems and bringing them back online. Legato has this file-system technology (recently purchased)and there is a share-ware product called SAMFS which is an HSM (hierarchical storage mgmt) filesystem used by some vendors (i.e. StorageTek, etc). By setting tablespaces to READ ONLY it becomes very easy to move them from disk to tape while retaining them within the same original database, simplifying the task of later retrieval (which is really important). Of course, Oracle's partitioning option is enormously expensive, but in this case it is a matter of the upfront license costs (withreduced downstream implementation costs due to simplicity)versusa largedownstream application-development cost. In this situation, I think roughly offsets everything. Since I'm not spending the money, I can afford such a calculation... :-) With the various storage technologies available, a single database can straddle several simultaneously, optimizing performance or cost as needed. Somefiles might reside on solid-state NVRAM "disk", some on SAN-based disk, some on NAS-based storage, and then finally reside in archive media file-systems such as tape or magneto-optical based HSM file-systems. - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Wednesday, November 06, 2002 2:13 AM Subject: Data Purging Strategy Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we hav
Re: Data Purging Strategy
Tim, I missed the part earlier where you mentioned HSM. Though somewhat familiar with it, I've never used it. Pretty common in mainframe environments. Do you have experience with it? Drawbacks other than performance? This seems like a much better solution to me than archiving onto tape and hoping you can use it later. Jared Tim Gorman [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 03:08 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Data Purging Strategy Agreed. The current Oracle datafile format (7.3, 8.0, 8.1, 9.0, or 9.2) may not survive 10i -- who the heck know? However, the tape-based file-systems (i.e. SAMFS, Legato DiskExtender, etc) can be treated like a file-system in all respects (only slower). In other words, convert the datafiles in place just as you would if they are on disk. After all, they are still active parts of an active Oracle database, even if they are in READ ONLY. The last time such a conversion was necessary was between Oracle7 and Oracle8; I think that it is valid to assume that Oracle will provide a similar migration utility should another such conversion become necessary. Whatever needs to be performed for one tablespace in converting file formats should be done for all, so this is not a problem specific to the strategy I mentioned... - Original Message - To: Multiple recipients of list ORACLE-L Sent: Wednesday, November 06, 2002 7:18 AM Tim, my problem with moving data to tape is as follows: Your one strategy involved moving read-only tablespaces to tape. what if you upgrade Oracle versions. will these read-only files still be valid? will they still be able to be put back on-line, or will they need to be converted along with the rest of the files to the newer version and then copied back to tape. if this is the case, is there disk space to put all of these back? if there is disk space, then why copy them to tape at all? they could always be available and on-line. Having a plan to save data to tape in hopes of resurrecting it later on has more challenges than anything I've come across lately. It just doesn't seem to make sense to do this anymore. With disk space at an all-time low-cost, why put ourselves thru this? the logistics are just too expensive, as well as the risk of never being able to get the data back because of software compatibility issues. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 06, 2002 8:49 AM To: Multiple recipients of list ORACLE-L This is a data-archival requirement, not a data-purge requirement. It only resembles a purge requirement based on the multiple-database-migration strategy you outlined. There are alternatives... Depending on the volume of data in your database and your availability requirements, implementing table- and index-partitioning will likely be crucial. One strategy is to have the most-active tables partitioned by a date column and have different sets of these partitions reside in time-variant tablespaces. With this arrangement, you can archive data to tape by simply setting the archived tablespaces to READ ONLY and then migrating them to tape-based (instead of disk-based) file-systems and bringing them back online. Legato has this file-system technology (recently purchased) and there is a share-ware product called SAMFS which is an HSM (hierarchical storage mgmt) filesystem used by some vendors (i.e. StorageTek, etc). By setting tablespaces to READ ONLY it becomes very easy to move them from disk to tape while retaining them within the same original database, simplifying the task of later retrieval (which is really important). Of course, Oracle's partitioning option is enormously expensive, but in this case it is a matter of the upfront license costs (with reduced downstream implementation costs due to simplicity) versus a large downstream application-development cost. In this situation, I think roughly offsets everything. Since I'm not spending the money, I can afford such a calculation... :-) With the various storage technologies available, a single database can straddle several simultaneously, optimizing performance or cost as needed. Some files might reside on solid-state NVRAM disk, some on SAN-based disk, some on NAS-based storage, and then finally reside in archive media file-systems such as tape or magneto-optical based HSM file-systems. - Original Message - To: Multiple recipients of list ORACLE-L Sent: Wednesday, November 06, 2002 2:13 AM Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged
Re: Data Purging Strategy
In response to a post on data purging Tim Gorman wrote some on SAN-based disk, some on NAS-based storage. Can someone please explain the differences between these technologies please. My understanding that a SAN is a group of disks which are available on a network and are not 'owned' by a server and have no direct cables into a server. I also understood NAS to be network based disk (duh!) Please correct, clarify, or comment as needed; I don't recall ever having seen a formal definition for either acronym: * SAN (storage area network): storage-arrays connected by dedicated high-speed interconnects (i.e. SCSI, SSA, FC-AL, etc) managed by a dedicated server, including switches and routers to provide storage for one or multiple storage clients (i.e. what we tend to call servers)... * NAS (network-attached storage): storage that is hosted by (i.e. mounted on) a dedicated, special-purpose server and made available to network clients via IP protocols like NFS, Samba, etc across general-purpose IP networks. For NAS, think dedicated NFS server or dedicated file server or the like and you've got the idea... There are so many technologies mixed into SANs that I find it difficult to generalize. It is probably more appropriate to define NAS first and then say SANs are everything else in networked storage, but I thought I'd try it the hard way... Further generalizing: * SANs are capable of faster and more sustainable I/O throughput rates, but more complex and more expensive * NAS are economical, easy to administer, and easy to implement, but provide lower sustained I/O throughput rates For this reason, I don't see the question as an either-or proposition (i.e. either all SAN or all NAS). They are each point-solutions along a continuum, as illustrated in the strategy in my previous reply. Data passes through a life-cycle, just like anything else. Requirements for storage and retrieval can change during that life-cycle... - ... continuum .. there's a high-class word I've been itching to use . has the potential to become as hoity-toity and annoying as paradigm and juxtaposition, though... :-) Thanks John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Tim Gorman INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Data Purging Strategy
Burnt mud ??? You're supposed to say peaty ! Or you could have said : Classic Glenmorangie, matured for 10 years in American white oak then finished in Sherry Butts. Light gold in colour, this product has a complex aroma full bodied, sherry wine notes with traces of honey. Sherry and nuts are both apparent in the flavour and these produce a warm, long lasting after taste. --- Steve McClure [EMAIL PROTECTED] a écrit : Sherry Finish? I thought you liked scotch that tasted like burnt mud? -Original Message- [EMAIL PROTECTED] Sent: Wednesday, November 06, 2002 10:55 AM To: Multiple recipients of list ORACLE-L That reminds me: Mark, your annual stipend is due. Make it a case of Glenmorangie this time, Sherry finish. :) Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 07:56 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Data Purging Strategy Hey Dennis, Mark Leith is the only person on this list allowed to mention 3rd party products. I am sure he bought the franchise from Jared :) John -Original Message- Sent: 06 November 2002 14:15 To: Multiple recipients of list ORACLE-L Prem - You are receiving some excellent advice from Tom and Tim. I would mention two items in addition: - If you ever hope to re-use the data you archive off-line, you must also archive all the related tables, because after all, this is a RELATIONAL database. - PrincetonSoftech has a product Active Archiving that looks pretty good from the demos I've seen. I haven't used it myself. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 06, 2002 6:54 AM To: Multiple recipients of list ORACLE-L Prem, I would re-visit the requirement. Why do you feel the need to delete the data from the database? What is the purpose for this type of requirement? It would be far easier to modify the requirement than to do what you are thinking of doing. Adding columns to database tables indicating that a record has passed it's retention policy and thus, is not included in queries, would be a much easier solution. Or, simply moving these records to historical tables in the database - and NOT deleting them from the system - is a much better solution. The data is always accessible and not available in the current tables. And you will not be playing the get the data from tape and reload it game with all of it's problems (writing an offload program, table structure changes offload program versions). Try and keep this as simple as possible. Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 06, 2002 4:13 AM To: Multiple recipients of list ORACLE-L Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we have designed for this. When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database. In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates. i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables. Regards Prem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from
