Feed aggregator

Tim is getting married...

Moans Nogood - Sat, 2008-08-02 10:33
Anette and I are in Denver, Colorado these days, because Tim Gorman is getting married to Lori tonight (Saturday). It's a hot wedding: This is, I think, the 20th day in a row with over 90 degrees Fahrenheit, which makes it the hottest period since 1857 or something like that.

Tim is very well known in the Oracle community: He spent many years inside Oracle where I had the pleasure of communicating a lot with him on the wonderful HELPKERN list there.

He also wrote a couple of books and contributed to the Tales of The OakTable book. Here's his website: http://www.evdbt.com/

Good luck to Lori & Tim! (said the guy on his third marriage...)

An unusual cause of ORA-12154

Jared Still - Fri, 2008-08-01 17:30
The ORA-12154 (and its cousin ORA-12514) have been the bane of many a novice DBA.

This error is essentially telling you that you have messed up your database naming configuration, whether it be tnsnames, oracle names or OID. The fix is normally quite simple - just correct the naming.

This is usually quite easily done with netmgr, a tool that is fairly good at its job. The syntax for setting up a tnsname is a little convoluted, and I've fallen back on netmgr a number of times when I can't seem to get it just right by typing the entries in the file.

There's at least one other way to cause ORA-12154 to pop up and consume more time than you may like to admit. I won't tell how long I played with this...

The cause is actually due to security policy. While the characters !@#$%^&*()_-=+~` are not normally allowed in Oracle passwords, it is actually quite simple to include them. Simply enclose the password in double quotes.
alter user scott identified by "th!sat^st";

This was done on a number of accounts on our databases, all in the name of security.
These types of passwords have been used without issue for some time now.

Today was a notable exception.

After spending some time fiddling with tnsnames.ora files, I realized what the problem actually was.

Suppose you were to have a password with an '@' in it? Yep, that was the problem.
First let's see what it looks like from within sqlplus:

15:41:52 SQL> alter user scott identified by "what@mistake";

User altered.

15:42:03 SQL> connect scott/"what@mistake";

scott SQL> select user from dual;


SQL> connect scott/what@mistake
ORA-12154: TNS:could not resolve the connect identifier specified

As long as the connect string was in double quotes, no problem.

Now let's try it from a linux command line:

Linux> sqlplus scott/'what@mistake'

SQL*Plus: Release - Production on Fri Aug 1 15:42:20 2008

ORA-12154: TNS:could not resolve the connect identifier specified

Linux> sqlplus scott/'what\@mistake'

SQL*Plus: Release - Production on Fri Aug 1 15:46:14 2008

ORA-01017: invalid username/password; logon denied

Linux> sqlplus scott/"what\@mistake"

SQL*Plus: Release - Production on Fri Aug 1 15:46:21 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

ORA-01017: invalid username/password; logon denied

Linux> sqlplus 'scott/what\@mistake'

SQL*Plus: Release - Production on Fri Aug 1 15:47:23 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

ORA-01017: invalid username/password; logon denied

Linux > sqlplus scott/what\@mistake

SQL*Plus: Release - Production on Fri Aug 1 15:48:52 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

ORA-12154: TNS:could not resolve the connect identifier specified

There doesn't seem to be any method to use a password with the @ character in it, at least not from sqlplus. The same password works fine when used on the command line with perl script that logs on to the same database and account:

Linux> DBI_template.pl -database dv11 -username scott -password 'what@mistake'

The 'X' is the correct output as this script simply selects * from dual.

Lesson learned, do not ever, under any circumstances, use the @ character in the password for an Oracle database account.

You might think that goes without saying, but it's actually pretty easy to get a password with '@' in it when the passwords are generated random characters.

FWIW, this problem was manifested in Windows as well
Categories: DBA Blogs

TGI g_friday

Oracle WTF - Fri, 2008-08-01 05:57

Found in a package body:

g_friday CONSTANT VARCHAR2(6) := 'Friday';

...then a couple of hundred lines later:

if to_char(business_date,'fmDay') = g_friday then
    ...end-of-week processing...
    ...regular processing...
end if;

Now that's flexible. If end-of-week processing is ever moved to the weekend, all you have to do is set g_friday := 'Saturday'.

OTN members, don't change your e-mail account!

Yasin Baskan - Fri, 2008-08-01 03:44
I am regular user of OTN and its forums. Last week I was trying to login to OTN from a public computer and I got the "invalid login" error everytime I tried. I was sure I was typing my password correct but I could not get in anyway. So, I tried to get my password reset and sent to my e-mail address. Then I remembered that the e-mail address I used to register for OTN was from my previous employer meaning I did not have access to it anymore. As OTN does not allow changing the registration e-mail address I was stuck. I send a request from OTN to get my password delivered to my current e-mail address. Here is the reply I got:
Resolution: Oracle's membership management system does not currently support
the editing of the email address or username in your membership profile.
(It will support this capability in a future release.)
Please create a new account with the new email address you wish to use. However,
it is possible to change the email at which you receive
Discussion Forum "watch" emails (see "Your Control Panel" when logged in).
They tell me to create a new user and forget about my history, watch list, everything. What a user centric approach this is.

If you are an OTN member do not lose your password and your e-mail account at the same time, you will not find anybody from OTN who is willing to solve your problem and help you to recover your password.

I am used to bad behavior and unwillingness to solve problems in Metalink, now I get the same behavior in OTN. Whatever, just wanted to let you know about it.

Metalink, SCM, the error and the good stuff

Mihajlo Tekic - Thu, 2008-07-31 23:45
Yesterday I got an e-mail from Oracle Support letting me know about the new Metalink interface which is supposed to go live this Fall:

Dear MetaLink Customer,

Oracle is committed to consistently improving your customer support experience. In the fall of 2008, MetaLink will have a new user interface. To help you prepare for the transition, you may now preview MetaLink's new user interface and provide valuable feedback about its features.


I clicked on the link, that was supposed to redirect me to Metalink, and I got one very fancy schmancy login page. Well, actually, it is the Software Configuration Manager (SCM).

I put in my login credentials and ... I got an error message "IO Error Error #2032"

WOW ... :-)

Well, OK the problem was fixed latter that day.

Regardless of the problem I experienced, I must say that I've been using SCM for quite a while and I am pretty impressed with its functionality. It is really much easier to create and manage service requests using the configurations you I have registered with SCM. Oracle Support engineers have all the information they need about the configuration of the server and the database.

Searching the Knowledge base looks improved as well. Now you can have your search results visible on the left panel of the screen, while, at the same time, you can read the content of the selected note. This makes navigation much easier.

Service Requests part has new design too.

All in all, improved functionality, better navigation, good design, some new features too ... Good job !!!

Useful Links:


Make it go away! (rman delete)

Claudia Zeiler - Thu, 2008-07-31 20:11
So much documentation tells you how to set up things for Oracle. It is harder to find information on how to get rid of it once you have it.

I am doing perfectly lovely incremental compressed backupsets of my database. Then one day, playing with EM, I in experimented with the 'Oracle recommended backup stategy'. I may have missed something in my set up, but I ended up with an un needed image copy backup of my database. Help! This thing is devouring my flashback area. So what is the proper way to remove it.

First step, of course, is to kill the Oracle job, so it doesn't do that again!

Then in rman:
RMAN> list copy;

gives me a nice long list - one line of which was:
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name

30428 4 A 29-JUL-08 224478 29-JUL-08 /bkup/flashbk/datafile/o1_mf_users_44y8x123_.dbf

I double check on the file system:
$> ls o1_mf_users_48y8x123_.dbf
Yes, the copy is there.

To delete it in RMAN I enter
RMAN> delete datafilecopy 30428;

and get:

List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name

30428 4 A 29-JUL-08 224478 29-JUL-08 /bkup/flashbk/datafile/o1_mf_users_44y8x123_.dbf

Do you really want to delete the above objects (enter YES or NO)? y
deleted datafile copy
datafile copy filename=/bkup/flashbk//datafile/o1_mf_users_44y8x123_.dbf recid=30428 stamp=1334123
Deleted 1 objects

I check in the file system and this time my ls returns
ls: 0653-341 The file
o1_mf_users_44y8x123_.dbf does not exist.

Good bye, nusance.


Mary Ann Davidson - Wed, 2008-07-30 07:06

This summer Idaho has had the loveliest profusion of wildflowers I’ve ever seen, the product of a healthy snow pack, full reservoirs and a late spring. Happily enough, many wildflowers have seeded themselves in my rock garden, which is far more diverse and healthy than is the case with whatever else is planted that is not coming up because I have a black thumb. (I’ve actually thought about planting weeds and hoping invasive flowers take over. A girl can dream.)

I also have excellent early warning systems in my backyard in Idaho. Specifically, the critters I support on my property are all – individually and collectively – quite good at alerting me when Something Is Happening. Birds, pine squirrels (more on them later) and – last but not least – my dog Thunder are all very good alarm system proxies. It took me a couple of years living away from large urban enclaves to learn how to “read” nature’s cues. Now, my ears have been retrained to the point that I listen to the birds, squirrels and my dog when they are trying to tell me something. I claim no special nature skills but I like to think that family genes (my grandfather was and my father is a consummate woodsman after years of hunting) are asserting themselves.

When I sit out in my backyard and hear the “chit-chit-chit-chit” of a pine squirrel, I know that it means “intruder at twelve o’clock.” Pine squirrels are really noisy, and thus very good at telling you when somebody or something is coming, at least 6 trees away from the action (and yes, I can tell the difference between pine squirrel alarms and pine squirrel pickup lines). The birds also get noisier, and in a different way, when there is a (fox, dog, cat, coyote, other) prowling through the sage brush that I can’t see, but I know is there because the birds have gone to Defcon 4. Thunder also has entirely different barks for “someone’s coming up the driveway I know,” “someone’s coming up the driveway I don’t know” and “a fox just ran across the porch and is hightailing it for the back yard.” The prize for alarm specificity goes to my sister’s miniature schnauzer Sneakers, whose bark (in increasing order of frenzy) refers to: a) a jogger b) a squirrel c) a fox d) the neighbor’s white dog e) deer or f) lots of deer.

My other “tenants” (the family of white-throated swifts that nests under my peaked roof) don’t warn of “incoming,” but they keep pests out of my yard. Late afternoon, there are eight to ten of them in aerial dogfights with any flying insects that darken my airspace. Watching the sparrows turn, bank, and maneuver is just about as big a thrill as watching the Blue Angels. I like to grab a glass of wine at the end of the workday, go outside and watch the swifts on evening pest patrol. It’s very soothing and lends new meaning to the phrase, “running the debugger.”

One of the things I have been doing some thinking and speaking about is the idea of synthesis. More specifically, the lessons we can learn in IT security from other disciplines, such as business, economics, history (especially military history and strategy) and biology. I confess that I felt a little nervous speaking on this topic at a university recently, because I figured any one of the professors or graduate students on the audience knew more than I did about IT security – certainly on the nerd level. On the other hand, they are all in the perfect environment to think differently about their profession via synthesis: all they have to do is walk across the quad to talk to another department. In fact, a professor of biology I met said that at her university, there was a tight synthesis between the computer science and biology departments. Each department had realized that they were kissin’ cousins, so to speak.

Of course, we IT security weenies know this intuitively. We speak of computer “viruses” because they “infect” vulnerable hosts unless the host has been “inoculated” against them. Some of the research going on focuses on making hosts just different enough that viruses are not able to infect all of them. Mirroring the arms race that biological hosts and opportunistic germs engage in, virus makers try to find ways to defeat anti-virus defenses by disguising their nasty, germy little packages so they aren’t recognized by the defense systems – just like you can’t be inoculated against the common cold because there are so many slightly different rhinoviruses, as I know all too well because I have spent two weeks and then some getting rid of a particularly rotten summer cold. And, just as in biology, computer viruses do not want to kill the host, but to use it.

A few years ago, there was an interesting paper positing that a software monoculture was a national security risk. That is, a lack of “biological diversity” in enterprises makes those enterprises more vulnerable to a cyber plague that affects the entire enterprise, not just a portion of it (just like the Irish potato famine wiped out millions of people because the strain of potatoes grown in Ireland was not resistant to the potato blight). Note that there is some happy medium here. If it is true that running only one kind of software may make the enterprise more susceptible to a cyber plague, it’s also true that running one of every type of application, database, operating system, and so on is neither economical nor easily secured, as one would have to be an expert in absolutely everything to manage such a system.

We know that biological entities use trickery to survive, thrive and propagate. Moths disguise themselves as other, more toxic moths to fool predatory birds. (What is a honey pot but a technical equivalent of a biological system designed to attract predators?)

I have read a couple of fascinating books on how companies are modifying plants to be resistant to some diseases. This is not without risk or without controversy. The University of Hawai’i, for example, just implemented a five-year ban on genetic modification of kalo (taro), in part, because for Hawaiians, kalo is not just a plant but part of their culture. I also note that genetic modification does not necessarily deliver all the promises claimed by the proponents (e.g., the so-called “golden rice,” genetically engineered to have Vitamin A in it, doesn’t have enough in it to do much good. More specifically, according to one book I read, you’d have to eat 12 pounds of the rice a day to get the minimum daily requirement and who eats 12 pounds of rice a day?)

I’ve had the same discussions over products that claim “native protection” against classes of attacks (like SQL injection – which I believe is doable) and that do “virtual patching” (which I don’t believe all the claims for). For those who are not up on “virtual patching,” it is the idea that you can replicate in a gatekeeper/cyber-Doberman function the exact equivalent of what a patch does. You can’t. You can (in some cases) have a good workaround, or you can prevent a specific exploit or exploits, which may buy customers needed time to patch. That is very useful, I agree. Unfortunately, “virtual patch” as a term is indiscriminate: “preventing known exploits” is more accurate but doesn’t reel in the gullible, so we have “virtual patching” as an industry term and not “can’t replace patching but gives you some protection, maybe, so might be worth a shot.” To my point, shilling “virtual patching” as a replacement for patching is as irresponsible and potentially harmful to customers as parents skipping inoculations for DPT is to their children: someone, some time is going to get hit by something horrible.

As I look at my backyard, I wonder what bright technoid will look at a white-throated swift and think, “I can build that. I can build a cyber patrolling predator so swift (no pun intended) and agile that it can dive bomb pests before they reach my cyberbackyard.” Instead of staying on the telephone wire and hoping a pest drives by (like static defenses people deploy now), the cyber swifts could circulate freely on perpetual pest patrol. I think about early warning systems as sophisticated, yet recognizable as my sister’s Schnauzer or the neighborhood pine squirrels. One frenzied bark or one “chit-chit-chit” and I have a pretty good idea what is out there and how worried I should be about it. I wish most of the cyber defenses we had now were as good, as recognizable, as accurate and descriptive. Of course, foxes, coyotes and cats aren’t constantly changing their guise to be unrecognizable to Neighborhood Crime Stopper Pine Squirrels, either.

There are other disciplines that have applicability to the world of IT security, if we choose to explore them. For example, when I was in graduate business school, one of the financial market theories I learned pertained to whether companies should diversify given that investors can do it themselves. For example, conglomerates (companies that have a lot of diverse, not-necessarily-complementary lines of business), the theory goes, are not necessarily valued correctly by the marketplace. And in fact, since investors can diversify their own investments (by buying, say, automobile stock and pharmaceutical company stock separately, if that’s what they want to own), there is no reason – per se – for conglomerates to have multiple lines of disparate businesses. The big idea then (and now to a certain extent) is to focus on core competencies (we see this today in discussions about outsourcing or software as a service: if IT is not a core competency, why do it yourself?)

A number of these business trends/theories, for better or worse and sometimes both, are extended to the global marketplace. For example, the idea that if they can produce sugar more cheaply in Foobaria, then the Snafu Republic should not subsidize their domestic sugar farmers but should happily import sugar from Foobaria. Over time, the Snafu Republic’s farmers will find something else to grow that they can grow better, cheaper or faster than Foobaria (or another country). (Note: You may be less enthused about this idea if you are a sugar farmer* in Foobaria than a policy wonk in Foobaria, because no policy wonk’s job has ever moved overseas that I know of.)

Another argument, more along the lines of industrial policy, is that the people of the Snafu Republic – instead of being subsistence farmers, barely eking out enough food to feed their families – should go work in factories or someplace that will give them a higher wage so they can buy food (and more besides). In a happy dappy world, everyone (or every country) will focus on his or its core competencies and outsource everything else. Globalization facilitates everyone doing what he does best and the rising tide lifts all economies.

I am not here to argue for or against globalization as a general policy or construct (it’s a lot more complicated than one can describe in a blog entry and I think it is dangerous to reduce complex ideas to sound bytes). But I do note that there are a number of interesting – if disturbing – discussions taking place recently about the limits of globalization as a result of spiraling food prices. Food prices, of course, are spiraling for a number of reasons: increased transportation costs, the “crowding out” effect of biofuels, higher demand for high quality food as a result of growing economies, crop failures in some key areas, and so on.

Some countries have acted to ban exports of key staples (rice, for example), wanting to ensure that they can feed their own people. As a result, have-not countries are potentially rethinking that policy that said “get the subsistence farmers into higher wage jobs,” because at least a subsistence farmer might have been able to feed his own family. If you can no longer import food because exporters hoard it, you can’t always eat what the factory is producing unless they are refining sugar. You can eat potato chips but not microchips.

In short, we’ve recently had a lesson that the theory of “everyone (read “every country”) does what it does best, and we all trade for what ever else we want” does not necessarily work when you have a shock to the system, like the transportation costs going through the roof, a result of which is that sugar schlepped from Foobaria is now really, really expensive to Snafuians. It also assumes that no country is ever going to use exports as a competitive weapon. Not only is that assumption a bigger stretch than most economists typically posit (“investors are rational” – they aren’t – otherwise how we do explain how breakfast cereal portal companies got funded in the DotCom days?), but we know from history it is not true. It’s never been true, in fact.

The second mistake a lot of policy wonks make is assuming peace, love and happiness in perpetuity. That’s not true, either. Natural resources such as food water, minerals, spices (yes, spices – salt and cloves being two that immediately come to mind – the British empire enforced a monopoly on salt within their empire, and the Portuguese dominated the spice trade for years) are often used as competitive weapons and the fight over them causes wars. Japan (prior to World War II) felt that they could never be a great empire without controlling their own supply of key resources and a proximate trigger of the Pacific War was the US cutting off the supply of scrap metal to Japan. Japan did not go on a territory-acquiring binge just to have more places for rice paddies, but to acquire natural resources that went with the territory. (And ultimately they lost the war because the US destroyed so much of their merchant shipping that they could no longer ship oil to where they needed it – their ships and planes.)

What’s the security issue? The security issue is that people need to think about their supply chain when formulating national security policies. Where are food, water, energy, spare parts, computer software and hardware coming from? Are any of those critical to national security, to the point where we need multiple suppliers or a “home grown” supplier because it is in one’s national security interests to do so? (For example, the Defense Science Board looked at this issue in relation to having a Trusted Foundry Program – domestic suppliers of integrated circuits for critical defense applications.) Do we actually trust non-domestic suppliers? (News flash: yes, other nation states would, too act to put malware or backdoors in software. A shock, I know, but some countries do act to advance their national interests at the expense of – gasp, horror – other nations. Been going on as long as recorded history.)

We should assume that this is happening and deal with it instead of worrying about Hurting Other Country’s Feelings by calling them on it (the international relations equivalent of telling a country We Are On To You, Knock That &^^%$ Off Right This Minute). I recently participated in a meeting where the debate was whether the group should issue guidance on how to protect your electronics (e.g., cel phone, laptop) when you travel overseas from being co-opted by Bad Guys (bad guys here could be bad guys working for the foreign government). The guidance was all good guidance and not aimed at any country in particular, but the discussion devolved to topics as diverse as “shouldn’t the State Department be the one issuing this guidance?” and “what are the political issues around upsetting some country or another if this guidance goes out?”

(It almost boggles the mind. We know this is happening, so why are people worried about making any country already engaged in industrial espionage, breaking into critical infrastructure and so on Feel Bad About It? It’s like wondering if the grizzly bear had a bad childhood as he is gnawing on your leg. Do I really care if you were an unwanted cub? Stop chewing on my leg!)

In short, the theory of competitive advantage as applied to nation-states sounds great on paper, and may even work great to a point, but it does not take national security needs into account. A nation that is dependent upon others for key materials – like spare parts for their aircraft or microchips or food – can easily be at the mercy of others unless they have an alternate supply (and in fact, a secure supply).

I am not advocating buying everything from inside one country or (getting back to a corporate example) avoiding outsourcing at all cost. Rather, the issue is that while you can outsource services and offshore production/services/sourcing, you can't outsource risk. Even financial markets tell us that you can diversify some kinds of risks, but not market risk – the risk that the entire market will tank. For example, I “outsource” medical care in that I go to see a doctor regularly since I am not an MD. However, I have a responsibility to take care of myself (e.g., to avoid high risk behaviors that are potentially damaging to my health like excessive drinking, using illegal drugs or abusing legal ones). I can’t outsource that risk and I can’t pass along 100% of my health responsibility to a doctor.

Accordingly, whether you are a company looking at service or product providers, or a nation-state contemplating industrial policy, you need to consider risk with steely-eyed objectivity and act appropriately. You could even say that, while there is no one easy set of answers, a non-exhaustive list of potential solutions includes: thinking about country of origin in light of political, social and economic factors, as well as the state of law and law enforcement in the country, using proven suppliers; keeping better handles on your supply chain; keeping attuned to political and governmental actions in countries where you operate; and so on. Hoping geopolitical or business conditions never change, and that everyone you deal with in business has the ethics of the Boy Scouts is not risk management or even optimism, it’s fantasy.

I have had many occasions recently to recount – as a cautionary tale – the story of Wake Island’s defenders in December 1941, one of many fine moments in the history of the US Marine Corps. The Marines managed to sink a Japanese ship from a shore battery (yes, really) but ultimately, the Japanese prevailed. Among other ironies, where did the metal come from for the armaments the Japanese used to shell the shore installations on Wake Island? Scrap metal the US had sold to Japan. If we need reminding, the lesson is that you should never, ever, ever arm your enemies.

* Yes, I realize you don’t actually grow sugar but something sugar is refined from, like sugar beets, sugar cane, even corn (high fructose corn syrup).

For more information:

Book(s) of the Week:

The Omnivore’s Dilemma
is one of the most thoughtful and thought-provoking books about food, where it comes from and the implications of how your food is grown. It will change the way you look at what’s on your plate. It’s well researched and yet deeply personal. The second, The Botany of Desire, is really fascinating look at four plants and their impact on the world. The ethical implications of “licensing plants” alone are worth the read (yes, the potato is one of the four plants).

You can find both of them and other works by Michael Pollan at:


A great book on the defense of Wake Island is Given Up For Dead:


A book on salt that includes a discussion of the British empire’s inter-empire monopoly on salt: Salt: A World History by Mark Kurlansky:


More on the Salt Tax:


A book about the history of the spice trade (who would think nations could be so combative over cloves?) is The Scents of Eden: A History of the Spice Trade:


A web site on Idaho birds:


And a picture of the white-throated swift:



About the Trusted Foundry Program:


The original paper on software monoculture that created such a stir


A really (really, really) good book on issues around genetic modification of food (it mentions the hubbub over kalo (taro)) is Uncertain Peril: Genetic Engineering and the Future of Seeds by Claire Hope Cummings:


More on the genetic modification of kalo (taro):


Absolutely nothing to do with any of the above topics, but a great video of one of my favorite Hawaiian groups (‘Ike Pono) doing one of my favorite songs (Ua Noho Au A Kupa). It is just really happy music:


If that doesn’t make you want to hula, there is no hope for you.

OK, and Bobby Moderow, Jr. of Moanalua doing "Koke’e" (which I just love):


Using EJBContext from an EJB 3 Interceptor

Debu Panda - Wed, 2008-07-30 03:44

I got a question from a customer that he wants to use EJBContext from an EJB 3 Interceptor.

Yes, it’s very simple. Just inject the EJBContext into the interceptor using @Resource injection.

See the example code that uses methods of SessionContext as follows:

package actionbazaar.buslogic;

import javax.annotation.Resource;
import javax.interceptor.AroundInvoke;
import javax.interceptor.InvocationContext;

public class CheckPermissionInterceptor {
private javax.ejb.SessionContext ctx;

public Object checkUserRole(InvocationContext ic) throws Exception {
System.out.println("*** CheckPermission Interceptor invoked for "
+ ic.getTarget() + " ***");
if (!ctx.isCallerInRole("admin")) {
throw new SecurityException("User: '"
+ ctx.getCallerPrincipal().getName()
+ "' does not have permissions for method "
+ ic.getMethod());
return ic.proceed();

If you want the run-able version of the example code, you can download from http://manning.com/panda and look at Chapter 5 example.

11g SQLNet Client trace files

Fairlie Rego - Wed, 2008-07-30 03:14
I spent half an hour wondering why I was not able to generate a SQL* Net client trace file from a 11g client to a 10.2 database.
Here are the contents of my client sqlnet.ora

bart:ELEV:/opt/oracle/product/11.1.0/db_1/network/admin> cat sqlnet.ora

It took me a while to realize that thanks to ADR the client trace files were actually going to $ORACLE_BASE/diag/clients/user_oracle/host_xxx/trace

The only way to disable this is to add the following in the sqlnet.ora

After setting the above line in the sqlnet.ora the client trace files were generated in /tmp

How to enable FND_HELP search by creating an index on FND_LOBS efficiently

Aviad Elbaz - Tue, 2008-07-29 03:57

Have you ever tried to use the search option of Online Help in Oracle Applications?
Our users did... and they got "The page cannot be found" message...

I checked it on firefox, hope to get more accurate message, and I got this:
"Not Found. The requested URL /pls/DEV/fnd_help.search was not found on this server".


I checked fnd_help package and it's compiled and looks fine.

These errors appeared in error_log_pls:

[Mon Jul 28 10:34:54 2008] [warn] mod_plsql: Stale Connection due to Oracle error 20000
[Mon Jul 28 10:34:54 2008] [error] mod_plsql: /pls/DEV/fnd_help.search ORA-20000
ORA-20000: Oracle Text error:
DRG-10599: column is not indexed
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1209
ORA-06512: at "SYS.DBMS_SQL", line 328
ORA-06512: at "APPS.FND_HELP", line 1043
ORA-06512: at "APPS.FND_HELP", line 873
ORA-06512: at line 20

I found note 306239.1 - "Cannot Search Online Help After Fresh Install of 11.5.10" which suggest that Applications interMedia Text indexes are corrupt. In my instance it even wasn't exist...

I followed this note which instructs to:

  1. drop index FND_LOBS_CTX;
  2. Rebuild the index using aflobbld.sql

But aflobbld.sql had been running for more than 10 hours and the size of DR$FND_LOBS_CTX$I table has reached to 35 GB !
I had been wondering how it can be that fnd_lobs table is less than 1GB and the index on it is 35 GB and counting.... ?!

Note 396803.1 - "FND_LOBS_CTX is having huge size, how to reduce the sizeof the index?" suggests it's a bug, indexing all documents in FND_LOB table, also the binary files, while using wrong filter.

So how can we make aflobbld.sql to index only FND_HELP documents?

For each row in FND_LOBS table the file_format column is populated with one of the following values: IGNORE, BINARY, TEXT.
aflobbld.sql will index only rows that have this column set to BINARY or TEXT.
If we set all rows to IGNORE except FND_HELP rows, we could index them only.

Note 397757.1 - "How to Speed Up Index Creation on FND_LOBS by indexing Only FND_HELP Data" suggests the steps to do it.

These are the steps:

  1. Backup the fnd_lobs table before updating it, we will use it later:
    create table fnd_lobs_bk as select * from fnd_lobs;
    ** you can create a backup of this table and omit the file_date column to make this backup faster
  2. Drop index FND_LOBS_CTX if exists:
    drop index applsys.FND_LOBS_CTX;
  3. Update all rows to IGNORE except FND_HELP rows:
    update fnd_lobs
    set file_format = 'IGNORE'
    where nvl(program_name,'@') <> 'FND_HELP' ;
  4. Execute aflobbld.sql from OS terminal:
    sqlplus apps/sppas @$FND_TOP/sql/aflobbld.sql applsys apps;
  5. Since I'm not sure about the impact of leaving the FND_LOBS rows as IGNORE, I updated them back to the previous state:
    create unique index fnd_lobs_bk_u1 on fnd_lobs_bk (file_id);
    update (select fl.file_format ffo,flb.file_format ffb
            from fnd_lobs fl
                ,fnd_lobs_bk flb
            where fl.file_id = flb.file_id)
    set ffo=ffb;
    drop table fnd_lobs_bk;
  6. Check the search option.... it should work now.

You are welcome to leave a comment .


Categories: APPS Blogs

My Sessions at Oracle Open World 2008

Debu Panda - Tue, 2008-07-29 01:31

I’ve two sessions at Oracle Open World 2008, San Francisco. Here are my session details:

S298520 : Best Practices for Managing Your Oracle WebLogic Environment with Oracle Enterprise Manager
Track: Application Server and Transaction Processing
Room: Rm 2006
Date: 2008-09-22
Start Time: 11:30

S298522 : Top Five Things DBAs Must Know About Managing Middleware
Track: Application Server and Transaction Processing
Room: Rm 2003
Date: 2008-09-25
Start Time: 15:00

See you at San Francisco!

One off patches, conflicts and merges

Fairlie Rego - Mon, 2008-07-28 21:23
So you are applying one of the zillion patches on top of and you hit the following error


ApplySession applying interim patch '6338357' to OH
Interim patch 6338357 has File Conflict with patch(es) [ 5399670 ]
in OH /opt/oracle/product/10.2.0/db_1


Wouldn't it be nice if you could check for conflicts before you apply a patch
Here is one way to do the same

1. You can check which source code file is being modified by doing the following

cd 6338357/etc/config
cat actions | grep .o
oracle.rdbms version="" opt_req="R"
archive name="libserver10.a" path="%ORACLE_HOME%/lib" object_name="lib/libserver10.a/kelt.o"
make change_dir="%ORACLE_HOME%/rdbms/lib" make_file="ins_rdbms.mk" make_target="ioracle"

2. Then go to the $ORACLE_HOME/.patch_storage and see if any of the previous patches have modified the same file

bart:TEST:/opt/oracle/product/10.2.0/db_1/.patch_storage> find . -name 'kelt.o' -print

So this clearly shows that Patch 5399670 was previously applied which modified the same source code file and you need a merge patch for both bugs (5399670 and 6338357)

1Z0-043 ... Passed

Mihajlo Tekic - Sun, 2008-07-27 23:32
I passed the 1Z0-043 exam a week ago. While waiting on Oracle to process my hands-on course requirement I am thinking what should be the next step. Certainly OCM is on my list, but in the mean time I'd like to go for SQL Expert Certificate.

I have been studying very hard, especially the last month. I started with Oracle 10g Certification and Oracle Database 10g OCP Certification All-In-One Exam Guide, but the most of the time I spent reading from the Official Documentation and of course, practicing a lot.

The books I mentioned are good, they cover some basics, but I found they are not quite sufficient to prepare you for the exam. Some of the material is not up to date with the exam topics. The sample tests that come with the books contain some questions out of the scope of the exam. Some of the questions contain answers that are wrong or arguable.

It is very important that while preparing for the exam you practice a lot. It is not very difficult to set up a test environment for each of the topics. It was slightly complicated to create a test environment for ASM on my Ubuntu Linux, but the post from Grégory Guillou How To Set Up Oracle ASM on Ubuntu Gutsy Gibbon has helped me with that.

Finally, while preparing for the exam, I have improved my skills significantly and learned some new stuff that I didn't have any experience with before (Resource Manager for instance).

Latest additions to Oracle enhanced adapter

Raimonds Simanovskis - Sat, 2008-07-26 16:00

Short information about latest enhancements in ActiveRecord Oracle enhanced adapter:

  • Oracle enhanced adapter is now compatible with composite_primary_keys gem which is quite useful if you are working with legacy databases.
  • Adapter now is also working correctly with Rails 2.1 partial_updates enabled. Previously I mentioned that you needed to disable partial_updates when using CLOB/BLOB columns. Now the issue is found and fixed and partial_updates are working with CLOB/BLOB columns.
  • Support for other date and time formats when assigning string to :date or :datetime column. For example, if you would like to assign strings with format dd.mm.yyyy to date and datetime columns then add the following configuration options:
    ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.string_to_date_format = "%d.%m.%Y"
    ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.string_to_time_format = "%d.%m.%Y %H:%M:%S"

To get the new release of Oracle enhanced adapter do as always:

sudo gem install activerecord-oracle_enhanced-adapter
Categories: Development

Randy Pausch's passing

Peter Khos - Sat, 2008-07-26 12:57
I read in my newspaper that Randy Pausch has passed away yesterday from pancreatic cancer which he has been battling for many months. He was a Computer Science professor at Carnegie Mellon University and in Sept 2007, he gave a "final lecture" to over 400 students at the University.A "Final Lecture" is an academic traditional which say that if you know that you have a few months to live (Peter Khttp://www.blogger.com/profile/14068944101291927006noreply@blogger.com2

Line Manager Time Entry funtionality

RameshKumar Shanmugam - Sat, 2008-07-26 10:38
This post in the continuation of the Previous posts Various way of entering time into OTL

Oracle Time & Labor Line Manager functionality is available with the product out of Box, The User Function name is Timecard Mgr (Function -HXC_TIMECARDS_MGR).

This function is included in the Seeded Manager Self Service menu HR_LINE_MANAGER_ACCESS_V4.0


By default this function will allow the manager to enter his own time, which most of the customer wont prefer the Manager to put in his time in the same place where the Manager can enter time for his direct reports.

there is an very simple way to restrict Manager for entering his time using the Timecard Mgr function

we will be able to limit by setting an additional parameter on the Timecard Mgr function (Function - HXC_TIMECARDS_MGR). To limit the manager from entering their own data, the parameter - pManagerEnabled should be entered at the end of other parameters (connector is &) with an N being 'Not Enabled', and a Y equaling 'Enabled'.

Example - to disable Line Manager entry of their own time using the Line Manager functionality, the parameter is added as: &pManagerEnabled=N

Try this out
Categories: APPS Blogs

Microsoft acquires DATAllegro

Nigel Thomas - Fri, 2008-07-25 02:19
Lots of interesting posts on this news, from:
Meanwhile Seth Grimes also uses the news to point up a real difference in quality between Google and Microsoft search capabilities.

Creating a Windows Service that uses ODP.NET, Oracle Database, a Custom Event Log, and Local Service

Mark A. Williams - Thu, 2008-07-24 18:41

The steps necessary to create a Windows Service that accesses Oracle Database via ODP.NET and uses the "NT AUTHORITY\LOCAL SERVICE" low-privileged operating system account are not substantially different from the steps necessary to create a service that does not access Oracle Database. As such, the steps below are substantially similar to the steps in the "Walkthrough: Creating a Windows Service Application in the Component Designer" topic available in the Visual Studio Programmer's Guide for the .NET Framework. You may wish to review this walkthrough in addition to the steps I provide below.

My goal is to provide the steps necessary to create a (very) simplistic Windows Service that uses the "NT AUTHORITY\LOCAL SERVICE" account to "talk" to Oracle Database. This is by no means an all-encompassing treatment of the topic of using a Windows Service in conjunction with ODP.NET, etc. However, it should provide the minimum amount of information to get started down this path.

So, without further ado, let's get started...

The Setup

Because this solution needs an installer, I am not using an Express Edition of Visual Studio. I'm using Visual Studio 2005 with ODP.NET on a Windows XP Professional machine. This machine will also serve as the deployment target for the installer and is hosting an Oracle 11g Release 1 database.

Create the Visual Studio Project

1.  Select File -> New -> Project... from the main menu.

2.  In the New Project dialog expand the Visual C# node under Project types, click the Windows option, select Windows Service in the Templates list, enter OraWinService as the Name, and select an appropriate location (or simply accept the suggested location). The New Project dialog should resemble the following:


3.  Click OK to create the project.

Add a Reference to the ODP.NET Assembly

1.  Select Project -> Add Reference... from the main menu.

2.  In the Add Reference dialog, scroll down and select Oracle.DataAccess under the Component Name. The dialog should look similar to the following:


3. Click OK to add the reference.

Set the Service Properties

1.  The Service1.cs file should be displayed in the design view. If it is not, right-click the Service1.cs file in the Solution Explorer and select View Designer.

2.  Ensure the Service1.cs file is selected in the designer by clicking anywhere on the surface except for on the hyperlinks.

3.  In the Properties window for Service1.cs, enter OraWinSvcDemo for the (Name) property. Also enter OraWinSvcDemo for the ServiceName property. Set the CanPauseAndContinue property to True. The AutoLog property should also be set to True, which is the default. When complete, the Properties window should resemble:


Edit the Main Method

1.  Double-click the Program.cs file in the Solution Explorer to open it in the editor.

2.  Replace the auto-created Main method with the following code:

static void Main() {
  System.ServiceProcess.ServiceBase[] ServicesToRun;
  ServicesToRun = new System.ServiceProcess.ServiceBase[] { new OraWinSvcDemo() };

3.  Select File -> Save All from the main menu and then select File -> Close from the main menu to close the Program.cs file.

Add an EventLog Component

1.  With the Service1.cs file displayed in the design view, expand the Components node in the Toolbox and drag and drop an EventLog component onto the design surface. This will create an instance of the EventLog component named eventLog1.

2.  If eventLog1 is not selected, left-click it to select. In the Properties window enter OraWinSvcLog for the Log property and enter OraWinSvcSource for the Source property. The Properties window should now look as follows:


Define the Service Methods

1.  Right-click the Service1.cs file in the Solution Explorer and select View Code from the context menu to open the file in the editor.

2.  Add the following three lines to the end of the "list of using statements". A timer will be used to periodically trigger an event to write information to the Event Log. The other two lines are boilerplate inclusions for the ODP.NET namespaces.

using System.Timers;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

3.  At the top of the class definition (but outside of any method definitions) add the following (be sure to adjust the Data Source in the connection string as appropriate for your environment). The user will be created later:

string constr = "User Id=/; Data Source=orademo; Enlist=false";
OracleConnection con = null;
Timer timer1 = null;

At this time the entire Service1.cs file should contain the following:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.ServiceProcess;
using System.Text;
using System.Timers;

using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace OraWinService {
  public partial class OraWinSvcDemo : ServiceBase {
    string constr = "User Id=/; Data Source=orademo; Enlist=false";
    OracleConnection con = null;
    Timer timer1 = null;

    public OraWinSvcDemo() {

    protected override void OnStart(string[] args) {
      // TODO: Add code here to start your service.

    protected override void OnStop() {
      // TODO: Add code here to perform any tear-down necessary to stop your service.

4.  Change the constructor to the following to create the timer and set the handler function for the timer event:

public OraWinSvcDemo() {

  timer1 = new Timer();

  timer1.Elapsed += new ElapsedEventHandler(OnTimer);

5.  Add the OnTimer method below the constructor to handle the timer event:

private void OnTimer(object source, ElapsedEventArgs e) {
  if (con != null) {
    if (con.State == ConnectionState.Open) {
      OracleCommand cmd = con.CreateCommand();
      cmd.CommandText = "select user, sysdate from dual";
      OracleDataReader dr = cmd.ExecuteReader();
      while (dr.Read()) {
        eventLog1.WriteEntry(String.Format("User: {0}, Date: {1}", dr[0].ToString(), dr[1].ToString()));


6.  Replace the generated OnStart method with the following which establishes a connection to the database and sets the timer properties (interval of 3 seconds and enables the timer):

protected override void OnStart(string[] args) {
  eventLog1.WriteEntry("In OnStart");

  con = new OracleConnection(constr);

  try {
    eventLog1.WriteEntry("Successfully connected to Oracle!");
  catch (OracleException ex) {
    eventLog1.WriteEntry("OracleException: " + ex.Message);

  timer1.Interval = 3000;
  timer1.Enabled = true;

7.  Replace the generated OnStop method with the following code:

protected override void OnStop() {
  eventLog1.WriteEntry("In OnStop");

  timer1.Enabled = false;

  if (con != null) {

8.  Add a handler for the OnPause event for the service below the OnStop method:

protected override void OnPause() {
  eventLog1.WriteEntry("In OnPause.");

  timer1.Enabled = false;

9.  Add a handler for the OnContinue event for the service below the OnPause method:

protected override void OnContinue() {
  eventLog1.WriteEntry("In OnContinue.");

  timer1.Enabled = true;

10.  Add a handler for the shutdown event below the handler for the OnContinue event:

protected override void OnShutdown() {
  eventLog1.WriteEntry("In OnShutdown");

  timer1.Enabled = false;

  if (con != null) {

11.  Select File -> Save All from the main menu to save all work and File -> Close to close the Service1.cs file in the editor.

Create Installers for the Components

1.  Service1.cs should in the design view mode within the editor. If it is not, right-click it in the Solution Explorer and select View Designer from the context menu.

2.  Create an installer by right-clicking on the design surface and selecting Add Installer from the context menu:


3.  After clicking Add Installer, ProjectInstaller.cs will be created and opened in the editor. Click the Service1.cs [Design] tab to switch back to Service1.cs in the editor. Add an installer for the eventLog1 component by right-clicking it and selecting Add Installer from the context menu:


4.  After adding the eventLog1 installer, left-click the serviceProcessInstaller1 component to select it. Next select LocalService from the drop-down list:


5.  Select File -> Save All from the main menu to save all files.

6.  Select Build -> Build OraWinService from the main menu to build the project, then select File -> Close twice to close the ProjectInstaller.cs and Service1.cs files.

Create a Setup Project for the Service

1.  Select File -> Add -> New Project... from the main menu, in the Project types pane expand the Other Project Types node, select Setup and Deployment, select Setup Project in the Templates list, enter OraWinServiceSetup for the Name, and select an appropriate location (or accept the default supplied value). The Add New Project dialog should look as follows:


2.  Click OK to add the setup project to the solution.

3.  In Solution Explorer, right-click OraWinServiceSetup then select Add -> Project Output... from the context menu. The Add Project Output Group dialog will be displayed:


4.  Ensure Primary Output is selected and click OK.

5.  In Solution Explorer, right-click OraWinServiceSetup then select View -> Custom Actions from the context menu to open the custom actions in the editor.

6.  In the Custom Action editor, right-click Custom Actions and select Add Custom Action... from the context menu:


7.  In the Select Item in Project dialog, double-click the Application Folder item, then select Primary output from OraWinService (Active), and click OK to add the output to each of the Install, Commit, Rollback, and Uninstall nodes:


8.  Select File -> Save All from the main menu and then File -> Close for each opened file in the editor.

9.  Build the setup program by right-clicking OraWinServiceSetup in Solution Explorer and selecting Build from the context menu:


Install the Windows Service

1.  Using Windows Explorer navigate to the directory where the setup project was built. For example, on my system this is in C:\My Projects\Test\C#\OraWinServiceSetup\Debug directory.

2.  Install the service by right-clicking the OraWinServiceSetup.msi file and selecting Install from the context menu. Step through the installation wizard to complete the installation.

Create the Database User

1.  Connect to the database that the service will use as a DBA user and determine the value of the os_authent_prefix parameter:

SQL> show parameter os_authent_prefix

NAME              TYPE        VALUE
----------------- ----------- -----
os_authent_prefix string

2. As you can see here I do not have a value for this parameter; therefore, when I create the user I do not use a prefix:

SQL> create user "NT AUTHORITY\LOCAL SERVICE" identified externally;

User created.

SQL> grant create session to "NT AUTHORITY\LOCAL SERVICE";

Grant succeeded.

3.  For more information on creating an Operating System authenticated user, see my earlier post on Configuring Windows Authentication.

Running the Service and Viewing Results

1.  Open the Services management console by clicking Start -> Administrative Tools -> Services. You can also right-click the My Computer icon on the desktop and select Manage from the context menu.

2.  Locate the OraWinSvcDemo service in the list of services and click the start button:


3.  Open the Event Viewer by clicking Start -> Administrative Tools -> Event viewer. Or right-click the My Computer icon on the desktop and select Manage from the context menu (if you have not already done so).

4.  In the Event Viewer, select OraWinSvcLog by left-clicking it. As the service writes entries to the log every 3 seconds while it is executing, you should see several entries after a 10 seconds or so:


5.  Experiment with the different service states (paused, resume, restart, etc) and verify the entries in the Event Viewer.

Will the Optimizer development team be at Oracle Open World?

Oracle Optimizer Team - Thu, 2008-07-24 17:50
The largest gathering of Oracle customers, partners, developers, and technology enthusiasts will happen in September when Oracle will host its annual user conference Open World in San Francisco and the Optimizer development group will be there! You will have two opportunities to meet the team -- attend the technical presentation "Inside the 11g Optimizer - Removing the mystery" on Tuesday morning at 9am or stop by the Oracle demo grounds (in Moscone West) to see all of the demos for the 11g new features and ask the development team any burning questions you may have!

Will the Optimizer development team be at Oracle Open World?

Inside the Oracle Optimizer - Thu, 2008-07-24 17:50
The largest gathering of Oracle customers, partners, developers, and technology enthusiasts will happen in September when Oracle will host its annual user conference Open World in San Francisco and the Optimizer development group will be there! You will have two opportunities to meet the team -- attend the technical presentation "Inside the 11g Optimizer - Removing the mystery" on Tuesday morning at 9am or stop by the Oracle demo grounds (in Moscone West) to see all of the demos for the 11g new features and ask the development team any burning questions you may have!

Categories: DBA Blogs, Development


Subscribe to Oracle FAQ aggregator