Oracle 10g Database Housekeeping

15 pts.
Tags:
Database housekeeping
Database maintenance
Oracle 10g
Oracle Database
i've been asked to work on database housekeeping, i don't know what i should do?

please advice



Software/Hardware used:
Oracle 10g
ASKED: May 17, 2010  8:28 PM
UPDATED: May 18, 2010  2:40 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

If I were you, I would ask for clarification of what is expected. Database housekeeping could include any number of things. It might be cleaning up audit (*.aud) and trace (*.trc) files, or backing up and removing archived redo logs. It could be checking internal audit storage or making sure large tables aren’t getting too unruly… maybe they need to be partitioned, or archived to another location. It’s possible that they want indexes rebuilt or user accounts removed for old employees… just any number of things could be included. Housekeeping is just a very general term, so unless you are sure of what is expected, I would ask questions.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • DB10g
    Dear Jcmdba; thanks for your answer which provided me the area i should work on, and i would appreciate if i can get more details and scripts to apply the database housekeeping, my manager told me he needs this housekeeping to maintain database growth and enhance performance so he would like me to do all the point you referred to Like (table partitioned, cleaning up audit and trace file, backing up and removing archived red logs, and rebuilt indexes) i'm just new in career of DBA and i don't know how to do it, i would really appreciate any tips or scripts to achieve this task thanks a lot
    15 pointsBadges:
    report
  • Jcmdba
    DB10g, Some of these tasks can be handled with basic scripts, but all need to be customized to your environment. If you are on UNIX, a script as simple as a find command could clean up audit and trace files. For example: find . -name "*.trc" -ctime +10 -exec rm {} ; would find trace files older than 10 days and delete them for you... The same logic could be applied to audit files. For backup of redos, that will depend on your backup/recovery tools available. In my case, I have TSM which has a built in delete option in the basic dsmc command. You will need to work with your backup team to determine the appropriate commands. For rebuilding indexes, if you simply want to rebuild all of them back into their existing tablespaces, you can use a simple script which generates another sql script and then executes it... something like: set echo off pages 0 head off spool rebuildindexes.sql select 'alter index '||segment_name||' rebuild tablespace '||tablespace_name||';' from user_segments where segment_type='INDEX'; spool off set echo on feedback 1 tab off pages 63 spool index_rebuild.log @rebuildindex.sql But this is very simplistic, you will need to consider your index types, space available, and a host of other things specific to your environment. I would suggest checking out Oracle documentation for built in packages and procedures as well. Be sure to test anything you write in an appropriate test environment before applying any activity to a production environment, especially if you are working in a regulated industry. Good luck.
    555 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following