SOLARIS Handy Commands

get kernel parameters

grep shm /etc/system

get Memory

prtconf | head -3 |grep Mem

get HW

uname -X

Display number of CPU’s: psrinfo -v|grep "Status of processor"|wc -l
RAM Size in Solaris: prtconf|grep -i mem

OPatch

Check Patch conflict

opatch prereq CheckConflictAgainstOH -ph $(pwd)

SQL - Joins

Equijoins or Inner Join

SQL >SELECT Table_A.letter, Table_B.letter
2    FROM Table_A, Table_B
3   WHERE Table_A.letter = Table_B.letter;

LETTER     LETTER
---------- ----------
A          A

SQL >SELECT Table_A.letter, Table_B.letter
2    FROM Table_A INNER JOIN Table_B
3      ON Table_A.letter = Table_B.letter;

LETTER     LETTER
---------- ----------
A          A

Self Joins

SQL >SELECT A1.letter, A2.letter
2    FROM Table_A A1, Table_A A2
3   WHERE A1.letter = A2.letter;

LETTER     LETTER
---------- ----------
A          A
B          B

SQL >SELECT A1.letter, A2.letter
2    FROM Table_A A1 INNER JOIN Table_A A2
3      ON A1.letter = A2.letter;

LETTER     LETTER
---------- ----------
A          A
B          B

Left Outer Joins

SQL >SELECT Table_A.letter, Table_B.letter
2    FROM Table_A, Table_B
3   WHERE Table_A.letter = Table_B.letter(+);

LETTER     LETTER
---------- ----------
A          A
B

SQL >SELECT Table_A.letter, Table_B.letter
2    FROM Table_A LEFT OUTER JOIN Table_B
3      ON Table_A.letter = Table_B.letter;

LETTER     LETTER
---------- ----------
A          A
B

Right Outer Joins

SQL >SELECT Table_A.letter, Table_B.letter
2    FROM Table_A, Table_B
3   WHERE Table_A.letter(+) = Table_B.letter;

LETTER     LETTER
---------- ----------
A          A
C

SQL >SELECT Table_A.letter, Table_B.letter

  2  FROM Table_A RIGHT OUTER JOIN Table_B
3  ON Table_A.letter = Table_B.letter;

LETTER     LETTER
---------- ----------
A          A
C

Full Outer Joins

SQL >SELECT Table_A.letter, Table_B.letter
2    FROM Table_A, Table_B
3   WHERE Table_A.letter = Table_B.letter(+)
4   UNION
5  SELECT Table_A.letter, Table_B.letter
6    FROM Table_A, Table_B
7   WHERE Table_A.letter(+) = Table_B.letter;

LETTER     LETTER
---------- ----------
A          A
B
C

SQL >SELECT Table_A.letter, Table_B.letter
2    FROM Table_A FULL OUTER JOIN Table_B
3      ON Table_A.letter = Table_B.letter;

LETTER     LETTER
---------- ----------
A          A
B
C

Cartesian Products

SQL >SELECT Table_A.letter, Table_B.letter
2    FROM Table_A, Table_B;

LETTER     LETTER
---------- ----------
A          A
A          C
B          A
B          C

SQL >SELECT Table_A.letter, Table_B.letter
2    FROM Table_A CROSS JOIN Table_B;

LETTER     LETTER
---------- ----------
A          A
A          C
B          A
B          C


Reference Document dbasupport.com 

Application Patch Info

APPLSYS
ad_bugs
ad_applied_patches

APPS
synonyms

SQL

  select * from dba_objects where object_name = 'AD_BUGS';

Voting Disk cheat Sheet

Voting disk

dd if=voting_disk_name of=backup_file_name
dd if=backup_file_name of=voting_disk_name


crsctl query css votedisk - lists the voting disks used by CSS
crsctl add css votedisk - adds a new voting disk
crsctl delete css votedisk - removes a voting disk

OCR Cheatsheet

Restore OCR from Backup

Connect as root and from the OCR backup directory choose the last backup before the problem started

[root@vmractest1]# cd /vmasmtest/BACKUP/OCR/
[root@vmractest1]# ls -ltr
total 458328
-rw-r--r-- 1 oracle dba 156279808 Nov 21 16:48 OCR_21_nov_06_backup1
-rw-r--r-- 1 oracle dba 156279808 May 3 16:39 OCR_03_may_07
-rw-r--r-- 1 oracle dba 156279808 May 16 16:06 OCR_16_may_07_small
Restore the last backup using the dd command
[root@vmractest1]# dd if=/vmasmtest/BACKUP/OCR/OCR_03_may_07 of=/dev/raw/ocr.dbf
305234+0 records in
305234+0 records out

OCR.LOC
used by ASM to start CSS daemon

/var/opt/oracle on Sun Solaris
/etc/oracle on RHEL

$ cat ocr.loc
ocrconfig_loc=/dev/asmdisk/ocr1 <-specifies the location of the Oracle Cluster Registry (OCR) used by the CSS daemon
ocrmirrorconfig_loc=/dev/asmdisk/ocr2
local_only=FALSE

OCR

$ ocrconfig –option
$ ocrcheck
$ ocrconfig -showbackup
$ ocrconfig –export myfile
$ ocrdump -backupfile my-file
$ ocrconfig –restore my_file
$ cluvfy comp ocr –n all -verbose #check OCR integrity
$ ocrconfig -replace ocr destination_file or disk
$ ocrconfig -replace ocrmirror destination_file or disk
# ../bin/crs stop

ASM Cheatsheet

# Start/Stop ASM

srvctl stop asm -n linux1
srvctl start asm -n linux1

# Status of an ASM instance

srvctl status asm -n linux1
ASM instance +ASM1 is running on node linux1.

# Display the configuration for the ASM instance(s)

srvctl config asm -n linux1
+ASM1 /u01/app/oracle/product/10.2.0/db_1

# asmcmd commands

asmcmd -p
asmcmd lsdg
asmcmd ls ORADATA001

# Check asm status

crsstat |grep asm

# ASM SQL's
prompt asm diskgroups
prompt
SELECT group_number, name, total_mb,state, type FROM V$asm_diskgroup
/

prompt All ASM disk that belong to the 'ORCL_DATA1' disk group
prompt
SELECT path FROM v$asm_disk
WHERE group_number IN (select group_number from v$asm_diskgroup
where name = upper('&diskgroup');

# Create on ASM the root directory of the database

$ asmcmd
ASMCMD> cd datadg
ASMCMD> mkdir racdbtst
ASMCMD> ls
racdbtst/

# Directories to be created for Duplicating Database

oracle@lnx521> . oraenv
ORACLE_SID = [xxxx] ? +ASM1
oracle@lnx521> asmcmd
ASMCMD> cd oradata001
ASMCMD> mkdir gwyd
ASMCMD> cd gwyd
ASMCMD> mkdir CONTROLFILE DATAFILE ONLINELOG PARAMETERFILE TEMPFILE
ASMCMD>

# Create pfile from spfile

create pfile='/vmasmtest/BACKUP/ASM-SPFile' from spfile;

CRS ( Oracle Clusterware ) Cheat Sheet

Oracle Clusterware enables servers in an Oracle database Real Application Cluster to coordinate simultaneous workload on the same database files. The crsctl command provides administrators many useful capabilities. For example, with crsctl, you can check Clusterware health disable/enable Oracle Clusterware startup on boot, find information on the voting disk and check the Clusterware version, and more.

1. Do you want to check the health of the Clusterware?
# crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy

if you receive any output other than the "…appears healthy" output, then kill crsd.bin on the node that gave you the unhealthy output ( login as root)

2. Do you want to reboot a node for maintenance without Clusterware coming up on boot?
## Disable clusterware on machine2 bootup:
# crsctl disable crs
## Stop the database then stop clusterware processes:
# srvctl stop instance –d db –i db2
# crsctl stop crs
# reboot

## Enable clusterware on machine bootup:
# crsctl enable crs
# crsctl start crs
# srvctl start instance –d db –i db2

3. Do you wonder where your voting disk is?
# crsctl query css votedisk
0. 0 /dev/raw/raw2

4. Do you need to find out what clusterware version is running on a server?
# crsctl query crs softwareversion
CRS software version on node [db2] is [10.2.0.2.0]

STOP CRS

Connect as root and from ORA_CRS_HOME execute the following command on both servers

[root@vmractest1 bin]# ./crsctl stop crs
Stopping resources.
Successfully stopped CRS resources
Stopping CSSD.
Shutting down CSS daemon.
Shutdown request successfully issued.

Restart CRS on Both Nodes

[root@vmractest1 bin]# ./crsctl start crs
[root@vmractest2 bin]# ./crsctl start crs
Restarting CRS brought up all resources:


CRS Verion

$ crsctl query crs softwareversion
$ crsctl query crs activeversion

Monitor CRS Heath using OEM

$ cat /opt/oracle/admin/OEM/UserDefinedHostScripts/checkCRS.sh
#!/usr/bin/ksh
# OEM User Defined OS Metric. Script is called by OEM to monitor the health of CRSd on a particular node.
if [[ $(/ora01/oracle/product/crs_1020/bin/crsctl check crsd) = "CRS appears healthy" ]]
then print "em_result=0\nem_message=CRSd is healthy\n"
else print "em_result=1\nem_message=CRSd not responding\n"
fi
exit

Datapump Sample Scripts

# Prepare for datapump
sqlplus / as sysdba
grant create any directory to scott;
create or replace directory dumpdir as '/ora01/oracle/dpdump/';
grant read,write on directory dumpdir to scott;

# export schema
expdp scott/manager schemas=scott dumpfile=t1 logfile=t1.log directory=dumpdir job_name=dpjob1

# impot schema
impdp scott/manager schemas=scott dumpfile=t1 logfile=t1.log directory=dumpdir job_name=dpjob1

# tables
expdp scott/manager tables=t1 dumpfile=t1 logfile=t1.log directory=dumpdir job_name=dpjob1

--
# export schema metadata only
date
ORACLE_SID=demod1;ORAENV_ASK=NO;. oraenv;ORAENV_ASK=YES
expdp system dumpfile=demod_dmp_dir:ACT_OWNCStructure.dmp logfile=demod_dmp_dir:ACT_OWNCStructure.log \
schemas=act_ownc content=METADATA_ONLY
date


# export schema data only
date
ORACLE_SID=demod1;ORAENV_ASK=NO;. oraenv;ORAENV_ASK=YES
expdp system dumpfile=demod_dmp_dir:demod_ACT_OWNC.dmp logfile=demod_dmp_dir:demod_ACT_OWNC.log \
schemas=act_ownc content=DATA_ONLY
date

# export schema data estimate_only
date
ORACLE_SID=demod1;ORAENV_ASK=NO;. oraenv;ORAENV_ASK=YES
expdp system estimate_only=y logfile=demod_dmp_dir:demod_ACT_OWNC_est.log \
schemas=act_ownc content=DATA_ONLY
date

# export data + metadata
date
ORACLE_SID=demod1;ORAENV_ASK=NO;. oraenv;ORAENV_ASK=YES
expdp act_ownb dumpfile=demod_dmp_dir:ACT_OWNBSelected2.dmp logfile=demod_dmp_dir:ACT_OWNBSelectedExport2.log \
tables=\
ACT_OWN_STATS,\
ALIAS,\
ATTRIBUTE
date

# export tables data only
date
ORACLE_SID=demod1;ORAENV_ASK=NO;. oraenv;ORAENV_ASK=YES
expdp act_ownc dumpfile=demod_dmp_dir:demod_ACT_OWNCp1.dmp logfile=demod_dmp_dir:demod_ACT_OWNCp1.log \
content=DATA_ONLY tables=\
TRAFFIC_UNCOMPRESSED_BASE,\
TRAFFIC_UNCOMPRESSED_FORECAST,\
TRAFFIC_UNCOMPRESSED_SP1,\
TRAFFIC_UNCOMPRESSED_SP3,\
TRAFFIC_UNCOMPRESSED_SP2
date


# import data ; truncate existing table before import
date
ORACLE_SID=demod1;ORAENV_ASK=NO;. oraenv;ORAENV_ASK=YES
impdp system/eaxo042 dumpfile=demod_dmp_dir:ACT_OWNBSelected.dmp logfile=demod_dmp_dir:actowncimp.log remap_schema=act_ownb:act_ownb \
table_exists_action=truncate
date

# import data Only; truncate existing table before import
date
ORACLE_SID=demod1;ORAENV_ASK=NO;. oraenv;ORAENV_ASK=YES
impdp system dumpfile=demod_dmp_dir:ACT_OWNDSPtables.dmp logfile=demod_dmp_dir:actowncimpsp.log \
remap_schema=act_ownd:act_ownc content=data_only \
table_exists_action=truncate
date


# import data append
date
ORACLE_SID=demod1;ORAENV_ASK=NO;. oraenv;ORAENV_ASK=YES
impdp system dumpfile=demod_dmp_dir:ACT_OWNB_mar13.dmp logfile=demod_dmp_dir:actownbimpmar13.log remap_schema=act_ownb:act_ownb \
table_exists_action=append
date

Clone Database

Create a Duplicate database with NOCATALOG on same Node

Source : LLAP
Target : llad

Steps
1. Prerequisities
Source is at least mounted

2. Init.ora configuration in duplicate database to AVOID original file being OVERWRITTEN

remote_login_passwordfile=EXCLUSIVE
db_file_name_convert =('LLAP','llad')
log_file_name_convert=('LLAP','llad')
Modify Name and location of control file
database name
instance name

create password file
startup nomount

3. Setup sqlnet configuration
add clone database entry to tnsnames.ora and listener.ora

4. Backup of Source Database

5. Get SCN/Log sequence/Time for cloning

6. Duplicate database

7. Post Duplicate Steps

Example

-- setup duplicate database enviornment(llad), source is LLAP
# create directory structure
# create init.ora file - cp init.ora init.ora
# change all references to except log_archive_????
# add following lines to init.ora
remote_login_passwordfile=EXCLUSIVE
db_file_name_convert =('LLAP','llad')
log_file_name_convert=('LLAP','llad')
# add target to oratab
# create link for init file in $ORACLE_HOME/dbs for target
# create password file in $ORACLE_HOME/dbs
orapwd file=orapw password=
# add target database to tnsnames.ora
# add target database to listerner.ora
# reload the listener
# startup nomount <-- target database
select * from v$instance


# backup source database

bkup.sh
-------
date
echo LLAP |. oraenv
cd /ora01/oracle/admin/BACKUP/llad
rman nocatalog target / cmdfile=rman_backup_LLAP_special.cmd
status=$?
date
exit $status


bkup.cmd
--------
# host 'echo Starting rman_backup_LLAP_special.cmd on rs204, `date`.';
# backup to several channels to disk
run {
allocate channel LLAP_d1 type disk format '/psoft200/oracle/BACKUP/LLAP/%U';
allocate channel LLAP_d3 type disk format '/psoft200/oracle/BACKUP/LLAP/%U';
backup setsize = 62914560
(database)
(current controlfile);
}
# host 'echo Ending rman_backup_LLAP_special.cmd on rs204, `date`.';



# get SCN
Method 1) set numwidth 18; select max(CHECKPOINT_CHANGE#) from v$backup_datafile;
2) list backup from rman
3) list backupset for database from rman


dup.sh
------
date
echo LLAP |. oraenv
cd /ora01/oracle/admin/BACKUP/llad
rman target / auxiliary "sys/manager@llad" @dup.cmd
date
exit


dup.cmd
-------
run {
set until scn 607710483792; <-- from get SCN step
duplicate target database to llad;
}

# add temp file
# rename global_name
# remove parameter added to target database for cloning
# bounce target database

References:
Metalink Note : 245262.1
72912.1
73912.1
228257.1
259694.1

RMAN Backup Report to get SCN for Recovery

Two methods.

1) >rman target / log=rmanlistbackup.log
Rman>list backup;
Rman>exit

vi rmanlistbackup.log. The last entry in the file shows this:
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
22021 Full 27M DISK 00:00:01 15-MAY-07
BP Key: 22025 Status: AVAILABLE Tag:
Piece Name: /ora01/oracle/product/9.2.0.4.0/dbs/c-4049168349-20070515-02 <-- control file backup at the very end of the last disk backup.
Controlfile Included: Ckp SCN: 607710483792 Ckp time: 15-MAY-07

2) login to database
check following dictionary Views

from v$backup_set where start_time > sysdate -1

from v$backup_piece
from v$backup_datafile
from v$backup_redolog


This will provide last SCN number
set numwidth 18
select max(CHECKPOINT_CHANGE#) from v$backup_datafile;

MAX(CHECKPOINT_CHANGE#)
-----------------------
607710483792

Stop All Processes in 10g

Stop All Processes

Stop all listeners and other processes running in the Oracle home directory, where you need to install the patch set.

Note: If you shut down ASM instances, you must first shut down all database instances that use ASM, even if these databases run from different Oracle homes.

• If you are upgrading a single instance installation, shut down the following Oracle Database 10g processes before installing the patch set:

  
  

  Note: You must perform these steps in the order listed.

  
  

  1. Shut down all processes in the Oracle home that might be accessing a database, for example Oracle Enterprise Manager Database Control or iSQL*Plus.

     
     

     Note: Before you shutdown all processes that are monitored by Enterprise Manager Grid Control, set a blackout in Grid Control for the processes that you intend to shut down. This is necessary so that the availability records for these processes indicate that the shutdown was planned downtime rather than an unplanned system outage.

     
     

  2. Shut down all database instances running in the Oracle home directory, where you need to install the patch set.

  3. Shut down the ASM instance, if the ASM instance is running in the Oracle home directory, where you need to install the patch set.

  4. Shut down all listeners running in the Oracle home directory, where you need to install the patch set.

  5. Shut down the Oracle Cluster Synchronization Services (CSS) daemon as the root user:

     # oracle_home/bin/crsctl stop crs
     
     

• If you are upgrading an Oracle Clusterware installation, shut down the following Oracle Database 10g processes before installing the patch set:

  
  

  Note: You must perform these steps in the order listed.

  
  

  1. Shut down all processes in the Oracle home on each node that might be accessing a database, for example Oracle Enterprise Manager Database Control.

     
     

     Note: Before you shutdown all processes that are monitored by Enterprise Manager Grid Control, set a blackout in Grid Control for the processes that you intend to shut down. This is necessary so that the availability records for these processes indicate that the shutdown was planned downtime rather than an unplanned system outage.

     
     

  2. Shut down all RAC instances on all nodes. To shut down all RAC instances for a database, enter the following command where db_name is the name of the database:

     $ oracle_home/bin/srvctl stop database -d db_name -i inst_name
     
     

  3. Shut down all ASM instances on all nodes. To shut down an ASM instance, enter the following command where node is the name of the node where the ASM instance is running:

     $ oracle_home/bin/srvctl stop asm -n node
     
     

  4. Stop all node applications on all nodes. To stop node applications running on a node, enter the following command where node is the name of the node where the applications are running:

     $ oracle_home/bin/srvctl stop nodeapps -n node
     
     

  5. Shut down the Oracle Clusterware process by entering the following command on all nodes as the root user:

     # crs_home/bin/crsctl stop crs
     
     

     
     

     Note: In these examples, crs_home is the Oracle Clusterware home directory where the Oracle Clusterware 10g installation that you want to upgrade is installed.

     
     
     
     

     Note: If the Oracle Clusterware installation is not on a shared Oracle home you can upgrade the Oracle Clusterware installation one node at a time. To do this, perform the preceding steps only on the first node that you are upgrading, then follow the instructions on the installer screen.

     
     

• If you are upgrading a RAC installation, shut down the following Oracle Database 10g processes before installing the patch set:

  
  

  Note: You must perform these steps in the order listed.

  
  

  1. Shut down all processes in the Oracle home on each node that might be accessing a database, for example Oracle Enterprise Manager Database Control.

     
     

     Note: Before you shutdown all processes that are monitored by Enterprise Manager Grid Control, set a blackout in Grid Control for the processes that you intend to shut down. This is necessary so that the availability records for these processes indicate that the shutdown was planned downtime rather than an unplanned system outage.

     
     

  2. Shut down all RAC instances on the nodes, which run from the Oracle home on which you are going to apply the patch set. To shut down all RAC instances for a database, enter the following command where db_name is the name of the database:

     $ oracle_home/bin/srvctl stop database -d db_name
     
     

  3. Shut down all ASM instances on all nodes. To shut down an ASM instance, enter the following command where node is the name of the node where the ASM instance is running:

     $ oracle_home/bin/srvctl stop asm -n node
     
     

  4. Stop any listeners that are running from the Oracle home that you are patching on all nodes. To stop the listener running on a node, enter the following command, where node is the name of the node where the listener is running:

     $ oracle_home/bin/srvctl stop listener -n node [-l listenername]
     
     

All About SCN

select start_scnw, start_scnb from v$transaction;

alter system checkpoint;

select CONTROLFILE_CHANGE#  from v$database;

select dbms_flashback.get_system_change_number flashback_scn from v$database;

SELECT dbms_flashback.get_system_change_number FROM dual;

what is my session doing

-- sessions in non-RAC
set linesize 132
Prompt "Oracle Sessions"
column osuser format A08 Head "OS User"
column user format A08 Head "User"
column module format A15 Head "Module"
column action format A25 Head "Action"
column logon_time format A15 Head "Logon Time"

select sid,
s.serial#,
spid,
substr(osuser,1,12) osuser,
substr(s.username,1,15) "user",
status,
decode(p.background,'1','Y','N') background,
decode(s.lockwait,null,'N','Y') lockwait,
decode(p.latchwait,null,'N','Y') latchwait,
substr(to_char(logon_time,'dd-mon-yy hh24:mi'),1,18) logon_time,
substr(module,1,45) module,
action
from v$session s,
v$process p
where s.username not in (' ','SYS','PATROL')
and p.addr=s.paddr
order by status desc, logon_time
/

set lines 80

-- sessions in RAC
set linesize 132
Prompt "RAC Oracle Sessions"
column inst_id format 9 Head "Inst|Id."
column osuser format A08 Head "OS User"
column user format A08 Head "User"
column module format A15 Head "Module"
column action format A25 Head "Action"
column logon_time format A15 Head "Logon Time"
select inst_id, sid, serial#,
substr(osuser,1,12) osuser, substr(username,1,15) "user",
status,
substr(to_char(logon_time,'dd-mon-yy hh24:mi'),1,18) logon_time,
substr(module,1,45) module, action
from gv$session
where username not in (' ','SYS','PATROL')
order by status desc;

-- 'Session Reponse in terms of |Waiting|IO Operation|CPU Operation'
ttitle 'Session Reponse in terms of |Waiting|IO Operation|CPU Operation'

Column event format a30
Column sid format 9999
Column session_cpu heading "CPU|used"
Column physical_reads heading "physical|reads"
Column consistent_gets heading "logical|reads"
Column seconds_in_wait heading "seconds|waiting"

select a.sid,
a.value session_cpu,
c.physical_reads,
c.consistent_gets,
d.event,
d.seconds_in_wait
from v$sesstat a,
v$statname b,
v$sess_io c,
v$session_wait d
where a.sid like '&Sid'
and b.name = 'CPU used by this session'
and a.statistic# = b.statistic#
and a.sid=c.sid
and a.sid=d.sid;


-- WHAT IS SESSION doing, we have pid

SELECT /*+ ordered */ p.spid, s.sid, s.serial#, s.username,
TO_CHAR(s.logon_time, 'mm-dd-yyyy hh24:mi') logon_time,
s.last_call_et, st.value, s.sql_hash_value, s.sql_address, sq.sql_text
FROM v$statname sn, v$sesstat st, v$process p, v$session s, v$sql sq
WHERE s.paddr=p.addr
AND s.sql_hash_value = sq.hash_value and s.sql_Address = sq.address
AND s.sid = st.sid
AND st.STATISTIC# = sn.statistic#
AND sn.NAME = 'CPU used by this session'
AND p.spid = &osPID -- parameter to restrict for a specific PID
AND s.status = 'ACTIVE'
ORDER BY st.value desc;

-- v$session_wait, current waits on the system
SELECT event,
sum(decode(wait_time,0,1,0)) "Curr",
sum(decode(wait_time,0,0,1)) "Prev",
count(*)"Total"
FROM v$session_wait
GROUP BY event
ORDER BY count(*);

-- v$session_event, cummulative session waits
SELECT event, total_waits waits, total_timeouts timeouts,
time_waited total_time, average_wait avg
FROM v$session_event
WHERE sid = &sid
ORDER BY time_waited DESC;

-- V$SESSTAT, session-level summary of resource usage since session startup
SELECT ses.sid
, DECODE(ses.action,NULL,'online','batch') "User"
, MAX(DECODE(sta.statistic#,9,sta.value,0))
/greatest(3600*24*(sysdate-ses.logon_time),1) "Log IO/s"
, MAX(DECODE(sta.statistic#,40,sta.value,0))
/greatest(3600*24*(sysdate-ses.logon_time),1) "Phy IO/s"
, 60*24*(sysdate-ses.logon_time) "Minutes"
FROM V$SESSION ses
, V$SESSTAT sta
WHERE ses.status = 'ACTIVE'
AND sta.sid = ses.sid
AND sta.statistic# IN (9,40)
GROUP BY ses.sid, ses.action, ses.logon_time
ORDER BY
SUM( DECODE(sta.statistic#,40,100*sta.value,sta.value) )
/ greatest(3600*24*(sysdate-ses.logon_time),1) DESC;

-- V$SYSTEM_EVENT , Finding the Total Waits on the System

SELECT event, total_waits waits, total_timeouts timeouts,
time_waited total_time, average_wait avg
FROM V$SYSTEM_EVENT
ORDER BY 4 DESC;

listerner log file - switch to new log file

#!/bin/ksh

. ~/.profile
ORACLE_SID=demo;ORAENV_ASK=NO;. oraenv;ORAENV_ASK=YES

listener_log_extension=`date '+%m%d%y'`

echo "set current_listener LISTENER" >/tmp/listener
echo "set log_file listener.$listener_log_extension">>/tmp/listener
echo "quit">>/tmp/listener


lsnrctl < /tmp/listener

Archive log

init.ora parameters
set log_checkpoints_to_alert=TRUE
remove log_checkpoint_timeout, log_checkpoint_interval

general
set numformat 999999999999
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

turn on archive log mode
steps

A. configure
1) alter system set db_recovery_file_dest='+ORAFLASH001' scope=both sid='*';
2) alter system set db_recovery_file_dest_size = 20G scope=both sid='*';

B. archivelog mode
1) shutdown database
2) startup database in mount mode
3) alter database archivelog; # as sysdba
4) archive log list # as sysdba
sys > ARCHIVE LOG LIST
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 416
Next log sequence to archive 418
Current log sequence 418
5) open database


FIX archive log destination to ORAFLASH001 in 10g using ASM, if set incorrectly
steps
# of course db_recovery_file_dest should be set to +ORAFLASH001
1) alter system set log_archive_dest_10='LOCATION=use_db_recovery_file_dest' scope=both sid='*';
2) alter system set log_archive_dest_1='' scope=both sid='*'; # to null out this parameter
3) alter system switch logfile; # to test, check alert log


others tests
alter system archive log current;
alter system archive log all;
alter system switch logfile;

increase archive log destination size. but 1st find out, How much archived
steps
1) select count(*),sum(blocks*block_size) from v$archived_log where backup_count=0 and deleted='NO';
2) increase db_recovery_file_dest_size
ALTER SYSTEM SET db_recovery_file_dest_size = 30G SCOPE=BOTH SID='*';
3) SHOW PARAMETER RECOVERY

Check space utilization
steps
1. col name format a20
select * from v$recovery_file_dest;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
--------------- ----------- ---------- ----------------- ---------------
+ORAFLASH001 2147483648 1271777792 596992 33

2. select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE .71 0 1
ONLINELOG 0 0 0
ARCHIVELOG 16.81 .03 24
BACKUPPIECE 41.63 0 8
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0

6 rows selected.

list archived files not backed-up
select NAME, ARCHIVED , deleted, status
from v$archived_log
where status='A';

others queries
SELECT SEQUENCE#, FIRST_TIME, FIRST_CHANGE#, NEXT_TIME, NEXT_CHANGE#
FROM v$archived_log;

SELECT sequence#, thread#, first_change#, next_change#
FROM v$archived_log;

SELECT MAX(sequence#)
FROM v$archived_log;

find archive logs to be restored

select completion_time,name,sequence#,first_change#,first_time,next_change#,next_time, archived
from v$archived_log;

restore archive log using seqeunce
run {
allocate channel demo_t1 type 'SBT_TAPE'
parms='ENV=(NB_ORA_CLASS=RMAN_rs204_Tier1)';
restore archivelog from sequence 35347 until sequence 35359;
}

run {
allocate channel demo_t1 type 'SBT_TAPE'
parms='ENV=(NB_ORA_CLASS=RMAN_rs204_Tier2)';
set archivelog destination to '/oraarc01/oracle/arch/demo/' ;
restore archivelog from logseq=70241 until logseq=70250 ;
}

run {
allocate channel demo_t3 type 'SBT_TAPE'
parms='ENV=(NB_ORA_CLASS=RMAN_rs204_Tier2)';
set archivelog destination to '/opt/splexmv/tmparclog/' ;
restore archivelog sequence between 70276 and 70376 ;
}
restore archive log using time
run {
allocate channel icmt_t1 type 'SBT_TAPE'
parms='ENV=(NB_ORA_CLASS=RMAN_rs204_Tier2,NB_ORA_CLIENT=rs204-bkup)';
restore archivelog
from time = "to_date('Apr 5 2005 19:20:00','Mon DD YYYY HH24:MI:SS')"
until time = "to_date('Apr 5 2005 20:00:00','Mon DD YYYY HH24:MI:SS')";
}

backup archive log
run {
allocate channel ch1 type 'SBT_TAPE'
parms='ENV=(NB_ORA_CLASS=RMAN_MTBackup_Tier3)';
set command id to 'rman backup';
backup
maxsetsize = 1G
diskratio = 0
skip inaccessible
tag = 'Archive Logs'
(archivelog all delete input format'SBT_TAPE/RMAN_%d.%t.arch.%c.%s.%p');
}

backup database including archivelog
backup setsize = 62914560
(database)
plus archivelog
skip inaccessible;


backup
full
filesperset = 1024
diskratio = 0
tag = 'Full'
(database format 'SBT_TAPE/RMAN_%d.%t.full.%c.%s.%p')
plus archivelog
skip inaccessible
delete input
format 'SBT_TAPE/RMAN_%d.%t.arch.%c.%s.%p';

Commands

Alter system archive log [start|stop|all|...]

alter system switch logfile;

alter system archive log all;
alter system archive log next;
alter system archive log sequence 104;
alter system archive log current;
alter system archive log stop;
alter system archive log current noswitch;

alter system checkpoint;

alter system flush buffer_cache;
alter system flush buffer_pool;

alter system quiesce restricted;
alter system suspend|resume;
alter system switch logfile;

More Info

export and import using pipe

# export and compress using pipe

export EXPDATE=`date "+%m%d_%H%M"`
export DUMPFILE=demo_user_${EXPDATE}.dmp
export EXPLOGFILE=demo_user_${EXPDATE}.log


/usr/sbin/mknod ${DUMPFILE} p
/usr/bin/gzip < ${DUMPFILE} > ${DUMPFILE}.gz &
sleep 30

exp scott/tiger file=${DUMPFILE} log=${EXPLOGFILE} \
owner=SYSADM consistent=y buffer=10000000 direct=Y statistics=none

tail -5 demo_user_${EXPDATE}.log | mailx -s "demo export" me@google.com

# import from compresed file using pipe

export DUMPFILE=myexp.dmp
export IMPLOGFILE=imp_user_${EXPDATE}.log

/usr/sbin/mknod ${DUMPFILE} p

imp scott/tiger file=${DUMPFILE} log=${IMPLOGFILE} \
fromuser=SYSADM touser=SYSADM indexes=y ignore=y buffer=10000000 commit=y &

/usr/bin/gzip -d < ${DUMPFILE}.gz > ${DUMPFILE}
sleep 300

tail -5 demo_imp_user_${EXPDATE}.log | mailx -s "demo export" me@google.com

export full, zip it and remove files other than 5 days - all in one

#!/bin/ksh
export ORACLE_SID=demo
export ORACLE_HOME=/ora01/oracle/product/db_1020
export PATH=$ORACLE_HOME/bin:$PATH
export EXPDATE=`date "+%m%d_%H%M"`

$ORACLE_HOME/bin/exp \'/ as sysdba\' file=demo_full_${EXPDATE}.dmp full=y statistics=none log=demo_full_${EXPDATE}.log

/bin/gzip demo_full_${EXPDATE}.dmp

find -name exp_demo\* -mtime +5 -exec rm {} \;

tail -5 demo_full_${EXPDATE}.log | mailx -s "demo export" me@google.com

SYSTEM related statistics in 10gr2

3 More SYSTEM related statistics in 10gr2 system, dictionary and fixed_objects

# Gather system statistics
execute dbms_stats.gather_system_stats('Start');

-- some time delay while the database is under a typical workload

execute dbms_stats.gather_system_stats('Stop');

-- OR

exec dbms_stats.gather_system_stats('interval', interval=>N);
where N is the number of minutes stats will be gathered

# collect OLTP and BATCH related system stats and import it when desired
exec dbms_stats.create_stat_table('SYS','CSX_STAT');

BEGIN
DBMS_STATS.GATHER_SYSTEM_STATS(
gathering_mode => 'interval',
interval => 720,
stattab => 'CSX_STAT',
statid => 'OLTP');
END;
/

BEGIN
DBMS_STATS.GATHER_SYSTEM_STATS(
gathering_mode => 'interval',
interval => 720,
stattab => 'CSX_STAT',
statid => 'OLAP');
END;
/

During the day, the following jobs import the OLTP statistics for the daytime run:
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,
'DBMS_STATS.IMPORT_SYSTEM_STATS(''CSX_STAT'',''OLTP'');'
SYSDATE, 'SYSDATE + 1');
COMMIT;
END;
/

During the night, the following jobs import the OLAP statistics for the nighttime run:

BEGIN
DBMS_JOB.SUBMIT(:jobno,
'DBMS_STATS.IMPORT_SYSTEM_STATS(''CSX_STAT'',''OLAP'');'
SYSDATE + 0.5, 'SYSDATE + 1');
COMMIT;
END;
/


# Gather statistics on dictionay
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
-- or
begin
dbms_stats.gather_dictionary_stats(
comp_id => null,
estimate_percent => 15,
granularity => 'DEFAULT',
cascade => TRUE);

end;
/

# gather fixed object(synamic performance tables eg. x$) stats
exec dbms_stats.gather_fixed_objects_stats;
-- or
SQL> conn / as sysdba
SQL> exec DBMS_STATS.CREATE_STAT_TABLE('sys','csxfixedtablestat');
SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ('csxfixedtablestat');



# Flush statistics from memory
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO

# view
USER_TAB_MODIFICATIONS

# system statistics views
select pname, pval1
from sys.aux_stats$
where sname = 'SYSSTATS_MAIN';

gather statistics, dbms_stats

Manage statistics using dbms_stats 
 
create stat table
begin
dbms_stats.create_stat_table(
ownname => 'SYSADM',
stattab => 'mystattable',
tblspace=> 'tools');
end;

drop stat table
begin
dbms_stats.drop_stat_table(
ownname => 'SYSADM',
stattab => 'mystattable');
end;

gather database Stat
begin
dbms_stats.gather_database_stats(
method_opt=> 'FOR ALL INDEXED COLUMNS SIZE 1',
estimate_percent => 15,
cascade=>TRUE);
end;

delete database stat
EXEC DBMS_STATS.delete_database_stats;

gather schema Stat
begin
dbms_stats.gather_schema_stats(
ownname => 'SYSADM',
method_opt=> 'FOR ALL INDEXED COLUMNS SIZE 1',
estimate_percent => 15,
cascade=>TRUE);
end;

delete schema stat
EXEC DBMS_STATS.delete_schema_stats('SCOTT');

export table stat
dbms_stats.export_table_stats(
ownname => 'SYSADM',
tabname =>'my_table',
stattab =>'mystattable',
statid =>'bkup',
cascade =>true);
end;

import table stat
begin
dbms_stats.import_table_stats(
ownname => 'SYSADM',
tabname =>'my_table',
stattab =>'mystattable',
statid =>'bkup',
cascade =>true);
end;

delete table stat
begin
dbms_stats.delete_table_stats(
ownname => 'SYSADM',
tabname =>'my_table');
end;

gather table stat
begin
dbms_stats.gather_table_stats(
ownname => 'SYSADM',
tabname => 'my_table',
method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1',
estimate_percent => 15,
cascade => TRUE );
end;

gather index stat
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK', estimate_percent => 15);

delete index stat
EXEC DBMS_STATS.delete_index_stats('SCOTT', 'EMPLOYEES_PK');

delete column stat
exec dbms_stats.delete_column_stats(USER, 'PERSON', 'PERSON_ID');

check stats
select table_name, num_rows from dba_tables
where table_name in ('PS_JOB','PS_SCRTY_TBL_DEPT','PS_NAMES','PS_PERSON') order by table_name ;

select table_name,index_name, num_rows from dba_indexes
where table_name in ('PS_JOB','PS_SCRTY_TBL_DEPT','PS_NAMES','PS_PERSON') order by table_name ;

select TABLE_NAME, COLUMN_NAME, NUM_BUCKETS from dba_tab_columns
where table_name in ('PS_JOB','PS_SCRTY_TBL_DEPT','PS_NAMES','PS_PERSON') order by table_name, column_name;

select count(*) from PS_SCRTY_TBL_DEPT ;
select count(*) from PS_JOB ;
select count(*) from PS_NAMES ;
select count(*) from PS_PERSON ;


Steps

1. Export current stats
2. Gather or Import or Set Stats
3. trace
4. tkprof

More info



Hope this helps. Regards Rupam

Substitue

# substitute word
perl -e 's/gopher/World Wide Web/gi' -p -i.bak *.html

# Renaming within the name:
ls -1 *old* | awk '{print "mv "$1" "$1}' | sed s/old/new/2 | sh
(although in some cases it will fail, as in file_old_and_old)

OR

for i in *.sh
> do
> y=`echo $i |sed -e 's/hr/hd/'`
> mv $i $y
> done

# Execute as shell script and redirect log file
echo “restore.sh >restore.log 2>&1”|at now

# convert to upper case
cat fileName.txt | tr '[a-z]' '[A-Z]' > newFileName.txt
perl -p -e 'tr/t/p/;' -p -i fileName.txt
perl -p -e 'tr/a-z/A-Z/;' -p -i fileName.txt

-p option tells it to read and process each line from standard input and print the results to standard output
-e option lets you specify a Perl expression (a program, actually) on the command line.

config of a RAC database

lion336@PSPX1$ srvctl config database -d PSPX -a

lion336 PSPX1 /ora01/oracle/product/db_1020
lion337 PSPX2 /ora01/oracle/product/db_1020
lion338 PSPX3 /ora01/oracle/product/db_1020
DB_NAME: PSPX
ORACLE_HOME: /ora01/oracle/product/db_1020
SPFILE: +ORADATA001/PSPX/spfilePSPX.ora
DOMAIN: null
DB_ROLE: null
START_OPTIONS: null
POLICY: AUTOMATIC
ENABLE FLAG: DB ENABLED
lion336@PSPX1:/ora01/oracle/admin/network $

Query RAC sessions

col username format a12
col FAILED_OVER format a12
col machine format a18
set lines 120
select USERNAME,INST_ID, MACHINE,FAILED_OVER,FAILOVER_TYPE,FAILOVER_METHOD,count(*) from gv$session
where username not in ('SYS','DBSNMP') group by
USERNAME,MACHINE,INST_ID,FAILED_OVER,FAILOVER_TYPE,FAILOVER_METHOD
order by USERNAME,INST_ID;

Query Import Status

col table_name format a30
select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
rows_processed,
round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
and open_versions > 0;

export/import using pipe at the same time

# Reference: Oracle Metalink Note 1018477.6 ‘Exporting on Unix Systems’
# example for demo 9.2.0.4 database on apollo (export consists of MMADMIN schema-
# you can also perform a full exp/imp to import all users to the target database):
#


1. Setup pipe and perform export from source

# example for demo 9.2.0.4 database on apollo

oracle@apollo> mknod /ora01/oracle/admin/demo/exp/demo.dmp p
oracle@apollo> exp system file=/ora01/oracle/admin/demo/exp/demo.dmp \
> owner=MMADMIN consistent=y statistics=none log=demo.log
Export: Release 9.2.0.4.0 - Production on Fri Feb 17 16:22:47 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Password:
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user MMADMIN
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user MMADMIN
About to export MMADMIN's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export MMADMIN's tables via Conventional Path ...
. . exporting table MMACCESS 2 rows exported
. . exporting table MMACCESS_BK 5 rows exported
. . exporting table MMACTION 12 rows exported
. . exporting table MMACTIONGROUPING 17 rows exported . . . .




2. Setup pipe and perform import on target

# example for demo 10.2.0.1 database on atlantis

oracle@atlantis> mknod /ora01/oracle/admin/demo/exp/demo2.dmp p
oracle@atlantis> ssh apollo dd if=/ora01/oracle/admin/demo/exp/demo.dmp \
> > /ora01/oracle/admin/demo/exp/demo2.dmp &
[1] 8761
oracle@atlantis> imp system file=/ora01/oracle/admin/demo/exp/demo2.dmp \
> fromuser=MMADMIN touser=MMADMIN buffer=10000000 commit=y log=demo.log
Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing MMADMIN's objects into MMADMIN
. . importing table "MMACCESS" 2 rows imported
. . importing table "MMACCESS_BK" 5 rows imported
. . importing table "MMACTION" 12 rows imported
. . importing table "MMACTIONGROUPING" 17 rows imported
. . importing table "MMAPPLICATION" 5 rows imported
. . importing table "MMCLASS" 202 rows imported
. . importing table "MMCLASSMERGEOPTION" 0 rows imported
. . importing table "MMCONTROL" 5 rows imported . . . .

Gather Financial APPS info

-- For patch information
set pages 1000
set lines 120
column application_name format a50
select a.application_name,
decode(b.status,'I','Installed','S','Shared','N/A') STATUS, PATCH_LEVEL from
APPS.fnd_application_vl a, APPS.fnd_product_installations b where
a.application_id = b.application_id order by 2,1;

--- for URL
select to_char(a.profile_option_id) id,b.profile_option_name,a.profile_option_value
from fnd_profile_option_values a, fnd_profile_options b
where a.profile_option_id=b.profile_option_id and profile_option_value like '%http%';

---for number of nodes
select concurrent_queue_name,target_node,node_name from fnd_concurrent_queues;
select distinct target_node from fnd_concurrent_queues;
select node_name, support_cp,support_forms,support_admin,support_web status from fnd_nodes;

-- node information
select concurrent_queue_name,target_node,node_name from fnd_concurrent_queues;
select distinct target_node from fnd_concurrent_queues;
select node_name, support_cp,support_forms,support_admin,support_web status from fnd_nodes;

Useful File and Filesystem Commands

directory name
dirname /x/y/z/

removing extension from file
$ basename /this/is/a/file.txt .txt

List subdirectores sorted by size
du -k | sort -n

List file sorted by size
ls -l | sort +4n

only in linux :
file size in K,M : ls -lh
files sorted by size : ls -lS



symbols along with file type
ls -F

List just the directory
ls -ld dirname

vmstat

vmstat 1 16 :
Legend:

- kthr = kernel thread - state change (the columns selected are of interest here)
- r = run-queue - number of tasks executing and consuming CPU resources - average per interval
- b = blocked-queue - number of tasks waiting (blocked ) for CPU resources - average per interval
- pi = pages-in - memory contents read to memory from disk (where it was paged out) - avg per second
- po = pages-out - memory contents written from memory to disk (non-zero is NORMAL) - avg per second
- wa = waiting-CPU - percentage of CPU time waiting for external operations (I/O) to finish - avg per interval

A few simple rules of thumb tell us that, when busy, the system is:

MEMORY-BOUND: - the paging size (in MB) > SGA, or non-zero values in the pi column, or page percent too high
CPU-BOUND: - the average values in columns r and b > # of CPU's, or load max > load coef (e.g., 10)
I/O-BOUND: - there are values in the wa column > 20% (up to max 40 percent by some authors)
RUNS-AT-FULL-CAPACITY: - the sum of us and sy approach 100% and id and wa are very small

AIX Handy Commands

CPU
lsdev -C|grep Process|wc -l

Memory
lsattr -El mem0

Swap
lsps -s

sar
sar -q -f /var/adm/sa/sa19|pg #sa19 means date 19th of the month

Process Management
truss -a -e -f -rall -wall -p mypid # mypid means pid number

More info on truss

SQL Performance related Dynamic Views

What is my sesison doing

steps
1. find sid of the session from v$session
2. check v$Session_wait for last wait activity
3. check v$session_event for commuvative waits
4. check v$sesstat for resource usage stats


from where and what



my sid v$mystat
rownum=1

others sid v$session ,v$process

what's up v$sesstat v$statname v$sess_io v$session_wait
CPU used by this session

which segment v$sesion_wait
buffer bust waits db file sequential read db file scattered read free buffer waits

which latch v$session_wait v$latchname
latch free


which sql v$sqltext v$sqlarea v$session
sid






Current State Views

V$SESSION - Sessions currently connected to the instance

v$session_wait - last/current wait
This is a key view for finding bottlenecks. It tells what every session in the
database is currently waiting for (or the last event waited for by the session
if it is not waiting for anything). This view can be used as a starting point
to find which direction to proceed in when a system is experiencing performance
problems.
Since 10g, Oracle displays the v$session_wait information also in the v$session view.

Summary Since Session Startup - cummulative

v$mystat - Resource usage summary for your own session
This view records statistical data about the session that accesses it.

v$session_event - Session-level summary of all the waits for current sessions
This view summarizes wait events for every session. While V$SESSION_WAIT shows
the current waits for a session, V$SESSION_EVENT provides summary of all the
events the session has waited for since it started.

v$sesstat - session-level summary of resource usage since session startup
V$SESSTAT stores session-specific resource usage statistics, beginning at login
and ending at logout.
Includes session logical reads, CPU used by this session, db block changes,
redo size, physical writes, parse count (hard), parse count (total),
sorts (memory), and sorts (disk).
V$SESSTAT can be used to find sessions with the following:

* The highest resource usage
* The highest average resource usage rate (ratio of resource usage to logon time)
* The current resource usage rate (delta between two snapshots)


v$sysstat - Summary of resource usage
V$SYSSTAT stores instance-wide statistics on resource usage, cumulative since
the instance was started.
Similar to V$SESSTAT, this view stores the following types of statistics:

* A count of the number of times an action occurred (user commits)
* A running total of volumes of data generated, accessed, or manipulated (redo size)
* If TIMED_STATISTICS is true, then the cumulative time spent performing some
actions (CPU used by this session)
The data in this view is used for monitoring system performance. Derived statistics, such as the buffer cache hit ratio and soft parse ratio, are computed from V$SYSSTAT data.


v$system_event - cummulative Instance wide summary of resources waited for
This view displays the count (total_waits) of all wait events since startup of the instance.
This view is a summary of waits for an event by an instance. While V$SESSION_WAIT
shows the current waits on the system, V$SYSTEM_EVENT provides a summary of all
the event waits on the instance since it started. It is useful to get a historical
picture of waits on the system. By taking two snapshots and doing the delta on
the waits, you can determine the waits on the system in a given time interval.

v$waitstat - Break down of buffer waits by block class
total_waits where event='buffer busy waits' is equal the sum of count in v$system_event.
This view keeps a summary all buffer waits since instance startup. It is useful
for breaking down the waits by class if you see a large number of buffer busy
waits on the system.

LINK
oracle 9i performance document
oracle 10g performance document