A number of recent threads in the Oracle-L list have made it pretty clear that Automated Workload Repository (AWR) is a tool that you are expected to use when troubleshooting a database problem.
Never mind the fact that AWR is still a product that is licensed separately from the database, and that a large segment of the Oracle DBA population doesn't seem to realize that. Or that Active Session History (ASH) is part of AWR, and falls under the same license restrictions.
So I conducted a poll regarding the use of AWR. AWR Usage Poll. If you haven't in the AWR Poll, please do so.
While the web site does provide a chart of results, those results don't include the extra comments made by poll takers. You may are may not be able to download all the results, I'm not sure if that is restricted to the poll owner.
Nonetheless, I have compiled the results from a 100 or so respondents in to an Excel workbook, along with a few charts. You may find some of the additional comments of interest as well. AWR Usage Results
Draw your own conclusions regarding these results. I think it interesting to that AWR appears to be quite widely used. Personally I fall into the category of not using it because of the expense. I may work on changing that for a couple of key servers, as AWR is not that expensive, but in a small shop, spending $20k on feature that is not often needed is sometimes a hard sell.
One question I purposely left out was "Do you use AWR even though you have not licensed it"? While it might satisfy the curiosity of some (including me) I didn't want to give any Oracle sales people (or Oracle attorneys for that matter) any reasons to contact me regarding the poll.
In retrospect a good question would have been: "Did you realize AWR/ASH is a separately licensed product?". Too late to add that now, but bringing that up quite often leads to lively discussion.
Another interesting bit was that a few people have extended STATSPACK in some way, even using it on Oracle 10g+. One even mentioned the excellent repository of statspack scripts assembled by Tim Gorman. Tim Gorman's Statspack Scripts
Monday, August 18, 2008
Friday, August 01, 2008
An unusual cause of ORA-12154
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.
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:
As long as the connect string was in double quotes, no problem.
Now let's try it from a linux command line:
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:
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
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";
Connected.
scott SQL> select user from dual;
USER
------------------------------
SCOTT
SQL> connect scott/what@mistake
ERROR:
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 10.2.0.3.0 - Production on Fri Aug 1 15:42:20 2008
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
Linux> sqlplus scott/'what\@mistake'
SQL*Plus: Release 10.2.0.3.0 - Production on Fri Aug 1 15:46:14 2008
ERROR:
ORA-01017: invalid username/password; logon denied
Linux> sqlplus scott/"what\@mistake"
SQL*Plus: Release 10.2.0.3.0 - Production on Fri Aug 1 15:46:21 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Linux> sqlplus 'scott/what\@mistake'
SQL*Plus: Release 10.2.0.3.0 - Production on Fri Aug 1 15:47:23 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Linux > sqlplus scott/what\@mistake
SQL*Plus: Release 10.2.0.3.0 - Production on Fri Aug 1 15:48:52 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
ERROR:
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'
X
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
Subscribe to:
Posts (Atom)