oracle 11g database maintenance

Other than weekly backups and etc. what are good maintenance scripts, or jobs to help performance, and functionality of the oracle database.

Cleanup, compact, defrag, etc.

I am new to oracle, and etc.

The oracle database is for an application that runs on windows server 2008r2.

to do stuff on oracle db, I usually connect via powershell
LVL 5
IndyrbAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Most scripts are already out there.

Figure out what you want to look at and Google around.

There are also sites that have a LOT of them in one place.

For example:
http://oracle-base.com/dba/scripts.php
http://www.dbatoolz.com/sql-scripts


Since you aren't the DBA you will likely be very limited in what views you have access to.
0
 
slightwv (䄆 Netminder) Commented:
Weekly backups?  Doesn't it change that often?  Must not be a production database or at least not an important one.

As for what you need to do:
Hard to say.  Every database and DBA is different.  What is important to one, may not be important to another.

As long as you have disk space, memory and CPU available, you should be good.  Anything else is database/DBA specific.
0
 
Steve WalesSenior Database AdministratorCommented:
If you have a production database you're probably going to want a much more frequent backup cycle than weekly.

How much data can you stand to lose in the event of a failure ?

You may want to consider something more like an incremental level 0 backup once or twice a week, incremental level 1's on the other days and frequent archive log backups (hourly, half hourly or more frequently depending upon how critical your data is.

There are lots of logs and trace files routinely generated by an Oracle database.

I rename my alert logs weekly and keep the last several weeks on disk, just in case I need to review them, I also do the same with my listener.log.

Under your diag destination (usually %ORACLE_BASE%/diag)  you'll want to clean up trace files (*.trc & &.trm) older than some number of days (15, 30, 60, depending on your needs) and also there are XML versions of the alert log and listener log files that need cleanup as well.

All of this can be scripted with Windows CMD scripting (I'm not familiar with Powershell in detail but I'm sure it can do the same).
0
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

 
IndyrbAuthor Commented:
This will be the third time I try to reply to this, as I keep getting A Sync re1quired errors....

So I will copy and paste this time, and hope I put in all previous statemnets on other responces.

I was just curious if there was anything other than regular backups or updates.

On backups, it could run daily, or more.. incremental then full weekly... I am not sure.. it runs on a solaris server, I don't administer.  However I connect via powershell or command propmpt with the sqlplus command.

So I was curious is there oracle commands or scripts that would make my database more optimal per say.
defarg, compact, cleanup, or etc.

On the windows 2008 R2 server runs that application that has an oracle client _instant client installed.
The application uses java, and gerinimo web... along with proprietary software.

Just curious on what all I can do from the database side to make sure it runs the best way possible.
0
 
slightwv (䄆 Netminder) Commented:
I'm guessing you are wanting more from an application performance check not really a DBA check.

>>I am not sure.. it runs on a solaris server, I don't administer.

Then you shouldn't be worried about backups and such.  If you are, then you need to talk to your DBAs to see what/how they are doing it.

Backup up the OS isn't good enough for Oracle.

>>Just curious on what all I can do from the database side to make sure it runs the best way possible.

Hire a good DBA.

Many of them have scripts they have kept with them for many years for many different things.

Most of the things you would need to be concerned with from a performance aspect is also in the DBA realm.

If you are licensed for it the Performance Pack has a LOT of useful stats and reports of things you can check.  Look around for AWR and ADDM.  They also have tuning 'Advisors' that will make suggestions based on available statistics like index creation, data movement, etc...

If you are not licensed for it, I believe StatsPack is still around.

I will warn  you now that the outputs from those tools are more for DBAs not application folks.

They will tell you things like 'bad' performing SQL and a few things that may help from an application developer perspective.
0
 
IndyrbAuthor Commented:
well. id be interested in these orable scripts that people hold on or evolve from time... I believe these could help me please... ideas thoughts. thanks in advance. and thanks for your replies
0
 
IndyrbAuthor Commented:
I have own on my database? but not on the server or whole oracle dabases. just mine
0
 
slightwv (䄆 Netminder) Commented:
>>I have own on my database? but not on the server or whole oracle dabases. just mine

I do not understand what this is trying to tell me.
0
 
IndyrbAuthor Commented:
The database my application uses, I have an account that has ownership of the database... But only this database.
Not the whole Oracle Server or other objects, only the ones in my database -- ownership... Sorry, Didn't know if this was relevant or worth saying.
0
 
slightwv (䄆 Netminder) Commented:
What permissions you have on what database will limit your ability to 'monitor' certain things.

For example:
If the scripts you find query the DBA level views, you'll need access to those views.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.