Listener Patch - Steps

As Oracle
agent11g
emctl stop agent

As root:
export ORACLE_HOME=/creditS/GRID/11.2.0.3
export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch
/creditS/orainst/install/crsctl_stat.sh -o

mv $ORACLE_HOME/OPatch $ORACLE_HOME/OPatch.old
mkdir $ORACLE_HOME/OPatch
chown grid:oinstall $ORACLE_HOME/OPatch
chmod 775 $ORACLE_HOME/OPatch


$ORACLE_HOME/bin/crsctl stop crs


$ORACLE_HOME/crs/install/rootcrs.pl –unlock

 
As grid:
export ORACLE_HOME=/creditS/GRID/11.2.0.3
export PATH=$PATH:$ORACLE_HOME/OPatch
unzip -d $ORACLE_HOME /tmp/p6880880_112000_Linux-x86-64.zip
mkdir –p /tmp/oracle_patch
unzip -d /tmp/oracle_patch  /tmp/p12880299_112030_Linux-x86-64.zip
cd /tmp/oracle_patch/12880299
opatch apply -oh $ORACLE_HOME -local /tmp/oracle_patch/12880299
# give no email address, just press enter.
# say Y to the question: Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: Y
 
As root:
export ORACLE_HOME=/creditS/GRID/11.2.0.3
export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch
$ORACLE_HOME/crs/install/rootcrs.pl -patch
/creditS/orainst/install/crsctl_stat.sh -o

If needed:
$ORACLE_HOME/bin/crsctl stop  crs
$ORACLE_HOME/bin/crsctl start crs

-------------------------------------------------


 As root:
(Logoff and login again to clear your environment)

export ORACLE_HOME=/creditS/oracle/product/11.2.0.3
export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch
/creditS/orainst/install/crsctl_stat.sh -o

mv $ORACLE_HOME/OPatch $ORACLE_HOME/OPatch.old
mkdir $ORACLE_HOME/OPatch
chown oracle:oinstall $ORACLE_HOME/OPatch
chmod 775 $ORACLE_HOME/OPatch

/creditS/GRID/11.2.0.3/bin/crsctl stop crs





As oracle:
export ORACLE_HOME=/creditS/oracle/product/11.2.0.3
export PATH=$PATH:$ORACLE_HOME/OPatch
cd /creditS/orainst/11.2.0.2/RedHat5_64Bit/patches
unzip –d $ORACLE_HOME p6880880_112000_Linux-x86-64.zip
cd /tmp/oracle_patch/12880299
opatch apply -local
# give no email address, just press enter.
# say Y to the question: Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: Y

As root:
export ORACLE_HOME=/creditS/GRID/11.2.0.3
export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch
$ORACLE_HOME/bin/crsctl start crs
/creditS/orainst/install/crsctl_stat.sh -o



As Oracle
agent11g
emctl start agent

MAP NFS

To map NFS execute below command -

$ cd /cts/orainst/install;./mapnfs.sh -B

---------------------------------------------------------------------

sudo /creditS/orainst/install/mapnfs.sh -A -t asmdat -g DATA1 -q 2 -s /data/oracle

sudo /creditS/orainst/install/mapnfs.sh -A -t asmdat -q 2 -s /data/oracle


I wanted to created 40 LUNs in /data/oracle/SLNTR010_DATA and have these available to ASM Disk Group SLNTR010_DATA1.


grid@srv-20001085:/creditS/oracle/grid/ [+ASM1] sudo /creditS/orainst/install/mapnfs.sh
INFO: UID of running user is 0
Usage: mapnfs.sh ... options ...
  -B # (Boot) - tworzy linki.
  -I [-z LUN size] # (Install)
  -D # (Deinstall)
  -A -t dbpw|asmdat [-g dgname] [-q num] [-s SMO mount] # (Add device(S))
                    [-z LUN size] # (Add device (cont)
  -R -l devname # (Remove device)
                                                         # (Install and Add Lun Sizes)
                                                         # LUN Size: Small = 9Gb Luns
                                                         # LUN Size: Medium = 64Gb Luns
                                                         # LUN Size: Large = 256Gb Luns
                                                         # LUN Size: XLarge = 512Gb Luns
grid@srv-20001085:/creditS/oracle/grid/ [+ASM1]


sudo /creditS/orainst/install/mapnfs.sh -A -t asmdat -g STNDBY20_DATA1 -q 2 -s /data/oracle/STNDBY20_DATA -z XLarge



grid@srv-20001085:/creditS/oracle/grid/ [+ASM1] sudo /creditS/orainst/install/mapnfs.sh -A -t asmdat -g STNDBY20_DATA1 -q 1 -s /data/oracle/STNDBY20_DATA
INFO: UID of running user is 0
Small Lun Size 9216 Mb.
Looks like there is enough room in /data/oracle/STNDBY20_DATA
Creating /data/oracle/STNDBY20_DATA/asmdat_s1lnmr20_data1.0052.9216.lun ...
9216+0 records in
9216+0 records out
9663676416 bytes (9.7 GB) copied, 74.6952 seconds, 129 MB/s
grid@srv-20001085:/creditS/oracle/grid/ [+ASM1]


sudo /creditS/orainst/install/mapnfs.sh -R -l /data/oracle/STNDBY20_DATA/asmdat_s1lnmr20_data1.0052.9216.lun



grid@srv-20001085:/creditS/oracle/grid/ [+ASM1] 0_DATA1 -q 2 -s /data/oracle/STNDBY20_DATA -z XLarge <
INFO: UID of running user is 0
XLarge Lun Size 524288 Mb.
Looks like there is enough room in /data/oracle/STNDBY20_DATA
Creating /data/oracle/STNDBY20_DATA/asmdat_s1lnmr20_data1.0052.524288.lun ...
524288+0 records in
524288+0 records out
549755813888 bytes (550 GB) copied, 5990.54 seconds, 91.8 MB/s
Creating /data/oracle/STNDBY20_DATA/asmdat_s1lnmr20_data1.0053.524288.lun ...
524288+0 records in
524288+0 records out
549755813888 bytes (550 GB) copied, 4766.49 seconds, 115 MB/s


just one command
sudo /creditS/orainst/install/mapnfs.sh -A -t asmdat -g STNDBY20_DATA1 -q 1 -s /data/oracle/STNDBY20_DATA

that will -A add asmdat disks to group STNDBY20_DATA1

-q quantity =1

-s link

so after that command
sudo /creditS/orainst/install/mapnfs.sh -A -t asmdat -g STNDBY20_DATA1 -q 1 -s /data/oracle/STNDBY20_DATA

You will have to run
sudo /creditS/orainst/install/mapnfs.sh -B
and You have new candidats in asm
if You add -z You can select what would be new size of disk
okay, great ..

Oracle wait events

db file sequential read  =>

Tune indexing, tune SQL (to do less I/O), tune disks, increase buffer cache. This event is indicative of disk contention on index reads. Make sure all objects are analyzed. Redistribute I/O across disks. The wait that comes from the physical side of the database. It related to memory starvation and non selective index use. Sequential read is an index read followed by table read because it is doing index lookups which tells exactly which block to go to.

db file scattered read => disk contention on full table scans. Add indexes, tune SQL, tune disks,
refresh statistics, and create materialized view. caused due to full table scans may be because of insufficient indexes or unavailability of updated statistics.

Archives Archiving Firmwares Objects Hot Blog For Poor Interview Database

db file parallel read  => tune SQL, tune indexing, tune disk I/O, increase buffer cache. if you are doing a lot of partition activity then expect to see that wait even. it could be a table or index partition.

db file parallel write  => if you are doing a lot of partition activity then expect to see that wait even. it could be a table or index partition.

db file single write  => if you see this event than probably you have a lot of data files in your database.

control file sequential read

control file parallel write

log file sync    => committing too often, archive log generation is more. Tune applications to commit
less, tune disks where redo logs exist, try using nologging/unrecoverable options, log buffer could be too large.

log file switch completion => May need more log files per group.

log file parallel write  => Deals with flushing out the redo log buffer to disk. Disks may be too slow or have an I/O bottleneck. Look for log file contention.

log buffer space   => Increase LOG_BUFFER parameter or move log files to faster disks. Tune application, use NOLOGGING, and look for poor behavior that updates an entire row when only a few columns change.

log file switch (checkpoint incomplete) => May indicate excessive db files or slow IO subsystem.

log file switch (archiving needed)    => Indicates archive files are written too slowly.

redo buffer allocation retries  => shows the number of times a user process waited for space in the redo log buffer.

redo log space wait time  => shows cumulative time (in 10s of milliseconds) waited by all processes waiting for space in the log buffer.

buffer busy waits/ read by other session  => Increase DB_CACHE_SIZE. Tune SQL, tune indexing, we often see this event along with full table scans, if the SQL is inserting data, consider increasing FREELISTS and/or INITRANS, if the waits are on segment header blocks, consider increasing extent sizes.

free buffer waits  => insufficient buffers, process holding buffers too long or i/o subsystem is over loaded. Also check you db writes may be getting clogged up.

cache buffers lru chain  => Freelist issues, hot blocks.

no free buffers   => Insufficient buffers, dbwr contention.

latch free
latch: session allocation

latch: in memory undo latch  => If excessive could be bug, check for your version, may have to turn
off in memory undo.

latch: cache buffer chains  => check hot objects.

latch: cache buffer handles  => Freelist issues, hot blocks.

direct path write => You wont see them unless you are doing some appends or data loads.

direct path reads => could happen if you are doing a lot of parallel query activity.
direct path read temp or direct path write temp => this wait event shows Temp file activity (sort,hashes,temp tables, bitmap) check pga parameter or sort area or hash area parameters. You might want to increase them.

library cache load lock

library cache pin => if many sessions are waiting, tune shared pool, if few sessions are waiting, lock is session specific.

library cache lock  => need to find the session holding the lock, look for DML manipulating an object being accessed, if the session is trying to recompile PL/SQL, look for other sessions executing the code.

undo segment extension  => If excessive, tune undo.

wait for a undo record   => Usually only during recovery of large transactions, look at turning off
parallel undo recovery.

enque wait events   => Look at V$ENQUEUE_STAT

Scripts related to TEMP TABLESPACE

To check instance-wise total allocated, total used TEMP for both rac and non-rac

set lines 152

col FreeSpaceGB format 999.999

col UsedSpaceGB format 999.999

col TotalSpaceGB format 999.999

col host_name format a30

col tablespace_name format a30

select tablespace_name,

(free_blocks*8)/1024/1024 FreeSpaceGB,

(used_blocks*8)/1024/1024 UsedSpaceGB,

(total_blocks*8)/1024/1024 TotalSpaceGB,

i.instance_name,i.host_name

from gv$sort_segment ss,gv$instance i where ss.tablespace_name in (select tablespace_name from dba_tablespaces where contents='TEMPORARY') and

i.inst_id=ss.inst_id;

Total Used and Total Free Blocks

select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks  from gv$sort_segment;

Another Query to check TEMP USAGE

col name for a20

SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management

"ExtManag",

TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", TO_CHAR(NVL(t.bytes,

0)/1024/1024,'99999,999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0),'99999,999.999') "Used (M)",

TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"

FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by

tablespace_name) a,

(select tablespace_name, sum(bytes_cached) bytes from

v$temp_extent_pool group by tablespace_name) t

WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+)

AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';

Temporary Tablespace groups

SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

select tablespace_name,contents from dba_tablespaces where tablespace_name like '%TEMP%';

select * from dba_tablespace_groups;

Block wise Check

select TABLESPACE_NAME, TOTAL_BLOCKS, USED_BLOCKS, MAX_USED_BLOCKS, MAX_SORT_BLOCKS, FREE_BLOCKS from V$SORT_SEGMENT;

select sum(free_blocks) from gv$sort_segment where tablespace_name = 'TEMP';

To Check Percentage Usage of Temp Tablespace

select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"

from (select sum(used_blocks) tot_used_blocks

from v$sort_segment where tablespace_name='TEMP') s,

(select sum(blocks) total_blocks

from dba_temp_files where tablespace_name='TEMP') f;

To check Used Extents ,Free Extents available in Temp Tablespace

SELECT tablespace_name, extent_size, total_extents, used_extents,free_extents, max_used_size FROM v$sort_segment;

To list all tempfiles of Temp Tablespace

col file_name for a45

select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_temp_files  order by file_name;

SELECT d.tablespace_name tablespace , d.file_name filename, d.file_id fl_id, d.bytes/1024/1024

size_m

, NVL(t.bytes_cached/1024/1024, 0) used_m, TRUNC((t.bytes_cached / d.bytes) * 100) pct_used

FROM

sys.dba_temp_files d, v$temp_extent_pool t, v$tempfile v

WHERE (t.file_id (+)= d.file_id)

AND (d.file_id = v.file#);

Additional checks

select distinct(temporary_tablespace) from dba_users;

select username,default_tablespace,temporary_tablespace from dba_users order by temporary_tablespace;

SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

Changing the default temporary Tablespace

SQL> alter database default temporary tablespace TEMP;

Database altered.

To add tempfile to Temp Tablespace

alter tablespace  temp  add tempfile '&tempfilepath' size 1800M;

alter tablespace temp add tempfile '/m001/oradata/SID/temp02.dbf' size 1000m;

alter tablespace TEMP add tempfile '/SID/oradata/data02/temp04.dbf' size 1800M autoextend on maxsize 1800M;

To resize the  tempfile in Temp Tablespace

alter database tempfile '/u02/oradata/TESTDB/temp01.dbf' resize 250M

alter database tempfile '/SID/oradata/data02/temp12.dbf' autoextend on maxsize 1800M;

alter tablespace TEMP add tempfile '/SID/oradata/data02/temp05.dbf' size 1800m reuse;

To find Sort Segment Usage by Users

select username,sum(extents) "Extents",sum(blocks) "Block"

from v$sort_usage

group by username;

To find Sort Segment Usage by a particular User

SELECT s.username,s.sid,s.serial#,u.tablespace, u.contents, u.extents, u.blocks

FROM v$session s, v$sort_usage u

WHERE s.saddr=u.session_addr

order by u.blocks desc;

To find Total Free space in Temp Tablespace

select 'FreeSpace  ' || (free_blocks*8)/1024/1024 ||' GB'  from v$sort_segment where tablespace_name='TEMP';

select tablespace_name , (free_blocks*8)/1024/1024  FreeSpaceInGB,

(used_blocks*8)/1024/1024  UsedSpaceInGB,

(total_blocks*8)/1024/1024  TotalSpaceInGB

from v$sort_segment where tablespace_name like '%TEMP%'

To find  Total Space Allocated for Temp Tablespace

select 'TotalSpace ' || (sum(blocks)*8)/1024/1024 ||' GB'  from dba_temp_files where tablespace_name='TEMP';

Get 10 sessions with largest temp usage

cursor bigtemp_sids is

select * from (

select s.sid,

s.status,

s.sql_hash_value sesshash,

u.SQLHASH sorthash,

s.username,

u.tablespace,

sum(u.blocks*p.value/1024/1024) mbused ,

sum(u.extents) noexts,

nvl(s.module,s.program) proginfo,

floor(last_call_et/3600)||':'||

floor(mod(last_call_et,3600)/60)||':'||

mod(mod(last_call_et,3600),60) lastcallet

from v$sort_usage u,

v$session s,

v$parameter p

where u.session_addr = s.saddr

and p.name = 'db_block_size'

group by s.sid,s.status,s.sql_hash_value,u.sqlhash,s.username,u.tablespace,

nvl(s.module,s.program),

floor(last_call_et/3600)||':'||

floor(mod(last_call_et,3600)/60)||':'||

mod(mod(last_call_et,3600),60)

order by 7 desc,3)

where rownum < 11;

Displays the amount of IO for each tempfile

SELECT SUBSTR(t.name,1,50) AS file_name,

f.phyblkrd AS blocks_read,

f.phyblkwrt AS blocks_written,

f.phyblkrd + f.phyblkwrt AS total_io

FROM   v$tempstat f,v$tempfile t

WHERE  t.file# = f.file#

ORDER BY f.phyblkrd + f.phyblkwrt DESC;

select * from (SELECT u.tablespace, s.username, s.sid, s.serial#, s.logon_time, program, u.extents, ((u.blocks*8)/1024) as MB,

i.inst_id,i.host_name

FROM gv$session s, gv$sort_usage u ,gv$instance i

WHERE s.saddr=u.session_addr and u.inst_id=i.inst_id  order by MB DESC) a where rownum<10;

Check for ORA-1652

show parameter background

cd <background dump destination>

ls -ltr|tail

view <alert log file name>

shift + G ---> to get the tail end...

?ORA-1652 ---- to search of the error...

shift + N ---- to step for next reported error...

I used these queries to check some settings:

-- List all database files and their tablespaces:

select  file_name, tablespace_name, status

,bytes   /1000000  as MB

,maxbytes/1000000  as MB_max

from dba_data_files ;

-- What temporary tablespace is each user using?:

select username, temporary_tablespace, default_tablespace from dba_users ;

-- List all tablespaces and some settings:

select tablespace_name, status, contents, extent_management

from dba_tablespaces ;

TABLESPACE_NAME                CONTENTS  EXTENT_MAN STATUS

------------------------------ --------- ---------- ---------

SYSTEM                         PERMANENT DICTIONARY ONLINE

TOOLS                          PERMANENT DICTIONARY ONLINE

TEMP                           TEMPORARY DICTIONARY OFFLINE

TMP                            TEMPORARY LOCAL      ONLINE

Now, the above query and the storage clause of the old 'create tablespace TEMP' command seem to tell us the tablespace only allows temporary objects, so it should be safe to assume that no one created any tables or other permanent objects in TEMP by mistake, as I think Oracle would prevent that. However, just to be absolutely certain, I decided to double-check. Checking for any tables in the tablespace is very easy:

-- Show number of tables in the TEMP tablespace - SHOULD be 0:

select count(*)  from dba_all_tables

where tablespace_name = 'TEMP' ;

Checking for any other objects (views, indexes, triggers, pl/sql, etc.) is trickier, but this query seems to work correctly - note that you'll probably need to connect internal in order to see the sys_objects view:

-- Shows all objects which exist in the TEMP tablespace - should get

-- NO rows for this:

column owner        format a20

column object_type  format a30

column object_name  format a40

select

o.owner  ,o.object_name

,o.object_type

from sys_objects s

,dba_objects o

,dba_data_files df

where df.file_id = s.header_file

and o.object_id = s.object_id

and df.tablespace_name = 'TEMP' ;

Identifying WHO is currently using TEMP Segments

10g onwards

SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.block_size)/1048576 MB_used, c.sql_text

FROM v$session a, v$tempseg_usage b, v$sqlarea c,

     (select block_size from dba_tablespaces where tablespace_name='TEMP') d

    WHERE b.tablespace = 'TEMP'

    and a.saddr = b.session_addr

    AND c.address= a.sql_address

    AND c.hash_value = a.sql_hash_value

    AND (b.blocks*d.block_size)/1048576 > 1024

    ORDER BY b.tablespace, 6 desc;

Scripts related to TEMP Tablespace

To see Temp Space:
-------------------

SELECT a.tablespace_name,ROUND((c.total_blocks*b.block_size)/1024/1024/1024,2)
"Total Size [GB]",ROUND((a.used_blocks*b.block_size)/1024/1024/1024,2) "Used_size[GB]",
ROUND(((c.total_blocks-a.used_blocks)*b.block_size)/1024/1024/1024,2) "Free_size[GB]",
ROUND((a.max_blocks*b.block_size)/1024/1024/1024,2) "Max_Size_Ever_Used[GB]",          
ROUND((a.max_used_blocks*b.block_size)/1024/1024/1024,2) "MaxSize_ever_Used_by_Sorts[GB]" ,
ROUND((a.used_blocks/c.total_blocks)*100,2) "Used Percentage"
FROM V$sort_segment a,dba_tablespaces b,(SELECT tablespace_name,SUM(blocks)
total_blocks FROM dba_temp_files GROUP by tablespace_name) c
WHERE a.tablespace_name=b.tablespace_name AND a.tablespace_name=c.tablespace_name;


==============================================================================================
To see top 10 consuming process :
---------------------------------

select * from
(SELECT d.tablespace_name,a.sid,a.serial#,a.program,a.module,a.action,a.username "DB Username",a.osuser,ROUND((b.blocks*d.block_size)/1024/1024,2) "Used MB",c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c,dba_tablespaces d
WHERE a.saddr = b.session_addr AND c.address= a.sql_address AND c.hash_value = a.sql_hash_value AND d.tablespace_name=b.tablespace ORDER BY b.tablespace, b.blocks DESC)
where rownum <=10


==============================================================================================

Query to check TEMP USAGE :
----------------------------

col name for a20
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management
"ExtManag",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", TO_CHAR(NVL(t.bytes,
0)/1024/1024,'99999,999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0),'99999,999.999') "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by
tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes from
v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';



=================================================================================================

To Check Percentage Usage of Temp Tablespace:
---------------------------------------------

select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"
from (select sum(used_blocks) tot_used_blocks
from v$sort_segment where tablespace_name='TEMP') s,
(select sum(blocks) total_blocks
from dba_temp_files where tablespace_name='TEMP') f;

=================================================================================================
To find Sort Segment Usage by a particular User:
------------------------------------------------

SELECT s.username,s.sid,s.serial#,u.tablespace, u.contents, u.extents, u.blocks
FROM v$session s, v$sort_usage u
WHERE s.saddr=u.session_addr
order by u.blocks desc;

===================================================================================================
To find Total Free space in Temp Tablespace :
---------------------------------------------

select tablespace_name , (free_blocks*8)/1024/1024  FreeSpaceInGB,
(used_blocks*8)/1024/1024  UsedSpaceInGB,
(total_blocks*8)/1024/1024  TotalSpaceInGB
from v$sort_segment where tablespace_name like '%TEMP%'

=====================================================================================================
Get 10 sessions with largest temp usage :
-----------------------------------------

cursor bigtemp_sids is
select * from (
select s.sid,
s.status,
s.sql_hash_value sesshash,
u.SQLHASH sorthash,
s.username,
u.tablespace,
sum(u.blocks*p.value/1024/1024) mbused ,
sum(u.extents) noexts,
nvl(s.module,s.program) proginfo,
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) lastcallet
from v$sort_usage u,
v$session s,
v$parameter p
where u.session_addr = s.saddr
and p.name = 'db_block_size'
group by s.sid,s.status,s.sql_hash_value,u.sqlhash,s.username,u.tablespace,
nvl(s.module,s.program),
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60)
order by 7 desc,3)
where rownum < 11;

============================================================================================

Identifying WHO is currently using TEMP Segments :
---------------------------------------------------


SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.block_size)/1048576 MB_used, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c,
     (select block_size from dba_tablespaces where tablespace_name='TEMP') d
    WHERE b.tablespace = 'TEMP'
    and a.saddr = b.session_addr
    AND c.address= a.sql_address
    AND c.hash_value = a.sql_hash_value
    AND (b.blocks*d.block_size)/1048576 > 1024
    ORDER BY b.tablespace, 6 desc;


=============================================================================================

I/O issue on QA environment - real time resolution

Please find below our findings regarding QA_DB performance issues.

Disk usage from last 31 days is showing us, that starting Monday disk reads significantly growth. You can see, that previously there was no so much reads from disks on the server. 

In alert log I’ve found high number of rows like (hundreds of them):

Deleted Oracle managed file +QDB_FR1/qln_db/flashback/log_2651.582.877530961  

Deleted Oracle managed file +QDB_FR1/qln_dbflashback/log_2650.2190.877530957  

Deleted Oracle managed file +QDB_FR1/qln_db/flashback/log_2649.2988.877530955  

Deleted Oracle managed file +QDB_FR1/qln_db/flashback/log_2648.337.877530953  

Deleted Oracle managed file +QDB_FR1/qln_db/flashback/log_2647.731.877530951  

Oracle started to cleanup space for new flashback logs by deleting old ones. This operation took most 

of I/O bandwidth, and You’ve experienced waits as described.

All those events occurred during the time, when You faced performance issues. All I/O performace issues are related to flashback feature enabled on your database.

Also all of them were observer in same time with big database workload (a lot of sessions, big number executions of some single sqlid’s, etc.)

According to my knowledge, this is how oracle is behaving, it is deleting flash log files, when needed. So in cases, when database is loaded by current ‘heavy’ usage, oracle delete operation can consume additional resources and cause performance issues.

You need to remember, that this is shared cluster, with many databases running. All resources are also shared between those databases.

Generating SSH key using ssh-keygen

oracle@DB Server:/cs/oracle/data/.ssh $ [OraDB1]ssh-keygen -t rsa

Generating public/private rsa key pair.

Enter file in which to save the key (/cs/oracle/data/.ssh/id_rsa): /cs/oracle/data/.ssh/id_rsa2

Enter passphrase (empty for no passphrase): 

Enter same passphrase again: 

Your identification has been saved in /cs/oracle/data/.ssh/id_rsa2.

Your public key has been saved in /cs/oracle/data/.ssh/id_rsa2.pub.

The key fingerprint is:

4e:8c:91:52:62:69:87:88:19:23:70:d8:34:af:e5:d6 oracle@DB Server

oracle@DB Server:/cs/oracle/data/.ssh $ [OraDB1]ssh-keygen -t dsa

Generating public/private dsa key pair.

Enter file in which to save the key (/cs/oracle/data/.ssh/id_dsa): /cs/oracle/data/.ssh/id_dsa2

Enter passphrase (empty for no passphrase): 

Enter same passphrase again: 

Your identification has been saved in /cs/oracle/data/.ssh/id_dsa2.

Your public key has been saved in /cs/oracle/data/.ssh/id_dsa2.pub.

The key fingerprint is:

b8:5c:8f:e9:4f:c0:ac:60:59:bc:ea:5c:68:b9:4b:10 oracle@DB Server

oracle@DB Server:/cs/oracle/data/.ssh $ [OraDB1]cat id_rsa2.pub > auth.temp

oracle@DB Server:/cs/oracle/data/.ssh $ [OraDB1]cat id_dsa2.pub >> auth.temp

oracle@DB Server:/cs/oracle/data/.ssh $ [OraDB1]cat auth.temp

CRS STAT - Command

will list all CRS related details , which disk is Up and which is not ,



crs_stat | awk -F= '/NAME=/{n=$2}/TYPE=/{t=$2}/TARGET=/{g=$2}/STATE=/{s=$2; printf("%-45s%-15s%-10s%-30s\n", n,t,g,s)}'

Add LUN in Disk Group from SPARE_DISK (ASM)

-- Create ASM metadata backup of DB01_DATA1 on Host:

ASMCMD> md_backup /cs/oracle/grid/asm_md_backup.041115

Please extend diskgroup DB01_DATA1 by using disk from Spare disk group.

Choosen disk for addition: /dev/oracle/asmdat.emc0_4ad8.258063.lun  -- chosen from SPARE_DG
 
-- check if disk is still in SPARE group ( if disk is still in SPARE_GROUP )

select inst_id, name, path from gv$asm_disk where path IN('/dev/oracle/asmdat.emc0_4ad8.258063.lun');


SQL> select inst_id, name, path from gv$asm_disk where path IN('/dev/oracle/asmdat.emc0_4ad8.258063.lun');

   INST_ID NAME                      PATH
---------- ------------------------- -----------------------------------------
         4 SPARE_DG_0004             /dev/oracle/asmdat.emc0_4ad8.258063.lun
         3 SPARE_DG_0004             /dev/oracle/asmdat.emc0_4ad8.258063.lun
         2 SPARE_DG_0004             /dev/oracle/asmdat.emc0_4ad8.258063.lun
         5 SPARE_DG_0004             /dev/oracle/asmdat.emc0_4ad8.258063.lun
         1 SPARE_DG_0004             /dev/oracle/asmdat.emc0_4ad8.258063.lun

SQL> 

-- drop disk from spare group

ALTER DISKGROUP SPARE_DG DROP DISK SPARE_DG_0004;

-- check whether rebalance is complete (keeping on checking until the output is "no rows selected").

  set lin 250
  select B.NAME as DISKGROUP_NAME, A.OPERATION, A.STATE, A.POWER, A.SOFAR, A.EST_WORK, A.EST_RATE, A.EST_MINUTES 
  from V$ASM_OPERATION a,v$asm_diskgroup b 
  where a.GROUP_NUMBER=b.group_number;

-- check free name in DB01_DATA1 group

  set pagesize 100
  SELECT a.NAME as DISK_NAME
  from v$asm_disk a, v$asm_diskgroup b 
  where a.group_number=b.group_number 
  and b.name like '%DB01_DATA1%'
  order by 1;

-- add disk  to DB01_DATA1

ALTER DISKGROUP DB01_DATA1 ADD DISK '/dev/oracle/asmdat.emc0_4ad8.258063.lun' NAME DB01_DATA1_0005;

-- check rebalance (keeping on checking until the output is "no rows selected")

  set lin 250
  select B.NAME as DISKGROUP_NAME, A.OPERATION, A.STATE, A.POWER, A.SOFAR, A.EST_WORK, A.EST_RATE, A.EST_MINUTES 
  from V$ASM_OPERATION a,v$asm_diskgroup b 
  where a.GROUP_NUMBER=b.group_number;

-- Remove the 36GB luns from the DB01_DATA1 DG 
-- choose the 36GB disks from the below query :
set lines 200 pages 200
col name format a25
col dg_name format a20
col path format a50
col state format a10
col MOUNT_STATUS format a10
col HEADER_STATUS format a12
select AD.GROUP_NUMBER,ADG.NAME DG_NAME,ADG.STATE,AD.DISK_NUMBER,AD.MOUNT_STATUS,AD.HEADER_STATUS,AD.NAME,AD.OS_MB,AD.PATH 
from 
v$asm_disk AD,
v$asm_diskgroup ADG
where AD.GROUP_NUMBER=ADG.GROUP_NUMBER (+)
--and ADG.NAME like '%SPARE%'
and ADG.NAME like '%DB01_DATA1%'
order by ADG.NAME,AD.NAME;

GROUP_NUMBER DG_NAME              STATE      DISK_NUMBER MOUNT_STAT HEADER_STATU NAME                           OS_MB PATH
------------ -------------------- ---------- ----------- ---------- ------------ ------------------------- ---------- --------------------------------------------------
          41 DB01_DATA1       MOUNTED              0 CACHED     MEMBER       DB01_DATA1_0000            36866 /dev/oracle/asmdat.emc0_10c6.36866.lun
          41 DB01_DATA1       MOUNTED              1 CACHED     MEMBER       DB01_DATA1_0001           258063 /dev/oracle/asmdat.emc0_3278.258063.lun
          41 DB01_DATA1       MOUNTED              2 CACHED     MEMBER       DB01_DATA1_0002            36866 /dev/oracle/asmdat.emc0_2401.36866.lun
          41 DB01_DATA1       MOUNTED              3 CACHED     MEMBER       DB01_DATA1_0003            36866 /dev/oracle/asmdat.emc0_1130.36866.lun
          41 DB01_DATA1       MOUNTED              4 CACHED     MEMBER       DB01_DATA1_0004            36866 /dev/oracle/asmdat.emc0_1126.36866.lun


-- chosen disks to be removed :
   /dev/oracle/asmdat.emc0_10c6.36866.lun
   /dev/oracle/asmdat.emc0_2401.36866.lun
   /dev/oracle/asmdat.emc0_1130.36866.lun
   /dev/oracle/asmdat.emc0_1126.36866.lun   
  

ALTER DISKGROUP DB01_DATA1 DROP DISK DB01_DATA1_0000;
ALTER DISKGROUP DB01_DATA1 DROP DISK DB01_DATA1_0002;
ALTER DISKGROUP DB01_DATA1 DROP DISK DB01_DATA1_0003;
ALTER DISKGROUP DB01_DATA1 DROP DISK DB01_DATA1_0004;

-- check rebalance (keeping on checking until the output is "no rows selected")

  set lin 250
  select B.NAME as DISKGROUP_NAME, A.OPERATION, A.STATE, A.POWER, A.SOFAR, A.EST_WORK, A.EST_RATE, A.EST_MINUTES 
  from V$ASM_OPERATION a,v$asm_diskgroup b 
  where a.GROUP_NUMBER=b.group_number;