tag:blogger.com,1999:blog-65507618990441209522024-03-06T10:25:07.395+03:00Mete Karar's WeblogYet Another DBA BlogUnknownnoreply@blogger.comBlogger43125tag:blogger.com,1999:blog-6550761899044120952.post-89055409743096221052015-09-28T23:30:00.000+03:002015-09-28T23:30:15.076+03:00Client Connection Fails to Instance on Linux<div style="text-align: justify;">
I faced with a problem which clients were failing to connect to a DB2 instance on linux. To check it out I logged on to the system as root and tried to "su" as instance user and got the following error message:</div>
<br />
<span style="font-family: Courier New, Courier, monospace;">$ su - ctginst1</span><br />
<span style="color: red; font-family: Courier New, Courier, monospace;">"cannot set user id: Resource temporarily unavailable" while trying to login or su as a local user in Red Hat Enterprise Linux</span><br />
<br />
<div style="text-align: justify;">
Issue was the user has reached the limit of maximum number of running processes which is set by nproc limit.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
The resolution is change the limit for the user. In Red Hat 6 and above add a line for the user with higher limits as follow, mind the newly added last line:</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<span style="font-family: Courier New, Courier, monospace;">$ vi /etc/security/limits.d/90-nproc.conf </span></div>
<div style="text-align: justify;">
<span style="font-family: Courier New, Courier, monospace;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: Courier New, Courier, monospace;"># Default limit for number of user's processes to prevent</span></div>
<div style="text-align: justify;">
<span style="font-family: Courier New, Courier, monospace;"># accidental fork bombs.</span></div>
<div style="text-align: justify;">
<span style="font-family: Courier New, Courier, monospace;"># See rhbz #432903 for reasoning.</span></div>
<div style="text-align: justify;">
<span style="font-family: Courier New, Courier, monospace;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: Courier New, Courier, monospace;">* soft nproc 1024</span></div>
<div style="text-align: justify;">
<span style="font-family: Courier New, Courier, monospace;">ctginst1 soft nproc 65536 </span></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Immediately after, problem is resolved. </div>
<div style="text-align: justify;">
<br /></div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6550761899044120952.post-73822820301180489642015-08-24T19:49:00.001+03:002015-08-24T19:49:37.267+03:00Adding Agent Software to 12c Cluod Control<div style="text-align: justify;">
When you install 12c Cloud Control (you can find <a href="http://metekarar.blogspot.com.tr/2015/08/installing-12c-cloud-control-r5.html" target="_blank">how to install 12c Cloud Control</a> in my previous post) it comes with its default agent which depends on your environment. If you install it on a Linux 64-bit box, you get only Linux x86-64 agent. Therefore you need to get agents for multi-OS environment which requires 4 easy steps:</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
1. Log on to Enterprise manager as sysman or an administrator user, from Setup menu on the right corner move to My Oracle Support --> Set Credentials to access Oracle Support.</div>
<div style="text-align: justify;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-kmQCOLKf5MM/VdnE1Jqvw1I/AAAAAAAAAdc/WMTTW3hQ664/s1600/12cR5_Agent_1.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="73" src="http://4.bp.blogspot.com/-kmQCOLKf5MM/VdnE1Jqvw1I/AAAAAAAAAdc/WMTTW3hQ664/s400/12cR5_Agent_1.PNG" width="400" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
2. Check for updates to download from Setup --> Extensibility --> Self Update</div>
<div style="text-align: justify;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-acvC14zszU0/VdnE1OKxDdI/AAAAAAAAAdc/1fSP4ohlqEI/s1600/12cR5_Agent_2.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="202" src="http://2.bp.blogspot.com/-acvC14zszU0/VdnE1OKxDdI/AAAAAAAAAdc/1fSP4ohlqEI/s400/12cR5_Agent_2.PNG" width="400" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
A job will be created to check, you can schedule it for later or run it right away. </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
3. When job succeeded, go to Setup --> Extensibility --> Self Update. Dig into Agent Software, select the ones you want to download and click download.</div>
<div style="text-align: justify;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-kcC2lqjREps/VdnE1Ge6z5I/AAAAAAAAAdc/tNVn5WfGOuc/s1600/12cR5_Agent_3.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="165" src="http://2.bp.blogspot.com/-kcC2lqjREps/VdnE1Ge6z5I/AAAAAAAAAdc/tNVn5WfGOuc/s400/12cR5_Agent_3.PNG" width="400" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Again a job will be created.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
4. When download job succeeds, go back to Agent Software page, select the one downloaded and apply it.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Now agent software is ready to deploy.</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6550761899044120952.post-7034941875234223122015-08-24T19:48:00.001+03:002015-08-24T19:48:17.999+03:00Installing 12c Cloud Control R5<div style="text-align: justify;">
To install 12 Cloud Control first thing you need is a database. You can find <a href="http://metekarar.blogspot.com.tr/2015/08/installing-12c-database-for-12c-cloud.html" target="_blank">how to install a 12c database for 12c Cloud Control</a> from my last post. After this crucial step, run the installer for Enterprise Manager as oracle (or any user you wish to use as software owner):</div>
<br />
<span style="font-family: Courier New, Courier, monospace;">$ unzip em12105_linux64_disk1.zip -d oem</span><br />
<span style="font-family: Courier New, Courier, monospace;">$ unzip em12105_linux64_disk2.zip -d oem</span><br />
<span style="font-family: Courier New, Courier, monospace;">$ unzip em12105_linux64_disk3.zip -d oem</span><br />
<div style="text-align: justify;">
<span style="font-family: Courier New, Courier, monospace;">$ cd oem</span></div>
<div style="text-align: justify;">
<span style="font-family: Courier New, Courier, monospace;">$ ./runInstaller</span></div>
<br />
<div style="text-align: justify;">
As a note, pre-install package for RDBMS 12c sets maximum number of files to 1024. However if you're installing OEM on the same server as its database, you need to increase t to 4096 as a requirement of 12c Cloud Control. Otherwise you may get a failure during prerequisite checks step of installation. To achieve this requirement, as root open /etc/security/limits.conf file and edit the necessary lines as follows:</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
# oracle-rdbms-server-11gR2-preinstall setting for nofile soft limit is 1024</div>
<div style="text-align: justify;">
# oracle soft nofile 1024</div>
<div style="text-align: justify;">
oracle soft nofile 4096</div>
<div style="text-align: justify;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-gN--dBo0Z5M/Vdm0EeSfcZI/AAAAAAAAAb8/R4O5E7tlolU/s1600/12cR5_1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://3.bp.blogspot.com/-gN--dBo0Z5M/Vdm0EeSfcZI/AAAAAAAAAb8/R4O5E7tlolU/s400/12cR5_1.png" width="400" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-BWgbhcqmweI/Vdm0EQnsvlI/AAAAAAAAAb8/ca_XtCYXQKI/s1600/12cR5_2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://3.bp.blogspot.com/-BWgbhcqmweI/Vdm0EQnsvlI/AAAAAAAAAb8/ca_XtCYXQKI/s400/12cR5_2.png" width="400" /></a></div>
<br />
I'll choose to install in Simple mode and keep settings and preferences as default. You can move on with advanced type to customize configuration.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-2CCorfkAAK8/Vdm0EYzH5EI/AAAAAAAAAb8/LULqurd0y4s/s1600/12cR5_3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://3.bp.blogspot.com/-2CCorfkAAK8/Vdm0EYzH5EI/AAAAAAAAAb8/LULqurd0y4s/s400/12cR5_3.png" width="400" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-QY7Qqy87ymM/Vdm0EYpd6vI/AAAAAAAAAb8/PV252r9tMYU/s1600/12cR5_4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://3.bp.blogspot.com/-QY7Qqy87ymM/Vdm0EYpd6vI/AAAAAAAAAb8/PV252r9tMYU/s400/12cR5_4.png" width="400" /></a></div>
<br />
Set database connection parameters.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-5Gtintk9a1U/Vdm0EfnjdjI/AAAAAAAAAb8/di-_58fXb6g/s1600/12cR5_5.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://1.bp.blogspot.com/-5Gtintk9a1U/Vdm0EfnjdjI/AAAAAAAAAb8/di-_58fXb6g/s400/12cR5_5.png" width="400" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-mtQb-vLU2yE/Vdm0Ecn2XHI/AAAAAAAAAb8/NPMtcFfNxVE/s1600/12cR5_6.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://1.bp.blogspot.com/-mtQb-vLU2yE/Vdm0Ecn2XHI/AAAAAAAAAb8/NPMtcFfNxVE/s400/12cR5_6.png" width="400" /></a></div>
<br />
Run root script<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-lLa4qdhOk_U/Vdm0EQWd_aI/AAAAAAAAAb8/FIm3Hqd2ggU/s1600/12cR5_7.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="283" src="http://3.bp.blogspot.com/-lLa4qdhOk_U/Vdm0EQWd_aI/AAAAAAAAAb8/FIm3Hqd2ggU/s400/12cR5_7.png" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-vazarsJ7ukA/VdnB_wmbhSI/AAAAAAAAAcw/GsJyXrff-8E/s1600/12cR5_8.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="327" src="http://3.bp.blogspot.com/-vazarsJ7ukA/VdnB_wmbhSI/AAAAAAAAAcw/GsJyXrff-8E/s400/12cR5_8.png" width="400" /></a></div>
<br />
On the summary screen you may find URLs to access Enterprise Manager.<br />
<br />
Next step is deploying agents...<br />
<br />Unknownnoreply@blogger.com2tag:blogger.com,1999:blog-6550761899044120952.post-17413471730162049082015-08-24T19:44:00.000+03:002015-08-24T19:44:06.287+03:00Installing 12c Database for 12c Cloud Control<div style="text-align: justify;">
As you know, you need a database for 12c Cloud Control as management repository. Since 12c is out there for a while and we are not upgrading our 11gR2 databases to 12c yet, I think it's a good idea to use 12c for this purpose and get custom with it. As a reminder, Oracle says it's free to install a database if you use it for OEM purposes only. So, let's begin.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
We'll install 12.1.0.2.4 database on Oracle Linux 6.6</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
First thing to do, as a requirement, is installing oracle-rdbms-server-12cR1-preinstall package. This package makes the whole process pretty easier. I can say this is first -and frankly the second- reason I choose to use Oracle Linux.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
As root:</div>
<div style="text-align: justify;">
<span style="font-family: Courier New, Courier, monospace;">$ yum install oracle-rdbms-server-12cR1-preinstall</span></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Then create necessary directory for installation and grant privileges:</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<span style="font-family: Courier New, Courier, monospace;">$ mkdir /u01</span></div>
<div style="text-align: justify;">
<span style="font-family: Courier New, Courier, monospace;">$ chown -R oracle:oinstall /u01</span></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Do not forget to set password for oracle user. Then log on as oracle and start installation. Unzip installation files an run the installer:</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<span style="font-family: Courier New, Courier, monospace;">$ unzip linuxamd64_12102_database_1of2.zip</span></div>
<div style="text-align: justify;">
<span style="font-family: Courier New, Courier, monospace;">$ unzip linuxamd64_12102_database_2of2.zip</span></div>
<div style="text-align: justify;">
<span style="font-family: Courier New, Courier, monospace;">$ cd database</span></div>
<div style="text-align: justify;">
<span style="font-family: Courier New, Courier, monospace;">$ ./runInstaller</span></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-1VjVSp1Iu5s/VdmzrHDSXqI/AAAAAAAAAXg/cSHFSs6Obd8/s1600/12cDB_1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://3.bp.blogspot.com/-1VjVSp1Iu5s/VdmzrHDSXqI/AAAAAAAAAXg/cSHFSs6Obd8/s400/12cDB_1.png" width="400" /></a></div>
<br />
Choose to install software only. It'll be a single instance.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-KhfWvnwUByU/Vdmz8bsGTwI/AAAAAAAAAaw/56EIZcL1iIc/s1600/12cDB_2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://2.bp.blogspot.com/-KhfWvnwUByU/Vdmz8bsGTwI/AAAAAAAAAaw/56EIZcL1iIc/s400/12cDB_2.png" width="400" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-4CCRptN3mc4/Vdmz8bb7BqI/AAAAAAAAAcA/P1nlegv0Oz0/s1600/12cDB_3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://1.bp.blogspot.com/-4CCRptN3mc4/Vdmz8bb7BqI/AAAAAAAAAcA/P1nlegv0Oz0/s400/12cDB_3.png" width="400" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-QWtN6BVyQJk/Vdmz8TSITaI/AAAAAAAAAaw/BXLGwkiXhZQ/s1600/12cDB_4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://2.bp.blogspot.com/-QWtN6BVyQJk/Vdmz8TSITaI/AAAAAAAAAaw/BXLGwkiXhZQ/s400/12cDB_4.png" width="400" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-AcTkcBmZ3e0/Vdmz8YuFt0I/AAAAAAAAAcA/NqUmFN1koAs/s1600/12cDB_5.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://3.bp.blogspot.com/-AcTkcBmZ3e0/Vdmz8YuFt0I/AAAAAAAAAcA/NqUmFN1koAs/s400/12cDB_5.png" width="400" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-c0JxKuT-Mfc/Vdmz8SR9jOI/AAAAAAAAAaw/cZ269VaVdac/s1600/12cDB_6.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://3.bp.blogspot.com/-c0JxKuT-Mfc/Vdmz8SR9jOI/AAAAAAAAAaw/cZ269VaVdac/s400/12cDB_6.png" width="400" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-NTCDxkVxzyg/Vdmz8apXCqI/AAAAAAAAAcA/0ASGC97uxGM/s1600/12cDB_7.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://4.bp.blogspot.com/-NTCDxkVxzyg/Vdmz8apXCqI/AAAAAAAAAcA/0ASGC97uxGM/s400/12cDB_7.png" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-TYKbBPeBBg4/Vdmz8WcI8JI/AAAAAAAAAcA/vS7_K4JSMc4/s1600/12cDB_8.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://3.bp.blogspot.com/-TYKbBPeBBg4/Vdmz8WcI8JI/AAAAAAAAAcA/vS7_K4JSMc4/s400/12cDB_8.png" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
Run root scripts as root<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-cbOimJyHPtE/Vdmz8QolMaI/AAAAAAAAAcA/7koeGr9oWPM/s1600/12cDB_9.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="302" src="http://3.bp.blogspot.com/-cbOimJyHPtE/Vdmz8QolMaI/AAAAAAAAAcA/7koeGr9oWPM/s400/12cDB_9.png" width="400" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-68sRhQzEl1E/Vdmz8fOAa6I/AAAAAAAAAcA/G1ZLXgnXFlQ/s1600/12cDB_10.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://4.bp.blogspot.com/-68sRhQzEl1E/Vdmz8fOAa6I/AAAAAAAAAcA/G1ZLXgnXFlQ/s400/12cDB_10.png" width="400" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Now install the latest PSU. You can check the latest one from <a href="https://support.oracle.com/epmos/faces/DocContentDisplay?id=756671.1" target="_blank">Doc ID 756671.1</a>. Before applying PSU update OPatch first. Check patch number 6880880 for latest OPatch. PSU Installation may differ from version to version, so please refer to its documentation.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Now it's time to create a database. There're <a href="http://www.oracle.com/technetwork/oem/enterprise-manager/downloads/index.html" target="_blank">Enterprise Manager templates </a>you can download. Follow the link and download db templates zip file. Before running DBCA to create a database unzip the file you downloaded to templates directory:</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: left;">
<span style="font-family: Courier New, Courier, monospace;">$ unzip 12.1.0.2.0_Database_Template_for_EM12_1_0_5_0_Linux_x64 -d /u01/app/oracle/product/12.1.0.2/dbhome_1/assistants/dbca/templates/</span></div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: justify;">
<span style="font-family: Courier New, Courier, monospace;">$ dbca</span></div>
<div style="text-align: justify;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-Dlsxjelz8Z4/Vdmz8bk6O2I/AAAAAAAAAcA/8SO32Zg_Yc4/s1600/12cDBCA_1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://1.bp.blogspot.com/-Dlsxjelz8Z4/Vdmz8bk6O2I/AAAAAAAAAcA/8SO32Zg_Yc4/s400/12cDBCA_1.png" width="400" /></a></div>
<br />
Choose advanced mode to continue:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-LzDUpz-mt8w/Vdmz8Uh1j6I/AAAAAAAAAcA/3QZpdHjMsMg/s1600/12cDBCA_2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://1.bp.blogspot.com/-LzDUpz-mt8w/Vdmz8Uh1j6I/AAAAAAAAAcA/3QZpdHjMsMg/s400/12cDBCA_2.png" width="400" /></a></div>
<br />
Select Database Template fro EM according to your EM deployment size; small, medium or large.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-Y0tOavxfmY4/Vdmz8bsgCTI/AAAAAAAAAcA/4f43WYwITfM/s1600/12cDBCA_3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://2.bp.blogspot.com/-Y0tOavxfmY4/Vdmz8bsgCTI/AAAAAAAAAcA/4f43WYwITfM/s400/12cDBCA_3.png" width="400" /></a></div>
<br />
Set your database name<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-kLXdKvk9qeQ/Vdmz8cBLsYI/AAAAAAAAAcA/3i8NIdytHtQ/s1600/12cDBCA_4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://4.bp.blogspot.com/-kLXdKvk9qeQ/Vdmz8cBLsYI/AAAAAAAAAcA/3i8NIdytHtQ/s400/12cDBCA_4.png" width="400" /></a></div>
<br />
De-select "Configure EM Database Express"<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-ZE1WD58sTCg/Vdmz8axEKXI/AAAAAAAAAcA/-A1faHnZIEw/s1600/12cDBCA_5.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://1.bp.blogspot.com/-ZE1WD58sTCg/Vdmz8axEKXI/AAAAAAAAAcA/-A1faHnZIEw/s400/12cDBCA_5.png" width="400" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-lIq-nqubM3M/Vdmz8Rnzv5I/AAAAAAAAAcA/fBatqi2yO50/s1600/12cDBCA_6.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://4.bp.blogspot.com/-lIq-nqubM3M/Vdmz8Rnzv5I/AAAAAAAAAcA/fBatqi2yO50/s400/12cDBCA_6.png" width="400" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-rjphsPWXAtk/Vdmz8ZxWguI/AAAAAAAAAcA/_yDxfQw_TQ8/s1600/12cDBCA_7.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://1.bp.blogspot.com/-rjphsPWXAtk/Vdmz8ZxWguI/AAAAAAAAAcA/_yDxfQw_TQ8/s400/12cDBCA_7.png" width="400" /></a></div>
<br />
Set appropriate directories for database files<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-ZZjNbtfHrOE/Vdmz8YUttmI/AAAAAAAAAcA/WDFFWMkBt5w/s1600/12cDBCA_8.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://4.bp.blogspot.com/-ZZjNbtfHrOE/Vdmz8YUttmI/AAAAAAAAAcA/WDFFWMkBt5w/s400/12cDBCA_8.png" width="400" /></a></div>
<br />
Select the following script file to run. It'll set a couple of parameters:<br />
<br />
/u01/app/oracle/product/12.1.0.2/dbhome_1/assistants/dbca/templates/shpool_12.1.0.2.0_Database_SQL_for_EM12_1_0_5_0.sql<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-_z5cMbGgMt8/Vdmz8fNwqZI/AAAAAAAAAcA/1QgVN6dDPic/s1600/12cDBCA_9.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://4.bp.blogspot.com/-_z5cMbGgMt8/Vdmz8fNwqZI/AAAAAAAAAcA/1QgVN6dDPic/s400/12cDBCA_9.png" width="400" /></a></div>
<br />
Set SGA and PGA according to your server size:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-yGmGeW9x8Lc/Vdmz8Y-Sy_I/AAAAAAAAAcA/2dE-5Z4dP30/s1600/12cDBCA_10.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://3.bp.blogspot.com/-yGmGeW9x8Lc/Vdmz8Y-Sy_I/AAAAAAAAAcA/2dE-5Z4dP30/s400/12cDBCA_10.png" width="400" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-g_oPJQ1iNdg/Vdmz8cd_rHI/AAAAAAAAAcA/nesr4FoeUs4/s1600/12cDBCA_11.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://2.bp.blogspot.com/-g_oPJQ1iNdg/Vdmz8cd_rHI/AAAAAAAAAcA/nesr4FoeUs4/s400/12cDBCA_11.png" width="400" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-WsmxPJfrySQ/Vdmz8W1IDVI/AAAAAAAAAcA/GVWmOrMB_sY/s1600/12cDBCA_12.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://3.bp.blogspot.com/-WsmxPJfrySQ/Vdmz8W1IDVI/AAAAAAAAAcA/GVWmOrMB_sY/s400/12cDBCA_12.png" width="400" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-jRkONfjF7yg/Vdmz8e2S_OI/AAAAAAAAAcA/E6TG1mD3NGo/s1600/12cDBCA_13.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://3.bp.blogspot.com/-jRkONfjF7yg/Vdmz8e2S_OI/AAAAAAAAAcA/E6TG1mD3NGo/s400/12cDBCA_13.png" width="400" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-LwHnWZJ-7uk/Vdmz8elJ5DI/AAAAAAAAAcA/55rHMrfR_XM/s1600/12cDBCA_14.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://1.bp.blogspot.com/-LwHnWZJ-7uk/Vdmz8elJ5DI/AAAAAAAAAcA/55rHMrfR_XM/s400/12cDBCA_14.png" width="400" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-03y-FDi-asw/Vdmz8Z-OhTI/AAAAAAAAAcA/jkceIr5bq0g/s1600/12cDBCA_15.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://4.bp.blogspot.com/-03y-FDi-asw/Vdmz8Z-OhTI/AAAAAAAAAcA/jkceIr5bq0g/s400/12cDBCA_15.png" width="400" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-civ7frdxQH0/Vdmz8SxdH9I/AAAAAAAAAcA/RxAZufi5adw/s1600/12cDBCA_16.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://2.bp.blogspot.com/-civ7frdxQH0/Vdmz8SxdH9I/AAAAAAAAAcA/RxAZufi5adw/s400/12cDBCA_16.png" width="400" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
And your database for 12c Cloud Control is ready. On my next post, I'll be explaining 12 Cloud Control installation.</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>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6550761899044120952.post-56510155337818513132015-02-24T22:25:00.000+02:002015-02-24T22:25:18.729+02:00Connection Timeouts and DNS<div style="text-align: justify;">
On an Oracle 11.1.0.7.0 database running on AIX system, clients were complaining about connection timeouts. Tnsping was showing latency more than 1000 ms.</div>
<div style="text-align: justify;">
<br /></div>
I opened a trace for tnsping using AIX truss:<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">$ truss -aefo tnsping.trc tnsping <tns_db></span><br />
<br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: _getpid() = 30539848</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: <span style="color: red;">kopen("/etc/resolv.conf", O_RDONLY)</span> = 5</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: kioctl(5, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: kioctl(5, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: kread(5, " n a m e s e r v e r 1".., 4096) = 114</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: kread(5, " n a m e s e r v e r 1".., 4096) = 0</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: statx("/etc/resolv.conf", 0x0FFFFFFFFFFF9108, 176, 0) = 0</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: close(5) = 0</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: socket(1, 1, 0) = 5</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: kfcntl(5, F_SETFD, 0x0000000000000001) = 0</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: connext(5, 0x0FFFFFFFFFFF8BD8, 1025) Err#2 ENOENT</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: close(5) = 0</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: kopen("/etc/netsvc.conf", O_RDONLY) = 5</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: kioctl(5, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: kioctl(5, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: kread(5, " # @ ( # ) 4 3 ".., 4096) = 4096</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: kread(5, " o n a n d r e s o l".., 4096) = 638</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: close(5) = 0</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: _thread_self() = 31654123</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: _thread_self() = 31654123</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: kopen("/etc/hesiod.conf", O_RDONLY) Err#2 ENOENT</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: __libc_sbrk(0x0000000000010020) = 0x00000001106A4D40</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: kopen("/etc/irs.conf", O_RDONLY) Err#2 ENOENT</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: _thread_self() = 31654123</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: getdomainname(0x09001000A02273B0, 1024) = 0</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: _thread_self() = 31654123</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: _thread_self() = 31654123</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: _thread_self() = 31654123</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: getdomainname(0x09001000A02273B0, 1024) = 0</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: _thread_self() = 31654123</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: _thread_self() = 31654123</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: _thread_self() = 31654123</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: _thread_self() = 31654123</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: _thread_self() = 31654123</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: _thread_self() = 31654123</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: _thread_self() = 31654123</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: getdomainname(0x09001000A02273B0, 1024) = 0</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: _thread_self() = 31654123</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: _thread_self() = 31654123</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: _thread_self() = 31654123</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: getdomainname(0x09001000A02273B0, 1024) = 0</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: _thread_self() = 31654123</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: _thread_self() = 31654123</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: <span style="color: red;">kopen("/etc/hosts", O_RDONLY)</span> = 5</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: kioctl(5, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: kfcntl(5, F_SETFD, 0x0000000000000001) = 0</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: kioctl(5, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: kread(5, " # @ ( # ) 4 7\t 1 . 2".., 4096) = 2018</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: kread(5, " # @ ( # ) 4 7\t 1 . 2".., 4096) = 0</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: close(5) = 0</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: kopen("/etc/hosts", O_RDONLY) = 5</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: kioctl(5, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: kfcntl(5, F_SETFD, 0x0000000000000001) = 0</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: kioctl(5, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: kread(5, " # @ ( # ) 4 7\t 1 . 2".., 4096) = 2018</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: kread(5, " # @ ( # ) 4 7\t 1 . 2".., 4096) = 0</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: socket(2, 2, 0) = 6</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: getsockopt(6, 65535, 4104, 0x0FFFFFFFFFFF7BE4, 0x0FFFFFFFFFFF7BE0) = 0</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: connext(6, 0x09001000A0167878, 16) = 0</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: _esend(6, 0x0FFFFFFFFFFF8AF0, 35, 0, 0x0000000000000000) = 35</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: _poll(0x0FFFFFFFFFFF7CB0, 1, 1000) = 1</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: _enrecvfrom(6, 0x0FFFFFFFFFFF9E20, 1024, 0, 0x0FFFFFFFFFFF8470, 0x0FFFFFFFFFFF7C98, 0x0000000000000000) = 104</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: _esend(6, 0x0FFFFFFFFFFF8AF0, 47, 0, 0x0000000000000000) = 47</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: _poll(0x0FFFFFFFFFFF7CB0, 1, 1000) = 1</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: _enrecvfrom(6, 0x0FFFFFFFFFFF9E20, 1024, 0, 0x0FFFFFFFFFFF8470, 0x0FFFFFFFFFFF7C98, 0x0000000000000000) = 122</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: close(6) = 0</span><br />
<span style="font-family: Courier New, Courier, monospace;">30539848: 31654123: close(5) = 0</span><br />
<div>
<br /></div>
<div>
<div style="text-align: justify;">
As you might notice, what tnsping and also any other Oracle Net service is doing is to check DNS server first then use local hosts file for name resolution. This is the cause of the latency because a public DNS server was set for this server and naturally there was no record for it on the DNS. Even you set hosts file as primary source in the netsvc.conf, Oracle does not care and uses DNS at first. </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
After clearing DNS configuration on the server, connection times get into order. It's also a known bug that is fixed in 11.2.0.2. So, you need to upgrade for an appropriate solution.</div>
<div style="text-align: justify;">
<br /></div>
</div>
Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-6550761899044120952.post-92031318739867785562015-02-24T22:17:00.000+02:002015-02-24T22:17:05.238+02:00Spatial Index Creation Fails with ORA-01031While re-creating a spatial index it failed as follow:<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">DROP INDEX TEST.SIX_POLYGON;</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">CREATE INDEX TEST.SIX_POLYGON ON TEST.POLYGON (GEOLOC) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS('TABLESPACE=INDEX_TS') NOPARALLEL;</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine</span><br />
<span style="font-family: Courier New, Courier, monospace;">ORA-13249: internal error in Spatial index: [mdidxrbd]</span><br />
<span style="font-family: Courier New, Courier, monospace;">ORA-13249: Error in Spatial index: index build failed</span><br />
<span style="font-family: Courier New, Courier, monospace;">ORA-13249: Error in R-tree: [mdrcrtscrt]</span><br />
<span style="font-family: Courier New, Courier, monospace;">ORA-13231: failed to create index table [MDRS_1EEB04$] during R-tree creation</span><br />
<span style="font-family: Courier New, Courier, monospace;">ORA-13249: Stmt-Execute Failure: CREATE SEQUENCE "TEST".MDRS_1EEB04$ ORDER START WITH 1 CACHE 100</span><br />
<span style="font-family: Courier New, Courier, monospace;">ORA-29400: data cartridge error</span><br />
<span style="font-family: Courier New, Courier, monospace;"><span style="color: red;">ORA-01031</span>: insufficient privileges</span><br />
<span style="font-family: Courier New, Courier, monospace;"><span style="color: red;">ORA-06512</span>: at "MDSYS.SDO_INDEX_METHOD_10I", line 10</span><br />
<br />
After some digging in, I found the missing privilege: create sequence. You can check if the user has required rights by:<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">select privilege</span><br />
<span style="font-family: Courier New, Courier, monospace;">from DBA_SYS_PRIVS</span><br />
<span style="font-family: Courier New, Courier, monospace;">where privilege in ('CREATE TABLE', 'CREATE SEQUENCE' )</span><br />
<span style="font-family: Courier New, Courier, monospace;">and grantee = 'TEST';</span><br />
<br />
All needed is to grant create sequence to the target user before creating index. Afterwards you can revoke the right:<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">GRANT CREATE SEQUENCE TO TEST;</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">CREATE INDEX TEST.SIX_POLYGON ON TEST.POLYGON (GEOLOC) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS('TABLESPACE=INDEX_TS') NOPARALLEL;</span><br />
<div>
<span style="font-family: Courier New, Courier, monospace;"><br /></span></div>
<span style="font-family: Courier New, Courier, monospace;">REVOKE CREATE SEQUENCE FROM TEST;</span>Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-6550761899044120952.post-78126720167977184862015-01-18T18:21:00.000+02:002015-01-18T18:21:58.314+02:00Upgrade Failed with PRKO-3226<div style="text-align: justify;">
It's been quite a while since my latest post. Let me get back on track with this short one.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
During an upgrade of 10.2.0.5 two node RAC -CRS and ASM- to 11.2.0.4, rootupgrade.sh script failed with some errors on the second node. I corrected them and re-run the script a couple of times. At last rootupgrade.sh ended with the following:</div>
<br />
<span style="font-family: Courier New, Courier, monospace;"><span style="color: red;">PRKO-3226</span> : Upgrade from version 11.2.0.4.0 to version 11.2.0.4.0 using srvctl upgrade model command of version 11.2.0.4.0 is not supported</span><br />
<br />
When it's queried, CRS's version seems upgraded:<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">$ crsctl query crs softwareversion</span><br />
<span style="font-family: Courier New, Courier, monospace;">Oracle Clusterware version on node [node2] is [11.2.0.4.0]</span><br />
<br />
<div style="text-align: justify;">
To be sure, I opened an SR on the issue. Oracle Support replied by stating that it's totally a misleading error since the node already has been upgraded and it's safe to continue. So I ignored the error and didn't re-run rootupgrade.sh, continued through UI and completed the process successfully.</div>
<div style="text-align: justify;">
<br /></div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6550761899044120952.post-71263207142680557482014-04-10T01:34:00.001+03:002014-04-10T01:34:02.831+03:00Exadata and iPhoneI never thought one day I will find a similarity between Oracle Exadata and iPhone. But it was before <a href="https://blogs.oracle.com/datawarehousing/entry/limited_edition_exadata_x4_2c" target="_blank">Oracle announced X4-2C</a>, the colorful Exadata:)<br />
<br />
<span style="font-family: "Calibri","sans-serif"; font-size: 12.0pt; mso-ansi-language: TR; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: TR; mso-fareast-theme-font: minor-latin;"><img alt="X4 2C" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgXDkIC4aoZuetUW0fSwdruCNCOUUkmdYlCQk8jf5RUtux3xN6AwaCIzoneIX3vbNgMjlGk1rpUad87huf9rZPAMdfz274rr_q7Ea95OwHM4p0HAdDBoT8YES3y472eJEEdQWGWlgfSPtk/?imgmax=800" height="344" id="_x0000_i1025" style="display: block; margin-left: auto; margin-right: auto;" title="X4-2C.jpg" width="640" /></span><br />
<span style="font-family: "Calibri","sans-serif"; font-size: 12.0pt; mso-ansi-language: TR; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: TR; mso-fareast-theme-font: minor-latin;"><br /></span>
<img src="http://assets.sbnation.com/assets/3197603/iphone5c-gallery2-2013.jpeg" height="384" width="640" /><br />
However, I don't think that X4-2C is cheaper as iPhone 5C is, since it's a limited edition...Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-6550761899044120952.post-79664483915276426092014-04-10T01:13:00.002+03:002014-04-10T01:13:48.183+03:00Data Redaction in Oracle 12c and 11gR2<div style="text-align: justify;">
Data Redaction is one of the new features of 12c actually, and also it's become available in 11gR2 with 11.2.0.4. Data Redaction is in Advanced Security option of enterprise edition.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
What Data Redaction does is basically masking the data on the fly based on the type and expression given. Let's do a demonstration:</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
First thing to do is to create a policy. Policies are created on tables and a table can have only one policy. If you try to add a policy to table with a policy, you get ORA-28069. Policy can be defined for only a single column at a time but you can add columns to the policy by altering it.</div>
<br />
<span style="font-family: Courier New, Courier, monospace;">begin</span><br />
<span style="font-family: Courier New, Courier, monospace;"> dbms_redact.add_policy (</span><br />
<span style="font-family: Courier New, Courier, monospace;"> object_schema => 'DEMO',</span><br />
<span style="font-family: Courier New, Courier, monospace;"> object_name => 'CUSTOMERS',</span><br />
<span style="font-family: Courier New, Courier, monospace;"> column_name => 'CNAME', </span><br />
<span style="font-family: Courier New, Courier, monospace;"> policy_name => 'customers_pol',</span><br />
<span style="font-family: Courier New, Courier, monospace;"> function_type => DBMS_REDACT.REGEXP,</span><br />
<span style="font-family: Courier New, Courier, monospace;"> regexp_pattern => '(\S{3})(\S+)',</span><br />
<span style="font-family: Courier New, Courier, monospace;"> regexp_replace_string => '\1***', </span><br />
<span style="font-family: Courier New, Courier, monospace;"> expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''SYS'''</span><br />
<span style="font-family: Courier New, Courier, monospace;"> );</span><br />
<span style="font-family: Courier New, Courier, monospace;">end;</span><br />
<span style="font-family: Courier New, Courier, monospace;">/</span><br />
<br />
<div style="text-align: justify;">
Above command creates a policy on table CUSTOMERS in schema DEMO and it masks column CNAME by using regular expressions. Masked values will contain first 3 letters of the name (each name if customer has a middle name) and 3 "*"s.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
There are 6 types of redaction; full, partial, regexp, random and none. You can find detail information in <a href="http://docs.oracle.com/cd/E11882_01/network.112/e40393/redaction_config.htm#ASOAG10490" target="_blank">Oracle documentation</a>. Please not the expression parameter. What we're saying here is apply this policy to users who are not sys. By default polices applied against users except sys and object owner. So if you also want table owner gets masked values or you want to redact data based on application, such an expression must be used. Expression is a mandatory parameter, you can set it as '1=1' if you have no rule to apply. </div>
<br />
<span style="font-family: Courier New, Courier, monospace;">begin</span><br />
<span style="font-family: Courier New, Courier, monospace;"> dbms_redact.alter_policy (</span><br />
<span style="font-family: Courier New, Courier, monospace;"> object_schema => 'DEMO',</span><br />
<span style="font-family: Courier New, Courier, monospace;"> object_name => 'CUSTOMERS', </span><br />
<span style="font-family: Courier New, Courier, monospace;"> policy_name => 'customers_pol',</span><br />
<span style="font-family: Courier New, Courier, monospace;"> action => DBMS_REDACT.ADD_COLUMN,</span><br />
<span style="font-family: Courier New, Courier, monospace;"> column_name => 'DOB',</span><br />
<span style="font-family: Courier New, Courier, monospace;"> function_type => DBMS_REDACT.PARTIAL,</span><br />
<span style="font-family: Courier New, Courier, monospace;"> function_parameters => DBMS_REDACT.REDACT_DATE_EPOCH,</span><br />
<span style="font-family: Courier New, Courier, monospace;"> expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''SYS''' </span><br />
<span style="font-family: Courier New, Courier, monospace;"> );</span><br />
<span style="font-family: Courier New, Courier, monospace;">end;</span><br />
<span style="font-family: Courier New, Courier, monospace;">/</span><br />
<br />
<div style="text-align: justify;">
By altering the policy we've created, we added another column of the table to be masked. Here we used built-in redaction function. This function set all date values to 01-Jan-1970. There are couple of more pre-defined functions such for SSN, e-mail zip code.</div>
<br />
<span style="font-family: Courier New, Courier, monospace;">begin</span><br />
<span style="font-family: Courier New, Courier, monospace;"> dbms_redact.alter_policy (</span><br />
<span style="font-family: Courier New, Courier, monospace;"> object_schema => 'DEMO',</span><br />
<span style="font-family: Courier New, Courier, monospace;"> object_name => 'CUSTOMERS', </span><br />
<span style="font-family: Courier New, Courier, monospace;"> policy_name => 'customers_pol',</span><br />
<span style="font-family: Courier New, Courier, monospace;"> action => DBMS_REDACT.ADD_COLUMN,</span><br />
<span style="font-family: Courier New, Courier, monospace;"> column_name => 'CID',</span><br />
<span style="font-family: Courier New, Courier, monospace;"> function_type => DBMS_REDACT.FULL, </span><br />
<span style="font-family: Courier New, Courier, monospace;"> expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''SYS''' </span><br />
<span style="font-family: Courier New, Courier, monospace;"> );</span><br />
<span style="font-family: Courier New, Courier, monospace;">end;</span><br />
<span style="font-family: Courier New, Courier, monospace;">/</span><br />
<br />
<div style="text-align: justify;">
This time we add CID column to the policy and redacted it fully. So masked values will be displayed as 0. To change a columns redaction type, alter policy again by setting altering action to MODIFY_COLUMN:</div>
<br />
<span style="font-family: Courier New, Courier, monospace;">begin</span><br />
<span style="font-family: Courier New, Courier, monospace;"> dbms_redact.alter_policy (</span><br />
<span style="font-family: Courier New, Courier, monospace;"> object_schema => 'DEMO',</span><br />
<span style="font-family: Courier New, Courier, monospace;"> object_name => 'CUSTOMERS', </span><br />
<span style="font-family: Courier New, Courier, monospace;"> policy_name => 'customers_pol',</span><br />
<span style="font-family: Courier New, Courier, monospace;"> action => DBMS_REDACT.MODIFY_COLUMN,</span><br />
<span style="font-family: Courier New, Courier, monospace;"> column_name => 'CID',</span><br />
<span style="font-family: Courier New, Courier, monospace;"> function_type => DBMS_REDACT.RANDOM, </span><br />
<span style="font-family: Courier New, Courier, monospace;"> expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''SYS''' </span><br />
<span style="font-family: Courier New, Courier, monospace;"> );</span><br />
<span style="font-family: Courier New, Courier, monospace;">end;</span><br />
<span style="font-family: Courier New, Courier, monospace;">/</span><br />
<br />
Now, we set it to use random masking, random values will be generated for the column. Redacted query output is as below at the end:<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">SQL> select cid, cname, dob from demo.customers;</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">CID<span class="Apple-tab-span" style="white-space: pre;"> </span>CNAME<span class="Apple-tab-span" style="white-space: pre;"> </span>DOB</span><br />
<span style="font-family: Courier New, Courier, monospace;">-----------------------------------------</span><br />
<span style="font-family: Courier New, Courier, monospace;">1311680984<span class="Apple-tab-span" style="white-space: pre;"> </span>ABD*** AYD***<span class="Apple-tab-span" style="white-space: pre;"> </span>01-JAN-70</span><br />
<span style="font-family: Courier New, Courier, monospace;">102691765<span class="Apple-tab-span" style="white-space: pre;"> </span>AHM*** YIL***<span class="Apple-tab-span" style="white-space: pre;"> </span>01-JAN-70</span><br />
<span style="font-family: Courier New, Courier, monospace;">819107024<span class="Apple-tab-span" style="white-space: pre;"> </span>ARI*** DEM***<span class="Apple-tab-span" style="white-space: pre;"> </span>01-JAN-70</span><br />
<span style="font-family: Courier New, Courier, monospace;">7285271581<span class="Apple-tab-span" style="white-space: pre;"> </span>AYD*** TUR***<span class="Apple-tab-span" style="white-space: pre;"> </span>01-JAN-70</span><br />
<span style="font-family: Courier New, Courier, monospace;">22688323660<span class="Apple-tab-span" style="white-space: pre;"> </span>AZM*** SEV***<span class="Apple-tab-span" style="white-space: pre;"> </span>01-JAN-70</span><br />
<span style="font-family: Courier New, Courier, monospace;">7508336149<span class="Apple-tab-span" style="white-space: pre;"> </span>HAC*** ELM***<span class="Apple-tab-span" style="white-space: pre;"> </span>01-JAN-70</span><br />
<span style="font-family: Courier New, Courier, monospace;">46158355970<span class="Apple-tab-span" style="white-space: pre;"> </span>HAL*** HAT***<span class="Apple-tab-span" style="white-space: pre;"> </span>01-JAN-70</span><br />
<div>
<br /></div>
<div>
Finally, if you need to drop a policy:</div>
<div>
<br /></div>
<div>
<div>
<span style="font-family: Courier New, Courier, monospace;">begin</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"> dbms_redact.drop_policy(</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"> object_schema => 'DEMO',</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"> object_name => 'CUSTOMERS',</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"> policy_name => 'customers_pol'</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"> ); </span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">end;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">/</span></div>
</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Data redaction is not a complicated way of defining security policies however what I see as a downside is one-to-one relationship between columns and policies: It is not allowed to add another policy/expression for a column, you get ORA-28060 error. So what you need is to create a rule base which has different masking types for different roles on the very same table, you need another tool. Otherwise you have practical data masking tool.</div>
Unknownnoreply@blogger.com3tag:blogger.com,1999:blog-6550761899044120952.post-84346080199391943462013-09-17T00:21:00.000+03:002013-09-17T00:21:33.234+03:00SQL Developer Hints to Format Output<div style="text-align: justify;">
SQL Developer may not be the perfect IDE, but it doesn't mean that it has not got any tricks at all. One of those features I'd like mention about can ease data transfer. SQL Developer excepts hints to format output. Here are the outputs for some of those hints; csv, loader, fixed and insert. As names implie; CSV gives rows with columns separated by commas, loader gives data formatted for SQL Loader, fixed returns columns separated by fixed spaces and insert creates insert commands.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: left;">
<span style="font-family: "Courier New",Courier,monospace;">select /*csv*/ * from scott.dept;</span></div>
<div style="text-align: left;">
<span style="font-family: "Courier New",Courier,monospace;">"DEPTNO","DNAME","LOC"<br />10,"ACCOUNTING","NEW YORK"<br />20,"RESEARCH","DALLAS"<br />30,"SALES","CHICAGO"<br />40,"OPERATIONS","BOSTON"</span></div>
<div style="text-align: left;">
<span style="font-family: "Courier New",Courier,monospace;"><br /></span></div>
<div style="text-align: left;">
<span style="font-family: "Courier New",Courier,monospace;">select /*loader*/ * from scott.dept;</span></div>
<div style="text-align: left;">
<span style="font-family: "Courier New",Courier,monospace;">10|"ACCOUNTING"|"NEW YORK"|<br />20|"RESEARCH"|"DALLAS"|<br />30|"SALES"|"CHICAGO"|<br />40|"OPERATIONS"|"BOSTON"|</span></div>
<div style="text-align: left;">
<span style="font-family: "Courier New",Courier,monospace;"><br /></span></div>
<div style="text-align: left;">
<span style="font-family: "Courier New",Courier,monospace;">select /*fixed*/ * from scott.dept;</span></div>
<div style="text-align: left;">
<span style="font-family: "Courier New",Courier,monospace;">"DEPTNO" "DNAME" "LOC" <br />"10" "ACCOUNTING" "NEW YORK" <br />"20" "RESEARCH" "DALLAS" <br />"30" "SALES" "CHICAGO" <br />"40" "OPERATIONS" "BOSTON" </span></div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
<span style="font-family: "Courier New",Courier,monospace;">select /*insert*/ * from scott.dept;<br />REM INSERTING into scott.dept<br />SET DEFINE OFF;<br />Insert into scott.dept (DEPTNO,DNAME,LOC) values (10,'ACCOUNTING','NEW YORK');<br />Insert into scott.dept (DEPTNO,DNAME,LOC) values (20,'RESEARCH','DALLAS');<br />Insert into scott.dept (DEPTNO,DNAME,LOC) values (30,'SALES','CHICAGO');<br />Insert into scott.dept (DEPTNO,DNAME,LOC) values (40,'OPERATIONS','BOSTON');</span></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
It's also possible to get output in html or xml format which can really be handy for a quick report. As you can see below, html format includes even a search box to make searches in content.</div>
<div style="text-align: justify;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgqSOGigq0zUz6a8ZHGg3GmrI2f434Ej6Fhkw_sWWnvufFuhXc-hE_mfJYevHRVsm9GutuszZZEvbL3nbDZrbad5oQI5ldIvnH8MSPdfVSwZ9FXhIU941T3QZwK8lA6OeVJZI9Mllx_Dtss/s1600/sql_dev_html_hint.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgqSOGigq0zUz6a8ZHGg3GmrI2f434Ej6Fhkw_sWWnvufFuhXc-hE_mfJYevHRVsm9GutuszZZEvbL3nbDZrbad5oQI5ldIvnH8MSPdfVSwZ9FXhIU941T3QZwK8lA6OeVJZI9Mllx_Dtss/s1600/sql_dev_html_hint.png" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
</div>
<div style="text-align: justify;">
Please note that hints are used in lower case and no space left between the hint and *s.</div>
Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-6550761899044120952.post-37031415045432726022013-09-05T18:17:00.000+03:002013-09-05T18:17:00.195+03:00BEA-337 Incidents on OMS 12c<div style="text-align: justify;">
Lately I noticed that free disk space on my 12c Cloud Control server was reducing for some amount. When I investigated, I've figured it out that there were daily incidents of BEA-337 errors with around 40 MB of log files in <span style="font-family: inherit;">/u01/app/Oracle/Middleware/oms12c/gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/adr/diag /ofm/GCDomain/EMGC_OMS1/incident</span><span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> </span>directory.</div>
<br />
<span style="font-family: Courier New, Courier, monospace;">Problem Key: <span style="color: red;">BEA-337 [WebLogicServer]</span></span><br />
<span style="font-family: Courier New, Courier, monospace;">Error Message Id: BEA-337</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">Description</span><br />
<span style="font-family: Courier New, Courier, monospace;">-----------</span><br />
<span style="font-family: Courier New, Courier, monospace;">Incident detected using watch rule "<span style="color: red;">StuckThread</span>":</span><br />
<span style="font-family: Courier New, Courier, monospace;">Watch ServerName: EMGC_OMS1</span><br />
<span style="font-family: Courier New, Courier, monospace;">Watch RuleType: Log</span><br />
<span style="font-family: Courier New, Courier, monospace;">Watch Rule: (SEVERITY = 'Error') AND ((MSGID = 'WL-000337') OR (MSGID = 'BEA-000337'))</span><br />
<span style="font-family: Courier New, Courier, monospace;">Watch DomainName: GCDomain</span><br />
<span style="font-family: Courier New, Courier, monospace;">Watch Data:</span><br />
<span style="font-family: Courier New, Courier, monospace;"> SERVER : EMGC_OMS1</span><br />
<span style="font-family: Courier New, Courier, monospace;"> MESSAGE : [STUCK] ExecuteThread: '7' for queue: 'weblogic.kernel.Default (self-tuning)' has been busy for "<span style="color: red;">611</span>" seconds working on the request "weblogic.servlet.internal.ServletRequestImpl@70733a9e[</span><br />
<span style="font-family: Courier New, Courier, monospace;">POST /em/websvcs/emws/ConsoleJobStepExecutorService HTTP/1.1</span><br />
<br />
<div style="text-align: justify;">
As you can see, incidents was caused due to a stuck thread watch rule. It was triggered because of "Refresh From My Oracle Support" job which is running more than 600 seconds. First thing I tried was rescheduling the job. From OEM, I navigated to jobs and found the job through advanced search by selecting ”Refresh From My Oracle Support” from the job type drop down and rescheduling it to some less busy time.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
However it didn't work out the problem. So I decided to increase the parameter controlling stuck thread watch rule which is StuckThreadMaxTime. It's default value is 600 (seconds) and my job takes 13.5 minutes to complete. </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
To alter the parameter:</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<b>1.</b> Log on to WebLogic administration console through EMGC_OMS1 target on OMS or directly navigating to https://<host>:7200/console </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<b>2.</b> Click on EMGC_OMS1 under GCDomain->Environment -> Servers. Find "Stuck Thread Max Time" setting on Configuration/Tuning tab. I've increased it to 1200 seconds to cover job duration.</div>
<div style="text-align: justify;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjmrJ5Njfyr5OIl19BS09DjaIVPJDOQLYxrYDJ8nhgQGv4HU2FM4Hz_fnEncu96lw1m8mdncALcTmx1FIhwhy9OdEnFHHseQQzQ01099GzNDXCtG7dbAHqQRLXb8U_skAa11UAG9QTAGdMV/s1600/StuckThreadMaxTime.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjmrJ5Njfyr5OIl19BS09DjaIVPJDOQLYxrYDJ8nhgQGv4HU2FM4Hz_fnEncu96lw1m8mdncALcTmx1FIhwhy9OdEnFHHseQQzQ01099GzNDXCtG7dbAHqQRLXb8U_skAa11UAG9QTAGdMV/s1600/StuckThreadMaxTime.png" height="424" width="640" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<b>3.</b> Save the configuration and then restart EMGC_OMS1 server from WebLogic administration console or from server console.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
This workaround did the job.</div>
Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-6550761899044120952.post-72938252354459803142013-09-03T18:23:00.000+03:002013-09-03T18:23:40.370+03:00Is Database 12c Supported on Exadata?Answer is yes, even you can see 12c is listed under supported versions in document [888828.1].<br />
<br />
However, there is a couple of <b>but</b>s (you should see this coming). Since latest Exadata Storage Software (version 11.2.3.2.1) does not have new offload libraries, smart scan offload filtering and storage indexes become disabled with 12c. Smart scans are initiated but nodes get back blocks instead of rows and columns in the projection list. Also IORM plans are not enforced and inter-database plans are disabled on cells. And finally, cell metrics display 12c databases under OTHER_DATABASE.<br />
<br />
So, it seems better to wait until next Exadata update and probably 12c patch set update to upgrade your Exadata. At least, to be able upgrade to a fully functional state. Meanwhile, you'd better upgrade to 11.2.0.3 if you haven't yet because upgrading from 11.2.0.1 is not supported.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6550761899044120952.post-73870131619607253332013-06-30T23:47:00.001+03:002013-06-30T23:47:23.644+03:00Installing Single Instance Oracle 12c Database on Linux<div style="text-align: justify;">
As you are all probably aware, Oracle released new database version named 12c a few days ago, on 25th of June to be exact. It's the time of reading and researching new features, testing and developing. So I'm starting with this entry and installing a single instance. I intend to keep writing as I try more complex installations and upgrades.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
OK, lets's start. I'll install a single instance 12c database on Oracle Linux 6.4 64-bit. As storage, NAS will be used, so no ASM therefore no GI will be installed. First step after the Linux installation is completing operating system configuration. Easiest way of achieving this is installing pre-install package. In 12c documentation it's said to run the command below as root to install package:</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<span style="font-family: Courier New, Courier, monospace;">$ yum install oracle-rdbms-server-12cR1-preinstall</span></div>
<div style="text-align: justify;">
<br />
However mentioned package cannot be found on ULN. Instead install previous version's pre-install package. It does the job as well:<br />
<br /></div>
<div style="text-align: justify;">
<span style="font-family: Courier New, Courier, monospace;">$ yum install oracle-rdbms-server-11gR2-preinstall</span><br />
<br />
Create installation directory and grant required permissions:<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">$ mkdir /u01</span><br />
<span style="font-family: Courier New, Courier, monospace;">$ chown oracle:oinstall /u01</span><br />
<span style="font-family: Courier New, Courier, monospace;">$ chmod 775 /u01</span><br />
<br />
As oracle, which is created by pre-install package, run the installer from the directory where you unzipped two installation zip files:<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">$ ./runInstaller</span><br />
<br />
Graphical installation starts:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-iEIzqRoKGVk/UdCCHW84yGI/AAAAAAAAANo/lOo3-hUhqp8/s803/1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-iEIzqRoKGVk/UdCCHW84yGI/AAAAAAAAANo/lOo3-hUhqp8/s803/1.jpg" height="300" width="400" /></a></div>
<br />
Skip updates<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-QPrDvV6iV-4/UdCCK4ck5xI/AAAAAAAAAO4/sZLWavvbdeM/s802/2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/-QPrDvV6iV-4/UdCCK4ck5xI/AAAAAAAAAO4/sZLWavvbdeM/s802/2.jpg" height="300" width="400" /></a></div>
<br />
You can choose to create a database right after software installation or you can choose to install software first then run DBCA to create the database.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-dOhEHI3EU2o/UdCCLlBSJnI/AAAAAAAAAPU/2dJXwBc40aM/s802/3.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/-dOhEHI3EU2o/UdCCLlBSJnI/AAAAAAAAAPU/2dJXwBc40aM/s802/3.jpg" height="300" width="400" /></a></div>
<br />
Select system class:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-GzaHGDss2Zc/UdCCMHDMMCI/AAAAAAAAAPY/wDBEtmOy2bI/s801/4.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/-GzaHGDss2Zc/UdCCMHDMMCI/AAAAAAAAAPY/wDBEtmOy2bI/s801/4.jpg" height="301" width="400" /></a></div>
<br />
Select GI option:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-4S-FhYPXA5A/UdCCM3MPG3I/AAAAAAAAAP8/Q0BIbFToFjI/s801/5.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-4S-FhYPXA5A/UdCCM3MPG3I/AAAAAAAAAP8/Q0BIbFToFjI/s801/5.jpg" height="301" width="400" /></a></div>
<br />
I choose to follow advanced installation path to see what is there:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-7cQII-2KcgY/UdCCM4ReufI/AAAAAAAAAPw/dqbrY_-LiEw/s800/6.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/-7cQII-2KcgY/UdCCM4ReufI/AAAAAAAAAPw/dqbrY_-LiEw/s800/6.jpg" height="301" width="400" /></a></div>
<br />
Select language(s):<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/--wce9ZA1I7Q/UdCCM_blI_I/AAAAAAAAAPo/er9XjPvB11M/s800/7.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/--wce9ZA1I7Q/UdCCM_blI_I/AAAAAAAAAPo/er9XjPvB11M/s800/7.jpg" height="301" width="400" /></a></div>
<br />
Select database edition to install:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-gu3ptQ62cHw/UdCCNhUpnFI/AAAAAAAAAP4/9YEXH2Bbxpw/s802/8.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/-gu3ptQ62cHw/UdCCNhUpnFI/AAAAAAAAAP4/9YEXH2Bbxpw/s802/8.jpg" height="300" width="400" /></a></div>
<br />
Specify installation paths:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-3fzxUIdQW1E/UdCCN4GJw2I/AAAAAAAAAQA/ZstqESUrVQE/s801/9.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/-3fzxUIdQW1E/UdCCN4GJw2I/AAAAAAAAAQA/ZstqESUrVQE/s801/9.jpg" height="301" width="400" /></a></div>
<br />
Specify inventory directory:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-0cJCo-8OMP8/UdCCH_x8v9I/AAAAAAAAAN0/OVgayG4e4Mw/s800/10.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/-0cJCo-8OMP8/UdCCH_x8v9I/AAAAAAAAAN0/OVgayG4e4Mw/s800/10.jpg" height="301" width="400" /></a></div>
<br />
Select database type:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-A6aTuvUgf0g/UdCCH55BufI/AAAAAAAAANs/khM-gOCGCGU/s801/11.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-A6aTuvUgf0g/UdCCH55BufI/AAAAAAAAANs/khM-gOCGCGU/s801/11.jpg" height="300" width="400" /></a></div>
<br />
Specify database name. As a new feature, you can choose to create a multi-tenant database.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-rbF--OCuQts/UdCCIeg4lhI/AAAAAAAAAOA/55l9pM0iL3I/s798/12.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-rbF--OCuQts/UdCCIeg4lhI/AAAAAAAAAOA/55l9pM0iL3I/s798/12.jpg" height="302" width="400" /></a></div>
<br />
Configure database by specifying memory options, character set and sample schema installation:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-6V-9ET6KB94/UdCCIfroWcI/AAAAAAAAAOY/V0a3K6TmHeU/s801/13.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-6V-9ET6KB94/UdCCIfroWcI/AAAAAAAAAOY/V0a3K6TmHeU/s801/13.jpg" height="300" width="400" /></a></div>
<br />
Specify storage, I set the path of mount point of NAS disk:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-1g_VzX1_wPE/UdCCIwfe9VI/AAAAAAAAAOQ/CMn7G0EZdO0/s799/14.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/-1g_VzX1_wPE/UdCCIwfe9VI/AAAAAAAAAOQ/CMn7G0EZdO0/s799/14.jpg" height="300" width="400" /></a></div>
<br />
Specify Cloud Control details if you'll manage the instance through it:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-k1zdvYJO8LI/UdCCJeDV3RI/AAAAAAAAAOM/f5u85EFQphk/s801/15.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/-k1zdvYJO8LI/UdCCJeDV3RI/AAAAAAAAAOM/f5u85EFQphk/s801/15.jpg" height="300" width="400" /></a></div>
<br />
Choose to use FRA and set path -or ASM- for FRA:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-EsOsqMA_Sg0/UdCCJZFYfcI/AAAAAAAAAOU/3NRyZVIUFXg/s799/16.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/-EsOsqMA_Sg0/UdCCJZFYfcI/AAAAAAAAAOU/3NRyZVIUFXg/s799/16.jpg" height="301" width="400" /></a></div>
<br />
Set passwords for built-in users:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-WimxnGRyDLs/UdCCJ-PglUI/AAAAAAAAAO0/76Kc7S1mgIc/s801/17.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/-WimxnGRyDLs/UdCCJ-PglUI/AAAAAAAAAO0/76Kc7S1mgIc/s801/17.jpg" height="302" width="400" /></a></div>
<br />
Set system groups:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-yBk7kg1LobQ/UdCCKbeDnqI/AAAAAAAAAOs/vL_Ywq7tEpA/s803/18.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/-yBk7kg1LobQ/UdCCKbeDnqI/AAAAAAAAAOs/vL_Ywq7tEpA/s803/18.jpg" height="300" width="400" /></a></div>
<br />
Investigate summary and if there's anything you need to chance:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-rl82hzcKkP0/UdCCKRJhGVI/AAAAAAAAAOw/fxsF-_AB-eA/s799/19.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-rl82hzcKkP0/UdCCKRJhGVI/AAAAAAAAAOw/fxsF-_AB-eA/s799/19.jpg" height="301" width="400" /></a></div>
<br />
Installation starts. Run the root scripts when prompted:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-4SpqX9Qrer0/UdCCLjXCUDI/AAAAAAAAAPQ/07-nCWCWxdQ/s833/20.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-4SpqX9Qrer0/UdCCLjXCUDI/AAAAAAAAAPQ/07-nCWCWxdQ/s833/20.jpg" height="290" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-UrrWEHzmIDw/UdCCLl_AgiI/AAAAAAAAAPM/wpr9vnbz6PI/s566/21.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/-UrrWEHzmIDw/UdCCLl_AgiI/AAAAAAAAAPM/wpr9vnbz6PI/s566/21.jpg" height="278" width="400" /></a></div>
<br />
<b>12c DBCA</b><br />
<br />
If you choose to install software only during installation or installation failed to create the database - as I've experienced - you can run database configuration assistant (DBCA) which is located under ORACLE_HOME/bin:<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">$ cd /u01/app/oracle/product/12.1.0/dbhome_1/bin</span><br />
<span style="font-family: Courier New, Courier, monospace;">$ ./dbca</span><br />
<br />
Graphical installation starts:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-eMK2OyUrgAw/UdCCgSrsNwI/AAAAAAAAAQk/ZrTdaYXlilY/s798/1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-eMK2OyUrgAw/UdCCgSrsNwI/AAAAAAAAAQk/ZrTdaYXlilY/s798/1.jpg" height="301" width="400" /></a></div>
<br />
I choose to follow advanced installation path to see what is there:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-8i3ExFTE9QM/UdCTIFK0lFI/AAAAAAAAASk/Jy4cd831bBQ/s799/2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/-8i3ExFTE9QM/UdCTIFK0lFI/AAAAAAAAASk/Jy4cd831bBQ/s799/2.jpg" height="300" width="400" /></a></div>
<br />
Select the template of database type:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-jC30XyrXntA/UdCCi_BY_QI/AAAAAAAAARU/L8SNMUTdinc/s800/3.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-jC30XyrXntA/UdCCi_BY_QI/AAAAAAAAARU/L8SNMUTdinc/s800/3.jpg" height="300" width="400" /></a></div>
<br />
Set database names including container database:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-ESgvSQZEIkQ/UdCCjYwPZ8I/AAAAAAAAARg/s_dZ4nsLzZU/s799/4.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-ESgvSQZEIkQ/UdCCjYwPZ8I/AAAAAAAAARg/s_dZ4nsLzZU/s799/4.jpg" height="301" width="400" /></a></div>
<br />
Choose to configure database with EM Database Express (previous version's dbconsole) or with Cloud Control:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-nZB9cb2x10M/UdCCjyflmpI/AAAAAAAAAR0/b4pjCXMGzcw/s799/5.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-nZB9cb2x10M/UdCCjyflmpI/AAAAAAAAAR0/b4pjCXMGzcw/s799/5.jpg" height="301" width="400" /></a></div>
<br />
Set passwords of built-in user:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-XTZklJ5PDHg/UdCCj1926oI/AAAAAAAAAR8/8cbwsRvxFko/s799/6.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-XTZklJ5PDHg/UdCCj1926oI/AAAAAAAAAR8/8cbwsRvxFko/s799/6.jpg" height="301" width="400" /></a></div>
<br />
Set listener configuration, leave it as default:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-VQYmZ61H2lM/UdCCj3WivRI/AAAAAAAAARw/smjAPq6rX84/s799/7.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/-VQYmZ61H2lM/UdCCj3WivRI/AAAAAAAAARw/smjAPq6rX84/s799/7.jpg" height="301" width="400" /></a></div>
<br />
Set storage specifications:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-0K-1djPFOlg/UdCCkqlP5kI/AAAAAAAAAR4/y3KrwloCfxM/s799/8.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/-0K-1djPFOlg/UdCCkqlP5kI/AAAAAAAAAR4/y3KrwloCfxM/s799/8.jpg" height="300" width="400" /></a></div>
<br />
Set database options:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-OLsBKzpRvqo/UdCClV2vg3I/AAAAAAAAASE/yYG_aQIW2gg/s799/9.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-OLsBKzpRvqo/UdCClV2vg3I/AAAAAAAAASE/yYG_aQIW2gg/s799/9.jpg" height="301" width="400" /></a></div>
<br />
Set initialization parameters for memory management, number of Oracle processes, character set and connection mode:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-y_wBJeh7jQE/UdCCgkdL9OI/AAAAAAAAAQg/Goa9Rpf-TbQ/s800/10.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-y_wBJeh7jQE/UdCCgkdL9OI/AAAAAAAAAQg/Goa9Rpf-TbQ/s800/10.jpg" height="301" width="400" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-uxwHLWeBTJY/UdCCgqPCTdI/AAAAAAAAAQc/lwQmSCESL4o/s799/11.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-uxwHLWeBTJY/UdCCgqPCTdI/AAAAAAAAAQc/lwQmSCESL4o/s799/11.jpg" height="301" width="400" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-QGxVcrZPy6s/UdCChX93CfI/AAAAAAAAAQ8/PS1Y_-y27KY/s799/12.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/-QGxVcrZPy6s/UdCChX93CfI/AAAAAAAAAQ8/PS1Y_-y27KY/s799/12.jpg" height="301" width="400" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-05aWYHaxdhE/UdCCh0-xX_I/AAAAAAAAAQ4/AvW-QtayttA/s800/13.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/-05aWYHaxdhE/UdCCh0-xX_I/AAAAAAAAAQ4/AvW-QtayttA/s800/13.jpg" height="300" width="400" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-mM55iyJvWU8/UdCChsljkrI/AAAAAAAAAQ0/pGL2zY9DKWk/s800/14.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-mM55iyJvWU8/UdCChsljkrI/AAAAAAAAAQ0/pGL2zY9DKWk/s800/14.jpg" height="300" width="400" /></a></div>
<br />
After pre-requisite check installation starts. When finished, necessary information is displayed:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-D_PYCwODU-A/UdCCi7yUrbI/AAAAAAAAARQ/Hje4Wy6CWoM/s801/15.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/-D_PYCwODU-A/UdCCi7yUrbI/AAAAAAAAARQ/Hje4Wy6CWoM/s801/15.jpg" height="300" width="400" /></a></div>
<br />
Enjoy and discover your first 12c database...</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6550761899044120952.post-627647993241827682013-04-15T20:24:00.002+03:002013-04-15T20:24:40.743+03:00Performance Improvement for ODP.Net OracleDataAdapter<div style="text-align: justify;">
Recently I faced with a performance complaint for a query. Query was simple as it could be, just a "select * from some_table". When I digged out the problem with my fellow developer, we discovered that almost all time was spent while the data adapter fills the data table. Table had a little more than 155K rows and was size of 31 MB. Yet, it took more than 5 minutes to complete for data adapter to fill data table.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
The cause of the problem was the number of round trips client need to accomplish to get all the rows from database. If you can reduce the number of round trips, you can increase the fetch size so that in each turn command object will fetch more rows from database. Here is how it's impletemented:</div>
<br />
<span style="font-family: Courier New, Courier, monospace;">using (OracleConnection conn = new OracleConnection())</span><br />
<span style="font-family: Courier New, Courier, monospace;">{</span><br />
<span style="font-family: Courier New, Courier, monospace;"> OracleCommand comm = new OracleCommand();</span><br />
<span style="font-family: Courier New, Courier, monospace;"> comm.Connection = conn;</span><br />
<span style="font-family: Courier New, Courier, monospace;"> <span style="color: blue;">comm.FetchSize = comm.FetchSize * 8;</span></span><br />
<span style="font-family: Courier New, Courier, monospace;"> comm.CommandText = "select * from some_table";</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;"> try</span><br />
<span style="font-family: Courier New, Courier, monospace;"> {</span><br />
<span style="font-family: Courier New, Courier, monospace;"> conn.Open();</span><br />
<span style="font-family: Courier New, Courier, monospace;"> OracleDataAdapter adap = new OracleDataAdapter(comm);</span><br />
<span style="font-family: Courier New, Courier, monospace;"> System.Data.DataTable dt = new System.Data.DataTable();</span><br />
<span style="font-family: Courier New, Courier, monospace;"> adap.Fill(dt);</span><br />
<span style="font-family: Courier New, Courier, monospace;"> }</span><br />
<span style="font-family: Courier New, Courier, monospace;"> finally</span><br />
<span style="font-family: Courier New, Courier, monospace;"> {</span><br />
<span style="font-family: Courier New, Courier, monospace;"> conn.Close();</span><br />
<span style="font-family: Courier New, Courier, monospace;"> }</span><br />
<span style="font-family: Courier New, Courier, monospace;">}</span><br />
<br />
Notice the line with blue font, fetch size of command object increased by 8 times its default which is 128 KB at ODP.Net 11.2.0.3. OracleDataReader also has the FetchSize property. By increasing fetch size, you increase the cache size in memory to fetch rows.<br />
<br />
What we gained is up to 96% performance improvement. Here are some timings with different fetch sizes:<br />
<br />
<table>
<tbody>
<tr><td><b>Fetch Size</b></td><td><b>Timing (MI:SS.FF3)</b></td></tr>
<tr><td>Default (128 KB)</td><td><div style="text-align: center;">
05:20.290</div>
</td></tr>
<tr><td>Default x 8 (1 MB)</td><td><div style="text-align: center;">
00:52.941</div>
</td></tr>
<tr><td>Default x 32 (4 MB)</td><td><div style="text-align: center;">
00:26.008</div>
</td></tr>
<tr><td>Default x 64 (8 MB)</td><td><div style="text-align: center;">
00:12.409</div>
</td></tr>
</tbody></table>
<br />
It's a easy way to improve your application's performance, isn't it.Unknownnoreply@blogger.com11tag:blogger.com,1999:blog-6550761899044120952.post-2161481076015687592013-03-17T20:02:00.000+02:002013-03-17T20:02:08.926+02:00Running Toad on LinuxIf you want to use Linux as your desktop OS however want to keep using Toad, thanks to <a href="http://www.winehq.org/" target="_blank">Wine</a> it is possible. I've succeed running Toad 10.5 on 64-bit Ubuntu 12.10.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjKd_ze6kBJboQ9FXujGUS3HFQlKPkeNhYIE8ZfE3xIDE7YGDYM8AsCa1MeLJiMvyOGgvcKMqLD32nt-kbtJBeF_VVZU0KI0BE3XaGjNjeMzBY9QqWEdRqhvcfbW5pzdrWwkjdlZok_fV-t/s1600/toad_ubuntu.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjKd_ze6kBJboQ9FXujGUS3HFQlKPkeNhYIE8ZfE3xIDE7YGDYM8AsCa1MeLJiMvyOGgvcKMqLD32nt-kbtJBeF_VVZU0KI0BE3XaGjNjeMzBY9QqWEdRqhvcfbW5pzdrWwkjdlZok_fV-t/s1600/toad_ubuntu.png" height="360" width="640" /></a></div>
<br />
These are steps to install Toad:<br />
<br />
<b>1.</b> Install Wine<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">$ sudo add-apt-repository ppa:ubuntu-wine/ppa</span><br />
<span style="font-family: Courier New, Courier, monospace;">$ sudo apt-get update</span><br />
<span style="font-family: Courier New, Courier, monospace;">$ sudo apt-get install wine1.4</span><br />
<br />
<b>2.</b> Configure Wine as 32-bit. Since you are running a 64-bit OS, Wine runs 64-bit basis as well. However Toad is a 32-bit application that requires 32-bit Oracle client. Therefore you need configure Wine to run on 32-bit basis.<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">$ rm -rf ~/.wine*</span><br />
<span style="font-family: Courier New, Courier, monospace;">$ WINEPREFIX='/home/<user_name>/prefix32' WINEARCH='win32' wine 'wineboot'</span><br />
<br />
<b>3.</b> Install requirements. I recommend installing .Net Framework first because it's a bit problematic and takes the longest time. During its setup, you will be asked to download different versions and place into Winetricks's cache directory. So you will need to run the command several times.<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">$ winetricks dotnet35sp1</span><br />
<br />
After successfully completing previous step, install rest of the requirements.<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">$ winetricks gecko fontfix gdiplus msxml3 msxml4 msxml6 vcrun2005sp1 vcrun2008 winxp volnumcd </span><br />
<br />
<b>4.</b> Install Oracle Client. Copy installation files of Oracle Client for 32-bit Windows into ~/.wine/drive_c/tmp. Unzip the installation and run setup. During setup, choose "Runtime" option and ignore any prerequisite check failures.<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">$ wine "c:\tmp\client\setup.exe"</span><br />
<br />
Do not forget to copy your tnsnames.ora file to ~/.wine/drive_c/app/<user_name>/product/11.2.0/client_1/network/admin after setup.<br />
<br />
<b>5.</b> Install Toad. You might copy setup file again into ~/.wine/drive_c/tmp as well. To run setup:<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">$ wine "c:\tmp\toad.exe"</span><br />
<br />
<b>6.</b> Run Toad. Installation creates desktop shortcuts, so it's possible to run Toad from these shortcuts or directly through Wine:<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">$ wine "C:\Program Files\Quest Software\Toad for Oracle 10.5\Toad.exe" </span><br />
or<br />
<span style="font-family: Courier New, Courier, monospace;">$ wine ~/.wine/drive_c/Program\ Files/Quest\ Software/Toad\ for\ Oracle\ 10.5/Toad.exe</span><br />
<br />
Enjoy Linux<br />
<br />Unknownnoreply@blogger.com2tag:blogger.com,1999:blog-6550761899044120952.post-82777376590572783982013-01-30T22:38:00.000+02:002013-01-30T22:38:22.932+02:00Data Pump Options<div style="text-align: justify;">
Data pump is really a handy tool. It has many options allowing you to try different ways and provides flexibility. In this entry, I'll use some of these options and try to explain how to export a remote database to the local ASM disk group.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Let's jump in. Start with setting the environment at the local server:</div>
<br />
<b>1.</b> Create a directory in ASM disk group:<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">SQL> alter diskgroup RECOVERY add directory '+RECOVERY/DPUMP';</span><br />
<br />
<b>2.</b> Create a directory in the database pointing to the ASM directory:<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">SQL> create directory ASM_DIR as '+RECOVERY/DPUMP';</span><br />
<br />
<b>3. </b>Grant write on directory created above to the user you'll use while exporting:<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">SQL> grant read, write on ASM_DIR to <export_user>;</span><br />
<br />
<b>3.</b> Create database a link:<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">SQL> create public database link target_db connect to <remote_user> identified by <password> using '<tns_entry>';</span><br />
<br />
It's required that both the local and remote users are granted to the EXP_FULL_DATABASE role.<br />
<br />
<b><u>Exporting</u></b><br />
<br />
Now it's time to export. At local server, run:<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">$ expdp NETWORK_LINK=target_db DUMPFILE=ASM_DIR:dump_file.dmp LOGFILE=DATA_PUMP_DIR:export.log FULL=Y EXCLUDE=STATISTICS COMPRESSION=ALL PARALLEL=8</span><br />
<br />
To briefly explain parameters given:<br />
<ul>
<li style="text-align: justify;">NETWOK_LINK is the one pointing to remote database. This is how we export remote database.</li>
<li style="text-align: justify;">As you may see, dump file is being created under directory ASM_DIR pointing the directory in ASM disk group.</li>
<li style="text-align: justify;">Notice that log file is <u><b>not</b></u> being created under ASM directory because it's not supported. That's why I've used default data pump directory. Also you may choose not to log by using parameter NOLOGFILE=Y instead.</li>
<li style="text-align: justify;">We're having a full database dump by using FULL=Y parameter. Instead you may use SCHEMAS, TABLES or TABLESPACES parameters to export/import only given schemas/tables or tablespaces.</li>
<li style="text-align: justify;">Our dump will not include statistics because of the parameter EXCLUDE=STATISTICS. You can use this option to exclude other objects such as tables or indexes, too. I choose to exclude statistics because I'd rather gather statistics at the database I'm going to import the dump.</li>
<li style="text-align: justify;">By means of the parameter COMPRESSION we'll have a smaller sized dump file. To give a hint how much could it be reduced in size, here is a few statistics I've had with different characteristics of data:</li>
</ul>
<div align="center">
<table border="1" style="width: 50%;">
<tbody align="center">
<tr><td width="35%"><b>Estimated</b></td><td width="35%"><b>Actual</b></td><td width="30%"><b>Gain Percentage</b></td></tr>
<tr><td>687.7 MB</td><td>188.13 MB</td><td>72.64%</td></tr>
<tr><td>154.3 GB</td><td>56.44 GB</td><td>63.42%</td></tr>
<tr><td>287.2 GB</td><td>207.93 GB</td><td>27.6%</td></tr>
</tbody></table>
</div>
<br />
<div style="text-align: justify;">
The reason of low compression ratio of the last one is that it is a table space with Hybrid Columnar Compression (HCC) enabled in a Exadata machine. It's already compressed quite a bit. It's possible to set COMPRESSION parameter with; ALL, METADATA_ONLY, DATA_ONLY or NONE.</div>
<ul>
<li>Lastly, to improve performance we use the parameter PARALLEL. Data pump is a tool you can get full advantage of parallelism. Therefore it's good to use as much as possible.</li>
</ul>
<b><u>Checking Up</u></b><br />
<br />
After exporting you can query and check dump files. To query files exported under ASM:<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">SQL> select a.name, f.bytes/1024/1024 mb, f.creation_date </span><br />
<span style="font-family: Courier New, Courier, monospace;">from v$asm_file f, v$asm_alias a</span><br />
<span style="font-family: Courier New, Courier, monospace;">where f.file_number=a.file_number and a.system_created='N' and f.type='DUMPSET';</span><br />
<br />
To check dump file, best tool to use is again data pump, use parameter SHOW:<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">$ impdp DUMPFILE=ASM_DIR:dump_file.dmp NOLOGFILE=Y FULL=Y SHOW=Y</span><br />
<br />
Finally, if you need to copy the dump file to somewhere else, it could be achieved by asmcmd tool:<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">$ asmcmd -p cp RECOVERY/DPUMP/dump_file.dmp /destination_directory</span>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6550761899044120952.post-41307535178213968352012-12-31T12:20:00.001+02:002012-12-31T12:20:37.734+02:00RMAN-03009 and ORA-27052 Due to Opportunistic LockingWhile backing up backup sets to a secondary location which was a Windows share, RMAN job failed with the following error:<br />
<br />
<i>RMAN-00571: ===========================================================</i><br />
<i>RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============</i><br />
<i>RMAN-00571: ===========================================================</i><br />
<span style="color: red;"><i>RMAN-03009: failure of backup command on ORA_DISK_1 channel at sometime</i></span><br />
<span style="color: red;"><i>ORA-27052: unable to flush file data</i></span><br />
<span style="color: red;"><i>Linux-x86_64 Error: 5: Input/output error</i></span><br />
<br />
When I digged up a little bit, I found the following lines in server's system log:<br />
<br />
<i><host> kernel: CIFS VFS: No response to cmd 47 mid 2921</i><br />
<i><host> kernel: CIFS VFS: Write2 ret -11, wrote 0</i><br />
<i><host> kernel: CIFS VFS: Write2 ret -112, wrote 0</i><br />
<i><host> kernel: CIFS VFS: Write2 ret -11, wrote 0</i><br />
<i><host> kernel: CIFS VFS: Write2 ret -11, wrote 0</i><br />
<br />
The problem was caused by optimistic locking property of CIFS module. If the file you are copying is large enough (in my case it was about 80 GB) through a CIFS channel, you may hit this problem. By default optimistic locking is set to 1 when the module is loaded. Disabling optimistic locking solves the problem with no performance drawback, at least I haven't observed.<br />
<br />
To disable optimistic locking:<br />
<span style="font-family: Courier New, Courier, monospace;">$ echo 0 > /proc/fs/cifs/OplockEnabled</span><br />
<br />Unknownnoreply@blogger.com2tag:blogger.com,1999:blog-6550761899044120952.post-69325346989199835912012-12-12T01:48:00.001+02:002012-12-12T01:48:54.193+02:00ETL with Bulk Insert Using ODP.Net<div style="text-align: justify;">
As a part of an ETL process it is likely to load data from some flat files. And in some situations, data in the file may not be well-formed. So data need to be cleaned/transformed before loading into database. To achieve this goal, developing your own tool could be much more practical. Let's say, you decided to write your code in C#. In that case, you need to use ODP.Net library to connect Oracle. I'd like to show you three ways of loading data using ODP.Net. </div>
<br />
First one is row-by-row basis. Pseudo code representing the case is as below:<br />
<br />
<i>For each row in the file</i><br />
<i> Read row;</i><br />
<i> Do transformations </i><i>of the</i><i> row;</i><br />
<i> Insert row;</i><br />
<i>loop;</i><br />
<br />
To execute insert statements, I will use the method below. It takes advantage of bind variable to avoid hard parsing.<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">private void ExecuteNonQuery(string commandText, Dictionary<string, object> parameters)</span><br />
<span style="font-family: Courier New, Courier, monospace;">{</span><br />
<span style="font-family: Courier New, Courier, monospace;"> using (OracleCommand comm = new OracleCommand(commandText))</span><br />
<span style="font-family: Courier New, Courier, monospace;"> {</span><br />
<span style="font-family: Courier New, Courier, monospace;"> if (orclConn.State != System.Data.ConnectionState.Open) </span><br />
<span style="font-family: Courier New, Courier, monospace;"> orclConn.Open();</span><br />
<span style="font-family: Courier New, Courier, monospace;"> comm.Connection = this.orclConn;</span><br />
<span style="font-family: Courier New, Courier, monospace;"> foreach (KeyValuePair<string, object> kvp in parameters)</span><br />
<span style="font-family: Courier New, Courier, monospace;"> comm.Parameters.Add(kvp.Key, kvp.Value);</span><br />
<span style="font-family: Courier New, Courier, monospace;"> comm.ExecuteNonQuery();</span><br />
<span style="font-family: Courier New, Courier, monospace;"> }</span><br />
<span style="font-family: Courier New, Courier, monospace;">}</span><br />
<br />
<div style="text-align: justify;">
When I tested the code above for a file with 46000 rows and 24 columns on a test database. It took 641.26 seconds to complete.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Second method is almost same with the first one accept it corrects a mistake of it. This method will commit only once. The code above doesn't contain a commit statement because OracleCommand objects commits automatically, in other words auto commit is on by default. Which means in the first method I've committed for 46000 times. To correct the situation by turning auto commit down, my coding will be:</div>
<br />
<i>Begin a transaction: </i><i>OracleCommand comm = GetTransaction();</i><br />
<i>For each row in the file</i><br />
<i> Read row;</i><br />
<i> Do transformations of the row;</i><br />
<i> Insert row: ExecuteNonQuery(comm, "<insert statement>", parameters);</i><br />
<i>loop</i><br />
<i>Commit transaction: comm.Transaction.Commit();</i><br />
<i><br /></i>
Methods referenced above are:<br />
<br />
<span style="font-family: 'Courier New', Courier, monospace;">private OracleCommand GetTransaction()</span><br />
<span style="font-family: Courier New, Courier, monospace;">{</span><br />
<span style="font-family: Courier New, Courier, monospace;"> OracleTransaction dbTran = this.BeginTransaction;</span><br />
<span style="font-family: Courier New, Courier, monospace;"> OracleCommand comm = new OracleCommand();</span><br />
<span style="font-family: Courier New, Courier, monospace;"> comm.Connection = this.orclConn;</span><br />
<span style="font-family: Courier New, Courier, monospace;"> comm.Transaction = dbTran;</span><br />
<span style="font-family: Courier New, Courier, monospace;"> return comm;</span><br />
<span style="font-family: Courier New, Courier, monospace;">}</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">private void ExecuteNonQuery(OracleCommand comm, string commandText, Dictionary<string, object> parameters)</span><br />
<span style="font-family: Courier New, Courier, monospace;">{</span><br />
<span style="font-family: Courier New, Courier, monospace;"> comm.CommandText = commandText;</span><br />
<span style="font-family: Courier New, Courier, monospace;"> foreach (KeyValuePair<string, object> kvp in parameters)</span><br />
<span style="font-family: Courier New, Courier, monospace;"> comm.Parameters.Add(kvp.Key, kvp.Value);</span><br />
<span style="font-family: Courier New, Courier, monospace;"> comm.ExecuteNonQuery();</span><br />
<span style="font-family: Courier New, Courier, monospace;">}</span><br />
<br />
<div style="text-align: justify;">
With this method, test took 604.04 seconds, faster than the first one a little bit. Why the difference is so minor? Because in Oracle, commits take almost the same time for a row or a million rows inserted. Redo buffer is already flushed probably many times since LGWR process flushes redo:</div>
<ul>
<li style="text-align: justify;">Every three seconds, </li>
<li style="text-align: justify;">When someone commits, </li>
<li style="text-align: justify;">When switching log file,</li>
<li style="text-align: justify;">When redo buffer gets full 1/3 of it or has 1 MB of cached redo log data</li>
</ul>
<div style="text-align: justify;">
So what is left for commit is triggering one more flush. Actually what is expensive in a transaction is rollback rather than commit.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Third and the final method is using arrays as bind variables as described in the <a href="http://www.oracle.com/technetwork/issue-archive/2009/09-sep/o59odpnet-085168.html" target="_blank">article</a>. To implement array binding, we'll store the rows in a list and extract values from that list as arrays:<br />
<br />
<div style="text-align: start;">
<i>For each row in the file</i></div>
<div style="text-align: start;">
<i> Read row;</i></div>
<div style="text-align: start;">
<i> Do transformations of the row;</i></div>
<div style="text-align: start;">
<i> Add row to a list;</i></div>
<div style="text-align: start;">
<i>loop</i></div>
<div style="text-align: start;">
<i>Insert rows;</i></div>
</div>
<div style="text-align: justify;">
<span style="text-align: start;"><br /></span>
<span style="text-align: start;">To insert rows using array binding, required code:</span><br />
<br />
<div style="text-align: left;">
<span style="text-align: start;"><span style="font-family: 'Courier New', Courier, monospace; text-align: justify;">OracleCommand comm = GetTransantionCommand;</span></span></div>
<br />
<div style="text-align: left;">
<span style="font-family: Courier New, Courier, monospace;">comm.ArrayBindCount = rows.Count;</span></div>
<div style="text-align: left;">
<span style="font-family: Courier New, Courier, monospace;">comm.CommandText = "insert into t value(:p1,:p2)";</span></div>
<div style="text-align: left;">
<span style="font-family: Courier New, Courier, monospace;">comm.Parameters.Add("p1", OracleDbType.Varchar2, rows.Select(t => t.Attribute1).ToArray(), ParameterDirection.Input);</span></div>
<div style="text-align: left;">
<span style="font-family: Courier New, Courier, monospace;">comm.Parameters.Add("p2", OracleDbType.Date, rows.Select(t => t.Attribute2).ToArray(), ParameterDirection.Input);</span></div>
<div style="text-align: left;">
<span style="font-family: Courier New, Courier, monospace;">comm.ExecuteNonQuery();</span></div>
<div style="text-align: left;">
<span style="font-family: 'Courier New', Courier, monospace;">// If another DML if needed, clear parameters and array bind</span></div>
<div style="text-align: left;">
<span style="font-family: Courier New, Courier, monospace;">comm.Parameters.Clear();</span></div>
<div style="text-align: left;">
<span style="font-family: Courier New, Courier, monospace;">comm.ArrayBindCount = 0;</span></div>
<div style="text-align: left;">
<span style="font-family: Courier New, Courier, monospace;">//</span></div>
<div style="text-align: left;">
<span style="font-family: Courier New, Courier, monospace;"><i>comm.Transaction.Commit();</i></span></div>
<div style="text-align: start;">
<br /></div>
This time test resulted in 9.81 seconds, more than 98% improvement. Single insert statement sweeps all the overhead of round trips caused by multiple inserts.<br />
<br />
These are the good, the bad and the ugly (if we skip using bind variable, it could be uglier) methods of bulk insert. </div>
Unknownnoreply@blogger.com2tag:blogger.com,1999:blog-6550761899044120952.post-20425286428460808212012-11-30T17:37:00.001+02:002012-11-30T17:37:32.123+02:00Changing IP Addresses of a 11g RACThere are three phases for changing IP addresses of an Oracle Cluster related to three groups of networks used; public, VIP and SCAN. Below you may find the steps to alter IP addresses of a 11g RAC installation running on Oracle Linux 5. Be sure you follow the steps in order because changes for public network should be done before VIP network changes.<br />
<br />
<b>Changing Public IPs</b><br />
<br />
<b>1.</b> Check current network information:<br />
<span style="font-family: Courier New, Courier, monospace;">$ </span><span style="font-family: 'Courier New', Courier, monospace;">$GRID_HOME</span><span style="font-family: Courier New, Courier, monospace;">/bin/oifcfg getif</span><br />
<i>eth0 10.10.11.0 global public</i><br />
<i>eth1 192.168.0.0 global cluster_interconnect</i><br />
<br />
<b>2.</b> Delete the existing interface information from OCR<br />
<span style="font-family: Courier New, Courier, monospace;">$ </span><span style="font-family: 'Courier New', Courier, monospace;">$GRID_HOME</span><span style="font-family: Courier New, Courier, monospace;">/bin/oifcfg delif -global eth0/10.10.11.0 </span><br />
<br />
<b>3.</b> Add it back with the correct information<br />
<span style="font-family: Courier New, Courier, monospace;">$ </span><span style="font-family: 'Courier New', Courier, monospace;">$GRID_HOME</span><span style="font-family: Courier New, Courier, monospace;">/bin/oifcfg setif -global eth0/10.10.12.0:public</span><br />
<br />
<b>4.</b> Shutdown the cluster<br />
<span style="font-family: Courier New, Courier, monospace;">$ crsctl stop cluster -all</span><br />
<br />
<b>5.</b> Modify the IP address at network layer, DNS and /etc/hosts file to reflect the change. Files to modify/check are:<br />
- /etc/sysconfig/network-script/ifcfg-eth0<br />
- /etc/sysconfig/network<br />
- /etc/hosts<br />
<br />
Restart network interface to activate changes<br />
<span style="font-family: Courier New, Courier, monospace;">$ ifdown eth0</span><br />
<span style="font-family: Courier New, Courier, monospace;">$ ifup eth0</span><br />
<br />
<b>6.</b> Restart the cluster<br />
<span style="font-family: Courier New, Courier, monospace;">$ crsctl start cluster -all </span><br />
<br />
<b>Changing VIPs</b><br />
<br />
<b>1.</b> Check current configuration<br />
$ srvctl config nodeapps -a<br />
<i>Network exists: 1/10.10.11.0/255.255.255.0/eth0, type static</i><br />
<i>VIP exists: /</i><i>racnode</i><i>1-vip/10.10.12.11/10.10.12.1/255.255.255.0/eth0, hosting node racnode1</i><br />
<i>VIP exists: /</i><i>racnode</i><i>2-vip/10.10.12.12/10.10.12.1/255.255.255.0/eth0, hosting node racnode2</i><br />
<br />
<b>2.</b> Stop the database instance and VIP:<br />
<span style="font-family: Courier New, Courier, monospace;">$ srvctl stop instance -d <db_name> -n racnode1</span><br />
<span style="font-family: Courier New, Courier, monospace;">$ srvctl stop vip -n racnode1 -f</span><br />
<br />
<b>3.</b> Ensure VIP is offline and VIP is not bounded to network interface<br />
<span style="font-family: Courier New, Courier, monospace;">$ crsctl stat res -t</span><br />
<span style="font-family: Courier New, Courier, monospace;">$ ifconfig -a</span><br />
<br />
<b>4.</b> Modify the IP address at network layer, DNS and /etc/hosts file to reflect the change<br />
<br />
<b>5.</b> Alter the VIP<br />
<span style="font-family: Courier New, Courier, monospace;">$ srvctl modify nodeapps -n racnode1 -A </span><i>10.10.12.12</i><span style="font-family: Courier New, Courier, monospace;">/255.255.255.0/eth0</span><br />
<br />
<b>6.</b> Verify the change<br />
<span style="font-family: Courier New, Courier, monospace;">$ srvctl config nodeapps -n racnode1 -a</span><br />
<span style="font-family: inherit;"><i>VIP exists.: /racnode1-vip/10.10.12.12/255.255.255.0/eth0 hosting node racnode1</i></span><br />
<br />
<b>7.</b> Start the database instance and VIP<br />
<span style="font-family: Courier New, Courier, monospace;">$ srvctl start vip -n racnode1</span><br />
<span style="font-family: Courier New, Courier, monospace;">$ srvctl start instance -d </span><span style="font-family: 'Courier New', Courier, monospace;"><db_name></span><span style="font-family: Courier New, Courier, monospace;"> -n racnode1</span><br />
<br />
<b>8.</b> Ensure VIP is online and VIP is bounded to network interface<br />
<span style="font-family: Courier New, Courier, monospace;">$ crsctl stat res -t</span><br />
<span style="font-family: Courier New, Courier, monospace;">$ ifconfig -a</span><br />
<br />
<b>9.</b> Repeat the steps above for the other nodes in the cluster<br />
<br />
<b>Changing SCAN IPs </b><br />
<br />
<b>1.</b> Update DNS with the new IP addresses. If host file is used, change IP in the host file.<br />
<br />
<b>2.</b> Stop the SCAN listener and the SCAN<br />
<span style="font-family: Courier New, Courier, monospace;">$ $GRID_HOME/bin/srvctl stop scan_listener</span><br />
<span style="font-family: Courier New, Courier, monospace;">$ $GRID_HOME/bin/srvctl stop scan</span><br />
<span style="font-family: Courier New, Courier, monospace;">$ $GRID_HOME/bin/srvctl status scan</span><br />
<br />
<b>3.</b> Check the current IP address(es) of the SCAN<br />
<span style="font-family: Courier New, Courier, monospace;">$ $GRID_HOME/bin/srvctl config scan</span><br />
<i>SCAN name: <scan_name>, Network: 1/10.10.12.0/255.255.255.0/eth0</i><br />
<i>SCAN VIP name: scan1, IP: /</i><i><scan_name></i><i>/10.10.11.15</i><br />
<br />
<b>4.</b> Refresh the SCAN with the new IP addresses from the DNS entry:<br />
<span style="font-family: Courier New, Courier, monospace;">$ $GRID_HOME/bin/srvctl modify scan -n <scan_name></span><br />
<br />
<b>5.</b> Check whether the SCAN has been changed<br />
<span style="font-family: Courier New, Courier, monospace;">$GRID_HOME/bin/srvctl config scan</span><br />
<i>SCAN name: <scan_name>, Network: 1/10.10.12.0/255.255.255.0/eth0</i><br />
<i>SCAN VIP name: scan1, IP: /</i><i><scan_name></i><i>/10.10.12.15</i><br />
<br />
You can refer to documents; [ID 276434.1] for public and VIP network changes and [ID 952903.1] for SCAN IP changes for detailed information.<br />
<br />Unknownnoreply@blogger.com4tag:blogger.com,1999:blog-6550761899044120952.post-47607048025093480382012-11-29T10:21:00.000+02:002012-11-29T10:39:43.454+02:00Oracle R Enterprise Output Error: ORA-22621Embedding R into Oracle database is a terrific idea. I believe anyone practicing data analysis and/or data mining should should take look at it. Although it's not a mature environment yet, some workarounds may be needed sometimes. Such as the one I come up against recently. To be clear beforehand, I'm running Oracle R Enterprise 1.1 on a 11.2.0.3 database. To illustrate the problem here is a demonstration:
<br />
<br />
Create a embedded function which returns a data frame/table with three columns which are type of integer, decimal and character respectively:<br />
<span style="font-family: "Courier New",Courier,monospace;">begin</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> sys.rqScriptCreate('Test',</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> 'function(){</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> data.frame(a=1:10, b= seq(0.1, by=0.1),c=letters[1:10])</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> }');</span><br />
<span style="font-family: "Courier New",Courier,monospace;">end;</span><br />
<span style="font-family: "Courier New",Courier,monospace;">/</span><br />
<br />
And call it within a query:<br />
<span style="font-family: "Courier New",Courier,monospace;">select a, b, c</span><br />
<span style="font-family: "Courier New",Courier,monospace;">from table(rqsys.rqEval(null,</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> 'select 1 a, 1 b, cast(''c'' as varchar2(30)) c from dual',</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> 'Test'));</span><br />
<br />
Which will result with error:<br />
<i><span style="color: red;">ORA-22621</span>: error transfering an object from the agent</i><br />
<i><span style="color: red;">ORA-06512</span>: at "RQSYS.RQTABLEEVALIMPL", line 60</i><br />
<i>22621. 00000 - "error transfering an object from the agent"</i><br />
<i>*Cause: Any error returned from pickler routines on the agent side.</i><br />
<i>*Action: Contact Oracle Support.</i><br />
<br />
The actual problem is the output format in fact. rq*Eval() functions takes output table definition as a parameter and it works smoothly with numeric columns, yet same cannot be said for character based columns. The workaround I came up with is taking advantage of good old XML. If you call the function with XML option instead of giving table format definition, you have the result set without an error. So keep on going this way and process the output which is CLOB containing XML:<br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">select x.*<br />from table(rqsys.rqEval(null, 'XML', '</span><span style="font-family: "Courier New",Courier,monospace;"><span style="font-family: "Courier New",Courier,monospace;">Test</span>')) t,<br /> xmltable('//root/frame_obj/ROW-frame_obj' passing xmltype.createxml(t.value)<br /> columns a varchar2(1) path 'a/text()',<br /> b varchar2(3) path 'b/text()',<br /> c varchar2(1) path 'c/text()')(+) x;</span><br />
<br />Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6550761899044120952.post-56258052994496561612012-10-31T12:15:00.000+02:002012-10-31T18:06:35.491+02:00Database TriggersDatabase triggers are well-matched ways of monitoring your database and logging events you'd like know about. It's possible to capture errors occurred and even notify yourself for exceptional situations. An example of these benefits is what I'll briefly present here. Below you'll find a demonstration of a trigger which logs errors and sends a mail when out of space error occurs.<br />
<br />
First of all, start with creating the table to log into:<br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">CREATE TABLE SERVERERROR_LOG<br />
( <br />
ERROR_TIME TIMESTAMP, <br />
DB_USER VARCHAR2(30), <br />
OS_USER VARCHAR2(30), <br />
USER_HOST VARCHAR2(64), <br />
CLIENT_PROGRAM VARCHAR2(48), <br />
SQLID VARCHAR2(13), <br />
ERROR_STACK VARCHAR2(2000)<br />
);</span><br />
<br />
Table keeps records of SQL running (actually ID of the SQL to save space, since full SQL text can be queried) and the error it caused beside client information. Here is the trigger:<br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">CREATE OR REPLACE TRIGGER log_server_errors<br />
AFTER SERVERERROR ON DATABASE<br />
DECLARE<br />
osuser VARCHAR2(30);<br />
machine VARCHAR2(64);<br />
prog VARCHAR2(48);<br />
sqlid VARCHAR2(13);<br />
err_type VARCHAR2(19);<br />
obj_type VARCHAR2(19);<br />
owner VARCHAR2(30);<br />
ts VARCHAR2(30);<br />
obj VARCHAR2(30);<br />
sub_obj VARCHAR2(19);<br />
BEGIN<br />
SELECT osuser, machine, program, sql_id<br />
INTO osuser, machine, prog, sqlid<br />
FROM gv$session<br />
WHERE audsid = userenv('sessionid') and inst_id = userenv('instance') and status = 'ACTIVE';<br />
<br />
INSERT INTO servererror_log VALUES<br />
(systimestamp, sys.login_user, osuser, machine, prog, sqlid, dbms_utility.format_error_stack);<br />
COMMIT;<br />
<br />
IF (SPACE_ERROR_INFO(err_type,obj_type,owner,ts,obj,sub_obj) = TRUE) THEN<br />
UTL_MAIL.SEND( sender => 'oradb@xyz.com', <br />
recipients => 'dba@xyz.com',<br />
subject => 'Insufficient Tablespace Size',<br />
message => err_type || ' at tablespace ' || ts<br />
); <br />
END IF;<br />
END log_server_errors;</span><br />
<br />
Trigger queries gv$session view to gather client information and inserts to log table. Then, if error is a out of space error, trigger sends an e-mail using UTL_MAIL package. This's a pretty straightforward trigger you can use and improve according to your needs.Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-6550761899044120952.post-43115195407202728962012-09-14T17:45:00.001+03:002012-09-14T18:34:51.116+03:00Upgrading Oracle Enterprise Manager 12c R1 to 12c R2<div style="text-align: justify;">
As you might know, release 2 of Oracle Cloud Control 12c is released. If you already have installed 12c R1, upgrading to R2 is a simple process. 1-system upgrade approach is the only way to upgrade, even you have installed bundle patch 1 or not. Basically, upgrade contains two steps; upgrading Oracle Management Service and its repository (installer handles both upgrades) and upgrading agents. Here are the steps on Linux :</div>
<br />
1. Download installation files from <a href="http://www.oracle.com/technetwork/oem/enterprise-manager/downloads/index.html" target="_blank">OEM Downloads</a> page and unzip them. A disk space of 5.3 GB is required for decompressed installation files.<br />
<br />
2. Stop OMS. You can also stop the service when installer prompts you to say it is still running.<br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">$ emctl stop oms</span><br />
<br />
3. Run the installer<br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">$ ./runInstaller</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-sc49QJA-gqA/UFMsJvSa3uI/AAAAAAAAAKQ/KS1B1-fRqek/s1600/12cR2_1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="240" src="http://4.bp.blogspot.com/-sc49QJA-gqA/UFMsJvSa3uI/AAAAAAAAAKQ/KS1B1-fRqek/s320/12cR2_1.png" width="320" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-P14r1nvSARM/UFMsJqD8EhI/AAAAAAAAAKU/3c9DjzN6n7k/s1600/12cR2_2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="240" src="http://4.bp.blogspot.com/-P14r1nvSARM/UFMsJqD8EhI/AAAAAAAAAKU/3c9DjzN6n7k/s320/12cR2_2.png" width="320" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-OotB0yqSzL8/UFMsJmrWoeI/AAAAAAAAAKM/I-SjP8SypK4/s1600/12cR2_3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="240" src="http://2.bp.blogspot.com/-OotB0yqSzL8/UFMsJmrWoeI/AAAAAAAAAKM/I-SjP8SypK4/s320/12cR2_3.png" width="320" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-4mYps5DN1w0/UFMsKLRVovI/AAAAAAAAAKY/5rsUictoRM8/s1600/12cR2_4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="240" src="http://2.bp.blogspot.com/-4mYps5DN1w0/UFMsKLRVovI/AAAAAAAAAKY/5rsUictoRM8/s320/12cR2_4.png" width="320" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-dwMk6DzvRzA/UFMsKf2plWI/AAAAAAAAAKc/OiEXdWMXRCs/s1600/12cR2_5.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="240" src="http://2.bp.blogspot.com/-dwMk6DzvRzA/UFMsKf2plWI/AAAAAAAAAKc/OiEXdWMXRCs/s320/12cR2_5.png" width="320" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-WK9nEfqXuBA/UFMsKcE6-FI/AAAAAAAAAKk/P9EOmeV8ZjY/s1600/12cR2_6.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="240" src="http://2.bp.blogspot.com/-WK9nEfqXuBA/UFMsKcE6-FI/AAAAAAAAAKk/P9EOmeV8ZjY/s320/12cR2_6.png" width="320" /></a></div>
<br />
As a prerequisite, emkey should be configured properly. Run the following command:<br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">$ emctl config emkey -copy_to_repos_from_file -repos_host <host_fqdn> -repos_port <port_number> -repos_sid <database_sid> -repos_user sysman -emkey_file /u01/app/Oracle/Middleware/wlserver_10.3.5/oms/sysman/config/emkey.ora</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-E6RXcY3hqv4/UFHvvtdM0kI/AAAAAAAAAIg/c3X1D29Swws/s1600/12cR2_6_emckey_error.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="177" src="http://1.bp.blogspot.com/-E6RXcY3hqv4/UFHvvtdM0kI/AAAAAAAAAIg/c3X1D29Swws/s320/12cR2_6_emckey_error.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-OEPJqHwjf1c/UFMsMVDic_I/AAAAAAAAALA/pxiacZ5GQaA/s1600/12cR2_7.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="240" src="http://3.bp.blogspot.com/-OEPJqHwjf1c/UFMsMVDic_I/AAAAAAAAALA/pxiacZ5GQaA/s320/12cR2_7.png" width="320" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/--GZc0ODkYIM/UFMsNCVapVI/AAAAAAAAALI/rBHAQLIKzxY/s1600/12cR2_8.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="240" src="http://4.bp.blogspot.com/--GZc0ODkYIM/UFMsNCVapVI/AAAAAAAAALI/rBHAQLIKzxY/s320/12cR2_8.png" width="320" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-Cy4XW0qAC4w/UFMsNZ-Ay-I/AAAAAAAAALM/kNXOQqZDVhI/s1600/12cR2_9.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="240" src="http://1.bp.blogspot.com/-Cy4XW0qAC4w/UFMsNZ-Ay-I/AAAAAAAAALM/kNXOQqZDVhI/s320/12cR2_9.png" width="320" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-h1ph4WDkJ9c/UFMsMftUclI/AAAAAAAAAK8/xLi4sccWVXg/s1600/12cR2_10.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="240" src="http://1.bp.blogspot.com/-h1ph4WDkJ9c/UFMsMftUclI/AAAAAAAAAK8/xLi4sccWVXg/s320/12cR2_10.png" width="320" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-qCpCPk8PXkI/UFMsMa9iXXI/AAAAAAAAALE/ys5xUy_rSzU/s1600/12cR2_11.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="240" src="http://4.bp.blogspot.com/-qCpCPk8PXkI/UFMsMa9iXXI/AAAAAAAAALE/ys5xUy_rSzU/s320/12cR2_11.png" width="320" /></a></div>
<br />
During installation process, repository database is also upgraded. <br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-zikNuBfkph0/UFHvtXZwr4I/AAAAAAAAAHw/UGIMjranZ9I/s1600/12cR2_1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><br /></a></div>
<div style="text-align: justify;">
4. After the upgrade process, as the second phase, you need to upgrade agents. Logon to OEM, from menu Setup -> Manage Cloud Control -> Upgrade Agents. Click "Add" and select agents to upgrade, then submit the job. </div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-LewCLwyxn0E/UFM9v6b34FI/AAAAAAAAAMI/KGufO-sly0Q/s1600/upgrade_agent_1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="144" src="http://2.bp.blogspot.com/-LewCLwyxn0E/UFM9v6b34FI/AAAAAAAAAMI/KGufO-sly0Q/s320/upgrade_agent_1.png" width="320" /></a></div>
<br />
<div style="text-align: justify;">
Linux based agents are marked as upgradable right away. However, when you
check not upgradable agent from the Upgrade Agents page, you will see
Windows based agent are listed. Because they need an extra
step. Agent software should be updated from Setup -> Extensibility
-> Self Update. Find the new versions of Microsoft Windows agents
under Agent Software and download them. After download (you may select
"Notify me" option for the download job), apply update. Following these
steps, you may find your Windows based agents in upgradable agents list.</div>
<br />
<div style="text-align: justify;">
Finally, if you have upgraded Linux based agents with a non-root user, you need to run root.sh script located in <agent_home>/core/12.1.0.2.0/</div>
<br />Unknownnoreply@blogger.com2tag:blogger.com,1999:blog-6550761899044120952.post-2488867776500382302012-09-13T18:14:00.000+03:002012-09-13T18:14:29.974+03:00NFS Sharing for LinuxSharing a directory using NFS to other Linux systems is a common and handy way. To be able to explain briefly, let me separate tasks into two as server and client side. I use Oracle Linux 6 and Oracle Linux 5 respectively as server and client, though just the opposite goes through same steps. However these commands should run on every Red Hat based system.<br />
<br />
On the server that you will create the share, edit the export file:<br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">$ vi /etc/exports</span><br />
<br />
Add line(s) to file such as:<br />
<i>/share_dir *(rw,sync,fsid=0) </i><br />
and/or<br />
<i>/shares/nfs 192.168.101.11(ro,sync,fsid=0)</i><br />
<br />
Pay attention not to use extra space on a line, it may cause a problem. Here, instead of "*" - which means sharing is for every one, to world - you can use client's IP or FQDN as shown above. Also if it's going to be read-only share write "ro" instead of "rw". We use "fsid=0" parameter not to get "mount.nfs4: Operation not permitted" error.<br />
<br />
Permissions of directory, "/share_dir" and/or "/shares/nfs", should be read and execute to others if it's a read-only share or read-write and execute to others if it's writable. Otherwise you will face with "mount.nfs4: Permission denied" error when mounting the share. So alter permissions of directory:<br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">$ chmod 757 /share_dir</span><br />
<br />
Restart related services (If your server is Linux 5, use portmap instead of portreserve. ):<br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">$ /etc/init.d/portreserve restart</span><br />
<span style="font-family: "Courier New",Courier,monospace;">$ /etc/init.d/nfs restart</span><br />
<br />
You can check shares by:<br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">$ showmount -e <server_ip> </span><br />
<span style="font-family: "Courier New",Courier,monospace;">$ exportfs -avr </span><br />
<br />
On the client simply mount the share by:<br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">$ mount -t nfs4 <server_ip_or_host_name>:/ /mnt</span><br />
<br />
Notice that, even the shared directory is "/share_dir" or "/shares/nfs" we use "/" only.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6550761899044120952.post-16917391549100611112012-08-29T11:31:00.001+03:002012-08-31T02:32:31.529+03:00Retrieving Index Usage StatisticsIndexes are old friends of every DBA. To check out what your friends are up to, in Oracle, basic way is enable monitoring for the index by:<br />
<div style="font-family: "Courier New",Courier,monospace;">alter index IX_FOO monitoring usage;</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div>Then you can query V$OBJECT_USAGE to see if it is being used. After a period of time meaningful to you, you can stop monitoring till the next time by:<br />
<div style="font-family: "Courier New",Courier,monospace;"><br />
alter index IX_FOO nomonitoring usage;</div><br />
However what is lacking in this way of monitoring is statistics. You can see whether index is used but cannot see how many times or how it is used. Fortunately Oracle provides many V$ views. The query below returns basic statistics about your indexes. How it is used (range or full scan etc.), how many times and when was the last time it was used:<br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">select sp.object_name as index_name,</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> sp.options,</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> sum(sa.executions) as executions#,</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> max(timestamp) as last_used</span><br />
<span style="font-family: "Courier New",Courier,monospace;">from v$sql_plan sp, v$sqlarea sa</span><br />
<span style="font-family: "Courier New",Courier,monospace;">where sa.address = sp.address</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> and sa.hash_value =sp.hash_value</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> and sp. operation = 'INDEX'</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> and object_owner in ('SCOTT')</span><br />
<span style="font-family: "Courier New",Courier,monospace;">group by object_name, options</span><br />
<span style="font-family: "Courier New",Courier,monospace;">order by 3 desc</span>;<br />
<br />
With Exadata, we've started to use less indexes and even dropped some existing ones. Because we want to use Exadata features like storage indexes and smart scan instead, we let the cells do their job. On Exadata we prefer to see fast full scans instead of range scans. To see Exadata specific statistics related to indexes, you can add columns with prefix "io_cell" in V$SQLSTATS or V$SQLAREA views. Such as:<br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">select sp.object_name as index_name,</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> sp.options,</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> sum(sa.executions) as executions#,</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> max(timestamp) as last_used,</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> sum(sa.disk_reads) as disk_reads,</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> sum(sa.direct_writes) as direct_writes,</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> sum(sa.io_cell_offload_eligible_bytes)/1024/1024 as offloadable_mb,</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> sum(sa.io_cell_offload_returned_bytes)/1024/1024 as offloaded_mb,</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> sum(sa.rows_processed) as rows_processed</span><br />
<span style="font-family: "Courier New",Courier,monospace;">from v$sql_plan sp, v$sqlarea sa</span><br />
<span style="font-family: "Courier New",Courier,monospace;">where sa.address = sp.address</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> and sa.hash_value =sp.hash_value</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> and sp. operation = 'INDEX'</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> and object_owner in ('SCOTT')</span><br />
<span style="font-family: "Courier New",Courier,monospace;">group by object_name, options</span><br />
<span style="font-family: "Courier New",Courier,monospace;">order by 3 desc;</span><br />
<br />
There are many other statistics you can find in V$ views, so you can improve the query according to your needs.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6550761899044120952.post-9279601078316210322012-07-30T11:22:00.002+03:002012-07-30T18:17:58.648+03:00ORA-08104 While Rebuilding Index<span style="color: #333333;">If a session has failed or cancelled by user while rebuilding an index online, when another user/session tries to rebuild or drop the same index may face with the following error:</span><br />
<br />
<span style="color: #333333;"><span style="color: red;">ORA-08104: this index object 123456 online is being built or rebuilt</span> </span><br />
<br />
<span style="color: #333333;">The reason beneath is that data dictionary has been left in a state reflecting a rebuild is on going, in fact it is not.</span><br />
<br />
<span style="color: #333333;">To solve the issue simply run the following:</span><br />
<br />
<span style="color: #333333;"><span style="font-family: "Courier New",Courier,monospace;">DECLARE </span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> RetVal BOOLEAN;</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> OBJECT_ID BINARY_INTEGER;</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> WAIT_FOR_LOCK BINARY_INTEGER;</span><br style="font-family: "Courier New",Courier,monospace;" /><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">BEGIN </span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> OBJECT_ID := 123456;</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> WAIT_FOR_LOCK := NULL;</span><br style="font-family: "Courier New",Courier,monospace;" /><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN (OBJECT_ID);</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> COMMIT; </span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">END; </span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">/</span> </span>Unknownnoreply@blogger.com0