tag:blogger.com,1999:blog-9408859378473358762024-03-13T22:52:35.268+05:30One Can Succeed at Almost Anything For Which He Has Enthusiasm...All the Views expressed here are my own and do not reflect opinions or views of the anyone else.All the views are tested on my testing environment and kindly test the post before applying anything on production.You can reach to me at neeraj.vishen@gmail.com .NEERAJ VISHENhttp://www.blogger.com/profile/04032578930084182369noreply@blogger.comBlogger241125tag:blogger.com,1999:blog-940885937847335876.post-20900554705907653622013-08-28T02:50:00.000+05:302013-08-28T12:58:56.434+05:30All About Blocking Locks in Oracle<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
A very nice post has been written by Oracle Ace Director <b><span style="background-color: #fff2cc;">Arup Nanda</span> </b> about the Blocking Locks . It is so simple and brilliantly explained . Check the below link and enjoy reading about basic concepts behind locks . Before going to this link , read my previous post on <a href="http://neeraj-dba.blogspot.in/2012/05/interested-transaction-list-itl-in.html" target="_blank">ITL ( </a><span style="background-color: white; color: #444444; font-family: inherit; text-align: start;"><a href="http://neeraj-dba.blogspot.in/2012/05/interested-transaction-list-itl-in.html" target="_blank">Interested Transaction List)</a> for better understading of this post .</span></div>
<a href="http://arup.blogspot.in/2011/01/how-oracle-locking-works.html">http://arup.blogspot.in/2011/01/how-oracle-locking-works.html</a><br />
<br />
<div style="text-align: justify;">
Another Brilliant demo on Blocking lock is explained by "<b style="background-color: #fff2cc;">Natalka Roshak</b>" . She has so wonderfully explained . Enjoy reading this link too. </div>
<div style="text-align: justify;">
<a href="http://www.orafaq.com/node/854">http://www.orafaq.com/node/854</a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<span style="text-align: left;">Here, i have added few more query on locks along with it's output . </span><span style="text-align: left;">For demo purpose , i have created a table "<span style="background-color: #fff2cc;">blck_tab</span>" and locked this table manually and accessing this locked table from other sessions .Let's have a look .</span></div>
<br />
SQL> <span style="background-color: #fff2cc;">create table blck_tab (id number , name varchar(22));</span><br />
Table created.<br />
<br />
SQL><span style="background-color: #fff2cc;"> insert into blck_tab values(1,'abc');</span><br />
1 row created.<br />
<br />
SQL> insert into blck_tab values(2,'xyz');<br />
1 row created.<br />
<br />
SQL><span style="background-color: #fff2cc;"> insert into blck_tab values(3,'pqr');</span><br />
1 row created.<br />
<br />
SQL> <span style="background-color: #fff2cc;">commit;</span><br />
Commit complete.<br />
<br />
SQL> <span style="background-color: #fff2cc;">select * from blck_tab for update ; </span> ----- Table Locked<br />
ID NAME<br />
---------- ----------------------<br />
1 abc<br />
2 xyz<br />
3 pqr<br />
<br />
<b style="background-color: #ffe599;">Session 1 : </b><br />
<b style="background-color: #fff2cc;"><br /></b>
SQL><span style="background-color: #fff2cc;">conn test1/test1</span><br />
SQL><span style="background-color: #fff2cc;"> update test.blck_tab set name='qwert' where id=3;</span><br />
<span style="background-color: #fce5cd;">--->> Waiter <---- </span><br />
<br />
<b style="background-color: #ffe599;">Session 2 : </b><br />
<br />
SQL> <span style="background-color: #fff2cc;">conn hr/hr</span><br />
SQL> <span style="background-color: #fff2cc;">delete test.blck_tab where id=1;</span><br />
<span style="background-color: #fce5cd;">--->> Waiter <---- </span><br />
<br />
<br />
<b style="background-color: #ffe599;">Query to check locks :</b><br />
(For Non-RAC)<br />
SQL> <span style="background-color: #fff2cc;">SELECT SID, DECODE(BLOCK, 0, 'NO', 'YES' ) BLOCKER,</span><br />
<span style="background-color: #fff2cc;"> 2 DECODE(REQUEST, 0, 'NO','YES' ) WAITER</span><br />
<span style="background-color: #fff2cc;"> 3 FROM V$LOCK</span><br />
<span style="background-color: #fff2cc;"> 4 WHERE REQUEST > 0 OR BLOCK > 0</span><br />
<span style="background-color: #fff2cc;"> 5 ORDER BY block DESC;</span><br />
SID BLOCKER WAITER<br />
------ ------------ ----------<br />
37 YES NO<br />
34 NO YES<br />
31 NO YES<br />
<b>or </b><br />
<br />
SQL> <span style="background-color: #fff2cc;">select l1.sid, ' IS BLOCKING ', l2.sid</span><br />
<span style="background-color: #fff2cc;"> 2 from gv$lock l1, gv$lock l2</span><br />
<span style="background-color: #fff2cc;"> 3 where l1.block >0 and l2.request > 0</span><br />
<span style="background-color: #fff2cc;"> 4 and l1.id1=l2.id1</span><br />
<span style="background-color: #fff2cc;"> 5 and l1.id2=l2.id2;</span><br />
SID 'ISBLOCKING' SID<br />
---------- ------------------ --------<br />
37 IS BLOCKING 31<br />
37 IS BLOCKING 34<br />
<br />
<b><span style="background-color: #f1c232;">For RAC</span> </b><br />
SQL><span style="background-color: #fff2cc;">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>0 and l2.request > 0and l1.id1 = l2.id1 and l2.id2 = l2.id2 and l1.inst_id = s1.inst_id;</span><br />
<div>
BLOCKING_STATUS</div>
------------------------------------------------------------------------------------------------------------<br />
TEST@WORKGROUP\NEERAJ-7D69D37B ( INST=1 SID=37 ) is blocking TEST1@WORKGROUP\NEERAJ-7D69D37B ( INST=1 SID=34 )<br />
<br />
TEST@WORKGROUP\NEERAJ-7D69D37B ( INST=1 SID=37 ) is blocking HR@WORKGROUP\NEERAJ-7D69D37B ( INST=1 SID=31 )<br />
<br />
<br />
Expanded Lock Query<br />
===============<br />
SQL> <span style="background-color: #fff2cc;">SELECT vs.username, vs.osuser, vh.sid locking_sid, vs.status status,</span><br />
<span style="background-color: #fff2cc;"> vs.module module, vs.program program_holding, jrh.job_name, vsw.username,</span><br />
<span style="background-color: #fff2cc;"> vsw.osuser, vw.sid waiter_sid, vsw.program program_waiting, jrw.job_name,</span><br />
<span style="background-color: #fff2cc;"> 'alter system kill session ' || ''''|| vh.sid || ',' || vs.serial# || ''';' "Kill_Command"</span><br />
<span style="background-color: #fff2cc;">FROM v$lock vh, v$lock vw, v$session vs, v$session vsw,</span><br />
<span style="background-color: #fff2cc;"> dba_scheduler_running_jobs jrh,</span><br />
<span style="background-color: #fff2cc;"> dba_scheduler_running_jobs jrw</span><br />
<span style="background-color: #fff2cc;">WHERE (vh.id1, vh.id2) IN (SELECT id1, id2 </span><br />
<span style="background-color: #fff2cc;"> FROM v$lock</span><br />
<span style="background-color: #fff2cc;"> WHERE request = 0</span><br />
<span style="background-color: #fff2cc;"> INTERSECT</span><br />
<span style="background-color: #fff2cc;"> SELECT id1, id2</span><br />
<span style="background-color: #fff2cc;"> FROM v$lock</span><br />
<span style="background-color: #fff2cc;"> WHERE lmode = 0)</span><br />
<span style="background-color: #fff2cc;"> AND vh.id1 = vw.id1</span><br />
<span style="background-color: #fff2cc;"> AND vh.id2 = vw.id2</span><br />
<span style="background-color: #fff2cc;"> AND vh.request = 0</span><br />
<span style="background-color: #fff2cc;"> AND vw.lmode = 0</span><br />
<span style="background-color: #fff2cc;"> AND vh.sid = vs.sid</span><br />
<span style="background-color: #fff2cc;"> AND vw.sid = vsw.sid</span><br />
<span style="background-color: #fff2cc;"> AND vh.sid = jrh.session_id(+)</span><br />
<span style="background-color: #fff2cc;"> AND vw.sid = jrw.session_id(+);</span><br />
<br />
<a href="http://4.bp.blogspot.com/--BFFcSMzGLQ/Uh0Szw_PBQI/AAAAAAAAAeI/ODTfhG7hl1A/s1600/2.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="24" src="http://4.bp.blogspot.com/--BFFcSMzGLQ/Uh0Szw_PBQI/AAAAAAAAAeI/ODTfhG7hl1A/s640/2.PNG" width="640" /></a><br />
<br />
<br />
<b style="background-color: #ffe599;">Blocking locks with Sid and SQl </b><br />
<b style="background-color: #ffe599;"><br /></b>
SQL> <span style="background-color: #fff2cc;">set lines 200</span><br />
<span style="background-color: #fff2cc;">column pu format a8 heading 'O/S|login|ID' justify left</span><br />
<span style="background-color: #fff2cc;">column su format a15 heading 'Oracle/User ID' justify left</span><br />
<span style="background-color: #fff2cc;">column prog format a15 heading 'Program' justify left</span><br />
<span style="background-color: #fff2cc;">column machine format a15 heading 'machine' justify left</span><br />
<span style="background-color: #fff2cc;">column stat format a8 heading 'Session|Status' justify left</span><br />
<span style="background-color: #fff2cc;">column sser format 999999 heading 'Oracle|Serial|No' justify right</span><br />
<span style="background-color: #fff2cc;">column txt format a28 word heading 'SQL TEXT' </span><br />
<span style="background-color: #fff2cc;">column RUNT format a15 word heading 'Run Time' </span><br />
<span style="background-color: #fff2cc;">set pagesize 1000</span><br />
<span style="background-color: #fff2cc;">select</span><br />
<span style="background-color: #fff2cc;"> s.username su,</span><br />
<span style="background-color: #fff2cc;"> s.program prog,</span><br />
<span style="background-color: #fff2cc;"> s.sid sid,</span><br />
<span style="background-color: #fff2cc;"> lpad(p.spid,7) pid,</span><br />
<span style="background-color: #fff2cc;"> substr(sa.sql_text,1,2000) txt,</span><br />
<span style="background-color: #fff2cc;"> ltrim(to_char(floor(s.last_call_et/3600),'00009')) ||':'</span><br />
<span style="background-color: #fff2cc;"> || ltrim(to_char(floor(mod(s.last_call_et,3600)/60),'09')) ||':'</span><br />
<span style="background-color: #fff2cc;"> || ltrim(to_char(mod(s.last_call_et,60),'09')) RUNT</span><br />
<span style="background-color: #fff2cc;">from v$process p,</span><br />
<span style="background-color: #fff2cc;"> v$session s,</span><br />
<span style="background-color: #fff2cc;"> v$sqlarea sa</span><br />
<span style="background-color: #fff2cc;">where p.addr = s.paddr</span><br />
<span style="background-color: #fff2cc;"> and s.username is not null</span><br />
<span style="background-color: #fff2cc;"> and s.sql_address=sa.address(+)</span><br />
<span style="background-color: #fff2cc;"> and s.sql_hash_value=sa.hash_value(+)</span><br />
<span style="background-color: #fff2cc;"> and s.sid in (SELECT SID FROM V$LOCK WHERE REQUEST > 0 OR BLOCK > 0)</span><br />
<span style="background-color: #fff2cc;">order by 1,2</span><br />
<span style="background-color: #fff2cc;">/</span><br />
<div>
<div>
Oracle/User ID Program SID PID SQL TEXT Run Time</div>
<div>
--------------- --------------- ------- ------- ---------------------------- ---------------</div>
<div>
HR sqlplus.exe 31 2812 delete test.blck_tab where 00001:32:11</div>
<div>
id=1</div>
<div>
<br /></div>
<div>
TEST sqlplus.exe 37 1928 00000:06:35</div>
<div>
TEST1 sqlplus.exe 34 1880 update test.blck_tab set 00002:11:34</div>
<div>
name='qwert' where id=3</div>
</div>
<br />
<br />
<b style="background-color: #ffe599;">Blocker/Waiter object details</b><br />
<br />
SQL> col object_name for a28<br />
SQL> col owner for a15<br />
SQL> <span style="background-color: #fff2cc;">select do.owner,do.object_name , row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#, dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#,ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)</span><br />
<span style="background-color: #fff2cc;"> 2 from</span><br />
<span style="background-color: #fff2cc;"> 3 v$session s,</span><br />
<span style="background-color: #fff2cc;"> 4 dba_objects do</span><br />
<span style="background-color: #fff2cc;"> 5 where sid in (select l2.sid blocking from v$lock l1, v$lock l2 where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2) and</span><br />
<span style="background-color: #fff2cc;"> 6 s.ROW_WAIT_OBJ# = do.OBJECT_ID;</span><br />
<br />
OWNER OBJECT_NAME ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# DBMS_ROWID.ROWID_C<br />
--------------- -------------- ------------- -------------- --------------- ------------- ------------------<br />
TEST BLCK_TAB 74697 4 524 2 AAASPJAAEAAAAIMAAC<br />
<br />
TEST BLCK_TAB 74697 4 524 0 AAASPJAAEAAAAIMAAA<br />
<br />
<br />
Note for RAC use gv$ instead of v$ .<br />
<br />
<br />
<b>Enjoy </b> <b style="background-color: yellow;"> :-) </b><br />
<br />
<br />
<br /></div>
NEERAJ VISHENhttp://www.blogger.com/profile/04032578930084182369noreply@blogger.com34tag:blogger.com,1999:blog-940885937847335876.post-56910159745496750842013-07-21T22:17:00.001+05:302013-07-21T22:17:54.562+05:30What are Patches and how to apply patches ? <div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
<span style="font-family: inherit;"><b style="background-color: #ffe599;">Patching</b> is one of the most common task performed by DBA's in day-to-day life . Here , we will discuss about the various types of patches which are provided by Oracle . Oracle issues product fixes for its software called patches. When we apply the patch to our Oracle software installation, it updates the executable files, libraries, and object files in the software home directory . The patch application can also update configuration files and Oracle-supplied SQL schemas . Patches are applied by using OPatch, a utility supplied by Oracle , OUI or Enterprise Manager Grid Control . </span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Oracle Patches are of various kinds . Here , we are broadly categorizing it into two groups .</span></div>
<span style="font-family: inherit;"><br /></span>
<b><span style="font-family: inherit;">1.) Patchset : </span></b><br />
<b><span style="font-family: inherit;">2.) Patchset Updates : </span></b><br />
<br />
<div style="text-align: justify;">
<span style="font-family: inherit;"><b>1.) <span style="background-color: #ffe599;">Patchset </span>: </b> A group of patches form a patch set. Patchsets are applied by invoking OUI (Oracle Universal Installer) . Patchsets are generally applied for Upgradation purpose . This results in a version change for our Oracle software, for example, from Oracle Database 11.2.0.1.0 to Oracle Database 11.2.0.3.0. We will cover this issue later .</span></div>
<span style="font-family: inherit;"><br /></span>
<div style="text-align: justify;">
<span style="font-family: inherit;"><b>2.) <span style="background-color: #ffe599;">Patchset Updates </span>:</b> Patch Set Updates are proactive cumulative patches containing recommended bug fixes that are released on a regular and predictable schedule . <span style="text-align: left;">Oracle has catergaries as :</span></span></div>
<span style="font-family: inherit;"><br /></span>
<div style="text-align: justify;">
<span style="font-family: inherit;"><b>i.) <span style="background-color: #fff2cc;">Critical Patch Update</span> (CPU) </b> now refers to the overall release of security fixes each quarter rather than the cumulative database security patch for the quarter. Think of the CPU as the overarching quarterly release and not as a single patch .</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br /></span></div>
<div style="text-align: left;">
<span style="font-family: inherit;"><b>ii.) <span style="background-color: #fff2cc;">Patch Set Updates</span></b> <b>(PSU)</b> are the same cumulative patches that include both the security fixes and priority fixes. The key with PSUs is they are minor version upgrades (e.g., 11.2.0.1.1 to 11.2.0.1.2). Once a PSU is applied, only PSUs can be applied in future quarters until the database is upgraded to a new base version. </span></div>
<span style="font-family: inherit;"><br /></span>
<div style="text-align: justify;">
<span style="font-family: inherit;"><b>iii.) <span style="background-color: #fff2cc;">Security Patch Update</span> (SPU) </b> terminology is introduced in the October 2012 Critical Patch Update as the term for the quarterly security patch. SPU patches are the same as previous CPU patches, just a new name . For the database, SPUs can not be applied once PSUs have been applied until the database is upgraded to a new base version.</span></div>
<span style="font-family: inherit;"><br /></span>
<div style="text-align: justify;">
<span style="font-family: inherit;"><b>iv.) <span style="background-color: #fff2cc;">Bundle Patches</span></b> are the quarterly patches for Windows and Exadata which include both the quarterly security patches as well as recommended fixes. </span></div>
<span style="font-family: inherit;"><br /></span>
<div style="text-align: justify;">
<span style="font-family: inherit;">PSUs(PatchSet Updates) or CPUs(Critical Patch Updates) ,SPU are applied via opatch utility.</span></div>
<span style="font-family: inherit;"><br /></span>
<span style="background-color: #ffe599; font-family: inherit;"><b>How to get Oracle Patches :</b></span><br />
<div style="text-align: justify;">
<span style="font-family: inherit;">We obtain patches and patch sets from <a href="https://support.oracle.com/" target="_blank"><span style="color: blue;">My Oracle Support (MOS)</span></a> . The ability to download a specific patch is based on the contracts associated to the support identifiers in </span><span style="font-family: inherit;">our My Oracle Support account. All MOS users are able to search for and view all patches, but we will be prevented from downloading certain types of </span><span style="font-family: inherit;">patches based on our contracts.</span></div>
<span style="font-family: inherit;"><br /></span>
<div style="text-align: justify;">
<span style="font-family: inherit;">While applying Patchset or patchset upgrades , basically there are two entities in the Oracle Database environment </span></div>
<span style="font-family: inherit;"><b>i. ) Oracle Database Software</b></span><br />
<span style="font-family: inherit;"><b>ii.) Oracle Database</b></span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">Most of the database patching activities involve, in the following sequence</span><br />
<br />
<ul style="text-align: left;">
<li style="text-align: justify;"><span style="font-family: inherit;"> Update "Oracle Database Software" using './runInstaller' or 'opatch apply' known as "Installation" Tasks.</span></li>
<li style="text-align: justify;"><span style="font-family: inherit;">Update "Oracle Database" (catupgrd.sql or catbundle.sql ...etc) to make it compatible for newly patched "Oracle database Software" known as "Post Installation" </span><span style="font-family: inherit; text-align: left;">tasks. </span></li>
</ul>
<br />
<div style="text-align: justify;">
<span style="font-family: inherit;"><b style="background-color: #fff2cc;">Patchset OR CPU/PSU </b> (or one-off) patch contains Post Installation tasks to be executed on all Oracle Database instances after completing the Installation tasks. If we </span><span style="font-family: inherit;">are planning to apply a patchset along with required one-off-patches (either CPU or PSU or any other one-off patch), then we can complete the Installation tasks of the <span style="background-color: #fff2cc;"> </span></span><span style="font-family: inherit; text-align: left;"><span style="background-color: #fff2cc;">Patchset+CPU/PSU/one-off</span> patches at once and then execute Post Installation tasks of the <span style="background-color: #fff2cc;">Patchset+CPU/PSU/one-off</span> patches in the same sequence as they were installed . </span></div>
<span style="font-family: inherit;"><br /></span>
<div style="text-align: justify;">
<span style="font-family: inherit;">This approach minimizes the requirement of database shutdown across each patching activity and simplifies the patching mechanism as two tasks: </span></div>
<div style="text-align: justify;">
</div>
<ul>
<li><span style="font-family: inherit; text-align: left;">Software update and then</span></li>
<li><span style="font-family: inherit; text-align: left;">Database update.</span></li>
</ul>
<br />
<div style="text-align: left;">
<span style="font-family: inherit;">Here , we will cover the <b><span style="background-color: #ffe599;">Opatch Utility</span> </b>in details along with example. </span></div>
<span style="font-family: inherit;"><br /></span>
<div style="text-align: justify;">
<span style="font-family: inherit;"><b style="background-color: #ffe599;">OPatch</b> is the recommended (Oracle-supplied) tool that customers are supposed to use in order to apply or rollback patches. OPatch is PLATFORM specific . </span><span style="font-family: inherit;">Release is based on Oracle Universal Installer version . </span><span style="text-align: left;"> OPatch resides in <span style="background-color: #fff2cc;">$ORACLE_HOME/OPatch </span>. </span><span style="font-family: inherit; text-align: left;">OPatch supports the following :</span></div>
<br />
<ul style="text-align: left;">
<li><span style="font-family: inherit;">Applying an interim patch.</span></li>
<li><span style="font-family: inherit;">Rolling back the application of an interim patch.</span></li>
<li style="text-align: justify;"><span style="font-family: inherit;">Detecting conflict when applying an interim patch after previous interim patches have been applied. It also suggests the best options to resolve a conflict .</span></li>
<li><span style="font-family: inherit;">Reporting on installed products and interim patch.</span></li>
</ul>
<br />
<span style="font-family: inherit;">The patch metadata exist in the inventory.xml and action.xml files exists under <span style="background-color: #fff2cc;"><stage_area>/<patch_id>/etc/config/</span> </span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">Inventory <span style="background-color: #fce5cd;">.xml file</span> have the following information :</span><br />
<br />
<ul style="text-align: left;">
<li><span style="font-family: inherit;">Bug number </span></li>
<li><span style="font-family: inherit;">Unique Patch ID</span></li>
<li><span style="font-family: inherit;">Date of patch year </span></li>
<li><span style="font-family: inherit;">Required and Optional components </span></li>
<li><span style="font-family: inherit;">OS platforms ID </span></li>
<li><span style="font-family: inherit;">Instance shutdown is required or not </span></li>
<li><span style="font-family: inherit;">Patch can be applied online or not </span></li>
</ul>
<br />
<span style="font-family: inherit;">Actions <span style="background-color: #fce5cd;">.xml file</span> have the following information .</span><br />
<br />
<ul style="text-align: left;">
<li><span style="font-family: inherit;">File name and it location to which it need to be copied </span></li>
<li><span style="font-family: inherit;">Components need to be re-linked </span></li>
<li><span style="font-family: inherit;">Information about the optional and required components</span></li>
</ul>
<br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><b><span style="background-color: #ffe599;">Here are steps for applying patches on linux Platform </span>: </b></span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><b>1.) Download the required Patches from My Oracle Support (MOS) : </b></span><br />
<br />
<ul style="text-align: left;">
<li><span style="font-family: inherit;">Login to metalink.</span></li>
<li><span style="font-family: inherit;">Click "Patches & Updates" link on top menu.</span></li>
<li><span style="font-family: inherit;">On the patch search section enter patch number and select the platform of your database.</span></li>
<li><span style="font-family: inherit;">Click search.</span></li>
<li><span style="font-family: inherit;">On the search results page, download the zip file.</span></li>
</ul>
<br />
<span style="font-family: inherit;"><b>2.) Opatch version :</b></span><br />
<span style="font-family: inherit; text-align: justify;">Oracle recommends that we use the latest released OPatch , which is available for download from My Oracle Support . </span><span style="text-align: justify;"> OPatch is compatible only with the version of Oracle Universal Installer that is installed in the Oracle home. We can get all Opatch command by using </span><span style="background-color: #fff2cc; text-align: justify;">Opatch help</span><span style="text-align: justify;"> command .</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><b>3.) Stop all the Oracle services :</b></span><br />
<span style="font-family: inherit;">Before applying Optach , make sure all the Oracle services are down . If they are not down then stop/down the oracle related Services . Let's crosscheck it </span><br />
<br />
<span style="font-family: inherit;">$<span style="background-color: #fff2cc;"> ps -ef |grep pmon</span></span><br />
<span style="font-family: inherit;">oracle 15871 15484 0 11:20 pts/2 00:00:00 grep pmon</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">$<span style="background-color: #fff2cc;"> ps -ef |grep tns</span></span><br />
<span style="font-family: inherit;">oracle 15874 15484 0 11:20 pts/2 00:00:00 grep tns</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><b>4.) Take Cold Backup : </b> </span><br />
<div style="text-align: justify;">
<span style="font-family: inherit;">It is highly recommended to backup the software directory which we are patching before performing any patch operation . This applies to Oracle Database or Oracle Grid Infrastructure software installation directories. T</span><span style="font-family: inherit; text-align: left;">ake the backup of following </span></div>
<br />
<ul style="text-align: left;">
<li><span style="font-family: inherit;">Take the Oracle software directory backup </span></li>
</ul>
<br />
<span style="font-family: inherit;">$ <span style="background-color: #fff2cc;">tar -zcvf /u01/app/oracle/product/11.2.0/ohsw-bkp-b4-ptch.tar.gz /u01/app/oracle/product/11.2.0</span> </span><br />
<br />
<ul style="text-align: left;">
<li><span style="font-family: inherit;">Take backup of oracle database . </span></li>
</ul>
<br />
<span style="font-family: inherit;">$<span style="background-color: #fff2cc;"> tar -zcvf /u01/app/oracle/oradata/dbfl-b4-ptch.tar.gz /u01/app/oracle/oradata </span> </span><br />
<span style="font-family: inherit;">Here all the database files are in oradata directory .</span><br />
<br />
<ul style="text-align: left;">
<li><span style="font-family: inherit;">Take backup of OraInventary</span></li>
</ul>
<br />
<span style="font-family: inherit;">$<span style="background-color: #fff2cc;"> tar -zcvf /u01/app/oraInventary/orinv-b4-ptch.tar.gz /u01/app/oraInventary </span></span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><b>5.) Apply OPatches</b> </span><br />
<span style="font-family: inherit;">Set our current directory to the directory where the patch is located and then run the OPatch utility by entering the following commands:</span><br />
<span style="font-family: inherit;"><br /></span>
$<span style="background-color: #fff2cc;"> export PATH=$ORACLE_HOME/OPatch:$PATH:</span><br />
$ <span style="background-color: #fff2cc;">opatch apply .</span><br />
<div>
<br /></div>
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><b>6.) Post Installation : </b></span><br />
<div style="text-align: justify;">
<span style="font-family: inherit;">Once , the Opatch installation completed successfully . Perform the post Installation steps . </span><span style="font-family: inherit;">Startup the oracle database with new patched software and run catbundle.sql scripts which is found in <span style="background-color: #fff2cc;">$ORACLE_HOME/rdbms/admin</span> directory .</span></div>
<span style="font-family: inherit;">The catbundle.sql execution is reflected in the dba_registry_history view by a row associated with bundle series PSU.</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><b>7.) Finally check the status of patch status : </b></span><br />
We can check the final status of applied patched new Oracle Home by using the below command .<br />
<span style="font-family: inherit;">SQL > <span style="background-color: #fff2cc;">select * from dba_registry_history order by action_time desc ;</span></span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><br /></span>
<span style="background-color: #ffe599; font-family: inherit;"><b>Notes :</b></span><br />
<span style="font-family: inherit;"><b>i.) </b>If we are using a Data Guard Physical Standby database, we must install this patch on both the primary database and the physical standby database .</span><br />
<b><br /></b>
<b>ii.) </b> While applying patching take care of mount point status .There should be sufficient Space .<br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">There are few good demo on Optach . Check the below link </span><br />
<a href="http://www.oracle-ckpt.com/applying-cpujan2012-patch-on-11-2-0-2-0linux64-bit/" target="_blank">Click Here</a><br />
<a href="http://www.oracleportal.org/knowledge-base/oracle-database/database-administration/installation-and-patching/patching/specific-patches/applying-patch-set-update-112023-(patch-12419353).aspx" target="_blank">Click Here</a><br />
<br />
<span style="font-family: inherit;">For More About Opatch : <a href="http://docs.oracle.com/cd/B16240_01/doc/em.102/e15294/options.htm" target="_blank">Click Here</a></span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><b>Enjoy <span style="background-color: yellow;">:-) :-)</span></b> </span><br />
<span style="font-family: inherit;"><br /></span>
<br /></div>
NEERAJ VISHENhttp://www.blogger.com/profile/04032578930084182369noreply@blogger.com9tag:blogger.com,1999:blog-940885937847335876.post-2343630935853622182013-06-30T17:51:00.001+05:302013-10-05T12:03:06.204+05:30Oracle Database 12C Release 1 Installation on Linux<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<b>Oracle 12c</b> (Oracle 12.1.0.1) has been released and is available for download . Oracle 12C Installation steps are almost same as that of <a href="http://neeraj-dba.blogspot.in/2011/04/oracle-10g-installation-on-redhat-51.html" target="_blank"><span style="color: blue;">Oracle 10g and 11g Installations</span></a> . Oracle 12c is available for 64 bit . Here , we will see step-by-step Installation of Oracle 12C database . </div>
<br />
<b style="background-color: #ffe599;">Step 1 : Oracle S/W Installation</b><br />
We can download Oracle 12c s/w from e-delivery or from OTN . Below are Link<br />
<br />
<a href="http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html">http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html</a><br />
<a href="https://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=16496132">https://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=16496132</a><br />
<br />
<br />
<b style="background-color: #ffe599;">Step 2 : Hardware Requirements </b><br />
Oracle Recommand the following requirement for installation . <br />
<b>RAM</b> = 2GB of RAM or more<br />
<b>Swap</b> = 1.5 times of RAM if RAM less than 2 GB , equal to size of RAM is RAm size is more than 2GB<br />
<b>Disk Space </b> = More than 6.4 GB for Enetrprise Edition .<br />
<b>Tmp directory </b> = Minimum 1GB of free space<br />
<br />
<br />
<b style="background-color: #ffe599;">Step 3 : Hardware Verifications </b><br />
[root@server1 ~]# <span style="background-color: #fff2cc;">grep MemTotal /proc/meminfo</span><br />
MemTotal: 3017140 kB<br />
<br />
[root@server1 ~]# <span style="background-color: #fff2cc;">grep SwapTotal /proc/meminfo</span><br />
SwapTotal: 4105420 kB<br />
<br />
[root@server1 ~]# <span style="background-color: #fff2cc;">df -h /tmp</span><br />
Filesystem Size Used Avail Use% Mounted on<br />
/dev/sda1 46G 19G 25G 44% /<br />
<br />
[root@server1 ~]# <span style="background-color: #fff2cc;">df -h </span><br />
Filesystem Size Used Avail Use% Mounted on<br />
/dev/sda1 46G 19G 25G 44% /<br />
tmpfs 1.5G 0 1.5G 0% /dev/shm<br />
/dev/hdc 3.4G 3.4G 0 100% /media/RHEL_5.3 x86_64 DVD<br />
<br />
[root@server1 ~]# <span style="background-color: #fff2cc;"> free</span><br />
total used free shared buffers cached<br />
Mem: 3017140 715376 2301764 0 109776 384096<br />
-/+ buffers/cache: 221504 2795636<br />
Swap: 4105420 0 4105420<br />
<br />
[root@server1 ~]# <span style="background-color: #fff2cc;">uname -m </span><br />
x86_64<br />
<br />
[root@server1 ~]# <span style="background-color: #fff2cc;">uname -a </span><br />
<div style="text-align: justify;">
Linux server1.example.com 2.6.18-128.el5 #1 SMP Wed Dec 17 11:41:38 EST 2008 x86 </div>
<div style="text-align: justify;">
_64 x86_64 x86_64 GNU/Linux</div>
<br />
<br />
<b style="background-color: #ffe599;">Step 4 : Packages Verifications </b><br />
<div style="text-align: justify;">
The following packages are required for the Oracle Installation , so make sure all the packages are installed .</div>
<div style="text-align: justify;">
<br /></div>
<span style="background-color: #fff2cc;">make-3.81</span><br />
<span style="background-color: #fff2cc;">binutils-2.17.50</span><br />
<span style="background-color: #fff2cc;">gcc-4.1.2 (x86_64)</span><br />
<span style="background-color: #fff2cc;">gcc-c++-4.1.2 (x86_64)</span><br />
<span style="background-color: #fff2cc;">compat-libcap1 (x86_64)</span><br />
<span style="background-color: #fff2cc;">compat-libstdc++-33 (x86_64)</span><br />
<span style="background-color: #fff2cc;">glibc-2.5-58 (x86_64)</span><br />
<span style="background-color: #fff2cc;">glibc-devel-2.5 (x86_64)</span><br />
<span style="background-color: #fff2cc;">libgcc-4.1.2 (x86_64) </span><br />
<span style="background-color: #fff2cc;">libstdc++-4.1.2 (x86_64) </span><br />
<span style="background-color: #fff2cc;">libstdc++-devel-4 (x86_64) </span><br />
<span style="background-color: #fff2cc;">libaio-0.3.106 (x86_64)</span><br />
<span style="background-color: #fff2cc;">libaio-devel-0.3 (x86_64) </span><br />
<span style="background-color: #fff2cc;">ksh </span><br />
<span style="background-color: #fff2cc;">sysstat </span><br />
<span style="background-color: #fff2cc;">unixODBC </span><br />
<span style="background-color: #fff2cc;">unixODBC-devel</span><br />
<br />
<div style="text-align: justify;">
Execute the below command as root to make sure that we have all this rpms installed. If not installed, then download them from appropriate linux site or we will find the <span style="text-align: left;">package from the Red Hat Enterprise Linux 5 DVD</span> . For example , </div>
#<span style="background-color: #fff2cc;"> rpm -qa | grep glib* </span><br />
<span style="background-color: #fff2cc;"><br /></span>
<br />
<div style="text-align: justify;">
The above command will display all the installed packages, name starting with glib, similarly we can check for all others packages . If any of the above packages are not installed, run the following command:</div>
<div style="text-align: justify;">
# <span style="background-color: #fff2cc;">rpm -ivh </path/to/><version>.i386.rpm</span></div>
<br />
<br />
<b style="background-color: #ffe599;">Steps 5 : Kernel Parameters</b><br />
Add the below kernel Parameters in the /etc/sysctl.conf file<br />
<br />
<span style="background-color: #fff2cc;">fs.file-max = 6815744</span><br />
<span style="background-color: #fff2cc;">kernel.sem = 250 32000 100 128</span><br />
<span style="background-color: #fff2cc;">kernel.shmmni = 4096</span><br />
<span style="background-color: #fff2cc;">kernel.shmall = 1073741824</span><br />
<span style="background-color: #fff2cc;">kernel.shmmax = </span><span style="background-color: #fff2cc; line-height: 16px;"><span style="font-family: inherit;">2147483648</span></span><br />
<span style="background-color: #fff2cc;">net.core.rmem_default = 262144</span><br />
<span style="background-color: #fff2cc;">net.core.rmem_max = 4194304</span><br />
<span style="background-color: #fff2cc;">net.core.wmem_default = 262144</span><br />
<span style="background-color: #fff2cc;">net.core.wmem_max = 1048576</span><br />
<span style="background-color: #fff2cc;">fs.aio-max-nr = 1048576</span><br />
<span style="background-color: #fff2cc;">net.ipv4.ip_local_port_range = 9000 65500</span><br />
<br />
<div style="text-align: justify;">
After adding these lines to /etc/sysctl.conf , run the below command as root to make them enabled.</div>
<span style="background-color: #fff2cc;"> # sysctl -p </span><br />
<br />
<br />
<b style="background-color: #ffe599;">Step 6 : Edit the /etc/security/limits.conf file</b><br />
<div style="text-align: justify;">
To improve the performance of the software on Linux systems, we must increase the following shell limits for the oracle user . Add the following lines to the /etc/security/limits.conf file :</div>
<div style="text-align: justify;">
<br /></div>
<span style="background-color: #fff2cc;">oracle soft nproc 2047</span><br />
<span style="background-color: #fff2cc;">oracle hard nproc 16384</span><br />
<span style="background-color: #fff2cc;">oracle soft nofile 1024</span><br />
<span style="background-color: #fff2cc;">oracle hard nofile 65536</span><br />
<div style="text-align: justify;">
Where "<b>nproc</b>" is the maximum number of processes available to the user and "<b>nofiles</b>" is the number of open file descriptors.</div>
<br />
<br />
<b style="background-color: #ffe599;">Step 7 : Create User and Groups </b><br />
<div style="text-align: justify;">
Starting with <b>Oracle Database 12c </b>, we can create new administrative privileges that are more task-specific and less privileged than the OSDBA/SYSDBA system privileges to support specific administrative privileges tasks required for everyday database operation. Users granted these system privileges are also authenticated through operating system group membership .</div>
<br />
<div style="text-align: justify;">
We do not have to create these specific group names, but during installation we are prompted to provide operating system groups whose members are granted access to these system privileges. we can assign the same group to provide authentication for these privileges, but Oracle recommends that we should provide a unique group to designate each privileges.</div>
<br />
<div style="text-align: justify;">
<b>i .) <span style="background-color: #fff2cc;">The OSDBA group (typically, dba) : </span></b> This group identifies operating system user accounts that have database administrative privileges (the SYSDBA privilege).</div>
#<span style="background-color: #fff2cc;">groupadd -g 501 dba </span><br />
<br />
<div style="text-align: justify;">
<b>ii .) </b><b style="background-color: #fff2cc;">The Oracle Inventory Group (oinstall) : </b> This group owns the Oracle inventory that is a catalog of all Oracle software installed on the system. A single Oracle Inventory group is required for all installations of Oracle software on the system.</div>
# <span style="background-color: #fff2cc;">groupadd -g 502 oinstall </span><br />
<div style="text-align: left;">
<span style="text-align: justify;">iii </span><b style="text-align: justify;">.) <span style="background-color: #fff2cc;">The OSOPER group for Oracle Database (typically, oper) :</span> </b><span style="text-align: justify;"> This is an optional group. We create this group if we want a separate group of operating system users to have a limited set of database administrative privileges for starting up and shutting down the database (the SYSOPER privilege). </span></div>
# <span style="background-color: #fff2cc;">groupadd -g 503 oper </span><br />
<br />
<b style="text-align: justify;">iv .) <span style="background-color: #fff2cc;">The OSBACKUPDBA group for Oracle Database (typically, backupdba) : </span> </b><span style="text-align: justify;"> Create this group if we want a separate group of operating system users to have a limited set </span>of database backup and recovery related administrative privileges (the SYSBACKUP privilege).<br />
#<span style="background-color: #fff2cc;"> groupadd -g 504 backupdba </span><br />
<br />
<div style="text-align: justify;">
<b>v .) <span style="background-color: #fff2cc;">The OSDGDBA group for Oracle Data Guard (typically, dgdba) :</span></b><span style="background-color: #fff2cc;"> </span> Create this group if we want a separate group of operating sytsem users to have a limited set of privileges to administer and monitor Oracle Data Guard (the SYSDG privilege).</div>
#<span style="background-color: #fff2cc;"> groupadd -g 505 dgdba </span><br />
<br />
<div style="text-align: justify;">
<b>vi .) <span style="background-color: #fff2cc;">The OSKMDBA group for encyption key management (typically, kmdba) :</span></b><span style="background-color: #fff2cc;"> </span> Create this group if we want a separate group of operating sytem users to have a limited set of privileges for encryption key management such as Oracle Wallet Manager management (the SYSKM privilege).</div>
# <span style="background-color: #fff2cc;">groupadd -g 506 kmdba </span><br />
<br />
<div style="text-align: justify;">
<b>vii .) <span style="background-color: #fff2cc;">The OSDBA group for Oracle ASM (typically, asmdba) : </span> </b>The OSDBA group for Oracle ASM can be the same group u sed as the OSDBA group for the database, or we can create a separate OSDBA group for Oracle ASM to provide administrative access to Oracle ASM instances .</div>
#<span style="background-color: #fff2cc;"> groupadd -g 507 asmdba </span><br />
<br />
<div style="text-align: justify;">
<b>viii .) <span style="background-color: #fff2cc;">The OSASM group for Oracle ASM Administration (typically, asmadmin) :</span></b> Create this group as a separate group if we want to have separate administration privileges groups for Oracle ASM and Oracle Database administrators. Members of this group are granted the SYSASM system privileges to administer Oracle ASM .</div>
# <span style="background-color: #fff2cc;"> groupadd -g 508 asmoper</span><br />
<br />
<div style="text-align: justify;">
<b>ix .) <span style="background-color: #fff2cc;">The OSOPER group for Oracle ASM (typically, asmoper) : </span></b> This is an optional group. Create this group if we want a separate group of operating system users to have a limited set of Oracle instance administrative privileges (the SYSOPER for ASM privilege), including starting up and stopping the Oracle ASM instance . By default , members of the OSASM group also have all privileges granted by the SYSOPER for ASM privilege.</div>
# <span style="background-color: #fff2cc;">groupadd -g 509 asmadmin </span><br />
<br />
<b>x . ) Create Oracle user :</b><br />
# <span style="background-color: #fff2cc;">useradd -u 54321 -g oinstall -G dba,asmdba,backupdba,dgdba,kmdba oracle</span><br />
#<span style="background-color: #fff2cc;">passwd oracle</span><br />
<<ORACLE PASSWORD >><br />
<br />
<div style="text-align: justify;">
The -u option specifies the user ID. Using this command flag is optional because the system can provide with an automatically generated user ID number. However, Oracle recommends that we should specify a number. We must note the user ID number because we need it during preinstallation.</div>
<div>
<br /></div>
<div>
<br /></div>
<b style="background-color: #ffe599;">Step 8 : Creating oracle directories </b><br />
<div style="text-align: justify;">
As per OFA, oracle base directory has the path : /mount_point/app/oracle_sw_owner where mount_point is the mount point directory for the file system that will contain the Oracle software . I have used /u01 for the mount point directory. However, we could choose another mount point directory, such as /oracle or /opt/soft.</div>
#<span style="background-color: #fff2cc;"> mkdir -p /u01/oracle/product/12.1.0/db_1</span><br />
#<span style="background-color: #fff2cc;"> chown -R oracle:oinstall /u01</span><br />
# <span style="background-color: #fff2cc;">chmod -R 777 /u01</span><br />
<br />
<br />
<b style="background-color: #ffe599;">Step 9 : Setting Oracle Enviroment </b><br />
Edit the /home/oracle/.bash_profile file and add following lines:<br />
# <span style="background-color: #fff2cc;">su - oracle</span><br />
$ vi .bash_profile<br />
<span style="background-color: #fff2cc;">export TMP=/tmp</span><br />
<span style="background-color: #fff2cc;">export TMPDIR=$TMP</span><br />
<span style="background-color: #fff2cc;">export ORACLE_BASE=/u01/oracle</span><br />
<span style="background-color: #fff2cc;">export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/db_1</span><br />
<span style="background-color: #fff2cc;">export PATH=/usr/sbin:$PATH</span><br />
<span style="background-color: #fff2cc;">export PATH=$ORACLE_HOME/bin:$PATH</span><br />
<span style="background-color: #fff2cc;">export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib</span><br />
<span style="background-color: #fff2cc;">export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib</span><br />
<br />
<br />
<b style="background-color: #ffe599;">Step 10 : Check firewall and Selinux </b><br />
Make sure Selinux be either disable or permissive . Check "/etc/selinux/config" file and make following changes .<br />
<span style="background-color: #fff2cc;">SELINUX=permissive</span><br />
Once ,Selinux value is set than restart the server or or run the below command<br />
# <span style="background-color: #fff2cc;">setenforce Permissive</span><br />
<br />
If Firewall is enabled ,we need to disable it . we can disable by using below command<br />
# <span style="background-color: #fff2cc;">service iptables stop</span><br />
# <span style="background-color: #fff2cc;">chkconfig iptables off</span><br />
<br />
<br />
<b style="background-color: #ffe599;">Step 11 : Finally run the runInstaller for Installation of Oracle 12c release 1</b><br />
<b style="background-color: #ffe599;"><br /></b>
<a href="http://1.bp.blogspot.com/-MEeXb1nAQbY/UdAW56KP_eI/AAAAAAAAAaI/Fbu65cCkrlM/s688/000001.PNG" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="259" src="http://1.bp.blogspot.com/-MEeXb1nAQbY/UdAW56KP_eI/AAAAAAAAAaI/Fbu65cCkrlM/s640/000001.PNG" width="640" /></a><b style="background-color: #ffe599;"><br /></b><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
Once , runInstaller get initaited , OUI get invoked and rest are interative graphical console .<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-0vc60dUGJfI/UdAY3jvMJkI/AAAAAAAAAaY/aZuoS4Tmqd8/s689/1.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="476" src="http://4.bp.blogspot.com/-0vc60dUGJfI/UdAY3jvMJkI/AAAAAAAAAaY/aZuoS4Tmqd8/s640/1.PNG" width="640" /></a></div>
<br />
Click next and proceed forward .<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-0ivS7WQ16sg/UdAbD5K9tEI/AAAAAAAAAao/kOg2W7qaZa8/s689/2.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="480" src="http://3.bp.blogspot.com/-0ivS7WQ16sg/UdAbD5K9tEI/AAAAAAAAAao/kOg2W7qaZa8/s640/2.PNG" width="640" /></a></div>
<br />
Click on "Yes" button and proceed .<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-vVA9gk7k3UA/UdAbMj46iyI/AAAAAAAAAaw/n9yHlvw9TVw/s692/4.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="478" src="http://4.bp.blogspot.com/-vVA9gk7k3UA/UdAbMj46iyI/AAAAAAAAAaw/n9yHlvw9TVw/s640/4.PNG" width="640" /></a></div>
<br />
Select "Skip Software Updates" option and click on next button .<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-gdOARU0MSVk/UdAbblIZxEI/AAAAAAAAAa4/oo8sKO79d44/s688/4_1.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="480" src="http://2.bp.blogspot.com/-gdOARU0MSVk/UdAbblIZxEI/AAAAAAAAAa4/oo8sKO79d44/s640/4_1.PNG" width="640" /></a></div>
<br />
Select "Create and configure a database" option and click on next button<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-YbkJ30nCEDE/UdAbgUMqV6I/AAAAAAAAAbA/y2iC5PHd4W0/s694/5.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="474" src="http://3.bp.blogspot.com/-YbkJ30nCEDE/UdAbgUMqV6I/AAAAAAAAAbA/y2iC5PHd4W0/s640/5.PNG" width="640" /></a></div>
<br />
Here , I selected the "Desktop Class" option . Click on next button<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-593wQhasyHc/UdAbkkpXZQI/AAAAAAAAAbU/8Bg4NOZ9Fvk/s691/6.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="480" src="http://4.bp.blogspot.com/-593wQhasyHc/UdAbkkpXZQI/AAAAAAAAAbU/8Bg4NOZ9Fvk/s640/6.PNG" width="640" /></a></div>
<br />
Enter the Administrative Password and click next<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-iGzPnV-EHGE/UdAbki2fstI/AAAAAAAAAbM/7xvCwKSoqkg/s689/7.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="482" src="http://4.bp.blogspot.com/-iGzPnV-EHGE/UdAbki2fstI/AAAAAAAAAbM/7xvCwKSoqkg/s640/7.PNG" width="640" /></a></div>
<br />
Click on "Yes" option and proceed forward<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-C9Lc6KLnKIw/UdAbkgERnxI/AAAAAAAAAbI/RCbiYZryu04/s690/8.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="480" src="http://4.bp.blogspot.com/-C9Lc6KLnKIw/UdAbkgERnxI/AAAAAAAAAbI/RCbiYZryu04/s640/8.PNG" width="640" /></a></div>
<br />
Click on next button<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-ZcxbPBkK2U4/UdAhW9KMjNI/AAAAAAAAAdY/HoPNHbIOgfI/s690/9.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="482" src="http://3.bp.blogspot.com/-ZcxbPBkK2U4/UdAhW9KMjNI/AAAAAAAAAdY/HoPNHbIOgfI/s640/9.PNG" width="640" /></a></div>
<br />
Make sure all the prerequisite must be successfull and passed .<br />
<a href="http://4.bp.blogspot.com/-czPDPMPRf1A/UdAcS9dLjZI/AAAAAAAAAb8/As7dSL-6mIA/s849/13.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img border="0" height="436" src="http://4.bp.blogspot.com/-czPDPMPRf1A/UdAcS9dLjZI/AAAAAAAAAb8/As7dSL-6mIA/s640/13.PNG" width="640" /></a><br />
<br />
Summary page displays all the locations and database information . Click next<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-B-biZCjCLD4/UdAcS-3LMJI/AAAAAAAAAbs/nulwPhfyTbM/s751/14.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="438" src="http://2.bp.blogspot.com/-B-biZCjCLD4/UdAcS-3LMJI/AAAAAAAAAbs/nulwPhfyTbM/s640/14.PNG" width="640" /></a></div>
<br />
Oracle Database Installation in process<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-1D7EPGPhlew/UdAcS8FnQKI/AAAAAAAAAbw/uha7yh_84io/s749/15.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="438" src="http://4.bp.blogspot.com/-1D7EPGPhlew/UdAcS8FnQKI/AAAAAAAAAbw/uha7yh_84io/s640/15.PNG" width="640" /></a></div>
<br />
Execute the configurations scripts from root<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-3sBCLLZFRg0/UdAcUUBO84I/AAAAAAAAAcE/9X777mdi-eA/s787/16.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="418" src="http://3.bp.blogspot.com/-3sBCLLZFRg0/UdAcUUBO84I/AAAAAAAAAcE/9X777mdi-eA/s640/16.PNG" width="640" /></a></div>
<br />
Run the scripts from root .<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-VcS-PygSMSE/UdAcUSbnnyI/AAAAAAAAAcI/HUcm0XCd3Z0/s909/17.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="432" src="http://2.bp.blogspot.com/-VcS-PygSMSE/UdAcUSbnnyI/AAAAAAAAAcI/HUcm0XCd3Z0/s640/17.png" width="640" /></a></div>
<br />
Oracle Database in process<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-d_EGumdW-3c/UdAcVFHMrbI/AAAAAAAAAcU/72HB8xyDeeA/s748/18.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="442" src="http://4.bp.blogspot.com/-d_EGumdW-3c/UdAcVFHMrbI/AAAAAAAAAcU/72HB8xyDeeA/s640/18.PNG" width="640" /></a></div>
<br />
Database creation in process .<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-9vAFNoScHOo/UdAcW-0NpRI/AAAAAAAAAcc/FsIOvJCl3u4/s904/19.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="366" src="http://1.bp.blogspot.com/-9vAFNoScHOo/UdAcW-0NpRI/AAAAAAAAAcc/FsIOvJCl3u4/s640/19.PNG" width="640" /></a></div>
<br />
Database creation in process .<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-GzKTevahbdg/UdAcW4aiPsI/AAAAAAAAAcg/HO9XbA-B8BA/s903/20.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="366" src="http://4.bp.blogspot.com/-GzKTevahbdg/UdAcW4aiPsI/AAAAAAAAAcg/HO9XbA-B8BA/s640/20.PNG" width="640" /></a></div>
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-qZtH-Oeg7fE/UdAcYDITkpI/AAAAAAAAAcs/fIUsljkqwMo/s905/21.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="362" src="http://1.bp.blogspot.com/-qZtH-Oeg7fE/UdAcYDITkpI/AAAAAAAAAcs/fIUsljkqwMo/s640/21.PNG" width="640" /></a></div>
<br />
Database Creation complted .<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-Gn5F4sEMSrs/UdAcYd7dcAI/AAAAAAAAAcw/Jo4-Pe-EqjU/s750/22.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="442" src="http://1.bp.blogspot.com/-Gn5F4sEMSrs/UdAcYd7dcAI/AAAAAAAAAcw/Jo4-Pe-EqjU/s640/22.PNG" width="640" /></a></div>
<br />
Installation of Oracle database was successfull .<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-v8I3MFaMrLY/UdAcYrf_0CI/AAAAAAAAAc8/XIDvIR_zNiU/s739/23.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="370" src="http://1.bp.blogspot.com/-v8I3MFaMrLY/UdAcYrf_0CI/AAAAAAAAAc8/XIDvIR_zNiU/s640/23.PNG" width="640" /></a></div>
<br />
Finally connected with Oracle 12c database .<br />
<br />
<br />
<b>Enjoy <span style="background-color: yellow;">:-) :-) :-) </span></b><br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
<br /></div>
NEERAJ VISHENhttp://www.blogger.com/profile/04032578930084182369noreply@blogger.com10tag:blogger.com,1999:blog-940885937847335876.post-26717918449484858352013-06-27T00:14:00.002+05:302013-06-27T00:20:05.711+05:30Oracle Database 12c Available for Download <div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
<span style="font-family: inherit;">Finally, Oracle has released the most awaited oracle Database i.e, 12c (Oracle 12.1.0.1) . It is available for download from the Oracle Software Cloud (formerly know as eDelivery) and OTN (Oracle Tech Network) for 64bit linux and solaris . Oracle 12c is not available for 32 bit . Oracle has yet not released the AIX and Window database 12c s/w, hopefully will release soon . Here are links for download Oracle s/w. </span></div>
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><b>eDelivery : </b><span style="color: blue;"><a href="https://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=16496132" target="_blank"><span style="color: blue;">Click here</span></a> </span> to download from eDelivery .</span><br />
<b style="font-family: inherit;">OTN </b><span style="font-family: inherit;"> <b> </b></span><b style="font-family: inherit;"> : </b><a href="http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html" style="font-family: inherit;" target="_blank"><span style="color: blue;">Click here</span></a><span style="font-family: inherit;"> to download from OTN .</span><br />
<span style="font-family: inherit;"><b>Documentation : </b></span><a href="http://www.oracle.com/technetwork/database/enterprise-edition/documentation/index.html" target="_blank"><span style="color: blue;">Click here</span></a><span style="font-family: inherit;"> to download document of 12c</span><br />
<br />
<div style="text-align: justify;">
<span style="font-family: inherit;">There are some very exiting features in Oracle 12c Database .One of them is "Pluggable Database " which allows a single Oracle database instance to hold many other databases, allowing for more efficient use of system resources and easier management. I will be soon download and post about this features .</span></div>
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">An official Oracle price list, which was updated Tuesday, showed a "multitenant" database option priced at $17,500 per processor. A processor license for the main Enterprise Edition remained priced at $47,500 per processor.</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">Enjoy <b style="background-color: yellow;">:-)</b> </span><br />
<span style="font-family: inherit;"><br /></span>
</div>
NEERAJ VISHENhttp://www.blogger.com/profile/04032578930084182369noreply@blogger.com3tag:blogger.com,1999:blog-940885937847335876.post-11963589982834846472013-05-24T17:39:00.001+05:302013-05-24T17:39:57.429+05:30ORA-00020 and Impact on database on increasing processes values <div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div style="text-align: justify;">
The maximum number of processes is specified by the initialization parameter "<b>processes</b>" . When this maximum number of process is reached, no more requests will be processed. If we are try to connect with database then we get the below errors . </div>
<br />
Here for testing purpose, i have set my processes value to 30 for this demo .<br />
<br />
[oracle@server1 ~]$ sqlplus "sys/xxx as sysdba"<br />
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 24 13:36:53 2013<br />
Copyright (c) 1982, 2009, Oracle. All rights reserved.<br />
<b style="background-color: #ffe599;">ERROR:</b><br />
<span style="background-color: #ffe599;"><b>ORA-00020: maximum number of processes (30) exceeded</b></span><br />
<br />
I usually check my alert logfile for any oracle errors and find following info .<br />
<br />
<b style="background-color: #f4cccc;">Alert logfile</b><br />
Fri May 24 13:38:30 2013<br />
ORA-00020: No more process state objects available<br />
ORA-20 errors will not be written to the alert log for<br />
the next minute. Please look at trace files to see all<br />
the ORA-20 errors.<br />
<br />
<b>According to oracle docs :</b><br />
<br />
<b>Error:</b> ORA 20 <br />
<b>Text: </b> maximum number of processes <num> exceeded<br />
-------------------------------------------------------------------------------<br />
<div style="text-align: justify;">
<b>Cause :</b> An operation requested a resource that was unavailable. The maximum number of processes is specified by the initialization parameter PROCESSES. When this maximum is reached, no more requests are processed.</div>
<br />
<div style="text-align: justify;">
<b>Action : </b>Try the operation again in a few minutes. If this message occurs often, shut down Oracle, increase the PROCESSES parameter in the initialization parameter file, and restart Oracle.</div>
<br />
<div style="text-align: justify;">
Finally , i have decided to increase the no. of processes but didn't find any exact formula or any optimal value to set this parameter . So i have set it to 200 for now . Another issue here with us to connect with oracle, since we getting error while connecting with oracle . Here is one trick to create a session by using "<b>Prelim</b>" option . Interesting things about this option is that we can only use <span style="text-align: left;">the "shut abort" command nothing else (AFAIK). Here are the steps to set the processes value : </span></div>
<br />
[oracle@server1 ~]$ sqlplus -prelim "sys/xxxx as sysdba"<br />
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 24 13:38:55 2013<br />
Copyright (c) 1982, 2009, Oracle. All rights reserved.<br />
<br />
SQL> <span style="background-color: #ffe599;">shut immediate</span><br />
ORA-01012: not logged on<br />
<br />
SQL> <span style="background-color: #ffe599;">shut abort</span><br />
ORACLE instance shut down.<br />
<br />
SQL><span style="background-color: #ffe599;"> exit </span><br />
<br />
Once the instance is down we can easily increase the process value at mount stage .<br />
<br />
[oracle@server1 ~]$ <span style="background-color: #ffe599;">sqlplus "sys/sys as sysdba"</span><br />
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 24 13:51:40 2013<br />
Copyright (c) 1982, 2009, Oracle. All rights reserved.<br />
Connected to an idle instance.<br />
<br />
SQL> <span style="background-color: #ffe599;">startup mount</span><br />
ORACLE instance started.<br />
<br />
Total System Global Area 418484224 bytes<br />
Fixed Size 1336932 bytes<br />
Variable Size 310380956 bytes<br />
Database Buffers 100663296 bytes<br />
Redo Buffers 6103040 bytes<br />
Database mounted.<br />
<br />
SQL> <span style="background-color: #ffe599;">alter system set processes=200 scope=spfile;</span><br />
System altered.<br />
<br />
SQL> <span style="background-color: #ffe599;">shut immediate</span><br />
ORA-01109: database not open<br />
<br />
Database dismounted.<br />
ORACLE instance shut down.<br />
<br />
SQL> <span style="background-color: #ffe599;">startup</span><br />
ORACLE instance started.<br />
<br />
Total System Global Area 418484224 bytes<br />
Fixed Size 1336932 bytes<br />
Variable Size 310380956 bytes<br />
Database Buffers 100663296 bytes<br />
Redo Buffers 6103040 bytes<br />
Database mounted.<br />
Database opened.<br />
<br />
SQL> <span style="background-color: #ffe599;">select name,open_mode from v$database;</span><br />
NAME OPEN_MODE<br />
--------- --------------------<br />
ORCL READ WRITE<br />
<br />
<br />
<b>Impact on db while increasing the processes :: </b><br />
<div style="text-align: justify;">
While googling , i found a very useful comment by "Jonathan Lewis" .According to him , Increasing processes from say 1000 to 5000 increases the amount of shared memory that needs to be reserved at the O/S level and disrupt the memory in the SGA. The OS must be configured to support the larger amount of shared memory.</div>
<br />
<div style="text-align: justify;">
The impact at the O/S is that every process that starts up will want to build a memory map for the SGA - depending on the way we have configured memory pages and the way that strategy our O/S adopts to build maps it could demand a huge amount of O/S memory in a short time. The technology we need to avoid this issue comes in two different flavours: large memory pages, and shared memory maps.</div>
<br />
<div style="text-align: justify;">
The impact on the SGA is two-fold - each process and session has to create an entry in v$process and v$session, and allocate various memory structures in the SGA: acquiring the rows in v$session and v$process are serial actions, and the memory allocation in the SGA can cause massive flushing of the library cache . </div>
<br />
So , it is advisable to increase the number of processes while keeping it's impact in mind .<br />
<br />
<br />
<b>Enjoy <span style="background-color: yellow;">:-)</span> </b><br />
<br />
<br />
</div>
NEERAJ VISHENhttp://www.blogger.com/profile/04032578930084182369noreply@blogger.com1tag:blogger.com,1999:blog-940885937847335876.post-71910153632277682162013-04-20T15:17:00.003+05:302013-04-20T15:17:57.811+05:30Largest Databases of the World<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div style="text-align: justify;">
Greetings Everyone <b>: </b> I am back after a long break . I was busy in some other aspects of my life and now i have decided to keep updating this blog to share my knowledge and experience with you all . It's always give me a great feeling when i share some this with you all and learn alot from you guys through your mails , comments and chat.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<div style="text-align: justify;">
One day , i was thinking about some scenario's about a large database then one thing click in my mind i.e, "What is the size of the largest database in this world" and what database are they using ? I guess, it may be google and then search on net and find some interesting facts which i will like to share you all . Let’s get started and just keep in mind that size alone does not determine how big a database is, it’s the information it contains in the form of fields and records <span style="text-align: left;">and eventually it all depends on the technology being employed for storage and management. </span></div>
</div>
<div style="text-align: justify;">
<br />
<br /></div>
<b>No 1. <span style="background-color: #ffd966;">The World Data Centre for Climate (WDCC)</span> : </b><br />
<div style="text-align: justify;">
WDCC is Operated by the Max Planck Institute for Meteorology and German Climate Computing Centre, The World Data Centre for Climate is the largest database in the world with 220 terabytes of data that is readily available on the internet. Add to that 110 terabytes of climate simulation data and 6 petabytes of data stored on magnetic tapes.</div>
<br />
<div style="text-align: justify;">
The WDCC is included in the CERA database system. Access to the CERA database is possible from the Internet by use of a Java- based browser. The CERA ( Climate and Environmental Retrieving and Archiving) - data archive is realised on an ORACLE database connected to a STK Silo system. Thus large data sets may be stored under control of this System while the metadata associated with CERA permit an easy way to relocate data, which have to be retrieved.</div>
<br />
<br />
<b>No 2. <span style="background-color: #ffd966;">National Energy Research Scientific Computing Center (NERSCC)</span> :</b><br />
<div style="text-align: justify;">
Based in Oakland, California, National Energy Research Scientific Computing Center or NERSC is owned and operated by the Lawrence and the U.S. Department of Energy. Included in its database of 2.8 petabytes is information on atomic energy research, high energy physics experiments and simulations of the early universe.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
The High Performance Storage System (HPSS) is a modern, flexible, performance-oriented mass storage system. It has been used at NERSC for archival storage since 1998.</div>
<br />
<b>No 3. <span style="background-color: #ffd966;">AT&T</span> :</b><br />
<div style="text-align: justify;">
Bigger than sprint, AT&T boasts 1.9 trillion calling records which contribute to 323 terabytes worth of information. One factor behind the massiveness of its database is the fact that AT&T has been maintaining databases from the time when the technology to store terabytes wasn’t even available.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
The Daytona® data management system is used by AT&T to solve a wide spectrum of data management problems. For example, Daytona is managing over 312 terabytes of data in a 7x24 production data warehouse whose largest table contains over 743 billion records as of Sept 2005. Indeed, for this database, Daytona is managing over 1.924 trillion records; it could easily manage more but we ran out of data. Update: as of June 2007, Daytona is managing over 2.8 trillion records in this same data warehouse, with over 938 billion records in the largest table.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
AT&T is the sole source for the Daytona product, service and support and is the only company authorized to use the Daytona trademark for a database product.</div>
<br />
<br />
<b>No 4.<span style="background-color: #ffd966;"> Google</span> :</b><br />
<div style="text-align: justify;">
<span style="font-family: inherit;">The list </span>wouldn't<span style="font-family: inherit;"> be complete without Google. Subjected to around 100 million searches per day, Google is one of the largest databases in the world that has over 33 trillion database entries.</span><span style="font-family: inherit;"> Although the exact size of Google’s database is unknown, it’s said that Google accounts every single search that makes each day into its database which is around 91 million searches per day. Google stores every search and makes patterns from previous searches so that the user can be easily directed. Google also collects information of their users and stores them as entries in their database which is said to expand over 33 trillion entries. On top of that Google has simply expanded their database with Gmail and Google ads and with their acquisitions like YouTube.</span><span style="background-color: white; color: #222222; font-family: inherit;"> </span></div>
<br />
<div style="text-align: justify;">
Bigtable is a distributed storage system (built by Google) for managing structured data that is designed to scale to a very large size: petabytes of data across thousands of commodity servers.Many projects at Google store data in Bigtable, including web indexing, Google Earth, and Google Finance. These applications place very different demands on Bigtable, both in terms of data size (from URLs to web pages to satellite imagery) and latency requirements (from backend bulk processing to real-time data serving).</div>
<br />
<br />
<b>No 5. <span style="background-color: #ffd966;">Sprint</span> :</b><br />
<div style="text-align: justify;">
The third largest wireless telecommunications network in the US has a database of over 55 million users. Sprint processes over 365 million call detail records per day. Making up its huge database are 2.85 trillion rows of information.</div>
<div style="text-align: justify;">
<br /></div>
<br />
<b>No 6. <span style="background-color: #ffd966;">LexisNexis</span> :</b><br />
<div style="text-align: justify;">
LexisNexis is a company providing computer-assisted legal research services which bought Choicepoint in 2008 , and Choicepoint was in the business of acquiring inform ation about the American population including everything from phone numbers to criminal histories. It had over 250 terabytes of data on the American population until it was bought by LexisNexis .</div>
<br />
<br />
<b>No 7. <span style="background-color: #ffd966;">YouTube</span> :</b><br />
<div style="text-align: justify;">
<span style="font-family: inherit;">With over 60 hours of video uploaded per minute, yes, per minute, YouTube has a video database of around 45 terabytes. It has over 100 million videos being watched every day Reports say that about a 100 million videos are watched in YouTube which is about 60% of the overall number of videos watched online.</span></div>
<br />
<br />
<b>No 8. <span style="background-color: #ffd966;">Amazon</span> :</b><br />
<div style="text-align: justify;">
Containing records of more than 60 million active users, Amazon also has more than 250,000 full text books available online and allows users to comment and interact on virtually every page of the website. Overall, the Amazon database is over 42 terabytes in size.</div>
<br />
<div style="text-align: justify;">
Amazon SimpleDB is a highly available and flexible non-relational data store that offloads the work of database administration. Developers simply store and query data items via web services requests and Amazon SimpleDB does the rest.</div>
<br />
<br />
<b>No 9. <span style="background-color: #ffd966;">Central Intelligence Agency</span> :</b><br />
<div style="text-align: justify;">
With exact size understandably not made public, the CIA has comprehensive statistics on more than 250 countries of the world and also collects and distributes information on people, places and things. Although, the database is not open to public, portions of it are made available. Freedom of Information Act (FOIA) Electronic Reading Room is one such example where 100s of items are added from the database monthly.</div>
<br />
<div style="text-align: justify;">
The ARC maintains an automated system containing information concerning each individual accession ("job"). The A RC database includes detailed information at the file folder level for each accession retired after 1978, including the job number, box and file number, file title, level of security classification, inclusive dates, and disposition instructions, including date when disposition action will be taken. Less detailed information is maintained for accessions retired before 1978.</div>
<br />
<br />
<b>No 10. <span style="background-color: #ffd966;">Library of Congress</span> :</b><br />
<div style="text-align: justify;">
With over 130 million items including 29 million books, photographs and maps, 10,000 new items added each day and nearly 530 miles of shelves, the Library of Congress is a wonder to behold in itself. Only the text portion of the library would take up 20 terabytes of space. If internet isn’t helping you ou t in your research, head to the oldest federal cultural institution in the United States in DC.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
The Library of Congress offers a wide variety of online databases and Internet resources to the public via the Web, including its own online catalog. In addition, LC provides an easy-to-use gateway for searching other institutions' online catalogs and extensive links to resources on the Internet.</div>
<div style="text-align: justify;">
<br /></div>
<b>Souce :: </b><i>http://globtech24x7.com/10-largest-databases-of-the-world/</i><br />
<br />
<br />
<b>Enjoy <span style="background-color: yellow;">:-) </span></b><br />
<b><br /></b>
<b><br /></b>
<b><br /></b></div>
NEERAJ VISHENhttp://www.blogger.com/profile/04032578930084182369noreply@blogger.com5tag:blogger.com,1999:blog-940885937847335876.post-28070276004227865012012-08-14T12:29:00.002+05:302012-08-17T11:33:39.741+05:30Manual Upgradation From Oracle 9i to 10g <div dir="ltr" style="text-align: left;" trbidi="on"><div style="text-align: justify;"><b style="background-color: #fff2cc;">Upgradation</b> is the process of replacing our existing software with a newer version of the same product . For example, replacing oracle 9i release to oracle 10g release . Upgrading our applications usually does not require special tools. Our existing reports should look and behave the same in both products. However, sometimes minor changes may be seen in product .Upgradation is done at Software level .<br />
<br />
<div>I received a mail from a reader regarding the upgradation of database . He wants to upgrade his database from 9i to 10g . Here, i will like advice that it's better to upgrade our database from 9i to 11g as compare to 9i to 10g because Oracle extended support for 10gR2 will ends on 31-Jul-2013 and also there are more features available in Oracle 11g . We can directly upgrade to oracle 11g, if our curent database is 9.2.0.4 or newer then its supports direct upgrades to versions <b>9.2.0.4, 10.1 </b>and<b> 10.2 . </b>We can upgrade the version as<br />
<ul><li><b>7.3.3 -> 7.3.4 -> 9.2.0.8 -> 11.1</b></li>
<li><b>8.0.5 -> 8.0.6 -> 9.2.0.8 -> 11.1</b></li>
<li><b>8.1.7 -> 8.1.7.4 -> 9.2.0.8 -> 11.1</b></li>
<li><b>9.0.1.3-> 9.0.1.4 -> 9.2.0.8 -> 11.1</b></li>
<li><b>9.2.0.3 (or lower) -> 9.2.0.8 -> 11.1</b></li>
</ul>Oracle 11g client can access Oracle databases of versions 8i, 9i and 10g.</div><div><br />
</div></div>There are<span style="background-color: #fff2cc;"> generally four method</span> to Upgrade the Oracle database .<br />
<b>1.) Manual Upgradation :</b><br />
<b>2.) Upgradation Using the DBUA .</b><br />
<b>3.) export/import</b><br />
<b>4.) Data Copying</b><br />
<br />
Let's have a look on manual upgradation .<br />
<b style="text-align: justify;"><span style="background-color: #ffe599;"><br />
</span></b> <br />
<div style="text-align: left;"><b style="text-align: justify;"><span style="background-color: #ffe599;">Manual Upgradation :</span> </b><span style="text-align: justify;">A manual upgrade consists of running SQL scripts and utilities from a command line to upgrade a database to the new Oracle Database 10g release. While a manual upgrade gives us finer control over the upgrade process, it is more susceptible to error if any of the upgrade or pre-upgrade steps are either not followed or are performed out of order. Below are the steps </span></div><div style="text-align: justify;"><br />
</div><div style="text-align: left;"><b>1.) Install Oracle 10g software :</b> For Upgradation , Invoke the .exe or rumInstaller ad select "Install software only" to Install the Oracle S/w .</div><br />
<b>2.) Take Full Backup Database :</b> Take full database backup of database which is to be upgraded .<br />
<br />
<b>3.) Check the invalid Objects :</b> Check the invalid objects by running ultrp.sql scripts as<br />
SQL> <span style="background-color: #fff2cc;">@ORACLE_HOME/rdbms/admin/utlrp.sql</span><br />
<br />
<b>4.) Login into 9i home and run the utlu102i.sql : </b>This script is in oracle 10g home .<br />
SQL> <span style="background-color: #fff2cc;">spool pre_upgrd.sql</span><br />
SQL> <span style="background-color: #fff2cc;">@<ORACLE_10G_HOME>/rdbms/admin/utlu102i.sql</span><br />
SQL> <span style="background-color: #fff2cc;">spool off</span><br />
<br />
<div style="text-align: justify;">The above scripts checks a number of areas to make sure the instance is suitable for upgrade including</div><ul style="text-align: left;"><li>Database version</li>
<li>Log file sizes </li>
<li>Tablespace sizes </li>
<li>Server options</li>
<li>Initialization parameters (updated, depercated and obsolete)</li>
<li>Database components</li>
<li>Miscellaneous Warnings </li>
<li>SYSAUX tablespace present</li>
<li>Cluster information</li>
</ul><div style="text-align: justify;">The issues indicated by this script should be resolved before a manual upgrade is attempted. Once we have resolved the above warning , then re-run the above script once more to cross-check .</div><br />
<div style="text-align: justify;"><b>5.) Check for the timestamp with timezone Datatype : </b>The time zone files that are supplied with Oracle Database 10g have been updated from version 1 to version 2 to reflect changes in transition rules for some time zone regions. The changes may affect existing data of TIMESTAMP WITH TIME ZONE datatype. To preserve this TIMESTAMP data for updating according to the new time zone transition rules, we must run the <span style="background-color: #fff2cc;">utltzuv2.sql</span> script on the database before upgrading. This script analyzes our database for TIMESTAMP WITH TIME ZONE columns that a re affected by the updated time zone transition rules.</div>SQL> <span style="background-color: #fff2cc;">@ORACLE_10G_HOME/rdbms/admin/utltzuv2.sql</span><br />
SQL> <span style="background-color: #fff2cc;">select * from sys.sys_tzuv2_temptab;</span><br />
<br />
<div style="text-align: justify;">If the utltzuv2.sql script identifies columns with time zone data affected by a database upgrade, then back up the data in character format before we upgrade the database. After the upgrade, we must update the tables to ensure that the data is stored based on the new rules. If we export the tables before upgrading and import them after the upgrade, the conversion will happen automatically during the import.</div><br />
<b>6.) Shutdown the database :</b><br />
shut down the database and copy the spfile(or pfile) and password file from 9i home to 10g home .<br />
<br />
<div style="text-align: justify;"><b>7.) Upgrade Database :</b> Set following environment for 10g and login using "<span style="background-color: #fff2cc;">SYS</span>" user . It takes roughly half an hour to complete. Spool the output to a file so that you can review it afterward.</div><span style="background-color: #fff2cc;">ORACLE_SID</span>=<sid><br />
<span style="background-color: #fff2cc;">ORACLE_HOME</span>=<10g home><br />
<span style="background-color: #fff2cc;">PATH</span>=<10g path><br />
<span style="background-color: #fff2cc;">sqlplus / as sysdba</span><br />
<span style="background-color: #fff2cc;"><br />
</span> SQL><span style="background-color: #fff2cc;"> startup upgrade</span><br />
SQL><span style="background-color: #fff2cc;">spool upgrd_log.sql</span><br />
SQL><span style="background-color: #fff2cc;">@catupgrd.sql</span><br />
SQL><span style="background-color: #fff2cc;"> spool off</span><br />
<br />
<div style="text-align: justify;"><b>8.) Recompile any invalid objects : </b>Compare the number of invalid objects with the number noted in step 4 . It should hopefully be the same or less.</div><br />
SQL><span style="background-color: #fff2cc;">@ORACLE_HOME/rdbms/admin/utlrp.sql</span><br />
<br />
<b>9.) Check the status of the upgrade :</b><br />
SQL> <span style="background-color: #fff2cc;">@ORACLE_HOME/rdbms/admin/utlu102s.sql </span><br />
<div style="text-align: justify;">The above script queries the DBA_SERVER_REGISTRY to determine upgrade status and provides information about invalid or incorrect component upgrades. It also provides names of scripts to rerun to fix the errors.</div><br />
<b>10.) Edit the spfile : </b>Create a pfile from spfile as<br />
SQL><span style="background-color: #fff2cc;">create pfile from spfile ;</span><br />
<br />
<div style="text-align: justify;">Open the pfile and set the compatible parameter to 10.2.0.0.0 . Shutdown the database and create the new modified spfile .</div>SQL><span style="background-color: #fff2cc;">shut immediate</span><br />
SQL> <span style="background-color: #fff2cc;">create spfile from pfile ;</span><br />
<br />
<b>11.) Start the database normally </b><br />
SQL> <span style="background-color: #fff2cc;">startup </span><br />
and finally configure the Oracle net and drop the old Oracle database software i.e, 9i using the OUI .<br />
<br />
<b>Reference ::</b> <a href="http://docs.oracle.com/cd/B19306_01/server.102/b14238/upgrade.htm">http://docs.oracle.com/cd/B19306_01/server.102/b14238/upgrade.htm</a><br />
<br />
<br />
<b>Enjoy <span style="background-color: yellow;">:-)</span></b><br />
<br />
<br />
</div>NEERAJ VISHENhttp://www.blogger.com/profile/04032578930084182369noreply@blogger.com0tag:blogger.com,1999:blog-940885937847335876.post-59374867419004460672012-07-11T15:00:00.002+05:302012-07-11T15:04:22.229+05:30Oracle Pro On Otn<div dir="ltr" style="text-align: left;" trbidi="on"><div style="text-align: left;"><span style="text-align: justify;">Today , I got the status of </span><b style="background-color: #fff2cc; text-align: justify;">Oracle Pro</b><span style="text-align: justify;"> on </span><a href="https://forums.oracle.com/forums/profile.jspa?userID=836436" style="text-align: justify;" target="_blank"><b>otn</b></a><span style="text-align: justify;"> site . We always feel good to acheive some awards or appreciations for good work and this time same with me . </span></div><br />
Working with oracle is always exiting , challenging and great fun . I am passionate about oracle and try to learn as much as i can .<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/-pvKyP1VAC-8/T_1HcbTBewI/AAAAAAAAAWo/oTC1R8XvZGg/s1600/oracle+pro+status.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="280" src="http://3.bp.blogspot.com/-pvKyP1VAC-8/T_1HcbTBewI/AAAAAAAAAWo/oTC1R8XvZGg/s640/oracle+pro+status.JPG" width="640" /></a></div><br />
<br />
<b><br />
</b><br />
<b><br />
</b><br />
<br />
<b>Enjoy <span style="background-color: yellow;"> :-)</span> </b><br />
<br />
<br />
</div>NEERAJ VISHENhttp://www.blogger.com/profile/04032578930084182369noreply@blogger.com13tag:blogger.com,1999:blog-940885937847335876.post-10432676675936606992012-07-11T12:16:00.000+05:302012-07-11T12:16:23.053+05:30Control File Parallel Read & Write wait Event<div dir="ltr" style="text-align: left;" trbidi="on"><br />
<div style="text-align: justify;"><span style="font-family: inherit;">A <a href="http://neeraj-dba.blogspot.in/2011/04/control-file-contents_30.html" target="_blank"><b style="background-color: #ffe599;">control file contains</b></a> information about the associated database that is required for access by an instance, both at startup and during normal operation . It is the Oracle control file(s) that records information about the consistency of a database's physical structures and operational statuses . The database state changes through activities such as adding data files, altering the size or location of datafiles, redo being generated, archive logs being created, backups being taken, SCN numbers changing, or checkpoints being taken.</span></div><br />
<span style="font-family: inherit;"><b style="background-color: #fff2cc;">Why Control File Waits Occur ?</b></span><br />
<span style="font-family: inherit;">Control File Waits Occur due to the following reason .</span><br />
<b style="font-family: inherit; text-align: justify;">1.)</b><span style="font-family: inherit; text-align: justify;"> This wait occurs when a server process is updating all copies of the controlfile i.e, the session is writing physical blocks to all control files at same time . </span><br />
<b style="font-family: inherit;">2.) </b><span style="font-family: inherit;">The session commits a transaction to a controlfile</span><br />
<b style="font-family: inherit;">3.)</b><span style="font-family: inherit;"> Changing a generic entry in the controlfile, the new value is being written to all controlfiles</span><br />
<b style="font-family: inherit;">4.) </b><span style="font-family: inherit;">Controlfile resides on such a disk which is heavily used i.e, facing lots of i/o's .</span><br />
<span style="font-family: inherit;"><br />
</span><br />
<span style="font-family: inherit;">We can check the wait experience by a session using v$session_wait views as </span><br />
<span style="font-family: inherit;">SQL><span style="background-color: #fff2cc;">select event, wait_time, p1, p2, p3 frpm v$session_wait wher event like '%control%';</span></span><br />
<span style="font-family: inherit;">Here </span><span style="background-color: white;">wait_time </span><span style="font-family: inherit;"> is the elapsed time for reads or writes.</span><br />
<span style="font-family: inherit;"><br />
</span><br />
<span style="font-family: inherit;"><b style="background-color: #fff2cc;">Possible steps to reduce this wait :</b></span><br />
<b style="font-family: inherit; text-align: justify;">1.)</b><span style="font-family: inherit; text-align: justify;"> Reduce the number of controlfile copies to the minimum that ensures that not all copies can be lost at the same time.</span><br />
<b style="font-family: inherit;">2.)</b><span style="font-family: inherit;"> Move the controlfile copies to less saturated storage locations.</span><br />
<b style="font-family: inherit;">3.) </b><span style="font-family: inherit;"> Reduce the frequent log switches . To find the optimal time and size for log switch <a href="http://neeraj-dba.blogspot.in/2011/11/how-often-redo-log-file-should-switch.html" target="_blank"><b>check this post</b></a> . </span><br />
<br />
<div style="text-align: justify;"><span style="font-family: inherit;">As far my experience , Control file access is governed by activities such as redo logfile switching and checkpointing . Therefore it can only be influenced indirectly by tuning . It rarely occurs in my report and it get solved </span>automatically <span style="font-family: inherit;">when i check the other wait metric especially "log sync wait" and others waits . </span></div><span style="font-family: inherit;"><br />
</span><br />
<br />
<b>Enjoy <span style="background-color: yellow;">:-)</span> </b><br />
<b><br />
</b><br />
<b><br />
</b><br />
</div>NEERAJ VISHENhttp://www.blogger.com/profile/04032578930084182369noreply@blogger.com1tag:blogger.com,1999:blog-940885937847335876.post-1444968776379030252012-06-29T11:51:00.002+05:302013-10-05T12:30:42.244+05:30Configure OEM in Oracle 11gR2 on Window<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<div style="text-align: justify;">
<span style="font-family: inherit;">The <span style="background-color: #ffe599;"><b>Oracle Enterprise Manager</b></span>, (OEM) is the standard monitoring tool for Oracle databases . <span style="background-color: white; text-align: -webkit-auto;"><span style="line-height: 24px;">The OEM allows the DBA to easily manage every aspect of the database, even non-traditional tasks like applying patches and scheduling jobs . Sometimes OEM doesn't work due to some issue and i face such a issues while re-configuring it and <a href="http://neeraj-dba.blogspot.in/2012/01/how-to-reconfigure-oem-in-oracle-10g-on.html" target="_blank">follow this method</a> . Let's have a look on this below issue . All the steps are same for window or linux/unix platform . (just change path and variable where required)</span></span></span></div>
<div style="text-align: -webkit-auto;">
<span style="background-color: white; text-align: -webkit-auto;"><span style="line-height: 24px;"><br />
</span></span></div>
</div>
<div style="text-align: justify;">
<span style="font-family: inherit;">C:\><span style="background-color: #fff2cc;">set ORACLE_SID=orcl</span></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">C:\><span style="background-color: #fff2cc;">emca -deconfig dbcontrol db -repos drop</span></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">STARTED EMCA at Jun 28, 2012 10:52:27 AM</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">EM Configuration Assistant, Version 11.2.0.0.2 Production</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Copyright (c) 2003, 2005, Oracle. All rights reserved.</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Enter the following information:</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Database SID: <span style="background-color: #fff2cc;">orcl</span></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Listener port number: <span style="background-color: #fff2cc;">1521</span></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Password for SYS user:</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Password for SYS user:<span style="background-color: #fff2cc;"> xxxx</span></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Do you wish to continue? [yes(Y)/no(N)]:<b style="background-color: #fff2cc;"> y</b></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Jun 28, 2012 10:52:53 AM oracle.sysman.emcp.EMConfig perform</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">INFO: This operation is being logged at C:\app\Neerajs\cfgtoollogs\emca\orcl\emca_2012_06_28_10_52_24.log.</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Jun 28, 2012 10:52:55 AM oracle.sysman.emcp.EMDBPreConfig performDeconfiguration</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">WARNING: EM is not configured for this database. No EM-specific actions can be performed.</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Jun 28, 2012 10:52:56 AM oracle.sysman.emcp.ParamsManager checkListenerStatusForDBControl</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">WARNING: Error initializing SQL connection. SQL operations cannot be performed</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Jun 28, 2012 10:52:56 AM oracle.sysman.emcp.EMReposConfig invoke</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">INFO: Dropping the EM repository (this may take a while) ...</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Jun 28, 2012 11:03:43 AM oracle.sysman.emcp.EMReposConfig invoke</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">INFO: <span style="background-color: #fff2cc;">Repository successfully dropped</span></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Enterprise Manager configuration completed successfully</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">FINISHED EMCA at Jun 28, 2012 11:03:44 AM</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Now, creating OEM</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">C:\><span style="background-color: #fff2cc;">sqlplus sys/xxxx@orcl as sysdba</span></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 28 11:07:03 2012</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Copyright (c) 1982, 2010, Oracle. All rights reserved.</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Connected to:</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">With the Partitioning, OLAP, Data Mining and Real Application Testing options</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">SQL> <span style="background-color: #fff2cc;"> drop user SYSMAN cascade ; </span></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"> drop user SYSMAN cascade</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"> *</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">ERROR at line 1:</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">ORA-01918: user 'SYSMAN' does not exist</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">SQL> <span style="background-color: #fff2cc;">drop user MGMT_VIEW ;</span></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">drop user MGMT_VIEW</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"> *</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">ERROR at line 1:</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">ORA-01918: user 'MGMT_VIEW' does not exist</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">SQL> drop role MGMT_USER ;</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"> drop role MGMT_USER</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"> *</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">ERROR at line 1:</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">ORA-01919: role 'MGMT_USER' does not exist</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span><br />
<span style="background-color: white; line-height: 16px; text-align: left;"><span style="font-family: inherit;">Drop all the dependent synonyms of "sysman" </span></span><br />
<span style="background-color: white; line-height: 16px; text-align: left;"><span style="font-family: inherit;"><br /></span></span>
<span style="font-family: inherit;"><span style="background-color: white; line-height: 16px; text-align: left;">SQL> </span><span style="background-color: #fff2cc; line-height: 16px; text-align: left;">spool drop_synonym.sql </span></span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">SQL> <span style="background-color: #fff2cc;">SELECT 'drop public synonym ' || synonym_name || ';' FROM dba_synonyms WHERE table_owner = 'SYSMAN';</span></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">no rows selected</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">If we have got any output here , then run the below script .</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><span style="background-color: white; line-height: 16px; text-align: left;">SQL> </span><span style="background-color: #fff2cc; line-height: 16px; text-align: left;">@drop_synonym.sql </span></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span><br />
Let's Configure EM<br />
<br /></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">C:\><span style="background-color: #fff2cc;">set ORACLE_SID=orcl</span></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">C:\><span style="background-color: #fff2cc;">set ORACLE_HOME=C:\app\Neerajs\product\11.2.0\dbhome_1</span></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">C:\></span><span style="background-color: #fff2cc; font-family: inherit;">emca -config dbcontrol db -repos create</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">STARTED EMCA at Jun 28, 2012 11:08:59 AM</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">EM Configuration Assistant, Version 11.2.0.0.2 Production</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Copyright (c) 2003, 2005, Oracle. All rights reserved.</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Enter the following information:</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Database SID: <span style="background-color: #fff2cc;">orcl </span></span></div>
<div style="text-align: left;">
<span style="background-color: #f4cccc; font-family: inherit;">Exception in thread "main" oracle.sysman.emcp.exception.DatabaseUnavailableException: <b>Database instance unavailable.</b></span></div>
<div style="text-align: justify;">
<span style="background-color: #f4cccc; font-family: inherit;"> at oracle.sysman.emcp.DatabaseChecks.throwDBUnavailableException(DatabaseChecks.java:151)</span></div>
<div style="text-align: justify;">
<span style="background-color: #f4cccc; font-family: inherit;"> at oracle.sysman.emcp.DatabaseChecks.checkDbAvailabilityImpl(DatabaseChecks.java:144)</span></div>
<div style="text-align: justify;">
<span style="background-color: #f4cccc; font-family: inherit;"> at oracle.sysman.emcp.DatabaseChecks.checkDbAvailability(DatabaseChecks.java:163)</span></div>
<div style="text-align: justify;">
<span style="background-color: #f4cccc; font-family: inherit;"> at oracle.sysman.emcp.DatabaseChecks.getDbServiceName(DatabaseChecks.java:582)</span></div>
<div style="text-align: justify;">
<span style="background-color: #f4cccc; font-family: inherit;"> at oracle.sysman.emcp.EMConfigAssistant.performConfiguration(EMConfigAssistant.java:1272)</span></div>
<div style="text-align: justify;">
<span style="background-color: #f4cccc; font-family: inherit;"> at oracle.sysman.emcp.EMConfigAssistant.statusMain(EMConfigAssistant.java:574)</span></div>
<div style="text-align: justify;">
<span style="background-color: #f4cccc; font-family: inherit;"> at oracle.sysman.emcp.EMConfigAssistant.main(EMConfigAssistant.java:522)</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">and the emca log contains the below details : </span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><span style="background-color: #f4cccc;">or</span><span style="background-color: #f4cccc;">acle.sysman.emcp.util.GeneralUtil initSQLEngineLoacly</span></span></div>
<div style="text-align: justify;">
<span style="background-color: #f4cccc; font-family: inherit;">CONFIG<b>: ORA-01031: insufficient privileges</b></span></div>
<div style="text-align: justify;">
<span style="background-color: #f4cccc; font-family: inherit;">oracle.sysman.assistants.util.sqlEngine.SQLFatalErrorException: ORA-01031: insufficient privileges</span></div>
<div style="text-align: justify;">
<span style="background-color: #f4cccc; font-family: inherit;"><span class="Apple-tab-span" style="white-space: pre;"> </span>at oracle.sysman.assistants.util.sqlEngine.SQLEngine.executeImpl(SQLEngine.java:1655)</span></div>
<div style="text-align: justify;">
<span style="background-color: #f4cccc; font-family: inherit;"><span class="Apple-tab-span" style="white-space: pre;"> </span>at oracle.sysman.assistants.util.sqlEngine.SQLEngine.connect(SQLEngine.java:978)</span></div>
<div style="text-align: justify;">
<span style="background-color: #f4cccc; font-family: inherit;"><span class="Apple-tab-span" style="white-space: pre;"> </span>at oracle.sysman.emcp.util.GeneralUtil.initSQLEngineLoacly(GeneralUtil.java:445)</span></div>
<div style="text-align: justify;">
<span style="background-color: #f4cccc; font-family: inherit;"><span class="Apple-tab-span" style="white-space: pre;"> </span>at oracle.sysman.emcp.util.GeneralUtil.initSQLEngine(GeneralUtil.java:637)</span></div>
<div style="text-align: justify;">
<span style="background-color: #f4cccc; font-family: inherit;"><span class="Apple-tab-span" style="white-space: pre;"> </span>at oracle.sysman.emcp.DatabaseChecks.checkDbAvailabilityImpl(DatabaseChecks.java:118)</span></div>
<div style="text-align: justify;">
<span style="background-color: #f4cccc; font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">From the above logs, we find two hints i.e, database instance is down and ORA-01031 . We search on meta-link and find some useful information . So, some possible reasons may be : </span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><b>1.)</b> Database may not be up.</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><b>2.)</b> Database is started setting environment variable ORACLE_HOME with trailing '/'. Reset ORACLE_HOME and bounce the database.</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><b>3.)</b> Confirm that the database is up. Connect to the database via sql*plus and do:-</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">C:\><span style="background-color: #fff2cc;">sqlplus sys/xxxx@orcl as sysdba</span></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 28 17:25:00 2012</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Copyright (c) 1982, 2010, Oracle. All rights reserved.</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Connected to:</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">With the Partitioning, OLAP, Data Mining and Real Application Testing options</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">SQL><span style="background-color: #fff2cc;"> select instance_name,status,version from v$instance;</span></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">INSTANCE_NAME STATUS VERSION</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">---------------- ------------ -----------------</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">orcl OPEN 11.2.0.1.0</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><b>4.)</b> Confirm whether the $ORACLE_HOME environmental variable is correct. This involves comparing the current ORACLE_HOME environmental setting, with the one that was set the last time the database was started. </span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><b>a.)</b> To check the current ORACLE_HOME :-</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">set ORACLE_HOME (windows)</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><b>b.)</b> To check the $ORACLE_HOME which was set at the time when the database was last started .C</span><span style="font-family: inherit;">onnect to sqlplus as SYS as SYSDBA and issue the following:-</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">SQL> <span style="background-color: #fff2cc;">var OHM varchar2(100);</span></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">SQL> <span style="background-color: #fff2cc;">EXEC dbms_system.get_env('ORACLE_HOME', :OHM) ;</span></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">PL/SQL procedure successfully completed.</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">SQL> <span style="background-color: #fff2cc;">PRINT OHM</span></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">OHM</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><Oracle_Home which was set at the time that the database was last started will be returned here></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><b>c.)</b> If the ORACLE_HOME was set wrongly at the time that the database was started (as determined by above steps), then the database will need to be shut down and re-started. To shut the database down, it will be necessary to set the 'wrong' ORACLE_HOME (the one which is returned in the OHM output). After the database has been successfully shut down, then the correct ORACLE_HOME should be set, and the database can be re-started.</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><b>5.)</b> Confirm whether it's possible to connect to the database as the SYSDBA user remotely, using sqlplus. As part of the repository creation/drop process, the SYS user needs to be able to connect as SYSDBA . The ORA-01031: insufficient privileges error is returned when the SYS user is unable to connect as SYSDBA. To test this outside of dbconsole, test a sqlplus remote connection (one which connects via a tnsnames.ora alias) as sysdba. eg:-</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">$<span style="background-color: #fff2cc;">ORACLE_HOME/bin>sqlplus sys/<password>@<alias> as sysdba</span></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">If the above returns the ora-1031 error, check the following :-</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><b>a.) </b>Check whether the REMOTE_LOGIN_PASSWORDFILE initialization parameter is set correctly in the database. For the purposes of configuring dbconsole, it must be set to 'EXCLUSIVE'. This value can be checked by logging into the database via sql*plus and typing "show parameter remote".</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">SQL> <span style="background-color: #fff2cc;">show parameter remote </span></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">NAME TYPE VALUE</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">---------------------------- --------- --------------</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">remote_login_passwordfile string EXCLUSIVE</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">If this parameter is not set to 'EXCLUSIVE', the following command can be run .</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">SQL><span style="background-color: #fff2cc;">alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;</span></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;"><b>b.) </b>For dbconsole 11.2.0.1 and 11.2.0.2 check whether the %ORACLE_HOME%/network/admin/sqlnet.ora (or %TNS_ADMIN%/sqlnet.ora) contains the entry . </span></div>
<div style="text-align: justify;">
<span style="background-color: #fff2cc; font-family: inherit;">SQLNET.AUTHENTICATION_SERVICES = (NTS)</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">In my case the value of SQLNET.AUTHENTICATION_SERVICES is NONE . So, i changed to NTS and finally able to configure the OEM . The rest are as </span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">C:\><span style="background-color: #fff2cc;">set ORACLE_SID=orcl</span></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">C:\><span style="background-color: #fff2cc;">set ORACLE_HOME=C:\app\Neerajs\product\11.2.0\dbhome_1</span></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">C:\></span><span style="background-color: #fff2cc; font-family: inherit;">emca -config dbcontrol db -repos create -SID ORCL -PORT 1521 -ORACLE_HOME C:\app\Neerajs\product\11.2.0\dbhome_1</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">STARTED EMCA at Jun 28, 2012 11:51:08 AM</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">EM Configuration Assistant, Version 11.2.0.0.2 Production</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Copyright (c) 2003, 2005, Oracle. All rights reserved.</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Enter the following information:</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">Listener ORACLE_HOME [ C:\app\Neerajs\product\11.2.0\dbhome_1 ]: <span style="background-color: #fff2cc;">C:\app\Neerajs\product\11.2.0\dbhome_1</span></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Password for SYS user:</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Password for DBSNMP user:</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Password for SYSMAN user:</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Email address for notifications (optional):</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Outgoing Mail (SMTP) server for notifications (optional):</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">-----------------------------------------------------------------</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">You have specified the following settings</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Database ORACLE_HOME ................ C:\app\Neerajs\product\11.2.0\dbhome_1</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Local hostname ................ ramtech-199</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Listener ORACLE_HOME ................ C:\app\Neerajs\product\11.2.0\dbhome_1</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Listener port number ................ 1521</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Database SID ................ ORCL</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Email address for notifications ...............</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Outgoing Mail (SMTP) server for notifications ...............</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">-----------------------------------------------------------------</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Do you wish to continue? [yes(Y)/no(N)]:<span style="background-color: #fff2cc;"><b> y</b></span></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Jun 28, 2012 11:51:48 AM oracle.sysman.emcp.EMConfig perform</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">INFO: This operation is being logged at C:\app\Neerajs\cfgtoollogs\emca\orcl\emca_2012_06_28_11_51_07.log.</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Jun 28, 2012 11:51:51 AM oracle.sysman.emcp.EMReposConfig createRepository</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">INFO: Creating the EM repository (this may take a while) ...</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Jun 28, 2012 12:55:16 PM oracle.sysman.emcp.EMReposConfig invoke</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">INFO: <span style="background-color: #fff2cc;">Repository successfully created</span></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Jun 28, 2012 12:55:53 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">INFO: Uploading configuration data to EM repository (this may take a while) ...</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Jun 28, 2012 1:07:37 PM oracle.sysman.emcp.EMReposConfig invoke</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">INFO: Uploaded configuration data successfully</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Jun 28, 2012 1:07:58 PM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">INFO: Software library configured successfully.</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Jun 28, 2012 1:07:58 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">INFO: Deploying Provisioning archives ...</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Jun 28, 2012 1:10:46 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">INFO: Provisioning archives deployed successfully.</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Jun 28, 2012 1:10:47 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">INFO: Securing Database Control (this may take a while) ...</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Jun 28, 2012 1:11:26 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">INFO: Database Control secured successfully.</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Jun 28, 2012 1:11:27 PM oracle.sysman.emcp.util.DBControlUtil startOMS</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">INFO: Starting Database Control (this may take a while) ...</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Jun 28, 2012 1:14:55 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">INFO: Database Control started successfully</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Jun 28, 2012 1:15:05 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">INFO: >>>>>>>>>>> The Database Control URL is https://ramtech-199:5500/em <<<<<<<<<<<</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Jun 28, 2012 1:15:48 PM oracle.sysman.emcp.EMDBPostConfig invoke</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">WARNING:</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">************************ WARNING ************************</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in the file: C:/app/neerajs/product/11.2.0/dbhome_1/ramtech-199_orcl/sysman/config</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">/emkey.ora. Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">***********************************************************</span></div>
<div style="text-align: justify;">
<span style="background-color: #fff2cc; font-family: inherit;">Enterprise Manager configuration completed successfully</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">FINISHED EMCA at Jun 28, 2012 1:15:48 PM</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<b><span style="font-family: inherit;">Enjoy <span style="background-color: yellow;">:-)</span> </span></b></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<br /></div>
</div>
NEERAJ VISHENhttp://www.blogger.com/profile/04032578930084182369noreply@blogger.com3tag:blogger.com,1999:blog-940885937847335876.post-23646203986781982292012-05-29T16:26:00.003+05:302013-08-27T23:41:39.401+05:30Interested Transaction List ( ITL ) in Oracle<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
<span style="font-family: inherit;">Few days back, I received an e-mail from a reader , who is having doubt on locking mechanism i.e, related to <b style="background-color: #fff2cc;">ITL</b> . This post will give some good concept about the ITL . Let's start from the beginning i.e, from the transaction .</span></div>
<div>
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><b style="background-color: #ffe599;">Transaction : </b> A transaction starts when an update to data such as insert, update or delete occurs (or the intention to do so, e.g. SELECT FOR UPDATE) and ends when the session issues a commit or rollback. Like everything else, a specific transaction should have a name or an identifier to differentiate it from another one of the same type. Each transaction is given a transaction ID.</span></div>
<div>
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><b style="background-color: #ffe599;">Locks :</b> In multi-user systems, many users may update the same information at the same time. Locking allows only one user to update a particular data at a time , another person cannot modify the same data. When any user start the transaction then oracle automatically locks the data . Oracle uses a different locking method then most other databases, Oracle locking policy consists of the following :</span></div>
<div style="text-align: justify;">
<ul>
<li><span style="font-family: inherit; text-align: left;">Oracle locks data at the row level on modification only. There is no lock escalation to a block or table level, ever.</span></li>
<li><span style="font-family: inherit; text-align: left;">Oracle never locks data just to read it. There are no locks placed on rows of data by simple reads.</span></li>
<li><span style="font-family: inherit; text-align: left;">A writer of data does not block a reader of data .</span></li>
<li><span style="font-family: inherit; text-align: left;">A writer of data is blocked only when another writer of data has already locked the row it was going after.</span></li>
</ul>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
<b style="background-color: #ffe599; font-family: inherit;"> What is Interested Transaction List (ITL) ? </b></div>
</div>
<div style="text-align: justify;">
<span style="font-family: inherit;">It is a simple data structure called "<span style="background-color: #fff2cc;"><b>Interested Transaction List</b></span>" (ITL), a list that maintains information on transaction. The transaction identifier will be stored as an entry in the ITL in the header of the data block. The ITL contains several placeholders (or slots) for transactions. When a row in the block is locked for the first time, the transaction places a lock in one of the slots. In other words, the transaction makes it known that it is interested in some rows (hence the term "Interested Transaction List"). </span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">When a different transaction locks another set of rows in the same block, that information is stored in another slot and so on. When a transaction ends after a commit or a rollback, the locks are released and the slot which was used to mark the row locks in the block is now considered free .</span></div>
<div>
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">ITL slots are required for every transaction, it contains the transaction ID (XID) which is a pointer to an entry in the transaction table of a rollback segment. We can still read the data but other processes wanting to change the data must wait until the lock is released (commit or rollback). The ITL entry contains a XID, undo byes address (UBA) information, flags indicating the transaction status (Flag) and lock count (Lck) showing the number of rows locked by this transaction within the block and SCN at which the transaction is updated.</span></div>
<div>
<span style="font-family: inherit;"><br />
</span></div>
<div>
<span style="font-family: inherit;"><b style="background-color: #ffe599;">How many slots are typically available in ITL ?</b></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">During the table creation, the INITRANS parameter defines how many slots are initially created in the ITL. INITRANS is a block level storage parameter which can be specified while creating an object (table). INITRANS and MAXTRANS parameters are used to control the concurrent access to the same block . When the transactions exhaust all the available slots and a new transaction comes in to lock a row, the ITL grows to create another slot. The ITL can grow up to the number defined by the MAXTRANS parameter of the table, provided there is space in the block. Nevertheless, if there is no more room in the block, even if the MAXTRANS is high enough, the ITL cannot grow. the maximum value for MAXTRANS parameter is 255.</span></div>
<div>
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Each ITL entry in the block transaction variable header takes 24 bytes. Though a block can have a maximum of 255 different ITL entries, the ITL allocation is limited by block size. The database block size plays an important role when allocating the number of inital ITL entries for the block. The rule is "the total size allocated for initial ITLs SHOULD be LESS THAN 50% of the database block size" .</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><b>i.e, </b> <span style="background-color: #fff2cc;">sizeof(INITIAL ITLs) < ( 50% of the DATABASE BLOCK SIZE )</span></span></div>
<div>
<span style="font-family: inherit;"><br />
</span></div>
<div>
<span style="font-family: inherit;">BLOCK SIZE(B)<span class="Apple-tab-span" style="white-space: pre;"> </span> NO OF ITL entries allocated in block header</span></div>
<div>
<span style="font-family: inherit;">============ <span class="Apple-tab-span" style="white-space: pre;"> </span> =============================</span></div>
<div>
<span style="font-family: inherit;">2048 41</span></div>
<div>
<span style="font-family: inherit;">4096 83</span></div>
<div>
<span style="font-family: inherit;">8192 169</span></div>
<div>
<span style="font-family: inherit;"><br />
</span></div>
<div>
<span style="font-family: inherit;"><b style="background-color: #ffe599;">What happens when a transaction does not find a free slot to place its lock information? </b></span></div>
<div>
<span style="font-family: inherit;">This can occur because either</span></div>
<div>
<span style="font-family: inherit;"><b>1.) </b> the block is so packed that the ITL cannot grow to create a free slot, or </span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><b>2.) </b>the MAXTRANS has already been reached. In this case, the transaction that needs to lock a row has to wait until a slot becomes available. This wait is termed as ITL waits and can be seen from the view v$session_wait, in which the session is waiting on an event named "enqueue" Since the INITRANS is one, there is only one slot for the ITL. The rest of the block is empty. </span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br />
</span></div>
<div>
<span style="font-family: inherit;"><b style="background-color: #ffe599;">How to Diagnose the ITL Wait How do we know that a segment is experiencing ITL waits? </b></span></div>
<div>
<span style="font-family: inherit;">To check for ITL waits, set up the STATISTICS_LEVEL to TYPICAL in init.ora or via ALTER SYSTEM, then examine the segment statistics for the waits.</span></div>
<div>
<span style="font-family: inherit;">SQL></span><span style="background-color: #fff2cc; font-family: inherit;">select owner ,object_name from v$segment_statistics where statistic_name='ITL waits' and value> 0 ;</span></div>
<div>
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">When we suspect that a database is suffering from these waits, query the view v$session_wait. If the event on which the system is waiting is "enqueue," then the session might be experiencing ITL waits. However, enqueue is a very broad event that encompasses any type of locks, so it does not accurately specify the ITL waits. When the wait event is experienced, issue the following complex query a table tab1 (say) </span></div>
<div>
<span style="font-family: inherit;"><br />
</span></div>
<div>
<span style="font-family: inherit;">SQL><span style="background-color: #fff2cc;"> Select s.sid SID, s.serial# Serial#, l.type type, ' ' object_name, lmode held, request request</span></span></div>
<div>
<span style="background-color: #fff2cc; font-family: inherit;">from v$lock l, v$session s, v$process p</span></div>
<div>
<span style="background-color: #fff2cc; font-family: inherit;">where s.sid = l.sid and s.username <> ' ' and s.paddr = p.addr and l.type <> 'TM' and</span></div>
<div>
<span style="background-color: #fff2cc; font-family: inherit;">(l.type <> 'TX' or l.type = 'TX' and l.lmode <> 6) </span></div>
<div>
<span style="background-color: #fff2cc; font-family: inherit;"><b>union</b></span></div>
<div>
<span style="background-color: #fff2cc; font-family: inherit;">select s.sid SID, s.serial# Serial#, l.type type, object_name object_name, lmode held, request request</span></div>
<div>
<span style="background-color: #fff2cc; font-family: inherit;">from v$lock l, v$session s, v$process p, sys.dba_objects o</span></div>
<div>
<span style="background-color: #fff2cc; font-family: inherit;">where s.sid = l.sid and o.object_id = l.id1 and l.type = 'TM' and s.username <> ' ' and s.paddr = p.addr</span></div>
<div>
<span style="background-color: #fff2cc; font-family: inherit;"><b>union</b></span></div>
<div>
<span style="background-color: #fff2cc; font-family: inherit;">select s.sid SID, s.serial# Serial#,l.type type, '(Rollback='||rtrim(r.name)||')' object_name, lmode held,</span></div>
<div>
<span style="background-color: #fff2cc; font-family: inherit;">request request</span></div>
<div>
<span style="background-color: #fff2cc; font-family: inherit;">from v$lock l, v$session s, v$process p, v$rollname r</span></div>
<div>
<span style="background-color: #fff2cc; font-family: inherit;">where s.sid = l.sid and l.type = 'TX' and l.lmode = 6 and trunc(l.id1/65536) = r.usn and s.username <> ' ' and s.paddr = p.addr order by 5, 6 ; </span></div>
<div>
<span style="background-color: #fff2cc; font-family: inherit;">/ </span></div>
<div>
<span style="font-family: inherit;">The output of the query will look something like this:</span></div>
<div>
<span style="font-family: inherit;">SID SERIAL# TY OBJECT_NAME HELD REQUEST</span></div>
<div>
<span style="font-family: inherit;">----- ---------- ------- ------------------- ------- -------------</span></div>
<div>
<span style="font-family: inherit;">36 8428 TX 0 4</span></div>
<div>
<span style="font-family: inherit;">36 8428 TM TAB1 3 0</span></div>
<div>
<span style="font-family: inherit;">52 29592 TM TAB1 3 0</span></div>
<div>
<span style="font-family: inherit;">52 29592 TX (Rollback=RBS1_6) 6 0 </span></div>
<div>
<span style="font-family: inherit;"><br />
</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Note how the sessions 36 and 52 both have a TM (DML) lock on the table TAB1 of type 3 (Row Exclusive), but session 52 also holds a TX (Transaction) lock on the rollback segment of mode 6 (Exclusive) and Session 36 is waiting for a mode 4 (Share) lock. If this combination of locking occurs, we can be sure that session 36 is waiting for ITL on the table TAB1. Beware of a similar but different diagnosis when two sessions try to insert the same key value (a real locking – primary key violation). In that case, we would also see an additional TX lock on a rollback segment from the session that is waiting; for ITL waits, this TX lock on the rollback segment would not be seen. To know more about working of ITL <a href="http://avdeo.com/2008/06/16/interested-transaction-list-itl/" target="_blank"><b>click here</b></a></span></div>
<div>
<br /></div>
<div>
<span style="font-family: inherit;"><b style="background-color: #ffe599;">Examining ITL allocation :</b></span></div>
<div>
<b style="font-family: inherit;">1.) </b><span style="font-family: inherit;">Create a table with INITRANS 4.</span></div>
<div>
<span style="font-family: inherit;">SQL> <span style="background-color: #fff2cc;">create table test1 (I number) INITRANS 4 ; </span></span></div>
<div>
<span class="Apple-tab-span" style="white-space: pre;"><span style="font-family: inherit;"> </span></span></div>
<div>
<span style="font-family: inherit;"><b>2.)</b> Insert few record for testing purpose. A blockdump can be taken later. </span></div>
<div>
<span style="font-family: inherit;">SQL><span style="background-color: #fff2cc;"> insert into test1 values (10) ; </span></span></div>
<div>
<span style="font-family: inherit;">SQL><span style="background-color: #fff2cc;"> commit ;</span></span></div>
<div>
<span style="background-color: white; font-family: inherit;">now updated the table from sessions too these transaction are in hang state .</span></div>
<div>
<span style="font-family: inherit;"><br />
</span></div>
<div>
<span style="font-family: inherit;"><b>3.)</b> Find out the block number and the file id for dumping the block :</span></div>
<div>
<span style="font-family: inherit;">SQL> <span style="background-color: #fff2cc;">select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from test;</span></span></div>
<div>
<span style="font-family: inherit;">or use the below command </span></div>
<div>
<span style="font-family: inherit;">SQL> <span style="background-color: #fff2cc;">select header_file,header_block from dba_segments where owner='HR' and segment_name='TEST' ;</span> </span></div>
<div>
<span style="font-family: inherit;"><br />
</span></div>
<div>
<span style="font-family: inherit;"><b>4.) </b>Dump the data block:</span></div>
<div>
<span style="font-family: inherit;">SQL> <span style="background-color: #fff2cc;">alter system dump datafile 4 block min 636 block max 636 ;</span></span></div>
<div>
<span style="font-family: inherit;"><br />
</span></div>
<div>
<span style="font-family: inherit;"><b>5.)</b> Open the dump trace file located in USER_DUMP_DEST directory and check the following:</span></div>
<div>
<span style="background-color: #fff2cc; font-family: inherit;">Block dump from disk:</span></div>
<div>
<span style="background-color: #fff2cc; font-family: inherit;">buffer <b>tsn</b>: 4 <b>rdba</b>: 0x0100027c (4/636)</span></div>
<div>
<span style="background-color: #fff2cc; font-family: inherit;"><b>scn:</b> 0x0000.002a1814 seq: 0x02 flg: 0x04 tail: 0x18140602</span></div>
<div>
<span style="background-color: #fff2cc; font-family: inherit;">frmt: 0x02 chkval: 0x1561 type: 0x06=trans data</span></div>
<div>
<span style="background-color: #fff2cc; font-family: inherit;">Hex dump of block: st=0, typ_found=1</span></div>
<div>
<span style="background-color: #fff2cc; font-family: inherit;">Block header dump: 0x0100027c</span></div>
<div>
<span style="background-color: #fff2cc; font-family: inherit;">Object id on Block? Y</span></div>
<div>
<span style="background-color: #fff2cc; font-family: inherit;"><b>seg/obj:</b> 0x1277b <b>csc:</b> 0x00.2a1372 itc: 10 flg: E typ: 1 - DATA</span></div>
<div>
<span style="background-color: #fff2cc; font-family: inherit;"> brn: 0 <b>bdba</b>: 0x1000278 ver: 0x01 opc: 0</span></div>
<div>
<span style="background-color: #fff2cc; font-family: inherit;"> inc: 0 exflg: 0</span></div>
<div>
<span style="background-color: #fff2cc; font-family: inherit;"><b> Itl Xid Uba Flag Lck Scn/Fsc </b> </span></div>
<div>
<span style="background-color: #fff2cc; font-family: inherit;">0x01 0x0001.001.00000380 0x00c01117.0204.07 --U- 2 fsc 0x0000.002a1376</span></div>
<div>
<span style="background-color: #fff2cc; font-family: inherit;">0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000</span></div>
<div>
<span style="background-color: #fff2cc; font-family: inherit;">0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000</span></div>
<div>
<span style="background-color: #fff2cc; font-family: inherit;">0x04 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000</span></div>
<div>
<span style="background-color: #fff2cc; font-family: inherit;">bdba: 0x0100027c</span></div>
<div>
<span style="background-color: #fff2cc; font-family: inherit;">data_block_dump,data header at 0xe511924</span></div>
<div>
<span style="font-family: inherit;"><br />
</span></div>
<div>
<span style="font-family: inherit;"><b>itl </b> = interested transaction list (list of transactions on a block that have changes on that block) </span></div>
<div>
<span style="font-family: inherit;"><b>xid</b> = transaction id (it </span>is a set of three numbers – undo segment#, undo slot# and undo record# - separated by periods.<span style="font-family: inherit;">)</span></div>
<div>
<span style="font-family: inherit;"><b>uba</b> = undo block address (pointer to a transactions list of undo blocks) </span></div>
<div>
<span style="font-family: inherit;"><b>scn/fsc</b> = system change/commit number of the current transaction</span></div>
<div>
<span style="font-family: inherit;"><b>tsn</b> = Tablespace Number</span></div>
<div>
<span style="font-family: inherit;"><b>rdba</b> = Relative data block address.Its value is "0100027c" in hexa decimal & 16777852 in decimal</span></div>
<div>
<span style="font-family: inherit;"><b>seg/obj</b> = is the object_id. 0x 1277b = 75643 in 75643 in decimal.</span></div>
<div>
<b style="font-family: inherit;">itc: 10 </b><span style="font-family: inherit;"> = Number of interested transaction list(ITL) entry in the block.</span></div>
<div>
<span style="font-family: inherit;"><b>typ: 1</b> = data –> Indicates this is an data block</span></div>
<div>
<span style="font-family: inherit;"><b>Flag </b> = State for current transaction (C – Commited) U- indicates that the particular ITL was used.</span></div>
<div>
<span style="font-family: inherit;"><b>Lck</b> = Number of Lock held by the transacion</span></div>
<div>
<b>csc: 0x00.2a1372</b> = This is the comit SCN number of the block </div>
<div>
<br /></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">If we use locally managed tablespaces with automatic segment space management , we can more or less forget about these parameters as Oracle takes care of it for us . In Oracle 10g and above, this is now more to concern . In the past they were much more important that they are now . </span></div>
<div>
<span style="font-family: inherit;"><br />
</span><br />
<b><span style="background-color: #ffe599; font-family: inherit;">Note :</span></b><br />
<br />
<ul style="text-align: left;">
<li>Lck” column shows a value. It shows “2”, meaning two rows in the blocks is locked by a transaction. But, which transaction? To get that answer, note the value under the “Xid” column. It shows the transaction ID - 0x0001.001.00000380 . These numbers are in hexadecimal (as indicated by the 0x at the beginning of the number).</li>
<li>A transaction is identified by a transaction ID (XID) which is a set of three numbers – undo segment#, undo slot# and undo record# - separated by periods.</li>
<li>We can also check all the active transactions in the view v$transaction, where the columns XIDUSN, XIDSLOT and XIDSQN denote the undo segment#, undo slot# and undo rec# - the values that make up the transaction ID.</li>
<li><span style="background-color: white; line-height: 18px;"><span style="font-family: inherit;">The v$transaction view also contains the session address under SES_ADDR column, which can be used to join with the SADDR column of v$session view to get the session details</span></span></li>
</ul>
<br />
<br /></div>
<div>
<span style="font-family: inherit;"><b> References:-</b> http://www.rampant-books.com/</span></div>
<div>
<span style="font-family: inherit;"><br />
</span></div>
<div>
<span style="font-family: inherit;"><br />
</span></div>
<div>
<span style="font-family: inherit;"><b>Enjoy <span style="background-color: yellow;"> :-) </span></b></span></div>
<div>
<span style="font-family: inherit;"><br />
</span></div>
<div>
<span style="font-family: inherit;"><br />
</span></div>
<div>
<span style="font-size: x-small;"><br />
</span></div>
</div>
NEERAJ VISHENhttp://www.blogger.com/profile/04032578930084182369noreply@blogger.com1tag:blogger.com,1999:blog-940885937847335876.post-78050134867207319892012-05-26T12:06:00.001+05:302012-05-26T12:06:32.360+05:30Interpreting Raw Sql Trace File<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: inherit;"><br /></span><br />
<div style="text-align: justify;">
<span style="font-family: inherit;"><b style="background-color: #ffe599;">SQL_TRACE</b> is the main method for collecting SQL Execution information in Oracle. It records a wide range of information and statistics that can be used to tune SQL operations. The<b> </b>sql trace file contains a great deal of information . Each cursor that is opened after tracing has been enabled will be recorded in the trace file. </span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">The raw trace file mostly contains the cursor number . Eg, PARSING IN CURSOR #3 . EXECutes, FETCHes and WAITs are recorded against a cursor. The information applies to the most recently parsed statement within that cursor . Firstly, let's have a look on "<span style="background-color: #fff2cc;"><b>Wait Events</b></span>" . </span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br /></span></div>
<div style="text-align: justify;">
</div>
<div style="text-align: justify;">
<span style="background-color: #fff2cc; text-align: left;">WAIT #6: nam='db file sequential read' ela= 8458 file#=110 block#=63682 blocks=1 obj#=221 tim=506028963546</span>
</div>
<br />
<span style="font-family: inherit;"><b>WAIT </b> = An event that we waited for. </span><br />
<span style="font-family: inherit;"><b>nam </b> =<span style="white-space: pre;"> </span>What was being waited for .<span class="Apple-tab-span" style="white-space: pre;"> </span>The wait events here are the same as are seen in view V$SESSION_WAIT .</span><br />
<span style="font-family: inherit;"><b>ela </b> <span style="white-space: pre;">= </span>Elapsed time for the operation.(microseconds)</span><br />
<span style="font-family: inherit;"><b>p1</b><span class="Apple-tab-span" style="white-space: pre;"> = </span>P1 for the given wait event.</span><br />
<span style="font-family: inherit;"><b>p2</b><span class="Apple-tab-span" style="white-space: pre;"> = </span>P2 for the given wait event.</span><br />
<span style="font-family: inherit;"><b>p3</b> <span class="Apple-tab-span" style="white-space: pre;"> = </span>P3 for the given wait event.</span><br />
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><b>Example No. 1 :</b> <span style="background-color: #fff2cc;">WAIT #6: nam='db file sequential read' ela= 8458 file#=110 block#=63682 blocks=1 obj#=221 tim=506028963546 </span></span></div>
<span style="font-family: inherit; text-align: justify;">The above line can be translated as : Completed waiting under CURSOR no 6 for "db file sequential read" . We waited 8458 microseconds i.e. approx. 8.5 milliseconds .For a read of: File 110, start block 63682, for 1 Oracle block of Object number 221. Timestamp was 506028963546 . </span><br />
<div style="text-align: justify;">
<span style="font-family: inherit;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><b>Example no.2 : </b><span style="background-color: #fff2cc;">WAIT #1: nam='library cache: mutex X' ela= 814 idn=3606132107 value=3302829850624 where=4 obj#=-1 tim=995364327604</span></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">The above line can be translated as : Completed WAITing under CURSOR no 1 for "library cache: mutex X" .We waited 814 microseconds i.e. approx. 0.8 milliseconds .To get an eXclusive library cache latch with Identifier 3606132107 value 3302829850624 location 4 . It was not associated with any particular object (obj#=-1) Timestamp 995364327604.</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br /></span></div>
<span style="font-family: inherit;">The trace file also show the <a href="http://docs.oracle.com/cd/A57673_01/DOC/server/doc/A48506/sqlconce.htm" target="_blank">processing of the sql statements </a>. </span><span style="font-family: inherit;">Oracle processes SQL statements as follow :</span><br />
<span style="font-family: inherit;"><b>Stage 1:</b> Create a Cursor</span><br />
<span style="font-family: inherit;"><b>Stage 2: </b>Parse the Statement</span><br />
<span style="font-family: inherit;"><b>Stage 3:</b> Describe Results</span><br />
<span style="font-family: inherit;"><b>Stage 4:</b> Defining Output</span><br />
<span style="font-family: inherit;"><b>Stage 5:</b> Bind Any Variables</span><br />
<span style="font-family: inherit;"><b>Stage 6:</b> Execute the Statement</span><br />
<span style="font-family: inherit;"><b>Stage 7: </b>Parallelize the Statement</span><br />
<span style="font-family: inherit;"><b>Stage 8: </b>Fetch Rows of a Query Result</span><br />
<span style="font-family: inherit;"><b>Stage 9:</b> Close the Cursor</span><br />
<span style="font-family: inherit;">Now let's move to another important term </span>PARSING IN CURSOR #n . EXECutes, FETCHes and WAITs are recorded against a cursor. The information applies to the most recently parsed statement within that cursor.<br />
<br />
<b style="background-color: #ffe599;">PARSING IN CURSOR# : </b><br />
<div style="text-align: justify;">
<span style="font-family: inherit;"><b>Cursor :</b> In order for Oracle to process an SQL statement, it needs to create an area of memory known as the context area; this will have the information needed to process the statement. This information includes the number of rows processed by the statement, a pointer to the parsed representa-tion of the statement (parsing an SQL statement is the process whereby information is transferred to the server, at which point the SQL statement is evaluated as being valid).</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">A cursor is a handle, or pointer, to the context area. Through the cursor, a PL/SQL program can control the context area and what happens to it as the statement is processed. Two important features about the cursor are</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><b>1.)</b> Cursors allow you to fetch and process rows returned by a SE-LECT statement, one row at a time.</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><b>2.)</b> A cursor is named so that it can be referenced.</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><b>Parsing :</b> Oracle Parsing is the first step in processing of any database statement . PARSE record is accompanied by the cursor number. Let's have a look on "<span style="background-color: #fff2cc;">Parsing in Cursor</span>" of a particular trace file .</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br /></span></div>
<br />
<div style="text-align: justify;">
<span style="background-color: #fff2cc;"><b>PARSING IN CURSOR #2</b> len=92 dep=0 uid=0 oct=3 lid=0 tim=277930332201 hv=1039576264 ad='15d51e60' sqlid='dsz47ssyzdb68'</span></div>
<div style="text-align: justify;">
<span style="background-color: #fff2cc;">select p.PID,p.SPID,s.SID from v$process p,v$session s where s.paddr = p.addr and s.sid = 12</span></div>
<div style="text-align: justify;">
<span style="background-color: #fff2cc;">END OF STMT</span></div>
<div style="text-align: justify;">
<span style="background-color: #fff2cc;"><b>PARSE#2</b>:c=31250,e=19173,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=836746634,tim=27930332198</span></div>
<div style="text-align: justify;">
<span style="background-color: #fff2cc;"><b>EXEC #2:</b>c=0,e=86,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=836746634,tim=77930335666</span></div>
<div style="text-align: justify;">
<span style="background-color: #fff2cc;"><b>WAIT #2:</b> nam='SQL*Net message to client' ela= 10 driver id=1413697536 #bytes=1 p3=0 obj#=116 tim=77930335778</span></div>
<div style="text-align: justify;">
<span style="background-color: #fff2cc;"><b>FETCH #2</b>:c=0,e=805,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=836746634,tim=77930336684</span></div>
<div style="text-align: justify;">
<span style="background-color: #fff2cc;">WAIT #2: nam='SQL*Net message from client' ela= 363 driver id=1413697536 #bytes=1 p3=0 obj#=116 tim=77930337227</span></div>
<div style="text-align: justify;">
<span style="background-color: #fff2cc;">FETCH #2:c=0,e=31,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=836746634,tim=77930337421</span></div>
<div style="text-align: justify;">
<span style="background-color: #fff2cc;"><b>STAT #2</b> id=1 cnt=1 pid=0 pos=1 obj=0 op='NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=0 size=152 card=1)'</span></div>
<div style="text-align: justify;">
<span style="background-color: #fff2cc;">STAT #2 id=2 cnt=27 pid=1 pos=1 obj=0 op='MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=156 us cost=0 size=96 card=1)'</span></div>
<div style="text-align: justify;">
<span style="background-color: #fff2cc;">STAT #2 id=3 cnt=1 pid=2 pos=1 obj=0 op='NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=0 size=39 card=1)'</span></div>
<div style="text-align: justify;">
<span style="background-color: #fff2cc;">STAT #2 id=4 cnt=1 pid=3 pos=1 obj=0 op='FIXED TABLE FIXED INDEX X$KSLWT (ind:1) (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)'</span></div>
<div style="text-align: justify;">
<span style="background-color: #fff2cc;">STAT #2 id=5 cnt=1 pid=3 pos=2 obj=0 op='FIXED TABLE FIXED INDEX X$KSLED (ind:2) (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)'</span></div>
<div style="text-align: justify;">
<span style="background-color: #fff2cc;">STAT #2 id=6 cnt=27 pid=2 pos=2 obj=0 op='BUFFER SORT (cr=0 pr=0 pw=0 time=78 us cost=0 size=57 card=1)'</span></div>
<div style="text-align: justify;">
<span style="background-color: #fff2cc;">STAT #2 id=7 cnt=27 pid=6 pos=1 obj=0 op='FIXED TABLE FULL X$KSUPR (cr=0 pr=0 pw=0 time=130 us cost=0 size=57 card=1)'</span></div>
<div style="text-align: justify;">
<span style="background-color: #fff2cc;">STAT #2 id=8 cnt=1 pid=1 pos=2 obj=0 op='FIXED TABLE FIXED INDEX X$KSUSE (ind:1) (cr=0 pr=0 pw=0 time=0 us cost=0 size=56 card=1)'</span></div>
<div style="text-align: justify;">
<span style="background-color: #fff2cc;">WAIT #2: nam='SQL*Net message to client' ela= 7 driver id=1413697536 #bytes=1 p3=0 obj#=116 tim=77930338248</span></div>
<div style="text-align: justify;">
<span style="background-color: #fff2cc;">*** 2012-05-19 15:07:22.843</span></div>
<div style="text-align: justify;">
<span style="background-color: #fff2cc;">WAIT #2: nam='SQL*Net message from client' ela= 38291082 driver id=1413697536 #bytes=1 p3=0 obj#=116 tim=77968629417</span></div>
<div style="text-align: justify;">
<span style="background-color: #fff2cc;"><b>CLOSE #2</b>:c=0,e=30,dep=0,type=0,tim=77968629737</span></div>
<br />
<div style="text-align: justify;">
<br /></div>
<span style="font-family: inherit;"><b>len</b> = the number of characters in the SQL statement</span><br />
<span style="font-family: inherit; text-align: justify;"><b>dep </b> = tells the application/trigger depth at which the SQL statement was executed. dep=0 indicates that it was executed by the client application. dep=1 indicates that the SQL statement was executed by a trigger, the Oracle optimizer, or a space management call. dep=2 indicates that the SQL statement was called from a trigger, dep=3 indicates that the SQL statement was called from a trigger that was called from a trigger.</span><br />
<span style="font-family: inherit;"><b>uid </b> =</span><span style="font-family: inherit; white-space: pre;"> </span><span style="font-family: inherit;">Schema id under which SQL was parsed.</span><br />
<span style="font-family: inherit;"><b>oct</b><span class="Apple-tab-span" style="white-space: pre;"> = </span>Oracle command type.</span><br />
<span style="font-family: inherit;"><b>lid</b><span class="Apple-tab-span" style="white-space: pre;"> = </span>Privilege user id</span><br />
<span style="font-family: inherit;"><b>tim </b> = Timestamp.</span><br />
<span style="font-family: inherit;"><b>hv</b><span class="Apple-tab-span" style="white-space: pre;"> = </span>Hash id.</span><br />
<span style="font-family: inherit;"><b>ad</b><span class="Apple-tab-span" style="white-space: pre;"> = </span>SQLTEXT address</span><br />
<span style="font-family: inherit;"><br /></span><br />
<div style="text-align: justify;">
<span style="background-color: #fff2cc; font-family: inherit;"><b>PARSE #3: </b>c=15625, e=177782, p=2, cr=3, cu=0, mis=1, r=0, dep=0, og=1, plh=272002086,</span></div>
<div style="text-align: justify;">
<span style="background-color: #fff2cc; font-family: inherit;">tim=276565143470</span></div>
<span style="font-family: inherit;"><br /></span><br />
<span style="font-family: inherit;"><b>c </b> = CPU time (microseconds rounded to centiseconds granularity on 9i & above)</span><br />
<span style="font-family: inherit;"><b>e<span style="white-space: pre;"> = </span></b>Elapsed time (centiseconds prior to 9i, microseconds thereafter)</span><br />
<span style="font-family: inherit;"><b>p<span style="white-space: pre;"> = </span></b>Number of physical reads. </span><br />
<span style="font-family: inherit;"><b>cr<span style="white-space: pre;"> = </span></b>Number of buffers retrieved for CR reads.(Consistent reads)</span><br />
<span style="font-family: inherit;"><b>cu <span style="white-space: pre;">=</span></b>Number of buffers retrieved in current mode.</span><br />
<span style="font-family: inherit;"><b>mis = </b>Cursor missed in the cache.</span><br />
<span style="font-family: inherit;"><b>r<span style="white-space: pre;"> = </span></b>Number of rows processed.</span><br />
<span style="font-family: inherit;"><b>dep</b><span class="Apple-tab-span" style="white-space: pre;"> = </span>Recursive call depth (0 = user SQL, >0 = recursive).</span><br />
<span style="font-family: inherit;"><b>og</b><span class="Apple-tab-span" style="white-space: pre;"> = </span>Optimizer goal: 1=All_Rows, 2=First_Rows, 3=Rule, 4=Choose</span><br />
<span style="font-family: inherit;"><br /></span><br />
<span style="font-family: inherit;">From the above Parse line it is very clear that the total time taken in parsing the statement is 0.177 sec and the no. of physical reads done are 2 .</span><br />
<span style="font-family: inherit;"><br /></span><br />
<div style="text-align: justify;">
<span style="font-family: inherit;"><b><span style="background-color: #ffe599;">Bind Variables :</span> </b> If the SQL statement does reference bind variables, then the following SQL statement shown in the cursor can locate a section of text associated with each bind variable. For each bind variable there are a number of attributes listed. </span><span style="font-family: inherit;"> The following are the ones we are interested in here:</span></div>
<span style="font-family: inherit;"><br /></span><br />
<span style="font-family: inherit;"><b>mxl </b> = the maximum length - ie. the maximum number of bytes occupied by the variable. Eg. dty=2 and mxl=22 denotes a NUMBER(22) column.</span><br />
<span style="font-family: inherit;"><b>scl =</b> the scale (for NUMBER columns)</span><br />
<span style="font-family: inherit;"><b>pre =</b> the precision (for NUMBER columns)</span><br />
<span style="font-family: inherit;"><b>value</b> = the value of the bind variable</span><br />
<b style="font-family: inherit;">dty</b><span style="font-family: inherit;"> = the datatype. Typical values are:</span><br />
<span style="font-family: inherit;">1 VARCHAR2 or NVARCHAR2</span><br />
<span style="font-family: inherit;">2 NUMBER</span><br />
<span style="font-family: inherit;">8 LONG</span><br />
<span style="font-family: inherit;">11 ROWID</span><br />
<span style="font-family: inherit;">12 DATE</span><br />
<span style="font-family: inherit;">23 RAW</span><br />
<span style="font-family: inherit;">24 LONG RAW</span><br />
<span style="font-family: inherit;">96 CHAR</span><br />
<span style="font-family: inherit;">112 CLOB or NCLOB</span><br />
<span style="font-family: inherit;">113 BLOB</span><br />
<span style="font-family: inherit;">114 BFILE</span><br />
<br />
<br />
<div style="text-align: justify;">
<span style="font-family: inherit;"><b style="background-color: #ffe599;">EXEC : </b> Execute a pre-parsed statement. At this point, Oracle has all necessary information and resources, so the statement is executed. For example</span></div>
<div style="text-align: justify;">
<span style="background-color: #fff2cc; font-family: inherit;">EXEC #2:c=0,e=225,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=282618239403 </span></div>
<span style="font-family: inherit;"><br /></span><br />
<span style="font-family: inherit;"><b style="background-color: #ffe599;">Fetch :</b> Fetch rows from a cursor . For example</span><br />
<span style="background-color: #fff2cc; font-family: inherit;">FETCH #4:c=0,e=8864,p=1,cr=26,cu=0,mis=0,r=1,dep=0,og=1,plh=3564694750,tim=282618267037</span><br />
<span style="font-family: inherit;"><br /></span><br />
<span style="font-family: inherit;"><b style="background-color: #ffe599;">STAT : </b> Lines report explain plan statistics for the numbered [CURSOR]. These let us know the 'run time' explain plan. For example</span><br />
<span style="background-color: #fff2cc; font-family: inherit;">STAT #1 id=1 cnt=7 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=0 pr=0 pw=0 time=0 us cost=2 size=2128 card=1)'</span><br />
<br />
<span style="font-family: inherit;"><b>id </b> = Line of the explain plan which the row count applies to (starts at line 1). This is effectively the row source row count for all row sources in the execution tree</span><br />
<span style="font-family: inherit;"><b>cnt</b><span class="Apple-tab-span" style="white-space: pre;"> =</span> Number of rows for this row source.</span><br />
<span style="font-family: inherit;"><b>pid</b><span class="Apple-tab-span" style="white-space: pre;"> </span> = Parent id of this row source.</span><br />
<span style="font-family: inherit;"><b>pos</b><span class="Apple-tab-span" style="white-space: pre;"> </span> = Position in explain plan.</span><br />
<span style="font-family: inherit;"><b>obj </b> = Object id of row source (if this is a base object).</span><br />
<span style="font-family: inherit;"><b>op='...' </b>The row source access operation</span><br />
<span style="font-family: inherit;"><br /></span><br />
<span style="font-family: inherit;"><b style="background-color: #ffe599;">XCTEND </b> A transaction end marker. For example </span><span style="background-color: #fff2cc; font-family: inherit;">XCTEND rlbk=0, rd_only=1, tim=282636050491</span><br />
<span style="font-family: inherit;"><b>rlbk =</b>1 if a rollback was performed, 0 if no rollback (commit).</span><br />
<span style="font-family: inherit;"><b>rd_only =</b>1 if transaction was read only, 0 if changes occurred.</span><br />
<span style="font-family: inherit;"><br /></span><br />
<b style="background-color: #ffe599; font-family: inherit;">CLOSE </b><span style="font-family: inherit;"> cursor is closed .for example </span><span style="background-color: #fff2cc; font-family: inherit;">CLOSE #4:c=0,e=32,dep=0,type=0,tim=282636050688</span><br />
<b>c</b> <span style="font-family: inherit;"> = CPU time (microseconds rounded to centiseconds granularity on 9i and above)</span><br />
<span style="font-family: inherit;"><b>e </b> = Elapsed time (centiseconds prior to 9i, microseconds thereafter)</span><br />
<span style="font-family: inherit;"><b>dep </b> = Recursive depth of the cursor</span><br />
<span style="font-family: inherit;"><b>type</b> = Type of close operation</span><br />
<br />
<span style="font-family: inherit;"><b style="background-color: #ffe599;">Note :</b> Timestamp are used to determine the time between any 2 operations.</span><br />
<b style="font-family: inherit;">Reference :</b><span style="font-family: inherit;"> Metalink [ID 39817.1]</span><br />
<br />
<span style="font-family: inherit;"><br /></span><br />
<span style="font-family: inherit;">Enjoy <b style="background-color: yellow;">:-) </b></span><br />
<span style="font-family: inherit;"><br /></span><br />
<br /></div>
</div>NEERAJ VISHENhttp://www.blogger.com/profile/04032578930084182369noreply@blogger.com1tag:blogger.com,1999:blog-940885937847335876.post-55463399195859928852012-05-19T17:57:00.000+05:302012-05-26T14:56:44.429+05:30Tracing using Event 10046<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div style="text-align: justify;">
<b style="background-color: #ffe599;">Event 10046</b> is an well known feature within the Oracle database that generates detailed information on how a statement is parsed, what the values of bind variables are, and what wait events occurred during a particular session . The 10046 event allows us to track, at a very fine level, the execution of a given set of SQL statements. The 10046 event can be set at both the session level (for tracing of a specific session) or at a database level. It can be set either via a parameter file setting, or it can be set dynamically for the database, for our session or even for another session. </div>
<br />
<div style="text-align: justify;">
Event 10046 is quite helpful for DBA to determine exactly, why the database is performing in a certain way, by documenting how a statement is parsed and noting what wait events occured during the statement execution. </div>
<br />
<div style="text-align: justify;">
Most of us are well known familiar with Oracle's sql_trace facility, which emits performance information about Oracle parse, execute, fetch, commit, and rollback database calls. Using sql_trace is actually the equivalent of using event 10046 set at level 1. </div>
<br />
The <b style="background-color: #fff2cc;">event 10046 is internally </b>described as:<br />
<br />
SQL> <span style="background-color: #fff2cc;">set serveroutput on </span><br />
SQL><span style="background-color: #fff2cc;"> begin </span><br />
<span style="background-color: #fff2cc;"> dbms_output.put_line (SQLERRM (-10046)) ; </span><br />
<span style="background-color: #fff2cc;">end ; </span><br />
<span style="background-color: #fff2cc;">/</span><br />
<br />
<b style="background-color: #ffe599;">Event 10046 level </b><br />
There are five different levels . The Levels are as<br />
<div style="text-align: justify;">
<b>1.) <span style="background-color: white;">Level 1 </span></b> is the default. This level traces all activities until the trace session is stopped. This event enable the standard SQL_TRACE facility (same as SQL_TRACE=TRUE).</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<b style="background-color: white;">2.) </b><b style="background-color: white;"> </b><span style="text-align: left;"><b style="background-color: white;">Level 4 </b> provides level 1 tracing and displays the entered value for all bind variables. Bind variables are the values that the user enters. The code displays these bind variables as: b1, etc. When level 4 is activated, the substituted value for the bind variable is displayed in the trace file.</span></div>
<div style="text-align: justify;">
<span style="text-align: left;"><br />
</span></div>
<div style="text-align: justify;">
<span style="text-align: left;"><b><span style="background-color: white;">3.)</span><span style="background-color: white;"> </span></b></span><span style="text-align: left;"><b style="background-color: white;">Level 8 </b> provides level 1 tracing and displays a list of all database wait events. Database wait events list the reasons if the elapsed time is greater than the CPU time in the tkprof report.</span></div>
<div style="text-align: justify;">
<span style="text-align: left;"><br />
</span></div>
<b>4.)<span style="background-color: white;"> Level 12 </span> </b>provides level 1 tracing in addition to both bind variable substitution and database wait events.<br />
<br />
<div style="text-align: justify;">
<b>5.) <span style="background-color: white;">Level 16 </span></b> added in 11g to generate STAT line dumps for each execution. STAT dumping has been amended in 11g so that they are not aggregated across all executions but are dumped after execution.</div>
<div style="text-align: justify;">
<br /></div>
<b style="background-color: #ffe599;">How to enable the event 10046 </b><br />
The following steps are required to enable event 10046<br />
<br />
<b>1.) Set timed statistics to TRUE</b><br />
SQL> <span style="background-color: #fff2cc;">alter session set timed_statistics=true ; </span><br />
Session altered.<br />
<br />
<b>2.) set statistics level</b><br />
SQL> <span style="background-color: #fff2cc;">alter session set statistics_level=all ; </span><br />
Session altered.<br />
<br />
<b>3.) Set max_dump_file_size to UNLIMITED </b><br />
SQL> <span style="background-color: #fff2cc;">alter session set max_dump_file_size=unlimited ; </span><br />
Session altered.<br />
<br />
<b>4.) Enable the event </b><br />
SQL><span style="background-color: #fff2cc;"> alter session set events '10046 trace name context forever, level 12' ; </span><br />
<br />
We can <b><a href="http://neeraj-dba.blogspot.in/2011/04/how-to-determine-name-of-trace-file-to.html" target="_blank">check the generated trace file</a></b> in the directory specified in the parameter user_dump_dest<br />
SQL> <span style="background-color: #fff2cc;">show parameter user_dump_dest </span><br />
<br />
<b style="background-color: white;">Stop Tracing </b><br />
If the session is not exited then the trace can be disabled using :<br />
SQL> <span style="background-color: #fff2cc;">alter session set events '10046 trace name context off' ; </span><br />
<br />
<b style="background-color: #ffe599;">Enabling tracing in other sessions </b><br />
<div style="text-align: justify;">
We can enable tracing in other session using the oradebug utility .Here the steps to enable the tracing in another session.</div>
<div style="text-align: justify;">
<br /></div>
<b>1.) </b><span style="background-color: white;"><b>Find the SPID of the session which we want to trace </b></span><br />
SQL> <span style="background-color: #fff2cc;">select p.PID,p.SPID,s.SID from v$process p,v$session s</span><br />
<span style="background-color: #fff2cc;">where s.paddr = p.addr and s.sid = &SESSION_ID ; </span><span style="background-color: white;"> </span><br />
<span style="background-color: white;"><b>or</b> </span><br />
<span style="background-color: white;">SQL></span><span style="background-color: #fff2cc;"> select spid from v$process </span><br />
<span style="background-color: #fff2cc;">where addr=(select paddr from v$session where sid=<enter the SID>);</span><span style="background-color: white;"> </span><br />
We can identify the session_id by using the v$session view using the following attribute sid, serial#, username, osuser, machine ,server ,terminal , program .<br />
<br />
<div style="text-align: justify;">
<b>2.) Once the OS process id for the process has been determined then login to SQL*Plus as a dba and execute the following </b></div>
c:\> <span style="background-color: #fff2cc;">sqlplus / as sysdba</span><br />
SQL> <span style="background-color: #fff2cc;">oradebug setospid <SPID> ; </span> or <br />
SQL> <span style="background-color: #fff2cc;">oradebug setorapid <PID> ; </span><br />
SQL> <span style="background-color: #fff2cc;">oradebug unlimit ; </span><br />
SQL> <span style="background-color: #fff2cc;">oradebug event 10046 trace name context forever, level 8 ; </span><br />
<span style="background-color: #fff2cc;"><br />
</span><br />
<b style="background-color: white;">Turn off tracing </b><br />
SQL> <span style="background-color: #fff2cc;">oradebug event 10046 trace name context off ; </span><br />
<br />
<div style="text-align: justify;">
There are various way of enable, disable and vary the contents of this trace . Here are the some way to enable and disable the tracing .</div>
SQL> <span style="background-color: #fff2cc;">EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=><b>12</b>, nm=>' '); </span><span style="background-color: white;"> <--enable</span><br />
SQL> <span style="background-color: #fff2cc;">EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=><b>0</b>, nm=>' '); </span><span style="background-color: white;"> <--disable</span><br />
<br />
In next post , i will try to cover the topic " <a href="http://neeraj-dba.blogspot.in/2012/05/interpreting-raw-sql-trace-file.html" target="_blank">Interpreting Trace File</a>" .<br />
<br />
<br />
<b>Enjoy <span style="background-color: yellow;">:-) </span></b><br />
<br />
<br /></div>NEERAJ VISHENhttp://www.blogger.com/profile/04032578930084182369noreply@blogger.com0tag:blogger.com,1999:blog-940885937847335876.post-88095709658238699982012-05-10T13:47:00.001+05:302012-05-10T14:31:10.943+05:30Difference between LGWR SYNC and ASYNC in Oracle DataGuard<div dir="ltr" style="text-align: left;" trbidi="on"><br />
<div style="text-align: justify;"><b style="background-color: #ffe599;">Oracle Data Guard</b> redo log transport offers <span style="background-color: #fff2cc;">synchronous</span> log transport mode (LogXptMode = 'SYNC') or <span style="background-color: #fff2cc;">asynchronous</span> log transport mode (LogXptMode = 'ASYNC'). The difference is all about when the COMMIT happens . </div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;"><b style="background-color: #ffe599;">LogXptMode = ('SYNC'):</b> As the name implies, SYNC mode synchronizes the primary with the standby database and all DML on the primary server will NOT be committed until the logs have been successfully transported to the standby servers. The synchronous log transport mode is required for the Maximum Protection and Maximum Availability data protection modes.</div><div style="text-align: justify;"><b><span style="background-color: #ffe599;">LogXptMode = ('ASYNC'):</span> </b>Conversely, asynchronous mode (ASYNC) allows updates (DML) to be committed on the primary server before the log file arrives on the standby servers. The asynchronous log transport mode is required for the Maximum Performance data protection mode.</div><br />
There is a very good post witten by "<span style="background-color: #fff2cc;">Shawn Kelley</span>" related to Sync and Async in dataguard .<br />
<br />
<div style="text-align: justify;">LGWR is an attribute of the LOG_ARCHIVE_DEST_n parameter which is used to specify the network transmission mode. Specifying the SYNC attribute (which is the default), tells the LGWR process to synchronously archive to the local online redo log files at the same time it transmits redo data to archival destinations. Specifically, the SYNC atrribute performs all network I/O synchornously in conjunction with each write operation to the online redo log file. Transactions are not committed on the primary database until the redo data necessary to recover the transactions is received by the destination.</div><br />
<div style="text-align: justify;">The ASYNC attribute perfoms all network I/O asynchronously and control is returned to the executing application or user immediately. When this attribute is specified, the LGWR process archives to the local online redo log file and submits the network I/O request to the network server (LNSn process for that destination, and the LGWR process continues processing the next request without waiting for the network I/O to complete.</div><br />
<div style="text-align: justify;"><b style="background-color: #fff2cc;">What happens if the network between the Primary and Standby [database] is lost with LGWR SYNC and ASYNC ? or </b><b style="background-color: #fff2cc; text-align: left;">What happens if the standby database is shutdown with LGWR SYNC and ASYNC?</b></div><div style="text-align: justify;">This is dependent upon the database mode we have set. If we have set Maximum Protection, we have chosen a configuration that guarantees that no data loss will occur. We have set this up by specifying the LWGR, SYNC, and AFFIRM attributes of the LOG_ARCHIVE_DEST_n parameter for at least one standby database. This mode provides the highest level of data protection possible and to achieve this the redo data needed to recover each transaction must be written to both the local online redo log and the standby redo log on at least one standby database before the transaction commits. To ensure data loss cannot occur, the primary database shuts down if a fault (such as the network going down) prevents it from writing its redo stream to at least one remote standby redo log.</div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;">If we have set the Maximum Availability mode, we have chosen a configuration that provides the highest level of data protection that is possible without compromising the availablity of the primary database. Like the maximum database does not shut down if a fault prevents it from writing its redo stream to a remote standby redo log. Instead, the primary database operates in maximum performance mode until the fault is corrected and all gaps in redo log files are resolved. When all gaps are resolved, the primary database automatically resumes operating in maximum availabitly mode. This guarantees that no data loss will occur if the primary database fails, but only if a second fault does not complete set of redo data being sent from the primary database to at least one standby database.</div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;">If we have set the Maximum Performance mode (the default), we have chosen a mode that provides the highest level of data protection that is possible without affecting the performance of the primary database. This is accomplished by allowing a transaction to commit as soon as the redo data needed to recover the transaction is written to the local online redo log. The primary database's redo data stream is also written to at least one standby database, bu that the redo stream is written asynchronously with respect to the commitment of the transactions that create the redo data.</div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;">The maximum performance mode enables us to either set the LGWR and AYSNC attributes, or set the ARCH attribute on the LOG_ARCHIVE_DEST_n parameter for the standby database destination. If the primary database fails, we can reduce the amount of data that is not received on the standby destination by setting the LGWR and ASYNC attributes.</div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;"><b style="background-color: #fff2cc;">If LGWR SYNC or ASYNC is deployed, what process(es) bring(s) the standby database back into sync with the primary [database] if the network is lost and is then restored? How does it do it?</b></div><div style="text-align: justify;"><b style="background-color: #fff2cc;"><br />
</b></div><div style="text-align: justify;">Again, this is dependent upon the mode we have chosen for our database. The LGWR process (and possibly the LNSn process if we have multiple standby databases) is responsible for closing the gap.</div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;">My biggest question is, <span style="background-color: #fff2cc;">when the network to the standby is lost with SYNC or ASYNC, where is the information queued and how is it retransmitted once the network has been re-established?</span></div><div style="text-align: justify;">This implies that our database has been set to either maximum availability or maximum performance mode. We cannot use the ASYNC attribute with maximum protection mode. The information is queued in the local online redo log and the LGWR (and the LNSn) process will transmit the data to the standby database's online redo log file to close the gap once the network connectivity has been re-established</div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;">Gap recovery is handled through the polling mechanism. For physical and logical standby databases, Oracle Change Data Capture, and Oracle Streams, Data Guard performs gap detection and resolution by automatically retrieving missing archived redo log files from the primary database. No extra configuration settings are required to poll the standby database(s) to detect any gaps or to resolve the gaps.</div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;">The important consideration here is that automatic gap recovery is contigent upon the availablity of the primary database. If the primary database is not available and we have a configuration with mulitple physical standby databases, we can set up additional initialization parameters so that the Redo Apply can resolve archive gaps from another standby database.</div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;">It is possible to manually determine if a gap exists and to resolve those archive gaps. To manually determine if a gap exists, query the V$ARCHIVE_GAP view on our physical standby database. If a gap is found, we will then need to locate the archived log files on our primary database, copy them to our standby database, and register them.</div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;"><b>Enjoy <span style="background-color: yellow;"> :) </span></b></div><div style="text-align: justify;"><br />
</div></div>NEERAJ VISHENhttp://www.blogger.com/profile/04032578930084182369noreply@blogger.com5tag:blogger.com,1999:blog-940885937847335876.post-23024848090222420512012-05-08T18:17:00.000+05:302012-05-08T18:17:01.344+05:30Insert an Image File into Oracle Database<div dir="ltr" style="text-align: left;" trbidi="on"><br />
<div style="text-align: justify;"><span style="text-align: -webkit-auto;"><span style="font-family: inherit;">Photographs and pictures and Oracle BLOB data are easy to add to a Oracle table. There are two ways to load BLOBs and CLOBs into the database. The first method uses PL/SQL and the DBMS_LOB package and the BFILE datatype to transfer external LOB files into the database internal LOB structures. The second uses the Oracle Call Interface (OCI) to perform the same function. Here, we will use the first method . For inserting an image, we follow the folowing steps : </span></span></div><span style="text-align: -webkit-auto;"><span style="font-family: inherit;"><br />
</span></span><br />
<span style="font-family: inherit;"><b><span style="background-color: #ffe599;">Step 1 :</span> </b>First, we need to create a directory on the database (which is mapped to a directory in the server's filesystem). The user must be granted the <b style="background-color: #fff2cc;">create any directory</b> privilege.</span><br />
<span style="font-family: inherit;">SQL></span><span style="background-color: #fff2cc; font-family: inherit;">create directory photo_dir as 'c:\photo_dir' ; </span><br />
Directory created.<br />
<br />
<span style="font-family: inherit;"><b><span style="background-color: #ffe599;">Step 2 :</span> </b>Then we need to create a table which is used by procedure to insert the image in our table . Here we have to use a BLOB to insert the image .</span><br />
<span style="font-family: inherit;">SQL> <span style="background-color: #fff2cc;">create table temp_photo </span></span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> (</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> ID NUMBER(3) NOT NULL,</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> PHOTO_NAME VARCHAR2(50),</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> PHOTO BLOB</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> );</span><br />
Table created.<br />
<br />
<b>Step 3 :<span style="font-family: inherit;"> </span></b><span style="font-family: inherit;">Now <span style="background-color: white; color: #333333; text-align: -webkit-auto;">let's write the procedure to insert the image in the table above.</span></span><br />
<span style="font-family: inherit;"> </span><br />
<span style="font-family: inherit;">SQL><span style="background-color: #fff2cc;"> create or replace PROCEDURE load_file (</span></span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> p_id number,</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> p_photo_name in varchar2) IS</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> src_file BFILE;</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> dst_file BLOB;</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> lgh_file BINARY_INTEGER;</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> BEGIN</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> src_file := bfilename('PHOTO_DIR', p_photo_name);</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> -- insert a NULL record to lock</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> INSERT INTO temp_photo</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> (id, photo_name, photo)</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> VALUES</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> (p_id , p_photo_name ,EMPTY_BLOB())</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> RETURNING photo INTO dst_file;</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> -- lock record</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> SELECT photo </span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> INTO dst_file</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> FROM temp_photo</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> WHERE id = p_id</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> AND photo_name = p_photo_name</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> FOR UPDATE;</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> -- open the file</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> dbms_lob.fileopen(src_file, dbms_lob.file_readonly);</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> -- determine length</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> lgh_file := dbms_lob.getlength(src_file);</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> -- read the file</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> dbms_lob.loadfromfile(dst_file, src_file, lgh_file);</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> -- update the blob field</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> UPDATE temp_photo </span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> SET photo = dst_file</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> WHERE id = p_id</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> AND photo_name = p_photo_name;</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> -- close file</span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> dbms_lob.fileclose(src_file); </span><br />
<span style="background-color: #fff2cc; font-family: inherit;"> END load_file;</span><br />
<span style="font-family: inherit;"><span style="background-color: #fff2cc;">/</span> </span><br />
<span style="font-family: inherit;"><br />
</span><br />
<span style="font-family: inherit;"><b style="background-color: #ffe599;">Step 4 :</b> We can test it from SQL*Plus </span><br />
<span style="font-family: inherit;">SQL> </span><span style="background-color: #fff2cc; font-family: inherit;">execute load_file(1,'rdht.jpg') ; </span><br />
<span style="font-family: inherit;"><br />
</span><br />
<span style="font-family: inherit;"><b>Note : </b>Remember that the file <span style="background-color: #fff2cc;">rdht.jpg</span> should exist in the server's <span style="background-color: #fff2cc;">'c:\photo_dir'</span> directory .</span><br />
<span style="font-family: inherit;"><br />
</span><br />
<span style="font-family: inherit;"><br />
</span><br />
<span style="font-family: inherit;"><b>Enjoy <span style="background-color: yellow;">:-)</span></b></span><br />
<span style="font-family: inherit;"><br />
</span><br />
<span style="font-family: inherit;"><br />
</span></div>NEERAJ VISHENhttp://www.blogger.com/profile/04032578930084182369noreply@blogger.com8tag:blogger.com,1999:blog-940885937847335876.post-22777953688775318102012-04-28T17:00:00.000+05:302012-04-28T17:00:28.687+05:30Understanding Indexes Concept<div dir="ltr" style="text-align: left;" trbidi="on"><br />
<div style="text-align: justify;"><b style="background-color: #ffe599;">Indexes</b> plays and crucial role in the performance tunning of a database . It is very important to know how the index work i.e, how indexes fetches the data's from a tables . There is a very good post by <b>rleishman</b> on the working of indexes . Let's have a look . </div><br />
<b>What is an Index ? </b><br />
<div style="text-align: justify;">An index is a schema object that contains an entry for each value that appears in the indexed column(s) of the table or cluster and provides direct, fast access to rows. It is just as the index in this manual helps us to locate information faster than if there were no index, an Oracle Database index provides a faster access path to table data . </div><div style="text-align: justify;"><br />
</div><b>Blocks </b><br />
<div style="text-align: justify;">First we need to understand a block. A block - or page for Microsoft boffins - is the smallest unit of disk that Oracle will read or write. All data in Oracle - tables, indexes, clusters - is stored in blocks. The block size is configurable for any given database but is usually one of 4Kb, 8Kb, 16Kb, or 32Kb. Rows in a table are usually much smaller than this, so many rows will generally fit into a single block. So we never read "just one row"; we will always read the entire block and ignore the rows we don't need. Minimising this wastage is one of the fundamentals of Oracle Performance Tuning.</div><br />
Oracle uses two different index architectures: <b style="background-color: #ffe599;">b-Tree indexes</b> and <b style="background-color: #ffe599;">bitmap indexes</b>. Cluster indexes, bitmap join indexes, function-based indexes, reverse key indexes and text indexes are all just variations on the two main types. b-Tree is the "normal" index .<br />
<br />
<b>The "-Tree" in b-Tree </b><br />
<div style="text-align: justify;">A b-Tree index is a data structure in the form of a tree - no surprises there - but it is a tree of database blocks, not rows. Imagine the leaf blocks of the index as the pages of a phone book . <span style="text-align: left;">Each page in the book (leaf block in the index) contains many entries, which consist of a name (indexed column value) and an address (ROWID) that tells us the physical location of the telephone (row in the table).</span></div>The names on each page are sorted, and the pages - when sorted correctly - contain a complete sorted list of every name and address<br />
<br />
<div style="text-align: justify;">A sorted list in a phone book is fine for humans, beacuse we have mastered "the flick" - the ability to fan through the book looking for the page that will contain our target without reading the entire page. When we flick through the phone book, we are just reading the first name on each page, which is usually in a larger font in the page header. Oracle cannot read a single name (row) and ignore the reset of the page (block); it needs to read the entire block.</div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;">If we had no thumbs, we may find it convenient to create a separate ordered list containing the first name on each page of the phone book along with the page number. This is how the branch-blocks of an index work; a reduced list that contains the first row of each block plus the address of that block. In a large phone book, this reduced list containing one entry per page will still cover many pages, so the process is repeated, creating the next level up in the index, and so on until we are left with a single page: the root of the tree.</div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;"><b>For example : </b></div><div style="text-align: justify;">To find the name <b>Gallileo</b> in this b-Tree phone book, we:</div><div style="text-align: justify;"><span style="background-color: #ead1dc;">=></span> Read page 1. This tells us that page 6 starts with Fermat and that page 7 starts with Hawking.</div><div style="text-align: justify;"><span style="background-color: #ead1dc;">=></span> Read page 6. This tells us that page 350 starts with Fyshe and that page 351 starts with Garibaldi.</div><div style="text-align: justify;"><span style="background-color: #ead1dc;">=></span> Read page 350, which is a leaf block; we find Gallileo's address and phone number.</div><div style="text-align: justify;"><span style="background-color: #ead1dc;">=></span> That's it; 3 blocks to find a specific row in a million row table. In reality, index blocks often fit 100 or more rows, so b-Trees are typically quite shallow. I have never seen an index with more than 5 levels. Curious? Try this:</div><br />
SQL> <span style="background-color: #fff2cc;">select index_name, blevel+1 from user_indexes order by 2 ;</span><br />
<div style="text-align: justify;">user_indexes.blevel is the number of branch levels. Always add 1 to include the leaf level; this tells us the number of blocks a unique index scan must read to reach the leaf-block. If we're really, really, insatiably curious; try this in SQL*Plus:</div><br />
<span style="background-color: white;">SQL> </span><span style="background-color: #fff2cc;">accept index_name prompt "Index Name: " </span><br />
<span style="background-color: white;">SQL> </span><span style="background-color: #fff2cc;">alter session set tracefile_identifier='&index_name' ; </span><br />
<span style="background-color: white;">SQL> </span><span style="background-color: #fff2cc;">column object_id new_value object_id</span><br />
<span style="background-color: white;">SQL> </span><span style="background-color: #fff2cc;">select object_id from user_objects where object_type = 'INDEX' and </span><span style="background-color: #fff2cc;">object_name=upper('&index_name');</span><br />
<span style="background-color: white;">SQL> </span><span style="background-color: #fff2cc;">alter session set events 'Immediate trace name treedump level &object_id';</span><br />
<span style="background-color: white;">SQL> </span><span style="background-color: #fff2cc;">alter session set tracefile identifier="" ;</span><br />
<span style="background-color: white;">SQL> </span><span style="background-color: #fff2cc;">show parameter user_dump_dest </span><br />
<span style="background-color: #d0e0e3;"><br />
</span><br />
<div style="text-align: justify;">Give the name of an index on a smallish table (because this will create a BIG file). Now, on the Oracle server, go to the directory shown by the final SHOW PARAMETER user_dump_dest command and find the trace file - the file name will contain the index name. Here is a sample:</div><br />
<span style="background-color: #d9ead3;">---- begin tree dump</span><br />
<span style="background-color: #d9ead3;">branch: 0x68066c8 109078216 (0: nrow: 325, level: 1)</span><br />
<span style="background-color: #d9ead3;"> leaf: 0x68066c9 109078217 (-1: nrow: 694 rrow: 694)</span><br />
<span style="background-color: #d9ead3;"> leaf: 0x68066ca 109078218 (0: nrow: 693 rrow: 693)</span><br />
<span style="background-color: #d9ead3;"> leaf: 0x68066cb 109078219 (1: nrow: 693 rrow: 693)</span><br />
<span style="background-color: #d9ead3;"> leaf: 0x68066cc 109078220 (2: nrow: 693 rrow: 693)</span><br />
<span style="background-color: #d9ead3;"> leaf: 0x68066cd 109078221 (3: nrow: 693 rrow: 693)</span><br />
<span style="background-color: #d9ead3;"> ...</span><br />
<span style="background-color: #d9ead3;"> ...</span><br />
<span style="background-color: #d9ead3;"> leaf: 0x68069cf 109078991 (320: nrow: 763 rrow: 763)</span><br />
<span style="background-color: #d9ead3;"> leaf: 0x68069d0 109078992 (321: nrow: 761 rrow: 761)</span><br />
<span style="background-color: #d9ead3;"> leaf: 0x68069d1 109078993 (322: nrow: 798 rrow: 798)</span><br />
<span style="background-color: #d9ead3;"> leaf: 0x68069d2 109078994 (323: nrow: 807 rrow: 807)</span><br />
<span style="background-color: #d9ead3;">----- end tree dump</span><br />
<span style="text-align: justify;">This index has only a root branch with 323 leaf nodes. Each leaf node contains a variable number of index entries up to 807! A deeper index would be more interesting, but it would take a while to dump.</span><br />
<br />
<b>"B" is for...</b><br />
<div style="text-align: justify;">Contrary to popular belief, b is not for binary; it's balanced.</div><div style="text-align: justify;">As we insert new rows into the table, new rows are inserted into index leaf blocks. When a leaf block is full, another insert will cause the block to be split into two blocks, which means an entry for the new block must be added to the parent branch-block. If the branch-block is also full, it too is split. The process propagates back up the tree until the parent of split has space for one more entry, or the root is reached. A new root is created if the root node splits. Staggeringly, this process ensures that every branch will be the same length. </div><br />
<b>How are Indexes used ?</b><br />
Indexes have three main uses:<br />
<br />
<ul style="text-align: left;"><li><b>To quickly find specific rows by avoiding a Full Table Scan</b></li>
</ul><br />
<div style="text-align: justify;">We've already seen above how a Unique Scan works. Using the phone book metaphor, it's not hard to understand how a Range Scan works in much the same way to find all people named "Gallileo", or all of the names alphabetically between "Smith" and "Smythe". Range Scans can occur when we use >, <, LIKE, or BETWEEN in a WHERE clause. A range scan will find the first row in the range using the same technique as the Unique Scan, but will then keep reading the index up to the end of the range. It is OK if the range covers many blocks.</div><div style="text-align: justify;"></div><ul><li><span style="text-align: left;"><b>To avoid a table access altogether</b></span></li>
</ul><br />
<div style="text-align: justify;">If all we wanted to do when looking up Gallileo in the phone book was to find his address or phone number, the job would be done. However if we wanted to know his date of birth, we'd have to phone and ask. This takes time. If it was something that we needed all the time, like an email address, we could save time by adding it to the phone book.</div><span style="text-align: justify;">Oracle does the same thing. If the information is in the index, then it doesn't bother to read the table. It is a reasonably common technique to add columns to an index, not because they will be used as part of the index scan, but because they save a table access. In fact, Oracle may even perform a Fast Full Scan of an index that it cannot use in a Range or Unique scan just to avoid a table access.</span><br />
<div style="text-align: justify;"></div><ul><li style="text-align: justify;"><span style="text-align: left;"><b>To avoid a sort</b></span></li>
</ul><br />
<div style="text-align: justify;">This one is not so well known, largely because it is so poorly documented (and in many cases, unpredicatably implemented by the Optimizer as well). Oracle performs a sort for many reasons: ORDER BY, GROUP BY, DISTINCT, Set operations (eg. UNION), Sort-Merge Joins, uncorrelated IN-subqueries, Analytic Functions). If a sort operation requires rows in the same order as the index, then Oracle may read the table rows via the index. A sort operation is not necessary since the rows are returned in sorted order.</div><br />
<div style="text-align: justify;">Despite all of the instances listed above where a sort is performed, I have only seen three cases where a sort is actually avoided.</div><br />
<b>1. <span style="background-color: #ffe599;">GROUP BY : </span></b><br />
<b><span style="background-color: #ffe599;"><br />
</span></b><br />
<b>SQL> </b><span style="background-color: #fff2cc;">select src_sys, sum(actl_expns_amt), count(*) from ef_actl_expns</span><br />
<span style="background-color: #fff2cc;"> where src_sys = 'CDW' and actl_expns_amt > 0 </span><br />
<span style="background-color: #fff2cc;"> group by src_sys ; </span><br />
-----------------------------------------------------------------------------------------<br />
| Id | Operation | Name |<br />
----------------------------------------------------------------------------------------<br />
| 0 | SELECT STATEMENT | |<br />
| 1 | SORT GROUP BY <b style="background-color: #cfe2f3;">NOSORT</b> <span style="color: red;"> <b><-------</b></span> | |<br />
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID | EF_ACTL_EXPNS |<br />
|* 3 | INDEX RANGE SCAN | EF_AEXP_PK |<br />
---------------------------------------------------------------------------------------<br />
<br />
Predicate Information (identified by operation id):<br />
----------------------------------------------------------------<br />
2 - filter("ACTL_EXPNS_AMT">0)<br />
3 - access("SRC_SYS"='CDW')<br />
Note the <b style="background-color: #fff2cc;">NOSORT</b> qualifier in Step 1.<br />
<br />
<b>2. <span style="background-color: #ffe599;">ORDER BY : </span></b><br />
<b><span style="background-color: #ffe599;"><br />
</span></b><br />
SQL> <span style="background-color: #fff2cc;">select * from ef_actl_expns</span><br />
<span style="background-color: #fff2cc;"> where src_sys = 'CDW' and actl_expns_amt > 0</span><br />
<span style="background-color: #fff2cc;"> order by src_sys </span><br />
----------------------------------------------------------------------------------------<br />
| Id | Operation | Name |<br />
----------------------------------------------------------------------------------------<br />
| 0 | SELECT STATEMENT | |<br />
|* 1 | TABLE ACCESS BY GLOBAL INDEX ROWID | EF_ACTL_EXPNS|<br />
|* 2 | INDEX RANGE SCAN | EF_AEXP_PK |<br />
----------------------------------------------------------------------------------------<br />
<br />
Predicate Information (identified by operation id):<br />
---------------------------------------------------<br />
1 - filter("ACTL_EXPNS_AMT">0)<br />
2 - access("SRC_SYS"='CDW')<br />
<br />
<div style="text-align: justify;">Note that there is no <b style="background-color: #fff2cc;">SORT</b> operation, despite the ORDER BY clause. Compare this to the following:</div><br />
SQL> <span style="background-color: #fff2cc;">select * from ef_actl_expns</span><br />
<span style="background-color: #fff2cc;"> where src_sys = 'CDW' and actl_expns_amt > 0</span><br />
<span style="background-color: #fff2cc;"> order by actl_expns_amt ; </span><br />
---------------------------------------------------------------------------------------------<br />
| Id | Operation | Name |<br />
---------------------------------------------------------------------------------------------<br />
| 0 | SELECT STATEMENT | |<br />
| 1 | SORT ORDER BY | |<br />
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID | EF_ACTL_EXPNS |<br />
|* 3 | INDEX RANGE SCAN | EF_AEXP_PK |<br />
----------------------------------------------------------------------------------------<br />
<br />
Predicate Information (identified by operation id):<br />
---------------------------------------------------<br />
2 - filter("ACTL_EXPNS_AMT">0)<br />
3 - access("SRC_SYS"='CDW')<br />
<br />
<b>3. <span style="background-color: #ffe599;">DISTINCT : </span></b><br />
<b><span style="background-color: #ffe599;"><br />
</span></b><br />
SQL> <span style="background-color: #fff2cc;">select distinct src_sys from ef_actl_expns</span><br />
<span style="background-color: #fff2cc;"> where src_sys = 'CDW' and actl_expns_amt > 0 ; </span><br />
-----------------------------------------------------------------------------------------------<br />
| Id | Operation | Name |<br />
-----------------------------------------------------------------------------------------------<br />
| 0 | SELECT STATEMENT | |<br />
| 1 | SORT UNIQUE NOSORT | |<br />
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID | EF_ACTL_EXPNS |<br />
|* 3 | INDEX RANGE SCAN | EF_AEXP_PK |<br />
--------------------------------------------------------------------------------------<br />
<br />
Predicate Information (identified by operation id):<br />
---------------------------------------------------<br />
2 - filter("ACTL_EXPNS_AMT">0)<br />
3 - access("SRC_SYS"='CDW')<br />
<br />
Again, note the <b style="background-color: #fff2cc;">NOSORT</b> qualifier.<br />
<br />
<div style="text-align: justify;">This is an extraordinary tuning technique in OLTP systems like SQL*Forms that return one page of detail at a time to the screen. A SQL with a DISTINCT, GROUP BY, or ORDER BY that uses an index to sort can return just the first page of matching rows without having to fetch the entire result set for a sort. This can be the difference between sub-second response time and several minutes or hours.</div><br />
<b>Full table Scans are not bad : </b><br />
<div style="text-align: justify;">Up to now, we've seen how indexes can be good. It's not always the case; sometimes indexes are no help at all, or worse: they make a query slower.</div><br />
<div style="text-align: justify;">A b-Tree index will be no help at all in a reduced scan unless the WHERE clause compares indexed columns using >, <, LIKE, IN, or BETWEEN operators. A b-Tree index cannot be used to scan for any NOT style operators: eg. !=, NOT IN, NOT LIKE. There are lots of conditions, caveats, and complexities regarding joins, sub-queries, OR predicates, functions (inc. arithmetic and concatenation), and casting that are outside the scope of this article. Consult a good SQL tuning manual.</div><br />
<div style="text-align: justify;">Much more interesting - and important - are the cases where an index makes a SQL slower. These are particularly common in batch systems that process large quantities of data.</div><br />
<div style="text-align: justify;">To explain the problem, we need a new metaphor. Imagine a large deciduous tree in our front yard. It's Autumn, and it's our job to pick up all of the leaves on the lawn. Clearly, the fastest way to do this (without a rake, or a leaf-vac...) would be get down on hands and knees with a bag and work our way back and forth over the lawn, stuffing leaves in the bag as we go. This is a Full Table Scan, selecting rows in no particular order, except that they are nearest to hand. This metaphor works on a couple of levels: we would grab leaves in handfuls, not one by one. A Full Table Scan does the same thing: when a bock is read from disk, Oracle caches the next few blocks with the expectation that it will be asked for them very soon. Type this in SQL*Plus:</div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;">SQL> <span style="background-color: #fff2cc;">show parameter db_file_multiblock_read_count </span></div><div style="text-align: justify;"><span style="background-color: #fff2cc;"><br />
</span></div><div style="text-align: justify;">Just to shake things up a bit (and to feed an undiagnosed obsessive compulsive disorder), we decide to pick up the leaves in order of size. In support of this endeavour, we take a digital photograph of the lawn, write an image analysis program to identify and measure every leaf, then load the results into a Virtual Reality headset that will highlight the smallest leaf left on the lawn. Ingenious, yes; but this is clearly going to take a lot longer than a full table scan because we cover much more distance walking from leaf to leaf.</div><br />
<div style="text-align: justify;">So obviously Full Table Scan is the faster way to pick up every leaf. But just as obvious is that the index (virtual reality headset) is the faster way to pick up just the smallest leaf, or even the 100 smallest leaves. As the number rises, we approach a break-even point; a number beyond which it is faster to just full table scan. This number varies depending on the table, the index, the database settings, the hardware, and the load on the server; generally it is somewhere between 1% and 10% of the table.</div><br />
<b>The main reasons for this are :</b><br />
<br />
<ul style="text-align: left;"><li>As implied above, reading a table in indexed order means more movement for the disk head.</li>
<li>Oracle cannot read single rows. To read a row via an index, the entire block must be read with all but one row discarded. So an index scan of 100 rows would read 100 blocks, but a FTS might read 100 rows in a single block.</li>
<li>The db_file_multiblock_read_count setting described earlier means FTS requires fewer visits to the physical disk.</li>
<li>Even if none of these things was true, accessing the entire index and the entire table is still more IO than just accessing the table.</li>
</ul><br />
<div style="text-align: justify;">So what's the lesson here? Know our data! If our query needs 50% of the rows in the table to resolve our query, an index scan just won't help. Not only should we not bother creating or investigating the existence of an index, we should check to make sure Oracle is not already using an index. There are a number of ways to influence index usage; once again, consult a tuning manual. The exception to this rule - there's always one - is when all of the columns referenced in the SQL are contained in the index. If Oracle does not have to access the table then there is no break-even point; it is generally quicker to scan the index even for 100% of the rows.</div><div style="text-align: justify;"><br />
</div><b>Summary : </b><br />
<div style="text-align: justify;">Indexes are not a dark-art; they work in an entirely predictable and even intuitive way. Understanding how they work moves Performance Tuning from the realm of guesswork to that of science; so embrace the technology and read the manual.</div><div><br />
</div><div>Reference: <a href="http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5010.htm">http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5010.htm</a> </div><div> <a href="http://www.orafaq.com/node/1403">http://www.orafaq.com/node/1403</a></div><div><br />
</div><div><br />
</div><div><b>Enjoy <span style="background-color: yellow;">:-)</span></b></div><div><b><span style="background-color: yellow;"><br />
</span></b></div><div><b><span style="background-color: yellow;"><br />
</span></b></div></div>NEERAJ VISHENhttp://www.blogger.com/profile/04032578930084182369noreply@blogger.com0tag:blogger.com,1999:blog-940885937847335876.post-28989798130430254312012-04-26T10:12:00.001+05:302012-04-26T10:12:24.784+05:30Beginning Performance Tuning<div dir="ltr" style="text-align: left;" trbidi="on"><br />
<div style="text-align: justify;"><b style="background-color: #ffe599;">Performance tunning</b> is one of the biggest responsibilities of a DBA to ensure that the Oracle database is tuned properly. The Oracle RDBMS is highly tunable and allows the database to be monitored and adjusted to increase its performance. Performance tunning is not an easy task. The main issues with tunning for beginners is that from where to start and what should be the right approach . Here is very good presesntation by <b style="background-color: #fff2cc;">Arup Nanda</b> who is having more than 16 years of experience as Oracle DBA . Click the below link to find his presentation .</div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;"><a href="http://www.proligence.com/pres/ioug12/beginning_performance_tuning.pdf"><b>http://www.proligence.com/pres/ioug12/beginning_performance_tuning.pdf</b></a></div><br />
<br />
<b>Enjoy <span style="background-color: yellow;"> :-) </span></b><br />
<br />
</div>NEERAJ VISHENhttp://www.blogger.com/profile/04032578930084182369noreply@blogger.com0tag:blogger.com,1999:blog-940885937847335876.post-62477574393140207642012-04-23T14:22:00.000+05:302012-04-23T14:22:43.175+05:30How to Identify the Static and Dynamic Parameter in Oracle<div dir="ltr" style="text-align: left;" trbidi="on"><br />
<div style="text-align: justify;">Sometimes, we may not very sure whether an oracle parameter is <b style="background-color: white;">static</b>(restarting database is required to come under the action) parameter or <b style="background-color: white;">dynamic</b>(can be changed without restarting) parameter . We can check this by using the <span style="background-color: #fff2cc;">v$parameter2</span> view which is very similar to v$parameter having few extra rows for long parameters . The another difference between the v$parameter and v$parameter2 is that the format of the output .. For example, if a parameter value say "x,y" in V$PARAMETER view does not tell us if the parameter has two values ("x" and "y") or one value ("x, y") whereas V$PARAMETER2 makes the distinction between the list parameter values clear.</div><div style="text-align: justify;"><br />
</div>SQL> <span style="background-color: #fff2cc;">select value from v$parameter WHERE name LIKE 'control_files' ; </span><br />
<a href="http://1.bp.blogspot.com/-hEfPM7pXP5Q/T5UV8SuaIuI/AAAAAAAAAVw/t4oL-CJTJ58/s1600/p1.bmp" imageanchor="1" style="clear: left; display: inline !important; margin-bottom: 1em; margin-right: 1em; text-align: center;"><img border="0" height="27" src="http://1.bp.blogspot.com/-hEfPM7pXP5Q/T5UV8SuaIuI/AAAAAAAAAVw/t4oL-CJTJ58/s640/p1.bmp" width="640" /></a><br />
<br />
SQL><span style="background-color: #fff2cc;"> select value from v$parameter2 WHERE name LIKE 'control_files' ; </span><br />
<a href="http://1.bp.blogspot.com/-DuEYrFWsi1k/T5UWJ-rZWFI/AAAAAAAAAV4/2ekamcI6M8Q/s1600/p2.bmp" imageanchor="1" style="clear: left; display: inline !important; margin-bottom: 1em; margin-right: 1em; text-align: center;"><img border="0" src="http://1.bp.blogspot.com/-DuEYrFWsi1k/T5UWJ-rZWFI/AAAAAAAAAV4/2ekamcI6M8Q/s1600/p2.bmp" /></a><br />
<br />
<span style="background-color: white;"><span style="text-align: justify;">Here, If </span><span style="text-align: justify;">ISSES_MODIFIABLE </span><span style="text-align: justify;"> parameter is true, the parameter can be changed on </span><span style="text-align: justify;"><b>session level</b></span><span style="text-align: justify;"> , and if </span><span style="text-align: justify;">ISSES_MODIFIABLE</span><span style="text-align: justify;"> or </span><span style="text-align: justify;">ISINSTANCE_MODIFIABLE</span><span style="text-align: justify;"> is true, then parameter can be changed on </span><b style="text-align: justify;">system level</b><span style="text-align: justify;">. Here is an example</span></span><br />
<br />
<div style="text-align: justify;">SQL> <span style="background-color: #fff2cc;">SELECT name,Value ,ISSES_MODIFIABLE , ISINSTANCE_MODIFIABLE FROM v$parameter2 WHERE name LIKE '%target%' ; </span></div><div style="text-align: justify;"><span style="background-color: #fff2cc;"><br />
</span></div><div class="separator" style="clear: both; text-align: center;"><a href="http://1.bp.blogspot.com/-zEdExYZ0lK8/T5UWplJJW0I/AAAAAAAAAWA/nPSrfdNOrtE/s1600/spfile.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/-zEdExYZ0lK8/T5UWplJJW0I/AAAAAAAAAWA/nPSrfdNOrtE/s1600/spfile.JPG" /></a></div><div style="text-align: justify;"><span style="background-color: #fff2cc;"><br />
</span></div><div style="text-align: justify;"><span style="background-color: #fff2cc;"><br />
</span></div><div style="text-align: justify;"><span style="background-color: #fff2cc;"><br />
</span></div><div style="text-align: justify;"><span style="background-color: #fff2cc;"><br />
</span></div><div style="text-align: justify;"><span style="background-color: #fff2cc;"><br />
</span></div><div style="text-align: justify;"><span style="background-color: #fff2cc;"><br />
</span></div><div style="text-align: justify;"><span style="background-color: #fff2cc;"><br />
</span></div><div style="text-align: justify;"><span style="background-color: #fff2cc;"><br />
</span></div><div style="text-align: justify;"><span style="background-color: #fff2cc;"><br />
</span></div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;"><span style="background-color: #fff2cc;"><br />
</span></div><div style="text-align: justify;"><b><span style="background-color: white;">Enjoy </span><span style="background-color: yellow;"> :-)</span></b></div><div style="text-align: justify;"><b><span style="background-color: yellow;"><br />
</span></b></div><div style="text-align: justify;"><b><span style="background-color: yellow;"><br />
</span></b></div><div style="text-align: justify;"><b><br />
</b></div></div>NEERAJ VISHENhttp://www.blogger.com/profile/04032578930084182369noreply@blogger.com1tag:blogger.com,1999:blog-940885937847335876.post-48586334287130536292012-04-13T18:19:00.000+05:302012-04-13T18:19:28.767+05:30Oracle(OUI) Silent Mode Installation on Linux<div dir="ltr" style="text-align: left;" trbidi="on"><br />
<div style="text-align: justify;"><span style="background-color: #ffe599;"><b>OUI</b> (<b>Oracle Universal Installer</b>)</span> is a program used to install Oracle software and database options.We generally use the OUI in GUI mode to install the Oracle software . Sometimes, it may required to install the oracle software in silent mode that is without invoking the OUI . This can be done by using the reponse file . </div><div style="text-align: justify;">An Oracle database response file specifies parameters for configuring an Oracle event database.The response file can be created by running the Oracle universal installer (OUI) and records the steps and stages in the response file or we can found the demo response file in oracle software in "<b>response</b>" directory .We can create the response file by invoking the below command </div><span style="background-color: white;">[oracle@Ramtek ~]$</span><span style="background-color: #fff2cc;"> ./runInstaller -record -destinationFile /tmp/db_silent.rsp </span><br />
<br />
<div style="text-align: justify;">The "<span style="background-color: #fff2cc;"><b>-record</b></span>" parameter tells the installer to write to the response file and the "<span style="background-color: #fff2cc;"><b>-destinationFile</b></span>" parameter defines the name and location of the response file.The process is similar to using Kickstart for Linux installations .<a href="http://neeraj-dba.blogspot.in/2011/04/oracle-10g-installation-on-redhat-51.html" style="background-color: #fff2cc;" target="_blank">We can prepare the oracle environment from here </a>. I have editted by response file and highlighted(bold with blue shade) the modified value. Below is response file.</div><br />
<span style="background-color: #fff2cc;">RESPONSEFILE_VERSION=2.2.1.0.0</span><br />
<span style="background-color: #fff2cc;">UNIX_GROUP_NAME="dba"</span><br />
<span style="background-color: #fff2cc;">FROM_LOCATION=</span><b style="background-color: #cfe2f3;">"/home/oracle/database/stage/products.xml"</b><br />
<span style="background-color: #fff2cc;">FROM_LOCATION_CD_LABEL=<Value Unspecified></span><br />
<span style="background-color: #fff2cc;">ORACLE_HOME=</span><span style="background-color: #cfe2f3;"><b>"/home/oracle/product/10.2.0/db_1"</b></span><br />
<span style="background-color: #fff2cc;">ORACLE_HOME_NAME=</span><b style="background-color: #cfe2f3;">"OraDb10g_home1"</b><br />
<span style="background-color: #fff2cc;">SHOW_WELCOME_PAGE=true</span><br />
<span style="background-color: #fff2cc;">SHOW_CUSTOM_TREE_PAGE=true</span><br />
<span style="background-color: #fff2cc;">SHOW_COMPONENT_LOCATIONS_PAGE=true</span><br />
<span style="background-color: #fff2cc;">SHOW_SUMMARY_PAGE=true</span><br />
<span style="background-color: #fff2cc;">SHOW_INSTALL_PROGRESS_PAGE=true</span><br />
<span style="background-color: #fff2cc;">SHOW_REQUIRED_CONFIG_TOOL_PAGE=true</span><br />
<span style="background-color: #fff2cc;">SHOW_CONFIG_TOOL_PAGE=true</span><br />
<span style="background-color: #fff2cc;">SHOW_RELEASE_NOTES=true</span><br />
<span style="background-color: #fff2cc;">SHOW_ROOTSH_CONFIRMATION=true</span><br />
<span style="background-color: #fff2cc;">SHOW_END_SESSION_PAGE=true</span><br />
<span style="background-color: #fff2cc;">SHOW_EXIT_CONFIRMATION=true</span><br />
<span style="background-color: #fff2cc;">NEXT_SESSION=false</span><br />
<span style="background-color: #fff2cc;">NEXT_SESSION_ON_FAIL=true</span><br />
<span style="background-color: #fff2cc;">NEXT_SESSION_RESPONSE=<Value Unspecified></span><br />
<span style="background-color: #fff2cc;">DEINSTALL_LIST={"oracle.server","10.2.0.1.0"}</span><br />
<span style="background-color: #fff2cc;">SHOW_DEINSTALL_CONFIRMATION=true</span><br />
<span style="background-color: #fff2cc;">SHOW_DEINSTALL_PROGRESS=true</span><br />
<span style="background-color: #fff2cc;">CLUSTER_NODES={}</span><br />
<span style="background-color: #fff2cc;">ACCEPT_LICENSE_AGREEMENT=true</span><br />
<span style="background-color: #fff2cc;">TOPLEVEL_COMPONENT={"oracle.server","10.2.0.1.0"}</span><br />
<span style="background-color: #fff2cc;">SHOW_SPLASH_SCREEN=false</span><br />
<span style="background-color: #fff2cc;">SELECTED_LANGUAGES={"en"}</span><br />
<span style="background-color: #fff2cc;">COMPONENT_LANGUAGES={"en"}</span><br />
<span style="background-color: #fff2cc;">INSTALL_TYPE="Custom"</span><br />
<span style="background-color: #fff2cc;">oracle.server:DEPENDENCY_LIST={"oracle.rdbms:10.2.0.1.0","oracle.options:10.2.0.1.0","oracle.network:10.2.0.1.0","oracle.sysman.console.db:10.2.0.1.0","oracle.rdbms.oci:10.2.0.1.0"}</span><br />
<span style="background-color: #fff2cc;">oracle.network:DEPENDENCY_LIST={"oracle.network.listener:10.2.0.1.0"}</span><br />
<span style="background-color: #fff2cc;">oracle.options:DEPENDENCY_LIST={"oracle.rdbms.partitioning:10.2.0.1.0"}</span><br />
<span style="background-color: #fff2cc;">sl_superAdminPasswds=<Value Unspecified></span><br />
<span style="background-color: #fff2cc;">sl_dlgASMCfgSelectableDisks={}</span><br />
<span style="background-color: #fff2cc;">s_superAdminSamePasswd=<Value Unspecified></span><br />
<span style="background-color: #fff2cc;">s_globalDBName="orcl"</span><br />
<span style="background-color: #fff2cc;">s_dlgASMCfgRedundancyValue="2 (Norm)"</span><br />
<span style="background-color: #fff2cc;">s_dlgASMCfgNewDisksSize="0"</span><br />
<span style="background-color: #fff2cc;">s_dlgASMCfgExistingFreeSpace="0"</span><br />
<span style="background-color: #fff2cc;">s_dlgASMCfgDiskGroupName="DATA"</span><br />
<span style="background-color: #fff2cc;">s_dlgASMCfgDiskDiscoveryString=""</span><br />
<span style="background-color: #fff2cc;">s_dlgASMCfgAdditionalSpaceNeeded=" MB"</span><br />
<span style="background-color: #fff2cc;">s_dbSelectedUsesASM=""</span><br />
<span style="background-color: #fff2cc;">s_dbSIDSelectedForUpgrade=""</span><br />
<span style="background-color: #fff2cc;">s_dbRetChar=""</span><br />
<span style="background-color: #fff2cc;">s_dbOHSelectedForUpgrade=""</span><br />
<span style="background-color: #fff2cc;">s_ASMSYSPassword=<Value Unspecified></span><br />
<span style="background-color: #fff2cc;">n_performUpgrade=0</span><br />
<span style="background-color: #fff2cc;">n_dlgASMCfgRedundancySelected=2</span><br />
<span style="background-color: #fff2cc;">n_dbType=1</span><br />
<span style="background-color: #fff2cc;">n_dbSelection=0</span><br />
<span style="background-color: #fff2cc;">b_useSamePassword=false</span><br />
<span style="background-color: #fff2cc;">b_useFileSystemForRecovery=true</span><br />
<span style="background-color: #fff2cc;">b_receiveEmailNotification=false</span><br />
<span style="background-color: #fff2cc;">b_loadExampleSchemas=false</span><br />
<span style="background-color: #fff2cc;">b_enableAutoBackup=false</span><br />
<span style="background-color: #fff2cc;">b_dlgASMShowCandidateDisks=true</span><br />
<span style="background-color: #fff2cc;">b_centrallyManageASMInstance=true</span><br />
<span style="background-color: #fff2cc;">sl_dlgASMDskGrpSelectedGroup={" "," "," "," "}</span><br />
<span style="background-color: #fff2cc;">s_dlgRBOUsername=""</span><br />
<span style="background-color: #fff2cc;">s_dlgEMCentralAgentSelected="No Agents Found"</span><br />
<span style="background-color: #fff2cc;">b_useDBControl=true</span><br />
<span style="background-color: #fff2cc;">s_superAdminSamePasswdAgain=<Value Unspecified></span><br />
<span style="background-color: #fff2cc;">s_dlgEMSMTPServer=""</span><br />
<span style="background-color: #fff2cc;">s_dlgEMEmailAddress=""</span><br />
<span style="background-color: #fff2cc;">s_dlgRBORecoveryLocation="/home/oracle/product/10.2.0/flash_recovery_area"</span><br />
<span style="background-color: #fff2cc;">n_upgradeDB=1</span><br />
<span style="background-color: #fff2cc;">n_configurationOption=1</span><br />
<span style="background-color: #fff2cc;">sl_upgradableSIDBInstances={}</span><br />
<span style="background-color: #fff2cc;">n_upgradeASM=0</span><br />
<span style="background-color: #fff2cc;">sl_dlgASMCfgDiskSelections={}</span><br />
<span style="background-color: #fff2cc;">s_ASMSYSPasswordAgain=<Value Unspecified></span><br />
<span style="background-color: #fff2cc;">n_dbStorageType=0</span><br />
<span style="background-color: #fff2cc;">s_rawDeviceMapFileLocation=""</span><br />
<span style="background-color: #fff2cc;">sl_upgradableRACDBInstances={}</span><br />
<span style="background-color: #fff2cc;">s_dlgRBOPassword=<Value Unspecified></span><br />
<span style="background-color: #fff2cc;">b_stateOfUpgradeDBCheckbox=false</span><br />
<span style="background-color: #fff2cc;">s_dbSid=</span><b style="background-color: #cfe2f3;">"orcl"</b><br />
<span style="background-color: #fff2cc;">b_dbSelectedUsesASM=false</span><br />
<span style="background-color: #fff2cc;">sl_superAdminPasswdsAgain=<Value Unspecified></span><br />
<span style="background-color: #fff2cc;">s_mountPoint=</span><b style="background-color: #cfe2f3;">"/home/oracle/product/10.2.0/oradata"</b><br />
<span style="background-color: #fff2cc;">b_stateOfUpgradeASMCheckbox=false</span><br />
<span style="background-color: #fff2cc;">oracle.assistants.server:OPTIONAL_CONFIG_TOOLS="{}"</span><br />
<span style="background-color: #fff2cc;">oracle.has.common:OPTIONAL_CONFIG_TOOLS="{}"</span><br />
<span style="background-color: #fff2cc;">oracle.network.client:OPTIONAL_CONFIG_TOOLS="{}"</span><br />
<span style="background-color: #fff2cc;">oracle.sqlplus.isqlplus:OPTIONAL_CONFIG_TOOLS="{}"</span><br />
<span style="background-color: #fff2cc;">oracle.sysman.console.db:OPTIONAL_CONFIG_TOOLS="{}"</span><br />
<span style="background-color: #fff2cc;">varSelect=3</span><br />
<span style="background-color: #fff2cc;">s_nameForOPERGrp="dba"</span><br />
<span style="background-color: #fff2cc;">s_nameForDBAGrp="dba"</span><br />
<br />
A silent installation is initiated using the following command.<br />
<br />
[root@Ramtek ~]# <span style="background-color: #fff2cc;">su - oracle</span><br />
[oracle@Ramtek ~]$ <span style="background-color: #fff2cc;">cd /home/oracle/database</span><br />
[oracle@Ramtek database]$ <span style="background-color: #fff2cc;">./runInstaller -silent -force -ignoreSysPrereqs -responseFile /tmp/db.rsp</span><br />
Starting Oracle Universal Installer...<br />
Checking installer requirements...<br />
Checking operating system version: must be redhat-3, SuSE-9, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2<br />
Passed<br />
All installer requirements met.<br />
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2012-04-12_11-25-29AM. Please wait ...[oracle@Ramtek database]$ Oracle Universal Installer, Version 10.2.0.1.0 Production<br />
Copyright (C) 1999, 2005, Oracle. All rights reserved.<br />
You can find a log of this install session at:<br />
/home/oracle/oraInventory/logs/installActions2012-04-12_11-25-29AM.log<br />
.................................................................................................... 100% Done.<br />
Loading Product Information<br />
................................................................................................................... 100% Done.<br />
Analyzing dependencies<br />
........................................................................<br />
Starting execution of Prerequisites...<br />
Total No of checks: 9<br />
Performing check for CertifiedVersions<br />
Checking operating system requirements ...<br />
Expected result: One of redhat-3,redhat-4,SuSE-9,asianux-1,asianux-2<br />
Actual Result: redhat-4<br />
Check complete. The overall result of this check is: Passed<br />
Check complete: Passed<br />
=======================================================================<br />
Performing check for Packages<br />
Checking operating system package requirements ...<br />
Checking for make-3.79; found make-1:3.81-3.el5. Passed<br />
Checking for binutils-2.14; found binutils-2.17.50.0.6-9.el5. Passed<br />
Checking for gcc-3.2; found Not found. Failed <<<<<br />
Checking for libaio-0.3.96; found libaio-0.3.106-3.2. Passed<br />
Check complete. The overall result of this check is: Failed <<<<<br />
<br />
Check complete: Failed <<<<<br />
Problem: Some packages required for the Oracle Database 10g to function properly are missing (see above).<br />
Recommendation: Install the required packages before continuing with the installation.<br />
=======================================================================<br />
Performing check for Security<br />
Checking security kernel parameters<br />
Checking for semmsl=250; found semmsl=250. Passed<br />
Checking for semmns=32000; found semmns=32000. Passed<br />
Checking for semopm=100; found semopm=100. Passed<br />
Checking for semmni=128; found semmni=128. Passed<br />
Checking for shmmax=536870912; found shmmax=4294967295. Passed<br />
Checking for shmmni=4096; found shmmni=4096. Passed<br />
Checking for shmall=2097152; found shmall=268435456. Passed<br />
Checking for file-max=65536; found file-max=65536. Passed<br />
Checking for VERSION=2.6.9; found VERSION=2.6.18-128.el5. Passed<br />
Checking for ip_local_port_range=1024 - 65000; found ip_local_port_range=1024 - 65000. Passed<br />
Checking for rmem_default=262144; found rmem_default=262144. Passed<br />
Checking for rmem_max=262144; found rmem_max=262144. Passed<br />
Checking for wmem_default=262144; found wmem_default=262144. Passed<br />
Checking for wmem_max=262144; found wmem_max=262144. Passed<br />
Check complete. The overall result of this check is: Passed<br />
Check complete: Passed<br />
=======================================================================<br />
Performing check for GLIBC<br />
Checking Recommended glibc version<br />
Expected result: ATLEAST=2.3.2-95.27<br />
Actual Result: 2.5-34<br />
Check complete. The overall result of this check is: Passed<br />
Check complete: Passed<br />
=======================================================================<br />
Performing check for TotalMemory<br />
Checking physical memory requirements ...<br />
Expected result: 922MB<br />
Actual Result: 1008MB<br />
Check complete. The overall result of this check is: Passed<br />
Check complete: Passed<br />
=======================================================================<br />
Performing check for SwapSpace<br />
Checking available swap space requirements ...<br />
Expected result: 1512MB<br />
Actual Result: 2047MB<br />
Check complete. The overall result of this check is: Passed<br />
Check complete: Passed<br />
=======================================================================<br />
Performing check for OracleBase<br />
Validating ORACLE_BASE location (if set) ...<br />
Check complete. The overall result of this check is: Passed<br />
Check complete: Passed<br />
=======================================================================<br />
Performing check for DetectAnyInvalidASMHome<br />
Checking for proper system clean-up....<br />
Check complete. The overall result of this check is: Passed<br />
Check complete: Passed<br />
=======================================================================<br />
Performing check for CompatibilityChecks_Custom<br />
Checking for Oracle Home incompatibilities ....<br />
Actual Result: NEW_HOME<br />
Check complete. The overall result of this check is: Passed<br />
Check complete: Passed<br />
=======================================================================<br />
PrereqChecks complete<br />
........................................... 100% Done.<br />
-----------------------------------------------------------------------------<br />
Summary<br />
Global Settings<br />
Source: /home/oracle/database/stage/products.xml<br />
Oracle Home: /home/oracle/product/10.2.0/db_1 (OraDb10g_home1)<br />
Installation Type: Custom<br />
Product Languages<br />
English<br />
Space Requirements<br />
/home/ Required 1.21GB : Available 8.40GB<br />
/ Required 108MB (only as temporary space) : Available 1004MB<br />
New Installations (100 products)<br />
Oracle Database 10g 10.2.0.1.0<br />
Enterprise Edition Options 10.2.0.1.0<br />
Oracle Partitioning 10.2.0.1.0<br />
Oracle Enterprise Manager Console DB 10.2.0.1.0<br />
Oracle Net Services 10.2.0.1.0<br />
Oracle Database 10g 10.2.0.1.0<br />
Oracle Net Listener 10.2.0.1.0<br />
HAS Files for DB 10.2.0.1.0<br />
Oracle Internet Directory Client 10.2.0.1.0<br />
Oracle Call Interface (OCI) 10.2.0.1.0<br />
Oracle interMedia 10.2.0.1.0<br />
Enterprise Manager Agent Core 10.2.0.1.0<br />
Oracle JVM 10.2.0.1.0<br />
Database Configuration and Upgrade Assistants 10.2.0.1.0<br />
Oracle interMedia Locator 10.2.0.1.0<br />
Oracle XML Development Kit 10.2.0.1.0<br />
Oracle Text 10.2.0.1.0<br />
Oracle Database Utilities 10.2.0.1.0<br />
Generic Connectivity Common Files 10.2.0.1.0<br />
Oracle Advanced Security 10.2.0.1.0<br />
Enterprise Manager Repository Core 10.2.0.1.0<br />
PL/SQL 10.2.0.1.0<br />
Oracle Net 10.2.0.1.0<br />
Assistant Common Files 10.2.0.1.0<br />
Enterprise Manager plugin Common Files 10.2.0.1.0 Beta<br />
Buildtools Common Files 10.2.0.1.0<br />
Installation Common Files 10.2.0.1.0<br />
Oracle LDAP administration 10.2.0.1.0<br />
Oracle Java Client 10.2.0.1.0<br />
Precompiler Common Files 10.2.0.1.0<br />
Oracle Recovery Manager 10.2.0.1.0<br />
SQL*Plus 10.2.0.1.0<br />
Enterprise Manager plugin Common Files 10.2.0.1.0<br />
HAS Common Files 10.2.0.1.0<br />
Oracle Clusterware RDBMS Files 10.2.0.1.0<br />
Oracle Wallet Manager 10.2.0.1.0<br />
Enterprise Manager Minimal Integration 10.2.0.1.0<br />
Oracle Database User Interface 2.2.13.0.0<br />
Secure Socket Layer 10.2.0.1.0<br />
Required Support Files 10.2.0.1.0<br />
Database SQL Scripts 10.2.0.1.0<br />
OLAP SQL Scripts 10.2.0.1.0<br />
PL/SQL Embedded Gateway 10.2.0.1.0<br />
Oracle Globalization Support 10.2.0.1.0<br />
Character Set Migration Utility 10.2.0.1.0<br />
LDAP Required Support Files 10.2.0.1.0<br />
Oracle Help for the Web 1.1.10.0.0<br />
Oracle JDBC Thin Driver for JDK 1.4 10.2.0.1.0<br />
Oracle JDBC Thin Driver for JDK 1.2 10.2.0.1.0<br />
Oracle interMedia Client Option 10.2.0.1.0<br />
Oracle Notification Service 10.1.0.3.0<br />
Oracle Code Editor 1.2.1.0.0I<br />
Perl Interpreter 5.8.3.0.2<br />
JDBC Common Files 10.2.0.1.0<br />
Oracle Locale Builder 10.2.0.1.0<br />
Oracle Containers for Java 10.2.0.1.0<br />
Database Workspace Manager 10.2.0.1.0<br />
Oracle Core Required Support Files 10.2.0.1.0<br />
Platform Required Support Files 10.2.0.1.0<br />
Oracle interMedia Locator RDBMS Files 10.2.0.1.0<br />
Oracle JDBC/OCI Instant Client 10.2.0.1.0<br />
Oracle interMedia Annotator 10.2.0.1.0<br />
SQLJ Runtime 10.2.0.1.0<br />
Oracle interMedia Java Advanced Imaging 10.2.0.1.0<br />
Oracle Database 10g interMedia Files 10.2.0.1.0<br />
Oracle Data Mining RDBMS Files 10.2.0.1.0<br />
Enterprise Manager Baseline 10.2.0.1.0<br />
Oracle Help For Java 4.2.6.1.0<br />
Oracle UIX 2.1.22.0.0<br />
XML Parser for Java 10.2.0.1.0<br />
Precompiler Required Support Files 10.2.0.1.0<br />
XML Parser for Oracle JVM 10.2.0.1.0<br />
Oracle Message Gateway Common Files 10.2.0.1.0<br />
Oracle Starter Database 10.2.0.1.0<br />
Sample Schema Data 10.2.0.1.0<br />
Parser Generator Required Support Files 10.2.0.1.0<br />
Agent Required Support Files 10.2.0.1.0<br />
Oracle RAC Required Support Files-HAS 10.2.0.1.0<br />
RDBMS Required Support Files 10.2.0.1.0<br />
RDBMS Required Support Files for Instant Client 10.2.0.1.0<br />
XDK Required Support Files 10.2.0.1.0<br />
DBJAVA Required Support Files 10.2.0.1.0<br />
SQL*Plus Required Support Files 10.2.0.1.0<br />
Oracle JFC Extended Windowing Toolkit 4.2.33.0.0<br />
Oracle Ice Browser 5.2.3.6.0<br />
Oracle Display Fonts 9.0.2.0.0<br />
Oracle Extended Windowing Toolkit 3.4.38.0.0<br />
Enterprise Manager Common Files 10.2.0.1.0<br />
Enterprise Manager Agent DB 10.2.0.1.0<br />
Oracle Net Required Support Files 10.2.0.1.0<br />
Enterprise Manager Repository DB 10.2.0.1.0<br />
SSL Required Support Files for InstantClient 10.2.0.1.0<br />
regexp 2.1.9.0.0<br />
Bali Share 1.1.18.0.0<br />
Oracle Universal Installer 10.2.0.1.0<br />
Oracle One-Off Patch Installer 10.2.0.1.0<br />
Installer SDK Component 10.2.0.1.0<br />
Java Runtime Environment 1.4.2.8.0<br />
Sun JDK 1.4.2.0.8<br />
Sun JDK extensions 10.1.2.0.0<br />
-----------------------------------------------------------------------------<br />
Installation in progress (Thu Apr 12 11:26:13 PDT 2012)<br />
.................................................... .......... 19% Done.<br />
............................................................... 38% Done.<br />
............................................................... 57% Done.<br />
............................................................. 75% Done.<br />
<b style="background-color: #fff2cc;">Install successful</b><br />
Linking in progress (Thu Apr 12 11:43:57 PDT 2012)<br />
............................................... 75% Done.<br />
Link successful<br />
Setup in progress (Thu Apr 12 11:44:36 PDT 2012)<br />
.............. 100% Done.<br />
Setup successful<br />
<span style="background-color: #fff2cc;">End of install phases</span>.(Thu Apr 12 11:45:01 PDT 2012)<br />
WARNING:A new inventory has been created in this session. However, it has not yet been registered as the central inventory of this system.<br />
To register the new inventory please run the script '/home/oracle/oraInventory/orainstRoot.sh' with root privileges.<br />
If you do not register the inventory, you may not be able to update or patch the products you installed.<br />
The following configuration scripts<br />
<span style="background-color: #fff2cc;">/home/oracle/product/10.2.0/db_1/root.sh</span><br />
need to be executed as root for configuring the system. If you skip the execution of the configuration tools, the configuration will not be complete and the product wont function properly. In order to get the product to function properly, you will be required to execute the scripts and the configuration tools after exiting the OUI.<br />
<br />
The installation of Oracle Database 10g was successful.<br />
Please check '/home/oracle/oraInventory/logs/silentInstall2012-04-12_11-25-29AM.log' for more details.<br />
<br />
[oracle@Ramtek database]$ <span style="background-color: #fff2cc;">su - </span><br />
Password:<br />
[root@Ramtek ~]# <span style="background-color: #fff2cc;">/home/oracle/oraInventory/orainstRoot.sh</span><br />
Changing permissions of /home/oracle/oraInventory to 770.<br />
Changing groupname of /home/oracle/oraInventory to dba.<br />
The execution of the script is complete<br />
<br />
For preparing environment and database installation <b><a href="http://neeraj-dba.blogspot.in/2011/04/oracle-10g-installation-on-redhat-51.html" target="_blank">click here</a></b><br />
<br />
<br />
<b>Enjoy <span style="background-color: yellow;"> :-)</span> </b><br />
<b><br />
</b><br />
<b><br />
</b><br />
<b><br />
</b></div>NEERAJ VISHENhttp://www.blogger.com/profile/04032578930084182369noreply@blogger.com0tag:blogger.com,1999:blog-940885937847335876.post-44963684552826160552012-04-11T18:51:00.000+05:302012-09-22T10:46:47.641+05:30What is redo log thread in oracle ?<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div style="text-align: justify;">
On googling about the <b style="background-color: #ffe599;">redo log thread</b>, i have not found proper documentation that clearly explains clearly what the redo log thread is . Here i am trying to cover the redo log threads in case of single instance and RAC taking reference from ASKTOM site .</div>
<br />
<div style="text-align: justify;">
Each instance has it's own personal set of redo and each redo thread is made up of at least two groups that have one or more members (files) .Two instances will never write to the same redo files - each instance has it's own set of redo logs to write to . Another instance may well READ some other instances redo logs - after that other instance fails for example - to perform recovery. Here is a scenario which helps us to understand the thread concepts .</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Most V$ views work by selecting information from the corresponding GV$ view with a predicate "where instance_id = <that instance>". So V$SESSION in single Instance(i.e, 1) is actually </div>
SQL><span style="background-color: #fff2cc;">select * from gv$instance where inst_id= 1 ;</span><br />
<br />
<div style="text-align: justify;">
On a three node RAC database, if we select from v$session, we get sessions from that instance only. Selecting from GV$SESSION creates parallel query slaves on the other instances and gets the information back to our session. </div>
<br />
<div style="text-align: justify;">
This works fine in almost all cases. There are few exceptions: in case of redo logs, the RAC instance must see all the redo logs of other instances as they become important for its recovery. Therefore, V$LOG actually shows all the redo logs, of all the instances, not just of its own. Contrast this with V$SESSION, which shows only sessions of that instance, not all. So, if there are 3 log file groups per instance (actually, per "thread") and there are 3 instances, V$LOG on any instance will show all 9 logfile groups, not 3. </div>
<br />
<div style="text-align: justify;">
When we select form GV$LOG, remember, the session gets the information from other instances as well. Unfortunately, the PQ servers on those instances also get 9 records each, since they also see the same information seen by the first instance. On a three instance RAC, we will get 3X9 = 27 records in GV$LOG! </div>
<div style="text-align: justify;">
To avoid this: </div>
<div style="text-align: justify;">
<b>1.) </b>Always select from V$LOG, V$LOGFILE and V$THREAD in a RAC instance. GV$ views are misleading or<span style="text-align: left;"> </span></div>
<div style="text-align: justify;">
<b>2.) </b> Add a predicate to match THREAD# with INST_ID. (Beware: thread numbers are by default the same as the instance_id; but we may have defined a different thread number while creating the database) as </div>
SQL> <span style="background-color: #fff2cc;">select * from gv$log where inst_log=thread# ; </span><br />
<b><br /></b>
<b>Ref : </b><a href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:18183400346178753">http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:18183400346178753</a><br />
<br />
<br />
<br />
Enjoy <b style="background-color: yellow;">:-) </b><br />
<br />
<br />
<br /></div>
NEERAJ VISHENhttp://www.blogger.com/profile/04032578930084182369noreply@blogger.com0tag:blogger.com,1999:blog-940885937847335876.post-18157573463697913502012-04-03T19:16:00.002+05:302012-04-03T20:20:13.504+05:30User Managed Hot Backups in Oracle<div dir="ltr" style="text-align: left;" trbidi="on"><span style="font-family: inherit; text-align: justify;">A cold backup does have the somewhat bad side effect of wiping out our shared pool, our buffer cache and preventing our users from logging in to do work. Our database is like a car, it runs better when it is warmed up. If we want to cold start it - be prepared for rough running when we restart as we have to rebuild that shared pool, that buffer cache and so on . I would never pick cold over hot given the chance. No benefit, only downsides (Acc. to Tkye). The only kind of backup we do on our production systems here is hot .</span><br />
<div style="text-align: justify;"><span style="font-family: inherit; text-align: left;"><br />
</span></div><div style="text-align: justify;"><span style="font-family: inherit; text-align: left;">There are two ways to perform Oracle backup and recovery : </span></div><br />
<div style="text-align: justify;"><span style="font-family: inherit;"><b>1.) <span style="background-color: #ffe599;">Recovery Manager (RMAN) :</span></b> It is an Oracle utility that can backup, restore, and recover database files. It is a feature of the Oracle database server and does not require separate installation.</span></div><div style="text-align: justify;"><span style="font-family: inherit;"><b>2.) <span style="background-color: #ffe599;">User-Managed backup and recovery :</span> </b>We use operating system commands for backups and SQL*Plus for recovery. This method is called user-managed backup and recovery and is fully supported by Oracle, although use of RMAN is highly recommended because it is more robust and greatly simplifies administration.</span></div><div style="text-align: justify;"><span style="font-family: inherit;"><br />
</span></div><span style="font-family: inherit;">There are basically <b>two types of backup</b> .The backup are as </span><br />
<span style="font-family: inherit;"><br />
</span><br />
<div style="text-align: justify;"><span style="font-family: inherit;"><b>1.) <span style="background-color: #ffe599;">Consistent Backup :</span></b> This is also know as <b><i>Cold Backup</i></b> . A consistent backup is one in which the files being backed up contain all changes up to the same system change number (SCN). This means that the files in the backup contain all the data taken from a same point in time .</span></div><div style="text-align: justify;"><span style="font-family: inherit;"><b>2.) <span style="background-color: #ffe599;">Inconsistent Backup :</span></b> This is also known as <b><i>Hot backup </i></b>. An inconsistent backup is a backup in which the files being backed up do not contain all the changes made at all the SCNs . This can occur because the datafiles are being modified as backups are being taken. </span></div><div style="text-align: justify;"><span style="font-family: inherit;"><br />
</span></div><div style="text-align: justify;"><span style="font-family: inherit;">There are some DBAs which prefer oracle user-managed backups.They put their database into backup mode prior to backing up and take it out of backup mode after backup. If we 're going to perform user-managed backups, we must back up all of the following file : </span></div><div style="text-align: justify;"></div><ul><li><span style="text-align: left;"><span style="font-family: inherit;">Datafiles</span></span></li>
<li><span style="text-align: left;"><span style="font-family: inherit;">Control files</span></span></li>
<li><span style="text-align: left;"><span style="font-family: inherit;">Online redo logs (if performing a cold backup)</span></span></li>
<li><span style="text-align: left;"><span style="font-family: inherit;">The parameter file (not mandatory )</span></span></li>
<li><span style="text-align: left;"><span style="font-family: inherit;">Archived redo logs</span></span></li>
<li><span style="text-align: left;"><span style="font-family: inherit;">Password file if used</span></span></li>
</ul><br />
<span style="font-family: inherit;">The below diagram shows the Whole Database Backup Options : </span><br />
<div class="separator" style="clear: both; text-align: center;"><span style="font-family: inherit; margin-left: 1em; margin-right: 1em;"><a href="http://1.bp.blogspot.com/-bp4CfNS6vak/T3rztOGBNoI/AAAAAAAAAVE/5Q-IvGWq-KY/s1600/bb11.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="153" src="http://1.bp.blogspot.com/-bp4CfNS6vak/T3rztOGBNoI/AAAAAAAAAVE/5Q-IvGWq-KY/s400/bb11.JPG" width="400" /></a></span></div><div class="separator" style="clear: both; text-align: center;"><span style="font-family: inherit; text-align: left;">A hot backup requires quite a bit more work than cold backup.Below are steps required for Hot backup.</span></div><b style="font-family: inherit; text-align: justify;"><br />
</b><br />
<b style="font-family: inherit; text-align: justify;">Step 1 : </b><span style="font-family: inherit; text-align: justify;"> </span><b style="background-color: #ffe599; font-family: inherit; text-align: justify;">Check the log mode of the database</b><span style="font-family: inherit; text-align: justify;"> Whenever we go for hot backup then the database must be in archivelog mode . </span><br />
<span style="font-family: inherit;">SQL> </span><span style="background-color: #fff2cc; font-family: inherit;">SELECT LOG_MODE FROM V$DATABASE ;</span><br />
<span style="font-family: inherit;">LOG_MODE</span><br />
<span style="font-family: inherit;">---------------</span><br />
<span style="font-family: inherit;">ARCHIVELOG</span><br />
<br />
<span style="font-family: inherit;"><b>Step 2 : <span style="background-color: #ffe599;">Put the database into backup mode</span></b> If we are using the oracle 10gR2 or later , then we can put the entire database into </span><span style="font-family: inherit;">backup mode and if we are using the oracle prior to 10gR2 ,then we have to </span><span style="font-family: inherit;">put each tablespace in backup mode . In my case , I am having 11gR2 . </span><br />
<span style="font-family: inherit;">SQL></span><span style="background-color: #fff2cc; font-family: inherit;"> alter database begin backup ; </span><br />
<span style="font-family: inherit;">Database altered.</span><br />
<span style="font-family: inherit;">In case of <span style="background-color: #fff2cc;">oracle prior to 10gR2</span> use the below command as </span><br />
<span style="font-family: inherit;">SQL> <span style="background-color: #fff2cc;">set echo off </span></span><br />
<span style="font-family: inherit;">SQL> <span style="background-color: #fff2cc;">set heading off </span></span><br />
<span style="font-family: inherit;">SQL> <span style="background-color: #fff2cc;"> set feedback off </span></span><br />
<span style="font-family: inherit;">SQL> <span style="background-color: #fff2cc;">set termout off </span></span><br />
<span style="font-family: inherit;">SQL> <span style="background-color: #fff2cc;">spool backmode.sql </span></span><br />
<span style="font-family: inherit;">SQL> </span><span style="background-color: #fff2cc; font-family: inherit;">select 'alter tablespace '||name||' begin backup ;' "Tablespace in backup mode" from v$tablespace;</span><br />
SQL> <span style="background-color: #fff2cc;">spool off </span><br />
<span style="font-family: inherit;">SQL> <span style="background-color: #fff2cc;"> @C:\backmode.sql </span></span><br />
<span style="font-family: inherit;"><span style="background-color: #fff2cc;"><br />
</span></span><br />
<div style="text-align: justify;"><span style="font-family: inherit;"><b>Step 3 : <span style="background-color: #ffe599;">Backup all the datafiles </span> </b>Copy all the datafile using the operating system command and Paste it on the desired backup location .Meanwhile,we</span><span style="font-family: inherit; text-align: left;"> can verify the status of the datafile by using the v$backup view to check the status of the datafiles.</span></div><span style="font-family: inherit;">SQL> <span style="background-color: #fff2cc;">select * from v$backup ; </span></span><br />
<span style="font-family: inherit;"> FILE# STATUS CHANGE# TIME</span><br />
<span style="font-family: inherit;">---------- ------------------ ---------- ---------</span><br />
<span style="font-family: inherit;"> 1 ACTIVE 3967181 03-APR-12</span><br />
<span style="font-family: inherit;"> 2 ACTIVE 3967187 03-APR-12</span><br />
<span style="font-family: inherit;"> 3 ACTIVE 3967193 03-APR-12</span><br />
<span style="font-family: inherit;"> 4 ACTIVE 3967199 03-APR-12</span><br />
<span style="font-family: inherit;"> 5 ACTIVE 3967205 03-APR-12</span><br />
<span style="font-family: inherit;"> 6 ACTIVE 3967211 03-APR-12</span><br />
<span style="font-family: inherit;"> 7 ACTIVE 3967217 03-APR-12</span><br />
<span style="font-family: inherit;"> 8 ACTIVE 3967223 03-APR-12</span><br />
<span style="font-family: inherit;"> 9 ACTIVE 3967229 03-APR-12</span><br />
<span style="font-family: inherit;">The Column </span><span style="background-color: #fff2cc; font-family: inherit;">STATUS=ACTIVE</span><span style="font-family: inherit;"> shows that the datafiles are in backup mode . </span><br />
<span style="font-family: inherit;"><br />
</span><br />
<div style="text-align: justify;"><span style="font-family: inherit;"><b>Step 4 : <span style="background-color: #ffe599;">Take out the database from backup mode</span> </b>If we are using 10gR2 or above version of oracle , we use the below command to take out the database from backup mode as </span></div><span style="font-family: inherit;">SQL> <span style="background-color: #fff2cc;">alter database end backup ; </span></span><br />
<span style="font-family: inherit;">Database Altered </span><br />
<span style="font-family: inherit;">If we are having version prior to 10gR2 , then we use the below command as above : </span><br />
<span style="background-color: white; font-family: inherit;">SQL> </span><span style="background-color: #fff2cc; font-family: inherit;">set echo off </span><br />
<span style="font-family: inherit;">SQL> </span><span style="background-color: #fff2cc; font-family: inherit;">set heading off </span><br />
<span style="font-family: inherit;">SQL> </span><span style="background-color: #fff2cc; font-family: inherit;">set feedback off </span><br />
<span style="font-family: inherit;">SQL> </span><span style="background-color: #fff2cc; font-family: inherit;">set termout off </span><br />
<span style="font-family: inherit;">SQL> </span><span style="background-color: #fff2cc; font-family: inherit;">spool end_mode.sql </span><br />
<span style="font-family: inherit;">SQL> </span><span style="background-color: #fff2cc; font-family: inherit;">select 'alter tablespace '||name||' end backup ;' "tablespace in backup mode" from v$tablespace ; </span><br />
SQL> <span style="background-color: #fff2cc;">spool off </span><br />
<span style="font-family: inherit;">SQL> </span><span style="background-color: #fff2cc;">@C:\endmode.sql </span><br />
<span style="background-color: #fff2cc;"><br />
</span><br />
<div style="text-align: justify;"><span style="font-family: inherit;"><b>Step 5 : <span style="background-color: #ffe599;"> Switch the redolog file and backup archivelogs </span> </b> After taking the database out of Hot Backup we must switch logfile (preferably more than once) and backup the archivelogs generated .We may backup archivelogs while the database is in backup mode but we must also backup the first archivelog(s) after the end backup. The best method to do both is to run the SQL command alter system archive log current. This switches the logfile but does not return the prompt until the previous redo log has been archived. We can run alter system switch logfile, but then we won't be sure that the latest redo log has been archived before we move on to the next step. </span></div><span style="font-family: inherit;">SQL> <span style="background-color: #fff2cc;">alter system archive log current ; </span></span><br />
<span style="font-family: inherit;">System altered.</span><br />
<span style="font-family: inherit;">SQL></span><span style="background-color: #fff2cc; font-family: inherit;"> / </span><br />
<span style="font-family: inherit;">System altered.</span><br />
<span style="font-family: inherit;">Now backup the archivelogs to the backup location .</span><br />
<span style="font-family: inherit;"><br />
</span><br />
<b style="font-family: inherit; text-align: justify;">Step 6 : <span style="background-color: #ffe599;">Back up the control file </span></b><span style="color: #ffe599; font-family: inherit; text-align: justify;"> </span><span style="font-family: inherit; text-align: justify;">Now , we can backup the controlfile as binary file and as human readable .We should use both methods to back up the control file; either one may come in handy at different times . The commands are as </span><br />
<span style="font-family: inherit;"><i style="background-color: #fce5cd;">(Human readable)</i></span><br />
<span style="font-family: inherit;">SQL> <span style="background-color: #fff2cc;">alter database backup controlfile to trace ; </span> or </span><br />
<span style="font-family: inherit;">Database altered.</span><br />
<span style="font-family: inherit;">SQL> <span style="background-color: #fff2cc;">alter database backup controlfile to trace as '<backup location>' ; </span></span><br />
<span style="font-family: inherit;">Database altered.</span><br />
<i style="background-color: #fce5cd; font-family: inherit;">(Binary format)</i><br />
<span style="font-family: inherit;">SQL> <span style="background-color: #fff2cc;">alter database backup controlfile to '<backup location>' ; </span></span><br />
<span style="font-family: inherit;">Database altered.</span><br />
<span style="font-family: inherit;"><br />
</span><br />
<div style="text-align: justify;"><span style="font-family: inherit;"><b>Step 7 : <span style="background-color: #ffe599;">Backup the passwordfile and spfile</span></b> We can backup the passwordfile and spfile though it is not mandatory.</span></div><span style="font-family: inherit;"><br />
</span><br />
<span style="background-color: #ffd966; font-family: inherit;"><b>Some Points Worth Remembering </b></span><br />
<ul style="text-align: left;"><li><span style="font-family: inherit;">We need to backup all the archived log files, these files are very important to do recovery. </span></li>
<li><span style="font-family: inherit;">It is advisable to backup all of tablespaces (except read-only tablespaces), else complete recovery is not possible.</span></li>
<li><span style="font-family: inherit;">Backup of online redo log files are not required, as the online log file has the end of backup marker and would cause corruption if </span><span style="font-family: inherit;">used in recovery.</span></li>
<li><span style="font-family: inherit;">I</span><span style="font-family: inherit;">t is Preferable to start the hot backups at low activity time.</span></li>
<li><span style="font-family: inherit;">When hot backups are in progress we "cannot" shutdown the database in NORMAL or IMMEDIATE mode (and it is also not </span><span style="font-family: inherit;">desirable to ABORT).</span></li>
</ul><br />
<br />
For More <b><span style="color: blue;"><a href="http://knol.google.com/k/oracle-begin-backup-end-backup#" target="_blank">Click Here</a> </span></b><br />
<br />
<br />
<b>Enjoy <span style="background-color: yellow;"> :-) </span></b><br />
<br />
<br />
<br />
</div>NEERAJ VISHENhttp://www.blogger.com/profile/04032578930084182369noreply@blogger.com2tag:blogger.com,1999:blog-940885937847335876.post-45652813806119724052012-03-17T11:41:00.001+05:302012-03-23T10:51:17.503+05:30Again A Time To Celebrate !!!!!!!<div dir="ltr" style="text-align: left;" trbidi="on"><br />
<div style="text-align: justify;">Today , my blog visitor's hits crossed <b style="background-color: #fff2cc;"><span style="color: #cc0000;">50,000</span></b> and offcourse a time to celebrate this moment . I am blogging from last year (i.e, march 2011) and almost taken one year to acheive this . This month is very special to me as ever. There are lots of thing to celebrate in this month . <b><span style="color: #cc0000;"><i>Holi</i> </span></b>one of my favourite festival , <i><b><span style="color: #cc0000;">My Birthday</span></b> </i>and this time my <b><i><span style="color: #cc0000;">Blog Hits</span></i></b> and others .</div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;"></div><div style="text-align: left;"></div>This blogs helps to find some good friends whom i have never met and they are very kind and helpful to me. Whenever I am publishing any post on my blog, it boost up my confidence and motivate me to post further . Every comment by you all is helping me to go more deep . Kindly free feel to comment and ask your doubt and i will try my best to give solution .<br />
<div style="text-align: justify;"><br />
</div><br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/-aKgK9VwpdxA/T2QokrYB7FI/AAAAAAAAAUc/9mKmX46Dcu0/s1600/f1.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="299" src="http://3.bp.blogspot.com/-aKgK9VwpdxA/T2QokrYB7FI/AAAAAAAAAUc/9mKmX46Dcu0/s640/f1.JPG" width="640" /></a></div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;"><br />
</div><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<a href="http://2.bp.blogspot.com/-VO0mffoGc3o/T2QpYiV7JWI/AAAAAAAAAUk/6r6IYCRe-TU/s1600/f2.JPG" imageanchor="1" style="clear: left; display: inline !important; margin-bottom: 1em; margin-right: 1em; text-align: center;"><img border="0" height="404" src="http://2.bp.blogspot.com/-VO0mffoGc3o/T2QpYiV7JWI/AAAAAAAAAUk/6r6IYCRe-TU/s640/f2.JPG" width="640" /></a><br />
<br />
I need your support and wishes to continue posting in future . <br />
<br />
<br />
<b><span style="color: red;"><i>Cheers !!!!!!!</i></span></b><br />
<div><br />
</div><div><br />
</div></div>NEERAJ VISHENhttp://www.blogger.com/profile/04032578930084182369noreply@blogger.com4tag:blogger.com,1999:blog-940885937847335876.post-78261754458150106132012-03-14T21:19:00.000+05:302012-03-14T21:19:08.534+05:30Automatically Starting/Stopping Oracle on Redhat Linux<div dir="ltr" style="text-align: left;" trbidi="on"><br />
<div style="text-align: justify;">Oracle recommends that we should configure our system to automatically start Database when the system starts up, and to automatically shut it down when the system shuts down. Automating database startup and shutdown guards against incorrect database shutdown .</div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;">To automate database startup and shutdown, oracle use the <b style="background-color: #ffe599;">dbstart</b> and <b style="background-color: #ffe599;">dbshut</b> scripts, which are located in the <span style="background-color: #fff2cc;">$ORACLE_HOME/bin </span> directory. Let's have Look on the above scripts .</div><br />
<div style="text-align: justify;"><b>1.)</b> <b><span style="background-color: #ffe599;">Dbstart </span>:</b> This script is used to start ORACLE from /etc/rc(.local). It should ONLY be executed as part of the system boot procedure.This script will start all databases listed in the oratab file whose third field is a "Y". If the third field is set to "Y" and there is no ORACLE_SID for an entry (the first field is a *), then this script will ignore that entry.</div><br />
<div style="text-align: justify;"><b>2.)</b> <b><span style="background-color: #ffe599;">Dbshut </span>:</b> This script is used to shutdown ORACLE from /etc/rc(.local). It should ONLY be executed as part of the system boot procedure.This script will shutdown all databases listed in the oratab file whose third field is a "Y". If the third field is set to "Y" and there is no ORACLE_SID for an entry (the first field is a *), then this script will ignore that entry.</div><br />
<div style="text-align: justify;">We need to create the script which will run dbshut and dbstart scripts in the /etc/init.d directory ,whenever the machine is shutdown or start . Whenever machine boots it runs the scripts beginning with <span style="background-color: #ffe599;"><b>Snnname</b></span> in <span style="background-color: #fff2cc;">/etc/rc3.d</span> ,where the <span style="background-color: #ffe599;"><b>nn</b> </span>indicates the order in which the scripts will run . Similarly in case of shutdown scripts are named as <b style="background-color: #ffe599;">Knnnames</b> which runs from rc0.d .If we want that Oracle is the last program that is automatically started, and it is the first to be shutdown then we will name the startup and shutdown scripts on OS like <span style="background-color: #fff2cc;">/etc/rc3.d/S99</span>oracle and <span style="background-color: #fff2cc;">/etc/rc0.d/K01</span>oracle respectively.</div><br />
Let's have a <b style="background-color: #ffe599;">demo</b> of Automate Startup/Shutdown on Linux :<br />
<br />
<div style="text-align: justify;"><b>Step 1 :</b> <b><span style="background-color: #ffe599;">Edit the oratab file :</span></b> </div><div style="text-align: justify;">Oratab file is created by root.sh and updated by the Database Configuration Assistant when creating a database. The first and second fields are the system identifier and home directory of the database respectively. The third filed indicates to the dbstart utility that the database should , "Y", or should not, "N", be brought up at system boot time .In my case it is <span style="background-color: #fff2cc;"><i><b>comcast:/home/oracle/product/10.2.0/db_1:N</b></i></span></div>So it edit as<br />
<b><i style="background-color: #fff2cc;">comcast:/home/oracle/product/10.2.0/db_1:Y</i></b><br />
Where my database name is <b style="background-color: #ffe599;">comcast</b> and ORACLE_HOME is "<b style="background-color: #ffe599;">/home/oracle/product/10.2.0/db_1</b>"<br />
<br />
<br />
<b>Step 2 : <span style="background-color: #ffe599;">Create a file called dbora and add the below lines </span></b><br />
[root@tech ~]# <span style="background-color: #fff2cc;">cd /etc/init.d</span><br />
[root@tech init.d]# <span style="background-color: #fff2cc;">vi dbora</span><br />
add the following line<br />
<br />
<span style="background-color: #fff2cc;">#! /bin/sh -x</span><br />
<span style="background-color: #fff2cc;">#</span><br />
<span style="background-color: #fff2cc;"># Change the value of ORACLE_HOME to specify the correct Oracle home</span><br />
<span style="background-color: #fff2cc;"># directory for your installation.</span><br />
<span style="background-color: #fff2cc;">ORACLE_HOME=/home/oracle/product/10.2.0/db_1</span><br />
<span style="background-color: #fff2cc;">#</span><br />
<span style="background-color: #fff2cc;"># Change the value of ORACLE to the login name of the</span><br />
<span style="background-color: #fff2cc;"># oracle owner at your site.</span><br />
<span style="background-color: #fff2cc;">#</span><br />
<span style="background-color: #fff2cc;">ORACLE=oracle</span><br />
<span style="background-color: #fff2cc;">PATH=${PATH}:$ORACLE_HOME/bin</span><br />
<span style="background-color: #fff2cc;">HOST=`hostname`</span><br />
<span style="background-color: #fff2cc;">PLATFORM=`uname`</span><br />
<span style="background-color: #fff2cc;">export ORACLE_HOME PATH</span><br />
<span style="background-color: #fff2cc;">#</span><br />
<span style="background-color: #fff2cc;">if [ ! "$2" = "ORA_DB" ] ; then</span><br />
<span style="background-color: #fff2cc;"> if [ "$PLATFORM" = "HP-UX" ] ; then</span><br />
<span style="background-color: #fff2cc;"> remsh $HOST -l $ORACLE -n "$0 $1 ORA_DB"</span><br />
<span style="background-color: #fff2cc;"> exit</span><br />
<span style="background-color: #fff2cc;"> else</span><br />
<span style="background-color: #fff2cc;"> rsh $HOST -l $ORACLE $0 $1 ORA_DB</span><br />
<span style="background-color: #fff2cc;"> exit</span><br />
<span style="background-color: #fff2cc;"> fi</span><br />
<span style="background-color: #fff2cc;">fi</span><br />
<span style="background-color: #fff2cc;">#</span><br />
<span style="background-color: #fff2cc;">case $1 in</span><br />
<span style="background-color: #fff2cc;">'start')</span><br />
<span style="background-color: #fff2cc;"> if [ "$PLATFORM" = "Linux" ] ; then</span><br />
<span style="background-color: #fff2cc;"> touch /var/lock/subsys/dbora</span><br />
<span style="background-color: #fff2cc;"> fi</span><br />
<span style="background-color: #fff2cc;"> $ORACLE_HOME/bin/dbstart $ORACLE_HOME &</span><br />
<span style="background-color: #fff2cc;"> ;;</span><br />
<span style="background-color: #fff2cc;">'stop')</span><br />
<span style="background-color: #fff2cc;"> $ORACLE_HOME/bin/dbshut $ORACLE_HOME &</span><br />
<span style="background-color: #fff2cc;"> ;;</span><br />
<span style="background-color: #fff2cc;">*)</span><br />
<span style="background-color: #fff2cc;"> echo "usage: $0 {start|stop}"</span><br />
<span style="background-color: #fff2cc;"> exit</span><br />
<span style="background-color: #fff2cc;"> ;;</span><br />
<span style="background-color: #fff2cc;">esac</span><br />
<br />
<b>Step 3 : <span style="background-color: #ffe599;">Change the group of the dbora file and set the permission</span> </b><br />
[root@tech init.d]# <span style="background-color: #fff2cc;">chgrp dba dbora</span><br />
[root@tech init.d]# <span style="background-color: #fff2cc;">chmod 750 dbora</span><br />
<span style="background-color: #fff2cc;"><br />
</span><br />
<br />
<b>Step 4 : <span style="background-color: #ffe599;">Create symbolic links to the dbora </span></b><br />
<div style="text-align: justify;">Create symbolic links to the dbora script in the appropriate run-level script directories as follows.We need to add the appropriate symbolic links to cause the script to be executed when the system goes down, or comes up.</div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;">[root@tech init.d]# <span style="background-color: #fff2cc;">ln -s /etc/init.d/dbora /etc/rc.d/rc0.d/K01dbora</span></div>[root@tech init.d]#<span style="background-color: #fff2cc;"> ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/S99dbora</span><br />
[root@tech init.d]# <span style="background-color: #fff2cc;">ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/S99dbora</span><br />
<br />
<br />
<b>Step 5 : <span style="background-color: #ffe599;">Test the scripts </span></b><br />
We can check the script by restarting the machine . Other alternative method is<br />
<br />
[root@tech init.d]# <span style="background-color: #fff2cc;">/etc/init.d/dbora start </span>(for startup)<br />
[root@tech init.d]# <span style="background-color: #fff2cc;">/etc/init.d/dbora stop </span> (for shutdown)<br />
<br />
Above command will work same as we start the services in Window .<br />
<br />
<br />
<b>Enjoy <span style="background-color: yellow;">:-)</span> </b><br />
<br />
</div>NEERAJ VISHENhttp://www.blogger.com/profile/04032578930084182369noreply@blogger.com1tag:blogger.com,1999:blog-940885937847335876.post-51723908868496789312012-03-12T23:59:00.000+05:302012-03-12T21:55:29.075+05:30Happy Birthday To Me<div dir="ltr" style="text-align: left;" trbidi="on"><br />
<div style="text-align: justify;">Today is my birthday , <b style="background-color: #ffe599;">13th March</b> . I will wish myself a Very Very Happy Birthday To Me and may god fulfill my wishes and dreams .</div><div style="text-align: justify;">I am honor to say it was my year of Joy, Happiness great turn around and l thank God for all . He has in place for me in many years to come,am just so happy another new year is added to my new sweet life, Glory to God for everthing . He has done and more to do.</div><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://2.bp.blogspot.com/-NQujHoi0a9c/T14gjNIQZjI/AAAAAAAAAUM/fN1z0dVxhHQ/s1600/brthday.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-NQujHoi0a9c/T14gjNIQZjI/AAAAAAAAAUM/fN1z0dVxhHQ/s1600/brthday.JPG" /></a></div><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
Here is a nice Poem which is dedicated to myself . :)<br />
<br />
<span style="color: red;"><b><span style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;">I'm not afraid of counting years;</span><br style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;" /><span style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;">Each year is a new myth exploded.</span><br style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;" /><span style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;">I'm not afraid of wrinkles and gray hair;</span><br style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;" /><span style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;">This body is how I navigate through the cosmos, so I try to love it and be</span><br style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;" /><span style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;">kind.</span><br style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;" /><span style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;">I'm not afraid of death; It is only the final myth to unravel. I'm not even afraid of the devil,</span><br style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;" /><span style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;">because it is our adversary who gives us life's most precious gifts. I'm</span><br style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;" /><span style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;">ftwenty years old and lost things are coming back:</span><br style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;" /><span style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;">Emails from long vanished friends</span><br style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;" /><span style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;">Bits of shattered faith discovered in between the cracks of the couch and</span><br style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;" /><span style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;">under the edges of the carpet that I'm piecing together into a mosaic New things</span><br style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;" /><span style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;">arrive every day too:</span><br style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;" /><span style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;">New poems</span><br style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;" /><span style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;">New stories spinning out of my brain</span><br style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;" /><span style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;">A new sense of my place in the old story</span><br style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;" /><span style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;">New meaning in the ancient struggle for justice Each year is a new crossroads.</span></b></span><b style="color: red;"><span style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;"> </span></b><br />
<b style="color: red;"><span style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;"> </span><span style="font-size: large;"><span style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; line-height: 18px; text-align: -webkit-auto;"> </span><span style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, sans-serif; line-height: 18px; text-align: -webkit-auto;">Happy birthday to me!!!!</span></span></b><br />
<br />
<br />
<br />
<br />
<br />
</div>NEERAJ VISHENhttp://www.blogger.com/profile/04032578930084182369noreply@blogger.com4tag:blogger.com,1999:blog-940885937847335876.post-10265704362270431512012-03-07T17:00:00.000+05:302012-03-07T17:00:17.325+05:30<div dir="ltr" style="text-align: left;" trbidi="on"><br />
<b><span style="color: red; font-size: large;"> </span></b><br />
<b><span style="color: red; font-size: large;"> Celebrating the colors of our beautiful relationship, </span></b><br />
<b><span style="color: red; font-size: large;"> I wish you and your family all </span></b><br />
<b><span style="color: red; font-size: large;"> the bright hues of life.</span></b><br />
<b><span style="background-color: white; color: red; font-size: large;"> Have a colourful holi !</span></b><br />
<b><span style="background-color: white; color: red; font-size: large;"><br />
</span></b><br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://1.bp.blogspot.com/-naBDjSMz9yY/T1dCqYCFiGI/AAAAAAAAAT0/30M2Hb3Bsac/s1600/holi.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/-naBDjSMz9yY/T1dCqYCFiGI/AAAAAAAAAT0/30M2Hb3Bsac/s1600/holi.JPG" /></a></div><b><span style="background-color: white; color: red; font-size: large;"> May God gift you all the colors of life ; </span></b><b><span style="background-color: white; color: red; font-size: large;"> colors of joy, </span></b><br />
<b><span style="background-color: white; color: red; font-size: large;"> colors of happiness , </span></b><b><span style="background-color: white; color: red; font-size: large;"> colors of friendship , colors of love</span></b><br />
<b><span style="background-color: white; color: red; font-size: large;"> and</span></b><b><span style="background-color: white; color: red; font-size: large;"> all the other colors , you want to paint your life with.</span></b><br />
<b><span style="background-color: white; color: red; font-size: large;"> </span></b><br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://1.bp.blogspot.com/-sHBCOudLsHk/T1dGeV-AZYI/AAAAAAAAAUA/Nnro-ZXJlXI/s1600/ghhhhhhh.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/-sHBCOudLsHk/T1dGeV-AZYI/AAAAAAAAAUA/Nnro-ZXJlXI/s1600/ghhhhhhh.JPG" /></a></div><b><span style="background-color: white; color: red; font-size: large;"><br />
</span></b><br />
<b><span style="background-color: white; color: red; font-size: large;"> </span><span style="background-color: white; color: purple; font-size: x-large;">H</span><span style="background-color: white; color: red; font-size: x-large;">a</span><span style="background-color: white; color: orange; font-size: x-large;">p</span><span style="background-color: white; color: lime; font-size: x-large;">p</span><span style="background-color: white; color: blue; font-size: x-large;">y</span><span style="background-color: white; color: red; font-size: x-large;"> H</span><span style="background-color: white; color: #38761d; font-size: x-large;">o</span><span style="background-color: white; color: #3d85c6; font-size: x-large;">l</span><span style="background-color: white; color: #a64d79; font-size: x-large;">i</span><span style="background-color: white; color: red; font-size: x-large;"> </span><span style="background-color: white; color: #351c75; font-size: x-large;">T</span><span style="background-color: white; color: #b45f06; font-size: x-large;">o</span><span style="background-color: white; color: red; font-size: x-large;"> </span><span style="background-color: white; color: magenta; font-size: x-large;">Y</span><span style="background-color: white; color: cyan; font-size: x-large;">o</span><span style="background-color: white; color: #8e7cc3; font-size: x-large;">u</span><span style="background-color: white; color: red; font-size: x-large;">.</span></b><br />
<span style="color: red; font-size: large;"><b><br />
</b></span><br />
<span style="color: red; font-size: large;"><b> Enjoy :-) </b></span><br />
<span style="color: red; font-size: large;"><b><br />
</b></span><br />
<span style="color: red; font-size: large;"><b><br />
</b></span><br />
</div>NEERAJ VISHENhttp://www.blogger.com/profile/04032578930084182369noreply@blogger.com0