Hello Surya Prakash,
Your concern isn't very clear.
What are the maintenance tasks that you are currently doing ?
One thing I would surely suggest is getting servers rebooted once a month. that helps
I would recommend the following:
- Stop Application/Database Services
- Clear Apache/OPMN logs. Make sure you recreate the directory structure
- Clear/Move Core Dumps, Traces, Backup and Move Audit Files
- Check for Zombie OS processes. You can restart the Server also once if its ok.
- Check $APPLCSF/$APPLLOG location for largest files and move/remove files as per your policy
- Run/Schedule Purge Concurrent Programs(Logs, Workflow, Concurrent Programs etc.) as per policy
- Move/Delete APPLTMP, APPLPTMP files
Thanks for your reply, I have few more questions,
1) can we do this activity once in a month or is their any recommended time interval for this activity ?
2) Along with this activity we are also planing to do index rebuilding can it be helpful?
It purely depends on the activity/transactions and load on the system. Begin with 1 month and then make the interval less if you feel you need it. Over 2-3 months you will find the ideal time interval for maintenance.
Index rebuilding might not be of much use. If you move or compress (Advanced COmpression or HCC) table, then it will be useful. You can also consider the Advanced Compression for tables with less DML. Remember that Advanced Compression needs additional license.
You can use:
ANALYZE INDEX index_name COMPUTE STATISTICS
ANALYZE INDEX index_name VALIDATE STRUCTURE