tag:blogger.com,1999:blog-51607825298927699982024-03-28T20:30:17.054-07:00oracle technology lovers blogA place for people who love Oracle technology and want to learn Oracle more and moreAnand Chavanhttp://www.blogger.com/profile/15193918016445917466noreply@blogger.comBlogger27125tag:blogger.com,1999:blog-5160782529892769998.post-70311926715669495992012-11-23T01:17:00.005-08:002012-11-23T01:17:27.441-08:00oracle technology lovers blog: View Free space from temporary tablespace<a href="http://oracletechlovers.blogspot.com/2010/04/view-free-space-from-temporary.html?spref=bl">oracle technology lovers blog: View Free space from temporary tablespace</a>: select substr(a.tablespace_name,1,20) "TS_name", substr(a.file_name,1,35) "File_Name", round(sum(a.bytes)/1024/1024,2) "Size_MB",sum(NVL(b.b...Anand Chavanhttp://www.blogger.com/profile/15193918016445917466noreply@blogger.com4tag:blogger.com,1999:blog-5160782529892769998.post-86398605924558364492012-11-23T01:17:00.003-08:002012-11-23T01:17:16.854-08:00oracle technology lovers blog: OS Watcher (OSW<a href="http://oracletechlovers.blogspot.com/2010/05/os-watcher-osw.html?spref=bl">oracle technology lovers blog: OS Watcher (OSW</a>: OS Watcher (OSW) is a collection of UNIX shell scripts intended to collect and archive operating system and network metrics to aid support...Anand Chavanhttp://www.blogger.com/profile/15193918016445917466noreply@blogger.com5tag:blogger.com,1999:blog-5160782529892769998.post-9484823243001282392012-11-23T01:17:00.001-08:002012-11-23T01:17:04.028-08:00oracle technology lovers blog: Monitor user session.<a href="http://oracletechlovers.blogspot.com/2010/08/monitor-user-session.html?spref=bl">oracle technology lovers blog: Monitor user session.</a>: set linesize 220 col first_load_time format a20 col client_info format a20 col username format a20 col last_load_time format a20 col sq...Anand Chavanhttp://www.blogger.com/profile/15193918016445917466noreply@blogger.com2tag:blogger.com,1999:blog-5160782529892769998.post-90927037446481259702012-11-23T01:16:00.003-08:002012-11-23T01:16:26.497-08:00oracle technology lovers blog: How can the ASM instance start when the spfile is ...<a href="http://oracletechlovers.blogspot.com/2010/09/how-can-asm-instance-start-when-spfile.html?spref=bl">oracle technology lovers blog: How can the ASM instance start when the spfile is ...</a>: SQL> show parameter pfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile ...Anand Chavanhttp://www.blogger.com/profile/15193918016445917466noreply@blogger.com0tag:blogger.com,1999:blog-5160782529892769998.post-91644954846488344202012-11-23T01:16:00.001-08:002012-11-23T01:16:08.701-08:00oracle technology lovers blog: Oracle RAC: restoring lost OCR file<a href="http://oracletechlovers.blogspot.com/2011/04/oracle-rac-restoring-lost-ocr-file.html?spref=bl">oracle technology lovers blog: Oracle RAC: restoring lost OCR file</a>: Shutdown everything oracle instances then run - rootdelete.sh - root.sh this will create OCR Add resource manually.Anand Chavanhttp://www.blogger.com/profile/15193918016445917466noreply@blogger.com0tag:blogger.com,1999:blog-5160782529892769998.post-61764785525370099652012-11-23T01:15:00.005-08:002012-11-23T01:15:44.695-08:00oracle technology lovers blog: steps to reset original password<a href="http://oracletechlovers.blogspot.com/2012/01/steps-to-reset-original-password.html?spref=bl">oracle technology lovers blog: steps to reset original password</a>: Use below steps to reset original password: Look at the example: schema name is : OASTOLTP SQL> set lines 9999 set pages 9999 set long 90...Anand Chavanhttp://www.blogger.com/profile/15193918016445917466noreply@blogger.com1tag:blogger.com,1999:blog-5160782529892769998.post-46802916168022581562012-11-23T01:15:00.003-08:002012-11-23T01:15:20.683-08:00oracle technology lovers blog: Extra Redo generation during online backup<a href="http://oracletechlovers.blogspot.com/2012/06/extra-redo-generation-during-online.html?spref=bl">oracle technology lovers blog: Extra Redo generation during online backup</a>: There is not excessive redo generated, there is additional information logged into the online redo log during a hot backup the first ti...Anand Chavanhttp://www.blogger.com/profile/15193918016445917466noreply@blogger.com0tag:blogger.com,1999:blog-5160782529892769998.post-28111525332452729512012-11-23T01:15:00.001-08:002012-11-23T01:15:05.535-08:00oracle technology lovers blog: Oracle RAC Basics<a href="http://oracletechlovers.blogspot.com/2012/11/oracle-rac-is-nothing-but-multiple.html?spref=bl">oracle technology lovers blog: Oracle RAC Basics</a>: Oracle RAC is nothing but multiple nodes (each with an Oracle instance) access a same shared database. ...Anand Chavanhttp://www.blogger.com/profile/15193918016445917466noreply@blogger.com2tag:blogger.com,1999:blog-5160782529892769998.post-78477889224181521882012-11-23T01:14:00.001-08:002012-11-23T01:14:33.845-08:00oracle technology lovers blog: Difference between Oracle Single instance and Orac...<a href="http://oracletechlovers.blogspot.com/2012/11/difference-between-oracle-single.html?spref=bl">oracle technology lovers blog: Difference between Oracle Single instance and Orac...</a>: Single Instance Oracle RAC Single instance accessing database Multiple instances accessing same database Database can be local or shar...Anand Chavanhttp://www.blogger.com/profile/15193918016445917466noreply@blogger.com0tag:blogger.com,1999:blog-5160782529892769998.post-355355598664582532012-11-22T04:34:00.000-08:002012-11-22T04:34:01.778-08:00Difference between Oracle Single instance and Oracle RAC<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<table align="center" border="2" cellpadding="3" cellspacing="3" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; color: #3e5867; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; width: 576px;"><tbody style="background-color: #f7f7f7; border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: center;">
<tr style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><th style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">Single Instance</th><th style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">Oracle RAC</th></tr>
<tr style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><td style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><span style="color: #3e5867; font-family: 'Times New Roman','serif'; font-size: 10pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: 'Times New Roman'; mso-fareast-language: EN-US;"><em>Single instance accessing database</em></span></td><td style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><span style="color: #3e5867; font-family: 'Times New Roman','serif'; font-size: 10pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: 'Times New Roman'; mso-fareast-language: EN-US;"><em>Multiple instances accessing same database</em></span></td></tr>
<tr style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><td style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><span style="color: #3e5867; font-family: 'Times New Roman','serif'; font-size: 10pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: 'Times New Roman'; mso-fareast-language: EN-US;"><em>Database can be local or shared</em></span></td><td style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><span style="color: #3e5867; font-family: 'Times New Roman','serif'; font-size: 10pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: 'Times New Roman'; mso-fareast-language: EN-US;"><em>Database must be on shared</em></span>.</td></tr>
<tr style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><td style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><span style="color: #3e5867; font-family: 'Times New Roman','serif'; font-size: 10pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: 'Times New Roman'; mso-fareast-language: EN-US;"><em>Minimum one node is Required</em></span></td><td style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><span style="color: #3e5867; font-family: 'Times New Roman','serif'; font-size: 10pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: 'Times New Roman'; mso-fareast-language: EN-US;"><em>Minimum two node required</em></span></td></tr>
<tr style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><td style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><span style="color: #3e5867; font-family: 'Times New Roman','serif'; font-size: 10pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: 'Times New Roman'; mso-fareast-language: EN-US;"><em>Clusterware software is not required</em></span></td><td style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><span style="color: #3e5867; font-family: 'Times New Roman','serif'; font-size: 10pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: 'Times New Roman'; mso-fareast-language: EN-US;"><em>Clusterware Software is required</em></span>.</td></tr>
<tr style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><td style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><span style="color: #3e5867; font-family: 'Times New Roman','serif'; font-size: 10pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: 'Times New Roman'; mso-fareast-language: EN-US;"><em>Consists of one SGA and one set of background<span style="mso-spacerun: yes;"> </span>processes</em></span></td><td style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><span style="color: #3e5867; font-family: 'Times New Roman','serif'; font-size: 10pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: 'Times New Roman'; mso-fareast-language: EN-US;"><em>Each instance having own<span style="mso-spacerun: yes;"> </span>one SGA and one set of background<span style="mso-spacerun: yes;"> </span>processes</em></span></td></tr>
<tr style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><td style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><span style="color: #3e5867; font-family: 'Times New Roman','serif'; font-size: 10pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: 'Times New Roman'; mso-fareast-language: EN-US;"><em><span style="color: #3e5867; font-family: 'Times New Roman','serif'; font-size: 10pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: 'Times New Roman'; mso-fareast-language: EN-US;">One set of redo logs</span></em></span></td><td style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><span style="color: #3e5867; font-family: 'Times New Roman','serif'; font-size: 10pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: 'Times New Roman'; mso-fareast-language: EN-US;"><em><span style="color: #3e5867; font-family: 'Times New Roman','serif'; font-size: 10pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: 'Times New Roman'; mso-fareast-language: EN-US;">Multiple set redo logs depending on number of instances</span></em></span></td></tr>
<tr style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><td style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><span style="color: #3e5867; font-family: 'Times New Roman','serif'; font-size: 10pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: 'Times New Roman'; mso-fareast-language: EN-US;"><em><span style="color: #3e5867; font-family: 'Times New Roman','serif'; font-size: 10pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: 'Times New Roman'; mso-fareast-language: EN-US;">No Cache fusion used un Single instance</span></em></span></td><td style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><span style="color: #3e5867; font-family: 'Times New Roman','serif'; font-size: 10pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: 'Times New Roman'; mso-fareast-language: EN-US;"><em><span style="color: #3e5867; font-family: 'Times New Roman','serif'; font-size: 10pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: 'Times New Roman'; mso-fareast-language: EN-US;">Cache fusion is used to transfer or access block from remote instance</span></em></span></td></tr>
<tr style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><td style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><span style="color: #3e5867; font-family: 'Times New Roman','serif'; font-size: 10pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: 'Times New Roman'; mso-fareast-language: EN-US;"><em><span style="color: #3e5867; font-family: 'Times New Roman','serif'; font-size: 10pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: 'Times New Roman'; mso-fareast-language: EN-US;">V$ views to monitor and manage instance</span></em></span></td><td style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><span style="color: #3e5867; font-family: 'Times New Roman','serif'; font-size: 10pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: 'Times New Roman'; mso-fareast-language: EN-US;"><em><span style="color: #3e5867; font-family: 'Times New Roman','serif'; font-size: 10pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: 'Times New Roman'; mso-fareast-language: EN-US;">For monitoring at the cluster level Oracle provides GV$ views.</span></em></span></td></tr>
<tr style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><td style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><span style="color: #3e5867; font-family: 'Times New Roman','serif'; font-size: 10pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: 'Times New Roman'; mso-fareast-language: EN-US;"><em><span style="color: #3e5867; font-family: 'Times New Roman','serif'; font-size: 10pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: 'Times New Roman'; mso-fareast-language: EN-US;"><span style="color: #3e5867; font-family: 'Times New Roman','serif'; font-size: 10pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: 'Times New Roman'; mso-fareast-language: EN-US;">Meeting some of the business requirements (availability, scalability) is limited to a single instance configuration.</span></span></em></span></td><td style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><span style="color: #3e5867; font-family: 'Times New Roman','serif'; font-size: 10pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: 'Times New Roman'; mso-fareast-language: EN-US;"><em><span style="color: #3e5867; font-family: 'Times New Roman','serif'; font-size: 10pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: 'Times New Roman'; mso-fareast-language: EN-US;"><span style="color: #3e5867; font-family: 'Times New Roman','serif'; font-size: 10pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: 'Times New Roman'; mso-fareast-language: EN-US;">Modern business requirements of high availability and linear scalability are provided by multiple instances sharing a common physical database</span></span></em></span></td></tr>
</tbody></table>
<br />
<br />
<br />
<div>
<br /></div>
<div>
<br /></div>
</div>
Anand Chavanhttp://www.blogger.com/profile/15193918016445917466noreply@blogger.com63tag:blogger.com,1999:blog-5160782529892769998.post-44018505739304484722012-11-20T06:43:00.000-08:002012-11-20T23:27:36.679-08:00Oracle RAC Basics<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none; text-align: left;" trbidi="on">
<br />
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
<div style="border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;">
<span style="font-family: Calibri;">Oracle RAC is nothing but multiple nodes (each with an Oracle instance) access a same shared database.<o:p></o:p></span><br />
<span style="background-color: black;"></span></div>
</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div style="border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;">
<div class="separator" style="clear: both; text-align: center;">
<span style="background-color: black;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg-7j4nr8QmX7ee20uPzHesGg4WxV_qLzJMDa0uuBycWLTtqOAI30ej_-CHPc44vCvJJhYup8hY1uyV7uuXG75f9ftNohC0oMR5ojY-r5sZAt4Ym97VQ8bWrr7FUpHGowfCTgxH2RkWL7G4/s1600/OracleRAC.png" imageanchor="1" style="clear: left; cssfloat: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="306" rea="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg-7j4nr8QmX7ee20uPzHesGg4WxV_qLzJMDa0uuBycWLTtqOAI30ej_-CHPc44vCvJJhYup8hY1uyV7uuXG75f9ftNohC0oMR5ojY-r5sZAt4Ym97VQ8bWrr7FUpHGowfCTgxH2RkWL7G4/s640/OracleRAC.png" width="640" /></a></span></div>
</div>
</div>
<div>
</div>
<br />
<span style="background-color: white;"></span> <br />
<span style="background-color: #999999;"><span style="background-color: red;"><span style="background-color: white;"><span style="background-color: red;"><span style="background-color: red;"><span style="background-color: black;"><span style="background-color: black;"><span style="background-color: black;"><span style="background-color: #f3f3f3;"><span style="background-color: white;"><div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
</span></span></span></span></span></span></span></span></span></span><br />
<div>
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgSxu-0ISDBu-Fc_fgYt4uE_SnnXGiQPGskVnJ4xnsmc7-ZA6rfCUE5siED38wRTf2q6XSuYzgnx2M8ub27QRExkZH1Slh9XfASDlOBSRFY_K_6vy18ndd_8dxzF8UBeCsyDMd19xKOf5mB/s1600/OracleRAC1.png" imageanchor="1" style="clear: left; cssfloat: left; float: left; margin-bottom: 1em; margin-right: 1em;"><span style="background-color: white; color: white;"></span></a></div>
<br />
<br />
<h4 align="left">
Component of Oracle RAC</h4>
<div align="left">
</div>
<div align="left">
<div>
</div>
<ul>
<li><div align="left">
<span style="font-family: "Courier New", Courier, monospace; font-size: small;">Node</span></div>
</li>
<li><div align="left">
<span style="font-family: "Courier New", Courier, monospace; font-size: small;">Instance</span></div>
</li>
<li><div align="left">
<span style="font-family: "Courier New", Courier, monospace; font-size: small;">Database</span></div>
</li>
<li><div align="left">
<span style="font-family: "Courier New", Courier, monospace; font-size: small;">Clusterware software. </span></div>
</li>
<li><div align="left">
<span style="font-family: "Courier New", Courier, monospace; font-size: small;">Interconnect</span></div>
</li>
<li><div align="left">
<span style="font-family: "Courier New", Courier, monospace; font-size: small;">OCR and Vote disk</span></div>
</li>
</ul>
<div align="left">
<br /></div>
</div>
<div align="left">
<br /></div>
<div align="left">
<span style="font-family: "Courier New", Courier, monospace;"><span style="font-size: small;"><strong>Node</strong>: Node is individual machine which has operating system install on it.</span></span></div>
<div align="left">
<span style="font-family: "Courier New", Courier, monospace;"><span style="font-size: small;"><strong>Instance</strong>: Instance is a way to access the database. It is combination of memory and background processes</span></span></div>
<div align="left">
<span style="font-family: "Courier New", Courier, monospace;"><span style="font-size: small;"><strong>Database</strong>: Database is set of Oracle files such as data file, Control files, Oracle redo log file and Archive log file</span></span></div>
<div align="left">
<span style="font-family: "Courier New", Courier, monospace;"><span style="font-size: small;"><strong>Clusterware software</strong>: It maintains integrity of Oracle database and manages shared and local resources.</span></span></div>
<div align="left">
<span style="font-family: "Courier New", Courier, monospace;"><span style="font-size: small;"><strong>Interconnect</strong>: Interconnect is nothing but network link between nodes. It maintains network heartbeat between RAC nodes. It also useful send block to other instance through network in case of cache fusion.</span></span></div>
<div align="left">
<span style="font-family: "Courier New", Courier, monospace;"><span style="font-size: small;"><strong>OCR</strong>: The OCR maintains cluster configuration information that is used by each node of the cluster to determine the state of the cluster. The OCR also maintains information on application resources that are defined within Clusterware such as databases, instance, services and the like and is therefore constantly changing. Each node in the cluster maintains a copy of the OCR in memory for better performance and each node is responsible for updating the OCR as required.</span></span></div>
<div align="left">
<span style="font-family: "Courier New", Courier, monospace;"><span style="font-size: small;"><strong>Vote Disk</strong>:The Voting Disk Files are used by Oracle Clusterware to determine which nodes are currently members of the cluster. The voting disk files are also used in concert with other Cluster components such as CRS to maintain the clusters integrity. As with most everything else Clusterware/RAC related, you need to consider redundancy in your architecture. Oracle expects that you will configure at least 3 voting disks for redundancy purposes. You should always configure an odd number of voting disks >= 3. This is because loss of more than half your voting disks will cause the entire cluster to fail.</span></span></div>
</div>
Anand Chavanhttp://www.blogger.com/profile/15193918016445917466noreply@blogger.com3tag:blogger.com,1999:blog-5160782529892769998.post-9777725265735214542012-06-24T02:58:00.001-07:002012-11-05T21:42:44.877-08:00Useful links<div dir="ltr" style="text-align: left;" trbidi="on">
<a href="http://orabackus.wordpress.com/2007/11/18/howto-manually-convert-single-instance-to-rac-on-unix/" rel="bookmark" style="text-decoration: none;" title="Permanent Link to HOWTO: Manually Convert Single Instance to RAC on Unix"><span style="color: #b45f06;">HOWTO: Manually Convert Single Instance to RAC on Unix</span></a><br />
<a href="http://orabackus.wordpress.com/2007/11/18/howto-manually-convert-single-instance-to-rac-on-unix/">http://orabackus.wordpress.com/2007/11/18/howto-manually-convert-single-instance-to-rac-on-unix/</a><br />
<br />
<div class="entry-title" style="text-align: left;">
<span style="color: #b45f06;">ASMLib</span></div>
<div class="entry-title" style="text-align: left;">
<a href="https://blogs.oracle.com/wim/entry/asmlib">https://blogs.oracle.com/wim/entry/asmlib</a><br />
<br />
<span style="color: #b45f06;">Oracle Clusterware Node Fencing</span><br />
<a href="http://www.trivadis.com/uploads/tx_cabagdownloadarea/Trivadis_oracle_clusterware_node_fencing_v.pdf">http://www.trivadis.com/uploads/tx_cabagdownloadarea/Trivadis_oracle_clusterware_node_fencing_v.pdf</a><br />
<br />
<br />
<div style="text-align: left;">
<span style="color: #b45f06;">Oracle Database 12c: New Features - Pluggable Databases</span></div>
<div style="text-align: left;">
<a href="http://www.orafaq.com/node/2756">http://www.orafaq.com/node/2756</a></div>
<div>
<br /></div>
</div>
</div>
Anand Chavanhttp://www.blogger.com/profile/15193918016445917466noreply@blogger.com0tag:blogger.com,1999:blog-5160782529892769998.post-81014072806339392882012-06-24T02:58:00.000-07:002012-11-22T04:07:31.366-08:00ASM: Difference's between CORSE and FINE Striping<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<table align="center" border="2" cellpadding="3" cellspacing="3" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; color: #3e5867; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; width: 576px;"><tbody style="background-color: #f7f7f7; border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: center;">
<tr style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><th style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">COARSE STRIPING</th><th style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">FINE GRAINED STRIPING</th></tr>
<tr style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><td style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">It is used for all voluminous input/output, e.g. input/output operations on datafiles.</td><td style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">It is used for all small input/output, e.g. input/output operations on online redolog files and control files.</td></tr>
<tr style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><td style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">The size of the coarse grained data stripes is large.</td><td style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">The size of the fine grained data stripes is small.</td></tr>
<tr style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><td style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">It manages the load balance across the disk groups.</td><td style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">It spreads the load on disk groups reducing latency for certain file types.</td></tr>
<tr style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><td style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">The size of the coarse-grained stripe is always equal to the size of ASM Allocation Units (AU).</td><td style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">The size of the fine-grained stripe is always 128 KB.</td></tr>
<tr style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><td style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">The size for coarse striping can be set using the _asm_ausize parameter.</td><td style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; font-size: 13px; margin: 0px; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">The size for fine grained striping can be set using the _asm_stripesize parameter.</td></tr>
</tbody></table>
<br />
<div>
<br /></div>
<div>
<br /></div>
</div>
Anand Chavanhttp://www.blogger.com/profile/15193918016445917466noreply@blogger.com1tag:blogger.com,1999:blog-5160782529892769998.post-10038762525026663902012-06-24T02:57:00.000-07:002012-11-05T04:48:37.925-08:00Extra Redo generation during online backup<div dir="ltr" style="text-align: left;" trbidi="on">
<pre><pre><div style="text-align: left;">
</div>
</pre>
</pre>
There is not excessive redo generated, there is additional information logged into the online redo log during a hot backup the first time a block is modified in a tablespace that is in hot backup mode. In hot backup mode only 2 things are different:<br />
<br />
<div style="text-align: left;">
</div>
<div style="text-align: left;">
The first time a block is changed in a datafile that is in hot backup mode, the ENTIRE </div>
<div style="text-align: left;">
BLOCK is written to the redo log files, not just the changed bytes. Normally only the </div>
<div style="text-align: left;">
changed bytes (a redo vector) is written. In hot backup mode, the entire block is logged </div>
<div style="text-align: left;">
the FIRST TIME. This is because you can get into a situation where the process copying </div>
<div style="text-align: left;">
the datafile and DBWR are working on the same block simultaneously. Lets say they are </div>
<div style="text-align: left;">
and the OS blocking read factor is 512bytes (the OS reads 512 bytes from disk at a time). </div>
<div style="text-align: left;">
The backup program goes to read an 8k Oracle block. The OS gives it 4k. Meanwhile -- </div>
<div style="text-align: left;">
DBWR has asked to rewrite this block. the OS schedules the DBWR write to occur right </div>
<div style="text-align: left;">
now. The entire 8k block is rewritten. The backup program starts running again </div>
<div style="text-align: left;">
(multi-tasking OS here) and reads the last 4k of the block. The backup program has now </div>
<div style="text-align: left;">
gotten an impossible block -- the head and tail are from two points in time. We cannot </div>
<div style="text-align: left;">
deal with that during recovery. Hence, we log the entire block image so that during </div>
<div style="text-align: left;">
recovery, this block is totally rewritten from redo and is consistent with itself at </div>
<div style="text-align: left;">
least. We can recover it from there.</div>
<br />
The datafile headers which contain the SCN of the last completed checkpoint are NOT<br />
updated while a file is in hot backup mode. This lets the recovery process understand<br />
what archive redo log files might be needed to fully recover this file.<br />
<br />
To limit the effect of this additional logging, you should ensure you only place one<br />
tablepspace at a time in backup mode and bring the tablespace out of backup mode as soon<br />
as you have backed it up. This will reduce the number of blocks that may have to be<br />
logged to the minimum possible.<br />
<div>
<br /></div>
</div>
Anand Chavanhttp://www.blogger.com/profile/15193918016445917466noreply@blogger.com0tag:blogger.com,1999:blog-5160782529892769998.post-9348655506352086842012-01-26T23:19:00.000-08:002012-01-26T23:25:49.793-08:00steps to reset original passwordUse below steps to reset original password:<br /><br />Look at the example:<br /><br />schema name is : OASTOLTP<br /><br />SQL> set lines 9999<br />set pages 9999<br />set long 90000000SQL> SQL><br />SQL><br />SQL> select dbms_metadata.get_ddl('USER','OASTOLTP') from dual;<br /><br />DBMS_METADATA.GET_DDL('USER','OASTOLTP')<br />--------------------------------------------------------------------------------<br /><br />CREATE USER "OASTOLTP" IDENTIFIED BY VALUES 'S:46DE8181197D5D6A2BEE2B43B21762<br />BE9B725B07217DA76100F37D12598D;6B9A3DF7BDFDD198'<br />DEFAULT TABLESPACE "USERS"<br />TEMPORARY TABLESPACE "TEMP_0"<br /><br /><br />SQL><br /><br />to reset original password you will user above command as<br /><br />ALTER USER OASTOLTP IDENTIFIED BY 'you will get encrypted password of OASTOLTP' default tablespace ........Anand Chavanhttp://www.blogger.com/profile/15193918016445917466noreply@blogger.com0tag:blogger.com,1999:blog-5160782529892769998.post-76317700897356420282011-05-30T03:48:00.000-07:002011-05-30T03:49:28.452-07:00Temporary tablespace usage for each userCOLUMN tablespace FORMAT A20 <br /><br />COLUMN temp_size FORMAT A20 <br /><br />COLUMN sid_serial FORMAT A20 <br /><br />COLUMN username FORMAT A20 <br /><br />COLUMN program FORMAT A40 <br /><br />SET LINESIZE 200 <br /><br /><br /><br />SELECT b.tablespace, <br /><br />ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size, <br /><br />a.sid||','||a.serial# AS sid_serial, <br /><br />NVL(a.username, '(oracle)') AS username, <br /><br />a.program <br /><br />FROM v$session a, <br /><br />v$sort_usage b, <br /><br />v$parameter p <br /><br />WHERE p.name = 'db_block_size' <br /><br />AND a.saddr = b.session_addr <br /><br />ORDER BY b.tablespace, b.blocks;Anand Chavanhttp://www.blogger.com/profile/15193918016445917466noreply@blogger.com0tag:blogger.com,1999:blog-5160782529892769998.post-81717043613895450522011-04-10T01:06:00.000-07:002011-04-10T01:08:02.178-07:00Oracle RAC: restoring lost OCR fileShutdown everything oracle instances then run<br /><br />- rootdelete.sh <br />- root.sh this will create OCR<br /> <br /><br />Add resource manually.Anand Chavanhttp://www.blogger.com/profile/15193918016445917466noreply@blogger.com0tag:blogger.com,1999:blog-5160782529892769998.post-47820003590696633652010-09-14T23:25:00.000-07:002010-09-14T23:30:47.477-07:00Silent mode Oracle RAC installationInstall Oracle clusterware and database software using response file as below.<br />------------------------------------------------------------------------------<br /><br /><br />1. To create a response file, start the OUI with the following command and perform an installation as normal.<br /><br /> ./runInstaller -record -destinationFile /tmp/10gR2.rsp<br /><br />2. A silent installation is initiated using the following command<br /><br /><br /> ./runInstaller -silent -responseFile /tmp/10gR2.rsp<br /><br /><br /><br />Database creation using silent mode.<br />------------------------------------<br /><br /><br />dbca -createDatabase -silent -templateName General_Purpose.dbc -gdbName dtrac -sysPassword vcs -systemPassword vcs -emConfiguration NONE -disableSecurityConfiguration ALL -datafileDestination /oradata -storageType CFS -nodelist node1,node2<br /><br /><br />You could get template information from: <br /><br />$ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbcAnand Chavanhttp://www.blogger.com/profile/15193918016445917466noreply@blogger.com0tag:blogger.com,1999:blog-5160782529892769998.post-36292828094941173052010-09-13T06:07:00.001-07:002010-09-13T06:07:34.717-07:00How can the ASM instance start when the spfile is located on a diskgroupSQL> show parameter pfile<br /> <br />NAME TYPE VALUE<br />------------------------------------ ----------- ------------------------------<br />spfile string +DATA/asm/asmparameterfile/registry.253.729008643<br /><br />The location of the initialization parameter file specified Grid Plug and Play.<br />However in the GPnP file, the OLR (Oracle Local Registry) and the OCR (Oracle Cluster Registry) they have the entries to identify the DISCOVERY String and the spfile location.<br />So ASM can startup but will then access the SPFILE on the ASM diskgroup.<br /><br />At startup, the Oracle ASM instance attempts to mount the following disk groups:<br />* Disk groups specified in the ASM_DISKGROUPS initialization parameter<br />* Disk group used by Cluster Synchronization Services (CSS) for voting files<br />* Disk groups used by Oracle Clusterware for Oracle Cluster Registry (OCR)<br />* Disk group used by the Oracle ASM instance to store the ASM server parameter file (SPFILE)Anand Chavanhttp://www.blogger.com/profile/15193918016445917466noreply@blogger.com0tag:blogger.com,1999:blog-5160782529892769998.post-91331902313542365282010-08-31T22:58:00.000-07:002010-08-31T22:59:06.284-07:00Monitor user session.set linesize 220 <br />col first_load_time format a20 <br />col client_info format a20 <br />col username format a20 <br />col last_load_time format a20 <br />col sql_text format a30 <br />select a.sid,a.serial#,a.client_info,a.username,a.logon_time,a.status,b.sql_text,a.last_call_et,b.first_load_time,b.last_load_time <br />from v$session a ,v$sql b <br />where a.sql_id=b.sql_id(+) <br />and a.sid='&sid'Anand Chavanhttp://www.blogger.com/profile/15193918016445917466noreply@blogger.com0tag:blogger.com,1999:blog-5160782529892769998.post-34208914994545495092010-05-26T02:42:00.000-07:002012-10-28T23:21:10.045-07:00OS Watcher (OSW<div dir="ltr" style="text-align: left;" trbidi="on">
<ol>
<li>OS Watcher (OSW) is a collection of UNIX shell scripts intended to collect and archive operating system and network metrics to aid support in diagnosing performance issues.<br /><br />It collected statastics for following commnad<br />ps<br />top<br />mpstat<br />iostat<br />netstat<br />traceroute<br />vmstat<br /><br />Steps to use it.<br />1. Download it from oracle metalink 301137.1<br />2. How to install it-<br />tar -xvf osw212.tar<br />3. How to start it<br />./startOSW.sh 60 1<br />where<br />ARG1 = snapshot interval in seconds.<br />ARG2 = the number of hours of archive data to store.<br />4. More details given<br />https://metalink2.oracle.com/metalink/plsql/docs/OSW_UG.htm </li>
</ol>
</div>
Anand Chavanhttp://www.blogger.com/profile/15193918016445917466noreply@blogger.com0tag:blogger.com,1999:blog-5160782529892769998.post-83990746506735895162010-04-06T03:08:00.000-07:002010-04-06T03:10:04.997-07:00Get DDL command from SQL queryselect dbms_metadata.get_ddl('TABLE','table name') FROM DUAL;<br /><br />e.g:<br /><br />select dbms_metadata.get_ddl('TABLE','ORDR') FROM DUAL;Anand Chavanhttp://www.blogger.com/profile/15193918016445917466noreply@blogger.com0tag:blogger.com,1999:blog-5160782529892769998.post-33264212029917680822010-04-06T03:06:00.001-07:002010-04-06T03:06:59.342-07:00View Free space from temporary tablespaceselect substr(a.tablespace_name,1,20) "TS_name", substr(a.file_name,1,35) "File_Name",<br />round(sum(a.bytes)/1024/1024,2) "Size_MB",sum(NVL(b.bytes,0)) "FreeSpace" from dba_TEMP_files a,<br />(select substr(tablespace_name,1,20) tablespace_name,file_id, round(sum(bytes_used)/1024/1024,2) used_sp,<br />round(sum(blocks_free * 8192)/1024/1024,2) bytes from v$temp_space_header group by substr(tablespace_name,1,20),file_id ) b where<br />a.tablespace_name=b.tablespace_name(+) and a.file_id=b.file_id(+) group by<br />substr(a.tablespace_name,1,20), substr(a.file_name,1,35)<br />/Anand Chavanhttp://www.blogger.com/profile/15193918016445917466noreply@blogger.com0tag:blogger.com,1999:blog-5160782529892769998.post-70352411522047130092010-04-06T03:04:00.000-07:002010-04-06T03:05:01.062-07:00View database size from sqlCOLUMN "Total Mb" FORMAT 999,999,999.0<br />COLUMN "Redo Mb" FORMAT 999,999,999.0<br />COLUMN "Temp Mb" FORMAT 999,999,999.0<br />COLUMN "Data Mb" FORMAT 999,999,999.0<br /><br />Prompt<br />Prompt "Database Size"<br /><br />select (select sum(bytes/1048576) from dba_data_files) "Data Mb", <br />(select NVL(sum(bytes/1048576),0) from dba_temp_files) "Temp Mb",<br />(select sum(bytes/1048576)*max(members) from v$log) "Redo Mb",<br />(select sum(bytes/1048576) from dba_data_files) +<br />(select NVL(sum(bytes/1048576),0) from dba_temp_files) +<br />(select sum(bytes/1048576)*max(members) from v$log) "Total Mb"<br />from dual;Anand Chavanhttp://www.blogger.com/profile/15193918016445917466noreply@blogger.com0tag:blogger.com,1999:blog-5160782529892769998.post-90612882781611338962010-04-05T23:50:00.000-07:002010-04-05T23:54:15.249-07:00Migrate OCR and Vote disk on ASM storageStoring the OCR and vote disk inside ASM eliminates the need for third party cluster and volume managers and also helps eliminate the complexity of managing separate disk partitions for the OCR in oracle clusterware installations<br /><br />Steps to migrate OCR and Vote disk on ASM storage:<br /><br />1. Create ASM diskgroup using ASMCA<br /><br /><br />2. Voting disk on raw before migration<br /><br /> (root@mysystem) /oracle/product/grid/bin/crsctl query css votedisk<br /> ## STATE File Universal Id File Name Disk group<br /> -- ----- ----------------- --------- ---------<br /> 1. ONLINE 63b1d7539d25ef7affff433cbdc40f28 (/dev/vx/rdsk/ocrvotedg/votevol) []<br /> Located 1 voting disk(s).<br /><br /><br />3. Relpacing votedisk from raw to ASM<br /> (root@mysystem) /oracle/product/grid/bin/crsctl replace votedisk +ocrdg<br /><br />4. Vote disk on ASM after migration<br />===================================<br /> (root@mysystem) /oracle/product/grid/bin/crsctl query css votedisk<br /> ## STATE File Universal Id File Name Disk group<br /> -- ----- ----------------- --------- ---------<br /> 1. ONLINE 96c7f0f903174fd1bf15432ad5c6dee6 (/dev/vx/rdsk/asmdg/crsvol1) [OCRDG]<br /> 2. ONLINE 14e557f987874f36bf1f116c8a51ba6c (/dev/vx/rdsk/asmdg/crsvol2) [OCRDG]<br /> 3. ONLINE 0b1961148b674f00bf64b21ea4e6a126 (/dev/vx/rdsk/asmdg/crsvol3) [OCRDG]<br /> Located 3 voting disk(s).<br /><br /><br />5. View existing OCR configuration before Migration<br /> (root@mysystem)[/] /oracle/11.2.0/gridhome/bin/ocrcheck<br /> Status of Oracle Cluster Registry is as follows :<br /> Version : 3<br /> Total space (kbytes) : 302956<br /> Used space (kbytes) : 5820<br /> Available space (kbytes) : 297136<br /> ID : 1087500263<br /> Device/File Name : /dev/vx/rdsk/ocrvotedg/ocrvol<br /> Cluster registry integrity check succeeded<br /><br />6. ADD new OCR by providing asm diskgroup<br /> ( root@mysystem)[/] /oracle/11.2.0/gridhome/bin/ocrconfig -add +ocrdg<br /><br />7. OCRCHECK after adding ASMDG for OCR<br />=========================================<br /> (root@mysystem)[/] /oracle/11.2.0/gridhome/bin/ocrcheck<br /> Status of Oracle Cluster Registry is as follows :<br /> Version : 3<br /> Total space (kbytes) : 302956<br /> Used space (kbytes) : 5820<br /> Available space (kbytes) : 297136<br /> ID : 1087500263<br /> Device/File Name : /dev/vx/rdsk/ocrvotedg/ocrvol<br /> Device/File integrity check succeeded<br /> Device/File Name : +ocrdg<br /> Cluster registry integrity check succeeded<br /><br /> Logical corruption check succeeded<br /><br />8. DELETING Old OCR <br /><br /> (root@mysystem)[/] /oracle/11.2.0/gridhome/bin/ocrconfig -delete /dev/vx/rdsk/ocrvotedg/ocrvol<br /><br /><br />9. OCRCHECK after deleting old OCR<br />=====================================<br /><br /> (root@mysystem)[/] /oracle/11.2.0/gridhome/bin/ocrcheck<br /> Status of Oracle Cluster Registry is as follows :<br /> Version : 3<br /> Total space (kbytes) : 302956<br /> Used space (kbytes) : 5820<br /> Available space (kbytes) : 297136<br /> ID : 1087500263<br /> Device/File Name : +ocrdg<br /> Device/File integrity check succeeded<br /> Cluster registry integrity check succeededAnand Chavanhttp://www.blogger.com/profile/15193918016445917466noreply@blogger.com1