Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: Data Archiving ...9i

    Hi guys

    I need to implement an archiving policy on the database in that it only contains the last 1 yr's data. Could you please help me understand what areas of the database need to be archived inorder to free up some space?

    Anyone know the sql to find out the amount of data in all schemas (tables/views) in the db?

    Regards

  2. #2
    Join Date
    Aug 2008
    Posts
    464
    Just wanted to rephrase my question. Is there any way to find out what kind of archiving is currently going on? Because all the tables have a min(datetime) of 15 SEP 09.

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Look at the application.

    Due to the fact that "archiving" as you state it refers to the data for an application in the database, that type of archiving is tightly bound and constrained by the requirements of the particular application.

    Therefore we cannot speculate as of what kind of archiving is currently going on.

    On the other hand on the question about the method to get an estimate of the amount of data in all tables, if you have decent statistics, you can aggregate the result of multiplying the NUM_ROWS by AVG_ROW_LEN column from the {ALL|DBA|USER}_TABLES views.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Aug 2008
    Posts
    464
    Thanks.

    Regarding Archiving, so it isn't possible to tell why the min(datetime) for tables is 15 SEP 09 (as of now)? This system has been running for 3 yrs.

    I have the archive log file, .arc but how can I read it?

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I have the archive log file, .arc but how can I read it?
    DBMS_LOGMINER
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Purging the ...

    Quote Originally Posted by shajju View Post
    Thanks.

    Regarding Archiving, so it isn't possible to tell why the min(datetime) for tables is 15 SEP 09 (as of now)? This system has been running for 3 yrs.

    I have the archive log file, .arc but how can I read it?
    Maybe the application is not archiving but actually "purging" the data.

    In order to figure out why the min(datetime) for tables is 15 SEP 09 after 3 years, start by searching the application documentation for clues.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You might be able to discern when the "cleanup" occured by reviewing the timestamps on the archived redo log files.
    The task will have generated a decent amount of DML & therefore redo logfile(s).
    Typically this type of activity is scheduled to occur during normal slack time.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Wow, reading the archivelog files... this should surely be an interesting and time consuming task.

    LKBrwn_DBA, I am assuming that you know that the Oracle archive log files are not an archive of your data, but only a log of the DML that modified it. For an archive of your data you actually need a second schema. I even question the need for an archive... are you trying to keep the tables "small" in order to keep performance high?

    If you want to know if data is being "purged" (or deleted) I would turn on the Oracle audit for the set of tables you are interested in. You would certainly get the date of the delete along with the exact SQL run, the user that performed the delete and lots of other information.

  9. #9
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down Wrong guy

    Quote Originally Posted by dayneo View Post
    Wow, reading the archivelog files... this should surely be an interesting and time consuming task.

    LKBrwn_DBA, ... Etc ...
    Dayneo, please pay more attention to whom you respond to, I am NOT the OP of this thread, you are replying to the WRONG GUY.

    Besides, the Oracle archive log files may not be an archive of the data but do contain every change made since the begining of the database (if you actually keep all of them).

    The archive logs can be mined using logminer as anacedent pointed out.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  10. #10
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    LKBrwn_DBA, my apologies... you are quite right... I need to learn to use the scroll button on the mouse better. The comment was in fact to shajju.

    I think there is a misunderstanding between the concept of "application data archiving" and the purpose of Oracle achive logs.

  11. #11
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by dayneo View Post
    I think there is a misunderstanding between the concept of "application data archiving" and the purpose of Oracle achive logs.
    That has been one of the points of this whole thread.

    The main concern of the OP is to find out where the data is going, since the database contains only data after 15 SEP 09.

    And you are correct, one way to know is to set Oracle audit on the tables.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by dayneo View Post
    I think there is a misunderstanding between the concept of "application data archiving" and the purpose of Oracle achive logs.
    Speak only for yourself WTR "misunderstanding".

    I well understand difference between "application data archiving" and the purpose of Oracle achive logs.

    Since OP had to clue as to how or when data was going missing, I suggested using LOGMINER.

    LOGMINER has the potential to providing both exact DDL that made the data go missing & it would tell when the data went missing.

    With the DDL, then OP could search for code which contained said DDL, either in the DB or in external scripts.

    With the date, OP could search for any log files created around this time.

    ENJOY!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •