ORA-01722: Invalid Number - Weird Problem

We have an ADF application, which hooks into Portal and SOA running on Weblogic 10.3.6 in a Fusion Middleware environment.

A few weeks ago after pushing a new release of the code to production, users encountered an ORA-01722 error in some screens of the application.    We captured the SQL and could run it via SQL*Plus and Sql Developer with no errors.

We opened an SR with Oracle and they offered some suggestions and in the end recommended a patch to fix a shared cursor bug.   We haven't applied that patch yet because we are on the latest PSU for 11.1.0.7 and that patch isn't available for it.  So we've issued a patch request with Oracle.

The weird problem is, that when my co-worker starts the WebLogic Managed Server, the users do not encounter the ORA-01722 error.   The only difference is that I am ssh'ing from a Mac and he is on windows.   We ssh into the same generic account and execute the same steps.  We've also confirmed that I can restart the server a dozen times with the error and my co-worker has never had the problem. We've verified this a few times.

Today we had some more maintenance and I tried restarting the server again.    Users encountered the same ORA-01722 error.   I restarted the server again but this time from my windows computer using SecureCRT.   Error disappears.

The following is a diff between a mac and windows ssh session.   There isn't a huge difference.

< TERM=xterm

---

> TERM=xterm-256color

5c5

< SSH_CLIENT=X.X.X.X 57952 22

---

> SSH_CLIENT=X.X.X.X 65516 22

7c7

< SSH_TTY=/dev/pts/1

---

> SSH_TTY=/dev/pts/4

10c10

< LS_COLORS=no=00:fi=00:di=00;34:ln=00;36:pi=40;33:so=00;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=00;32:*.cmd=00;32:*.exe=00;32:*.com=00;32:*.btm=00;32:*.bat=00;32:*.sh=00;32:*.csh=00;32:*.tar=00;31:*.tgz=00;31:*.arj=00;31:*.taz=00;31:*.lzh=00;31:*.zip=00;31:*.z=00;31:*.Z=00;31:*.gz=00;31:*.bz2=00;31:*.bz=00;31:*.tz=00;31:*.rpm=00;31:*.cpio=00;31:*.jpg=00;35:*.gif=00;35:*.bmp=00;35:*.xbm=00;35:*.xpm=00;35:*.png=00;35:*.tif=00;35:

---

> LS_COLORS=

16c16

< LANG=en_US.UTF-8

---

> LANG=en_CA.UTF-8

23c23

< SSH_CONNECTION=X.X.X.X 57952 X.X.X.X 22

---

> SSH_CONNECTION=X.X.X.X 65516 X.X.X.X 22

The only one that catches my eye is the LANG env variable but I honestly don't see that as having any affect on the application.   If there was an environmental difference I would expect to see other types of issues.  ORA-01722 is pretty specific.

Also, we can only reproduce this on our production server.   If I restart DEV/TEST from my Mac there are no issues.    During a future maintenance window I may try changing the different environment settings after I ssh in from my Mac just to see if the error re-occurs.

Regardless, we'll still proceed with the patch request just in case it happens again.  Weird Problem!

#OOW12 - Day 4

The last day of Openworld started late and ended early.   My first session was at 11:15 and it was all over by 3:15.   I've been to quite a few Openworld's and this one has gone by much quicker than most.  Which is a good thing because it means I had a great time.

I met up with my co-worker at the first session of the day and we realized we had the same schedule.   So I switched out the middle session and he switched the last slot.   I attended two sessions on E-Business Suite.   One on performance (Recommendations for Oracle E-Business Suite Performance Tuning), the other on security (The Top 10 Things You Can Do to Secure Your Oracle E-Business Suite Instance).   While both sessions are worth downloading, I thought the second one on security was very interesting.     The presenter took what they thought are the easiest yet biggest bang for the buck things you could do to improve security.   The top ten list included checking for missing profile options, default passwords, restricting access to servers based on IP, non-reversible passwords and encrypting credit card data.

The other session I attended  Oracle Database Optimizer: An Insider’s View of How the Optimizer Works.   This was an in-depth discussion around the database optimizers and new features.     I love these low-level technical discussions and wish I could fill all my sessions with them.      I won't try to delve into the topics discussed here but if your interested in how the optimizer works then you should definitely download this presentation.    You should also follow their blog at: https://blogs.oracle.com/optimizer/

The "Its A Wrap Party" this year was hosted by the Five Hundreds and the Hives.     I believe this is the first time I stayed to the end of the closing party.   Normally I show up to check it out, have some food, a few drinks and head back to the hotel to get ready for the trip home.

However once The Hives were on stage I couldn't leave.    They put on an awesome show and by far were the most entertaining of all the bands i've seen this week.   A big kodos to the Oracle team that put together the music festival and the acts they were able bring in.   I hope they keep doing this from now on!

Friday we took time to explore the city.   We had breakfast at Sears Fine Foods, walked to Fisherman's Wharf and managed to catch some of the air show rehearsals.   A not so smart decision had us walking back towards downtown, checking out Lombardi St. on the way.   After walking all week my feet have given up and are aching considerably.    It was all well worth it tho and I hope to get the chance to do it again next year!

#OOW12 - Day 3

My first session on Wednesday was Privileged Database User Best Practices.  A new feature in (for the life of me I can't remember if the presenter said OEM 12c or DB 12c) is the ability perform privilege analysis.   Over a period of time privilege information is captured and afterwards a report shows which privileges were used, unused and how the user obtained the privileges.

This provides the DBA with the ability to easily identify and remove unused privileges which ensures that database users have the least amount of privileges required.   How often are we confronted with developers who don't know what privileges they need and request full access?  Especially if they need to troubleshoot a production issue. It happens to me almost weekly.

However, if you don't leave the capture process on long enough, information will be missing.  So its hard to say how useful this feature may be.

The second session I attended was on DaaS, Database as a Service.   I don't work for a service provider but this feature may be nice to help out developers.    Our developers like to get their hands dirty and end up installing full stacks on their desktops.    Maybe we can use DaaS to help them get a database up and running quickly for proof of concepts, etc.     Its was a good session and I plan on investigating further.

Another good session was Tips and Tricks for Hardening Fusion Middleware.   There doesn't seem to be alot of this type of information around so it was very useful to hear how other administrators are securing their environments.   Alot of the features they discussed tho were behind the firewall.  For example, we use SSL but it terminates at the load balancer.     Some of the features they talked about were using connection filters for WebLogic at the network layer, securing everything with SSL, using secureListener with node manager, separating admin traffic for your domains, etc.

I realize that statistically a large number of attacks occur internally.   Some figures estimate as high as 60%.   Even with this figure I have had a hard time persuading architects to implement more secure configurations.   They say that these configurations unnecessarily make the environment more complex, which makes it harder to develop and troubleshoot.   Have you dealt with this before, were you successful in implementing additional security layers?

Finally I attended a session which I had hoped would help with promoting changes within a Fusion Middleware environment.  The session was titled Oracle Fusion Middleware Test-to-Production Movement: Best Practices and Strategy.   This process is documented in the Fusion Middleware Administrators Guide, http://docs.oracle.com/cd/E28271_01/core.1111/e10105/clone.htm.  The T2P process (test-to-production) allows you to create a new FMW environment from an existing one.   Config settings, customizations and patches are included, transactional data isn't.  

It doesn't seem to be suited to what I need,  which is help moving changes from test to an existing production environment.   Currently to implement a change in production we follow the same steps as test.   However, this is a manual process and is prone to user error.    

Cloning (taking a copy of the software, configuration and data) will be addressed in 12c.   With e-Business suite we clone dev and test from prod at the beginning of each release cycle so that we can be sure all the environments are identical.     I'm looking forward to this feature in FMW 12c as its very easy for environments to become out of sync.

After a quick stop back at the hotel we headed out to the appreciation event on Treasure Island.   The first few days we were here San Francisco experienced unusually high temperatures.   Unfortunately it ended on Wednesday.    Even with a long sleeved shirt, hoodie and jacket I was on the borderline of being uncomfortable.   I am not sure how all the people who showed up in t-shirts and short skirts survived.     I am a big fan of Pearl Jam and was looking forward to seeing them.   However, I was disappointed by the sound quality.   I'm not sure if the wind was a factor but the music sounded very distorted.    I don't recall noticing this at previous events.     I still had a great time tho and it was nice to relax and listen to great music.

#OOW12 - Day 2

Lets just say it was a rough morning trying to get out the door.    I am not sure when I arrived back at the hotel but I know it was late.  

I didn't realize what time it was until my co-worker texted me asking if I was ready to leave.    I was still in bed.   Needless to say I jumped up, grabbed a quick shower and headed off to my first session.

As an EBS DBA a mandatory session for me is Steven Chan's EBS Technology Roadmap.   It provides a great overview of certifications, where you should be, what you should be planning, etc.

My next session was Tim Gorman's RDBMS Forensics, Troubleshooting with ASH.    I have to say, this was a great session.   I didn't take many notes as it was very interesting and I wanted to understand the context around the slides for when I download the presentation later.

Tim has quite a few scripts he uses in troubleshooting and he has graciously provided them online.   Be sure to bookmark his website: http://www.evdbt.com/tools.htm

I skipped my next presentation in order to line up for the keynote.  I only go to 2-3 keynotes and I like to sit near the front so I can snap off a few photos.    I took advantage of my blogger pass (Thanks Oracle!!) and was granted early access to the hall which allowed me to get a front row seat.

Unfortunately working in a small shop means most of the topics discussed have no relevance to my day job.  However, I do enjoy keeping tabs on Oracle's direction.

My last session was on the Application Management Pack for E-Business suite.   We are in the process of implementing this pack and I was looking to hear about some of the new features.  Unfortunately my sinuses started to act up and I ended up leaving.   Pretty sure the people around me were getting pretty annoyed at my sniffing.

After a quick stop at the Hotel I met up with a co-worker and we headed out to a vendor event at LuLu's restaurant.    Even tho Oracle is a huge conference, its amazing how often you run into people you know.   Earlier in the week we were introduced to some people who actually work less than a few km's from us back in our hometown.

They happened to be there, so we talked shopped for a little while before heading up to Union (opps... Oracle) Square to see Joss Stone.  


If I had known there was already a crowd there,  I would have left earlier.   In the end we still managed to find a decent spot to stand.   The only annoyance was the guy in front of me trying to take pictures and video on his phone.   Every time I tried to snap off a shot his arm/head/phone would get in the way.

For all those trying to take pictures with a cell phone, your wasting your time especially in low light.   The pictures this guy was getting were out of focus, blurry and not exposed correctly. Cell phones do a horrible job of metering in difficult environments such as a concert.

Also, don't zoom! Unless the camera has an optical zoom your better off leaving it as is and cropping it later. Its one thing to grab a pic for Instagram, Twitter or to upload to facebook to make your friends jealous, but taking 200 pictures with it over the course of an hour is an absolute waste of time. Unfortunately most don't realize this until they get home and see the pictures on their monitor. Anyways, thats my rant for today!

#OOW12 - Day 1

Wow, the conference is already half over...   The past 3 days have gone by pretty fast.    4/5 of my sessions were really good on Monday.    Currently we are running OEM 11g and there has been some talk about upgrading to 12c.   So I added Upgrading Oracle Enterprise Manager to 12c, Best Practices to my schedule. I'm glad I attended because the session provided a great overview of the upgrade process and various upgrade paths you could take.   Now an upgrade doesn't seem to be as daunting a task as I had assumed.

The next session I attended was an overview of WebCenter.   We are in the process of evaluating the product and I was asked to install the product in a dev environment.   I was impressed with the editing abilities and integrated mobile support.   It appears to be much easier to use than our current solution Oracle Portal.

Oracle E-Business Suite Future Directions mostly talked about features coming in 12.2.   After Steven Chan's presentation on Sunday I thought for sure 12.2 was going to be announced this year at Openworld.  However, I haven't heard a thing yet.    Max Arderius talked about WebLogic Server replacing the 10.1.3 stack and its impact.    

Moving forward autoconfig will have a reduced role, limited to the database, EBS and part of the weblogic server configuration.   Fusion Middleware tools will handle the rest.   There is a good slide in the presentation which provides better context.    The cloning process will also change.  Now preclone on the appsTier will take longer and require 3-4GB of disk space.   If I understood correctly you won't need to copy the weblogic files, just the 10.1.2, appl_top and common_top directories.

My last session of the day was on Best Practices for Managing and Monitoring Weblogic Server.   Our WebLogic install is pretty basic and as of yet, we haven't had a requirement for high availability.   So I am not that familiar WL HA configs.   The session provided a great overview of using clustering, ideas on how to segregate applications and some of the reasons why you would want to.  For example, some applications may need to scale up more than others.  

The session also covered some of the OEM 12c features for managing WL.   A routing topology viewer which helps identify the impact of performance issues across the various components/tiers.    One issue we had is making sure our configuration from dev-test-prod are identical.  OEM 12c apparently provides the ability to compare WL domains and alert when differences are detected.  

Just as important, as the conference (well, almost) are the networking opportunities.  The provide you with the ability to meet other like minded people and talk sports and shop.

Monday night started with an awesome event at Benu with Oracle.   The entire restaurant was open to us, including the kitchen.   You could walk right in, watch the staff prepare the food and eat it.   I'm not entirely sure what some of the things I ate were but most were very tasty.   Those I wasn't sure about I made my co-worker eat first to see his reaction.

After Benu we walked over to the W Hotel for a meetup with Pythian.   We recently signed a contract with them and it was a great opportunity to meet some of their staff and of course Mr. Paul Vallee.   I'm looking forward to working with them as I have heard great things about them.

Finally, as my coworker called an early night (wuss), I met up with my manager who was at an IBM event.   The Spazmatics (sp) were providing the music and I have to say they were pretty good.   Pretty sure I won't be able to convince the guys I jam with to play NKOTB tho.    After the event we headed over to a local pub for a few more drinks before calling it a night.

#OOW12 - Day 0

Day 0 went by pretty fast and Day 1 is just about to start.  Shortly i'll be heading down to Moscone to catch the keynote with I believe Mark Hurd.

However before I leave I thought I would write up a quick note about Day 0.   Day 0 is full of user group presentations and typically they dive into quite a bit of detail.   This time was no exception.  I attended sessions on Oracle VM and using the command line with OEM.  A rather low level view of unix-base troubleshooting with a session by Riyaj Shamsudeen titled Truss, pmap and more.  Lastly I attended a session on WebLogic HA.

Other than one session which was a complete dud (will remain nameless because there is the off chance that I misinterpreted the abstract), it was a great day.  I haven't been to IOUG before but seeing sessions like these make me think i'm missing out.  

The opening keynote started at 5 to a packed out crowd.. There had to be at least 10k people there.

A number of new announcements were made this year.   I won't dive into detail because thats not the sort of things I post about and there are probably dozens of news articles/blogs already published with in depth coverage.   In short tho 4 new products were announced

1. Infrastructure as a Service (IaaS)
2. The Oracle Private Cloud
3. Multitenant Plugable Database Architecture
4. Finally the impressive Exadata X3

Our company hosts our production and test environments with 80% of it being Oracle products, along with 3rd party, in-house developed products running on Fusion Middleware.     I'm wondering what management will think of Oracle's IaaS offering?

Multitenant database looks interesting but I would like to see more details (undoubtably throughout the conference).   Specifically database recovery.  As we all know, having multiple schemas in a single database can pose some recovery issues depending on the type of failure.

The day ended with the Welcome Reception.   My co-worker bailed early so I stayed around to see Macy Gray.   Not really my style of music but I generally like almost all types of music live.    Macy Gray put on a great show and it seemed the crowed really enjoyed it.

I better get moving or I won't get a decent spot for the keynote.