tag:blogger.com,1999:blog-322773032024-03-13T04:23:15.405-05:00Oracle by MadridUnknownnoreply@blogger.comBlogger77125tag:blogger.com,1999:blog-32277303.post-6987502266926916912016-08-11T17:11:00.003-05:002016-08-11T17:11:43.256-05:00Temporary tablespace takes ages to dropA simple command like 'DROP TABLESPACE TEMP;' in a regular database can become excessively slow. The reason behind this is quite simple, even if the users defaults to a different temporary tablespace there could still be sessions holding temporary segments there. It's just a matter of finding them and killing them to free the blocks on the temporary segments and you're good to go.<br />
<br />
The operation of dropping the temporary tablespace looks like this on Enterprise Manager:<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhdGYqUiG2s3gl9we326mgJig1a_z5ddNLkF9z1Ny2jle3GIGr2ZOFp8aAWonI_M7bfDU4Giz4_vs54Zi1z6z_FJfPgvuf2i27SOzp2ejN2kUARI_Tmn2Wdvkt-HcBVVNzGwijmtw/s1600/tmpTSIssue.jpg" imageanchor="1"><img border="0" height="280" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhdGYqUiG2s3gl9we326mgJig1a_z5ddNLkF9z1Ny2jle3GIGr2ZOFp8aAWonI_M7bfDU4Giz4_vs54Zi1z6z_FJfPgvuf2i27SOzp2ejN2kUARI_Tmn2Wdvkt-HcBVVNzGwijmtw/s640/tmpTSIssue.jpg" width="640" /></a><br />
<div style="text-align: left;">
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div style="text-align: left;">
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,</span></div>
<div style="text-align: left;">
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">a.username,a.osuser, a.status</span></div>
<div style="text-align: left;">
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">FROM v$session a,v$sort_usage b</span></div>
<div style="text-align: left;">
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">WHERE a.saddr = b.session_addr</span></div>
<div style="text-align: left;">
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">order by tablespace;</span></div>
<br />
Sessions still reported using the target temporary tablespace have to be killed.<br />
<br />
After it is unlocked the process runs immediately.<br />
<br />
<br />
<br />
<br />Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-32277303.post-36922712828969898702016-08-11T15:29:00.002-05:002016-08-11T15:38:11.250-05:00ORA-00600 [2663], [12], ....After not having spotted ORA-00600 for a while a new one showed up. <br />
<br />
ORA-00600: internal error code, arguments: [2663], [12], [3665664941], [12], [3884717804], [], [], [], [], [], [], []<br />
<br />
<br />
This happened out of the blue after a switchover back from a Datagurad environment. Basically this is due to a bug, the patch is already included in 11.2.0.2.0 and 11.2.0.3.0 patch sets, but it is not enabled by default, it is activated after setting a parameter in the spfile (pfile) parameter file.<br />
<br />
<br />
alter system set "_ktb_debug_flags"=8 ;<br />
<div>
<br /></div>
<div>
<br /></div>
<div>
Fixed.</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-32277303.post-591908152914586512014-12-09T16:49:00.001-06:002014-12-09T16:49:57.058-06:00ORA-00600 [kdiRelocateCommitted:DBAupd]This error recently showed up in a 11.2.0.3.0 Enterprise Edition RDBMS on Oracle Enterprise Linux: ORA-00600: internal error code, arguments: [kdiRelocateCommitted:DBAupd], [2126573183], [], [], [], [], [], [], [], [],<br />
[], []<br />
Incident details in: /u01/app/oracle/diag/rdbms/ ....._ora_5642_i10412.trc<br />
<br />
After the trace file I was able to identify which operation produced the error and identify the specific partition that had problems. The error was triggered during a partitioned index maintenance operation:<br />
<br />
alter index INDEX_OWNER.INDEX_NAME modify partition PART_NAME shrink space<br />
*<br />
ERROR at line 1:<br />
ORA-00600: internal error code, arguments: [kdiRelocateCommitted:DBAupd], [2063617647], [], [], [], [], [], [], [], [], [], []<br />
<div>
<br /></div>
<div>
The error is not documented, and so far Oracle Support doesn't have information about this error</div>
<div>
<br /></div>
<div>
The workaround was to rebuild the index. After rebuilding it the same operation produced no error.</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-32277303.post-9411587205518617302014-01-22T16:55:00.000-06:002014-01-23T07:35:52.920-06:00ORA-12012: error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB"Note: This particular scenario happened on an Oracle 11g Rel.2 (11.2.0.3.0) RAC on OEL6 x64 with Active Dataguard.<br />
<br />
The following error shows on a scheduled basis<br />
<br />
<b>ORCL1:</b><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">Sat Jan 18 23:00:06 2014</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">Errors in file /u01/app/oracle/diag/rdbms/orcl/ORCL1/trace/ORCL1_j008_6097.trc:</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">ORA-12012: error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB"</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">ORA-06502: PL/SQL: numeric or value error</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">ORA-06512: at "DBSNMP.BSLN_INTERNAL", line 2073</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">ORA-06512: at line 1</span><br />
<br />
<b>Reason:</b><br />
The underlying table required as part of one of the schedule maintenance tasks has invalid values. <br />
The related table is <b>DBSNMP.BSLN_BASELINES. </b><br />
<br />
After taking a look at the job log we see the failed execution attempts:<br />
<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">SQL> select log_date,status from dba_scheduler_job_run_details </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> where job_name='BSLN_MAINTAIN_STATS_JOB';</span><br />
<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">LOG_DATE STATUS</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">------------------------------------- ------------------------------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">11-JAN-14 11.00.07.315077 PM -06:00 FAILED</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">04-JAN-14 11.00.05.595559 PM -06:00 FAILED</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">18-JAN-14 11.00.06.554385 PM -06:00 FAILED</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">28-DEC-13 11.01.05.966337 PM -06:00 FAILED</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><b style="background-color: yellow;">22-JAN-14 03.36.50.995888 PM -06:00 FAILED</b></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">5 rows selected.</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span>
<b>FIX Procedure:</b><br />
Look for the values on the table, detect the invalid values and remove them.<br />
<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">SQL> select * from DBSNMP.BSLN_BASELINES;</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span>
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> DBID INSTANCE_NAME BASELINE_ID BSLN_GUID TI A STATUS LAST_COMPUT</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">---------- ---------------- ----------- -------------------------------- -- - ---------------- -----------</span><br />
<span style="background-color: yellow; font-family: Courier New, Courier, monospace; font-size: xx-small;">3038864366 orcl 0 0A03A0424F06F7E6B3841C6CC0999F7C NW N ACTIVE 131027:0000</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">3038864366 ORCL2 0 A396EEB2AB3A39AF477DC4A1AEE70CC9 ND Y ACTIVE 131228:2300</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">3038864366 ORCL1 0 176C36D62D62855075AD0BBD90B2DA30 ND Y ACTIVE 131228:2300</span><br />
<br />
You must know exactly which row you're deleting, in my very particular case I know which rows do not make sense since this database was recently involved in a fail over scenario and the former name is still being held in the table.<br />
<br />
<br />
The corrupt row is removed with a simple DELETE command:<br />
<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">SQL> DELETE FROM DBSNMP.BSLN_BASELINES WHERE INSTANCE_NAME ='orcl';</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">1 row deleted.</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">SQL> commit;</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">Commit complete.</span><br />
<br />
Manually re-execute the job and check the execution log, it must show the job executed successfully. It takes a couple of minutes after execution to show the results in the log table.<br />
<br />
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">SQL> exec dbms_scheduler.run_job('BSLN_MAINTAIN_STATS_JOB',false);</span><br />
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">PL/SQL procedure successfully completed.</span><br />
<br />
<br />
The issue was fixed, validate it by querying he DBA_SCHEDULER_JOB_RUN_DETAILS view.<br />
<div>
<br /></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">SQL> select log_date,status from dba_scheduler_job_run_details </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> where job_name='BSLN_MAINTAIN_STATS_JOB';</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">LOG_DATE STATUS</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">------------------------------------- ------------------------------</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">11-JAN-14 11.00.07.315077 PM -06:00 FAILED</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">04-JAN-14 11.00.05.595559 PM -06:00 FAILED</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">18-JAN-14 11.00.06.554385 PM -06:00 FAILED</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">28-DEC-13 11.01.05.966337 PM -06:00 FAILED</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">22-JAN-14 03.36.50.995888 PM -06:00 FAILED</span></div>
<div>
<b><span style="background-color: yellow; font-family: Courier New, Courier, monospace; font-size: x-small;">22-JAN-14 03.41.20.714453 PM -06:00 SUCCEEDED</span></b></div>
</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-32277303.post-69356660548719619792013-09-22T16:23:00.001-05:002013-09-24T01:13:11.822-05:00The event has started<div><br></div><div><br></div>Oracle has been an industry leader for decades already, the two key factors, innovation and aquisitions have been the elements that Larry Ellison has known well how to manage. Oracle (ORCL) is a company who has the complete set of products, ranging from Hardware, Operating System, Database, Middle Tier, and Applications. The product list every year grows and keeps on growing to the point that it's becoming difficult to even number them. <div>The database features every new release are increased, over 100 new fetures can be listed every new major release. </div><div>The Oracle Open World is the main Oracle event that shows the trends, new products, where partners, customers and peers converge to exchange knowledge, show new products, and where Larry Ellison unveils new surprises.</div><div>The name of the event is where you can get an idea about what will be the main attraction of the Oracle Open World.</div><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivUA1ZIJSVrNoxTL_pgcHVN04_PpD0yGVfHGfH5ZEBWlEIIAsJH5UR15A4MU-qi3MItjjNneYx0z0kIAxmNuwOlm6eKt4OnBVJZ68bOxTF1aatvoV2Tv4OKRfmBrLbbTJ_YNBbLQ/s640/blogger-image-1191131653.jpg" imageanchor="1" style="-webkit-tap-highlight-color: rgba(26, 26, 26, 0.296875); -webkit-composition-fill-color: rgba(175, 192, 227, 0.230469); -webkit-composition-frame-color: rgba(77, 128, 180, 0.230469); margin-left: 1em; margin-right: 1em; "><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivUA1ZIJSVrNoxTL_pgcHVN04_PpD0yGVfHGfH5ZEBWlEIIAsJH5UR15A4MU-qi3MItjjNneYx0z0kIAxmNuwOlm6eKt4OnBVJZ68bOxTF1aatvoV2Tv4OKRfmBrLbbTJ_YNBbLQ/s640/blogger-image-1191131653.jpg"></a></div><div><br></div>This #OOW13 is about <b>"Hardware and Software Engineered to Work Together"</b>. The trend since Oracle aquired Sun a few years ago has been to make the hardware work with the software, a home made whole system under a single name. Nobody in the industry has the same capability to offer the complete set at the high end level under a real single brand, but Oracle.<div><br></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-32277303.post-54319782425992329062013-09-17T15:06:00.000-05:002013-09-17T15:06:11.761-05:00OpenSSH failed to add the hosts to the list of known hosts<br />
<b>Error message: OpenSSH failed to add the hosts to the list of known hosts</b><br />
<br />
<b>Environment</b>: <br />
Workstation: Windows 7 x64 Authenticated with domain server<br />
OpenSSH: OpenSSH for Windows v3.8.1p1-1 x64<br />
<br />
This issue has been going on for a while in my machine, what is boring is that every time I try to log on to any of my Linux servers the same message appears, asking for confirmation and then showing the error, which prevents me from performing batch tasks and scripts from my workstation against the Linux servers. <br />
<br />
The way to solve this basically has to do with an environment variable that has to be declared, OpenSSH doesn't know the value of <b>$HOME,</b> which is equivalent to the <b>%USERPROFILE%</b> windows variable. So it has to be declred.<br />
<br />
Set a Windows User environment variable named HOME that points to the %USERPROFILE%:<br />
<ol>
<li>Open the Control Panel > System and Security > System</li>
<li>Click on the Advanced System Settings</li>
<li>Click the Environment Variables</li>
<li>In the User variables box click on the "New" Button</li>
<li>Declare a variable named HOME and set its value to %USERPROFILE%</li>
<li>Accept and click OK</li>
</ol>
Open a new DOS windows and retry, this time instead of the error you should see a warning letting you know the host has just been added:<br />
<br />
<span style="font-size: x-small;"><span style="font-family: "Courier New",Courier,monospace;">C:\Users\HMadrid\.ssh>ssh oracle@myHost</span></span><br />
<br /><span style="font-size: x-small;"><span style="font-family: "Courier New",Courier,monospace;">The authenticity of host 'myHost (192.168.XX.XX)' can't be established.<br />RSA key fingerprint is xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx.<br />Are you sure you want to continue connecting (yes/no)? yes<br /><span style="background-color: yellow;">Warning: Permanently added 'myHost,192.168.xx.xx' (RSA) to the list of known hosts.</span><br />oracle@MyHost's password:<br />Last login: Mon Sep 16 17:33:58 2013 from 192.168.xx.xx<br />[oracle@stwrac1 ~]$ exit<br />logout<br /><br />Connection to myHost closed.</span></span><br /><br /><br />
<br />
<br />
<br />Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-32277303.post-76699433928240474992012-10-04T12:03:00.001-05:002012-10-04T12:03:45.552-05:00100,000 + hits for Oracle by MadridRecently this professional effort known as "Oracle by Madrid" reached 100,000+ hits, I'm grateful to the Oracle community for making this possible, this blog has created a positive synergy in many ways.<br />
<br />
Once I heard someone who said <i>"When you teach you learn twice"</i>, and it is very true, helping others through this blog has in the long run helped me too. This blog has been a valuable reference for me too, while "googling" for a specific issue I'm facing sometimes I find the answer in a post I made in the past that I didn't remember, so I am not doomed to repeat my past. <br />
<br />
Another saying I remember, <i>"You receive what you give"</i>, very true, by giving away knowledge somehow life has rewarded me without looking for it, as if life would have kept some sort of invisible balance sheet where it records everything, and it pays back when the time is right.<br />
<br />
I am grateful and happy this effort has had a positive echo with the Oracle Community and has been a reference to help others around the globe.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgF3itp6sPs8u8yripKnXHLxo5AangdBXQTpgT3ljIBP1VYj6EkEQyIHIHC04ktsUiexB_vSZ17k8vV-4lxqmVAtp3x4FzmBZHeG5oC8j0G23u3KpMRUmCKWouf9thQ4lOXlWbEhA/s1600/ClusterMaps+(2).bmp" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="120" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgF3itp6sPs8u8yripKnXHLxo5AangdBXQTpgT3ljIBP1VYj6EkEQyIHIHC04ktsUiexB_vSZ17k8vV-4lxqmVAtp3x4FzmBZHeG5oC8j0G23u3KpMRUmCKWouf9thQ4lOXlWbEhA/s320/ClusterMaps+(2).bmp" width="320" /></a></div>
<br />
<br />
<br />
<br />
A Big Thank You All !!!<br />
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-32277303.post-83642234901399024602012-09-27T15:56:00.001-05:002012-09-27T15:56:17.047-05:00iOS6 removes the YouTube appI recently received a notice about the availability of the iOS6 upgrade for my iPad. I thought it was cool to see the upgrade and install it. After a warning related to the battery level (at least 50% or plugged in to the electric outlet) in my iPad I proceeded to download it and have it installed.<br />
<br />
Everything was just fine, in my opinion there's not too many new functionality I could take advantage of at this time, and there were some new apps installed and yes, a very cool world clock similar to that one I used to have in my iPod. And the maps application, an absolute divorce decree for Apple, they have their own. The addition of the location services (optional), which sounds to me more like an Android like behavior. Is Apple secretly overtly trying to emulate the Google's success formula? <br />
<br />
What it came to my attention was that the YouTube application had disappeared. I looked for it all around, and it was gone. I thought I may have accidentally removed it. I tried to look for it at the apple store, without any luck and finding more than 200 hits related with youTube like applications. And ... after "googling" it for a while I found that this is "normal". The YouTube application was removed from it because Apple is planning to design one of their own that will replace the traditional YouTube app. May be Apple didn't have his own iYouTube ready on time with the release of iOS6, and this could have made the users more anxious than not being able to play their cool playlists.<br />
<br />
I've tracked the Apple share in the stock market (NASDAQ:AAPL) for a while, extrapolating the behavior of it correlated to the release of the iPhone5 and the future marketing strategies of Apple, and this made me remember the story of the Tulips in Holland ( http://www.damninteresting.com/the-dutch-tulip-bubble-of-1637 ) where there was a huge euphoria about the Viceroy Tulip bulbs to the point that a trader could make a profit of an equivalent of $60,000 USD today a month, and the price of a Tulip could be worth the price of a house. And when this temporary madness was replaced by the reasoning the bubble was bursted and the price of the Tulips felt all the way down creating a big depression in the Dutch economy.<br />
<br />
After Apple released the iPhone5 its share reached the all time top value of $700.00 USD, as soon as it was announced that even though the sales of it were good but not as good as expected the price retreated to $665.00 per share. If Apple cannot be innovative and creative once more, people may see the Apple bubble burst. So what's the next move? why is Apple divorcing from specific apps? Is their marketing strategy focused towards emulating the discrete giant steps of Google? Is the Steve Job's iCar something that is going to surprise the market once more? ( http://www.stuff.co.nz/motoring/6958816/Steve-Jobs-wanted-to-build-Apple-iCar ), is the new iYouTube app going to be available to watch movies in the built in retina touchscreen in the car? will the aux audio device allow compatibility with pre-iWhatever devices? or you'll have to buy an adapter for it too?<br />
<br />
In my personal opinion, Apple has reached a historic top level where it has to reinvent itself once more and after the iPhone5 the next step cannot be an iPhone6, 7 ... 20, cause otherwise the market will be saturated. So to prevent the investors from looking elsewhere they'll have to open new markets. May be it's about time to change the way we watch T.V. In the mean time as long as the common saying reads "I'll buy it if it is shiny and made by Apple" keeps on being a valid success formula, Apple will keep on being a growing giant, no matter if they "forgot" to warn about the YouTube omission. <br />
<br />
<br />Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-32277303.post-9132999327309363582012-09-13T16:33:00.005-05:002012-09-13T16:35:35.077-05:00Oracle 11.2.0.3.0 PSU 9 Post-Install issues<span style="font-size: small;"><b><span style="font-family: Verdana,sans-serif;">Platform: Windows 2003 R2 x64 </span></b></span><br />
<span style="font-family: Verdana,sans-serif; font-size: small;"><b>Version : Oracle 11.2.0.3.0 PSU 9</b></span><br />
<span style="font-size: small;"><br /></span>
<span style="font-family: Verdana,sans-serif; font-size: small;">After applying the PSU the DBMS_MEATADA didn't work.</span><br />
<span style="font-size: small;"><br /></span>
<span style="font-family: Verdana,sans-serif; font-size: small;"><br /></span>
<span style="font-family: Verdana,sans-serif; font-size: small;"><span style="font-family: "Courier New",Courier,monospace;">SQL> select DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT') from DUAL;<br />ERROR:<br /><b>ORA-39212: installation error: XSL stylesheets not loaded correctly</b><br />ORA-06512: at "SYS.DBMS_METADATA", line 5088<br />ORA-06512: at "SYS.DBMS_METADATA", line 7589<br />ORA-06512: at line 1</span></span><br />
<span style="font-family: Verdana,sans-serif; font-size: small;"><br /></span>
<br />
<span style="font-family: Verdana,sans-serif; font-size: small;">A side effect of this, when trying to execute DataPump it failed, since this requries DBMS_METADATA to extract the object metadata.<br /><br /><span style="font-family: "Courier New",Courier,monospace;">C:\> expdp aUser/aPassword schemas=SCOTT dumpfile=SCOTT logfile=SCOTT job_name=SCOTT<br /><br />Export: Release 11.2.0.3.0 - Production on Thu Sep 13 11:15:16 2012<br /><br />Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.<br /><br />Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production<br />With the Partitioning, Automatic Storage Management, OLAP, Data Mining<br />and Real Application Testing options<br /><b>ORA-39006: internal error</b><br /><b>ORA-39213: Metadata processing is not available</b></span></span><br />
<span style="font-family: Verdana,sans-serif; font-size: small;">The reason is because after applying the PSU according to the MOS note: "Data Pump Export Started Failing After Applying CPU Patch [ID 453796.1]" some tables required by Data Pump are not loaded and the XML Style sheets for DBMS_METADATA are not loaded either.<br /><br />The dictionary tables required by Data Pump are not loaded. <br />If you execute this query:<br /><br /><span style="font-family: "Courier New",Courier,monospace;">SQL> select count(*) from metanametrans$;<br /><br /> COUNT(*)<br />----------<br /> 0</span><br /><br />and if there are no rows returned then you'll have to proceed as stated in the MOS note.<br /><br />The procedure to fix this issue is listed in that MOS Note, so since you found this error after applying a PSU or a CPU, then it means you have access to My Oracle Support, just look for the previously referred MOS Note and you'll see what's the procedure to fix this issue.<br /><br />Once the procedure has been applied, the above query returns the following result:<br /><br /><span style="font-family: "Courier New",Courier,monospace;">SQL> -- Oracle 11g Rel.2 (11.2.0.3.0 PSU9)<br />SQL> select count(*) from metanametrans$;<br /><br /> COUNT(*)<br />----------<br /> 3302</span><br /><br /><br />And both, the DBMS_METADATA and Data Pump work like a charm.</span><br />
<br />Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-32277303.post-40383886631954100892012-09-12T10:27:00.001-05:002012-09-13T16:35:50.122-05:00OPatch 11.2.0.3.0 issues<span style="font-family: Verdana,sans-serif; font-size: small;">Recently I have applied the PSU5 and PSU9, in both cases a couple of issues happened when executing the OPatch utility. </span><br />
<span style="font-family: Verdana,sans-serif; font-size: small;"><br /></span>
<span style="font-family: Verdana,sans-serif; font-size: small;"><b>msvcr71.dll</b></span><br />
<span style="font-family: Verdana,sans-serif; font-size: small;">The first one, has to do with a runtime error during the execution of the OPatch utility. Suddenly a window pops up saying it cannot find the msvcr71.dll library, this is a module containing standard C library functions such as asprintf, memcpy and cos, it is part of the Microsoft C Runtime Library. The workaround for it was to look for it at one of the previously installed Oracle Homes and copy it to the C:\Windows directory. </span><br />
<span style="font-family: Verdana,sans-serif; font-size: small;"><br /></span>
<span style="font-family: Verdana,sans-serif; font-size: small;"><b><span id="pt1:sd_r1:0:dv_rDoc:ot71" style="color: black;">%ORACLE_HOME%\oui\jlib\srvm.jar</span></b></span><br />
<span style="font-family: Verdana,sans-serif; font-size: small;"><span id="pt1:sd_r1:0:dv_rDoc:ot71" style="color: black;"> The second issue has to do with a file, srvm.jar, which after the OPatch evaluation, it says this file is in use. It looks like the 11.2.0.3.0 version of the OPatch utility cannot identify it is the same utility who is using the file. This is a known issue and is documented in the M.O.S. Note "OPATCH 11.2.0.3.0 LOCKS SRVM.JAR WHEN APPLYING ANY 11.2.0.3 BUNDLE PATCH ON WINDOWS X64 [ID 1446273.1]" This will be fixed in future OPatch versions, and the workaround is to look for a jar file, %ORACLE_HOME%\OPatch\jlib\oracle.opatch.classpath.jar, rename it and retry. Once the OPatch utility is done rename back the file to its original name.</span></span><br />
<span style="font-family: Verdana,sans-serif; font-size: small;"><span id="pt1:sd_r1:0:dv_rDoc:ot71" style="color: black;"><br /></span><span id="pt1:sd_r1:0:dv_rDoc:ot71" style="color: black;">You can use the Microsoft utility Process Explorer to see who is using these files. Just make sure that you exit the utility before relaunching OPatch, otherwise it will misinterpret the locks of the Process Explorer on several oracle related dll's and the list of Oracle dynamic linked libraries in use will be longer.</span></span>
<br />
<span id="pt1:sd_r1:0:dv_rDoc:ot71" style="color: black; font-size: x-small;"><br /></span>Unknownnoreply@blogger.com2tag:blogger.com,1999:blog-32277303.post-7968956471347337612012-08-02T19:51:00.000-05:002012-08-02T19:51:00.612-05:00Justin Kestelyn announces new professional goals.Justin Kestelyn, a key element for the Oracle community announced he will leave Oracle, I do personally want to publicly thank Justin for his remarkable and inspiring contribution throughout the years to the Oracle community, the Oracle Technology Network, the Oracle ACE program, the Oracle Magazine, the Oracle Open World events, among many other numerous and highly valuable technical and administrative efforts which make a huge difference.<br />
<br />
Thank you Justin and Good Luck with your new professional goals.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-32277303.post-55773036007901489382012-06-22T16:25:00.002-05:002012-06-22T16:37:32.680-05:00ORA-16792 configuration property value is inconsistent with database settingAs per the reference this error literally means:<br />
<br />
<b>Cause</b>: The values of one or more configuration properties were inconsistent
with database in-memory settings or server parameter file settings. This
may happen by altering initialization parameters directly instead of
altering property values using Data Guard broker.<br />
<br />
<b>Actions</b>: Query property the InconsistentProperties on the database or check the
Data Guard broker log to find which properties are set inconsistently.
Reset these properties to make them consistent with the database
settings. Alternatively, enable the database or the entire configuration
to allow the configuration property settings to be propagated to to the
initialization parameters.<br />
<br />
When the dataguard broker was queried about the status, it showed the following results:<br />
<br />
<span style="font-size: x-small;"><span style="font-family: "Courier New",Courier,monospace;">DGMGRL> <b>show configuration verbose</b></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;">Configuration - OMCDG1120</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;"> Protection Mode: MaxPerformance</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> Databases:</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> OMCPRO - Primary database</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> OMCADG - Physical standby database</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> Warning: ORA-16792: configurable property value is inconsistent with database setting</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;"> Properties:</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> FastStartFailoverThreshold = '30'</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> OperationTimeout = '30'</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> FastStartFailoverLagLimit = '30'</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> CommunicationTimeout = '180'</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> FastStartFailoverAutoReinstate = 'TRUE'</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> FastStartFailoverPmyShutdown = 'TRUE'</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> BystandersFollowRoleChange = 'ALL'</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;">Fast-Start Failover: DISABLED</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;">Configuration Status:</span><br style="font-family: "Courier New",Courier,monospace;" /><b><span style="font-family: "Courier New",Courier,monospace;">WARNING</span></b></span><br />
<br />
<br />
The parameter involved in this particular case was db_file_name_convert, the paths defined at the spfile were different from those known to the Dataguard Broker. db_file_name_convert is a static parameter which means the standby database had to be restarted so the parameter was effective. The actual actions that took place to solve this issue were to set the parameter at the database level and modify the value at the dgmgrl prompt.<br />
<br />
Step 1. Modify the instance parameter.<br />
<div style="font-family: "Courier New",Courier,monospace;">
<b><span style="font-size: x-small;">alter system set db_file_name_convert='C:\Oracle11g\Oradata\OMCPro', 'J:\Oracle11g\Oradata\OMCDG', 'D:\Oracle\Oradata\OMCPro', 'D:\Oracle\Oradata\OMCDG' scope=spfile;</span></b></div>
<br />
Step 2. Modify the parameter at the DGMgrl prompt<br />
<div style="font-family: "Courier New",Courier,monospace;">
<span style="font-size: x-small;">C:\> <b>dgmgrl</b></span></div>
<div style="font-family: "Courier New",Courier,monospace;">
<br /></div>
<div style="font-family: "Courier New",Courier,monospace;">
<span style="font-size: x-small;">DGMGRL> <b>edit DATABASE "OMCADG" SET PROPERTY DbFileNameConvert = 'C:\Oracle11g\Oradata\OMCPro, J:\Oracle11g\Oradata\OMCDG, D:\Oracle\Oradata\OMCPro, D:\Oracle\Oradata\OMCDG' ;</b></span></div>
<div style="font-family: "Courier New",Courier,monospace;">
<span style="font-size: x-small;"><br />Property "dbfilenameconvert" updated</span> </div>
<br />
Once the parameter is consistent both at the spfile and the Dataguard broker it updates its status to <b>SUCCESS</b>.<br />
<br />
<span style="font-size: x-small;"><span style="font-family: "Courier New",Courier,monospace;">DGMGRL> <b>SHOW CONFIGURATION VERBOSE;</b></span><b><br style="font-family: "Courier New",Courier,monospace;" /></b><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">Configuration - </span></span><span style="font-family: "Courier New",Courier,monospace; font-size: x-small;">OMCDG1120</span><span style="font-size: x-small;"><br style="font-family: "Courier New",Courier,monospace;" /><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> Protection Mode: MaxPerformance</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> Databases:</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> OMCPRO - Primary database</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> OMCADG - Physical standby database</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;"> Properties:</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> FastStartFailoverThreshold = '30'</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> OperationTimeout = '30'</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> FastStartFailoverLagLimit = '30'</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> CommunicationTimeout = '180'</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> FastStartFailoverAutoReinstate = 'TRUE'</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> FastStartFailoverPmyShutdown = 'TRUE'</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> BystandersFollowRoleChange = 'ALL'</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;">Fast-Start Failover: DISABLED</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;">Configuration Status:</span><br style="font-family: "Courier New",Courier,monospace;" /><b><span style="font-family: "Courier New",Courier,monospace;">SUCCESS</span></b></span><br />
<br />
<br />
<br />Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-32277303.post-56532025084296100712012-06-18T15:15:00.000-05:002012-06-18T15:15:36.984-05:00Flash Player 11.3 kept crashing with Firefox 13.01The browser I use the most is Firefox, a personal reason that started back at the time when the way to avoid internet worms from infecting the computer was to either endlessly keep on updating the I.E. or by using low maintenance reliable browser. Ever since Firefox became my preferred choice.<br />
<br />
Recently something weird happened, one of those auto updates that bring down the system and ask for restart of applications, and suddenly my Firefox stopped displaying the Enterprise Manager performance graphs. I had to temporarily switch to Internet Explorer while I was able to find a solution for it. The funny thing is that it only replied with a kind of progress bar that read RSL Error 1 of 3 or RSL Error 2 of 3 and a small caps error message at the bottom of the display where the graph was supposed to be shown that read Error #2032. Nothing else ... so after a long Google search that lasted the whole morning, digging for a clue on what was going on I finally came up with a diagnostics and a possible solution.<br />
<br />
Every time I tried to load a page with flash player contents it crashed, and in the case of YouTube it just mentioned the contents couldn't be played at this time and it asked to retry later (some sort of insanity, it doesn't matter how many times I'd have tried nor how later I'd retried the result would have been always the same). So none of the contents that required the flash player plugin could be played. <br />
<br />
The solution was found in this Firefox support article <a href="http://support.mozilla.org/en-US/kb/keep-flash-up-to-date-and-troubleshoot-problems" target="_blank">"Flash 11.3 doesn't load video in Firefox"</a><br />
I had installed the Real Player and the feature to download and save flash contents was making the Flash 11.3 plugin to crash. The solution, just as it was described in the above article was to start the RealPlayer and from the main menu / Preferences / Download & Recording submenu, I unchecked the "Enable web download & recording for these installed browsers" option. I restarted Firefox and everything came back to normal. Since this is my work machine, I'm not too much worried about not being able to download my favorite videos by means of the Realplayer plugin. I'm much more concerned about being able to display my Performance graphs and accessing the flash version of My Oracle Support.<br />
<a href="http://support.mozilla.org/en-US/kb/keep-flash-up-to-date-and-troubleshoot-problems" target="_blank"><br /></a>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-32277303.post-87057786392965280682012-01-19T12:14:00.003-06:002012-01-19T12:18:50.110-06:00ORA-00600 [kgiinb_invalid_obj]Another ORA-00600 error:<br /><br />This error was recently reported to me,<br /><br /><strong>ORA-00600: internal error code, arguments: [kgiinb_invalid_obj],</strong> [0x102BE8B38],<br />[0x12CF3EED8], [], [], [], [], [], [], [], [], []at<br />Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode,<br />OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object<br />src, String procedure ....<br /><br />Oracle Server - Enterprise Edition - Version: 11.1.0.7<br />This problem can occur on any platform.<br />Oracle Server Enterprise Edition - Version: 11.1.0.7<br /><br />After some research I found that basically this is due to a bug (7420394) which can be fixed by applying the patch for it, or upgrading to 11.2.0.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-32277303.post-27805111275790804892012-01-17T09:23:00.002-06:002012-01-17T09:35:45.264-06:00ORA-00700 [kesqsMakeSql-invstat:cpuTime]ORA-00700: soft internal error, arguments: [kesqsMakeSql-invstat:cpuTime], [], [], [], [], [], [], [<br />] ... hmmm ... it looks impressive, overall considering that an ORA-00600 is not one that probably is going to make your DBA day.<br /><br />Actually it is not as tragic and epic as it could seem to be at first glance, it has to do with a bug. In this particular case it showed up in an Oracle 11g Rel.1 (11.1.0.7.0) version on a x64 windows platform. This error is triggered when the SYS_AUTO_SQL_TUNING_TASK runs, and it may spontaneously show up and keep on showing up for a while. It may happen to be annoying.<br /><br />The root reason is the Bug 7025700 , and some other related bugs, Bug 7757533, Bug 8224438, Bug 7643188, the arguments of the ORA-00700 may be slightly different: [KESQSMAKESQL-INVSTAT:CPUTIME], or [KESQSMAKESQL-INVSTAT:ELPSTIME], and the solution is to apply the patch 7643188 or apply the PSU patch to upgrade to 11.1.0.7.2.<br /><br />Applying the patch will fix the problem from occurring in the future, but it won't fix the current corrupted data at the internal statistics tables, but eventually those corrupt rows will be flushed away, so in the mean time you'll have jut to get used to acknowledge it and manually clear the errrors at your E.M. Console.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-32277303.post-77448241152195726882011-12-16T11:27:00.002-06:002011-12-16T12:22:25.258-06:00How ORA-13236 was about to steal ChristmasRecently an error was reported to me ...<br /><br /><span style="font-size:85%;"><span style="font-family: courier new;">Message: ORA-00604: error occurred at recursive SQL level 1</span><br /><span style="font-family: courier new;">ORA-13236: internal error in R-tree processing: [insertion at root (mdrbin_mem_ins_rt)]</span><br /><span style="font-family: courier new;">ORA-13236: internal error in R-tree processing: [partition and pair bucket (mdrbin_optmz_mem_ins_node)]</span><br /><span style="font-family: courier new;">ORA-13236: internal error in R-tree processing: [pair buckets (mdrbin_partition_pair_bckts)]</span><br /><span style="font-family: courier new;">ORA-13236: internal error in R-tree processing: [mdrugnd - getting a node (mdrbin_pair_bckts)]</span><br /><span style="font-family: courier new;">ORA-13234: failed to access R-tree-index table [MDRT Table]</span><br /><span style="font-family: courier new;">ORA-29400: data cartridge error</span><br /><span style="font-family: courier new;">Error - OCI_NODATA</span><br /><span style="font-family: courier new;">ORA-06512: at "MDSYS.SDO_IDX", line 149</span><br /><span style="font-family: courier new;">ORA-06512: at line 1</span><br /><span style="font-family: courier new;"><br />StackTrace: at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)</span><br /><span style="font-family: courier new;">at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src)</span><br /><span style="font-family: courier new;">at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()</span><br /><span style="font-family: courier new;">at X.SasquatchEngine.ProcessingUtilities.DataLayerRetrieval.RetrieveTaskData(Int32 orderTaskID, Int32 summitGroupID)</span></span><br /><br /><br /><br />According to Oracle Support Note: Ora-13236 Internal Error In R-Tree Processing During Heavy DML [ID 443422.1] this error is due to a Bug (4570769) The solution procedure is described in the note. This bug was first seen in Oracle 10g, and so far it has not been updated.<br /><br />The effects of this bug was the impossibility of insertion to a table containing an geometry element. Some geometries could be queried, some others on a very particular region couldn't be queried, so it was a symptom that a specific geographic region was corrupted inside the R-Index.<br /><br />Actually we didn't try the workaround defined in the previously referred M.O.S. note, we rebuilt the R-Index instead.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-32277303.post-77096060411167895812011-10-04T16:46:00.006-05:002011-10-05T01:28:54.451-05:00The Oracle Open World, Day 1 - Key Conference IThis year the slogan is ... "Engineered for Innovation" According to the slogan is the kind of surprise Larry Ellison will have prepared for the Oracle community, so if you have some intuition and track the recent history of Oracle you may guess what the surprise from the big boss is about.<br /><br />Larry Ellison entered in his own personal style, and the first phrase that he pronounced gave the guideline to what this was about ... he said "Sometime back when we bought Sun the customers said that Oracle was already into the hardware business, but we didn't receive the memo" ... referring to the hardware he was displaying on the stage he pointed at it and he said ... "we have a lot of hardware on the desktop". These keywords were the elements that defined what Larry Ellison wanted to show to the world this year.<br /><p style="margin-bottom: 0in;">The main goal of the hardware is to deliver the power of the hardware with highest performance and the lowest price. As usual the comparison against the IBM P795. Larry Ellison seems to enjoy these comparisons. The Exadata and Exalogic are aimed at achieve the maximum performance with the minimum cost, he referred the price of the servers is cheaper than the x86 servers world's cheapest servers.</p> <p style="margin-bottom: 0in;">What's the magic behind the servers ? The magic word is Parallelism and compression. The philosophy is to parallelize everything, this way data can be moved faster. The X machines parallelize both hardware and software. Since the current hardware cannot be made faster due to physical limitations, unless new technology arrive, in the mean time we have to use what's available and the challenge is to configure it so the best performance possible can be taken out of it.</p> <p style="margin-bottom: 0in;">Oracle is not creating a faster hardware, you cannot make the hardware faster due to physical constraints, so how can the hardware be made faster? Using the same hardware in a more efficient configuration. Not one big machine, not one big storage server, but a fundamental <span style="font-weight: bold;">parallel everything architecture</span>. If properly configured this increases throughput, performance, reduces energy and space consumption. The Exadata and Exalogic machines architecture probably is well known by now, infiniband connectors parallel infiniband network pipe connections moving data in and out. Infiniband is the fastest network. Faster, more reliable, and with a better performance than that of the internet.</p> <p style="margin-bottom: 0in;">"Performance is about moving data, not about processor", So if you think technology from this perspective, your goal will be to reduce the amount of data and increase the speed of moving it, if you are able to move less data at a faster speed the result will be the eXtreme performance.<br /></p> <p style="margin-bottom: 0in;">The OS for the X machines was engineered to exploit the advantages of the parallelism, with all the advantages parallelism provides, such as no single point of failure and increased throughput and performance. </p> <p style="margin-bottom: 0in;">How can the amount of data be reduced? Well, from the hardware perspective the magic is created by compression. 10x data compression which means 10x less data to move, 10x data to store and another power related indirect consequences which makes this machine to require less physical space and have a small carbon dioxide footprint.</p><br />And what other surprises has revealed the big boss of Oracle? ... the Exanalytic machine (another X) and the Sun Supercluster (S), ... and I'll be talking about it in my next post.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-32277303.post-18541403296741728322011-10-04T16:32:00.003-05:002011-10-04T16:45:55.562-05:00The Oracle Open World, Day 0<p style="margin-bottom: 0in;">Oracle Open World Day 0. Arrival to SFO.</p> <p style="margin-bottom: 0in;">Visiting the Oracle Open World in my personal experience is the opportunity to meet with peers, networking, updating knowledge, interact with people, meet the gurus, and have fun. My OOW started on Saturday, and the visit to the holy Oracle Headquarters is a must for me. It's the reminder of the long journey I've walked so far along with the company.<br /></p><p style="margin-bottom: 0in;">The temparature was nice, a typical autum californian day, beautiful, I love this weather. However, you never know, I remember some years back San Francisco had a severe cold front that was mixed with a high humidity level in the air and the consequence was that you could hear a lot of people sneezing and coughing in the conference rooms. <br /></p><p style="margin-bottom: 0in;">On the other hand, taking pictures of the architecture is always an amazing experience. The reflecting windows of the cylindrical shaped buildings that in my personal opinion emulate the physical shape of hard disks built with steel, concrete and glass. The blue colored windows disguise the buildings with the sky. Always an amazing landscape.<br /></p><p style="margin-bottom: 0in;">I don't know why the OOW doesn't start on Saturday, it would be great for me to use two weekend days and three vacation days rather than using one weekend day and four vacation days to attend the event. Most probably the logistics and the convenience for most of the people who attend the event.</p><p style="margin-bottom: 0in;">The OOW is always a great experience that is absolutely worthy.<br /></p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-32277303.post-24228722488327279022011-08-04T09:56:00.003-05:002011-08-04T10:04:40.860-05:00Display a formatted time differenceThis is a simple query to display the difference between two dates in a formatted way.<br /><br />Let's assume a given date in a default DD-MON-RR format like this:<br /><br /><span style="font-family: courier new;">select sysdate - to_date('08-OCT-75') from dual;</span><br /><span style="font-family: courier new;">SYSDATE-TO_DATE('08-OCT-75')</span><br /><span style="font-family: courier new;">----------------------------</span><br /><span style="font-family: courier new;"> 13084.4081</span><br /><br />Define a Variable which will hold the number in days and fraction of days<br /><br /><span style="font-family: courier new;">define DateDay = 13084.4081</span><br /><br />Then the query<br /><br /><span style="font-family: courier new;">SELECT</span><br /><span style="font-family: courier new;"> TO_NUMBER(SUBSTR(A,1,4)) - 2000 years,</span><br /><span style="font-family: courier new;"> TO_NUMBER(SUBSTR(A,6,2)) - 01 months,</span><br /><span style="font-family: courier new;"> TO_NUMBER(SUBSTR(A,9,2)) - 01 days,</span><br /><span style="font-family: courier new;"> SUBSTR(A,12,2) hours,</span><br /><span style="font-family: courier new;"> SUBSTR(A,15,2) minutes,</span><br /><span style="font-family: courier new;"> SUBSTR(A,18,2) seconds</span><br /><span style="font-family: courier new;">FROM (SELECT TO_CHAR(TO_DATE('20000101','YYYYMMDD')</span><br /><span style="font-family: courier new;"> + &DateDay,'YYYY MM DD HH24:MI:SS') A</span><br /><span style="font-family: courier new;"> FROM DUAL);</span><br /><br /><br />and you've got your formatted output.<br /><br /><span style="font-family: courier new;"> YEARS MONTHS DAYS HO MI SE</span><br /><span style="font-family: courier new;">---------- ---------- ---------- -- -- --</span><br /><span style="font-family: courier new;"> 35 9 27 09 47 40</span>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-32277303.post-31976684043275752242011-07-07T13:54:00.003-05:002011-07-07T14:49:24.748-05:00Moving a datafile OnlineA datafile cannot be completely moved online in a user transparent way, but what it can be done is to minimize the time required to perform the operation, and avoid the traditional procedure executed when the database is mounted, which permits more availability for the non affected production tablespaces.<br /><br />The operation can be performed by creating a datafile copy, either with the traditional commands or by means of recovery manager. In this case the following script was prepared to move a datafile.<br /><br /><span style="font-size:85%;"><span style="font-family: courier new;"> run {</span><br /><span style="font-family: courier new;"> copy datafile '<source_datafile>'</source_datafile></span><br /><span style="font-family: courier new;"> to '<destination_datafile>';</destination_datafile></span><br /><span style="font-family: courier new;"> sql 'ALTER TABLESPACE <tablespace_name> OFFLINE IMMEDIATE';</tablespace_name></span><br /><span style="font-family: courier new;"> set newname for datafile '<source datafile="">'</span><br /><span style="font-family: courier new;"> to '<destination_datafile>';</destination_datafile></span><br /><span style="font-family: courier new;"> switch datafile all;</span><br /><span style="font-family: courier new;"> recover tablespace TABLESPACE_NAME;</span><br /><span style="font-family: courier new;"> sql 'ALTER TABLESPACE TABLESPACE_NAME ONLINE';</span><br /><span style="font-family: courier new;">}</span></span><br /><br />The most time consuming task is moving or copying the file by itself, so this operation goes first, once it's ready, then the datafile goes offline and the switch command is issued, this updates the controlfile contents and performs the recover from the time the tablespace was backed up until it was moved. <br /><br />This is the part of the process that will generate errors if used with concurrent operation on this.<br /><br /><span style="font-size:85%;"><span style="font-family: courier new;">ERROR at line 1:</span><br /><span style="font-family: courier new;">ORA-00376: file <fileid> cannot be read at this time</fileid></span><br /><span style="font-family: courier new;">ORA-01110: data file 7: '<file name="">'</file></span></span><br /><br />So a Maintenance window has to be open to be able to work on the affected tablespaces.<br /><br />What happens with a Dataguard Environment?<br />It is not affected when datafiles are moved at the primary database since the recovery process looks for the datafile ID, not the datafile location at the destination.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-32277303.post-86415619768315450712011-07-05T15:33:00.004-05:002011-07-05T17:03:56.511-05:00The listener supports no servicesThis error means in general there are communication issues. It can be generic, so the outline here only depicts a very particular case.<br /><br />Environment:<br /><ul><li>Windows 2003, x86</li><li>Oracle 11.1.0.7.0</li></ul><br />When trying to start the listener, it took more time than usual, and after a while it showed up the following messages on the console:<br /><br />Starting tnslsnr: please wait...<br /><br /><span style="font-size:85%;"><span style="font-family: courier new;">TNSLSNR for 32-bit Windows: Version 11.1.0.7.0 - Production</span><br /><span style="font-family: courier new;">...</span><br /><span style="font-family: courier new;">Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))</span><br /><span style="font-family: courier new;"> The listener supports no services</span><br /><span style="font-family: courier new;"> The command completed successfully</span><br /></span><br /><br />And when trying to display the services this was shown by the lsnrctl services command:<br />...<br /><span style="font-size:85%;"><span style="font-family: courier new;">Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=echo.world)(PORT=15</span><br /><span style="font-family: courier new;">21)))</span><br /><span style="font-family: courier new;">TNS-12535: TNS:operation timed out</span><br /><span style="font-family: courier new;"> TNS-12560: TNS:protocol adapter error</span><br /><span style="font-family: courier new;"> TNS-00505: Operation timed out</span><br /><span style="font-family: courier new;"> 32-bit Windows Error: 60: Unknown error</span><br /><span style="font-family: courier new;">Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))</span><br /><span style="font-family: courier new;">The listener supports no services</span><br /><span style="font-family: courier new;">The command completed successfully</span></span><br /><br />The trace was enabled and the stack of messages was recorded at the trace file. By taking a look at the file a particular message came to my attention:<br /><span style="font-size:85%;"><span style="font-family: courier new;">...</span><br /><span style="font-family: courier new;">2011-07-05 15:09:59.231337 : nsinh_hoff:connection inherited</span><br /><span style="font-family: courier new;">2011-07-05 15:09:59.231349 : nsinherit:connected</span><br /><span style="font-family: courier new;">2011-07-05 15:09:59.237138 : nsglma:Listener's pid=3056</span><br /><span style="font-family: courier new;">2011-07-05 15:09:59.249332 : nsglbgetRSPidx:returning ecode=0</span><br /><span style="font-family: courier new;">2011-07-05 15:09:59.249389 : nsc2addr:(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=echo.oracle.com)(PORT=1521)))</span><br /><span style="font-weight: bold; font-family: courier new; color: rgb(255, 0, 0);">2011-07-05 15:09:59.249561 : snlinGetAddrInfo:getaddrinfo() failed with error 11001</span><br /><span style="font-family: courier new;">2011-07-05 15:09:59.249633 : nttbnd2addr:looking up IP addr for host: echo.oracle.com</span></span><br /><br />From this point on, several other errors showed up, but this is the first point where everything began to be not normal.<br /><br />The call getaddrinfo() failed, which means there was an inconsistency when trying to resolve the host IP address.<br /><br />By taking a look at the hosts file and the ipconfig command output the discrepancies appeared.<br />Contents at the windows hosts file:<br /><span style="font-size:85%;"><span style="font-family: courier new;">192.168.50.78 echo.world echo</span></span><br /><br />ipconfig output:<br /><br /><span style="font-family: courier new;font-size:85%;" >C:\Oracle\app\product\11.1.0\db_1\NETWORK\ADMIN>ipconfig<br />Windows IP Configuration<br /></span><span style="font-size:85%;"><br /><span style="font-family: courier new;">Ethernet adapter Loopback Adapter:</span><br /><br /><span style="font-family: courier new;"> Connection-specific DNS Suffix . :</span><br /><span style="font-family: courier new;"> IP Address. . . . . . . . . . . . : 192.168.2.115</span><br /><span style="font-family: courier new;"> Subnet Mask . . . . . . . . . . . : 255.255.255.0</span><br /><span style="font-family: courier new;"> Default Gateway . . . . . . . . . :</span></span><br /><span style="font-family: courier new;font-size:85%;" ><br />Ethernet adapter Local Area Connection:<br /><br /> Connection-specific DNS Suffix . : world<br /> IP Address. . . . . . . . . . . . : 192.168.50.68<br /> Subnet Mask . . . . . . . . . . . : 255.255.255.0<br /> Default Gateway . . . . . . . . . : 192.168.50.1</span><br /><br />The network adapter is configured in a DHCP environment, and according to the Oracle installation manual for DHCP environments, a loopback adapter has to be configured, and its address is the one configured at the local hosts file.<br /><br />So configuring the adapters according to the installation guide it makes the system to run as expected.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-32277303.post-6971016989268453102011-06-28T11:36:00.002-05:002011-06-28T11:57:26.202-05:00Yet Another ORA-00600ORA-600 [kkoipt:incorrect pwj].<br /><br />There are some ORA-600 which can be easily googled, and there are others which are pretty difficult to find. This is the case. M.O.S reports this ORA600 to be related to a bug (9929660) on the 11.2 platform and which is fixed on 12.0.<br /><br />Recently this error showed up on a Windows 2003 platform with Oracle 11.1.0.7.0. After deciphering the almost cryptic related trace and dump file and diagnosing after the possible environment causes. I found out this error was triggered at one of the internal performance maintenance task, and the root cause was related to a lack of processes in the database.<br /><br />This error was fixed in this particular case and platform by increasing the value of the PROCESSES instance parameter.<br /><br />The only one who has the authority to decode and understand the trace files related to the Oracle internals is Oracle support ... unless you are willing to take the quest of reading them and trying to understand them as your last resource.<br /><br />The information provided in this post is only for information purposes, and to share a very particular experience in my local environment. The only one authorized to provide a diagnose about an ORA-00600 is Oracle Support Services.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-32277303.post-26718050764105289872011-04-11T16:07:00.002-05:002011-04-11T16:20:02.653-05:00V$OBJECT_USAGE view empty.This is a very old issue, but one that sometimes is hard to remember when needed. I tried to monitor index usage to get rid of some indexes which in my opinion are not in use. I implemented the traditional method of index monitoring:<br /><br />Start monitoring the index.<br />alter index SchemaOwner.IndexName monitoring usage;<br /><br />After a while, query the <span style="font-family: courier new;">V$OBJECT_USAGE</span> view.<br /><br /><span style="font-size:85%;"><span style="font-family: courier new;">select * from V$OBJECT_USAGE;</span></span><br /><br />It came to my surprise that the view was empty. The reason ... I knew it some time back ... but I forgot. I googled and I found a very good reference from Alex Gorvachev, who went through some sort of similar experience ( <a href="http://www.oracloid.com/2006/05/vobject_usage-empty/">v$object_usage empty ?</a> ).<br /><br />The reason is because internally the v$object_usage view displays filters the objects that belong to the user who performs the query. If you are the owner of the index, or if you have access to the schema owner password you just go ahead, logon to the database with the schema owner password and query the view, if you don't have access to the password or you are not the owner of the index, even if you have DBA privileges you won't be able to query the view.<br /><br />So the trick in this case is to create a home made view that works around this issue. It is assumed you have enough privileges to access the underlying SYS objects queried by the view.<br /><br /><span style="font-size:85%;"><span style="font-family: courier new;">create or replace view V$ALL_OBJECT_USAGE</span><br /><span style="font-family: courier new;">(OWNER</span><br /><span style="font-family: courier new;">,INDEX_NAME</span><br /><span style="font-family: courier new;">,TABLE_NAME</span><br /><span style="font-family: courier new;">,MONITORING</span><br /><span style="font-family: courier new;">,USED</span><br /><span style="font-family: courier new;">,START_MONITORING</span><br /><span style="font-family: courier new;">,END_MONITORING</span><br /><span style="font-family: courier new;">)</span><br /><span style="font-family: courier new;">as</span><br /><span style="font-family: courier new;">select u.name</span><br /><span style="font-family: courier new;">, io.name</span><br /><span style="font-family: courier new;">, t.name</span><br /><span style="font-family: courier new;">, decode(bitand(i.flags, 65536), 0, 'NO', 'YES')</span><br /><span style="font-family: courier new;">, decode(bitand(ou.flags, 1), 0, 'NO', 'YES')</span><br /><span style="font-family: courier new;">, ou.start_monitoring</span><br /><span style="font-family: courier new;">, ou.end_monitoring</span><br /><span style="font-family: courier new;">from</span><br /><span style="font-family: courier new;">sys.user$ u</span><br /><span style="font-family: courier new;">, sys.obj$ io</span><br /><span style="font-family: courier new;">, sys.obj$ t</span><br /><span style="font-family: courier new;">, sys.ind$ i</span><br /><span style="font-family: courier new;">, sys.object_usage ou</span><br /><span style="font-family: courier new;">where</span><br /><span style="font-family: courier new;">i.obj# = ou.obj#</span><br /><span style="font-family: courier new;">and io.obj# = ou.obj#</span><br /><span style="font-family: courier new;">and t.obj# = i.bo#</span><br /><span style="font-family: courier new;">and u.user# = io.owner#</span><br /><span style="font-family: courier new;">/</span><br /></span><br />enjoy!<br /><br />The view source code was taken as is from the Alex Gorvachev's blog.Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-32277303.post-89006609472750005532011-01-18T15:38:00.002-06:002011-01-18T15:54:07.418-06:00Where is the password column from DBA_USERS in 11g?Oracle 11g brought several security enhancements, as it is well known by the 11g users. On previous Oracle versions it was possible to query the DBA_USERS PASSWORD column to get the hashed password string. It was useful when someone tried to temporarily reset the user's password and restore it to its original value without actually knowing it.<br /><br />The command:<br /><br /><span style="font-size:85%;"><span style="font-family: courier new;">ALTER USER IDENTIFID BY VALUES '14C785FC66029BF9';</span></span><br /><br />it could take the hashed value from the DBA_USERS data dictionary view. However starting with Oracle 11g this column is null ... so where are we supposed to take this hashed value from?.<br /><br /><span style="font-size:85%;"><span style="font-family: courier new;">SQL> SELECT USERNAME, PASSWORD</span><br /><span style="font-family: courier new;"> 2 FROM DBA_USERS</span><br /><span style="font-family: courier new;"> 3 WHERE USERNAME='SYSTEM';</span><br /><br /><span style="font-family: courier new;">USERNAME PASSWORD</span><br /><span style="font-family: courier new;">--------------- ------------------------------</span><br /><span style="font-family: courier new;">SYSTEM</span><span style="font-family: courier new;"> </span></span><span style="font-family: courier new;font-size:85%;" ><></span><span style="font-size:85%;"><span style="font-family: courier new;"> </span></span> <br /><br /><br />By taking a look at the underlying data dictionary table where the DBA_USERS view is built on, we can easily find the data dictionary table is SYS.USER$, and it has a column named ... guess ... PASSWORD.<br /><br />Oracle 11g only makes it a little bit more difficult to get the hashed password, but if you have enough privileges you can still apply the traditional procedure to temporarily reset the password, and still have access to the hashed password.<br /><br /><span style="font-size:85%;"><span style="font-family: courier new;">SQL> SELECT NAME, PASSWORD</span><br /><span style="font-family: courier new;"> FROM SYS.USER$</span><br /><span style="font-family: courier new;"> WHERE NAME = 'SYSTEM' ;</span><br /><br /><span style="font-family: courier new;">NAME PASSWORD</span><br /><span style="font-family: courier new;">------------------------------ ------------------------------</span><br /><span style="font-family: courier new;">SYSTEM 2D594E86F93B17A1</span><br /><br /><br /><span style="font-family: courier new;">SQL> ALTER USER SYSTEM IDENTIFIED BY tempPasswd;</span><br /><span style="font-family: courier new;">User altered.</span><br /><br /><span style="font-family: courier new;">SQL> connect system/tempPasswd@orcl;</span><br /><span style="font-family: courier new;">Connected.</span><br /><br /><span style="font-family: courier new;">SQL> ALTER USER SYSTEM IDENTIFIED BY VALUES '2D594E86F93B17A1'</span><br /><span style="font-family: courier new;">User altered.</span><br /><br /><span style="font-family: courier new;">SQL> connect system/manager@orcl</span><br /><span style="font-family: courier new;">Connected.</span><br /><br /></span>Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-32277303.post-88511727620801393182010-11-10T16:41:00.003-06:002010-11-10T17:12:56.345-06:00Reading the alert.log as a local table.Reading the alert.log file is a must for the DBA. This is the primary source of information about the Database. The traditional way to read it is by means of any text based tool that can open the file and lets you take a look at it and look for specific text patterns. This requires access to the Operating System, which probably is something the DBA can take for granted at most shops. However, it happens that because of security issues, access to the Operating System is restricted ... sounds familiar? ... either you have someone to send you the file on a regular basis, you have a link to the file (assuming proper permissions) or you have to create your own routines to access it. Another natural choice is to access it by means of either the EM Control Console or the Grid Console, and the of course an External Table.<br /><br />In this example I will create an externa table to access the alert.log file<br /><br /><span style="font-size:85%;"><span style="font-family:courier new;">create table ALERT_LOG (</span><br /><span style="font-family:courier new;"> text_line varchar2( 512)</span><br /><span style="font-family:courier new;">)</span><br /><span style="font-family:courier new;"> organization external (</span><br /><span style="font-family:courier new;"> type ORACLE_LOADER</span><br /><span style="font-family:courier new;"> default directory BACKGROUND_DUMP_DEST_DIR</span><br /><span style="font-family:courier new;"> access parameters (</span><br /><span style="font-family:courier new;"> records delimited by newline</span><br /><span style="font-family:courier new;"> nobadfile</span><br /><span style="font-family:courier new;"> nodiscardfile</span><br /><span style="font-family:courier new;"> nologfile</span><br /><span style="font-family:courier new;"> )</span><br /><span style="font-family:courier new;"> location( 'alertORCL.log')</span><br /><span style="font-family:courier new;">);</span></span><br /><br />This example assumes the BACKGROUND_DUMP_DEST_DIR exists and you have access to it. It also assumes your Oracle instance is the classical ORCL instance.<br /><br />Once the external table has been created just access it as a regular Oracle table with select only privileges.<br /><br /><br /><span style="font-size:85%;"><span style="font-family:courier new;">SELECT * FROM ALERT_LOG;</span><br /><br /><span style="font-family:courier new;">TEXT_LINE</span><br /><span style="font-family:courier new;">--------------------------------------------------------------------------------</span><br /><span style="font-family:courier new;">Starting ORACLE instance (normal)</span><br /><span style="font-family:courier new;">LICENSE_MAX_SESSION = 0</span><br /><span style="font-family:courier new;">LICENSE_SESSIONS_WARNING = 0</span><br /><span style="font-family:courier new;">Shared memory segment for instance monitoring created</span><br /><span style="font-family:courier new;">Picked latch-free SCN scheme 3</span><br /><span style="font-family:courier new;">Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST</span><br /><span style="font-family:courier new;">Autotune of undo retention is turned on.</span><br /><span style="font-family:courier new;">IMODE=BR</span><br /><span style="font-family:courier new;">ILAT =18</span><br /><span style="font-family:courier new;">LICENSE_MAX_USERS = 0</span><br /><span style="font-family:courier new;">SYS auditing is disabled</span><br /><br /><span style="font-family:courier new;">Starting up ORACLE RDBMS Version: 11.1.0.7.0.</span><br /><span style="font-family:courier new;">Using parameter settings in client-side pfile C:\ORACLE\APP\ADMIN\ORCL\PFILE\I</span><br /><span style="font-family:courier new;">NIT.ORA on machine HECTOR</span><br /><br /><span style="font-family:courier new;">System parameters with non-default values:</span><br /><span style="font-family:courier new;"> processes = 150</span><br /><span style="font-family:courier new;"> memory_target = 1648M</span><br /><span style="font-family:courier new;"> control_files = "C:\ORACLE\APP\ORADATA\ORCL\CONTROL01.CTL"</span><br /><span style="font-family:courier new;"> control_files = "C:\ORACLE\APP\ORADATA\ORCL\CONTROL02.CTL"</span><br /><span style="font-family:courier new;"> control_files = "C:\ORACLE\APP\ORADATA\ORCL\CONTROL03.CTL"</span><br /><br /><span style="font-family:courier new;">19 rows selected.</span></span><br /><br /><br />This works for 9i, 10g and 11g, (Rel. 1 and Rel. 2). In the particular case of 11g Oracle created a new X$ view which can be accessed just like any other X$ view to read the alert.log file, the <span style="font-weight: bold;">X$DBGALERTEXT</span> view. You must have proper privileges to access it.<br /><br /><br /><a href="https://www.packtpub.com/oracle-10g-11g-data-and-database-management-utilities/book">Reference: Oracle Data and Database Management Utilities. Chapter 3</a>Unknownnoreply@blogger.com0