tag:blogger.com,1999:blog-39922392255299760522024-03-13T06:28:27.160+05:30Oracle DBA Tweets .... Learning is Fun-tasticOracle DBA Tweets .... Learning is Fun-tastic ....
You must stop talking about the problem and start talking about the solution, start speaking words of victory.
We should never stop working on ourselves.
Always be positive and keep smiling..have a great day friends :)
Source: InternetAnonymoushttp://www.blogger.com/profile/04664995477596294650noreply@blogger.comBlogger138125tag:blogger.com,1999:blog-3992239225529976052.post-14046693914113095102017-08-25T16:49:00.004+05:302017-08-25T16:49:56.107+05:30CPU consumptions by ACTIVE sessions<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<pre><span class="hl-comment">--
-- Show CPU Usage for Active Sessions</span><span class="hl-code">
</span><span class="hl-comment">--
</span><span class="hl-code">
</span><span class="hl-reserved">SET</span><span class="hl-code"> </span><span class="hl-identifier">PAUSE</span><span class="hl-code"> </span><span class="hl-reserved">ON</span><span class="hl-code">
</span><span class="hl-reserved">SET</span><span class="hl-code"> </span><span class="hl-identifier">PAUSE</span><span class="hl-code"> </span><span class="hl-quotes">'</span><span class="hl-string">Press Return to Continue</span><span class="hl-quotes">'</span><span class="hl-code">
</span><span class="hl-reserved">SET</span><span class="hl-code"> </span><span class="hl-identifier">PAGESIZE</span><span class="hl-code"> </span><span class="hl-number">60</span><span class="hl-code">
</span><span class="hl-reserved">SET</span><span class="hl-code"> </span><span class="hl-identifier">LINESIZE</span><span class="hl-code"> </span><span class="hl-number">300</span><span class="hl-code">
</span><span class="hl-reserved">COLUMN</span><span class="hl-code"> </span><span class="hl-identifier">username</span><span class="hl-code"> </span><span class="hl-identifier">FORMAT</span><span class="hl-code"> </span><span class="hl-identifier">A30</span><span class="hl-code">
</span><span class="hl-reserved">COLUMN</span><span class="hl-code"> </span><span class="hl-identifier">sid</span><span class="hl-code"> </span><span class="hl-identifier">FORMAT</span><span class="hl-code"> </span><span class="hl-number">999</span><span class="hl-code">,</span><span class="hl-number">999</span><span class="hl-code">,</span><span class="hl-number">999</span><span class="hl-code">
</span><span class="hl-reserved">COLUMN</span><span class="hl-code"> </span><span class="hl-identifier">serial</span><span class="hl-comment"># FORMAT 999,999,999</span><span class="hl-code">
</span><span class="hl-reserved">COLUMN</span><span class="hl-code"> </span><span class="hl-quotes">"</span><span class="hl-string">cpu usage (seconds)</span><span class="hl-quotes">"</span><span class="hl-code"> </span><span class="hl-identifier">FORMAT</span><span class="hl-code"> </span><span class="hl-number">999</span><span class="hl-code">,</span><span class="hl-number">999</span><span class="hl-code">,</span><span class="hl-number">999.0000</span><span class="hl-code">
</span><span class="hl-reserved">SELECT</span><span class="hl-code">
</span><span class="hl-identifier">s</span><span class="hl-code">.</span><span class="hl-identifier">username</span><span class="hl-code">,
</span><span class="hl-identifier">t</span><span class="hl-code">.</span><span class="hl-identifier">sid</span><span class="hl-code">,
</span><span class="hl-identifier">s</span><span class="hl-code">.</span><span class="hl-identifier">serial</span><span class="hl-comment">#,</span><span class="hl-code">
</span><span class="hl-var">SUM</span><span class="hl-brackets">(</span><span class="hl-reserved">VALUE</span><span class="hl-code">/</span><span class="hl-number">100</span><span class="hl-brackets">)</span><span class="hl-code"> </span><span class="hl-reserved">as</span><span class="hl-code"> </span><span class="hl-quotes">"</span><span class="hl-string">cpu usage (seconds)</span><span class="hl-quotes">"</span><span class="hl-code">
</span><span class="hl-reserved">FROM</span><span class="hl-code">
</span><span class="hl-identifier">v</span><span class="hl-code">$</span><span class="hl-reserved">session</span><span class="hl-code"> </span><span class="hl-identifier">s</span><span class="hl-code">,
</span><span class="hl-identifier">v</span><span class="hl-code">$</span><span class="hl-identifier">sesstat</span><span class="hl-code"> </span><span class="hl-identifier">t</span><span class="hl-code">,
</span><span class="hl-identifier">v</span><span class="hl-code">$</span><span class="hl-identifier">statname</span><span class="hl-code"> </span><span class="hl-identifier">n</span><span class="hl-code">
</span><span class="hl-reserved">WHERE</span><span class="hl-code">
</span><span class="hl-identifier">t</span><span class="hl-code">.</span><span class="hl-identifier">STATISTIC</span><span class="hl-comment"># = n.STATISTIC#</span><span class="hl-code">
</span><span class="hl-reserved">AND</span><span class="hl-code">
</span><span class="hl-var">NAME</span><span class="hl-code"> </span><span class="hl-reserved">like</span><span class="hl-code"> </span><span class="hl-quotes">'</span><span class="hl-string">%CPU used by this session%</span><span class="hl-quotes">'</span><span class="hl-code">
</span><span class="hl-reserved">AND</span><span class="hl-code">
</span><span class="hl-identifier">t</span><span class="hl-code">.</span><span class="hl-identifier">SID</span><span class="hl-code"> = </span><span class="hl-identifier">s</span><span class="hl-code">.</span><span class="hl-identifier">SID</span><span class="hl-code">
</span><span class="hl-reserved">AND</span><span class="hl-code">
</span><span class="hl-identifier">s</span><span class="hl-code">.</span><span class="hl-identifier">status</span><span class="hl-code">=</span><span class="hl-quotes">'</span><span class="hl-string">ACTIVE</span><span class="hl-quotes">'</span><span class="hl-code">
</span><span class="hl-reserved">AND</span><span class="hl-code">
</span><span class="hl-identifier">s</span><span class="hl-code">.</span><span class="hl-identifier">username</span><span class="hl-code"> </span><span class="hl-reserved">is</span><span class="hl-code"> </span><span class="hl-reserved">not</span><span class="hl-code"> </span><span class="hl-reserved">null</span><span class="hl-code">
</span><span class="hl-reserved">GROUP</span><span class="hl-code"> </span><span class="hl-reserved">BY</span><span class="hl-code"> </span><span class="hl-identifier">username</span><span class="hl-code">,</span><span class="hl-identifier">t</span><span class="hl-code">.</span><span class="hl-identifier">sid</span><span class="hl-code">,</span><span class="hl-identifier">s</span><span class="hl-code">.</span><span class="hl-identifier">serial</span><span class="hl-comment">#</span><span class="hl-code">
/</span></pre>
</div>
Anonymoushttp://www.blogger.com/profile/04664995477596294650noreply@blogger.com6tag:blogger.com,1999:blog-3992239225529976052.post-11406343572647127492017-08-25T16:33:00.002+05:302017-08-25T16:34:26.822+05:30Getting Session Details | sid.sql<div dir="ltr" style="text-align: left;" trbidi="on">
col sid format 99999<br />
col username format a10<br />
col osuser format a10<br />
col program format a25<br />
col process format 9999999<br />
col spid format 999999<br />
col logon_time format a13<br />
<br />
set lines 150<br />
<br />
set heading off<br />
set verify off<br />
set feedback off<br />
<br />
undefine sid_number<br />
undefine spid_number<br />
rem accept sid_number number prompt "pl_enter_sid:"<br />
<br />
col sid NEW_VALUE sid_number noprint<br />
col spid NEW_VALUE spid_number noprint<br />
<br />
<br />
select s.sid sid,<br />
p.spid spid<br />
-- ,decode(count(*), 1,'null','No Session Found with this info') " "<br />
FROM v$session s,<br />
v$process p<br />
WHERE s.sid LIKE NVL('&sid', '%')<br />
AND p.spid LIKE NVL ('&OS_ProcessID', '%')<br />
AND s.process LIKE NVL('&Client_Process', '%')<br />
AND s.paddr = p.addr<br />
-- group by s.sid, p.spid;<br />
<br />
PROMPT Session and Process Information<br />
PROMPT -------------------------------<br />
<br />
col event for a30<br />
<br />
select ' SID : '||v.sid || chr(10)||<br />
' Serial Number : '||v.serial# || chr(10) ||<br />
' Oracle User Name : '||v.username || chr(10) ||<br />
' Client OS user name : '||v.osuser || chr(10) ||<br />
' Client Process ID : '||v.process || chr(10) ||<br />
' Client machine Name : '||v.machine || chr(10) ||<br />
' Oracle PID : '||p.pid || chr(10) ||<br />
' OS Process ID(spid) : '||p.spid || chr(10) ||<br />
' Session''s Status : '||v.status || chr(10) ||<br />
' Logon Time : '||to_char(v.logon_time, 'MM/DD HH24:MIpm') || chr(10) ||<br />
' Program Name : '||v.program || chr(10) ||<br />
' module : '||v.module || chr(10) ||<br />
' Hashvalue : '||v.sql_hash_value || chr(10)<br />
from v$session v, v$process p<br />
where v.paddr = p.addr<br />
and v.serial# > 1<br />
and p.background is null<br />
and p.username is not null<br />
and sid = &sid_number<br />
order by logon_time, v.status, 1<br />
/<br />
<br />
<br />
PROMPT Sql Statement<br />
PROMPT --------------<br />
<br />
select sql_text<br />
from v$sqltext , v$session<br />
where v$sqltext.address = v$session.sql_address<br />
and sid = &sid_number<br />
order by piece<br />
/<br />
<br />
PROMPT<br />
PROMPT Event Wait Information<br />
PROMPT ----------------------<br />
<br />
select ' SID '|| &sid_number ||' is waiting on event : ' || x.event || chr(10) ||<br />
' P1 Text : ' || x.p1text || chr(10) ||<br />
' P1 Value : ' || x.p1 || chr(10) ||<br />
' P2 Text : ' || x.p2text || chr(10) ||<br />
' P2 Value : ' || x.p2 || chr(10) ||<br />
' P3 Text : ' || x.p3text || chr(10) ||<br />
' P3 Value : ' || x.p3<br />
from v$session_wait x<br />
where x.sid= &sid_number<br />
/<br />
<br />
PROMPT<br />
PROMPT Session Statistics<br />
PROMPT ------------------<br />
<br />
select ' '|| b.name ||' : '||decode(b.name, 'redo size', round(a.value/1024/1024,2)||' M', a.value)<br />
from v$session s, v$sesstat a, v$statname b<br />
where a.statistic# = b.statistic#<br />
and name in ('redo size', 'parse count (total)', 'parse count (hard)', 'user commits')<br />
and s.sid = &sid_number<br />
and a.sid = &sid_number<br />
--order by b.name<br />
order by decode(b.name, 'redo size', 1, 2), b.name<br />
/<br />
<br />
COLUMN USERNAME FORMAT a10<br />
COLUMN status FORMAT a8<br />
column RBS_NAME format a10<br />
<br />
PROMPT<br />
PROMPT Transaction and Rollback Information<br />
PROMPT ------------------------------------<br />
<br />
select ' Rollback Used : '||t.used_ublk*8192/1024/1024 ||' M' || chr(10) ||<br />
' Rollback Records : '||t.used_urec || chr(10)||<br />
' Rollback Segment Number : '||t.xidusn || chr(10)||<br />
' Rollback Segment Name : '||r.name || chr(10)||<br />
' Logical IOs : '||t.log_io || chr(10)||<br />
' Physical IOs : '||t.phy_io || chr(10)||<br />
' RBS Startng Extent ID : '||t.start_uext || chr(10)||<br />
' Transaction Start Time : '||t.start_time || chr(10)||<br />
' Transaction_Status : '||t.status<br />
FROM v$transaction t, v$session s, v$rollname r<br />
WHERE t.addr = s.taddr<br />
and r.usn = t.xidusn<br />
and s.sid = &sid_number<br />
/<br />
<br />
PROMPT<br />
PROMPT Sort Information<br />
PROMPT ----------------<br />
<br />
column username format a20<br />
column user format a20<br />
column tablespace format a20<br />
<br />
SELECT ' Sort Space Used(8k block size is asssumed : '||u.blocks/1024*8 ||' M' || chr(10) ||<br />
' Sorting Tablespace : '||u.tablespace || chr(10)||<br />
' Sort Tablespace Type : '||u.contents || chr(10)||<br />
' Total Extents Used for Sorting : '||u.extents<br />
FROM v$session s, v$sort_usage u<br />
WHERE s.saddr = u.session_addr<br />
AND s.sid = &sid_number<br />
/<br />
<br />
<br />
set heading on<br />
set verify on<br />
<br />
clear column</div>
Anonymoushttp://www.blogger.com/profile/04664995477596294650noreply@blogger.com3tag:blogger.com,1999:blog-3992239225529976052.post-25515010891282227172014-06-22T04:22:00.004+05:302014-06-22T04:22:51.033+05:30Tablespace Utilization Script for Tablespace Space Used % more than 80 %<div dir="ltr" style="text-align: left;" trbidi="on">
Oracle Tablespace Utilization Script (including AUTOEXTEND) for generating report of more than 80 % used tablespaces (IN GB)<br />
<br />
1. Check the database details.<br />
2. Check the tablespace Utilization.<br />
3. Check the details of the datafiles for a particular TableSpace which needs attention.<br />
4. Resize or Add the datafiles as per the standards of the existing datafiles on the database.<br />
<br />
<b>1. Check the database details.</b><br />
$ sqlplus "/as sysdba"<br />
<br />
set pages 9999 lines 300<br />
col OPEN_MODE for a10<br />
col HOST_NAME for a30<br />
select name DB_NAME,HOST_NAME,DATABASE_ROLE from v$database,v$instance;<br />
<br />
<b>2. Check the tablespace Utilization.</b><br />
Tablespace Utilization Script including AUTOEXTEND (IN GB)<br />
----------------------------------------------------------<br />
$ sqlplus "/as sysdba"<br />
<br />
set pages 50000 lines 32767<br />
col tablespace_name format a30<br />
col TABLESPACE_NAME heading "Tablespace|Name"<br />
col Allocated_size heading "Allocated|Size(GB)" form 99999999.99<br />
col Current_size heading "Current|Size(GB)" form 99999999.99<br />
col Used_size heading "Used|Size(GB)" form 99999999.99<br />
col Available_size heading "Available|Size(GB)" form 99999999.99<br />
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99<br />
<br />
select a.tablespace_name<br />
,a.alloc_size/1024/1024/1024 Allocated_size<br />
,a.cur_size/1024/1024/1024 Current_Size<br />
,(u.used+a.file_count*65536)/1024/1024/1024 Used_size<br />
,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024/1024 Available_size<br />
,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used<br />
from dba_tablespaces t<br />
,(select t1.tablespace_name<br />
,nvl(sum(s.bytes),0) used<br />
from dba_segments s<br />
,dba_tablespaces t1<br />
where t1.tablespace_name=s.tablespace_name(+)<br />
group by t1.tablespace_name) u<br />
,(select d.tablespace_name<br />
,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size<br />
,sum(d.bytes) cur_size<br />
,count(*) file_count<br />
from dba_data_files d<br />
group by d.tablespace_name) a<br />
where t.tablespace_name=u.tablespace_name<br />
and ((u.used+a.file_count*65536)*100)/a.alloc_size>80<br />
and t.tablespace_name=a.tablespace_name<br />
order by t.tablespace_name<br />
/<br />
<div>
<br /></div>
<div>
<div>
<b>3. Check the details of the datafiles for a particular TableSpace which needs attention.</b></div>
<div>
Datafiles of a particular TableSpace:</div>
<div>
------------------------------------</div>
<div>
set pages 50000 lines 32767</div>
<div>
col tablespace_name for a30</div>
<div>
col CREATION_TIME for a15</div>
<div>
col file_name for a70</div>
<div>
select dd.tablespace_name TABLESPACE_NAME,dd.file_name,dd.bytes/1024/1024 Size_MB,dd.autoextensible,dd.maxbytes/1024/1024 MAXSIZE_MB,df.CREATION_TIME</div>
<div>
from dba_data_files dd, v$datafile df where df.name=dd.file_name and tablespace_name='&TABLESPACENAME' order by 1,2,6;</div>
<div>
<br /></div>
<div>
<b>Note:- If required, can get the DDL of a tablespace as below.</b></div>
<div>
<b><br /></b></div>
<div>
<b>TABLESPACE DDL</b></div>
<div>
--------------</div>
<div>
set pagesize 0</div>
<div>
SET LONG 9999999</div>
<div>
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;</div>
</div>
</div>
Anonymoushttp://www.blogger.com/profile/04664995477596294650noreply@blogger.com5tag:blogger.com,1999:blog-3992239225529976052.post-61402103311593581282014-06-22T02:05:00.002+05:302014-06-22T02:05:16.430+05:30How to generate Multiple AWR reports<div dir="ltr" style="text-align: left;" trbidi="on">
<b>Multiple AWR report generation script</b><br />
<br />
The following script can be used for AWR reports generation, for specific intervals between the required Snapshots generated.<br />
<br />
<br />
The below script (awr_report_generate.sh) is to generate AWR reports for the specific intervals between the required Snapshots generated.<br />
Let say, there is a requirement to generate awr reports for every 2 hours of previous day from 00:00 hrs to current date 00:00 hrs ie., 24 hrs.<br />
<br />
Reuirements:-<br />
===========<br />
<br />
First we need to get the Begin Snap ID and End Snap ID, in order to generate multiple awr reports using the below script.<br />
<br />
$ sqlplus "/as sysdba"<br />
SQL> @?/rdbms/admin/awrrpt.sql (For RAC, SQL> @?/rdbms/admin/awrrpti.sql)<br />
.<br />
.<br />
.<br />
<br />
Specify the Report Type<br />
~~~~~~~~~~~~~~~~~~~~~~~<br />
Enter 'html' for an HTML report, or 'text' for plain text<br />
Defaults to 'html'<br />
Enter value for report_type: ---------------> press 'Enter' Key<br />
<br />
Specify the number of days of snapshots to choose from<br />
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~<br />
Enter value for num_days:<br />
<br />
Listing the last n days of Completed Snapshots ---------------> here 'n' represents the number of days we have entered for generating awr reports.<br />
<br />
NOTE:-<br />
====<br />
Here, for the required period, make a note of the required Begin Snap ID and End Snap ID.<br />
<br />
<br />
Create a script awr_report_generate.sh:-<br />
======================================<br />
SQL> exit<br />
<br />
$ vi awr_report_generate.sh<br />
<br />
#!/usr/bin/sh<br />
echo "Enter the value for Begin Snapshot Id :"<br />
read beginid<br />
echo "Enter the value for End Snapshot Id:"<br />
read endid<br />
echo "Enter the value for interval between Snapshot Id's. To generate reports between consecutive Snapshop Id's, Enter '1'. Else, enter desired value:"<br />
read snapint<br />
echo "Enter the value for report type: html/text"<br />
read repfmt<br />
echo "Enter the path for unix directory to generate the reports. Press 'Enter' to generate the reports in current working directory:"<br />
read repdir<br />
if [ "$repdir" = "" ]<br />
then<br />
repdir=$PWD<br />
fi<br />
<br />
while [ $beginid -lt $endid ]<br />
do<br />
tempid=`echo $beginid + $snapint |bc`<br />
sqlplus -s '/as sysdba'<<EOF<br />
set verify off<br />
set feedback off<br />
set pages 0<br />
set serveroutput on<br />
clear break compute;<br />
repfooter off;<br />
ttitle off;<br />
btitle off;<br />
<br />
set heading on;<br />
set timing off veri off space 1 flush on pause off termout on numwidth 10;<br />
set echo off feedback off pagesize 0 linesize 1500 newpage 1 recsep off;<br />
set trimspool on trimout on define "&" concat "." serveroutput on;<br />
set underline on;<br />
col endid new_value endid;<br />
col repname new_value repname;<br />
col dbid new_value dbid;<br />
col inst_num new_value inst_num;<br />
<br />
define beginid=$beginid;<br />
define tempid=$tempid;<br />
<br />
variable repname varchar2(60);<br />
variable dbid varchar2(10);<br />
variable inst_num varchar2(2);<br />
<br />
select dbid dbid from v\$database;<br />
select instance_number inst_num from v\$instance ;<br />
select '$repdir/AWR_'||(select instance_name inst_name from v\$instance)||'_'||(select to_char(END_INTERVAL_TIME,'DDMONYY_HH24MI')from dba_hist_snapshot where snap_id='$beginid' and instance_number=(select instance_number from v\$instance))||'_'||(select to_char(END_INTERVAL_TIME,'DDMONYY_HH24MI')from dba_hist_snapshot where snap_id='$tempid' and instance_number=(select instance_number from v\$instance))||'.$repfmt' repname from dual;<br />
spool &repname<br />
select output from table(dbms_workload_repository.awr_report_$repfmt(&dbid,&inst_num,&&beginid,&&tempid,0));<br />
spool off<br />
exit<br />
EOF<br />
beginid=`echo $beginid + $snapint |bc`<br />
done<br />
<br />
<br />
-- Press esc<br />
:wq<br />
<br />
$ ls -lrt awr_report_generate.sh<br />
<br />
Run the script:-<br />
==============<br />
<br />
$ sh awr_report_generate.sh<br />
<br />
<br />
$ ls -lrt<br />
<br />
<br />
<br />
<br />
<br /></div>
Anonymoushttp://www.blogger.com/profile/04664995477596294650noreply@blogger.com0tag:blogger.com,1999:blog-3992239225529976052.post-75440791636327812192014-06-22T02:03:00.004+05:302014-06-22T02:05:38.711+05:30How to check AWR interval and retention Settings<div dir="ltr" style="text-align: left;" trbidi="on">
<b>AWR interval and retention Settings</b><br />
<br />
The following query can be used to check the current settings for the AWR interval and AWR retention.<br />
The query returns the current AWR interval values in minutes.<br />
<br />
set pages 50000 lines 32767<br />
col snap_interval format a20<br />
col retention format a20<br />
col topnsql format a20<br />
select extract( day from snap_interval) *24*60+<br />
extract( hour from snap_interval) *60+<br />
extract( minute from snap_interval ) "Snapshot Interval",<br />
extract( day from retention) *24*60+<br />
extract( hour from retention) *60+<br />
extract( minute from retention ) "Retention Interval"<br />
from dba_hist_wr_control;<br />
<br />
<br />
Snapshot Interval Retention Interval<br />
----------------- ------------------</div>
Anonymoushttp://www.blogger.com/profile/04664995477596294650noreply@blogger.com0tag:blogger.com,1999:blog-3992239225529976052.post-52387815179015670202014-06-22T01:59:00.005+05:302014-06-22T01:59:38.119+05:30Database health checks in Oracle<div dir="ltr" style="text-align: left;" trbidi="on">
Performing Database health checks, when there is an issue reported by Application users.<br />
<br />
1. Check the Database details<br />
2. Monitor the consumption of resources<br />
3. Check the Alert Log<br />
4. Check Listener log<br />
5. Check Filesystem space Usage<br />
6. Generate AWR Report<br />
7. Generate ADDM Report<br />
8. Finding Locks,Blocker Session and Waiting sessions in a oracle database<br />
9. Check for alerts in OEM<br />
<br />
1. Check the Database details :-<br />
=============================<br />
set pages 9999 lines 300<br />
col OPEN_MODE for a10<br />
col HOST_NAME for a30<br />
select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;<br />
<br />
For RAC:<br />
-------<br />
set pages 9999 lines 300<br />
col OPEN_MODE for a10<br />
col HOST_NAME for a30<br />
select INST_ID,INSTANCE_NAME, name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;<br />
<br />
<br />
2. Monitor the consumption of resources :-<br />
=======================================<br />
select * from v$resource_limit where resource_name in ('processes','sessions');<br />
<br />
The v$session views shows current sessions (which change rapidly),<br />
while the v$resource_limit shows the current and maximum global resource utilization for some system resources.<br />
<br />
<br />
3. Check the Alert Log :-<br />
======================<br />
$locate alert_<ORACLE_SID><br />
<br />
--- OR ---<br />
<br />
UNIX/Linux command to locate the alert log file<br />
-----------------------------------------------<br />
<br />
$ find / -name 'alert_*.log' 2> /dev/null<br />
<br />
vi <alert_log_location_of_the_above_output><br />
shift+g<br />
?ORA- ---> press enter key<br />
press 'n' to check backwards/up side and 'N' for forward/down side search.<br />
<br />
:q! --and press enter, for exiting vi editor<br />
<br />
<br />
--- OR ---<br />
<br />
11G<br />
===<br />
$ sqlplus "/as sysdba"<br />
set pages 9999 lines 300<br />
col NAME for a15<br />
col VALUE for a60<br />
select name, value from v$diag_info where name = 'Diag Trace';<br />
<br />
On a server with multiple instances, each instance will have it's own background_dump_dest in $ORACLE_HOME/diag/$ORACLE_SID/trace directory<br />
<br />
Before 11G<br />
==========<br />
$ sqlplus "/as sysdba"<br />
set pages 9999 lines 300<br />
show parameter BACKGROUND_DUMP_DEST;<br />
<br />
On a server with multiple instances, each instance will have it's own background_dump_dest in $ORACLE_HOME/admin/$ORACLE_SID/bdump directory<br />
<br />
<br />
4. Check Listener log :-<br />
=====================<br />
$locate listener.log<br />
<br />
--- OR ---<br />
<br />
UNIX/Linux command to locate the listener log file<br />
--------------------------------------------------<br />
$ find / -name 'listener.log' 2> /dev/null<br />
vi <listener.log><br />
shift+g<br />
?TNS- ---> press enter key<br />
press 'n' to check backwords and 'N' for forword search.<br />
<br />
AND<br />
<br />
shift+g<br />
?error ---> press enter key<br />
press 'n' to check backwords and 'N' for forword search.<br />
<br />
:q! --and press enter, for exiting vi editor<br />
<br />
--- OR ---<br />
<br />
$lsnrctl status<br />
<br />
from the output you can get the listener log location (see the value for "Listener Log File" in the output).<br />
<br />
<br />
5. Check Filesystem space Usage :-<br />
===============================<br />
df -h (Linux / UNIX)<br />
<br />
df -g (AIX)<br />
<br />
6. Generate AWR Report :-<br />
======================<br />
Generate AWR report for current and before to compare<br />
<br />
SQL> @?/rdbms/admin/awrrpt.sql (For RAC, @?/rdbms/admin/awrrpti.sql - for each instance)<br />
<br />
If Required,<br />
SQL> @?/rdbms/admin/awrddrpt.sql ----> Produces Workload Repository Compare Periods Report<br />
<br />
<br />
7. Generate ADDM Report :-<br />
=======================<br />
Generate ADDM report for current and before to compare.<br />
<br />
ADDM report provides Findings and Recommendations to fix the issue.<br />
<br />
SQL> @?/rdbms/admin/addmrpt.sql (For RAC, @?/rdbms/admin/addmrpti.sql - for each instance)<br />
<br />
<br />
8. Finding Locks,Blocker Session and Waiting sessions in a oracle database :-<br />
========================================================================<br />
Select * from v$lock;<br />
<br />
Select * from gv_$lock; (For RAC)<br />
<br />
A fast way to check blocking/waiting situations<br />
-----------------------------------------------<br />
SELECT * FROM v$lock WHERE block > 0 OR request > 0;<br />
<br />
set pages 50000 lines 32767<br />
select object_name,s.inst_id,s.sid,s.serial#,p.spid,s.osuser,s.program,s.server,s.machine,s.status from gv$locked_object l,gv$session s,gv$process p,dba_objects o where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr;<br />
<br />
set pages 50000 lines 32767<br />
col OBJECT_NAME for a40<br />
col USERNAME for a10<br />
col LOCKED_MODE for a15<br />
col OBJECT_OWNER for a15<br />
col OS_USER_NAME for a12<br />
SELECT b.inst_id,b.session_id AS sid,NVL(b.oracle_username, '(oracle)') AS username,a.owner AS object_owner,a.object_name,<br />
Decode(b.locked_mode, 0, 'None',1, 'Null (NULL)',2, 'Row-S (SS)',3, 'Row-X (SX)',4, 'Share (S)',5, 'S/Row-X (SSX)',6, 'Exclusive (X)',<br />
b.locked_mode) locked_mode,b.os_user_name FROM dba_objects a, gv$locked_object b WHERE a.object_id = b.object_id ORDER BY 1, 2, 3, 4;<br />
<br />
Blocker Session and Waiting sessions<br />
====================================<br />
column Username format A15 column Sid format 9990 heading SID<br />
column Type format A4 column Lmode format 990 heading 'HELD'<br />
column Request format 990 heading 'REQ' column Id1 format 9999990<br />
column Id2 format 9999990 break on Id1 skip 1 dup<br />
SELECT SN.Username, M.Sid, M.Type,<br />
DECODE(M.Lmode, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row<br />
Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive',<br />
LTRIM(TO_CHAR(Lmode,'990'))) Lmode,<br />
DECODE(M.Request, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row<br />
Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive',<br />
LTRIM(TO_CHAR(M.Request, '990'))) Request,<br />
M.Id1, M.Id2<br />
FROM V$SESSION SN, V$LOCK M<br />
WHERE (SN.Sid = M.Sid and M.Request ! = 0)<br />
or (SN.Sid = M.Sid and M.Request = 0 and Lmode != 4 and (id1, id2)<br />
in (select S.Id1, S.Id2 from V$LOCK S where Request != 0 and S.Id1<br />
= M.Id1 and S.Id2 = M.Id2) ) order by Id1, Id2, M.Request;<br />
<br />
USERNAME SID TY LMODE REQUEST ID1 ID2<br />
---------------- ------- -- ------------- ------------- ---------- --------<br />
ORAPLAYERS 10 TX Exclusive None 123456 200<br />
ORAPLAYERS 100 TX None Exclusive 123456 200<br />
<br />
Session 10 is blocking(LMODE=Exclusive)<br />
<br />
Session 100 is waiting(REQUEST=Exclusive)<br />
<br />
The meaning of ID1 and ID2 depends on the lock TYPE.<br />
<br />
• We can see situations where a session is both a Blocker and a Waiter.<br />
<br />
• If there are only two sessions and both are Blockers and Waiters then we got a deadlock situation (which Oracle will solve automatically).<br />
<br />
<br />
To find waiters:<br />
---------------<br />
set pages 50000 lines 32767<br />
col LOCK_TYPE for a10<br />
col MODE_HELD for a10<br />
col MODE_REQUESTED for a10<br />
<br />
select * from dba_waiters;<br />
<br />
WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2<br />
--------------- --------------- --------- --------- -------------- -------- --------<br />
<br />
Blocking details:<br />
----------------<br />
set pages 50000 lines 32767<br />
select distinct s1.username || '@' || s1.machine || ' ( INST=' || s1.inst_id || ' SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( INST=' || s1.inst_id || ' SID=' || s2.sid || ' ) ' as blocking_status from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 and l1.inst_id = s1.inst_id;<br />
<br />
set pages 50000 lines 32767<br />
col BLOCKER for a20<br />
col BLOCKEE for a20<br />
select (select username from v$session where sid = a.sid ) blocker,a.sid, 'is blocking ',(select username from v$session where sid =b.sid) blockee,b.sid from v$lock a, v$lock b where a.block =1 and b.request > 0 and a.id1 = b.id1 and a.id2 = b.id2; <br />
<br />
BLOCKER SID 'ISBLOCKING' BLOCKEE SID<br />
------- ---------- ---------- ------- --------<br />
<br />
<br />
set pages 50000 lines 32767<br />
select blocking_session, sid, serial#, wait_class,seconds_in_wait, username, osuser, program, logon_time from v$session where blocking_session is not NULL order by 1;<br />
<br />
9. Check for alerts in OEM :-<br />
============================<br />
Login to Oracle Enterprise Manager with valid username and password<br />
click on "Alerts" tab<br />
then select the below tabs one by one to see the alerts generated<br />
Targets Down/Critical/Warning/Errors/<br />
<div>
<br /></div>
</div>
Anonymoushttp://www.blogger.com/profile/04664995477596294650noreply@blogger.com0tag:blogger.com,1999:blog-3992239225529976052.post-53768045426505431442014-06-22T01:55:00.001+05:302014-06-23T11:05:17.207+05:30How to check RMAN backup job status in Oracle using v$rman_backup_job_details<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div>
RMAN backup job details for 'n' number of days:-</div>
<div>
=========================================</div>
<div>
<b>Monitoring RMAN backup status using v$rman_backup_job_details and v$rman_status.</b></div>
<div>
<br /></div>
<div>
Note : - Enter the number of days required for status report, for 1 day backup status report provide input as '1'.</div>
<div>
<br />
<b>RMAN backup status using v$rman_backup_job_details :-</b><br />
set pages 9999 lines 500<br />
col INSTANCE for a9<br />
col ELAPSED for a30<br />
SELECT ( SELECT instance_name FROM v$instance)<br />
|| ' '<br />
|| ( SELECT instance_number FROM v$instance)<br />
instance,<br />
-- TO_CHAR (start_time, 'YYYY-MM-DD HH24:MI') start_time,<br />
to_date (start_time, 'DD-MM-YYYY HH24:MI:SS') start_time,<br />
TO_CHAR (output_bytes / 1048576, '999,999,999.9') output_mb,<br />
TO_CHAR (output_bytes_per_sec / 1048576, '999,999.9') output_mb_per_sec,<br />
time_taken_display elapsed,input_type,status<br />
FROM v$rman_backup_job_details<br />
where start_time >= sysdate - &NUMBER_OF_DAYS <br />
ORDER BY start_time<br />
/<br />
<br />
<br />
<b>RMAN backup status using v$rman_backup_job_details , v$rman_status:-</b></div>
<div>
<div>
set pages 9999 lines 500</div>
<div>
set numformat 99999.99</div>
<div>
set trim on </div>
<div>
set trims on</div>
<div>
alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';</div>
<div>
col INSTANCE for a9</div>
<div>
col status for a22</div>
<div>
col COMMAND_ID for a20</div>
<div>
col INPUT_TYPE for a10</div>
<div>
col OUTPUT_DEVICE_TYPE for a10</div>
<div>
col OUTPUT_BYTES_PER_SEC_DISPLAY for a9</div>
<div>
col status heading "BACKUP|STATUS" </div>
<div>
col COMMAND_ID heading "BACKUP NAME" </div>
<div>
col STARTED_TIME heading "START TIME" </div>
<div>
COL END_TIME heading "END TIME" </div>
<div>
col ELAPSED_TIME heading "MINUTES | TAKEN" </div>
<div>
col INPUT_TYPE heading "INPUT|TYPE" </div>
<div>
col OUTPUT_DEVICE_TYPE heading "OUTPUT|DEVICES" </div>
<div>
col INPUT_SIZE heading "INPUT SIZE|GB"</div>
<div>
col OUTPUT_SIZE heading "OUTPUT SIZE|GB" </div>
<div>
col OUTPUT_BYTES_PER_SEC_DISPLAY heading "OUTPUT | RATE|(PER SEC)"</div>
<div>
</div>
<div>
SELECT (SELECT instance_name FROM v$instance) || ' ' || (SELECT instance_number FROM v$instance) instance,rs.sid,</div>
<div>
rj.COMMAND_ID,</div>
<div>
rj.STATUS,</div>
<div>
max(rj.START_TIME) STARTED_TIME, </div>
<div>
rj.END_TIME,</div>
<div>
rj.ELAPSED_SECONDS/60 ELAPSED_TIME,</div>
<div>
rj.INPUT_TYPE,</div>
<div>
rj.OUTPUT_DEVICE_TYPE,</div>
<div>
rj.INPUT_BYTES/1024/1024/1024 INPUT_SIZE, </div>
<div>
rj.OUTPUT_BYTES/1024/1024/1024 OUTPUT_SIZE,</div>
<div>
rj.OUTPUT_BYTES_PER_SEC_DISPLAY</div>
<div>
from v$rman_backup_job_details rj, v$rman_status rs</div>
<div>
where rj.COMMAND_ID=rs.COMMAND_ID</div>
<div>
group by rs.sid,rj.COMMAND_ID,rj.STATUS,rj.START_TIME,rj.END_TIME,rj.ELAPSED_SECONDS,rj.INPUT_TYPE,rj.OUTPUT_DEVICE_TYPE,rj.INPUT_BYTES,rj.OUTPUT_BYTES,rj.OUTPUT_BYTES_PER_SEC_DISPLAY </div>
<div>
having max(rj.START_TIME) > sysdate-&NUMBER_OF_DAYS order by rj.START_TIME desc</div>
<div>
/</div>
</div>
<div>
<br /></div>
<div>
<div>
BACKUP MINUTES INPUT OUTPUT INPUT SIZE OUTPUT SIZE OUTPUT RATE</div>
<div>
INSTANCE SID BACKUP NAME STATUS START TIME END TIME TAKEN TYPE DEVICES GB GB (PER SEC)</div>
<div>
--------- --- -------------------- ---------------------- ------------------- ------------------- --------- ---------- ---------- ---------- ----------- ------------</div>
<div>
<br /></div>
</div>
<div>
<br />
<b>To get the job details for a specific backup job, use the following query:-</b><br />
<br />
set lines 220<br />
set pages 1000<br />
col cf for 9,999<br />
col df for 9,999<br />
col elapsed_seconds heading "ELAPSED|SECONDS"<br />
col i0 for 9,999<br />
col i1 for 9,999<br />
col l for 9,999<br />
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"<br />
col session_recid for 999999 heading "SESSION|RECID"<br />
col session_stamp for 99999999999 heading "SESSION|STAMP"<br />
col status for a10 trunc<br />
col time_taken_display for a10 heading "TIME|TAKEN"<br />
col output_instance for 9999 heading "OUT|INST"<br />
select<br />
j.session_recid, j.session_stamp,<br />
to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,<br />
to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,<br />
(j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,<br />
decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',<br />
3, 'Tuesday', 4, 'Wednesday',<br />
5, 'Thursday', 6, 'Friday',<br />
7, 'Saturday') dow,<br />
j.elapsed_seconds, j.time_taken_display,<br />
x.cf, x.df, x.i0, x.i1, x.l,<br />
ro.inst_id output_instance<br />
from V$RMAN_BACKUP_JOB_DETAILS j<br />
left outer join (select<br />
d.session_recid, d.session_stamp,<br />
sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,<br />
sum(case when d.controlfile_included = 'NO'<br />
and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,<br />
sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,<br />
sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,<br />
sum(case when d.backup_type = 'L' then d.pieces else 0 end) L<br />
from<br />
V$BACKUP_SET_DETAILS d<br />
join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count<br />
where s.input_file_scan_only = 'NO'<br />
group by d.session_recid, d.session_stamp) x<br />
on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp<br />
left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id<br />
from GV$RMAN_OUTPUT o<br />
group by o.session_recid, o.session_stamp)<br />
ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp<br />
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS<br />
order by j.start_time;<br />
<br />
Where,<br />
<br />
CF: Number of controlfile backups included in the backup set <br />
<br />
DF: Number of datafile full backups included in the backup set <br />
<br />
I0: Number of datafile incremental level-0 backups included in the backup set <br />
<br />
I1: Number of datafile incremental level-1 backups included in the backup set <br />
<br />
L: Number of archived log backups included in the backup set<br />
<br />
<b>Backup set details : -</b><br />
<br />
To get the Backup set details for a specific backup job, identified by the
(SESSION_RECID, SESSION_STAMP) pair, use the following query:<br />
<br />
set lines 220<br />
set pages 1000<br />
col backup_type for a4 heading "TYPE"<br />
col controlfile_included heading "CF?"<br />
col incremental_level heading "INCR LVL"<br />
col pieces for 999 heading "PCS"<br />
col elapsed_seconds heading "ELAPSED|SECONDS"<br />
col device_type for a10 trunc heading "DEVICE|TYPE"<br />
col compressed for a4 heading "ZIP?"<br />
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"<br />
col input_file_scan_only for a4 heading "SCAN|ONLY"<br />
select<br />
d.bs_key, d.backup_type, d.controlfile_included, d.incremental_level, d.pieces,<br />
to_char(d.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,<br />
to_char(d.completion_time, 'yyyy-mm-dd hh24:mi:ss') completion_time,<br />
d.elapsed_seconds, d.device_type, d.compressed, (d.output_bytes/1024/1024) output_mbytes, s.input_file_scan_only<br />
from V$BACKUP_SET_DETAILS d<br />
join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count<br />
where session_recid = &SESSION_RECID<br />
and session_stamp = &SESSION_STAMP<br />
order by d.start_time;<br />
<br />
<b>Backup job output :-</b><br />
<br />
To get the Backup job output for a specific backup job, identified by the (SESSION_RECID, SESSION_STAMP) pair, use the following query:<br />
<br />
set lines 200<br />
set pages 1000<br />
select output<br />
from GV$RMAN_OUTPUT<br />
where session_recid = &SESSION_RECID<br />
and session_stamp = &SESSION_STAMP<br />
order by recid;<br />
<br /></div>
</div>
Anonymoushttp://www.blogger.com/profile/04664995477596294650noreply@blogger.com2tag:blogger.com,1999:blog-3992239225529976052.post-91651854567510981582014-05-04T13:06:00.002+05:302014-05-04T13:06:41.066+05:30How to compress Listener log file in Linux<div dir="ltr" style="text-align: left;" trbidi="on">
How to compress files in Linux<br />
<div>
<br /></div>
<div>
How to compress Listener log file in Linux</div>
<div>
<br /></div>
<div>
<b>Step:-1</b></div>
<div>
<b>-------</b></div>
<div>
cd /u01/app/oracle/10.2.0/db_1/network/admin --- listener log location</div>
<div>
du -sh *|sort -n</div>
<div>
du -sh listener.log</div>
<div>
<br /></div>
<div>
<b>Step:-2</b></div>
<div>
<b>-------</b></div>
<div>
vi filecompress.sh</div>
<div>
cd /u01/app/oracle/10.2.0/db_1/network/admin --- listener log location</div>
<div>
cp listener.log listener_currentdate.log</div>
<div>
cat /dev/null > listener.log</div>
<div>
<br /></div>
<div>
-- press esc key</div>
<div>
:wq</div>
<div>
<br /></div>
<div>
Run the filecompress.sh in nohup</div>
<div>
---------------------------------</div>
<div>
nohup sh -x filecompress.sh > filecompress.log 2>> filecompress.err &</div>
<div>
<br /></div>
<div>
<b>Step:-3</b></div>
<div>
<b>-------</b></div>
<div>
gzip -9 listener_currentdate.log</div>
<div>
du -sh *.gz</div>
<div>
--- OR ---</div>
<div>
tar -zcvf listener_currentdate.tar.gz listener_currentdate.log</div>
<div>
du -sh *.tar.gz</div>
<div>
<br /></div>
<div>
<b>Note:-</b></div>
<div>
<b>====</b></div>
<div>
Listener will be available in the above process and no data loss of listener log file.</div>
<div>
<br /></div>
<div>
For Example, 3 GB file will be compressed to 100M (approx).</div>
</div>
Anonymoushttp://www.blogger.com/profile/04664995477596294650noreply@blogger.com0tag:blogger.com,1999:blog-3992239225529976052.post-87227542863760870452014-02-22T12:05:00.001+05:302014-02-22T12:05:49.798+05:30Oracle DBA Performance Tuning Scripts<div dir="ltr" style="text-align: left;" trbidi="on">
<b>Performance Tuning Scripts</b><br /><br />Listed below are some SQL queries which I find particularly useful for performance tuning. These are based on the Active Session History v$active_session_history<br />View to get a current perspective of performance and the DBA_HIST_* AWR history tables for obtaining performance data pertaining to a period of time in the past.<br /><br /><b>Top Recent Wait Events</b><br /><br />set pages 50000 lines 32767<br />col EVENT format a60 <br /><br />select * from (<br />select active_session_history.event,<br />sum(active_session_history.wait_time +<br />active_session_history.time_waited) ttl_wait_time<br />from v$active_session_history active_session_history<br />where active_session_history.event is not null<br />group by active_session_history.event<br />order by 2 desc)<br />where rownum < 6<br />/<br /><br /><b>Top Wait Events Since Instance Startup</b><br /><br />set pages 50000 lines 32767<br />col event format a60<br /><br />select event, total_waits, time_waited<br />from v$system_event e, v$event_name n<br />where n.event_id = e.event_id<br />and n.wait_class !='Idle'<br />and n.wait_class = (select wait_class from v$session_wait_class<br />where wait_class !='Idle'<br />group by wait_class having<br />sum(time_waited) = (select max(sum(time_waited)) from v$session_wait_class<br />where wait_class !='Idle'<br />group by (wait_class)))<br />order by 3<br />/<br /><br /><b>List Of Users Currently Waiting</b><br />set pages 50000 lines 32767<br />col username format a12<br />col sid format 9999<br />col state format a15<br />col event format a50<br />col wait_time format 99999999<br />set pagesize 100<br />set linesize 120<br /><br />select s.sid, s.username, se.event, se.state, se.wait_time<br />from v$session s, v$session_wait se<br />where s.sid=se.sid<br />and se.event not like 'SQL*Net%'<br />and se.event not like '%rdbms%'<br />and s.username is not null<br />order by se.wait_time<br />/<br /><b><br />Find The Main Database Wait Events In A Particular Time Interval</b><br /><br />First determine the snapshot id values for the period in question.<br /><br />In this example we need to find the SNAP_ID for the period 10 PM to 11 PM on the 14th of November, 2013.<br /><br />set pages 50000 lines 32767<br /><br />select snap_id,begin_interval_time,end_interval_time<br />from dba_hist_snapshot<br />where to_char(begin_interval_time,'DD-MON-YYYY')='14-NOV-2013'<br />and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23;<br />set verify off<br />select * from (<br />select active_session_history.event,<br />sum(active_session_history.wait_time +<br />active_session_history.time_waited) ttl_wait_time<br />from dba_hist_active_sess_history active_session_history<br />where event is not null<br />and SNAP_ID between &ssnapid and &esnapid<br />group by active_session_history.event<br />order by 2 desc)<br />where rownum<br />/<br /><b><br />Top CPU Consuming SQL During A Certain Time Period</b><br /><br />Note – in this case we are finding the Top 5 CPU intensive SQL statements executed between 9.00 AM and 11.00 AM<br /><br />set pages 50000 lines 32767<br /><br />select * from (<br />select<br />SQL_ID,<br />sum(CPU_TIME_DELTA),<br />sum(DISK_READS_DELTA),<br />count(*)<br />from<br />DBA_HIST_SQLSTAT a, dba_hist_snapshot s<br />where<br />s.snap_id = a.snap_id<br />and s.begin_interval_time > sysdate -1<br />and EXTRACT(HOUR FROM S.END_INTERVAL_TIME) between 9 and 11<br />group by<br />SQL_ID<br />order by<br />sum(CPU_TIME_DELTA) desc)<br />where rownum<br />/<br /><br /><b>Which Database Objects Experienced the Most Number of Waits in the Past One Hour</b><br /><br />set pages 50000 lines 32767<br />col event format a40<br />col object_name format a40<br /><br />select * from <br />(<br />select dba_objects.object_name,<br />dba_objects.object_type,<br />active_session_history.event,<br />sum(active_session_history.wait_time +<br />active_session_history.time_waited) ttl_wait_time<br />from v$active_session_history active_session_history,<br />dba_objects<br />where <br />active_session_history.sample_time between sysdate - 1/24 and sysdate<br />and active_session_history.current_obj# = dba_objects.object_id<br />group by dba_objects.object_name, dba_objects.object_type, active_session_history.event<br />order by 4 desc)<br />where rownum < 6<br />/<br /><br /><b>Top Segments ordered by Physical Reads</b><br /><br />set pages 50000 lines 32767<br />col segment_name format a20<br />col owner format a10 <br /><br />select segment_name,object_type,total_physical_reads<br />from ( select owner||'.'||object_name as segment_name,object_type,<br />value as total_physical_reads<br />from v$segment_statistics<br />where statistic_name in ('physical reads')<br />order by total_physical_reads desc)<br />where rownum;<br /><b><br />Top 5 SQL statements in the past one hour</b><br /><br />set pages 50000 lines 32767<br /><br />select * from (<br />select active_session_history.sql_id,<br />dba_users.username,<br />sqlarea.sql_text,<br />sum(active_session_history.wait_time +<br />active_session_history.time_waited) ttl_wait_time<br />from v$active_session_history active_session_history,<br />v$sqlarea sqlarea,<br />dba_users<br />where <br />active_session_history.sample_time between sysdate - 1/24 and sysdate<br />and active_session_history.sql_id = sqlarea.sql_id<br />and active_session_history.user_id = dba_users.user_id<br />group by active_session_history.sql_id,sqlarea.sql_text, dba_users.username<br />order by 4 desc )<br />where rownum<br />/<br /><b><br />SQL with the highest I/O in the past one day</b><br /><br />set pages 50000 lines 32767<br /><br />select * from <br />(<br />SELECT /*+LEADING(x h) USE_NL(h)*/ <br />h.sql_id,SUM(10) ash_secs<br />FROM dba_hist_snapshot x,dba_hist_active_sess_history h<br />WHERE x.begin_interval_time > sysdate -1<br />AND h.SNAP_id = X.SNAP_id<br />AND h.dbid = x.dbid<br />AND h.instance_number = x.instance_number<br />AND h.event in ('db file sequential read','db file scattered read')<br />GROUP BY h.sql_id<br />ORDER BY ash_secs desc )<br />where rownum<br />/<br /><br /><b>Top CPU consuming queries since past one day</b><br /><br />set pages 50000 lines 32767<br /><br />select * from (<br />select SQL_ID, sum(CPU_TIME_DELTA),sum(DISK_READS_DELTA),count(*)<br />from DBA_HIST_SQLSTAT a, dba_hist_snapshot s<br />where<br />s.snap_id = a.snap_id<br />and s.begin_interval_time > sysdate -1<br />group by SQL_ID<br />order by sum(CPU_TIME_DELTA) desc)<br />where rownum<br />/<b><br /><br />Find what the top SQL was at a particular reported time of day</b><br /><br />First determine the snapshot id values for the period in question.<br /><br />In thos example we need to find the SNAP_ID for the period 10 PM to 11 PM on the 14th of November, 2013.<br /><br />set pages 50000 lines 32767<br /><br />select snap_id,begin_interval_time,end_interval_time<br />from dba_hist_snapshot<br />where to_char(begin_interval_time,'DD-MON-YYYY')='14-NOV-2013'<br />and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23;<br />select * from<br />(<br />select<br />sql.sql_id c1,<br />sql.buffer_gets_delta c2,<br />sql.disk_reads_delta c3,<br />sql.iowait_delta c4<br />from<br />dba_hist_sqlstat sql,dba_hist_snapshot s<br />where<br />s.snap_id = sql.snap_id<br />and<br />s.snap_id= &snapid<br />order by<br />c3 desc)<br />where rownum < 6<br />/<b><br /><br />Analyse a particular SQL ID and see the trends for the past day</b><br /><br />set pages 50000 lines 32767<br /><br />select<br />s.snap_id,<br />to_char(s.begin_interval_time,'HH24:MI') c1,<br />sql.executions_delta c2,<br />sql.buffer_gets_delta c3,<br />sql.disk_reads_delta c4,<br />sql.iowait_delta c5,<br />sql.cpu_time_delta c6,<br />sql.elapsed_time_delta c7<br />from<br />dba_hist_sqlstat sql,<br />dba_hist_snapshot s<br />where<br />s.snap_id = sql.snap_id<br />and s.begin_interval_time > sysdate -1<br />and<br />sql.sql_id='&sqlid'<br />order by c7<br />/<br /><br /><b>Do we have multiple plan hash values for the same SQL ID – in that case may be changed plan is causing bad performance</b><br /><br />set pages 50000 lines 32767<br /><br />select <br />SQL_ID <br />, PLAN_HASH_VALUE <br />, sum(EXECUTIONS_DELTA) EXECUTIONS<br />, sum(ROWS_PROCESSED_DELTA) CROWS<br />, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS<br />, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60) ELA_MINS<br />from DBA_HIST_SQLSTAT <br />where SQL_ID in (<br />'&sqlid') <br />group by SQL_ID , PLAN_HASH_VALUE<br />order by SQL_ID, CPU_MINS<br />/<br /><b><br />Top 5 Queries for past week based on ADDM recommendations</b><br /><br />/*<br />Top 10 SQL_ID's for the last 7 days as identified by ADDM from DBA_ADVISOR_RECOMMENDATIONS and dba_advisor_log<br />*/<br /><br />set pages 50000 lines 32767<br />col SQL_ID form a16<br />col Benefit form 9999999999999<br /><br />select * from (<br />select b.ATTR1 as SQL_ID, max(a.BENEFIT) as "Benefit" <br />from DBA_ADVISOR_RECOMMENDATIONS a, DBA_ADVISOR_OBJECTS b <br />where a.REC_ID = b.OBJECT_ID<br />and a.TASK_ID = b.TASK_ID<br />and a.TASK_ID in (select distinct b.task_id<br />from dba_hist_snapshot a, dba_advisor_tasks b, dba_advisor_log l<br />where a.begin_interval_time > sysdate - 7 <br />and a.dbid = (select dbid from v$database) <br />and a.INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance) <br />and to_char(a.begin_interval_time, 'yyyymmddHH24') = to_char(b.created, 'yyyymmddHH24') <br />and b.advisor_name = 'ADDM' <br />and b.task_id = l.task_id <br />and l.status = 'COMPLETED') <br />and length(b.ATTR4) > 1 group by b.ATTR1<br />order by max(a.BENEFIT) desc) where rownum < 6<br />/<br /><b><br />Reference:-</b><br /><a href="http://gavinsoorma.com/2012/11/ash-and-awr-performance-tuning-scripts/">http://gavinsoorma.com/2012/11/ash-and-awr-performance-tuning-scripts/</a><br /><br /></div>
Anonymoushttp://www.blogger.com/profile/04664995477596294650noreply@blogger.com0tag:blogger.com,1999:blog-3992239225529976052.post-12705682355759562772014-02-21T01:22:00.004+05:302014-02-21T01:22:31.445+05:30vi Editor Commands<div dir="ltr" style="text-align: left;" trbidi="on">
<b>vi Editor Commands</b><br /><br />$ vi <filename><br /><br /><b>Option ==> Action</b><br />vi ==> Starts editing session in memory.<br />vi ==> Starts session and opens the specified file.<br />vi * ==> Opens first file that matches the wildcard pattern. Use :n to navigate to the next matched file.<br />view ==> Opens file in read-only mode.<br />vi -R ==> Opens file in read-only mode.<br />vi -r ==> Recovers file and recent edits after abnormal abort from editing session (like a system crash).<br />vi +n ==> Opens file at specified line number n.<br />vi + ==> Opens file at the last line.<br />vi +/ ==> Opens file at first occurrence of specified string pattern.<br /><br /><b>Common Techniques to Enter vi Insert Mode:<br />Enter Insert Command ==> Action</b><br />i ==> Insert text in front of the cursor.<br />a ==> Insert text after the cursor.<br />I ==> Insert text at the beginning of the line.<br />A ==> Insert text at the end of the line.<br />o ==> Insert text below the current line.<br />O ==> Insert text above the current line.<br /><br /><b>Useful vi Exit Commands<br />Exit Command ==> Action</b><br />:wq ==> Save and exit.<br />ZZ ==> Save and exit.<br />:x ==> Save and exit.<br />:w ==> Save the current edits without exiting.<br />:w! ==> Override file protections and save.<br />:q ==> Exit the file.<br />:q! ==> Exit without saving.<br />:n ==> Edit next file.<br />:e! ==> Return to previously saved version.<br /><br /><b>Common Navigation Commands<br />Command ==> Action</b><br />j (or down arrow) ==> Move down a line.<br />k (or up arrow) ==> Move up a line.<br />h (or left arrow) ==> Move one character left.<br />l (or right arrow) ==> Move one character right.<br />Ctrl+f (or Page Down) ==> Scroll down one screen.<br />Ctrl+b (or Page Up) ==> Scroll up one screen.<br />1G ==> Go to first line in file.<br />G ==> Go to last line in file.<br />nG ==> Go to n line number.<br />H ==> Go to top of screen.<br />L ==> Go to bottom of screen.<br />w ==> Move one word forward.<br />b ==> Move one word backward.<br />0 ==> Go to start of line.<br />$ ==> Go to end of line.<br /><br /><b>Common Options for Copying, Deleting, and Pasting Text<br />Option ==> Action</b><br />yy ==> Yank (copy) the current line.<br />nyy ==> Yank (copy) n number of lines.<br />p ==> Put yanked line(s) below the cursor.<br />P ==> Put yanked line(s) above the cursor.<br />x ==> Delete the character that the cursor is on.<br />X ==> Delete the character to the left of the cursor.<br />dw ==> Delete the word the cursor is currently on.<br />dd ==> Delete current line of text.<br />ndd ==> Delete n lines of text<br />D ==> Delete to the end of the current line.<br /><br /><b>Common Options for Changing Text<br />Option ==> Action</b><br />r ==> Replace the character that the curser is on with the next character you type.<br />~ ==> Change the case of a character.<br />cc ==> Delete the current line and insert text.<br />C ==> Delete to the end of the line and insert text.<br />c$ ==> Delete to the end of the line and insert text.<br />cw ==> Delete to the end of the word and insert text.<br />R ==> Type over the characters in the current line.<br />s ==> Delete the current character and insert text.<br />S ==> Delete the current line and insert text.<br /><br /><b>Common Options for Text Searching<br />Option ==> Action</b><br />/ ==> Search forward for a string.<br />? ==> Search backward for a string.<br />n ==> Repeat the search forward.<br />N ==> Repeat the search backward.<br />f ==> Search forward for a character in the current line.<br />F ==> Search backward for a character in the current line.<br /><br />:set number ==> Displaying Line Numbers<br /><br />u ==> Undoing a Command</div>
Anonymoushttp://www.blogger.com/profile/04664995477596294650noreply@blogger.com0tag:blogger.com,1999:blog-3992239225529976052.post-38936891726473371832014-02-20T11:47:00.000+05:302014-02-20T12:19:36.402+05:30Oracle DBA Interview Questions<div dir="ltr" style="text-align: left;" trbidi="on">
Oracle DBA Interview Questions <br />
<br />
What are the versions you are working on<br />
Database<br />
RAC database<br />
Dataguard<br />
<br />
What is the recent patch you applied?<br />
What happens when you run Root.sh during installation<br />
<br />
RAC Architechture?<br />
<br />
What is Node Eviction? In what scenarios the
node be evicted? Which background process is responsible for node
eviction and fixing evicted node and bringing into cluster? How do you
troubleshoot Node Eviction?<br />
<br />
What is cache fusion? What is responsible for cache fusion?<br />
<br />
How will you take VD backup in 11gR1 and 11gR2?<br />
<br />
How to check VD backup location<br />
<br />
How to check OCR backup location<br />
<br />
Starup Restrict vs Startup Upgrade<br />
<br />
Difference between 11g and 10g ?<br />
<br />
Difference between sga_target and memory_target?<br />
<br />
Difference between 11gR2,11gR1,10gR2 and 10gR1 RAC ?<br />
<br />
Materialized views and its types<br />
<br />
Database upgrade takes 1hr normally and client want to upgrade without down time.<br />
How can you achieve that in a RAC and Standalone.How can we upgrade with only 5 min downtime instead of 1 hour, as per client requirement? <br />
<br />
Can exp be faster than expdp? If yes? In which scenario it happens?<br />
<br />
Exp vs Datapump<br />
<br />
Datapump vs RMAN<br />
<br />
If RMAN backs up only changed blocks, what happens if the database has some read only ts and I have taken level 0 backup. Will the readonly ts backs up or not as there are no changed blocks in them?<br />
<br />
What is Dataguard?<br />
<br />
Logical vs Physical Standby databases?<br />
<br />
Protection modes in Dataguard? What Protection mode do you have in your project?<br />
<br />
DB_LINK? Syntax?<br />
<br />
DB_LINK VS NETWORK_LINK in Datapump<br />
<br />
what happens internally when you export or import in Datapump<br />
<br />
How to check whether DATAPUMP dump File is corrupted or not?<br /><br />How to see the DDL contents of an export dump file in Data Pump import and Traditional import?<br />
<br />
Datapump Import failed after running for long time and even resumable time also execeeded? What action will you take?<br />
<br />
Datapump Import hanged after running for long time? What action will you take?<br />
<br />
How will you stop and start an import in datapump.<br />
<br />
From which view you will find out datapump jobs?<br />
<br />
Tell me some issues you faced with Datapump and how did you fix them?<br />
<br />
Tell me the toughest issue you faced with Dataguard and how did you fix it?<br />
<br />
Tell me the toughest issue you faced with RAC and how did you fix it?<br />
<br />
Tell me the toughest issue you faced in Performance Tuning and how did you fix it?<br />
<br />
CPU VS PSU Patching? What patching you are implementing in your project?<br />
<br />
What is the full form of Opatch<br />
<br />
opatch apply vs napply<br />
<br />
Pre requirement for upgrade to 11g from 10g.<br />
<br />
Steps for database cloning using RMAN<br />
<br />
Steps for DR rebuild<br />
<br />
<br />
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br />
<br />
Temp ts is 100% full,adding space still filling up, we cant add more space to it, what fix will you apply?<br />
<br />
How to make RMAN backups run faster?<br />
<br />
What is block change tracking? How does it work?<br />
<br />
Apps team complained database is performing slow? How will you confirm the slowness being a DBA ? What actions will you perform?<br />
<br />
Apps team complained database performence is low compared to last week? What actions will you take?<br />
<br />
Which columns will you check in active_session_history<br />
<br />
Write query for populating Active sessions excluding background processes?<br />
<br />
Fast failover in DG? Is Fast Failover there in your environment?<br />
<br />
What is a Scan listener? How it works? </div>
Anonymoushttp://www.blogger.com/profile/04664995477596294650noreply@blogger.com0tag:blogger.com,1999:blog-3992239225529976052.post-37451256563892916142013-12-19T00:22:00.001+05:302013-12-19T00:56:06.754+05:30Oracle RAC Load balancing and Failover <div dir="ltr" style="text-align: left;" trbidi="on">
<b>LOAD BALANCING in RAC:- </b><br />
The Oracle RAC system can distribute the load over many nodes this feature called as load balancing.<br />
<br />
There are two methods of load balancing<br />
<b>1.Client load balancing<br />2.Server load balancing</b><br />
<br />
<b>1.Client Load Balancing</b><br />
Client Load Balancing distributes new connections among Oracle RAC nodes so that no one server is overloaded with connection requests and it is configured at net service name level by providing multiple descriptions in a description list or multiple addresses in an address list. For example, if connection fails over to another node in case of failure, the client load balancing ensures that the redirected connections are distributed among the other nodes in the RAC.<br />
<br />
Configure Client-side connect-time load balancing by setting <b>LOAD_BALANCE=ON</b> in the corresponding client side TNS entry.<br />
<br />
TESTRAC =<br />
(DESCRIPTION =<br />
(ADDRESS_LIST=<br />
<b>(LOAD_BALANCE = ON)</b><br />
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC1-VIP)(PORT = 1521))<br />
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC2-VIP)(PORT = 1521))<br />
)<br />
(CONNECT_DATA = (SERVICE_NAME = testdb.selectstarfrom.com))<br />
)<br />
<br />
<b>2.Server Load Balancing</b><br />
Server Load Balancing distributes processing workload among Oracle RAC nodes. It divides the connection load evenly between all available listeners and distributes new user session connection requests to the least loaded listener(s) based on the total number of sessions which are already connected. Each listener communicates with the other listener(s) via each database instance’s PMON process.<br />
<br />
Configure Server-side connect-time load balancing feature by setting <b>REMOTE_LISTENERS</b> initialization parameter of each instance to a TNS name that describes list of all available listeners.<br />
<br />
TESTRAC_LISTENERS =<br />
(DESCRIPTION =<br />
(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC1)(PORT = 1521)))<br />
(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC2)(PORT = 1521))))<br />
)<br />
<br />
Set *.remote_listener= TESTRAC_LISTENERS’ initialization parameter in the database’s shared SPFILE and add TESTRAC_LISTENERS’ entry to the TNSNAMES.ORA file in the Oracle Home of each node in the cluster.<br />
<br />
Once you configure Server-side connect-time load balancing, each database’s PMON process will automatically register the database with the database’s local listener as well as cross-register the database with the listeners on all other nodes in the cluster. Now the nodes themselves decide which node is least busy, and then will connect the client to that node.<br />
<br />
-------------------------------------------------------------------------------------------------------------------------------------------------------------<br />
<b><br />FAILOVER in RAC:-</b><br />
The Oracle RAC system can protect against failures caused by O/S or server crashes or hardware failures. When a node failure occurs in RAC system, the connection attempts can fail over to other surviving nodes in the cluster this feature called as Failover.<br />
<br />
There are two methods of failover<br />
<b>1. Connection Failover<br />2. Transparent Application Failover (TAF)</b><br />
<br />
<b>1. Connection Failover</b><br />
If a connection failure occurs at connect time, the application failover the connection to another active node in the cluster. This feature enables client to connect to another listener if the initial connection to the first listener fails.<br />
<br />
Enable client-side connect-time Failover by setting <b>FAILOVER=ON</b> in the corresponding client side TNS entry.<br />
<br />
TESTRAC =<br />
(DESCRIPTION =<br />
(ADDRESS_LIST=<br />
<b>(LOAD_BALANCE = ON)<br />(FAILOVER = ON)</b><br />
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC1-VIP)(PORT = 1521))<br />
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC2-VIP)(PORT = 1521))<br />
)<br />
(CONNECT_DATA = (SERVICE_NAME = testdb.selectstarfrom.com))<br />
)<br />
<br />
If LOAD_BALANCE is set to on then clients randomly attempt connections to any nodes. If client made connection attempt to a down node, the client needs to wait until it receives the information that the node is not accessible before trying alternate address in ADDRESS_LIST.<br />
<br />
<b>2. Transparent Application Failover (TAF)</b><br />
If connection failure occurs after a connection is established, the connection fails over to other surviving nodes. Any uncommitted transactions are rolled back and server side program variables and session properties will be lost. In some case the select statements automatically re-executed on the new connection with the cursor positioned on the row on which it was positioned prior to the failover.<br />
<br />
TESTRAC =<br />
(DESCRIPTION =<br />
<b>(LOAD_BALANCE = ON)<br />(FAILOVER = ON)</b><br />
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC1-VIP)(PORT = 1521))<br />
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC1-VIP)(PORT = 1521))<br />
(CONNECT_DATA =<br />
(SERVICE_NAME = testdb.selectstarfrom.com)<br />
<b>(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5))</b><br />
)<br />
)<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjONvvF8uHDdyUBo7QJa80HVXp_jY1sQWiIiStRv1hpTqXy6CBSqTm3yjHAlVxk6xGoh7DklJtbioUFIhskPQTjfD8l3rdfLA2ZNbzoZf5Qpk45XcGDWJw18qk3rB1-MyzCChC0ZXfSMOYG/s1600/failover.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjONvvF8uHDdyUBo7QJa80HVXp_jY1sQWiIiStRv1hpTqXy6CBSqTm3yjHAlVxk6xGoh7DklJtbioUFIhskPQTjfD8l3rdfLA2ZNbzoZf5Qpk45XcGDWJw18qk3rB1-MyzCChC0ZXfSMOYG/s1600/failover.JPG" /></a></div>
</div>
Anonymoushttp://www.blogger.com/profile/04664995477596294650noreply@blogger.com0tag:blogger.com,1999:blog-3992239225529976052.post-42683471960934586422013-12-04T23:38:00.001+05:302013-12-04T23:38:41.733+05:30Oracle APPS DBA Interview Questions and Answers - Beginners 11i<div dir="ltr" style="text-align: left;" trbidi="on">
<b>Oracle APPS DBA Interview Questions and Answers </b><br />
<br />
5. What are u r regular activities?<br />
Ans: Patching, patch analysis, monitoring, trouble shooting, resolving lock issues, providing trace files,cloning, shutdown/starups,backup etc….<br />
<br />
6. What is a patch?<br />
Ans : A patch can be a solution for a bug/it can be a new feature.<br />
<br />
7. What are the different types of patches?<br />
Ans : oneoff, mini packs, family packs, maintanance packs, rollup pathches, colsolidated patches.<br />
<br />
8. What is a oneoff patch?<br />
Ans : An oneoff patch is a small patch of (20-90K size) without any pre-req’s<br />
<br />
9. What is a mini pack ?<br />
Ans : A mini pack is one which will upgrade any product patchset level to next level like AD.H to AD.I<br />
<br />
10. What is Family pack ?<br />
Ans : A Family pack is one which will upgade the patchset level of all the products in that family to perticular patchsetlevel.<br />
<br />
11. What is Maintanance pack ?<br />
Ans : A maintanance pack will upgrade applications from one version to another like 11.5.8 to 11.5.9<br />
<br />
12. What is a Rollup patch?<br />
Ans : A rollup patch is one which will deliver bug fixes identified after the release of any major application versions like 11.5.8/11.5.9<br />
<br />
13. What is consilidated patch?<br />
Ans: Consolidated patches will come into pictures after upgrades from one version of applications to anoter, all post upgrade patches will a consolidated and given as consolidated patch.<br />
<br />
14. How u will find whether a patch is applied/not?<br />
Ans : Query ad_bugs.<br />
<br />
15. What is the other table where u can query what are the patches applied?<br />
Ans : Ad_applied_patches<br />
<br />
16. What is the difference between ad_bugs and ad_applied_patches?<br />
Ans: A patch can deliver solution for more than one bug, so ad_applied_patches may not give u the perfect information as in case of ad_bugs.<br />
<br />
17. How u apply a patch?<br />
Ans : adpatch<br />
<br />
18. What inputs you need to apply a patch other than driver name and etc?<br />
Ans : apps and system passwords<br />
<br />
19. What are the table u r adpatch will create and when?<br />
Ans : Adpatch will creat FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS table when it will apply d,g and u drivers<br />
<br />
20. What is the significance of FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS table?<br />
Ans: FND_INSTALL_PROCESSES table will store the worker information like what job is assigned to which worker and its status. AD_DEFERRED_JOBS will come into picture when some worker is failed, it will be moved to AD_DEFERRED_JOBS table, from where again adpatch will take that job and try to resign, after doing this 3 times if still that worker is failing, then adpatch will stop patching and throw the error that perticular worker has failed. We need to trouble shoot and restrart the worker.<br />
<br />
21. If it is a multinode installation which driver we need to apply on which node?<br />
Ans: c,d,g on concurrent node and c, g on web node. If it is u-driver we need to apply on all nodes.<br />
<br />
22.While applying a application patch is that necessary that u r database and listener should be up?<br />
Ans: Yes . why because adpatch will connect to database and update so many tables etc…..<br />
<br />
23. While applying a patch if that patch is failing because of a pre-req then how you will apply that pre-req patch and resume with the current patch?<br />
Ans: We need to take the backup of FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS tables and restart directory at APPL_TOP/amdin/SID and then use adctrl to quit all the workers. Then apply the pre-req patch , after that rename u r restart directory to its original name and create FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS tables from the bcakup tables. Start adpatch session and take the options want to continue previous session.<br />
<br />
24. What is adctrl?<br />
Ans: Adctrl is one of the adutilities, which is used to check the status of workers and to manage the workers.<br />
<br />
25. Can u name some of the menu options in adctrl?<br />
Ans: Check the status of workers, tell manager that worker has quited, restart a failed worker etc….<br />
<br />
26. How to skip a worker and why?<br />
Ans: We can skip a worker using option 8 in adctrl which is hidden. We will go for skipping a worker when we have executed the job which the worker is supposed to do.<br />
<br />
27. How adpatch knows what are the pre-req’s for the patch which it is applying?<br />
Ans: With every patch a file called b<patch_number>.ldt file will be delivered which contain the pre-req information. adpatch load this into databse using FNDLOAD and check , whether those pre-req patches were applied or not.<br />
<br />
28. What is FNDLOAD ?<br />
Ans: FNDLOAD is a utility which is similar to sqlloder but loads code objects into database, where as SQLLOADER loads data objects into database.<br />
<br />
<br />
29. What c-driver will do?<br />
Ans: C-drive copies the files from patch unzipped directory to required location in u r application file system. Before copying it will check the file version of the existing file at the file system with the file version of the file in the patch. If the patch file version is higher than what it is at file system level then only c-driver will copy that files.<br />
<br />
30. How adpatch will know the file versions of the patch delivered files?<br />
Ans: With each patch a file with name f<patch_number>.ldt is delivered , which contain the file versions of the files dilivered with the patch. Adpatch will use this file to compare the file versions of files its delivering with the file on file system.<br />
<br />
31. What is the adpatch log file location?<br />
Ans : APPL_TOP/admin/SID/log<br />
<br />
32. What is the worker log file name and its location?<br />
Ans : adwork01,adwork02…… and location is APPL_TOP/admin/SID/log<br />
<br />
33. How u will know what are the files the patch is going to change just my unzipping the patch?<br />
Ans: When u unzip a patch it will keep all the files related to a particular product under that directory inside u r patch directory for example if the patch delivering files related to FND product then it will create a sub directory under the patch directory with the name FND in which it will put all related files to that product<br />
<br />
34. What is the significance of backup directory under u r patch directory?<br />
Ans: When we apply a patch it will keep the copy of the files which its going to change in file system.<br />
<br />
35. What are the different modes you can run your adpatch?<br />
Ans :<br />
1.Interactive – default mode<br />
2.Non interactive – Use defaults files to store prompt values (adpatch defaultsfile=<filename> interactive=no)<br />
3.Test – Without actually applying a patch just to check what its doing.(adpatch apply=no)<br />
4.Pre-install – (adpatch preinstall=y) This mode will be usefull to discrease upgrade downtime as its applies bus fixes without running SQL,EXEC and generate portion of patch.<br />
<br />
36. How u will monitor u r applications as well as database?<br />
Ans: We have our custom scripts which is sheduled to run at a specific time which will monitor whether applications and databases are up/not. And it will mail us if some processes is not running. And we have one script which will check database alert log for ORA errors and mails it to us . Based on this we will react.<br />
<br />
37. What are the latest ORA errors u have encountered?<br />
Ans : Useually we will get the ORA errors like unable to extend the tablespace by so and so size. And we will check those tablespaces for space, if space is not there we will resize the datafile and add one more datafile.<br />
<br />
38. Which table u will query to check the tablespace space issues?<br />
Ans : bytes column in dba_free_spaces and dba_data_files<br />
<br />
39. Which table u will query to check the temp tablespace space issues?<br />
Ans : dba_temp_files<br />
<br />
40. What is temp tablespace? And what is the size of temp tablespace in u r instances?<br />
Ans : Temp tablespace is used by so many application programs for sorting and other stuff. Its size is between 3 to 10 GB.<br />
<br />
41. What is autoconfig?<br />
Ans : Autoconfig is an adutility which is used to main application environment and configuration files.<br />
<br />
42. What are the parameter autoconfig will ask for?<br />
Ans : Context file name and apps password<br />
<br />
43. What is context file?<br />
Ans : Context file is a central repositary, which stores all application configuration information. The name is like <Instance name>_ <Server name>.xml<br />
<br />
44. How you will find autoconfig is enabled/not for u r applications?<br />
Ans:<br />
1. Open any env / configuration files, the first few lines will tell u that this files are maintained by autoconfig.<br />
2. If contextname.xml file is there in APPL_TOP/admin<br />
<br />
45. How autoconfig will create env and configuration files?<br />
Ans: Autoconfig will go to each and every top template directory take the templates from there and fill the values from xml file and create the required files.<br />
<br />
46. In how many phases autoconfig will run?<br />
Ans : Autoconfig will run in 3 phases.<br />
1.INIT – Instantiate the drivers and templates<br />
2.SETUP – Fill the templated with values from xml and create files<br />
3.PROFILE – Update the profile values in database.<br />
<br />
47. What is the location of adconfig log file?<br />
Ans : APPL_TOP/admin/<context_name>/log/<timestamp directory><br />
<br />
48. Is it possiable to restore a autoconfig run?<br />
Ans : Partially. Adconfig will create a restore.sh script at $APPL_TOP/admin/<context_name>/out/<timestamp directory>. This restore.sh will copy the backed up files before autoconfig run to its original locations. But the profile values updated in the database can’t be restored back.<br />
<br />
49. How to run autoconfig in test mode?<br />
Ans : adchkcfg.sh script at AD_TOP/bin. This script will run autoconfig in test mode and create the difference file which tells us what is going to change , when u actually run autoconfig.<br />
<br />
50. How to find autoconfig is enabled or not for database?<br />
Ans: If we have appsutil directory under RDBMS_ORACLE_HOME<br />
<br />
51. When a patch delivers java files what extra file u will get when u unzip the patch, other then u r dirver and readme files?<br />
Ans : j<patch_number>.zip<br />
<br />
52. What is apps.zip/appsbrog2.zip file?<br />
Ans : apps.zip/appsbrog2.zip is the patchable archive of all java class files required for oracle application.<br />
Apps.zip was used to old application version, but from 11.5.8 onwards its appsbrog2.zip<br />
<br />
53. What is the location of apps.zip/appsbrog2.zip?<br />
Ans : AU_TOP/java and JAVA_TOP<br />
<br />
54. What is for “validating apps schema” option in adadmin?<br />
Ans: It will check for the corrupted objects in apps schema<br />
<br />
55. What is “compile apps schema” option in adadmin?<br />
Ans : It will compile the invalid database objects.<br />
<br />
56. How to find invalid objects in database?<br />
Ans : select count(*) from dba_objects where status=’INVALID’;<br />
<br />
57. How to find MRC is enabled or not?<br />
Ans: In adadmin if covert to MRC options is there , then MRC is not enabled.<br />
If maintain MRC options is there , then MRC is enabled.<br />
<br />
58. How to find Multi-Org is enabled or not?<br />
Ans : In adadmin if covert to Multi org option is there, then Multi-org is not enabled. If maintain multi-org options is there, then Multi-org is enabled.<br />
<br />
59. What is mean by MRC?<br />
Ans: MRC stands for Multiple reporting Currency, this should be enabled to see the reports in different currencies like (rupees,yaans etc).<br />
<br />
60. What is Multi-Org?<br />
Ans: If this is enabled we can store multiple organization information in a single oracle application instance.<br />
<br />
61. What is the configuration file for adutilities (like adadmin,adconfig etc)?<br />
Ans: adconfig.txt @APPL_TOP/admin<br />
<br />
62. What is adrelink?<br />
Ans : adrelink will relink the executables with the libraries. Generally we will go for adrelink when some patch delivers some library files, or when executables were corrupted.<br />
<br />
63. How to find the version of a file?<br />
Ans :<br />
1. adident Header <filename><br />
2. strings -a filename | grep Header<br />
<br />
64. What is adodfcmp utility?<br />
Ans : This utility is used to recreate/repair corrupted database objects from odf(object defination files) files.<br />
<br />
65. How you will change apps password?<br />
Ans: FNDCPASS 0 y apps/<pwd> system/<pwd> SYSTEM APPLSYS <new pwd><br />
<br />
66. What if apps password is changed with alter command?<br />
Ans : Applications won’t work.<br />
<br />
66. What is the difference between alter and FNDCPASS in changing apps password?<br />
Ans : FNDCPASS will update some fnd tables other than standard tables.<br />
<br />
67. Where the FNDCPASS utility is located?<br />
Ans : Concurrent node @FND_TOP/bin<br />
<br />
68. How to find out what component of u r oracle applications were installed on which node?<br />
Ans : Xml file (context file)<br />
<br />
69. How to find the version of httpd/Apache web server?<br />
Ans : $IAS_ORACLE_HOME/Apache/bin/httpd –version<br />
<br />
70. What is the configuration file for httpd and what is the location of it ?<br />
Ans : httpd.conf @IAS_ORACLE_HOME/Apache/Apache/conf<br />
<br />
71. Where you will see when you have some problem with u r webserver(httpd/Apache)?<br />
Ans : access_log & error_log @IAS_ORACLE_HOME/Apache/Apache/logs<br />
<br />
72. When Apache starts what other components its start ?<br />
Ans : PL/SQL Listener, Servlet Engine, OJSP Engine<br />
<br />
73. What is jserv?<br />
Ans : jserv is nothing but servlet engine which will run u r servlets. It’s a module of apache which supports servlets.<br />
<br />
74. What is self service application?<br />
Ans : Whatever part of u r oracle application u r able to see through web browser is self service.<br />
<br />
75. Where u will see when u r not able to get self service applications?<br />
Ans : access_log,error_log, error_pls, jserv.log, wdbsvr.app(for apps password)<br />
<br />
76. What is the location of jserv.log?<br />
Ans : IAS_ORACLE_HOME/Apache/Jserv/log<br />
<br />
77. What is the location of wdbsvr.app ?<br />
Ans : IAS_ORACLE_HOME/Apache/modplsql/cfg<br />
<br />
78. What are jserv.conf and jserv.properties files?<br />
Ans : These are the configuration files which were used to start jvm’s(servlet engine) by apache.<br />
<br />
79. What is mean by clearing cache and bouncing apache?<br />
Ans :<br />
1. Stop apache (adapcctl.sh stop apps)<br />
2. Clear cache – Go to $COMMON_TOP/html/_pages and delete _oa_html directory (rm –r _oa__html)<br />
3. Start apache (adapcctl.sh start apps)<br />
<br />
80. What is forms configuration file and its location?<br />
Ans : appsweb_contextname.cfg @$COMMON_TOP/html/bin<br />
<br />
81. What are the different modes u can start u r form server?<br />
Ans : socket and servlet<br />
<br />
82. What is the difference beween socket and servlet mode?<br />
Ans : In socket mode forms sessions are represented by f60webmx<br />
In servlet mode forms sessions are represented by apache processes.<br />
<br />
83. What is forms metric server and client?<br />
Ans : When there are more than one form sever instances then forms metric server and clinet will be used to load balance.<br />
<br />
84. Where the forms server related errors will be logged?<br />
Ans : access_log and error_log<br />
<br />
85. What is report server configuration and log file name and its location?<br />
Ans : Configuration file – REP_<SID>.ora<br />
Log file – REP_<SID>.log @806_ORACLE_HOME/reports60/server<br />
<br />
86. What is CGIcmd.dat file and its location?<br />
Ans : CGIcmd.dat file is the run time parameter file the report server located @ 806_ORACLE_HOME/reports60/server<br />
<br />
87. What is the significance of DISPLAY variable?<br />
Ans : Vnc server should be up and running at the specified port value in DISPLAY variable, otherwise reportserver may not able to show the graphics in<br />
Reports.<br />
<br />
88. Where is the concurrent manager log file located?<br />
Ans : $COMMON_TOP/admin/<SID>/log or $APPLCSF/$APPLLOG<br />
<br />
89. Is apps password necessary to start all the components of oracle application?<br />
Ans : No. Only to start/stop concurrent managers apps password is needed.<br />
<br />
90. What is a concurrent manager?<br />
Ans : A concurrent manager is one which runs concurrent requests.<br />
<br />
91. What are the different types of concurrent managers?<br />
Ans :<br />
1. Internal concurrent manager – Will start all other managers and monitor<br />
2. Standard Manager – All concurrent request by default will to go this<br />
3. Conflict resolution manager – Concurrent programs with incompatabilites will be handled by this<br />
4. Transaction manager – Handle all transaction requests<br />
<br />
92. What are actual and target count in ‘Adminster Concurrent Managers form’?<br />
Ans : Target is the no. of concurrent processes a manager is supposed to start(specified in the defination of concurrent manager).<br />
Actual is the no. of processes a manager started actually.<br />
Target and Actual should be always same.<br />
<br />
93. What if Target and Actual are not same?<br />
Ans : It means at operating system level resources are low to accomidate the required processes for concurrent managers.<br />
<br />
94. What are work shifts?<br />
Ans : Work shifts are nothing but timings at which the concurrent manager is supposed to run.<br />
<br />
95. What if internal concurrent manager target and actual are not same?<br />
Ans : we need to bounce the concurrent manager using adcmctl.sh<br />
<br />
96. How to bounce a single concurrent manager?<br />
Ans : From frontend using ‘Administer Concurrent Manager form’.<br />
<br />
97. When we change apps password , is it necessary to bounce application?<br />
Ans : Only we need to bounce concurrent managers.<br />
<br />
98. What is dbc file and its location?<br />
Ans : dbc file contain database connection information. DBC file is used by oracle applications to connect to database. Its location is $FND_TOP/secure<br />
<br />
99. What is the other script by which u can start apache other than adapcctl.sh?<br />
Ans : apachectl @IAS_ORACLE_HOME/Apache/bin<br />
<br />
100. What is the configuration file for PL/SQL listener?<br />
Ans : httpd_pls.conf @IAS_ORACLE_HOME/Apache/Apache/conf<br />
<br />
101. How to skip copy portion while applying a patch?<br />
Ans : Adpatch options=nocopyportion<br />
<br />
102. How to merge patches and what type of patches can be merged?<br />
Ans : admrgpch. We can merge any kind of application patches, if any of the patch contain a u-driver then merged patch will contain u_merged.drv otherwise c_merged.drv, d_merged.drv and g_merged.drv<br />
<br />
103. What is the Tiered architecture of u r instance?<br />
Ans : Two Tier: Web and Forms on one node and Conc, admin and report on other node.<br />
<br />
104. How to find formserver version?<br />
Ans: f60gen and press enter, it will tell u the formserver version or we can find out from the frondend using help menu.<br />
<br />
105. What is RRA?<br />
Ans : RRA stands for Report Review Agent. RRA is nothing but FNDFS which is part of apps listener. RRA job is to pick the log/out file from the file system and show on the editor when u press view log/out button in ‘View concurrent request form’.<br />
<br />
106. What is apps listener?<br />
Ans : Apps lintener is the combination of FNDFS and FNDSM. FNDSM is service manager which will monitor application services on that node when GSM:enable profile value is ‘Y’.<br />
<br />
107. What is GSM?<br />
Ans : GSM stands for Generic service Manager, which will monitor application processes like web, forms etc and restarts any of this processes if goes down.<br />
<br />
108. How to find the application version like 11.5.8/11.5.9….?<br />
Ans : select release_name from fnd_product_groups;<br />
<br />
109. How to find the database/sqlplus version?<br />
Ans : select banner from v$version;<br />
<br />
110. How to find out what are the languages enabled in u r applications?<br />
Ans : Query fnd_languages<br />
<br />
111. What is the size of u r database?<br />
Ans : 200 to 500 GB<br />
<br />
112. How to find operating system version?<br />
Ans : uname –a<br />
<br />
113. What are the problems u have faced while shutting down applications?<br />
Ans : While shutting down application generally concurrent manager won’t go down because some or the other request may be running. We will see what are the concurrent requests running by querying fnd_concurrent_requests, fnd_concurrent_program_vl, v$session,v$process and v$sqltext. If that request is only doing some select statement then we will kill those requests, otherwise we will check what time it will take to complete by querying the previous runs of that request and then we will decide what to do.<br />
<br />
114. What are the problems u have faced while starting up applications?<br />
Ans : Most of the time we will encounter problem with starting up concurrent managers. Reasons , database listener may be down or FNDSM entries are wrong in tnsnames.ora of 806_ORACLE_HOME.<br />
<br />
115. How to find the locks and what is the resolution?<br />
Ans : we can find general locks with the following query:<br />
select * from sys.dba_dml_locks order by session_id.<br />
<br />
We can find the dead locks with the following query:<br />
select * from v$lock where lmode > 0 and id1 in (select distinct id1 from v$lock where request > 0)<br />
If it’s a dead lock, we need to kill that session.<br />
<br />
116. How to kill a database session?<br />
Ans : alter system kill session '&sid,&sno';<br />
<br />
117. How to find adconfig is enabled for oracle operating system user/database?<br />
Ans : If appsutil directory is there in RDBMS_ORACLE_HOME<br />
<br />
118. Which files tell u the database helath?<br />
Ans : alert log file @RDBMS_ORACLE_HOME/admin/<Contextname>/bdump<br />
<br />
119. How to apply a rdbms patch?<br />
Ans : Using opatch<br />
<br />
120. How to find opatch is enabled or not for u r database?<br />
Ans : If Opatch directory exists under RDBMS_ORACLE_HOME.<br />
<br />
121. What is the pre-req for applying a rdbms patch?<br />
Ans : Inventory should be set in file oraInst.loc @/var/opt/oracle or /etc<br />
<br />
122. What is Inventroy?<br />
Ans: The oraInventory is the location for the OUI (Oracle Universal Installer)'s bookkeeping. The inventory stores information about:<br />
1. All Oracle software products installed in all ORACLE_HOMES on a machine<br />
2. Other non-Oracle products, such as the Java Runtime Environment (JRE)<br />
In a 11i Application system the RDBMS and iAS ORACLE_HOMEs are registered in the oraInventory. The 806 ORACLE_HOME, which is not managed through OUI, is not.<br />
<br />
123. What are different types of inventories?<br />
Ans: The Global inventory (or Central inventory)<br />
The Local inventory (or Home inventory)<br />
<br />
124. What is Global inventory? <br />
Ans : The Global Inventory is the part of the XML inventory that contains the high level list of all oracle products installed on a machine. There should therefore be only one per machine. Its location is defined by the content of oraInst.loc. The Global Inventory records the physical location of Oracle products installed on the machine, such as ORACLE_HOMES (RDBMS and IAS) or JRE. It does not have any information about the detail of patches applied to each ORACLE_HOMEs. The Global Inventory gets updated every time you install or de-install an ORACLE_HOME on the machine, be it through OUI Installer, Rapid Install, or Rapid Clone. Note: If you need to delete an ORACLE_HOME, you should always do it through the OUI de-installer in order to keep the Global Inventory synchronized.<br />
<br />
125. What is local inventory?<br />
Ans : There is one Local Inventory per ORACLE_HOME. It is physically located inside the ORACLE_HOME at $ORACLE_HOME/inventory and contains the detail of the patch level for that ORACLE_HOME. The Local Inventory gets updated whenever a patch is applied to the ORACLE_HOME, using OUI.<br />
<br />
126. What is rapid clone?<br />
Ans : Rapid Clone is the new cloning utility introduced in Release 11.5.8. Rapid Clone leverages the new installation and configuration technology utilized by Rapid Install<br />
<br />
127. How do I determine if my system is rapid clone enabled?<br />
Ans : First, verify that your system is AutoConfig enabled. Then, verify that you have applied the latest Rapid Clone patch.<br />
<br />
128. Explain the cloning process?<br />
Ans :<br />
1. Run adpreclone as applmgr and oracle user on source<br />
Perl adpreclone.pl dbTier as oracle user<br />
Perl adpreclone.pl appsTier as applmgr user<br />
2. Take the cold/hotbackup of source database<br />
3. Copy the five directories <prod>appl,<prod>comn,<prod>ora , <prod>db,<prod>data to target<br />
4. Rename the directories, and change the permisssion<br />
5. Set the inventory in oraInst.loc<br />
6. Run perl adcfgclone.pl dbTier as oracle user,if the backup type is cold<br />
7. If the backup type is hotbackup then<br />
Perl adcfgclone.pl dbTechStack.<br />
Create the control file on target from the control script trace file from source<br />
Recover the database<br />
Alter database open resetlogs<br />
8. Run autoconfig with the ports changed as per requirement in xml.<br />
9. Run perl adcfgclone.pl appsTier as applmgr<br />
10.Run autoconfig with the ports changed as per requirement in xml.<br />
<br />
129. What is the location of adpreclone.pl for oracle user?<br />
Ans : RDBMS_ORACLE_HOME/appsutil/scripts/<contextname><br />
<br />
130. What is the location of adpreclone.pl for applmgr user?<br />
Ans : $COMMON_TOP/admin/scripts/<contextname><br />
<br />
131. What is the location of adcfgclone.pl for oracle user?<br />
Ans : $RDBMS_ORACLE_HOME/appsutil/clone/bin<br />
<br />
132. What is the location of adcfgclone.pl for applmgr user?<br />
Ans : $COMMON_TOP/clone/bin<br />
<br />
133. What is statspack?<br />
Ans : Statspack is a database utility to gather database and session level performance information.<br />
<br />
134. How to install statspack?<br />
Ans : Run the script spcreate.sql @RDBMS_ORACLE_HOME/rdbms/admin<br />
Note more details on statspack refer metalink noteid: 149113.1<br />
<br />
135. How to enable trace at database level?<br />
Ans : set init.ora parameter sql_trace<br />
<br />
136. How to enable trace for a session?<br />
Ans: Alter system set sql_trace=true;<br />
Execute the sql query<br />
Alter system set sql_trace=false;<br />
This will create a trace file at $RDBMS_ORACLE_HOME/admin/contextname/udump with the spid of the current sql session.<br />
<br />
137. How to enable trace for other session?<br />
Ans : exec sys.dbms_system.set_sql_trace_in_session(sid,serial#,true/false)<br />
<br />
Eg:<br />
To enable trace for sql session with sid 8<br />
SQL> exec sys.dbms_system.set_sql_trace_in_session(8,121,true);<br />
PL/SQL procedure successfully completed.<br />
<br />
To disable trace<br />
SQL> exec sys.dbms_system.set_sql_trace_in_session(8,121,false);<br />
<br />
138.What is the location of inint.ora ?<br />
Ans : $RDBMS_ORACLE_HOME/dbs<br />
<br />
139. What is that trace files contains and the utiliy used to read them?<br />
Ans : Trace file contains the detail diagnostics of a sql statement like explain plan, physical reads, logical reads, buffer gets etc. Tkprof utility is used to convert trace file into readable format.<br />
<br />
140. What is the syntax for tkprof?<br />
Ans: tkprof <trace filename> <output filename> explain=apps/<pwd> sys=no<br />
<br />
141.How do we find adpreclone is run in source or not ?<br />
Ans : If clone directory exists under RDBMS_ORACLE_HOME/appsutil for oracle user and $COMMON_TOP for applmgr user.<br />
<br />
142. How to find that the database is 64-bit/32-bit?<br />
Ans : $RDBMS_ORACLE_HOME/bin/file oracle<br />
Ex: /oraDB/angdb/920/bin> file oracle<br />
oracle: setuid setgid ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.2.5, dynamically linked (uses shared libs), not stripped<br />
<br />
143. How to find trace file for a given concurrent request id?<br />
Ans : Go to $RDBMS_ORACLE_HOME/admin/<context_name>/udump<br />
Grep “<request id> “ *<br />
<br />
144. What is a database link? How to create it?<br />
Ans : If we want to access objects of another database from this database then we need a database link from this database to the other.<br />
1.Login as oracle user<br />
2.sqlplus “/as sysdba”<br />
3.create database link <dblink name> connect to <destination user> identified by <d;estination user pwd> using '<destination database name>';<br />
Ex:<br />
SQL> create database link TEST1_TO_TEST2 connect to apps identified by apps using 'TEST2';<br />
Database link created.<br />
SQL> select name from v$database@ TEST1_TO_TEST2;<br />
NAME<br />
---------<br />
TEST2<br />
SQL>select db_link from dba_db_links;<br />
4.Add destination database tns entry in tnsnames.ora<br />
<br />
145. How many clonings u have done?<br />
Ans : If u r very much confident on cloning processes then say 5 to 8 otherwise just 2 or 3.<br />
<br />
146. What u know abt RMAN?<br />
Ans : If u r good at RMAN then say yes, otherwise say we are not using RMAN for backup/recovery , why because we are using netapp snap technology for backups.<br />
<br />
147. What is netapp?<br />
Ans : Netapp is a storage technology.<br />
<br />
148. Have u done any performance tunning?<br />
Ans : Better say No. If u r very good in performance tunning then say ‘yes’.<br />
<br />
149. What is formserver url?<br />
Ans :http://hostname.domain:<port>/dev60cgi/f60cgi<br />
<br />
150. What is jinitiator?<br />
Ans : Oracle jinitiator is the one which provide the required jvm to run forms interface/applet. When we access forms applet first time , oracle jinitiator will be installed automatically.<br />
<br />
151. What is discoverer server?<br />
Ans : Discoverer server is reporting tools which allows novoice user to use oracle application reports. Discoverer will come along with oracle applications when installed.<br />
<br />
152. What is discoverer viewer url?<br />
Ans: http://hostname.domain:<port>/discoverer4i/viewer<br />
<br />
153. What is discoverer plus url?<br />
Ans : http://hostname.domain:<port>/discwb4/html/english/welcome.htm<br />
<br />
154. What is aoljtest and its url?<br />
Ans : Aoljtest is a web based utility to test the availability of the different components of oracle applications like jserv,modplsql,jsp,forms etc<br />
URL: http://hostname.domain:<port>/OA_HTML/jsp/fnd/aoljtest.jsp<br />
<br />
155. What is adsplicer?<br />
Ans : Adsplicer is a uitility used to register off cycle products.<br />
<br />
156. What is licence manager?<br />
Ans : Licence manager(adlicmgr) utility is used to licence/unlicence , enable new languages,enbale country specific functionality.<br />
<br />
157. What is tnsping?<br />
Ans : tnsping is command used to check the connectivity to the database server node from other nodes.<br />
Ex: tnsping <dbsid><br />
Note: Tns entry should be there in tnsnames.ora for the database we are trying to work this command.<br />
<br />
158. What is top command?<br />
Ans : top is a operating system command, it will display top 10 processes which are taking high cpu and memory.<br />
<br />
159. How to compile a form using f60gen?<br />
Ans : f60gen module=/TEST/testappl/au/11.5.0/forms/F/ARXTWMAI.fmb userid=APPS/APPS output_file=/TEST/testappl/ar/11.5.0/forms/F/ARXTWMAI.fmx module_type=form batch=yes compile_all=special<br />
<br />
160. What is APPLPTMP environment variable?<br />
Ans : This is the temporary file location for the pl/sql temp files. If this variable was not set then the concurrent programs may errored out.<br />
<br />
161. What is mean by enabling maintanance mode?<br />
Ans : Maintanance mode is the adadmin option introduced from AD.I. When maintanance mode is enabled user may able to login to application but they only get profile option in the frontend navigation menu.<br />
<br />
162. Is that necessary to enable maintanance mode while applying a patch?<br />
Ans : We can even apply a patch without enabling maintanance mode with the following option<br />
Adpatch options=hotpatch<br />
<br />
163. How to find out oracle application framework version?<br />
Ans : 1. Through aoljtest<br />
2. cd $COMMON_TOP/html/<br />
3. adident Header OA.jsp<br />
<br />
164. How to find out what are the rdbms patches applied to an oracle home?<br />
Ans : 1. opatch –lsinventory<br />
2. $RDBMS_ORACLE_HOME/.patch_storage directory contains the directories with the rdbms patch number, which are applied to this oracle home.<br />
<br />
165. Is that necessary to shutdown database while applying a database patch?<br />
Ans : Yes.<br />
<br />
166. What is the command line utility to submit a concurrent request?<br />
Ans : CONSUB<br />
<br />
167. What is the significance of utl_file_dir parameter in init.ora file?<br />
Ans : The value of this parameter is the group of directories to which u r database can write, means u r database packages have permission to write to flat files in these directories.<br />
<br />
168. How you will find out discoverer version?<br />
Ans : cd $806_ORACLE_HOME/discwb4/lib<br />
strings libd* | grep 'Version:'<br />
<br />
169. While applying a rdbms patch using opatch you are getting the error, unable to read inventory/inventory is corrupted/ORACLE_HOME is not not registered, what you will do, and how you will apply the patch?<br />
Ans: We will check the inventory directory permission, try to apply the patch after giving 777 permissions to that inventory directory. If still it won’t work we will apply patch with the following command:<br />
Opatch apply –no_inventory<br />
<br />
170. Have you opened any TAR and for what?<br />
Ans : Yes. If we have any issues on production then we will raise seviarity 1 tar otherwise seriarity 2/normal.<br />
<br />
171. For what you have raised the TAR?<br />
Ans : We got ORA-7445 error (memory leak) in alert log, for which we have raised a Seviarity 1 TAR.<br />
<br />
172. Have you applied rdbms patches and for what?<br />
Ans : We got ORA-7445 error in alert log, for which oracle recommended to apply a rdbms patch.<br />
<br />
173. What are the patch errors , you have encountered?<br />
Ans :<br />
1)Patch fails with the error, unable to generate perticular form, do u want to continue. We continue patching by saying “yes”, then we manually regenarate the form using f60gen utility.<br />
2) Unable to generate jar files under JAVA_TOP<br />
AutoPatch error:<br />
Failed to generate the product JAR files<br />
Solution:<br />
Run adjkey -initialize -----------to creat identitydb.obj file which will be<br />
used by adjava to sign jar files.<br />
<br />
174. What is adjkey? What files it will create?<br />
Ans : adjkey is an adutility which will create digital signature, which will be used to sign all the jar files when we generate jar files using adadmin. The purpose of this is, when you access forms interface every time, the signature of the jar files which is going to download is compared with the existing cached jar files signature in client machine. If signature won’t match, it will download that jar files again.<br />
It will create the following files:<br />
adsign.txt@APPL_TOP/admin<br />
appltop.cer@APPL_TOP/admin<br />
identitydb.obj@applmgr home<br />
<br />
175. Have done any OWC with oracle?<br />
Ans : No.<br />
<br />
176. What are the post installation task?<br />
Ans : Running adjkey –initialize and then runnning adadmin to regerate jar files.<br />
<br />
177. What are the clone errors, you have encountered?<br />
Ans :<br />
Error:<br />
RC-50013: Fatal: Failed to instantiate driver /u01/fms2c/appfms2c/fms2cora/iAS/appsutil/driver/instconf.drv<br />
Cause:<br />
The source instance has files that adpreclone flags as 'autoconfigable' but in reality they are not. So adpreclone.pl adds these files into the instconf.drv. Then when adcfgclone.pl is run on target it looks for the template file to instantiate for these files and since there isn't a template file adcfgclone.pl fails.<br />
Solution:<br />
Modify the target's instconf.drv and remove the offending lines. Then rerun adcfgclone.pl<br />
<br />
178. What are the real time problems you have encountered and how you trouble shooted that?<br />
Ans:<br />
1. Concurrent Program is erroing out with snapshot too old error. To resolve this we have added space to temp tablespace.<br />
2. Concurrent Program is erroing out with unable to extent a perticular tablespace by so and so extents. To resolve this we have added on more data file to that tablespace.<br />
3. When we are trying to start apache with adapcctl.sh script after a autoconfig run, its saying that “node id is not matching with the application server id”. To resolve this we have updated the server id column in fnd_nodes table with the server id value in dbc file.<br />
<br />
179. How you will find workflow version?<br />
Ans : Run wfver.sql@FND_TOP/sql script as apps user<br />
<br />
180 . When forms are running in servlet mode then the environment variables required for forms must be defined in what file and its location?<br />
Ans : formsservlet.ini@$APACHE_TOP/Jserv/etc.<br />
<br />
181. How to find out which patch driver is applied(like c,d,g or u)?<br />
Ans: query ad_patch_drivers.<br />
<br />
182. How to find out whether a language patch is applied for a perticular patch?<br />
Ans : Query ad_patch_driver_langs.<br />
<br />
183. How to validate that sysadmin password is correct or not from backend?<br />
Ans: select fnd_web_sec.validate_login('SYSADMIN','Qwert8765') from dual;<br />
<br />
184. How to compile jsp's(other than from adadmin)?<br />
Ans: Force compilation of all jsps using the following command<br />
ojspCompile.pl --compile --flush<br />
<br />
185. How to rotate logs for apache logs?<br />
Ans: Using rotatelogs executable in httpd.conf file. Use Errorlog for error_log file rotation. Transferlog for other log files.<br />
<br />
186. Other way of checking whether MRC is enabled or not besides using adadmin?<br />
<br />
Ans : select multi_currency_flag from fnd_product_groups;<br />
<br />
187. How to compile rdf?<br />
Ans: Either using adadmin or rwcon60<br />
<br />
188. How you will see hidden files in linux/solaris?<br />
Ans : ls –la<br />
<br />
189. How to change file/directory owner in linux/solaris?<br />
Ans : chown –R <username>:<group> <file/directory><br />
Ex:<br />
chown –R applmgr:dba testappl<br />
<br />
190. How to change the permission of file/directory in linux/solaris?<br />
Ans : chmod –R <permissions> <file/directory><br />
Ex:<br />
chmod –R 755 testappl<br />
<br />
191. What are the files which contain apps password?<br />
Ans :<br />
1. wdbsrv.app@IAS_ORACLE_HOME/Apache/modplsql/cfg<br />
2. CGIcmd.dat@806_ORACLE_HOME/reports60/server<br />
3. wfmail.cfg@FND_TOP/resource - optional<br />
4. CatalogLoader.conf@OA_JAVA - optional<br />
5. CatalogLoader.xml@OA_HTML - optional<br />
<br />
192. What is the script to find out ICM status?<br />
Ans : afimchk.sql@FND_TOP/sql<br />
<br />
193. What is the script to list the concurrent request status?<br />
Ans: afrqrun.sql@FND_TOP/sql<br />
<br />
194. What is the script that Lists managers that currently are running a request?<br />
Ans : afcmrrq.sql@FND_TOP/sql<br />
<br />
195) How can I determine whether a template is customizable or non-customizable?<br />
Answer: If a keyword "LOCK" is present at the end of the file entry in the respective driver, then it is a non-customizable template. If the "LOCK" keyword is not seen, then that template can be customized.<br />
<div>
<br /></div>
</div>
Anonymoushttp://www.blogger.com/profile/04664995477596294650noreply@blogger.com1tag:blogger.com,1999:blog-3992239225529976052.post-54073853408162443682013-11-06T15:58:00.004+05:302013-11-06T15:58:49.791+05:30How to find high CPU using sessions in Oracle Database<div dir="ltr" style="text-align: left;" trbidi="on">
If the problem relates to CPU bound applications then CPU information for each session can be examined to determine the culprits. The v$sesstat view can be queried to find high cpu using sessions and then SQL can be listed. <br /><br />1.Find the 'CPU used by this session' statistic.<br /><br />SQL>SELECT name ,statistic# FROM v$statname WHERE name LIKE '%CPU%session';<br /><br /> NAME STATISTIC#<br /> ----------------------------------- ----------<br /> CPU used by this session 14<br /><br />2. Then determine which session is using most of the cpu.<br /><br />SQL>SELECT * FROM v$sesstat WHERE statistic# = 14;<br /><br /> SID STATISTIC# VALUE<br /> ---------- ---------- ----------<br /> 1 14 0<br /> 2 14 0<br /> 3 14 0<br /> 4 14 0<br /> 5 14 0<br /> 6 14 0<br /> 7 14 0<br /> 8 14 0<br /> 9 14 0<br /> 10 14 0<br /> 11 14 0<br /> 12 14 0<br /> 16 14 1930<br /><br />3. Lookup details for the session which is using most of the cpu.<br /><br />SQL>SELECT address ,SUBSTR(sql_text,1,20) Text, buffer_gets, executions,buffer_gets/executions AVG<br /> FROM v$sqlarea a, v$session s<br /> WHERE sid = 16 <br /> AND s.sql_address = a.address<br /> AND executions > 0<br /> ORDER BY 5;<br /><br />4. Use v$sqltext to extract the whole SQL text.<br /><br />set pages 50000 lines 32767<br />col SPID for a10<br />col PROGRAM for a15<br />col OSUSER for a10<br />col ACTION for a10<br />col EVENT for a25<br />col SQL_TEXT for a25<br />col MACHINE for a10<br />col P1TEXT for a10<br />col P2TEXT for a10<br />col P3TEXT for a10 <br />SELECT b.sid, b.serial#, a.spid, b.sql_id, b.program, b.osuser, b.machine, b.type,<br />b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time<br />FROM v$process a, v$session b, v$sqltext c WHERE a.addr=b.paddr<br />AND b.sql_hash_value = c.hash_value<br />AND b.sid = '&sid' ORDER BY a.spid, c.piece<br />/<br /><br />5. Once the whole SQL statement has been identified it can be tuned. <br /><br />Explain the queries and examine their access paths.<br />Autotrace is a useful tool for examining access paths. <br /><br />Syntax:-<br /><br />Explain plan for 'sql statement';<br /><br /><br /></div>
Anonymoushttp://www.blogger.com/profile/04664995477596294650noreply@blogger.com0tag:blogger.com,1999:blog-3992239225529976052.post-20688616034537382652013-11-06T00:02:00.000+05:302013-11-06T00:02:11.060+05:30How to Move ASM DATABASE FILES from ONE DISKGROUP TO ANOTHER<div dir="ltr" style="text-align: left;" trbidi="on">
Steps to Move ASM DATABASE FILES from ONE DISKGROUP TO ANOTHER<br /><br />Since ASM files cannot be accessed through normal operating system interfaces, RMAN is the preferred means of copying ASM file.<br /><b><br />The steps to moving a datafile from a diskgroup to another is as below, using RMAN. </b><br /><br />1) Identify the datafile to be moved.<br />2) Identify the diskgroup on to which the datafile has to be moved.<br />3) Take the datafile offline.<br />4) Copy the datafile to new diskgroup using Either RMAN or DBMS_FILE_TRANSFER.<br />5) Rename the datafile to point to new location.<br />6) Recover the datafile.<br />7) Bring the datafile online.<br />8) Verify the new datafile locations.<br />9) Delete the datafile from its original location.<br /><b><br />1) Identify the datafile to be moved.</b><br /><br />SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;<br />+ASMDISK2/orcl/datafile/users.256.565313879 <======= Move this to ASMDISK1.<br />+ASMDISK1/orcl/sysaux01.dbf<br />+ASMDISK1/orcl/undotbs01.dbf<br />+ASMDISK1/orcl/system01.dbf<br /><br /><b>2) Identify the diskgroup on to which the datafile has to be moved.</b><br /><br />SQL> SELECT GROUP_NUMBER, NAME FROM V$ASM_DISKGROUP;<br />GROUP_NUMBER NAME<br />------------ ---------<br />1 ASMDISK1<br />2 ASMDISK2<br /><br /><b>3) Take the datafile offline.</b><br />SQL> ALTER DATABASE DATAFILE '+ASMDISK2/orcl/datafile/users.256.565313879' OFFLINE;<br /><br /><b>4) Copy the datafile to new diskgroup using Either RMAN or DBMS_FILE_TRANSFER.</b><br /><br /> a) DBMS_FILE_TRANSFER package or <br /> b) RMAN <br /> <br />a).Using DBMS_FILE_TRANSFER package <br /> <br />SQL> create or replace directory orcl1 as '+ASMDISK1/orcl/datafile';<br /><br />SQL> Alter disgroup ASMDISK2 add directory '+ASMDISK2/test';<br /> <br />SQL> create or replace directory orcl2 as '+ASMDISK2/test';<br /><br />SQL> <br />BEGIN<br /> DBMS_FILE_TRANSFER.COPY_FILE(<br /> source_directory_object => 'ORCL1',<br /> source_file_name => 'users.259.565359071',<br /> destination_directory_object => 'ORCL2',<br /> destination_file_name => 'USERS01.DBF');<br />END; <br />Database altered.<br /><br />-------------------- OR --------------------<br /> <br />b).Using RMAN copy the file to new diskgroup.<br /><br />$ rman target /<br /><br />connected to target database: ORCL (DBID=1020304050)<br /><br />RMAN> COPY DATAFILE '+ASMDISK2/orcl/datafile/users.256.565313879' TO '+ASMDISK1';<br /><br />Starting backup at 03-AUG-98<br />using target database controlfile instead of recovery catalog<br />allocated channel: ORA_DISK_1<br />channel ORA_DISK_1: sid=146 devtype=DISK<br />channel ORA_DISK_1: starting datafile copy<br />input datafile fno=00004 name=+ASMDISK2/orcl/datafile/users.256.565313879<br />output filename=+ASMDISK1/orcl/datafile/users.259.565359071 tag=TAG19980803T12110<br />9 recid=2 stamp=565359071<br />channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03<br />Finished backup at 03-AUG-98<br /><br /><b>5) Rename the datafile to point to new location. </b><br /><br />If you have used DBMS_FILE_TRANSFER (method 4 a)) use the following command to rename: <br />SQL> ALTER DATABASE RENAME FILE '+ASMDISK2/orcl/datafile/users.256.565313879' TO <br /><br />'+ASMDISK1/orcl/datafile/users.259.565359071';<br /><br />Database altered. <br /><br />If you have used RMAN (method 4 b) use the following option of RMAN <br />RMAN><br />run <br />{ <br />set newname for datafile '+ASMDISK2/orcl/datafile/users.256.565313879' <br />to '+ASMDISK1/orcl/datafile/users.259.565359071'; <br />switch datafile all; <br />} <br /><br /><b>6) Recover the datafile.</b><br /><br />SQL> RECOVER DATAFILE '+ASMDISK1/orcl/datafile/users.259.565359071'; <br />Media recovery complete.<br /><br /><b>7) Bring the datafile online.</b><br /><br />SQL> ALTER DATABASE DATAFILE '+ASMDISK1/orcl/datafile/users.259.565359071' ONLINE;<br />Database altered.<br /><br /><b>8) Verify the new datafile locations.</b><br /><br />SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;<br /><br />FILE_NAME<br />-------------------------------------------------------------------------------<br />+ASMDISK1/orcl/datafile/users.259.565359071<br />+ASMDISK1/orcl/sysaux01.dbf<br />+ASMDISK1/orcl/undotbs01.dbf<br />+ASMDISK1/orcl/system01.dbf<br /><br /><b>9) Delete the datafile from its original location.</b><br /><br />SQL> ALTER DISKGROUP ASMDISK2 DROP FILE users.256.565313879;<br />or<br />ASMCMD> rm -rf <filename><br /><br /><b><br />Note:-<br />====</b><br /><br />The steps provided above assume that the database is open and in Archivelog mode.<br />Besides these steps are not appropriated for system or sysaux datafiles. <br /><br />For System and Sysaux an approach similar to the one given below can be used:-<br /><br />1. Create a Copy of datafile in target Diskgroup<br />RMAN> backup as copy tablespace system format '<New DG>'; <br />RMAN> backup as copy tablespace sysaux format '<New DG>'; <br /><br />2. Then shutdown the database and restart to a mounted state <br />RMAN> shutdown immediate; <br />RMAN> startup mount; <br /><br />3. switch the datafiles to the copy <br />RMAN> switch tablespace system to copy; <br />RMAN> switch tablespace sysaux to copy; <br /><br />4. Recover the changes made to these tablespaces<br />RMAN> recover database;<br /><br /><b>Note:-<br />====</b><br /><br />Most ASM files do not need to be manually deleted because, as Oracle managed files, they are removed automatically <br /><br />when they are no longer needed.<br /><br />However, if you need to drop an Oracle Managed File (OMF) manually you should use the fully qualified filename if <br /><br />you reference the file. Otherwise you will get an error (e.g. ORA-15177).<br /><br />ALTER DISKGROUP ASMDISK2 DROP FILE '+ASMDISK2/orcl/datafile/users.256.565313879';<br /></div>
Anonymoushttp://www.blogger.com/profile/04664995477596294650noreply@blogger.com0tag:blogger.com,1999:blog-3992239225529976052.post-5479989194949568482013-10-07T13:18:00.003+05:302013-10-07T13:18:36.273+05:30Patch 13621679 - 11.1.0.7.11 Patch Set Update<div dir="ltr" style="text-align: left;" trbidi="on">
Patchset/PSU Patch Number Description<br />11.1.0.7.11 13621679 DATABASE PATCH SET UPDATE 11.1.0.7.11 (INCLUDES CPU APR2012)<br /><br />Patch 13621679 - 11.1.0.7.11 Patch Set Update<br />=============================================<br /><a href="http://bbs.dbsupport.cn/thread-160-1-1.html">http://bbs.dbsupport.cn/thread-160-1-1.html</a><br /><br />Patch InformationPatch Set Update (PSU) patches are cumulative. That is, the content of all previous PSUs is included in the latest PSU patch.<br /><br />PSU 11.1.0.7.11 includes all fixes previously included in PSU 11.1.0.7.10 and those listed in .<br />To install the PSU 11.1.0.7.11 patch, the Oracle home must have the 11.1.0.7.0 Database installed. Subsequent PSU patches can be installed on Oracle Database 11.1.0.7.0 or any PSU with a lower 5th numeral version than the one being installed.<br /><br />OPatch Utility<br />==============<br />You must use the OPatch utility version 11.1.0.8.2 or later to apply this patch. Oracle recommends that you use the latest released OPatch 11.1, which is available for download from My Oracle Support patch6880880 by selecting the 11.1.0.0.0 release.<br />For information about OPatch documentation, including any known issues, see My Oracle Support Note 293369.1 OPatch documentation list.<br /><br />PSU Patching 11G Steps on PRIMARY database<br />==========================================<br /><br />check DR sysnc status<br />=====================<br />http://select-star-from.blogspot.in/2013/09/data-guard-sync-status.html<br /><br />------------------------------------------------------------------------------------------------------------------<br /><br />Start OEM black out<br />===================<br /><br />Confirmation <br />Blackout "Blackout-Apr 24 1998 9:33:42 PM" created successfully, Blackout status will be propagated to the target(s) shortly. <br /><br />------------------------------------------------------------------------------------------------------------------<br /><br />Node 1: (AS ORACLE) <br />===================<br /><br />$ hostname<br />$ uname<br />$ bash<br />$ date<br />$ df -kg or df -h<br />$ cat /etc/oratab or cat/var/opt/oracle/oratab ------> Note ASM and Database Home Paths<br />$ ps -ef| grep pmon<br />$ ps -ef| grep tns<br />$ ps -ef| grep emagent<br />$ ps -ef|grep oracle<br />$ ps -ef| grep d.bin<br />$ ps -ef|grep crs<br /> root 344170 1 0 Apr 24 - 0:00 /oradb/crs/product/11.1.0/crs_1/bin/oclskd.bin<br /><br />$ cd /oradb/app/admin/orasw/patches/PSU_patches/Jan2012<br />$ opatch version<br />$ /oradb/crs/product/11.1.0/crs_1/OPatch/opatch version<br />$ /oradb/asm/product/11.1.0/asm_1/OPatch/opatch version<br />$ /oradb/app/oracle/product/11.1.0/db_1/OPatch/opatch version<br />$ which opatch<br />/oradb/app/oracle/product/11.1.0/db_1/OPatch/opatch<br />. oraenv --- asm instance<br />$ opatch lsinventory<br />. oraenv --- database 1 instance<br />$ opatch lsinventory<br />. oraenv --- database 2 instance<br />$ opatch lsinventory<br /><br />$ ps -ef| grep pmon<br />$ srvctl status database -d <database_1> or srvctl status instance -d <database_1> -i <database_1_instance> <br />$ srvctl status database -d <database_2> or srvctl status instance -d <database_2> -i <database_2_instance><br /><br />$ srvctl status asm -n <Node 1><br />$ srvctl status asm -n <Node 2><br /><br />$ srvctl status listener -n <Node 1><br />$ srvctl status listener -n <Node 2><br /><br />$ srvctl status nodeapps -n <Node 1><br />$ srvctl status nodeapps -n <Node 2><br /><br />$ ps -ef|grep oracle or ps -ef|grep emagent<br />./emctl status agent or /oradb/app/oracle/product/agent1/agent11g/bin/emctl status agent<br /><br />$ cd /oradb/crs/product/11.1.0/crs_1/bin<br />$ ./crsctl check cluster<br /><br />$ cd /oradb/crs/product/11.1.0/crs_1/bin<br />$ ./crsctl check crs<br /><br />$ cd /oradb/crs/product/11.1.0/crs_1/bin<br />$ crs_stat -t<br />Name Type Target State Host<br />------------------------------------------------------------<br /><br />$ srvctl stop database -d <database_1> or srvctl stop insatnce -d <database_1> -i <database_1_instance><br />$ srvctl stop database -d <database_2> -o immediate or srvctl stop insatnce -d <database_2> -i <database_2_instance><br /><br />$ srvctl stop asm -n <Node 1><br />$ srvctl stop asm -n <Node 2><br /><br />$ srvctl stop listener -n <Node 1><br />$ srvctl stop listener -n <Node 2><br /><br />$ srvctl stop nodeapps -n <Node 1><br />$ srvctl stop nodeapps -n <Node 2><br /><br />$ ps -ef|grep oracle or ps -ef|grep emagent<br />./emctl stop agent or /oradb/app/oracle/product/agent1/agent11g/bin/emctl stop agent<br /><br />$ ps -ef| grep oracle<br />$ lsnrctl stop -------------------------------to stop default listener<br />$ ps -ef| grep oracle<br /><br />------------------------------------------------------------------------------------------------------------------<br /><br />Node 2: (AS ORACLE) <br />===================<br /><br />$ hostname<br />$ uname<br />$ bash<br />$ date<br />$ df -kg or df -h<br />$ cat /etc/oratab or cat/var/opt/oracle/oratab ------> Note ASM and Database Home Paths<br />$ ps -ef| grep pmon<br />$ ps -ef| grep tns<br />$ ps -ef| grep emagent<br />$ ps -ef|grep oracle<br />$ ps -ef| grep d.bin<br />$ ps -ef|grep crs<br /> root 344170 1 0 Apr 24 - 0:00 /oradb/crs/product/11.1.0/crs_1/bin/oclskd.bin<br /><br />$ cd /oradb/app/admin/orasw/patches/PSU_patches/Jan2012<br /><br />$ opatch version<br /><br />$ /oradb/crs/product/11.1.0/crs_1/OPatch/opatch version<br />$ /oradb/asm/product/11.1.0/asm_1/OPatch/opatch version<br />$ /oradb/app/oracle/product/11.1.0/db_1/OPatch/opatch version<br /><br />$ which opatch<br />/oradb/app/oracle/product/11.1.0/db_1/OPatch/opatch<br /><br />$ ps -ef| grep pmon<br /><br />$ ps -ef|grep oracle or ps -ef|grep emagent<br />./emctl status agent or /oradb/app/oracle/product/agent1/agent11g/bin/emctl status agent<br /><br />$ cd /oradb/crs/product/11.1.0/crs_1/bin<br />$ ./crsctl check cluster<br /><br />$ cd /oradb/crs/product/11.1.0/crs_1/bin<br />$ ./crsctl check crs<br /><br />$ cd /oradb/crs/product/11.1.0/crs_1/bin<br />$ crs_stat -t<br />Name Type Target State Host<br />------------------------------------------------------------<br /><br />$ ps -ef|grep oracle or ps -ef|grep emagent<br />./emctl stop agent or /oradb/app/oracle/product/agent1/agent11g/bin/emctl stop agent<br /><br />$ ps -ef| grep oracle<br />$ lsnrctl stop -------------------------------to stop default listener<br />$ ps -ef| grep oracle<br /><br />------------------------------------------------------------------------------------------------------------------<br /><br />Pre-rootpatch<br />=============<br /><br />Node 1: (AS ROOT)<br />=================<br /><br /># cd /oradb/crs/product/11.1.0/crs_1/bin<br /># ./crsctl check cluster<br /># ./crsctl check crs<br /># ./crsctl stop crs<br /><br /># ps -ef| grep oracle<br /><br /># ssh <Node 2><br /><br /># cd /oradb/crs/product/11.1.0/crs_1/bin<br /># ./crsctl check cluster<br /># ./crsctl check crs<br /># ./crsctl stop crs<br /><br /># ps -ef| grep oracle<br /><br /># exit<br /># hostname<br /><Node 1><br /><br /># ps -ef| grep pmon ---- (0 instances running)<br /># cd /oradb/app/admin/orasw/patches/PSU_patches/Jan2012/CRS/111077/11724953<br /># sh custom/scripts/prerootpatch.sh -crshome /oradb/crs/product/11.1.0/crs_1 -crsuser oracle<br /><br /># ssh <Node 2><br /><br /># ps -ef| grep pmon ---- (0 instances running)<br /># cd /oradb/app/admin/orasw/patches/PSU_patches/Jan2012/CRS/111077/11724953<br /># sh custom/scripts/prerootpatch.sh -crshome /oradb/crs/product/11.1.0/crs_1 -crsuser oracle<br /><br /># exit<br /># hostname<br /><Node 1><br /><br />------------------------------------------------------------------------------------------------------------------<br /><br />Pre-patch<br />=========<br /><br />Node 1: (AS ORACLE)<br />===================<br /><br />$ cd /oradb/app/admin/orasw/patches/PSU_patches/Jan2012/CRS/111077/11724953<br />$ custom/scripts/prepatch.sh -crshome /oradb/crs/product/11.1.0/crs_1<br /><br />$ cd /oradb/app/admin/orasw/patches/PSU_patches/Jan2012/CRS/111077/11724953<br />$ custom/server/11724953/custom/scripts/prepatch.sh -dbhome /oradb/asm/product/11.1.0/asm_1<br /><br />$ cd /oradb/app/admin/orasw/patches/PSU_patches/Jan2012/CRS/111077/11724953<br />$ custom/server/11724953/custom/scripts/prepatch.sh -dbhome /oradb/app/oracle/product/11.1.0/db_1<br /><br />------------------------------------------------------------------------------------------------------------------<br /><br />Opatch Apply<br />============<br /><br />Node 1: (AS ORACLE)<br />===================<br /><br />CRS<br />===<br />$ cd /oradb/app/admin/orasw/patches/PSU_patches/Jan2012/CRS/111077<br />$ opatch napply -oh /oradb/crs/product/11.1.0/crs_1 -id 11724953<br /><br />ASM<br />===<br />$ cd /oradb/app/admin/orasw/patches/PSU_patches/Jan2012/CRS/111077/11724953/custom/server<br />$ opatch napply custom/server/ -oh /oradb/asm/product/11.1.0/asm_1 -id 11724953<br /><br />RDBMS<br />=====<br />$ cd /oradb/app/admin/orasw/patches/PSU_patches/Jan2012/CRS/111077/11724953/custom/server<br />$ opatch napply custom/server/ -oh /oradb/app/oracle/product/11.1.0/db_1 -id 11724953<br /><br />------------------------------------------------------------------------------------------------------------------<br /><br />Do the post configure for CRS and RDBMS home<br />============================================<br /><br />Node 1: (AS ORACLE)<br />===================<br /><br />CRS<br />===<br />$ cd /oradb/app/admin/orasw/patches/PSU_patches/Jan2012/CRS/111077/11724953/custom/scripts<br />$ ./postpatch.sh -crshome /oradb/crs/product/11.1.0/crs_1<br /><br />ASM<br />===<br />$ cd /oradb/app/admin/orasw/patches/PSU_patches/Jan2012/CRS/111077/11724953/custom/server/11724953<br />$ ./custom/scripts/postpatch.sh -dbhome /oradb/asm/product/11.1.0/asm_1<br /><br />RDBMS<br />=====<br />$ cd /oradb/app/admin/orasw/patches/PSU_patches/Jan2012/CRS/111077/11724953/custom/server/11724953<br />$ ./custom/scripts/postpatch.sh -dbhome /oradb/app/oracle/product/11.1.0/db_1<br /><br />------------------------------------------------------------------------------------------------------------------<br /><br />11.1.0.7.11 PSU (13621679) and (9734685)<br />========================================<br /><br />Node 1: (AS ORACLE)<br />===================<br /><br />13621679<br />========<br /><br />$ cd /oradb/app/admin/orasw/patches<br />$ chmod -R 777 PSU_patches<br />$ cd PSU_patches<br />$ pwd <br />/oradb/app/admin/orasw/patches/PSU_patches<br /><br />$ ls -lrt p13621679_111070_AIX5L.zip<br />$ unzip p13621679_111070_AIX5L.zip<br />$ ls -lrt<br />$ cd 13621679<br />$ pwd<br /><br />ASM<br />===<br />$ /oradb/app/admin/orasw/patches/PSU_patches/13621679<br />$ opatch apply -oh /oradb/asm/product/11.1.0/asm_1<br /><br />RDBMS<br />=====<br />$ cd /oradb/app/admin/orasw/patches/PSU_patches/13621679<br />$ opatch apply -oh /oradb/app/oracle/product/11.1.0/db_1<br /><br />------------------------------------------------------------------------------------------------------------------<br /><br />9734685<br />=======<br /><br />$ cd /oradb/app/admin/orasw/patches/PSU_patches/<br />$ ls -lrt p9734685_1110711_AIX64-5L.zip<br />$ unzip p9734685_1110711_AIX64-5L.zip<br /><br />ASM<br />===<br />$ cd /oradb/app/admin/orasw/patches/PSU_patches/9734685<br />$ opatch apply -oh /oradb/asm/product/11.1.0/asm_1<br /><br />RDBMS<br />=====<br />$ cd /oradb/app/admin/orasw/patches/PSU_patches/9734685<br />$ opatch apply -oh /oradb/app/oracle/product/11.1.0/db_1<br /><br />------------------------------------------------------------------------------------------------------------------<br /><br />After 9734685 Patch applied relink has to be done in both ASM and DB homes ------- Linking Oracle<br />==========================================================================<br /><br />Node 1: (AS ORACLE)<br />===================<br /><br />RDBMS<br />=====<br />$ cd /oradb/app/oracle/product/11.1.0/db_1/rdbms/lib<br />$ make -f ins_rdbms.mk ipc_g ioracle<br /><br />ASM<br />===<br />$ cd /oradb/asm/product/11.1.0/asm_1/rdbms/lib<br />$ make -f ins_rdbms.mk ipc_g ioracle<br /><br />Node 2: (AS ORACLE)<br />=======<br /><br />RDBMS<br />=====<br />$ cd /oradb/app/oracle/product/11.1.0/db_1/rdbms/lib<br />$ make -f ins_rdbms.mk ipc_g ioracle<br /><br />ASM<br />===<br />$ cd /oradb/asm/product/11.1.0/asm_1/rdbms/lib<br />$ make -f ins_rdbms.mk ipc_g ioracle<br /><br />------------------------------------------------------------------------------------------------------------------<br /><br />Post-rootpatch<br />==============<br /><br />Node 1: (AS ROOT)<br />=================<br /><br /># cd /oradb/app/admin/orasw/patches/PSU_patches/Jan2012/CRS/111077/11724953<br /><br /># /oradb/app/admin/orasw/patches/PSU_patches/Jan2012/CRS/111077/11724953/custom/scripts/postrootpatch.sh -crshome /oradb/crs/product/11.1.0/crs_1<br /><br /># ps -ef | grep pmon --- Both the db and asm instances will be up and running<br /><br /># ssh <Node 2><br /><br /># cd /oradb/app/admin/orasw/patches/PSU_patches/Jan2012/CRS/111077/11724953/custom/scripts/postrootpatch.sh -crshome /oradb/crs/product/11.1.0/crs_1<br /><br /># ps -ef | grep pmon --- Both the db and asm instances will be up and running<br /><br />------------------------------------------------------------------------------------------------------------------<br /><br />Start ASM<br /><br />------------------------------------------------------------------------------------------------------------------<br /><br />Run on each database, only in one node<br />======================================<br /><br />NODE 1: (AS ORACLE)<br />===================<br /><br />$ sqlplus /nolog<br />Enter user-name: / as sysdba<br />Connected to an idle instance.<br /><br />SQL>startup;<br />SQL>@?/rdbms/admin/catbundle.sql psu apply<br />SQL>exit<br /><br />$. oraenv ---- --- database 2 instance<br />$ sqlplus /nolog<br />Enter user-name: / as sysdba<br /><br />SQL>startup;<br />SQL>@?/rdbms/admin/catbundle.sql psu apply<br />SQL>exit<br /><br />------------------------------------------------------------------------------------------------------------------<br /><br />Patch Validation:-<br />================<br /><br />opatch lsinventory | grep 11724953<br />opatch lsinventory | grep 13621679<br />opatch lsinventory | grep 9734685<br /><br />start local listeners, (if any)<br /><br />$ cd /oradb/crs/product/11.1.0/crs_1/bin<br />$ ./crsctl check cluster<br /><br />$ cd /oradb/crs/product/11.1.0/crs_1/bin<br />$ ./crsctl check crs<br /><br />$ cd /oradb/crs/product/11.1.0/crs_1/bin<br />$ crs_stat -t<br />Name Type Target State Host<br />------------------------------------------------------------<br /><br />$ hostname<br />$ uname<br />$ bash<br />$ date<br />$ who -b<br />$ uptime<br />$ df -kg or df -h<br />$ cat /etc/oratab or cat/var/opt/oracle/oratab ------> Note ASM and Database Home Paths<br />$ ps -ef| grep pmon<br />$ ps -ef| grep tns<br />$ ps -ef| grep emagent<br />$ ps -ef|grep oracle<br />$ ps -ef| grep d.bin<br />$ ps -ef|grep crs<br /><br />$ ps -ef| grep pmon<br /><br />$ srvctl status database -d <database_1> <br />$ srvctl status database -d <database_2> <br /><br />$ srvctl status asm -n <Node 1><br />$ srvctl status asm -n <Node 2><br /><br />$ srvctl status listener -n <Node 1><br />$ srvctl status listener -n <Node 2><br /><br />$ srvctl status nodeapps -n <Node 1><br />$ srvctl status nodeapps -n <Node 2><br /><br />$ ps -ef|grep oracle or ps -ef|grep emagent<br />./emctl status agent or /oradb/app/oracle/product/agent1/agent11g/bin/emctl status agent<br /><br />Run on each database only in one node<br />=====================================<br /><br />NODE 1: (AS ORACLE)<br />===================<br />SQL> select name db_name,DB_UNIQUE_NAME,instance_name,status,DATABASE_STATUS,open_mode, database_role, host_name,platform_id,version db_version,log_mode,flashback_on ,protection_mode,protection_level,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "UP TIME" from gv$instance,v$database;<br /><br />SQL> select to_char(sysdate,'yyyy-mm-dd HH24:MI:SS') from dual<br />/<br /><br />SQL> <br />col ACTION_TIME for a12<br />col NAMESPACE for a10<br />col VERSION for a10<br />col BUNDLE_SERIES for a15<br />col COMMENTS for a20<br />select action_time, action,namespace, version, id,bundle_series, comments from registry$history;<br /><br />------------------------------------------------------------------------------------------------------------------<br /><br />check DR sysnc status<br />=====================<br />http://select-star-from.blogspot.in/2013/09/data-guard-sync-status.html<br /><br />------------------------------------------------------------------------------------------------------------------<br /><br />Stop OEM black out<br />==================<br /><br />Confirmation <br />Request to stop blackout "Blackout-Apr 24 1998 9:33:42 PM" was processed successfully, Blackout status will be propagated to the target(s) shortly. <br /><br />------------------------------------------------------------------------------------------------------------------</div>
Anonymoushttp://www.blogger.com/profile/04664995477596294650noreply@blogger.com0tag:blogger.com,1999:blog-3992239225529976052.post-559859566569593852013-10-07T06:08:00.000+05:302013-10-07T06:08:32.356+05:30 Oracle DBA Interview Questions and Answers - RAC - 2<div dir="ltr" style="text-align: left;" trbidi="on">
<b>Oracle RAC Interview Questions and Answers </b><br /><br />1. Where are the Clusterware files stored on a RAC environment? <br />The Clusterware is installed on each node (on an Oracle Home) and on the shared disks (the voting disks and the CSR file) <br /><br />2. Where are the database software files stored on a RAC environment? <br />The base software is installed on each node of the cluster and the <br />database storage on the shared disks. <br /><br />3. What kind of storage we can use for the shared Clusterware files? <br />- OCFS (Release 1 or 2) <br />- raw devices <br />- third party cluster file system such as GPFS or Veritas <br /><br />4. What kind of storage we can use for the RAC database storage? <br />- OCFS (Release 1 or 2) <br />- ASM <br />- raw devices <br />- third party cluster file system such as GPFS or Veritas <br /><br />5. What is a CFS? <br />A cluster File System (CFS) is a file system that may be accessed (read and write) by all members in a cluster at the same time. This implies that all members of a cluster have the same view. <br /><br />6. What is an OCFS2? <br />The OCFS2 is the Oracle (version 2) Cluster File System which can be used for the Oracle Real Application Cluster. <br /><br />7. Which files can be placed on an Oracle Cluster File System? <br />- Oracle Software installation (Windows only) <br />- Oracle files (controlfiles, datafiles, redologs, files described by the bfile datatype) <br />- Shared configuration files (spfile) <br />- OCR and voting disk <br />- Files created by Oracle during runtime <br />Note: There are some platform specific limitations. <br /><br />8. Do you know another Cluster Vendor? <br />HP Tru64 Unix, Veritas, Microsoft <br /><br />9. How is possible to install a RAC if we don’t have a CFS? <br />This is possible by using a raw device. <br /><br />10. What is a raw device? <br />A raw device is a disk drive that does not yet have a file system set up. Raw devices are used for Real Application Clusters since they enable the sharing of disks. <br /><br />11. What is a raw partition? <br />A raw partition is a portion of a physical disk that is accessed at the lowest possible level. A raw partition is created when an extended partition is created and logical partitions are assigned to it without any formatting. Once formatting is complete, it is called cooked partition. <br /><br />12. When to use CFS over raw? <br />A CFS offers: <br />- Simpler management <br />- Use of Oracle Managed Files with RAC <br />- Single Oracle Software installation <br />- Autoextend enabled on Oracle datafiles <br />- Uniform accessibility to archive logs in case of physical node failure <br />- With Oracle_Home on CFS, when you apply Oracle patches CFS guarantees that the updated Oracle_Home is visible to all nodes in the cluster. <br />Note: This option is very dependent on the availability of a CFS on your platform. <br /><br />13. When to use raw over CFS? <br />- Always when CFS is not available or not supported by Oracle. <br />- The performance is very, very important: Raw devices offer best performance without any intermediate layer between Oracle and the disk. <br />Note: Autoextend fails on raw devices if the space is exhausted. However the space could be added online if needed. <br /><br />14. What CRS is? <br />Oracle RAC 10g Release 1 introduced Oracle Cluster Ready Services (CRS), a platform-independent set of system services for cluster environments. In Release 2, Oracle has renamed this product to Oracle Clusterware. <br /><br />15. What is VIP IP used for? <br />It returns a dead connection IMMIDIATELY, when its primary node fails. Without using VIP IP, the clients have to wait around 10 minutes to receive ORA-3113: “end of file on communications channel”. However, using Transparent Application Failover (TAF) could avoid ORA-3113. <br /><br />16. Why we need to have configured SSH or RSH on the RAC nodes? <br />SSH (Secure Shell,10g+) or RSH (Remote Shell, 9i+) allows “oracle” UNIX account connecting to another RAC node and copy/ run commands as the local “oracle” UNIX account. <br /><br />17. Is the SSH, RSH needed for normal RAC operations? <br />No. SSH or RSH are needed only for RAC, patch set installation and clustered database creation. <br /><br />18. Do we have to have Oracle RDBMS on all nodes? <br />Each node of a cluster that is being used for a clustered database will typically have the RDBMS and RAC software loaded on it, but not actual data files (these need to be available via shared disk). <br /><br />19. What are the restrictions on the SID with a RAC database? Is it limited to 5 characters? <br />The SID prefix in 10g Release 1 and prior versions was restricted to five characters by install/ config tools so that an ORACLE_SID of up to max of 5+3=8 characters can be supported in a RAC environment. The SID prefix is relaxed up to 8 characters in 10g Release 2, see bug 4024251 for more information. <br /><br />20. Does Real Application Clusters support heterogeneous platforms? <br />The Real Application Clusters do not support heterogeneous platforms in the same cluster. <br /><br />21. Are there any issues for the interconnect when sharing the same switch as the public network by using VLAN to separate the network? <br />RAC and Clusterware deployment best practices suggests that the interconnect (private connection) be deployed on a stand-alone, physically separate, dedicated switch. On big network the connections could be instable. <br /><br />22. What is the Load Balancing Advisory? <br />To assist in the balancing of application workload across designated resources, Oracle Database 10g Release 2 provides the Load Balancing Advisory. This Advisory monitors the current workload activity across the cluster and for each instance where a service is active; it provides a percentage value of how much of the total workload should be sent to this instance as well as service quality flag. <br /><br />23. How many nodes are supported in a RAC Database? <br />With 10g Release 2, we support 100 nodes in a cluster using Oracle Clusterware, and 100 instances in a RAC database. Currently DBCA has a bug where it will not go beyond 63 instances. There is also a documentation bug for the max-instances parameter. With 10g Release 1 the Maximum is 63. <br /><br />24. What is the Cluster Verification Utiltiy (cluvfy)? <br />The Cluster Verification Utility (CVU) is a validation tool that you can use to check all the important components that need to be verified at different stages of deployment in a RAC environment. <br /><br />25. What versions of the database can I use the cluster verification utility (cluvfy) with? <br />The cluster verification utility is release with Oracle Database 10g Release 2 but can also be used with Oracle Database 10g Release 1. <br /><br />26. If I am using Vendor Clusterware such as Veritas, IBM, Sun or HP, do I still need Oracle Clusterware to run Oracle RAC 10g? <br />Yes. When certified, you can use Vendor Clusterware however you must still install and use Oracle Clusterware for RAC. Best Practice is to leave Oracle Clusterware to manage RAC. For details see Metalink Note 332257.1 and for Veritas SFRAC see 397460.1. <br /><br />27. Is RAC on VMW are supported? <br />Yes. <br /><br />28. What is hangcheck timer used for ? <br />The hangcheck timer checks regularly the health of the system. If the system hangs or stop the node will be restarted automatically. <br /><br />There are 2 key parameters for this module: <br /><br />-> hangcheck-tick: this parameter defines the period of time between checks of system health. The default value is 60 seconds; Oracle recommends setting it to 30seconds. <br /><br />-> hangcheck-margin: this defines the maximum hang delay that should be tolerated before hangcheck-timer resets the RAC node. <br /><br />29. Is the hangcheck timer still needed with Oracle RAC 10g? <br />Yes. <br /><br />30. What files can I put on Linux OCFS2? <br />For optimal performance, you should only put the following files on Linux OCFS2: <br />- Datafiles <br />- Control Files <br />- Redo Logs <br />- Archive Logs <br />- Shared Configuration File (OCR) <br />- Voting File <br />- SPFILE <br /><br />31. Is it possible to use ASM for the OCR and voting disk? <br />No, the OCR and voting disk must be on raw or CFS (cluster file system). <br /><br />32. Can I change the name of my cluster after I have created it when I am using Oracle Clusterware? <br />No, you must properly uninstall Oracle Clusterware and then re-install. <br /><br />33. What the O2CB is? <br />The O2CB is the OCFS2 cluster stack. OCFS2 includes some services. These services must be started before using OCFS2 (mount/ format the file systems). <br /><br />34. What the OCR file is used for? <br />OCR is a file that manages the cluster and RAC configuration. <br /><br />35. What the Voting Disk file is used for? <br />The voting disk is nothing but a file that contains and manages information of all the node memberships. <br /><br />36. What is the recommended method to make backups of a RAC environment? <br />RMAN to make backups of the database, dd to backup your voting disk and hard copies of the OCR file. <br /><br />37. What command would you use to check the availability of the RAC system? <br />crs_stat -t -v (-t -v are optional) <br /><br />38. What is the minimum number of instances you need to have in order to create a RAC? <br />You can create a RAC with just one server. <br /><br />39. Name two specific RAC background processes <br />RAC processes are: LMON, LMDx, LMSn, LKCx and DIAG. <br /><br />40.Can you have many database versions in the same RAC? <br />Yes, but Clusterware version must be greater than the greater database version. <br /><br />41. What was RAC previous name before it was called RAC? <br />OPS: Oracle Parallel Server <br /><br />42. What RAC component is used for communication between instances? <br />Private Interconnect. <br /><br />43. What is the difference between normal views and RAC views? <br />A RAC view has the prefix ‘G’. For example, GV$SESSION instead of V$SESSION <br /><br />44. Which command will we use to manage (stop, start) RAC services in command-line mode? <br />srvctl <br /><br />45. How many alert logs exist in a RAC environment? <br />One for each instance. <br /><br />46. What are Oracle Clusterware Components <br />Voting Disk — Oracle RAC uses the voting disk to manage cluster membership by way of a health check and arbitrates cluster ownership among the instances in case of network failures. The voting disk must reside on shared disk. <br /><br />Oracle Cluster Registry (OCR) — Maintains cluster configuration information as well as configuration information about any cluster database within the cluster. The OCR must reside on shared disk that is accessible by all of the nodes in your cluster <br /><br />47. How do you backup voting disk <br />#dd if=voting_disk_name of=backup_file_name <br /><br />48. How do I identify the voting disk location <br />#crsctl query css votedisk <br /><br />49. How do I identify the OCR file location <br />check /var/opt/oracle/ocr.loc or /etc/ocr.loc ( depends upon platform) <br />or <br />#ocrcheck <br /><br />50. How do you backup the OCR <br />There is an automatic backup mechanism for OCR. The default location is : $ORA_CRS_HOME\cdata\"clustername"\ <br />To display backups : <br />#ocrconfig -showbackup <br />To restore a backup : <br />#ocrconfig -restore <br />With Oracle RAC 10g Release 2 or later, you can also use the export command: <br />#ocrconfig -export -s online, and use -import option to restore the contents back. <br />With Oracle RAC 11g Release 1, you can do a manaual backup of the OCR with the command: <br /># ocrconfig -manualbackup <br /><br />51. What is SCAN? <br />Single Client Access Name (SCAN) is s a new Oracle Real Application Clusters (RAC) 11g Release 2 feature that provides a single name for clients to access an Oracle Database running in a cluster. The benefit is clients using SCAN do not need to change if you add or remove nodes in the cluster. <br /><br />52. What are Oracle Clusterware processes for 10g on Unix and Linux <br />Cluster Synchronization Services (ocssd) — Manages cluster node membership and runs as the oracle user; failure of this process results in cluster restart. <br /><br />Cluster Ready Services (crsd) — The crs process manages cluster resources (which could be a database, an instance, a service, a Listener, a virtual IP (VIP) address, an application process, and so on) based on the resource's configuration information that is stored in the OCR. This includes start, stop, monitor and failover operations. This process runs as the root user <br /><br />Event manager daemon (evmd) —A background process that publishes events that crs creates. <br /><br />Process Monitor Daemon (OPROCD) —This process monitor the cluster and provide I/O fencing. OPROCD performs its check, stops running, and if the wake up is beyond the expected time, then OPROCD resets the processor and reboots the node. An OPROCD failure results in Oracle Clusterware restarting the node. OPROCD uses the hangcheck timer on Linux platforms. <br /><br />RACG (racgmain, racgimon) —Extends clusterware to support Oracle- specific requirements and complex resources. Runs server callout scripts when FAN events occur. <br /><br />53. What are Oracle database background processes specific to RAC <br />•LMS—Global Cache Service Process <br />•LMD—Global Enqueue Service Daemon <br />•LMON—Global Enqueue Service Monitor <br />•LCK0—Instance Enqueue Process <br />To ensure that each Oracle RAC database instance obtains the block that it needs to satisfy a query or transaction, Oracle RAC instances use two processes, the Global Cache Service (GCS) and the Global Enqueue Service (GES). The GCS and GES maintain records of the statuses of each data file and each cached block using a Global Resource Directory (GRD). The GRD contents are distributed across all of the active instances. <br /><br />54. How do you troubleshoot node reboot <br />Please check metalink ... <br />Note 265769.1 Troubleshooting CRS Reboots <br />Note.559365.1 Using Diagwait as a diagnostic to get more information for diagnosing Oracle Clusterware Node evictions. <br /><br />55. Is ssh required for normal Oracle RAC operation ? <br />"ssh" are not required for normal Oracle RAC operation. However "ssh" should be enabled for Oracle RAC and patchset installation. <br /><br />56. What is the purpose of Private Interconnect ? <br />Clusterware uses the private interconnect for cluster synchronization (network heartbeat) and daemon communication between the the clustered nodes. This communication is based on the TCP protocol. <br />RAC uses the interconnect for cache fusion (UDP) and inter-process communication (TCP). Cache Fusion is the remote memory mapping of Oracle buffers, shared between the caches of participating nodes in the cluster. <br /><br />57. Why do we have a Virtual IP (VIP) in Oracle RAC? <br />Without using VIPs or FAN, clients connected to a node that died will often wait for a TCP timeout period (which can be up to 10 min) before getting an error. As a result, you don't really have a good HA solution without using VIPs. <br /><br />When a node fails, the VIP associated with it is automatically failed over to some other node and new node re-arps the world indicating a new MAC address for the IP. Subsequent packets sent to the VIP go to the new node, which will send error RST packets back to the clients. This results in the clients getting errors immediately. <br /><br />58. What do you do if you see GC CR BLOCK LOST in top 5 Timed Events in AWR Report? <br />This is most likely due to a fault in interconnect network. <br />Check netstat -s <br />if you see "fragments dropped" or "packet reassemblies failed" , Work with your system administrator find the fault with network. <br /><br />59. How many nodes are supported in a RAC Database? <br />10g Release 2, support 100 nodes in a cluster using Oracle Clusterware, and 100 instances in a RAC database. <br /><br />60. Srvctl cannot start instance, I get the following error PRKP- 1001 CRS-0215, however sqlplus can start it on both nodes? How do you identify the problem? <br />Set the environmental variable SRVM_TRACE to true.. And start the instance with srvctl. Now you will get detailed error stack. <br /><br />61. What is the purpose of the ONS daemon? <br />The Oracle Notification Service (ONS) daemon is an daemon started by the CRS clusterware as part of the nodeapps. There is one ons daemon started per clustered node. <br />The Oracle Notification Service daemon receive a subset of published clusterware events via the local evmd and racgimon clusterware daemons and forward those events to application subscribers and to the local listeners. <br /><br />This in order to facilitate: <br /><br />a. the FAN or Fast Application Notification feature or allowing applications to respond to database state changes. <br /><br />b. the 10gR2 Load Balancing Advisory, the feature that permit load balancing accross different rac nodes dependent of the load on the different nodes. The rdbms MMON is creating an advisory for distribution of work every 30seconds and forward it via racgimon and ONS to listeners and applications. </div>
Anonymoushttp://www.blogger.com/profile/04664995477596294650noreply@blogger.com1tag:blogger.com,1999:blog-3992239225529976052.post-6871926759009009082013-09-30T00:30:00.001+05:302013-09-30T00:30:20.766+05:30Script to Gather data from the Linux OS<div dir="ltr" style="text-align: left;" trbidi="on">
#####################################################################<br />
### Unix script os_stats.sh ###<br />
### Designed to be run periodically to collate information ###<br />
### START OF SCRIPT ###<br />
#####################################################################<br />
#<br />
LOG_FILE="OS_`hostname`_`date '+%m%d%y_%H%M'`.txt"<br />
#<br />
echo "**********************************************" >$LOG_FILE<br />
date >> $LOG_FILE<br />
echo "Running as `id`" >> $LOG_FILE<br />
echo "**********************************************" >>$LOG_FILE<br />
echo "uname -a" >>$LOG_FILE<br />
uname -a >>$LOG_FILE<br />
cat /etc/issue >>$LOG_FILE<br />
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE<br />
echo "ulimit -a" >>$LOG_FILE<br />
ulimit -a >>$LOG_FILE<br />
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE<br />
echo "ulimit -Ha" >>$LOG_FILE<br />
ulimit -Ha >>$LOG_FILE<br />
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE<br />
echo "Netstat -i" >>$LOG_FILE<br />
netstat -i >> $LOG_FILE<br />
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE<br />
echo "netstat -an">> $LOG_FILE<br />
netstat -an >> $LOG_FILE<br />
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE<br />
echo "netstat -s">> $LOG_FILE<br />
netstat -s >> $LOG_FILE<br />
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE<br />
echo "sar -u 5 3">> $LOG_FILE<br />
sar -u 5 3 >> $LOG_FILE<br />
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE<br />
echo "sar -q 5 3">> $LOG_FILE<br />
sar -q 5 3 >> $LOG_FILE<br />
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE<br />
echo "ps -e -w -o user,pid,ppid,s,pcpu,pmem,vsz,rss,stime,time,args" >> $LOG_FILE<br />
ps -e -ww -o user,pid,ppid,s,pcpu,pmem,vsz,rss,stime,time,args >>$LOG_FILE<br />
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE<br />
echo "iostat -t -x" >> $LOG_FILE<br />
iostat -t -x >>$LOG_FILE<br />
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE<br />
echo "df -h" >> $LOG_FILE<br />
df -h >>$LOG_FILE<br />
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE<br />
echo "mpstat 5 3" >> $LOG_FILE<br />
/usr/bin/mpstat 5 3 >>$LOG_FILE<br />
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE<br />
echo "vmstat 5 3" >> $LOG_FILE<br />
vmstat 5 3 >>$LOG_FILE<br />
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE<br />
echo "free -m -s 5 -c 3" >> $LOG_FILE<br />
free -m -s 5 -c 3 >>$LOG_FILE<br />
grep MemTotal /proc/meminfo >>$LOG_FILE<br />
grep SwapTotal /proc/meminfo >>$LOG_FILE<br />
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE<br />
echo "IPCS data" >> $LOG_FILE<br />
ipcs -l >> $LOG_FILE<br />
echo " ---------------------------- " >> $LOG_FILE<br />
ipcs -u >> $LOG_FILE<br />
echo " ---------------------------- " >> $LOG_FILE<br />
ipcs >> $LOG_FILE<br />
echo " ---------------------------- " >> $LOG_FILE<br />
ipcs -t >> $LOG_FILE<br />
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE<br />
#####################################################################<br />
### END OF SCRIPT ###<br />
#####################################################################</div>
Anonymoushttp://www.blogger.com/profile/04664995477596294650noreply@blogger.com0tag:blogger.com,1999:blog-3992239225529976052.post-79950889756721780452013-09-30T00:20:00.001+05:302013-09-30T00:20:14.661+05:30Script to Gather data from the database for Process information and User connections<div dir="ltr" style="text-align: left;" trbidi="on">
REM<br />
REM START OF SQL<br />
REM<br />
REM Filename : mzRunSQL.sql<br />
REM Author : Mike Shaw<br />
REM Date Updated : 29 August 2008<br />
REM Purpose : Script to gather data from the database for process<br />
REM information and user connections<br />
REM<br />
set serveroutput on size 1000000<br />
set echo on<br />
set timing on<br />
set feedback on<br />
set long 10000<br />
set pagesize 132<br />
set linesize 110<br />
col username form a10<br />
col program form a30<br />
col how_many forma 9999<br />
col machine form a25<br />
col module form a50<br />
--<br />
rem show time<br />
select to_char(sysdate, 'DD-MON-RR HH24:MI:SS') START_TIME from dual<br />
/<br />
rem Instance identification<br />
select *<br />
from v$instance<br />
/<br />
REM Summary of database connections<br />
select s.module, s.machine, s.username, count(*) how_many<br />
from (select distinct PROGRAM, PADDR, machine, username, module, inst_id from gV$SESSION) s,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>gv$process p<br />
where s.paddr = p.addr<br />
and p.inst_id = s.inst_id<br />
group by rollup(s.module,s.machine,s.username)<br />
/<br />
REM Connections by machine and instance<br />
select s.machine, s.username, s.module, s.inst_id, count(*) how_many<br />
from (select distinct PROGRAM, PADDR, machine, username, module, inst_id from gV$SESSION) s,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>gv$process p<br />
where s.paddr = p.addr<br />
and p.inst_id = s.inst_id<br />
group by s.machine,s.username, s.module, s.inst_id<br />
/<br />
REM<br />
REM Get count of number of connected Apps 11i users<br />
REM<br />
col user_name format a15<br />
col first_connect format a18<br />
col last_connect format a18<br />
col How_many_user_sessions format 9999999999<br />
col How_many_sessions format 9999999999<br />
REM<br />
REM Summary of how many users<br />
REM<br />
select 'Number of user sessions : ' || count( distinct session_id) How_many_user_sessions<br />
from icx_sessions icx<br />
where disabled_flag != 'Y'<br />
and PSEUDO_FLAG = 'N'<br />
and (last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,limit_time, 0,limit_time,FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate <br />
and counter < limit_connects<br />
/<br />
REM<br />
REM Number of sessions per user<br />
REM<br />
select user_name, count(*) How_many_sessions<br />
from icx_sessions icx, fnd_user u<br />
where icx.user_id = u.user_id<br />
and disabled_flag != 'Y'<br />
and PSEUDO_FLAG = 'N'<br />
and (last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,limit_time, 0,limit_time,FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate <br />
and counter < limit_connects<br />
group by user_name<br />
order by 2 desc<br />
/<br />
REM<br />
REM Quick look for blockers and lockers<br />
REM<br />
col BLOCKER form a5<br />
col Username format A15<br />
col Sid format 9990 heading SID<br />
col Type format A4<br />
col Lmode format 990 heading 'HELD'<br />
col Request format 990 heading 'REQ'<br />
col Id1 format 999999999999<br />
col Id2 format 999999999999<br />
break on Id1 skip 1 dup<br />
--<br />
SELECT <span class="Apple-tab-span" style="white-space: pre;"> </span>M.Sid,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>M.Type,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>DECODE(M.Lmode, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive',LTRIM(TO_CHAR(Lmode,'990'))) Lmode,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>DECODE(M.Request, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive',<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>LTRIM(TO_CHAR(M.Request, '990'))) Request,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>M.Id1,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>M.Id2,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>DECODE(block, 0, 'NO', 'YES' ) BLOCKER<br />
FROM V$LOCK M<br />
WHERE<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>M.Request ! = 0<span class="Apple-tab-span" style="white-space: pre;"> </span>or (M.Request = 0 and Lmode != 4 and (id1, id2) in (select S.Id1, S.Id2 from V$LOCK S where Request != 0 and S.Id1 = M.Id1 and S.Id2 = M.Id2) )<br />
ORDER BY Id1, Id2, M.Request<br />
/<br />
REM<br />
REM Check for database statement leaks<br />
REM May give symptoms like OutOfMemoryError<br />
REM<br />
set lines 132<br />
set pages 1000<br />
column module format A40<br />
column program format A30<br />
select oc.sid,oc.hash_value,oc.sql_text,count(*) How_Many<br />
from gv$open_cursor oc<br />
group by sid,hash_value,sql_text<br />
having count(*) > 5<br />
order by 4<br />
/<br />
rem show time<br />
select to_char(sysdate, 'DD-MON-RR HH24:MI:SS') END_TIME from dual<br />
/<br />
exit;<br />
REM<br />
REM END OF SQL<br />
REM </div>
Anonymoushttp://www.blogger.com/profile/04664995477596294650noreply@blogger.com0tag:blogger.com,1999:blog-3992239225529976052.post-53312734327409314012013-09-16T17:50:00.001+05:302013-09-16T17:50:19.018+05:30Speech by Chetan Bhagat at Symbiosis<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgkzYmUzC62Jk9nByhn8YZsf7ytIyapy_ZwMqis8d2ELS7dVCBX0ri8cHmbYN7DuAWW9mjSVh89DBefyb93IBwdwAuDQNP3n9O67KQMRux2VIEG1pxfJlnI4ZDRRiJgMdkzYwIsCfUASTGx/s1600/163832_178674665498435_100000677078797_446761_3117214_n.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgkzYmUzC62Jk9nByhn8YZsf7ytIyapy_ZwMqis8d2ELS7dVCBX0ri8cHmbYN7DuAWW9mjSVh89DBefyb93IBwdwAuDQNP3n9O67KQMRux2VIEG1pxfJlnI4ZDRRiJgMdkzYwIsCfUASTGx/s1600/163832_178674665498435_100000677078797_446761_3117214_n.jpg" /></a></div>
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsEQqlF2gRoTM_vhZvgW50d-UtDuAdF9nQaOPKF_heOlPDTsZAJ7Dh_jDOI2Croa4tBmLecRPUMuq5drKM07jpUcWx7w-VLsWP7wQj_OZlMFXmIxnY1PPbwK9-wQRkDJ4wwQLbHY4VRtR5/s1600/chetan-bhagat.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsEQqlF2gRoTM_vhZvgW50d-UtDuAdF9nQaOPKF_heOlPDTsZAJ7Dh_jDOI2Croa4tBmLecRPUMuq5drKM07jpUcWx7w-VLsWP7wQj_OZlMFXmIxnY1PPbwK9-wQRkDJ4wwQLbHY4VRtR5/s1600/chetan-bhagat.png" /></a></div>
<br /></div>
Anonymoushttp://www.blogger.com/profile/04664995477596294650noreply@blogger.com0tag:blogger.com,1999:blog-3992239225529976052.post-41902953480014089022013-09-15T21:10:00.002+05:302013-09-15T21:10:41.567+05:30RECOVERY Methods in Oracle<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgwjUOjgvnxQz_r0Fh3Ce2Iah4F2e7Ne4-bUDqUknXEhw1F99-bYHUX308dQmctpBkBdb_Y1fjv8gBy0F4rXBCjmFE8Vy9SBH-Zt-uRY4ckGs9ND8Lbkg6lbrw2TghDL8YOUC2qOZC3Vcw-/s1600/rman_recovery_methods.gif" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgwjUOjgvnxQz_r0Fh3Ce2Iah4F2e7Ne4-bUDqUknXEhw1F99-bYHUX308dQmctpBkBdb_Y1fjv8gBy0F4rXBCjmFE8Vy9SBH-Zt-uRY4ckGs9ND8Lbkg6lbrw2TghDL8YOUC2qOZC3Vcw-/s1600/rman_recovery_methods.gif" /></a></div>
<br />
<br />
<br />
<br />
<br />
In general there are three steps involved in restoring files:<br />
<br />Ensure that the target database is started in the appropriate mode for the restoration operation. For lost control files, this will be nomount mode. If the entire database needs to be restored, this will be mount mode. If datafiles that don't belong to the SYSTEM tablespace are damaged, you have the option of keeping the database open and taking only the tablespace(s)/datafile(s) that needs to be restored offline.<br />Start RMAN and connect to the target and recovery catalog if one is being used.<br />Run the appropriate RMAN RESTORE command to bring back required files. The requested files and the appropriate archived redo log files will be restored.<br />Once the necessary files are restored, you need to recover your database and open it for use. You can recover the database from either RMAN or SQL*Plus.<br /><br />
</div>
Anonymoushttp://www.blogger.com/profile/04664995477596294650noreply@blogger.com0tag:blogger.com,1999:blog-3992239225529976052.post-68406453999621074262013-09-13T00:29:00.000+05:302013-09-13T00:29:24.583+05:30ORA-01502 index or partition of such index is in unusable state<div dir="ltr" style="text-align: left;" trbidi="on">
<b>ERROR:-<br />-----</b><br />ORA-01502: index "owner.index_name" or partition of such index is in unusable state<br /><br /><b>SOLUTION:-<br />--------</b><br />REBUILD UNUSABLE INDEXES in Oracle Database<br /><br />$sqlplus "/as sysdba"<br /><br />select owner,index_name,table_name,status from dba_indexes where index_name='&index_name';<br />OWNER INDEX_NAME TABLE_NAME STATUS<br />----- ---------- ---------- ------<br />owner index_name table_name INVALID<br /><br />select owner,segment_name,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where segment_name='&segment_name';<br />OWNER SEGMENT_NAME SIZE IN MB <br />----- ------------ ---------- <br />owner index_name 3000 <br /><br />alter session set current_schema='&schema_name';<br />Session altered.<br /><br />alter index <index_name> rebuild;<br />Index altered.<br /><br />select owner,index_name,table_name,status from dba_indexes where index_name='&index_name';<br />OWNER INDEX_NAME TABLE_NAME STATUS<br />----- ---------- ---------- ------<br />owner index_name table_name VALID<br /><br />select owner,segment_name,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where segment_name='&segment_name';<br />OWNER SEGMENT_NAME SIZE IN MB <br />----- ------------ ---------- <br />owner index_name 800<br /><br />------------------------------------ OR ------------------------------------------------------<br /><br />SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD;' FROM DBA_INDEXES WHERE STATUS = 'UNUSABLE';<br /><br />This will output statements for all "unusable" indexes. Run them, so that the indexes can be "usable" again.<br /><br /></div>
Anonymoushttp://www.blogger.com/profile/04664995477596294650noreply@blogger.com1tag:blogger.com,1999:blog-3992239225529976052.post-45544789024700171832013-09-11T03:38:00.001+05:302013-09-11T04:09:00.384+05:30Oracle DBA Interview Questions and Answers - Oracle 11g Database New Features<div dir="ltr" style="text-align: left;" trbidi="on">
<b>Oracle 11g Database New Features Interview Questions and Answers</b><br />
<b></b><br />
1. Database Replay<br />
2. The SQL Performance Analyzer<br />
3. Online Patching in Oracle Database Control<br />
4. Automatic Diagnostic Repository (ADR) <br />
5. Data Recovery Advisor<br />
6. Automatic Memory Management<br />
7. Invisible Indexes<br />
8. Read-Only Tables <br />
9. Shrinking Temporary Tablespaces and Tempfiles<br />
10. Server Result Cache<br />
11. SQL Tuning Automation <br />
12. SQL Plan Management <br />
13. Database ADDM <br />
14. New SYSASM Privilege for ASM Administration <br />
15. Enhanced Block Media Recovery<br />
16. VALIDATE Command<br />
17. Configuring an Archived Redo Log Deletion Policy<br />
18. Active Database Duplication<br />
19. Virtual Private Catalogs<br />
20. ASM Restricted Mode<br />
21. Checking Diskgroup<br />
22. The FORCE option with Drop Diskgroup Command<br />
23. Active Data Guard is a new option for Oracle Database 11g Enterprise Edition<br />
<br />
<b><b>ASM Rolling Upgrades</b></b><br />
<b><b><br /></b></b>
<b>Database Replay</b><br />
Database Replay (sometimes named as Workload Replay) feature in Oracle11g allows you to reproduce the<br />
production database conditions in a testing environment.In other words, with this feature you can capture the<br />
actual workload on a production system and replay it in a test system. This way, you can analyze the condition of the production database without working on the actual production database.<br />
<br />
This feature enables you to test the impact of applying changes on a production database. These changes could be database upgrades, switching to RAC, application upgrades, operating system upgrades or storage system changes. <br />
<br />
<b>The SQL Performance Analyzer</b><br />
The SQL Performance Analyzer (SPA) aims at measuring the impact of applying any change on the database on the performance of the SQL statements execution. If it finds out performance degradation in one or more SQL statements, it provides you recommendations on how to improve their performance.<br />
<br />
This is very useful for a DBA to analyze how a change on the database (including database upgrade) may affect the execution efficiency of SQL statements. Using this tool is explained here because you may consider using it to study the effect of upgrading an Oracle database 10g release 2 to 11g. <br />
<b><br />Note:</b><br />
If you plan to use SPA on a test database, it is highly recommended to make the test database resemble the production database as closely as possible. You can use the RMAN duplicate command for this purpose. <br />
<b><br />Online Patching in Oracle Database Control</b><br />
Patching through Database Control is enhanced in Oracle 11g. With Oracle 11g online patching (or called hot patching),you can apply or roll back a database patch while the instance is running. Also it can detect conflicts between two online patches.On the other hand, online patching consumes more memory than the conventional method.<br />
<br />
In UNIX systems, you use the script $ORACLE_HOME/OPatch/opatch to invoke the online patching. <br />
<br />
<b>Automatic Diagnostic Repository (ADR)</b><br />
The Automatic Diagnostic Repository (ADR) is a file system repository to store diagnostic data source such as alter log, trace files, user and background dump files, and also new types of troubleshooting files such as Health Monitor reports, Incident packages, SQL test cases and Data repair records.<br />
<br />
In Oracle 11g, there is a new framework (named as fault diagnosability infrastructure) consisting of many tools for diagnosing and repairing the errors in the database. All those tools refer to the ADR in their operation.<br />
<br />
<b>ADR is developed to provide the following advantages:</b><br />
1.Diagnosis data, because it is stored in file system, is available even when the database is down.<br />
2.It is easier to provide Oracle support with diagnosis data when a problem occurs in the database.<br />
3.ADR has diagnosis data not only for the database instance. It has troubleshooting data for other Oracle components such as ASM and CRS.<br />
<br />
<b>Note:</b><br />
For each database instance two alert log files are generated: one as text file and one with xml<br />
format. Contents of the xml-formatted file can be examined using adrci tool.<br />
<br />
Also the xml-formatted alert log is saved in the ADR and specifically in the directory<br />
$ORACLE_BASE/diag/rdbms/$INSTANCE_NAME/$ORACLE_SID/alert<br />
<br />
<b>Data Recovery Advisor</b><br />
Data Recovery Advisor is an Oracle Database 11g tool that automatically diagnoses data failures, determines and presents appropriate repair options,and executes repairs at the user's request. Data Recovery Advisor can diagnose failures such as the following:<br />
<br />
1. Inaccessible components like datafiles and control files.<br />
2. Physical corruptions such as block checksum failures and invalid block header<br />
3. Field values<br />
4. Inconsistent datafiles (online and offline)<br />
5. I/O failures<br />
<br />
The advisor however doe not recover from failures on standby databases or RAC environment. This advisor can be used through RMAN or the Enterprise Manager. <br />
<br />
<b>Automatic Memory Management</b><br />
In Oracle 11g, a new parameter named as MEMORY_TARGET is added to automate memory allocation for both the SGA and PGA. When this parameter is set, the SGA and the PGA memory sizes are automatically determined by the instance based on the database workload.<br />
<br />
This parameter is dynamic and can be alter using the ALTER SYSTEM command as shown below:<br />
ALTER SYSTEM SET MEMORY_TARGET = 1024M ;<br />
<br />
However, if the database is not configured to use this parameter and you want to use it, you must restart the<br />
database after setting the parameter. <br />
<b><br />Invisible Indexes</b><br />
Invisible index is an index that is not considered by the optimizer when creating the execution plans. This can be used to test the effect of adding an index to a table on a query (using index hint) without actually being used by the other queries.<br />
<br />
When using invisible indexes, consider the following:<br />
- If you rebuild an invisible index, the resulting operation will make the index visible.<br />
- If you want the optimizer to consider the invisible indexes in its operation, you can set the new initialization<br />
parameter OPTIMIZER_USE_INVISIBLE_INDEXES to TRUE (the default is FALSE). You can set the parameter in the system and session levels.<br />
<b><br />Read-Only Tables</b><br />
In Oracle 11g, you can set a table to be read only,i.e. users can only query from the table but no DML<br />
statement is allowed on the table. <br />
<b><br />Shrinking Temporary Tablespaces and Tempfiles </b><br />
In Oracle 11g, you can shrink temporary tablespaces and tempfiles. <br />
<b><br />Server Result Cache</b><br />
In Oracle 11g, there is a new SGA component called result cache, which is used cache SQL query and PL/SQL function results. The database serves the results for the executed SQL queries and PL/SQL functions from the cache instead of re-executing the actual query. Of course,the target is to obtain high response time. The cached results stored become invalid when data in the dependent database objects is modified.<br />
<br />
As clear from its concept, result cache is mostly useful in for frequently executed queries with rare changes on the retrieved data. <br />
<b><br />SQL Tuning Automation</b><br />
The SQL Tuning Advisor is run by default every night during the automated maintenance window. Basically, the advisor catches the SQL statements from AWR that are candidate for tuning (they are called buckets) during four different time periods.It then automatically creates SQL profile for any poor SQL statement, if that helps. Tuned plans are automatically added to the SQL plan baselines by the automatic SQL tuning task. <br />
<br />
The advisor also may recommend actions like creating new indexes, refreshing statistics or re-writing the statement. These actions, however, are not automatically implemented by the advisor. <br />
<br />
On Oracle 11g Release 2 (11.2.0.2), a new package named as DBMS_AUTO_SQLTUNE should be used instead of the DBMS_SQLTUNE package. The new package provides more restrictive access to the Automatic SQL Tuning feature.<br />
<br />
To use the DBMS_AUTO_SQLTUNE package, you must have the DBA role, or have EXECUTE privileges granted by an administrator. The only exception is the EXECUTE_AUTO_TUNING_TASK procedure, which can only be run by SYS. <br />
<b><br />SQL Plan Management</b><br />
SQL plan management (SPM), is a new feature in Oracle 11g that prevents performance regressions resulting from sudden changes to the execution plan of a SQL statement by providing components for capturing, selecting, and evolving SQL plan information.Changes to the execution plan may be resulted from database upgrades,system and data changes, application upgrade or bug fixes.<br />
<br />
When SPM is enabled, the system maintains a plan history that contains all plans generated by the optimizer and store them in a component called plan baseline. Among the plan history in the plan baseline, plans that are verified not to cause performance regression are marked as acceptable. The plan baseline is used by the optimizer to decide on the best plan to use when compiling a SQL statement.<br />
<br />
Repository stored in data dictionary of plan baselines and statement log maintained by the optimizer is called<br />
SQL management base(SMB).<br />
<br />
SQL Plan management is implemented by undertaking the following phases:<br />
1.Capturing SQL Plan Baselines: this can be done automatically or manually.<br />
2.Selecting SQL Plan Baselines by the optimizer<br />
3.Evolving SQL Plan Baselines <br />
<b><br />Database ADDM</b><br />
Oracle Database 11g has added a new layer of analysis to ADDM called Database ADDM. The mode ADDM was working in Oracle 10g is now called instance ADDM. The main target of database ADDM is to analyze and report on RAC environment. To enable Database ADDM, you set the parameter INSTANCES in DBMS_ADVISOR. <br />
<br />
<b>New SYSASM Privilege for ASM Administration</b><br />
SYSASM is a new privilege introduced in Oracle 11g. Users who are granted this privilege can perform ASM administration tasks. The idea behind this privilege is to separate database management and the storage management responsibilities. <br />
<br />
<b>Backup and Recovery New Features</b>:-<br />
<br />
<b>Enhanced Block Media Recovery</b><br />
In Oracle Database 11g, there is a new command to perform block media recovery, named the recover ...<br />
blockcommand replacing the old blockrecover command. The new command is more efficient since because it searches the flashback logs for older uncorrupted versions of the corrupt blocks. This requires the database to work in archivelog mode and has the Database Flashback enabled.<br />
<br />
While the block media recovery is going on, any attempt by users to access data in the corrupt blocks will result in an error message, telling the user that the data block is corrupt. <br />
<b><br />VALIDATE Command </b><br />
You can use the new command VALIDATE to manually check for physical and logical corruptions in datafiles,backup sets, and even individual data blocks. The comma nd by default checks for physical corruption. You can optionally specify CHECK LOGICAL . Corrupted blocks are reported in V$DATABASE_BLOCK_CORRUPTION.<br />
<br />
<b>Configuring an Archived Redo Log Deletion Policy</b><br />
You can use RMAN to create a persistent configuration that controls when archived redo logs are eligible for<br />
deletion from disk or tape. This deletion policy applies to all archiving destinations, including the flash recovery area. When the policy is configured, it applies on the automatic deletion of the logs in the flash recovery area and the manual deletion by the BACKUP ... DELETE and DELETE ... ARCHIVELOG commands.<br />
<br />
To enable an archived redo log deletion policy, run the CONFIGURE ARCHIVELOG DELETION POLICY BACKED UP n TIMES command with the desired options.<br />
<b><br />Active Database Duplication</b><br />
In Oracle Database 11g, you can directly duplicate a data base over the network without having to back up and provide the source database files. This direct database duplication is called active database duplication. It can be done either with Database Control or through RMAN. Instance that runs the duplicated database is called auxiliary instance. <br />
<b><br />Virtual Private Catalogs</b><br />
In Oracle Database 11g, you can restrict access to the recovery catalog by granting access to only a subset of the metadata in the recovery catalog. The subset that a user has read/write access to is termed as virtual private catalog, or just virtual catalog. The central or source recovery catalog is now called the base recovery catalog. <br />
<br />
<b>ASM Restricted Mode</b><br />
<br />
In Oracle 11g, you can start the ASM instance in restricted mode. When in restricted mode, databases will not be permitted to access the ASM instance. Also, individual diskgroup can be set in restricted mode. <br />
<br />
<b>Checking Diskgroup</b><br />
<br />
Starting from Oracle Database 11g, you can validate the internal consistency of ASM diskgroup metadata using the ALTER DISKGROUP ... CHECK command. Summary of errors is logged in the ASM alert log file. <br />
<b><br />The FORCE option with Drop Diskgroup Command</b><br />
<br />
If a disk is destroyed beyond repair, you want to drop it. But because the disk is practically damaged, you cannot mount it and thus you cannot issue the DROP DISKGROUP command against it. In such a condition, Oracle 11g provides the FORCE INCLUDING CONTENTS option to drop the diskgroup even if it is not mounted. <br />
<br />
SQL>DROP DISKGROUP <DISKGROUP_NAME> FORCE INCLUDING CONTENTS; <br />
<b><br />ASM Rolling Upgrades</b><br />
ASM rolling upgrades enable you to independently upgrade or patch clustered ASM nodes without affecting<br />
database availability, thus providing greater uptime. Rolling upgrade means that all of the features of a clustered ASM environment function when one or more of the nodes in the cluster use different software versions. <br />
<br />
<b>Active Data Guard is a new option for Oracle Database 11g Enterprise Edition</b><br />
An Active Data Guard standby database is an exact copy of the primary that is open read-only while it continuously applies changes transmitted by the primary database. An active standby can offload ad-hoc queries, reporting, and fast incremental backups from the primary database, improving performance and scalability while preventing data loss or downtime due to data corruptions, database and site failures, human error, or natural disaster. Oracle Active Data Guard enables read-only access to a physical standby database.<br />
<br />
With Oracle Active Data Guard, a physical standby database can be used for real-time reporting, with minimal latency between reporting and production data. Compared with traditional replication methods, Active Data Guard is very simple to use, transparently supports all datatypes, and offers very high performance. Oracle Active Data Guard also allows backup operations to be off-loaded to the standby database, and be done very fast using intelligent incremental backups.<br />
<br />
Active Dataguard Features:<br />
1. Physical Standby with Real-time Query<br />
2. Fast Incremental Backup on Physical Standby.<br />
3. Automatic Block Repair.<br />
<br />
<br />
<a href="http://ahmedbaraka.com/computer/docs/oracle_11g_nf_1.0.pdf">http://ahmedbaraka.com/computer/docs/oracle_11g_nf_1.0.pdf</a><br />
<br />
<a href="http://pocketoracle.blogspot.com/search/label/10g%20New%20Features">http://pocketoracle.blogspot.com/search/label/10g%20New%20Features</a><br /><br /><a href="http://pocketoracle.blogspot.com/search/label/11g%20New%20Features">http://pocketoracle.blogspot.com/search/label/11g%20New%20Features</a><br />
<br />
<br /></div>
Anonymoushttp://www.blogger.com/profile/04664995477596294650noreply@blogger.com0tag:blogger.com,1999:blog-3992239225529976052.post-62619039815418269572013-09-10T16:05:00.003+05:302013-09-10T16:05:50.036+05:30How to find Tablespace Fragmentation in Oracle Database<div dir="ltr" style="text-align: left;" trbidi="on">
<b>How to find Tablespace Fragmentation in Oracle Database</b><br /><br />To determine if your tablespaces are having a problem with fragmentation, you can use the below script:<br /><br />set pages 50000 lines 32767<br />select tablespace_name,count(*) free_chunks,decode(round((max(bytes) / 1024000),2),null,0,<br />round((max(bytes) / 1024000),2)) largest_chunk, nvl(round(sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)) )),2),0) fragmentation_index<br />from sys.dba_free_space group by tablespace_name order by 2 desc, 1;<br /><br />Here,fragmentationindex column will give your tablespace an overall ranking with respect to how badly it is actually fragmented. A 100% score indicates no fragmentation at all. Lesser scores verify the presence of fragmentation.<br /><br />The free chunks count column will tell you how many segments of free space are scattered throughout the tablespace. One thing to keep in mind is that tablespaces with multiple datafiles will always show a free chunk count greater than one because each datafile will likely have at least one pocket of free space.<br /></div>
Anonymoushttp://www.blogger.com/profile/04664995477596294650noreply@blogger.com0tag:blogger.com,1999:blog-3992239225529976052.post-5707887680061194692013-09-10T15:54:00.003+05:302013-09-10T16:06:31.482+05:30How to find Table Fragmentation in Oracle Database<div dir="ltr" style="text-align: left;" trbidi="on">
<b>How to find Table Fragmentation in Oracle Database</b><br />
<br />
<b>What is Oracle Table Fragmentation?</b><br />
If a table is only subject to inserts, there will not be any fragmentation.<br />
Fragmentation comes with when we update/delete data in table.<br />
The space which gets freed up during non-insert DML operations is not immediately re-used (or sometimes, may not get reuse ever at all). This leaves behind holes in table which results in table fragmentation.<br />
<br />
To understand it more clearly, we need to be clear on how oracle manages space for tables.<br />
<br />
When rows are not stored contiguously, or if rows are split onto more than one block, performance decreases because these rows require additional block accesses.<br />
<br />
Note that table fragmentation is different from file fragmentation. When a lot of DML operations are applied on a table, the table will become fragmented because DML does not release free space from the table below the HWM.<br />
<br />
HWM is an indicator of USED BLOCKS in the database. Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted. Since Oracle knows that blocks beyond the high water mark don't have data, it only reads blocks up to the high water mark when doing a full table scan.<br />
<br />
DDL statement always resets the HWM.<br />
<b><br />What are the reasons to reorganization of table?</b><br />
<br />
a) Slower response time (from that table)<br />
b) High number of chained (actually migrated) rows. <br />
c) Table has grown many folds and the old space is not getting reused.<br />
<br />
Note: Index based queries may not get that much benefited by reorg as compared to queries which does Full table scan.<br />
<br />
<b>How to find Table Fragmentation?</b><br />
<br />
In Oracle schema there are tables which has huge difference in actual size (size from User_segments) and expected size from user_tables (Num_rows*avg_row_length (in bytes)). This all is due to fragmentation in the table or stats for table are not updated into dba_tables.<br />
<br />
<b>Steps to Check and Remove Table Fragmentation:- <br />=============================================</b><br />
<b>1. Gather table stats:<br />---------------------</b><br />
To check exact difference in table actual size (dba_segments) and stats size (dba_tables). The difference between these value will report actual fragmentation to DBA. So, We have to have updated stats on the table stored in dba_tables. Check LAST_ANALYZED value for table in dba_tables. If this value is recent you can skip this step. Other wise i would suggest to gather table stats to get updated stats.<br />
<br />
exec dbms_stats.gather_table_stats('&schema_name','&table_name');<br />
<br />
<b>2. Check Table size:<br />-------------------</b><br />
Now again check table size using and will find reduced size of the table.<br />
<br />
select table_name,bytes/(1024*1024*1024) from dba_table where table_name='&table_name';<br />
<br />
<b>3. Check for Fragmentation in table:<br />-----------------------------------</b><br />
Below query will show the total size of table with fragmentation, expected without fragmentation and how much % of size we can reclaim after removing table fragmentation. Database Administrator has to provide table_name and schema_name as input to this query.<br />
<br />
set pages 50000 lines 32767<br />
select owner,table_name,round((blocks*8),2)||'kb' "Fragmented size", round((num_rows*avg_row_len/1024),2)||'kb' "Actual size", round((blocks*8),2)-round((num_rows*avg_row_len/1024),2)||'kb',<br />
((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 "reclaimable space % " from dba_tables where table_name ='&table_Name' AND OWNER LIKE '&schema_name'<br />
/<br />
<b>Note:</b> This query fetch data from dba_tables, so the accuracy of result depends on dba_table stats.<br />
<br />
If you find reclaimable space % value more than 20% then we can expect fragmentation in the table. Suppose, DBA find 50% reclaimable space by above query, So he can proceed for removing fragmentation.<br />
<b><br />4. How to reset HWM / remove fragemenation?<br />---------------------------------------</b><br />
We have four options to reorganize fragmented tables:<br />
<br />
1. Alter table move (to another tablespace, or same tablespace) and rebuild indexes:- <br />
(Depends upon the free space available in the tablespace) <br />
2. Export and import the table:- (difficult to implement in production environment)<br />
3. Shrink command (fron Oracle 10g)<br />
(Shrink command is only applicable for tables which are tablespace with auto segment space management)<br />
<br />
Here, I am following Options 1 and 3 option by keeping table availability in mind. <br />
<br />
<br />
<b>Option: 1 Alter table move (to another tablespace, or same tablespace) and rebuild indexes:-<br />------------------------------------------------------------------------------------------</b><br />
Collect status of all the indexes on the table:-<br />
----------------------------------------------<br />
We will record Index status at one place, So that we get back them after completion of this exercise, <br />
<br />
select index_name,status from dba_indexes where table_name like '&table_name';<br />
<br />
Move table in to same or new tablespace:<br />
---------------------------------------<br />
In this step we will move fragmented table to same tablespace or from one tablespace to another tablespace to reclaim fragmented space. Find Current size of you table from dba_segments and check if same or any other tablespace has same free space available. So, that we can move this table to same or new tablespace.<br />
<br />
Steps to Move table in to same tablespace:<br />
-----------------------------------------<br />
alter table <table_name> move; ------> Move to same tablespace<br />
<br />
OR<br />
<br />
Steps to Move table in to new tablespace:<br />
----------------------------------------<br />
alter table <table_name> enable row movement;<br />
alter table <table_name> move tablespace <new_tablespace_name>;<br />
<br />
Now, get back table to old tablespaces using below command<br />
<br />
alter table table_name move tablespace old_tablespace_name;<br />
<br />
Now,Rebuild all indexes:<br />
-----------------------<br />
We need to rebuild all the indexes on the table because of move command all the index goes into unusable state.<br />
<br />
SQL> select status,index_name from dba_indexes where table_name = '&table_name';<br />
<br />
STATUS INDEX_NAME<br />
-------- ------------------------------<br />
UNUSABLE INDEX_NAME -------> Here, value in status field may be valid or unusable.<br />
<br />
SQL> alter index <INDEX_NAME> rebuild online; -------> Use this command for each index<br />
Index altered.<br />
<br />
SQL> select status,index_name from dba_indexes where table_name = '&table_name';<br />
<br />
STATUS INDEX_NAME<br />
-------- ------------------------------<br />
VALID INDEX_NAME -------> Here, value in status field must be valid.<br />
<br />
Gather table stats:<br />
------------------<br />
SQL> exec dbms_stats.gather_table_stats('&owner_name','&table_name');<br />
PL/SQL procedure successfully completed.<br />
<br />
Check Table size:<br />
-----------------<br />
Now again check table size using and will find reduced size of the table.<br />
<br />
select table_name,bytes/(1024*1024*1024) from dba_table where table_name='&table_name';<br />
<br />
Check for Fragmentation in table:<br />
--------------------------------<br />
set pages 50000 lines 32767<br />
select owner,table_name,round((blocks*8),2)||'kb' "Fragmented size", round((num_rows*avg_row_len/1024),2)||'kb' "Actual size", round((blocks*8),2)-round((num_rows*avg_row_len/1024),2)||'kb',<br />
((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 "reclaimable space % " from dba_tables where table_name ='&table_Name' AND OWNER LIKE '&schema_name'<br />
/<br />
==================================================================================================================<br />
<b>Option: 3 Shrink command (fron Oracle 10g):-<br />------------------------------------------</b><br />
<br />
<b>Shrink command: <br />--------------</b><br />
Its a new 10g feature to shrink (reorg) the tables (almost) online which can be used with automatic segment space management.<br />
<br />
This command is only applicable for tables which are tablespace with auto segment space management.<br />
<br />
Before using this command, you should have row movement enabled.<br />
<br />
SQL> alter table <table_name> enable row movement;<br />
Table altered.<br />
<br />
There are 2 ways of using this command.<br />
<br />
1. Rearrange rows and reset the HWM:<br />
-----------------------------------<br />
Part 1: Rearrange (All DML's can happen during this time)<br />
SQL> alter table <table_name> shrink space compact;<br />
Table altered.<br />
<br />
Part 2: Reset HWM (No DML can happen. but this is fairly quick, infact goes unnoticed.)<br />
SQL> alter table <table_name> shrink space;<br />
Table altered.<br />
<br />
2. Directly reset the HWM:<br />
-------------------------<br />
SQL> alter table <table_name> shrink space; (Both rearrange and restting HWM happens in one statement)<br />
Table altered.<br />
<br />
Advantages over the conventional methods are:<br />
--------------------------------------------<br />
1. Unlike "alter table move ..",indexes are not in UNUSABLE state.After shrink command,indexes are updated also.<br />
2. Its an online operation, So you dont need downtime to do this reorg.<br />
3. It doesnot require any extra space for the process to complete.<br />
<br /></div>
Anonymoushttp://www.blogger.com/profile/04664995477596294650noreply@blogger.com2