Home » RDBMS Server » Performance Tuning » DB Health checks (oracle 11gr2 on linux)
DB Health checks [message #667019] Tue, 05 December 2017 05:49 Go to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
Hi Guys,

I want to do proactive db health check so catch any issue which may come in future

I am using many sql scripts for daily monitoring ,but want to cross check if i am missing anything

Please share what is critical db metrics to be monitored and logged,and which can be used for comparision if any issue arises

also i like to create monthly db dashboard which reflect db health status ,by which i can show to non-dba's ,which depicts database is in good shape

please share your views and related scripts

Thanks
Re: DB Health checks [message #667020 is a reply to message #667019] Tue, 05 December 2017 06:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
which metrics measure database health?

>I am using many sql scripts for daily monitoring
Please post them.
Re: DB Health checks [message #667033 is a reply to message #667020] Tue, 05 December 2017 09:45 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
scripts for like hit ratios,tablespace free space,top sql,sga usage etc
if other ways please suggest
Re: DB Health checks [message #667040 is a reply to message #667033] Tue, 05 December 2017 14:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

To post others you have to post an exhaustive list of those you have, doesn't it?

Re: DB Health checks [message #667045 is a reply to message #667040] Tue, 05 December 2017 23:00 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
Below is list of checks:
DB SESSION DETAILS(count group by app server).,REDO LOG GENERATION Trend for a day,status of db,datafiles,tempfile,listener ,backup etc,error in alert log,hit ratio,top io queries(10),top cpu queries(10),db cpu response,hard/soft parse percentage,sga usage,undo usage,long running sessions, queries having high parsing
Re: DB Health checks [message #667050 is a reply to message #667045] Wed, 06 December 2017 01:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you have all the scripts you mentioned then you have all information to determine you db health.

Re: DB Health checks [message #667053 is a reply to message #667050] Wed, 06 December 2017 02:56 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
so you mean its enough

Anyone if have more info on this please comment,lets make it as comprehensive as possible

Thanks
Re: DB Health checks [message #667059 is a reply to message #667053] Wed, 06 December 2017 03:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As comprehensive as you posted your scripts?

Re: DB Health checks [message #667062 is a reply to message #667059] Wed, 06 December 2017 03:59 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
haha Michel you some how want to close the post


Guys please share more info on this
Re: DB Health checks [message #667063 is a reply to message #667062] Wed, 06 December 2017 04:04 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Cone on, man. What are the scripts? What is their output? Post the code and the reports, then perhaps you will get some advice on whether they are meaningful or inadequate.
Re: DB Health checks [message #667066 is a reply to message #667062] Wed, 06 December 2017 04:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Your main problem is you didn't answer BlackSwan's question:

Quote:
which metrics measure database health?
Re: DB Health checks [message #667071 is a reply to message #667066] Wed, 06 December 2017 05:52 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
cmon leave my scripts,assume i have no scripts ,let me know what is database best practises for checking db health ,if know
Re: DB Health checks [message #667072 is a reply to message #667071] Wed, 06 December 2017 06:06 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Ah. So you have no scripts.
You could present what you think are "best practices" for your site, based on your knowledge of your environment. I do not believe that there are any "best practices" that apply to all sites. If there were, Oracle would implement them out-of-the-box for everyone.
Re: DB Health checks [message #667097 is a reply to message #667072] Thu, 07 December 2017 02:52 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
So How to check health of DB on day to day basis,what to check to ensure db is healthy and in case of issue what benchmark can use to compare
Re: DB Health checks [message #667098 is a reply to message #667097] Thu, 07 December 2017 02:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 06 December 2017 11:59

Your main problem is you didn't answer BlackSwan's question:

Quote:
which metrics measure database health?
Re: DB Health checks [message #667099 is a reply to message #667097] Thu, 07 December 2017 03:03 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
All right, I'll try to explain once more: I don't know what matters to your clients. You do.

However, if you really want to do something without considering what is needed, all I can suggest is that you use the dbms_hm package (hey, "hm" stands for "health monitor"!) to run the supplied checks:
orclx> SELECT name FROM v$hm_check;

NAME
------------------------------
HM Test Check
DB Structure Integrity Check
CF Block Integrity Check
Data Block Integrity Check
Redo Integrity Check
Logical Block Check
Transaction Integrity Check
Undo Segment Integrity Check
No Mount CF Check
Mount CF Check
CF Member Check
All Datafiles Check
Single Datafile Check
Tablespace Check Check
Log Group Check
Log Group Member Check
Archived Log Check
Redo Revalidation Check
IO Revalidation Check
Block IO Revalidation Check
Failover Check
Txn Revalidation Check
Failure Simulation Check
Dictionary Integrity Check
ASM Mount Check
ASM Allocation Check
ASM Disk Visibility Check
ASM File Busy Check
ASM Toomanyoff Check
ASM Insufficient Disks Check
ASM Insufficient Mem Check
ASM DGFDM Check No DG Name
ASM DG Force Dismount Check
ASM Sync IO Fail Check

34 rows selected.

orclx>
Does that help?
Re: DB Health checks [message #667101 is a reply to message #667099] Thu, 07 December 2017 05:43 Go to previous message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
Thanks John,

That makes sense,i was looking for something similar

Will explore it
Previous Topic: Running out of 64gb Temp Space
Next Topic: AWR Interpretation and analysis
Goto Forum:
  


Current Time: Thu Mar 28 05:54:49 CDT 2024