mylearning4oracle

From 11GR2 on wards oracle have introduced grid infrastructure software , which contains both cluster-ware and ASM . We can use the same software to install oracle RAC also Pre-checks: 1-> download the oracle grid software from OTN 2-> I am using oracle vmware , so go to settings and then go to storage and the select create new virtual hard disk of fixed storage 3-> Now start the RHEL6 and then start creating partitions using FDISK utility 4-> Now we have to create RAW disks using raw utility but we have to make sure the raw partition information to be permanently updated for every reboot of the system. note: === Raw  Disk Definition. The term  raw  disk refers to the accessing of the data on a hard disk drive (HDD) or other disk storage device or media directly at the individual byte level instead of through its  file-system  as is usually done 5-> Now check the network connectivity  and also configure required network setting...

Performance Tuning for overall system performance goes down ============================================================== 1) first look at the top processes. using TOP command 2) ps -ef| grep  says the database to which the session belongs. or take strace -p < pid> , it will generate trace file for the PID, you can get the instance name for which this pid belongs to. 3) From the TOP command know the process id and match the paddr in v$session with addr in v$process where spid = process number that you see in the top command In the select clause, you project,sid,  3)After finding the sid we need to get the sql text select sql_text from v$sqltext where sid=153; wait for few minutes and see if it the correct one. 4)select event,total_waits from v$session where sid=153 order by total_waits; 5) see the two tables how much time the session is waiting for the event to occur v$session_event, is the what event the session is waiting. 6) Then go to attain a trac...

Execute the following query to determine who is using a particular UNDO or Rollback Segment: ============================================================================================= SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,         NVL(s.username, 'None') orauser,         s.program,         r.name undoseg,         t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"    FROM sys.v_$rollname    r,         sys.v_$session     s,         sys.v_$transaction t,         sys.v_$parameter   x  WHERE s.taddr = t.addr      AND r.usn   = t.xidusn(+)     AND x.name  = 'db_block_size'  Execute the following query to determine who is using a TEMP Segment: ======================================...

select to_char(creation_time, 'MM-RRRR') "Month", sum(bytes)/1024/1024/1024 "Growth in GB"  from sys.v_$datafile  where to_char(creation_time,'RRRR')='2017'  group by to_char(creation_time, 'MM-RRRR')  order by  to_char(creation_time, 'MM-RRRR');  select b.tsname tablespace_name , MAX(b.used_size_mb) cur_used_size_mb , round(AVG(inc_used_size_mb),2)avg_increas_mb   from ( SELECT a.days,a.tsname , used_size_mb , used_size_mb - LAG (used_size_mb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb  from ( SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days  ,ts.tsname ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb  from dba_hist_tbspc_space_usage  tsu , dba_hist_tablespace_stat  ts ,dba_hist_snapshot  sp, dba_tablespaces  dt   where tsu.tablespace_id= ts.ts#  AND tsu.snap_id = sp.snap_id  AND ts.tsname = dt.tablespace_na...

#!/usr/bin/ksh ## Description : This script will go through each and every ASM disk and validate Queue_Depth,Reserve_Policy,pvid and also provides the basic disk information. function asm_details { cd /home/oracle/ export SID=`ps -ef | grep pmon | grep ASM | awk -Fpmon_ '{print $2}'` export ORACLE_SID=$SID export ORACLE_HOME=`grep $SID /etc/oratab | cut -d: -f2` export PATH=$ORACLE_HOME/bin:$PATH ct=$(date "+%Y.%m.%d-%H.%M.%S") $ORACLE_HOME/bin/kfod status=TRUE disk=all dscvgroup=TRUE > Disk_details_$ct export f_name=Disk_details_$ct printf "=======================================================================================================================\n" > asm_disk_details_$ct printf "%-22s | %-10s |%-15s | %-10s |%-11s | %-10s |%-4s | %-12s | %-10s\n "  Disk_Name Perm Owner size-MB Status DG Q_Depth Res_Policy pvid>> asm_disk_details_$ct printf "==============================================================...

ASH analysis : Who is blocking the SQL between start time and end time. @ash_wait_chains username||':'||program2||event2 sql_id='3rtbs9vqukc71' "timestamp'2013-10-05 01:00:00'" "timestamp'2013-10-05 03:00:00'"           -- Usage:      --     @ash_wait_chains <grouping_cols> <filters> <fromtime> <totime> -- -- Example: --     @ash_wait_chains username||':'||program2||event2 session_type='FOREGROUND' sysdate-1/24 sysdate -- -- Other: --     This script uses only the in-memory V$ACTIVE_SESSION_HISTORY, use --     @dash_wait_chains.sql for accessiong the DBA_HIST_ACTIVE_SESS_HISTORY archive -- --     Oracle 10g does not  have the BLOCKING_INST_ID column in ASH so you'll need --     to comment out this column in this script. This may give you somewhat --     incorrect results in RAC environment with ...

Top of Form 1 ASH, awr, Performance Tuning, scripts Top Recent Wait Events col EVENT format a60 select * from ( select active_session_history.event, sum(active_session_history.wait_time + active_session_history.time_waited) ttl_wait_time from v$active_session_history active_session_history where active_session_history.event is not null group by active_session_history.event order by 2 desc) where rownum < 6 / Top Wait Events Since Instance Startup col event format a60 select event, total_waits, time_waited from v$system_event e, v$event_name n where n.event_id = e.event_id and n.wait_class !='Idle' and n.wait_class = (select wait_class from v$session_wait_class  where wait_class !='Idle'  group by wait_class having sum(time_waited) = (select max(sum(time_waited)) from v$session_wait_class where wait_class !='Idle' group by (wait_class))) order by 3; List Of Users Currently Waiting col username fo...