Re: Data Purging Strategy

[Yechiel Adar]

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 fo

Re: Data Purging Strategy

[Stephane Faroult]

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

[Stephen Lee]

> -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

[DENNIS WILLIAMS]

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.


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

RE: Data Purging Strategy

[Rachel Carmichael]

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

RE: Data Purging Strategy

[Jared . Still]

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 Dieg

RE: Data Purging Strategy

[MacGregor, Ian A.]

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 

Re: Data Purging Strategy

[Darlene Marley]

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

[Whittle Jerome Contr NCI]

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

[Scott . Shafer]

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 

Re: Data Purging Strategy

[Jared Still]

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 

RE: Data Purging Strategy

[Ron Rogers]

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 possibl

RE: Data Purging Strategy

[cjgait]

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 
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   
> 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: UNSU

RE: Data Purging Strategy

[Miller, Jay]

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

[DENNIS WILLIAMS]

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 Stra

RE: Data Purging Strategy

[Mercadante, Thomas F]

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 spellin

RE: Data Purging Strategy

[Miller, Jay]

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

[Mark Leith]

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 co

RE: Data Purging Strategy

[Rachel Carmichael]

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)

RE: Data Purging Strategy

[Mark Leith]

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
---

Re: Data Purging Strategy

[Yechiel Adar]

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

[paquette stephane]

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
>
---

Re: Data Purging Strategy

[Tim Gorman]

> 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

[Jared . Still]

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, bu

Re: Data Purging Strategy

[Tim Gorman]

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 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 - 
  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 
   

RE: Data Purging Strategy

[Steve McClure]

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

RE: Data Purging Strategy

[Rachel Carmichael]

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-- M

RE: Data Purging Strategy

[Jared . Still]

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 m

RE: Data Purging Strategy

[Mark Leith]

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!



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]

F

Re: Data Purging Strategy

[Jared . Still]

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

[DENNIS WILLIAMS]

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

[John . Hallas]

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

[John . Hallas]

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

[DENNIS WILLIAMS]

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

[Conboy, Jim]

  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 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 - 
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

[Mercadante, Thomas F]

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 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 - 
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 Gorman]

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 - 
  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

[Mercadante, Thomas F]

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-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