This discussion is archived
12 Replies Latest reply: Oct 18, 2013 6:00 PM by yxes2013 RSS

Cleanup Production DB

yxes2013 Newbie
Currently Being Moderated

Hi all,

 

11.2.0.1

aix6.1

 

Our prod database is almost out of space. My boss tasked me to backup unused tables and drop them as there are lots of un-identified tables which were used in testing and were not cleaned-up or dropped.

This is a 3rd party apps and there is no one to identify which tables are being used or not.

 

Our 3rd party apps can not display the webpage correctly when rows selected is reached 2 year payments period or about 5,000 payment records.

I checked the table in which the apps can not view the pages but it is only less than 50,000 rows.

I suspect this is a programming issue?

 

How do I identify unused tables?

Can you share me your process how do you cleanup your prod database?

 

Thanks a lot,

zxy

  • 1. Re: Cleanup Production DB
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated

    Don't you want to finish your previous task before going to a new one ?

    Which is efficient?

     

    Nicolas.

  • 2. Re: Cleanup Production DB
    yxes2013 Newbie
    Currently Being Moderated

    Ok I will close the other one

  • 3. Re: Cleanup Production DB
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated

    That's just a shame and unprofessional behavior for all those people who spent their time out there.

    And regarding your current issue, have you already forget your dozen of threads about AUDIT https://forums.oracle.com/search.jspa?view=content&resultTypes=&dateRange=all&q=audit+zxy&rankBy=relevance&contentType=all&containerType=&container=&containerName=&userID=&numResults=15

    You're not going to "clean" the production database by dropping tables, since unused does not mean anything.

     

    Nicolas.

  • 4. Re: Cleanup Production DB
    yxes2013 Newbie
    Currently Being Moderated

    Honestly, I want to do auditing but I got phobia about it becuase right now our admin/adump/*.aud keep generating lost of  audits logs I keep on deleting it everyday because it reached count of more than 1,000,0000+ audit logs a day. This auditing was turned on last year by the previous consultants. Maybe the consultants just doing the test audit and forgot to turn it off. That is why I am afraid to add more auditing again.

    I have not yet investigated what audits was turned on. And my boss does not know about it so I am free to delete all logs or else our system will blowup. I wanted to stop the audit that is generating these lots of logs but I have lots of thing to attend to hence I was not able to stop it. And besides the PCIDSS auditor recommended only SYSLOG which is and OS file directed to another server with big storage.

     

    Thanks,

  • 5. Re: Cleanup Production DB
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated

    yxes2013 wrote:

     

    Hi all,

     

    11.2.0.1

    aix6.1

     

    Our prod database is almost out of space. My boss tasked me to backup unused tables and drop them as there are lots of un-identified tables which were used in testing and were not cleaned-up or dropped.

    This is a 3rd party apps and there is no one to identify which tables are being used or not.

     

    It's a paradox.

     

    yxes2013 wrote:

    ...

     

    Our 3rd party apps can not display the webpage correctly when rows selected is reached 2 year payments period or about 5,000 payment records.

    I checked the table in which the apps can not view the pages but it is only less than 50,000 rows.

    ...

    How dropping tables can help to reduce the number of rows in a table ?

     

    Nicolas.

  • 6. Re: Cleanup Production DB
    TSharma-Oracle Guru
    Currently Being Moderated

    How to drop a table in Production:

    i) backup the table

    ii) drop the table.

     

    It's simple. isn't it? BUT BUT BUT nobody here can tell you which tables to drop because nobody has never seen your environment here. It's only you and your application team can decide what to drop or what not. There could be some table which are being used on annual basis and there could be some which are being every month or 6 months. Do you think these tables are unused and a drop candidate?    

    Specially when this is 3rd party app, you should not be playing with database definitions without engaging the 3rd party.

    And, if you think something is a programming issue, ask your application tam or take a look at the application code and try to understand it.

  • 7. Re: Cleanup Production DB
    yxes2013 Newbie
    Currently Being Moderated

    Hi Nic,

     

    How dropping tables can help to reduce the number of rows in a table ?


    Actually it is another task of "purging" history tables. Because our 3rd party app can not handle  select range if rows like a 2 years billing payments .I can not understand why this app gets unpredictable in displaying the page when it displaying more rows? Maybe it is not displaying by block and next block? Maybe it is taking the whole data and put them all together in display page? How can I prove the wrong behavior of this program?

     

    Right now when the app can not display the page, I backup the previous year 2012 payments just to make it works. Then when the company is needing the records for references and verification, I load it back again. The is something wrong with the program, I just do not know how to prove it to the vendor as it is not good to point the blame on their app if you have no proof. My issue is the blame the database as not "tuned up" hence the issue of the program occurred. I can not understand why a  2year period of payments (approx 5,000 rows per company) cannot be properly displayed in an app webpage?

    Do you have a similar issue like this? How do you tune the database for this app? without purging need data?

     

    Thanks,

  • 8. Re: Cleanup Production DB
    yxes2013 Newbie
    Currently Being Moderated

    Thanks Tsh,

     

    I believe there is not yearly used tables in our db. But I should do backups before dropping it.

    Thanks for reminding.

  • 9. Re: Cleanup Production DB
    TSharma-Oracle Guru
    Currently Being Moderated

    There could be an application limitation showing records. When application runs this query, try to capture the exact query run by application. Make sure you capture the query exactly running by application. Then you might be able to see if its a limitation at application side. You can always ask your application team about this if they have any limitation of showing result. May be application is limited to show only one year of result.

  • 10. Re: Cleanup Production DB
    yxes2013 Newbie
    Currently Being Moderated

    Thanks Tsh,

     

    I will try to suggest that. How can I explain to them that purging is not the solution because the data  are still needed? the audit policy is at least 5-year retention. How can I prove that it is not related to performance issue?

  • 11. Re: Cleanup Production DB
    TSharma-Oracle Guru
    Currently Being Moderated

    I do not know if data is the issue or not. You have to capture the query and check the explain plan. Try running that query directly in sqlplus and check the execution plan. You have to rule out all the possibilities one by one.

  • 12. Re: Cleanup Production DB
    yxes2013 Newbie
    Currently Being Moderated

    But its hard to identify the query when its a 3rd party apps and lots of program are running on prod. Although I know the table which is being accessed? Maybe I can start from there. Thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points