To Generate Details database reports(HTML SCRIPT)
set lines 300 pages 50000 trims on feed off termout off
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
col rep_name new_value rep_name
select 'dbreport_' || instance_name || '_' || to_char(sysdate,'DDMONYYYY_HH24MI') || '.html' rep_name from v$instance;
set termout on
prompt
prompt This script will generate &rep_name File.
prompt
prompt Please wait...
set termout off
spool &rep_name
prompt <style type='text/css'>
prompt body {font:10pt Arial,Helvetica,sans-serif; color:#663300; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}
prompt p {font:14pt Arial,Helvetica,sans-serif; color:#663300; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}
prompt table,tr,td {font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;}
prompt th {font:bold 10pt Arial,Helvetica,sans-serif; color:#336699; background:#cccc99; padding:0px 0px 0px 0px;}
prompt h1 {font:18pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99;
prompt margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;}
prompt h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;}
prompt a {font:9pt Arial,Helvetica,sans-serif; color:#663300; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}
prompt </style><title>SQL*Plus Report</title>
prompt <BR><BR><H1><center>Database Information Report</center></H1>
prompt <HR>
set head off pages 0;
select 'Report Generated on : ' || to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') from dual;
select '<p>' from dual;
set head on pages 50000;
set markup HTML on HEAD "<title>SQL*Plus Report</title>" BODY "BGCOLOR=RED" TABLE "border='1' align='center'" SPOOL OFF ENTMAP ON PREFORMAT OFF
Prompt
prompt Server Details
Prompt
select HOST_NAME "Host Name",(select value from v$osstat where STAT_NAME like 'NUM_CPU_CORES') "Cores Sockets",
(select value from v$osstat where STAT_NAME like 'NUM_CPUS') "CPUs",
(select value/1024/1024/1024 from v$osstat where STAT_NAME like 'PHYSICAL_MEMORY_BYTES') "Memory(GB)" from v$instance;
Prompt
Prompt Database Details
Prompt
select name db_name, log_mode, database_role, LPAD(force_logging,10) force_logging,
LPAD(supplemental_log_data_min,10) SupLog_Min,
lpad(supplemental_log_data_pk,10) SUPLOG_PK,
LPAD(supplemental_log_data_ui,10) SUPLOG_UI,
LPAD(supplemental_log_data_fk,10) SUPLOG_FK,
LPAD(supplemental_log_data_all,10) "SUPLOG_ALL",
to_char(created, 'MM-DD-YYYY HH24:MI:SS') Created,
PLATFORM_NAME, FLASHBACK_ON
from v$database;
Prompt
Prompt Instance Details
Prompt
select instance_number inst_no, instance_name, host_name, startup_time, thread#, database_status from gv$instance;
Prompt
Prompt Database Properties
Prompt
SELECT property_name,
property_value
FROM database_properties
ORDER BY property_name;
Prompt
Prompt Database Version Details
Prompt
select banner "Database Component Details" from v$version;
Prompt
Prompt Database Features already being used
Prompt
SELECT NAME, DETECTED_USAGES, CURRENTLY_USED, FIRST_USAGE_DATE
FROM DBA_FEATURE_USAGE_STATISTICS
WHERE VERSION = (SELECT VERSION FROM V$INSTANCE) AND
(DETECTED_USAGES > 0 OR CURRENTLY_USED != 'FALSE');
Prompt
Prompt Object Count by Schema
Prompt
select owner, count(*) from dba_objects
where owner not in ('SYS','SYSTEM','OUTLN','DBSNMP')
group by owner
order by owner;
Prompt
Prompt Object Count by Schema, Object Type
Prompt
select owner, object_type, count(*) objects from dba_objects
where owner not in ('SYS','SYSTEM','OUTLN','DBSNMP')
group by owner, object_type
order by owner, object_type;
Prompt
Prompt Control File Details
Prompt
select name from sys.v$controlfile;
Prompt
Prompt Redo Log File Locations
Prompt
select a.group#,b.thread#,a.member,b.bytes/1024/1024 as size_mb
from sys.gv_$logfile a,
v$log b
where a.group#=b.group#;
Prompt
Prompt Data Files Details
Prompt
select F.file_id "FILE#",
F.tablespace_name tablespace,
F.file_name,
F.bytes/(1024*1024) size_mb,
decode(F.status,'AVAILABLE','OK',F.status) status
from sys.dba_data_files F
order by tablespace_name, FILE#;
Prompt
Prompt Tablespace Usage
Prompt
select a.tablespace_name,
round(nvl(a.asize,0)) "TOTAL",
round(nvl(a.asize-nvl(f.free,0),0)) "USED",
round(nvl(f.free,0)) "FREE",
round(((nvl(a.asize-f.free,0))/a.asize)*100) "USED %",
round((nvl(f.free,0)/a.asize)*100) "FREE %"
from (select tablespace_name, sum(bytes)/1024/1024 "ASIZE"
from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) free,round(max(bytes)/1024/1024) maxfree
from dba_free_space
group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by round((nvl(a.asize-f.free,0)/a.asize)*100);
Prompt
Prompt Redo Generation per Day
Prompt
select trunc(completion_time) rundate, inst_id "instance", count(*) logswitch, round((sum(blocks*block_size)/1024/1024)) redo_per_day
from gv$archived_log
where trunc(completion_time) >= to_date(to_char(sysdate,'dd-mon-rr'),'dd-mon-rr') - 7
group by trunc(completion_time), inst_id
order by 1;
Prompt
Prompt Debug Enabled Objects
Prompt
select OWNER,OBJECT_NAME,OBJECT_TYPE,LAST_DDL_TIME from sys.ALL_PROBE_OBJECTS where debuginfo='T';
Prompt
Prompt Resource Utilization
Prompt
select * from gv$resource_limit;
Prompt
Prompt Tables with CLOB,BLOB,LONG,NCLOB or LONG RAW Columns
Prompt
SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM all_tab_columns
WHERE OWNER not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')
AND data_type in ('CLOB', 'BLOB', 'LONG', 'LONG RAW', 'NCLOB', 'LOB')
ORDER BY OWNER;
Prompt
Prompt Initialization parameters having non-default values
Prompt
select name, value from v$parameter where ISDEFAULT <> 'TRUE' order by name;
Prompt
Prompt RMAN backup information
Prompt
select DISTINCT c.name, decode(BACKUP_TYPE,'D','DB INCR BACKUP LEVEL 0','I','DB INCR BACKUP LEVEL 1','L','ARCHIVELOG BACKUP',backup_type) backup1,
decode(b.status,'COMPLETED','SUCCESSFUL','FAILED','FAILED','RUNNING','RUNNING','COMPLETED WITH WARNINGS') status,a.DEVICE_TYPE DEVICE_TYPE,
B.START_TIME, B.end_time, b.TIME_TAKEN_DISPLAY elapse_time, (case WHEN backup_type = 'L' then round((sum(original_input_bytes) over(Partition by a.session_stamp,b.start_time,
command_id,a.session_recid order by (b.start_time)))/1024/1024,1) ELSE NULL END) al_INPUT_BYTES_mb,
(case WHEN backup_type in('D','I') then round((sum(original_input_bytes) over(Partition by a.session_stamp,b.start_time,command_id,
a.session_recid order by (b.start_time)))/1024/1024,1) ELSE NULL END) db_INPUT_BYTES_mb, round((sum(a.output_bytes) over(partition by
a.session_stamp,b.start_time,command_id,a.session_recid order by (b.start_time)))/1024/1024,2) output_bytes_mb
from V$BACKUP_SET_DETAILS a,
V$rman_backup_job_details b,
V$database c
where a.session_recid=b.session_recid
and a.session_stamp=b.session_stamp
order by B.start_time desc;
set markup html off;
SET PAGES 0 HEAD OFF FEED OFF;
select '<BR><BR>' FROM DUAL;
SET PAGES 50000 HEAD ON FEED ON;
spool off;
set termout on
prompt Done.
prompt Please check &rep_name file for errors, if any.
Prompt
By Prasanth Bhaskaran (Oracle PeopleSoft Application and Upgrade Specialist )
