My Photography Slideshow

Many of you may or may not know, that I am not only passionate about Oracle and new technologies, but also passionate about Photography. 

I am working on creating a small slideshow to showcase some of my special work - just to keep you even more relaxed. 

If you wish to check it out, you can visit my:

Facebook Page: https://www.facebook.com/rizwanwangdephotography
500px: https://500px.com/rizwanwangde

I'm back...

Hi Folks,

I am back ! This blog has been dormant for way too long now. There are so many things I could have shared in the past year but lost on all those opportunities. I promise to make it up, and post as regularly as possible. 

Other than the daily stuff, my short term major goals are as below:

1. EM12cR3 Setup on HP-UX 

2. DataGuard 12c Setup - ASM <--> Filesystem

Regards,

Rizwan Wangde

Oracle 10g Undo Auto Retention Bug causing continuous increase in UNDO tablespace utilization

Something interesting happened the other day on one of our Production Databases. The UNDO tablespace just kept filling up, without showing any downward trend. As this was a mission critical database, core-banking, we couldn't take the risk.

I check the usage of UNDO and for any other abnormal sessions or jobs that might be out of place. Nothing!!! But the space usage just kept increasing. I realized this had to be some sort of a bug. Well, after so much experience on the Oracle Databases, a DBA must have the intuition that "this must be a bug!". It was weird because the Undo Segments just wouldn't Expire... All were in the Active state.

I checked the Metalink for some time, and found a document which was very closely related to my situation.

Bug 5387030 Automatic tuning of undo_retention causes unusual extra space allocation 

Problem Description:

When undo tablespace is using NON-AUTOEXTEND datafiles,V$UNDOSTAT.TUNED_UNDORETENTION may be calculated too high preventing undo block from being expired and reused. In extreme cases the undo tablespace could be filled to capacity by these unexpired blocks.

To find out if you are hitting the same, check the output of the below statement:

select end_time, MAXQUERYLEN, TUNED_UNDORETENTION 

from v$undostat where

trunc(end_time)>trunc(sysdate-1) order by 1,2,3;

This will show a comparison between the maxquerylen and the tuned_undoretention. Ideally, the maximum amount of time set by the automatic Undo Retention should be either the maxquerylen or the value of undo_retention parameter. But in this case, the values under TUNED_UNDORETENTION column may turn out to be much higher than the both the maxquerylen or the value of undo_retention parameter

Solution:

Set the below hidden parameter:

alter system set "_smu_debug_mode" = 33554432 scope=both;

You can see that all the UNDO segments that were ACTIVE and well passed the UNDO_RETENTION value have finally expired, and the space released from the UNDO Tablespace.

Oracle Database

ORA-01017: invalid username/password when connecting from a 9i ODBC driver to an 11g Database

Hi folks,

HP Openview, used for monitoring and alerting, is in it's migration phase. The database now runs on Oracle 11g. However, the Reporting Server that connects to the database underwent no such migration or upgrade. This Reporting Server is still using the Oracle 9.1 ODBC drivers.

While trying to connect to the new 11g database, the team encountered the following error:

Unable to connect
SQLState=28000
[Oracle][ODBC][Ora]ORA-01017: invalid username/password; logon denied

Interestingly, if I tried to connect using SQLPLUS, I could establish a connection without any problems.


C:\Documents and Settings\xxxxxx>sqlplus

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Sep 5 10:13:57 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter user-name: opc_report@xxxxxxxxxxxxx
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$version;

BANNER
-------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for HPUX: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL>

Cause and Solution:

This was because of the usage of CASE SENSITIVE passwords in Oracle 11g. This was resolved by turning off the case sensitive feature in the database:

$ sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 5 10:05:06 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> alter system set SEC_CASE_SENSITIVE_LOGON = FALSE
  2  scope=both;

System altered.

And, woolaaa:

Windows 8 and the Blank Laptop LCD Screen issue

I recently installed the leaked Windows 8 RTM, to try it out before I purchase (and I will most definitely buy it as it cost only 15$ for the upgrade).
I have worked on the Windows 8 for 2 days now, and I think "THIS IS IT!". Windows 8 is one of the most amazing and an innovative platform of OS' that Microsoft has ever released. It's blazing FAST, beautiful and easy to use. I know that the first time users will find it a little difficult to get used to the new style of Navigation, but 2 days in and you'll love everything about Windows.

Blank LCD/Screen issue on loading Windows 8:
Many of you might be facing this issue - after Windows 8 completes loading, you will see a blank screen instead of the Login Screen. When I connect the laptop to an external LCD monitor, it works fine. But the Laptop's LCD just wont!

I tried a lot of tricks to fix it, but this is what clicked at the end:

1. Put your laptop into Sleep Mode by pressing the Power Button once or you can just close the lid of your laptop. 

2. Wake your Laptop from the Sleep Mode by pressing the Power Button again or opening the LCD lid. 

Woola... I can see my screen now!

I had to do this 2-3 times, and after that I have rebooted, or hard reset my laptop many times without facing the Blank LCD issue.

Let me know if you faced this issue and if the above trick helped you.

After applying Oracle PSU of JAN 2012 (10.2.0.5.6), "Cursor: pin S" waits and 100% CPU are BACK !!!

In my earlier post, dated 27-Sept-2011, I shared how we were able to resolve the issue of Cursor Pin S Waits and 100 % CPU.

1. Apply patch 6904068
2. Set _first_spare_parameter = 1 (keep increasing this value slowly if the problem still exists, do not set it very high as this can worsen things)

We generally apply the Oracle Quarterly PSU Patches within a month of their release. Patch 13343471 (PSU 10.2.0.5.6 for January 2012) was applied, which conflicted with the earlier applied patch to fix the High CPU usage when there are "cursor: pin S" waits, and rolled it back.

The following day, we saw the bug hit again, on two of our databases. We immediately got in touch with Oracle Support, and found out that there's another patch -- Bug 13619396: MERGE REQUEST ON TOP OF 10.2.0.5.6 PSU FOR BUGS 6904068 7045446 8684595.

Beware, the _first_spare_parameter that we had set when we initially set is not applicable after applying the updated Bug Fix. Instead, you have to set _third_spare_parameter.

Hope this helps, as there is no mention of this on the internet as yet.