Silent mode Oracle RAC installation

Install Oracle clusterware and database software using response file as below.
------------------------------------------------------------------------------


1. To create a response file, start the OUI with the following command and perform an installation as normal.

./runInstaller -record -destinationFile /tmp/10gR2.rsp

2. A silent installation is initiated using the following command


./runInstaller -silent -responseFile /tmp/10gR2.rsp



Database creation using silent mode.
------------------------------------


dbca -createDatabase -silent -templateName General_Purpose.dbc -gdbName dtrac -sysPassword vcs -systemPassword vcs -emConfiguration NONE -disableSecurityConfiguration ALL -datafileDestination /oradata -storageType CFS -nodelist node1,node2


You could get template information from:

$ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc

How can the ASM instance start when the spfile is located on a diskgroup

SQL> show parameter pfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/asm/asmparameterfile/registry.253.729008643

The location of the initialization parameter file specified Grid Plug and Play.
However in the GPnP file, the OLR (Oracle Local Registry) and the OCR (Oracle Cluster Registry) they have the entries to identify the DISCOVERY String and the spfile location.
So ASM can startup but will then access the SPFILE on the ASM diskgroup.

At startup, the Oracle ASM instance attempts to mount the following disk groups:
* Disk groups specified in the ASM_DISKGROUPS initialization parameter
* Disk group used by Cluster Synchronization Services (CSS) for voting files
* Disk groups used by Oracle Clusterware for Oracle Cluster Registry (OCR)
* Disk group used by the Oracle ASM instance to store the ASM server parameter file (SPFILE)

Monitor user session.

set linesize 220
col first_load_time format a20
col client_info format a20
col username format a20
col last_load_time format a20
col sql_text format a30
select a.sid,a.serial#,a.client_info,a.username,a.logon_time,a.status,b.sql_text,a.last_call_et,b.first_load_time,b.last_load_time
from v$session a ,v$sql b
where a.sql_id=b.sql_id(+)
and a.sid='&sid'

OS Watcher (OSW

1. OS Watcher (OSW) is a collection of UNIX shell scripts intended to collect and archive operating system and network metrics to aid support in diagnosing performance issues.
   
   It collected statastics for following commnad
   ps
   top
   mpstat
   iostat
   netstat
   traceroute
   vmstat
   
   Steps to use it.
   1. Download it from oracle metalink 301137.1
   2. How to install it-
   tar -xvf osw212.tar
   3. How to start it
   ./startOSW.sh 60 1
   where
   ARG1 = snapshot interval in seconds.
   ARG2 = the number of hours of archive data to store.
   4. More details given
   https://metalink2.oracle.com/metalink/plsql/docs/OSW_UG.htm

Get DDL command from SQL query

select dbms_metadata.get_ddl('TABLE','table name') FROM DUAL;

e.g:

select dbms_metadata.get_ddl('TABLE','ORDR') FROM DUAL;

View Free space from temporary tablespace

select substr(a.tablespace_name,1,20) "TS_name", substr(a.file_name,1,35) "File_Name",
round(sum(a.bytes)/1024/1024,2) "Size_MB",sum(NVL(b.bytes,0)) "FreeSpace" from dba_TEMP_files a,
(select substr(tablespace_name,1,20) tablespace_name,file_id, round(sum(bytes_used)/1024/1024,2) used_sp,
round(sum(blocks_free * 8192)/1024/1024,2) bytes from v$temp_space_header group by substr(tablespace_name,1,20),file_id ) b where
a.tablespace_name=b.tablespace_name(+) and a.file_id=b.file_id(+) group by
substr(a.tablespace_name,1,20), substr(a.file_name,1,35)
/

View database size from sql

COLUMN "Total Mb" FORMAT 999,999,999.0
COLUMN "Redo Mb" FORMAT 999,999,999.0
COLUMN "Temp Mb" FORMAT 999,999,999.0
COLUMN "Data Mb" FORMAT 999,999,999.0

Prompt
Prompt "Database Size"

select (select sum(bytes/1048576) from dba_data_files) "Data Mb",
(select NVL(sum(bytes/1048576),0) from dba_temp_files) "Temp Mb",
(select sum(bytes/1048576)*max(members) from v$log) "Redo Mb",
(select sum(bytes/1048576) from dba_data_files) +
(select NVL(sum(bytes/1048576),0) from dba_temp_files) +
(select sum(bytes/1048576)*max(members) from v$log) "Total Mb"
from dual;

Migrate OCR and Vote disk on ASM storage

Storing the OCR and vote disk inside ASM eliminates the need for third party cluster and volume managers and also helps eliminate the complexity of managing separate disk partitions for the OCR in oracle clusterware installations

Steps to migrate OCR and Vote disk on ASM storage:

1. Create ASM diskgroup using ASMCA


2. Voting disk on raw before migration

(root@mysystem) /oracle/product/grid/bin/crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 63b1d7539d25ef7affff433cbdc40f28 (/dev/vx/rdsk/ocrvotedg/votevol) []
Located 1 voting disk(s).


3. Relpacing votedisk from raw to ASM
(root@mysystem) /oracle/product/grid/bin/crsctl replace votedisk +ocrdg

4. Vote disk on ASM after migration
===================================
(root@mysystem) /oracle/product/grid/bin/crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 96c7f0f903174fd1bf15432ad5c6dee6 (/dev/vx/rdsk/asmdg/crsvol1) [OCRDG]
2. ONLINE 14e557f987874f36bf1f116c8a51ba6c (/dev/vx/rdsk/asmdg/crsvol2) [OCRDG]
3. ONLINE 0b1961148b674f00bf64b21ea4e6a126 (/dev/vx/rdsk/asmdg/crsvol3) [OCRDG]
Located 3 voting disk(s).


5. View existing OCR configuration before Migration
(root@mysystem)[/] /oracle/11.2.0/gridhome/bin/ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 302956
Used space (kbytes) : 5820
Available space (kbytes) : 297136
ID : 1087500263
Device/File Name : /dev/vx/rdsk/ocrvotedg/ocrvol
Cluster registry integrity check succeeded

6. ADD new OCR by providing asm diskgroup
( root@mysystem)[/] /oracle/11.2.0/gridhome/bin/ocrconfig -add +ocrdg

7. OCRCHECK after adding ASMDG for OCR
=========================================
(root@mysystem)[/] /oracle/11.2.0/gridhome/bin/ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 302956
Used space (kbytes) : 5820
Available space (kbytes) : 297136
ID : 1087500263
Device/File Name : /dev/vx/rdsk/ocrvotedg/ocrvol
Device/File integrity check succeeded
Device/File Name : +ocrdg
Cluster registry integrity check succeeded

Logical corruption check succeeded

8. DELETING Old OCR

(root@mysystem)[/] /oracle/11.2.0/gridhome/bin/ocrconfig -delete /dev/vx/rdsk/ocrvotedg/ocrvol


9. OCRCHECK after deleting old OCR
=====================================

(root@mysystem)[/] /oracle/11.2.0/gridhome/bin/ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 302956
Used space (kbytes) : 5820
Available space (kbytes) : 297136
ID : 1087500263
Device/File Name : +ocrdg
Device/File integrity check succeeded
Cluster registry integrity check succeeded

Migrate Oracled database from Admin managed to Policy Managed

Two Management Styles for Oracle RAC. Policy managed database introduced in oracle 11gr2


Steps to migrate Oracled database from Admin managed to Policy Managed

1. Add server pool
srvctl add srvpool -g oradbsrv -l 4 -u 4 -i 999 -n slial01,slial02,slial03,slial04

2. Check configuration of serverpool
srvctl config srvpool

3. Modify database with new serverpool created in step1
srvctl modify database -d oradb -g oastsrv

4. Check configuration of serverpool
srvctl config database -d oradb -a

5. Check status base of database and name of instances since its will changes after restarting instances
srvctl status database -d oradb

6. Stop and start DB
srvctl stop database -d oradb
srvctl start database -d oradb

5. Check status base of database with new name of instances
srvctl status database -d oradb

Note: New instance name will be diffrent from instance name before migration.

Quicktune database - Collect DB info from Script

-------------------------------------------------------------------------------------------------------
-- Reportingquicktune.sql
-------------------------------------------------------------------------------------------------------
---******************************************************
--- Change location of spool file as per directory
--- Execute Reportingquicktune.sql file on SQL prompt
---SQL> @/quicktune.sql
---******************************************************
SET FEEDBACK OFF;
SET HEADING ON;
SET ECHO OFF;
SET PAGESIZE 50000;
SET TRIMSPOOL ON
SET LINESIZE 122;
CLEAR SCREEN;

spool \quicktune.log;

Prompt ============== Start Time ==============
prompt
SELECT TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MI') StartTime
FROM DUAL ;
prompt
prompt
Prompt ============== Instance Information ==============
prompt
prompt
SET LINESIZE 300;
SELECT * FROM v$instance ;
prompt
prompt
SET LINESIZE 122;
prompt =======================================================================================================
prompt QUICK TUNE PARAMETERS
prompt =======================================================================================================
prompt
prompt DATABASE INFO
column date_of_run format a30;
column open_time format a30;
column block_size_bytes format a20;

select d.name DATABASE,
to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') DATE_OF_RUN,
to_char(open_time, 'DD-MON-YYYY HH24:MI:SS') OPEN_TIME,
value BLOCK_SIZE_BYTES
from v$database d,
v$parameter p,
v$thread t
where p.name = 'db_block_size';

prompt
prompt
prompt =======================================================================================================
prompt
prompt DB BUFFER CACHE HIT RATIO
column "DB BUFFER CACHE HIT RATIO" format 999,999,999,999,999,999.9999

select (1-c.value/(a.value+b.value+0.000001))*100 "DB BUFFER CACHE HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c, v$sysstat d
where a.name = 'db block gets' -- 'logical_reads' = 'db block gets' + 'consistent gets'
and b.name = 'consistent gets'
and c.name = 'physical reads'
and d.name = 'physical writes';

prompt
prompt
prompt =======================================================================================================
prompt
prompt DICTIONARY CACHE HIT RATIO
column "DICTIONARY CACHE HIT RATIO" format 999.9999

select (1-sum(getmisses)/sum(gets))*100 "DICTIONARY CACHE HIT RATIO"
from v$rowcache;

prompt
prompt
prompt =======================================================================================================
prompt
prompt LIBRARY CACHE HIT RATIO
column "LIBRARY CACHE HIT RATIO" format 999.9999

select (1-sum(reloads)/sum(pins))*100 "LIBRARY CACHE HIT RATIO"
from v$librarycache;

prompt
prompt
prompt =======================================================================================================
prompt
prompt LIBRARY CACHE - GET and PIN HIT RATIO
column "LIBRARY CACHE GET HIT RATIO" format 999.9999
column "LIBRARY CACHE PIN HIT RATIO" format 999.9999

select min(gethitratio)*100 "LIBRARY CACHE GET HIT RATIO",
min(pinhitratio)*100 "LIBRARY CACHE PIN HIT RATIO"
from v$librarycache
where namespace IN ('SQL AREA', 'TABLE/PROCEDURE', 'BODY', 'TRIGGER');

prompt
prompt
prompt =======================================================================================================
prompt
prompt REDO BUFFER ALLOCATION RETRIES RATIO
column "REDO ALLOCATION RETRIES RATIO" format 999.9999

select a.value/(b.value+0.0001)*100 "REDO ALLOCATION RETRIES RATIO"
from v$sysstat a, v$sysstat b
where a.name = 'redo buffer allocation retries'
and b.name = 'redo writes';

prompt
prompt
prompt =======================================================================================================
prompt
prompt REDO BUFFER SPACE REQUEST RATIO
column "REDO SPACE REQUEST RATIO" format 999.9999

select a.value/(b.value+0.0001)*100 "REDO SPACE REQUEST RATIO"
from v$sysstat a, v$sysstat b
where a.name = 'redo log space requests'
and b.name = 'redo entries';

prompt
prompt
prompt =======================================================================================================
prompt
prompt FREE_MEMORY/SHARED_POOL RATIO
column "FREE_MEMORY/SHARED_POOL RATIO" format 999.9999

select min(least((b.bytes/(c.value+0.0001)),1)*100) "FREE_MEMORY/SHARED_POOL RATIO"
from v$sgastat b, v$parameter c
where b.name = 'free memory' -- sometimes 'free memory' shows all RAM
and c.name = 'shared_pool_size';

prompt
prompt
prompt =======================================================================================================
prompt
prompt FREE_MEMORY/SHARED_POOL_RESERVED RATIO
column "FREE_MEMORY/SP_RES RATIO" format 999.9999

select min(least((b.bytes/(c.value+0.0001)),1)*100) "FREE_MEMORY/SP_RES RATIO"
from v$sgastat b, v$parameter c
where b.name = 'free memory' -- sometimes 'free memory' shows all RAM
and c.name = 'shared_pool_reserved_size';

prompt
prompt
prompt =======================================================================================================
prompt
prompt SHARED POOL SIZES RATIOS
column "SP_RES/SP RATIO" format 999.9999
column "SP_RES_MIN_ALLOC/SP_RES RATIO" format 999.9999

select (b.value/(a.value+0.0001))*100 "SP_RES/SP RATIO",
(to_number(replace(c.value,'K','000'))/(b.value+1000))*100 "SP_RES_MIN_ALLOC/SP_RES RATIO"
from v$parameter a, v$parameter b, v$parameter c
where a.name = 'shared_pool_size'
and b.name = 'shared_pool_reserved_size'
and c.name = 'shared_pool_reserved_min_alloc';

prompt
prompt
prompt =======================================================================================================
prompt
prompt SHARED POOL REQUESTS RATIOS
column "REQUEST MISSES RATIO" format 999.9999
column "REQUEST FAILURES RATIO" format 999.9999

select (request_misses/(requests+0.0001))*100 "REQUEST MISSES RATIO",
(request_failures/(requests+0.0001))*100 "REQUEST FAILURES RATIO"
from v$shared_pool_reserved;

prompt
prompt
prompt =======================================================================================================
prompt
prompt SHARED POOL RESERVED MINALLOC SIZE RATIOS
column "SP_RES_MIN_ALLOC/LAST_FAIL" format 999.9999
column "SP_RES_MIN_ALLOC/AVG_FREE" format 999.9999

select (to_number(replace(a.value,'K','000'))
/greatest(b.max_used_size,b.last_failure_size,(to_number(replace(a.value,'K','000')))))*100
"SP_RES_MIN_ALLOC/LAST_FAIL",
(to_number(replace(a.value,'K','000'))
/greatest(b.avg_free_size,(to_number(replace(a.value,'K','000')))))*100
"SP_RES_MIN_ALLOC/AVG_FREE"
from v$parameter a, v$shared_pool_reserved b
where a.name = 'shared_pool_reserved_min_alloc';

prompt
prompt
prompt =======================================================================================================
prompt
prompt SORT AREA SIZE RATIOS
column "SORT_AREA/SHARED_POOL" format 999.9999
column "SA_RETAINED/SORT_AREA" format 999.9999

select (b.value/(a.value+0.0001))*100 "SORT_AREA/SHARED_POOL",
(c.value/(b.value+0.0001))*100 "SA_RETAINED/SORT_AREA"
from v$parameter a, v$parameter b, v$parameter c
where a.name = 'shared_pool_size'
and b.name = 'sort_area_size'
and c.name = 'sort_area_retained_size';

prompt
prompt
prompt =======================================================================================================
prompt
prompt SORT IN MEMORY/TOTAL RATIO
column "SORT IN MEMORY/TOTAL RATIO" format 999.9999

select (a.value/(a.value+b.value+0.0001))*100 "SORT IN MEMORY/TOTAL RATIO"
from v$sysstat a, v$sysstat b
where a.name = 'sorts (memory)'
and b.name = 'sorts (disk)';

prompt
prompt
prompt =======================================================================================================
prompt
prompt CPU TIME WAIT RATIO
column "CPU TIME WAIT RATIO" format 999.9999

select (a.value-b.value)/(c.value+0.0001)*100 "CPU TIME WAIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.name = 'parse time elapsed'
and b.name = 'parse time cpu'
and c.name IN ('parse count', 'parse count (total)');

prompt
prompt
prompt =======================================================================================================
prompt
prompt RBS PERFORMANCE HIT RATIO
column "RBS PERFORMANCE HIT RATIO" format 999.9999;

select (1-max(w.count/sum(s.value)))*100 "RBS PERFORMANCE HIT RATIO"
from v$waitstat w, v$sysstat s
where w.class IN ('system undo header',
'system undo block',
'undo header',
'undo block')
and s.name IN ('db block gets', 'consistent gets') -- "Total Number of Requests for Data"
group by w.count;

prompt
prompt
prompt =======================================================================================================
prompt
prompt RBS SEGMENT CONTENTION RATIO
column "RBS SEGMENT CONTENTION RATIO" format 999.9999

select max(waits/gets)*100 "RBS SEGMENT CONTENTION RATIO"
from v$rollstat;

prompt
prompt
prompt =======================================================================================================
prompt
prompt FREELIST CONTENTION RATIO
column "FREELIST CONTENTION RATIO" format 999.9999;

select w.count/sum(s.value)*100 "FREELIST CONTENTION RATIO"
from v$waitstat w, v$sysstat s
where w.class IN ('free list')
and s.name IN ('db block gets', 'consistent gets') -- "Total Number of Requests for Data"
group by w.count;

prompt
prompt
prompt =======================================================================================================
prompt
prompt LATCH W2W_MISS/IMMED_MISS RATIO
column "LATCH W2W MISS RATIO" format 999.9999
column "LATCH IMMED MISS RATIO" format 999.9999

select max((l.misses/(l.gets+l.misses+0.0001))*100) "LATCH W2W MISS RATIO",
max((l.immediate_misses/(l.immediate_gets+l.immediate_misses+0.0001))*100) "LATCH IMMED MISS RATIO"
from v$latch l;

prompt
prompt
prompt =======================================================================================================
prompt
prompt MAX SESSION EVENT AVERAGE WAIT
column "MAX SESSION EVENT AVERAGE WAIT" format 999,999,999.9999;

select max(average_wait) "MAX SESSION EVENT AVERAGE WAIT"
from v$session_event;

prompt
prompt
prompt =======================================================================================================
prompt
prompt MTS DISPATCHER BUSY RATE RATIO
column "MTS DISPATCHER BUSY RATE RATIO" format 999.9999

select NVL(sum(busy)/sum(busy+idle),0)*100 "MTS DISPATCHER BUSY RATE RATIO"
from v$dispatcher;

prompt
prompt
prompt =======================================================================================================
prompt
prompt MTS DISPATCHER TIME WAIT RATIO
column "MTS DISPATCHER TIME WAIT RATIO" format 999.9999

select NVL(sum(q.wait)/(sum(q.totalq)+0.0001),0)*100 "MTS DISPATCHER TIME WAIT RATIO"
from v$queue q, v$dispatcher d
where q.type IN ('DISPATCHER', 'COMMON')
and q.paddr = d.paddr;

prompt
prompt
prompt
prompt =======================================================================================================
set linesize 132
column "%FREE" format 99.99
prompt
prompt Find Free Space for tablespace
prompt

Select A.TableSpace_Name, B.Bytes/1024/1024 bytes,
(B.Bytes - A.Bytes)/1024/1024 used,
A.Bytes/1024/1024 free,
(A.Bytes / B.Bytes) * 100 "%FREE"
From (select tablespace_name, sum(nvl(bytes,0)) bytes
from Sys.DBA_Free_Space group by tablespace_name) A,
(select tablespace_name, sum(nvl(bytes,0)) bytes
from Sys.DBA_Data_Files group by tablespace_name) B
Where A.tablespace_name = B.tablespace_name order by 5;

prompt
prompt =======================================================================================================
prompt
prompt Find tablespace status, stores data, extent management and allocation type
prompt

select TABLESPACE_NAME,STATUS,CONTENTS,EXTENT_MANAGEMENT,ALLOCATION_TYPE
from dba_tablespaces ;


prompt
prompt =======================================================================================================
prompt


prompt
prompt Find autoextended datafiles and maxbytes
prompt

select TABLESPACE_NAME,STATUS,CONTENTS,EXTENT_MANAGEMENT,ALLOCATION_TYPE
from dba_tablespaces ;

prompt
prompt =======================================================================================================
prompt




set linesize 132
prompt I/O BALANCE RATIOS
column FS_SIZE_STDDEV_RATIO format 999.999 heading "FILE|SYSTEM|SIZES|STANDARD|DEVIATION|RATIO"
column PHYS_READS_STDDEV_RATIO format 999.999 heading "PHYSICAL|READS|STANDARD|DEVIATION|RATIO"
column PHYS_WRITES_STDDEV_RATIO format 999.999 heading "PHYSICAL|WRITES|STANDARD|DEVIATION|RATIO"

select stddev(sum(a.bytes))/avg(sum(a.bytes))*100 FS_SIZE_STDDEV_RATIO,
stddev(sum(b.phyrds))/avg(sum(b.phyrds))*100 PHYS_READS_STDDEV_RATIO,
stddev(sum(b.phywrts))/avg(sum(b.phywrts))*100 PHYS_WRITES_STDDEV_RATIO
from v$datafile a,
v$filestat b
where a.file# = b.file#
group by substr(name,1,instr(name,'/',-1)), substr(name,1,instr(name,'\',-1));

prompt
prompt
prompt =======================================================================================================
prompt
prompt I/O CONTENTION
prompt
prompt =======================================================================================================
prompt
column TOTALS format a44 heading "DATABASE";
column file_system_name format a44 heading "File System|Name";
column tablespace_name format a44 heading "TableSpace|Name";
column file_name format a44 heading "File|Name";
column MBytes format 9,999,999 heading "Size|(MBytes)";
column phyblkrd format 9,999,999 heading "Number|Blocks|Read|(000's)";
column phyblkwrt format 9,999,999 heading "Number|Blocks|Written|(000's)";
column read_time_per_block_msec format 9,999,999 heading "Time|to|Read|1 Block|(msecs)";
column write_time_per_block_msec format 9,999,999 heading "Time|to|Write|1 Block|(msecs)";
column avg_access_time_tot format 9,999,999 heading "Average|Time|to|Read/Write|1 Block|(msecs/blk)";
column avg_access_time format 9,999,999 heading "SORTED|ON THIS|COLUMN|:|Average|Time|to|Read/Write|1 Block|(msecs/blk)";
column avg_access_speed format 999,999,999 heading "Average|Speed|to|Read/Write|1 Second|(blks/sec)";
column avg_estimated_srcer_speed format 9,999.999 heading "Estimated|Average|SRCER|Perform.|(recs/sec)";
column avg_estimated_rlser_speed format 9,999.999 heading "Estimated|Average|RLSER|Perform.|(recs/sec)";
column tps format 9,999.999 heading "ORACLE TRANSACTIONS PER SECOND";
column spt format 9,999.999 heading " SECONDS PER ORACLE TRANSACTION";

select d.name DATABASE,
to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') DATE_OF_RUN,
to_char(open_time, 'DD-MON-YYYY HH24:MI:SS') OPEN_TIME,
value BLOCK_SIZE_BYTES
from v$database d,
v$parameter p,
v$thread t
where p.name = 'db_block_size';


select 'TOTALS' TOTALS ,
sum(bytes)/(1024*1024) MBytes,
sum(phyblkrd)/1000 phyblkrd,
sum(phyblkwrt)/1000 phyblkwrt,
(sum(readtim)*10)/(sum(phyblkrd)+1) read_time_per_block_msec,
(sum(writetim)*10)/(sum(phyblkwrt)+1) write_time_per_block_msec,
(((sum(readtim)*10)/(sum(phyblkrd)+1))*(sum(phyblkrd)) + ((sum(writetim)*10)/(sum(phyblkwrt)+1))*(sum(phyblkwrt))) /
(sum(phyblkrd) + sum(phyblkwrt) + 1)
avg_access_time_tot,
(sum(phyblkrd) + sum(phyblkwrt)) /
(((((sum(readtim)*10)/(sum(phyblkrd)+1))*(sum(phyblkrd)) + ((sum(writetim)*10)/(sum(phyblkwrt)+1))*(sum(phyblkwrt))) + 1)) * 1000
avg_access_speed
from v$filestat,
dba_data_files
where file_id = file#;

select substr(name,1,instr(name,'/',-1))||substr(name,1,instr(name,'\',-1)) file_system_name,
sum(bytes)/(1024*1024) MBytes,
sum(phyblkrd)/1000 phyblkrd,
sum(phyblkwrt)/1000 phyblkwrt,
avg((readtim*10)/(phyblkrd+1)) read_time_per_block_msec,
avg((writetim*10)/(phyblkwrt+1)) write_time_per_block_msec,
(avg((readtim*10)/(phyblkrd+1))*sum(phyblkrd)/1000 + avg((writetim*10)/(phyblkwrt+1))*sum(phyblkwrt)/1000) /
(sum(phyblkrd)/1000 + sum(phyblkwrt)/1000 + 1)
avg_access_time,
((sum(phyblkrd) + sum(phyblkwrt)) /
(avg((readtim*10)/(phyblkrd+1))*sum(phyblkrd) + avg((writetim*10)/(phyblkwrt+1))*sum(phyblkwrt) + 1)) * 1000
avg_access_speed
from v$datafile a,
v$filestat b
where a.file# = b.file#
group by substr(name,1,instr(name,'/',-1)), substr(name,1,instr(name,'\',-1))
order by 7 desc, 6 desc;

select t.tablespace_name,
sum(bytes)/(1024*1024) MBytes,
sum(phyblkrd)/1000 phyblkrd,
sum(phyblkwrt)/1000 phyblkwrt,
avg((readtim*10)/(phyblkrd+1)) read_time_per_block_msec,
avg((writetim*10)/(phyblkwrt+1)) write_time_per_block_msec,
(avg((readtim*10)/(phyblkrd+1))*sum(phyblkrd)/1000 + avg((writetim*10)/(phyblkwrt+1))*sum(phyblkwrt)/1000) /
(sum(phyblkrd)/1000 + sum(phyblkwrt)/1000 + 1)
avg_access_time,
((sum(phyblkrd) + sum(phyblkwrt)) /
(avg((readtim*10)/(phyblkrd+1))*sum(phyblkrd) + avg((writetim*10)/(phyblkwrt+1))*sum(phyblkwrt) + 1)) * 1000
avg_access_speed
from dba_tablespaces t,
v$filestat,
dba_data_files d
where t.tablespace_name = d.tablespace_name
and d.file_id = file#
group by t.tablespace_name,
t.status
order by 7 desc, 6 desc;

select file_name,
bytes/(1024*1024) MBytes,
phyblkrd/1000 phyblkrd,
phyblkwrt/1000 phyblkwrt,
(readtim*10)/(phyblkrd+1) read_time_per_block_msec,
(writetim*10)/(phyblkwrt+1) write_time_per_block_msec,
(((readtim*10)/(phyblkrd+1))*(phyblkrd) + ((writetim*10)/(phyblkwrt+1))*(phyblkwrt)) /
(phyblkrd + phyblkwrt + 1)
avg_access_time,
(phyblkrd + phyblkwrt) /
(((((readtim*10)/(phyblkrd+1))*(phyblkrd) + ((writetim*10)/(phyblkwrt+1))*(phyblkwrt)) + 1)) * 1000
avg_access_speed
from v$filestat,
dba_data_files
where file_id = file#
order by 7 desc, 6 desc;

prompt
prompt
prompt =======================================================================================================
prompt
prompt ORACLE TRANSACTIONS (USER COMMITS + TRANSACTION ROLLBACKS) = TOTAL AVERAGES SINCE DATABASE STARTUP

select max(d.name) DATABASE,
to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') DATE_OF_RUN,
sum(s.value/(24*60*60*(sysdate-t.open_time))+0.001) tps,
1/sum(s.value/(24*60*60*(sysdate-t.open_time))+0.001) spt
from v$database d, v$sysstat s, v$thread t
where s.name in ('user commits', 'transaction rollbacks');

prompt
prompt
prompt
prompt
prompt =======================================================================================================
prompt
prompt SYSTEM EVENTS
col event format a37 heading 'Event'
col total_waits format 99999999 heading 'Total|Waits'
col time_waited format 9999999999 heading 'Time Wait|In Hndrds'
col total_timeouts format 999999 heading 'Timeout'
col average_wait heading 'Average|Time' format 999999.999

set pages 999

select *
from v$system_event ;

prompt
prompt
prompt =======================================================================================================
prompt
prompt SYSTEM STATISTICS
column value format 9999999999
select statistic#, name, value
from v$sysstat
where value > 0 ;

prompt
prompt
prompt =======================================================================================================
prompt
prompt ROLLBACK STATISTICS:
prompt

column extents format 999 heading 'Extents'
column rssize format 999,999,999 heading 'Size in|Bytes'
column optsize format 999,999,999 heading 'Optimal|Size'
column hwmsize format 999,999,999 heading 'High Water|Mark'
column shrinks format 9,999 heading 'Num of|Shrinks'
column wraps format 9,999 heading 'Num of|Wraps'
column extends format 999,999 heading 'Num of|Extends'
column aveactive format 999,999,999 heading 'Average size|Active Extents'
column rownum noprint

select rssize, optsize, hwmsize,
shrinks, wraps, extends, aveactive
from v$rollstat
order by rownum ;
prompt
prompt
prompt =======================================================================================================
prompt
prompt ROLLBACK STATISTICS - gets waits writes

select rownum, extents, rssize,
xacts, gets, waits, writes
from v$rollstat
order by rownum ;

prompt
prompt
prompt =======================================================================================================
prompt
prompt FREE SPACE

select
a.tablespace_name , tbsize , tbfree ,
round( b.tbfree/a.tbsize*100,2) "RATIO" , b.Largest "LARGEST SPACE"
from ( select tablespace_name,round(sum(bytes)/1024/1024,2) tbsize
from dba_data_files group by tablespace_name) a,
( select tablespace_name,round(sum(bytes)/1024/1024 ,2)tbfree,
round(max(bytes)/1024/1024,2) Largest
from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name order by 4 ;
prompt
prompt
prompt =======================================================================================================
prompt LOG FILE SIZE
SELECT * FROM
V$LOG;


prompt =======================================================================================================
prompt
prompt
prompt LOG FILES
column MEMBER format a60
SELECT * FROM
V$LOGFILE;

prompt
prompt =======================================================================================================
prompt
prompt
prompt
prompt Rollback setment information
select SEGMENT_NAME,TABLESPACE_NAME ,INITIAL_EXTENT,NEXT_EXTENT,STATUS
from dba_ROLLBACK_SEGS;

prompt
prompt =======================================================================================================
prompt
prompt User Information

select USERNAME ,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE
from dba_users ;

prompt
prompt =======================================================================================================
prompt
prompt
prompt Table Fragmentation
prompt Wait..... Script is running....
column owner noprint new_value owner_var
column segment_name format a30 heading 'Object Name'
column segment_type format a9 heading 'Table/Indx'
column sum(bytes) format 999,999,999 heading 'Bytes Used'
column count(*) format 999,999 heading 'No.'

select a.owner, segment_name, segment_type,
sum(bytes), max_extents, count(*)
from dba_extents a, dba_tables b
where segment_name = b.table_name
and a.owner='EDA_USER'
having count(*) > 3
group by a.owner, segment_name, segment_type, max_extents
order by a.owner, segment_name, segment_type, max_extents ;


prompt
prompt
prompt =======================================================================================================
prompt
prompt
prompt Index Fragmentation
prompt Wait..... Script is running....
select a.owner, segment_name, segment_type,
sum(bytes), max_extents, count(*)
from dba_extents a, dba_indexes b
where segment_name = index_name
and a.owner='EDA_USER'
having count(*) > 3
group by a.owner, segment_name, segment_type, max_extents
order by a.owner, segment_name, segment_type, max_extents ;

prompt
prompt
prompt =======================================================================================================
prompt
prompt
prompt Initialize Parameter files
column name format a60
column value format a60
SELECT name, value
FROM v$parameter;
prompt
prompt
prompt =======================================================================================================
prompt
prompt - BLOCK LEVEL FRANGMENTATION FOR INDEX
set serveroutput on size 100000
set lines 132

declare
sowner varchar2(30):='EDA_USER'; ---- Segment Owner
segtype varchar2(30):='INDEX'; ---- Segment Type
segname varchar2(81); ---- Segment Name
v_total_blocks number; ---- Total No. of Blocks in a Segment
v_total_bytes number; ---- Total No. of Bytes in a Segment
v_unused_blocks number; ---- Total No. of Unused blocks
v_unused_bytes number; ---- Total No. of Unused Bytes
v_last_used_extent_file_id number; ---- Last used Datafile No.
v_last_used_extent_block_id number; ---- Last used Extent No.
v_last_used_block number; ---- Last used block
freeblocks number; ---- Blocks Below High water Mark

cursor selsegment(ssowner varchar2,segtype varchar2)
is
select segment_name from dba_segments
where owner=upper(sowner) and segment_type=upper(segtype);

begin

dbms_output.put_line(' Owner => '||sowner);
dbms_output.put_line(' Segment_type => '||segtype);

open selsegment(sowner,segtype); ------ select the segment_name
dbms_output.put_line('Segment Name |'||'Total Blocks|'||'Total Bytes|'||'Unused Blocks|'||'Unused Bytes|'||'Lusedfile|'||'Lusedextent|'||'Last used block|'||'Free Blocks');
loop
fetch selsegment into segname;
exit when selsegment%notfound;
dbms_space.unused_space (sowner,segname,segtype,v_total_blocks, v_total_bytes, v_unused_blocks,v_unused_bytes,v_last_used_extent_file_id, v_last_used_extent_block_id, v_last_used_block);


DBMS_SPACE.FREE_BLOCKS (sowner,segname,segtype,0,freeblocks);


dbms_output.put_line(segname||'|'||v_total_blocks||'|'||v_total_bytes||'|'||v_unused_blocks||'|'||v_unused_bytes||'|'||v_last_used_extent_file_id||'|'||v_last_used_extent_block_id||'|'||v_last_used_block||'|'||freeblocks);
end loop;
close selsegment;
end;
/
prompt
prompt
prompt =======================================================================================================
prompt
prompt
prompt - BLOCK LEVEL FRANGMENTATION FOR TABLES
set serveroutput on size 100000
set lines 132

declare
sowner varchar2(30):='EDA_USER'; ---- Segment Owner
segtype varchar2(30):='TABLE'; ---- Segment Type
segname varchar2(81); ---- Segment Name
v_total_blocks number; ---- Total No. of Blocks in a Segment
v_total_bytes number; ---- Total No. of Bytes in a Segment
v_unused_blocks number; ---- Total No. of Unused blocks
v_unused_bytes number; ---- Total No. of Unused Bytes
v_last_used_extent_file_id number; ---- Last used Datafile No.
v_last_used_extent_block_id number; ---- Last used Extent No.
v_last_used_block number; ---- Last used block
freeblocks number; ---- Blocks Below High water Mark

cursor selsegment(ssowner varchar2,segtype varchar2)
is
select segment_name from dba_segments
where owner=upper(sowner) and segment_type=upper(segtype);

begin

dbms_output.put_line(' Owner => '||sowner);
dbms_output.put_line(' Segment_type => '||segtype);

open selsegment(sowner,segtype); ------ select the segment_name
dbms_output.put_line('Segment Name |'||'Total Blocks|'||'Total Bytes|'||'Unused Blocks|'||'Unused Bytes|'||'Lusedfile|'||'Lusedextent|'||'Last used block|'||'Free Blocks');
loop
fetch selsegment into segname;
exit when selsegment%notfound;
dbms_space.unused_space (sowner,segname,segtype,v_total_blocks, v_total_bytes, v_unused_blocks,v_unused_bytes,v_last_used_extent_file_id, v_last_used_extent_block_id, v_last_used_block);


DBMS_SPACE.FREE_BLOCKS (sowner,segname,segtype,0,freeblocks);


dbms_output.put_line(segname||'|'||v_total_blocks||'|'||v_total_bytes||'|'||v_unused_blocks||'|'||v_unused_bytes||'|'||v_last_used_extent_file_id||'|'||v_last_used_extent_block_id||'|'||v_last_used_block||'|'||freeblocks);
end loop;
close selsegment;
end;
/
prompt
prompt
prompt =======================================================================================================
prompt
prompt
prompt Buffer GetS - for > 10 execution
set linesize 400
column SQL_TEXT format a80
SELECT DISK_READS, BUFFER_gETS, EXECUTIONS,ROWS_PROCESSED,
DISK_READS/DECODE(EXECUTIONS,0,1,EXECUTIONS) PEREXECDISK,
BUFFER_gETS/DECODE(EXECUTIONS,0,1,EXECUTIONS) PEREXECBUFF,SQL_TEXT
FROM V$SQLAREA
WHERE BUFFER_gETS/DECODE(EXECUTIONS,0,1,EXECUTIONS) > 1000
and executions > 10
ORDER BY BUFFER_gETS/DECODE(EXECUTIONS,0,1,EXECUTIONS) DESC ;
prompt
prompt
prompt =======================================================================================================
prompt
prompt Disk Reads - for > 10 executions
SELECT DISK_READS, BUFFER_gETS, EXECUTIONS,ROWS_PROCESSED,
DISK_READS/DECODE(EXECUTIONS,0,1,EXECUTIONS) PEREXECDISK,
BUFFER_gETS/DECODE(EXECUTIONS,0,1,EXECUTIONS) PEREXECBUFF,SQL_TEXT
FROM V$SQLAREA
WHERE DISK_READS/DECODE(EXECUTIONS,0,1,EXECUTIONS) >= 500
and executions >=10
ORDER BY DISK_READS/DECODE(EXECUTIONS,0,1,EXECUTIONS) DESC ;


prompt
prompt
prompt =======================================================================================================
prompt
prompt
prompt
prompt Buffer GetS - for top 100 queries
SELECT * FROM (
SELECT DISK_READS, BUFFER_gETS, EXECUTIONS,ROWS_PROCESSED,
DISK_READS/DECODE(EXECUTIONS,0,1,EXECUTIONS) PEREXECDISK,
BUFFER_gETS/DECODE(EXECUTIONS,0,1,EXECUTIONS) PEREXECBUFF,SQL_TEXT
FROM V$SQLAREA
WHERE executions >= 1
ORDER BY BUFFER_gETS/DECODE(EXECUTIONS,0,1,EXECUTIONS) DESC )
WHERE ROWNUM <= 100 ;

prompt
prompt
prompt
prompt =======================================================================================================

prompt
prompt Disk Reads - for top 100 queries

SELECT * FROM (
SELECT DISK_READS, BUFFER_gETS, EXECUTIONS,ROWS_PROCESSED,
DISK_READS/DECODE(EXECUTIONS,0,1,EXECUTIONS) PEREXECDISK,
BUFFER_gETS/DECODE(EXECUTIONS,0,1,EXECUTIONS) PEREXECBUFF,SQL_TEXT
FROM V$SQLAREA
WHERE executions >=1
ORDER BY DISK_READS/DECODE(EXECUTIONS,0,1,EXECUTIONS) DESC )
WHERE ROWNUM <= 100;

prompt
prompt
prompt =======================================================================================================
prompt
prompt
prompt
prompt
prompt Parse to execution
column SQL_TEXT format a100
SELECT PARSE_CALLS,EXECUTIONS,SQL_TEXT
FROM ( select PARSE_CALLS,EXECUTIONS,SQL_TEXT from v$sql
where executions < parse_calls
and parse_calls > 0
order by parse_calls desc )
WHERE rownum <= 20;

prompt
prompt
prompt =======================================================================================================
prompt
prompt
prompt
prompt Parse to execution ratio %
prompt
--- low number may indicate a literal SQL or other sharing problem
--- OLTP should be 90 percent

select 100 * (1-a.hard_parses/b.executions) noparse_hitratio
from (select value hard_parses
from v$sysstat
where name = 'parse count (hard)' ) a
,(select value executions
from v$sysstat
where name = 'execute count') b;

prompt
prompt
prompt =======================================================================================================
prompt
prompt TABLE STATISTICS
prompt
select table_name,num_rows,blocks,avg_row_len,user_stats,
IOT_NAME,SAMPLE_SIZE,LAST_ANALYZED
FROM dba_tables
where OWNER='EDA_USER';
prompt
prompt
prompt =======================================================================================================
prompt
prompt INDEDX STATISTICS
prompt
select OWNER,INDEX_NAME, INDEX_TYPE,
num_rows,user_stats , UNIQUENESS,
SAMPLE_SIZE,LAST_ANALYZED,FUNCIDX_STATUS
FROM dba_indexes
where table_owner='EDA_USER' ;
prompt
prompt
prompt =======================================================================================================
prompt
prompt LOG SWITCH INFORMATION
prompt
SELECT thread#, sequence#, first_time
FROM ( select thread#, sequence#,
to_char(first_time,'mm/dd/yyyy hh24:mi:ss') first_time
from v$loghist
order by sequence# desc )
WHERE rownum <=100;
prompt
prompt
prompt =======================================================================================================
prompt
prompt
Prompt ============== End Time ==============
prompt
SELECT TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MI') EndTime
FROM DUAL ;
Prompt =======================================
prompt
prompt
prompt End Report
prompt
spool off;