Home » Infrastructure » Unix » Oracle Assessment, Metrics and Monitoring (Oracle 8i, 9i, 10g on HPUX and HP Itanium)
 Oracle Assessment, Metrics and Monitoring [message #347111] |
Wed, 10 September 2008 14:09  |
techvader01
Messages: 17
Registered: August 2006
Location: Los Angeles
|
Junior Member |
|
|
I have been tasked with the following questions in regards to our Oracle database systems across my company's enterprise.
How many databases do we have that are not used and are just sitting idle?
How much over allocation of Database storage do we have?
Can we create a process or purchase a system that will measure or monitor Database usage and storage?
We have several hundred Oracle database instances running on a substantial number of HP servers. Some of our databases are RAC but most are not. I am looking for a clean solution to the questions above. Any help to point me in the right direction would be greatly appreciated. Thank you.
|
|
|
|
| Re: Oracle Assessment, Metrics and Monitoring [message #441557 is a reply to message #347111] |
Mon, 01 February 2010 22:03  |
oratab
Messages: 5
Registered: April 2008
Location: Atlanta and/or Kansas Cit...
|
Junior Member |
|
|
Your approach to this will probably depend on capabilities you've been given.
For instance:
(1) the level of permission/access you have for each server
...both unix permissions, and Oracle DBA access
(2) whether you have a TNSNAMES file (or equiv. documentation)
that will allow you to write SQL scripts to connect to
all DB servers, remotely and execute centeralized queries.
(3) other tools, such as OEM you have at your disposal
(4) db_links to central/admin database, allowing remote queries
(5) ability to run ssh or rsh scripts on all these servers
(6) your tools for distribution+collection of files/scripts/logs
(7) instructions from your Boss and department, about what you
can/not change on each server: such as cron jobs, scripts,
triggers, DB-Auditing, configs,
This is not going to be a simple or quick, with hundreds of databases, and probably many different configurations.
My first thoughts, to assess level of activity would be:
>>> Check the "AWR" statistics, to see what info compares
between different servers, for MANY actvity indicators.
If several of your databases are older, you may need to
use statspack info instead.
>>> Run querys every hour, to gather V$SESSION information
with details from application/users. (and not listing
just DBA+Internal connections) This won't tell you how
MUCH work is being done, but how many users/apps are
connected to the database, each tiem you run the query.
>>>> Determine "general" level of database CHANGE/UPDATE
activity for each database, by finding the total size (Meg)
of DB-Archive.LOGs that are being generated by each database.
Possible sources of this info:
Query each database V$Archived_log or V$Archive_log_summary
You may be able to get unix/system/backup logs from a central
backup server, if Archive.logs are run as a seperate group/pool.
You may also decide to scan the Alert.log for redo log switch
information, indicating levels of change/update activity.
OEM may help you collect query data across many servers, if
it has been setup/configured for your data center.
...
(This will *not* tell you what data is being READ, just written)
>>>> Check with your unix sysadmin team, and see if they are
using any sort of performance measuring tool, to show
levels of activity/load for CPU or filesystems.
May even be able to get "sar" reports from sysadmins, to
look at read/write activity levels on Oracle filesystems.
Make sure to weed out READ activity during backup times.
>>>> for DB-Size information, you can always run queries of:
SELECT tablespace_name,round(sum(bytes)/1024/1204) MEG
FROM dba_data_files group by tablespace_name;
>>>> You can use some of the Oracle Audit features, to track
every database login/connect - and report on them weekly.
>>>> If you want to get elaborate, you can even create
database LOGOUT triggers to run several admin queries each
time a session ends, collecting user,time-in/out, number
of logical reads/writes for each session, and more.
It just depends on what you KNOW how to do, and what you are ALLOWED to do with each server.
Got any ideas yet ???
|
|
|
|
Goto Forum:
Current Time: Mon Dec 02 06:26:23 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
