Sunday, November 18, 2012
AIX commands for Oracle DBA
Usefull Commands on AIX for Oracle DBA.
Displaying the top 10 CPU-consuming processes
--> ps aux | head -1; ps aux | sort -rn +2 | head -10
Displaying the top 10 memory-consuming processes
--> ps aux | head -1; ps aux | sort -rn +3 | head
Displaying the top 10 memory-consuming processes using SZ
--> ps -ealf | head -1 ; ps -ealf | sort -rn +9 | head
Displaying the processes in order of being penalized
--> ps -eakl | head -1 ; ps -eakl | sort -rn +5
Displaying the processes in order of priority
--> ps -eakl | sort -n +6 | head
Displaying the processes in order of nice value
--> ps -eakl | sort -n +7
Displaying the processes in order of time
--> ps vx | head -1 ; ps vx | grep -v PID | sort -rn +3 | head -10
Displaying the processes in order of real memory use
--> ps vx | head -1 ; ps vx | grep -v PID | sort -rn +6 | head -10
Displaying the processes in order of I/O
--> ps vx | head -1 ; ps vx | grep -v PID | sort -rn +4 | head -10
Displaying WLM classes ( work load manager)
--> ps -a -o pid,user,class,pcpu,pmem,args
Determining the PID of wait processes
--> ps vg | head -1 ; ps vg | grep -w wait
Wait processes bound to CPUs
--> ps -mo THREAD -p 516,774,1032,1290
Determining which processes are using the most real memory
--> svmon -Pu -t 3|grep -p Pid|grep '^.*[0-9]'
Determining which processes use the most paging space
--> svmon -gP -t 3 |grep -p Pid|grep '^.*[0-9]'
Displaying memory used by a WLM class
--> svmon -W shared
Finding out most utilized segments
--> svmon -S
Finding out what files a process or command is using
--> svmon -pP
Finding out which segments use paging space
--> svmon -gS
--> svmon -D sid (We can use the -D option to display frame information about each segment.)
Thursday, October 11, 2012
All Undo related queries
To check retention guarantee for undo tablespace:
---------------------------------------------------
select tablespace_name,status,contents,logging,retention from dba_tablespaces where tablespace_name like '%UNDO%';
To show ACTIVE/EXPIRED/UNEXPIRED Extents of Undo Tablespace:
-------------------------------------------------------------
select tablespace_name,
status,
count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",
sum(blocks)*8/(1024*1024) total_space
from dba_undo_extents
group by tablespace_name, status;
Extent Count and Total Blocks:
-------------------------------
set linesize 152
col tablespace_name for a20
col status for a10
select tablespace_name,status,count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB
from dba_undo_extents
where tablespace_name in ('&undotbsp')
group by tablespace_name,status;
To show UndoRetention Value:
-----------------------------
Show parameter undo_retention;
Undo retention in hours:
-------------------------
col "Retention" for a30
col name for a30
col value for a50
select name "Retention",value/60/60 "Hours" from v$parameter where name like '%undo_retention%';
To check space related statistics of UndoTablespace from stats$undostat of 90 days:
-------------------------------------------------------------------------------------
select UNDOBLKS,BEGIN_TIME,MAXQUERYLEN,UNXPSTEALCNT,EXPSTEALCNT,NOSPACEERRCNT from stats$undostat
where BEGIN_TIME between sysdate-90 and sysdate and UNXPSTEALCNT > 0;
To check space related statistics of UndoTablespace from v$undostat:
----------------------------------------------------------------------
select
sum(ssolderrcnt) "Total ORA-1555s",
round(max(maxquerylen)/60/60) "Max Query HRS",
sum(unxpstealcnt) "UNExpired STEALS",
sum(expstealcnt) "Expired STEALS"
from v$undostat
order by begin_time;
Date wise occurrence of ORA-1555:
------------------------------------
select to_char(begin_time, 'mm/dd/yyyy hh24:mi') "Int. Start",
ssolderrcnt "ORA-1555s", maxquerylen "Max Query",
unxpstealcnt "UNExp SCnt",UNXPBLKRELCNT "UnEXPblks", expstealcnt "Exp SCnt",EXPBLKRELCNT "ExpBlks",
NOSPACEERRCNT nospace
from v$undostat where ssolderrcnt>0
order by begin_time;
Total number of ORA-1555s since instance startup:
-------------------------------------------------
select 'TOTAL # OF ORA-01555 SINCE INSTANCE STARTUP : '
to_char(startup_time,'DD-MON-YY HH24:MI:SS')
from v$instance;
To check for Active Transactions:
----------------------------------
set head on
select usn,extents,round(rssize/1048576)
rssize,hwmsize,xacts,waits,optsize/1048576 optsize,shrinks,wraps
from v$rollstat where xacts>0
order by rssize;
Undo Space Utilization by each Sessions:
------------------------------------------
set lines 200
col sid for 99999
col username for a10
col name for a15
select s.sid,s.serial#,username,s.machine,
t.used_ublk ,t.used_urec,rn.name,(t.used_ublk *8)/1024/1024 SizeGB
from v$transaction t,v$session s,v$rollstat rs, v$rollname rn
where t.addr=s.taddr and rs.usn=rn.usn and rs.usn=t.xidusn and rs.xacts>0;
List of long running queries since instance startup:
----------------------------------------------------
set head off
select 'LIST OF LONG RUNNING - QUERY SINCE INSTANCE STARTUP' from dual;
set head on
select *
from
(select to_char(begin_time, 'DD-MON-YY hh24:mi:ss') BEGIN_TIME ,
round((maxquerylen/3600),1) Hours
from v$undostat
order by maxquerylen desc)
where rownum < 11;
Undo Space used by all transactions:
--------------------------------------
set lines 200
col sid for 99999
col username for a10
col name for a15
select s.sid,s.serial#,username,s.machine,
t.used_ublk ,t.used_urec,rn.name,(t.used_ublk *8)/1024/1024 SizeGB
from v$transaction t,v$session s,v$rollstat rs, v$rollname rn
where t.addr=s.taddr and rs.usn=rn.usn and rs.usn=t.xidusn and rs.xacts>0;
List of All active Transactions:
---------------------------------
select sid,username,
t.used_ublk ,t.used_urec
from v$transaction t,v$session s
where t.addr=s.taddr;
To list all Datafile of UndoTablespace:
-----------------------------------------
select tablespace_name,file_name,file_id,autoextensible,bytes/1048576
Mbytes, maxbytes/1048576 maxMbytes
from dba_data_files
where tablespace_name like '%UNDO%'
or tablespace_name like '%RBS%'
order by tablespace_name,file_name;
select tablespace_name,file_name,file_id,autoextensible,bytes/1048576
Mbytes, maxbytes/1048576 maxMbytes
from dba_data_files
where tablespace_name like '%UNDOTBS2%'
order by tablespace_name,file_name;
col file_name for a40
set pagesize 100
select tablespace_name,file_name,file_id,autoextensible,bytes/1048576
Mbytes, maxbytes/1048576 maxMbytes
from dba_data_files
where tablespace_name like '%APPS_UNDOTS1%'
order by tablespace_name,file_name;
select file_name,tablespace_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible
from dba_data_files where file_name like '%undo%' order by file_name;
To check when a table is last analysed:
----------------------------------------
select OWNER,TABLE_NAME,TABLESPACE_NAME,STATUS,LAST_ANALYZED,PARTITIONED,DEPENDENCIES,DROPPED from dba_tables where TABLE_NAME like 'MLC_PICK_LOCKS_DETAIL';
select OWNER,TABLE_NAME,TABLESPACE_NAME,LAST_ANALYZED,PARTITIONED,DEPENDENCIES,DROPPED from dba_tables where TABLE_NAME like 'APPS.XLA_AEL_GL_V';
To list all Undo datafiles with status and size
------------------------------------------------
show parameter undo
show parameter db_block_size
col tablespace_name form a20
col file_name form a60
set lines 120
select tablespace_name, file_name, status, bytes/1024/1024 from dba_data_files
where tablespace_name=(select tablespace_name from dba_tablespaces where contents='UNDO');
Total undo space:
---------------------
select sum(bytes)/1024/1024/1024 GB from dba_data_files where tablespace_name='&Undo_TB_Name';
Undo Tablespace:
--------------------
select tablespace_name from dba_tablespaces where tablespace_name like '%UNDO%';
To find MaxQueryLength from stats$undostat:
--------------------------------------------
Select Max(MAXQUERYLEN) from stats$undostat;
>select max(maxquerylen) from v$undostat;
>select begin_date,u.maxquerylen from
(select to_char(begin_time,'DD-MON-YYYY:HH24-MI-SS') begin_date,maxquerylen
from v$undostat order by maxquerylen desc) u where rownum<11>
>select begin_date,u.maxquerylen from
(select maxquerylen,to_char(begin_time,'DD-MON-YYYY:HH24-MI-SS') begin_date from
v$undostat order by maxquerylen DESC) u where rownum<26 asc="asc" begin_date="begin_date" by="by" desc="desc" maxquerylen="maxquerylen" order="order">
>select begin_date,u.maxquerylen from
(select maxquerylen,to_char(begin_time,'DD-MON-YYYY:HH24-MI-SS') begin_date from
v$undostat order by maxquerylen DESC) u where rownum<26 by="by" desc="desc" maxquerylen="maxquerylen" order="order">
>select sum(u.maxquerylen)/25 AvgUndoRetTime
from (select maxquerylen from v$undostat order by maxquerylen desc) u where rownum<26>
>select sum(u.maxquerylen)
from (select maxquerylen from v$undostat order by maxquerylen desc) u where rownum<26>
DBA_UNDO_EXTENTS:
-------------------
set linesize 152
col tablespace_name for a20
col status for a10
select tablespace_name,status,count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB
from dba_undo_extents
group by tablespace_name, status
order by tablespace_name;
Mapping Undo Segments to usernames:
------------------------------------
select s.sid,s.serial#,username,s.machine,
t.used_ublk ,t.used_urec,(rs.rssize)/1024/1024 MB,rn.name
from v$transaction t,v$session s,v$rollstat rs, v$rollname rn
where t.addr=s.taddr and rs.usn=rn.usn and rs.usn=t.xidusn and rs.xacts>0;
Total Undo Statistics:
----------------------------
alter session set nls_date_format='dd-mon-yy hh24:mi';
set lines 120
set pages 2000
select BEGIN_TIME, END_TIME, UNDOBLKS, TXNCOUNT , MAXQUERYLEN , UNXPSTEALCNT ,
EXPSTEALCNT , SSOLDERRCNT , NOSPACEERRCNT
from v$undostat;
Total Undo Statistics since specified year:
---------------------------------------------
select 'TOTAL STATISTICS SINCE Jan 01, 2005 - STATSPACK' from dual;
set head on
set lines 152
column undotsn format 999 heading 'Undo
TS#';
column undob format 9,999,999,999 heading 'Undo
Blocks';
column txcnt format 9,999,999,999,999 heading 'Num
Trans';
column maxq format 999,999 heading 'Max Qry
Len (s)';
column maxc format 9,999,999 heading 'Max Tx
Concurcy';
column snol format 9,999 heading 'Snapshot
Too Old';
column nosp format 9,999 heading 'Out of
Space';
column blkst format a13 heading 'uS/uR/uU/
eS/eR/eU' wrap;
column unst format 9,999 heading 'Unexp
Stolen' newline;
column unrl format 9,999 heading 'Unexp
Relesd';
column unru format 9,999 heading 'Unexp
Reused';
column exst format 9,999 heading 'Exp
Stolen';
column exrl format 9,999 heading 'Exp
Releas';
column exru format 9,999 heading 'Exp
Reused';
select undotsn
, sum(undoblks) undob
, sum(txncount) txcnt
, max(maxquerylen) maxq
, max(maxconcurrency) maxc
, sum(ssolderrcnt) snol
, sum(nospaceerrcnt) nosp
, sum(unxpstealcnt)
'/'
sum(unxpblkrelcnt)
'/'
sum(unxpblkreucnt)
'/'
sum(expstealcnt)
'/'
sum(expblkrelcnt)
'/'
sum(expblkreucnt) blkst
from stats$undostat
where dbid in (select dbid from v$database)
and instance_number in (select instance_number from v$instance)
and end_time > to_date('01012005 00:00:00', 'DDMMYYYY HH24:MI:SS')
and begin_time < (select sysdate from dual)
group by undotsn;
>SELECT (SUM(undoblks))/ SUM ((end_time - begin_time) * 86400) FROM v$undostat;
Checking for Recent ORA-1555:
-----------------------------
show parameter background
cd
ls -ltr
tail
view
shift + G ---> to get the tail end...
?ORA-1555 ---- to search of the error...
shift + N ---- to step for next reported error...
Rollback segment queries:
--------------------------
Wraps
-----
select name,extents,rssize/1048576 rssizeMB ,xacts,writes/1024/1024,optsize/1048576 optsize,
shrinks,wraps,extends,aveshrink/1048576,waits,rs.status,rs.curext
from v$rollstat rs, v$rollname rn where rn.usn=rs.usn
order by wraps;
Wraps column as high values for the all segments size of rollback segments are small for
long running queries and transactions by increasing the rollback segments size we can avoid the ORA-01555 errors
Undo Contention:
-----------------
Rollback Segment Contention
----------------------------
prompt If any ratio is > .01 then more rollback segments are needed
column "total_waits" format 999,999,999
column "total_timeouts" format 999,999,999
column "Ratio" format 99.99999
select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
Sample Output:
REM NAME WAITS GETS Ratio
REM ------------------------------ ---------- ---------- ---------
REM SYSTEM 0 269 .00000
REM R01 0 304 .00000
REM R02 0 2820 .00000
REM R03 0 629 .00000
REM R04 1 511 .00196
REM R05 0 513 .00000
REM R06 1 503 .00199
REM R07 0 301 .00000
REM R08 0 299 .00000
Looking at the tcl script to see what sql gets performed to determine rollback
segment contention:
---------------------
select count from v$waitstat where class = 'system undo header';
select count from v$waitstat where class = 'system undo block';
select count from v$waitstat where class = 'undo header';
select count from v$waitstat where class = 'undo block';
Rollback Segment Information:
-----------------------------------
set lines 152
col segment_type for a10
col tablespace_name for a20
select owner,tablespace_name,extents,next_extent/1024 next_extnentKB,max_extents,pct_increase
from dba_segments
where segment_type='ROLLBACK';
> set lines 152
col name for a15
select name,extents,rssize/1048576 rssizeMB ,xacts,writes/1024/1024,optsize/1048576 optsize,
shrinks,wraps,aveshrink/1048576,waits,rs.status,rs.curext
from v$rollstat rs, v$rollname rn where rn.usn=rs.usn and rs.xacts>0;
>select name,extents,rssize/1048576 rssizeMB ,xacts,writes/1024/1024,optsize/1048576 optsize,
shrinks,wraps,extends,aveshrink/1048576,waits,rs.status,rs.curext
from v$rollstat rs, v$rollname rn where rn.usn=rs.usn
order by wraps;
>select name,extents,optsize/1048576 optsize,
shrinks,wraps,aveshrink/1048576,aveactive,rs.status,rs.curext
from v$rollstat rs, v$rollname rn where rn.usn=rs.usn;
>select sum(rssize)/1024/1024/1024 sizeGB from v$rollstat;
>select sum(xacts) from v$rollstat;
select sum(rssize)/1024/1024/1024 sizeGB from v$rollstat where xacts=0;
select sum(rssize)/1024/1024/1024 sizeGB from v$rollstat where xacts>0;
select sum(xacts) from v$rollstat;
>select tablespace_name,segment_name,initial_extent,next_extent,min_extents,max_extents,status
from dba_rollback_segs
where status='ONLINE';
>select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible
from dba_data_files where file_name like '%&filename%';
>select sum(bytes)/1024/1024 from dba_free_space where tablespace_name='&tbs';
Optimize Oracle UNDO Parameters:
-----------------------------------------
Actual Undo Size
SELECT SUM(a.bytes/1024/1024/1024) "UNDO_SIZE"
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#;
UNDO_SIZE
----------
209715200
Undo Blocks per Second:
-------------------------
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
"UNDO_BLOCK_PER_SEC"
FROM v$undostat;
UNDO_BLOCK_PER_SEC
------------------
3.12166667
Undo Segment Summary for DB:
--------------------------------
Undo Segment Summary for DB: S901 Instance: S901 Snaps: 2 -3
-> Undo segment block stats:
-> uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
-> eS - expired Stolen, eR - expired Released, eU - expired reUsed
Undo Undo Num Max Qry Max Tx Snapshot Out of uS/uR/uU/
TS# Blocks Trans Len (s) Concurcy Too Old Space eS/eR/eU
---- -------------- ---------- -------- ---------- -------- ------ -------------
1 20,284 1,964 8 12 0 0 0/0/0/0/0/0
Undo Segment Stats for DB:
---------------------------
Undo Segment Stats for DB: S901 Instance: S901 Snaps: 2 -3
-> ordered by Time desc
Undo Num Max Qry Max Tx Snap Out of uS/uR/uU/
End Time Blocks Trans Len (s) Concy Too Old Space eS/eR/eU
------------ ------------ -------- ------- -------- ------- ------ -------------
12-Mar 16:11 18,723 1,756 8 12 0 0 0/0/0/0/0/0
12-Mar 16:01 1,561 208 3 12 0 0 0/0/0/0/0/0
Undo Segment Space Required = (undo_retention_time * undo_blocks_per_seconds)
As an example, an UNDO_RETENTION of 5 minutes (default) with 50 undo blocks/second (8k blocksize)
will generate:
-------------------
Undo Segment Space Required = (300 seconds * 50 blocks/ seconds * 8K/block) = 120 M
select tablespace_name,file_name,file_id,autoextensible,bytes/1048576
Mbytes, maxbytes/1048576 maxMbytes
from dba_data_files
where tablespace_name like '%UNDO%'
or tablespace_name like '%RBS%'
or tablespace_name like '%ROLLBACK%'
order by tablespace_name,file_name;
select a.owner,a.tablespace_name,b.status, a.extents,a.next_extent/1024 next_extnentKB,a.max_extents,a.pct_increase from dba_segments a,dba_tablespaces b
where segment_type='ROLLBACK' and a.tablespace_name=b.tablespace_name;
select tablespace_name,status from dba_tablespaces where tablespace_name='ROLLBACK';
Actual Undo Size:
-----------------
SELECT SUM(a.bytes/1024/1024) "UNDO_SIZE"
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#;
UNDO_SIZE
----------
209715200
Undo Blocks per Second:
------------------------
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
"UNDO_BLOCK_PER_SEC"
FROM v$undostat;
UNDO_BLOCK_PER_SEC
------------------
3.12166667
DB Block Size
------------------
SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
FROM v$parameter
WHERE name = 'db_block_size';
DB_BLOCK_SIZE [Byte]
--------------------
4096
Optimal Undo Retention:
------------------------
209'715'200 / (3.12166667 * 4'096) = 16'401 [Sec]
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$Using Inline Views, you can do all in one query$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/
ACTUAL UNDO SIZE [MByte]
------------------------
200
UNDO RETENTION [Sec]
--------------------
10800
OPTIMAL UNDO RETENTION [Sec]
----------------------------
16401
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
Calculate Needed UNDO Size for given Database Activity
If you are not limited by disk space, then it would be better to choose the UNDO_RETENTION time
that is best for you (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity:
Again, all in one query:
---------------------------------------
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/
ACTUAL UNDO SIZE [MByte]
------------------------
200
UNDO RETENTION [Sec]
--------------------
10800
NEEDED UNDO SIZE [MByte]
------------------------
131.695313
----------------------------------------------------------------------------------------------------------------------------
Checking when tables are last analyzed
select
OWNER,TABLE_NAME,TABLESPACE_NAME,STATUS,LAST_ANALYZED,PARTITIONED,DEPENDENCIES,DROPPED from
dba_tables where TABLE_NAME like 'MLC_END_USER_REGISTRATION';
DECLARE
v_table_space_name VARCHAR2(30);
v_table_space_size_in_MB NUMBER(9);
v_auto_extend BOOLEAN;
v_undo_retention NUMBER(9);
v_retention_guarantee BOOLEAN;
v_undo_info_return BOOLEAN;
BEGIN
v_undo_info_return := dbms_undo_adv.undo_info(v_table_space_name, v_table_space_size_in_MB, v_auto_extend, v_undo_retention, v_retention_guarantee);
dbms_output.put_line(’UNDO Tablespace Name: ‘
v_table_space_name);
dbms_output.put_line(’UNDO Tablespace size (MB) : ‘
TO_CHAR(v_table_space_size_in_MB));
dbms_output.put_line(’If UNDO tablespace is auto extensible above size indicates max possible size of the undo tablespace’);
dbms_output.put_line(’UNDO tablespace auto extensiable is : ‘
CASE WHEN v_auto_extend THEN ‘ON’ ELSE ‘OFF’ END);
dbms_output.put_line(’Undo Retention (Sec): ‘
v_undo_retention);
dbms_output.put_line(’Retention : ‘
CASE WHEN v_retention_guarantee THEN ‘Guaranteed ‘ ELSE ‘NOT Guaranteed’ END);
END;
undo_autotune
This function is used to find auto tuning of undo retention is ENABLED or NOT.
Set serverout on
declare
v_autotune_return Boolean := null;
v_autotune_enabled boolean := null;
begin
v_autotune_return:= dbms_undo_adv.undo_autotune(v_autotune_enabled);
dbms_output.put_line(CASE WHEN v_autotune_return THEN 'Information is available :' ELSE 'Information is NOT available :' END
CASE WHEN v_autotune_enabled THEN 'Auto tuning of undo retention is ENABLED' ELSE 'Auto tuning of undo retention is NOT enabled' END);
end;
/
select dbms_undo_adv.longest_query from dual
select dbms_undo_adv.required_retention from dual
select dbms_undo_adv.best_possible_retention from dual
select dbms_undo_adv.required_undo_size(1800) from dual
DECLARE
v_undo_health_return number;
v_retention number;
v_utbsize number;
v_problem VARCHAR2(1024);
v_recommendation VARCHAR2(1024);
v_rationale VARCHAR2(1024);
BEGIN
v_undo_health_return := dbms_undo_adv.undo_health(problem => v_problem,
recommendation => v_recommendation,
rationale => v_rationale,
retention => v_retention,
utbsize => v_utbsize);
dbms_output.put_line(’Problem : ‘
v_problem);
dbms_output.put_line(’Recommendation= : ‘
v_recommendation);
dbms_output.put_line(’Rationale : ‘
v_retention);
dbms_output.put_line(’Retention : ‘
v_retention);
dbms_output.put_line(’UNDO tablespace size : ‘
v_utbsize);
END;
undo_advisor:
-------------
It uses oracle’s advisor framework to find out problem and provide recommendations.
DECLARE
v_undo_advisor_return VARCHAR2(100);
BEGIN
v_undo_advisor_return := dbms_undo_adv.undo_advisor(instance => 1);
dbms_output.put_line(v_undo_advisor_return);
END;
----------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
select tablespace_name,status,contents,logging,retention from dba_tablespaces where tablespace_name like '%UNDO%';
To show ACTIVE/EXPIRED/UNEXPIRED Extents of Undo Tablespace:
-------------------------------------------------------------
select tablespace_name,
status,
count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",
sum(blocks)*8/(1024*1024) total_space
from dba_undo_extents
group by tablespace_name, status;
Extent Count and Total Blocks:
-------------------------------
set linesize 152
col tablespace_name for a20
col status for a10
select tablespace_name,status,count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB
from dba_undo_extents
where tablespace_name in ('&undotbsp')
group by tablespace_name,status;
To show UndoRetention Value:
-----------------------------
Show parameter undo_retention;
Undo retention in hours:
-------------------------
col "Retention" for a30
col name for a30
col value for a50
select name "Retention",value/60/60 "Hours" from v$parameter where name like '%undo_retention%';
To check space related statistics of UndoTablespace from stats$undostat of 90 days:
-------------------------------------------------------------------------------------
select UNDOBLKS,BEGIN_TIME,MAXQUERYLEN,UNXPSTEALCNT,EXPSTEALCNT,NOSPACEERRCNT from stats$undostat
where BEGIN_TIME between sysdate-90 and sysdate and UNXPSTEALCNT > 0;
To check space related statistics of UndoTablespace from v$undostat:
----------------------------------------------------------------------
select
sum(ssolderrcnt) "Total ORA-1555s",
round(max(maxquerylen)/60/60) "Max Query HRS",
sum(unxpstealcnt) "UNExpired STEALS",
sum(expstealcnt) "Expired STEALS"
from v$undostat
order by begin_time;
Date wise occurrence of ORA-1555:
------------------------------------
select to_char(begin_time, 'mm/dd/yyyy hh24:mi') "Int. Start",
ssolderrcnt "ORA-1555s", maxquerylen "Max Query",
unxpstealcnt "UNExp SCnt",UNXPBLKRELCNT "UnEXPblks", expstealcnt "Exp SCnt",EXPBLKRELCNT "ExpBlks",
NOSPACEERRCNT nospace
from v$undostat where ssolderrcnt>0
order by begin_time;
Total number of ORA-1555s since instance startup:
-------------------------------------------------
select 'TOTAL # OF ORA-01555 SINCE INSTANCE STARTUP : '
to_char(startup_time,'DD-MON-YY HH24:MI:SS')
from v$instance;
To check for Active Transactions:
----------------------------------
set head on
select usn,extents,round(rssize/1048576)
rssize,hwmsize,xacts,waits,optsize/1048576 optsize,shrinks,wraps
from v$rollstat where xacts>0
order by rssize;
Undo Space Utilization by each Sessions:
------------------------------------------
set lines 200
col sid for 99999
col username for a10
col name for a15
select s.sid,s.serial#,username,s.machine,
t.used_ublk ,t.used_urec,rn.name,(t.used_ublk *8)/1024/1024 SizeGB
from v$transaction t,v$session s,v$rollstat rs, v$rollname rn
where t.addr=s.taddr and rs.usn=rn.usn and rs.usn=t.xidusn and rs.xacts>0;
List of long running queries since instance startup:
----------------------------------------------------
set head off
select 'LIST OF LONG RUNNING - QUERY SINCE INSTANCE STARTUP' from dual;
set head on
select *
from
(select to_char(begin_time, 'DD-MON-YY hh24:mi:ss') BEGIN_TIME ,
round((maxquerylen/3600),1) Hours
from v$undostat
order by maxquerylen desc)
where rownum < 11;
Undo Space used by all transactions:
--------------------------------------
set lines 200
col sid for 99999
col username for a10
col name for a15
select s.sid,s.serial#,username,s.machine,
t.used_ublk ,t.used_urec,rn.name,(t.used_ublk *8)/1024/1024 SizeGB
from v$transaction t,v$session s,v$rollstat rs, v$rollname rn
where t.addr=s.taddr and rs.usn=rn.usn and rs.usn=t.xidusn and rs.xacts>0;
List of All active Transactions:
---------------------------------
select sid,username,
t.used_ublk ,t.used_urec
from v$transaction t,v$session s
where t.addr=s.taddr;
To list all Datafile of UndoTablespace:
-----------------------------------------
select tablespace_name,file_name,file_id,autoextensible,bytes/1048576
Mbytes, maxbytes/1048576 maxMbytes
from dba_data_files
where tablespace_name like '%UNDO%'
or tablespace_name like '%RBS%'
order by tablespace_name,file_name;
select tablespace_name,file_name,file_id,autoextensible,bytes/1048576
Mbytes, maxbytes/1048576 maxMbytes
from dba_data_files
where tablespace_name like '%UNDOTBS2%'
order by tablespace_name,file_name;
col file_name for a40
set pagesize 100
select tablespace_name,file_name,file_id,autoextensible,bytes/1048576
Mbytes, maxbytes/1048576 maxMbytes
from dba_data_files
where tablespace_name like '%APPS_UNDOTS1%'
order by tablespace_name,file_name;
select file_name,tablespace_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible
from dba_data_files where file_name like '%undo%' order by file_name;
To check when a table is last analysed:
----------------------------------------
select OWNER,TABLE_NAME,TABLESPACE_NAME,STATUS,LAST_ANALYZED,PARTITIONED,DEPENDENCIES,DROPPED from dba_tables where TABLE_NAME like 'MLC_PICK_LOCKS_DETAIL';
select OWNER,TABLE_NAME,TABLESPACE_NAME,LAST_ANALYZED,PARTITIONED,DEPENDENCIES,DROPPED from dba_tables where TABLE_NAME like 'APPS.XLA_AEL_GL_V';
To list all Undo datafiles with status and size
------------------------------------------------
show parameter undo
show parameter db_block_size
col tablespace_name form a20
col file_name form a60
set lines 120
select tablespace_name, file_name, status, bytes/1024/1024 from dba_data_files
where tablespace_name=(select tablespace_name from dba_tablespaces where contents='UNDO');
Total undo space:
---------------------
select sum(bytes)/1024/1024/1024 GB from dba_data_files where tablespace_name='&Undo_TB_Name';
Undo Tablespace:
--------------------
select tablespace_name from dba_tablespaces where tablespace_name like '%UNDO%';
To find MaxQueryLength from stats$undostat:
--------------------------------------------
Select Max(MAXQUERYLEN) from stats$undostat;
>select max(maxquerylen) from v$undostat;
>select begin_date,u.maxquerylen from
(select to_char(begin_time,'DD-MON-YYYY:HH24-MI-SS') begin_date,maxquerylen
from v$undostat order by maxquerylen desc) u where rownum<11>
>select begin_date,u.maxquerylen from
(select maxquerylen,to_char(begin_time,'DD-MON-YYYY:HH24-MI-SS') begin_date from
v$undostat order by maxquerylen DESC) u where rownum<26 asc="asc" begin_date="begin_date" by="by" desc="desc" maxquerylen="maxquerylen" order="order">
>select begin_date,u.maxquerylen from
(select maxquerylen,to_char(begin_time,'DD-MON-YYYY:HH24-MI-SS') begin_date from
v$undostat order by maxquerylen DESC) u where rownum<26 by="by" desc="desc" maxquerylen="maxquerylen" order="order">
>select sum(u.maxquerylen)/25 AvgUndoRetTime
from (select maxquerylen from v$undostat order by maxquerylen desc) u where rownum<26>
>select sum(u.maxquerylen)
from (select maxquerylen from v$undostat order by maxquerylen desc) u where rownum<26>
DBA_UNDO_EXTENTS:
-------------------
set linesize 152
col tablespace_name for a20
col status for a10
select tablespace_name,status,count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB
from dba_undo_extents
group by tablespace_name, status
order by tablespace_name;
Mapping Undo Segments to usernames:
------------------------------------
select s.sid,s.serial#,username,s.machine,
t.used_ublk ,t.used_urec,(rs.rssize)/1024/1024 MB,rn.name
from v$transaction t,v$session s,v$rollstat rs, v$rollname rn
where t.addr=s.taddr and rs.usn=rn.usn and rs.usn=t.xidusn and rs.xacts>0;
Total Undo Statistics:
----------------------------
alter session set nls_date_format='dd-mon-yy hh24:mi';
set lines 120
set pages 2000
select BEGIN_TIME, END_TIME, UNDOBLKS, TXNCOUNT , MAXQUERYLEN , UNXPSTEALCNT ,
EXPSTEALCNT , SSOLDERRCNT , NOSPACEERRCNT
from v$undostat;
Total Undo Statistics since specified year:
---------------------------------------------
select 'TOTAL STATISTICS SINCE Jan 01, 2005 - STATSPACK' from dual;
set head on
set lines 152
column undotsn format 999 heading 'Undo
TS#';
column undob format 9,999,999,999 heading 'Undo
Blocks';
column txcnt format 9,999,999,999,999 heading 'Num
Trans';
column maxq format 999,999 heading 'Max Qry
Len (s)';
column maxc format 9,999,999 heading 'Max Tx
Concurcy';
column snol format 9,999 heading 'Snapshot
Too Old';
column nosp format 9,999 heading 'Out of
Space';
column blkst format a13 heading 'uS/uR/uU/
eS/eR/eU' wrap;
column unst format 9,999 heading 'Unexp
Stolen' newline;
column unrl format 9,999 heading 'Unexp
Relesd';
column unru format 9,999 heading 'Unexp
Reused';
column exst format 9,999 heading 'Exp
Stolen';
column exrl format 9,999 heading 'Exp
Releas';
column exru format 9,999 heading 'Exp
Reused';
select undotsn
, sum(undoblks) undob
, sum(txncount) txcnt
, max(maxquerylen) maxq
, max(maxconcurrency) maxc
, sum(ssolderrcnt) snol
, sum(nospaceerrcnt) nosp
, sum(unxpstealcnt)
'/'
sum(unxpblkrelcnt)
'/'
sum(unxpblkreucnt)
'/'
sum(expstealcnt)
'/'
sum(expblkrelcnt)
'/'
sum(expblkreucnt) blkst
from stats$undostat
where dbid in (select dbid from v$database)
and instance_number in (select instance_number from v$instance)
and end_time > to_date('01012005 00:00:00', 'DDMMYYYY HH24:MI:SS')
and begin_time < (select sysdate from dual)
group by undotsn;
>SELECT (SUM(undoblks))/ SUM ((end_time - begin_time) * 86400) FROM v$undostat;
Checking for Recent ORA-1555:
-----------------------------
show parameter background
cd
ls -ltr
tail
view
shift + G ---> to get the tail end...
?ORA-1555 ---- to search of the error...
shift + N ---- to step for next reported error...
Rollback segment queries:
--------------------------
Wraps
-----
select name,extents,rssize/1048576 rssizeMB ,xacts,writes/1024/1024,optsize/1048576 optsize,
shrinks,wraps,extends,aveshrink/1048576,waits,rs.status,rs.curext
from v$rollstat rs, v$rollname rn where rn.usn=rs.usn
order by wraps;
Wraps column as high values for the all segments size of rollback segments are small for
long running queries and transactions by increasing the rollback segments size we can avoid the ORA-01555 errors
Undo Contention:
-----------------
Rollback Segment Contention
----------------------------
prompt If any ratio is > .01 then more rollback segments are needed
column "total_waits" format 999,999,999
column "total_timeouts" format 999,999,999
column "Ratio" format 99.99999
select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
Sample Output:
REM NAME WAITS GETS Ratio
REM ------------------------------ ---------- ---------- ---------
REM SYSTEM 0 269 .00000
REM R01 0 304 .00000
REM R02 0 2820 .00000
REM R03 0 629 .00000
REM R04 1 511 .00196
REM R05 0 513 .00000
REM R06 1 503 .00199
REM R07 0 301 .00000
REM R08 0 299 .00000
Looking at the tcl script to see what sql gets performed to determine rollback
segment contention:
---------------------
select count from v$waitstat where class = 'system undo header';
select count from v$waitstat where class = 'system undo block';
select count from v$waitstat where class = 'undo header';
select count from v$waitstat where class = 'undo block';
Rollback Segment Information:
-----------------------------------
set lines 152
col segment_type for a10
col tablespace_name for a20
select owner,tablespace_name,extents,next_extent/1024 next_extnentKB,max_extents,pct_increase
from dba_segments
where segment_type='ROLLBACK';
> set lines 152
col name for a15
select name,extents,rssize/1048576 rssizeMB ,xacts,writes/1024/1024,optsize/1048576 optsize,
shrinks,wraps,aveshrink/1048576,waits,rs.status,rs.curext
from v$rollstat rs, v$rollname rn where rn.usn=rs.usn and rs.xacts>0;
>select name,extents,rssize/1048576 rssizeMB ,xacts,writes/1024/1024,optsize/1048576 optsize,
shrinks,wraps,extends,aveshrink/1048576,waits,rs.status,rs.curext
from v$rollstat rs, v$rollname rn where rn.usn=rs.usn
order by wraps;
>select name,extents,optsize/1048576 optsize,
shrinks,wraps,aveshrink/1048576,aveactive,rs.status,rs.curext
from v$rollstat rs, v$rollname rn where rn.usn=rs.usn;
>select sum(rssize)/1024/1024/1024 sizeGB from v$rollstat;
>select sum(xacts) from v$rollstat;
select sum(rssize)/1024/1024/1024 sizeGB from v$rollstat where xacts=0;
select sum(rssize)/1024/1024/1024 sizeGB from v$rollstat where xacts>0;
select sum(xacts) from v$rollstat;
>select tablespace_name,segment_name,initial_extent,next_extent,min_extents,max_extents,status
from dba_rollback_segs
where status='ONLINE';
>select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible
from dba_data_files where file_name like '%&filename%';
>select sum(bytes)/1024/1024 from dba_free_space where tablespace_name='&tbs';
Optimize Oracle UNDO Parameters:
-----------------------------------------
Actual Undo Size
SELECT SUM(a.bytes/1024/1024/1024) "UNDO_SIZE"
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#;
UNDO_SIZE
----------
209715200
Undo Blocks per Second:
-------------------------
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
"UNDO_BLOCK_PER_SEC"
FROM v$undostat;
UNDO_BLOCK_PER_SEC
------------------
3.12166667
Undo Segment Summary for DB:
--------------------------------
Undo Segment Summary for DB: S901 Instance: S901 Snaps: 2 -3
-> Undo segment block stats:
-> uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
-> eS - expired Stolen, eR - expired Released, eU - expired reUsed
Undo Undo Num Max Qry Max Tx Snapshot Out of uS/uR/uU/
TS# Blocks Trans Len (s) Concurcy Too Old Space eS/eR/eU
---- -------------- ---------- -------- ---------- -------- ------ -------------
1 20,284 1,964 8 12 0 0 0/0/0/0/0/0
Undo Segment Stats for DB:
---------------------------
Undo Segment Stats for DB: S901 Instance: S901 Snaps: 2 -3
-> ordered by Time desc
Undo Num Max Qry Max Tx Snap Out of uS/uR/uU/
End Time Blocks Trans Len (s) Concy Too Old Space eS/eR/eU
------------ ------------ -------- ------- -------- ------- ------ -------------
12-Mar 16:11 18,723 1,756 8 12 0 0 0/0/0/0/0/0
12-Mar 16:01 1,561 208 3 12 0 0 0/0/0/0/0/0
Undo Segment Space Required = (undo_retention_time * undo_blocks_per_seconds)
As an example, an UNDO_RETENTION of 5 minutes (default) with 50 undo blocks/second (8k blocksize)
will generate:
-------------------
Undo Segment Space Required = (300 seconds * 50 blocks/ seconds * 8K/block) = 120 M
select tablespace_name,file_name,file_id,autoextensible,bytes/1048576
Mbytes, maxbytes/1048576 maxMbytes
from dba_data_files
where tablespace_name like '%UNDO%'
or tablespace_name like '%RBS%'
or tablespace_name like '%ROLLBACK%'
order by tablespace_name,file_name;
select a.owner,a.tablespace_name,b.status, a.extents,a.next_extent/1024 next_extnentKB,a.max_extents,a.pct_increase from dba_segments a,dba_tablespaces b
where segment_type='ROLLBACK' and a.tablespace_name=b.tablespace_name;
select tablespace_name,status from dba_tablespaces where tablespace_name='ROLLBACK';
Actual Undo Size:
-----------------
SELECT SUM(a.bytes/1024/1024) "UNDO_SIZE"
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#;
UNDO_SIZE
----------
209715200
Undo Blocks per Second:
------------------------
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
"UNDO_BLOCK_PER_SEC"
FROM v$undostat;
UNDO_BLOCK_PER_SEC
------------------
3.12166667
DB Block Size
------------------
SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
FROM v$parameter
WHERE name = 'db_block_size';
DB_BLOCK_SIZE [Byte]
--------------------
4096
Optimal Undo Retention:
------------------------
209'715'200 / (3.12166667 * 4'096) = 16'401 [Sec]
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$Using Inline Views, you can do all in one query$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/
ACTUAL UNDO SIZE [MByte]
------------------------
200
UNDO RETENTION [Sec]
--------------------
10800
OPTIMAL UNDO RETENTION [Sec]
----------------------------
16401
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
Calculate Needed UNDO Size for given Database Activity
If you are not limited by disk space, then it would be better to choose the UNDO_RETENTION time
that is best for you (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity:
Again, all in one query:
---------------------------------------
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/
ACTUAL UNDO SIZE [MByte]
------------------------
200
UNDO RETENTION [Sec]
--------------------
10800
NEEDED UNDO SIZE [MByte]
------------------------
131.695313
----------------------------------------------------------------------------------------------------------------------------
Checking when tables are last analyzed
select
OWNER,TABLE_NAME,TABLESPACE_NAME,STATUS,LAST_ANALYZED,PARTITIONED,DEPENDENCIES,DROPPED from
dba_tables where TABLE_NAME like 'MLC_END_USER_REGISTRATION';
DECLARE
v_table_space_name VARCHAR2(30);
v_table_space_size_in_MB NUMBER(9);
v_auto_extend BOOLEAN;
v_undo_retention NUMBER(9);
v_retention_guarantee BOOLEAN;
v_undo_info_return BOOLEAN;
BEGIN
v_undo_info_return := dbms_undo_adv.undo_info(v_table_space_name, v_table_space_size_in_MB, v_auto_extend, v_undo_retention, v_retention_guarantee);
dbms_output.put_line(’UNDO Tablespace Name: ‘
v_table_space_name);
dbms_output.put_line(’UNDO Tablespace size (MB) : ‘
TO_CHAR(v_table_space_size_in_MB));
dbms_output.put_line(’If UNDO tablespace is auto extensible above size indicates max possible size of the undo tablespace’);
dbms_output.put_line(’UNDO tablespace auto extensiable is : ‘
CASE WHEN v_auto_extend THEN ‘ON’ ELSE ‘OFF’ END);
dbms_output.put_line(’Undo Retention (Sec): ‘
v_undo_retention);
dbms_output.put_line(’Retention : ‘
CASE WHEN v_retention_guarantee THEN ‘Guaranteed ‘ ELSE ‘NOT Guaranteed’ END);
END;
undo_autotune
This function is used to find auto tuning of undo retention is ENABLED or NOT.
Set serverout on
declare
v_autotune_return Boolean := null;
v_autotune_enabled boolean := null;
begin
v_autotune_return:= dbms_undo_adv.undo_autotune(v_autotune_enabled);
dbms_output.put_line(CASE WHEN v_autotune_return THEN 'Information is available :' ELSE 'Information is NOT available :' END
CASE WHEN v_autotune_enabled THEN 'Auto tuning of undo retention is ENABLED' ELSE 'Auto tuning of undo retention is NOT enabled' END);
end;
/
select dbms_undo_adv.longest_query from dual
select dbms_undo_adv.required_retention from dual
select dbms_undo_adv.best_possible_retention from dual
select dbms_undo_adv.required_undo_size(1800) from dual
DECLARE
v_undo_health_return number;
v_retention number;
v_utbsize number;
v_problem VARCHAR2(1024);
v_recommendation VARCHAR2(1024);
v_rationale VARCHAR2(1024);
BEGIN
v_undo_health_return := dbms_undo_adv.undo_health(problem => v_problem,
recommendation => v_recommendation,
rationale => v_rationale,
retention => v_retention,
utbsize => v_utbsize);
dbms_output.put_line(’Problem : ‘
v_problem);
dbms_output.put_line(’Recommendation= : ‘
v_recommendation);
dbms_output.put_line(’Rationale : ‘
v_retention);
dbms_output.put_line(’Retention : ‘
v_retention);
dbms_output.put_line(’UNDO tablespace size : ‘
v_utbsize);
END;
undo_advisor:
-------------
It uses oracle’s advisor framework to find out problem and provide recommendations.
DECLARE
v_undo_advisor_return VARCHAR2(100);
BEGIN
v_undo_advisor_return := dbms_undo_adv.undo_advisor(instance => 1);
dbms_output.put_line(v_undo_advisor_return);
END;
----------------------------------------------------------------------------------------------------------------------------------------
Wednesday, October 3, 2012
Tuesday, September 25, 2012
Tuesday, September 18, 2012
Archive generation query
select db.name ,
db.log_mode , trunc(FIRST_TIME)
, count(1), to_char(sum(blocks*block_size)/1024/1024,'999,999.99') "Size_in_MB" from v$archived_log al,v$database db
where COMPLETION_time>=sysdate-7 and (al.name like '%/%' or al.name like '%\%') group by db.log_mode,db.name,trunc(FIRST_TIME) order by 1;
archivelog generation per hour
SET PAGESIZE 90
SET LINESIZE 150
set heading on
column "00:00" format 9999
column "01:00" format 9999
column "02:00" format 9999
column "03:00" format 9999
column "04:00" format 9999
column "05:00" format 9999
column "06:00" format 9999
column "07:00" format 9999
column "08:00" format 9999
column "09:00" format 9999
column "10:00" format 9999
column "11:00" format 9999
column "12:00" format 9999
column "13:00" format 9999
column "14:00" format 9999
column "15:00" format 9999
column "16:00" format 9999
column "17:00" format 9999
column "18:00" format 9999
column "19:00" format 9999
column "20:00" format 9999
column "21:00" format 9999
column "22:00" format 9999
column "23:00" format 9999
SELECT * FROM (
SELECT * FROM (
SELECT TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0), '99')) "00:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0), '99')) "01:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0), '99')) "02:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0), '99')) "03:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0), '99')) "04:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0), '99')) "05:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0), '99')) "06:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0), '99')) "07:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0), '99')) "08:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0), '99')) "09:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0), '99')) "10:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0), '99')) "11:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0), '99')) "12:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0), '99')) "13:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0), '99')) "14:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0), '99')) "15:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0), '99')) "16:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0), '99')) "17:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0), '99')) "18:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0), '99')) "19:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0), '99')) "20:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0), '99')) "21:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0), '99')) "22:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0), '99')) "23:00"
FROM V$LOG_HISTORY
WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM')
) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC
) WHERE ROWNUM <8 span="span">
SET LINESIZE 150
set heading on
column "00:00" format 9999
column "01:00" format 9999
column "02:00" format 9999
column "03:00" format 9999
column "04:00" format 9999
column "05:00" format 9999
column "06:00" format 9999
column "07:00" format 9999
column "08:00" format 9999
column "09:00" format 9999
column "10:00" format 9999
column "11:00" format 9999
column "12:00" format 9999
column "13:00" format 9999
column "14:00" format 9999
column "15:00" format 9999
column "16:00" format 9999
column "17:00" format 9999
column "18:00" format 9999
column "19:00" format 9999
column "20:00" format 9999
column "21:00" format 9999
column "22:00" format 9999
column "23:00" format 9999
SELECT * FROM (
SELECT * FROM (
SELECT TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0), '99')) "00:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0), '99')) "01:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0), '99')) "02:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0), '99')) "03:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0), '99')) "04:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0), '99')) "05:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0), '99')) "06:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0), '99')) "07:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0), '99')) "08:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0), '99')) "09:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0), '99')) "10:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0), '99')) "11:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0), '99')) "12:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0), '99')) "13:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0), '99')) "14:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0), '99')) "15:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0), '99')) "16:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0), '99')) "17:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0), '99')) "18:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0), '99')) "19:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0), '99')) "20:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0), '99')) "21:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0), '99')) "22:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0), '99')) "23:00"
FROM V$LOG_HISTORY
WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM')
) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC
) WHERE ROWNUM <8 span="span">
Monday, August 13, 2012
How to disable 10g Gather_stats_Job
exec dbms_scheduler.ENABLE(NAME => 'SYS.GATHER_STATS_JOB');
To check the status of the jobs in dba_scheduler_jobs:
SQL> select JOB_NAME,STATE,ENABLED from dba_scheduler_jobs;
JOB_NAME STATE ENABL
------------------------------ --------------- -----
AUTO_SPACE_ADVISOR_JOB SCHEDULED TRUE
GATHER_STATS_JOB DISABLED FALSE
FGR$AUTOPURGE_JOB DISABLED FALSE
PURGE_LOG SCHEDULED TRUE
MGMT_CONFIG_JOB SCHEDULED TRUE
MGMT_STATS_CONFIG_JOB SCHEDULED TRUE
6 rows selected.
To check the status of the jobs in dba_scheduler_jobs:
SQL> select JOB_NAME,STATE,ENABLED from dba_scheduler_jobs;
JOB_NAME STATE ENABL
------------------------------ --------------- -----
AUTO_SPACE_ADVISOR_JOB SCHEDULED TRUE
GATHER_STATS_JOB DISABLED FALSE
FGR$AUTOPURGE_JOB DISABLED FALSE
PURGE_LOG SCHEDULED TRUE
MGMT_CONFIG_JOB SCHEDULED TRUE
MGMT_STATS_CONFIG_JOB SCHEDULED TRUE
6 rows selected.
Tuesday, August 7, 2012
How to Export and Import Database Statistics
Step by Step Export of Database Statistics
1. Log onto the database
sqlplus '/ as sysdba'
2. Create a table to hold the exported statistics.
SQL> exec DBMS_STATS.CREATE_STAT_TABLE('','
1. Log onto the database
sqlplus '/ as sysdba'
2. Create a table to hold the exported statistics.
SQL> exec DBMS_STATS.CREATE_STAT_TABLE('
Friday, August 3, 2012
Example syntax for Secure Copy (scp)
What is Secure Copy?
scp allows files to be copied to, from, or between different hosts. It uses ssh for data transfer and provides the same authentication and same level of security as ssh.
Examples
Copy the file "foobar.txt" from a remote host to the local host
$ scp your_username@remotehost.edu:foobar.txt /some/local/directory
Copy the file "foobar.txt" from the local host to a remote host
$ scp foobar.txt your_username@remotehost.edu:/some/remote/directory
Copy the directory "foo" from the local host to a remote host's directory "bar"
$ scp -r foo your_username@remotehost.edu:/some/remote/directory/bar
Copy the file "foobar.txt" from remote host "rh1.edu" to remote host "rh2.edu"
$ scp your_username@rh1.edu:/some/remote/directory/foobar.txt \
your_username@rh2.edu:/some/remote/directory/
Copying the files "foo.txt" and "bar.txt" from the local host to your home directory on the remote host
$ scp foo.txt bar.txt your_username@remotehost.edu:~
Copy the file "foobar.txt" from the local host to a remote host using port 2264
$ scp -P 2264 foobar.txt your_username@remotehost.edu:/some/remote/directory
Copy multiple files from the remote host to your current directory on the local host
$ scp your_username@remotehost.edu:/some/remote/directory/\{a,b,c\} .
$ scp your_username@remotehost.edu:~/\{foo.txt,bar.txt\} .
scp Performance
By default scp uses the Triple-DES cipher to encrypt the data being sent. Using the Blowfish cipher has been shown to increase speed. This can be done by using option -c blowfish in the command line.
$ scp -c blowfish some_file your_username@remotehost.edu:~
It is often suggested that the -C option for compression should also be used to increase speed. The effect of compression, however, will only significantly increase speed if your connection is very slow. Otherwise it may just be adding extra burden to the CPU. An example of using blowfish and compression:
$ scp -c blowfish -C local_file your_username@remotehost.edu:~
scp allows files to be copied to, from, or between different hosts. It uses ssh for data transfer and provides the same authentication and same level of security as ssh.
Examples
Copy the file "foobar.txt" from a remote host to the local host
$ scp your_username@remotehost.edu:foobar.txt /some/local/directory
Copy the file "foobar.txt" from the local host to a remote host
$ scp foobar.txt your_username@remotehost.edu:/some/remote/directory
Copy the directory "foo" from the local host to a remote host's directory "bar"
$ scp -r foo your_username@remotehost.edu:/some/remote/directory/bar
Copy the file "foobar.txt" from remote host "rh1.edu" to remote host "rh2.edu"
$ scp your_username@rh1.edu:/some/remote/directory/foobar.txt \
your_username@rh2.edu:/some/remote/directory/
Copying the files "foo.txt" and "bar.txt" from the local host to your home directory on the remote host
$ scp foo.txt bar.txt your_username@remotehost.edu:~
Copy the file "foobar.txt" from the local host to a remote host using port 2264
$ scp -P 2264 foobar.txt your_username@remotehost.edu:/some/remote/directory
Copy multiple files from the remote host to your current directory on the local host
$ scp your_username@remotehost.edu:/some/remote/directory/\{a,b,c\} .
$ scp your_username@remotehost.edu:~/\{foo.txt,bar.txt\} .
scp Performance
By default scp uses the Triple-DES cipher to encrypt the data being sent. Using the Blowfish cipher has been shown to increase speed. This can be done by using option -c blowfish in the command line.
$ scp -c blowfish some_file your_username@remotehost.edu:~
It is often suggested that the -C option for compression should also be used to increase speed. The effect of compression, however, will only significantly increase speed if your connection is very slow. Otherwise it may just be adding extra burden to the CPU. An example of using blowfish and compression:
$ scp -c blowfish -C local_file your_username@remotehost.edu:~
Wednesday, July 11, 2012
To determine top 10 memory consuming processes
#prstat -s size -n 10 (to determine top 10 memory consuming processes)
To determine top 10 cpu consuming processes
#prstat -s cpu -n 10 (to determine top 10 cpu consuming processes)
Monday, July 9, 2012
Check RMAN Backup Status
This script will report on all backups – full, incremental and archivelog backups -
col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;
This script will report all on full and incremental backups, not archivelog backups -
col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
where input_type='DB INCR'
col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;
This script will report all on full and incremental backups, not archivelog backups -
col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
where input_type='DB INCR'
Sunday, June 3, 2012
Saturday, June 2, 2012
10.2.0.5 patch application and database upgrade from 10.2.0.4-hp-ux
10.2.0.5 patch application and database upgrade from 10.2.0.4-hp-ux
Applying 10.2.0.5 patch and Database Upgrade:
Introduction: Database patching primarily involves the following steps:
1. Applying the patch using OUI.
2. Upgrade the database to a higher version using Manual upgrade.
Environment Details: Server Name LAB2.server.org(HP-UX)
Database Name LAB2 Current Oracle DB Version 10.2.0.4.0
Oracle Home Location /oraLAB201/u01/app/oracle/product/10.2.0/LAB2
Listener Name LISTENER_LAB2
Pre-checks &
Preparations before applying 10.2.0.5 patch set:
1. Set the ORACLE_HOME and ORACLE_SID Environment Variables
Enter the following commands to set the ORACLE_HOME and ORACLE_SID
environment variables:
$ ORACLE_HOME=/oraLAB201/u01/app/oracle/product/10.2.0/LAB2
$ ORACLE_SID= LAB2
$ export ORACLE_HOME ORACLE_SID
2. Shut Down Oracle Databases and related Oracle processes
Shut down any existing Oracle Database instances with normal or immediate priority.
$ emctl stop dbconsole
$ lsnrctl stop LISTENER_ LAB2
$ sqlplus “/ as sysdba”
SQL> SHUTDOWN IMMEDIATE
3. Back Up the Oracle Database and Oracle Home:
Oracle recommends that you create a backup of the Oracle Inventory, Oracle 10g home and Oracle 10g Database before you install the patch set.
Command to backup Oracle Home:
tar -cvf orahome_.tar $ORACLE_HOME
Make sure that the latest full backup of the database was successful before proceeding with patch installation.
Also, take a backup of the following files:
o Init.ora or spfile.ora,Tnsnames.ora,Listener.ora
4. Comment out all the jobs scheduled for the database until the upgrade is over.
$ crontab –e
Comment the entries related to LAB2 database.
5. Display settings for GUI:
If you are not installing the software on the local computer, then perform the following steps on remote machine (LAB2.server.org).
1. Enable the X11 option in Putty. Navigate as shown below.
Putty configurationà SSHà Enable X11(after loading the server in putty).
2. Launch the Xming application.
3. Execute XCLOCK command in the Usetxora02 server and it should give the clock window.
6.
Initializing the Oracle Universal Installer:
Enter the following commands to start Oracle Universal Installer,
where /home/oracle/patchdir/10205_Patch is the directory where you unpacked the patch set software:
$ cd /home/oracle/patchdir/10205_Patch /Disk1
$ ./runInstaller
On the Welcome screen, click Next.
On the Specify Home Details screen,
select the name of the Oracle home that you want to update (10.2.0.4 in this case),
or select the Path that you want to update from the list, then click Next.
On the Product-specific Prerequisite Checks screen,
correct any reported errors, and click Next.
On the Summary screen,
click Install.
This screen lists all of the patches available for installation.
When prompted,
run the $ORACLE_HOME/root.sh script as the root user.
(Contact the system admin to run this script)
.
On the End of Installation screen,
click Exit,
then click Yes to exit from Oracle Universal Installer.
Post Installation Tasks
Log in as the Oracle software owner user
.
Set the values for the environment variables
$ORACLE_HOME,
$ORACLE_SID and
$PATH.
Start the listener as follows:
$ lsnrctl start LISTENER_ LAB2
Database Upgrade: Manually Upgrading a Release 10.2 Database
If you are upgrading database manually,
then you should analyze it by running the Pre-Upgrade Information Tool.
Run the Pre-Upgrade Information Tool
Start the database in the UPGRADE mode:
SQL> STARTUP UPGRADE
Set the system to spool results to a log file for later analysis:
SQL> SPOOL upgrade_info.log
Run the Pre-Upgrade Information Tool:
SQL> @$ORACLE_HOME/rdbms/admin/utlu102i.sql
Turn off the spooling of script results to the log file:
SQL> SPOOL OFF
Check the output of the Pre-Upgrade Information Tool in the upgrade_info.log file
Upgrading a Release 10.2 Database After you install the patch set,
you must perform the following steps on every database associated with the upgraded Oracle home:
1. Log in as the Oracle software owner user
2. Start the listener as follows: $ lsnrctl start LISTENER_LAB2
3. Enter the following SQL*Plus commands:
SQL> STARTUP UPGRADE
SQL> SPOOL patch.log
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
SQL> SPOOL OFF
4. Review the patch.log file for errors and inspect the list of components that is displayed at the end of catupgrd.sql script.
This list provides the version and status of each SERVER component in the database.
If necessary, rerun the catupgrd.sql script after correcting any problems.
5. Restart the database:
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
POST UPGRADE CHECKS:
Run the utlrp.sql script to recompile all invalid PL/SQL packages.
SQL> @?/rdbms/admin/utlrp.sql
When the 10.2.0.5 patch set is applied to an Oracle10g Standard Edition database,
there may be 54 invalid objects after the utlrp.sql script runs.
These objects belong to the unsupported components and do not affect the database operation.
Ignore any messages indicating that the database contains invalid recycle bin objects similar to the following: BIN$4lzljWIt9gfgMFeM2hVSoA==$0
Run the following command to check the status of all the components after the upgrade:
SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;
In the output of the preceding command, the status of all the components should be VALID for a successful upgrade.
To configure and secure Enterprise Manager follow these steps:
Ensure the database and Listener are operational.
Execute
emca -upgrade db
Note:If you are upgrading a database where Oracle Database Control is configured in non-secure mode, then Oracle Database Control will run in secure mode after upgrade.
Removing the Patch Set Software The catdwgrd.sql script enables the user to restore the Oracle Database installation of the database back to the original 10.2 release that the user backed up before applying the patch set.
The catdwgrd.sql script is run in the 10.2.0.5 Oracle home to perform necessary downgrade actions.
After the 10.2.0.4 release is restored, the catrelod.sql script is run from 10.2.0.4 Oracle home to reload the 10.2.0.4 release packages and views.
Removing the Patch Set Software for Single Instance Installation Perform the following steps for removing the patch set from the 10.2.0.5 patch release:
Use SQL*Plus to log in to the database as the SYS user with SYSDBA privileges:
2. SQL> SHUTDOWN IMMEDIATE
Take a backup of the following files in 10.2.0.5 Oracle home directory:
$ ORACLE_HOME/rdbms/admin/catrelod.sql
$ ORACLE_HOME/network/admin/tnsnames.ora
Enter the following SQL*Plus commands:
SQL> CONNECT SYS AS SYSDBA
SQL> STARTUP DOWNGRADE
SQL> SPOOL downgrade.out
SQL> @?/rdbms/admin/catdwgrd.sql
SQL> SPOOL OFF
SQL> SHUTDOWN IMMEDIATE
SQL> EXIT
Review the downgrade.out file for errors.
Restore the Oracle10g installation and Central Inventory (see /etc/oraInst.loc) that you backed up before applying the patch set.
After restoring the original Oracle home, copy the saved version of catrelod.sql script into the restored Oracle home rdbms/admin directory.
Also copy the saved version 10.2.0.5/network/admin/tnsnames.ora
directory into the restored Oracle home /network/admin/tnsnames.ora directory,
and then perform the following steps:
SQL> CONNECT SYS AS SYSDBA
SQL> STARTUP DOWNGRADE
SQL> SPOOL catrelod.out
SQL> @?/rdbms/admin/catrelod.sql
SQL> SPOOL OFF
SQL> SHUTDOWN IMMEDIATE
Review the catrelod.out file for errors.
Note: Ignore the ORA-29844 and ORA-31085 errors found in the catrelod.out file
. These errors are tracked with Oracle bugs 4758112 and 4425495 respectively.
After reloading the 10.2.0.2 release, the version numbers for Oracle Data Mining and Oracle OLAP Analytic Workspace remain at 10.2.0.5.
These issues are tracked with Oracle bugs 4758695 and 4751917.
Start the database and recompile the remaining invalid objects:
SQL> STARTUP
SQL> @?/rdbms/admin/utlrp.sql
Cross-check the status of installed components with the following script:
SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY; *********************END OF DOCUMENT************************
Applying 10.2.0.5 patch and Database Upgrade:
Introduction: Database patching primarily involves the following steps:
1. Applying the patch using OUI.
2. Upgrade the database to a higher version using Manual upgrade.
Environment Details: Server Name LAB2.server.org(HP-UX)
Database Name LAB2 Current Oracle DB Version 10.2.0.4.0
Oracle Home Location /oraLAB201/u01/app/oracle/product/10.2.0/LAB2
Listener Name LISTENER_LAB2
Pre-checks &
Preparations before applying 10.2.0.5 patch set:
1. Set the ORACLE_HOME and ORACLE_SID Environment Variables
Enter the following commands to set the ORACLE_HOME and ORACLE_SID
environment variables:
$ ORACLE_HOME=/oraLAB201/u01/app/oracle/product/10.2.0/LAB2
$ ORACLE_SID= LAB2
$ export ORACLE_HOME ORACLE_SID
2. Shut Down Oracle Databases and related Oracle processes
Shut down any existing Oracle Database instances with normal or immediate priority.
$ emctl stop dbconsole
$ lsnrctl stop LISTENER_ LAB2
$ sqlplus “/ as sysdba”
SQL> SHUTDOWN IMMEDIATE
3. Back Up the Oracle Database and Oracle Home:
Oracle recommends that you create a backup of the Oracle Inventory, Oracle 10g home and Oracle 10g Database before you install the patch set.
Command to backup Oracle Home:
tar -cvf orahome_
$ lsnrctl start LISTENER_ LAB2
Database Upgrade: Manually Upgrading a Release 10.2 Database
If you are upgrading database manually,
then you should analyze it by running the Pre-Upgrade Information Tool.
Run the Pre-Upgrade Information Tool
Start the database in the UPGRADE mode:
SQL> STARTUP UPGRADE
Set the system to spool results to a log file for later analysis:
SQL> SPOOL upgrade_info.log
Run the Pre-Upgrade Information Tool:
SQL> @$ORACLE_HOME/rdbms/admin/utlu102i.sql
Turn off the spooling of script results to the log file:
SQL> SPOOL OFF
Check the output of the Pre-Upgrade Information Tool in the upgrade_info.log file
Upgrading a Release 10.2 Database After you install the patch set,
you must perform the following steps on every database associated with the upgraded Oracle home:
1. Log in as the Oracle software owner user
2. Start the listener as follows: $ lsnrctl start LISTENER_LAB2
3. Enter the following SQL*Plus commands:
SQL> STARTUP UPGRADE
SQL> SPOOL patch.log
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
SQL> SPOOL OFF
4. Review the patch.log file for errors and inspect the list of components that is displayed at the end of catupgrd.sql script.
This list provides the version and status of each SERVER component in the database.
If necessary, rerun the catupgrd.sql script after correcting any problems.
5. Restart the database:
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
POST UPGRADE CHECKS:
Run the utlrp.sql script to recompile all invalid PL/SQL packages.
SQL> @?/rdbms/admin/utlrp.sql
When the 10.2.0.5 patch set is applied to an Oracle10g Standard Edition database,
there may be 54 invalid objects after the utlrp.sql script runs.
These objects belong to the unsupported components and do not affect the database operation.
Ignore any messages indicating that the database contains invalid recycle bin objects similar to the following: BIN$4lzljWIt9gfgMFeM2hVSoA==$0
Run the following command to check the status of all the components after the upgrade:
SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;
In the output of the preceding command, the status of all the components should be VALID for a successful upgrade.
To configure and secure Enterprise Manager follow these steps:
Ensure the database and Listener are operational.
Execute
emca -upgrade db
Note:If you are upgrading a database where Oracle Database Control is configured in non-secure mode, then Oracle Database Control will run in secure mode after upgrade.
Removing the Patch Set Software The catdwgrd.sql script enables the user to restore the Oracle Database installation of the database back to the original 10.2 release that the user backed up before applying the patch set.
The catdwgrd.sql script is run in the 10.2.0.5 Oracle home to perform necessary downgrade actions.
After the 10.2.0.4 release is restored, the catrelod.sql script is run from 10.2.0.4 Oracle home to reload the 10.2.0.4 release packages and views.
Removing the Patch Set Software for Single Instance Installation Perform the following steps for removing the patch set from the 10.2.0.5 patch release:
Use SQL*Plus to log in to the database as the SYS user with SYSDBA privileges:
2. SQL> SHUTDOWN IMMEDIATE
Take a backup of the following files in 10.2.0.5 Oracle home directory:
$ ORACLE_HOME/rdbms/admin/catrelod.sql
$ ORACLE_HOME/network/admin/tnsnames.ora
Enter the following SQL*Plus commands:
SQL> CONNECT SYS AS SYSDBA
SQL> STARTUP DOWNGRADE
SQL> SPOOL downgrade.out
SQL> @?/rdbms/admin/catdwgrd.sql
SQL> SPOOL OFF
SQL> SHUTDOWN IMMEDIATE
SQL> EXIT
Review the downgrade.out file for errors.
Restore the Oracle10g installation and Central Inventory (see /etc/oraInst.loc) that you backed up before applying the patch set.
After restoring the original Oracle home, copy the saved version of catrelod.sql script into the restored Oracle home rdbms/admin directory.
Also copy the saved version 10.2.0.5/network/admin/tnsnames.ora
directory into the restored Oracle home /network/admin/tnsnames.ora directory,
and then perform the following steps:
SQL> CONNECT SYS AS SYSDBA
SQL> STARTUP DOWNGRADE
SQL> SPOOL catrelod.out
SQL> @?/rdbms/admin/catrelod.sql
SQL> SPOOL OFF
SQL> SHUTDOWN IMMEDIATE
Review the catrelod.out file for errors.
Note: Ignore the ORA-29844 and ORA-31085 errors found in the catrelod.out file
. These errors are tracked with Oracle bugs 4758112 and 4425495 respectively.
After reloading the 10.2.0.2 release, the version numbers for Oracle Data Mining and Oracle OLAP Analytic Workspace remain at 10.2.0.5.
These issues are tracked with Oracle bugs 4758695 and 4751917.
Start the database and recompile the remaining invalid objects:
SQL> STARTUP
SQL> @?/rdbms/admin/utlrp.sql
Cross-check the status of installed components with the following script:
SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY; *********************END OF DOCUMENT************************
Friday, June 1, 2012
Oracle DBA Interview Questions
1. Basic (Every DBA should answer correctly ALL these questions. This knowledge is just basic for a 3+ year experienced DBA)
1.1 Q- Which are the default passwords of SYSTEM/SYS?
A- MANAGER / CHANGE_ON_INSTALL
1.2 Q- How can you execute a script file in SQLPLUS?
A- To execute a script file in SQLPlus, type @ and then the file name.
1.3 Q- Where can you find alertlog?
A- bdump directory (show parameter background)
1.4 Q- What is the address of the Official Oracle Support?
A- metalink.oracle.com or support.oracle.com
1.5 Q- What file will you use to establish Oracle connections from a remote client?
A- tnsnames.ora
1.6 Q- How can you check if the database is accepting connections?
A- lsnrctl status or lsnrctl services
1.7 Q- Which log would you check if a database has a problem?
A- Alert log
1.8 Q- Name three clients to connect with Oracle, for example, SQL Developer:
A- SQL Developer, SQL-Plus, TOAD, dbvisualizer, PL/SQL Developer… There are several, but an experienced dba should know at least three clients.
1.9 Q- How can you check the structure of a table from sqlplus?
A- DESCRIBE or DESC
1.10 Q- What command will you start to run the installation of Oracle software on Linux?
A- runInstaller
2. Moderate (Standard knoledge for a daily-work of every DBA. He could fail one or two questions, but not more)
2.1 Q- What should you do if you encounter an ORA-600?
A- Check alertlog file for understanding the cause and investigate. Paralley raise call with Oracle Support
2.2 Q- Explain the differences between PFILE and SPFILE
A- A PFILE is a Static, text file that initialices the database parameter in the moment that it’s started. If you want to modify parameters in PFILE, you have to restart the database.
A SPFILE is a dynamic, binary file that allows you to overwrite parameters while the database is already started (with some exceptions)
2.3 Q- In which Oracle version was Data Pump introduced?
A- Oracle 10g
2.4 Q- Say two examples of DML, two of DCL and two of DDL
A- DML: SELECT, INSERT, UPDATE, DELETE, MERGE, CALL, EXPLAIN PLAN, LOCK TABLE
DDL: CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME
DCL: GRANT, REVOKE
2.5 Q- You want to save the output of an Oracle script from sqlplus. How would you do it?
A- spool script_name.txt
select * from your_oracle_operations;
spool off;
2.6 Q- What is the most important requirement in order to use RMAN to make consistent hot backups?
A- Your database has to be in ARCHIVELOG mode.
2.7 Q- Can you connect to a local database without a listener?
A- Yes, you can.
2.8 Q- In which view can you find information about every view and table of oracle dictionary?
A- DICT or DICTIONARY
2.9 Q- How can you view all the users account in the database?
A- SELECT USERNAME FROM DBA_USERS;
2.10 Q- In linux, how can we change which databases are started during a reboot?
A- Edit /etc/oratab
3. Advanced (A 3+ year experienced DBA should have enough knowledge to answer these questions. However, depending on the work he has done, he could still fail up to 4 questions)
3.1 Q- When a user process fails, what Oracle background process will clean after it?
A- PMON
3.2 Q- How can you reduce the space of TEMP datafile?
A- Prior to Oracle 11g, you had to recreate the datafile. In Oracle 11g a new feature was introduced, and you can shrink the TEMP tablespace.
3.3 Q- How can you view all the current users connected in your database in this moment?
A- SELECT COUNT(*),USERNAME FROM V$SESSION GROUP BY USERNAME;
3.4 Q- Explain the differences between SHUTDOWN, SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE AND SHUTDOWN ABORT
A- SHUTOWN NORMAL = SHUTDOWN : It waits for all sessions to end, without allowing new connections.
SHUTDOWN IMMEDIATE : Rollback current transactions and terminates every session.
SHUTDOWN ABORT : Aborts all the sessions, leaving the database in an inconsistent state. It’s the fastest method, but can lead to database corruption.
3.5 Q- Is it possible to backup your database without the use of an RMAN database to store the catalog?
A- Yes, but the catalog would be stored in the controlfile.
3.6 Q- Which are the main components of Oracle Grid Control?
A- OMR (Oracle Management Repository), OMS (Oracle Management Server) and OMA (Oracle Management Agent).
3.7 Q- What command will you use to navigate through ASM files?
A- asmcmd
3.8 Q- What is the difference between a view and a materialized view?
A- A view is a select that is executed each time an user accesses to it. A materialized view stores the result of this query in memory for faster access purposes.
3.9 Q- Which one is faster: DELETE or TRUNCATE?
A- TRUNCATE
3.10 Q- Are passwords in oracle case sensitive?
A- Only since Oracle 11g.
4. RAC (Only intended for RAC-specific DBAs, with varied difficultied questions)
4.1 Q- What is the recommended method to make backups of a RAC environment?
A- RMAN to make backups of the database, dd to backup your voting disk and hard copies of the OCR file.
4.2 Q- What command would you use to check the availability of the RAC system?
A- crs_stat -t -v (-t -v are optional)
4.3 Q- What is the minimum number of instances you need to have in order to create a RAC?
A- 1. You can create a RAC with just one server.
4.4 Q- Name two specific RAC background processes
A- RAC processes are: LMON, LMDx, LMSn, LKCx and DIAG.
4.5 Q- Can you have many database versions in the same RAC?
A- Yes, but Clusterware version must be greater than the greater database version.
4.6 Q- What was RAC previous name before it was called RAC?
A- OPS: Oracle Parallel Server
4.7 Q- What RAC component is used for communication between instances?
A- Private Interconnect.
4.8 Q- What is the difference between normal views and RAC views?
A- RAC views has the prefix ‘G’. For example, GV$SESSION instead of V$SESSION
4.9 Q- Which command will we use to manage (stop, start…) RAC services in command-line mode?
A- srvctl
4.10 Q- How many alert logs exist in a RAC environment?
A- One for each instance.
5. Master (A 3+ year experienced DBA would probably fail these questions, they are very specifid and specially difficult. Be glad if he’s able to answer some of them)
5.1 Q- How can you difference a usual parameter and an undocumented parameter?
A- Undocumented parameters have the prefix ‘_’. For example, _allow_resetlogs_corruption
5.2 Q- What is BBED?
A- An undocumented Oracle tool used for foresnic purposes. Stans for Block Browser and EDitor.
5.3 Q- The result of the logical comparison (NULL = NULL) will be… And in the case of (NULL != NULL)
A- False in both cases.
5.4 Q- Explain Oracle memory structure
The Oracle RDBMS creates and uses storage on the computer hard disk and in random access memory (RAM). The portion in the computer s RAM is called memory structure. Oracle has two memory structures in the computer s RAM. The two structures are the Program Global Area (PGA) and the System Global Area (SGA).
The PGA contains data and control information for a single user process. The SGA is the memory segment that stores data that the user has retrieved from the database or data that the user wants to place into the database.
5.5 Q- Will RMAN take backups of read-only tablespaces?
A- No
5.6 Q- Will a user be able to modify a table with SELECT only privilege?
A- He won’t be able to UPDATE/INSERT into that table, but for some reason, he will still be able to lock a certain table.
5.7 Q- What Oracle tool will you use to transform datafiles into text files?
A- Trick question: you can’t do that, at least with any Oracle tool. A very experienced DBA should perfectly know this.
5.8 Q- SQL> SELECT * FROM MY_SCHEMA.MY_TABLE;
SP2-0678: Column or attribute type can not be displayed by SQL*Plus
Why I’m getting this error?
A- The table has a BLOB column.
5.9 Q- What parameter will you use to force the starting of your database with a corrupted resetlog?
A- _ALLOW_RESETLOGS_CORRUPTION
5.10 Q- Name the seven types of Oracle tables
A- Heap Organized Tables, Index Organized Tables, Index Clustered Tables, Hash Clustered Tables, Nested Tables, Global Temporary Tables, Object Tables
Wednesday, May 9, 2012
Recruitment, Job openings
OPenings for freshers(BE, BTECH, Bsc, BCA, MCA, Msc, MBA) to work in Backup technologies.
Min Requirement: Degree with Good Communication Skills. 2011 passed out.
Mail your resume to mail2satheeshmba@gmail.com
Monday, February 13, 2012
Al DBA sql queries and scripts
http://www.blacksheepnetworks.com/security/resources/www.think-forward.com/sqltips.htm
Monday, January 16, 2012
How to Validate the Restore of Backups
RESTORE VALIDATE and VALIDATE BACKUPSET commands check whether you can restore from your backups.
---------------------------------------------------------
Examples:
RESTORE CONTROLFILE VALIDATE;
RESTORE TABLESPACE SYSTEM VALIDATE;
RESTORE ARCHIVELOG ALL VALIDATE;
---------------------------------------------------------
---------------------------------------------------------
Examples:
VALIDATE BACKUPSET 1121,1122;
---------------------------------------------------------
---------------------------------------------------------
Examples:
RESTORE CONTROLFILE VALIDATE;
RESTORE TABLESPACE SYSTEM VALIDATE;
RESTORE ARCHIVELOG ALL VALIDATE;
---------------------------------------------------------
---------------------------------------------------------
Examples:
VALIDATE BACKUPSET 1121,1122;
---------------------------------------------------------
To Preview Backups Used in Restore Operations.
The RESTORE PREVIEW can be used to any RESTORE operation to produce a detailed report of every backup to be used in the RESTORE operation.
----------------------------------------------------------------
Examples:
RESTORE DATABASE PREVIEW;
RESTORE TABLESPACE users PREVIEW;
RESTORE DATAFILE 3 PREVIEW;
RESTORE ARCHIVELOG FROM LOGSEQ 200 PREVIEW;
RESTORE ARCHIVELOG FROM TIME 'SYSDATE-7' PREVIEW;
RESTORE ARCHIVELOG FROM SCN 234546 PREVIEW;
---------------------------------------------------------------------
----------------------------------------------------------------
Examples:
RESTORE DATABASE PREVIEW;
RESTORE TABLESPACE users PREVIEW;
RESTORE DATAFILE 3 PREVIEW;
RESTORE ARCHIVELOG FROM LOGSEQ 200 PREVIEW;
RESTORE ARCHIVELOG FROM TIME 'SYSDATE-7' PREVIEW;
RESTORE ARCHIVELOG FROM SCN 234546 PREVIEW;
---------------------------------------------------------------------
Thursday, January 5, 2012
Query to calculate CPU utilisation in a database.
select
ss.username,
se.SID,
VALUE/100 cpu_usage_seconds
from
v$session ss,
v$sesstat se,
v$statname sn
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like '%CPU used by this session%'
and
se.SID = ss.SID
and
ss.status='ACTIVE'
and
ss.username is not null
order by VALUE desc;
----------------------------------------
Select sql_text from v$sql where address=(select sql_address from v$session where paddr=(select addr from v$process where spid= &spid));
Select sql_text from v$sql where address in (select sql_address from v$session where paddr in (select addr from v$process where spid=622666));
Select sql_text from v$sqltext where hash_value=(select sql_hash_value from v$session where paddr=(select addr from v$process where spid=1929696));
Select sql_text from v$sqltext where hash_value in (select sql_hash_value from v$session where paddr in (select addr from v$process where spid=1138912));
select username from v$session where paddr=(select addr from v$process where spid=1138912);
-----------------------------------------------------------
ss.username,
se.SID,
VALUE/100 cpu_usage_seconds
from
v$session ss,
v$sesstat se,
v$statname sn
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like '%CPU used by this session%'
and
se.SID = ss.SID
and
ss.status='ACTIVE'
and
ss.username is not null
order by VALUE desc;
----------------------------------------
Select sql_text from v$sql where address=(select sql_address from v$session where paddr=(select addr from v$process where spid= &spid));
Select sql_text from v$sql where address in (select sql_address from v$session where paddr in (select addr from v$process where spid=622666));
Select sql_text from v$sqltext where hash_value=(select sql_hash_value from v$session where paddr=(select addr from v$process where spid=1929696));
Select sql_text from v$sqltext where hash_value in (select sql_hash_value from v$session where paddr in (select addr from v$process where spid=1138912));
select username from v$session where paddr=(select addr from v$process where spid=1138912);
-----------------------------------------------------------
Subscribe to:
Posts (Atom)