Oracle Grid control is one of the useful tools for DBAs and System administrators.
You can generate a documentation of your database using Oracle Grid Control with two click of mouse button.
You can acheive this using the report functionality in the grind control.
Grid Control management repository stores configuration and performance information of your database and provides many useful views.
The schema SYSMAN holds information of objects such as tables,tablespaces, views, triggers, packages, procedures, synonyms etc of target databases.
It also holds information of the host server including hardware and OS
Here i will share some of the SQLs used for generating report.
Grid control provides Dynamic Parameters of target and timeperiod. You can use this by including bind variable placeholders in your SQL statement.
For example; ??EMIP_BIND_TARGET_GUID?? - this variable will bind UID of the target for the report in your SQL statement.
1. To get Server Details
========================
Below SQL provides the hardare and OS details and present in a tabular format.
select
decode(d.rn,1,'Host Name', 2,'Domain',3,'Operating System',4,'System Arch',5,'Manufacturer',6,'Memory(MB)',7,'IP Address') as col1,
decode(d.rn,1,h.host_name,2,h.domain,3, h.os_summary, 4,h.system_config,5,h.ma, 6,h.MEM,7,p1.PROPERTY_VALUE) as col2
from MGMT$OS_HW_SUMMARY h , (select level as rn from dual connect by level <= 7) d, mgmt_targets t1, mgmt_target_properties p1
where h.TARGET_GUID=(select t.TARGET_GUID from MGMT$DB_DBNINSTANCEINFO_ALL i,MGMT$TARGET t
where t.target_name=i.host_name
and i.TARGET_GUID='2AE31523681AB4BD3F1BC404F83E979C')
and p1.PROPERTY_NAME='IP_address'
and p1.target_guid=t1.target_guid
and t1.TARGET_NAME=h.host_name
2. DB Instance Details
======================
select
decode(d.rn,1,'Database Name', 2,'Characterset',3,'Instance Name',4,'National Characterset',5,'Global Name',6,'Log Mode',7,'Version',8,'Default Temp Tblspce') as Property,
decode(d.rn,1,o.database_name, 2,o.characterset,3,o.instance_name,4,o.national_characterset,5,o.global_name,6,o.log_mode,7,o.banner,8,o.default_temp_tablespace) as Value
from mgmt$db_dbninstanceinfo o, (select level as rn from dual connect by level <= 8) d
where o.target_guid = ??EMIP_BIND_TARGET_GUID??
union
select 'DB Size(GB)', to_char(sum(FILE_SIZE)/1024/1024/1024) from MGMT$DB_DATAFILES
where TARGET_GUID=??EMIP_BIND_TARGET_GUID??
3. SGA Details
================
select
substr(sganame,1,length(sganame)-4) as Name,
decode(substr(sganame,length(sganame)-3,4),'(MB)',sgasize*1024,sgasize) as Value
from MGMT$DB_SGA
where TARGET_GUID=??EMIP_BIND_TARGET_GUID??
and sganame in ('Buffered Cache (MB)','Fixed SGA (KB)','Redo Buffers (KB)','Variable SGA (MB)')
4. Variable SGA details
=======================
select
substr(sganame,1,length(sganame)-4) as Name,
decode(substr(sganame,length(sganame)-3,4),'(MB)',sgasize*1024,sgasize) as Value
from MGMT$DB_SGA
where TARGET_GUID=??EMIP_BIND_TARGET_GUID??
and sganame like '%Pool%'
5. Non Default parameters
===========================
select Name,Value from MGMT$DB_INIT_PARAMS
where TARGET_GUID=??EMIP_BIND_TARGET_GUID??
and isdefault='FALSE'
6. Control File Details
=======================
select file_name as FileName,Status from MGMT$DB_CONTROLFILES
where TARGET_GUID=??EMIP_BIND_TARGET_GUID??
7. Intance Performace
======================
select column_label,value FROM SYSMAN.MGMT$METRIC_CURRENT
Where metric_name='instance_efficiency'
and target_guid=??EMIP_BIND_TARGET_GUID??
Above are some of the SQLs used to generate report. In addition to that you can get the details of the Redo Logs, Tablespaces, Datafiles, Invalid Objects, Patches applied, Users with power privileges etc.
