Querying grid control tables to find the size and name of the databases http://www.orafaq.com/forum/./mv/msg/184219/567189/#msg_567189
Can anyone tell me the query to get the name and size of all the databases associated to grid..

I need to find this to do capacity planning in our environment..

Thanks in advance..

]]>srinirandy2012-09-25T06:46:04-00:00Re: Querying grid control tables to find the size and name of the databases http://www.orafaq.com/forum/./mv/msg/184219/567207/#msg_567207
howtodba.com/old/165/storage-usage-for-backup-locations-from-oracle-grid-database

Get storage usage, for $ORACLE_HOME, from Oracle Grid Database:
howtodba.com/old/164/how-storage-usage-for-%24oracle_home-from-oracle-grid-database

Get storage usage, for datafiles, from Oracle Grid Database:
howtodba.com/old/161/how-get-storage-usage-for-datafiles-from-oracle-grid-database

I've used the queries in the links above for some Oracle Grid Dashboard, last month.
]]>howtodba2012-09-25T09:51:47-00:00Re: Querying grid control tables to find the size and name of the databases http://www.orafaq.com/forum/./mv/msg/184219/567257/#msg_567257
I can see the sample data in your website as given below

so for capacity planning i need to collect this information and again store it in a table

can u tell me the table that stores this historical data. i mean in grid we can see the database growth graph how they are fetching historical information.

It will be very helful if u tell the table or views that is used by grid control to show us the historical growth of database.

Thanks in advance.]]>srinirandy2012-09-25T17:31:13-00:00Re: Querying grid control tables to find the size and name of the databases http://www.orafaq.com/forum/./mv/msg/184219/567281/#msg_567281
My queries were made over the view "MGMT$METRIC_DAILY", for daily data about DB parameters.

If you need historic data also, have a look at the other 2 views: MGMT$METRIC_HOURLY & MGMT$METRIC_DAILY

So, in order to get some hist data on datafile size (for example), you'll replace MGMT$METRIC_DAILY with MGMT$METRIC_DAILY in those queries.

* The tables are in SYSMAN schema.
* To query the data in the 3 tables above, you'll need to know the values for "TARGET_GUID" & "METRIC_GUID".
* To make things easier, instead of querying the tabels, use the below 3 views (created over those tables). This views include the columns "target_name" and "metric_name", so it will be easier to search for the params you want.

MGMT$METRIC_DETAILS
MGMT$METRIC_HOURLY
MGMT$METRIC_DAILY

# To change the retention for a given parameter, you can use:
# -----------------------------------------------------------
INSERT INTO SYSMAN.MGMT_PARAMETERS
(PARAMETER_NAME, PARAMETER_VALUE,PARAMETER_COMMENT)
VALUES
(mgmt_day_keep_window,'720','Keep daily agg data for 720 days');

]]>howtodba2012-09-26T07:18:40-00:00Re: Querying grid control tables to find the size and name of the databases http://www.orafaq.com/forum/./mv/msg/184219/567775/#msg_567775
sorry to disturb you again
I jusz modified your query as given below for getting the historical information in a monthly basis
select
r1.Database_Name Database_Name,
r1.MachineName Server_Name,
AllocatedSpace_GB,
UsedSpace_GB
from
(
select
Database_Name,
AllocatedSpace_GB,
UsedSpace_GB,
MachineName
from
(
select q1.target_name Server_Name,q1.AllocatedSpace_GB,q2.UsedSpace_GB
from
(select
target_name,
avg(AVERAGE) AllocatedSpace_GB
from
sysman.mgmt$METRIC_DAILY
where
metric_name like 'DATABASE_SIZE'
and column_label like 'Allocated Space(GB)'
and rollup_timestamp>=sysdate-120 and rollup_timestamp<=sysdate-90
group by target_name
) q1,
(
select
target_name,
avg(AVERAGE) UsedSpace_GB
from
sysman.mgmt$METRIC_DAILY
where
metric_name like 'DATABASE_SIZE'
and column_label like 'Used Space(GB)'
and rollup_timestamp>=sysdate-90 and rollup_timestamp<=sysdate-60
group by target_name
) q2
where
q1.target_name=q2.target_name
)w1,
(
select
Database_Name,
MachineName
from
(
select
target_name Database_Name,
HOST_NAME MachineName
from
SYSMAN.MGMT_TARGETS
where
TARGET_TYPE = 'oracle_database' or TARGET_TYPE= 'rac_database'
)
) w2
where
w1.Server_Name=w2.Database_Name
) r1

my question is why no of rows selected is different in two timestamps???

and can we get the sizes for all the databases in mgmt_targets
AS i showed it is 220

Thanks in advance

]]>srinirandy2012-10-05T09:11:13-00:00Re: Querying grid control tables to find the size and name of the databases http://www.orafaq.com/forum/./mv/msg/184219/567777/#msg_567777
If you don't know how then use SQL Formatter.
And Please read How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.Use the "Preview Message" button to verify.

Regards
Michel

[Edit: missing word]]]>Michel Cadot2012-10-05T09:26:39-00:00Re: Querying grid control tables to find the size and name of the databases http://www.orafaq.com/forum/./mv/msg/184219/567779/#msg_567779
As Michel said, please format your query, and post some lines from the output you get from each query.
You ask about some differances in timestamps ? We cannot reply if you don't show us the values .]]>howtodba2012-10-05T09:32:15-00:00