We have an requirement where we have to send a monthly report on space usage and other details to management. Our shops has more than 600 DB. It's very difficult to get into each DB and generate these report. Luckily for us we use 10g OEM grid. So we have developed a script which uses owa_sylk (refer asktom) to generate the excel sheet. I am giving some of the code which we use.
Size of all the DB:
This script gives hostname,db wise details. The size will be equilent to sum(bytes) from dba_extents.
declare
output utl_file.file_type;
lv_target Varchar2(30) := 'ALL';
lv_file_name varchar2(30) ;
begin
lv_file_name := lv_target||'_DB_Size.slk';
output := utl_file.fopen( 'UTL_FILE_DIR', lv_file_name, 'w', 32000 );
owa_sylk.show(
p_file => output,
p_query => 'select target_name DB_NAME,host_name Host,sum(trunc(tablespace_used_size/1024/1024/1024,2))size_GB'||
' from MGMT$DB_TABLESPACES group by host_name,target_name order by target_name ',
p_sum_column =>
owa_sylk.owaSylkArray( 'N', 'N', 'Y'),
p_show_grid => 'NO' ,
p_user_heading => 'Size of ALL the DB as on '||sysdate);
utl_file.fclose( output );
end;
Space usage of Arch location:
This script generate space usage. This script may not work for all the environment, the pre-condition to it is, arch mountpoint should have "arch" keyword.
You need to create a table called RPT_ARCH_SIZES.
declare
output utl_file.file_type;
lv_host Varchar2(30) ;
lv_file_name varchar2(30) ;
begin
delete rpt_arch_sizes;
Insert into rpt_arch_sizes select target_name,
decode(instr(substr(mountpoint,instr(mountpoint,'/',-1)+1),'arch'),0,Upper(substr(mountpoint,instr(mountpoint,'/',-1)+1)),substr((substr(mountpoint,instr(mountpoint,'/',2)+1)),1,Instr(substr(mountpoint,instr(mountpoint,'/',2)+1),'/',1)-1)) database_name,
mountpoint,
trunc((sizeb/1024/1024/1024),2) Total_Size_GB ,
trunc((usedb/1024/1024/1024),2) Used_Space_GB,
trunc((freeb/1024/1024/1024),2) Available_GB,
trunc((trunc((usedb/1024/1024/1024),2)/trunc ((sizeb/1024/1024/1024),2))*100,2) perc_Used
from MGMT$STORAGE_REPORT_LOCALFS b
where mountpoint like '%arch%';
commit;
lv_file_name := 'All_Storage_arch.slk';
output := utl_file.fopen( 'UTL_FILE_DIR', lv_file_name, 'w', 32000 );
owa_sylk.show(
p_file => output,
p_query => 'select target_name,upper(database_name) db_name,mountpoint,Total_Size_GB ,Used_Space_GB, Available_GB,perc_free Perc_used '||
' from RPT_ARCH_SIZES order by perc_free desc ',
p_show_grid => 'NO' ,
p_user_heading => ' ARCHIEVELOG STORAGE REPORT OF ALL THE HOST as on '||sysdate);
utl_file.fclose( output );
end;
Backup information of all the DB:
The below script will give backup information. But in our environment, for some db the status and other columns are null. Need to look into that.
declare
output utl_file.file_type;
lv_target Varchar2(30) := 'All';
lv_file_name varchar2(30) ;
begin
lv_file_name := lv_target||'_Backup.slk';
output := utl_file.fopen( 'UTL_FILE_DIR', lv_file_name, 'w', 32000 );
owa_sylk.show(
p_file => output,
p_query => 'select host,Database_name,status,end_time,output_device_type '||
' from MGMT$HA_BACKUP order by end_time desc',
p_show_grid => 'NO' ,
p_user_heading => 'BACKUP REPORT Of all DB as on '||sysdate);
utl_file.fclose( output );
end;
DB Features:
This gives the list of DB features.
declare
output utl_file.file_type;
lv_target Varchar2(30) := 'oemrep';
lv_file_name varchar2(30) ;
begin
lv_file_name := lv_target||'_db_feature_info.slk';
output := utl_file.fopen( 'UTL_FILE_DIR', lv_file_name, 'w', 32000 );
owa_sylk.show(
p_file => output,
p_query => 'select Name,Currently_used,Description '||
' from MGMT$DB_FEATUREUSAGE '||
'where database_name=:HOSTNAME ',
p_parm_names =>
owa_sylk.owaSylkArray( 'HOSTNAME'),
p_parm_values =>
owa_sylk.owaSylkArray( lv_target ),
p_show_grid => 'NO' ,
p_user_heading => 'DB feature info of '||lv_target||' as on '||sysdate);
utl_file.fclose( output );
end;
Host Storage:
This script will give host storage.
declare
output utl_file.file_type;
lv_host Varchar2(30) := 'cas2s040';
lv_file_name varchar2(30) ;
begin
lv_file_name := lv_host||'_Storage.slk';
output := utl_file.fopen( 'UTL_FILE_DIR', lv_file_name, 'w', 32000 );
owa_sylk.show(
p_file => output,
p_query => 'select mountpoint,trunc((sizeb/1024/1024),2) Total_Size_MB ,trunc((usedb/1024/1024),2) Used_Space_MB, trunc((sizeb/1024/1024),2) Available_MB '||
' from MGMT$STORAGE_REPORT_LOCALFS '||
'where target_name=:HOSTNAME',
p_parm_names =>
owa_sylk.owaSylkArray( 'HOSTNAME'),
p_parm_values =>
owa_sylk.owaSylkArray( lv_host ),
p_sum_column =>
owa_sylk.owaSylkArray( 'N', 'Y', 'Y', 'Y'),
p_show_grid => 'NO' ,
p_user_heading => 'STORAGE REPORT Of '||lv_host||' as on '||sysdate);
utl_file.fclose( output );
end;
Redo log size:
This scripts gives the redo log file size for the inputted DB. Replace "oemrep" with your db name.
declare
output utl_file.file_type;
lv_target Varchar2(30) := 'oemrep';
lv_file_name varchar2(30) ;
begin
lv_file_name := lv_target||'_db_redo_info.slk';
output := utl_file.fopen( 'UTL_FILE_DIR', lv_file_name, 'w', 32000 );
owa_sylk.show(
p_file => output,
p_query => 'select group_num,members,thread_num,file_name,trunc(logsize/1024/1024,2) Redo_Size_MB,status '||
' from MGMT$DB_REDOLOGS '||
'where target_name=:HOSTNAME ',
p_parm_names =>
owa_sylk.owaSylkArray( 'HOSTNAME'),
p_parm_values =>
owa_sylk.owaSylkArray( lv_target ),
p_show_grid => 'NO' ,
p_user_heading => 'DB redolog info of '||lv_target||' as on '||sysdate);
utl_file.fclose( output );
end;
DB features:
DB features used in database. Change "oemrep" with your db name.
declare
output utl_file.file_type;
lv_target Varchar2(30) := 'oemrep';
lv_file_name varchar2(30) ;
begin
lv_file_name := lv_target||'_db_feature_info.slk';
output := utl_file.fopen( 'UTL_FILE_DIR', lv_file_name, 'w', 32000 );
owa_sylk.show(
p_file => output,
p_query => 'select Name,Currently_used,Description '||
' from MGMT$DB_FEATUREUSAGE '||
'where database_name=:HOSTNAME ',
p_parm_names =>
owa_sylk.owaSylkArray( 'HOSTNAME'),
p_parm_values =>
owa_sylk.owaSylkArray( lv_target ),
p_show_grid => 'NO' ,
p_user_heading => 'DB feature info of '||lv_target||' as on '||sysdate);
utl_file.fclose( output );
end;
Error Message:
Error message encountered by the DB in last 3 months. Replace "oemrep" with your db name.
declare
output utl_file.file_type;
lv_target Varchar2(30) := 'oemrep';
lv_file_name varchar2(30) ;
begin
lv_file_name := lv_target||'_Err_hist.slk';
output := utl_file.fopen( 'UTL_FILE_DIR', lv_file_name, 'w', 32000 );
owa_sylk.show(
p_file => output,
p_query => 'select collection_timestamp,Metric_name,Error_message '||
' from MGMT$METRIC_ERROR_HISTORY '||
'where target_name=:DATABASE_NAME order by 1',
p_parm_names =>
owa_sylk.owaSylkArray( 'DATABASE_NAME'),
p_parm_values =>
owa_sylk.owaSylkArray( lv_target ),
p_show_grid => 'NO' ,
p_user_heading => 'Error REPORT Of '||lv_target||' for last 3 months');
utl_file.fclose( output );
end;
DB init parameters:
This script will give init.ora parameters for DB. Change "oemrep" with your dbname.
declare
output utl_file.file_type;
lv_target Varchar2(30) := 'oemrep';
lv_file_name varchar2(30) ;
begin
lv_file_name := lv_target||'_db_param.slk';
output := utl_file.fopen( 'UTL_FILE_DIR', lv_file_name, 'w', 32000 );
owa_sylk.show(
p_file => output,
p_query => 'select Name,Value,isdefault '||
' from MGMT$DB_INIT_PARAMS '||
'where target_name=:HOSTNAME ',
p_parm_names =>
owa_sylk.owaSylkArray( 'HOSTNAME'),
p_parm_values =>
owa_sylk.owaSylkArray( lv_target ),
p_show_grid => 'NO' ,
p_user_heading => 'DB parameter info of '||lv_target||' as on '||sysdate);
utl_file.fclose( output );
end;
convert this post to pdf.